Assume a schema of Emp ( Id, Name, DeptId ) , Dept ( Id, Name).If there are 10 records in the Emp table and 5 records in the Dept table, how many rows will be displayed in the result of the following SQL query: Select * From Emp, Dept
The query will result in 50 rows as a “cartesian product” or “cross join”, which is the default whenever the ‘where’ clause is omitted.
Given a table SALARIES, such as the one below, that has m = male and f = female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table.
columns: Id, Name, Sex, Salary
UPDATE SALARIES SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END
Given a table TBL with a field Nmbr that has rows with the following values:
1,0,0,1,1,1,1,0,0,1,0,1,0,1,0,1
Write a query to add 2 where Nmbr is 0 and add 3 where Nmbr is 1.
update TBL set Nmbr = case when Nmbr > 0 then Nmbr+3 else Nmbr+2 end;
Write a SQL query to find the 10th highest employee salary from an Employee table. Explain your answer.
SELECT TOP (1) Salary FROM
(
SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC
) AS Emp ORDER BY Salary
WHEN TOP IS NOT SUPPORTED
SELECT Salary FROM
(
SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 10
) AS Emp ORDER BY Salary DESC LIMIT 1;
Write a SQL query using UNION ALL (not UNION) that uses the WHERE clause to eliminate duplicates. Why might you want to do this?
SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X
The key is the AND a!=X part. This gives you the benefits of the UNION (a.k.a., UNION DISTINCT) command,
Table Name : Employee
Employee_id First_name Last_name Salary Joining_date Department
Table Name : Incentives
Employee_ref_id Incentive_date Incentive_amount
Get First_Name from employee table in upper case
Select upper(FIRST_NAME) from EMPLOYEE
Select first 3 characters of FIRST_NAME from EMPLOYEE
select substr(FIRST_NAME,0,3) from employee //oracle
select substring(FIRST_NAME,1,3) from employee //mysql
Get position of 'o' in name 'John' from employee table
Select instr(FIRST_NAME,'o') from employee where first_name='John' //oracle
Select LOCATE('o',FIRST_NAME) from employee where first_name='John' //mysql
Get FIRST_NAME from employee table after removing white spaces from right side
select RTRIM(FIRST_NAME) from employee
Get length of FIRST_NAME from employee table
select length(FIRST_NAME) from employee
Get First_Name from employee table after replacing 'o' with '$'
select REPLACE(FIRST_NAME,'o','$') from employee
Get First_Name and Last_Name as single column from employee table separated by a '_'
Select FIRST_NAME|| '_' ||LAST_NAME from EMPLOYEE //oracle
Select concat(FIRST_NAME,'_',LAST_NAME) from EMPLOYEE //mysql
Get FIRST_NAME ,Joining year,Joining Month and Joining Date from employee table
Select FIRST_NAME, to_char(joining_date,'YYYY') JoinYear , to_char(joining_date,'Mon'), to_char(joining_date,'dd') from EMPLOYEE //oracle
select year(joining_date),month(joining_date), DAY(joining_date) from EMPLOYEE //mysql
Get all employee details from the employee table order by First_Name Ascending and Salary descending
Select * from employee order by FIRST_NAME asc,SALARY desc
Get employee details from employee table whose employee name are “John” and “Roy”
Select * from EMPLOYEE where FIRST_NAME in ('John','Roy')
Get employee details from employee table whose joining year is “2013”
Select * from EMPLOYEE where to_char(joining_date,'YYYY')='2013' //oracle
Select * from EMPLOYEE where year(joining_date)='2013' //mysql
Get employee details from employee table who joined before January 1st 2013
Select * from EMPLOYEE where JOINING_DATE <to_date('01/01/2013','dd/mm/yyyy') //oracle
Select * from EMPLOYEE where joining_date <'2013-01-01' //mysql
Get Joining Date,Time including milliseconds from employee table
select to_char(JOINING_DATE,'dd/mm/yyyy HH:mi:ss.ff') from EMPLOYEE . Column Data Type should be “TimeStamp” //oracle
Select MICROSECOND(joining_date) from EMPLOYEE //mysql
Get names of employees from employee table who has '%' in Last_Name. Tip : Escape character for special characters in a query.
Select FIRST_NAME from employee where Last_Name like '%?%%' //oracle
Select FIRST_NAME from employee where Last_Name like '%\%%' //mysql
Get department wise average salary from employee table order by salaryascending
select DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalary asc
Get department wise maximum salary from employee table order by salaryascending
select DEPARTMENT,max(SALARY) MaxSalary from employee group by DEPARTMENT order by MaxSalary asc
Select department,total salary with respect to a department from employee table where total salary greater than 800000 order by Total_Salary descending
Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT having sum(SALARY) >800000 order by Total_Salary desc
Select employee details from employee table if data exists in incentive table ?
select * from EMPLOYEE where exists (select * from INCENTIVES)
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 * .10 ELSE SALARY * .15 END "Deduced_Amount" FROM EMPLOYEE
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)
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' )
Select first_name, incentive amount from employee and incentives table for those employees who have incentives and incentive amount greater than 3000
Select FIRST_NAME,INCENTIVE_AMOUNT from employee a inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID and INCENTIVE_AMOUNT >3000
Select first_name, incentive amount from employee and incentives table for all employes even if they didn't get incentives
Select FIRST_NAME,INCENTIVE_AMOUNT from employee a left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID
Select TOP N salary from employee table
select * from (select * from employee order by SALARY desc) where rownum <N + 1 //oracle
select * from employee order by salary desc limit N //mysql
Select Nth Highest salary from employee table
select min(salary) from (select * from (select * from employee order by SALARY desc) where rownum <N + 1)
select min(SALARY) from (select * from employee order by salary desc limit N) a
The query will result in 50 rows as a “cartesian product” or “cross join”, which is the default whenever the ‘where’ clause is omitted.
Given a table SALARIES, such as the one below, that has m = male and f = female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table.
columns: Id, Name, Sex, Salary
UPDATE SALARIES SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END
Given a table TBL with a field Nmbr that has rows with the following values:
1,0,0,1,1,1,1,0,0,1,0,1,0,1,0,1
Write a query to add 2 where Nmbr is 0 and add 3 where Nmbr is 1.
update TBL set Nmbr = case when Nmbr > 0 then Nmbr+3 else Nmbr+2 end;
Write a SQL query to find the 10th highest employee salary from an Employee table. Explain your answer.
SELECT TOP (1) Salary FROM
(
SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC
) AS Emp ORDER BY Salary
WHEN TOP IS NOT SUPPORTED
SELECT Salary FROM
(
SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 10
) AS Emp ORDER BY Salary DESC LIMIT 1;
Write a SQL query using UNION ALL (not UNION) that uses the WHERE clause to eliminate duplicates. Why might you want to do this?
SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X
The key is the AND a!=X part. This gives you the benefits of the UNION (a.k.a., UNION DISTINCT) command,
Table Name : Employee
Employee_id First_name Last_name Salary Joining_date Department
Table Name : Incentives
Employee_ref_id Incentive_date Incentive_amount
Get First_Name from employee table in upper case
Select upper(FIRST_NAME) from EMPLOYEE
Select first 3 characters of FIRST_NAME from EMPLOYEE
select substr(FIRST_NAME,0,3) from employee //oracle
select substring(FIRST_NAME,1,3) from employee //mysql
Get position of 'o' in name 'John' from employee table
Select instr(FIRST_NAME,'o') from employee where first_name='John' //oracle
Select LOCATE('o',FIRST_NAME) from employee where first_name='John' //mysql
Get FIRST_NAME from employee table after removing white spaces from right side
select RTRIM(FIRST_NAME) from employee
Get length of FIRST_NAME from employee table
select length(FIRST_NAME) from employee
Get First_Name from employee table after replacing 'o' with '$'
select REPLACE(FIRST_NAME,'o','$') from employee
Get First_Name and Last_Name as single column from employee table separated by a '_'
Select FIRST_NAME|| '_' ||LAST_NAME from EMPLOYEE //oracle
Select concat(FIRST_NAME,'_',LAST_NAME) from EMPLOYEE //mysql
Get FIRST_NAME ,Joining year,Joining Month and Joining Date from employee table
Select FIRST_NAME, to_char(joining_date,'YYYY') JoinYear , to_char(joining_date,'Mon'), to_char(joining_date,'dd') from EMPLOYEE //oracle
select year(joining_date),month(joining_date), DAY(joining_date) from EMPLOYEE //mysql
Get all employee details from the employee table order by First_Name Ascending and Salary descending
Select * from employee order by FIRST_NAME asc,SALARY desc
Get employee details from employee table whose employee name are “John” and “Roy”
Select * from EMPLOYEE where FIRST_NAME in ('John','Roy')
Get employee details from employee table whose joining year is “2013”
Select * from EMPLOYEE where to_char(joining_date,'YYYY')='2013' //oracle
Select * from EMPLOYEE where year(joining_date)='2013' //mysql
Get employee details from employee table who joined before January 1st 2013
Select * from EMPLOYEE where JOINING_DATE <to_date('01/01/2013','dd/mm/yyyy') //oracle
Select * from EMPLOYEE where joining_date <'2013-01-01' //mysql
Get Joining Date,Time including milliseconds from employee table
select to_char(JOINING_DATE,'dd/mm/yyyy HH:mi:ss.ff') from EMPLOYEE . Column Data Type should be “TimeStamp” //oracle
Select MICROSECOND(joining_date) from EMPLOYEE //mysql
Get names of employees from employee table who has '%' in Last_Name. Tip : Escape character for special characters in a query.
Select FIRST_NAME from employee where Last_Name like '%?%%' //oracle
Select FIRST_NAME from employee where Last_Name like '%\%%' //mysql
Get department wise average salary from employee table order by salaryascending
select DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalary asc
Get department wise maximum salary from employee table order by salaryascending
select DEPARTMENT,max(SALARY) MaxSalary from employee group by DEPARTMENT order by MaxSalary asc
Select department,total salary with respect to a department from employee table where total salary greater than 800000 order by Total_Salary descending
Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT having sum(SALARY) >800000 order by Total_Salary desc
Select employee details from employee table if data exists in incentive table ?
select * from EMPLOYEE where exists (select * from INCENTIVES)
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 * .10 ELSE SALARY * .15 END "Deduced_Amount" FROM EMPLOYEE
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)
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' )
Select first_name, incentive amount from employee and incentives table for those employees who have incentives and incentive amount greater than 3000
Select FIRST_NAME,INCENTIVE_AMOUNT from employee a inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID and INCENTIVE_AMOUNT >3000
Select first_name, incentive amount from employee and incentives table for all employes even if they didn't get incentives
Select FIRST_NAME,INCENTIVE_AMOUNT from employee a left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID
Select TOP N salary from employee table
select * from (select * from employee order by SALARY desc) where rownum <N + 1 //oracle
select * from employee order by salary desc limit N //mysql
Select Nth Highest salary from employee table
select min(salary) from (select * from (select * from employee order by SALARY desc) where rownum <N + 1)
select min(SALARY) from (select * from employee order by salary desc limit N) a
No comments:
Post a Comment