DBMS LAB Part-B 3

Ex : 3
Create two tables Department_detail and Employee_detail with the following fields:
Department_detail (DEPTNO, DEPTNAME, TOTEMP, CHARGE/HOUR, BONUS%PER_HOUR)
Employee_detail (EMPNAME, DEPTNO, TOTAL_HOURS)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Create table department_detail

SQL> create table department_detail(
    deptno varchar2(6) primary key,
   deptname varchar2(15) not null,
   totemp number(3) not null,
    charge_hour number(5) not null,
    bonus_per_hr number(3) not null,
    check (deptno like ‘d%’));

Table created.

Display table fields
SQL> desc department_detail;

Name               Null?               Type
---------------------------------------------------------------------
DEPTNO          NOT NULL           VARCHAR2(6)
DEPTNAME          NOT NULL          VARCHAR2(15)
TOTEMP          NOT NULL           NUMBER(3)
CHARGE_HOUR    NOT NULL           NUMBER(5)
BONUS_PER_HR   NOT NULL           NUMBER(3)

Insert values into table
SQL> insert into department_detail values(
   ‘d001’,’accounts’,5,750,10);

1 row created.

SQL> select * from department_detail;

DEPTNO  DEPTNAME  TOTEMP   CHARGE_HOUR   BONUS_PER_HR
------------------------------------------------------------------------------------------
d001         accounts            5              750                10
d002         sales                  10             2000                10
d003         service               5               500                10
d004         purchase            4               600                10

Create table employee_detail
SQL> create table employee_detail(
Empname varchar2(15) not null,
deptno varchar2(6)references department_detail(deptno),
total_hours number(3) not null,
primary key(empname, deptno));

Table created.

Display table fields
SQL> desc employee_detail;

Name              Null?                      Type
--------------------------------------------------------------------------
EMPNAME         NOT NULL            VARCHAR2(15)
DEPTNO          NOT NULL            VARCHAR2(6)
TOTAL_HOURS     NOT NULL            NUMBER(3)

Insert values into table
SQL> insert into employee_detail values(
     'raman','d002',8);

1 row created.

SQL> select * from employee_detail;

EMPNAME         DEPTNO TOTAL_HOURS
---------------------------------------------------------------
raman           d002             8
deekshith       d001             6
ramnath         d003             4
naveen          d002             7
shyam           d004             5

1. Query to display employee name, department number and department name of all employees.

SQL> select e.empname,e.deptno,d.deptname
    from department_detail d,employee_detail e
    where d.deptno=e.deptno;

EMPNAME         DEPTNO DEPTNAME
----------------------------------------------------------
raman           d002   sales
deekshith       d001   accounts
ramnath         d003   service
naveen          d002   sales
shyam           d004   purchase

2. Query to display the department name to which Mr. Ramnath belongs to.

SQL> select d.deptname from department_detail d, 
     employee_detail e
     where d.deptno=e.deptno and
     e.empname='ramnath';

DEPTNAME
-----------------
Service

3. Query to display the bonus got by Mr. Raman.

SQL> select(d.charge_hour*e.total_hours
     *d.bonus_per_hr/100)   "bonus"
    from department_detail d,employee_detail e
    where d.deptno=e.deptno and
    e.empname='raman';

    Bonus
    --------
    1600

Query to display the total number of hours taken by accounts department.

SQL> select sum(e.total_hours) "total hours"
    from department_detail d,employee_detail e
    where d.deptno=e.deptno and
    d.deptname='accounts';

total hours
--------------
6