Example of SQL Fundamentals

Examples of SQL Fundamentals

AU: Dec.-14,15,17,19, May-15,16,17,18, Marks 15

• Structure Query Language(SQL) is a database query language used for storing and managing data in Relational DBMS.

Example 1.14.1 Write the DDL, DML, DCL for the students database. Which contains student details: name, id,DOB, branch, DOJ.

Course details: Course name, Course id, Stud.id,Faculty name, id, mark

AU: Dec.-17, Marks 15

Solution:

DDL Commands

CREATE TABLE Student

(

stud_name varchar(20),

stud_id int(3),

DOB varchar(15),

branch varchar(10),   

DOJ varchar(15),

);

CREATE TABLE Course

(

course_name varchar(20),

course_id int(5),

stud_id int(3),

facult_name varchar(20),

faculty_id varchar(5),

marks real

);

DML Commands

The commands which we will use here are insert and select. The insert command is used to insert the values into database tables. Using the select command, the database values can be displayed.

(1) Inserting values into Student table

insert into Student(stud_name,stud_id,DOB,branch,DOJ)

values('AAA',11,'01-10-1999', 'computers','5-3-2018')

insert into Student(stud_name,stud_id,DOB,branch,DOJ)

values('BBB',12, 24-5-1988', 'Mechanical', '17-2-2016')

insert into Student(stud_name, stud_id,DOB,branch,DOJ)

values('CCC',13,'8-1-1990', 'Electrical','22-9-2017')

(2) Inserting values into Course table

insert into Course (course_name,course_id,stud_id,faculty_name, faculty_id,marks) values('Basic',101,11,'Archana', 'F001','50')

insert into Course(course_name,course_id,stud_id,faculty_name, faculty_id,marks) values('Intermediate',102,12,'Rupali', 'F002','70')

insert into Course (course_name,course_id,stud_id,faculty_name, faculty_id,marks) values('Advanced',103,13,'Sunil','F003', '100')

(3) Displaying records of Student table

Select * from Student;

(4) Displaying records of Course table

Select * from Course;

DCL Commands

The DCL command is used to control privileges in Database. To perform any operation in the database, such as for creating tables, sequences or views, a user needs privileges.

(1) GRANT Command

SQL GRANT is a command used to provide access or privileges on the database objects to the users.

Syntax

GRANT privilege_name

ON object_name

TO {user_name |PUBLIC |role_name}

[WITH GRANT OPTION];

privilege_name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT.

• object_name is the name of an database object like TABLE, VIEW, STORED PROC and SEQUENCE.

• user_name is the name of the user to whom an access right is being granted.

• PUBLIC is used to grant access rights to all users.

• roll_name are a set of privileges grouped together.

• WITH GRANT OPTION - allows a user to grant access rights to other users.

Example        

GRANT SELECT ON student_details TO user1

This query will grant the SELECT permission to student_details table to user named user1.

Similarly we can GRANT more than one privileges to user in a table

GRANT SELECT, INSERT, DELETE, UPDATE ON student details TO user1

For granting all privileges to user we use sysdba. The sysdba is a set of priviliges which has all the permissions in it.

GRANT sysdba TO user1

(2) REVOKE

The REVOKE command removes user access rights or privileges to the database objects.

Syntax

REVOKE privilege_name

ON object_name

FROM {user_name |PUBLIC |role_name}

Example

To remove access right for SELECT to the table student_details for userl we write the query

REVOKE SELECT ON student details FROM user1;

Example 1.14.2 Write the following queries in relational algebra and SQL

(i) Find the names of employee who have borrowed a book published by McGraw Hill

(ii) Find the names of employees who have borrowed all books published by McGraw-Hill AU: May-17, Marks 10

Solution:              

We will assume the databases as -

member(memb_no, name, dob)

books(isbn, title, authors, publisher)

borrowed(memb_no, isbn, date)

(i) Relational Algebra:

Πname((publisher McGraw Hill' books)  borrowed  member)

SQL:

SELECT name

FROM member

WHERE meber.memb_no=borrowed.memb_no AND books.isbn=borrowed.isbn

AND books.publisher 'McGraw Hill';

(ii) Relational Algebra

ρ(Tempname,( Πmemb_no,isbn borrowed)/ Πisbn (σpublisher-McGraw Hill' books))) Πname (Tempname member)

SQL:

SELECT distinct M.name

FROM Member M,

WHERE NOT EXIST

(                 

(SELECT isbn

FROM books

WHERE publisher = 'McGraw Hill'

)

EXCEPT

(SELECT isbn

FROM borrowed R

WHERE R.memb_no = M.memb_no

)

)

Example 1.14.3 Assume the following table.

Degree (degcode, name, subject)

Candidate (seatno, degcode, name, semester, month, year, result)

Marks (seatno, degcode, semester, month, year, papcode, marks)

[degcode - degree code, name - name of the degree (Eg. MSc.), subject - subject of the course (Eg. Physis), papcode - paper code (Eg. A1)]

Solve the following queries using SQL;

Write a SELECT statement to display,

(i) all the degree codes which are there in the candidate table but not present in degree table in the order of degcode.

(ii) the name of all the candidates who have got less than 40 marks in exactly 2 subjects.

(iii) the name, subject and number of candidates for all degrees in which there are less than 5 candidates.

(iv) the names of all the candidate who have got highest total marks in MSc. Maths. AU: Dec.-15, Marks 4+4 +4 +4

Solution:

(i) SELECT C.degcode

FROM Candidate C,

WHERE

NOT EXISTS

(SELECT D.degcode

FROM Degree D

WHERE D.degcode=C.degcode)

ORDER by C.degcode

(ii) SELECT C.name

FROM Candidate C, Degree D, Marks M

WHERE

C.seatno M.seatno AND C.degcode=D.degcode AND C.degcode-M.degcode AND M.marks <40 GROUP BY C.seatno

HAVING count(D.subject)=2;

(iii) SELECT D.name,D.subject,count(*)

FROM degree D, Candidate C

WHERE D.degcode=C.degcode

HAVING( SELECT count(*) FROM Candidate <5);

(iv) SELECT C.name

FROM Candidate C, Degree D, Marks M

WHERE      

D.degname='MSc' AND D.subject='Maths' AND C.degcode=D.degcode AND C.seatno-M.seatno

AND

M.marks (SELECT max(M.marks) FROM Marks M)

Example 1.14.4 Consider a student registration database comprising of the below given table schema.

Student Number Course Number Date

Consider a suitable sample of tuples / records for the above mentioned tables and write DML statements (SQL) to answer for the queries listed below.

i) Which courses does a specific professor teach?

ii) What courses are taught by two specific professors?

iii) Who teaches a specific course and where is his/her office?

iv)For a specific student number, in which courses is the student registered and what is his/her name?

v) Who are the professors for a specific student?

vi) Who are the students registered in a specific course?

Solution:

(i)

SELECT P.name,C.description

FROM Professor P, Course C

WHERE P.ProfessorNumber=C.ProfessorNumber

HAVING count(DISTINCT P.name)=2

(ii)

SELECT P.name,C.description

FROM Professor P, Course C

WHERE P.Professor Number=C.ProfessorNumber

(iii)

SELECT P.name,P.office, C.description

FROM Professor P, Course C

WHERE P.ProfessorNumber=C.Professor Number

(iv)

SELECT S.StudentNumber,S.StudentNumber, C.Description

FROM Student S, Course C, Registration R

WHERE S.StudentNumber-R.StudentNumber AND C.CourseNumber=R.CourseNumber

(v)

SELECT S.StudentName, P.Name

FROM Student S, Course C, Professor P, Registration R

WHERE C.ProfessorNumber=P.Professor Number

AND C.CourseNumber=R.Course Number

AND S.StudentNumber=R.StudentNumber GROUP BY P.Professor Number

(vi)

SELECT S.StudentName, C.Description

FROM Student S, Course C, Registration R

WHERE S.StudentNumber-R.Student Number

AND R.CourseNumber=C.CourseNumber

GROUP BY C.CourseNumber

Example 1.14.5 For the following database, identify primary key and foreign key wherever applicable and solve the given queries in SQL.

Item (ino, description, unit_price)

Supplier (sno, sname, address)

Supplied (sno, ino, sdate, qty, per_unit_discount)

1. Find supplier name for the suppliers who supply every item.

2. Find distinct item names for the items supplied with total discount > 500.

3. Pair of supplier names supplying same items on same dates.

Solution:

• The primary key for Item table is ino.

• The primary key for supplier table is sno

• The primary key for supplied table is (sno,ino)

(1) Find supplier name for the suppliers who supply every item.

SELECT S.sname

FROM Supplier S

WHERE NOT EXISTS (

(SELECT * FROM Item I

WHERE NOT EXISTS

SELECT *

FROM Supplied SP

WHERE SP.sno S.sno

AND SP.ino I.ino)

)

(2) Find distinct item names for the items supplied with total discount > 500. SELECT DISTINCT description

FROM Item, Supplied

WHERE Item.ino=Supplied.ino

AND Supplied.per_unit_discount >500

(3) Pair of supplier names supplying same items on same dates.

SELECT sname

FROM Supplier

WHERE Supplied.sno-Supplier.sno

AND

Item.ino Supplied.ino

)

Example 1.14.6 Write SQL statements for the following (any five)

Consider the following database

pilot (pid, pname)

flight (fid, ftype, capacity)

route (pid, fid, from_city, to_city)

i) List the details of flights having capacity more than 300.

ii) List the flights between 'Surat' and 'Mumbai'.

iii) List the names of the pilots who fly from 'Pune'.

iv) List the route on which, pilot named 'Mr Kapoor' flies

v) List the pilots whose names, starts with letter 'A' %' but does not end with letter 'A'. vi) List the name of pilots who fly 'boeing 737' type of flights.

Solution:

(i) List the details of flights having capacity more than 300.

SELECT FROM flight

WHERE capacity>300

(ii) List the flights between 'Surat' and 'Mumbai'.

SELECT fid

FROM flight, route

WHERE

flight.fid=route.fid AND

route.from_city= 'Surat' AND route.to_city-'Mumbai';

(iii) List the names of the pilots who fly from 'Pune'.

SELECT pname

FROM pilot, route

WHERE pilot.pid=route.pid

AND route.from_city= 'Pune';

(iv) List the route on which, pilot named 'Mr Kapoor' flies

SELECT from city, to_city

FROM route, pilot

WHERE pilot.pid=route.pid

AND pilot.pname='Mr.Kapoor';

(v) List the pilots whose names, starts with letter 'A' %' but does not end with letter 'A'.

SELECT pname

FROM pilot

WHERE pname LIKE 'A%' MINUS

SELECT pname FROM pilot

WHERE pname LIKE '%A';

(vi) List the name of pilots who fly 'boeing 737' type of flights.

SELECT pname

FROM pilot, flight, route

WHERE flight.ftype = 'boeing 737' AND

pilot.pid route.pid AND

route.fid flight.fid;

Example 1.14.7 Consider the relation Database.

Person (SSN, Name, city)

Car (License_no, year, Model, SSN)

Accident(drive_no, SSN, license_no, accidentyear, damage_Amt) Query:

1. Find out total no of cars that had accident in 1988.

2. Find the Name of driver who did not have an accident in 'Delhi'.

 3. Find the car, who don't have total damage of more than 1000.

4. Find the cars sold in 2006 and whose owner are from 'Vadodara'

5. How many different models of car are used by 'Mr.abc'?

6. Find the lucky persons who have not met any accident yet.

Solution :

1. Find out total no of cars that had accident in 1988.

SELECT count(License_no)

FROM Car, Accident

WHERE Accident.accidentyear=1988

2. Find the Name of driver who did not have an accident in 'Delhi'.

SELECT Name

FROM Person, Accident

WHERE Person.SSN=Accident.SSN AND Person.city<>'Delhi'

3. Find the car, who don't have total damage of more than 1000.

SELECT License_no

FROM Car, Accident

WHERE Accident.damage_amt <1000 AND

Car.License_no=Accident.licence_no;

4. Find the cars sold in 2006 and whose owner are from 'Vadodara'

SELECT license no

FROM Car, Person

WHERE Car.SSN = Person. SSN AND

Car.year 2006 and Person.city = 'Vadodara';

5. How many different models of car are used by 'Mr.abc' ?

SELECT count(model)

FROM Car, Person

WHERE Car.SSN = Person. SSN AND

Person.Name = 'Mr.abc';

6. Find the lucky persons who have not met any accident yet.

SELECT Name

FROM Person

WHERE person.SSN NOT IN (SELECT SSN FROM Accident);

Example 1.14.8 We have following relations:

Supplier (S#, sname, status, city)

Parts (P#, pname, color, weight, city)

SP(S#, P#, quantity)

Answer the following queries in SQL,

i) Find name of supplier for city = 'Delhi'.

ii) Find suppliers whose name start with 'AB'.

iii) Find all suppliers whose status is 10, 20 or 30.

iv) Find total number of city of all suppliers.

v) Find s# of supplier who supplies 'red' part.

vi) Count number of supplier who supplies 'red' part. vii) Sort the supplier table by sname.

Solution:

i) Find name of supplier for city = 'Delhi'.

SELECT sname

FROM Supplier

WHERE city= 'Delhi'

ii) Find suppliers whose name start with 'AB'.

SELECT sname

FROM Supplier

WHERE sname='AB%'

iii) Find all suppliers whose status is 10, 20 or 30.

SELECT sname

FROM Supplier

WHERE status BETWEEN 10 AND 30

iv) Find total number of city of of all suppliers.

SELECT count(*) FROM (SELECT DISTINCT CITY FROM Supplier);

v) Find s# of supplier who supplies 'red' part.

SELECT DISTINCT supplier.S#

FROM Supplier, Parts, SP

WHERE Supplier.S# = SP.S#

AND SP.P# = Parts.P# AND Parts.color = 'red';

vi) Count number of supplier who supplies 'red' part. SELECT count(*)

FROM (SELECT DISTINCT Supplier.S#

FROM Supplier, Parts, SP

WHERE Supplier.S# SP.S#

AND SP.P# = Parts.P# AND Parts.color = 'red');

vii) Sort the supplier table by sname.

SELECT *

FROM Supplier

ORDER BY sname;

Example 1.14.9We have following relations:

Supplier (S#, sname, status, city)

Parts (P#, pname, color, weight, city)

SP(S#, P#, quantity)

Answer the following queries in SQL,

i) Delete records in supplier table whose status is 40.

ii)Add one field in supplier table.

Solution:

i) Delete records in supplier table whose status is 40.

DELETE FROM Supplier

WHERE status=40

ii) Add one field in supplier table.

ALTER TABLE Supplier

ADD(PhoneNo number);

Example 1.14.10 We have following relations:

Supplier (S#, sname, status, city)

Parts (P#, pname, color, weight, city)

SP(S#, P#, quantity)

Answer the following queries in SQL,

i) Find name of parts whose color is 'red'.

ii) Find parts name whose weight less than 10 kg.

iii) Find all parts whose weight from 10 to 20 kg.

iv) Find average weight of all parts.

v) Find S# of supplier who supply part 'p2.

vi) Find name of supplier who supply maximum parts. vii) Sort the parts table by pname.

Solution:

i) Find name of parts whose color is 'red'.

SELECT pname

FROM Parts

WHERE Parts.color = 'red';

ii) Find parts name whose weight less than 10 kg.

SELECT pname

FROM Parts

WHERE Parts.weight < 10;

iii) Find all parts whose weight from 10 to 20 kg.

SELECT pname, weight

FROM Parts

WHERE Parts.weight BETWEEN 10 AND 20;

iv) Find average weight of all parts.

SELECT AVG (weight)

FROM Parts;

v) Find S# of supplier who supply part 'p2'.

SELECT S#

FROM SP

WHERE p# = 'p2';

vi) Find name of supplier who supply maximum parts.

SELECT sname, MAX(quantity)

FROM Supplier, Parts,SP

WHERE Supplier.S# = SP.S#

AND SP.P# = P.P#

GROUP BY sname;

vii) Sort the parts table by pname.

SELECT *

FROM Parts

ORDER BY pname;

Example 1.14.11 Consider following schema and write SQL for given statements.

Student (rollno, name, branch)

exam(rollno, subject_code, obtained_marks, paper_code)

papers(paper_code, paper_satter_name, university)

i) Display name of student who got first class in subject '130703'.

ii) Display name of all student with their total mark.

iii) Display list number of student in each university.

iv) Display list of student who has not given any exam.

Solution:

i) Display name of student who got first class in subject '130703'.

SELECT name

FROM student,exam

WHERE exam.obtained_marks>60 AND exam.subject_code='130703' AND

student.rollno = exam.rollno  

ii) Display name of all student with their total mark.

SELECT name, SUM(obtained_marks)

FROM student,exam

WHERE student.rolino-exam.rollno

iii) Display list number of student in each university.

SELECT COUNT(Rollno)

FROM student, exam, papers

WHERE student.rollno-exam.rollno AND exam.paper_code=papers.paper_code;

iv) Display list of student who has not given any exam.

SELECT name

FROM student NOT IN

(SELECT rollno

FROM student, exam

WHERE sudent.rollno-exam.rollno)

Example 1.14.12 Write down the query for the following table where primary keys are underlined.

Person(ss#, name, address)

Car(license, year, model)

Accident(date, driver, damage-amount)

Owns(ss#, license)

Log(license, date, driver)

1) Find the total number of people whose cars were involved in accidents in 2009.

2) Find the number of accidents in which the cars belonging to "S.Sudarshan"

3) Add a new customer to the database.

4) Add a new accident recorded for the santro belonging to "KORTH"

Solution:

1) Find the total number of people whose cars were involved in accidents in 2009.

SELECT count(ss#)

FROM person,owns, accident,log

WHERE person.ss#=owns.ss# AND

owns.licence=log.licence AND

log.driver accident.driver AND   

accident.date >= '01-jan-2009' and accident.date<='31-dec-09;

2) Find the number of accidents in which the cars belonging to "S.Sudarshan"

SELECT COUNT(accident.date)

FROM person,owns, accident,log                   

WHERE log.date-accident.date AND log.driver accident.driver AND

own.licence-log.licence AND

person.ss#=own.ss# AND

person.name = 's.sudarshan';

3) Add a new customer to the database.

INSERT INTO person (ss#, name, address) values (101, 'Madhav', 'Pune');

INSERT INTO owns (ss#, license) values (101, 'L101');

INSERT INTO car (license, year, model) values ('L101', 2017, 'Honda'); INSERT INTO log (license, date, driver) values (L101', NULL, 'Shankar');

4) Add a new accident recorded for the santro belonging to "KORTH"

INSERT INTO accident (date, driver, damage-amount) VALUES ('31-Dec-2016', 'Shankar', 10000);

INSERT INTO log (license, date, driver) VALUES ('L111','31-Dec-2016','Shankar');

INSERT INTO car (license, year, model) VALUES ('L111', '2005, 'SANTRO'); INSERT INTO person (ss#, name, address) VALUES (111, 'Korth', 'Mumbai');

INSERT INTO owns (ss#, license) VALUES (111, 'L111');

Example 1.14.13 Consider the employee data. Give an expression in SQL for the following query:

Employee(employee_name, street,city)

Works (employee_name,company_name,salary)

Company(company_name,city) Manages(employee_name,manager_name)

1) Find the name of all employees who work for State Bank.

2) Find the names and cities of residence of all employees who work for State Bank.

3) Find all employee in the database who do not work for State Bank.

4) Find all employee in the database who earn more than every employee of UCO bank.

Solution:

1) Find the name of all employees who work for State Bank.

SELECT employee_name

FROM Works

WHERE company_name='State Bank'; 

2)Find the names and cities of residence of all employees who work for State Bank.

SELECT employee_name,city

FROM Employee, Works

WHERE company_name='State Bank' AND

Works.employee_name=Employee.employee_name;

3) Find all employee in the database who do not work for State Bank.

SELECT employee_name

FROM Works

WHERE company_name<>'State Bank';

4) Find all employee in the database who earn more than every employee of UCO bank.

SELECT employee_name from Works

WHERE salary>(SELECT MAX(salary) FROM Works

WHERE company_name='UCO bank');

Example 1.14.14 Consider following schema and write SQL for given statements. Student (Rollno,Name,Age, Sex, City).

Student_marks (Rollno,Sub1,Sub2,Sub3, Total,Average)

Write query to

i) Calculate and store total and average marks from sub1, sub2 and sub3.

ii)Display name of students who got more than 60 marks in subject Sub1.

iii)Display name of students with their total and average marks.

iv) Display name of students who got equal marks in subject sub2.

Solution:

i) Calculate and store total and average marks from sub1, sub2 and sub3.

UPDATE Student_marks

SET Total=sub1+sub2+sub3,

Avarage (sub1+sub2+sub3)/3;

ii) Display name of students who got more than 60 marks in subject Sub1. SELECT Name

FROM Student, Student marks

WHERE Student.Rollno-Student_marks. Rollno AND Student_marks.sub1>60

iii) Display name of students with their total and average marks.

SELECT Name, Total, Average

FROM Student, Student_marks

WHERE Student.Rollno-Student marks.Rollno

iv) Display name of students who got equal marks in subject sub2.

SELECT Name

FROM Student S, Student_marks SM1, Student_marks SM2

WHERE S.Rollno-SM1.Rollno AND SM1.sub2-SM2.sub2;

Example 1.14.15 We have following relations.

EMP (empno, ename, jobtitle, manager no, hiredate, sal, comm, dept no)

DEPT (dept no, dname, loc)

i) The employees who are getting salary greater than 3000 for those persons belongings to the department 20

ii) Employees who are not getting any commission.

iii) Find how many job titles are available in employee table.

iv) Display total salary spent for each job category.

v) Display number of employees working in each department and their department name.

vi) List ename whose manager is NULL.

vii) List all employee names and their salaries, whose salary lies between 1500/- and 3500/- both inclusive.

Solution:

i) The employees who are getting salary greater than 3000 for those persons belongings to the department 20

SELECT ename

FROM EMP

WHERE EMP.sal>3000 AND EMP.dept_no=20

ii) Employees who are not getting any commission.

SELECT ename

FROM EMP

WHERE EMP.comm IS NULL;

iii) Find how many job titles are available in employee table.

SELECT count(jobtitle)

FROM EMP

iv) Display total salary spent for each job category.

SELECT ename, SUM(sal)

FROM EMP    

GROUP BY jobtitle;

v) Display number of employees working in each department and their department name.

SELECT COUNT(EMP.eno), DEPT.dname

FROM EMP, DEPT

WHERE EMP.dept_no=DEPT.dept_no

GROUP BY DEPT.dept_no;

vi) List ename whose manager is NULL.

SELECT ename

FROM EMP

WHERE manager_no IS NULL;

vii) List all employee names and their salaries, whose salary lies between 1500/- and 3500/- both inclusive.

SELECT ename, sal

FROM EMP

WHERE sal>=1500 AND sal<=3500;

Example 1.14.16 We have following relations.

EMP (empno, ename, jobtitle, manager no, hiredate, sal, comm, dept no) DEPT (dept no, dname, loc)

Answer the following queries in SQL

i) Find the employees working in the department 10, 20, 30 only.

ii) Find employees whose names start with letter A or letter a. iii) Find employees along with their department name.

iv) Find employees whose manager is KING.

v) Find the employees who are working in smith's department.

vi) Find the employees who get salary more than Allen's salary.

vii) Display employees who are getting maximum salary in each department. Solution:

i) Find the employees working in the department 10, 20, 30 only.

SELECT empno

FROM EMP

WHERE dept_no BETWEEN 10 AND 30

ii) Find employees whose names start with letter A or letter a.

SELECT ename

FROM EMP

WHERE ename='A%' OR ename='a%';

iii) Find employees along with their department name.

SELECT EMP.ename,DEPT.dname

FROM EMP, DEPT   

WHERE EMP.dept_no=DEPT.dept_no ;

iv) Find employees whose manager is KING.

SELECT ename

FROM EMP

WHERE managerno = (SELECT empno FROM EMP WHERE ename='KING');

v) Find the employees who are working in Smith's department.

SELECT ename

FROM EMP, DEPT

WHERE EMP.dept_no-DEPT.dept_no AND ename='Smith';

vi) Find the employees who get salary more than Allen's salary.

SELECT ename

FROM EMP

WHERE sal> (SELECT sal FROM EMP WHERE ename='Allen');

vii) Display employees who are getting maximum salary in each department.

SELECT ename, MAX(sal)

FROM EMP

GROUP BY dept_no;

Example 1.14.17 Write queries for the following table.

T1 (Empno, Ename, Salary, Designation),

T2 (Empno, Deptno.)

i) Display all rows for salary greater than 5000

ii) Display the deptno for the ename='syham'.

iii) Add a new column deptname in table T2.

iv) Change the designation of ename='ram' from 'clerk' to 'senior clerk'.

v) Find the total salary of all the rows.

vi) Display Empno, Ename, Deptno and Deptname.

vii) Drop the table T1.

Solution:

i) Display all rows for salary greater than 5000 SELECT *

FROM T1

WHERE Salary>5000

ii) Display the deptno for the ename='shyam'. SELECT deptno

FROM T1,T2

WHERE T1.Empno-T2.Empno AND T1.Ename='shyam';

iii) Add a new column deptname in table T2.

ALTER TABLE T2

ADD (deptname VARCHAR(20));

iv) Change the designation of ename='ram' from 'clerk' to 'senior clerk'. UPDATE T1

SET T1.designation='Senior Clerk'

WHERE T1.ename='ram';

v) Find the total salary of all the rows.

SELECT SUM(Salary)

FROM T1;    

vi) Display Empno, Ename, Deptno and Deptname.

SELECT E.Empno, E.Ename, D.Deptno, D.Deptname

FROM T1 E, T2 D   

WHERE E.Empno-D.Empno;

vii) Drop the table T1.

DROP Table T1;

Example 1.14.18 Solve following queries with following table, where underlined attribute is primary key.

primery key.

Person(SS#, name, address)

Car(license, year, model)

Accident(date, driver,damage-amount).

Owns(SS##,license)

Log(licence, date, driver)

i) Find the name of person whose license number is '12345'.

ii) Display name of driver with number of accidents done by that driver.

iii) Add a new accident by 'Ravi' for 'BMW' car on 01/01/2013 for damage amount of 1.5 lakh rupees.

Solution:

i) Find the name of person whose license number is '12345'.

SELECT name

FROM Person, Owns, Car

WHERE Person.SS#=Owns.SS# AND

Car.license-Owns.license AND

Car.license-'12345';

ii) Display name of driver with number of accidents done by that driver.

SELECT driver,COUNT(*)

FROM Accident

GROUP BY driver

iii) Add a new accident by 'Ravi' for 'BMW' car on 01/01/2013 for damage amount of 1.5 lakh rupees.

INSERT INTO Person (SS#, name, address) VALUES (111, 'Ravi', 'Mumbai');

INSERT INTO Car (license, year, model) VALUES ('L111', '2008, 'BMW');

INSERT INTO Owns (SS#, license) VALUES(111, 'L111');

INSERT INTO Accident('01/01/2013', 'Ravi', 150000);

INSERT INTO Log('L111', '01/01/2013','Ravi');

Example 1.14.19 For Supplier - Parts database

Supplier(S#, sname, status, city)

Parts(P#, pname,color,weight,city)

SP(S#, P#, quantity)

Answer the following queries in SQL.

i) Display the name of supplier who lives in 'Ahmedabad'.

ii) Display the parts name which is not supplied yet.

iii) Find all suppliers whose status is either 20 or 30. Solution:

i) Display the name of supplier who lives in 'Ahmedabad'.

SELECT sname

FROM Supplier

WHERE city='Ahmedabad';

ii) Display the parts name which is not supplied yet.

SELECT pname

FROM Parts, SP   

WHRE SP.P# <> Parts.P#

iii) Find all suppliers whose status is either 20 or 30.

SELECT sname

FROM Supplier   

WHERE status = 20 or status = 30;

Example 1.14.20 For Supplier - Parts database

Supplier(S#, sname, status, city)

Parts(P#, pname,color,weight,city)

SP(S#, P#, quantity)

Answer the following queries in SQL.

i) Find the name of parts having 'Red' colour.

ii) Delete parts whose weight is more than 100 gram.

iii) Count how many times each supplier has supplied part 'P2'.

iv) How much times shipment is for more than 100 quantities?

Solution:

i) Find the name of parts having 'Red' colour.

SELECT pname

FROM Parts   

WHERE color='Red';

ii) Delete parts whose weight is more than 100 gram.

DELETE FROM Parts

WHERE weight>100

iii) Count how many times each supplier has supplied part 'P2'.

SELECT S#, COUNT(*)

FROM SP    

WHERE P#='P2'

iv) How much times shipment is for more than 100 quantities?

SELECT S#, COUNT(*)

FROM SP

WHERE quantity>100;

Example 1.14.21 Consider following schema and write SQL for given statements. Student(RollNo, Name, Age, Sex, City)

Student_marks(RollNo, Sub1, Sub2, Sub3, Total, Average)

Write query to

i) Display name and city of students whose total marks are greater than 225.

ii) Display name of students who got more than 60 marks in each subject.

iii) Display name of city from where more than 10 students come from.

iii) Display a unique pair of male and female students.

Solution:

i) Display name and city of students whose total marks are greater than 225. SELECT name, city

FROM student, student_marks

WHERE student.RollNo = student marks. RollNo AND student marks. Total>225

ii) Display name of students who got more than 60 marks in each subject. SELECT name

FROM student, student_marks

WHERE student.RollNo = student marks. RollNo AND

student marks.Sub1>60 OR student marks.Sub2>60 OR student marks.Sub3>60;

iii) Display name of city from where more than 10 students come from. SELECT city

FROM student

WHERE count(RollNo)>10

iv) Display a unique pair of male and female students.

SELECT $1.name            

FROM Student S1, Student $2

WHERE S1.Name=S2.Name AND S1.sex='M' S2.sex-'F'

Example 1.14.22 C Write queries for the following tables:

T1 (Empno, Ename, Salary, Designation)

T2 (Empno, Deptno.)

1) Display all the details of the employee whose salary is lesser than 10 K.

2) Display the Deptno in which employee Seeta is working.

3) Add a new column Deptname in table T2.

4) Change the designation of Geeta from 'Manager' to 'Senior Manager'.

5) Find the total salary of all the employees.

6) Display Empno, Ename, Deptno and Deptname

7) Drop the table T1.

Solution:

1) Display all the details of the employee whose salary is lesser than 10 K. SELECT *

FROM T1    

WHERE Salary<10000

2) Display the Deptno in which employee Seeta is working.

SELECT Deptno

FROM T2, T1

WHERE T1.Empno-T2.Empno AND T1.Ename='Seeta';

3) Add a new column Deptname in table T2.

ALTER TABLE T2

ADD (Deptname VARCHAR(20));

4) Change the designation of Geeta from 'Manager' to 'Senior Manager'.

UPDATE T1

SET Designation 'Senior Manager'

WHERE Ename='Geeta';

5) Find the total salary of all the employees.

SELECT SUM(Salary)

FROM T1

6)Display Empno, Ename, Deptno and Deptname

SELECT T1.Empno,T1.Ename, T2.Deptno, T2.Deptname

 FROM T1, T2

7) Drop the table T1.

DROP TABLE T1

Example 1.14.23 We have following relations:

EMP(empno, ename, jobtitle, managerno, hiredate, sal, comm, deptno) DEPT(deptno, dname, loc)

Answer the following queries in SQL.

i) Find the Employees working in the department 10, 20, 30 only.

ii) Find Employees whose names start with letter A or letter a.

iii) Find Employees along with their department name.

iv) Insert data in EMP table.

v) Find the Employees who are working in Smith's department

vi) Update Department name of Department No = 10

vii) Display employees who are getting maximum salary in each department Solution:

i) Find the Employees working in the department 10, 20, 30 only.

SELECT ename

FROM EMP    

WHERE deptno BETWEEN 10 AND 30

ii) Find Employees whose names start with letter A or letter a.

SELECT ename

FROM EMP

WHERE ename='A%' OR ename='a%';

iii) Find Employees along with their department name.

SELECT EMP.ename,DEPT.dname

FROM EMP, DEPT    

WHERE EMP.deptno-DEPT.deptno;

iv) Insert data in EMP table.

INSERT INTO EMP( empno, ename, jobtitle, managerno, hiredate, sal, comm, deptno)

VALUES ('E111', 'AAA', 'Manager', M123,01-01-2010,20000,2000, 'D111');

v) Find the Employees who are working in Smith's department

SELECT ename

FROM EMP,DEPT

WHERE EMP.deptno=DEPT.deptno AND ename='Smith';

vi) Update Department name of Department No = 10  

UPDATE DEPT

SET dname='Accounts'

WHERE deptno=10;

vii) Display employees who are getting maximum salary in each department

SELECT ename, MAX(sal)

FROM EMP

GROUP BY deptno;

Example 1.14.24 Consider following Hotel database, primary keys are underlined:

hotel(hotel_no,name,type,price)

room(room-no,hotel-no,type,price)

booking(hotel-no,guest-no,date-from,date-to,room-no)

guest(guest-no,name,address)

Give an expression in SQL for each of the following queries

(1) List the names and addresses of all guests in London, alphabetically ordered by name.

(2) List out hotel name and total number of rooms available

(3) List the details of all the rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied.

(4) List all guests currently staying a the Grosvenor Hotel.

(5) List the rooms that are currently unoccupied at the Grosvenor Hotel.

(6) List the number of rooms in each hotel in London.

(7) List out all guests who have booked room for three or more days.

Solution:

(1) List the names and addresses of all guests in London, alphabetically ordered by name.

SELECT name, address

FROM guest

WHERE address LIKE '%London%'

ORDER BY name;                  

(2) List out hotel name and total number of rooms available

SELECT name, COUNT(room-no)

FROM hotel, room

WHERE hotel.hotel_no= room.hotel_no

GROUP by hotel no;

(3) List the details of all the rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied.

SELECT r.* FROM Room r LEFT JOIN

(SELECT g.guestName, h.hotelNo, b.roomNo

FROM guest g, booking b, hotel h

WHERE g.guest_no = b.guest_no AND

b.hotel_no = h.hotel_no AND

h.name= 'Grosvenor Hotel' AND  

date_from <= CURRENT_DATE AND

date_to >= CURRENT DATE)

AS XXX ON r.hotel_no = XXX.hotel_no AND r.room_no = XXX.room_no;

(4) List all guests currently staying at the Grosvenor Hotel.

SELECT FROM guest

WHERE guest_no =

(SELECT guest_no FROM booking

WHERE

date-from <= CURRENT DATE AND date-to >= CURRENT DATE AND

hotel_no =    

(SELECT hotel_no FROM hotel

WHERE name = 'Grosvenor Hotel'));

(5) List the rooms that are currently unoccupied at the Grosvenor Hotel.

SELECT (r.hotel_no, r.room_no, r.type, r.price)

FROM room r, hotel h

WHERE r.hotel_no = h.hotel no AND

h.name 'Grosvenor Hotel' AND

NOT EXIST

(SELECT *    

FROM booking b, hotel h

WHERE (date from <= 'CURRENT DATE'

AND date_to >= 'CURRENT DATE')

AND r.hotel_no=b.hotel_no

AND r.room_no=b.room_no

AND r.hotel_no=h.hotel_no

AND name = 'Grosvenor Hotel');

(6) List the number of rooms in each hotel in London.

SELECT hotel_no, COUNT(room_no) AS count

FROM room r, hotel h

WHERE r.hotel_no = h.hotel_no AND city = 'London'

GROUP BY hotel_no;

(7) List out all guests who have booked room for three or more days.

SELECT guest-no, name

FROM guest g,booking b

WHERE b.date-to Minus b.date-from >=3

Example 1.14.25 Consider following schema and write SQL for given statements

employee(employee-name,street,city)

works(employee-name, company-name, salary)

company(company-name, city)

manages(employee-name,manager-name)

(1) Find the names of all employees who work for First Bank Corporation.

(2) Give all employees of First Bank Corporation a 10-percent raise.

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

(4) Find the names and Street addresses, cities of residence of all employees who work for First Bank Corporation and earn more than $10,000

(5) Find all employees in the database who live in the same cities as the companies for which they work.

(6) Find all employees in the database who do not work for First Bank Corporation.

(7) Find the company and number of employees in company that has more than 30 employees.

Solution:

(1) Find the names of all employees who work for First Bank Corporation.

SELECT employee_name

FROM works   

WHERE company_name='First Bank Corporation';

(2) Give all employees of First Bank Corporation a 10-percent raise.

UPDATE works

SET salary salary*1.1

WHERE company_name='First Bank Corporation';

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

SELECT employee_name,city

FROM employee

WHERE employee_name IN

(SELECT employee_name

FROM works

WHERE company-name='First Bank Corporation');

(4) Find the names and Street addresses, cities of residence of all employees who work for First Bank Corporation and earn more than $10,000

SELECT employee_name,city

FROM employee

WHERE employee_name IN

(SELECT employee_name

FROM works

WHERE company-name='First Bank Corporation' AND salary>10000);

OR

SELECT E.employee_name, E.street, E.city

FROM employee as E, works as W

WHERE

E.employee_name=W.employee_name AND

W.company_name='First Bank Corporation' AND W.salary>10000

(5) Find all employees in the database who live in the same cities as the companies for which they work.

SELECT E.employee_name

FROM employee as E, works as W, company as C

where E.employee_name=W.employee_name AND E.city=C.city AND

W.company_name=C.company_name;

(6) Find all employees in the database who do not work for First Bank Corporation.

SELECT employee_name

FROM works

WHERE company_name <>'First Bank Corporation';

(7) Find the company and number of employees in company that has more than 30 employees

SELECT company-name,COUNT(employee-name)

FROM employee E, company C,works W

WHERE E.employee-name=W.employee-name AND

W.company-name=C.company-name

HAVING COUNT(employee-name)>30;

Example 1.14.26 Consider the following Schema: Suppliers(sid:integer, sname:string, address: string) Parts(pid:integer, pname:string, color: string) Catalog(sid:integer, pid: integer, cost: real)

Write the following queries in SQL

1) Find the names of suppliers who supply some red part.

2) Find the sids of suppliers who supply some red part and some green part.

3) Find the sids of suppliers of supply every red part.

4) Find the pids of parts supplied by at least two different suppliers.

Solution:

1) SELECT S.sname

FROM Suppliers S, Parts P, Catalog C

WHERE P.color = 'red' AND C.pid = P.pid AND C.sid = S.sid

2) SELECT C.sid

FROM Catalog C, Parts P

WHERE (P.color = 'red' OR P.color = 'green') AND P.pid = C.pid

3) SELECT C.sid.

FROM catalog C

WHERE NOT EXISTS(SELECT P.pid

FROM Parts P

WHERE P.color='red'

AND (NOT EXISTS (SELECT C1.sid

FROM Catalog C1

WHERE C1.sid = C.sid AND C1.pid = P.pid)))

4) SELECT C.pids

FROM Catalog C

WHERE EXISTS(SELECT C1.sid

FROM Catalog C1

WHERE C1.pid = C.pid AND C1.sid + C.sid)