DATABASE LAB WORK

Q1:  Consider the following three tables.

        EMPLOYEE( empno, name, deptno, dob, city, phone), DEPARTMENT( deptno, dname, manager)

        SALARY(eno, basic, deptno, tax), DEPENDENT( eno, name, relationship, dob)

        Write equivalent SQL for the following query. ( Use foreign key to join the tables. )

1. Get the name and city of the employee working for the accounting department?
2. Get the name, department name of all the employees whose pay is greater than 10000.
3. Get the name of the employee in ascending and descending order.
4. Update the city of the employee no.2 from Mumbai to Delhi.
5. Get the sum of the basic salary of the employees belongs to Delhi city.
6. Get the details of the highest income tax payee.
7. Which employee is the senior most?
8. Give the details of second highest salary employee ( without use of ‘<’ operator ).
9. Give the details of all employees of 6^th highest salary ( or n^th highest salary ).

ANS :-

 

STEP 1 :-  CREATE TABLE COMMAND

               

(i)       Table :- Department_11_BS_121

               

                create  table  department_11_BS_121

              (

                              deptno21 int primary key,

                              dname21 varchar(20),

                              manager21 varchar(20)

              );

(ii)       Table :- Employee_11_BS_121

create  table employee_11_BS_121

 (

                                empno21 int primary key,

                                name21 varchar(20),

                                deptno21 int,

                                dob21 date,

                                city21 varchar(20),

                                phone21 int,

                                foreign key (deptno21) references department_11_BS_121 (deptno21)

              );          

(iii)       Table :- Salary_11_BS_121

create  table  salary_11_BS_121

 (

                                empno21 int primary key,

                                basic21 int,

                                deptno21 int,

                                tax21 int,

                                foreign key (empno21) references employee_11_BS_121 (empno21)

                );

           

       (iv)       Table :- Dependent_11_BS_121

create  table  dependent_11_BS_121

                (

                                empno21 int,

                                name21 varchar(20),

                                 relationship21 varchar(20),

                                dob21 date,

                                foreign key (empno21) references employee_11_BS_121 (empno21),

                                primary key (empno21,name21)

 );

STEP 2 :- DATA ENTRY COMMAND

(i)       Table :- Department_11_BS_121

mysql> insert into department_11_BS_121 ( deptno21, dname21, manager21) values (

101,'Accounting','Brooks');

mysql> insert into department_11_BS_121 ( deptno21, dname21, manager21) values (

102,'Finance','Rohit');

mysql> insert into department_11_BS_121 ( deptno21, dname21, manager21) values (

103,'H-R','Rahul');

mysql> insert into department_11_BS_121 ( deptno21, dname21, manager21) values (

104,'Facitity','Jones');

mysql> insert into department_11_BS_121 ( deptno21, dname21, manager21) values (

105,'Library','Anupam');

mysql> insert into department_11_BS_121 ( deptno21, dname21, manager21) values (

106,'Transport','David');

mysql> insert into department_11_BS_121 ( deptno21, dname21, manager21) values (

107,'Trading','Raja');

(ii)       Table :- Employee_11_BS_121

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (1,' Adams ',101,' 1962-06-15','Delhi',' 246469080');

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (2,' Brooks',101,'1972-05-20','Delhi',' 24649205');

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (3,'Green',102,'1965-11-13',' Chennai',' 24647529');

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (4,'Hayes',103,'1976-08-17','Jaipur','24648538');

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (5,'Jones',104,'1982-07-18','Delhi','24647732');

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (6,'Smith',101,'1979-08-10','Lucknow','24642808');

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (7,'William',104,'1963-01-11','Delhi','24692807');

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (8,'Rahul',103,'1973-02-02','Delhi','24662626');

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (9,'Ajay',104,'1978-11-14','Hyderabad','24672727');

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (10,'Rohit',102,'1969-07-17','Delhi','24682828');

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (11,'Anupam',105,'1975-10-09','Jaipur','24632468');

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (12,'Sana',103,'1983-05-08','Lucknow','24692829');

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (13,'David',106,'1986-03-15','Delhi','24645720');

mysql> insert into employee_11_BS_121 (empno21,name21,deptno21,dob21,city21,phon

e21) values (14,'Raja',107,'1962-07-14','Delhi','24645621');

(iii)       Table :- Salary_11_BS_121

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (1,10000,101,1);

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (2,60000,101,6);

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (3,98000,102,9);

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (4,70000,103,7);

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (5,8000,104,0);

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (6,10000,101,1);

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (7,50000,104,5);

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (8,20000,103,2);

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (9,90000,104,9);

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (10,40000,102,4);

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (11,30000,105,3);

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (12,6000,103,0);

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (13,20000,106,2);

mysql> insert into salary_11_BS_121 ( empno21, basic21, deptno21, tax21) values (14,30000,107,3);

(iv)       Table :- Dependent_11_BS_121

mysql> insert into dependent_11_BS_121 (empno21, name21, relationship21, dob21)

values (1,'Humaira','Daughter','1987-03-21');

mysql> insert into dependent_11_BS_121 (empno21, name21, relationship21, dob21)

values (1,'Sadia','Sister','1989-05-21-08');

mysql> insert into dependent_11_BS_121 (empno21, name21, relationship21, dob21)

values (2,'Sandeep','Brother','1992-02-21-02');

mysql> insert into dependent_11_BS_121 (empno21, name21, relationship21, dob21)

values (3,'Neha','Sister','1982-11-14');

mysql> insert into dependent_11_BS_121 (empno21, name21, relationship21, dob21)

values (4,'Farhan','Son','1985-07-03');

mysql> insert into dependent_11_BS_121 (empno21, name21, relationship21, dob21)

values (5,'Faizan','Brother','1973-10-17');

mysql> insert into dependent_11_BS_121 (empno21, name21, relationship21, dob21)

values (5,'Naheed','Wife','1984-12-26');

mysql> insert into dependent_11_BS_121 (empno21, name21, relationship21, dob21)

values (6,'Ajay','Father','1945-11-27');

mysql> insert into dependent_11_BS_121 (empno21, name21, relationship21, dob21)

values (6,'Sandeep','Brother','1998-07-17');

mysql> insert into dependent_11_BS_121 (empno21, name21, relationship21, dob21)

values (7,'Rashmi','Mother','1952-10-09');

mysql> insert into dependent_11_BS_121 (empno21, name21, relationship21, dob21)

values (7,'Sumita','Wife','1965-08-28');

Queries :-

1. Get the name and city of the employee working for the accounting department?

mysql> select e.name21,e.city21 from employee_11_BS_121 as e, department_11_BS_121 as d where e.deptno21=d.deptno21 and d.dname21='Accounting';

OUTPUT :-

2.  Get the name, department name of all the employees whose pay is greater than 10000.

mysql> select e.name21, d.dname21, s.basic21 from employee_11_BS_121 as e, department_11_BS_121 as d, salary_11_BS_121 as s where e.deptno21=d.deptno21 and s.empno21=e.empno21 and s.basic21>10000;

OUTPUT :-

  

3. Get the name of the employee in ascending and descending order.

mysql> select name21 from employee_11_BS_121 order by name21 DESC;

mysql> select name21 from employee_11_BS_121 order by name21 ASC;

OUTPUT :-

4.  Update the city of the employee no.2 from Mumbai to Delhi.

mysql> update employee_11_BS_121 set city21='Delhi' where empno21=2;

mysql> select * from employee_11_BS_121;

OUTPUT :- 

5.  Get the sum of the basic salary of the employees belongs to Delhi city.

mysql> select sum(s.basic21) as BASIC from salary_11_BS_121 as s, employee_11_BS

_121 as e where e.empno21=s.empno21 and e.city21='Delhi';

OUTPUT :-

6.  Get the details of the highest income tax payee.

mysql> select * from employee_11_BS_121 where empno21 in ( select empno21 from s

alary_11_BS_121 where tax21 in ( select max(tax21) from salary_11_BS_121));

OUTPUT :-

7.  Which employee is the senior most?

mysql> select empno21,name21 from employee_11_BS_121 where dob21 in ( select min(dob21) from employee_11_BS_121);

OUTPUT :-

8. Give the details of second highest salary employee ( without use of ‘<’ operator ).

mysql> select * from employee_11_BS_121 where empno21 in ( select empno21 from s

alary_11_BS_121 where basic21 in ( select max(basic21) from salary_11_BS_121 whe

re basic21 not in ( select max(basic21) from salary_11_BS_121)));

OUTPUT :-

9.  Give the details of all employees of 6^th highest salary ( or n^th highest salary ).

mysql> select * from employee_11_BS_121 where empno21 = ( select empno21 from            salary_11_BS_121  where basic21 = ( select distinct basic21 from salary_11_BS_121 order by

basic21 DESC LIMIT 5,1));

OUTPUT :-

Q2:  Consider the following three tables. Make a suitable key as foreign key and use the foreign key to join the tables.

        CATALOG( bookid, pubid, title, author), ORDER( bookid, type, author, price, qty)

        PUBLISHER(isbn, pubid, pub-name, title, year)

        Write SQL statement for the following .

1. Increase the price of all books of computer type by 15 % publisher by BPB.
2. Display the maximum price of the books.
3. Display the second highest price of the books.
4. Get the publisher id and publisher name of all the books whose quantity is greater than 50.
5. Get the total price of all the books published in year 2005.
6. Get the publisher name and author of all the books.
7. Find the name of all the authors whose second character is ‘n’ .
8. Change the year of publishing all the books from 2005 to 2008 where quantity is more than 10 .

ANS :-

STEP 1 :-  CREATE TABLE COMMAND

(i)                   Table :- Publisher_11_BS_121

create table publisher_11_BS_121

 (

     isbn21 varchar(40),

     pubid21 int primary key,

     pubname21 varchar(40),

     title21 varchar(40),

     year21 int

 );

(ii)                   Table :- Catalog_11_BS_121

create table catalog_11_BS_121

(

     bookid21 int primary key,

     pubid21 int,

     title21 varchar(50),

     author21 varchar(30),

     foreign key (pubid21) references publisher_11_BS_121 (pubid21)

);

(iii)                   Table :- Order_11_BS_121

create table order_11_BS_121

 (

      bookid21 int primary key,

      type21 varchar(20),

      author21 varchar(30),

      price21 int,

      qty21 int,

      foreign key (bookid21) references catalog_11_BS_121 (bookid21)

 );

STEP 2 :- DATA ENTRY COMMAND

(i)       Table :- Publisher_11_BS_121

mysql> insert into publisher_11_BS_121 (isbn21, pubid21, pubname21, title21, yea

r21) values ('31-297-0228-2', 2, 'Pearsons Education', 'Fundamentals Of Database

 System', 2004);

mysql> insert into publisher_11_BS_121 (isbn21, pubid21, pubname21, title21, yea

r21) values ('0-07-0669074', 1, 'McGraw Hill', 'Object-Oriented Programming', 2010);

mysql> insert into publisher_11_BS_121 (isbn21, pubid21, pubname21, title21, yea

r21) values ('007-124476-X', 3, 'McGraw Hill', 'Database System Concepts', 2006);

mysql> insert into publisher_11_BS_121 (isbn21, pubid21, pubname21, title21, yea

r21) values ('81-88458-20-1', 4, 'S.K.Kataria', 'Communication Systems', 2010);

mysql> insert into publisher_11_BS_121 (isbn21, pubid21, pubname21, title21, yea

r21) values ('81-8333-163-7', 5, 'BPB', 'Let Us C', 2005);

mysql> insert into publisher_11_BS_121 (isbn21, pubid21, pubname21, title21, yea

r21) values ('0-07-063414-9', 6, 'McGraw Hill', 'Data Communications and Networking',2005);

mysql> insert into publisher_11_BS_121 (isbn21, pubid21, pubname21, title21, yea

r21) values ('0-07-463454-2', 7, 'Tata McGraw Hill', 'Mastering C++', 2005);

mysql> insert into publisher_11_BS_121 (isbn21, pubid21, pubname21, title21, yea

r21) values ('0-07-482167-3', 8, 'Dhanpat Rai & Co.', 'Data Structure Through C',2007);

mysql> insert into publisher_11_BS_121 (isbn21, pubid21, pubname21, title21, yea

r21) values ('0-07-463682-0', 9, 'Tata McGraw Hill', 'Electronic Communication',2005);

mysql> insert into publisher_11_BS_121 (isbn21, pubid21, pubname21, title21, yea

r21) values ('978-81-265-2051-0', 10, 'Wiley', 'Operating System Concepts', 2009);

mysql> insert into publisher_11_BS_121 (isbn21, pubid21, pubname21, title21, yea

r21) values ('82-381-0339-3', 11, 'BPB', 'Fundamentals Of Computer', 2005);

mysql> insert into publisher_11_BS_121 (isbn21, pubid21, pubname21, title21, yea

r21) values ('84-341-2131-6', 12, 'BPB', 'Mechanical Engineering Series', 2004);

(ii)       Table :- Catalog_11_BS_121

mysql> insert into catalog_11_BS_121 ( bookid21, pubid21, title21, author21) values

(101,1,'Object Oriented Programming','E.Balagurusamy');

mysql> insert into catalog_11_BS_121 ( bookid21, pubid21, title21, author21) values

(102,2,'Fundamentals Of Database Systems','Elmasri Navathe');

mysql> insert into catalog_11_BS_121 ( bookid21, pubid21, title21, author21) values

(103,3,'Database System Concept','Korth');

mysql> insert into catalog_11_BS_121 ( bookid21, pubid21, title21, author21) values

(104,4,'Communication Systems','Sanjay Sharma');

mysql> insert into catalog_11_BS_121 ( bookid21, pubid21, title21, author21) values

(105,5,'Let Us C','Yashavant Kanetkar');

mysql> insert into catalog_11_BS_121 ( bookid21, pubid21, title21, author21) values

(106,6,'Data Communication and Networking','Forouzan');

mysql> insert into catalog_11_BS_121 ( bookid21, pubid21, title21, author21) values

(107,7,'Mastering C++','Venugopal');

mysql> insert into catalog_11_BS_121 ( bookid21, pubid21, title21, author21) values

(108,8,'Data Structures through C','G.S.Baluja');

mysql> insert into catalog_11_BS_121 ( bookid21, pubid21, title21, author21) values

(109,9,'Electronic Communication Systems','Kennedy');

mysql> insert into catalog_11_BS_121 ( bookid21, pubid21, title21, author21) values

(110,10,'Operating System Concept','Galvin');

mysql> insert into catalog_11_BS_121 ( bookid21, pubid21, title21, author21) values

(111,11,'Fundamentals Of Computer','A.K.Sharma');

mysql> insert into catalog_11_BS_121 ( bookid21, pubid21, title21, author21) values

(112,12,'Mechanical Engineering Series','Anderson Maxwell');

(iii)       Table :- Order_11_BS_121

mysql> insert into order_11_BS_121 (bookid21, type21, author21, price21, qty21)

values (101,'Computer', 'E.Balagurusamy', 275,10);

mysql> insert into order_11_BS_121 (bookid21, type21, author21, price21, qty21)

values (102,'Computer', 'Elmasri Navathe', 500,70);

mysql> insert into order_11_BS_121 (bookid21, type21, author21, price21, qty21)

values (103,'Computer', 'Elmasri Navathe', 500,70);

mysql> insert into order_11_BS_121 (bookid21, type21, author21, price21, qty21)

values (104,'Electronics', 'Sanjay Sharma', 375,65);

mysql> insert into order_11_BS_121 (bookid21, type21, author21, price21, qty21)

values (105,'Computer', 'Yashavant Kanetkar', 198,10);

mysql> insert into order_11_BS_121 (bookid21, type21, author21, price21, qty21)

values (106,'Electronics', 'Forouzan', 500,50);

mysql> insert into order_11_BS_121 (bookid21, type21, author21, price21, qty21)

values (107,'Computer', 'Venugopal', 700,90);

mysql> insert into order_11_BS_121 (bookid21, type21, author21, price21, qty21)

values (108,'Computer', 'G.S.Baluja', 350,10);

mysql> insert into order_11_BS_121 (bookid21, type21, author21, price21, qty21)

values (109,'Electronic', 'Kennedy', 450,5);

mysql> insert into order_11_BS_121 (bookid21, type21, author21, price21, qty21)

values (110,'Computer', 'Galvin', 899,50);

mysql> insert into order_11_BS_121 (bookid21, type21, author21, price21, qty21)

values (111,'Computer', 'A.K.Sharma', 375,35);

mysql> insert into order_11_BS_121 (bookid21, type21, author21, price21, qty21)

values (112,'Mechanical', 'Anderson Maxwell', 1000,80);

Queries :-

1. Increase the price of all books of computer type by 15 % publisher by BPB.

(i)                   mysql> update order_11_BS_121 set price21=price21+price21*0.15 where bookid21 in

 ( select bookid21 from catalog_11_BS_121 where pubid21 in ( select pubid21 from

 publisher_11_BS_121 where pubname21='BPB'));

(ii)                 mysql> select p.title21,p.pubname21, o.price21 from catalog_11_BS_121 as c, Order_11_BS_121 as o, publisher_11_BS_121 as p where c.bookid21=o.bookid21 and c.pubid21=p.pubid21;

OUTPUT :-

(ii)                Display the maximum price of the books.

mysql> select max(price21) from order_11_BS_121;

OUTPUT :-

(iii)                Display the second highest price of the books.

mysql> select max(price21) from order_11_BS_121 where price21 not in ( select max(price21)

from order_11_BS_121 order by price21 desc);

OUTPUT :-

(iv)                Get the publisher id and publisher name of all the books whose quantity is greater than 50.

mysql> select pubid21, pubname21 from publisher_11_BS_121 where pubid21 in ( sel

ect pubid21 from catalog_11_BS_121 where bookid21 in ( select bookid21 from order_11_BS_121

where qty21>50));

OUTPUT :-

(v)                Get the total price of all the books published in year 2005.

mysql> select sum(o.price21) from order_11_BS_121 as o, publisher_11_BS_121 as p

, catalog_11_BS_121 as c where c.bookid21=o.bookid21 and c.pubid21=p.pubid21 and p.year21=2005;

 OUTPUT :-

(vi)                Get the publisher name and author of all the books.

mysql> select p.pubname21, c.author21 from publisher_11_BS_121 as p, order_11_BS_121 as o,

catalog_11_BS_121 as c where c.bookid21=o.bookid21 and c.pubid21=p.pubid21;

OUTPUT :-

(vii)                Find the name of all the authors whose second character is ‘n’ .

mysql> select author21 from catalog_11_BS_121 where author21 like '_n%';

OUTPUT :-

(viii)                Change the year of publishing all the books from 2005 to 2008 where quantity is more than 10 .

mysql> update publisher_11_BS_121 set year21=2008 where pubid21 in ( select pubi

d21 from catalog_11_BS_121 where bookid21 in ( select bookid21 from order_11_BS_

121 where qty21>10));

OUTPUT :-

mysql> select p.title21,p.year21,o.qty21 from publisher_11_BS_121 as p,catalog_11_BS_121 as c,

order_11_BS_121 as o where c.bookid21=o.bookid21 and c.pubid21=p.pubid21;

OUTPUT :-

Q3:  Write MySQL query for the following :

1. Write a function and stored procedure in mysql to print ‘hello yourname how are you’
2. Write a function and stored procedure in mysql to give the total number of the employee in a employee table.
3. Write a function and stored procedure in mysql to calculate the factorial of a number. ( Take INT as BIGINT ).
4. Write a function and stored procedure in mysql to calculate the avg of 3 number.
5. Write a function and stored procedure in mysql to calculate the sum of Fibonacci Series.
6. Write a function and stored procedure in mysql to print ‘hello yourname how are you’
7. Write a stored procedure to insert employee info ( eno, ename, eage) if age lies between 18 and 60.

NOTE : Stored Procedure should display appropriate error message.

8. Write a stored procedure to insert a value in employee when salary is greater than 2000 otherwise error message.

ANS :-

1.       Write a function and stored procedure in mysql to print ‘hello yourname how are you’

(i)                  Procedure :-

mysql> delimiter $$

mysql> create procedure hello_11_BS_121 ( in n varchar(20), out o varchar(40))

    -> begin

    ->      select concat('Hello', n ,'How are you');

    -> end;

    -> $$

mysql> delimiter ;

                      mysql> call hello_11_BS_121(' Lubna ',@t);

OUTPUT :-

(ii)                  Function :-

        mysql> delimiter $$

        mysql> create function hello_11_BS_121(n varchar(20))

                 -> returns varchar(50)

                 -> begin

                 -> return concat('Hello', n ,'How are you');

                 -> end;

                   -> $$

           mysql> delimiter ;

           mysql> select hello_11_BS_121(' Lubna ') as MESSAGE;

OUTPUT :-

2.       Write a function and stored procedure in mysql to give the total number of the employee in a employee table.

(i)                  Create table emp_11_BS_121 :-

mysql> create table emp_11_BS_121

    -> (

    ->    id21 int primary key,

    ->    name21 varchar(20),

    ->    sal21 int,

    ->    dob21 date

    -> );

(ii)                  Insert values in emp_11_BS_121 :-

mysql> insert into emp_11_BS_121 (id21,name21,sal21,dob21) values (1,'Lubna',500

                             00,'1989-05-08');

mysql> insert into emp_11_BS_121 (id21,name21,sal21,dob21) values (2,'Williams',300

                             00,'1985-08-17');

mysql> insert into emp_11_BS_121 (id21,name21,sal21,dob21) values (3,'Smith',350

                             00,'1985-06-20');

mysql> insert into emp_11_BS_121 (id21,name21,sal21,dob21) values (4,'Brown',200

                             00,'1987-03-21');

mysql> insert into emp_11_BS_121 (id21,name21,sal21,dob21) values (5,'Harry',600

                             00,'1988-07-13');

(iii)                  Function :-

mysql> delimiter $$

mysql> create function total_11_BS_121()

    -> returns int

    -> begin

    -> declare s int default 0;

    ->      set s = ( select count(*) from emp_11_BS_121 );

    -> return s;

    -> end;

    -> $$

mysql> delimiter ;

mysql> select total_11_BS_121() as TOTAL;

OUTPUT :-

(iv)                  Procedure :-

mysql> delimiter $$

mysql> create procedure total_11_BS_121(in id21 int)

    -> begin

    ->      select count(*) from emp_11_BS_121;

    -> end;

    -> $$

mysql> delimiter ;

mysql> call total_11_BS_121(@t);

OUTPUT :-

3.       Write a function and stored procedure in mysql to calculate the factorial of a number. ( Take INT as BIGINT ).

(i)                  Function :-

a)        

mysql> delimiter $$

mysql> create function factorial_11_BS_121(n BIGINT) returns BIGINT

    -> begin

    -> declare f BIGINT default 1;

    -> declare i BIGINT default 1;

    ->          increment : loop

    ->          set i=i+1;

    ->          if i>n then

    ->          leave increment;

    ->          end if;

    ->          set f=f*i;

    ->          end loop increment;

    ->          return f;

    -> end;

    -> $$

mysql> delimiter ;

mysql> select factorial_11_BS_121(4) as FACTORIAL;

b)        mysql> delimiter $$

mysql> create function factorial(n int) returns int

    -> begin

    -> declare f int default 1;

    -> declare i int default 1;

    ->     while(i<=n) do

    ->        set f=f*i;

    ->        set i=i+1;

    ->     end while;

    ->     return f;

    -> end;

    -> $$

mysql> delimiter ;

mysql> select factorial(2) as FACTORIAL;

   

OUTPUT :-

(ii)                  Procedure :-

a)       mysql> delimiter $$

mysql> create procedure factorial_11_BS_121(in n BIGINT, out res BIGINT)

    -> begin

    -> declare f BIGINT default 1;

    -> declare i BIGINT default 1;

    ->          increment : loop

    ->          set i=i+1;

    ->          if i>n then

    ->          leave increment;

    ->          end if;

    ->          set f=f*i;

    ->          end loop increment;

    ->          select f;

    -> end;

    -> $$

mysql> delimiter ;

mysql> call factorial_11_BS_121(4,@t);

b)        create procedure factorial(in n int, out o int)

begin

declare f int default 1;

declare i int default 1;

    while(i<=n) do

       set f=f*i;

       set i=i+1;

    end while;

    select f;

end;

OUTPUT :-

4.       Write a function and stored procedure in mysql to calculate the avg of 3 number.

(i)                  Function :-

mysql> delimiter $$

mysql> create function average_11_BS_121(a int, b int, c int)

    -> returns int

    -> begin

    ->     declare avg int;

    ->         set avg=(a+b+c)/3;

    ->         return avg;

    -> end;

    -> $$

mysql> delimiter ;

mysql> select average_11_BS_121(5,3,2);

OUTPUT :-

(ii)                  Procedure :-

mysql> delimiter $$

mysql> create procedure average_11_BS_121(in a int,in b int,in c int, out d float)

    -> begin

    ->         set d=(a+b+c)/3;

    -> end;

    -> $$

mysql> delimiter ;

mysql> call average_11_BS_121(5,3,2,@t);

mysql> select @t as AVERAGE;

OUTPUT :-

5.       Write a function and stored procedure in mysql to calculate the sum of Fibonacci Series.

(i)                  Function :-

mysql> delimiter $$

mysql> create function fibonacci_11_BS_121(n int)

    -> returns int

    -> begin

    -> declare f int default 0;

    -> declare s int default 1;

    -> declare sm int default 0;

    -> declare i int default 2;

    -> declare sum int default 1;

    -> increment : loop

    ->    set i=i+1;

    ->    if i>n then

    ->    leave increment;

    ->    end if;

    ->    set sm=f+s;

    ->    set f=s;

    ->    set s=sm;

    ->    set sum=sum+sm;

    ->    end loop increment;

    ->    return sum;

    -> end;

    -> $$

mysql> delimiter ;

mysql> select fibonacci_11_BS_121(4) as FIB_SUM;

OUTPUT :-

(ii)                  Procedure :-

mysql> delimiter $$

mysql> create procedure fibonacci_11_BS_121(in n int, out o int)

    -> begin

    -> declare f int default 0;

    -> declare s int default 1;

    -> declare sm int default 0;

    -> declare i int default 2;

    -> declare sum int default 1;

    -> increment : loop

    ->    set i=i+1;

    ->    if i>n then

    ->    leave increment;

    ->    end if;

    ->    set sm=f+s;

    ->    set f=s;

    ->    set s=sm;

    ->    set sum=sum+sm;

    ->    end loop increment;

    ->    select sum as FIBONACCI_SUM;

    -> end;

    -> $$

mysql> delimiter ;

mysql> call fibonacci_11_BS_121(4,@t);

OUTPUT :-

6.       Write a stored procedure to insert employee info ( eno, ename, eage) if age lies between 18 and 60.

       NOTE : Stored Procedure should display appropriate error message.

(i)                  Create table emp_11_BS_121 :-

mysql> create table emp_11_BS_121

    -> (

    ->    eno21 int primary key,

    ->    ename21 varchar(20),

    ->    eage21 int

    -> );

OUTPUT :-

(ii)                  Procedure :-

mysql> delimiter $$

mysql> create procedure myproc_11_BS_121(in eno21 int, in ename21 varchar(20), in

dob21 date)

    -> begin

    -> declare t int default 0;

    -> set t=(year(curdate())-year(dob21))-(right(curdate(),5)<right(dob21,5));

    -> if t<18 then

    -> select 'Age is below 18 years, please enter age between 18 to 60 years';

    -> elseif

    -> t>60 then

    -> select 'Age is above 60 years, please enter age between 18 to 60 years';

    -> else

    -> insert into emp_11_BS_121 values(eno21, ename21,t);

    -> end if;

    -> end;

    -> $$

mysql> delimiter ;

mysql> call myproc_11_BS_121(1,'Lubna','1989-05-08');

mysql> call myproc_11_BS_121(2,'William','1985-08-17');

mysql> call myproc_11_BS_121(3,'Smith','1985-06-20');

mysql> call myproc_11_BS_121(4,'Brown','1987-03-21');

mysql> call myproc_11_BS_121(5,'Hary','1988-07-13');

mysql> call myproc_11_BS_121(6,'John','1950-05-17');

OUTPUT :-

7.       Write a stored procedure to insert a value in employee when salary is greater than 2000 otherwise error message.

(i)                  Create table emp_11_BS_121 :-

mysql> create table emp_11_BS_121

    -> (

    ->       eno21 int primary key,

    ->       ename21 varchar(20),

    ->       sal21 int

    -> );

(ii)                  Procedure :-

mysql> delimiter $$

mysql> create procedure myproc_11_BS_121(in eno21 int, in ename21 varchar(20), i

n sal21 int)

    -> begin

    ->      if sal21 > 2000 then

    ->             insert into emp_11_BS_121 values (eno21,ename21,sal21);

    ->      else

    ->             select 'Salary is not valid';

    ->      end if;

    -> end;

    -> $$

mysql> delimiter ;

mysql> call myproc_11_BS_121(1,'Williams',3000);

mysql> call myproc_11_BS_121(2,'Adams',4000);

mysql> call myproc_11_BS_121(3,'Smith',5000);

mysql> call myproc_11_BS_121(4,'Hayes',7000);

mysql> call myproc_11_BS_121(5,'Brown',2000);

OUTPUT :-

Q4:  Suppose the following four relation. Use appropriate foreign key to join the tables.

        Supplier( S#, sname, status, city), Parts( P#, pname, color, weight, city)

        Project(J#, jname, city ), Shipment( S#, P#, J#, qty)

        Write SQL query for following.

1. Get supplier name for suppliers who supply parts p2.
2. Get full details of all the projects.
3. Get all shipment where quantity is in the range of 300 and 750.
4. Get the quantity of part p1 supplied by supplier s1.
5. Get color of parts supplied by supplier s1.
6. Get supplier names for suppliers who supply at least one red part.
7. Get supplier names for supplier who do not supply parts p2.
8. Get the part numbers for parts supplied to all projects in Delhi.
9. List of product name which have 3^rd highest quantity.

ANS :-

 

STEP 1 :-  CREATE TABLE COMMAND

(i)                   Table :- Supplier_11_BS_121

                       

create table supplier_11_BS_121

 (

        Sno21 varchar(10) primary key,

        Sname21 varchar(30),

        Status21 int,

        City21 varchar(20)

 );

(ii)                   Table :- Parts_11_BS_121

create table Parts_11_BS_121

 (

      Pno21 varchar(10)  primary key,

      Pname21 varchar(20),

      Color21 varchar(20),

      Weight21 int,

      City21 varchar(20)

 );

(iii)                   Table :- Project_11_BS_121

 create table Project_11_BS_121

 (

      Jno21 varchar(10)  primary key,

      Jname21 varchar(50),

      City21 varchar(20)

 );

(iv)                   Table :- Shipment_11_BS_121

create table Shipment_11_BS_121

(

      Sno21 varchar(10),

      Pno21 varchar(10),

      Jno21 varchar(10),

      Qty21 int,

      foreign key (Sno21) references Supplier_11_BS_121 (Sno21),

      foreign key (Pno21) references Parts_11_BS_121 (Pno21),

      foreign key (Jno21) references Project_11_BS_121 (Jno21),

      primary key (Sno21, Pno21, Jno21)

);

STEP 2 :- DATA ENTRY COMMAND

(i)                   Table :- Supplier_11_BS_121

mysql> insert into Supplier_11_BS_121 (Sno21, Sname21, Status21, City21) values

('S1', 'Adams',10, 'Delhi');

mysql> insert into Supplier_11_BS_121 (Sno21, Sname21, Status21, City21) values

('S2', 'Green',30, 'Mumbai');

mysql> insert into Supplier_11_BS_121 (Sno21, Sname21, Status21, City21) values

('S3', 'Jones',10, 'Delhi');

mysql> insert into Supplier_11_BS_121 (Sno21, Sname21, Status21, City21) values

('S4', 'Williams',40, 'Kolkata');

mysql> insert into Supplier_11_BS_121 (Sno21, Sname21, Status21, City21) values

('S5', 'Hayes',50, 'Bangalore');

mysql> insert into Supplier_11_BS_121 (Sno21, Sname21, Status21, City21) values

('S6', 'Brooks',10, 'Delhi');

mysql> insert into Supplier_11_BS_121 (Sno21, Sname21, Status21, City21) values

('S7', 'Smith',20, 'Chennai');

mysql> insert into Supplier_11_BS_121 (Sno21, Sname21, Status21, City21) values

('S8', 'Ajay',20, 'Chennai');

mysql> insert into Supplier_11_BS_121 (Sno21, Sname21, Status21, City21) values

('S9', 'Rohit',50, 'Bangalore');

mysql> insert into Supplier_11_BS_121 (Sno21, Sname21, Status21, City21) values

('S10', 'Rahul',30, 'Mumbai');

(ii)                   Table :- Parts_11_BS_121

mysql> insert into Parts_11_BS_121 (Pno21, Pname21, Color21, Weight21, City21) values

('P1', 'Bulb', 'Pink' , 100, 'Delhi');

mysql> insert into Parts_11_BS_121 (Pno21, Pname21, Color21, Weight21, City21) values

('P2', 'Tube', 'White' , 400, 'Kolkata');

mysql> insert into Parts_11_BS_121 (Pno21, Pname21, Color21, Weight21, City21) values

('P3', 'Fan', 'Brown' , 1500, 'Ahmadabad');

mysql> insert into Parts_11_BS_121 (Pno21, Pname21, Color21, Weight21, City21) values

('P4', 'Cooler', 'Black' , 10000, 'Jaipur');

mysql> insert into Parts_11_BS_121 (Pno21, Pname21, Color21, Weight21, City21) values

('P5', 'Bulb', 'Red' , 100, 'Delhi');

mysql> insert into Parts_11_BS_121 (Pno21, Pname21, Color21, Weight21, City21) values

('P6', 'Fan', 'Red' , 1500, 'Ahmadabad');

mysql> insert into Parts_11_BS_121 (Pno21, Pname21, Color21, Weight21, City21) values

('P7', 'Washing Machine', 'Red' , 20000, 'Jaipur');

mysql> insert into Parts_11_BS_121 (Pno21, Pname21, Color21, Weight21, City21) values

('P8', 'Tube', 'Blue' , 400, 'Mumbai');

mysql> insert into Parts_11_BS_121 (Pno21, Pname21, Color21, Weight21, City21) values

('P9', 'Cooler', 'White' , 10000, 'Hyderabad');

        mysql> insert into Parts_11_BS_121 (Pno21, Pname21, Color21, Weight21, City21) values

       ('P10', 'Fan', 'White' , 1500, 'Bangalore');

(iii)                   Table :- Project_11_BS_121

mysql> insert into Project_11_BS_121 (Jno21, Jname21, City21) values ('J1', 'CWGame', 'Delhi');

mysql> insert into Project_11_BS_121 (Jno21, Jname21, City21) values ('J2', 'Mono Rail', 'Mumbai');

mysql> insert into Project_11_BS_121 (Jno21, Jname21, City21) values ('J3', 'Tram Service', 'Kolkata');

mysql> insert into Project_11_BS_121 (Jno21, Jname21, City21) values ('J4', 'Delhi Metro', 'Delhi');

mysql> insert into Project_11_BS_121 (Jno21, Jname21, City21) values ('J5', 'Children Welfare', 'Bangalore');

mysql> insert into Project_11_BS_121 (Jno21, Jname21, City21) values ('J6', 'Metro', 'Bangalore');

mysql> insert into Project_11_BS_121 (Jno21, Jname21, City21) values ('J7', 'Charminar Project', 'Hyderabad');

mysql> insert into Project_11_BS_121 (Jno21, Jname21, City21) values ('J8', 'Yamuna Bridge Construction',

 'Delhi');

mysql> insert into Project_11_BS_121 (Jno21, Jname21, City21) values ('J9', 'Delhi Zoo', 'Delhi');

mysql> insert into Project_11_BS_121 (Jno21, Jname21, City21) values ('J10', 'Tsunami Project', 'Chennai');

(iv)                   Table :- Shipment_11_BS_121

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S1', 'P1', 'J4', 300);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S1', 'P1', 'J1', 500);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S2', 'P1', 'J8', 450);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S3', 'P2', 'J4', 750);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S4', 'P2', 'J3', 750);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S9', 'P3', 'J5', 750);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S5', 'P1', 'J7', 690);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S6', 'P4', 'J9', 350);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S7', 'P2', 'J10', 800);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S10','P1', 'J2', 500);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S9', 'P2', 'J6', 350);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S3', 'P5', 'J4', 850);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S10','P6', 'J2', 200);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S6', 'P7', 'J9', 900);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S3', 'P5', 'J1', 490);

mysql> insert into Shipment_11_BS_121 (Sno21, Pno21, Jno21, Qty21) values ('S2', 'P6', 'J2',370);

Queries :-

1. Get supplier name for suppliers who supply parts p2.

mysql> select s.sname21 from supplier_11_BS_121 as s, parts_11_BS_121 as p, ship

ment_11_BS_121 as sh where s.sno21=sh.sno21 and p.pno21=sh.pno21 and p.pno21='p2';

OUTPUT :-

2.  Get full details of all the projects.

mysql> select pr.jname21, pr.city21, s.sname21, p.pname21, sh.qty21 from project_11_BS_121

as pr, shipment_11_BS_121 as sh, supplier_11_BS_121 as s, parts_11_BS_121 as p

where pr.jno21=sh.jno21 and s.sno21=sh.sno21 and p.pno21=sh.pno21;

OUTPUT :-

3.  Get all shipment where quantity is in the range of 300 and 750.

mysql> select * from shipment_11_BS_121 where qty21 between 300 and 750;

OUTPUT :-

4.  Get the quantity of part p1 supplied by supplier s1.

mysql> select qty21 from shipment_11_BS_121 where pno21='P1' and sno21='S1';

OUTPUT :-

5.   Get color of parts supplied by supplier s1.

mysql> select distinct p.color21 from parts_11_BS_121 as p, supplier_11_BS_121 a

s s, shipment_11_BS_121 as sh where s.sno21=sh.sno21 and p.pno21=sh.pno21 and s.

sno21='S1';

OUTPUT :-

6.   Get supplier names for suppliers who supply at least one red part.

mysql> select distinct sname21 from supplier_11_BS_121 as s, shipment_11_BS_121

as sh, parts_11_BS_121 as p where s.sno21=sh.sno21 and p.pno21=sh.pno21 and p.co lor21='red';

OUTPUT :-

7.   Get supplier names for supplier who do not supply parts p2.

mysql> select sname21 from supplier_11_BS_121 where sno21 not in ( select sno21

from shipment_11_BS_121 where pno21='P2');

OUTPUT :-

8.  Get the part numbers for parts supplied to all projects in Delhi.

mysql> select p.pname21,p.pno21,pr.jname21 from parts_11_BS_121 as p, shipment_1

1_BS_121 as sh, project_11_BS_121 as pr where pr.jno21=sh.jno21 and sh.pno21=p.p

no21 and pr.city21='delhi';

OUTPUT :-

9.   List of product name which have 3^rd highest quantity.

mysql> select pname21 from parts_11_BS_121 where pno21 = ( select pno21 from shipment_11_BS_121 where qty21= ( select distinct qty21 from shipment_11_BS_121 order by qty21 desc limit 2,1));

OUTPUT :-

Q5:  Create a table employee with attributes empno, ename, job, mgr, hiredate, sal, commission, deptno. And table department with attributes deptno, dname, loc. Use foreign key.

       

1. How many clerks are there in the company?
2. Which department has exactly one employee as clerk?
3. Which department has the highest number of clerks? Show the department and count.
4. How many employees are there in each department?
5. List the lowest salary for different jobs used in a company and list them in descending order.
6. Which department average salary is the lowest among all? Show the deptno, average salary.
7. List the minimum , maximum and average salary for each job.
8. List the names of the employees whose name  contains LA.
9. List the names of the employees whose joining date is between 2^nd April,1981 and 8^th September,1981.
10. How many different job titles exist in the employee table?
11. Compute the difference between maximum and minimum salary.
12. Compute the sum of all salaries of employee working under deptno=30.
13. For each salesman in the emp table retrieve the deptno and department name.
14. List the names of all the employees with their name of the manager.
15. List all employees who are working in department located at CHICAGO.
16. List all the employees who are working in same department as their managers.
17. Retrieve all the employees who are working in deptno=10 and who earn salary atleast as much as any employee working in deptno=30.
18. List all the department who have no employees.

ANS :-

 

STEP 1 :-  CREATE TABLE COMMAND

(i)       Table :- Department_11_BS_121

create table department_11_BS_121

(

     deptno21 int primary key,

     dname21 varchar(20),

     LOC21 varchar(20)

);

(ii)       Table :- Employee_11_BS_121

create table employee_11_BS_121

 (

      empno21 int primary key,

      ename21 varchar(20),

      job21 varchar(30),

      mgr21 varchar(20),

      hiredate21 date,

      sal21 int,

      commission21 int,

      deptno21 int,

      foreign key (deptno21) references department_11_BS_121 (deptno21)

 );

STEP 2 :- DATA ENTRY COMMAND

(i)                   Table :- Department_11_BS_121

mysql> insert into department_11_BS_121 ( deptno21, dname21, LOC21 ) values ( 10,'H.R.','New York');

mysql> insert into department_11_BS_121 ( deptno21, dname21, LOC21 ) values ( 20,

'Accounts','Washington');

mysql> insert into department_11_BS_121 ( deptno21, dname21, LOC21 ) values ( 30,

'Marketing','Chicago');

mysql> insert into department_11_BS_121 ( deptno21, dname21, LOC21 ) values ( 40,'Software Development','Delhi');

mysql> insert into department_11_BS_121 ( deptno21, dname21, LOC21 ) values ( 50,'Facilities','New York');

mysql> insert into department_11_BS_121 ( deptno21, dname21, LOC21 ) values ( 60,'Trading','New York');

(ii)                   Table :- Employee_11_BS_121

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (1,'Adams','Manager','Brown','1981-04-02',40000,1000,10);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (2,'Brooks','Receptionist','Adams','1990-12-17',15000,600,10);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (3,'Green','Receptionist','Adams','1991-02-20',15000,600,10);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (4,'Hayes','Assistant','Adams','1986-06-03',12000,600,10);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (5,'Jones','Assistant','Adams','1993-02-22',12000,600,10);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (6,'Smith','Clerk','Adams','1981-09-08',15000,600,10);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (7,'Williams','Clerk','Adams','1981-03-24',15000,600,10);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (8,'Rahul','Team Leader','Brown','1982-07-12',40000,1000,40);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (9,'Ajay','Database Developer','Rahul','1996-01-01',30000,800,40);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (10,'Rohit','Database Developer','Rahul','1981-04-13',30000,800,40);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (11,'Anupam','Software Engineer','Rahul','1981-05-21',30000,800,40);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (12,'Sana','Software Engineer','Rahul','2002-06-12',30000,800,40);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (13,'David','Network Engineer','Rahul','1981-08-09',30000,800,40);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (14,'Ramesh','Network Engineer','Rahul','2001-01-03',30000,800,40);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (15,'Jack','Accountant','Brown','2002-09-02',40000,1000,20);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (16,'Jill','Finance Officier','Jack','2001-08-04',25000,800,20);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (17,'Mary','Assistant','Jack','1982-05-24',12000,600,20);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (18,'Lane','Clerk','Jack','2003-07-08',15000,600,20);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (19,'Lane','Clerk','Jack','2003-04-26',15000,600,20);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (20,'Lakshmi','Manager','Brown','2004-05-09',40000,1000,30);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (21,'Lakesha','Salesman','Lakshmi','2004-06-08',10000,600,30);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (22,'Lara','Salesman','Lakshmi','1981-09-19',10000,600,30);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (23,'Latasha','Salesman','Lakshmi','2004-09-09',10000,600,30);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (24,'Larry','Assistant','Lakshmi','2005-10-12',12000,600,30);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (25,'Lars','Assistant','Lakshmi','2004-12-10',12000,600,30);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (26,'Laisha','Clerk','Lakshmi','2005-10-26',15000,600,30);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (27,'Williams','Manager','Brown','2001-12-30',40000,1000,50);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (28,'Larissa','Assistant','Williams','2002-11-30',12000,600,50);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (29,'Laken','Clerk','Williams','1986-12-29',15000,600,50);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (30,'LeMarcus','Clerk','Williams','2007-03-13',15000,600,50);

mysql> insert into employee_11_BS_121 ( empno21, ename21, job21, mgr21, hiredate21, sal21, commission21, deptno21) values (31,'Lena','Salesman','Rahul','2006-04-15',10000,600,40);

Queries :-

1.       How many clerks are there in the company?

mysql> select count(*) as Number_Of_Clerks from employee_11_BS_121 where job21='

Clerk';

OUTPUT :-

2.  Which department has exactly one employee as clerk?

mysql> select deptno21, count(*) from employee_11_BS_121 where job21='Clerk' gro

up by deptno21 having count(*)=1;

                      

       OUTPUT :-

3.   Which department has the highest number of clerks? Show the department and count.

mysql> SELECT count(e.deptno21) as number_of_clerk, e.deptno21, d.dname21  FROM

employee_11_bs_121 as e , department_11_bs_121 as d where e.deptno21 = d.deptno21 and job21='Clerk' group by e.deptno21;

OUTPUT :-

4.     How many employees are there in  each department?

mysql> select deptno21, count(*) from employee_11_BS_121 group by deptno21;

OUTPUT :-

5.       List the lowest salary for different jobs used in a company and list them in descending order.

mysql> select job21, min(sal21) from employee_11_BS_121 group by job21 order by

min(sal21) desc;

OUTPUT :-

6.       Which department average salary is the lowest among all? Show the deptno, average salary.

mysql> select deptno21, avg(sal21) from employee_11_BS_121 group by deptno21 hav

ing deptno21 = ( select deptno21 from employee_11_BS_121 group by deptno21 havin

g avg(sal21) order by avg(sal21) limit 0,1);

OUTPUT :-

7.       List the minimum , maximum and average salary for each job.

mysql> select job21, min(sal21), max(sal21), avg(sal21) from employee_11_BS_121

group by job21;

OUTPUT :-

8.       List the names of the employees whose name  contains LA.

mysql> select ename21 from employee_11_BS_121 where ename21 like '%la%';

OUTPUT :-

9.       List the names of the employees whose joining date is between 2^nd April,1981 and 8^th September,1981.

mysql> select ename21 from employee_11_BS_121 where hiredate21 between '1981-04-

02' and '1981-09-08';

OUTPUT :-

10.       How many different job titles exist in the employee table?

mysql> select count(distinct(job21)) from employee_11_bs_121;

OUTPUT :-

11.       Compute the difference between maximum and minimum salary.

mysql> select max(sal21)-min(sal21) as Deducted from employee_11_BS_121;

OUTPUT :-

12.       Compute the sum of all salaries of employee working under deptno=30.

mysql> select sum(sal21) as SUM from employee_11_BS_121 where deptno21=30;

OUTPUT :-

13.       For each salesman in the emp table retrieve the deptno and department name.

mysql> SELECT e.empno21, e.ename21, e.job21, d.dname21, e.deptno21 FROM employee_11_bs_121 e , department_11_bs_121 d where e.deptno21 = d.deptno21 and e.job21

= 'Salesman';

OUTPUT :-

14.       List the names of all the employees with their name of the manager.

mysql> select ename21, mgr21 from employee_11_BS_121;

OUTPUT :-

15.       List all employees who are working in department located at CHICAGO.

mysql> select ename21 from employee_11_BS_121 where deptno21 in ( select deptno21 from department_11_BS_121 where loc21='Chicago');

OUTPUT :-

16.       List all the employees who are working in same department as their managers.

mysql> SELECT distinct(e.ename21), e.deptno21 FROM employee_11_bs_121 e , employee_11_bs_121 f where e.deptno21 = f.deptno21;

OUTPUT :-

17.       Retrieve all the employees who are working in deptno=10 and who earn salary atleast as much as any employee working in deptno=30.

mysql> select ename21 from employee_11_BS_121 where deptno21 = 10 and sal21 >=

any ( select sal21 from employee_11_BS_121 where deptno21=30);

OUTPUT :-

18.       List all the department who have no employees.

        mysql> select deptno21 from department_11_BS_121 where deptno21 not in ( select deptno21 from employee_11_BS_121 );

OUTPUT :-

Q6:  Consider the following ER Diagram. ( Use the foreign key to join the tables).

Write equivalent SQL for the following query. (Use the foreign key to join the tables).

1. Use stored procedure to insert in employee table with appropriate error message (e.g. AGE should be between 18 and 60 ).
2. Give the name of employee who work at least in two department.
3. Give the details of all employees of 6^th highest salary ( or n^th highest salary ).

ANS :-

(1)     Create table Command

(i)                  emp_11_BS_121 :-

create table employee_11_BS_121

(

     Empid21 int primary key,

     Name21 varchar(20),

     City21 varchar(20),

     DOB21 date,

     Age21 int,

     Salary21 int

);

(ii)                  department_11_BS_121 :-

create table department_11_BS_121

(

     Deptno21 int primary key,

     Deptname21 varchar(20),

     LOC21 varchar(20)

);

(iii)                  workfor_11_BS_121 :-

create table workfor_11_BS_121

(

      Empid21 int,

      Deptno21 int,

      primary key(Empid21,Deptno21),

      foreign key(Empid21) references employee_11_BS_121(Empid21),

      foreign key(Deptno21) references department_11_BS_121(Deptno21)

);

(2)     Insert into table Command :-

(i)                  Insert into table Employee_11_BS_121 through procedure :-

mysql> delimiter $$

mysql> create procedure mypro_11_BS_121(in Empid21 int, in Name21 varchar(20),

n City21 varchar(20), in DOB21 date, in salary21 int)

    -> begin

    -> declare t int default 0;

    -> set t=(year(curdate())-year(dob21))-(right(curdate(),5)<right(dob21,5))

    -> if t<18 then

    ->      select 'Age is below 18 years, please enter age between 18 to 60 yrs';

    -> elseif

    -> t>60 then

    ->      select 'Age is above 60 years, please enter age between 18 to 60 yrs';

    -> else

    ->      insert into Employee_11_BS_121 values(Empid21,Name21,City21,DOB21,t,Salary21);

    -> end if;

    -> end;

    -> $$

mysql> delimiter ;

mysql> call mypro_11_BS_121(1,'Lubna','Delhi','1989/05/08',35000);

mysql> call mypro_11_BS_121(2,'Sam','Delhi','1990/03/04',29000);

mysql> call mypro_11_BS_121(3,'Smith','Bangalore','1987/03/04',22000);

mysql> call mypro_11_BS_121(4,'John','Chennai','1988/03/04',25000);

mysql> call mypro_11_BS_121(5,'Shaun','Chennai','1988/03/04',27000);

mysql> call mypro_11_BS_121(6,'Watson','Hyderabad','1986/11/25',23000);

mysql> call mypro_11_BS_121(7,'Kallis','Patna','1985/10/25',30000);

mysql> call mypro_11_BS_121(8,'Green','New York','1995/05/08',30000);

mysql> call mypro_11_BS_121(8,'Green','New York','1950/05/08',30000);

mysql> call mypro_11_BS_121(8,'Adams','Patna','1985/10/24',23000);

mysql> select * from employee_11_BS_121;

(ii)                  Insert into table Department_11_BS_121:-

mysql> insert into department_11_BS_121 values (101,'Accounts','Ground Floor');

mysql> insert into department_11_BS_121 values (102,'HR','2nd Floor');

mysql> insert into department_11_BS_121 values (103,'Sales','2nd Floor');

mysql> insert into department_11_BS_121 values (104,'IT','1st Floor');

mysql> select * from department_11_BS_121;

(iii)                  Insert into table WorkFor_11_BS_121:-

mysql> insert into workfor_11_BS_121 values (3,101);

mysql> insert into workfor_11_BS_121 values (7,101);

mysql> insert into workfor_11_BS_121 values (6,101);

mysql> insert into workfor_11_BS_121 values (1,102);

mysql> insert into workfor_11_BS_121 values (2,102);

mysql> insert into workfor_11_BS_121 values (5,102);

mysql> insert into workfor_11_BS_121 values (6,102);

mysql> insert into workfor_11_BS_121 values (4,103);

mysql> insert into workfor_11_BS_121 values (5,103);

mysql> insert into workfor_11_BS_121 values (1,104);

mysql> insert into workfor_11_BS_121 values (7,104);

mysql> select * from workfor_11_BS_121;

2.   Give the name of employee who work at least in two department.

select b.name21, b.empid21 from employee_11_BS_121 as b join workfor_11_BS_121 as w on b.empid21=w.empid21 group by w.empid21 having count(w.deptno21)>1;

3.   Give the details of all employees of 6^th highest salary ( or n^th highest salary ).

select * from employee_11_BS_121 where salary21=(select distinct salary21 from employee_11_BS_121 order by salary21 desc limit 5,1);

Click here to download Database_MySQL(LUBNA KHAN).pdf