Q: Consider the following tables ITEM and CUSTOMER.

Write SQL commands

for the statements (1) to (4) and give output for (5) to (6).

Table:Item

item_ID ItemName Manufacturer Price

PC01 Personal ABC 35000

Computer

LC05 Laptop ABC 55000

PC03 Personal XYZ 32000

Computer

PC06 Personal COMP 37000

Computer

LC03 Laptop PQR 57000

Table:Customer

Cs_ID Costumer City Item_ID

Name

01 N Roy Delhi LC03

06 H Singh Mumbai PC03

12 R Pandey Delhi PC06

15 C Sharma Delhi LC03

16 K Agrawal Banglore PC01

(1) To display the details of those customers whose city is Delhi.

 (2) To display the details of Item whose price is in the range of 35000
55000(both values included).
(3) To display the CostumerName, City from the table Customer, and
price from the table ITEM, with their corresponding matching item_ID.
(4) To increase the price of all items by 1000 in table ITEM.
(5) SELECT DISTINCT City FROM Customer;
(6) SELECT ItemName, Max(Price), Count(*) FROM Item GROUP BY ItemName;

SOLUTIONS:

(1) SELECT * FROM Customer WHERE CITY = ‘Delhi’;

(2) SELECT * DROM Item WHERE Price>=35000 AND Price<=55000;
(3) SELECT CustomerName , City, ItemName, Price FROM Customer A
INNER JOIN Item B WHERE A.Item_ID=B.Item_ID;
(4) UPDATE Item SET Price=Price+1000;
(5) City
Delhi
Mumbai
Banglore
Name Max(Price) Count(*)

Laptop 58000 2

Personal 38000 3
Computer

****************************************END*************************************
**********************************************************************************

Q.Write a SQL queries for (1) to (4) and find outputs for SQL queries (5) to (8),

which are based on the tables.

TABLE : VEHICLE

CODE VTYPE PERKM

101 VOLVO BUS 160
102 AC DELUXE BUS 150

103 ORIDANARY BUS 90

105 SUV 40
104 CAR 20

PERKM is Freight Charges per kilometre

VTYPE is Vehicle Type

TABLE : TRAVEL

NO NAME TDATE KM CODE NOP

101 Janish Kin 2015-11-13 200 101 32
103 Vedika Sahai 2016-04-21 100 103 45
105 Tarun Ram 2016-03-23 350 102 42
102 John Fen 2016-02-13 90 102 40
107 Ahmed Khan 2015-01-10 75 104 2
104 Raveena 2016-05-28 80 105 4
106 Kripal Anaya 2016-02-06 200 101 25
NO is Traveller Number

KM is Kilometer Travelled

NOP is Number of Travellers Travelled in Vehicle

TDATE is Travel Date

(1) Todisplay NO,NAME TDATE from the table TRAVEL in

descending order of NO.

(2) To display the NAME of all the travellers from the table TRAVEL who are
travelling by vehicle with code 101 or 102.

(3) To display the NO and NAME of those travellers form the table TRAVEL
who travelled between ‘2015-12-31’ and ‘2015-04-01’.

(4) To display all the details from table TRAVEL for the travellers, who have
travelled distance more than 100KM in ascending order of NOP.

(5) SELECT COUNT(*), CODE FROM TRAVEL

GROUP BY CODE HAVING COUNT(*)>1;

(6) SELECT DISTINCT CODE FROM TRAVEL;

(7) SELECT A.CODE, NAME, VTYPE

FROM TRAVEL A, VEHICLE B

WHERE A.CODE = B.CODE AND KM < 90;

(8) SELECT NAME, KM * PERKM

 FROM TRAVEL A, VEHICLE B

WHERE A.CODE = B.CODE AND A.CODE = ‘105’;

SOLUTION

(1)SELECT NO, NAME, TDATE

FROM TRAVEL

ORDER BY NO DESC;

(2)SELECT NAME

FROM TRAVEL

WHERE CODE IN(101,102);

(3)SELECT NO, NAME

FROM TRAVEL

WHERE DATE>=’2015-04-01’ AND TDATE <=’2015-12-31’;

(4)SELECT*

FROM TRAVEL

WHERE KM > 100

ORDER BY NOP;

(5)2 101

2 102
(6)101

103

102

104

105

(7)104 Ahmed Khan CAR

105 Raveena SUV

(8)Raveena 3200

****************************************END*************************************
**********************************************************************************

Q. Given the following student relation. Write SQL commands for (1) to (5) and
write output for (6).

No. Name Age Department Dateofadm Fee Sex

1. Pankaj 24 Computer 10/01/97 120 M
2. Shalini 21 History 24/03/98 200 F
3. Sanjay 22 Hindi 12/12/96 300 M
4. Sudha 25 History 01/07/99 400 F
5. Rakesh 22 Hindi 05/09/97 250 M
6. Shakeel 30 History 27/06/98 300 M
7. Surya 34 Computer 25/02/97 210 M
8. Shikha 23 Hindi 31/07/97 200 F

(1)To show all information about the students of History department.

(2)To list the names of female students who are in Hindi department

(3)To list the names of all students with their date of admission in ascending
order.

(4)To display student’s Name, Fee, Age, for male Students only.

(5)To count the number of student with Age<23.

(6)To inset a new row in the STUDENT table with the following data:

9,”Zaheer”,36,”Computer”,{12/03/97},230,”M”

(7)Give the output of following SQL statements:

(7.1)Select COUNT (distinct department) from STUDENT;

(7.2)Select MAX (Age) from STUDENT where Sex=”F”;

(7.3)Select AVG (Fee) from STUDENT where Dataeofadm<{01/01/98};

(7.4)Select SUM (Fee) from STUDENT where Dateofadm<{01/01/98};

SOLUTION:

(1)SELECT*

FROM Student

WHERE Department=”History”;

(2)SELECT Name

FROM Student

WHERE sex=”F” AND Department=”Hindi”

(3)SELECT Name

FROM Student

ORDER BY Dateofadm;

(4)SELECT Name, Fee, Age

FROM Student

WHERE sex=”M”;

(5)SELECT COUNT(*)

FROM Student

WHERE Age<23;

(6)INSERT INTO Student

VALUES(9,”Zaheer”,”Computer”,{12/03/97},230,”M”);

(7)

(7.1)3

(7.2)25

(7.3)236

(7.4)1080

****************************************END*************************************
**********************************************************************************

Q. Study the following tables DOCTOR and SALARY .Write SQL commands for
the statements (1) to (4) and give output for the SQL queries(5) to (6).

TABLE:DOCTOR

ID NAME DEPT SEX EXPERIENCE

101 John ENT M 12
104 Smith ORTHOPEDIC M 5
107 George CARDIOLOGY M 10
114 Lara SKIN F 3
109 K. George MEDICINE F 9
105 Johnson ORTHOPEDIC M 10
117 Lucy ENT F 3
111 Bill MEDICINE F 12
130 Morphy ORTHOPEDIC M 15

TABLE:SALARY

ID BASIC ALLOWANCE CONSULTATION

101 12000 1000 300
104 23000 2300 500
107 32000 4000 500
114 12000 5200 100
109 42000 1700 200
105 18900 1690 300
130 21700 2600 300

(1)Display NAME of all Doctors who are in “MEDICINE” having more than 10
years experience from the table DOCTOR.

(2)Display the average salary of all doctors working in “ENT” department using
table DOCTOR and SALARY.Salary=BASIC+ALLOWANCE.

(3)Display the minimum ALLOWANCE of female doctors.

(4)Display the highest consultation fee among all male doctors.

(5)SELECT count(*) from DOCTOR where Sex=”F”.

(6)SELECT NAME,DEPT,BASIC from DOCTOR,SALARY WHERE DEPT=”ENT” AND

DOCTOR.ID=SALARY.ID.
SOLUTION.

(1)SELECT Name

FROM Doctor

WHERE Dept=’Medicine’ AND Experience>10;

(2)SELECT AVG(Basic+Allowance)

FROM Doctor A ,Salary B

WHERE Dept=’ENT’ AND A.ID=B.ID;

(3)SELECT MIN(Allowance)

FROM Doctor A AND Salary B

WHERE Sex=’F’ AND A.ID=B.ID;

(4)SELECT MAX(CONST)

FROM Doctor A, Salary B

WHERE Sex=’M’ AND A.ID=B.ID;

(5)count(*)

(6) NAME DEPT BASIC

John ENT 12000

****************************************END*************************************

**********************************************************************************
Q.Given the following tables for a database LIBRARY . Write SQL queries for (1)
to (6).

Table:BOOKS

Book_Id Book_Name Author_Name Publishers Price Type Quantity

C0001 Fast Cook Lata Kapoor EPB 355 Cookery 5
F0001 The Tear William First Publ. 650 Fiction 20
Hopkins
T0001 My First C++ Brian&Brooke EPB 350 Text 15
T0002 C++ A.W. TDH 350 Text 10
Brainworks Rossaine
F0002 Thunderbolts Anna Roberts First Publ. 750 Fiction 50

Table:ISSUED

BOOK_ID QUANTITY_ISSUED
T0001 4
C0001 5
F0001 2

(1)To show book name ,author name and price of books of first
publ.publishers.

(2)To list the name from books of text type.

(3)To display the names and price from books in ascending order of their
price.

(4)To increase the price of all books of EPB publishers by 50.

(5)To display the Book_Id,Book_Name and Quantity_Issued for all books which
have been issued.

(6)To insert a new row in the table Issued having the following data:

"F0003",1.

Solutions:

(1)SELECT Book_Name,Author_Name,Price
 FROM Books

WHERE Publishers = "First publ.";

(2)SELECT Book_Name

FROM Books

WHERE Type = "Text";

(3)SELECT Book_Name,Price

FROM Books

ORDER BY Price;

(4)UPDATE Books

SET Price = Price+50

WHERE Publishers = "EPB";

(5)SELECT Books.Book_Id,Book_Name,Quantity_Issued

FROM Books,Issued

WHERE Books.Book_Id = Issued.Book_Id;

(6)INSERT INTO Issued

VALUES("F0003",1);

****************************************END*************************************