SQL Queries For Practice - Advanced SQL Queries PDF

10/20/2020 sql queries for practice | advanced sql queries
 Tech Altum Tutorial
chaltum.com (Avinash Malhotra, IIT Alumni, 8+ Exp). Both Online and Classroom Tr
Free SQL Course
Learn SQL for Data Science in

4 hours by completing 41
interactive exercises.
DataCamp
SQL Queries for Practice with Answer

Written By:- Isha Malhotra
SQL Interview Question Tech Altum
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 Q
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
Empid EmpName Department ContactNo EmailId EmpHeadId
101 Isha E-101 1234567890 isha@gmail.com 105
102 Priya E-104 1234567890 priya@yahoo.com 103
103 Neha E-101 1234567890 neha@gmail.com 101
104 Rahul E-102 1234567890 rahul@yahoo.com 105
105 Abhishek E-101 1234567890 abhishek@gmail.com 102 
https://tutorial.techaltum.com/sql-queries-for-practice.html 1/15

Schema:-
create table employee(empid int primary key,empname varchar(100), department varchar(50),contactno
varchar(100), empheadid int)
Table :- EmpDept
DeptId DeptName Dept_off DeptHead
E-101 HR Monday 105
E-102 Development Tuesday 101
E-103 Hous Keeping Saturday 103
E-104 Sales Sunday 104
E-105 Purchage Tuesday 104
Schema:-
create table empdept(deptid varchar(50) primary key,deptname varchar(100), dept_off varchar(100), d
references employee(empid))
Table :- EmpSalary
EmpId Salary IsPermanent
101 2000 Yes
102 10000 Yes
103 5000 No
104 1900 Yes
105 2300 Yes
Schema:-
create table empsalary(empid int foreign key references employee(empid), salary bigint, ispermanent
Ads by
Send feedback Why this ad?
Table :- Project
ProjectId Duration
p-1 23
p-2 15
p-3 45
p-4 2
p-5 30
Schema:-
create table project(projectid varchar(50) primary key, duration int)
Table :- Country
cid cname
c-1 India
c-2 USA
c-3 China

c-4 Pakistan
c-5 Russia
Schema:-
create table country(cid varchar(50) primary key, cname varchar(100))
Table :- ClientTable
ClientId ClientName cid
cl-1 ABC Group c-1
cl-2 PQR c-1
cl-3 XYZ c-2
cl-4 tech altum c-3
cl-5 mnp c-5
Schema:-
create table clienttable(clientid varchar(50) primary key, clientname varchar(100), cid varchar(50)
country(cid))
Table :- EmpProject
EmpId ProjectId ClientID StartYear EndYear

101 p-1 Cl-1 2010 2010
102 p-2 Cl-2 2010 2012
103 p-1 Cl-3 2013
104 p-4 Cl-1 2014 2015
105 p-4 Cl-5 2015
Schema:-
create table empproject(empid int foreign key references employee(empid), projectid varchar(50) for
project(projectid), clientid varchar(50) foreign key references clienttable(clientid),startyear int
Ads by
Perform-to-animate
2D Cartoons
Use motion capture to interact
with audience and scene
elements during live show.
Cartoon Animator
Queries:-
Simple Queries
1. Select the detail of the employee whose name start with P.
select * from employee where empname like 'p%'

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:-
3. Select the detail of employee whose emailId is in gmail. 

select * from employee where emaildid like '%@gmail.com'
output:-
Ads by
4. Select the details of the employee who work either for departm
102.
select * from employee where department='E-102' or department='E-104'
or
select * from employee where department in ('E-102','E-104')

output:-
 5. Tech
What is the department name for DeptID E-102?
Altum Tutorial
select deptname from empdept where deptid ='E-102'

output:-
6. What is total salarythat is paid to permanent employees?
select sum(salary) as salary from empsalary where ispermanent='yes'

output:-
7. List name of all employees whose name ends with a.
select * from employee where empname like '%a'

output:-
8. List the number of department of employees in each project.
select count(empid) as employee, projectid from empproject group by projectid

output:-

9. How many project started in year 2010.
select count(projectid) as project from empproject where startyear=2010

output:-
10. How many project started and finished in the same year.
select count(projectid) as project from empproject where startyear=endyear

output:-
11. select the name of the employee whose name's 3rd charactor
select * from employee where empname like '__h%'

output:-
Nested Queries
1. Select the department name of the company which is assigned
whose employee id is grater 103.
select deptname from empdept where deptid in (select department from employee where empid>103)
output:-

2. Select the name of the employee who is working under Abhish
select empname from employee where empheadid =(select empid from employee where empname='abhishek')
output:-
3. Select the name of the employee who is department head of H
select empname from employee where empid =(select depthead from empdept where deptname='hr')
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 emp
ispermanent='yes')
output:-
5. Select the name and email of the Dept Head who is not Perma
select empname, emaildid from employee where empid in(select depthead from empdept ) and empid in(s
empsalary where ispermanent='no')
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:-
7. select the indian clinets details.
select * from clienttable where cid in(select cid from country where cname='india')
output:-
8. select the details of all employee working in development depa
select * from employee where department in(select deptid from empdept where deptname='development')
output:-
Video
TechTutorial for Basic SQL Queries
Altum Tutorial
SQL Queries for Practice - Part 1 - basic sql query
SQL Queries based on Aggregate Function
SQL Queries for Practice - Part 2 - SQL Queries based on A…

A…
Frontend Tutorial Backend Tutorial
1. Web Designing Tutorial 1. Node JS
2. HTML Tutorial 2. Asp.net
3. CSS Tutorial 3. MVC
4. JavaScript Tutorial 4. Linq
5. JQuery Tutorial 5. WCF
6. Angular JS Tutorial 6. WPF
7. HTML5 Tutorial 7. Entity Framework
8. CSS3 Tutorial 8. PHP
9. SASS Tutorial 9. Java
10. PSD to HTML 10. Python
11. Bootstrap 11. Ruby
Others Tutorials Interview Questions
1. Angular 1. UI Interview Questions
2. MongoDB 2. C# Interview Questions


3. Selenium 3. SQL Interview Questions
4. Linux 4. MVC Interview Questions

5. Hadoop 5. Angular JS Interview Questions
6. Android 6. Online Quiz
7. R language
8. Video Tutorial
Tutorial.techaltum.com is run and maintained by Tech Altum, IT Training Institute in Noida (An IIT
Alumni Institute).
For Video Tutorials, Subscribe our Youtube Channel. Tech Altum Youtube Channel.

SQL Queries For Practice - Advanced SQL Queries PDF

Uploaded by

Copyright:

Available Formats

SQL Queries For Practice - Advanced SQL Queries PDF

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Queries For Practice - Advanced SQL Queries PDF

Uploaded by

Copyright:

Available Formats

10/20/2020 sql queries for practice | advanced sql queries

 Tech Altum Tutorial

Free SQL Course

Learn SQL for Data Science in

SQL Queries for Practice with Answer

SQL Interview Question Tech Altum

SQL Queries with Answer

For this tutorial i am using following tables:-

Table Name:- Employee

Empid EmpName Department ContactNo EmailId EmpHeadId

101 Isha E-101 1234567890 isha@gmail.com 105

102 Priya E-104 1234567890 priya@yahoo.com 103

103 Neha E-101 1234567890 neha@gmail.com 101

104 Rahul E-102 1234567890 rahul@yahoo.com 105

105 Abhishek E-101 1234567890 abhishek@gmail.com 102 

 Tech Altum Tutorial

DeptId DeptName Dept_off DeptHead

E-101 HR Monday 105

E-102 Development Tuesday 101

E-103 Hous Keeping Saturday 103

E-104 Sales Sunday 104

E-105 Purchage Tuesday 104

EmpId Salary IsPermanent

101 2000 Yes

102 10000 Yes

104 1900 Yes

105 2300 Yes

ClientId ClientName cid

cl-1 ABC Group c-1

cl-2 PQR c-1

cl-3 XYZ c-2

cl-4 tech altum c-3

cl-5 mnp c-5

EmpId ProjectId ClientID StartYear EndYear

select * from employee where empname like 'p%'

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

select count(salary) as count from empsalary where ispermanent='yes' and salary>5000

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

 Tech Altum Tutorial

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

select * from employee where department in ('E-102','E-104')

select deptname from empdept where deptid ='E-102'

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

select sum(salary) as salary from empsalary where ispermanent='yes'

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

select * from employee where empname like '%a'

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

select count(empid) as employee, projectid from empproject group by projectid

 Tech Altum Tutorial

9. How many project started in year 2010.

select count(projectid) as project from empproject where startyear=2010

select count(projectid) as project from empproject where startyear=endyear

select * from employee where empname like '__h%'

 Tech Altum Tutorial

 Tech Altum Tutorial

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

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

 Tech Altum Tutorial

6. Select the employee whose department off is monday