DBMS Lab Cycle 5 (employee,department,project,works_in)

Create the following tables


employee

department

project

works_in

CREATE TABLE employee(
ssn NUMBER,
ename VARCHAR2(40) NOT NULL,
desig VARCHAR2(20),
dno NUMBER,
doj DATE,
salary NUMBER,
PRIMARY KEY (ssn)
);
CREATE TABLE department(
dnumber NUMBER,
dname VARCHAR2(20),
loc VARCHAR2(40),
mgrssn NUMBER REFERENCES employee(ssn),
PRIMARY KEY(dnumber)
);
ALTER TABLE employee ADD FOREIGN KEY (dno) REFERENCES department(dnumber);
CREATE TABLE projetc(
pnumber NUMBER,
pname VARCHAR2(20),
dnum NUMBER REFERENCES department(dnumber),
PRIMARY KEY(pnumber)
);

CREATE TABLE works_in(
essn NUMBER REFERENCES employee (ssn),
pno NUMBER REFERENCES project(pnumber),
hrs NUMBER,
PRIMARY KEY(essn,pno)
);
INSERT INTO department (dnumber,dname,loc) 
VALUES (1,'Admin','Banglore');

 INSERT INTO department (dnumber,dname,loc) 
VALUES (2,'RMG','Kochi');

 INSERT INTO department (dnumber,dname,loc) 
VALUES (3,'HR','Chennai');

 INSERT INTO department (dnumber,dname,loc) 
VALUES (4,'Finance','Hyderabad');

 INSERT INTO department (dnumber,dname,loc) 
VALUES (5,'Utilities','Delhi');
INSERT INTO employee (ssn,ename,desig,dno,doj,salary) 
VALUES (101,'Adharsh Sunny','RMG Mngr',2,to_date('26-JUN-2010'),75000);

 INSERT INTO employee (ssn,ename,desig,dno,doj,salary) 
VALUES (102,'Dimal DQ','Admin Mngr',1,to_date('22-DEC-2009'),100000);

 INSERT INTO employee (ssn,ename,desig,dno,doj,salary) 
VALUES (103,'Christin Cahcko','Utilities Mngr',5,to_date('17-MAR-2011'),70000);

 INSERT INTO employee (ssn,ename,desig,dno,doj,salary) 
VALUES (104,'Manuel Cherian','HR Mngr',3,to_date('03-OCT-2009',58000);

 INSERT INTO employee (ssn,ename,desig,dno,doj,salary) 
VALUES (105,'Visakh Harikumar','Finance Mngr',4,to_date('12-JUL-2012'),61000);

 INSERT INTO employee (ssn,ename,desig,dno,doj,salary) 
VALUES (106,'Denny Jose','Snr crdntr',4,to_date('17-JUL-2013',41250);

 INSERT INTO employee (ssn,ename,desig,dno,salary) 
VALUES (107,'Fazil Habeeb','Jnr RMG',2,to_date('07-JAN-2012',),29000);

 INSERT INTO employee (ssn,ename,desig,dno,doj,salary) 
VALUES (108,'Kishore Thomas','Utilities sprvsr',5,to_date('19-FEB-2014'),37000);

 INSERT INTO employee (ssn,ename,desig,dno,doj,salary) 
VALUES (109,'Nirmal Thomas','Jr HR',3,to_date('05-APR-2010'),29000);

 INSERT INTO employee (ssn,ename,desig,dno,doj,salary) 
VALUES (110,'Arun Joji','Admin Exctv',1,to_date('23-NOV-2013'),43500);

 INSERT INTO employee (ssn,ename,desig,dno,doj,salary) 
VALUES (111,'Jewel Lukose','Snr crdntr',4,to_date('28-FEB-2009'),47250);

 INSERT INTO employee (ssn,ename,desig,dno,doj,salary) 
VALUES (112,'Subin Sabu','Snr crdntr',4,to_date('03-MAY-2011'),41000);

 INSERT INTO employee (ssn,ename,desig,dno,doj,salary) 
VALUES (113,'Shijin Philip','Snr crdntr',4,to_date('18-OCT-2013'),43580);

 INSERT INTO employee (ssn,ename,desig,dno,doj,salary) 
VALUES (114,'Albert Abraham','Utilities sprvsr',5,to_date('26-DEC-2015'),32200);

UPDATE department SET mgrssn=&ssn_of_mgr_Of_Admin_dpt WHERE dnumber=1;

 UPDATE department SET mgrssn=&ssn_of_mgr_Of_RMG_dpt WHERE dnumber=2;

 UPDATE department SET mgrssn=&ssn_of_mgr_Of_HR_dpt WHERE dnumber=3;

 UPDATE department SET mgrssn=&ssn_of_mgr_Of_Finance_dpt WHERE dnumber=4;

 UPDATE department SET mgrssn=&ssn_of_mgr_Of_Utilities_dpt WHERE dnumber=5;

INSERT INTO project (pnumber,pname,dnum) 
VALUES (301,'Amex',4);

 INSERT INTO project (pnumber,pname,dnum) 
VALUES (302,'Bancs Trsry',3);

 INSERT INTO project (pnumber,pname,dnum) 
VALUES (303,'Nielsen',5);

 INSERT INTO project (pnumber,pname,dnum) 
VALUES (304,'World Bnk',1);

 INSERT INTO project (pnumber,pname,dnum) 
VALUES (305,'Singapore Arlines',2);

INSERT INTO works_in (essn,pno,hrs) 
VALUES (101,305,null);

 INSERT INTO works_in (essn,pno,hrs) 
VALUES (107,305,null);

 INSERT INTO works_in (essn,pno,hrs) 
VALUES (102,303,null);

 INSERT INTO works_in (essn,pno,hrs) 
VALUES (110,303,null);

 INSERT INTO works_in (essn,pno,hrs) 
VALUES (104,301,null);

 INSERT INTO works_in (essn,pno,hrs) 
 (109,301,null);
INSERT INTO works_in (essn,pno,hrs) 
VALUES (113,304,null);

 INSERT INTO works_in (essn,pno,hrs) 
VALUES (112,304,null);

 INSERT INTO works_in (essn,pno,hrs) 
VALUES (105,304,null);

 INSERT INTO works_in (essn,pno,hrs) 
VALUES (103,302,null);
--> Retrieve all employees in department 5 whose salary is between Rs 30,000 and Rs 40,000.

SELECT e.ename
FROM employee e
LEFT OUTER JOIN department d on d.dnumber=e.dno
WHERE e.salary BETWEEN 30000 AND 40000 AND
d.dnumber=5;

--> Retrieve a list of employees and the projects they are working on, where the departments and the employees within the department are alphabetically by name.

SELECT e.ename,d.dname
FROM employee e
LEFT OUTER JOIN department d on e.dno=d.dnumber
ORDER BY d.dname ASC,e.ename ASC;

--> Retrieve the project number, the project name, and the number of employees who work in each  project.

SELECT p.pnumber,p.pname,count(e.ssn)
FROM works_in w
LEFT OUTER JOIN project p on w.pno=p.pnumber
LEFT OUTER JOIN employee e on w.essn=e.ssn
GROUP BY p.pname,p.pnumber;

--> For the project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project.

 SELECT p.pnumber,p.pname,count(e.ssn)
FROM works_in w
LEFT OUTER JOIN project p on w.pno=p.pnumber
LEFT OUTER JOIN employee e on w.essn=e.ssn
GROUP BY p.pname,p.pnumber
HAVING count(e.ssn) > 2;

--> For each project, retrieve the project number, the project name, and the number of employees from department 5 who work on the project.

SELECT p.pnumber,p.pname,d.dnumber,count(e.ssn)
FROM project p
LEFT OUTER JOIN department d on d.dnumber=p.dnum
LEFT OUTER JOIN employee e on e.dno=p.dnum
GROUP BY p.pname,p.pnumber,d.dnumber
HAVING d.dnumber=5;

--> For the departments having more than five employees, display the department id and the number and details of employees earning more than Rs 40,000 per month.

SELECT d.dname,d.dnumber,e.ssn,e.ename,e.desig,e.doj,e.salary
FROM department d,employee e 
WHERE 
(SELECT COUNT(*) FROM employee e WHERE e.dno = d.dnumber AND e.salary > 40000) > 4 AND 
e.dno=d.dnumber
GROUP BY d.dname,d.dnumber,e.ssn,e.ename,e.desig,e.doj,e.salary;

--> Display the employee details, departments that the departments are same in both the emp and dept. (Equi-join)

SELECT *
FROM employee e,department d
WHERE e.dno=d.dnumber;
--> Display the employee details, departments that the departments are not same in both the emp and dept. (Non Equi-join)

SELECT *
FROM employee e,department d
WHERE NOT(e.dno=d.dnumber);

--> Perform Left outer join on the emp and dept tables.

SELECT *
FROM employee e
LEFT OUTER JOIN department d ON e.dno=d.dnumber;
--> Perform Right outer join on the emp and dept tables.

 SELECT *
FROM employee e
RIGHT OUTER JOIN department d ON e.dno=d.dnumber;
--> Perform inner join on the emp and dept tables.

SELECT *
FROM employee e
INNER JOIN department d ON e.dno=d.dnumber;

Comments

Post a Comment

Popular posts from this blog

DBMS Lab Cycle 6 (client_master, product_master, salesman_master, sales_order, sales_order_details)

Image
CREATE THE FOLLOWING TABLES AND INSERT THE VALUES client_master product_master salesman_master sales_order sales_order_details CREATE TABLE client_master( client_no VARCHAR2(10), name VARCHAR2(50), city VARCHAR2(40), pincode NUMBER(6), state VARCHAR2(40), bal_due NUMBER(9,2), PRIMARY KEY(client_no) ); INSERT INTO client_master VALUES ('C00001','Anup Dalal','Mumbai',400054,'Maharashtra',15000); INSERT INTO client_master  VALUES ('C00002','Vandana Srivastava','Chennai',600018,'Tamil Nadu',0); INSERT INTO client_master  VALUES ('C00003','Kaustubh Khare','Mumbai',400056,'Maharashtra',5000); INSERT INTO client_master  VALUES ('C00004','Ashok Agarwal','Mumbai',400057,'Maharashtra',0); INSERT INTO client_master  VALUES ('C00005','Vivek Misra','Delhi...
Read more

DBMS Lab Cycle 4 (sailor,boat,reserve)

Image
Create the following tables and execute the queries given below sailor boat reserve CREATE TABLE sailors( sid NUMBER(3), sname varchar2(100), rating NUMBER(2), age NUMBER(3), PRIMARY KEY(sid) ); INSERT INTO sailors VALUES(22,'Dustin',7,45); INSERT INTO sailors VALUES(29,'Brutas',1,33); INSERT INTO sailors VALUES(31,'Luber',8,55); INSERT INTO sailors VALUES(32,'Andy',8,25); INSERT INTO sailors VALUES(58,'Rusty',10,35); INSERT INTO sailors VALUES(64,'Horatio',9,35); INSERT INTO sailors VALUES(71,'Zorba',10,16); INSERT INTO sailors VALUES(74,'Horatio',9,35); INSERT INTO sailors VALUES(85,'Art',3,25.5); INSERT INTO sailors VALUES(95,'Bob',3,63.5); CREATE TABLE boats( bid NUMBER(3), bname VARCHAR2(50), color VARCHAR2(25), PRIMARY KEY(bid) ); INSERT INTO boats VALUES(101,'Interlake','Blue'); INSERT INTO boats VALUES(102,'Inter...
Read more

DBMS Lab Cycle 2 (student_info)

Image
1.  Create the table STUDENT_INFO with Columns: Sid, Stud_name & stude_score . create table STUDENT_INFO( sid NUMBER(3) NOT NULL, stud_name VARCHAR2(100) NOT NULL, stud_score NUMBER(3) DEFAULT 20, primary key(sid) ); -->   Insert  values into STUDENT_INFO with the following constraints: Stud name NOT NULL and stude_score DEFAULT value of 20 . INSERT INTO STUDENT_INFO (sid,stud_name) VALUES(9,'Adharsh Sunny'); INSERT INTO STUDENT_INFO VALUES(2'Dimal Mathew',200); INSERT INTO STUDENT_INFO VALUES(3,'Arun Joji',170); INSERT INTO STUDENT_INFO VALUES(4,'MAnuel Cherian',180); INSERT INTO STUDENT_INFO VALUES(5,'Christin Chacko',90); INSERT INTO STUDENT_INFO VALUES(6,'Jithu Sebastian',110); INSERT INTO STUDENT_INFO VALUES(7,'Fazil Habeeb',160); INSERT INTO STUDENT_INFO VALUES(8,'Visakh harikumar',101); -->   Update stude_score by adding a value of 5 to stude_score in the table STUDENT_...
Read more