Query to select employee details from employee table if data exists in incentive table.
select * from EMPLOYEE where exists (select * from INCENTIVES)
Query to fetch data that are common in two query results.
select * from EMPLOYEE where EMPLOYEE_ID INTERSECT select * from EMPLOYEE where EMPLOYEE_ID < 4
Query to get employee ID’s of those employees who didn’t receive incentives without using sub query.
select EMPLOYEE_ID from EMPLOYEE MINUS select EMPLOYEE_REF_ID from INCENTIVES
Query to select 20% of salary from John, 10% of salary for Roy and for other 15% of salary from employee table.
SELECT FIRST_NAME, CASE FIRST_NAME WHEN 'John' THEN SALARY*.2 WHEN 'Roy' THEN SALARY*.1 ELSE SALARY*.15 END "Deducted_Amount" from EMPLOYEE
Query to select Banking as ‘Bank Dept’, Insurance as ‘Insurance Dept’ and Services as ‘Services Dept’ from employee table.
select case DEPARTMENT when 'Banking' then 'Bank Dept' when 'Insurance' then 'Insurance Dept' when 'Services' then 'Services Dept' end from employee
Query to delete employee data from employee table who got incentives in incentive table.
delete from EMPLOYEE where EMPLOYEE_ID in (select EMPLOYEE_REF_ID from INCENTIVES)
Insert into employee table Last Name with ” ‘ ” ( Single Quote – Special Character)
Insert into employee (LAST_NAME) values ("Test'")
Query to select last name from employee table which contain only numbers.
select * from employee where lower(LAST_NAME)=upper(LAST_NAME)
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
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')