Scribd
Upload
2K views12 pages

DBMS Module 2

The document contains several SQL queries related to employee, department, project and other database tables. It asks to write SQL queries to retrieve specific data based on conditions provided in different sections. Some key points include retrieving employee details based on name, ID or other fields, counting number of projects in a given year, getting manager details and aggregating data by department. Overall the questions cover basic to advanced SQL queries on employee, project and other relational databases.

Uploaded by

F07 Shravya Gullody
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2K views12 pages

DBMS Module 2

The document contains several SQL queries related to employee, department, project and other database tables. It asks to write SQL queries to retrieve specific data based on conditions provided in different sections. Some key points include retrieving employee details based on name, ID or other fields, counting number of projects in a given year, getting manager details and aggregating data by department. Overall the questions cover basic to advanced SQL queries on employee, project and other relational databases.

Uploaded by

F07 Shravya Gullody
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 12

QUERIES

1.

a) Select the detail of the employee whose name start with P.

b) Select the detail of employee whose emailId is in gmail.

c) How many project started in year 2010.


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

e) Select the name of the employee who is working under Abhishek.

2.

a. Write an SQL query to fetch the EmpId and FullName of all the
employees working under the Manager with id – ‘986’.
b. Write an SQL query to fetch the different projects available from the
EmployeeSalary table.

c. Write an SQL query to find the employee id whose salary lies in the
range of 9000 and 15000.

d. Write an SQL query to fetch the employees whose name begins with any
two characters, followed by a text “hn” and ends with any sequence of
characters.

e. Write an SQL query to fetch employee names having a salary greater


than or equal to 5000 and less than or equal to 10000.

3. Write the SQL queries for the following database schema:


Student (USN, NAME, BRANCH, PERCENTAGE)
Course (CID, CNAME, FID)
Faculty (FID, FNAME, DEPARTMENT, DESIGNATION, SALARY)
Enroll (CID, USN, GRADE)
1. Retrieve the names of all students enrolled for the course 'CS_54'
SELECT NAME FROM STUDENT S, COURSE C WHERE C.CID='CS-54';
2. List all the departments having an average salary of the faculties above
Rs. 10,000.
SELECT DEPARTMENT, AVG(SALARY) as AVG_SALARY FROM FACULLTY
WHERE AVG_SALARY>10000;
3. List the names of the students enrolled for the course'CS_51' and
having 'B' grade.
SELECT NAME FROM FACULTY F, ENROLL E WHERE E.CID='CS-51' AND E.
GRADE='B';

4. Given the following schema:


emp (fname, Lname, SSN. Bdate, address, gender, salary, superSSN, Dno)
dept (Dname, Dnumber, MgrSSN, mgrstartdate)
dept_loc (Dnumber, Dloc)
project (Pname, Pnumber, Ploc, Duum)
works on (ESSN, Pno, hours)
Dependent (ESSN, dependent_name, gender, bdate, relationship)
Give the relation algebra expression for the following:
i) Retrieve the name of the manager of each department.

ii) For each project retrieve the project number, project name and
number of employees who worked on that project.
R1 ←PROJECT pnumber=pnoWORKS_ON
R2←π pnumber, pname, count(*) (R1)
iii) Retrieve the names of employees who work on all the project
controlled by department 5
DEPT5_PROJS ← ρ(Pno)(πPnumber(σDnum=5(PROJECT)))
EMP_PROJ ← ρ (Ssn, Pno) (πEssn, Pno (WORKS_ON))
RESULT_EMP_SSNS ← EMP_PROJ ÷ DEPT5_PROJS
RESULT ← πLname, Fname (RESULT_EMP_SSNS * EMPLOYEE)

iv) Retrieve the name of employees who have no dependents.


v) Retrieve number of Male and Female employee working in the
Company.
genderFCOUNT(gender) (EMP)

vi) Retrieve the name and address of all employees who work for
'sports' department.
R1← ρ DNAME=’sports’(DEPT)

R2←R1 dnumber=Dno EMP


R3←πFname, Lname, Address(R2)
vii)Retrieve each department number, number of employers and
their average salary.

viii) List the project number, controlling department number


and department manager's last name, address and birthdate.

ix) Retrieve the name of employees with 2 or more dependents.

x) List female employees from dno = 20 earning more than 50000


R1← ρ Dno=’20’ and salary>50000 and gender=’female’ (EMP)
R2←πFname, Lname(R1)
xi) Retrieve the name and address of all employees who work for
the “Accounts” department.
ACCOUNTS_DEPT ← σDname= ‘Accounts’(DEPARTMENT)

ACCOUNTS_EMPS ← (ACCOUNTS_DEPT  Dnumber=Dno EMPLOYEE)


RESULT ← πFname, Lname, Address (ACCOUNTS_EMPS)
5. Consider the following schema :
Sailors (Sid, Sname, rating , age)
Boats (bid, bname, color)
Reserves (Sid, bid, day).
Write queries in SQL.
i) Find the names of sailors who have reserved at least one boat
ii) Find sailors whose rating is better than some sailors called 'Jennifer'.
(Use nested query)
iii) Find the average age of sailor for each rating level that at least two
sailors.
iv) Find the name and age of the oldest sailor.
i) Find the ages of sailors whose name begins and ends with A and has
atleast three characters.
Select age
from sailors
where Sname like ‘A___%A’;

ii) Find the age of the youngest sailor who is eligible to vote (i.e. is
atleast 18 years old) for each rating level with atleast two such
sailors.
Select s.rating,MIN(s.age)
from sailors s
where s.age>18
group by s,rating
having 1 < (SELECT COUNT (*)
FROM Sailors s2
WHERE s.rating=s2.rating);

iii) Find the names of sailors who have not reserved a red boat. (use
nested query).
SELECT S.sname
FROM Sailors S
WHERE S.sid NOT IN ( SELECT R.sid
FROM Reserves R
WHERE R.bid IN ( SELECT B.bid
FROM Boats B
WHERE B.color=`red’ ));
iv) Compute increments for the rating of persons who have sailed two
different boats on the same day.
SELECT r.sid, r.day, COUNT(*), s.rating
FROM reserves r
JOIN sailors s
ON r.sid=s.sid
GROUP BY day
HAVING COUNT(r.day)=2;

6.Given the schema:


Passenger (pid, pname, pgender, pcity)
Agency (aid, anme, acity)
Flight (fid, fdate, time, src, dest)
Booking (pid, aid, fid, fdate)
Give relation algebra expression for the following:
1. Get the complete details of all flights to new Delhi
σ dest= “New Delhi” (flight)
2. Find only the flight numbers for passenger with paid 123 for flights
to Chennai before 06/11/2020
Π fid (σ pid = 123 (booking) ⨝ σ dest = “Chennai” ^ fdate < 06/11/2020 (flight))

3. Find the passenger names for those who do not have any bookings
in any flights
Π pname  ((Π  pid  (passenger) - Π  pid  (booking)) ⨝ passenger)
4. Get the details of flights that are scheduled on both dates
01/12/2020 and 02/12/2020 at 16:00 hours
(σ  fdate = 01/12/2020 ^ time = 16:00  (flight)) ∩ (σ  fdate = 02/12/2020 ^ time = 16:00  (flight))
5. Find the details of all male passengers who are associated with jet
agency.
Π  passengers.pid, pname, pcity  (σ  pgender = “Male” ^ aname =

‘Jet’  (passengers ⨝ booking ⨝ agency))

7.Write SQL query for the following database scheme:


Employee(employee_name, street, city)
Works (employee_name, company_name, salary)
Company(company_name, city)
Manages(employee_name, manager_name)
1. Find the names, street address, and cities of residence for all
employees who work for 'First Bank Corporation' and earn more than
$10,000
select employee. Employee_name, employee. Street, employee. City
from employee, works where employee. Employee_name=works.
Employee_name and company_name = 'First Bank Corporation' and
salary > 10000);

2. Find the names of all employees in the database who do not work for
First Bank Corporation'. Assume that all people work for exactly one
company
select employee_name from works where company_name != ’First Bank
Corporation’;

3. Find the names of all employees in the database who earn more that
every employee of Small Bank Corporation'. Assume that all people
work for at most one company
select employee_name from works where salary > all (select salary from
works where company_name = ’Small Bank Corporation’);

4. Find the name of the company that has the smallest payroll
select company-name from works group by company-name having sum
(salary) <= all (select sum (salary) from works group by company-name);
5. Find the names of all employees in the database who live in the same
cities and on the same streets as do their managers.
select P.employee-name from employee P, employee R, manages M
where P.employee-name = M.employee-name and M.manager-name =
R.employee-name and P.street = R.street and P.city = R.city;

8.

ANSWERS:

9.

ANSWERS
10.

ANSWERS
11.

ANSWERS

You might also like