dbms calicut university B. sc Cs 4th sem.pdf

Its a seminar ppt on database management system using sql

1. DBMS
SEMINAR
SHINANA UZHUNNAN
27 MAY, 2024
201

2. Q 8
Consider the employee database given below. Give an expression in SQL for each of the following queries:
EMPLOYEE (Employee-Name, City) WORKS (Employee-Name, Company-Name, Salary) COMPANY (Company-Name,
City) MANAGES (Employee-Name, Manager-Name)
EMPLOYEE (Employee-Name, City)
create table employee(empname varchar(20) primary key,city varchar(20));
COMPANY (Company-Name, City)
create table company(company_name varchar(20) primary key,city varchar(20));
WORKS (Employee-Name, Company-Name, Salary)
create table works(empname varchar(20) primary key references employee(empname),cname varchar(20) references
company(company_name),salary int);
MANAGES (Employee-Name, Manager-Name)
create table manages(empname varchar(20) references employee(empname),manager_name varchar(20) references
employee(empname),primary key(empname,manager_name));
Table Creation

3. insert into employee values('Swathi','Kozhikode'),('Vishnu','Trivandrum'),('Shreya','USA'), ('Adam','Dubai'),('Sajid','Malappuram');
select * from employee;
insert into company values('Infosys','Trivandrum'),('Chandrika','Trissur'),('Wipro','Kochi'), ('Tata','Mumbai'),('Bajaj','Delhi');
select * from company;
insert into works values('Swathi','Infosys',10000),('Vishnu','Infosys',15000), ('Shreya','Wipro',71500,('Adam','Infosys',8000),
('Sajid','Wipro',18000);
select * from works;
insert into manages values('Swathi','Adam'),('Vishnu','Adam'),('Sajid','Shreya');
select * from manages;
insertion to tables

6. A) Find the names of all employees who work in Infosys
B) Find the names and cities of residence of all employees who works in Wipro
C) Find the names, and cities of all employees who work in Infosys and earn
more than Rs. 10,000.
D) Find the employees who live in the same cities as the companies for which
they work.
E) Find all employees who do not work in Wipro Corporation.
F) Find the company that has the most employees.

7. A)
B)
C)
D)

8. E)
F)

9. Q 14
Create a function to check whether a given number is prime or not
create or replace function prime(n int)
returns varchar(25) as $$
declare i int;
begin if n<2 then return n ||'is not a prime no';
end if;
for i in 2..n/2 loop if mod(n,i)=0 then return n ||'is not a prime
no';
end if;
end loop;
return n||'is a prime no';
end;
$$ language plpgsql;
select prime(5);

11. Thank You
SHINANA UZHUNNAN