DELHI PRIVATE SCHOOL, DUBAI

CERTIFICATE

Certified that this project is the bonafide work of

Master /Miss
..................................Class..XII.........Div……. Roll
No………….. recorded in the school computer
laboratory during academic year 2019 to 2020.

____________________ ______________________ _____________________

Teacher in-Charge HOD External Examiner

 INDEX
S.NO TOPICS PAGE
1. ACKNOWLEDGEMENT 1
2. PROJECT AIM 2
3. PROJECT DESCRIPTION 3
4. PROGRAM SOURCE CODE 4
5. SAMPLE OUTPUT 11
6. POSSIBLE IMPROVEMENTS 17
7. BIBLIOGRAPHY 18
 STRUCTURED QUERY LANGUAGE

S.NO PROGRAMS TEACHER'S

SIGNATURE

1. Consider the following tables Product and

Client. Write SQL commands for the
statements (i) to (iv) and give outputs for
SQL queries (v) to (viii)

2. Consider the following tables Product and

Client. Write SQL commands for the
statements (i) to (iv) and give outputs for
SQL queries (v) to (viii)

3. Consider the following tables Stock and

Dealers and answer (a1) and (a2) parts of
this question

4. Consider the following tables EMPLOYEE

and SALGRADE and answer (A) and (B)
parts of this question.

5. Consider the following tables STORES AND

ITEM and answer (A) and (B) parts of this
question
6. Consider the following tables Item and
Customer. Write answer for the
statement (i) to (viii).

7. Consider the following tables SENDER

AND RECIPIENT and answer (A) and (B)
parts of this question
QUESTION 1

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

TABLE: Product

TABLE: Clients

A. SQL Commands:
i. To display the details of those clients whose city is Delhi
ii. To display the details of Products whose price is in the range of 50 to 100 (Both
values included)
iii. To display the ClientName, City from table Client, and ProductName and Price from
table Products with their corresponding matching P_ID.
iv. To increase the Price of all Products by 10.

B. OUTPUT Queries:
v. SELECT DISTINCT city FROM client;
vi. SELECT manufacturer, Max(price), Min(price), Count (*)
FROM product
GROUP BY manufacturer;
vii. SELECT clientname, productname
FROM product, client;
WHERE client.p_id = product.p_id;
viii. SELECT productname, price * 4
FROM product;
CREATING TABLES:
PRODUCT

CREATE TABLE Product (P_ID varchar (10), ProductName char (30), Manufacturer char
(30), Price int);
INSERT INTO Product VALUES('TP01', 'Talcom Powder', 'LAK', 40);
INSERT INTO Product VALUES('FW05', 'Face Wash', 'ABC', 45);
INSERT INTO Product VALUES('BS01', 'Bath Soap', 'ABC', 55);
INSERT INTO Product VALUES('SH06', 'Shampoo', 'XYZ', 120);
INSERT INTO Product VALUES('FW12', 'Face Wash', 'XYZ', 95);

CLIENTS

CREATE TABLE CLIENTS(C_ID int, ClientName char(30), City char(30), P_ID

varchar(10));
INSERT INTO Clients VALUES(1, 'Cosmetic Shop', 'Delhi', 'FW05');
INSERT INTO Clients VALUES(6, 'Total Health', 'Mumbai', 'BS01');
INSERT INTO Clients VALUES(12, 'Live Life', 'Delhi', 'SH06');
INSERT INTO Clients VALUES(15, 'Pretty Woman', 'Delhi', 'FW12');
INSERT INTO Clients VALUES(16, 'Dreams', 'Banglore', 'TP01');
 ANSWERS:
i. SELECT *
FROM clients
WHERE city = 'Delhi';

ii. SELECT *
FROM product
WHERE price BETWEEN 50 AND 100;

iii. SELECT clientname, city, productname, price

FROM clients, product
WHERE clients.p_id = product.p_id;
iv. UPDATE product
SET price = price + 10;

v. SELECT DISTINCT city FROM client;

vi. SELECT manufacturer, Max(price), Min(price), Count (*)

FROM product
GROUP BY manufacturer;
vii. SELECT clientname, productname
FROM product, client;

viii. SELECT productname, price * 4

FROM product;
QUESTION 2

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

TABLE: Consignor

TABLE: Consignee

A. SQL Commands:
i. To display the names of all Consignors from Mumbai
ii. To display the CneeID, CnorName, CnorAddress, CneeName, CneeAddress for
corresponding Cnor_ID
iii. To display consignee details in ascending order of CneeName
iv. To display number of consignors from each city
B. OUTPUT Queries:
v. SELECT DISTINCT City FROM Consignor;
vi. SELECT A.CnorName, B.CneeName 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');
viii. SELECT CneeID, CneeName FROM Consignee
WHERE CnorID = 'MU15' OR CnorID = 'ND01';
CREATING TABLES:
CONSIGNOR

CREATE TABLE Consignor

(CnorID varchar(10), CnorName char(30), CnorAddress varchar(30), City char(30));
INSERT INTO CONSIGNOR
VALUES('ND01', 'R Singhal', '24, ABC Enclave', 'New Delhi');
INSERT INTO CONSIGNOR
VALUES('ND02', 'Amit Kumar', '123, Palm Avenue', 'New Delhi');
INSERT INTO CONSIGNOR
VALUES('MU15', 'R Kohli', '5/A, South Street', 'Mumbai');
INSERT INTO CONSIGNOR
VALUES('MU50', 'S Kaur', '27-K Westend', 'Mumbai');

CONSIGNEE

CREATE TABLE Consignee(CneeID varchar(30), CnorID varchar(30), CneeName char(30),

CneeAddress varchar(30), CneeCity char(30));
INSERT INTO Consignee values('MU05', 'ND01', 'Rahul Kishore', '5, Park Avenue', 'Mumbai');
INSERT INTO Consignee values('ND08', 'ND02', 'P Dhingra', '16/J, Moore Enclave', 'Mumbai');
INSERT INTO Consignee values('KO19', 'MU15', 'A P Roy', '2A, Central Avenue', 'Mumbai');
INSERT INTO Consignee values('MU32', 'ND02', 'S Mittal', 'p 245, AB Colony', 'Mumbai');
INSERT INTO Consignee values('ND48', 'MU50', 'B P Jain', '13, Block D, A Vihar', 'Mumbai');
ANSWERS:

i. SELECT cnorname
FROM consignor
WHERE City = 'Mumbai';

ii. SELECT CneeID, CnorName, CnorAddress, CneeName, CneeAddress

FROM Consignor, Consignee
WHERE Consignor.CnorID = Consignee.CnorID;

iii. SELECT *
FROM Consignee
ORDER BY CneeName;
iv. SELECT CneeCity, Count(CneeCity)
FROM Consignee
GROUP BY CneeCity

v. SELECT DISTINCT City FROM Consignor;

vi. SELECT A.CnorName, B.CneeName 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');

viii. SELECT CneeID, CneeName FROM Consignee

WHERE CnorID = 'MU15' OR CnorID = 'ND01';
QUESTION 3

Consider the following tables Stock and Dealers and answer (a1) and (a2) parts of
this question.

Table: Stock

Table: Dealers

A. SQL Commands:
i. To display details of all Items in the Stock table in ascending order of StockDate
ii. To display ItemNo and Item name of those items from Stock table whose
UnitPrice is more than Rupees 10.
iii. To display the details of those items whose dealer code (Dcode) is 102 or
Quantity in Stock (Qty) is more than 100 from the table Stock.
iv. To display Maximum UnitPrice of items for each dealer individually as per Dcode
from the table stock.
B. OUTPUT Queries:
v. SELECT COUNT(DISTINCT Dcode) FROM Stock;
vi. SELECT Qty*UnitPrice from Stock
WHERE ItemNo = 5006;
vii. SELECT Item, Dname FROM Stock S, Dealer D
WHERE S.Dcode = D.Dcode AND ItemNo = 5004;
viii. SELECT MIN(StockDate) FROM Stock;
CREATING TABLES:
STOCK

CREATE TABLE Stock

(ItemNo int, Item varchar(40), Dcode int, Qty int, UnitPrice int, StockDate date);
INSERT INTO stock VALUES(5005, 'Ball Pen 0.5', 102, 100, 16, '31-Mar-10');
INSERT INTO stock VALUES(5003, 'Ball Pen 0.25', 102, 150, 20, '01-Jan-10');
INSERT INTO stock VALUES(5002, 'Gel Pen Premium', 101, 125, 14, '14-Feb-10');
INSERT INTO stock VALUES(5006, 'Gel Pen Classic', 101, 200, 22, '01-Jan-09');
INSERT INTO stock VALUES(5001, 'Eraser Small', 102, 210, 5, '19-Mar-09');
INSERT INTO stock VALUES(5004, 'Eraser Big', 102, 60, 10, '12-Dec-09');
INSERT INTO stock VALUES(5009, 'Sharpener Classic', 103, 160, 8, '23-Jan-09');

DEALERS

CREATE TABLE Dealers(Dcode int, Dname char(30));

INSERT INTO Dealers VALUES(101, 'Reliable Stationers');
INSERT INTO Dealers VALUES(103, 'Classic Plastics');
INSERT INTO Dealers VALUES(102, 'Clear Deals');
ANSWERS:
i. SELECT * FROM Stock
ORDER BY Stockdate;
/*date in YY/MM/DD format*/

ii. SELECT ItemNo, Item FROM Stock

WHERE UnitPrice > 10;

iii. SELECT * FROM Stock

WHERE Dcode = 102 OR Qty >100;

iv. SELECT Dcode, MAX(UnitPrice) FROM Stock

GROUP BY Dcode;
v. SELECT COUNT(DISTINCT Dcode) FROM Stock;

vi. SELECT Qty*UnitPrice from Stock

WHERE ItemNo = 5006;

vii. SELECT Item, Dname FROM Stock S, Dealer D

WHERE S.Dcode = D.Dcode AND ItemNo = 5004;

viii. SELECT Item, Dname FROM Stock S, Dealer D

WHERE S.Dcode = D.Dcode AND ItemNo = 5004;
QUESTION 4

Consider the following tables EMPLOYEE and SALGRADE and answer (A) and
(B) parts of this question.

Table: EMPLOYEE

Table: SALGRADE

A. SQL Commands:
i. To display the details of all Employees in descending order of DOJ.
ii. To display NAME and DESIG of those Employees, whose SALGRADE is either
S02 or S03.
iii. To display the content of all the employees table, whose DOJ is in between '09-
Feb-2006' and '08-Aug-2009'.
iv. To add a new row with the following: 19, 'Harish Roy', 'HEAD-IT', 'S02', '09-
Sep-2007', '21-Apr-1983'.
B. Output Queries:
v. SELECT COUNT (Sgrade), Sgrade from employee group by sgrade;
vi. SELECT MIN(DOB), MAX(DOJ) FROM Employee;
vii. SELECT Name, Salary from Employee E,
SALGRADE S WHERE E.Sgrade = S.sgrade and E.Ecode<103;
viii. SELECT SGRADE, SALARY + HRA FROM SALGRADE WHERE SGRADE
= 'S02';
CREATING TABLES:

EMPLOYEE

CREATE TABLE Employee

(
ECODE int,
NAME char(30),
DESIG char(30),
SGRADE varchar(30),
DOJ date,
DOB date
);
INSERT INTO Employee VALUES(101, 'Abdul Ahmad', 'EXECUTIVE', 'S03', '23-Mar-
2003', '13-Jan-1980');
INSERT INTO Employee VALUES(102, 'Ravi Chander', 'HEAD-IT', 'S02', '12-Feb-2010',
'22-Jul-1987');
INSERT INTO Employee VALUES(103, 'John Ken', 'RECEPTIONIST', 'S03', '24-Jun-2009',
'24-Feb-1983');
INSERT INTO Employee VALUES(105, 'Nazar Ameen', 'GM', 'S02', '11-Aug-2006', '03-
Mar-1984');
INSERT INTO Employee VALUES(108, 'Priyam Sen', 'CEO', 'S01', '29-Dec-2004', '19-Jan-
1982');

SALGRADE

CREATE TABLE SALGRADE

( SGRADE varchar(20), SALARY int, HRA int );
INSERT INTO SALGRADE VALUES('S01', 56000, 18000);
INSERT INTO SALGRADE VALUES('S02', 32000, 12000);
INSERT INTO SALGRADE VALUES('S03', 24000, 8000);
ANSWERS:
i. SELECT * FROM Employee ORDER BY DOJ DESC;

ii. SELECT name, desig FROM Employee, SALGRADE

WHERE SALGRADE.sgrade = Employee.sgrade;

iii. SELECT * FROM Employee

WHERE DOJ BETWEEN '2006-Aug-09' and '2009-Aug-08';

iv. INSERT INTO Employee VALUES(19, 'Harish Roy', 'HEAD-IT', 'S02', '09-Sept-2007',
'21-Apr-1983');

v. SELECT COUNT (Sgrade), Sgrade from employee group by sgrade;

vi. SELECT MIN(DOB), MAX(DOJ) FROM Employee;

vii. SELECT Name, Salary from Employee E, SALGRADE S
WHERE E.Sgrade = S.sgrade and E.Ecode<103;

viii. SELECT SGRADE, SALARY + HRA FROM SALGRADE

WHERE SGRADE = 'S02';
QUESTION 5

Consider the following tables STORES AND ITEM and answer (A) and (B) parts
of this question.

Table: STORE

Table: ITEM

A. SQL Commands:
i. To display Iname and Price of all the Items in ascending order of their price.
ii. To display SNo and SName of all Stores located in CP.
iii. To display minimum and maximum price of each IName from the table Item.
iv. To display IName, Price of all items and their respective SName where they are
available.
B. Output Queries:
v. SELECT DISTINCT INAME FROM ITEM WHERE PRICE >= 5000;
vi. SELECT AREA, COUNT (*) FROM STORE GROUP BY AREA;
vii. SELECT COUNT(DISTINCT AREA) FROM STORE;
viii. SELECT INAME, PRICE * 0.05 DISCOUNT FROM ITEM WHERE SNO IN
('S02', 'S03')
CREATING TABLES:
STORE

CREATE table STORE(Sno varchar(3) not null primary key,SName char(40),Area char(20));
INSERT INTO STORE VALUES ('S01','ABC Copmutronics','GK II');
INSERT INTO STORE VALUES ('S02','All Infotech Media','CP');
INSERT INTO STORE VALUES ('S03','Tech Shoppe','Nehru Place');
INSERT INTO STORE VALUES ('S04','Geeks Techo Soft','Nehru Place');
INSERT INTO STORE VALUES ('S05','Hitech Tech Store','CP');

ITEM

CREATE table ITEM(INo varchar(3) not null primary key,IName char(20),Price int,SNo
varchar(3));
INSERT INTO ITEM VALUES ('T01','Mother Board',12000,'S01');
INSERT INTO ITEM VALUES ('T02','Hard Disk',5000,'S01');
INSERT INTO ITEM VALUES ('T03','Keyborad',500,'S02');
INSERT INTO ITEM VALUES ('T04','Mouse',300,'S01');
INSERT INTO ITEM VALUES ('T05','Mother Board',13000,'S02');
INSERT INTO ITEM VALUES ('T06','Keyboard',400,'S03');
INSERT INTO ITEM VALUES ('T07','LCD',6000,'S04');
INSERT INTO ITEM VALUES ('T08','LCD',5500,'S05');
INSERT INTO ITEM VALUES ('T09','Mouse',350,'S05');
INSERT INTO ITEM VALUES ('T10','Hard Disk',4500,'S03');
ANSWERS:
i. SELECT IName,Price FROM ITEM
ORDER BY Price;

ii. SELECT SNo, SName FROM STORE

WHERE Area='CP';

iii. SELECT IName, MIN(Price), MAX(Price) FROM ITEM

GROUP BY IName;
 iv. SELECT IName,Price,SName FROM ITEM,STORE
WHERE ITEM.SNo=STORE.SNo;

v. SELECT DISTINCT INAME FROM ITEM

WHERE PRICE >= 5000;

vi. SELECT AREA, COUNT (*) FROM STORE GROUP BY AREA;

vii. SELECT COUNT(DISTINCT AREA) FROM STORE;

viii. SELECT INAME, PRICE * 0.05 DISCOUNT FROM ITEM WHERE SNO IN ('S02',
'S03')
QUESTION 6

Consider the following tables Item and Customer. Write answer for the statement
(i) to (viii).

TABLE: CUSTOMER

TABLE: ITEM

A. SQL Commands:
i. To display the details of those customers whose city is Delhi.
ii. To display the details of item whose price is in the range of 35000 to 55000 (both values
included).
iii. To display the customer name, city from table Customer, and ItemName and Price from
table Item, with their corresponding i_ID.
iv. To increase the price of all items by 1000 in the table Item.

B. Output Queries:
v. SELECT DISTINCT CITY FROM CUSTOMER;
 vi. SELECT ITEMNAME, MAX(PRICE), COUNT(*) FROM ITEM GROUP BY
ITEMNAME;
vii. SELECT CustomerName, Manufacturer from Item, Customer WHERE
Item.I_id=Customer.I_id;
viii. SELECT ItemName,Price * 100 From Item WHERE Manufacturer='ABC';
CREATING TABLES:
CUSTOMER

CREATE TABLE CUSTOMER (C_ID VARCHAR(10), CUSTOMERNAME CHAR(20),

CITY CHAR (20), I_ID VARCHAR(10));
INSERT INTO CUSTOMER VALUES ('01', 'N ROY', 'DELHI', 'LC03');
INSERT INTO CUSTOMER VALUES ('06', 'H SINGH', 'MUMBAI', 'PC03');
INSERT INTO CUSTOMER VALUES ('12', 'R PANDEY', 'DELHI', 'PC06');
INSERT INTO CUSTOMER VALUES ('15', 'C SHARMA', 'DELHI', 'LC03');
INSERT INTO CUSTOMER VALUES ('16', 'K AGARWAL', 'BANGLORE', 'PC01');

ITEM

CREATE TABLE ITEM (I_ID VARCHAR(10), ITEMNAME CHAR(20), MANUFACTURER

CHAR (20), PRICE DECIMAL)
INSERT INTO ITEM VALUES ('PC01', 'PERSONAL COMPUTER', 'ABC', 35000)
INSERT INTO ITEM VALUES ('LC05', 'LAPTOP', 'ABC', 55000)
INSERT INTO ITEM VALUES ('PC03', 'PERSONAL COMPUTER', 'XYZ', 32000)
INSERT ITEM VALUES ('PC06', 'PERSONAL COMPUTER', 'COMP', 37000)
INSERT INTO ITEM VALUES ('LC03', 'LAPTOP', 'PQR', 57000)
ANSWERS:
i) SELECT * FROM CUSTOMER WHERE CITY='DELHI';

ii) SELECT * FROM ITEM WHERE PRICE BETWEEN 35000 AND 55000;

iii) SELECT CUSTOMERNAME, CITY, ITEMNAME, PRICE FROM CUSTOMER, ITEM

WHERE CUSTOMER.I_ID=ITEM.I_ID;

iv) UPDATE ITEM SET PRICE=PRICE+1000

v) SELECT DISTINCT CITY FROM CUSTOMER;

vi) SELECT ITEMNAME, MAX(PRICE), COUNT(*) FROM ITEM GROUP BY
ITEMNAME;

vii) SELECT CustomerName, Manufacturer from Item, Customer WHERE

Item.I_id=Customer.I_id;

viii) SELECT ItemName,Price * 100 From Item WHERE Manufacturer='ABC';

QUESTION 7

Consider the following tables SENDER AND RECIPIENT and answer (A) and (B)
parts of this question

TABLE: SENDER

TABLE: RECIPIENT

A. SQL Commands:
i. To display the names of all senders from Mumbai.
ii. To display the recID, senderName, senderAddress, RecName, RecAddress for
every recipt.
iii. To display the sender details in ascending order of SenderName.
iv. To display number of Recipients from each city.
B. Output Queries:
v. SELECT DISTINCT SenderCity FROM Sender;
vi. SELECT A.SenderName A, B.RecName FROM Sender A, Recipient B
WHERE A.SenderID=B. SenderID AND B.RecCity='Mumbai';
vii. SELECT RecName,RecAddress FROMRecipient WHERE RecCity Not IN
('Mumbai',Kolkata');
viii. SELECT RecID, RecName FROM Recipient WHERE SenderID = 'MU02' OR
SenderID = 'ND50';
CREATING TABLES:

SENDER

CREATE TABLE SENDER (SenderID varchar(5), SenderName char(30), SenderAddress

varchar(30), SenderCity char(30));
INSERT INTO SENDER VALUES('ND01' , 'R Jain', '2, ABC Appts', 'New Delhi');
INSERT INTO SENDER VALUES ('MU02', 'H Sinha', '12, Newton', 'Mumbai');
INSERT INTO SENDER VALUES('MU15 , 'S Jha' , '27/A, Park Street', 'New Delhi');
INSERT INTO SENDER VALUES('ND50' , 'T Prasad', '122-K, SDA', 'Mumbai');

RECIPIENT

CREATE TABLE Recipient( RecID varchar(5), SenderId varchar(5), RecName char(30),

RecAddress char(30), RecCity char(30));
INSERT INTO Recipient VALUES('KO05', 'ND01', 'R Bajpayee', '5, Central Avenue',
'Kolkata');
INSERT INTO Recipient VALUES('ND08', 'MU02', 'S Mahajan', '116, A Vihar', 'New Delhi');
INSERT INTO Recipient VALUES('MU19', 'ND01', 'H Singh', '2A,Andheri East', 'Mumbai;);
INSERT INTO Recipient VALUES ('MU32', 'MU15', 'PK Swamy', 'B5, CS erminus', 'Mumbai');
INSERT INTO Recipient VALUES ('ND48', 'ND50', 'S Tripathi', '13, B1 D,Mayur Vihar', 'New
Delhi');
ANSWERS:
i. SELECT * FROM Sender
WHERE SenderCity ='Mumbai';

ii. SELECT recID, SenderName, SenderAddress, RecName, RecAddress

FROM Sender, Recipient
WHERE Sender.Senderid=Recipient.SenderId;

iii. SELECT * FROM Sender

ORDER BY SenderName;

iv. SELECT RecCity,Count(*) FROM Recipient

GROUP BY RecCity;

v. SELECT DISTINCT SenderCity FROM Sender;

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

vii. SELECT RecName,RecAddress FROM Recipient WHERE RecCity Not IN

('Mumbai',Kolkata');

viii. SELECT RecID, RecName FROM Recipient WHERE SenderID = 'MU02' OR SenderID
= 'ND50';
 INDEX

S.no Topic

1. Structures

2. Classes and Objects

3. Constructor and Destructor
4. Inheritence
5. File Handling
6. Pointers
7. Arrays

8. Stacks,Queues and Linked Lists

9. SQL