1.Construct the following tables and answer the questions. Table - I (CLIENT_MASTER ).

Write SQL commands for the following:

Q1. List all details from the client_master table for clients whose Bal_due = 0.
Q2. Update table client_master, Change city of Client_no C00004 to Jaipur.
Q3. Retrieve records of clients residing in Mumbai.
Q4. Find the name and address of customer who has placed Order_no 'O19003' and
'O19002' respectively.
Q5. Construct English like sentence from the table client_master ({Customer name} live in
the city of {city}).
Q6. List the client_no, name, city and pincode of clients whose Order_status is "In
process".
Output
SQL> create table client_master(client_no char(6)primary key,name char(30)not null,city char(20) not
null, pincode integer not null, bal_due integer not null);

Table created.

SQL> insert into client_master values('C00001','SMITHA JAIN','Mumbai',400054,15000);

1 row created.

SQL> insert into client_master values('C00002','VANDANA RAO','Chennai',780001,0);

1 row created.

SQL> insert into client_master values('C00003','PREM PUJARI','Mumbai',400057,5000);

1 row created.

SQL> insert into client_master values('C00004','BASU NATH','Mumbai',400056,0);

1 row created.

SQL> insert into client_master values('C00005','RAVI SANKAR','Delhi',100001,2000);

1 row created.

SQL> insert into client_master values('C00006','Roopa','Mumbai',400050,0);

1 row created.

SQL> select * from client_master;

SQL> create table sale_order(order_no char(6)primary key,order_date date,client_no

char(6),salesman_no char(6),delay_date date,order_status char(15),foreign key(client_no) references
client_master(client_no));

Table created.

SQL> insert into sale_order values('O19001','12-jan-13','C00001','S00001','20-jan-13','In process');

1 row created.

SQL> insert into sale_order values('O19002','25-jan-13','C00002','S00002','27-jan-13','Cancelled');

1 row created.

SQL> insert into sale_order values('O46865','18-feb-13','C00003','S00003','20-feb-13','Fulfilled');

1 row created.

SQL> insert into sale_order values('O19003','03-Apr-13','C00001','S00001','07-Apr-13','Fulfilled');

1 row created.

SQL> insert into sale_order values('O46866','20-May-13','C00004','S00002','07-Apr-13','Cancelled');

1 row created.

SQL> insert into sale_order values('O19008','24-May-13','C00005','S00004','26-May-13','In process');

1 row created.

SQL> select * from sale_order;

Q1. List all details from the client_master table for clients whose Bal_due= 0.

SQL> select * from client_master where bal_due=0;

Q2. Update table client_master , Change city of Client_no C00004 to Jaipur.

SQL> update client_master set city='Jaipur' where client_no='C00004';

1 row updated.

SQL> select * from client_master;

Q3. Retrieve records of clients residing in Mumbai.

SQL> select * from client_master where city='Mumbai';

Q4. Find the name and address of customer who has placed Order_no ‘O19003’and ‘O19002
‘respectively.

SQL> select client_master.client_no,name,city,pincode from client_master, sale_order where

client_master.client_no= sale_order.client_no and(order_no='O19003'or order_no='O19002');

Q5. Construct English like sentence from the table client_master ({Customer name} live in the city
of {city}).

SQL> select trim(name)||'live in the city of'||city as address from client_master;

Q6. List the client_no, name, city and pincode of clients whose Order_status is “In process”.

SQL> select client_master.client_no,name,city,pincode from client_master, sale_order where

client_master.client_no= sale_order.client_no and order_status='In process';
2.Construct the following tables and answer the questions.

Write SQL commands for the following:

Q1. Update table EMPLY, add 15 years of age to employee with EMPCODE 'E107'.
Q2. Create a view which shows details of employees whose age is between 35 and 45.
Q3. Retrieve empcode, empname, address, Netpay from EMPLY and PAYSLIP. Display in
ascending order of empcode.
Q4. Create an English like sentence to display the following output from payslip table.
"Employees with empcode E101 draws a basic salary 4000".
Q5. List the names and address of employees drawing a basic salary between 5000 and
7000.
Q6. Add new column Total_Sal in the PAYSLIP table and display all details including total
salary for Employees with Empcode> E107.
Output
SQL> create table emply(empcode varchar(4)primary key,empname varchar(20) not null,address
varchar(30),age number(2)not null,deptcode varchar (4)not null);

Table created.

SQL> insert into emply values('E101','Anjaly','Anjaly Nivas,Thiruvalla',25,'D302');

1 row created.

SQL> insert into emply values('E102','Bobby','Alappuzha',28,'D305');

1 row created.

SQL> insert into emply values('E103','Aravind','Chennai',31,'D305');

1 row created.

SQL> insert into emply values('E104','Lakshmi','Mannar',55,'D707');

1 row created.

SQL> insert into emply values('E105','Daisy','Chithram,Angamaly',35,'D707');

1 row created.

SQL> insert into emply values('E106','Esha','Mumbai',23,'D707');

1 row created.

SQL> insert into emply values('E107','George','Pala',45,'D909');

1 row created.

SQL> insert into emply values('E108','Prakash','Vennikulam',36,'D110');

1 row created.

SQL> insert into emply values('E109','Madhavan','Mynakum,Kottayam',46,'D202');

1 row created.

SQL> insert into emply values('E110','Anugraha','Aparna,Angamaly',47,'D301');

1 row created.

SQL> insert into emply values('E111','Deva','Trichy',38,'D301');

1 row created.

SQL> insert into emply values('E112','Saju','Dhanya,Eranakulam',27,'D202');

1 row created.

SQL> insert into emply values('E113','Priyesh','Priya Nivas,Kottayam',26,'D302');

1 row created.

SQL> select * from emply;

SQL> create table payslip(empcode varchar2 (4) primary key,basic number (6) not null,hra number
(6) not null, da number(6) not null, foreign key(empcode)references emply(empcode));

Table created.

SQL> insert into payslip values('E101',4000,1900,1500);

1 row created.

SQL> insert into payslip values('E102',4500,2200,2000);

1 row created.

SQL> insert into payslip values('E103',5000,2800,2200);

1 row created.

SQL> insert into payslip values('E104',7000,3000,2700);

1 row created.

SQL> insert into payslip values('E105',3000,1500,1200);

1 row created.

SQL> insert into payslip values('E106',5700,3000,2900);

1 row created.

SQL> insert into payslip values('E107',6200,3300,3000);

1 row created.

SQL> insert into payslip values('E108',5700,3700,3000);

1 row created.

SQL> insert into payslip values('E109',7500,4660,545);

1 row created.

SQL> insert into payslip values('E110',5000,300,100);

1 row created.

SQL> insert into payslip values('E111',6000,200,120);

1 row created.

SQL> select * from payslip;

Q1. Update table EMPLY, add 15 years of age to employee with EMPCODE ‘E107’.

SQL> update emply set age=age+25 where empcode='E107';

1 row updated.

SQL> select * from emply;

Q2. Create a view which shows details of employees whose age is between 35 and 45.

SQL> create view empview as select* from emply where age between 35 and 45;

View created.

SQL> select * from empview;

Q3. Retrieve empcode, empname, address, Netpay from EMPY1 and PAYSLIP. Display in ascending
order of empcode.

SQL> select e.empcode, e.empname, e.address, (p.basic + p.hra + p.da) as netpay from emply e,
payslip p where e.empcode= p.empcode order by e.empcode;

Q4. Create an English like sentence to display the following output from payslip table.“Employees
with empcode E101 draws a basic salary 4000”.

SQL> select 'Employee with empcode'||trim(empcode)||'draws a basic salary'||trim(basic) from

payslip;

Q5. List the names and address of employees drawing a basic salary between 5000 and 7000.

SQL> select e.empname as Name,e.address as Address from emply e, payslip p where

e.empcode=p.empcode and p.basic between 5000and 7000;
Q6. Add new column Total_Sal in the PAYSLIP table and display all details including total salary for
Employees with Empcode> E107.

SQL> alter table payslip add(total_sal number(6));

Table altered.

SQL> desc payslip;

SQL> update payslip set total_sal=basic+hra+da;

11 rows updated.

SQL> select*from payslip where empcode>'E107';

SQL> select e.empcode,e.empname,e.address,e.age,e.deptcode,p.basic,p.hra,p.da,p.total_sal from

emply e, payslip p where e.empcode =p.empcode and e.empcode>'E107';
3.Construct the following tables and answer the questions.

Write SQL commands for the following:

Q1. Display records from EMPY1 table for employees whose age is between 25 and 45.
Q2. Retrieve the Deptcode and total no of employees in each department.
Q3. Retrieve empcode, empname, address, deptcode for all employees in "account" and
"stock" departments.
Q4. Display average, maximum and minimum age of employees. Q5. Delete all records
belonging to research department in the EMPY1 table.
output
SQL> create table emply(empcode varchar(4)primary key,empname varchar(20) not null,address
varchar(30),age number(2)not null,deptcode varchar (4)not null);

Table created.

SQL> insert into emply values('E101','Anjaly','Anjaly Nivas,Thiruvalla',25,'D302');

1 row created.

SQL> insert into emply values('E102','Bobby','Alappuzha',28,'D305');

1 row created.

SQL> insert into emply values('E103','Aravind','Chennai',31,'D305');

1 row created.

SQL> insert into emply values('E104','Lakshmi','Mannar',55,'D707');

1 row created.

SQL> insert into emply values('E105','Daisy','Chithram,Angamaly',35,'D707');

1 row created.

SQL> insert into emply values('E106','Esha','Mumbai',23,'D707');

1 row created.

SQL> insert into emply values('E107','George','Pala',45,'D909');

1 row created.

SQL> insert into emply values('E108','Prakash','Vennikulam',36,'D110');

1 row created.

SQL> insert into emply values('E109','Madhavan','Mynakum,Kottayam',46,'D202');

1 row created.

SQL> insert into emply values('E110','Anugraha','Aparna,Angamaly',47,'D301');

1 row created.

SQL> insert into emply values('E111','Deva','Trichy',38,'D301');

1 row created.

SQL> insert into emply values('E112','Saju','Dhanya,Eranakulam',27,'D202');

1 row created.

SQL> insert into emply values('E113','Priyesh','Priya Nivas,Kottayam',26,'D302');

1 row created.

SQL> select * from emply;

SQL> create table dept(deptcode varchar2 (4)primary key ,deptname varchar2 (20));

Table created.

SQL> insert into dept values('D301','sales');

1 row created.

SQL> insert into dept values('D302','Account');

1 row created.

SQL> insert into dept values('D707','Research');

1 row created.

SQL> insert into dept values('D909','Advertising');

1 row created.

SQL> insert into dept values('D202','Stock');

1 row created.

SQL> insert into dept values('D110','Computer');

1 row created.

SQL> insert into dept values('D305','Marketing');

1 row created.
Q1. Display records from EMPLY table for employees whose age is between 25 and 45.
SQL> select* from emply where age between 25 and 45;

Q2. Retrieve the Deptcode and total no of employees in each department

SQL> select deptcode, count(*) as No_of_Employees from emply group by deptcode order by
deptcode;

Q3. Retrieve empcode, empname, address, deptcode for all employees in “account” and “stock”

SQL> select e.empcode,e.empname,e.address,e.deptcode from emply e, dept d where

e.deptcode=d.deptcode and(d.deptname='Account'or d.deptname='Stock');

Q4. Display average, maximum and minimum age of employees.

SQL> select avg(age) as Average_Age, max(age) as Maximum_Age, min(age) as Minimum_Age from

emply;
Q5. Delete all records belonging to research department in the EMPY1 table.

SQL> delete from emply where deptcode in (select deptcode from dept where
deptname='Research');

3 rows deleted.

SQL> select * from emply;

4.Construct the following tables and answer the questions.

Write SQL commands for the following:

Q1. List details of employee with empcode 'E105'.
Q2. Count the number of employees whose age is less than 45.
Q3. Compute NET SALARY (Netsal = Basic + HRA +DA) for employees in the Payslip table.
Q4. Give the employee with empcode 'E110' a bonus of 15% of Basic and update the value
held in column Netsal.
Q5. Display Name, Address, Empcode, Net salary, & age from EMPLY and PAYSLIP tables
respectively.
Q6. Retrieve all information from EMPLY table for employees whose Basic Salary is more
than 6000.
Output
SQL> create table emply(empcode varchar(4)primary key,empname varchar(20) not null,address
varchar(30),age number(2)not null,deptcode varchar (4)not null);

Table created.

SQL> insert into emply values('E101','Anjaly','Anjaly Nivas,Thiruvalla',25,'D302');

1 row created.

SQL> insert into emply values('E102','Bobby','Alappuzha',28,'D305');

1 row created.

SQL> insert into emply values('E103','Aravind','Chennai',31,'D305');

1 row created.

SQL> insert into emply values('E104','Lakshmi','Mannar',55,'D707');

1 row created.

SQL> insert into emply values('E105','Daisy','Chithram,Angamaly',35,'D707');

1 row created.

SQL> insert into emply values('E106','Esha','Mumbai',23,'D707');

1 row created.

SQL> insert into emply values('E107','George','Pala',45,'D909');

1 row created.

SQL> insert into emply values('E108','Prakash','Vennikulam',36,'D110');

1 row created.

SQL> insert into emply values('E109','Madhavan','Mynakum,Kottayam',46,'D202');

1 row created.

SQL> insert into emply values('E110','Anugraha','Aparna,Angamaly',47,'D301');

1 row created.

SQL> insert into emply values('E111','Deva','Trichy',38,'D301');

1 row created.

SQL> insert into emply values('E112','Saju','Dhanya,Eranakulam',27,'D202');

1 row created.
SQL> insert into emply values('E113','Priyesh','Priya Nivas,Kottayam',26,'D302');

1 row created.

SQL> select * from emply;

SQL> create table payslip(empcode varchar2 (4) primary key,basic number (6) not null,hra number
(6) not null, da number(6) not null, foreign key(empcode)references emply(empcode));

Table created.

SQL> insert into payslip values('E101',4000,1900,1500);

1 row created.

SQL> insert into payslip values('E102',4500,2200,2000);

1 row created.

SQL> insert into payslip values('E103',5000,2800,2200);

1 row created.

SQL> insert into payslip values('E104',7000,3000,2700);

1 row created.

SQL> insert into payslip values('E105',3000,1500,1200);

1 row created.

SQL> insert into payslip values('E106',5700,3000,2900);

1 row created.

SQL> insert into payslip values('E107',6200,3300,3000);

1 row created.
SQL> insert into payslip values('E108',5700,3700,3000);

1 row created.

SQL> insert into payslip values('E109',7500,4660,545);

1 row created.

SQL> insert into payslip values('E110',5000,300,100);

1 row created.

SQL> insert into payslip values('E111',6000,200,120);

1 row created.

SQL> select * from payslip;

1.List details of employee with empcode ‘E105’.

SQL> select * from emply where empcode='E105';

2. Count the number of employees whose age is less than 45.

SQL> select count(*) from emply where age<45;

3.Compute net salary (Netsal = Basic + HRA +DA) for employees in the Payslip table.

SQL> alter table payslip add(netsal number(6));

Table altered.

SQL> update payslip set netsal=basic+hra+da;

11 rows updated.

SQL> select* from payslip;

Q4. Give the employee with empcode 'E110' a bonus of 15% of Basic and update the value
held in column Netsal.
SQL> update payslip set netsal=netsal+basic*0.15 where empcode='E110';

1 row updated.

SQL> select * from payslip;

Q5. Display Name, Address, Empcode, Net salary, & age from EMPLY and PAYSLIP tables
respectively.
SQL> select e.empname, e.address, e.empcode, p.netsal, e.age from emply e, payslip p where
e.empcode = p.empcode;
Q6. Retrieve all information from EMPLY table for employees whose Basic Salary is more
than 6000.
SQL> select * from emply where empcode in (select empcode from payslip where basic > 6000);

5. Construct the following tables and answer the questions

Q1. Retrieve the content of the column PRODUCT_CODE and DESCRIPTION from
PRODUCT_MASTER table and compute 5% and 105% of the field SELLING_PRICE for each row
retrieved. Rename SELLING_PRICE * 0.05 as INCREASE_PRICE and sell price * 1.05 as NEW_PRICE.
Q2. Retrieve information from SALES_ORDER_DETAILS for records whose QTY_ORDERED *
PRODUCT_RATE > 8000.

Q3. Retrieve information from PRODUCT_MASTER table and SALES_ORDER_DETAILS table for
product whoseORDER_NO is O19001 and O19008 respectively.

Q4. Retrieve PRODUCT_CODE and DESCRIPTION from PRODUCT_MASTER table,

SALES_ORDER_DETAILS table for product whose QTY_ORDERED < 3.

Q5. Delete all records from PRODUCT_MASTER table corresponding to ORDER_NO = 'O46865'.

Q6. Update SALES_ORDER_DETAILS table, change the PRODUCT_RATE of 'P06734' to the

SELLING_PRICE from PRODUCT_MASTER table.

SQL> create table product_master(product_code varchar(6) primary key, description varchar(20),

qty_on_hand number(4), sell_price number(6));

Table created.

SQL> insert into product_master values('P00001', '1.44 FLOPPIES', 100, 525);

1 row created.

SQL> insert into product_master values('P03453', 'MONITORS', 10,12000);

1 row created.

SQL> insert into product_master values('P06734', 'MOUSE', 20, 1050);

1 row created.
SQL> insert into product_master values('P07865', '1.22 FLOPPIES', 100, 525);

1 row created.

SQL> insert into product_master values('P07868', 'KEYBOARDS', 10,3150);

1 row created.

SQL> insert into product_master values('P07885', 'CD DRIVE', 10,5250);

1 row created.

SQL> insert into product_master values('P07965', '540 HDD', 10, 8400);

1 row created.

SQL> insert into product_master values('P07975', '1.44 DRIVE', 10, 1050);

1 row created.

SQL> insert into product_master values('P08865', '1.44 DRIVE', 2, 1050);

1 row created.

SQL> select * from product_master;

SQL> create table sales_order_details(order_no varchar(6), product_code varchar(6),qty_ordered

number(4),product_rate number(6), foreign key(product_code) references
product_master(product_code));

Table created.

SQL> insert into sales_order_details values('O19001', 'P00001',4, 525);

1 row created.

SQL> insert into sales_order_details values('O19001', 'P07965',2, 8400);

1 row created.

SQL> insert into sales_order_details values('O19001', 'P07885',2, 5250);

1 row created.

SQL> insert into sales_order_details values('O19002', 'P00001',10, 525);

1 row created.

SQL> insert into sales_order_details values('O46865', 'P07868',3, 5250);

1 row created.

SQL> insert into sales_order_details values('O46865', 'P07885',3, 3150);

1 row created.

SQL> insert into sales_order_details values('O46865', 'P00001',10, 525);

1 row created.

SQL> insert into sales_order_details values('O46865', 'P03453',4, 1050);

1 row created.

SQL> insert into sales_order_details values('O19003', 'P03453',2, 1050);

1 row created.

SQL> insert into sales_order_details values('O19003', 'P06734',1, 12000);

1 row created.

SQL> insert into sales_order_details values('O46866', 'P07965',1, 8400);

1 row created.

SQL> insert into sales_order_details values('O46866', 'P07975',1, 1050);

1 row created.

SQL> insert into sales_order_details values('O19008', 'P00001',10, 525);

1 row created.

SQL> insert into sales_order_details values('O19008', 'P07975',5, 1050);

1 row created.

SQL> select * from sales_order_details;

Q1. Retrieve the content of the column PRODUCT_CODE and DESCRIPTION from
PRODUCT_MASTER table and compute 5% and 105% of the field SELLING_PRICE for each row
retrieved. Rename SELLING_PRICE * 0.05 as INCREASE_PRICE and sell price * 1.05 as NEW_PRICE.

SQL> select product_code, description, sell_price * 0.05 as Increase_Price, sell_price * 1.05 as

New_Price from product_master;

Q2. Retrieve information from SALES_ORDER_DETAILS for records whose QTY_ORDERED *

PRODUCT_RATE >8000.

SQL>select * from sales_order_details where qty_ordered * product_rate> 8000;

Q3. Retrieve information from

PRODUCT_MASTER table and SALES_ORDER_DETAILS table for product whoseORDER_NO is
O19001 and O19008 respectively.

SQL> select p.product_code, p.description, p.qty_on_hand, p.sell_price, s.order_no,

s.qty_ordered,s.product_rate from product_master p, sales_order_details s where p.product_code =
s.product_code and (s.order_no = 'O19001' or s.order_no = 'O19008');
Q4. Retrieve PRODUCT_CODE and DESCRIPTION from PRODUCT_MASTER table,
SALES_ORDER_DETAILS table for product whose QTY_ORDERED < 3.

SQL> select p.product_code, p.description from product_master p, sales_order_details s where

p.product_code = s.product_code and s.qty_ordered< 3;

Q5. Delete all records from PRODUCT_MASTER table corresponding to ORDER_NO = 'O46865'.

SQL> delete sales_order_details where order_no = 'O46865';

delete from product_master where product_code in (select product_code from sales_order_details

where order_no = 'O46865');

Q6. Update SALES_ORDER_DETAILS table, change the PRODUCT_RATE of 'P06734' to the

SELLING_PRICE from PRODUCT_MASTER table.
SQL>update sales_order_details set product_rate = (select sell_price from product_master where
product_master.product_code=sales_order_details.product_code)where
sales_order_details.product_code='P06734';