RDBMS ASSIGNMENT

Punjab Technical University

Lab Assignment

Relational Database management System

AN OVERVIEW   OF DBMS AND DATABASE SYSTEM ARCHITECTURE :

1.       Create a table named employee(e#, e_name, hire_date )

SQL> create table employee(e# number(8), e_name char(30),salary number(8), hire_date date);

2.       Insert the following data as the following tables:

E#	e_name	Salary	hire_date
10021	Samar	5000	05/12/08
10056	Anita	18000	05/03/04
10065	Mrinmoy	27000	04/04/01
10011	Rejaul	13000	07/11/05
10093	Akshay	7000	28/04/07
10035	Krishna	23000	07/04/02

SQL>  insert into employee (e#, e_name, salary, hire_date) values (10021,’Samar’,5000,’05-dec-2008’);

SQL>  insert into employee (e#, e_name, salary, hire_date) values (10056,’Anita’,18000,’05-mar-2004’);

SQL>  insert into employee (e#, e_name, salary, hire_date) values (10065,’Mrinmoy’,27000,’05-apr-2001’);

SQL>  insert into employee (e#, e_name, salary, hire_date) values (10011,’Rejaul’,13000,’05-nov-2005’);

SQL>  insert into employee (e#, e_name, salary, hire_date) values (10093,’Akshay’,7000,’05-apr-2007’);

SQL>  insert into employee (e#, e_name, salary, hire_date) values (10035,’Krishna’,23000,’05-apr-2002’);

3.       Find all the name of the employee.

SQL>  select e_name from employee;

4.       Find the Name of the employees, whose salary is greater than 5000.

SQL>  select e_name from employee where salary>5000;

5.       Find the name of the employee, who joins in the month of April.

SQL>  select e_name from employee where TO_CHAR(hire_date,’MM’)=04;

6.        Find the name of the employee, whose names are same but salary is different.

SQL> select distinct(salary),e_name from employee where e_name IN (select e_name from employee group by   e_name having count(e_name)>1);

7.       Create a table named payroll (e#, salary, bonus).

E#	Salary	Bonus
10021	5000	1000
10056	18000	3000
10065	27000	4800
10011	13000	--
10093	7000	800
10035	23000	3500

SQL> create table payroll(e# number(8),salary number(8),bonus number(8));

Value Insertion

SQL>  insert into payroll(e#,  salary, bonus) values (10021,5000,1000);

SQL>  insert into payroll(e#,  salary, bonus) values (10056,18000,3000);

SQL>  insert into payroll(e#,  salary, bonus) values (10065,27000,4800);

SQL>  insert into payroll(e#,  salary) values (10011,13000);

SQL>  insert into payroll(e#,  salary, bonus) values (10093,7000,800);

SQL>  insert into payroll(e#,  salary, bonus) values (10035,23000,3500);

8.       Find the name and hire_date of the employees, who draw bonus, greater than 2000.

SQL>  select E.e_name,E.hire_date from employee E, payroll D where D.bonus>2000;

9.       Find the name of the employees, who join in the year 2005 or 2007 and do not draw any bonus.

SQL>   select E.e_name from employee E, payroll D where D.bonus=’ ’ AND TO CHAR(E.hire date, ‘YY’)=05 OR TO_CHAR(hire_date, ‘YY’)=07 ;

THE NORMALIZATION PROCESS

10.    Drop Table employee and payroll.

SQL> drop table employee;

SQL> drop table payroll;

11.   Create a table named employee (e#. e name, salary, hire_date) again, such that e# is the primary key.

SQL>  create table employee (e#  number(10) primary key, e_name varchar2(30),                                      salary number(8),hire_date date);

Insert values on that table :

SQL>  insert into employee (e#, e_name, salary, hire_date) values (10021,’Samar’,5000,’05-dec-2008’);

SQL>  insert into employee (e#, e_name, salary, hire_date) values (10056,’Anita’,18000,’05-mar-2004’);

SQL>  insert into employee (e#, e_name, salary, hire_date) values (10065,’Mrinmoy’,27000,’05-apr-2001’);

SQL>  insert into employee (e#, e_name, salary, hire_date) values (10011,’Rejaul’,13000,’05-nov-2005’);

SQL>  insert into employee (e#, e_name, salary, hire_date) values (10093,’Akshay’,7000,’05-apr-2007’);

SQL>  insert into employee (e#, e_name, salary, hire_date) values (10035,’Krishna’,23000,’05-apr-2002’);

12.   Create the table payroll (e#, salary, bonus) again such that e# on payroll references e# on employee.

SQL> create table payroll(e# number(8) constraint fkey references employee(e#), salary number(8),bonus number(8));

13.  Insert an employee who joined recently and whose e# is not generated till now (Insertion Anomaly):

SQL> insert into employee (e_name, salary, hire_date) values (‘ VKumar’ ,5000,'05-dec- 2012’);

14.    Insert two employees with the same name with "Arun";

SQL>  insert into employee (e#, e_name, salary, hire_date) values(10001,’Arun',25OOO,'01-dec- 2012’);

SQL> insert into employee (e#, e_name, salary, hire_date) values(10002,’Arun',35OOO,'02-dec- 2012’);

15.  Upgrade Arun salary with Rs. 10000 (Update Anomaly)

SQL>  update employee set salary=10000 where e#=10001;

16.  Delete an employee whose e# is 20054 from the table employee.

SQL>  delete from employee where e#=20054;

17. Find bonus of the employee whose e# is 20054 from payroll (Deletion Anomaly)

SQL> select bonus from payroll where e# = 20054;   (No row deleted)

THE ENTITY – RELATIONSHIP MODEL

18. Draw an entity relationship diagram for hospital management system

      Entities:

Doctor, Staff, patient, Nurse, Outdoor Department, Department, Reception, Enquiry, User, Administrator, Clinical Department.

Show all the relations, weak entity, and strong entity.

INTERACTIVE SQL

19. Create a table Product(product#,p_name,price,shop) in a given table space.

SQL>  create table product(product# varchar2(10), p_name varchar2(15), price number(6), shop varchar2(10));

Insert 7 rows in the table product.

SQL>  insert Into Product (product#, p_name, price, shop) values(‘p001',’Laptop’, 35000,’emall’);

SQL>  insert Into Product (product#, p_name, price, shop)values(‘p002’,’DeskTop’,30000,’emall');

SQL>  insert Into product (product#, p_name, price, shop) values(‘p003’,’UPS’,5000,'eshop');

SQL>  insert Into product (product#, p_name, price, shop) values(‘p004’,'CPU’,15000,'electron');

SQL>  insert into product (product#, p_name, price, shop) values(‘p005','Pendrive',400,'emall');

SQL>  insert Into product (product#, p_name, price, shop) values(‘p006’,’HardDisk’,4500,'eshop');

SQL>  insert into product (product#, p_name, price, shop) values(‘p007','Modem',2000,'icore'),

20. Alter that table by add a column p_qtv for the quantity of product.

SQL>  alter table product add(p_qty number(8));

21. Alter product table by changing the name of the column p_qty by p_quantity.

SQL>  ALTER TABLE product RENAME COLUMN p_qty to p_quantity;

22. Alter table product by adding constraint, such that p_quantity must not be negative.

SQL> ALTER TABLE product modify (p_quantity number(8) check (p_quantity>0));

23. Drop the column shop from the table products :

SQL>   Alter table product Drop column shop;

 VIEWING THE DATA

24. Create table employee (empno, empname, post, salary, bonus, hiredate, department).

SQL>  Create table employee(empno number(8), empname varchar2(30),post varchar2( 15),salary number(6), bonus number(6), hiredate date, department varchar2(20));

Value inserting on that table :

SQL> insert into employee values (01,'Pawan’,'Manager’, 30000, 5000,'01-jan-06’,’IT’);

SQL> insert into employee values (02,'Tom’,'Developer’, 30000, 5000,'11-jan-05’,’Software’);

SQL> insert into employee values (03,'Samar’,'Designer’, 30000, 5000,'21-jan-11’,’software’);

SQL> insert into employee values (04,’Anita’,'Programmer’, 30000, 5000,'01-jan-02’,’software’);

SQL> insert into employee values (05,'Mrinmoy’,'Developer’, 30000, 5000,'15-jan-12’,’Website’);

SQL> insert into employee values (06,'Rejaul’,'Team leader’, 30000, 5000,'01 -feb-12’,’IT’);

SQL> insert into employee values (07,'Akshay’,'Sr. Eng’, 30000, 5000,'01-jan-12’,’HR’);

25. Find the employee name whose total withdrawn (salary + bonus)> 15000.

Select empname from employee where salary + bonus > 15000 ;

26. Find the name of the employee who draws the maximum salary.

 Select empname, salary from employee where salary = (select max(salary) from employee);

27. List name for all employee with a bonus from Rs 1000 to Rs 2000. Sorted by bonus.

 select empname from employee where bonus > 1000 AND BONUS < 2000 order by bonus;

28. List name for all employee with a bonus Rs.1500 and Rs 1700. Sorted by salary

select empname from employee where bonus=1000 AND bonus=1700 order by salary;

29.  find the name of employee whose name start with the letter "M"

 SQL> select empname from employee where empname LIKE 'M%';

30. Find the name of employee whose second letter of name is 'a'.

SQL> select  empname from employee where empname LIKE '_a%';

31.    Find the name and e# of the employee whose second letter of name is ”i” and last letter of name is “a”.

SQL>  select empname, empno from employee where empname LIKE  ‘_i%a%’;

32.    Find the name of the  employee who draw salary, greater than 5000 or bonus greater then 2000.

SQL>  select empname from employee where salary>5000 OR bonus>3000;

33.    Find the number of employee in each department.

SQL>  select department,count(department) from employee GROUP BY department;

34.    List the name of the departments with total spent for salaries higher than 10000.

SQL>  select empname , count(department), from employee where salary > 10000 group by department;

35.    Convert all the hire date to string and show with the employee name.

SQL>  select empname NAME,TO_CHAR(hire_date,'MONTH DD.YYYY') Heiring_Date FROM EMPLOYEE;

36.   show all the employee name. Remove Extra Space if exits, while Showing.

SQL>  select trim(leading ‘ ‘ from empname) from employee;

37.     Create the following three tables:

Muppets(NAME,ANIMAL,COLOR)

Casting(NAME,SHOW, NETWORK)

 Ratings(NETWORK.RANK)

SQL> create table muppets (name varchar2(30),animal varchar2(30),color varchar2(20));

SQL> create table casting (name varchar2(30),show varchar2(30), network varchar2(20));

SQL> create table ratings(network varchar2(20),rank varchar2(20));

Now solve the following:

38.    Get the name of all frog muppets.

SQL> select name from muppets where animal=frog' ;

39.    Gets show casting green muppets.

SQL> select e.show from casting e,muppets d where e.name=d.names AND d.color=‘green';

40.    Get the ranking of all network casting frogs.

SQL> select e.rank from ratings e.castings d where e.network=d.network AND d.name=’frog’:

41.    Create a view myemp, which contains empno, empname, post, department, from the table employee.

SQL> create view myemp AS select empno, empname, post, department from employee;

42.    Change the view : exclude department and include salary.

SQL> create or replace view myemp as select empno,empname,post,salary from employees;

43.    Fetch employees name from the view who draw minimum 5 salaries among all the employee.

SQL>  select e_name,salary from myemp where rownum<=5 order by salary;

44.    Now create an unique index on myemp(empno) including empname.

SQL> create unique index myindex on myemp(empno,empname);