TASK – 1
DDL COMMANDS (Create, Alter, Drop, Truncate)
1. Create a table EMP with the following structure.
Name Type
EMPNO NUMBER(6)
ENAME VARCHAR2(20)
JOB VARCHAR2(10)
MGR NUMBER(4)
DEPTNO NUMBER(3)
SAL NUMBER(7,2)
Query:
SQL>create table emp(empno number(6), ename varchar2(20), jobvarchar2(10), mgr number(4), deptno
number(3), sal number(7,2));
Table created.
Output:
2. Add a column commission to the EMP table. Commission should be numeric with null values allowed.
Query:
SQL>Alter table empadd(commission number(4));
Output:
Table altered.
3. Modify the column width of the job field of emp table.
Query:
SQL>Alter table empmodify(job varchar2(15));
Output:
Table altered.
4. Create dept table with the following structure.
Name Type
DEPTNO NUMBER(2)
DNAME VARCHAR2(10)
LOC VARCHAR2(10)
Query:
SQL>create table dept(deptno number(2), dname varchar2(10), loc varchar2(10));
Output:
Table created.
5. Add constraint to the emp table that is empno as primary key and deptno as foreign key.
Query:
SQL>alter table emp add constraint emp_id_pk primary key(empno);
SQL>alter table dept add constraint pk primary key(deptno);
Output:
Table altered
SQL>Alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept(deptno);
6. Add constraints to the emp table to check the empno value while entering i.eempno>100. Salary value by
default is 5000, otherwise it should accept the values from the user.
Query:
SQL>alter table emp add check (empno>100);
SQL>alter table emp modify sal default 5000;
Output:
7. Add column DOB to the emp table Add and drop a column DOJ to the emp table.
Query:
SQL>alter table emp add(dob date);
SQL>alter table emp add(doj date);
SQL>alter table emp drop(doj);
Output:
TASK – 2
DML COMMANDS (Insert, Select, Update, Delete)
1.Insert 5 records into dept table.Insert few rows and truncate those from emp1 table and also drop it. .
Query:
SQL>Insert into dept values(&deptno,’&dname’,’&loc’);
SQL>create table emp1 as select * from emp;
SQL>insert into emp1 values(7000, ’King’, ‘Pres’, 10, 20,10000,500, ’12-Jan-92’);
SQL>insert into emp1 values(7010, ‘Jack’, ‘VP’, 10, 30, 9000, 300, ’19-Jul-92’);
SQL>Truncate table emp1;
SQL>Drop table emp1;
Output:
2. Insert 11 records into the emp table.
Query:
SQL>insert into empvalues(&no, ’&name’, ’&job’, &mgr, &deptno, &sal, &comm, ‘&dob’);
Note: Repeat execution of this statement for 11 times for 11 record insertions
Output:
3. Update the emp table to set the default commission of all employees to Rs.1000 /- who are working as
managers.
Query:
SQL>update emp set commission=1000 where job like ‘%Manager%’;
Output:
4. Delete only those who are working as Supervisors.
Query:
SQL>delete from employee where job like ‘%Supervisor’;
Output:
5. Delete the rows whose empno is 7599.
Query:
SQL>delete from employee where empno=7599;
Output:
TASK – 3
SQL Operators
1. List the records in the emp table order by salary in descending order.
Query:
SQL>select * from emp order by saldesc;
Output:
2. Display only those employees whose deptno is 30.
Query:
SQL>select * from emp where deptno=30;
Output:
3. Display deptno from the table employee avoiding the duplicate values.
Query:
SQL>select distinct deptno from emp;
Output:
4. List all employee names, salary and 15% rise in salary.Label the column as New Sal.
Query:
SQL>select ename, sal, (sal*1.15) “New Sal” from emp;
Output:
5. Display the rows whose empno ranges from 7500 to 7600.
Query:
SQL>select empno, ename, sal from emp where empno between 7500 and 7600;
Output:
6. Display all the employees in dept 10 and 20 in alphabetical order of names.
Query:
SQL>select empno, ename, deptno from emp where deptnoin(10,20) group by ename;
Output:
7. List the employe names who do not earn commission.
Query:
SQL>select empno, ename, sal from emp where commission is null;
Output:
8. Display all the details of the records with 5 character names with ‘S’ as starting character.
Query:
SQL>select * from employees where lengty(last_name)=5 and last_name like ‘s%’;
9.Display joining date of all employees in the year of 1998.
Query:
SQL>select employee_id ,hire_date from employees where hire_date between ‘1-jan-1998’ and ’31-dec-1998’;
10. List out the employee names whose salary is greater than 5000and less than6000.
Query:
SQL>select ename from emp where sal>5000 and sal>6000;
Output:
TASK – 4
SQL Aggregate Functions, Group By clause, Having clause
1. Count the total records in the emp table.
Query: select count(*) from emp;
Output:
2. Calculate the total and average salary of the employees.
Query: select sum(sal) “Total”, avg(sal) “Average” from emp;
Output:
3. Determine the maximum and minimum salary of the employees and rename the columns max_salalary and
min_salary.
Query: select max(sal) “max_salary”, min(sal) “min_salary” from emp;
Output:
4. Find the no.of departments in employee table.
Query: select deptno, count(deptno) from emp group by deptno;
Output:
5. Display job wise sum, avg, max, min salaries.
Query: select job, sum(sal), avg(sal), max(sal), min(sal) from emp group by job;
Output:
6. Display maximum salaries of all departments having maximum salary>2000.
Query: select deptno, max(sal) from emp group by deptno having max(sal)>2000;
Output:
7. Display job wise sum, avg, max and min salaries in department 10 having average salary>1000 and result is
orederd by sum of salary in desc order.
Query: select job, sum(sal), avg(sal), max(sal), min(sal) from emp where deptno=10 group by job having
avg(sal)>1000 order by sum(sal) desc;
Output:
TASK - 5
Exercise on SQL Functions
1. Display the employee name concatenated with empno.
Query:
select concat(empno, concat(' ', ename)) from emp;
Output:
2. Display half of employee name in upper case and half in lower case.
Query:
Select upper(substr(ename,0,length(ename)/2))||lower(substr(ename,(length(ename)/2)+1,length(ename)))
“Name” from emp;
Output:
3. Display the month name of date “14-jul-09” in full.
Query:
select to_char(to_date('14-jul-09'),'MONTH') “Month” from dual;
Output:
4. Display the DOB of all employees in the format 'dd-mm-yy'.
Query:
select to_char(dob,'dd-mm-yy') from emp;
Output:
5. Display the date two months after the DOB of employees.
Query:
select add_months(dob,2) from emp;
Output:
6. Display the last date of that month in “05-Oct-09”.
Query:
select last_day(to_date('05-oct-09')) “Last” from dual;
Output:
7. Display the rounded date in the year format, month format, day format in the employee.
Query:
select round(dob, 'dd'), round(dob, 'month'), round(dob, 'year') from emp;
Output:
8.Display the commissions earned by employees. If they do not earn commission, display it as “No Commission”.
Query:
select employee_id,last_name,nvl(to_char(commission_pct),’No Commission’)”commission” from employees;
TASK – 6
Nested Queries
1. Find the third highest salary of the employees.
Query:
select max(sal) from emp where sal<(select max(sal) from emp where sal<(select max(sal) from emp));
Output:
2. Display all the employee names and salary whose salary is greater than the minimum salary and job title starts
with 'M'.
Query:
select ename, sal from emp where sal>(select min(sal) from emp) and job like 'M%';
Output:
3. Write a Query to display information about employees who earn more than any employee in department 30.
Query:
select empno, ename, sal, deptno from emp where sal>any (select sal from emp where deptno=30);
Output:
4. Display the employees who have the same job as Jones and whose salary>=Fords.
Query:
select empno, ename, sal, job from emp where job= (select job from emp where ename='Jones') and sal>= (select
sal from emp where ename='Fords');
Output:
5. List out the employee names who get the salary> maximum salary of dept with deptno 20,30.
Query:
select ename from emp where sal>(select max(sal) from emp where deptno in(20,30));
Output:
6. Display the maximum salaries of the departments whose maximum salary>9000.
Query:
select max(sal) from emp group by deptno having max(sal)>9000;
Output:
7. Create a table employee with the same structure as the table emp and insert rows into the table using select
clauses.
Query:
SQL>create table employee as (select * from emp);
Output:
8. Create a manager table from the emp table which should hold details only about managers.
Query:
SQL>create table manager as (select * from emp where job like ‘%Manager%’);
TASK – 7
Joins, Set Operators
1. Display all the employees and departments implementing left outer join.
Query: select e.empno, e.ename, d.deptno, d.dname from emp e left outer join dept d on(e.deptno=d.deptno);
Output:
2. Display the employee name and department name in which they are working implementing a full outer join.
Query: select e.ename,d.dname from emp e full outer join dept d on(e.deptno=d.deptno);
Output:
3. Write a Query to display the employee name and manager’s name and salary for all employees.
Query: select e.ename, m.ename “MGR”, m.sal “MGRSAL” from emp e, emp m where e.mgr=m.empno;
Output:
4. Write a Query to Output the name, job, employee number, department name, location for each department even
if there are no employees.
Query: select e.empno, e.ename, e.job, d.dname, d.loc from emp e join dept d on(e.deptno=d.deptno);
Output:
5. Display the details of those who draw the same salary.
Query:select empno, ename, sal from emp where sal=&sal;
Output:
TASK – 8
Views
1.Create a view that displays the employee id, name and salary of employees who belong to
10th department.
Query:
Create view emp_view as select employee_id,last_name,salary from employees where department_id=10;
2.Create a view with read only option that displays the employee name and their department name
Query:
create view emp_dept as select employee_id,last_name,department_id from employees with read onlyh
constraint emp_dept_readonly;
3. Display all the views generated.
Query:
select view_name from user_views;
Output:
4. Execute the DML commands on the view created.and drop them.
Query:
• delete from my_view where empno=7900;
• insert into manager_view values(8000, 'Grant', 'ExeHead', null, 10, 19000, 200, '19-dec-90');
• update manager_view set sal=15000 where sal<11000;
Output:
Drop a view.
Query: drop view my_view;
Output:
TASK – 9
Practices on DCL Commands
1. SQL>Create user test identified by pswd;
Output:
2.SQL> Grant create session, create table, create sequence, create view to test;
Output:
3. SQL>Create role manager;
SQL>Grant create table, create view to manager;
SQL>Grant manager to test;
Output:
4. SQL>Alter user test identified by qwerty;
Output:
5. SQL>Grant select on employees to test;
Output:
6. SQL>Grant update (department_name,location_id) on departments to test;
Output:
7.SQL>Grant select,insert on hr.locations to test;
Output:
8. SQL>Revoke select,insert on departments from test;
Output:
INDEXES
1. Function based indexes:
SQL>create index emp_index on emp (upper(ename));
Output:
SQL>select employee_id,last_name,job_id from employees where last_name between 'N' and 'P';
Output:
2. Creating Index while creating Table.
SQL>create table emp2 (empnonumber(6) PRIMARY KEY USING INDEX (CREATE INDEX emp_idx ON
emp2(empno)) ,ename varchar2(20),job varchar2(20));
Output:
User-defined indexes: GoSQL>select index_name,table_name from user_indexes where table_name='EMP2';
Output:
3. create table emp3(empnonumber(6) primary key, ename varchar2(20), job varchar2(10));
Output:
Default indexes.
SQL>select index_name,table_name from user_indexes where table_name='EMP3';
Output:
4. Displaying all
the indexes.
SQL>Select index_name, table_name from user_indexes;
Output:
4.SQL>select table_name, index_name, column_name from user_ind_columns where table_name='EMPLOYEES';
Output:
5. Dropping an index:
SQL> drop index emp_index;
Output:
SEQUENCE
1. SQL>create sequence my_seq start with 10 increment by 10 maxvalue 100 nocache;
Output:
2.
SQL>select my_seq.nextval from dual;
Output:
3. SQL>select my_seq.currval from dual;
Output:
4. SQL>create table dept(deptno number(6),dname varchar2(20),loc varchar2(10));
SQL>insert into dept values(my_seq.nextval,'Executive','US');
SQL>insert into dept values(my_seq.nextval,'Marketing','UK');
5. SQL>drop sequence my_seq;
TASK - 9
1. Write a PL/SQL code to retrieve the employee name, join date and designation from employee database of an
employee whose number is input by the user.
Program:
/*Employee details*/
DECLARE
v_name varchar2(25);
v_joindate date;
v_dsgn employees.job_id%type;
BEGIN
select last_name,hire_date,job_id into v_name,v_joindate,v_dsgn from employees where employee_id=&id;
DBMS_OUTPUT.PUT_LINE('Name:'||v_name||' Join Date:'||v_joindate||' Designation:'||v_dsgn);
END;
Output:
2. Write a PL/SQL code to calculate tax for an employee of an organization.
Program:
/*Calculate Tax*/
DECLARE
v_sal number(8);
v_tax number(8,3);
v_name varchar2(25);
BEGIN
select salary,last_name into v_sal,v_name from employees where employee_id=&id;
if v_sal<10000 then
v_tax:=v_sal*0.1;
elsif v_sal between 10000 and 20000 then
v_tax:=v_sal*0.2;
else
v_tax:=v_sal*0.3;
END IF;
DBMS_OUTPUT.PUT_LINE('Name:'||v_name||' Salary:'||v_sal||'Tax:'||v_tax);
END;
Output:
3. Write a PL/SQL program to display top 10 employee details based on salary using cursors.
Program:
/*Top 10 salary earning employee details*/
DECLARE
cursor c_emp_cursor is select employee_id, last_name, salary from employees order by salary desc;
v_rec c_emp_cursor%rowtype;
v_i number(3):=0;
BEGIN
open c_emp_cursor;
loop
v_i:=v_i+1;
fetch c_emp_cursor into v_rec;
exit when v_i>10;
DBMS_OUTPUT.PUT_LINE(v_rec.employee_id||' '||v_rec.last_name||' '||v_rec.salary);
END LOOP;
close c_emp_cursor;
END;
/
Output:
4. Write a PL/SQL program to update the commission values for all employees with salary less than 5000 by
adding 1000 to existing employees.
Program:
/*Updation*/
declare
cursor c_emp is select salary,commission_pct from employees;
v_emp c_emp%rowtype;
v_temp number(7,2);
v_temp1 number;
BEGIN
open c_emp;
loop
fetch c_emp into v_emp;
exit when c_emp%notfound;
v_temp1:=v_emp.commission_pct;
v_temp:=(v_emp.salary*v_emp.commission_pct)+1000;
v_temp:=v_temp/v_emp.salary;
if(v_emp.salary<5000) then
update employees set commission_pct=v_temp where employee_id=v_temp.employee_id;
end if;
DBMS_OUTPUT.PUT_LINE('Commission % updated from '||v_temp1||' to '||v_temp);
end loop;
END;
Output:
TASK – 11
1. Write a trigger on the employee table which shows the old values and new values of ename after any
updations on ename on Employee table.
Program:
create or replace trigger t_emp_name after update of last_name on salary_table FOR EACH ROW
begin
DBMS_OUTPUT.PUT_LINE('Name updated from '||:OLD.last_name||' to '||:NEW.last_name);
END;
Output:
2. Write a PL/SQL procedure for inserting, deleting and updating in employee table.
Program:
create or replace procedure proc_dml (p_id emp.employee_id%type, p_sal number,p_case number)
is
BEGIN
case p_case
when 1 then
DBMS_OUTPUT.PUT_LINE('Insertion...');
insert into emp(employee_id,last_name,email,hire_date,job_id)
values(p_id,'Franco',’FJames’,'12-JAN-02','ST_CLERK');
when 2 then
DBMS_OUTPUT.PUT_LINE('Deletion...');
delete from emp where employee_id=p_id;
when 3 then
DBMS_OUTPUT.PUT_LINE('Updation...');
update emp set salary=p_sal where employee_id=p_id;
end case;
DBMS_OUTPUT.PUT_LINE('DML operation performed on '||SQL%rowcount||' rows');
END;
DECLARE
v_id employees.employee_id%type:=&id;
v_sal employees.salary%type:=&sal;
v_case number:=&case1or2or3;
begin
proc_dml(v_id,v_sal,v_case);
END;
Output:
3. Write a PL/SQL function that accepts department number and returns the total salary of the department.
Program:
create function func_dept (p_dept number) return number is
v_total number;
BEGIN
select sum(salary) into v_total from employees where department_id=p_dept;
return v_total;
END;
/
DECLARE
v_dept number:=&department_id;
v_total number;
BEGIN
v_total:=func_dept(v_dept);
DBMS_OUTPUT.PUT_LINE('Total salary in Department '||v_dept||' is '||v_total);
END;
Output:
Task-12
1. Write a PL/SQL program to handle predefined exceptions.
Program:
declare
v_id number(6):=&employee_id;
v_sal employees.salary%type;
v_name employees.last_name%type;
v_job employees.job_id%type;
begin
select last_name, salary into v_name, v_sal from employees where employee_id=v_id;
DBMS_OUTPUT.PUT_LINE(v_name||q'['s salary is ]'||v_sal);
select job_id into v_job from employees where last_name=v_name;
DBMS_OUTPUT.PUT_LINE(v_name||q'['s job is ]'||v_job);
EXCEPTION
when no_data_found then
DBMS_OUTPUT.PUT_LINE('No employee with ID:'||v_id);
when too_many_rows then
DBMS_OUTPUT.PUT_LINE('Many employees with Name:'||v_name);
when others then
DBMS_OUTPUT.PUT_LINE('Some other error occured');
end;
Output:
2. Write a PL/SQL program to handle user defined exception.
Program:
DECLARE
v_dept number:=&department_id;
e_nodept exception;
BEGIN
update employees set salary=salary+1050 where department_id=v_dept;
IF SQL%notfound then
raise e_nodept;
ELSE
DBMS_OUTPUT.PUT_LINE(SQL%rowcount||' rows updated');
END IF;
EXCEPTION
when e_nodept then
DBMS_OUTPUT.PUT_LINE('No Department with ID:'||v_dept)
END;
Output:
3)Write a PL/SQL code to create
a. Package specification.
Program:
create or replace package pack_dml is
procedure proc_dml(p_id number,choice number);
END pack_dml;
Output:
b. Package body for the insert, retrieve, update and delete operations on student table.
Program:
create or replace package body pack_dml is
procedure proc_dml(p_id number,choice number) is
v_name varchar2(20);
v_total number;
BEGIN
case choice
when 1 then
DBMS_OUTPUT.PUT_LINE('Insertion...');
insert into student values(p_id,'Franco',90);
when 2 then
DBMS_OUTPUT.PUT_LINE('Deletion...');
delete from student where sid=p_id;
when 3 then
DBMS_OUTPUT.PUT_LINE('Updation...');
update student set total=total+1 where sid=p_id;
when 4 then
select sname,total into v_name,v_total from student where sid=p_id;
DBMS_OUTPUT.PUT_LINE('Total marks of '||v_name||' is '||v_total);
end case;
DBMS_OUTPUT.PUT_LINE('DML operation performed on '||SQL%rowcount||' rows');
END proc_dml;
END pack_dml;
BEGIN
pack_dml.proc_dml(&StudentID,&choice1or2or3or4);
END;
Output:
