Informatics Practices Class XI Ch-9 Question Answers

 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));