DBMS Lab Practical
Lab Practical No.1
Question:
1. (Exercise on retrieving records from the table) EMPLOYEES (Employee_Id, First_Name,
Last_Name, Email, Phone_Number, Hire_Date, Job_Id, Salary,
Commission_Pct,
Manager_Id, Department_Id)
(a) Find out the employee id, names, salaries of all the
employees
(b) List out the employees who works under manager 100
(c) Find the names of the employees who have a salary
greater than or equal to 4800
(d) List out the employees whose last name is ‘AUSTIN’
(e) Find the names of the employees who works in departments
60,70 and 80
(f) Display the unique Manager_Id
Employees table:
1. create an employee’s table with the following fields:
(Emp_id,First_name,Last_name,Phone_No,Hire_date,Job_id,Emp_Salary,Comission_Pct,manager
_id,Department_id)
Query:
create table Employees (Emp_id NUMBER(6),First_name
CHAR(25),Last_name
CHAR(20),Phone_No NUMBER(12),Hire_date DATE,Job_Id
NUMBER(5),Emp_Salary
NUMBER(7),Comission_Pct NUMBER(5),manager_id
NUMBER(5),Department_id
NUMBER(5));
2. Insert five records into the table employees:
Query:
SQL> insert into employees values
(47401,'Rama','Rao',8965324170,'28-Jan-
2003',301,60000,601,100,60);
1 row created.
SQL> insert into employees values
(47402,'Ranga','Reddy',7020321450,'23-Jun-
2004',302,56464,602,101,70);
1 row created.
SQL> insert into employees values
(47403,'Raja','Shekhar',9848002255,'12-aug-
2004',303,58451,603,103,80);
1 row created.
SQL> insert into employees values (47404,'Ravi',' AUSTIN
',9701811356,'30-sep-
2006',304,36520,604,100,90);
Lab Practical No: 2
Question:
(Exercise on updating records in table)Create Client_master
with the following fields(ClientNO,
Name, Address, City, State, bal_due)
(a) Insert five records
(b) Find the names of clients whose bal_due> 5000
(c) Change the bal_due of ClientNO “ C123” to Rs. 5100
(d) Change the name of Client_master to Client12
(e) Display the bal_due heading as “BALANCE” Client master
table:
1. create a client master table with attributes
(Client_no,Client_Name,Client_Address,Client_City,Client_State,Balance_Due)
Query:
create table Client_Master(Client_no varchar(6),Client_Name
char(25),Client_Address
varchar(25),Client_City varchar(20),Client_State
varchar(20),Balance_Due number(20));
a) insert five records into the Client_Master
Query:
sql>insert into CLIENT_MASTER Values('C123','Ramesh','L B
Nagar', 'Hyderabad',
'Telangana', 7000);
sql>insert into CLIENT_MASTER Values('C124', 'Suresh',
'Dilsuknagar', 'Hyderabad',
'Telangana',6000);
sql>insert into CLIENT_MASTER Values('C125','Vignesh','Saroor
nagar', 'Hyderabad',
'Telangana',3500);
sql>insert into CLIENT_MASTER Values('C126','Rajiv','A S
Rao Nagar','Hyderabad',
'Telangana',4500);
sql>insert into CLIENT_MASTER Values('C127','Ranga',
'Vanasthalipuram','Hyderabad',
'Telangana',5478);
1. Display Client Master Table
Query: sql>select * from Client_Master;
b) Find the name of Clients whose balance_due >5000
Query: sql>select Client_Name from Client_Master where
Balance_Due>5000;
c) Change the bal_due of ClientNO “ C123” to Rs. 5100
Query: sql>update Client_Master set Balance_Due=5100
where Client_No='C123';
d ) Change the name of Client_master to Client12 .
Query: sql>rename Client_Master to Client12;
e ) Display the bal_due heading as “BALANCE” Client master
table:
Query: sql> select Client_No, Balance_Due Balance from
Client_Master
Lab Practical No: 3
Question:
Rollback and Commit commands
Create Teacher table with the following fields (Name,
DeptNo, Date of joining, DeptName,
Location, Salary)
(a) Insert five records
(b) Give Increment of 25% salary for Mathematics Department
(c) Perform Rollback command
(d) Give Increment of 15% salary for Commerce Department
(e) Perform commit command
Create Teacher table with the following fields(Id,Name,
DeptNo, Date of joining, DeptName,
Location, Salary)
Query :
SQL> create table teacher (Id number(2) primary key, name
varchar2(20) not null, Deptno
number(2) not null, Deptname varchar2(20) not null, joinDate
date not null, location varchar2(20)
not null, salary number(10,2) not null);
( a ) Insert five records
SQL> insert into teacher values(10,'kiran',4,'computer
science', '03-Jun-2003', 'hyderabad',
50000);
SQL> insert into teacher
values(11,'ramu',5,'mathematics', '13-Jul-2008', 'hyderabad', 40000);
SQL> insert into teacher values(12,'raju',6,'commerce',
'23-Dec-2005', 'abids', 30000);
SQL> insert into teacher
values(13,'manirathnam',6,'commerce', '18-Dec-2006', 'abids', 30000);
SQL> insert into teacher values(14,'sita',6,'commerce',
'28-Aug-2016', 'kingkoti', 23000);
( b ) Give Increment of 25% salary for Mathematics
Department .
Sql> update teacher set salary= salary+(salary * 0.25)
where Deptname= ‘mathematics';
( c ) Perform Rollback command
Sql>rollback;
( d ) Give Increment of 15% salary for Commerce Department
Sql> update teacher set salary= salary+(salary * 0.15)
where Deptname= ‘commerce';
( e ) Perform commit command
Sql>commit;
Lab Practical No: 4
Question:
4. (Exercise on order by and group by clauses) Create Sales
table with the following fields( Sales
No, Salesname, Branch, Salesamount, DOB)
(a) Insert five records
(b) Calculate total salesamount in each branch
(c) Calculate average salesamount in each branch .
(d) Display all the salesmen, DOB who are born in the month
of December as day in character
format i.e. 21-Dec-09
(e) Display the name and DOB of salesman in alphabetical
order of the month.
Sales Table:
Create a Sales Table with the following fields
(Sales_No,Sales_Name,Branch,Sales_Amount,DOB)
Query:
Sql> Create Table Sales(Sales_No number(5),Sales_Name
char(25),Branch char(25),
Sales_Amount number(10), DOB Date);
(a) Insert five records
(b) Calculate total salesamount in each branch
(c) Calculate average salesamount in each branch .
(d) Display all the salesmen, DOB who are born in the month
of December as day in character
format i.e. 21-Dec-09
(e) Display the name and DOB of salesman in alphabetical
order of the month.
Query:
Sql> insert into Sales
VALUES(1020,'AutoMobiles','Hyderabad',68452,'28-JUL-1985');
Sql> insert into Sales
VALUES(1021,'Electronics','Secunderabad',47850,'22-DEC-1995');
Sql> insert into Sales
VALUES(1022,'Electronics','Secunderabad',44500,'03-JUN-1986');
Sql> insert into Sales
VALUES(1023,'AutoMobiles','Hyderabad',74200,'28-SEP-1996');
Sql> insert into Sales
VALUES(1024,'AutoMobiles','Hyderabad',54500,'28-OCT-1984');
Display the Sales table:
Query: sql>select * from Sales;
( b ) Calculate total salesamount in each branch
Query: sql>select branch, sum(Sales_Amount) from sales
group by Branch;
( c ) Calculate average salesamount in each branch .
Query:
Select branch,avg(Sales_Amount) from sales group by Branch;
( d ) Display all the salesmen, DOB who are born in the
month of December as day in character
format i.e. 21-Dec-09
Query:
SQL> select Sales_Name, DOB from sales where
SUBSTR(DOB,4,3)='DEC';
( e ) Display the name and DOB of salesman in alphabetical
order of the month.
Query:
sql>select Sales_Name,to_char(DOB,'MONTH') from sales
Order by to_Char(DOB,'Day');
Lab Practical No: 5
Question:
5. Create an Emp table with the following fields:(EmpNo,
EmpName, Job,Basic, DA, HRA,PF,
GrossPay, NetPay)
(Calculate DA as 30% of Basic and HRA as 40% of Basic)
( a ) Insert Five Records and calculate GrossPay and NetPay.
( b ) Display the employees whose Basic is lowest in each
department .
( c ) If NetPay is less than <Rs. 10,000 add Rs. 1200 as
special allowances .
( d ) Display the employees whose GrossPay lies between
10,000 & 20,000
( e ) Display all the employees who earn maximum salary .
Employee Table
create an employee table with the following fields:
(Emp_No,Emp_ Name, Designation, basic, DA, HRA, PF, Gross
pay, Net pay);
Query:
Sql> create table Employee (Emp_No number(6) primary key,
Emp_Name char(25) not null ,
Designation varchar(25),Emp_Basic number(10,2));
( a ) Insert Five Records and calculate GrossPay and NetPay.
Query:
insert into Employee values(4019,'Talatam
Venkatesh','Director',10000);
insert into Employee values(4039,'Gumaa','Technical
Engineer',15000);
insert into Employee values(4015,'Anudeep Varma','Technical
Engineer',12000);
insert into Employee values(4016,'I Vasudeva
Varma','Technical Engineer',13000);
insert into Employee values(4027,'Uday
Reddy','Director',16000);
insert into Employee values(4006,'Sai
Mani','Director',10000);
#Updating Attributes DA, HRA, PF, Gross pay, Net Pay ?
# Adding coloumn to table and Updating Attributes DA
Sql>alter table employeeadd(Emp_DA number(6));
Sql>update Employeeset Emp_DA=(30/100)*Emp_Basic;
# Adding coloumn to table and Updating Attributes HRA
Sql>alter table employee add(Emp_HRA number(6));
update Employee set Emp_HRA=(40/100)*Emp_Basic;
# Adding coloumn to table and Updating Attributes PF
Sql>alter table employee add (Emp_PF number(6));
Sql> Employee set Emp_pf= Emp_basic*(12/100);
# Adding coloumn to table and Updating Attributes Gross Pay
Sql>alter table employee add (Emp_Grosspay number(6));
Sql>update Employee set Emp_grosspay=
EMp_hra+emp_da+emp_basic;
# Adding coloumn to table and Updating Attributes Net Pay
alter table employee add (Emp_netpay number(6));
update Employee set Emp_netpay=emp_grosspay-emp_pf;
Display the employeetable:
Query: sql>select * from employee;
( b ) Display the employees whose Basic is lowest in each
department .
Query: sql>select min(emp_basic) from employee group by
designation;
( c ) If NetPay is less than <Rs. 10,000 add Rs. 1200 as
special allowances .
Query:
Sql> update employee set emp_netpay=emp_netpay+1200 where
emp_netpay<30000;
( d ) Display the employees whose GrossPay lies between
10,000 & 20,000
Query: sql>select * from employee where emp_grosspay
between 10000 and 20000;
( e ) Display all the employees who earn maximum salary .
Query:
sql>select * from employee where emp_grosspay = (select
max(emp_grosspay) from employee);
Lab Practical No:6
Question:
6. Employee Database An Enterprise wishes to maintain a
database to automate its operations.
Enterprise is divided into certain departments and each
department consists of employees. The
following two tables describes the automation schemas
Dept (deptno, dname, loc)
Emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
a)Update the employee salary by 15%, whose experience is
greater than 10 years.
b)Delete the employees, who completed 30 years of service.
c)Display the manager who is having maximum number of
employees working under him?
d)Create a view, which contain employee names and their
manager
Creating Dept and Emp table
Create Dept table : Dept (deptno, dname, loc)
Sql> create table dept(deptno number(3) primary key,
dname varchar2(30) not null, loc
varchar2(30) not null);
Create Dept table : Emp (empno, ename, job, mgr, hiredate,
sal, comm, deptno)
Sql>create table emp(empno number(3) primary key, ename
varchar2(20) not null, job
varchar2(20) not null, mgr number(3) references emp(empno),
hiredate date not null, sal
number(10,2) not null, comm Number(10,2), deptno number(3));
Inserting data int Dept and Emp tables
Inserting data into Dept table
Sql>insert into dept values(101, ‘FINANCE’,’ SYDNEY’);
Sql>insert into dept values(102, ‘AUDIT’,’ MELBOURNE’);
Sql>insert into dept values(103, ‘MARKETING’,’ PERTH’);
Sql>insert into dept values(104, ‘PRODUCTION’,’
BRISBANE’);
Sql>insert into dept values(105, ‘Humanresource’,’ hyderabad’);
Inserting data into Emp table
Sql> insert into emp (empno, ename ,job, hiredate, sal,
deptno) values
(68319,’KAYLING’,’PRESIDENT’,’18-Nov-1991’,6000.00,101);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(66928,’ BLAZE’,’ MANAGER’,68319,’09 -Jun-1991’,
2750.00,103);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(67832,’ CLARE’,’ MANAGER’,68319,’18-Nov-1991’,
2550.00,101);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(65646,’ JONAS’,’ MANAGER’,68319,’02-Apr-1991’,
2957.00,102);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(67858,’ SCARLET’,’ ANALYST’, 65646,’19-Apr-1997’,
3100.00,102);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(69062,’ FRANK’,’ ANALYST’, 65646,’03-Dec-1991’,
3100.00,102);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(63679,’ SANDRINE’,’ CLERK’, 69062,’18-Dec-1990’,
900.00,102);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal,comm, deptno) values
(64989,’ ADELYN’,’ SALESMAN’, 66928,’20-Feb-1991’,
1700.00,400, 103);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal,comm, deptno) values
(65271,’ WADE’,’ SALESMAN’, 66928,’22-Feb-1991’,
1350.00,600, 103);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal,comm, deptno) values
(66564,’ MADDEN’,’ SALESMAN’, 66928,’28-Sep-1991’,
1350.00,1500, 103);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal,comm, deptno) values
(68454,’ TUCKER’,’ SALESMAN’, 66928,’08-Sep-1991’, 1600.00,
0, 103);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(68736,’ ADNRES’,’ CLERK’, 67858,’23-May-1997’,
1200.00,102);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(69000,’ JULIUS’,’ CLERK’, 66928,’03-Dec-1991’,
1050.00,103);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(69324,’ MARKER’,’ CLERK’, 67832,’23-Jan-1992’,
1400.00,101);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(69924,’ MARKER’,’ CLERK’, 67832,’23-Jan-1992’,
1400.00,104);
a)Update the employee salary by 15%, whose experience is
greater than 30 years.
Query
Sql> update emp set sal=sal+(sal*0.15) where (sysdate-hiredate)/365>30;
b)Delete the employees, who completed 30 years of service.
Sql> delete from emp where (sysdate-hiredate)/365>30;
c)Display the manager who is having maximum number of
employees working under him?
Create view
Sql> create view mgrcount as select mgr, count(empno)
total from emp group by mgr;
Sql> select mgr from mgrcount where total in (select
max(total) from mgrcount);
d)Create a view, which contain employee names and their
manager
Sql> create view employee_manager as select e1.ename
,e2.ename from emp e1, emp e2
where e1.mgr=e2.empno;
Lab Practical No:7
Question:
7. Using Employee Database perform the following queries
a)Determine the names of employee, DBMS Lab Practical
Lab Practical No.1
Question:
1. (Exercise on retrieving records from the table) EMPLOYEES
(Employee_Id, First_Name,
Last_Name, Email, Phone_Number, Hire_Date, Job_Id, Salary,
Commission_Pct,
Manager_Id, Department_Id)
(a) Find out the employee id, names, salaries of all the
employees
(b) List out the employees who works under manager 100
(c) Find the names of the employees who have a salary
greater than or equal to 4800
(d) List out the employees whose last name is ‘AUSTIN’
(e) Find the names of the employees who works in departments
60,70 and 80
(f) Display the unique Manager_Id
Employees table:
1. create an employee’s table with the following fields:
(Emp_id,First_name,Last_name,Phone_No,Hire_date,Job_id,Emp_Salary,Comission_Pct,manager
_id,Department_id)
Query:
create table Employees (Emp_id NUMBER(6),First_name
CHAR(25),Last_name
CHAR(20),Phone_No NUMBER(12),Hire_date DATE,Job_Id
NUMBER(5),Emp_Salary
NUMBER(7),Comission_Pct NUMBER(5),manager_id
NUMBER(5),Department_id
NUMBER(5));
2. Insert five records into the table employees:
Query:
SQL> insert into employees values
(47401,'Rama','Rao',8965324170,'28-Jan-
2003',301,60000,601,100,60);
1 row created.
SQL> insert into employees values
(47402,'Ranga','Reddy',7020321450,'23-Jun-
2004',302,56464,602,101,70);
1 row created.
SQL> insert into employees values
(47403,'Raja','Shekhar',9848002255,'12-aug-
2004',303,58451,603,103,80);
1 row created.
SQL> insert into employees values (47404,'Ravi',' AUSTIN
',9701811356,'30-sep-
2006',304,36520,604,100,90);
Lab Practical No: 2
Question:
(Exercise on updating records in table)Create Client_master
with the following fields(ClientNO,
Name, Address, City, State, bal_due)
(a) Insert five records
(b) Find the names of clients whose bal_due> 5000
(c) Change the bal_due of ClientNO “ C123” to Rs. 5100
(d) Change the name of Client_master to Client12
(e) Display the bal_due heading as “BALANCE” Client master
table:
1. create a client master table with attributes
(Client_no,Client_Name,Client_Address,Client_City,Client_State,Balance_Due)
Query:
create table Client_Master(Client_no varchar(6),Client_Name
char(25),Client_Address
varchar(25),Client_City varchar(20),Client_State varchar(20),Balance_Due
number(20));
a) insert five records into the Client_Master
Query:
sql>insert into CLIENT_MASTER Values('C123','Ramesh','L B
Nagar', 'Hyderabad',
'Telangana', 7000);
sql>insert into CLIENT_MASTER Values('C124', 'Suresh',
'Dilsuknagar', 'Hyderabad',
'Telangana',6000);
sql>insert into CLIENT_MASTER
Values('C125','Vignesh','Saroor nagar', 'Hyderabad',
'Telangana',3500);
sql>insert into CLIENT_MASTER Values('C126','Rajiv','A S
Rao Nagar','Hyderabad',
'Telangana',4500);
sql>insert into CLIENT_MASTER Values('C127','Ranga',
'Vanasthalipuram','Hyderabad',
'Telangana',5478);
1. Display Client Master Table
Query: sql>select * from Client_Master;
b) Find the name of Clients whose balance_due >5000
Query: sql>select Client_Name from Client_Master where
Balance_Due>5000;
c) Change the bal_due of ClientNO “ C123” to Rs. 5100
Query: sql>update Client_Master set Balance_Due=5100
where Client_No='C123';
d ) Change the name of Client_master to Client12 .
Query: sql>rename Client_Master to Client12;
e ) Display the bal_due heading as “BALANCE” Client master
table:
Query: sql> select Client_No, Balance_Due Balance from
Client_Master
Lab Practical No: 3
Question:
Rollback and Commit commands
Create Teacher table with the following fields (Name,
DeptNo, Date of joining, DeptName,
Location, Salary)
(a) Insert five records
(b) Give Increment of 25% salary for Mathematics Department
(c) Perform Rollback command
(d) Give Increment of 15% salary for Commerce Department
(e) Perform commit command
Create Teacher table with the following fields(Id,Name,
DeptNo, Date of joining, DeptName,
Location, Salary)
Query :
SQL> create table teacher (Id number(2) primary key, name
varchar2(20) not null, Deptno
number(2) not null, Deptname varchar2(20) not null, joinDate
date not null, location varchar2(20)
not null, salary number(10,2) not null);
( a ) Insert five records
SQL> insert into teacher values(10,'kiran',4,'computer
science', '03-Jun-2003', 'hyderabad',
50000);
SQL> insert into teacher
values(11,'ramu',5,'mathematics', '13-Jul-2008', 'hyderabad', 40000);
SQL> insert into teacher values(12,'raju',6,'commerce',
'23-Dec-2005', 'abids', 30000);
SQL> insert into teacher
values(13,'manirathnam',6,'commerce', '18-Dec-2006', 'abids', 30000);
SQL> insert into teacher values(14,'sita',6,'commerce',
'28-Aug-2016', 'kingkoti', 23000);
( b ) Give Increment of 25% salary for Mathematics
Department .
Sql> update teacher set salary= salary+(salary * 0.25)
where Deptname= ‘mathematics';
( c ) Perform Rollback command
Sql>rollback;
( d ) Give Increment of 15% salary for Commerce Department
Sql> update teacher set salary= salary+(salary * 0.15)
where Deptname= ‘commerce';
( e ) Perform commit command
Sql>commit;
Lab Practical No: 4
Question:
4. (Exercise on order by and group by clauses) Create Sales
table with the following fields( Sales
No, Salesname, Branch, Salesamount, DOB)
(a) Insert five records
(b) Calculate total salesamount in each branch
(c) Calculate average salesamount in each branch .
(d) Display all the salesmen, DOB who are born in the month
of December as day in character
format i.e. 21-Dec-09
(e) Display the name and DOB of salesman in alphabetical
order of the month.
Sales Table:
Create a Sales Table with the following fields
(Sales_No,Sales_Name,Branch,Sales_Amount,DOB)
Query:
Sql> Create Table Sales(Sales_No number(5),Sales_Name
char(25),Branch char(25),
Sales_Amount number(10), DOB Date);
(a) Insert five records
(b) Calculate total salesamount in each branch
(c) Calculate average salesamount in each branch .
(d) Display all the salesmen, DOB who are born in the month
of December as day in character
format i.e. 21-Dec-09
(e) Display the name and DOB of salesman in alphabetical order
of the month.
Query:
Sql> insert into Sales
VALUES(1020,'AutoMobiles','Hyderabad',68452,'28-JUL-1985');
Sql> insert into Sales
VALUES(1021,'Electronics','Secunderabad',47850,'22-DEC-1995');
Sql> insert into Sales
VALUES(1022,'Electronics','Secunderabad',44500,'03-JUN-1986');
Sql> insert into Sales
VALUES(1023,'AutoMobiles','Hyderabad',74200,'28-SEP-1996');
Sql> insert into Sales
VALUES(1024,'AutoMobiles','Hyderabad',54500,'28-OCT-1984');
Display the Sales table:
Query: sql>select * from Sales;
( b ) Calculate total salesamount in each branch
Query: sql>select branch, sum(Sales_Amount) from sales
group by Branch;
( c ) Calculate average salesamount in each branch .
Query:
Select branch,avg(Sales_Amount) from sales group by Branch;
( d ) Display all the salesmen, DOB who are born in the
month of December as day in character
format i.e. 21-Dec-09
Query:
SQL> select Sales_Name, DOB from sales where
SUBSTR(DOB,4,3)='DEC';
( e ) Display the name and DOB of salesman in alphabetical
order of the month.
Query:
sql>select Sales_Name,to_char(DOB,'MONTH') from sales Order by to_Char(DOB,'Day');
Lab Practical No: 5
Question:
5. Create an Emp table with the following fields:(EmpNo,
EmpName, Job,Basic, DA, HRA,PF,
GrossPay, NetPay)
(Calculate DA as 30% of Basic and HRA as 40% of Basic)
( a ) Insert Five Records and calculate GrossPay and NetPay.
( b ) Display the employees whose Basic is lowest in each
department .
( c ) If NetPay is less than <Rs. 10,000 add Rs. 1200 as
special allowances .
( d ) Display the employees whose GrossPay lies between
10,000 & 20,000
( e ) Display all the employees who earn maximum salary .
Employee Table
create an employee table with the following fields:
(Emp_No,Emp_ Name, Designation, basic, DA, HRA, PF, Gross
pay, Net pay);
Query:
Sql> create table Employee (Emp_No number(6) primary key,
Emp_Name char(25) not null ,
Designation varchar(25),Emp_Basic number(10,2));
( a ) Insert Five Records and calculate GrossPay and NetPay.
Query:
insert into Employee values(4019,'Talatam Venkatesh','Director',10000);
insert into Employee values(4039,'Gumaa','Technical
Engineer',15000);
insert into Employee values(4015,'Anudeep Varma','Technical
Engineer',12000);
insert into Employee values(4016,'I Vasudeva
Varma','Technical Engineer',13000);
insert into Employee values(4027,'Uday
Reddy','Director',16000);
insert into Employee values(4006,'Sai
Mani','Director',10000);
#Updating Attributes DA, HRA, PF, Gross pay, Net Pay ?
# Adding coloumn to table and Updating Attributes DA
Sql>alter table employeeadd(Emp_DA number(6));
Sql>update Employeeset Emp_DA=(30/100)*Emp_Basic;
# Adding coloumn to table and Updating Attributes HRA
Sql>alter table employee add(Emp_HRA number(6));
update Employee set Emp_HRA=(40/100)*Emp_Basic;
# Adding coloumn to table and Updating Attributes PF
Sql>alter table employee add (Emp_PF number(6));
Sql> Employee set Emp_pf= Emp_basic*(12/100);
# Adding coloumn to table and Updating Attributes Gross Pay
Sql>alter table employee add (Emp_Grosspay number(6));
Sql>update Employee set Emp_grosspay=
EMp_hra+emp_da+emp_basic;
# Adding coloumn to table and Updating Attributes Net Pay
alter table employee add (Emp_netpay number(6));
update Employee set Emp_netpay=emp_grosspay-emp_pf;
Display the employeetable:
Query: sql>select * from employee;
( b ) Display the employees whose Basic is lowest in each
department .
Query: sql>select min(emp_basic) from employee group by
designation;
( c ) If NetPay is less than <Rs. 10,000 add Rs. 1200 as
special allowances .
Query:
Sql> update employee set emp_netpay=emp_netpay+1200 where
emp_netpay<30000;
( d ) Display the employees whose GrossPay lies between
10,000 & 20,000
Query: sql>select * from employee where emp_grosspay
between 10000 and 20000;
( e ) Display all the employees who earn maximum salary .
Query:
sql>select * from employee where emp_grosspay = (select
max(emp_grosspay) from employee);
Lab Practical No:6
Question:
6. Employee Database An Enterprise wishes to maintain a
database to automate its operations.
Enterprise is divided into certain departments and each
department consists of employees. The
following two tables describes the automation schemas
Dept (deptno, dname, loc)
Emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
a)Update the employee salary by 15%, whose experience is
greater than 10 years.
b)Delete the employees, who completed 30 years of service.
c)Display the manager who is having maximum number of
employees working under him?
d)Create a view, which contain employee names and their
manager
Creating Dept and Emp table
Create Dept table : Dept (deptno, dname, loc)
Sql> create table dept(deptno number(3) primary key,
dname varchar2(30) not null, loc
varchar2(30) not null);
Create Dept table : Emp (empno, ename, job, mgr, hiredate,
sal, comm, deptno)
Sql>create table emp(empno number(3) primary key, ename
varchar2(20) not null, job
varchar2(20) not null, mgr number(3) references emp(empno),
hiredate date not null, sal
number(10,2) not null, comm Number(10,2), deptno number(3));
Inserting data int Dept and Emp tables
\Inserting data into Dept table
Sql>insert into dept values(101, ‘FINANCE’,’ SYDNEY’);
Sql>insert into dept values(102, ‘AUDIT’,’ MELBOURNE’);
Sql>insert into dept values(103, ‘MARKETING’,’ PERTH’);
Sql>insert into dept values(104, ‘PRODUCTION’,’
BRISBANE’);
Sql>insert into dept values(105, ‘Humanresource’,’
hyderabad’);
Inserting data into Emp table
Sql> insert into emp (empno, ename ,job, hiredate, sal,
deptno) values
(68319,’KAYLING’,’PRESIDENT’,’18-Nov-1991’,6000.00,101);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(66928,’ BLAZE’,’ MANAGER’,68319,’09 -Jun-1991’,
2750.00,103);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(67832,’ CLARE’,’ MANAGER’,68319,’18-Nov-1991’,
2550.00,101);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(65646,’ JONAS’,’ MANAGER’,68319,’02-Apr-1991’,
2957.00,102);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(67858,’ SCARLET’,’ ANALYST’, 65646,’19-Apr-1997’,
3100.00,102);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(69062,’ FRANK’,’ ANALYST’, 65646,’03-Dec-1991’,
3100.00,102);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(63679,’ SANDRINE’,’ CLERK’, 69062,’18-Dec-1990’,
900.00,102);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal,comm, deptno) values
(64989,’ ADELYN’,’ SALESMAN’, 66928,’20-Feb-1991’,
1700.00,400, 103);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal,comm, deptno) values
(65271,’ WADE’,’ SALESMAN’, 66928,’22-Feb-1991’,
1350.00,600, 103);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal,comm, deptno) values
(66564,’ MADDEN’,’ SALESMAN’, 66928,’28-Sep-1991’,
1350.00,1500, 103);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal,comm, deptno) values
(68454,’ TUCKER’,’ SALESMAN’, 66928,’08-Sep-1991’, 1600.00,
0, 103);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(68736,’ ADNRES’,’ CLERK’, 67858,’23-May-1997’,
1200.00,102);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(69000,’ JULIUS’,’ CLERK’, 66928,’03-Dec-1991’,
1050.00,103);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(69324,’ MARKER’,’ CLERK’, 67832,’23-Jan-1992’,
1400.00,101);
Sql> insert into emp (empno, ename ,job,mgr, hiredate,
sal, deptno) values
(69924,’ MARKER’,’ CLERK’, 67832,’23-Jan-1992’,
1400.00,104);
a)Update the employee salary by 15%, whose experience is
greater than 30 years.
Query
Sql> update emp set sal=sal+(sal*0.15) where
(sysdate-hiredate)/365>30;
b)Delete the employees, who completed 30 years of service.
Sql> delete from emp where (sysdate-hiredate)/365>30;
c)Display the manager who is having maximum number of
employees working under him?
Create view
Sql> create view mgrcount as select mgr, count(empno)
total from emp group by mgr;
Sql> select mgr from mgrcount where total in (select
max(total) from mgrcount);
d)Create a view, which contain employee names and their
manager
Sql> create view employee_manager as select e1.ename
,e2.ename from emp e1, emp e2
where e1.mgr=e2.empno;
Lab Practical No:7
Question:
7. Using Employee Database perform the following queries
a)Determine the names of employee, who earn more than their
managers.
b)Determine the names of employees, who take highest salary
in their departments.
c)Determine the employees, who are located at the same
place.
d)Determine the employees, whose total salary is like the
minimum Salary of any department.
e)Determine the department which does not contain any
employees.
a)Determine the names of employee, who earn more than their
managers.
sql>select e1.ename from emp e1, emp e2 where
e1.mgr=e2.empno and e1.sal>e2.sal;
b)Determine the names of employees, who take highest salary
in their departments.
Create a view to store maximum salaries of each department
SQL> create view maxsalaries as select max(sal)
maxsalary, deptno from emp group by deptno;
Displaythe names of employees, who take highest salary in
their departments.who earn more than their managers.
b)Determine the names of employees, who take highest salary
in their departments.
c)Determine the employees, who are located at the same
place.
d)Determine the employees, whose total salary is like the
minimum Salary of any department.
e)Determine the department which does not contain any
employees.
a)Determine the names of employee, who earn more than their
managers.
sql>select e1.ename from emp e1, emp e2 where
e1.mgr=e2.empno and e1.sal>e2.sal;
b)Determine the names of employees, who take highest salary
in their departments.
Create a view to store maximum salaries of each department
SQL> create view maxsalaries as select max(sal)
maxsalary, deptno from emp group by deptno;
Displaythe names of employees, who take highest salary in
their departments.
0 Comments
"Thank you for your message! I appreciate your prompt response and the information you've provided. If you have any further details or if there's anything else I should know, please feel free to let me know. Looking forward to our continued communication!" -- Peaknotes.in