Term Work1 :
Create a data model for the given business scensrio and prepare schema using
appropriate SQL structures . Insert data to check validate the following integrity
constraints
1.Entity integrity
2.Row integrity
3.Referential integrity
SQL>create table employee
(
empno integer not null
constraints EMPLOYEE_PK_VIOLATION
primary key,
empname char(25) not null,
sex char(1) not null
constraints EMPLOYEE_SEX_VIOLAION
check(sex in('m','f')),
phone integer null,
dob date default '15-apr-68' not null
);
Table created.
SQL> create table project
(
projno integer not null,
projectname char(20) not null,
cheifarchitect char(20) default 'UPK' not null,
constraints PROJECT_PK_VIOLATION
primary key(projno)
);
Table created.
SQL> create table assigned_to
(
empno integer not null,
projno integer not null,
constraints ASSIGNED_TO_PK_VIOLATION
primary key(empno,projno),
constraints ASSIGNED_TO_FK_EMP_VIOLATION
foreign key(empno)
references employee,
constraints ASSIGNED_TO_FK_PRJ_VIOLATION
foreign key(projno)
references project
);
Table created.
SQL> insert into employee values(01,'Ananya','f',777,'07-sep-04');
1 row created.
SQL> insert into employee values(02,'Aditi','f',666,'06-jun-06');
1 row created.
SQL> insert into employee values(03,'Amay','m',333,'13-apr-88');
1 row created.
SQL> insert into employee values(04,'Akshata','f',141,'14-nov-98');
1 row created.
SQL> insert into employee (empno,empname,sex,phone) values(05,'Mani','m',007);
1 row created.
SQL> select * from employee;
EMPNO EMPNAME S PHONE DOB
---------- ------------------------- - ---------- ---------
1 Ananya f 777 07-SEP-04
2 Aditi f 666 06-JUN-06
3 Amay m 333 13-APR-88
4 Akshata f 141 14-NOV-98
5 Mani m 7 15-APR-68
SQL> insert into project values(01,'DBMS','UPK');
1 row created.
SQL> insert into project values(02,'AIML','PK');
1 row created.
SQL> insert into project values(03,'Software','YS');
1 row created.
SQL> insert into project values(04,'WebTechnology','IRM');
1 row created.
SQL> insert into project values(05,'IOT','NK');
1 row created.
SQL> select * from project;
PROJNO PROJECTNAME CHEIFARCHITECT
---------- -------------------- --------------------
1 DBMS UPK
2 AIML PK
3 Software YS
4 WebTechnology IRM
5 IOT NK
SQL> insert into assigned_to values(1,1);
1 row created.
SQL> insert into assigned_to values(2,1);
1 row created.
SQL> insert into assigned_to values(4,2);
1 row created.
SQL> insert into assigned_to values(3,1);
1 row created.
SQL> insert into assigned_to values(5,4);
1 row created.
SQL> insert into assigned_to values(2,2);
1 row created.
SQL> insert into assigned_to values(5,3);
1 row created.
SQL> insert into assigned_to values(4,5);
1 row created.
SQL> insert into assigned_to values(3,2);
1 row created.
SQL> select * from assigned_to;
EMPNO PROJNO
---------- ----------
1 1
2 1
4 2
3 1
5 4
2 2
5 3
4 5
3 2
9 rows selected.
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
----------------------
Term Work 2:
a. Write SQL statement to obtain the empID# of all employees working on Project =1.
SQL>select e.empno from employee e,assigned_to at
where e.empno = at.empno
and projno =1;
EMPNO
----------
1
2
3
b. WSQL to get the details of employee working on project 1.
SQL>select e.* from employee e , assigned_to at
where e.empno=at.empno
and projno=1;
EMPNO EMPNAME S PHONE DOB
---------- ------------------------- - ---------- ---------
1 Ananya f 777 07-SEP-04
2 Aditi f 666 06-JUN-06
3 Amay m 333 13-APR-88
c. WSQL to get the details of the employee who is working on 'DBMS' project
SQL> select e.* from employee e,assigned_to at, project pr
2 where e.empno=at.empno
3 and pr.projno=at.projno
4 and projectname='DBMS';
EMPNO EMPNAME S PHONE DOB
---------- ------------------------- - ---------- ---------
1 Ananya f 777 07-SEP-04
2 Aditi f 666 06-JUN-06
3 Amay m 333 13-APR-88
d. WSQL to get employee details of employee working on both project 1 and 2
SQL> select e.* from employee e, assigned_to at
2 where e.empno=at.empno
3 and projno=1
4 intersect
5 select e.* from employee e, assigned_to at
6 where e.empno=at.empno
7 and projno=2;
EMPNO EMPNAME S PHONE DOB
---------- ------------------------- - ---------- ---------
2 Aditi f 666 06-JUN-06
3 Amay m 333 13-APR-88
e. WSQL to get the details of the employee who is working on either project 1 or 2.
;
SQL> select e.* from employee e,assigned_to at
where e.empno=at.empno
and (projno=1 AND projno=2);
EMPNO EMPNAME S PHONE DOB
---------- ------------------------- - ---------- ---------
1 Ananya f 777 07-SEP-04
2 Aditi f 666 06-JUN-06
4 Akshata f 141 14-NOV-98
3 Amay m 333 13-APR-88
2 Aditi f 666 06-JUN-06
3 Amay m 333 13-APR-88
-----------------------------------------------------------------------------------
------------------
T3:Modify the schema to store the information about fine to be paid by employees
create table employee_fine
(
empNo int not null,
fine Number ,
constraints VIOLATION_PK_CONSTRAINTS
primary key(empNo,fine),
foreign key(empNo)
references employee
);
Table created.
T4 :Modify the schema to store the details of dependents for all employees if
exixts.
1.List all employees who have 2 dependents
2.List all the employees who have their mother as dependent
create table dependents
(
empNo int not null,
DepName char(45) not null,
DepRelation char(45) not null,
constraints EMP_NO_FK_VIOLATION
foreign key(empNo)
references employee,
constraints DEPENDENT_PK_VIOLATION
primary key(empNo,DepName)
);
Table created.
1.
select e.empName
from employee e
Join dependents d on e.empNo = d.empNo
group by e.empNo,e.empName
having count(d.empNo) = 2;
EMPNAME
-------------------------
Ananya
2.
select e.empName from employee e,dependents d
2 where e.empNo = d.empNo
3 and d.deprelation='Mother';
EMPNAME
-------------------------
Ananya
T5 .
T6.
***********************************************************************************
***************
T7.WSQL statement to display name and DOB of all employees who are on the bench
select e.empName from employee e
MINUS
select e.empName from employee e,assigned_to at
where e.empNo=at.empNo
-----------------------------------------------------------------------------------
-----
T8 . WSQL statement to display the name of all employees working on all projects
select e.empName from employee e
join assigned_to at on e.empNo=at.empNo
group by e.empNo,e.empName
having count(distinct at.projNo)=(select count(*) from project);
***********************************************************************************
********
T9 . Display name of all eployees working on atleast all of the project that
employee 1 is working
select empName
from employee
where empNo
in(select empNo from assigned_to where projNo
in(select projNo from assigned_to where empNo=1));
-----------------------------------------------------------------------------------
--------
T10 .Display the details of senior most 3 employees.
SELECT *
FROM (SELECT * FROM employee ORDER BY dob)
WHERE ROWNUM <= 3;
-----------------------------------------------------------------------------------
-----
T11.Find for each employee is penalty incurred
select e.empNo,sum(ef.fine)
from employee e
join employee_fine ef on e.empNo = ef.empNo
group by ef.empNo;
EMPNO SUM(EF.FINE)
---------- ------------
1 490
2 145
3 855
4 340
===================================================================================
======
T12.Display the no of employees working under each project having count>3;SELECT
select COUNT(a.empNo) projNo
FROM assigned_to a
JOIN project p ON p.projNo = a.projNo
GROUP BY p.projNo, p.projectName
HAVING COUNT(a.empNo) >= 3;
===================================================================================
======
T13.Study of Order by clause
a)Order By
SQL>select * from employee
order by dob asc;
EMPNO EMPNAME S PHONE DOB
---------- ------------------------- - ---------- ---------
5 Mani m 7 15-APR-68
3 Amay m 333 13-APR-88
6 Anusha f 778 09-MAR-94
4 Akshata f 141 14-NOV-98
1 Ananya f 777 07-SEP-04
2 Aditi f 666 06-JUN-06
6 rows selected.
b)Alter
SQL>Alter table employee
add email char(30);
Table altered.
SQL>
SQL> select * from employee;
EMPNO EMPNAME S PHONE DOB
---------- ------------------------- - ---------- ---------
EMAIL
------------------------------
1 Ananya f 777 07-SEP-04
2 Aditi f 666 06-JUN-06
3 Amay m 333 13-APR-88
EMPNO EMPNAME S PHONE DOB
---------- ------------------------- - ---------- ---------
EMAIL
------------------------------
4 Akshata f 141 14-NOV-98
5 Mani m 7 15-APR-68
6 Anusha f 778 09-MAR-94
6 rows selected.
T14. Study of statistical functions'
a) Min()
SQL>select Min(salary) from employee;
MIN(SALARY)
-----------
14500
SQL> select Max(salary) from employee;
b) Max()
MAX(SALARY)
-----------
55000
c) Sum()
SQL> select sum(salary) from employee;
SUM(SALARY)
-----------
187500
d) Avg()
SQL> select Avg(salary) from employee;
AVG(SALARY)
-----------
31250
e) variance()
SQL> select variance(salary) from employee;
VARIANCE(SALARY)
----------------
229975000
f)stddev()
SQL> select stddev(salary) from employee;
STDDEV(SALARY)
--------------
15164.9266
===================================================================================
========
T15.Study of clauses
a) between
SQL> select * from employee
where salary between 25000 and 40000;
EMPNO EMPNAME S PHONE DOB SALARY
---------- ------------------------- - ---------- --------- ----------
2 Aditi f 666 06-JUN-06 25000
3 Amay m 333 13-APR-88 40000
5 Mani m 7 15-APR-68 35000
b) LIKE
SQL>select *
from tenant
where name LIKE 'G%';
TID NAME ADHARNO CONTACT
---------- --------------------------------------------- ---------- ----------
6 Gagan 1.2346E+11 9234567876
c) ALL
select
d)ANY
select projectName
from project
where projNo = ANY
(
select projNo
from assigned_to
where empNo = 2 OR empNo=4);
SQL>
PROJECTNAME
--------------------
DBMS
AIML
IOT
e) IN
'SQL> select * from assigned_to
where ProjNo in 1;
EMPNO PROJNO
---------- ----------
1 1
2 1
3 1
4 1
5 1
f) EXISTS
SQL> select name
from tenant
where exists
(
select *
from holdProperty
where Tenant.TID = HoldProperty.TID);
NAME
---------------------------------------------
David
Virat
Rohit
Smriti
Gagan
Akshata Gaonkar
6 rows selected.
g)RowNum
SQL> select *
from employee
where rownum<=3;
EMPNO EMPNAME S PHONE DOB SALARY
---------- ------------------------- - ---------- --------- ----------
1 Ananya f 777 07-SEP-04 14500
2 Aditi f 666 06-JUN-06 25000
3 Amay m 333 13-APR-88 40000
h)count()
SQL> select count(empNo) from employee;
COUNT(EMPNO)
------------
6
i) Distinct
===================================================================================
========
T16.Study of date related functions
SQL>select * from employee where dob = '07-SEP-04';
EMPNO EMPNAME S PHONE DOB SALARY
---------- ------------------------- - ---------- --------- ----------
1 Ananya f 777 07-SEP-04 14500
SQL> SELECT SYSDATE AS CurrentDateTime FROM dual;
CURRENTDA
---------
05-NOV-24
SQL> SELECT ADD_MONTHS(SYSDATE, 3) AS DatePlus3Months FROM dual;
DATEPLUS3
---------
05-FEB-25
SQL> SELECT NEXT_DAY(SYSDATE, 'FRIDAY') AS NextFriday FROM dual;
NEXTFRIDA
---------
08-NOV-24
SQL> SELECT LAST_DAY(SYSDATE) AS EndOfMonth FROM dual;
ENDOFMONT
---------
30-NOV-24
SQL> SELECT TRUNC(SYSDATE, 'MM') AS StartOfMonth FROM dual;
STARTOFMO
---------
01-NOV-24
===================================================================================
========
T17.Study of views
a)with check option
create view emp1 as
select empNo , dob,salary
from employee
where dob>='1-Jan-68';
View created.
SQL> select * from emp1;
EMPNO DOB SALARY
---------- --------- ----------
1 07-SEP-04 14500
2 06-JUN-06 25000
3 13-APR-88 40000
4 14-NOV-98 55000
5 15-APR-68 35000
6 09-MAR-94 18000
6 rows selected.
b)Without check option
SQL> create view emp2 as
select empNo , empName , sex, salary
from employee;
View created.
SQL> select * from emp2;
EMPNO EMPNAME S SALARY
---------- ------------------------- - ----------
1 Ananya f 14500
2 Aditi f 25000
3 Amay m 40000
4 Akshata f 55000
5 Mani m 35000
6 Anusha f 18000
6 rows selected.
SQL> drop view emp1;
View dropped.
SQL> drop view emp2;
View dropped.
===================================================================================
========T18 . Study of a)Copying table b)Synonym
a)
SQL> create table emp_details
AS select * from employee;
Table created.
SQL> select * from emp_details;
EMPNO EMPNAME S PHONE DOB SALARY
---------- ------------------------- - ---------- --------- ----------
1 Ananya f 777 07-SEP-04 14500
2 Aditi f 666 06-JUN-06 25000
3 Amay m 333 13-APR-88 40000
4 Akshata f 141 14-NOV-98 55000
5 Mani m 7 15-APR-68 35000
6 Anusha f 778 09-MAR-94 18000
6 rows selected.
b)synonym
SQL> CREATE SYNONYM emp_syn FOR EMPLOYEE;
Synonym created.
SQL> select * from emp_syn;
EMPNO EMPNAME S PHONE DOB SALARY
---------- ------------------------- - ---------- --------- ----------
1 Ananya f 777 07-SEP-04 14500
2 Aditi f 666 06-JUN-06 25000
3 Amay m 333 13-APR-88 40000
4 Akshata f 141 14-NOV-98 55000
5 Mani m 7 15-APR-68 35000
6 Anusha f 778 09-MAR-94 18000
6 rows selected.
===================================================================================
========
T19. Study of PL/SQL features
SQL> declare
projectCount integer;
begin
insert into project values(10,'CN','IRU');
select count(projNo) into projectCount from project;
delete from project where projNo=10;
end;
/
PL/SQL procedure successfully completed.
SQL> SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
2 projectCount INTEGER;
3 BEGIN
4 INSERT INTO project VALUES (6, 'CN', 'IRU');
5
6 SELECT COUNT(projNo) INTO projectCount FROM project;
7
8 DELETE FROM project WHERE projNo = 6;
9
10 DBMS_OUTPUT.PUT_LINE('No of projects = ' || projectCount);
11 END;
12 /
No of projects = 6
PL/SQL procedure successfully completed.
SQL> declare
projectCount integer;
status char(20);
begin
insert into project values(10,'CN','IRU');
select count(projNo) into projectcount from project;
if projectCount>3 then
status := 'Very few projects';
else
status:='suffiecient project';
END if;
delete from project where projNo=10;
DBMS_output.put_line('No of projects = ' || projectCount || ' Status = ' ||
status);
end;
/
No of projects = 6 Status = Very few projects
PL/SQL procedure successfully completed.
SQL> declare
rowNo integer;
pName char(20);
projectCount integer;
begin
rowNo:=1;
select count(projNo) into projectCount from project;
while rowNo<=4 loop
select projectName into pName from project
where projNo=rowNo;
DBMS_output.put_line('Project Name = '||pName);
rowNo := rowNo+1;
End loop;
end;
Project Name = DBMS
Project Name = AIML
Project Name = Software
Project Name = WebTechnology
PL/SQL procedure successfully completed.
===================================================================================
========
T20.Study of TRIGGERS
CREATE TRIGGER t3_salaryUpdated
BEFORE UPDATE ON employee
FOR EACH ROW
WHEN ((NEW.salary / OLD.salary) > 1.1)
BEGIN
INSERT INTO salaryUpdated VALUES (:NEW.empNo, :OLD.salary,:NEW.salary);
END;
/
Trigger created.
SQL> select * from salaryUpdated;
EMPNO NEWSALARY
---------- ----------
4 55000
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON employee
FOR EACH ROW
WHEN (NEW.empNo > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
SQL> CREATE OR REPLACE TRIGGER display_salary_changes
2 BEFORE DELETE OR INSERT OR UPDATE ON employee
3 FOR EACH ROW
4 WHEN (NEW.empNo > 0)
5 DECLARE
6 sal_diff number;
7 BEGIN
8 sal_diff := :NEW.salary - :OLD.salary;
9 dbms_output.put_line('Old salary: ' || :OLD.salary);
10 dbms_output.put_line('New salary: ' || :NEW.salary);
11 dbms_output.put_line('Salary difference: ' || sal_diff);
12 END;
13 /
Trigger created.
SQL> update employee
2 set salary = 50000
3 where empNo=3;
Old salary: 40000
New salary: 50000
Salary difference: 10000
1 row updated.
SQL> select * from salaryUpdated;
EMPNO OLDSALARY NEWSALARY
---------- ---------- ----------
2 25000 30000
3 40000 50000
===================================================================================
=====
T21. Study of Stored Procedures
T22. Study of stored functions
T23. Study of cursors
T24. Study of svepoints,rollback and commit feature of SQL support for transaction
with desription
