DBMS concepts and SQL

Relational Database
A relational database is a collection of data items organized as logically related tables.

Relational Database Management System

The software required to handle/manipulate these table/relations is know as Relational
Database Management System (RDBMS). Example - Oracle , Sybase, DB2, MSSQL, etc.

Table/Relation
A group of rows and columns from a table. The horizontal subset of the Table is known as
a Row/Tuple. The vertical subset of the Table is known as a Column/an Attribute.
A relation in a database has the following characteristics:

 Every value in a relation is a atomic-i.e. it can not be further divided

 Names of columns are distinct and order of columns is immaterial
 The rows in the relation are not ordered
Degree
No.of columns of Table.

Cardinality
No. of Rows of Table

Key
An Attribute/group of attributes in a table that identifies a tuple uniquely is known as a
key. A table may have more than one such attribute/group of identifies that identifies a
tuple uniquely, all such attributes(s) are known as Candidate Keys. Out of Candidate
keys, one is selected as Primary key, and others become Alternate Keys.
A Foreign Key is defined in a second table, but it refers to the primary key in the first
table.
Relational algebra
Relational algebra is a formal system for manipulating relations. Set of operations that can be
carried out on a relation:
 Selection : To select a horizontal subset of a relation
 Projection : To select vertical subset of a relation
 Cartesian Product: It operates on two relations and is denoted by X. for example
Cartesian product of two relation R1 and R2 is represented by R=R1X R2. The degree
of R is equal to sum of degrees of R1 and R2. The cardinality of R is product of
cardinality of R1 and cardinality of R2
Example cartesian Product

The table R1
Empno Ename Dept

1 Bill A

2 Sarah C

3 John A
The table R2
Dno Dname

A Marketing

B Sales

C Legal

R1 X R2
Empno Ename Dept Dno Dname
 1 Bill A A Marketing

1 Bill A B Sales

1 Bill A C Legal

2 Sarah C A Marketing

2 Sarah C B Sales

2 Sarah C C Legal

3 John A A Marketing

3 John A B Sales

3 John A C Legal

SQL- Structured Query Language

SQL commands classified by function:

Data definition language (DDL) - used to define or change database structure(s)

(e.g., CREATE, ALTER, DROP)
Data manipulation language (DML) - used to select or change data (e.g., INSERT,
UPDATE, DELETE, SELECT)
Data control language (DCL) - used to control user access (e.g., GRANT, REVOKE)
Transactional language - used to control logical units of work (e.g., COMMIT)

Creating a new table in the database

Syntax :

CREATE TABLE table_name

(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
Example :
CREATE TABLE student
(
rno int,
name char(25),
fees int,
dob date,
class char(3)
);

Inserting a new row at the bottom of the table

Syntax :

INSERT INTO table_name

VALUES (value1,value2,value3,...);
You can also specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1,column2,column3,...)

VALUES (value1,value2,value3,...);
Examples
INSERT INTO student
VALUES(10, 'Alex', 7800, '1998-10-03','K12');
INSERT INTO student(rno, name, fees, dob, class)
values(11, 'Peter', 6700, '1997-11-15', 'K12');

Displaying the content from a table – SELECT

Example :
SELECT * FROM student;
rno name fees dob class
10 Alex 7800 1998-10-03 K12
11 Peter 6700 1997-11-15 K12

12 Alisha 7800 1999-07-03 K11

13 John 6900 2000-12-13 K11
SELECT name FROM student;
name
Alex
Peter

Alisha
John
Relational Operator
=, <, >, <=, >=, <>

Logical Operator
AND, OR, NOT
SELECT * FROM student WHERE fees < 7000;
rno name fees dob class
11 Peter 6700 1997-11-15 K12
13 John 6900 2000-12-13 K11

SELECT * FROM student WHERE fess > 7000 AND fees < 8000;
rno name fees dob class
10 Alex 7800 1998-10-03 K12
 12 Alisha 7800 1999-07-03 K11

SELECT * FROM student WHERE fees > 7000 OR class = 'K12';

rno name fees dob class
10 Alex 7800 1998-10-03 K12
11 Peter 6700 1997-11-15 K12
12 Alisha 7800 1999-07-03 K11

SELECT name, fees FROM student WHERE NOT (class = 'K12');

name fees

Alisha 7800
John 6900

SELECT name, fees FROM student WHERE class <> 'K12';

name fees
Alisha 7800
John 6900

SELECT * FROM student WHERE rno IN(10, 12, 13);

rno name fees dob class
10 Alex 7800 1998-10-03 K12
12 Alisha 7800 1999-07-03 K11
13 John 6900 2000-12-13 K11

SELECT * FROM student WHERE rno BETWEEN 11 AND 13;

rno name fees dob class

11 Peter 6700 1997-11-15 K12

12 Alisha 7800 1999-07-03 K11
13 John 6900 2000-12-13 K11

SELECT name FROM student WHERE name LIKE 'A%';

name
Alex
Alisha

SELECT * name FROM student WHERE name LIKE '%a';

rno name fees dob class
12 Alisha 7800 1999-07-03 K11

SELECT name FROM student WHERE Name LIKE '%e%' ;

 name
Alex
Peter

Modifying the existing content of the table

Syntax:

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
Example
UPDATE student
SET fees = '7900'
WHERE rno = 12 ;
SELECT * FROM student;
rno name fees dob class
10 Alex 7800 1998-10-03 K12

11 Peter 6700 1997-11-15 K12

12 Alisha 7900 1999-07-03 K11

13 John 6900 2000-12-13 K11

Arranging the data in ascending or descending order of
one/multiple columns (ORDER BY clause)
Syntax:

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;
Example
SELECT * FROM student ORDER BY name;
rno name fees dob class
10 Alex 7800 1998-10-03 K12
12 Alisha 7900 1999-07-03 K11
13 John 6900 2000-12-13 K11
11 Peter 6700 1997-11-15 K12

SELECT * FROM student ORDER BY fees DESC;

rno name fees dob class
12 Alisha 7900 1999-07-03 K11
10 Alex 7800 1998-10-03 K12

13 John 6900 2000-12-13 K11

 11 Peter 6700 1997-11-15 K12

SELECT class, name, dob, fees FROM student ORDER BY class, name DESC;
class name dob fees
K11 John 2000-12-13 6900
K11 Alisha 1999-07-03 7900
K12 Peter 1997-11-15 6700

K12 Alex 1998-10-03 7800

SELECT class, name, fees, fees*12 annualfees FROM student;

class name fees annualfees
K12 Alex 7800 93600
K12 Peter 6700 80400

K11 Alisha 7900 94800

K11 John 6900 82800
Using Aggregate Functions with SELECT
COUNT( ) To count the number of rows
SUM( ) To find the sum of values in the column
MAX( ) To find the maximum value in the column
MIN( ) To find the minimum value in the column
AVG( ) To find the average of values in the column
SELECT COUNT(*) FROM student;
COUNT(*)
4

SELECT COUNT(rno) FROM student;

COUNT(rno)

SELECT SUM(fees) FROM student;

SUM(fees)
29300

SELECT AVG(fees) FROM student;

AVG(fees)
7325.0000

SELECT MAX(fees), MIN(fees) FROM student;

MAX(fees) MIN(fees)
 7900 6700
Grouping data under given Column- (GROUP BY)
SELECT class, SUM(fees) FROM student GROUP BY class;
class SUM(fees)
K11 14800
K12 14500
SELECT class, MAX(fees), MIN(fees) FROM student GROUP BY class;
class MAX(fees) MIN(fees)
K11 7900 6900

K12 7800 6700

SELECT class, MAX(dob) FROM student GROUP BY class HAVING COUNT(*)>1;

class MAX(dob)
K11 2000-12-13
K12 1998-10-03

Deleting a row/rows from a table

Syntax:
DELETE FROM table_name
WHERE some_column=some_value;
Example:
DELETE FROM Student WHERE rno = 13;

Adding a new column(s) in the table

Syntax :

ALTER TABLE table_name

ADD column_name datatype
Examples :
ALTER TABLE student ADD (grade CHAR(2));

Modifying the data type of a column

Syntax;

ALTER TABLE table_name

MODIFY column_name datatype
Example:
ALTER TABLE student MODIFY (grade CHAR(1));

Deleting a table
Syntax:
DROP TABLE table_name

Example:
DROP TABLE student;

Working with more than one table

Syntax:

SELECT col1, col2, col3...

FROM table_name1, table_name2
WHERE table_name1.col2 = table_name2.col1;
Table - product

product_id product_name supplier_name unit_price

100 Camera Nikon 300

101 Television Onida 100

102 Refrigerator Videocon 150

103 Ipod Apple 75

104 Mobile Nokia 50

Table - order_items

order_id product_id total_units customer

5100 104 30 Infosys

5101 102 5 Satyam
5102 103 25 Wipro

5103 101 10 TCS

SELECT order_id, product_name, unit_price, supplier_name, total_units
FROM product, order_items
WHERE order_items.product_id = product.product_id;
Board Question

Consider the following tables Product and Client. Write SQL commands for the statement (i) to (iv) and give outputs for SQL
queries (v) to (viii)

Table: PRODUCT

P_ID Product Name Manufacturer Price

TP01 TalcomPowder LAK 40

FW05 Face Wash ABC 45

BS01 Bath Soap ABC 55

SH06 Shampoo XYZ 120

FW12 Face Wash XYZ 95

Table: CLIENT

C_ID Client Name City P_ID

01 TalcomPowder Delhi FW05

06 Face Wash Mumbai BS01

12 Bath Soap Delhi SH06

15 Shampoo Delhi FW12

16 Face Wash Banglore TP01

(i) To display the details of those Clients whose city is Delhi.

Ans: Select all from Client where City=”Delhi”

(ii) To display the details of Products whose Price is in the range of 50 to 100(Both values included).

Ans: Select all from product where Price between 50 and 100

(iii) To display the ClientName, City from table Client, and ProductName and Price from table Product, with their corresponding
matching P_ID.

Ans: Select ClientName,City,ProductName, Price from Product,Client where Product.P_ID=Client.P_ID.

(iv) To increase the Price of all Products by 10

Ans: Update Product Set Price=Price +10

(v) SELECT DISTINCT Address FROM Client.

Ans: ( The above question may consist DISTINCT City. If it is DISTINCT City, the following is the answer)

City
-----
Delhi
Mumbai
Bangalore

(vi) SELECT Manufacturer, MAX(Price), Min(Price), Count(*) FROM Product GROUP BY Manufacturer;

Ans:

Manufacturer Max(Price) Min(Price) Count(*)

LAK 40 40 1
ABC 55 45 2
XYZ 120 95 2

(vii) SELECT ClientName, ManufacturerName FROM Product, Client WHERE Client.Prod_Id=Product.P_Id;

Ans:

ClientName ManufacturerName
Cosmetic Shop ABC
Total Health ABC
Live Life XYZ
Pretty Woman XYZ
Dreams LAK

(viii) SELECT ProductName, Price * 4 FROM Product.

ProductName Price*4
Talcom Poweder 160
Face Wash 180
Bath Soap 220
Shampoo 480
Face Wash 380

Outside Delhi 2008:

5.b) Consider the following tables Item and Customer. Write SQL commands for the statement (i) to (iv) and give outputs for SQL
queries (v) to (viii)

Table: ITEM

C_ID ItemName Manufacturer Price

PC01 Personal Computer ABC 35000

LC05 Laptop ABC 55000

PC03 Personal Computer XYZ 32000

PC06 Personal Computer COMP 37000

LC03 Laptop PQR 57000

Table: CUSTOMER

C_ID CustomerName City P_ID

 01 N.Roy Delhi LC03

06 H.Singh Mumbai PC03

12 R.Pandey Delhi PC06

15 C.Sharma Delhi LC03

16 K.Agarwalh Banglore PC01

(i) To display the details of those Customers whose city is Delhi.Ans: Select all from Customer Where City=”Delhi”

(ii) To display the details of Item whose Price is in the range of 35000 to 55000 (Both values included).

Ans: Select all from Item Where Price>=35000 and Price <=55000

(iii) To display the CustomerName, City from table Customer, and ItemName and Price from table Item, with their corresponding
matching I_ID.

Ans: Select CustomerName,City,ItemName, Price from Item,Customer where Item.I_ID=Customer.I_ID.

(iv) To increase the Price of all Items by 1000 in the table Item.

Ans: Update Item set Price=Price+1000

(v) SELECT DISTINCT City FROM Customer.

Ans: City
Delhi
Mumbai
Bangalore

(vi) SELECT ItemName, MAX(Price), Count(*) FROM Item GROUP BY ItemName;

Ans:

ItemName Max(Price) Count(*)

Personal Computer 37000 3
Laptop 57000 2

(vii) SELECT CustomerName, Manufacturer FROM Item, Customer WHERE Item.Item_Id=Customer.Item_Id;

Ans:

CustomerName Manufacturer Name

N.Roy PQR
H.Singh XYZ
R.Pandey COMP
C.Sharma PQR
K.Agarwal ABC

(viii) SELECT ItemName, Price * 100 FROM Item WHERE Manufacturer = „ABC‟;

Ans:
ItemName Price*100
Personal Computer 3500000
Laptop 5500000

Outside Delhi 2007:

5.b) Consider the following tables Consignor and Consignee. Write SQL command for the statements(i)to(iv) And give outputs for
the SQL quries (v) to ( viii). 6

TABLE : CONSIGNOR

CnorID CnorName CnorAddress City

ND01 R singhal 24,ABC Enclave New Delhi

ND02 AmitKumar 123,Palm Avenue New Delhi

MU15 R Kohil 5/A,South,Street Mumbai

MU50 S Kaur 7-K,Westend Mumbai

TABLE : CONSIGNEE

CneeID CnorID CneeName CneeAddress CneeCity

MU05 ND01 RahulKishore 5,Park Avenue Mumbai

ND08 ND02 P Dhingr a 16/j,Moore Enclave New Delhi

KO19 MU15 A P Roy 2A,Central/ avenue Kolkata

MU32 ND0 2 S mittal P 245, AB Colony Mumbai

ND48 MU5 0 B P jain 13,Block d,a,viha New Delhi

(i) To display the names of all consignors from Mumbai.

Ans: Select CnorName from Consignor where city=”Mumbai”;

(ii) To display the cneeID, cnorName, cnorAddress, CneeName, CneeAddress for every Consignee.

Ans: Select CneeId, CnorName, CnorAddress, CneeName, CneeAddress from Consignor,Consignee where
Consignor.CnorId=Consignee.CnorId;

(iii) To display the consignee details in ascending order of CneeName.

Ans: Select * from Consignee Orderby CneeName Asc;

(iv) To display number of consignors from each city.

Ans: Select city, count(*) from Consignors group by city;

(v) SELECT DISTINCT City FROM CONSIGNEE;

Ans:

CneeCity
Mumbai

New Delhi

Kolkata

(vi) SELECT A.CnorName A, B.CneeName B FROM Consignor A, Consignee B WHERE A.CnorID=B.CnorID AND
B.CneeCity=‟Mumbai‟;

(vii) SELECT CneeName,CneeAddress FROM Consignee WHERE CneeCity Not IN („Mumbai‟, „Kolkata‟);

Ans:

(viii) SELECT CneeID, CneeName FROM Consignee WHERE CnorID = „MU15‟ OR CnorID = „ND01‟;

senderCity

New Delhi

Mumbai

Ans: CneeID
CneeName
MU05
Rahul Kishore
KO19 A P Roy

Delhi 2007:

5.b) Consider the following tables. Write SQL command for the statements (i)to(iv)and give outputs for the SQL quries (v) to (viii). 6

TABLE : SENDER

SenderID SenderName Sender Address Sender City

ND01 R jain 2,ABC Appts New Delhi

MU02 H sinha 12, Newton Mumbai

MU1 5 S haj 27/ A,Park Street New Delhi

ND5 0 T Prasad 122-K,SDA Mumbai

TABLE :RECIPIENT

RecID SenderID ReCName RecAddress ReCCity

KO05 ND01 RBajpayee 5,Central Avenue Kolkata

ND08 MU0 2 S Mahajan 116, A Vihar NewDelhi

MU19 ND01 H sing 2A,Andheri East Mumbai

MU32 MU1 5 PK Swamy B5, CS erminus Mumbai

ND48 ND50 S Tripathi 13, B1 D,Mayur Vihar NewDelhi

(i) To display the names of all senders from Mumbai.

Ans: Select * from Sender where SenderCity =‟Mumbai‟;

(ii) To display the recID, senderName, senderAddress, RecName, RecAddress for every recipt.

Ans: Select recID, SenderName, SenderAddress, RecName, RecAddress from Sender, Recipient where
Sender.Senderid=Recipient.RenderId;

(iii) To display the sender details in ascending order of SenderName.

Ans: Select * from Sender order by SenderName;

(iv) To display number of Recipients from each city.

Ans: Select RecCity,Count(*) from Recipient group by RecCity;

CnorName

R singhal

Amit Kumar

CneeName

Rahul Kishore

S mittal

CneeName CneeAddress

P Dhingra 16/j,Moore Enclave

B P jain 13,Block d,a,viha

(v) SELECT DISTINCT SenderCity FROM Sender;

Ans:

(vi) SELECT A.SenderName A, B.RecName FROM Sender A, Recipient B WHERE A.SenderID=B. SenderID AND
B.RecCity=‟Mumbai‟;

Ans: SenderName RecName

R.Jain H.Singh
S.Jha P.K.Swamy

(vii) SELECT RecName,RecAddress FROMRecipient WHERE RecCity Not IN („Mumbai‟,Kolkata‟);

Ans: RecName RecAddressS
Mahajan 116, A Vihar
S Tripati 13, B1 D, Mayur Vihar

(viii) SELECT RecID, RecName FROM Recipient WHERE SenderID = „MU02‟ OR SenderID = „ND50‟;

Ans: RecID RecName

ND08 S Mahajan
ND48 S Tripathi

OUTSIDE DELHI 2006:

5.b) Study the following tables FLIGHTS and FARES and write SQL commands for the questions (i) to (iv) and give outputs for
SQL quires (v) to(vi).

TABLE: FLIGHTS

NO_
FL_NO STARTING ENDING NO_ STOPS
FLGHTS

IC301 MUMBAI DELHI 8 0

IC799 BANGALORE DELHI 2 1

MC101 INDORE MUMBAI 3 0

IC302 DELHI MUMBAI 8 0

AM812 KANPUR BANGLORE 3 1

IC899 MUMBAI KOCHI 1 4

AM501 DELHI TRIVENDRUM 1 5

MU499 MUMBAI MADRAS 3 3

IC701 DELHI AHMEDABAD 4 0

TABLE:FLIGHTS

FL_NO AIRLINES FARE TAX%

IC701 INDIAN AIRLINES 6500 10

MU499 SAHARA 9400 5

AM501 JET AIRWAYS 13450 8

IC899 INDIAN AIRLINES 8300 4

IC302 INDIAN AIRLINES 4300 10

IC799 INDIAN AIRLINES 1050 10

MC101 DECCAN AIRLINES 3500 4

(i) Display FL_NO and NO_FLIGHTS from “KANPUR” TO “BANGALORE” from the table FLIGHTS.

Ans: Select FL_NO, NO_FLIGHTS from FLIGHTS where Starting=”KANPUR” AND ENDING=”BANGALORE”

(ii) Arrange the contents of the table FLIGHTS in the ascending order of FL_NO.
Ans: (Children, Try this as an assignment)

(iii) Display the FL_NO and fare to be paid for the flights from DELHI to MUMBAI using the tables FLIGHTS and FARES, where the
fare to paid = FARE+FARE+TAX%/100.

Ans: Select FL_NO, FARE+FARE+(TAX%/100) from FLIGHTS, FARES where Starting=”DELHI” AND Ending=”MUMBAI”

(iv) Display the minimum fare “Indian Airlines” is offering from the tables FARES.

Ans: Select min(FARE) from FARES Where AIRLINES=”Indian Airlines”

v) Select FL_NO,NO_FLIGHTS,AIRLINES from FLIGHTS, FARES Where STARTING = “DELHI” AND FLIGHTS.FL_NO =
FARES.FL_NO

Ans: FL_NO NO_FLIGHTS AIRLINES IC799 2 Indian Airlines

(vi) SELECT count (distinct ENDING) from FLIGHTS.

Ans: (Children, Try this answer as an assignment)

DELHI 2006:

5.b) Study the following tables DOCTOR and SALARY and write SQL commands for the questions (i) to (iv) and give outputs for
SQL queries (v) to (vi) :

TABLE: DOCTOR

ID NAME DEPT SEX EXPERIENCE

101 Johan 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 Murphy ORTHOPEDIC M 15

TABLE: SALARY

ID BASIC ALLOWANCE CONSULTAION

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

(i) Display NAME of all doctors who are in “MEDICINE” having more than 10 years experience from the Table DOCTOR.

Ans: Select Name from Doctor where Dept=”Medicine” and Experience>10

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

Ans: Select avg(basic+allowance) from Doctor,Salary where Dept=”Ent” and Doctor.Id=Salary.Id

(iii) Display the minimum ALLOWANCE of female doctors.

Ans: Select min(Allowance) from Doctro,Salary where Sex=”F” and Doctor.Id=Salary.Id

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

Ans: Select max(Consulation) from Doctor,Salary where Sex=”M” and Doctor.Id=Salary.Id

(v) SELECT count (*) from DOCTOR where SEX = “F”

Ans: 4

(vi) SELECT NAME, DEPT , BASIC from DOCTOR, SALRY Where DEPT = “ENT” AND DOCTOR.ID = SALARY.ID

Ans: Name Dept Basic

Jonah Ent 12000

DELHI 2005:

(5) Consider the following tables EMPLOYEES and EMPSALARY. write SQL commands for the Statements (i) to (iv) and give
outputs for SQL quires (v) to (viii).

EMPID FIRSTNAME LASTNAME ADDRESS CITY

010 GEORGE Smith 83 First Street Howard

105 MARY Jones 842VineAve Losantiville

152 SAM Tones 33 Elm st Paris

215 SARAH Ackerman 440 U.S.110 Upton

244 MANILA Sengupta 24 FriendsStreet New Delhi

300 ROBERT Samuel 9 Fifth Cross Washington

335 HENRY Williams 12 Moore Street Boston

400 RACHEL Lee 121 Harrison New York

441 PETER Thompson 11 Red road Paris

EMPSALRAY

EMPID SALARY BENEFITS DESIGNATION

010 75000 15000 Manager

105 65000 15000 Manager

152 80000 25000 Director

215 75000 12500 Manager

244 50000 12000 Clerk

300 45000 10000 Clerk

335 40000 10000 Clerk

400 32000 7500 Salesman

441 28000 7500 Salesman

(i) To display Firstname, Lastname, Address and City of all employees living in Paris from the table EMPLOYEES.

Ans: Select Firstname,Lastname,Address,City from Employees where City=”Paris”

(ii) To display the content of EMPLOYEES table in descending order of FIRSTNAME.

Ans: Select * from Employees Order By Firstname Desc

(iii) To display the Firstname, Lastname, and Total Salary of all managers from the tables, where Total Salary is calculated as
Salary+Benifts.

Ans: Select Firstname,Lastname,Salary+Benefits from Employees, Empsalary where Designation=”Manager” and

Employees.EmpId=EmpSalary.EmpId

(iv) To display the Maximum salary among Managers and Clerks from the table EMPSALARY.

Ans: Select Designation,max(Salary) from EmpSalary where Designation=”Manager” or Designation=”Clerk”

(v) SELECT FIRSTNAME,SALARY FROM EMPLOYEES,EMPSALARY WHERE DESTINATION =‟Salesman‟AND

EMPOLYEES.EMPID=EMPSALARY.EMPID ;

Ans: Firstname Salary

Rachel 32000
Peter 28000

(vi) SELECT COUNT (DISTINT DESIGNATION ) FROM EMPSALARY

Ans: 4

(vii) SELECT DESIGNATION , SUM(SALARY) FROM EMPSALARY GROUP BY DESIGNATION HAVING COUNT(*)>2;

Ans: Designation Sum(Salary)

Manager 215000
Clerk 135000
(viii) SELECT SUM (BENEFITS) FROM EMPSALARY WHERE DESIGNATION=‟Clerk‟;

Ans: 32000

OUTSIDE DELHI 2005:

5) Consider the following tables WORKERS and DESIG. Write SQL commands for the statements (i) to (iv) and give outputs for
SQL queries (v) to (viii). WORKERS

W_ID FIRSTNAME LASTNAME ADDRESS CITY

102 Sam Tones 33 Elm St. Paris

105 Sarah Ackerman 44 U.S.110 NewYork

144 Manila Sengup ta 24 Friends Street New Delhi

210 George Smith 83 First Street Howard

255 Mary Jones 842 Vine Ave. Losantiville

300 Robert Samuel 9 Fifth Cross Washington

335 Henry Williams 12Moore Street Boston

403 Ronny Lee 121 Harrison St. New York

451 Pat Thomps on 11 Red Road Paris

DESIG

DESIGINA
W_ID SALARY BENEFITS
TION

102 75000 15000 Manager

105 85000 25000 Director

144 70000 15000 Manager

210 75000 12500 Manager

255 50000 12000 Clerk

300 45000 10000 Clerk

335 40000 10000 Clerk

400 32000 7500 Salesman

451 28000 7500 Salesman

(i) To display W_ID Firstname, address andCity of all employees living in New York fromthe Table WORKERs

Ans: select W_ID ,firstname,address,city from workers where city=”New York”

(ii) To display the content of workers table in ascending order of LASTNAME.

Ans:Select * from Worker Order By lastname Asc

(iii) To display the FIRSTNAME, LASTNAME and Total Salary of all Clerks from the tables WORKERS And DESIG, where Total
salary is calculated as Salary + benifts.
Ans: Select firstname, lastname, salary+benefits where worker.w_id=desg.w_id and Designation=”Clerk”

(iv) To display the minimum salary among managers and Clerks from the tables DESIG.

Ans: (Try This Answer)

(v) SELECT FIRSTNAME, SALARY FROM WORKERS, DESIG WHERE DESIGINATION = “MANAGER” AND WORKERS.W_ID =
DESIGN.W_ID

Ans: FIRSTNAME SALARY

Sam 75000
Manila 70000
George 75000

(vi)SELECT COUNT(DISTINCT DESIGNATION) FROM DESIGN ;

Ans: 4

(vii) SELECT DESIGNATION, SUM(SALARY) FROM DESIG GROUP BY DESIGNATION HAVING COUNT (*) < 3;

Ans: Designation Sum(Salary)

Director 85000
Salesman 60000

(viii) SELECT SUM(BENIFTS) FROM DESIG WHERE DESIGINATION =”salesman”;

Ans: 15000

2004:

5. Give the following table for database a LIBRARY.

TABLE : BOOKS

BOOK_ID BOOK_NAME AUTHONAME PUBLISHER PRICE TYPE QUANTITY

F0001 The Tears William Hopkins First Publ 750 Fiction 10

F0002 Thund erbolts Anna Roberts First Publ. 700 Fiction 5

T0001 My first C+ + Brains & Brooke EPB 250 Text 10

T0002 C++ Brain works A.W.Ros saine TDH 325 Text 5

C001 Fast Cook Lata Kapoore EPB 350 Cookery 8

TABLE:ISSUED

BOOK_ID QUANTITY_ISSUED

F0001 3

T0001 1

C0001 5
Write SQL queries from b to g.

(b)To show Book name, Author name and Price of books of EPB publisher.

Ans: select Book_name,Author_name, price from books where Publisher =”EPB”

(c) To list the names of the books of FICTIONS type.

Ans: Select Book_name from books where type=”FICTION”

(d) To display the names and prices of the books in descending order of their price.

Ans: Select Book_name, price from books order by price desc;

(e) To increase the price of all books of First Pub.by 50.

Ans: update books set price= price+50 where publishers = “First Publ”

(f) To Display the Book_ID, Book_Name and Quantity Issued for all books Which have been issued.

Ans:Select Book_ID, Book_Name, Quantity_Issued from Books,Issued where Books.BookId=Issued.BookId;

(g) To insert a new row in the table Issued having the following data: “F0002”,4

Ans: insert into Issued values(“F0002”,4)

(h) Give the output of the following queries on the above tables:

(i) Select Count(Distinct Publishers) From Books

Ans: 3

(ii) Select Sum(Price) From Books Where Quantity>5 Ans: 1350.

(iii) Select Book_Name,Author_Name From Books Where Price<500.

Ans: Book_Name Author_Name My First C++ Brian & Brooks C++ Brainworks A.W.Rossaine Fast Cook Lata Kapoor.

(iv) Select Count(*) From Books.

Ans: 5

2003:

5.b Write SQL commands for (b) to (g) and write the outputs for (h) on the basis of tables TNTERIORS and NEWONES.

TABLE: INTERIORS
 NO ITEM NAME TYPE DATEOFSTOCK PRICE DISCOUNT

1 Red rose DoubleBed 23/02/02 32000 15

2 Soft touch Baby cot 20/01/02 9000 10

3 Jerry‟shome Baby cot 19/02/02 8500 10

4 Rough wood Office Table 01/01/02 20000 20

5 Comfort zone Double Bed 12/01/02 15000 20

6 Jerry look Baby cot 24/02/02 7000 19

7 Lion king Office Table 20/02/02 16000 20

8 Royal tiger Sofa 22/02/02 30000 25

9 Park sitting Sofa 13/12/01 9000 15

10 Dine paradise DinningTable 19/02/02 11000 15

TABLE:NEWONES

NO ITEMNAME TYPE DATEOFSTOCK PRICE DISCOUNT

11 White wood Doublebed 23/03/03 20000 20

12 James007 Sofa 20/02/03 15000 15

13 Tom look Baby cot 21/02/03 7000 10

(b) To show all information about the sofas from the INTERIORS table.

Ans: Select * from INTERIORS where type= “sofa”.

(d) To list ITEMNAME and TYPE of those items, in which DATEOFSTOCK is before 22/01/02 from the INTERIORS table in
descending order of ITEMNAME.

Ans: Select Itemname,Type From Interiors Where Dateofstock<{22/01/02} order by Itemname

(e) To display ITEMNAME and DATEOFSTOCK of those items in which the Discount percentage is more than 15 from
INTERIORS.

Ans: Select Itemname,Dateofstock from Interiors Where Discount>15

(f) To count the number of items whose type is “Double bed”;

Ans: Select Count(*) from Interiors Where Type=”Double Bed”

(g) To insert new row in the NEWONES table with the following data:14, “True Indian “, “Office Table “, {28/03/03},15000,20.

Ans: Insert into Newones values(14,”True Indian”,”Office Table”,”{28/03/03},15000,20).

(h) Give the outputs for the following SQL statements.

(i) Select COUNT (distinct TYPE) from INTERIORS;

Ans: 5
(ii) Select AVG(DISCOUNT)from INTERIORS where TYPE =”Baby cot”;

Ans: 13

(iii) Select SUM(price)from INTERIORS where DATEOFSTOCK<{12/02/02};

Ans: 53000

2002:

5. Given the following Teacher Relation. Write SQL Commands fro (b) to (g)

No Name Department DateofJoining Salary Sex

1 Raja Computer 21/5/98 8000 M

2 Sangita History 21/5/97 9000 F

3 Ritu Sociology 29/8/98 8000 F

4 Kumar Linguistics 13/6/96 10000 M

5 Venka traman History 31/10/99 8000 M

6 Sindhu Computer 21/5/86 14000 M

7 Aishwarya Sociology 11/1/1998 12000 F

(b) To select all the information of teacher in computer department.

Ans: Select * from Teacher where Department=”Computer”

(c ) To list the name of female teachers in History Department.

Ans: Select Name from Teacher Where Sex=”F” And Department=”History”.

(d) To list all names of teachers with date of admission in ascending order.

Ans: Select Name from Teacher Order By Dateofjoining Asc

(e) To display Teacher's Name, Department, and Salary of female teachers

Ans: Select Name,Department,Salary from Teacher Where Sex=”F”

(f)To count the number of items whose salary is less than 10000

Ans: Select Count(*) from Teacher Where Salary<10000.

(g) To insert a new record in the Teacher table with the following data: 8,”Mersha”,”Computer”, (1/1/2000),12000,”M”.Ans: Insert
into Teacher values ,”Mersha”, ”Computer”,{1/1/2000),12000,”M”);

2001:
5.b) Write the SQL commands for (i) to (vii) on the basis of the table SPORTS

TABLE: SPORTS

Studno Class Name Game1 Grade1 Game2 Grade2

10 7 Smeer Criket B Swimming A

11 8 Sujit Tennis A Skating C

12 7 Kamala Swimming B Football B

13 7 Veena Tennis C Tennis A

14 9 Archana Basketball A Cricket A

15 10 Arpit Cricket A Athletics C

(i) Display the names of the students who have grade „C‟ in either Game1 or Game2 or both.

Ans: Select Name From Sports Where Grade1=”C” OR Grade2=”C”

(ii) Display the number of students getting grade „A‟ in Cricket.

Ans: Select Count(*) from Sports Where (Game1=”Cricket” and Grade1=”A”) or (Game2=”Cricket” and Grade2=”A”)

(iii) Display the names of the students who have same game for both game1 and game2

Ans: Select Name From Sports Where Game1=Game2

(iv) Display the games taken up by the students, whose name starts with „A‟.

Ans: Select Game1,Game2 From Sports Where Name Like “A%”

(v) Add a new column named „marks‟.

Ans: Alter Table Sports Add Marks Number(5);

(vi) Assign a value 200 for marks for all those who are getting grade „B‟ or „A‟ in both Game1 and Game2.

Ans: (Children, Try This Answer as an assignment)

(vii) Arrange the whole table in the alphabetical order of name.

Ans: Select * from Sports Order By Name

2000 :

5. Write SQL commands for the (b) to (e) and write the outputs for (g) on thse basis of table CLUB.

TABLE: CLUB

COACH COACH DATE OF

AGE SPORTS PAY SEX
ID NAME APP
1 KUKREJA 35 KARATE 27/03/96 1000 M

2 RAVINA 34 KARATE 20/01/98 1200 F

3 KARAN 34 SQUASH 19/01/98 2000 M

4 TARUN 33 BASKET BAL 01/01/98 1500 M

5 ZUBIN 36 SWIMMING 12/01/98 750 M

6 KETAKI 36 SWIMMING 24/02/98 800 F

7 ANKITA 39 SQUASH 20/02/98 2200 F

8 ZAREEN 37 KARATE 22/02/98 1100 F

9 KUSH 41 SWIMMING 13/01/98 900 M

10 SHAILYA 37 BASKETBALL 19/02/98 1700 M

(b) To show all information about the swimming coaches in the club.

Ans: Select * from Club

(c) To list names of all coaches with their date of appointment (DATOFAPP) in descending order.

Ans: Select Coachname from Club order by Dataofapp desc

(d) To display a report, showing coachname, pay, age and bonus(15% of pay) for all coaches.

Ans: Select Coachname,Pay,Age,Pay*0.15 from Club

(e) To insert a new row in the CLUB table with following data: 11,”PRAKASH”,37,”SQUASH”, {25/02/98},2500,”M”

Ans: Insert into Club Values (11,”PRAKASH”,37,”SQUASH”,{25/02/98}, 2500,”M”)

(f) Give the output of the following SQL statements:

(i) select COUNT (distinct SPORTS)from CLUB;

Ans: 4

(ii) select MIN(AGE) from CLUB where SEX =”F”;

Ans: 34

(iii) select AVG(PAY) fromCLUB where SPORTS = “KARATE”;

Ans: 1100

(iv) select SUM(PAY) from CLUB where DATAOFAPP>{31/01/98};

Ans: 7800

(G) Assuming that there is one more table COACHES in the database as shown below:

TABLE:COACHES
SPORTS PERSON SEX COACH_NO

AJAY 1 M 1

SEEMA F 2

VINOD M 1

TANEJA F 3

What will be the output of the following query:

SELECT SPORTS PERSON, COACHNAME FROM CLUB,COACHES WHERE COACH_ID=COACH_NO

Ans:

SPORTS PERSON COACHNAME

AJAY 1 KUKREJA

SEEMA RAVINA

VINOD KUKREJA

TANEJA KARAN

1999:

5.) Given the following Teacher relation: Write SQL commands for questions (b) to (g). TEACHER

NO NAME DEPARTMENT DATEOFJOING SALARY SEX

1 RAJA COMPUTER 21/5/98 8000 M

2 SANGITA History 21/5/97 9000 F

3 RITU MATHS 29/8/98 8000 F

4 KUMAR HISTORY 13/6/96 10000 M

5 VENKAT MATHS 31/10/99 8000 M

6 SINDU HISTORY 21/5/86 14000 F

7 ASHWARYA MATHS 11/1/98 12000 F

(b) To show all information about the teachers of history department.

Ans:select *from teacher where department=‟history‟;

(c) To list names of female teacher who are in math department.

Ans: select name from teacher where sex=‟male‟ and department= ‟maths‟;

d) To list names of all teacher with their date of joining in ascending order.

Ans:Select Name From Teacher order by dateofjoing;

(f) To count the number of teachers with age >23.

Ans: Select count(number of teachers) from ,teacher where age>23;

(g) To insert a new row in the teacher table with the following data: 9, “raja‟, 26,“computer”, {13/5/95 }, 2300, “M”.

Ans: Insert into Teacher values(9,”raja”,26, ”computer”, {13/05/95},2300,”M”);