Structured query Language Questions

TABLE : DEPT

DEPT NO	DAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

TABLE : EMP

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPT NO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	2-Apr-81	2975		20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	1-May-81	2850		30
7782	CLARK	MANAGER	7839	9-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	9-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	8-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7900	JAMES	CLERK	7698	3-Dec-81	950		30
7902	FORD	ANALYST	7566	3-Dec-81	3000		20
7934	MILLER	CLERK	7782	23-Jan-82	1300		10

                                                  Questions ON OPERATORS

   1)    Display all the employees who are getting 2500 and excess salaries in department 20.

2)    Display all the managers working in 20 & 30 department.

3)    Display all the managers who don’t have a manager

4)    Display all the employees who are getting some commission with their designation is neither MANAGER nor ANALYST

5)    Display all the Analysts whose name doesn’t ends with ‘S’

6)    Display all the employees whose naming is having letter ‘E’ as the last but one character

7)    Display all the employees who total salary is more than 2000.

(Total Salary = Sal + Comm)

         

8)    Display all the employees who are getting some commission in department 20 & 30.

9)    Display all the managers whose name doesn't start with A & S

10)  Display all the employees who earning salary not in the range of 2500 and 5000 in department 10 & 20.

QUESTIONS ON GROUPING

11)   Display job-wise maximum salary.

12)   Display the departments that are having more than 3 employees under it.

13)   Display job-wise average salaries for the employees whose employee number is not from 7788 to 7790.

14)   Display department-wise total salaries for all the Managers and Analysts, only if the average salaries for the same is greater than or equal to 3000.

Consider the following table: -

     Questions 5,6,7 & 8 are based on the following table

Table Name : SKILLS

ID	Name
101	Oracle
102	Oracle
103	Oracle
101	Oracle
102	Java
103	Java
101	Java
102	Java
103	Java
101	Java
101	Java
101	Oracle
101	VB
102	ASP

15)   Select only the duplicate records along-with their count.

16)   Select only the non-duplicate records.

17)   Select only the duplicate records that are duplicated only once.

18)   Select only the duplicate records that are not having the id=101.

Questions ON SUB QUERIES

19)    Display all the employees who are earning more than all the managers.

20) Display all the employees who are earning more than any of the managers.

21) Select employee number, job & salaries of all the Analysts who are earning more than any of the managers.

22) Select all the employees who work in DALLAS.

23) Select department name & location of all the employees working for CLARK.

24) Select all the departmental information for all the managers

25) Display the first maximum salary.

26) Display the second maximum salary.

27) Display the third maximum salary.

28) Display all the managers & clerks who work in Accounts and Marketing departments.

29) Display all the salesmen who are not located at DALLAS.

30)               Get all the employees who work in the same departments as of SCOTT.

31)               Select all the employees who are earning same as SMITH.

32)               Display all the employees who are getting some commission in marketing department where the employees have joined only on weekdays.

33)               Display all the employees who are getting more than the average salaries of all the employees.

Questions ON JOINS

Questions ON EQUI-JOINS

34) Display all the managers & clerks who work in Accounts and Marketing departments.

35) Display all the salesmen who are not located at DALLAS.

36) Select department name & amp; location of all the employees working for CLARK.

37) Select all the departmental information for all the managers

38) Select all the employees who work in DALLAS.

39)               Delete the records from the DEPT table that don’t have matching records in EMP

Questions ON OUTER-JOINS

40)  Display all the departmental information for all the existing employees and if a department has no employees display it as “No employees”.

41) Get all the matching & non-matching records from both the tables.

42) Get only the non-matching records from DEPT table (matching records shouldn’t be selected).

43) Select all the employees name along with their manager names, and if an employee does not have a manager, display him as “CEO”.

Questions ON SELF-JOINS

44) Get all the employees who work in the same departments as of SCOTT

45) Display all the employees who have joined before their managers.

46) List all the employees who are earning more than their managers.

47) Fetch all the employees who are earning same salaries.

48) Select all the employees who are earning same as SMITH.

49)               Display employee name , his date of joining, his manager name & his manager's date of joining.