SQL Queries for Practice with Answer

Written By:- Isha Malhotra

SQL Queries with Answer

In this tutorial I am representing some sql queries with answer. It will help all to improve your sql skills. These SQL Queries categorized into two part. In first part i have discussed basic sql queries with answers. in Second part i have discussed nested queries.

For this tutorial i am using following tables:-

Table Name:- Employee

		


        

        EmpidEmpNameDepartmentContactNoEmailIdEmpHeadId
        

        
        

        101IshaE-1011234567890isha@gmail.com105
        

        
    

        102PriyaE-1041234567890priya@yahoo.com103
        

        
        

        103NehaE-1011234567890neha@gmail.com101
        

        
        

        104RahulE-1021234567890rahul@yahoo.com105
        

        
        

        105AbhishekE-1011234567890abhishek@gmail.com102
        

                        
        

Schema:-
create table employee(empid int primary key,empname varchar(100), department varchar(50),contactno bigint, emaildid varchar(100), empheadid int)

Table :- EmpDept

		


        

        DeptIdDeptNameDept_offDeptHead
        

        
        

        E-101HRMonday105
        

        
        

        E-102DevelopmentTuesday101
        

        
        

        E-103Hous KeepingSaturday103
        

        
        

        E-104SalesSunday104
        

        
        

        E-105PurchageTuesday104
        

        

Schema:-
create table empdept(deptid varchar(50) primary key,deptname varchar(100), dept_off varchar(100), depthead int foreign key references employee(empid))

Table :- EmpSalary

		


        
 EmpIdSalaryIsPermanent
        

        
        
     1012000Yes
        

        
         
        10210000Yes
        

        
         
        1035000No
        

        
         
        1041900Yes
        

        
         
        1052300Yes
        

        

Schema:-
create table empsalary(empid int foreign key references employee(empid), salary bigint, ispermanent varchar(3))

Table :- Project

		


        

        ProjectIdDuration
        

        
        
        p-123
        

        
        
        p-215
        
 
        
        
        p-345
        
 
        
        
        p-42
        
 
        
        
        p-530
        

        
        

Schema:-
create table project(projectid varchar(50) primary key, duration int)

Table :- Country

		


        

        cidcname
        

        
        
        c-1India
        

        
        
        c-2USA
        
 
        
        
        c-3China
        
 
        
        
        c-4Pakistan
        
 
        
        
        c-5Russia
        

        
        

Schema:-
create table country(cid varchar(50) primary key, cname varchar(100))

Table :- ClientTable

		


        
        ClientIdClientNamecid
        

        
        
        cl-1ABC Groupc-1
        

        
        
        cl-2PQRc-1
        

        
        
        
        cl-3XYZc-2
        

        
          
        cl-4tech altumc-3
        

        
         
        cl-5mnpc-5
        

        
        

Schema:-
create table clienttable(clientid varchar(50) primary key, clientname varchar(100), cid varchar(50) references country(cid))

Table :- EmpProject




        

        EmpIdProjectIdClientIDStartYearEndYear
        

        
        
        101p-1Cl-120102010
        

        
        
        102p-2Cl-220102012
        

        
        
        103p-1Cl-32013
        

        
        
        104p-4Cl-120142015
        

        
        
        105p-4Cl-52015
        

        
        

Schema:-
create table empproject(empid int foreign key references employee(empid), projectid varchar(50) foreign key references project(projectid), clientid varchar(50) foreign key references clienttable(clientid),startyear int, endyear int)

Queries:-

Simple Queries

1.   Select the detail of the employee whose name start with P.


select * from employee where empname like 'p%'
output:-
query 1 output

2.   How many permanent candidate take salary more than 5000.


select count(salary) as count from empsalary where ispermanent='yes' and salary>5000
output:-
query 2 output

3.   Select the detail of employee whose emailId is in gmail.


select * from employee where emaildid like '%@gmail.com'
output:-
query 3 output

4.   Select the details of the employee who work either for department E-104 or E-102.


select * from employee where department='E-102' or department='E-104'

or

select * from employee where department in ('E-102','E-104')
output:-
query 4 output

5.   What is the department name for DeptID E-102?


select deptname from empdept where deptid ='E-102'
output:-
query 5 output

6.  What is total salarythat is paid to permanent employees?


select sum(salary) as salary from empsalary where ispermanent='yes'
output:-
query 6 output

7.  List name of all employees whose name ends with a.


select * from employee where empname like '%a'
output:-
query 7 output

8.  List the number of department of employees in each project.


select count(empid) as employee, projectid from empproject group by projectid
output:-
query 8 output

9.  How many project started in year 2010.


select count(projectid) as project from empproject where startyear=2010
output:-
query 9 output

10.  How many project started and finished in the same year.


select count(projectid) as project from empproject where startyear=endyear
output:-
query 10 output

11.  select the name of the employee whose name's 3rd charactor is 'h'.


select * from employee where empname like '__h%'
output:-
query 11 output

Nested Queries

1.   Select the department name of the company which is assigned to the employee whose employee id is grater 103.


select deptname from empdept where deptid in (select department from employee where empid>103)
output:-
nested query 1 output

2.   Select the name of the employee who is working under Abhishek.


select empname from employee where empheadid =(select empid from employee where empname='abhishek') 
output:-
nested query 2 output

3.   Select the name of the employee who is department head of HR.


select empname from employee where empid =(select depthead from empdept where deptname='hr')
output:-
nested query 2 output

4.   Select the name of the employee head who is permanent.

		
select empname from employee where empid in(select empheadid from employee) and empid in(select empid from empsalary where ispermanent='yes')
output:-
nested query 4 output

5.   Select the name and email of the Dept Head who is not Permanent.

		
select empname, emaildid from employee where empid in(select depthead from empdept ) and empid in(select empid from empsalary where ispermanent='no')
output:-
nested query 5 output

6.   Select the employee whose department off is monday

		
select * from employee where department in(select deptid from empdept where dept_off='monday')
output:-
nested query 6 output

7.   select the indian clinets details.

		
select * from clienttable where cid in(select cid from country where cname='india')
output:-
nested query 7 output

8.   select the details of all employee working in development department.

		
select * from employee where department in(select deptid from empdept where deptname='development')
output:-
nested query 8 output

Video Tutorial for Basic SQL Queries

SQL Queries based on Aggregate Function