Sudhakar
CHAPTER- MySQL Lab exercise
1. Consider the following table named "GYM" with details about Fitness products being sold in the store.
Table Name: GYM
PrCode stores Codes of Products
PrName stores names of Products
(UnitPrice is in Rs.)
PrCode
PrName
UnitPrice
Manufacturer
P101
Cross Trainer
25000
Avon Fitness
P102
TreadMill
32000
AG Fitline
P103
MassageChair
20000
Fit Express
P104
Vibration Trainer
22000
Avon Fitness
P105
Bike
13000
Fit Express
Write SQL statements to do the following:
a) Display the names of all the products in the store.
b) Display the names and unit price of all the products in the store
c) Display the names of all the products with unit price less than Rs.20000.00
d) Display details of all the products with unit price in the range 20000 to 30000
e) Display names of all products by the manufacturer "Fit Express"
f) Display all rows sorted in descending order of unit price.
g) Add a new row for product with the details: "P106","Vibro Exerciser", 23000, manufacturer: "Avon Fitness".
h) Change the Unit Price data of all the rows by applying a 10% discount reduction on all the products.
i)Display details of all products with manufacturer name starting with "A"
2. Consider the following tables Employee and Department. Employee
Ecode
LastName
FirstName
Department
101
Sharma
Amit
Sales
102
Arora
Shiv
Personnel
103
Lakshmi
KS
Accounts
104
Rajlani
Shivika
Accounts
105
Thakral
Satvik
Sales
Department
DepCode
DepName
Budget
101
Sales
200000
102
Personnel
150000
104
Accounts
300000 Write SQL statements to do the following:
a) Display the last names and first names of all employees.
b) Display the Department names of all employees, without duplicates.
c) Display all the details of employees with last name as "Lakshmi".
d)
1. Consider the following table named "GYM" with details about Fitness products being sold in the store.
Table Name: GYM
PrCode stores Codes of Products
PrName stores names of Products
(UnitPrice is in Rs.)
PrCode
PrName
UnitPrice
Manufacturer
P101
Cross Trainer
25000
Avon Fitness
P102
TreadMill
32000
AG Fitline
P103
MassageChair
20000
Fit Express
P104
Vibration Trainer
22000
Avon Fitness
P105
Bike
13000
Fit Express
Write SQL statements to do the following:
a) Display the names of all the products in the store.
b) Display the names and unit price of all the products in the store
c) Display the names of all the products with unit price less than Rs.20000.00
d) Display details of all the products with unit price in the range 20000 to 30000
e) Display names of all products by the manufacturer "Fit Express"
f) Display all rows sorted in descending order of unit price.
g) Add a new row for product with the details: "P106","Vibro Exerciser", 23000, manufacturer: "Avon Fitness".
h) Change the Unit Price data of all the rows by applying a 10% discount reduction on all the products.
i)Display details of all products with manufacturer name starting with "A"
2. Consider the following tables Employee and Department. Employee
Ecode
LastName
FirstName
Department
101
Sharma
Amit
Sales
102
Arora
Shiv
Personnel
103
Lakshmi
KS
Accounts
104
Rajlani
Shivika
Accounts
105
Thakral
Satvik
Sales
Department
DepCode
DepName
Budget
101
Sales
200000
102
Personnel
150000
104
Accounts
300000 Write SQL statements to do the following:
a) Display the last names and first names of all employees.
b) Display the Department names of all employees, without duplicates.
c) Display all the details of employees with last name as "Lakshmi".
d)