Ex : 6
Create two table’s employee_master and attendance with the following fields:
Employee_master(emp_id, emp_name, emp_add, phone, email, boj, dor).
Attendance (emp_id, wom, mhrs, tuhrs, whrs, thhrs, frhrs, sahrs, suhrs).
Identify primary and foreign keys. Specify the not null property for mandatory keys (doj<dor and valid values of wom<=5)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Create table employee_master
SQL> create table employee_master(
emp_id number(6) primary key,
emp_name varchar2(20) not null,
emp_add varchar2(30) not null,
phone number(10),
email varchar2(30),
doj date not null,
dor date not null,
check (doj<dor));
Table created.
Display table fields
SQL> desc employee_master;
Name Null? Type
----------------------------------------------------
EMP_ID NOT NULL NUMBER(6)
EMP_NAME NOT NULL VARCHAR2(20)
EMP_ADD NOT NULL VARCHAR2(30)
PHONE NOT NULL NUMBER(10)
EMAIL NOT NULL VARCHAR2(30)
DOJ NOT NULL DATE
DOR NOT NULL DATE
Insert values into table
SQL> insert into employee_master
values(2001,'harsha','kavoor',224560,
'harsha@yahoo.com','30-jun-1985','15-jan-1997');
1 row created.
SQL> select * from employee_master;
EMP_ID EMP_NAME EMP_ADD PHONE EMAIL DOJ DOR
-------------------------------------------------------------------------------------------------
2001 harsha kavoor 224560 harsha@yahoo.com 30-JUN-85 15-JAN-97
2002 mahesh bejai 254608 mahesh@yahoo.com 25-MAR-96 10-MAY-10
2003 swarna balmatta 268754 23-JUL-00 12-AUG-16
2004 priya kulur 268945 21-DEC-02 29-DEC-15
2005 hema pvs 258743 15-NOV-96 31-JAN-13
2006 preetham marakada 268745 preethu@yahoo.com 21-OCT-90 25-SEP-14
2007 shilpa madikeri 268475 24-APR-10 30-OCT-20
2008 manasa Bangalore man@www.com 05-JUL-94 05-JUL-19
2009 rashmi kavoor ras@www.com 15-JUL-90 18-SEP-16
Create table attendance
SQL> create table attendance (
emp_id number(6) references employee_master(emp_id)
on delete cascade,
wom number(1) not null,
mhrs number(3) not null,
tuhrs number(3) not null,
whrs number(3) not null,
thhrs number(3) not null,
frhrs number(3) not null,
sahrs number(3) not null,
suhrs number(3) not null,
check (wom<=5));
Table created.
Display table fields
SQL> desc attendance;
Name Null? Type
-----------------------------------------------------
EMP_ID NOT NULL NUMBER(6)
WOM NOT NULL NUMBER(1)
MHRS NOT NULL NUMBER(3)
TUHRS NOT NULL NUMBER(3)
WHRS NOT NULL NUMBER(3)
THHRS NOT NULL NUMBER(3)
FRHRS NOT NULL NUMBER(3)
SAHRS NOT NULL NUMBER(3)
SUHRS NOT NULL NUMBER(3)
Insert values into table
SQL> insert into attendance values(
2002,2,4,6,7,5,8,0,0);
1 row created.
SQL> select * from attendance;
EMP_ID WOM MHRS TUHRS WHRS THHRS FRHRS SAHRS SUHRS
----------------------------------------------------------------------------------------
2002 2 4 6 7 5 8 0 0
2001 1 6 0 11 3 4 0 0
2005 3 6 8 9 0 1 0 0
2001 5 0 0 5 3 0 0 0
2009 2 5 3 0 0 4 0 4
2005 3 0 0 0 4 6 0 4
2001 1 5 6 0 4 2 0 7
Query to display employee name and email_id of all the employees who are working on Sunday.
SQL> select distinct emp_name,email
from employee_master
where emp_id in( select emp_id from attendance
where suhrs>0);
EMP_NAME EMAIL
----------------------------------------
harsha harsha@yahoo.com
hema
rashmi ras@www.com
Query to display the total hours worked by the employee ‘mahesh’.
SQL> select sum(a.mhrs+a.tuhrs+a.whrs+a.thhrs+a.frhrs
+a.sahrs+a.suhrs) "Total hours worked by mahesh"
from employee_master e, attendance a
where e.emp_id=a.emp_id and
e.emp_name in('mahesh');
Total hours worked by mahesh
----------------------------
30
Query to display the names of the employee who never attended the duty so far.
SQL> select distinct emp_id,emp_name
from employee_master
where emp_id not in(select distinct emp_id from
attendance);
EMP_ID EMP_NAME
-------------------------------
2003 swarna
2004 priya
2006 preetham
2007 shilpa
2008 manasa
4. Query to Delete the records of the employees who are already retiered.
SQL> delete from employee_master
where dor<sysdate;
2 rows deleted.
5. Query to Display the names of employee who have total number of hours more than 20 hours a week.
SQL> select distinct e.emp_name from employee_master
e,attendance a
where e.emp_id=a.emp_id and
(a.mhrs+a.tuhrs+a.whrs+a.thhrs+a.frhrs
+a.sahrs+a.suhrs)>20;
EMP_NAME
--------------
harsha
hema
mahesh
No comments:
Post a Comment