SQL Important Queries

Create table Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department

&

Incentives

Employee_ref_id	Incentive_date	Incentive_amount

============================

Write Sql Queries for following Question

1. Get all employee details from the employee table

2.Get First_Name,Last_Name from employee table

3. Get First_Name from employee table using alias name “Employee Name”

4. Get First_Name from employee table in upper case

5. Get First_Name from employee table in lower case

6. Get unique DEPARTMENT from employee table

7.Select first 3 characters of FIRST_NAME from EMPLOYEE

select substring(FIRST_NAME,1,3) from employee

8. Get position of 'o' in name 'John'
from employee table

Select LOCATE('o',FIRST_NAME) from employee
where first_name='John'

9. Get FIRST_NAME from employee table
after removing white spaces from right side

select RTRIM(FIRST_NAME) from employee

10. Get length of
FIRST_NAME from employee table

select len(FIRST_NAME) from employee

11.Get First_Name from
employee table after replacing 'o' with '$'

select REPLACE(FIRST_NAME,'o','$') from employee

12. Get First_Name and
Last_Name as single column from employee table separated by a '_'

Select concat(FIRST_NAME,'_',LAST_NAME) from
EMPLOYEE

13. Get FIRST_NAME
,Joining year,Joining Month and Joining Date from employee table

select year(joining_date),month(joining_date),
DAY(joining_date) from EMPLOYEE

14. Get all employee details from the
employee table order by First_Name Ascending

Select * from employee order by FIRST_NAME asc

15. Get all employee details from the
employee table order by First_Name descending

Select * from employee order by FIRST_NAME desc

16. 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

17. Get employee
details from employee table whose employee name is “John”

Select * from EMPLOYEE where FIRST_NAME='John'

18.Get employee details
from employee table whose employee name are “John” and “Roy”

Select * from EMPLOYEE where FIRST_NAME in
('John','Roy')

19.Get employee details
from employee table whose employee name are not “John” and “Roy”

Select * from EMPLOYEE where FIRST_NAME not in
('John','Roy')

20.Get employee details
from employee table whose first name starts with 'J'

Select * from EMPLOYEE where FIRST_NAME like
'J%'

21. Get employee
details from employee table whose first name contains 'o'

Select * from
EMPLOYEE where FIRST_NAME like '%o%'

22. Get employee details from employee
table whose first name ends with 'n'

Select * from
EMPLOYEE where FIRST_NAME like '%n'

23. Get employee details from employee
table whose first name ends with 'n' and name contains 4 letters

Select * from
EMPLOYEE where FIRST_NAME like '___n' (Underscores)

24. Get employee details from employee
table whose first name starts with 'J' and name contains 4 letters

Select * from
EMPLOYEE where FIRST_NAME like 'J___' (Underscores)

25. Get employee details from employee
table whose Salary between 500000 and 800000

Select * from
EMPLOYEE where Salary between 500000 and 800000

26. Get employee details from employee
table whose joining year is “2013”

Select * from EMPLOYEE where
year(joining_date)='2013'

27. Get employee
details from employee table whose joining month is “January”

 Select * from EMPLOYEE where
month(joining_date)='01'

28.Get employee details
from employee table who joined before January 1st 2013

 Select * from EMPLOYEE where joining_date
<'2013-01-01'

29. Get Joining Date
and Time from employee table

Select
CONVERT(DATE_FORMAT(joining_date,'%Y-%m-%d-%H:%i:00'),DATETIME) from
EMPLOYEE

30. Get Joining
Date,Time including milliseconds from employee table

Select MICROSECOND(joining_date) from EMPLOYEE

40.Get database date 
:- select now()

41. Get names of
employees from employee table who has '%' in Last_Name.

Select FIRST_NAME from employee where Last_Name
like '%\%%'

42. Get Last Name from
employee table after replacing special character with white space

Select REPLACE(LAST_NAME,'%',' ') from employee

43. Get
department,total salary with respect to a department from employee
table.

Select
DEPARTMENT,sum(SALARY) Total_Salary from employee group by department

44. Get department,no
of employees in a department,total salary with respect to a
department from employee table order by total salary descending

Select DEPARTMENT,count(FIRST_NAME),sum(SALARY)
Total_Salary from employee group by DEPARTMENT order by Total_Salary
descending

45. Get department wise
average salary from employee table order by salary ascending

select DEPARTMENT,avg(SALARY) AvgSalary from
employee group by DEPARTMENT order by AvgSalary asc

46. Select employee
details from employee table if data exists in incentive table ?

select * from
EMPLOYEE where exists (select * from INCENTIVES)

Explanation : Here "exists" statement helps us to do the job of If statement. Main query will get executed if the sub query returns at least one row. So we can consider the sub query as "If condition" and the main query as "code block" inside the If condition. We can use any SQL commands (Joins, Group By , having etc) in sub query. This command will be useful in queries which need to detect an event and do some activity.
47. How to fetch data that are common in two query results ?

select * from
EMPLOYEE where EMPLOYEE_ID INTERSECT select * from EMPLOYEE where
EMPLOYEE_ID < 4

48. 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

49. 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

Explanation : Here, we are using "SQL CASE" statement to achieve the desired results. After case statement, we had to specify the column on which filtering is applied. In our case it is "FIRST_NAME". And in then condition, specify the name of filter like John, Roy etc
50. 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

Explaination : Here "DECODE" keyword is used to specify the alias name. In oracle we had specify, Column Name followed by Actual Name and Alias Name as arguments. In SQL Server and MySQL, we can use the earlier switch case statements for alias names.

51. 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)

52. 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)

53. Select first_name,
incentive amount from employee and incentives table for those
employees who have incentives

Select
FIRST_NAME,INCENTIVE_AMOUNT from employee a inner join incentives B
on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

54. 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

55.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

56. Select first_name, incentive amount
from employee and incentives table for all employees who got
incentives using left join

Select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0)
from employee a right join incentives B on
A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

57.Select max incentive
with respect to employee from employee and incentives table using sub
query

select DEPARTMENT,(select IFNULL
(max(INCENTIVE_AMOUNT),0) from INCENTIVES where
EMPLOYEE_REF_ID=EMPLOYEE_ID) Max_incentive from EMPLOYEE

58. Top N Salary"
SQL Questions 

Select TOP 2 salary from employee
table

select * from employee order by salary desc
limit 2

59.Select TOP
N salary from employee table

select * from employee order by salary desc
limit N

60.Select 2nd
Highest salary from employee table

select min(SALARY) from (select * from employee
order by salary desc limit 2) 

61. Select Nth Highest salary from employee table

select min(SALARY) from (select * from employee
order by salary desc limit N) a

62. Select First_Name,LAST_NAME from employee table as separate rows

select FIRST_NAME
from EMPLOYEE union select LAST_NAME from EMPLOYEE

63.What is
the difference between UNION and UNION ALL ?

Both
UNION and UNION ALL is used to select information from structurally
similar tables. That means corresponding columns specified in the
union should have same data type. For example, in the above query, if
FIRST_NAME is DOUBLE and LAST_NAME is STRING above query wont work.
Since the data type of both the columns are VARCHAR, union is made
possible. Difference between UNION and UNION ALL is that , UNION
query return only distinct values.

64. Write Sql Syntax to create EMPLOYEE_REF_ID in INCENTIVES table as foreign key with respect to EMPLOYEE_ID in employee table

ALTER TABLE
INCENTIVES ADD CONSTRAINT INCENTIVES_FK FOREIGN KEY (EMPLOYEE_REF_ID)
REFERENCES EMPLOYEE(EMPLOYEE_ID)

65.Write SQL to drop foreign key on employee table

ALTER TABLE
INCENTIVES drop CONSTRAINT INCENTIVES_FK;

66. What is SQL Injection ?

SQL Injection is
one of the the techniques uses by hackers to hack a website by
injecting SQL commands in data fields.