ASSIGNMENTS

DAY-1

Create table structure for the following .

insert data into the tables

Solution:
Day-2
Solution:

//Cdac DATABASE//

1.

/*SELECT *

from course

WHERE ( NOT cname = 'dac' AND NOT cname = 'dbda' );*/

2.

/*SELECT credits, cname

from course

WHERE cname <>'dac'; */

3.

/*SELECT rollNo,concat(name, ' ', 'has completed his ', ' ', degree,' ','in year'),year,sex,deptNo,advisor

FROM student

WHERE rollNo>=1;*/

4.

/*SELECT empId, name,sex,startYear, deptNo,phone

FROM professor

where phone is null;*/

5.

/*SELECT startYear

FROM professor

WHERE startYear BETWEEN '2017-01-01' AND '2017-12-31';*/

6.

/*SELECT startYear
FROM professor

WHERE startYear>='2017-01-01'

AND startYear<='2017-12-31';*/

7.

/*SELECT cname

FROM course

WHERE credits='d'

AND (courseId=1 AND deptNo=1);*/

8.

/*SELECT cname

FROM course

WHERE credits='b'

and deptNo>1;*/

9.

/*SELECT cname

FROM course

WHERE credits='b'

and deptNo=5;*/

10.

/*CREATE VIEW [UNIQUE name] AS //SELECT DISTINCT name ,deptId,hod why not this we can write */

SELECT DISTINCT name

from department

WHERE deptId>=1;
11.

/*SELECT [Link]

from department

INNER JOIN professor ON [Link]=[Link]

WHERE deptNo<4;

12.

/*SELECT empId, name

from professor INNER JOIN Customers ON [Link]=[Link];

WHERE deptNo <3;*/

13.

/*SELECT name

FROM student

WHERE sex='female';*/

14.

/*SELECT cname

FROM course;*/

15.

/*SELECT name,degree,year,sex,deptNo,advisor

FROM student

WHERE rollNo>=1;*/
Day-3

CREATE TABLE dept(

deptno int(2) primary key,

dname varchar(50) not null,

location_id varchar(50) references locations(location_id ) );

insert into dept values (10,'Accounting','1001');

insert into dept values (20,'Research','1002');

insert into dept values (30,'Sales','1003');

insert into dept values (40,'Operations','1004');

insert into dept values (50,'HR','1004');

CREATE TABLE locations(location_id varchar(50) primary key,

city varchar(50) not null);

insert into locations values ('1001','New Delhi');

insert into locations values ('1002','pune');

insert into locations values ('1003','Mohali');

insert into locations values ('1004','Mumbai');

insert into locations values ('1005','Banglore');

CREATE TABLE employee(

empno int(4) not null primary key,

ename varchar(50) not null,

job varchar(50) not null,

mgr int(4),

hiredate date,

sal decimal(10,2),

comm decimal(10,2),

deptno int(2) references dept(deptno));

insert into employee values

(7369,'SMITH','CLERK',7902,'93/6/13',800,0.00,20);

insert into employee values

(7499,'ALLEN','SALESMAN',7698,'98/8/15',1600,300,30);

insert into employee values

(7521,'WARD','SALESMAN',7698,'96/3/26',1250,500,30);

insert into employee values

(7566,'JONES','MANAGER',7839,'95/10/31',2975,null,20);

insert into employee values

(7698,'BLAKE','MANAGER',7839,'92/6/11',2850,null,30);

insert into employee values

(7782,'CLARK','MANAGER',7839,'93/5/14',2450,null,10);

insert into employee values

(7788,'SCOTT','ANALYST',7566,'96/3/5',3000,null,20);

insert into employee values

(7839,'KING','PRESIDENT',null,'90/6/9',5000,0,10);
insert into employee values
(7844,'TURNER','SALESMAN',7698,'95/6/4',1500,0,30);

insert into employee values

(7876,'ADAMS','CLERK',7788,'99/6/4',1100,null,20);

insert into employee values

(7900,'JAMES','CLERK',7698,'00/6/23',950,null,30);

insert into employee values

(7934,'MILLER','CLERK',7782,'00/1/21',1300,null,10);

insert into employee values

(7902,'FORD','ANALYST',7566,'97/12/5',3000,null,20);

insert into employee values

(7654,'MARTIN','SALESMAN',7698,'98/12/5',1250,1400,30);

CREATE TABLE salgrade(

grade int(4) not null primary key,

losal decimal(10,2),

hisal decimal(10,2));

insert into salgrade values (1,700,1200);

insert into salgrade values (2,1201,1400);

insert into salgrade values (3,1401,2000);

insert into salgrade values (4,2001,3000);

insert into salgrade values (5,3001,99999);

commit;

1. Write a query to display the last name, department number, and department name for all employees.

2. Create a unique listing of all jobs that are in department 80. Include the location of the department in
the output.

3. Write a query to display the employee last name, department name, location ID, and city of all
employees who earn a commission.

4. Display the employee last name and department name for all employees who have a (lowercase) in
their last names.

5. Write a query to display the last name, job, department number, and department name for all
employees who work in Toronto.

6. Display the employee last name and employee number along with their manager’s last name and
manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.

7. display all employees including King, who has no manager. Order the results by the employee
number.

8. Create a query that displays employee last names, department numbers, and all the employees who
work in the same department as a given employee. Give each column an appropriate label.

9. Show the structure of the JOB_GRADES table. Create a query that displays the name, job,

department name, salary, and grade for all employees.

10. Create a query to display the name and hire date of any employee hired after employee Davies.
 11. Display the names and hire dates for all employees who were hired before their managers,

along with their manager’s names and hire dates. Label the columns Employee, Emp

Hired, Manager, and Mgr Hired, respectively.

Solution:Assingment Answers

1.

select DISTINCT ename,dname,[Link] from employee cross join dept

or

select ename,dname,[Link] from employee cross join dept

or

select ename,dname,[Link] from employee,dept where [Link]=[Link]

2.

select DISTINCT([Link]),[Link],d.location_id,[Link] from employee e,dept d,locations l where

[Link]=20

3.

select ename,dname,d.location_id,city from employee e inner join dept d on [Link]=[Link] inner

join locations l on d.location_id=l.location_id;

or

select [Link],[Link],d.location_id,[Link] from employee e,dept d,locations l where

d.location_id=l.location_id AND [Link] IS NOT NULL

or

select [Link],[Link],d.location_id,[Link] from employee e,dept d,locations l where [Link] IS NOT

NULL
4.

select [Link],[Link] from employee,dept where [Link]=[Link] AND ename LIKE %A%;

5.

select [Link],[Link],[Link],[Link] from locations l,employee e, dept d where [Link]=[Link]

AND d.location_id=l.location_id AND [Link]='Banglore'

6.

select [Link] AS 'Employee' ,[Link] AS 'Emp#',[Link] AS 'Department',[Link] AS 'dept#' FROM

employee e inner join dept d on [Link]=[Link];

or

select [Link] AS 'Employee' ,[Link] AS 'Emp#',[Link] AS '#man',[Link] AS 'Manager' FROM

employee e inner join employee m on [Link]=[Link];

or

select [Link] AS 'Employee' ,[Link] AS 'Emp#',[Link] AS '#man',[Link] AS 'Manager' FROM

employee e Right join employee m on [Link]=[Link];

7.

select [Link],[Link],[Link] from employee e ORDER BY [Link];

8.

select [Link] AS 'Employee_name',[Link] AS 'Department_name',[Link] AS 'Department_no'

from employee e inner join dept d on [Link]=[Link] ORDER BY [Link];

or

select [Link] AS 'Employee_name', [Link] AS 'Department_no' from employee e inner join

employee on [Link]=[Link] ORDER BY [Link];

or
select [Link],[Link],[Link],[Link]

from employee e,employee c where [Link]=[Link] AND [Link]<>[Link] ORDER BY [Link];

9.

Alter table employee ADD COLUMN Grade varchar(10);

update employee SET Grade='A' where empno IN(7566,7698);

select [Link],[Link],[Link],[Link],[Link] from employee e left join dept d on [Link]=[Link];

or

select [Link],[Link],[Link],[Link],[Link]

from emp e,dept d,salgrade s where [Link]=[Link]

and [Link] between [Link] and [Link];

or

select [Link],[Link],[Link],[Link],[Link] from employee e left join dept d on [Link]=[Link]

ORDER BY [Link];

or

select [Link],[Link],[Link],[Link],[Link] from employee e,dept d where [Link]=[Link](+)

ORDER BY [Link];

10.

SELECT [Link], [Link], [Link]

FROM employee e

JOIN employee SMITH

ON [Link]=[Link]

AND [Link]< [Link];

or
SELECT [Link], [Link]

FROM employee e where [Link]>(select [Link] from employee e where [Link]='WARD')

ORDER BY [Link];

or

select [Link],[Link]

from employee e, employee x

where [Link]='FORD'

and [Link]<[Link];

11.

SELECT [Link] AS 'Employee_name', [Link] AS 'EMP_Hired',[Link] AS

'Manager_name',[Link] AS 'Mang_hired'

FROM employee e

JOIN employee MANAGER

ON ([Link] = '7566')

WHERE [Link]> [Link];

Day-4

Question on function and Group by clause

[Link] the average salary of each department by using group by function

ANS: select avg(sal) 'ToatalAvg',deptno from employee group by deptno;

[Link] the total no. of employee in each department

ANS: select count(ename) 'TotalEmployee',deptno from employee group by deptno;

[Link] the total number of manager working in organization

ANS: select count(job) AS 'Total Manager' from employee where job='Manager';

[Link] the difference between highest sal and lowest salary

ANS: select max(sal)-min(sal) 'Salary(Highest-Lowest)' from employee;

[Link] the manager id & salary of the lower-paid employee that [Link] where mgrno is not
known.

Exclude the group where min salary is less than 600

Sort the record in decending order.

ANS: select mgr,min(sal)

from employee

where mgr IS NOT NULL

group by mgr

having min(sal)<6000

order by min(sal) DESC

Day-5

SUB QUERY ASSIGNMENT

Ques1:Display the name of employee who are getting the salary more than x.

Ans: select ename,job,sal from employee where sal>(select sal from employee where ename='BLAKE');

Ques2: Display the emp list who work in the same dept of x.

ANS2:select ename,job,sal from employee where deptno=(select deptno from employee where
ename='BLAKE');

or

select ename,job,sal from employee where deptno=(select deptno from employee where
ename='BLAKE');

or

select ename,job,sal

from employee

where sal>(select max(sal) from employee group by deptno );

ques3:Display the emp name & job who work are same job of x.

ANS3. select ename,job,sal

from employee

where job=(select job from employee where ename='BLAKE' );

or

select ename,job,sal

from employee

where job=(select job from employee where ename='BLAKE' )

AND ename<>'BLAKE';
Ques4:Print the details of emp who’s job id is like empid x and salary is greater than equal to salary of
emp id y.

Ans:4. select ename,job,sal

from employee

where empno='7839' AND sal>=(select sal from employee where empno='7698');

or

select ename,job,sal

from employee

where job=(select job from employee where ename='BLAKE')

AND sal>=(select sal from employee where ename='CLARK');

or

select ename,job,sal

from employee

where job=(select job from employee where ename='BLAKE')

AND sal>=(select sal from employee where ename='CLARK')

AND ename<>'BLAKE' AND ename<>'CLARK';

Ques5:select the name of emp which getting min salary.

Ans:5. select min(sal), ename

from employee ;

or

select ename,sal

from employee

where sal=(select min(sal)

from employee)
Ques6:select the name of emp which getting max salary.

Ans:6. select max(sal), ename

from employee ;

or

select ename,sal

from employee

where sal=(select max(sal)

from employee)

Ques7: select the name of emp which getting 2nd max salary.

Ans:7. select max(sal) '2nd MAX', ename

from employee where sal<(select max(sal) from employee);

Ques8:Display top five salary.

Ans8. select sal from (select sal from employee) employee LIMIT 5;

or

select sal from (select sal from employee) employee ORDER BY sal LIMIT 5;

Ques9:Display all dept that have min salary greater than the min salary of dept 30.

Ans:9.

select min(sal),deptno

from employee

group by deptno

having min(sal)>(select min(sal) from employee where deptno=30);

Ques10:Find the job with lowest average salary.

Ans:10.

select min([Link]),job from

(select avg(sal) 'avgsal',job

from employee

group by job) e;

Ques11:Display the name of the employees who earn same salary as the min salary of each dept.

Ques12:Display employee who are not programmer and who’ salary is less than any of the IT prog.

Ques13:Display employee who have sb body’s.

Ques14:Display employee who do not have subbody’s.

Ques15:Display the detail of the employee who are manager and work in the same department as the
employee with empno=7499 and empno=7521.
Day-6

Assignment on PL/SQL

Ques1: Write PL/SQL procedure to display the employee list.

Ques2: Write PL/SQL block to display the employee details according to their deptno. Will be a user choice.

Ques3: Write a PL/SQL procedure to display the details of the employee who’s salary is greater than amount
entered by user.

Ques4: Write PL/SQL procedure to display high salary of a particular dept of user choice.

Ques5: Write a PL/SQL procedure to display no. of records or rows entered by the user.

Ques6: Write a PL/SQL procedure to display the no. of employee in a particular job category.

Solution:

/*delimiter $$

create procedure print4(in a int(2),in b int(2),out c int(2))

begin

set c=a+b;

end $$

*/

call print4(23,34,@c);

select @c;
/*delimiter $$

create procedure getdata(in d1 int(2))

begin

select max(sal)

from employee where deptno=d1;

end $$

*/

call getdata(10);

/*delimiter $$

create procedure getdata3(in d1 int(2),out top int(10))

begin

select max(sal) into top from employee where deptno=d1;

end $$

*/

call getdata3(20,@top)

select @top;

1.

/*delimiter $$

create procedure display()

begin

select ename from employee;

end$$

*/

call display();

2.

/*delimiter $$

create procedure display1(in d1 int(2))

begin

select *

from employee where deptno=d1;

end $$

*/

call display1(20);

3.

/*delimiter $$

create procedure display2(in salary int(2))

begin

select *

from employee where sal>salary;

end $$

*/

call display2(2000);
4.

/*delimiter $$

create procedure display2(in salary int(2))

begin

select max(e.max_salary)from (select max(sal) 'max_salary'

from employee group by deptno) e;

end $$

*/

5.

/*delimiter $$

create procedure display3a(in d1 int(2))

begin

select max(sal)

from employee where deptno=d1;

end $$

*/

call display3a(20);

6.

/*delimiter $$

create procedure display4(in l1 int(2))

begin

select *
from employee LIMIT l1;

end $$

*/

call display4(5);

7.

/*delimiter $$

create procedure display5()

begin

select count(ename)

from employee where job='MANAGER';

end $$

*/

call display5();

8.

/*delimiter $$

create procedure display7(in jobx varchar(50))

begin

select count(ename)

from employee where job=jobx;

end $$

*/

call display7('MANAGER');
Day-7

Date 16-04-2018 Assignment On PLSQL

Q1:write PL/SQL procedure to find number is even or odd.

/*delimiter $$

create procedure Q1(in a int(10))

begin

select a;

if mod(a,2) then

select "no. is even";

else

select "no. is odd";

end if;

end $$

*/

call Q1(24);

/*delimiter $$

create procedure Q1a(in a int(10))

begin

select a;

if mod(a,2)=0 then
select "no. is even";

else

select "no. is odd";

end if;

end $$

*/

Q2:Leap Year Check

/*delimiter $$

create procedure Q2(in b int(10))

begin

select b;

if mod(b,4)=0 or mod(b,100)=0 and mod(b,400)=0

then

select "Year is leap ";

else

select "year is no leap year ";

end if;

end $$

*/

Q3: create a procedure to display or find the seniority of the two employee according to their date of
joining.

/*delimiter $$

create procedure q3a(in e1 int(4),in e2 int(4))

begin

declare h1 date;

declare h2 date;

select hiredate into h1 from employee

where empno=e1;

select hiredate into h2 from employee

where empno=e2;

if h1>h2 then

select ename from employee where empno=e1;

else

select emane from employee where empno=e2;

end if;

end $$

*/

Q4: Write PL/SQL procedure that will accept the emp no. and find the salary of that person. If the
salary of that person is less than 1000 then hike 30% ,if the salary is b/w 1001-2000 give him 20% hike
& give 10% if other.

/*delimiter $$

create procedure q4(in eno int(4))

begin

declare sal1 int(10);

select sal into sal1 from employee where empno=eno;

if sal1<1000

then
update employee set sal=sal1+sal1*0.10 where empno=eno;

elseif (sal1>1001 && sal1<2000)

then

update employee set sal=sal1+sal1*0.20 where empno=eno;

else

update employee set sal=sal1+sal1*0.10 where empno=eno;

end if;

end $$

*/

Q5: Above question by using case.

/*delimiter $$

create procedure q5(in eno int(10))

begin

declare sal1 int(10);

select sal into sal1 from employee where empno=eno;

case sal1

when sal1<1000 then

select "10% hike in salary";

when (sal1>1001&&sal1<2000) then

select "20% hike in salary"

else

select "10% hike in salary";

end case;
end $$

*/

Q6:Write PL/SQL block to find the grade of a particular employee if the grade is 1 or A then print
Excellent. 2->very good etc.

/*delimiter $$

create procedure q7(in hisal1 int(4))

begin

declare g int(4);

select hisal1;

select grade into g from salgrade where hisal=hisal1;

if(g=1)

then

select "Excellent";

elseif(g=2)

then

select "Good";

else

select "Average";

end if;

end $$

*/
DAY-8 PL/SQL Function And Procedure

/*delimiter $$

create procedure Test1()

begin

declare x int(5);

set x=1;

Repeat

select x;

set x=x+1;

until x>10

end Repeat;

end $$

*/

/*delimiter $$

create procedure Test2()

begin

declare x int(5);

set x=1;

while x<5

do

select x;

set x=x+1;
end while;

end $$

*/

/*delimiter $$

create procedure Test2a(in y int(5))

begin

declare x int(5);

set x=1;

while (x<=y)

do

select x;

set x=x+1;

end while;

end $$

*/

/*create procedure Test5()

begin
declare fact int(100);

declare i int (5);

set fact=1;

set i=1;

while i<=3 do

select fact;

select i;

set fact=fact*i;

set i=i+1;

end while;

select fact;
end $$

*/

/*delimiter //

create function Test6(n int)

returns int(10)

DETERMINISTIC

begin

declare fact int(10);

declare i int (5);

set fact=1;

set i=1;

while i<=7 do

return fact;

return i;

set fact=fact*i;

set i=i+1;

end while;
return fact;

end //

*/

call Test6(6);

*/

/*

delimiter //

create function f1()

returns int

DETERMINISTIC
begin

declare x int(10);

declare str varchar(10);

set str='';

set x=1;

xyz:loop

if x<=10 then

set str =concat(str,x,',');

set x=x+1;

iterate xyz;

else
leave xyz;

end if;

end loop;

return str;

end //

*/

/*delimiter //
create function f3b()

returns varchar(20)

DETERMINISTIC

begin

declare x int(10);

declare str varchar(20);

set str='';

set x=1;

xyz:loop

if x<=10 then

set str =concat(str,x,',');

set x=x+1;

iterate xyz;

else

leave xyz;

end if;

end loop;

return str;

end //

*/
/*delimiter //

create function f3e()

returns varchar(20)

DETERMINISTIC

begin

declare x int(5);

declare str varchar(30);

declare str1 varchar(30);

declare str2 varchar(30);

set str='';

set str2='';

set str1='A';

set x=1;

xyz:loop

if x<=10 then

set str =concat(str1,x);

set str2 =concat(str2,str,',');

set x=x+1;

iterate xyz;

else
leave xyz;

end if;

end loop;

return str2;

end //

*/

select f3e();