EXAMPLES ON SQL

EXAMPLE 1
Consider the given database
Employee(emp-no,skill,pay-rate)
Position(posting-no,skill)
Duty-allocation(posting-no,emp-no,day,shift)

1) Find complete details from duty-allocation.

Select *
From duty-allocation;

2) Find duty allocation details for emp-no 101 for the month of April 2003.

Select posting-no,shift,day
From duty-allocation
Where emp-no=101 and
Day  20030401 and Day  20030430;

3) Find the shift details of employee „Bhushan”

Select posting-no,shift,day
 From duty-allocation,employee
Where [Link]-no=[Link]-no
and name=‟Bhushan‟;

4) find employees whose rate of pay is more than or equal to the rate of pay
of employee „AHIRE‟.

select [Link],[Link]
from employee as S, Employee as T
where [Link] > [Link];
and [Link]=‟AHIRE‟;

5) Find all pairs of posting-no requiring the same skill.

Select [Link]-no,[Link]-no
From position as S,position as T
Where [Link]=[Link]
And [Link]-no < [Link]-no;

6) Find the employee eligible to fill a position.

Select [Link]-no,position-posting-no,[Link]
From employee,position
Where [Link]=[Link];

7) find the names and pay rates of employee with emp-no less than 1000
whose pay-rate is more than the rate of pay of at least one employee with
emp-no greatar than or equal to 1000.

Select name,payrate
From employee
Where emp-no < 1000 and
Pay-rate > some
(select payrate
from employee
where emp-no  1000);

8) Find employees who are working either on the date 19-04-20023 or

20-04-2003.

Select emp-no
From duty-allocation
Where Day in(20030419 or 20030420);
OR
Select emp-no
From duty-allocation
Where day=20030419 or day=20030420;

9) Find the employees with the lowest pay-rate

Select emp-no,name,pay-rate
From employee
 Where pay-rate 
(select pay-rate
from employee);

10) Find the programmer‟s paid at the minimum pay-rate.

Select name
From employee
Where skill=”programmer”
and pay-rate  all
(select pay-rate
from Employee
where skill=‟programmer‟);

11) Find the names and the pay-rates of all employee who are allocated
duty.

Select name,pay-rate
From employee
Where exists
(select *
from duty-allocation
where [Link]-no=[Link]-no);

12) Find the names and the rate of pay of all employees who are not
allocated duty.

Select name,pay-rate
From employee
Where not exists
(select *
from duty-allocation
where [Link]-no=[Link]-no);

13) find employee numbers of persons who work at posting-no 100 but
don‟t have the skill of „programmer‟.

(select emp-no
from duty-allocation
where posting-nol=100)
minus
(select emp-no
from employee
where skill=‟programmer‟);

14) Find the employees who are programmer;s or work at position no.
100.

Select emp-no
From employee
Where skill=‟programmer‟
Union
 (select emp-no
from duty-allocation
where posting-no=100);

15) Find a list of employees not assigned a duty.

(Select emp-no
From employee)
Minus
(select emp-no
from duty-allocation);

16) Find a list of names of employees with skill of programmer who are
assigned duty.

Select name
From employee
Where emp-no in
((select emp-no
from duty-allocation
where skill=‟programmer‟)
intersect
(select emp-no
from duty-allocation));

17) Get a count of different employees on each shift.

Select shift,count(distinct emp-no)

From duty-allocation
Group by shift;

18) Find the employee numbers of all employee working on at least two
dates.
Select emp-no
From duty-allocation
Group by emp-no
Having (count *) >1;

EXAMPLE 2

Consider the following database.

Doctor (Doctor_no, Doctor_name, Address, City).
Hospital (Hospital_no, Name, Street, City).
Doc_Hosp (Doctor_no, Hospital_no, Date).

Construct the following Queries in SQL.

1. Find out all Doctors who have visited to Hospital in same city in which
they live.

Select Doctor_no
from Doc_Hosp
where Doctor_no in
 (select Doctor_no
from Doctor, Hospital
where [Link] = [Link]);

2. Find to which Hospital “Dr. Joshi “ has visited.

Select Hospital_no
from Doc_Hosp
where Doctor_no =
(select Doctor_no
from Doctor
where Doctor_name =‟[Link]‟);

3. Count no of Doctors visited to “Shree Clinic” on 1st March 2003.

Select count(*)
from Doc_Hosp, Hospital
where Date = 20030301 AND [Link] = „Shree Clinic‟;

4. Find out How many Files „Dr. Joshi‟ has visited in „Shree Clinic‟.
Select count (*)
from Doc_Hosp, Hospital
where Doctor_no =
(select Doctro_no
from Doctor
where Doctor.Doctor_Name = „Joshi‟ AND
[Link] = „Shree Clinic‟);

EXAMPLE 3

Consider the following database.

1) Student (roll_no, name, address).
2) Subject (sub_code, sub_name).
3) Marks (roll_no, sub_code, marks).

Construct the following Queries.

1. Find out the average marks of each Student along with name of the
Student.

select avg(marks), name

from Student, Marks
where Student.roll_no = Marks.roll_no;

2. Find out how many students have failed i.e. obtained less than 40 marks
in „DBMS‟.

Select count (*)

from Marks
where marks < 40 AND
sub_code in (select sub_code
from Subject
where sub_name = „DBMS‟);
EXAMPLE 4
Consider the given database
Project(project-id,proj-name,chief-arch)
Employee(emp-id,emp-name)
Assigned-to(project-id,emp-id)

Find SQL queries for the following statements:

1) Find the employee no. of employees wokring on project „P100‟

Select emp-id
From Assigned-to
Where project-id=‟P 100‟;

2) Find details of employees working on project „P100‟

Select emp-id,emp-name
From employee
where emp-id in
( select emp-id
from Assigned-to
where project-id=‟P100‟);

3) Find details of employees working on „Banking Project‟

Select emp-id,emp-name
From employee
Where emp-id
(select emp-id
from assigned-to,project
where [Link]-id=[Link]-id
and proj-name=‟Database‟);

4) Find details of employees working on both „P100‟ and „P200‟ projects.

Select emp-id,emp-name
From employee
where emp-id in
( select emp-id
from Assigned-to
where project-id=‟P100‟ and project-id=‟P 200‟);

5) Find the employee numbers of employees who do not work on project

„P210‟

Select emp-id,emp-name
From employee
where emp-id in
 ( select emp-id
from Assigned-to
where project-id <> ‟P210‟) ;

6) Find the employee details working on all projects.

Select emp-id
From assigned-to
Where project-id=all
(select project-id
from project);
7) Find the employee numbers who work on at least all those projects that
employee „E100‟ works on.

(Select emp-id
From assigned-to
Where project-id=all
(select project-id
from assigned-to
where emp-id=‟E100‟))
minus E100);

EXAMPLE 5
Consider the following database
employee(employee-name, street, city)
works(employee-name, company-name, salary)
company(company-name, city)
manages(employee-name, manager-name)
For each of the following queries, give an expression in SQL.

a. Find the names of all employees who work for First Bank Corporation.

Select employee-name
From works
Where company-name=‟First Bank Corporation‟;

a. Find the names and cities of residence of all employees who work for
First Bank Corporation.

Select [Link]-name,city
From works,employee
Where [Link]-name = [Link]-name
and company-name=‟First Bank Coropration‟;

b. Find the names, street address, and cities of residence of all employees
who work for First Bank Corporation and earn more than Rs. 10,000
per annum

Select [Link]-name,city
From works,employee
Where [Link]-name = [Link]-name
 and company-name=‟First Bank Coropration‟
and salary >10000;

c. Find the names of all employees in this database who live in the same
city as the company for which they work.

Select employee-name
From employee,company,works
Where [Link]-name=[Link]-name
and [Link]-name=[Link]-name
[Link]=[Link];

d. Find the names of all employees in this database who do not work for
First Bank Corporation.

Select employee-name
From employee
Where employee-name not-in
(select employee-name
from works
where company-name=‟First Bank Corporation‟);

e. Find the names of all employees who earn more than every employee of
Small Bank Corporation.

Select employee-name
From works
Where salary > (select max(salary)
From works
Where company-name=‟First Bank Corporation‟);

EXAMPLE 6
Consider the following database
Item(Item_no,description,price)_
Order(Order_no,Item_no,Salesman_id,Qty)
Salesman(Salesmand_Id,Name,Address,Telno)

i) Find all the items orderd by salesman „BOB‟ having quantity on

order greater than 100.

select item_no
From order,salesman
Where order.salesman_id=salesman.salesman_id and
Name=‟BOB‟;

ii) Find all the salesman whose name starts with „B‟.

Select salesman_id,name
From salesman
Where name like „B%‟;
 iii) Find all the salesman who order all the items.

Select salesman_id,name
From salesman
Where Item_no = all(select Item_no
From item);

iv) Find all salesman who order the same Item no. with same quantity
select salesman_Id,name
From salesman
Where salesman-id in
(select salesman-id
from order
where order-no=qty);
EXAMPLE 7
Consider the following database
Members(Name,address,balance)
Orders(Order-no,name,item,quantity)
Suppliers(Supplier-name,supplier-address,item,price)
Give an expression in SQL.
a) Find the names and addresses of suppliers who supply either milk or
icecream.

Select supplier-name,supplier-address
From suppliers
Where item=‟milk‟ or item=‟icecream‟;

b) Find the suppliers that supply every item ordered by Mr. Patel.

Select supplier-name
From supplier
Where item=all ( select item from orders where name =‟Mr Patel‟);

c) Find the supplier-name , item and prices of all suppliers that supply
atleast one item orderd by Mr. Patel.

Select supplier-name
From supplier
Where item=some( select item from orders where name =‟Mr Patel‟);

d) Find the names and addresses of members who are having balance
greater than the average balance

Select name,address
From members
Where balance > (select avg(balance) from members);
EXAMPLE 8
Given the following database
Enroll(S#,C#,Section) - S# represents Student no.
Teach(Prof,C#,Section) - S# represents Student no.
Guides(Prof,S#) - Prof is project guide of S#
Student(S#,Sname) - Sname is student name
Express following queries in SQL.
a) List all students taking course with Prof. Rao.

Select Sname
From Student,Enroll,Teach
Where Student.S#=Enroll.S#
And Enroll.C#=Teach.C#
And Prof=‟Rao‟;

b) List all students taking at least one course that their project guide
teaches.

Select sname ,S#

From Student,Teach,Guides
Where [Link] =[Link]
And Student.S# = Guide.S#;

EXAMPLE 9
Consider the following relations concerning driving school
Student(St-name,Class#,Theroy-Mark,Driving-Mark)
Student-Driving_Teacher(St-Name,Dr-T-Name)
Teacher-Theory-Class(Class#,Th-T-Name)
Teacher-Vehicle(Dr-T-Name,Licence#)
Vehicle(License#,Make,Model,Year)
A student takes one theory class as well as driving lessons and at the lessons
and at the end of session receives marks for theory and driving. A teacher
may teach theory, driving or both. Write the following queries in SQL.

a) Find the list of teachers who teach theory and give driving lessons on all
the vehicles.

Select Th-T-Name
From Teacher-Theory-Class,Teacher-vehicle,Vehicle
Where [Link]-T-Name=[Link]-T-Name
And [Link]#=[Link]#;

b) Find the list of teachers who can drive all the vehicles.

Select Dr-T-Name
From Teacher-vehicle,vehicle
Where [Link]#=[Link]#;
OR
Select Dr-T-Name
From Teacher-vehicle,
Where License#=all(select Licence# from Teacher-Vehicle);

c) Find the list of students who have scored more marks in theory classes
than their driving lessons.
Select St-name
From student
Where Theory-Mark >( select Driving-Mark from Student);
d) Find the list of students who have scored more than average marks in
theory class.

Select st-name
From student
Where Theory-Mark >( select avg(Theory-Mark) from Student);

EXAMPLE 10
Consider the following database:
Students(sid, sname, gender)
Takes(sid, cid, grade)
Courses(cid, cname, dept, year)
Give an SQL expression for each of the following operations.
a. Find the names of all students who take the course called
"Introduction to DBMS".

select sname
from Students, Takes, Courses
where cname = "Introduction to DBMS"
and [Link] = [Link]
and [Link] = [Link];
b. Find the names of all students whose average grade is above 3.5.

select sname
from Students, Takes
where [Link] = [Link]
group by [Link]
having avg(grade) > 3.5;
c. Find the sid's of all students who take at least six 4th-year courses
from the department of Computer Science.
select sid from
Takes, Courses
where [Link] = [Link]
and dept = "computer science"
and year = 4
group by sid
having count(distinct cid) >= 6;

d. numbers of (distinct) male and female students who take at least one
course from the department of Computer Science.

select gender, count(distinct [Link])

from Students, Takes, Courses
where dept = "computer science"
and [Link] = [Link]
and [Link] = [Link]
group by gender;

e. Find the highest grade of each computer science course.

select [Link], max(grade)

 from Takes, Courses
where dept = "computer science" and [Link] = [Link]
group by [Link];

f. Find the names of all courses taken by student "RAKESH".

select cname
from Students, Takes, Courses
where [Link] = [Link] and
[Link] = [Link] and
sname = "RAKESH";

g. Find the ID's of all courses that are taken by at least 100 students in
the year 1995.

select cid
from Takes, Courses
where [Link] = [Link] and year = 1995
group by cid
having count(sid) >= 100;

h. Find the names of all students whose lowest grade is at least 3.5

select sname
from Students, Takes
where [Link] = [Link]
group by [Link]
having min(grade) >= 3.5

EXAMPLE 11

Part(Part-no,Part-name,color,weight,city)
Supplier(Supp-no,Supp-name,Status,city)
Supplied-parts(Supp-no,Part-no,Qty)

1) Get name of all parts supplied

Select part-name
From part,ssuplied-parts
Where [Link]-no=[Link]-no;

2) Get the supplier no of suppliers from Dhule with status greater than 10
Select supp-no
From supplier
Where city=‟Dhule‟
& status>10;

3) Get all name & pno from parts supplied

Select part-name ,part-no
From part,supplied-part
 Where [Link]=[Link]-no;

4) Get the supplier no & status for the supplier in Dhule in decending order
by status
Select supp-no,status
From supplier
Where city =‟Dhule‟
Order by status DESC;

5) For each part supplied get the part no & name of cities supply in that
parts
Select part-no,[Link]
From supplied-part,supplier
Where [Link]-no=[Link]-no;

6) Get the supplier name to supply part-no 10

Select supp-name
From supplier,supplied-parts
Where [Link]=[Link]-no
AND part-no=10;

7) Get the supplier name for the supplier to supply „WHITE‟ parts
Select supp-name
From supplier,supplied-parts
Where [Link]-no=[Link]-no and
part-no in( select part-no
From part
Where color =‟WHITE‟)

8) Get the part no for the part having weight grater than 1kg but not
supplied by supp no=10
Select part-no
From part
Where weight>1
Minus
Select part-no
From supplied-parts
Where supp-no=10;

9) Get the part-no of the parts that either weight more than 1kg or are
currently by supplier no. 10
Select part-no
From part
Where weght>1
UNION
Select part-no
From supplied-parts
Where supp-no=10;

10) Find out the parts having weight greater than 1kg &supplied by supplier
10.
Select part-no
From part
 Where weight>1
INTERSECT
Select part-no
From supplied-parts
Where supp-no=10;

11) Find out the no. of supplier

Select count (*)

From supplier;

12) Find out the no. of supplier from Dhule

Select count (*)
From supplier
Where city=‟Dhule‟;

13) Find minimum weight of all parts

Select min(weight )
From part;

14) Find the sum of all weight in p

Select sum(weight)
From part;

15) Find the supp-no which is having max-status

Select supp-no
From supplier
Where status =(select max(status)
From supplier);

16) Find the average of st. of the suppiler

Select AVG(status )
From supplier;

17) Find the part supplied get the part-no & qty supplied by the supplier

Select part-no,sum(qty)
From supplied-parts;
Group by part-no;

18) Get the part-no for all parts supplied by more than one supplier
Select part-no
From supplied-parts
Group by part-no
Having count (*) >1;

Example 12
Consider the database schema
Gives(Student,seminar,Marks)
Seminar(Seminar,Guide)
Guide(Guide,Department)