Sql Queries For Best Practices

Sql quires for Interview preparation and for best practices

Answers for the Sql queries

1. SQL > Select * from employee;

2. SQL > Select * from department;

3. SQL > Select * from job;

4. SQL > Select * from loc;

5. SQL > Select first_name, last_name, salary, commission from employee;

6. SQL > Select employee_id “id of the employee”, last_name “name", department id as “department id” from employee;

7. SQL > Select last_name, salary*12 “annual salary” from employee

8. SQL > Select * from employee where last_name=’SMITH’;

9. SQL > Select * from employee where department_id=20

10. SQL > Select * from employee where salary between 3000 and 4500

11. SQL > Select * from employee where department_id in (20,30)

12. SQL > Select last_name, salary, commission, department_id from employee where department_id not in (10,30)

13. SQL > Select * from employee where last_name like ‘S%’

14. SQL > Select * from employee where last_name like ‘S%H’

15. SQL > Select * from employee where last_name like ‘S___’

16. SQL > Select * from employee where department_id=10 and salary>3500

17. SQL > Select * from employee where commission is Null

18. SQL > Select employee_id, last_name from employee order by employee_id

19. SQL > Select employee_id, last_name, salary from employee order by salary desc

20. SQL > Select employee_id, last_name, salary from employee order by last_name, salary desc

21. SQL > Select employee_id, last_name, salary from employee order by last_name, department_id desc

22. SQL > Select department_id, count(*), from employee group by department_id

23. SQL > Select department_id, count(*), max(salary), min(salary), avg(salary) from employee group by department_id

24. SQL > Select job_id, count(*), max(salary), min(salary), avg(salary) from employee group by job_id

25. SQL > Select to_char(hire_date,’month’)month, count(*) from employee group by to_char(hire_date,’month’) order by month

26. SQL > Select to_char(hire_date,’yyyy’) Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’)

27. SQL > Select department_id, count(*) from employee group by department_id having count(*)>=4

28. SQL > Select to_char(hire_date,’mon’) month, count(*) from employee group by to_char(hire_date,’mon’) having to_char(hire_date,’mon’)=’jan’

29. SQL > Select to_char(hire_date,’mon’) month, count(*) from employee group by to_char(hire_date,’mon’) having to_char(hire_date,’mon’) in (‘jan’,’sep’)

30. SQL > Select to_char(hire_date,’yyyy’) Year, count(*) from employee group by to_char(hire_date,’yyyy’) having to_char(hire_date,’yyyy’)=1985

31. SQL > Select to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 group by to_char(hire_date,’yyyy’),to_char(hire_date,’mon’)

32. SQL > Select to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 and to_char(hire_date,’mon’)=’mar’ group by to_char(hire_date,’yyyy’),to_char(hire_date,’mon’)

33. SQL > Select department_id, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 and to_char(hire_date,’mon’)=’apr’ group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’), department_id having count(*)>=3

34. SQL > Select * from employee where salary=(select max(salary) from employee)

35. SQL > Select * from employee where department_id IN (select department_id from department where name=’SALES’)

36. SQL > Select * from employee where job_id in (select job_id from job where function=’CLERK’

37. SQL > Select * from employee where department_id=(select department_id from department where location_id=(select location_id from location where regional_group=’New York’))

38. SQL > Select * from employee where department_id=(select department_id from department where name=’SALES’ group by department_id)

39. SQL > Update employee set salary=salary*10/100 wehre job_id=(select job_id from job where function=’CLERK’)

40. SQL > delete from employee where department_id=(select department_id from department where name=’ACCOUNTING’)

41. SQL > Select * from employee where salary=(select max(salary) from employee where salary <(select max(salary) from employee))

42. SQL > Select distinct e.salary from employee where & no-1=(select count(distinct salary) from employee where sal>e.salary)

43. SQL > Select * from employee where salary > all (Select salary from employee where department_id=30)

44. SQL > Select * from employee where salary > any (Select salary from employee where department_id=30)

45. SQL > Select employee_id, last_name, department_id from employee e where not exists (select department_id from department d where d.department_id=e.department_id)

46. SQL > Select name from department d where not exists (select last_name from employee e where d.department_id=e.department_id)

47. SQL > Select employee_id, last_name, salary, department_id from employee e where salary > (select avg(salary) from employee where department_id=e.department_id)

48. SQL > Select employee_id, last_name, name from employee e, department d where e.department_id=d.department_id

49. SQL > Select employee_id, last_name, function from employee e, job j where e.job_id=j.job_id

50. SQL > Select employee_id, last_name, name, regional_group from employee e, department d, location l where e.department_id=d.department_id and d.location_id=l.location_id

51. SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name

52. SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name having name=’SALES’

53. SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name having count (*)>=5 order by name

54. SQL > Select function, count(*) from employee e, job j where j.job_id=e.job_id group by function

55. SQL > Select regional_group, count(*) from employee e, department d, location l where e.department_id=d.department_id and d.location_id=l.location_id and regional_group=’NEW YORK’ group by regional_group

56. SQL > Select employee_id, last_name, grade_id from employee e, salary_grade s where salary between lower_bound and upper_bound order by last_name

57. SQL > Select grade_id, count(*) from employee e, salary_grade s where salary between lower_bound and upper_bound group by grade_id order by grade_id desc

58. SQL > Select grade_id, count(*) from employee e, salary_grade s where salary between lower_bound and upper_bound and lower_bound>=2000 and lower_bound<=5000 group by grade_id order by grade_id desc

59. SQL > Select e.last_name emp_name, m.last_name, mgr_name from employee e, employee m where e.manager_id=m.employee_id

60. SQL > Select e.last_name emp_name, e.salary emp_salary, m.last_name, mgr_name, m.salary mgr_salary from employee e, employee m where e.manager_id=m.employee_id and m.salary

61. SQL > Select m.manager_id, count(*) from employee e, employee m where e.employee_id=m.manager_id group by m.manager_id

62. SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id(+)=d.department_id

63. SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id(+)=d.department_id and d.department_idin (select department_id from department where name IN (‘SALES’,’OPERATIONS’))

64. SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’SALES’)) union Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’ACCOUNTING’))

65. SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’SALES’)) union all Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’ACCOUNTING’))

66. SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’RESEARCH’)) intersect Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’ACCOUNTING’)) order by function

Joining Three or More Tables

Joining Three or More Tables

Although each join specification joins only two tables, FROM clauses can contain multiple join specifications. This allows many tables to be joined for a single query.

The titleauthor table of the pubs database offers a good example of a situation in which joining more than two tables is helpful. This Transact-SQL query finds the titles of all books of a particular type and the names of their authors:

USE pubs
SELECT a.au_lname, a.au_fname, t.title
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id JOIN titles t
ON ta.title_id = t.title_id
WHERE t.type = 'trad_cook'
ORDER BY t.title ASC

Here is the result set:

au_lname          au_fname             title
----------------- -------------------- ---------- 

Blotchet-Halls      Reginald          Fifty Years in Buckingham Palace
Kitchens
Panteley            Sylvia            Onions, Leeks, and Garlic: 
Cooking Secrets of the Mediterranean
O'Leary             Michael            Sushi, Anyone?
Gringlesby          Burt               Sushi, Anyone?
Yokomoto            Akiko              Sushi, Anyone?
(5 row(s) affected)

Notice that one of the tables in the FROM clause, titleauthor, does not contribute any columns to the results. Also, none of the joined columns, au_id and title_id, appear in the results. Nonetheless, this join is possible only by using titleauthor as an intermediate table.

The middle table of the join (the titleauthor table) can be called the translation table or intermediate table, because titleauthor is an intermediate point of connection between the other tables involved in the join.

When there is more than one join operator in the same statement, either to join more than two tables or to join more than two pairs of columns, the join expressions can be connected with AND or with OR.

See Also http://msdn2.microsoft.com/en-us/library/aa213227(SQL.80).aspx