Advanced SQL Queries
61. Select employee details from employee table if data exists in incentive table?
SELECT * FROM EMPLOYEE WHERE EXISTS (SELECT * FROM INCENTIVES);
Here, we use ‘EXISTS’ clause to get the result. The query inside the braces will be executed first to see if there is any data. If it returns at least one record, then it will execute the outer query. Inner query will not be executed to get whole data but the outer query will be executed to get whole matching records. Here inner query acts as a IF clause, i.e.; it will check if there is any records in INCENTIVES table, and if yes, then executes the outer query.
62. How to fetch data that are common in two query results?
Records that are common in both the queries are fetched using INTERSECT clause. It will check if each record in the first query matches with the each record in the second query. If it finds any matching records, then it will display it in the result set.
SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID >2 INTERSECT SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID < 4;
63. Get Employee ID’s of those employees who didn’t receive incentives without using sub query?
Here, we have to use MINUS clause to find the records that are in EMPLOYEE but not in INCENTIVES. This clause will compare and extract the records that are in first query but not in second query.
SELECT EMPLOYEE_ID FROM EMPLOYEE MINUS SELECT EMPLOYEE_REF_ID FROM INCENTIVES;
64. Select 20 % of salary from John , 10% of Salary for Roy and for other 15 % of salary from employee table?
Since here the salary needs to be deducted differently based on the employees, we have to use CASE statement to check the employee name and then apply the percentage of deduction like below.
SELECT FIRST_NAME, CASE FIRST_NAME WHEN 'John' THEN SALARY * .2 WHEN 'Roy' THEN SALARY * .10 ELSE SALARY * .15 END "Deduced_Amount" FROM EMPLOYEE;
65. Select Banking as ‘Bank Dept’, Insurance as ‘Insurance Dept’ and Services as ‘Services Dept’ from employee table?
DECODE statement is used here to rename the department names in the result. Decode statement will check for the department name, and then rename the names accordingly.
SELECT DISTINCT DECODE (DEPARTMENT, 'Banking', 'Bank Dept', 'Insurance', 'Insurance Dept', 'Services', 'Services Dept') FROM EMPLOYEE;
66. Delete employee data from employee table who got incentives in incentive table?
In order to delete the data from EMPLOYEE table who has incentives, we have to first get the employee ids who has incentives in incentive department. For those ids, we have to apply delete. Therefore we can first query INCENTIVES table to get employee ids and then use those ids in IN clause like below to delete them.
DELETE FROM EMPLOYEE WHERE EMPLOYEE_ID IN ( SELECT EMPLOYEE_REF_ID FROM INCENTIVES);
67. Insert into employee table Last Name with ” ‘ ” (Single Quote – Special Character)?
We need to use another single quote to insert the names with single quote.
INSERT INTO EMPLOYEE (LAST_NAME) VALUES ('TEST''');
68. Select Last Name from employee table which contain only numbers?
This is little tricky question. When we use UPPER and LOWER case, it will give ASCII values. Here ASCII value for upper and lower case will be different. But for number, upper and lower case ASCII will be same. Hence it will give results with numbers.
SELECT * FROM EMPLOYEE WHERE LOWER(LAST_NAME)=UPPER(LAST_NAME);
69. Write a query to rank employees based on their incentives for a month?
SELECT FIRST_NAME, INCENTIVE_AMOUNT, DENSE_RANK() OVER (PARTITION BY INCENTIVE_DATE ORDER BY INCENTIVE_AMOUNT DESC ) AS Rank FROM EMPLOYEE a, INCENTIVES b WHERE a.EMPLOYEE_ID=b.EMPLOYEE_REF_ID;
70. Update incentive table where employee name is ‘John’?
UPDATE INCENTIVES SET INCENTIVE_AMOUNT='9000' WHERE EMPLOYEE_REF_ID= ( SELECT EMPLOYEE_ID FROM EMPLOYEE WHERE FIRST_NAME='John' );