NAME:-Abir Chakraborty

ROLL NO:-11

SUBJECT:- Msc computer science

PAPER CODE:-CMSPAEC01P
Assignment No-: 01 (Basic SQL)

CREATE DATABASE COMPANY;

USE COMPANY;
CREATE TABLE EMPLOYEE_DATA (empno VARCHAR(5), empname
VARCHAR(10),address VARCHAR(20), dob DATE, doj DATE, salary INT(8),
PRIMARY KEY (empno));
INSERT INTO EMPLOYEE_DATA VALUES
('a-101', 'suman', '20 convent road', '1976-02-03', '2003-05-05',20000),
('a-103', 'bobby', 'p.p. colony', '1973-05-07', '2002-05-07', 12000),
('b-101', 'shekhar', 'bidhannagarh kol-66', '1971-09-08', '2002-07-07', 14000),
('c-101', 'bobby', 'behala cal-34', '1970-05-07', '2002-05-07', 5000),
('a-105', 'sugoutam', 'new market cal-71', '1968-05-08', '2002-07-03', 15000),
('a-104', 'bikas', 'p.p. colony', '1973-05-07', '2002-05-07', 11000),
('a-109', 'sumanta', 'p.p. colony', '1973-05-07', '2002-05-07', 12000);

i. Write SQL query to select all employee whose salary is greater than 12000.
(sql query) :

SELECT * FROM EMPLOYEE_DATA WHERE (salary > 12000);

ii. Write SQL query to see the information of all employee.

(sql query) :

SELECT * FROM EMPLOYEE_DATA;

iii. Write SQL query to select all employee whose name is bobby.
(sql query) :

SELECT * FROM EMPLOYEE_DATA WHERE (empname = 'bobby');

iv. Write SQL query to change the salary of bikas to15000

(sql query) :

UPDATE EMPLOYEE_DATA SET salary = 15000 WHERE empno = 'a-104';

v. Delete the record of the employee whose empno is a-109

(sql query) :

DELETE FROM EMPLOYEE_DATA WHERE empno = 'a-109'; vi.

Alter the table employee adds a new column category (char (1)).
(sql query) :

ALTER TABLE EMPLOYEE_DATA ADD category CHAR(1);

vii. Alter the table employee increase the width of the column empno to 8. (sql
query) :
 ALTER TABLE EMPLOYEE_DATA MODIFY empno VARCHAR(8); viii.

Write SQL column to drop a column from a table.

(sql query) :

ALTER TABLE EMPLOYEE_DATA DROP COLUMN category; ix.

Write a SQL that will drop multiple columns from a table.

(sql query) :

ALTER TABLE EMPLOYEE_DATA ADD category CHAR(1);

ALTER TABLE EMPLOYEE_DATA ADD dodDATE;
ALTER TABLE EMPLOYEE_DATA DROP COLUMN category, DROP
COLUMN dod;

x. Create a read only view that will show empno, empname, address, salary
(sql query) :

CREATE VIEW ROV AS SELECT empno, empname, address, salary FROM

EMPLOYEE_DATA;

xi. Write a SQL Query to find the Average and Minimum and Sum of Salary of
the Employee.
(sql query) :

SELECT AVG(salary) AS AverageOfSalary FROM EMPLOYEE_DATA;

SELECT MIN(salary) AS MinimumOfSalary FROM EMPLOYEE_DATA;

 SELECT SUM(salary) ASSumOfSalary FROM EMPLOYEE_DATA;

xii. Write a SQL Query to find the Maximum Salary of the employees and it will
display Column asMaximum Salary.
(sql query) :

SELECT MAX(salary) AS MaximumSalary FROM EMPLOYEE_DATA; xiii.

Write a SQL Query to find the Average Salary of the Employees.

(sql query) :

SELECT AVG(salary) ASAverageSalaryOfEmployees FROM

EMPLOYEE_DATA;

xiv. Write a SQL Query which will show all employees (the empno, empname,
Address) whose Salary is greater than the average Salary of the employee.
(sql query) :

SELECT empno, empname, address FROM EMPLOYEE_DATA WHERE salary

> (SELECT AVG(salary) FROM EMPLOYEE_DATA);

xv. Write a SQL Query to count the number of employee whose salary is greater
than Rs.10000.
(sql query) :

SELECT COUNT(*) AS num_employees FROM EMPLOYEE_DATAWHERE

salary > 10000;

xvi. Write a SQL Query to find the names of all employees whose name start with
‘S’and ends with‘n’.
 (sql query) :

SELECT empname FROMEMPLOYEE_DATA WHERE empname LIKE 'S%n';

xvii. Write a SQL Query to compute 5*6, 4 to the power 3.

(sql query) :

SELECT 5 * 6, POW(4, 3); xviii. Write a SQL

Query to find the current time.

(sql query) :

SELECT NOW();

xix. Write a SQL Query to list all employees in ascending order of their salary.
(sql query) :

SELECT empno, empname, address, salary FROMEMPLOYEE_DATA ORDER

BY salary ASC; xx. Write a SQL Query that will set category to
• ‘A’ if salary of employee is>20000.
• ‘B’ if salary of employee is>=10000 but <20000.
• ‘C’ if Salary of employee is <10000 but >=7000.
• ‘D’ if salary of employee is<7000.
(sql query) :

SELECT empno, empname, address, salary,

CASE
WHEN salary > 20000 THEN 'A'
WHEN salary >= 10000 AND salary < 20000 THEN 'B'
WHEN salary >= 7000 AND salary < 10000 THEN 'C'
ELSE 'D'
END AS category
 FROMEMPLOYEE_DATA;

xxi. Write a SQL Query to find all employee names in whose name there are two
Consecutive ‘o’.
(sql query) :

SELECT empname FROME MPLOYEE_DATA WHERE empname LIKE '%oo%';

xxii. Write a SQL Query to find sum of salary of employee grouped by category with
a minimum sum of 30000.
(sql query) :

SELECT
CASE
WHEN salary > 20000 THEN 'A'
WHEN salary >= 10000 AND salary <= 20000 THEN 'B'
WHEN salary >= 7000 AND salary < 10000 THEN 'C'
ELSE 'D'
END AS category,
SUM(salary) AS total_salary
FROMEMPLOYEE_DATA
GROUP BY category
HAVING SUM(salary) >= 30000; xxiii. Write a SQL Query to show the list of
employee whose salary is between 7000 and 15000.
(sql query) :

SELECT empname, salary FROMEMPLOYEE_DATA WHERE salary

BETWEEN 7000 AND 15000;
xxiv. Write a SQL Query to show the list of employee whose category is A or B or C.
(sql query) :
 SELECT empname, category FROM ( SELECT empname, salary,
CASE
WHEN salary > 20000 THEN 'A'
WHEN salary >= 10000 AND salary < 20000 THEN 'B'
WHEN salary >= 7000 AND salary < 10000 THEN 'C'
ELSE 'D'
END AS category
FROMEMPLOYEE_DATA
) AS employees
WHERE category IN ('A', 'B', 'C');

xxv. Write a SQL Query to show the fields of employee separated by ‘,’.
(sql query) :

SELECT CONCAT(empno, ',', empname, ',', address, ',', dob, ',', doj, ',', salary) AS
employee_fields FROMEMPLOYEE_DATA;

xxvi. Write a SQL Query to show employee list where empname=B.Jana and
dob=07/05/73 will be displayed as ‘B. Jana born on seventh May 1973’.
(sql query) :

SELECT CONCAT(empname, ' born on ', DATE_FORMAT(dob, '%D %M %Y')) AS

employee_info FROM EMPLOYEE_DATA;

xxvii. Create a table empp with same attributes length as employee and copy
thecontent of employee into empp.
(sql query) :

CREATE TABLE empp AS SELECT * FROMEMPLOYEE_DATA WHERE

1=0;
INSERT INTO empp SELECT * FROMEMPLOYEE_DATA;
2. Create a database called COMPANY consisting of two tables-
EMPLOYEES & DEPARTMENTS

EMPLOYEES

DEPARTMENTS
i. List the names of analysts and salesmen.

(sql query) : Select ename from employees where job='analyst'or job='salesman';

(Output):

ii. List details of employees who have joined before 30 Sep 81.

(sql query) : select * from employees where hiredate<'1981-9-30';

iii. List names of employees who are not managers.

(sql query): Select ename from employees where job!='manager';

iv. List the names of employees whose employee numbers are 7369, 7521, 7839,
7934, 7788.

(sql query) Select ename from employees where empno=7369 or empno=7521 or

empno=7839 or empno=7934 or empno=7788;

v. List employees not belonging to department 30, 40, or 10.

(sql query)
SELECT ENAME FROM EMPLOYEES WHERE DEPTNO NOT IN (10, 30, 40);

vi. List employee names for those who have joined between 30 June and 31 Dec. ‘81.
(sql query)SELECT ENAME FROM EMPLOYEES WHERE HIREDATE BETWEEN '1981-
06-30' AND '1981-12-31';

vii. List the different designations in the company.

(sql query)
SELECT DISTINCT JOB FROM EMPLOYEES;

viii. List the names of employees who are not eligible for commission.
(sql query)
SELECT ENAME FROM EMPLOYEES WHERE COMM IS NULL;

ix. List the name and designation of the employee who does not report to anybody.
(sql query)
SELECT ENAME, JOB FROM EMPLOYEES WHERE MGR IS NULL;

x. List the employees not assigned to any department.

(sql query)
SELECT ENAME FROM EMPLOYEES WHERE DEPTNO IS NULL; xi.

List the employees who are eligible for commission.

(sql query)
SELECT ENAME FROM EMPLOYEES WHERE COMM IS NOT NULL;

xii. List employee’s hose names either start or end with “S”.
(sql query)
SELECT ENAME FROM EMPLOYEES WHERE ENAME LIKE 'S%' OR ENAME
LIKE '%S';

xiii. List names of employees whose names have “i” as the second character.
(sql query)
SELECT ENAME FROM EMPLOYEES WHERE ENAME LIKE '_i%'; xiv.

List the number of employees working with the company.

(sql query)
SELECT COUNT(*) FROM EMPLOYEES;

xv. List the number of designations available in the EMP table.

(sql query)

SELECT COUNT(DISTINCT JOB) FROM EMPLOYEES; xvi.

List the total salaries paid to the employees.

(sql query)
SELECT SUM(SAL) FROM EMPLOYEES;

xvii. List the maximum, minimum and average salary in the company.
(sql query)
 SELECT MAX(SAL), MIN(SAL), AVG(SAL) FROM EMPLOYEES; xviii.

List the maximum salary paid to a salesman.

(sql query)
SELECT MAX(SAL) FROM EMPLOYEES WHERE JOB = 'Salesman';
3. A. Write a PL/SQL that will take a number as input and calculate sum of its digits .

[PL/SQL]:-
Drop procedure sumofdigits; Delimiter
//
create procedure sumofdigits(in n int(4))
begin declare r integer; declare s
integer; set @s = 0; while @n<>0 do set
@r=@n%10; set @s=@s+@r;
set@n=@n div 10; end while; select
@s; end // delimiter ; set @n=12345;
call sumofdigits(@n);

[OUTPUT]:-

3.B. Write a PL/SQL that will generate the natural series from 1 to 10.

[PL/SQL-1]:-
Drop procedure Natural_number; Delimiter
//
create procedure Natural_number() begin
Declare n INT;
Declare str VARCHAR(255);
set n=1; set str='';
 label1: Loop
IF n>10 THEN
LEAVE label1;
END IF;
set str= CONCAT(str,n,','); set
n=n+1;
END LOOP;
SELECT str;
END //
Delimiter ; call
Natural_number;
[OUTPUT 1]:-

[PL/SQL-2]:-
Drop procedure Natural_number;
Delimiter // create procedure
Natural_number () begin declare n
integer; declare str
VARCHAR(255);
 set str = ''; set n=0; while
n<10 do set n= n+1; set
str=CONCAT(str,n,',');
end while; select str; end
// Delimiter ; call
Natural_number();

[OUTPUT 2]:-

3.C. Write a PL/SQL that will generate the Fibonacci series upto7 terms.

[PL/SQL]:-
Drop Procedure Fibo;
Delimiter //
Create Procedure Fibo()
begin declare f INT; declare
f1 INT; declare f2 INT;
declare n INT; declare str
VARCHAR(255); set f1=0;
set f2=1; set n=0; set
str='0,1,';
 while n<5 do set f=f1+f2;
set f1=f2; set f2=f; set
str=CONCAT(str,f,',');
set n=n+1; end while;
select str; end // Delimiter
; call Fibo();

[OUTPUT]:-

3.D. Write a PL/SQL that will accept a string and reverse it.

[PL/SQL]:-
Drop procedure rString; Delimiter
//
create procedure rString (in str varchar(20), inout rstr
varchar(20)) begin
set rstr = reverse(@str); end
//
delimiter ; set
@str='Abir
chakraborty'; set
@rstr='';
call rString(@str,@rstr); select
@rstr;
[OUTPUT]:-

3. E. WAP which accepts the student rollno and returns the highest percent
and name of that student to thecalling block.
STUDENT(Stud_ID,Stud_name,percent);

[PL/SQL]:-
create table student (studId integer primary key,studName varchar(25),percent
decimal);
insert into student values(1,'Abir roy',65.25);
insert into student values(2,'Sushant',72.85); insert
into student values(3,'Dikvijay',82.74);
select * from student; Drop
procedure findStudent;
Delimiter $$
create procedure findStudent (in roll int(4), out sname varchar(25),out per decimal)
begin select studName into sname from student where studId=roll; select percent
into per from student where studId=roll; end $$ delimiter ; set @r=2; set @sn=' ';
set @p=1.0;
call findStudent(@r,@sn,@p); select
@sn,@p;
[OUTPUT]:-

3.F. Write a procedure which accepts the empno and returns the associated
empname. If empno does notexist than give proper error message.
EMP(Empno, Empname).

[PL/SQL]:-
Drop table emp;
create table emp(empno int ,empname varchar(35));
insert into emp values(01,'Abir roy'),(02,'Ram Prasad'),(03,'Ujjal Karmakar'),(04,'shyam
Roy');

select * from emp;

Drop procedure checkId;

delimiter //
create procedure checkId(in eid int(5),out ename varchar(100)) begin
declare msg varchar(255); set
msg='Employee ID No not exist';
select empname into ename from emp where
empno=eid; if ename is Null then select msg;
end if;
end //

delimiter ; set @id=1;

set @n=’’; call
checkId(@id,@n);
select @n;

set @id=5; set @n=''; call

checkId(@id,@n); select
@n;

[OUTPUT- 1]:-

[OUTPUT- 2]:-

4.A. Create a cursor for the emp table. Produce the output in following format:
{empname} employee working in department {deptno} earns Rs. {salary}.
EMP(empno, empname, salary, deptno);
[PL/SQL]:-
Drop table emp;
create table emp(eno int primary key,ename varchar(35),salary decimal(10,2),deptno int);

insert into emp values(1,'Abir roy',125000.00,10),(2,'Mohit

Kumar',25000.00,11),(3,'Rohit Pandey',50000.00,10),(4,'Monoj Saha',35000.00,10);

select * from emp;

drop procedure showRecord();

delimiter //
create procedure showRecord() begin
declare name varchar(255);
declare dno int; declare sal
decimal(10,2); declare
finished int default 0;
declare msg varchar(255);
declare emp_cursor cursor for select ename,deptno,salary from emp;
declare continue handler for not found set finished=1; open
emp_cursor; set msg=''; get_information: loop
fetch emp_cursor into
name,dno,sal; if finished=1 then
leave get_information; end if;
set msg=CONCAT(name," working in department ",dno," earn RS.",sal);
select msg; end loop;
close emp_cursor ;
end //
delimiter ;

call showRecord();
[OUTPUT]:-

4.B. Create a cursor for updating the salary of emp working in deptno 10 by
20%. If any rows are affected than display the no of rows affected. Use implicit
cursor.

[PL/SQL]:- select

* from emp;
Drop procedure salaryUpdate(); delimiter
//
create procedure salaryUpdate() begin
declare count int;
declare id int; declare sal
decimal(10,2); declare
finished int default 0;
declare emp_cursor cursor for select eno,salary from emp where deptno=10;
declare continue handler for not found set finished=1; open emp_cursor; set
count =0; get_record: loop fetch emp_cursor into id,sal;
if finished=1 then LEAVE
get_record;
end if;
update emp set salary=salary*1.2 where eno=id;
set count=count+1; end loop; close emp_cursor;
select count; end // delimiter ; call
salaryUpdate();
select * from emp;

[OUTPUT]:-
Before Update

After Update
5.A. Write a Trigger that stores the old data table of student table in
student_backup while updating thestudent table.Student_backup (Stud_ID,
Stud_name, Address, Contact_no, Branch, Operation_date)
Student (Stud_ID, Stud_name, Address, Contact_no, Branch)

[PL/SQL]:-

create table student(studId integer primary key,studName varchar(25));

insert into student values(1,'gk'); insert into student values(2,'rs');
select * from student;
create table studentBackup(studId integer primary key,studName varchar(25),
opDate date);
insert into studentBackup values(1,'gk','11-21-2022');
select * from studentBackup; delete from
studentBackup; drop trigger backupTrigger;
delimiter // create trigger backupTrigger before
update on student for each row begin
insert into studentBackup set studId=OLD.studId,
studName=OLD.studName,
opDate=NOW(); end // delimiter ;
update student set studName='ratna' where studId=2;

[OUTPUT]:-
5.B. Write a trigger which checks the age of employee while inserting the
record in emp table. If it is negativethan generate the error and display proper
message.

[PL/SQL]:-
create table employee(empId integer primary key,empName varchar(25),empAge
integer);
insert into employee values(1,'gk',25);
select * from employee; drop trigger
checkTrigger; delimiter // create
trigger checkTrigger before insert on
employee for each row begin declare
msg varchar(50); if NEW.empAge <
0 then
set NEW.empAge = 0;
end if; end
//
delimiter ;
insert into employee values(3,'cd',-5);
select * from employee; [OUTPUT]:-
Filename: DBMS ASSIGNMENT2
Directory: C:\Users\Abir chakraborty\Documents
Template: C:\Users\Abir
chakraborty\AppData\Roaming\Microsoft\Templates\Normal.dotm
Title:
Subject:
Author: Deep Das
Keywords:
Comments:
Creation Date: 22-02-2023 00:09:00
Change Number: 1
Last Saved On: 22-02-2023 00:09:00
Last Saved By:
Total Editing Time: 28 Minutes
Last Printed On: 22-02-2023 00:42:00
As of Last Complete Printing
Number of Pages: 25
Number of Words: 2,398 (approx.)
Number of Characters: 13,673 (approx.)