DBMS LAB 3 SOLUTION
Create table employee(emp_id number(5) primary
key,emp_name varchar2(25));
SQL>desc employee;
Name Null? Type
………………………………………………………………………………………..
EMP_ID NOT
NULL NUMBER(5)
EMP_NAME VARCHAR2(25)
Valid Test
Data:
SQL>insert into employee
values(&emp_id,’&emp_name’);
SQL>select * from employee;
EMP_ID
EMP_NAME
………………………………………………………….
10 Robert
21 Coulthard
30
Fernando Alonso
39
Kartikeyan
87
Kimmi
SQL>create table department(dept_id number(5)
primary key,dept_name varchar2(20));
SQL>desc department;
Name
Null? Type
………………………………………………………………………………………..
DEPT_ID NOT NULL NUMBER(5)
DEPT_NAME
VARCHAR2(20)
SQL>insert
into department values(&dept_id,’&dept_name’);
SQL>select * from department;
DEPT_ID DEPT_NAME
……………………………………………………………………………..
100
sales
101
accounts
102
administration
103
production
104
supervisor
SQL>create table paydetails(emp_id number(5)
references employee(emp_id),dept_id number(5) reerences department(dept_id),basic
number(7,2),deductions number(5,2),additions number(5,2),doj date);
SQL>desc paydetails;
Name
Null? Type
………………………………………………………………………………………..
EMP_ID NUMBER(5)
DEPT_ID NUMBER(5)
BASIC NUMBER(7,2)
DEDUCTIONS NUMBER(5,2)
ADDITIONS NUMBER(5,2)
DOJ DATE
Different
Data Sets:
SQL>insert into paydeatils
values(&emp_id,&dept_id,
&basic,&deductions,&additions,&doj);
SQL>select * from paydeatils;
EMP_ID DEPT_ID BASIC
DEDUCTIONS ADDITIONS DOJ
…………………………………………………………………………………………………………………..
10 101
25023.12 43.09
71.23 08-JAN-93
21 100
10500.29 23.98 40.9 01-JAN-06
30 102 6500.5
30.54 15 06-JUL-97
39 103 9700.45
32.78 65.09 08-AUG-03
87 104 15000
97.66 154.8 24-SEP-04
SQL>create
table payroll(emp_id number(5)references employee(emp_id),pay_date date);
SQL>desc payroll;
Name Null? Type
………………………………………………………………………………………..
EMP_ID NUMBER(5)
PAY_DATE DATE
SQL>insert into payroll
values(&emp_id,’&date’);
SQL>select * from payroll;
EMP_ID
PAY_DATE
………………………………………………………….
10 31-JAN-06
21 03-FEB-06
30 15-JAN-06
39 27-JAN-06
87 04-FEB-06
c) List the employee details department
wise
SQL>select empid,deptid from
paydet;
EMPID DEPTID
…………………………
401
500
402
200
403
600
404
400
405
1200
d)
List
all the employee names who joined after particular date
SQL>select e,empname from employee
e,paydet p where e.empid=p.empid and p.doj>=’05-mar-06’;
EMPNAME
…………………
AVINASH
NITIN
PHALGUN
e)
List the details of employees whose basic salary
is between 10,000 and 20,000
sqL>
Select empid,empname from employee where
salary between 10000 and 20000;
EMPID EMPNAME
…………………………….
402
AKHILA
403
aaaaaaaa
EMPID EMPNAME
…………………………….
AKHILA
f) Give a count of how many employees
are working in each department
SQL>select
count(empid),deptid from paydet group by deptid;
COUNT
(EMPID) DEPTID
………………………………………………………
1 200
1 400
1 500
1 600
1 1200
g) Give a names of the employees whose
netsalary>10,000
SQL>
select empname from employee where empid in(select empid from paydet where
basic-deduction>10000);
EMPNAME
………………
AVINASH
AKHILA
HARISH
NITIN
PHALGUN
h) List the details for an employee_id=5
sql> select * from employee where empid=5;
empid empname
------------------------------------------
5 Coulthard
i) Create a view which lists out the emp_name,
department, basic, dedeuctions, netsalary
j) Create a view which lists the emp_name and
his netsalary
Comments
Post a Comment