228W1A12J1

WEEK 6
Date: 09/03/24

Aim: Implement queries using SQL functions

 Aggregate functions
 Grouping (Group by, Having)
 Sorting

AGGREGATE FUNCTIONS:

Syntax :

SELECT c1, aggregate_function(c2) FROM table GROUP BY c1;

consider a table;

The following are the commonly used SQL aggregate functions:

 AVG() – returns the average of a set.

syntax: select avg(col_name) from table_name where condition group by col_name;

 228W1A12J1

 COUNT() – returns the number of items in a set.

syntax: select count(col_name) from table_name where condition group by col_name;

 MAX() – returns the maximum value in a set.

syntax: select max(col_name) from table_name where condition group by col_name;

 MIN() – returns the minimum value in a set

syntax: select min(col_name) from table_name where condition group by col_name;

 228W1A12J1

 SUM() – returns the sum of all or distinct values in a set

syntax: select sum(col_name) from table_name;

GROUPING (GROUP BY AND HAVING):

The GROUP BY clause is used in SQL to group rows that have the same values into summary rows. It is often
used in conjunction with aggregate functions like SUM, COUNT, AVG, MAX, and MIN.

Syntax:

SELECT column1, column2, ..., aggregate_function(column) FROM table_name GROUP BY column1,

column2, ... [HAVING condition];

Let us consider two tables:

 228W1A12J1

Employees Table:

a. Get the total salary for each department where the total salary is greater than $500:

select department, sum(salary) as total_salary from employees group by department having sum(salary) >
500;

b. Get the number of orders and the total order amount for each employee who has placed more
than 1 orders:
 228W1A12J1

select emp_id, count(order_id) as order_count, sum(order_amount) as total_amount from orders group by

emp_id having count(order_id) > 1;

SORTING(using order by):

Syntax :

SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC |
DESC];
 228W1A12J1

WEEK 7
Date: 16/03/24

Aim: to implement queries using key constraints

Task-1:
I.Consider the following schema for a Library Database:
BOOK (Book_id p, Title, Publisher_Name f, Pub_Year)

BOOK_AUTHORS (Book_idf ,Author_Name p)

PUBLISHER (Name p, Address, Phone)

BOOK_COPIES (Book_id f, Branch_id f, No-of_Copies)

BOOK_LENDING (Book_id f, Branch_id f, Card_No p, Date_Out, Due_Date)

LIBRARY_BRANCH (Branch_id p, Branch_Name, Address)

Creating library database:

mysql> create database db;

mysql> use db;

Library_branch tale:

mysql> create table library_branch(branch_id int(50) primary key,branch_name varchar(50),address

varchar(50));

mysql> insert into library_branch values(150,"joseph branch","4/190 joseph"),(167,"st.marry","3-987 marie

street"),(133,"youna gardens","ihvana street");

mysql> select * from library_branch;

Publisher table:

mysql> create table publisher(name varchar(50) primary key,address varchar(50),phone int(15));

 228W1A12J1

mysql> insert into publisher values("charlie","8/45 josph gardens",6754389654),("John","33-786 st.mary

street",9876545670),("Sharon","88/A peter church",7766554433);

mysql> select * from publisher;

Book1 table:

mysql> create table book1(book_id varchar(10) primary key,title varchar(50),publisher_name

varchar(50),pub_year year,foreign key(publisher_name) references publisher(name));

mysql> insert into book1 values(1456,"the wings of fire","charlie",2000),(1345,"the dark key","john",2010),

(1188,"five keys","sharon",2012);

mysql> select * from book1;

Book_copies table:

mysql> create table book_copies(book_id varchar(10),branch_id int(50),no_of_copies int(100),foreign

key(book_id) references book1(book_id),foreign key(branch_id) references library_branch(branch_id));

mysql> insert into book_copies values(1188,133,100),(1345,150,100),(1456,167,200),(1456,150,200);

mysql> select * from book_copies;

 228W1A12J1

Book_lending table:

mysql> create table book_lending(card_no int(50) primary key,date_out date, due_date date,book_id
varchar(50),branch_id int(50),foreign key(book_id) references book1(book_id),foreign key(branch_id)
references library_branch(branch_id));

mysql> insert into book_lending values(123,'2024.03.25','2024.03.15',1188,133),

(145,'2024.04.01','2024.03.21',1456,167),(345,'2024.04.03','2024.03.11',1345,150);

mysql> select * from book_lending;

Book_author table:

mysql> create table book_author(author_name varchar(100) primary key,book_id varchar(10), foreign

key(book_id) references book1(book_id));

mysql> insert into book_author values("Haleena",1188),("Vasu",1345),("rosie",1456);

mysql> select * from book_author;

Write SQL queries to

1. Retrieve details of all books in the library – id, title, name of publisher, authors, number of
copies in each branch, etc.
mysql> select book1.book_id, book1.title, book1.publisher_name, book_copies.no_of_copies,
book_author.author_name from book1, book_copies,book_author where
book1.book_id=book_copies.book_id and book1.book_id=book_author.book_id;
 228W1A12J1

2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to
Jun 2017
mysql> select b.card_no,count(*) as borrowed from book_lending b where b.date_out between '2024.01.01'
and '2024.06.01' group by b.card_no having count(*)>=1;

3. Delete a book in BOOK table. Update the contents of other tables to reflect this data
manipulation operation.
mysql> delete from book_copies where book_id='1188';

mysql> delete from book_lending where book_id='1188';

mysql> delete from book_author where book_id='1188';

mysql> delete from book1 where book_id='1188';

mysql> select * from book1;

 228W1A12J1

4. Partition the BOOK table based on year of publication. Demonstrate its working with a simple
query.
mysql> create view yr2000 as select * from book1 where pub_year='2000';

mysql> create view yr2010 as select * from book1 where pub_year='2010';

mysql> select * from yr2000;

5. Create a view of all books and its number of copies that are currently available in the Library.
mysql> create view view1 as select b.book_id,c.no_of_copies from book1 b,book_copies c where
b.book_id=c.book_id;

mysql> select * from view1;

 228W1A12J1

Task-2:

II. Consider the following schema

SALESMAN(Salesman_id p,Name,City,Commission)

CUSTOMER (Customer_idp,Cust_Name,City,Grade,Salesman_id f)

ORDERS(Ord_No p,Purchase_Amt,Ord_Date,Customer_id f,Salesman_id f)

Database creation:

mysql> create database sales;

mysql> use sales;
salesman table:
mysql> create table salesman (s_id varchar(10) primary key,s_name varchar(20),city varchar(10),comission
decimal(10,2));
mysql> insert into salesman values ("123","David","Mexico","12.99"),("456","Rheo","sweden","19.8"),
("789","Don","ohoi","56.1");
mysql> select * from salesman;

customer table:
mysql> create table customer (c_id varchar(10) primary key,c_name varchar(100),city varchar(15),grade
int(1),s_id varchar(10),foreign key(s_id) references salesman(s_id));
mysql> insert into customer values ("234","rosie","ohoi","4","789"),("567","emma","mexico",'5',"123"),
('890','luna','mexico','3','123'),('999','olivia','sweden','3','456');
mysql> select * from customer;
 228W1A12J1

orders table:
mysql> create table orders1 (o_no int primary key,p_am decimal(10,5),o_date date,c_id varchar(10),s_id
varchar(10), foreign key(c_id) references customer(c_id), foreign key(s_id) references salesman(s_id));
mysql> insert into orders1 values (45,'120000.78','2024.01.03','234','789'),
(77,'30000','2024.02.11','567','123'),(89,'23000.09','2023.12.12','890','123'),
(101,'56000','2023.11.12','999','456');
mysql> select * from orders1;

Write SQL queries to

1. Count the customers with grades above Bangalore’s average.

mysql> select count(*) as above_grades from customer where grade>(select avg(grade) from customer
where city="mexico") and city!="mexico";

2. Find the name and numbers of all salesmen who had more than one customer.
 228W1A12J1

mysql> select s.s_name,count(*) as total_customers from salesman s,customer c where s.s_id=c.s_id group
by s.s_id having count(*)>1;

3. List all salesmen and indicate those who have and don’t have customers in their cities (Use UNION
operation.)
mysql> select s.s_id,s.s_name,"has customer" as status from salesman s where s.s_id in (select distinct s_id
from customer) union select s.s_id,s.s_name,"no customer" as status from salesman s where s.s_id in (select
distinct s_id from customer);

4. Create a view that finds the salesman who has the customer with the highest order of a day.
mysql> create view view4 as select s.s_name,max(o.p_am) from salesman s,orders1 o where s.s_id=o.s_id
group by s_name;
mysql> select * from view4;
+-----------+--------------------+
| s_name | max(o.p_am) |
+-----------+-------------------+
| Don | 99999.99999 |
| David | 30000.00000 |
| Rheo | 56000.00000 |
+-----------+-------------------+

5. Demonstrate the DELETE operation by removing salesman with id 1000.All his orders must also
be deleted.
mysql> delete from orders1 where s_id="123";
mysql> delete from customer where s_id="123";
mysql> delete from salesman where s_id="123";
mysql> select * from orders1;
 228W1A12J1

mysql> select * from customer;

mysql> select * from salesman;
 228W1A12J1

Task-3:
III. Create the following tables with appropriate data types and Integrity constraints:
PERSON(driver_id, name, address)

PARTICIPATED(driver_id, regno, report_number,damage_amount)

Person table:

mysql> create table person1 (d_id char(10) primary key,d_name char(100),address char(100));

mysql> insert into person1 values ("12345","lucy","29-9 ambedkarstreet,vij"),("67890","rosie","24-24-8

chandanagar ,hyd"),("24680","emma","787/A gandhi colony"),("13579","emily","3/A6 nehru colony");

mysql> select * from person1;

Participate table:

mysql> create table participate (d_id char(10),regno char(10),rp_no int(10),damage_amount int,foreign

key(d_id) references person1(d_id));

mysql> insert into participate values ("12345","34",77,12000),("24680","23",89,50000),

('13579','78',23,100000),('67890','99',101,76000);

mysql> select * from participate;

 228W1A12J1

1. Display the names of the drivers starting with ‘S’

mysql> select d_name from person1 where d_name like "e%";

2. Display the ids of drivers whose damage_amount is > 50000 and who belongs to Vijayawada

mysql> select o.d_id from person1 o,participate p where p.damage_amount>10000 and o.address like "%,vij"
and o.d_id=p.d_id;

3. Implement the queries using any 3string functions

mysql> select upper(d_name) from person1;

mysql> select length(d_name) from person1;

mysql> select ascii(d_name) from person1;

mysql> select char_length(d_name) from person1;

228W1A12J1
 228W1A12J1

Task-4:

IV.Create the following tables

PERSON(driver_id, name, address,carregno,gender)

CAR(regno, model,year )

Person table:

mysql> create table person (driver_id int primary key,name varchar(50),address varchar(100),carregno
varchar(10), gender char(1),foreign key(carregno) references car(regno));

mysql> insert into person values (101,"Rani","Vijayawada","APG546","Female"),

(453,"Raju","Tirupathi","TSH675","Male"),(567,"Vishwak","Vizag","GTj789","Female");

Car table:

mysql> create table car (regno varchar(50) primary key, model varchar(20),year year);

mysql> insert into car values ("APG546","mahindra","2018"),("TSH675","Renault","2019"),

("GTj789","Maruti","2017"),("JHI86H","Kia","2019");
 228W1A12J1

1. Display the names of all female drivers

2. Display the names and driver ids of persons who had a car model before year 2020

WEEK 8
 228W1A12J1

Date: 23/03/24

Aim: Implement Nested Queries using operators

Let us consider two tables:

1. Find employees who locate in a different location.

select e.first_name, e.last_name, e.dept_id, d.location from employee e, department d where e.dept_id =
d.dept_id and e.dept_id <> d.location;

2. Find all employees who salaries are greater than the average salary of all employees

select first_name, last_name, salary from employee where salary > (select avg(salary) from employee);
 228W1A12J1

3. Find all departments which have at least one employee with the salary is greater than 10,000

select distinct d.dept_name from department d where exists ( select 1 from employee e where e.dept_id =
d.dept_id and e.salary > 10000 );

4. Retrieve the lowest salary by department SELECT departname-id, MIN(salary) FROM employees
GROUP BY department_id ORDER BY MIN(salary)DESC;

select d.dept_name, min(e.salary) as lowest_salary from department d, employee e where d.dept_id =

e.dept_id group by d.dept_name order by lowest_salary desc;

5. Get all employees whose salaries are greater than the lowest salary of every department

select first_name, last_name, salary from employee e1 where e1.salary > ( select min(salary) from employee
e2 where e2.dept_id = e1.dept_id );
 228W1A12J1

6. Find all employees whose salaries are greater than or equal to the highest salary of every
department.

select first_name, last_name, salary from employee e1 where e1.salary >= ( select max(salary) from
employee e2 where e2.dept_id = e1.dept_id );

7. Retrieve the salaries of all employees, their average salary, and the difference between the salary
of each employee and the average salary.

select salary, (select avg(salary) from employee) as avg_salary, salary - (select avg(salary) from employee) as
diff_from_avg from employee;

8. Find the employees who have the highest salary

select first_name, last_name, salary from employee where salary = ( select max(salary) from employee);
 228W1A12J1

9. Find all departments that do not have any employee with the salary greater than 10,000

select dept_name from department d where not exists ( select 1 from employee e where e.dept_id =
d.dept_id and e.salary > 10000 );

Empty set (0.00 sec)

 228W1A12J1

WEEK 9
Date: 06.04.24

Aim: Implement Nested Queries using operators

 Correlated sub queries

 Set operators

Implement Nested Queries using set operators :

a. ANY operator

The ANY operator:

returns a boolean value as a result , returns TRUE if ANY of the subquery values meet the condition

ANY means that the condition will be true if the operation is true for any of the values in the range.

ANY Syntax

SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name
FROM table_name WHERE condition);

Command:

select all employees who have a salary greater than ANY of the salaries of employees in the "IT" department

b. All:

The ALL operator:

 228W1A12J1

returns a boolean value as a result

returns TRUE if ALL of the subquery values meet the condition

ALL Syntax With SELECT

SELECT ALL column_name(s) FROM table_name WHERE condition;

ALL Syntax With WHERE or HAVING

SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name
FROM table_name WHERE condition);

Command:

selects all employee names from the "employees" table where the salary is greater than 50000.

c. Some:

SOME operator evaluates the condition between the outer and inner tables and evaluates to true if the final
result returns any one row. If not, then it evaluates to false.

The SOME and ANY comparison conditions are similar to each other and are completely interchangeable.

SOME must match at least one row in the subquery and must be preceded by comparison operators.

Syntax:

SELECT column_name(s) FROM table_name WHERE expression comparison_operator SOME (subquery)

Command:

selects the names of employees whose salary is greater than at least one salary in the HR department.
 228W1A12J1

d. Exists:

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

EXISTS Syntax

SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE
condition);

Command:

select the names of employees who have a salary greater than 60000

e. IN :

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

Syntax :

SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT);

NOT IN

Command:
 228W1A12J1

selects all columns from the "employees" table where the department is either 'HR' or 'IT'.

selects all columns from the "employees" table where the department is located in India, based on the
"departments" table

selects all columns from the "employees" table where the department is located in India, based on the
"departments" table.

GROUP BY and HAVING functions with the COUNT function

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number
of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to
group the result-set by one or more columns.
 228W1A12J1

GROUP BY Syntax

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY

column_name(s);

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate
functions.

HAVING Syntax

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s)

HAVING condition ORDER BY column_name(s);

Command:

Between

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

BETWEEN Syntax

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

command:

selects all columns from the "employees" table where the salary falls within the range of 50000 to 70000
 228W1A12J1

SQL Operators:

The SQL Set operation is used to combine the two or more SQL SELECT statements.

Types of Set Operation

1. Union

2. UnionAll

3. Intersect

4. Minus

Consider these two tables;

1.Union:

The SQL Union operation is used to combine the result of two or more SQL SELECT queries. In the union
operation, all the number of datatype and columns must be same in both the tables on which UNION
operation is being applied. The union operation eliminates the duplicate rows from its resultset.

Syntax

SELECT column_name FROM table1 UNION SELECT column_name FROM table2;

 228W1A12J1

2.Union All:

Union All operation is equal to the Union operation. It returns the set without removing duplication and
sorting the data.

Syntax:

SELECT column_name FROM table1 UNION ALL SELECT column_name FROM table2;

3. Intersect:

It is used to combine two SELECT statements. The Intersect operation returns the common rows from both
the SELECT statements. In the Intersect operation, the number of datatype and columns must be the same. It
has no duplicates and it arranges the data in ascending order by default.

Syntax

SELECT column_name FROM table1 INTERSECT SELECT column_name FROM table2;

 228W1A12J1

4.Minus:

It combines the result of two SELECT statements. Minus operator is used to display the rows which are
present in the first query but absent in the second query. It has no duplicates and data arranged in ascending
order by default.

Syntax:

SELECT column_name FROM table1 MINUS SELECT column_name FROM table2;

 228W1A12J1

WEEK 10
Date: 13/04/2024

AIM:
Implement advanced queries using joins and grouping (Group by, Having)

Queries:

Equi Join:

select e.*, d.department_name from employees e join departments d on e.department_id =

d.department_id;

Non-Equi Join:

select e.*, d.department_name from employees e join departments d on e.salary> (select avg(salary) from
employees);

Left Outer Join:

select e.*, d.department_name from employees e left join departments d on e.department_id =

d.department_id;
 228W1A12J1

Right Outer Join:

select e.*, d.department_name from employees e right join departments d on e.department_id =

d.department_id;

Full Outer Join:

select e.*, d.department_name from employees e left join departments d on e.department_id =

d.department_id union select e.*, d.department_name from employees e right join departments d on
e.department_id = d.department_id;

Self-Join:

select e.first_name, e.last_name as employee_id, d.department_namefrom employees ejoin departments d

on e.department_id = d.department_id;
 228W1A12J1

Group by , having:

create table student(sid int primary key,sname varchar(20),sadd varchar(30),sage int(2),sphn int);

insert into student values(1,"John","colony 1",31,125456),(2,"Robert","colony 2",22,123756),

(3,"David","colony 3",22,123454),(4,"Betty","colony 4",25,129456);

select * from student;

create table course(cid varchar(2) primary key,cname varchar(20));

insert into course values('c1','dsa') ,('c2','dbms'),('c3','java'),('c4','r');

select * from course;

create table studentcourse(sidint,cid varchar(2),foreign key(sid) references student(sid),foreign key(cid)

references course(cid));

insert into studentcourse values(4,'c4'),(1,'c1'),(2,'c2'),(3,'c1');

 228W1A12J1

select * from studentcourse;

Find C_ID for C_NAME =‟DSA‟ or „DBMS‟

select cid from course where cname='dsa' or cname='dbms';

Find out names of STUDENTs who have either enrolled in „DSA‟ or „DBMS

select s.sname from student s where s.sidin( select sc.sid from studentcoursesc where sc.cidin( select c.cid
from course c where c.cname='dsa' or c.cname='dbms') );

Find out S_IDs of STUDENTs who have neither enrolled in „DSA‟ nor in „DBMS‟

select s.sid from student s where s.sidin( select sc.sid from studentcoursesc where sc.cid in ( select c.cid
from course c where c.cname<>'dsa' and c.cname<>'dbms' ) );
 228W1A12J1

find out S_NAME of STUDENTs who are enrolled in C_ID „C1‟,

select s.sname from student s where s.sidin ( select sc.sid from studentcourses c where sc.cidin ( select c.cid
from course c wherec.cid='c1'));
 228W1A12J1

WEEK 11
Date: 20.04.24

Aim: Combining tables and execution of queries

Practicing Queries on key constraints

First let us create the database, relations and insert the values into the relations:

Database Company:

mysql> create database Company;

mysql> use Company;

Department table:

mysql> create table department(dep_id integer primary key,dep_name varchar(10),dep_loc varchar(15));

mysql> insert into department values(1,"Project","Delhi"),(2,"HR","Delhi"),(3,"developers","Mumbai"),

(4,"accoutant","Hyderabad"),(5,"clerk","Hyderabad");

mysql> select * from department;

 228W1A12J1

Employee table:

mysql> create table employee (emp_id integer primary key,emp_name varchar(15),job_name

varchar(10),manager_id integer,hire_date date,salary decimal(10,2),comission decimal(7,2),dep_id
integer,foreign key(dep_id) references department(dep_id));

mysql> insert into employee values (12,"Asha","accountant",33,"14.01.7",25000,1000,4),

(14,"Bhanu","HR",33,"20.02.05",300000,10000,2),(45,"Cherry","developer",33,"18.08.6",100000,9000,3),
(67,"Dhruva","Project",33,"18.09.6",100000,1000,1);

mysql> select * from employee;

salary_grade table:

mysql> create table salary_grade (grade integer primary key,min_salary integer,max_salary integer);

mysql> insert into salary_grade values(1,100000,500000),(2,50000,400000),(3,25000,300000),

(4,25000,200000);

mysql> select * from salary_grade;

 228W1A12J1

Practice queries:

1. find the salaries of all employees.

mysql> select emp_name,salary from employee;

2. find the unique designations of the employees.

mysql> select distinct job_name from employee;

3. list the employees’ name, increase their salary by 15%

mysql> select emp_name,salary*0.15 from employee;

 228W1A12J1

4. find those employees with hire date in the format like February 22, 1991

mysql> select emp_name from employee where date_format(hire_date,'%M %e,%Y')='February 22,1991';

Empty set (0.00 sec)

5. find the unique department with jobs.

mysql> select distinct dep_id,job_name from employee;

6. find those employees who do not belong to the department 2001

mysql> select emp_name from employee where dep_id!=4;

7. find those employees who joined before 1991.

mysql> select emp_name from employee where year(hire_date)<2020;

 228W1A12J1

8. calculate the average salary of employees who work as analysts

mysql> select avg(salary) from employee where job_name="HR";

9. find the details of the employee ‘BLAZE’.

mysql> select * from employee where emp_name='Bhanu';

10. Identify employees whose commissions exceed their salaries

mysql> select emp_name from employee where salary<comission;

Empty set (0.03 sec)

11. Identify those employees whose salaries exceed 3000 after receiving a 25% salary increase.

mysql> select * from employee where (salary*0.25)>3000;

 228W1A12J1

12. find the names of the employees whose length is six.

mysql> select emp_name from employee where length(emp_name)=6;

13. find out which employees joined in the month of January.

mysql> select emp_name,hire_date from employee where month(hire_date)="january";

Empty set, 1 warning (0.00 sec)

14. find those employees whose designation is “ACCOUNTANT”

mysql> select emp_name,job_name from employee where job_name="accountant";

15. identify employees with more than 10 years of experience

mysql> select emp_name from employee where hire_date-curdate()>10;

Empty set (0.00 sec)

 228W1A12J1

16. find those employees whose salaries are less than 3500

17. find those employees who joined before 1st April 1991

mysql> select emp_name from employee where date_format(hire_date,'%est %M %Y')<'1st April 1991';

Empty set (0.00 sec)

18. identify the employees who do not report to a manager

mysql> select emp_name from employee where manager_id=NULL;

Empty set (0.00 sec)

19. find out which employees earn more than 100 per day as a salary.

mysql> select emp_name from employee where (salary/(12*30))>100;

20. identify the employees whose salaries are odd.

mysql> select emp_name from employee where mod(salary,2)=1;

Empty set (0.00 sec)

21. find those employees whose designation is ‘CLERK’ and work in the department ID 2001

mysql> select emp_name from employee where job_name="hr" and dep_id=2;

 228W1A12J1

22. find those employees who are either CLERK or MANAGER and identify those employees who joined
in any month other than February

mysql> select emp_name from employee where (job_name="clerk" or job_name="manager") and

month(hire_date)!="february";

Empty set (0.00 sec)

23. Search for all employees with an annual salary between 24000 and 50000

mysql> select emp_name from employee where salary between 24000 and 50000;

24. find out which employees are working under the managers 63679, 68319, 66564, or 69000

mysql> select emp_name from employee where manager_id in(63679,68319,66564,69000);

Empty set (0.01 sec)

25. find those employees who joined in 90's.

mysql> select emp_name from employee where year(hire_date) between 1990 and 1999;

Empty set (0.00 sec)

26. find those managers who are in the department 1001 or 2001

mysql> select emp_name from employee where job_name="manager" and dep_id in(1001,2001);

Empty set (0.00 sec)

 228W1A12J1

27. find employees along with their department details.

mysql> select employee.emp_name,employee.dep_id,department.dep_name,department.dep_loc from

employee,department where employee.dep_id=department.dep_id;

28. identify employees whose salaries are higher than their managers' salaries

mysql> SELECT e.emp_name, e.salary, m.emp_name AS manager_name, m.salary AS manager_salary FROM

employee e, employee m WHERE e.manager_id = m.emp_id AND e.salary > m.salary;

Empty set (0.00 sec)

29. find those employees whose salary is between 2000 and 5000 (Begin and end values are included.)
and location is PERTH

mysql> SELECT * FROM employee WHERE salary BETWEEN 2000 AND 5000 AND dep_id = (SELECT dep_id
FROM department WHERE dep_loc = 'PERTH');

Empty set (0.00 sec)

30. find those employees whose manager name is JONAS.

mysql> SELECT e.emp_name, m.emp_name AS manager_name FROM employee e, employee m WHERE

e.manager_id = m.emp_id AND m.emp_name = 'JONAS';

Empty set (0.00 sec)

 228W1A12J1

WEEK-12
27/04/2024

Aim: ER diagrams

1. Bank database ER diagram:

ER diagram is known as Entity-Relationship diagram. It is used to analyze to structure of the Database. It

shows relationships between entities and their attributes. An ER model provides a means of communication.

ER diagram of Bank has the following description :

 Bank have Customer.

 Banks are identified by a name, code, address of main office.

 Banks have branches.

 Branches are identified by a branch_no., branch_name, address.

 Customers are identified by name, cust-id, phone number, address.

 Customer can have one or more accounts.

 Accounts are identified by account_no., acc_type, balance.

 228W1A12J1

 Customer can avail loans.

 Loans are identified by loan_id, loan_type and amount.

 Account and loans are related to bank’s branch.

Entities and their Attributes are :

 Bank Entity : Attributes of Bank Entity are Bank Name, Code and Address.
Code is Primary Key for Bank Entity.

 Customer Entity : Attributes of Customer Entity are Customer_id, Name, Phone Number and
Address.
Customer_id is Primary Key for Customer Entity.

 Branch Entity : Attributes of Branch Entity are Branch_id, Name and Address.
Branch_id is Primary Key for Branch Entity.

 Account Entity : Attributes of Account Entity are Account_number, Account_Type and Balance.
Account_number is Primary Key for Account Entity.

 Loan Entity : Attributes of Loan Entity are Loan_id, Loan_Type and Amount.
Loan_id is Primary Key for Loan Entity.

Relationships are :

 Bank has Branches => 1 : N

One Bank can have many Branches but one Branch can not belong to many Banks, so the relationship
between Bank and Branch is one to many relationship.

 Branch maintain Accounts => 1 : N

One Branch can have many Accounts but one Account can not belong to many Branches, so the
relationship between Branch and Account is one to many relationship.

 Branch offer Loans => 1 : N

One Branch can have many Loans but one Loan can not belong to many Branches, so the relationship
between Branch and Loan is one to many relationship.

 Account held by Customers => M : N

One Customer can have more than one Accounts and also One Account can be held by one or more
Customers, so the relationship between Account and Customers is many to many relationship.

 Loan availed by Customer => M : N

(Assume loan can be jointly held by many Customers).
One Customer can have more than one Loans and also One Loan can be availed by one or more
Customers, so the relationship between Loan and Customers is many to many relationship.
 228W1A12J1

Hotel management:

Entities in Hotel Management

1. Hotel: The main physical establishment providing lodging and hospitality services to guests.
2. Rooms: Space within the hotel premises for guest accommodation, typically equipped with furniture
and amenities.
3. Departments: Segments within the hotel organization responsible for specific functions such as
housekeeping, food and beverage, maintenance, and management. Some of the departments may be
as:
4. Guests: Individuals or groups who rent the hotel or accommodation facility.
5. Reservation: The process of securing a room or accommodation for a specific period in advance.
6. Staff: Employees of the hotel who carry out various roles and responsibilities, including management,
reception, housekeeping, and catering.

Attributes in Hotel Management

1. Guest:

 Guest_ID: Unique ID for each guest.

 Name: Name of the guest.
 228W1A12J1

 Contact Information: Phone number, email, or address of the guest.

 Nationality: Nationality or country of origin of the guest.
 Gender: Sexuality of the guest.
 Reservation_History: Previous reservations made by the guest.

2. Hotel:

 Hotel_ID: Unique ID for each hotel.

 Name: Name of the hotel.
 Location: Physical location of the hotel.
 Number of Rooms: Total count of rooms available in the hotel.
 Rating: Overall rating or classification of the hotel.
 Contact Information: Phone number or other contacts for contacting the hotel.

3. Reservation:

 Reservation_IDreservation: Unique reservation ID for each reservation.

 Check-in DateThe date: Date when the guest is scheduled to check in.
 Check-out Date: Date when the guest is scheduled to check out.

4. Department:

 Department_ID: Unique ID for each department.

 D_Head: Identifier of the department manager.
 D_Role: Function of the department.
 Staff-Count: Number of staff members assigned to the department.
 Contact Information: Phone number, email, or address of the department.

5. Staff:

 Staff_ID: Unique ID for each staff member.

 Name: Name of the staff member.
 Age: Age of the employee.
 Contact Information: Phone number, email, or address of the staff member.
 Salary: Compensation or salary of the staff member.

6. Room:

 Room_No. : Unique number for each room.

 Category: Type or category of the room (e.g., single, double, suite).
 Rent: Price per night for the room.
 Status: Current availability status of the room.

Hotel to Room (1:N):

 One hotel can have many rooms, but each room belongs to only one hotel.
 228W1A12J1

 The relationship between the hotel and its rooms is a one-to-many relationship.

Guest to Reservation (1:N):

 A guest can make multiple reservations, but each reservation is made by only one guest.

 There is a one-to-many relationship between the guest and reservations.

Reservation to Room (1:1):

 A reservation is for one room, but each of the rooms can be reserved multiple times.

 This is a one-to-one relationship between reservation and room.

Staff to Department (N:1):

 Each staff member works in one department, but each department can have multiple staff members.

Hotel and Department (1:N):

 Each hotel has multiple departments, such as Front Desk, Housekeeping, Food and Beverage,
Maintenance, and Management.

 Each department operates within a specific hotel.

 This is a one-to-many relationship, as one hotel can have multiple departments, but each department
belongs to only one hotel.