MYSQL
Table 1: STUDENT
No Name Stipend Stream Avgmarks Grade Section
1 Karan 400 Medical 78 B 12
2 Divakar 450 Commerce 45 D 11
3 Anil 399 Humanities 67 C 11
4 Vikas 500 Commerce 74 B 12
5 Manu 650 Medical 89 A 11
a) Display list of all the commerce stream students from the table.
b) List the names of students who are in class 12 sorted by stipend in ascending order.
c) List the details of all students sorted by Avgmarks in descending order within
section in ascending order.
d) Display a report listing name, stream and stipend received in a year assuming that
stipend is paid every month.
e) To count the number of students with grade C.
f) To display sum of Avgmarks for B grade students.
g) To display sum of stipend by grade.
h) To display details of students whose name starts with ‘V’.
i) To display details of students who have opted for medical stream.
j) Add a new column Mobno to the table Student.
Table 2: EMPLOYEE
Eno Ename Designation Department
MG01 Arina Manager Logistics
MG02 Devisha Manager Sales
CR09 Henry Clerk Logistics
OF01 Angel Officer Reception
MG03 Priyam Clerk Personnel
ACCOUNT
Eno Basic DA Tax
MG01 8000 3200 800
MG02 8500 3400 850
CR09 6200 2480 248
OF01 7500 3000 300
MG03 6000 2400 240
a) To display Ename and Department of persons working as Manager.
b) To display Ename and net income of all persons (net income=Basic+DA-tax).
c) To display Ename and Basic of all persons whose basic is greater than 7000.
d) To display count of people working as Manager.
e) To insert a new row into the table having values as
(“OF03”,”Ruban”,”Officer”,”Sales”).
f) To increment the value of DA by 25% of the existing value for all employees
whose DA is less than 3000.
Table 3: MOVIE
Movieid Moviename Actor Type Cost Language Number
F001 Terminator Schwazneger Fiction 250 English 5
R001 Hum Tum Saif Ali Romance 150 Hindi 3
A005 The Mask Jim Carey Comedy 200 English 4
A115 Bruce Jum Carey Comedy 250 English 5
Almighty
A007 Black Rani Social 150 Hindi 3
ISSUED
Movieid Qty_Issued
A005 2
R001 1
A115 3
a) To display moviename, actor and cost of all English movies.
b) To list the names of all comedy movies.
c) To display the moviename, cost of movies in ascending order of cost.
d) To increase the price of all comedy movies by 50.
e) To insert a new row into the table.
f) To display names of movies starting with T.
g) To display details of all 5 lettered movies in the movie table.
Table 4: INSURANCE
Company Type Years Name Coverage
CUF Car 15 Care safe 80000
LIT Life 40 Insured Life policy 200000
HC House 25 House Secure policy 550000
URFree Life 35 Life Insurance 750000
Care Child 25 Future Child 500000
a) Give the name and type of all companies whose coverage is more than 100000
but less than 700000.
b) List the name, company and years of all those whose type is life and years are
more than 20.
c) List the different types of policies.
d) Calculate the average coverage of all the policies.
e) List the name in descending order of coverage.
f) Display the sum of coverage on the basis of type.
g) Display the details of all companies whose insurance years range from 20 to 30
years.
h) Add a new row to the table insurance.
Table 5: BATSMAN
Player Matches Runs Century Halfcentury Average Age
S.Ravindra 85 3456 16 12 56.7 21
Rama 112 7642 23 20 72.7 26
R.Singh 36 1950 7 12 42.2 28
A.Panwar 106 4756 11 19 45.3 30
Aryan 20 958 8 5 53.2 19
a) List the names of all batsmen whose average is more than 50.
b) List details of batsmen whose age is between 18 and 25.
c) List the details of batsmen whose centuries exceed half centuries.
d) Display details of batsman whose average is greater than 50.
e) Insert a new record into the table.
f) Display details of batsman whose name starts with R.
g) Display details of batsman in descending order of matches.
h) Add a new column Wickets taken to the table batsman.
i) To count the number of batsman whose no of halfcentury exceeds 15.
Table 6 : PREPAID
No Cname Model Connect Activation Date Validity Amount
1 Sita Nokia Airtel 2004-06-04 365 3300
2 Ramesh Nokia Hutch 2005-02-12 60 890
3 Reena Samsung Idea 2004-05-14 180 560
4 Meethali LG Airtel 2004-09-24 30 600
5 Ramanuj Sony Reliance 2004-10-16 90 900
6 Karan LG Idea 2005-02-02 60 300
a) To display Cname and Amount of customers having “Nokia” model and “Hutch”
connection arranged in descending order of amount.
b) To display the different types of connection available.
c) To display the sum of validity for each type of connection grouping.
d) To display Cname, Connect of all the people whose date of activation started in
the month of February 2005.
e) To display details of customers whose activation date is in the year 2005
f) To add a new row to the table.
g) To add a new column Surname to the table prepaid.
h) To display the details of people with name starting with R.
Table 7 : BOOKS-
Bid Bname Autname Publ Price Type Quantity
C001 Fast Cook Lata K EPB 355 Cookery 5
F005 The Tears William H First Publ 650 Fiction 20
T008 My First C++ Brian Brok EPB 350 Text 10
F002 Thunderbolts Anna Robs First Publ 750 Fiction 50
T003 C++ Codes Rossaine A TDH 300 Text 5
RELATION : ISSUED
Bid QIssued
T008 4
C001 5
F005 2
Write SQL commands for questions i) to v) and state output for questions vi) to ix):-
a) To list the names of books of Text type.
b) To display name and price of books in descending order of price.
c) To display details of books having the highest price.
d) To display maximum price of books based on their type.
e) To display Bid,Bname and QIssued for all books which have been issued.
f) SELECT COUNT(DISTINCT Publ) FROM BOOKS;
g) SEELCT MAX(Quantity) FROM BOOKS;
h) SELECT Bname,Autname FROM BOOKS WHERE Publ=’EPB’.
i) SELECT COUNT(*) FROM BOOKS WHERE PRICE IS BETWEEN 300 AND
400.
TABLE 8: ACTIVITY
Acode Activityname Noofparticipants Prizemoney Scheduledate
1001 Relay 4*100 16 16000 2004-01-23
1005 Highjump 10 10000 2003-12-12
1002 Shortput 12 6000 2004-02-14
1003 Longjump 12 9000 2004-01-01
1008 Discussthrow 10 8000 2004-03-19
a) To display name of all activities with their Acodes in descending order.
b) To display sum of Prizemoney for each Noofparticipants groupings.
c) To display Activityname and Noofparticipants whose schedule date is in the year
2004.
d) To display details of activities whose prizemoney is between 10000 and 20000.
e) To add a new column Coachname to the table ACTIVITY
f) To display the names of the different activities provided.
TABLE 9 : PARTTIME
Empid Ename Stipend Profession Dateofjoining Grade
1002 Karan 1000 Accounts 2007-02-23 A
1005 Manu 2000 Medical 2006-12-12 A
1001 Kamal 800 Clerical 2007-02-06 C
1008 Vikas 500 Clerical 2006-11-18 D
1003 Anil 1200 Medical 2007-08-04 B
a) List the details of all employees sorted by stipend in descending order.
b) To display details of employees whose name starts with ‘A’.
c) To display sum of stipend for each grade groupings.
d) To display details of employees who have joined in the year 2007.
e) To add a new column Noofhoursworked to the table PARTIME.
