Intersect and Except Operator in SQL

Intersect and Except Operator in SQL

In SQL, the INTERSECT and EXCEPT operators are used to perform set operations between two queries. These operators are typically used in conjunction with the SELECT statement to retrieve data from tables based on certain conditions. Let's take a closer look at each operator:

INTERSECT Operator:

• Think of it like finding common elements.
• If you have two sets (like lists of things), INTERSECT helps you find the items that are present in both sets.
• In SQL, this means you get rows that match between two queries.
• INTERSECT is used to retrieve common rows that exist in two SELECT statements.
• It returns only the rows that are common to both result sets of the SELECT statements.

Let's consider a scenario where we have two tables, "employees" and "managers," both containing information about employees, and we want to find the common employees who are both employees and managers. The common employees are those who exist in both tables.

-- Create sample tables CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(50) ); CREATE TABLE managers ( manager_id INT PRIMARY KEY, manager_name VARCHAR(50) ); -- Insert sample data INSERT INTO employees (employee_id, employee_name) VALUES (1, 'John Doe'), (2, 'Jane Smith'), (3, 'Bob Jones'); INSERT INTO managers (manager_id, manager_name) VALUES (2, 'Jane Smith'), (3, 'Bob Jones'), (4, 'Alice Johnson'); -- Find common employees who are both employees and managers SELECT employee_id, employee_name FROM employees INTERSECT SELECT manager_id, manager_name FROM managers;

In this example:

• The employees table represents regular employees.
• The managers table represents employees who are also managers.

The INTERSECT operator is used to find employees who exist in both tables. The result would be the common employees who are both regular employees and managers. In this case, the result would include Jane Smith and Bob Jones because they are present in both tables.

In SQL, when using the INTERSECT operator, the columns selected in both queries must have the same names and data types.

EXCEPT Operator:

• Think of it like finding the unique items.
• If you have two sets, EXCEPT helps you find the items that are in the first set but not in the second set.
• In SQL, this means you get rows from the first query that are not present in the second query.
• EXCEPT is used to retrieve rows from the result set of the first SELECT statement that do not appear in the result set of the second SELECT statement.
• It returns the distinct rows from the first SELECT statement that are not present in the second SELECT statement.

Let's consider a scenario where we have two tables, "developers" and "managers," both containing information about employees, and we want to find the developers who are not managers. The EXCEPT operator can be used to achieve this.

-- Create sample tables CREATE TABLE developers ( employee_id INT PRIMARY KEY, employee_name VARCHAR(50) ); CREATE TABLE managers ( employee_id INT PRIMARY KEY, manager_name VARCHAR(50) ); -- Insert sample data INSERT INTO developers (employee_id, employee_name) VALUES (1, 'John Doe'), (2, 'Jane Smith'), (3, 'Bob Jones'); INSERT INTO managers (employee_id, manager_name) VALUES (2, 'Jane Smith'), (3, 'Bob Jones'), (4, 'Alice Johnson'); -- Find developers who are not managers SELECT employee_id, employee_name FROM developers EXCEPT SELECT employee_id, manager_name FROM managers;

In this example:

• The developers table represents employees who are developers.
• The managers table represents employees who are managers.

The EXCEPT operator is used to find employees who are in the developers table but not in the managers table. The result would be the developers who are not also managers. In this case, the result would include John Doe, as he is a developer but not a manager.

• The INTERSECT operator finds common elements, while the EXCEPT operator finds unique elements in the first set.
• You can use these operators to compare and analyze data in different ways, just like comparing sets of items.

NOTE:-

• JOIN is used to combine rows based on related columns.
• INTERSECT is used to retrieve common rows between two result sets.
• EXCEPT is used to retrieve distinct rows from the first result set that are not present in the second result set.

Union and Union All In SQL

Union and Union All

In SQL, UNION and UNION ALL are used to combine the results of two or more SELECT statements. Both operations are used to combine rows from multiple tables or queries, but they have some key differences.

UNION:

• The UNION operator is used to combine the result sets of two or more SELECT statements while eliminating duplicate rows.
• It returns only distinct (unique) rows from the combined result sets.
• The columns in each SELECT statement must be of the same data type and in the same order.

Syntax

SELECT column1, column2, ...

FROM table1

UNION

SELECT column1, column2, ...

FROM table2;

UNION ALL:

• The UNION ALL operator, like UNION, is used to combine the result sets of two or more SELECT statements, but it does not eliminate duplicate rows.
• It includes all rows from all SELECT statements, regardless of whether they are duplicates.
• UNION ALL is generally faster than UNION because it does not need to perform the additional step of removing duplicates.

Syntax

SELECT column1, column2, ...

FROM table1

UNION ALL

SELECT column1, column2, ...

FROM table2;

Example

Certainly! Let's consider three tables: employees, contractors, and clients. We will use the following example tables:

Create and Insert Query for employees table:

Create employees table

CREATE TABLE employees (

    employee_id INT PRIMARY KEY,

    employee_name VARCHAR(50)

);

Insert data into employees table

INSERT INTO employees (employee_id, employee_name) VALUES

(1, 'John'),

(2, 'Alice'),

(3, 'Bob'),

(4, 'Mary');

Create and Insert Query for contractors table:

Create contractors table

CREATE TABLE contractors (

    contractor_id INT PRIMARY KEY,

    contractor_name VARCHAR(50)

);

Insert data into contractors table

INSERT INTO contractors (contractor_id, contractor_name) VALUES

(2, 'Alice'),

(3, 'Bob'),

(5, 'Charlie'),

(6, 'Diane');

Create and Insert Query for clients table:

Create clients table

CREATE TABLE clients (

    client_id INT PRIMARY KEY,

    client_name VARCHAR(50)

);

Insert data into clients table

INSERT INTO clients (client_id, client_name) VALUES

(1, 'XYZ Corp'),

(3, 'ABC Inc'),

(5, 'Acme Co'),

(6, 'Tech Ltd');

Now, let's see examples for both UNION and UNION ALL

Example using UNION:

SELECT employee_id, employee_name

FROM employees

UNION

SELECT contractor_id, contractor_name

FROM contractors;

Result

Explanation: The UNION operator combines the unique rows from both tables, and duplicates are removed. In this case, it returns a result set with distinct combinations of employee_id and employee_name from the employees table and contractor_id and contractor_name from the contractors table.

Example using UNION ALL:

SELECT employee_id, employee_name

FROM employees

UNION ALL

SELECT contractor_id, contractor_name

FROM contractors;

Result

Explanation: The UNION ALL operator combines all rows from both tables, including duplicates. In this case, it returns a result set with all combinations of employee_id and employee_name from the employees table and contractor_id and contractor_name from the contractors table, without removing duplicates.

Now, let's see an example using UNION and UNION ALL with these three tables.

Example using UNION:

SELECT employee_id, employee_name

FROM employees

UNION

SELECT contractor_id, contractor_name

FROM contractors

UNION

SELECT client_id, client_name

FROM clients;

Result

Example using UNION ALL:

SELECT employee_id, employee_name

FROM employees

UNION ALL

SELECT contractor_id, contractor_name

FROM contractors

UNION ALL

SELECT client_id, client_name

FROM clients;

Result

SQL INTERVIEW QUESTIONS ON JOINS [EQUI JOIN, NATURAL JOIN, OUTER JOIN, SELF JOIN]

1. LIST ENAME, JOB, ANNUAL SAL, DEPTNO, DNAME WHO EARN 30000 PER YEAR AND WHO ARE NOT CLERKS .

SQL INTERVIEW QUESTIONS ON SUB-QUERY

SQL INTERVIEW QUESTIONS ON SUB-QUERY

1. DISPLAY ALL THE EMPLOYEES WHOSE DEPARTMET NAMES ENDING 'S'
2. QUERY TO DISPLAY THE EMPLOYEE NAMES WHO IS HAVING MAXIMUM SALARY IN DEPT NAME "ACCOUNTING"
3. QUERY TO DISPLAY THE DEPT NAME WHO IS HAVING HIGHEST COMMISSION
4. QUERY TO DISPLAY THE EMPLOYEE NAMES WHOSE DEPARTMENT NAME HAS 2ND CHARACTER AS 'O'.
5. QUERY TO DISPLAY ALL THE EMPLOYEES WHO’S DEPT NUMBER IS SAME AS SCOTT.
6. QUERY TO DISPLAY ALL THE EMPLOYEES IN 'OPERATIONS AND ACCOUNTING' DEPT.
7. LIST THE EMPLOYEES WHO HAS SALARY GREATER THAN MILLER
8. LIST DEPARTMENT NAME HAVING ATLEAST 3 SALESMAN
9. DISPLAY THE DNAME OF AN EMPLOYEES WHO HAS NO REPORTING MANAGER.
10. LIST ALL THE EMPLOYEES WHO ARE REPORTING TO JONES MANAGER
11. LIST EMPLOYEES FROM RESEARCH & ACCOUNTING HAVING ATLEAST 2 REPORTING.
12. DISPLAY THE DEPARTNAME OF THE EMPLOYEE WHOSE NAME DOES NOT STARTS WITH S AND SALARY BETWEEN 1500 TO 3000.
13. DISPLAY LOCATION OF EMPLOYEE WHOSE SALARY IS MINIMUM SALARY BUT SALARY IS GREATER THAN 2000
14. DISPLAY THE LOCATION OF AN EMPLOYEE IN ACCOUNTING DEPARTMENT.
15. DISPLAY THE DEPARTMENT ‘S LOCATION THAT IS HAVING GREATER THAN FOUR EMPLOYEES IN IT.
16. WRITE A QUERY TO DISPLAY ALL THE EMPLOYEE WHOSE JOB NOT SAME AS ALLEN AND SALARY IS GREATER THAN MARTIN.
17. DISPLAY ALL THE EMPLOYEES WHO IS HAVING LOCATION IS SAME AS ADAM'S MANAGER?
18. DISPLAY THE JOB, MANAGER NUMBER OF EMPLOYEES WHO IS WORKING FOR JONES?
19. DISPLAY THE EMPLOYEE NAMES, HIGHER DATE, COMMISSION OF FORD'S MANAGER?
20. DISPLAY THE NUMBER OF EMPLOYEES WHO ARE GETTING SALARY LESS THAN THE BLAKE'S MANAGER
21. LIST EMPLOYEES WHO LOCATED IN CHICAGO AND THEIR COMMISSION IS ZERO.
22. LIST EMPLOYEES WHO WORK FOR SALES DEPARTMENT AND THEIR SALARY GREATER THAN AVERAGE SALARY OF THEIR DEPARTMENT.
23. LIST EMPLOYEES WHO ARE WORKING IN RESEARCH DEPARTMENT AND THEY ARE MANAGER.
24. DISPLAY DEPARTMENT NAME OF THE EMPLOYEES WHO EARN COMMISSION.
25. DISPLAY DEPARTMENT NAME OF THE EMPLOYEE WHO EARN MAXIMUM SALARY AND HAVE NO REPORTING MANAGER.
26. DISPLAY EMPLOYEE DETAILS WHO ARE REPORTING TO BLAKE AND HAVE COMMISSION WITHOUT USING NULL OR NOT NULL
27. LIST ALL THE DEPTNAME AND LOC OF ALL THE SALESMAN MANAGER-MANAGER'S
28. LIST THE EMPLOYEE DEPTNAME AND LOC OF ALL THE EMPLOYEES WHO ARE CLERK, REPORTING TO BLAKE AND SALARY IS LESSER THAN MARTIN SALARY
29. LIST THE EMPLOYEES WHO DOES NOT DIRECTLY REPORT TO PRESIDENT,HAVE COMMISSION AND SALARY MORE THAN MAX SALARY OF ALL THE CLERK WITHOUT USING NULL OR NOT NULL
30. LIST THE EMPLOYEES WHO JOINED AFTER 2 YEARS OF FIRST EMPLOYEE OF THE COMPANY AND MORE THAN BLAKE SALARY
31. DISPLAY LOCATION OF ALL THE EMPLOYEES WHO REPORTING TO BLAKE
32. LIST ALL THE EMPLOYEES WHOSE JOB IS SAME AS JONES AND THEIR SALARY LESSER THAN SCOTT
33. DISPLAY ALL THE EMPLOYEES OF DEPARTMENT 30, 20 WITH THERE ANUAL SALARY AND HAVING ATLEAST 3 EMPLOYEES
34. DISPLAY ALL THE EMPLOYEES WHO ARE EARN LESS THAN ANY OF THE SALESMAN?
35. DISPLAY ALL THE EMPLOYEES WHO ARE JOINED BEFORE THE LAST PERSON?
36. FIND 3RD MINIMUM SALARY IN THE EMPLOYEE TABLE.
37. DISPLAY ALL THE EMPLOYEES WHO ARE EARNING MORE THAN ANY OF THE MANAGER.
38. LIST EMPLOYEES WHO JOINED AFTER 4 YEARS OF 1ST EMPLOYEE OF THE COMPANY AND LESS THAN BLAKE SALARY.
39. DISPLAY THE DEPARTMENT INFORMATION OF EMPLOYEE WHO IS WORKING FOR NEW YORK LOCATION
40. DISPLAY LOCATION OF EMPLOYEES, WHOSE NAME DOESN'T START WITH A AND SALARY BETWEEN 1000 AND 3000.
41. DISPLAY DEPARTMENT NAME OF ALL THE EMPLOYEES WHO ARE REPORTING TO BLAKE.
42. DISPLAY MARTIN'S MANAGER'S MANAGER'S DEPARTMENT NAME AND LOCATION.
43. DISPLAY THE MANAGER NUMBER,JOB AND DEPARTMENT NUMBER FOR THOSE WHO DON'T HAVE COMMISSION IN THE LOCATION CHICAGO OR DALLAS
44. DISPLAY THE EMPLOYEE DETAILS WITH THEIR ANNUAL SALARY WHO EARN MAXIMUM COMMISSION
45. DISPLAY ALL THE EMPLOYEE WHOSE DEPARTMENT IS SALES AND WHO IS EARNING SOME COMMISSION (I.E COMMISSION IS NOT NULL OR ZERO)AND WHO IS HIRED BEFORE THE LAST PERSON HIRED.
46. DISPLAY ALL THE DEPARTMENT NAMES FOR WARD'S MANAGER'S MANAGER
47. DISPLAY DEPARTMENT NAMES OF EMPLOYEE'S WHOSE SALARY IS GREATER THAN AVERAGE SALARY OF ALL THE CLERK'S
48. DISPLAY THE LAST EMPLOYEE RECORD WITH 25% HIKE IN SALARY.
49. DISPLAY THE DEPARTMENT NUMBER WHO WORKING IN SALES DEPARTMENT AND THEY ARE MANAGER.
50. DISPLAY DEPARTMENT NAME OF THE EMPLOYEE WHO EARN MINMUM SALARY AND HAVE REPORTING MANAGER.
51. DISPLAY HIREDATE AND JOB OF ALL THE EMPLOYEES WORKING FOR SALES
52. DISPLAY LOCATION AND DNAME OF EMPLOYEE WHO WORKING AS PRESIDENT
53. DISPLAY THE DNAME OF EMPLOYEES WHOES SALARY IS MAXIMUM SALARY BUT LESSER THAN 3000
54. DISPLAY THE DEPARTMENT NAME WHO ARE REPORTING TO ADAMS.
55. DISPLAY LAST EMPLOYEE RECORD ACCORDING TO EMPNO.
56. DISPLAY ALL THE EMPLOYEE WHOSE SALARY IS GREATER THAN AVERAGE SALARY OF DEPARTMENT 30.
57. DISPLAY THE NUMBER OF EMPLOYEES WHO WORK FOR RESEARCH DEPT AND THEIR SALARY IS LESSER THAN ONE OF THE SALARY IN DEPARTMENT IN 10.
58. DISPLAY THE DNAME THAT ARE HAVING CLERK IN IT.
59. DISPLAY THE DEPARTMENT NAMES THAT ARE HAVING ATLEAST ONE L IN IT.
60. DISPLAY ALL THE EMPLOYEES WHO ARE JOINED AFTER BLAKE.
61. LIST THE DEPT NAME THAT ARE HAVING AT LEAST 3 EMPLOYEES BUT NOT MORE THAN 5 EMPLOYEES IN IT.
62. DISPLAY THE LOCATION OF ALL EMPLOYEES WHOSE REPORTING MANAGER SALARY IS GREATER THAN 2000.
63. SELECT THE EMPLOYEES WHOSE DNAME IS HAVING AT LEAST TWO 'E' IN IT.
64. DISPLAY ENAME,SAL OF EMPLOYEES WHO ARE EARNING MORE THAN ANY OF THE ANALYST.
65. SELECT ALL THE EMPLOYEES WHO ARE WORKING FOR CHICAGO
66. QUERY TO DISPLAY EMPLOYEE NAMES WHO IS HAVING MINIMUM SALARY IN DEPARTMENT RESEARCH.
67. LIST THE DEPARTMENT NAMES THAT ARE HAVING SALESMAN.
68. LIST THE DEPARTMENT NAMES THAT ARE HAVING AT LEAST 3 EMPLOYES IN IT.
69. LIST EMPLOYEES FROM RESEARCH AND ACCOUNTING DEPARTMENT HAVING ATLEAST TWO REPORTING
70. WRITE A QUERY TO DISPLAY EMPLOYEE NAME, JOB,LOCATION OF ALL EMPLOYEES WHO ARE WORKING AS MANAGER AND WORKS AT CHICAGO.
71. SELECT ENAME OF EMPLOYEE WHO EARNS 2ND MAX SALARY AND WORKS FOR LOCATION DALLAS.
72. WRITE A QUERY TO DISPLAY THE EMPLOYEE INFORMATION WHO IS NOT TAKING COMMISSION AND JOINED COMPANY AFTER JULY 83.
73. LIST EMPLOYEES FROM SALES AND RESEARCH DEPARTMENT HAVING ATLEAST 2 REPORTING EMPLOYEES .
74. LIST EMPLOYEES WHO HAVE COMMISSION GREATER THAN MAXIMUM SALARY OF ALL THE SALESMAN AND WHO DO NOT REPORT TO KING DIRECTLY .
75. DISPLAY THE LOCATION OF ALL THE DEAPRTMENTS WHICH HAVE EMPLOYEES JOINED IN THE YEAR 81
76. DISPLAY DEPARTMENT WISE MINIMUM SALARY WHICH IS LESS THAN AVERAGE SALARY OF EMPLOYEES.
77. DISPLAY ALL THE EMPLOYEES WHO ARE REPORTING TO 'JONES'.
78. DISPLAY ALL THE EMPLOYEE INFORMATION WHO ARE LIVING IN A LOCATION WHICH IS HAVING AT LEAST 2 'O' IN IT.
79. DISPLAY THE NAMES OF EMPLOYEE FROM DEPARTMENT NUMBER 10 WITH SALARY GREATER THAN THAT OF ALL EMPLOYEE WORKING IN OTHER DEPARTMENTS.
80. DISPLAY THE NAMES OF EMPLOYEES WHO EARN HIGHEST SALARY IN THEIR RESPECTIVE JOBS.
81. DISPLAY THE EMPLOYEE NUMBER AND NAME OF EMPLOYEE WORKING AS CLERK AND EARNING HIGHEST SALARY AMONG CLERKS.
82. WRITE A QUERY TO FIND SMITH'S MANAGER'S MANAGER HIREDATE.
83. LIST THE NUMBER OF EMPLOYEES WHOSE JOB IS SALESMAN WORKING FOR NEWYORK AND CHICAGO
84. LIST THE DEPARTMENT NAMES IN WHICH THE EMPLOYEES ARE HIRED BETWEEN 1ST OF JAN 1981 AND 31ST DEC 1982 WITH SALARY MORE THAN 1800.
85. DISPLAY LOCATION OF THE EMPLOYEE WHO EARN MAXIMUM SALARY AND HAVE NO REPORTING MANAGER
86. LIST EMPLOYEES WHO WORKS FOR ACCOUNTING DEPARTMENT AND THEIR SALARY GREATER THAN AVERAGE SALARY OF THEIR DEPARTMENT
87. DISPLAY LOCATION OF THE EMPLOYEE WHO EARN COMMISSION
88. LIST THE EMPLOYEES WHO DOES NOT DIRECTLY REPORT TO PRESIDENT,HAVE COMMISSION AND SALARY MORE THAN MAX SALARY OF ALL THE CLERK WITHOUT USING NULL OR NOT NULL
89. DISPLAY ALL THE EMPLOYEES WHOSE SALARY IS GREATER THAN AVG SAL OF DEPARTMENT 20
90. LIST THE EMPLOYEE DEPTNAME AND LOC OF ALL THE EMPLOYEES WHO ARE CLERK ,REPORTING TO BLAKE AND SALARY IS LESSER THAN MARTIN SALARY
91. DISPLAY LOC AND DNAME WHOSE JOB IS MANAGER AND HAS SAL LESS THAN CLERK.
92. DISPLAY EMPLOYEES LOCATION WHO HAS SOME COMMISSION.
93. DISPLAY EMPNO, ENAME, JOB, WHOSE JOB HAS 'E' IN IT AND DISPLAY EMPNO IN DESCENDING ORDER.
94. DISPLAY DNAME, LOC,DEPTNO OF EMPLOYEES WHO HAS SAME REPORTING MANAGER???
95. DISPLAY AVG SALARY OF ALL EMPLOYEES WHOSE DEPT NAME IS ACCOUNTING???
96. DISPLAY ALL EMPLOYEES DETAILS WHOSE HIREDATE IS IN YEAR 81???
97. DISPAY DETAILS OF SMITH AND EMPLOYEES WORKING AS PRESIDENT ALONG WITH HIKE OF 35% IN SALARY.
98. DISPLAY NUMBER OF EMPLOYEES WHOSE COMMISSION IS MORE THAN SALARY.
99. LIST THE EMPLOYEES WHOSE DAILY SALARY IS GREATER THAN 1500 AND WHO ARE JOINED BEFORE 1982 ONLY.
100. DISPLAY NUMBER OF EMPLOYEES WHOSE COMMISSION IS MORE THAN SALARY.
101. LIST THE EMPLOYEES WHOSE DAILY SALARY IS GREATER THAN 1500 AND WHO ARE JOINED BEFORE 1982 ONLY.
102. DISPLAY ALL THE EMPLOYEES WHOSE JOB SAME AS 'SMITH' AND DEPARTMENT SAME AS 'JONES' AND SALARY MORE THAN 'TURNER'
103. DISPLAY ALL THE EMPLOYEES WHOSE NAME START WITH 'S' AND HAVING SALARY MORE THAN 'ALLEN' AND LESS THAN FORD
104. DISPLAY ALL THE CLERKS AND ANALYST WHO ARE NOT WORKING FOR 'DALLAS'
105. DISPLAY DEPARTMENT NAME WHICH IS HAVING AT LEAST ONE 'MANAGER'
106. DISPLAY MAXIMUM SALARY OF 'SALES' DEPARTMENT
107. DISPLAY THE 2ND MAXIMUM SALARY
108. DISPLAY THE DEPT NAME OF THE EMP WHO GETS 3RD MAXIMUM SALARY
109. DISPLAY ALL THE EMPLOYEES WHO ARE EARNING MORE THAN ALL THE MANAGERS(JOB).
110. DISPLAY ALL THE EMPLOYEES WHO ARE EARNING MORE THAN ANY OF THE MANAGER(JOB)
111. SELECT EMPNO, JOB AND SALARY OF ALL THE ANALYST WHO ARE EARNING MORE THAN ANY OF THE MANAGER(JOB)
112. SELECT THE DEPARTMENT NAME AND LOCATION OF ALL THE EMPLOYEES WORKING FOR CLARK.
113. SELECT ALL THE EMPLOYEES WORKING FOR DALLAS
114. DISPLAY ALL THE EMPLOYEES WHOSE SALARY IS GREATER THAN AVG SAL OF DEPARTMENT 20
115. DISPLAY ALL THE EMPLOYEES WHO GETS MAXIMUM SALARY.
116. DISPLAY FIRST EMPLOYEE RECORD BASED ON HIREDATE

SQL Single Row Function Interview Question

1. List employees whose name having 4 characters

2. List employees whose job is having 7 characters

3. Find out how many times letter 'S' occurs in 'solutions'

4. List the employees whose job is having last 3 characters as 'man'

5. List employees whose job is having first 3 characters as 'man'.

6. Display all the names whose name is having exactly 1 'L'

7. Display dept names which are having letter 'O'

8. Display the output as shown below, Scott working as a clerk earns 3000 in dept 20

9. Calculate number of L in string 'HELLLLL'

10. Display all the employees whose job has a string 'MAN'

11. Display all the employees whose job starts with string 'MAN'

12. Display all the employees whose job ends with string 'MAN'

13. Display first 3 characters of ename in lower case and rest everything in upper case. If ename is 'CODINGMOMENTS' then display this as ‘codINGMOMENTS’

14. Display the result from emp table as below. SMITH is a CLERK and gets salary 2000 Here SMITH is ename column, CLERK is JOB and 2000 is SAL column and rest everything is literal strings.

15.list the employees hired on a Wednesday

16.list the employees hired on a leap year

17.list the employees hired on a Sunday in the month of may

18. WAQTD HIREDATES IN US FORMAT

19. DISPLAY FIRST HALF OF NAME IN LOWER CASE AND REST IN REVERSE.

20. WAQTD LAST 3 CHAR OF EMP WHO ARE WORKING AS MANAGER

21. WAQTD NAME OF THE EMP WHOSE NAME ENDS WITH ‘S’

22. WAQTD NAME OF THE EMP WHOSE NAME ENDS WITH S OR N

23. WAQTD NAME OF EMP IF HIS NAME START WITH VOWEL

24. WAQTD 1ST HALF OF EMP NAME

25. WAQTD 2ND HALF OF EMP NAME

26. WAQTD NAME OF EMP WITHOUT 1ST AND LAST CHAR

27. WAQTD NAME OF THE EMP WITH CHAR ‘A’ IN NAME

28. WAQTD NAME OF THE EMP AND HIS DNAME IF CHAR ‘O’ IS PRESENT IN EMP’S NAME AS WELL AS IN DNAME

29.WAQT D EMPS NAME WHO ARE GETTING EVEN SAL

SQL Interview Question On GROUP BY AND HAVING CLAUSE

SQL QUESTIONS ON GROUP BY AND HAVING CLAUSE 

1. WRITE A QUERY TO DISPLAY TOTAL SALARY NEEDED TO PAY EACH JOB IN EMPLOYEE TABLE.

2. WRITE A QUERY TO DISPLAY THE HIRE DATE ON WHICH AT LEAST 3 EMPLOYEES WHERE HIRED.

3. WRITE A QUERY TO DISPLAY THE DEPARTMENT NUMBER WHICH HAS MORE THAN 2 EMPLOYEES AND THE TOTAL AMOUNT REQUIRED TO PAY THE MONTHLY SALARIES OF ALL THE EMPLOYEES IN THAT DEPARTMENT SHOULD BE MORE THAN 9000.

4. WRITE A QUERY TO DISPLAY NUMBER OF EMPLOYEES WORKING IN EACH DEPARTMENT AND ITS’ AVERAGE SALARY BY EXCLUDING ALL THE EMPLOYEES WHOSE SALARY IS LESS THAN THEIR COMMISSION.

5. WRITE A QUERY TO DISPLAY THE SALARIES WHICH HAS REPETITIONS IN THE SAL COLUMN OF EMPLOYEE TABLE.

6. WRITE A QUERY TO DISPLAY THE EMPLOYEE NAME ONLY IF MORE THAN ONE PERSON IN THE EMPLOYEES OF THE COMPANY HAS SAME NAME.

7. WRITE A QUERY TO DISPLAY THE DEPARTMENT NUMBER WHOSE AVERAGE SALARY IS BETWEEN 2500 AND 3000.

8. WRITE A QUERY TO DISPLAY THE NUMBER OF EMPLOYEES ONLY IF THEY ARE WORKING AS MANAGER OR ANALYST AND THEIR ANNUAL SAL SHOULD END WITH A ZERO, IN EACH DEPARTMENT.

9. WRITE A QUERY TO DISPLAY NO OF CLERKS WORKING IN EACH DEPARTMENT.

10. WRITE A QUERY TO DISPLAY HIGHEST SALARY GIVEN TO A MANAGER IN EACH DEPARTMENT.

11. WRITE A QUERY TO DISPLAY NO OF TIMES THE SALARIES HAVE REPEATED IN THE EMP TABLE.

12.WRITE A QUERY TO DISPLAY DEPTNO AND MUNBER OF EMPLOYEES  WHORKING IN EACH DEPARTMENT EXCEPT FOR THOSE WORKING IN DEPT  10

13.WAQTD NUMBER OF EMPLOYEES GETTING COMISSION IN EACH  DEPARTMENT

14.WAQTD NUMBER OF EMPLOYEES GETTING SALARY MORE THAN 1600  EXCLUDING ALL THE MANAGERS IN EACH DEPARTEMNT

15.WAQTD AVERAGE SALARY NEEDED TO PAY ALL THE EMPLOYEES WHO ARE HAVING A REPORTING MANAGER IN EACH JOB .

16.WAQTD NUMBER OF EMPLOYEES HIRED INTO THE SAME DEPARTMENT ON THE SAME DAY

17.WAQTD NUMBER OF EMPLOYEES GETTING THE SAME SALARY ,  WORKING IN THE SAME DEPARTMENT

18.WAQTD MAXIMUM SALARY GIVEN IN EACH DESIGNATION EXCLUDING  THOSE WHOS NAME STARTS WITH ‘K’

19.WAQTD NUMBER OF EMPLOYEES REPORTING TO 7839 IN EACH DEPT

20.WAQTD NUMBER OF EMPLOYEE NAMES STARTING WITH AN VOWEL IN  EACH DEPARTMENT