Lab Exercise
Ques.1)
Consider the following table name “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
|
Tread
mill
|
32000
|
AG
Fitness
|
P103
|
Massage
Chair
|
20000
|
Fit
Fitness
|
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.
Select PrName from gym;
b)
Display the
names and unit price of all the products in the store
Select PrName ,UnitPrice from gym;
c)
Display the
names of all the products with unit price less than Rs.20000.00
Select * from gym where
unitprice<20000;
d)
Display details
of all the products with unit price in the range 20000 to 30000
Select * from gym where unitprice between 20000 and 30000;
e)
Display names of
all the products by the manufacturer “Fit Express”
Select * from gym where manufacturer=”Fit Express”;
f)
Display all rows
sorted in descending order of unit price.
Select * from gym order by unit price desc;
g)
Add a new row
for product with details:”P106”,”Vibro Exerciser”,23000,manufacturer:”Avon
Fitness”.
Insert into gym values(“P106”,”Vibro Exerciser”, 23000,”Avon Fitness”);
h)
Change the Unit
Price data of all the rows by applying a
10% discount reduction on all the products.
Update gym set
unitprice=unitprice-unitprice*1/10;
i)
Display details
of all the products with manufacturer
name starting with “A”
Select * from gym where manufacturer like’g%’;
Ques.2) Consider the following table 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 the employees.
Select last name, first name from employee;
b)
Display the
department names all the employees, without duplicates.
Select distinct department from employee;
c)
Display all the
details of employees with last name as ”Lakshmi”.
Select * from employee where last name=’Lakshmi’;
d)
Display all the
details of employees whose last name is “Rajlani” or ”Sharma”.
Select * from employee where last name=’Rajlani’ and last name =’Sharma’;
e)
Display the
codes and first names of all the employees of ‘Accounts’ department.
Select ecode, firstname from employee where department=’accounts’;
f)
Display
department names of departments with budget above 18000.
Select department from department where budget>18000;
g)
Display all the
details of employees whose First name begins with “S”.
Select * from employee where first name like’s%’;
h)
Display
department details(from Department table) in descending order of Budget amount.
Select * from department where budget desc;
i)
Change the
Department name “Sales” to “Marketing” everywhere in the table “Employee” and
“Department”
Update employee set department=’Marketing’ where department=’sales’;
Update department set department=’Marketing’ where department=’sales’;
j)
Add a new row
with appropriate data values in department table.
Insert into department values(103,”Management”,50000)
k)
Create table
Department with columns of appropriate data types.
Create table department (DepCode integer(3),DepName varchar(10),Budget
integer(8));
