SQL QUERIES (21CSG45)
NOTE: Create and Insert queries are not given as it is already discussed
in the Class. Syntax is given for your reference
CREATE TABLE TABLE_NAME (ATTRIBUTE1 DATATYPE1,
ATTRIBUTE2 DATATYPE2, ……….. ATTRIBUTEn DATATYPEn);
INSERT INTO TABLE_NAME VALUES(“ “, …….);
KEYWORDS FOR SPECIFYING CONSTRAINTS:
PRIMARY KEY
REFERENCES
NOT NULL
UNIQUE
STUDENT LIBRARY
c) List all the student names with their membership numbers
select s.studname, m.memno
from student s, membership m
where m.studno=s.studno;
d) List all the issues for the current date with student and Book names
select i.issno, s.studname, b.bookname
from iss_rec I, membership m, student s, book b
where i.memno=m.memno and m.studno=s.studno and i.issdate=to_char(sysdate);
e) Give a count of how many books have been bought by each student
select s.studno, count(i.bookno)
from student s, membership m, book b, iss_rec i
where s.studno=m.studno and b.bookno=i.bookno and i.mem_no=m.mem_no
group by s.studno;
f) Give a list of books taken by student with stud_no as 5
select bookname
from book
where bookno in (select bookno
from iss_rec
where memno in (select memno
from membership
where studno in (select studno
from student where studno=5)));
PROJECT
c) Find the list of guide, who are guiding more than two student groups.
select guide_name,count(*)
from guide g, project1 p
where g.guide_no=p.guide_no
group by guide_name
having count(*)>1;
d) Find the list of project no, project name & name of guide, in domain of Data Base.
select project_no, project_title, guide_name
from project1, guide
where project1.guide_no= guide.guide_no and
project1.project_domain='database';
e) Create a view as student_project details that lists student name, project name and guide
name
create view student_project as
select name,guide_name,project_title
from student1,guide,project1
where project1.guide_no=guide.guide_no;
select * from student_project;
AIRLINE
c) Find the names of aircraft such that all pilots certified to operate them earn more than
80,000.
SELECT ANAME
FROM AIRCRAFT A, EMPLOYEES E, CERTIFIED C
WHERE C.EID=E.EID AND C.AID=A.AID AND E.SALARY>80000;
d) For each pilot who is certified for more than three aircraft, find the eid and the maximum
cruising range of the aircraft that he (or she) is certified for.
SELECT C.EID, MAX(A.CRANGE)
FROM EMPLOYEES E,CERTIFIED C,AIRCRAFT A
WHERE E.EID=C.EID AND C.AID=A.AID
GROUP BY C.EID
HAVING COUNT(*)>3;
e)Find the names of pilots whose salary is less than the price of the cheapest route from Los
Angeles to Honolulu.
SELECT DISTINCT E.ENAME
FROM EMPLOYEES E,CERTIFIED C
WHERE E.EID=C.EID AND E.SALARY<
(SELECT MIN(PRICE) FROM FLIGHT F WHERE
F.FFROM='BANGALORE' AND
F.FTO='FRANKFURT');
f) Find the second highest salary of an employee.
SELECT MAX(SALARY)
FROM EMPLOYEES
WHERE SALARY NOT IN ( SELECT MAX(SALARY) FROM EMPLOYEES);
OR
SELECT MAX(SALARY)
FROM EMPLOYEES
WHERE SALARY <> ( SELECT MAX(SALARY) FROM EMPLOYEES);
FOR nth highest salary
SELECT *
FROM EMPLOYEES EMP1
WHERE (N-1) = ( SELECT COUNT(DISTINCT(EMP2.SALARY))
FROM EMPLOYEES EMP2
WHERE EMP2.SALARY > EMP1.SALARY);
COMPANY
c) Find the names and address of all employees who work on same department.
Assume Department Name is Research
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME=’RESEARCH’ AND DNUMBER=DNO;
d) Retrieve a list of employees and the projects they are working on, ordered by department
and, within each department, ordered alphabetically by last name, then first name.
SELECT D.DNAME, E.LNAME, E.FNAME, P.PNAME
FROM DEPARTMENT D, EMPLOYEE E, WORKS_ON W, PROJECT P
WHERE D.DNUMBER= E.DNO AND E.SSN= W.ESSN AND W.PNO= P.PNUMBER
ORDER BY D.DNAME, E.LNAME, E.FNAME;
e) Create a view Dept_info that gives details of department name, Number of employees and
total salary of each employee.
CREATE VIEW DEPT_INFO(Dept_name, No_of_emps, Total_sal) AS
SELECT DNAME, COUNT (*), SUM (Salary)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY DNAME;
SAILORS
c) Find the names of sailors who have reserved a red boat, and list in the order of age.
select s.sname, s.age
from sailors s, reserves r, boats b
where s.sid=r.sid and r.bid=b.bid and b.color='red'
order by s.age;
d) Find the names of sailors who have reserved boat 103
select s.sname
from sailors s
where s.sid in (select r.sid
from reserves r
where r.bid=103);
OR
select s.sname
from sailors s
where exists (select * from reserves r where r.bid=103 and r.sid=s.sid);
e) Find the name and the age of the youngest sailor.
select s.sname, s.age
from sailors s
where s.age<=ALL (select age from sailors);
f) Find the average age of sailors for each rating level that has at least two sailors.
select s.rating, avg(s.age) as average_age
from sailors s
group by s.rating
having count(*)>1;
