MYSQL PRACTICAL QUESTIONS

SNO TOPIC SIGNATURE

1. MYSQL Question I
Create a table Product with following structure:P_ID CHAR(4) PRIMARY
KEY, PRODUCTNAME VARCHAR(12), MANUFACTURER CHAR(3), PRICE
INTEGER, EXPIRYDATE DATE.
1. Add 5 records (refer to page 8.64)
2. Write queries to do the following:
2.1 To display Productname, Manufacturer, Expirydate for all
products that expired on or before ‘2010-12-31’
2.2 To display all records with Manufacturer starting with ‘L’ .
2.3 To display all records in descending order of price.
2.4 To display number of unique Productname existing in the
table.
2.5 To display average of price, minimum price , maximum price
and count Productname wise.(Hint : Use Group By) .
Increase price of products by 10%.
2.
Create MYSQL Question II
Create a table Client with following structure:
C_ID integer (Primary Key), ClientName varchar(15),City varchar(10),
P_ID char(4)
1. Add 5 records as given on page 8.64
2. Write queries to do the following:
2.1 To display C_ID, ClientName, City and ProductName for all
Clients residing in Delhi.
2.2 To add a field Balance Numeric(8,2) with a default value 12000.
2.3 To show C_ID, ClientName and Monthly Balance(Balance/12) .
Using aliasing for MonthlyNbalance.
2.4 To show records of all clients with ClientName consisting of 4
characters.
2.5 Delete all records of clients with City=’Bengaluru”
6. MYSQL Question III
Create the following tables ACTIVITY and COACH. Write SQL commands
for the statements (i) to (v)
Table: ACTIVITY

ACode ActivityName ParticipantsNum PrizeMoney ScheduleDate

1001 Relay 100x4 16 10000 23-Jan-2004

1002 High jump 10 12000 12-Dec-2003

1003 Shot Put 12 8000 14-Feb-2004

1005 Long Jump 12 9000 01-Jan-2004

1008 Discuss Throw 10 15000 19-Mar-2004

 PCode Name ACode

Table: COACH 1 Ahmad Hussain 1001

2 Ravinder 1008

3 Janila 1001

4 Naaz 1003

i) To display the name of all activities with their Acodes in

descending order.
ii) To display sum of PrizeMoney for each of the Number of
participants groupings (as shown in column
ParticipantsNum 10,12,16)
iii) To display the coach’s name ,Activity name and ACodes from
the table COACH and ACTIVITY
iv) To display the content of the GAMES table whose
ScheduleDate earlier than 01/01/2004 in ascending order of
ParticipantNum.
(v) To display Maximum Scheduledate and Minimum
Scheduledate from table Activity

4. MYSQL Question IV
Create the following tables Customer and Bill. Write SQL commands for
the statements (i) to (v)
Table : Customer
Cust_Id Cust_Name Address Phone_no City
C007 Pritam Sharma 12,M.G Road 71274250 Bangalore
C008 Sutopa 14/1 Pritam Pura 41206819 Delhi
C010 Anurag Basu 15A, Park Road 61281921 Kolkata
C012 Hrithik 7/2 Vasant Kunj 26121949 Delhi
C013 Firoz Shah 2, Servamali road 25014192 Bangalore
C025 Vinod Nagpal 46-a Navi Mumbai 64104944 Mumbai
C027 Sameer 41,Dwarka 42101619 Delhi
C002 Pasunjit Bose 16/A K.G Marg 27220012 Bangalore
C035 Aamina Begum 13/A Versova 41612181 Mumbai

Table : BILL
Ord_id Cust_id Item Ord_date Qty Price
7002 C007 Pizza 20-11-07 1 249.50
7003 C013 Garlic Bread 24-10-05 3 75.75
7004 C012 Pasta 03-03-07 4 173.00
7005 C010 Ice Cream 01-01-08 30 195.75
7006 C035 Pizza 02-03-06 4 249.50
7009 C035 Garlic Bread 02-03-08 2 75.75
7010 C013 Brownie 04-05-07 4 40.50
7011 C014 Ice Cream 02-06-08 5 195.75
7012 C002 Pizza 01-02-08 7 249.50
(i) Display a report containing cust_id, cust_name,
Item,qty,price and bill amount. Bill amount is calculated
as the sum of qty*price
(ii) Display how many customers have ordered Pizza in the
month of August
 (iii) Display the name of customer along with their city in
alphabetical order of city
(iv) Insert into table customer a column City and enter
Delhi to all of them.
(v) To display how many clients are there in each city.

5. MYSQL Question V
Create the tables BOOKS and ISSUES and write the SQL commands for
(i) to (v)

(i) To show Book name, Author name and Price of books of ABC publisher.
(ii) To display the details of the books in descending order of their price.
(iii) To decrease the Qty_Issued from ISSUES table by 3 (all rows must
decrease).
(iv) To display the Book Id, Book name, Publisher, Price, Qty, Qty_Issued
from both the tables with their matching Book ID.
(v) To display the Price of Books where Quantity Issued is equal to 5.

Table: ISSUES

Book_ID Qty_Issued

L02 13

L04 5

L05 21

Table: BOOKS
Book_ID BookName AuthorName Publisher Price Qty

L01 Maths Raman ABC 70 20

L02 Science Agarkar DEF 90 15

L03 Social Suresh XYZ 85 30

L04 Computer Sumita ABC 75 7

L05 Telugu Nannayya DEF 60 25

L06 English Wordsworth DEF 55 12