SQL Practical Assignment File

SUBMITTED AS PARTIAL FULLFILMENT

FOR THE COMPLETION OF
CLASS XII

(ALL INDIA SENIOR SCHOOL CERTIFICATE EXAMINATION)

AISSCE

SUBMITTED BY:-

STUDENTS NAME

CLASS – XII

SQL Practical Assignment File

 TABLE OF CONTENT

QUESTION – 1 – ALL OPERATIONS AT A GLANCE ............................................................. 3

QUESTION – 2 – SENDER AND RECIPIENT ............................................................................ 8

QUESTION – 3 CONSIGNOR AND CONSIGNEE ................................................................. 10

QUESTION – 4 : GAMES AND PLAYER ................................................................................... 12

QUESTION 5 : RESORT AND OWNER .................................................................................... 14

Page | 2
QUESTION – 1 – ALL OPERATIONS AT A GLANCE
Use the following table to answer the queries given below :

Table: empinfo
first last id age city state
John Jones 99980 45 Payson Arizona
Mary Jones 99982 25 Payson Arizona
Eric Edwards 88232 32 San Diego California
Mary Ann Edwards 88233 32 Phoenix Arizona
Ginger Howell 98002 42 Cottonwood Arizona
Sebastian Smith 92001 23 Gila Bend Arizona
Gus Gray 22322 35 Bagdad Arizona
Mary Ann May 32326 52 Tucson Arizona
Erica Williams 32327 60 Show Low Arizona
Leroy Brown 32380 22 Pinetop Arizona
Elroy Cleaver 32382 22 Globe Arizona

Q.1. Write SQL queries to :

(a) Display everyone's first name and their age for everyone that's in table.

SQL> select first, age from empinfo;

(b) Display the first name, last name, and city for everyone that's not from Payson.

SQL> select first, last, city from empinfo where city <> 'Payson';

(c) Display all columns for everyone that is over 40 years old.

SQL> select * from empinfo where age > 40;

(d) Display the first and last names for everyone whose last name ends in an "ay".

SQL> select first, last from empinfo where last LIKE '%ay';

(e) Display all columns for everyone whose first name equals "Mary".

SQL> select * from empinfo where first = 'Mary';

(f) Display all columns for everyone whose first name contains "Mary".

SQL> select * from empinfo where first LIKE '%Mary%';

Page | 3
Q.2. Create Table Exercise

You have just started a new company. It is time to hire some employees. You will need to
create a table that will contain the following information about your new employees:
firstname, lastname, title, age, and salary.

SQL> create table myemployees

(firstname varchar(30),

lastname varchar(30),

title varchar(30),

age number(2),

salary number(8,2));

Q.3. Insert statement exercises

Your first three employees are the following:

Jonie Weber, Secretary, 28, 19500.00

Potsy Weber, Programmer, 32, 45300.00
Dirk Smith, Programmer II, 45, 75020.00

Enter these employees into your table first, and then insert at least 5 more of your own
list of employees in the table.

SQL> insert into myemployees (firstname, lastname, title, age, salary)

values ('Jonie', 'Weber', 'Secretary', 28, 19500.00),

('Potsy', 'Weber', 'Programmer', 32, 45300.00),

('Dirk', 'Smith', 'Programmer', 45, 75020.00);

Q.4. After they're inserted into the table, enter select statements to:

(a) Select all columns for everyone in your employee table.

SQL> select * from myemployees;

(b) Select all columns for everyone with a salary over 30000.

SQL> select * from myemployees where salary > 30000;

(c) Select first and last names for everyone that's under 30 years old.

SQL> select firstname, lastname from myemployees where age < 30;

Page | 4
(d) Select first name, last name, and salary for anyone with "Programmer" in their title.

SQL> select firstname, lastname, salary from myemployees

where title LIKE '%Programmer%';

(e) Select all columns for everyone whose last name contains "ebe".

SQL> select * from myemployees where lastname LIKE '% ebe %';

(f) Select the first name for everyone whose first name equals "Potsy".

SQL> select firstname from myemployees where firstname = 'Potsy';

(g) Select all columns for everyone over 80 years old.

SQL> select * from myemployees where age > 80;

(h) Select all columns for everyone whose last name ends in "ith".

SQL> select * from myemployees where lastname LIKE '% ith';

Q.5. Delete statement exercises

(Use the select statement to verify your deletes):

Jonie Weber-Williams just quit, remove her record from the table:

SQL> delete from myemployees where lastname = 'Weber-Williams';

It's time for budget cuts. Remove all employees who are making over 70000 dollars.

SQL> delete from myemployees where salary > 70000;

Q.6. Aggregate Functions

Use these tables :

items_ordered

customerid order_date item quantity price

10330 30-Jun-1999 Pogo stick 1 28.00
10101 30-Jun-1999 Raft 1 58.00
10298 01-Jul-1999 Skateboard 1 33.00
10101 01-Jul-1999 Life Vest 4 125.00
10299 06-Jul-1999 Parachute 1 1250.00
10339 27-Jul-1999 Umbrella 1 4.50
10449 13-Aug-1999 Unicycle 1 180.79
Page | 5
10439 14-Aug-1999 Ski Poles 2 25.50
10101 18-Aug-1999 Rain Coat 1 18.30
10449 01-Sep-1999 Snow Shoes 1 45.00
10439 18-Sep-1999 Tent 1 88.00
10298 19-Sep-1999 Lantern 2 29.00
10410 28-Oct-1999 Sleeping Bag 1 89.22
10438 01-Nov-1999 Umbrella 1 6.75
10438 02-Nov-1999 Pillow 1 8.50
10298 01-Dec-1999 Helmet 1 22.00
10449 15-Dec-1999 Bicycle 1 380.50
10449 22-Dec-1999 Canoe 1 280.00
10101 30-Dec-1999 Hoola Hoop 3 14.75
10330 01-Jan-2000 Flashlight 4 28.00
10101 02-Jan-2000 Lantern 1 16.00
10299 18-Jan-2000 Inflatable Mattress 1 38.00
10438 18-Jan-2000 Tent 1 79.99
10413 19-Jan-2000 Lawnchair 4 32.00
10410 30-Jan-2000 Unicycle 1 192.50
10315 2-Feb-2000 Compass 1 8.00
10449 29-Feb-2000 Flashlight 1 4.50
10101 08-Mar-2000 Sleeping Bag 2 88.70
10298 18-Mar-2000 Pocket Knife 1 22.38
10449 19-Mar-2000 Canoe paddle 2 40.00
10298 01-Apr-2000 Ear Muffs 1 12.50
10330 19-Apr-2000 Shovel 1 16.75

customers

customerid firstname lastname city state

10101 John Gray Lynden Washington
10298 Leroy Brown Pinetop Arizona
10299 Elroy Keller Snoqualmie Washington
10315 Lisa Jones Oshkosh Wisconsin
10325 Ginger Schultz Pocatello Idaho
10329 Kelly Mendoza Kailua Hawaii
10330 Shawn Dalton Cannon Beach Oregon
10338 Michael Howell Tillamook Oregon
10339 Anthony Sanchez Winslow Arizona
10408 Elroy Cleaver Globe Arizona
10410 Mary Ann Howell Charleston South Carolina
10413 Donald Davids Gila Bend Arizona
10419 Linda Sakahara Nogales Arizona
10429 Sarah Graham Greensboro North Carolina
10438 Kevin Smith Durango Colorado
10439 Conrad Giles Telluride Colorado
10449 Isabela Moore Yuma Arizona

Page | 6
 (a) Select the maximum price of any item ordered in the items_ordered table. Hint:
Select the maximum price only.

SQL> SELECT max(price) FROM items_ordered;

(b) Select the average price of all of the items ordered that were purchased in the
month of Dec.

SQL> SELECT avg(price) FROM items_ordered

WHERE order_date LIKE '%Dec%';

(c) What are the total number of rows in the items_ordered table?

SQL> SELECT count(*) FROM items_ordered;

(d) For all of the tents that were ordered in the items_ordered table, what is the price
of the lowest tent? Hint: Your query should return the price only.

SQL> SELECT min(price) FROM items_ordered WHERE item = 'Tent';

Page | 7
QUESTION – 2 – SENDER AND RECIPIENT
Consider the following tables. Write SQL commands for the statements (i) to (iv) and
give outputs for SQL queries (v) to (viii):

TABLE: SENDER

SenderlD SenderName SenderAddress SenderCity

ND01 R Jain 2,ABC Appts New Delhi
MU02 H Sinha 12, Newtown Mumbai
MU15 S Jha 27/A,Park Street Mumbai
ND50 T Prasad 122-K, SDA New Delhi

TABLE : RECIPIENT

RecID SenderlD RecName RecAddress RecCity

KO05 ND01 R Bajpayee 5,Central Avenue Kolkata
ND08 MU02 S Mahajan 116,A Vihar New Delhi
MU19 ND01 H Singh 2A, Andheri East Mumbai
MU32 MU15 P K Swamy B5,C S Terminus Mumbai
ND48 ND50 S D Tripathi 13 B1, Mayur Vihar New Delhi

(i) To display the names of all Senders from Mumbai

SQL> select SenderName from SENDER where SenderCity = ‘Mumbai’;

(ii) To display the RecID, SenderName, SenderAddress, RecName, RecAddress

for every Recipient

SQL> select RecID,SenderName, SenderAddress, RecName, RecAddress

from SENDER S,RECIPIENT R where S.SenderID=R.SenderID;

(iii) To display Recipient details in ascending order of RecName

SQL> select * from RECIPIENT order by RecName;

(iv) To display number of Recipients from each city

SQL> Select count(*) from RECIPIENT group by RecCity;

Page | 8
(v) SELECT DISTINCT SenderCity FROM Sender;

SenderCity

New Delhi

Mumbai

(vi) SELECT A. SenderName, B.RecName FROM Sender A, Recipient B

WHERE A. SenderlD = B.SenderlD AND B.RecCity = ‘Mumbai’;

SenderName RecName

R Jain H Singh

S Jha P K Swamy

(vii) SELECT RecName, RecAddress FROM Recipient

WHERE RecCity NOT IN (‘Mumbai’, ‘Kolkata’);

RecName RecAddress
S Mahajan 116,A Vihar
S D Tripathi 13 B1, Mayur Vihar

(viii) SELECT RecID, RecName FROM Recipient

WHERE SenderID=’MU02' OR SenderID=’ND50';

RecID RecName

ND08 S Mahajan

ND48 S D Tripathi

Page | 9
QUESTION – 3 CONSIGNOR AND CONSIGNEE
Consider the following tables Consignor and Consignee. Write SQL commands for the
statements (i) to (iv) and give outputs for SQL queries (v) to (viii).

TABLE : CONSIGNOR

CnorlD CnorName CnorAddress City

ND01 R Singhal 24,AC Enclave New Delhi
ND02 Amit Kumar 123, Palm Avenue New Delhi
MU15 R Kohli 5/A,South Street Mumbai
MU50 S Kaur 27-K,Westend Mumbai

TABLE : CONSIGNEE

CneelD CnorlD CneeName CneeAddress CneeCity

MU05 ND01 Rahul Kishore 5,Park Avenue Mumbai
ND08 ND02 P Dhingra 16/J, Moore Enclave New Delhi
KO19 MU15 A P Roy 2A,Central Avenue Kolkata
MU32 ND02 S P Mittal 245,AB Colony Mumbai
ND48 MU50 B P Jain 13,D Block,Anand Vihar New Delhi

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

SQL> select CnorName from CONSIGNOR where City= ‘Mumbai’;

(ii) To display the CneelD, CnorName, CnorAddress, CneeName, CneeAddress for every
Consignee.

SQL> select CneelD, CnorName, CnorAddress, CneeName, CneeAddress

from CONSIGNOR CO,CONSIGNEE CE where CO. CnorlD = CE. CnorlD;

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

SQL> select * from CONSIGNEE order by CneeName;

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

SQL> select count(CnorID),CneeCity from CONSIGNEE group by CneeCity;

Page | 10
(v) SELECT DISTINCT City FROM CONSIGNEE;

CneeCity
Mumbai
New Delhi
Kolkata

(vi) SELECT A.CnorName, B.CneeName FROM Consignor A, Consignee B

WHERE A.CnorID = B.CnorlD AND B.CneeCity = ‘Mumbai’;

CnorName CneeName

R Singhal Rahul Kishore

Amit Kumar S P Mittal

(vii) SELECT CneeName, CneeAddress FROM Consignee

WHERE CneeCity NOT IN (‘Mumbai’, ‘Kolkata’);

CneeName CneeAddress CneeCity

P Dhingra 16/J, Moore Enclave New Delhi
B P Jain 13,D Block,Anand Vihar New Delhi

(viii) SELECT CneelD, CneeName FROM Consignee WHERE CnorID=’MU15' OR

CnorID=’ND01';

CneelD CneeName

MU05 Rahul Kishore

KO19 A P Roy

Page | 11
QUESTION – 4 : GAMES AND PLAYER
Consider the following tables GAMES and PLAYER and answer (b) and (c) parts of this
question:

Table: GAMES

GCode GameName Type Number PrizeMoney ScheduleDate

101 Carom Indoor 2 5000 23-Jan-2004
Board
102 Badminton Outdoor 2 12000 12-Dec-2003
103 Table Indoor 4 8000 14-Feb-2004
Tennis
105 Chess Indoor 2 9000 01-Jan-2004
108 Lawn Outdoor 4 25000 19-Mar-2004
Tennis

Table: PLAYER

PCode Name GCode

1 Nabi Ahmad 101
2 Ravi Sahai 108
3 Jatin 101
4 Nazneen 103

(b) Write SQL commands for the flowing statements:

(i) To display the name of all GAMES with their GCodes

SQL> Select GCode,GameName from GAMES;

(ii) To display details of those GAMES which are having PrizeMoney more than 7000.

SQL> Select * from GAMES where PrizeMoney>7000;

(iii) To display the content of the GAMES table in ascending order of Schedule Date.

SQL> Select * from GAMES order by ScheduleDate;

(iv) To display sum of PrizeMoney for each Type of GAMES

SQL> Select sum(PrizeMoney) from GAMES group by Type;

Page | 12
(c) Give the output of the following SQL queries: 2

(i) SELECT COUNT(DISTINCT Number) FROM GAMES;

Number

(ii) SELECT MAX(ScheduleDate), MIN(ScheduleDate) FROM GAMES;

MAX(ScheduleDate) MIN(ScheduleDate)

19-Mar-2004 12-Dec-2003

(iii) SELECT Name, GameName FROM GAMES G, PLAYER P WHERE

G.GCode=P.GCode AND G.PrizeMoney>10000;

Name GameName

Ravi Sahai Lawn Tennis

(iv) SELECT DISTINCT GCode FROM PLAYER;

GCode

101

103

108

Page | 13
QUESTION 5 : RESORT AND OWNER
Consider the following table RESORT and OWNER and answer questions (A)
and (B)

Table : RESORT

RCODE PLACE RENT TYPE STARTDATE

R101 GOA 15000 5 Star 23 Jan 2008
R102 HIMANCHAL 12000 4 Stat 12 Nov 2007
R103 KERALA 12500 5 Star 18 Mar 2006
R104 HIMANCHAL 10900 3 Star 09 Jan 2007
R105 GUJARAT 8000 2 Star 29 Apr 2008
R106 GOA 16000 7 Star 03 Mar 2003
R107 ORISSA 9600 3 Star 16 Oct 2005
R108 KERALA 12000 4 Star 12 Aug 2006
R109 HIMANCHAL 8500 2 Star 25 Jan 2004
R110 GOA 12800 4 Star 23 Feb 2008

Table : OWNEDBY

PLACE OWNER
GOA SUN VILLAGE
KERALA KTDC
HIMANCHAL KALRA RESORTS
GUJARAT KINJAL GROUP
ORISSA OTDC
(A) Write SQL commands for the following statements:

(i) to display the RCODE and PLACE of all ‘2 Star’ resorts in the alphabetical
order of the place from table RESORT.

SQL> Select RCODE,PLACE from RESORT where TYPE= ‘2 Star’ order by

PLACE;
(ii) to display the maximum & minimum rent for each type of resort from table
RESORT.

SQL> Select max(RENT),min(RENT) from RESORT group by TYPE;

(iii) to display the details of all resorts which are started after 31-Dec-04 from
table RESORT.

SQL> Select * from RESORT where STARTDATE> ’31-Dec-04’;

(iv) to display the owner of all ‘5 Star’ resorts from tables RESORT and
OWNEDBY.

SQL> Select owner from RESORT R,OWNEDBY O where TYPE= ‘5 Star’ and
R.PLACE=O.PLACE;

Page | 14
(B) Give output for the following queries:

(i) SELECT MIN(RENT) FROM RESORT WHERE PLACE=’KERALA’;

12000
(ii) SELECT TYPE, STARTDATE FROM RESORT WHERE TYPE=’2 Star’
ORDER BY STARTDATE;

TYPE STARTDATE
2 Star 29 Apr 2008
2 Star 25 Jan 2004
(iii) SELECT PLACE, OWNER FROM OWNEDBY WHERE PLACE LIKE ‘%L’;

PLACE OWNER
HIMANCHAL KALRA RESORTS

(iv) SELECT RCODE, RENT FROM RESORT, OWNEDBY WHERE

RESORT.PLACE=OWNEDBY.PLACE AND TYPE>=’4 Star’;

RCODE RENT
R101 15000
R103 12500
R106 16000

Page | 15
 CERTIFICATE

This is to certify that the Practical Assignment in SQL has been successfully carried
out by , Student of CLASS XII, ST.
MONTFORT SR. SEC. SCHOOL, BHOPAL , for the partial fulfillment of the requirement of
"HIGHER SECONDARY EXAMINATION”.

Their performance has been good and we wish them good luck for their future.

EXTERNAL EXAMINER

Ms. GARGEE CHATTOPADHYAY Bro. MONACHAN K.K.

(TEACHER IN-CHARGE) (PRINCIPAL)

Page | 16