CH2.

SQL (Structured Query Language)

Learning Objectives...

 To know about basic concept of relational database design.

 To learn about normalization and normal forms of database.
 To get family with SQL.
 To know about DDL and DML commands.
 To use aggregate functions within query.
 To learn how to create simple and nested queries.

2.1 Introduction

 Purpose of SQL: SQL is a query language for the relational model, based on relational
algebra and relational calculus, allowing users to represent and execute queries efficiently.
 User-Friendly Language: SQL is designed to be more user-friendly compared to relational
algebra, combining powerful constructs for database interaction.
 Comprehensive Capabilities: SQL not only facilitates querying a database but also
supports features for defining data structures, modifying data, and specifying security
constraints.
 Standard Relational Language: SQL is the standard relational database language used in
popular RDBMS platforms like Oracle, Sybase, PostgreSQL, and others, enabling users to
access and manage data.
 Execution by DBMS: Users write and submit SQL statements to the DBMS, which
processes the query, retrieves the data from the disk, and returns the results to the user.

 From 1986, SQL has become universally adopted language.

 SQL is a special purpose, non-procedural language that supports the definition,
manipulation and control of data in RDBMS.
 It is called a special purpose because only the database can be handled.

Advantages of SQL:

1. Simplicity: Many problems can be expressed in SQL more easily and concisely than in
lower level languages. Simplicity in turn means increase in productivity.
2. Completeness: The language is relationally complete. User can write very large class of
queries.
3. Non-Procedurality: SQL DML are non-procedural languages.
4. Data independence: SQL DML statements include no reference to explicit access paths
such as indexes or physical sequence.
5. Ease of extension: It can be easily extended by using built-in functions.
6. Support for higher level languages: It means it will work as intermediate step in the
translation of those languages to the language of the underlying machine.
2.2 Normalization
Normalization is the process of organizing data in a database to reduce redundancy and improve
data integrity. It involves dividing a large database into smaller, more manageable tables and
establishing relationships between them, following certain rules. The goal is to eliminate
undesirable characteristics like data redundancy, update anomalies, and insertion or deletion
anomalies.

Why is Normalization Necessary?

Normalization is necessary for the following reasons:

 Minimize Redundancy: Reduces repeated data, saving storage space and ensuring data
consistency.
 Avoid Update Anomalies: Reduces the chances of inconsistencies when updating data
across the database.
 Simplify Data Structure: Organizes data in a way that makes it easier to maintain and
query.
 Improve Data Integrity: Ensures that the data adheres to certain rules, increasing its
reliability and consistency.

2.2.1 First Normal Form (1NF)

A table is in First Normal Form (1NF) if:

 All columns contain atomic values (i.e., indivisible values).

 Each column contains only one type of data (no repeating groups or arrays).
 Each column must have a unique name.
 The order in which data is stored does not matter.

Example of 1NF:

Consider a table storing student information with their enrolled subjects.

StudentID StudentName Subjects

101 John Math, Science, English
102 Jane Math, History
103 Alex English, Science

This table is not in 1NF because the Subjects column contains multiple values (not atomic). To
bring it into 1NF, we split the subjects into separate rows:

StudentID StudentName Subject

101 John Math
101 John Science
101 John English
102 Jane Math
102 Jane History
103 Alex English
103 Alex Science
Now, the table is in 1NF, as each column contains atomic values.

2.2.2 Second Normal Form (2NF)

A table is in Second Normal Form (2NF) if:

 It is in 1NF.
 It has no partial dependency, meaning no non-prime attribute (non-key attribute) is
dependent on a part of the candidate key.

To achieve 2NF, you need to remove partial dependencies, especially in tables where composite
keys are used.

Example of 2NF:

Consider the following table:

StudentID CourseID StudentName CourseName

101 C01 John Math
101 C02 John Science
102 C01 Jane Math
103 C03 Alex History

Here, StudentID + CourseID is the composite key, but StudentName depends only on
StudentID, and CourseName depends only on CourseID. This violates 2NF because non-prime
attributes are partially dependent on part of the composite key.

To convert this table into 2NF, we split it into two tables:

1. Student Table:

StudentID StudentName
101 John
102 Jane
103 Alex

2. Course Enrollment Table:

StudentID CourseID CourseName

101 C01 Math
101 C02 Science
102 C01 Math
103 C03 History

Now, the table is in 2NF, as there are no partial dependencies.

2.2.3 Third Normal Form (3NF)

A table is in Third Normal Form (3NF) if:

 It is in 2NF.
 It has no transitive dependency, meaning non-prime attributes should not depend on other
non-prime attributes.

Example of 3NF:

Consider the following table:

StudentID StudentName DepartmentID DepartmentName

101 John D01 Computer Science
102nb Jane D02 Physics
103 Alex D01 Computer Science

Here, DepartmentName depends on DepartmentID, and DepartmentID depends on

StudentID. This is a transitive dependency.

To convert it into 3NF, we break it into two tables:

1. Student Table:

StudentID StudentName DepartmentID

101 John D01
102 Jane D02
103 Alex D01

2. Department Table:

DepartmentID DepartmentName
D01 Computer Science
D02 Physics

Now, the table is in 3NF, as there are no transitive dependencies.

2.2.4 Boyce-Codd Normal Form (BCNF)

A table is in Boyce-Codd Normal Form (BCNF) if:

 It is in 3NF.
 Every determinant is a candidate key (i.e., there should be no non-trivial dependencies
where a non-key attribute determines another non-key attribute).

Example of BCNF:

Consider the following table:

 StudentID CourseID Instructor
101 C01 Prof. Smith
101 C02 Prof. Johnson
102 C01 Prof. Smith

Here, CourseID → Instructor is a functional dependency, but CourseID is not a candidate key.
To achieve BCNF, we need to split the table into two:

1. Course Instructor Table:

CourseID Instructor
C01 Prof. Smith
C02 Prof. Johnson

2. Student Enrollment Table:

StudentID CourseID
101 C01
101 C02
102 C01

Now, the table is in BCNF because all functional dependencies have the determinant as a
candidate key.

Summary:

 1NF: Eliminate repeating groups; ensure atomicity of data.

 2NF: Eliminate partial dependencies (non-prime attributes should depend on the whole
key).
 3NF: Eliminate transitive dependencies (non-prime attributes should not depend on other
non-prime attributes).
 BCNF: Eliminate all non-trivial functional dependencies where a non-key attribute
determines another non-key attribute.

Normalization improves the efficiency and integrity of databases, making them more
manageable and less prone to anomalies.

2.2 Basic Structure: A relational database consists of a collection of relation, each of which
is assigned a unique name. For retrieving record from table, we use basic structure for data
retrieval.

This SQL expression consists of 3 clauses or components,These components are as follows:

SELECT < list of attributes >

FROM < table name >
WHERE < condition >;

A SQL query has the following form:

SELECT A1, A2, A3 ….…An
FROM r1, r2, r3,………..rm
WHERE p

Here the An represents attributes, rm represents the relation name and p is predicate i.e.
[condition].

2.3 Data Definition Language (DDL) Commands:

 DDL is a part of SQL which describes the structure of information in the database.
 DDL basically allows us to create, delete and alter (modify) tables.
2.3.1 Data Types: Let's learn some basic data types used in SQL for table creation. The
attributes which we define in the table must have a data type. The data types supported by SQL
are:
1. Char
2. Varchar
3. Number
4. Int or integer
5. Float or real
6. Date and Time

1. CHAR(size):

 Stores fixed-length alphanumeric data.

 The size is defined in bytes (default is 1).
 Example: CHAR(5) will always store 5 characters, padding with spaces if needed.

For example, name char (10);

 Here, memory of 10 bytes will be stored for the name as shown below in Fig.

 Even if we accept or write a name of 6 character. The utilization of memory is for 6

characters. But rest 4 bytes are not used for other purpose. It is memory wastage.

2. VARCHAR(size) or VARCHAR2(size):

 Stores variable-length alphanumeric data.

 The size defines the maximum length.
 Example: VARCHAR2 (10) stores up to 10 characters without padding.

3. NUMBER(precision, scale):

 Stores numeric data with specified precision (total digits) and scale (decimal places).
 Example: NUMBER (8, 2) can store values like 123456.78.
  This data type represents a decimal number.
For example, rollno number (3);
 Here, 3 are representing the number of digits present. If we want to write the above
 NUMBER(7, 2):
o The number 7 represents the precision: the total number of significant digits (both
before and after the decimal point).
o The number 2 represents the scale: the number of digits to the right of the decimal
point.
 How it works:
o The column SAL can store numbers with up to 7 digits in total, of which 2 digits
are reserved for decimal places.
o For example:
 Valid values: 12345.67, 123.45, 12.34
 Invalid values: 1234567.89 (exceeds precision), 123.456 (exceeds scale)
 This definition is typically used to store monetary or salary values where precision and
scale are important.
 In summary, SAL NUMBER(7, 2) ensures that the values in the SAL column are
numeric, with a maximum of 7 digits and 2 decimal places.

4. INT or INTEGER:

 A subtype of NUMBER, stores whole numbers.

 Equivalent to NUMBER(38) with no decimal places.
 Example: 12345.

5. FLOAT or REAL:

 Stores floating-point numbers with precision.

 Example: FLOAT(7) supports numbers with up to 7 significant digits.

6. DATE and TIME:

 DATE: Stores both date and time (to the second) in the format DD-MON-YYYY
HH24:MI:SS.
 TIMESTAMP: Includes fractional seconds in addition to DATE values.
 Example: 01-JAN-2025 10:30:00.
SQL Operators

SQL operators are symbols or keywords used to specify conditions in SQL statements, perform
calculations, and manipulate data. They are categorized based on their functionality.

Types of SQL Operators

1. Arithmetic Operators

Used to perform mathematical operations.

Operator Description Example

+ Addition SELECT 10 + 5;

- Subtraction SELECT 10 - 5;

* Multiplication SELECT 10 * 5;

/ Division SELECT 10 / 5;

% Modulus (remainder) SELECT 10 % 3;

2. Comparison Operators

Used to compare two values.

Operator Description Example

= Equal to SELECT * FROM employees WHERE salary = 5000;

!= or <> Not equal to SELECT * FROM employees WHERE salary != 5000;

> Greater than SELECT * FROM employees WHERE salary > 5000;

< Less than SELECT * FROM employees WHERE salary < 5000;

>= Greater than or equal to SELECT * FROM employees WHERE salary >= 5000;

<= Less than or equal to SELECT * FROM employees WHERE salary <= 5000;

3. Logical Operators

Used to combine multiple conditions.

Operator Description Example

Returns true if both conditions SELECT * FROM employees WHERE salary > 5000
AND
are true AND dept_id = 1;

Returns true if any condition is SELECT * FROM employees WHERE salary > 5000
OR
true OR dept_id = 1;

SELECT * FROM employees WHERE NOT salary >

NOT Negates the condition
5000;

4. Special Operators

Used for specific conditions.

Operator Description Example

BETWEEN ... Checks if a value is SELECT * FROM employees WHERE salary BETWEEN
AND within a range 3000 AND 5000;

Checks if a value is SELECT * FROM employees WHERE dept_id IN (1, 2,

IN
in a list 3);

Matches a pattern SELECT * FROM employees WHERE emp_name LIKE

LIKE
using wildcards 'J%';

Checks for NULL

IS NULL SELECT * FROM employees WHERE dept_id IS NULL;
values

SELECT * FROM employees WHERE EXISTS

Checks if a subquery
EXISTS (SELECT 1 FROM managers WHERE employees.emp_id
returns any rows
= managers.emp_id);

Example Combining Operators

SELECT emp_id, emp_name, salary

FROM employees
WHERE salary > 3000 AND dept_id IN (1, 2)
ORDER BY salary DESC;

This query retrieves employees with a salary greater than 3000 and in departments 1 or 2, sorted
in descending order by salary.

1. CREATE TABLE: The CREATE TABLE command is used to create a new table in the
database. Constraints are used to enforce rules on the data stored in the table.

Syntax:
CREATE TABLE table_name
(
col1 datatype constraint1,
col2 datatype constraint2,
...
[CONSTRAINT constraint_name PRIMARY KEY (col1)],
[CONSTRAINT constraint_name FOREIGN KEY (col2) REFERENCES
other_table(column)],
...
);

Example:

CREATE TABLE employees (

eid NUMBER(5) PRIMARY KEY,
ename VARCHAR2(50) NOT NULL,
department VARCHAR2(30) DEFAULT 'General'
);

Combined Example:

Column Level Constraint:

CREATE TABLE employees

(
eid NUMBER(5) CONSTRAINT epk PRIMARY KEY,
ename VARCHAR2(50) CONSTRAINT nn_ename NOT NULL,
email VARCHAR2(100) CONSTRAINT unique_email UNIQUE,
salary NUMBER(10, 2) CONSTRAINT chk_salary CHECK (salary > 0),
did NUMBER(5) REFERENCES departments(did)
)

Table Level Constraint:

CREATE TABLE employees

(
eid NUMBER(5) CONSTRAINT epk PRIMARY KEY,
ename VARCHAR2(50) CONSTRAINT nn_ename NOT NULL,
email VARCHAR2(100) CONSTRAINT unique_email UNIQUE,
salary NUMBER(10, 2) CONSTRAINT chk_salary CHECK (salary > 0),
did NUMBER(5),
CONSTRAINT fk_dept FOREIGN KEY (did) REFERENCES departments(did)
)

This creates a table with the following constraints:

 PRIMARY KEY on eid.

 NOT NULL on ename.
 UNIQUE on email.
 CHECK on salary to ensure it's greater than 0.
 FOREIGN KEY to reference the departments table.
Summary of Constraints:

Constraint Purpose
PRIMARY KEY Uniquely identifies each row in a table.
FOREIGN KEY Links two tables based on a column relationship.
CHECK Validates that values meet a specific condition.
UNIQUE Ensures no duplicate values in a column.
NOT NULL Ensures that a column cannot have NULL values.
DEFAULT Assigns a default value when no value is provided.

2. DROP TABLE Command:

 When the condition changes, it becomes unnecessary to maintain the information in the table.
 So we need to delete a table.
 If a table is not needed any more, the table should get deleted. For this we use drop command.
 It removes the structure of the table as well as the data contained in it.

Syntax: DROP TABLE table_name

For example: Drop table Account;

It will delete table account from the database. Delete is used to delete tuples from the table where
as drop is used to delete the whole table (structure of the table as well as the data within it)

3. ALTER TABLE Command:

 Without deleting a relation we may forcefully modify the existing relation.
 These modifications can be performed by the statement called Alter Table.
 The most important options are ADD,DROP and MODIFY.
 The Alter Table command is used to add attributes to an existing relation.
 The newly added attribute has all NULL values at the start. After that we can insert
values for it.

The ALTER TABLE command is used to modify the structure of an existing table. It allows you
to add, delete, or modify columns, and also apply or remove constraints.

Common Uses of ALTER TABLE

1. Adding new columns to a table.

2. Deleting existing columns from a table.
3. Modifying the data type or size of columns.
4. Adding or dropping constraints.

1. ADD a Column

Used to add one or more columns to an existing table.

Syntax:
ALTER TABLE table_name ADD column_name datatype [constraint];
Example:
ALTER TABLE employees ADD hire_date DATE;

This adds a hire_date column of type DATE to the employees table.

2. DROP a Column

Used to remove a column from a table.

Syntax:
ALTER TABLE table_name DROP COLUMN column_name;

Example:
ALTER TABLE employees DROP COLUMN hire_date;

This removes the hire_date column from the employees table.

3. MODIFY a Column

Used to change the data type, size, or constraints of a column.

Syntax:
ALTER TABLE table_name
MODIFY column_name new_datatype [constraint];

Example:
ALTER TABLE employees MODIFY emp_name VARCHAR2(100) NOT NULL;

This modifies the emp_name column to have a maximum length of 100 characters and applies
the NOT NULL constraint.

4. ADD a Constraint

Used to add constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, etc.

Syntax:
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type
(column_name);

Example:
ALTER TABLE employees ADD CONSTRAINT emp_email_unique UNIQUE (email);

This adds a UNIQUE constraint to the email column of the employees table.
5. DROP a Constraint

Used to remove constraints from a table.

Syntax:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example:
ALTER TABLE employees DROP CONSTRAINT emp_email_unique;

This removes the UNIQUE constraint from the email column.

6. Rename a Column

Used to rename an existing column.

Syntax:
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Example:
ALTER TABLE employees RENAME COLUMN emp_name TO employee_name;

This renames the emp_name column to employee_name.

7. Add Multiple Columns

You can add multiple columns in a single command.

Syntax:
ALTER TABLE table_name ADD (column1 datatype, column2 datatype, ...);

Example:
ALTER TABLE employees ADD (address VARCHAR2(200), phone_number
VARCHAR2(15));

This adds address and phone_number columns to the employees table.

Key Points:

 Always check if changes to a table affect existing data or applications.

 Use MODIFY cautiously for columns with existing data, as it might lead to errors or data
truncation.
 ALTER TABLE is a DDL command, and changes are automatically committed.
Summary Table:

Operation Syntax Example

Add a column ALTER TABLE employees ADD hire_date DATE;
Drop a column ALTER TABLE employees DROP COLUMN hire_date;
Modify a ALTER TABLE employees MODIFY emp_name VARCHAR2(100);
column
Add a constraint ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY
KEY(emp_id);
Drop a ALTER TABLE employees DROP CONSTRAINT emp_pk;
constraint
Rename a ALTER TABLE employees RENAME COLUMN emp_name TO
column employee_name;

This provides a comprehensive overview of the ALTER TABLE command and its versatile
options.

2.4 DML Commands: Data Manipulation Language (DML) in SQL

DML is a subset of SQL used to manipulate and manage the data stored in a database. It is
responsible for operations like inserting, updating, deleting, and retrieving data from tables.

Key DML Commands:

1. INSERT: Adds new rows to a table.

2. UPDATE: Modifies existing rows in a table.
3. DELETE: Removes rows from a table.
4. SELECT: Retrieves data from a table.

1. INSERT Command: Used to insert new records into a table.

Syntax:

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

Example:

INSERT INTO employees (emp_id, emp_name, salary, dept_id)

VALUES (101, 'John Doe', 50000, 1);

2. UPDATE Command: Used to modify existing records in a table.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:
UPDATE employees
SET salary = 55000
WHERE emp_id = 101;

3. DELETE Command: Used to remove rows from a table.

Syntax:

DELETE FROM table_name

WHERE condition;

Example:

DELETE FROM employees

WHERE emp_id = 101;

4. SELECT Command: Used to retrieve data from a table.

Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE condition;

Example:

SELECT emp_id, emp_name, salary

FROM employees
WHERE dept_id = 1;

Key Points to Note:

 INSERT: Adds only one row at a time unless a subquery is used.

 UPDATE: Modifies data based on the WHERE condition. Without it, all rows will be
updated.
 DELETE: Removes rows based on the WHERE condition. Without it, all rows will be
deleted.
 SELECT: Retrieves data based on specified columns and conditions.

Summary Table:

Command Purpose Example

INSERT Add new rows to a table. INSERT INTO employees VALUES (...);
UPDATE Modify existing rows in a table. UPDATE employees SET ... WHERE ...;
DELETE Remove rows from a table. DELETE FROM employees WHERE ...;
SELECT Retrieve rows from a table. SELECT * FROM employees WHERE ...;

2.5 Simple Queries

1: Create a table Customer with name, city name and street name.
Query:
Create table Customer
(
cust_name varchar2(20) NOT NULL,
cust_city varchar2(20) NOT NULL,
cust_street varchar2(40)
)
This will create a table with name Customer.

2: Create a table Customer with name as primary key, city name and street name.
Query:
Create table Customer
(
cust_name varchar2(20) NOT NULL,
cust_city varchar2(20) NOT NULL,
cust_street varchar2(40) NOT NULL,
constraint PK_cust_name primary key(cust_name)
)

3: Create a table depositor where cust_name is a foreign key and acct_no is also foreign key.
Acct_no is from table account which has been already created.
Query:
Create table Depositor
(
cust_name varchar2(20) NOT NULL,
acct_no int NOT NULL,
constraint FK_cust_name Foreign Key(cust_name)references customer(cust_name),
constraint FK_acc_no Foreign Key(acc_no)references Account(acct_no)
)

4: Create table project with primary key Pno and Foreign key Dno.
Query:
Create Table Project
(
pname varchar(15) NOT NULL,
Pno int NOT NULL,
Dnum int NOT NULL,
constraint PK_Pno primary Key(Pno),
Unique (pname),
constraint FK_Dnum Foreign Key(Dnum) references Dept(Dno)
)

2.6 Constraint (Not NULL, Check, Unique, Default)

Integrity Constraints: Integrity Constraints are used to apply business rules for the database
tables. The constraints available in SQL are Foreign Key, Not Null, Unique, Check. Constraints
can be defined in two ways:
1. The constraints can be specified immediately after the column definition. This is called
column-level definition.
2. The constraints can be specified after all the columns are defined. This is called table-
level definition.
Types of Constraints:

1. PRIMARY KEY:
o Ensures that the column values are unique and not null.
o A table can have only one primary key.
Syntax:
column_name datatype CONSTRAINT constraint_name PRIMARY KEY
Example:
CREATE TABLE employees (
eid NUMBER(5) CONSTRAINT epk PRIMARY KEY,
ename VARCHAR2(50)
);

2. FOREIGN KEY:
o Establishes a relationship between two tables.
o Ensures the value in the column exists in the referenced table.
Syntax:
column_name datatype CONSTRAINT constraint_name
FOREIGN KEY REFERENCES parent_table(column)
Example:
CREATE TABLE orders (
order_id NUMBER(5) PRIMARY KEY,
cid NUMBER(5) CONSTRAINT fk_customer REFERENCES customers (cid)
);

3. CHECK:
o Ensures the value in a column meets a specific condition.
Syntax:
column_name datatype CONSTRAINT constraint_name CHECK (condition)
Example:
CREATE TABLE products (
product_id NUMBER(5) PRIMARY KEY,
price NUMBER(10, 2) CONSTRAINT chk_price CHECK (price > 0)
);

4. UNIQUE:
o Ensures that all values in the column are unique.
Syntax:
column_name datatype CONSTRAINT constraint_name UNIQUE
Example:
CREATE TABLE users (
user_id NUMBER(5) PRIMARY KEY,
email VARCHAR2(100) CONSTRAINT unique_email UNIQUE
);

5. NOT NULL:
o Ensures that the column cannot have NULL values.
Syntax:
column_name datatype CONSTRAINT constraint_name NOT NULL
Example:
CREATE TABLE students (
student_id NUMBER(5) PRIMARY KEY,
 student_name VARCHAR2(50) CONSTRAINT nn_name NOT NULL
);

6. DEFAULT:
o Assigns a default value to the column when no value is specified.
Syntax:
column_name datatype DEFAULT default_value

Column Level Constraint :

CREATE TABLE employees
(
eid NUMBER(5) CONSTRAINT epk PRIMARY KEY,
ename VARCHAR2(50) CONSTRAINT nn_ename NOT NULL,
email VARCHAR2(100) CONSTRAINT unique_email UNIQUE,
salary NUMBER(10, 2) CONSTRAINT chk_salary CHECK (salary > 0),
did NUMBER(5),
CONSTRAINT fk_dept FOREIGN KEY (did) REFERENCES departments(did)
)

Table Level Constraint:

CREATE TABLE employees (
eid NUMBER(5),
ename VARCHAR2(50),
email VARCHAR2(100),
salary NUMBER(10, 2),
did NUMBER(5),
CONSTRAINT epk PRIMARY KEY (eid),
CONSTRAINT nn_ename NOT NULL (ename),
CONSTRAINT unique_email UNIQUE (email),
CONSTRAINT chk_salary CHECK (salary > 0),
CONSTRAINT fk_dept FOREIGN KEY (did) REFERENCES departments (did)
);

Difference between Primary Key and Unique Constraints:

1. A table can have only one primary key, but it can have many unique constraints.
2. When a primary key is defined, the columns that compose the primary key are automatically
mandatory. But with the unique constraint you must have to specify the column as NOT NULL.

2.7 Aggregate function (Min, Max, Avg, Count, Sum)

Aggregate Functions in SQL

Aggregate functions in SQL perform a calculation on a set of values and return a single value.
These functions are commonly used with the GROUP BY clause for summarizing data in tables.

Key Aggregate Functions:

1. MIN(): Returns the smallest value in a column.

2. MAX(): Returns the largest value in a column.
3. AVG(): Calculates the average of the values in a column.
4. COUNT(): Counts the number of rows that match a condition.
5. SUM(): Calculates the total of all values in a column.
1. MIN() Function:

Finds the smallest value in a numeric or non-numeric column.

Syntax:

SELECT MIN(column_name) FROM table_name;

Example:

SELECT MIN(salary) AS MinSalary

FROM employees;

Returns the smallest salary in the employees table.

2. MAX() Function:

Finds the largest value in a numeric or non-numeric column.

Syntax:

SELECT MAX(column_name) FROM table_name;

Example:

SELECT MAX(salary) AS MaxSalary

FROM employees;

Returns the highest salary in the employees table.

3. AVG() Function:

Calculates the average (mean) of values in a numeric column.

Syntax:

SELECT AVG(column_name) FROM table_name;

Example:

SELECT AVG(salary) AS AvgSalary

FROM employees;

Returns the average salary of all employees.

4. COUNT() Function:

Counts the number of rows that meet a specified condition.

Syntax:

SELECT COUNT(column_name) FROM table_name;

Example:

SELECT COUNT(emp_id) AS EmployeeCount

FROM employees
WHERE dept_id = 1;

Counts the number of employees in department 1.

5. SUM() Function:

Calculates the total sum of values in a numeric column.

Syntax:

SELECT SUM(column_name) FROM table_name;

Example:

SELECT SUM(salary) AS TotalSalary

FROM employees;

Returns the total salary paid to all employees.

Summary Table of Aggregate Functions:

Function Purpose Example

MIN() Finds the smallest value. SELECT MIN(salary) FROM employees;
MAX() Finds the largest value. SELECT MAX(salary) FROM employees;
AVG() Calculates the average value. SELECT AVG(salary) FROM employees;
COUNT() Counts the number of rows. SELECT COUNT(emp_id) FROM employees;
SUM() Calculates the total sum of values. SELECT SUM(salary) FROM employees;

Use with GROUP BY:

Aggregate functions are often used with the GROUP BY clause to calculate grouped results.
Example with GROUP BY:

SELECT dept_id, AVG(salary) AS AvgSalary

FROM employees
GROUP BY dept_id;

Calculates the average salary for each department.

2.8 Clause (Group By, Order By, Having)

Clauses in SQL: GROUP BY, ORDER BY, and HAVING

SQL clauses are used to filter, organize, and control how data is presented in a query. Let’s look
at the purpose of each clause along with examples.

1. GROUP BY Clause

The GROUP BY clause is used to group rows that have the same values in specified columns
into summary rows, like "total salary by department."

Purpose:

 Used with aggregate functions like SUM(), AVG(), COUNT(), etc.

 Groups the data into subsets based on one or more columns.

Syntax:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

Example:
SELECT dept_id, SUM(salary) AS TotalSalary
FROM employees
GROUP BY dept_id;

This query calculates the total salary for each department.

2. ORDER BY Clause

The ORDER BY clause is used to sort the result set in either ascending (ASC) or descending
(DESC) order based on one or more columns.

Purpose:

 Organizes data in a specified order.

 By default, it sorts in ascending order.
Syntax:
SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC|DESC];

Example:
SELECT emp_name, salary
FROM employees
ORDER BY salary DESC;

This query lists employees with their salaries, sorted by salary in descending order.

3. HAVING Clause

The HAVING clause is used to filter grouped data based on conditions, similar to the WHERE
clause, but it works on groups (aggregated data) rather than individual rows.

Purpose:

 Filters the grouped results produced by the GROUP BY clause.

 Used with aggregate functions.

Syntax:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

Example:
SELECT dept_id, AVG(salary) AS AvgSalary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 50000;

This query calculates the average salary for each department and filters only those departments
where the average salary is greater than 50,000.

Combining the Clauses

These clauses are often used together in queries.

Example:
SELECT dept_id, SUM(salary) AS TotalSalary
FROM employees
GROUP BY dept_id
HAVING SUM(salary) > 200000
ORDER BY TotalSalary DESC;
Explanation:

1. GROUP BY: Groups the employees by dept_id.

2. HAVING: Filters departments where the total salary is greater than 200,000.
3. ORDER BY: Sorts the result in descending order of TotalSalary.

Key Differences:

Clause Purpose Works On

GROUP BY Groups rows based on column values. Raw data rows.
ORDER BY Sorts the result set in ascending or descending order. Raw or grouped data.
HAVING Filters grouped data using aggregate conditions. Grouped data.

Quick Summary Table:

Clause Purpose Example

GROUP BY Groups data for aggregation. GROUP BY dept_id
ORDER BY Sorts data in ascending/descending order. ORDER BY salary DESC
HAVING Filters groups based on a condition. HAVING SUM(salary) > 200000

By understanding and combining these clauses, you can create powerful SQL queries to analyze
and present data effectively.

2.9 Nested Queries

Nested Queries in SQL

A nested query, also known as a subquery, is a query embedded within another SQL query.
The result of the subquery is used by the main query (also called the outer query).

Purpose:

 To simplify complex queries.

 To perform operations where the result of one query depends on another query.

Types of Nested Queries:

1. Single-row subquery: Returns one row.

2. Multi-row subquery: Returns multiple rows.
3. Multi-column subquery: Returns multiple columns.
4. Correlated subquery: Refers to the outer query for each row processed.
Syntax:

SELECT column1, column2

FROM table1
WHERE column = (SELECT column FROM table2 WHERE condition);

Examples:

1. Single-row Subquery

The subquery returns a single value.

SELECT emp_name, salary

FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

This query selects employees earning more than the average salary.

2. Multi-row Subquery

The subquery returns multiple rows, typically combined with IN, ANY, or ALL.

SELECT emp_name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'Pune');

This query retrieves the names of employees who work in departments located in Pune.

3. Multi-column Subquery

The subquery returns multiple columns.

SELECT emp_name, dept_id

FROM employees
WHERE (dept_id, salary) IN (
SELECT dept_id, MAX(salary)
FROM employees
GROUP BY dept_id
)

This query retrieves the employees with the highest salary in each department.

4. Correlated Subquery

The subquery depends on the outer query for its execution.

SELECT emp_name, salary
FROM employees e1
WHERE salary > ( SELECT AVG(salary) FROM employees e2
WHERE e1.dept_id = e2.dept_id
)

This query retrieves employees whose salary is above the average salary in their respective
departments.

Key Points:

 A subquery can be used in the SELECT, FROM, WHERE, or HAVING clause.

 Subqueries must be enclosed in parentheses.
 Subqueries can return scalar (single value), column, or table results.
 For multi-row subqueries, operators like IN, ANY, or ALL are used.

Quick Comparison of Operators with Subqueries:

Operator Description Example

IN Matches any value in a list. WHERE dept_id IN (SELECT dept_id FROM
dept)
ANY Matches any value returned by WHERE salary > ANY (SELECT salary
subquery. FROM emp)
ALL Matches all values returned by WHERE salary > ALL (SELECT salary
subquery. FROM emp)
=, >, etc. Works for single-row subqueries. WHERE salary = (SELECT MAX(salary)
FROM emp)

Use Cases of Nested Queries:

1. Filtering data dynamically based on another query.

2. Performing comparisons using aggregated data.
3. Simplifying query logic by breaking it into manageable pieces.

Conclusion:

Nested queries are a powerful tool in SQL that allows for complex query operations by using the
results of one query inside another. They improve readability and reusability of SQL code.

2.10 Case Study on SQL

Case Study 1: Consider the following entities and their relationships. Create a RDB in 3 NF
with appropriate data types and Constraints.
Emp(eno ,ename ,designation ,salary, Date_Of_Joining)
Dept(dno,dname ,loc)
The relationship between Dept & Emp is one-to-many.
Constraints: - Primary Key, ename should not be NULL, salary must be greater than 0.
Consider the above tables and Execute the following queries:

-- Create Dept Table

CREATE TABLE Dept (
dno INT PRIMARY KEY, -- Primary Key
dname VARCHAR(100) NOT NULL, -- Department Name cannot be NULL
loc VARCHAR(100) NOT NULL -- Location cannot be NULL
)

-- Create Emp Table

CREATE TABLE Emp (
eno INT PRIMARY KEY, -- Primary Key
ename VARCHAR(100) NOT NULL, -- Name cannot be NULL
desg VARCHAR(100) NOT NULL, -- Designation cannot be NULL
sal NUMBER(10, 2) CHECK (sal> 0), -- Salary must be > 0
doj DATE NOT NULL, -- Date of Joining cannot be NULL
dno INT NOT NULL, -- Foreign Key to [Link]
CONSTRAINT FK_Dept FOREIGN KEY (dno) REFERENCES Dept(dno)
)

INSERT INTO Dept VALUES(1, 'Account', 'Mumbai')

INSERT INTO Dept VALUES(2, 'Quality', 'Pune')
INSERT INTO Dept VALUES(3, 'HR', 'Delhi')
INSERT INTO Dept VALUES(4, 'Sales', 'Pune')
INSERT INTO Dept VALUES(5, 'IT', 'Bangalore')
INSERT INTO Dept VALUES(6, 'Computer Science', 'Chennai')

-- Insert Sample Records for Emp Table

INSERT INTO Emp VALUES(101, 'Mr. Advait', 'Manager', 60000, TO_DATE('01-JAN-2018',
'DD-MON-YYYY'), 1)
INSERT INTO Emp VALUES(102, 'Mr. Roy', 'Developer', 55000, TO_DATE('10-MAR-2020',
'DD-MON-YYYY'), 6)
INSERT INTO Emp VALUES(103, 'Ms. Priya', 'Tester', 45000, TO_DATE('15-JUL-2019', 'DD-
MON-YYYY'), 2)
INSERT INTO Emp VALUES(104, 'Mr. Amit', 'Manager', 70000, TO_DATE('12-AUG-2017',
'DD-MON-YYYY'), 2)
INSERT INTO Emp VALUES(105, 'Ms. Neha', 'Engineer', 40000, TO_DATE('20-JUN-2016',
'DD-MON-YYYY'), 4)
INSERT INTO Emp VALUES(106, 'Mr. Rohan', 'Analyst', 52000, TO_DATE('25-DEC-2019',
'DD-MON-YYYY'), 5)

Questions
1. Add column phone_No into Emp table with data type int.
ALTER TABLE Emp ADD phone_No INT
2. Delete the details of Employee whose designation is ‘Manager’.
DELETE FROM Emp WHERE desg = 'Manager'

3. Display the count of employees department wise.

SELECT dno, COUNT(*) AS employee_count FROM Emp GROUP BY dno

or
select d.*, t.* from dept d, (SELECT dno, COUNT(*) AS employee_count FROM Emp GROUP
BY dno)t
where [Link]=[Link]

4. Display the name of employee who is ‘Manager’ of “Account Department”.

SELECT [Link]
FROM Emp e, Dept d
WHERE [Link] = [Link] and [Link] = 'Manager' AND [Link] = 'Account Department'
or
SELECT [Link],[Link],[Link]
FROM Emp e, Dept d
WHERE [Link] = [Link] and [Link] = 'Manager' AND [Link] = 'Account'

5. Display the name of department whose location is “Pune” and “Mr. Advait” is working
in it.

SELECT [Link]
FROM Dept d, Emp e WHERE [Link] = [Link] and
[Link] = 'Pune' AND [Link] = 'Mr. Advait'

6. Display the names of employees whose salary is greater than 50000 & department is
“Quality”.

SELECT [Link]
FROM Emp e, Dept d WHERE [Link] = [Link] and
[Link] > 50000 AND [Link] = 'Quality'

7. Update Dateofjoining of employee to ‘15/06/2019’ whose department is ‘computer

science’ and name is “Mr. Roy’.

UPDATE Emp
SET doj = TO_DATE('15/06/2019', 'DD/MM/YYYY')
WHERE ename = 'Mr. Roy' AND dno = (SELECT dno FROM Dept WHERE dname =
'Computer Science')

Case Study 2: Consider the following entities and their relationships. Create a RDB in 3 NF
with appropriate data types and Constraints.

Sales_order (ordNo, ordDate),cno

Client (clientNo, ClientName, addr)
The relationship between Client & Sales_order is one-to-many.
Constraints: - Primary Key, ordDate should not be NULL.
-- Create Client Table
CREATE TABLE Client (
cno INT PRIMARY KEY, -- Primary Key
cname VARCHAR(100) NOT NULL, -- Client name cannot be NULL
addr VARCHAR(255) NOT NULL -- Address cannot be NULL
)

-- Create Sales_order Table

CREATE TABLE Sales_order (
ono INT PRIMARY KEY, -- Primary Key
odate DATE NOT NULL, -- Order Date cannot be NULL
cno INT NOT NULL, -- Foreign Key to [Link]
CONSTRAINT FK_Client FOREIGN KEY (cno) REFERENCES Client(cno)
)

ALTER TABLE Sales_order ADD amount INT

Consider the above tables and execute the following queries:

1. Add column amount into Sales_order table with data type int.
ALTER TABLE Sales_order ADD amount INT

2. Delete the details of the clients whose names start with ‘A’ character.
DELETE FROM Client WHERE cname LIKE '____A%'

4. Delete sales order details of client whose name is “Patil” and order date is “09/08/2019”.

DELETE FROM Sales_order

WHERE cno = (SELECT cno FROM Client WHERE cname = 'Patil')
AND odate = TO_DATE('09/08/2019', 'DD/MM/YYYY')

5. Change order date of client_No ‘CN001’ to ‘18/03/2019’.

UPDATE Sales_order
SET odate = TO_DATE('18/03/2019', 'DD/MM/YYYY')
WHERE cno = 'CN001'

6. Delete all sales_record having order date is before ‘10 /02/2018’.

DELETE FROM Sales_order

WHERE odate < TO_DATE(‘10/02/2018’, ‘DD/MM/YYYY’)

7. Display date wise sales_order given by clients.

SELECT odate, COUNT(ono) AS total_orders

FROM Sales_order GROUP BY odate ORDER BY odate

8. Update the address of client to “Pimpri” whose name is ‘Mr. Roy’.

UPDATE Client
SET addr = 'Pimpri'
WHERE cname = 'Mr. Roy'
Case Study 3
Consider the following entities and their relationships. Create a RDB in 3 NF with
appropriate data types and Constraints.
Hospital (hno ,hname , city, Est_year, addr)
Doctor (dno , dname , addr, Speciality)
The relationship between Hospital and Doctor is one - to – Many
Constraints: - Primary Key, Est_year should be greater than 1990.

Create Tables and Constraints

-- Create Hospital Table
CREATE TABLE Hospital (
hno INT PRIMARY KEY, -- Primary Key
hname VARCHAR(100) NOT NULL, -- Hospital Name
city VARCHAR(100) NOT NULL, -- City
est_year INT CHECK (est_year > 1990) NOT NULL, -- est_year > 1990
addr VARCHAR(255) NOT NULL -- Address
)

-- Create Doctor Table

CREATE TABLE Doctor (
dno INT PRIMARY KEY, -- Primary Key
dname VARCHAR(100) NOT NULL, -- Doctor Name
addr VARCHAR(255) NOT NULL, -- Address
Speciality VARCHAR(100) NOT NULL, -- Speciality
hno INT NOT NULL, -- Foreign Key to [Link]
CONSTRAINT FK_Hospital FOREIGN KEY (hno) REFERENCES Hospital(hno) ON
DELETE CASCADE
)

Consider the above tables and execute the following queries:

Queries
1. Delete addr column from the Hospital table.

ALTER TABLE Hospital DROP COLUMN addr

2. Display doctor name, hospital name, and specialty of doctors from “Pune City”.

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

FROM Doctor d,Hospital h WHERE [Link] = [Link] and [Link] = 'Pune'

3. Display the names of hospitals located in “Pimpri” city.

SELECT hname
FROM Hospital
WHERE city = 'Pimpri'

4. Display the names of doctors working in “Birla” Hospital and city name is
“Chinchwad”.

SELECT [Link]
FROM Doctor d,Hospital h WHERE [Link] = [Link], and [Link] = 'Birla' AND [Link] =
'Chinchwad'
5. Display the specialty of the doctors who are working in “Ruby” hospital.

SELECT [Link]
FROM Doctor d,Hospital h WHERE [Link] = [Link] and [Link] = 'Ruby'

6. Count the number of doctors hospital-wise for hospitals located in “Pimple Gurav”.

SELECT [Link], COUNT([Link]) AS doctor_count

FROM Doctor d,Hospital h WHERE [Link] = [Link],[Link] = 'Pimple Gurav'
GROUP BY [Link]

7. Update the address of a doctor to “Pimpri” whose hospital is “Ruby clinic”.

UPDATE Doctor
SET addr = 'Pimpri'
WHERE hno = (SELECT hno FROM Hospital WHERE hname = 'Ruby clinic')

Case Study 4:
Consider the following entities and their relationships. Create a RDB in 3 NF with
appropriate data types and Constraints.
Patient (PCode, Name, Addr, Disease)
Bed (Bed_No, RoomNo, loc)
Relationship: - There is one-one relationship between patient and bed.
Constraints: - Primary key, RoomNo must be greater than Bed_No, Addr should not be null.
Consider the above tables and execute the following queries:

-- Create Tables with Constraints

-- Create Patient Table

CREATE TABLE Patient (
pcode INT PRIMARY KEY, -- Primary Key
pname VARCHAR(100) NOT NULL, -- Patient Name
paddr VARCHAR(255) NOT NULL, -- Address cannot be NULL
pdisease VARCHAR(100) NOT NULL -- Disease
CONSTRAINT FK_Bed FOREIGN KEY (pcode) REFERENCES Bed(bedno)
)

-- Create Bed Table

CREATE TABLE Bed (
bedno INT PRIMARY KEY, -- Primary Key
roomno INT NOT NULL CHECK (roomno > bedno), -- RoomNo > BedNo
loc VARCHAR(100) NOT NULL -- Location
)

-- Create Relationship (One-to-One)

ALTER TABLE Patient ADD CONSTRAINT FK_Bed FOREIGN KEY (pcode)
REFERENCES Bed(bedno) ON DELETE CASCADE

-- SQL Queries

1. Display the details of patients who are from "Pimple Gurav".

SELECT *
FROM Patient
WHERE paddr = 'Pimple Gurav'

2. Delete the details of patient whose Bed_No is 1 and RoomNo is 105.

DELETE FROM Patient
WHERE pcode = (SELECT bedno FROM Bed WHERE bedno = 1 AND roomno = 105)

3. Display the count of patients room-wise.

SELECT [Link], COUNT([Link]) AS patient_count
FROM Patient p, Bed b WHERE [Link] = [Link]
GROUP BY [Link]

4. Display the names of patients who are admitted in room no 101.

SELECT [Link]
FROM Patient p,Bed b WHERE [Link] = [Link] and [Link] = 101

5. Display the disease of patient whose Bed_No is 1.

SELECT [Link]
FROM Patient p
WHERE [Link] = 1

6. Display the room_no and bed_no of patient whose name is "Mr Roy".
SELECT [Link], [Link]
FROM Patient p,Bed b WHERE [Link] = [Link] and [Link] = 'Mr Roy'

7. Give the details of the patient who is admitted on 2nd floor in roomno 102.
SELECT p.*
FROM Patient p,Bed b WHERE [Link] = [Link] and [Link] LIKE '2nd flr%' AND [Link] =
102

Case Study 5:
Consider the following entities and their relationships. Create a RDB in 3 NF with
appropriate data types and Constraints.
Customer (cno, cname, caddr, city)
Loan (lno, lamt)
The relationship between Customer and Loan is Many to Many
Constraint: Primary key, loan_amt should be > 0.
Consider the above tables and execute the following queries:
-- Create Tables with Constraints

-- Create Customer Table

CREATE TABLE Customer (
cno INT PRIMARY KEY, -- Primary Key
cname VARCHAR(100) NOT NULL, -- Customer Name
caddr VARCHAR(255) NOT NULL, -- Customer Address
city VARCHAR(100) NOT NULL -- Customer City
)

-- Create Loan Table

CREATE TABLE Loan (
lno INT PRIMARY KEY, -- Primary Key
lamt DECIMAL(15, 2) CHECK (lamt > 0) NOT NULL -- Loan Amount > 0
)

-- Create Relationship Table for Many-to-Many Relationship

CREATE TABLE Customer_Loan (
cno INT NOT NULL, -- Foreign Key to [Link]
lno INT NOT NULL, -- Foreign Key to [Link]
PRIMARY KEY (cno, lno),
CONSTRAINT FK_Customer FOREIGN KEY (cno) REFERENCES Customer(cno) ON
DELETE CASCADE,
CONSTRAINT FK_Loan FOREIGN KEY (lno) REFERENCES Loan(lno) ON DELETE
CASCADE
)

-- SQL Queries

1. Add Phone_No column in customer table with data type INT.

ALTER TABLE Customer ADD Phone_No INT

2. Delete the details of customer whose loan_amt < 1000.

DELETE FROM Customer
WHERE cno IN (
SELECT cno
FROM Customer_Loan cl,Loan l WHERE [Link] = [Link] and [Link] < 1000
)

3. Find details of all customers whose loan_amt is greater than 10 lakh.

SELECT c.*
FROM Customer c
JOIN Customer_Loan cl ON [Link] = [Link]
JOIN Loan l ON [Link] = [Link]
WHERE [Link] > 1000000

4. List all customers whose name starts with 'D' character.

SELECT *
FROM Customer
WHERE cname LIKE 'D%'

5. List the names of customers in descending order who have taken a loan from Pimpri city.
SELECT [Link]
FROM Customer c,Customer_Loan cl,Loan l WHERE [Link] = [Link] and [Link] = [Link]
WHERE [Link] = 'Pimpri'
ORDER BY [Link] DESC

6. Display customer details having the maximum loan amount.

SELECT c.*, [Link]
FROM Customer c,Customer_Loan cl,Loan l WHERE [Link] = [Link] and
[Link] = [Link] and [Link] = (SELECT MAX(lamt) FROM Loan)

7. Update the address of the customer whose name is “Mr. Patil” and loan_amt is greater
than 100000.
UPDATE Customer
SET caddr = 'Updated Address'
WHERE cname = 'Mr. Patil'
AND cno IN (
SELECT cno
FROM Customer_Loan cl,Loan l WHERE [Link] = [Link]
and [Link] > 100000
)

Case Study 6:
Consider the following entities and their relationships. Create a RDB in 3 NF with
appropriate data types and Constraints.
Project (pno, pname, start_date, budget, status)
Department (dno, dname, HOD, loc)
The relationship between Project and Department is Many to One.
Constraint: Primary key.
Project Status Constraints: C – Completed,
P - Progressive,
I - Incomplete
Consider the above tables and execute the following queries:

-- Create Tables with Constraints

-- Create Department Table

CREATE TABLE Department (
dno INT PRIMARY KEY, -- Primary Key
dname VARCHAR(100) NOT NULL, -- Department Name
HOD VARCHAR(100) NOT NULL, -- Head of Department
loc VARCHAR(100) -- Location
)

-- Create Project Table

CREATE TABLE Project (
pno INT PRIMARY KEY, -- Primary Key
pname VARCHAR(100) NOT NULL, -- Project Name
start_date DATE NOT NULL, -- Project Start Date
budget DECIMAL(15, 2) NOT NULL, -- Project Budget
status CHAR(1) CHECK (status IN ('C', 'P', 'I')) NOT NULL, -- Project Status
dno INT NOT NULL, -- Foreign Key to [Link]
CONSTRAINT FK_Department FOREIGN KEY (dno) REFERENCES Department(dno) ON
DELETE CASCADE
)

-- SQL Queries

1. Drop loc column from department table.

ALTER TABLE Department DROP COLUMN loc

2. Display the details of projects whose start_date is before one month and status is
"Progressive".
SELECT *
FROM Project
WHERE start_date < ADD_MONTHS (SYSDATE, -1) AND status = 'P'
3. Display the names of projects and departments working on projects whose status is
"Completed".
SELECT [Link], [Link]
FROM Project p,Department d WHERE [Link] = [Link] and [Link] = 'C'

4. Display the total budget of each department.

SELECT [Link], SUM([Link]) AS total_budget
FROM Project p,Department d WHERE [Link] = [Link]
GROUP BY [Link]

5. Display incomplete projects of each department.

SELECT [Link], [Link]
FROM Project p,Department d WHERE [Link] = [Link] and [Link] = 'I'

6. Display all projects working under "[Link]".

SELECT p.*
FROM Project p,Department d WHERE [Link] = [Link] and [Link] = '[Link]'

7. Display department-wise HOD.

SELECT [Link], [Link]
FROM Department d

Case Study 7:
Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate
data types and Constraints.
Room (rno, rdesc, rate)
Guest (gno, gname, no_of_days)
The relationship between Room and Guest is One to One.
Constraint: Primary key, no of days should be > 0.
Consider the above tables and execute the following queries:

-- Create Tables with Constraints

-- Create Room Table

CREATE TABLE Room (
rno INT PRIMARY KEY, -- Primary Key
rdesc VARCHAR(100) NOT NULL, -- Room Description
rate DECIMAL(10, 2) NOT NULL -- Room Rate
)

-- Create Guest Table

CREATE TABLE Guest (
gno INT PRIMARY KEY, -- Primary Key
gname VARCHAR(100) NOT NULL, -- Guest Name
no_of_days INT CHECK (no_of_days > 0) NOT NULL, -- Number of Days > 0
rno INT UNIQUE NOT NULL, -- Foreign Key to [Link] (One-to-One)
CONSTRAINT FK_Room FOREIGN KEY (rno) REFERENCES Room(rno) ON DELETE
CASCADE
)

-- SQL Queries
1. Update the rate of room to 5000 whose type is “AC”.
UPDATE Room
SET rate = 5000
WHERE rdesc = 'AC'

-- 2. Display the name of guest who is staying 2 days in roomno 101.

SELECT [Link]
FROM Guest g, Room r WHERE [Link] = [Link] and g.no_of_days = 2 AND [Link] = 101

-- Q4 Queries

-- 1. Display room details according to its rates in ascending order.

SELECT *
FROM Room
ORDER BY rate ASC

-- 2. Display the roomno in which “Mr. Advait” is staying for 7 days.

SELECT [Link]
FROM Guest g,Room r WHERE [Link] = [Link] and [Link] = 'Mr. Advait' AND g.no_of_days =
7

-- 3. Find the number of AC rooms.

SELECT COUNT(*) AS ac_room_count
FROM Room
WHERE rdesc = 'AC'

-- 4. Find names of guests with maximum room charges.

SELECT [Link], (g.no_of_days * [Link]) AS total_charges
FROM Guest g,Room r WHERE [Link] = [Link]
and (g.no_of_days * [Link]) = (
SELECT MAX(g2.no_of_days * [Link])
FROM Guest g2, Room r2 WHERE [Link] = [Link]
)

-- 5. Display guest-wise halt days.

SELECT [Link], g.no_of_days AS halt_days FROM Guest g

Case Study 8
Consider the following entities and their relationships. Create a RDB in 3 NF with
appropriate data types and Constraints.
Book (Book_no, title, author, price, year_published)
Customer (cid, cname, addr)
Relation between Book and Customer is Many to Many with quantity as
descriptive attribute.
Constraint: Primary key, price should be >0.
Consider the above tables and execute the following queries:

-- Create Tables with Constraints

-- Create Book Table

CREATE TABLE Book (
 bno INT PRIMARY KEY, -- Primary Key
btitle VARCHAR(100) NOT NULL, -- Book Title
author VARCHAR(100) NOT NULL, -- Author
price DECIMAL(10, 2) CHECK (price > 0) NOT NULL, -- Price > 0
year_pub INT NOT NULL -- Year Published
);

-- Create Customer Table

CREATE TABLE Customer (
cid INT PRIMARY KEY, -- Primary Key
cname VARCHAR(100) NOT NULL, -- Customer Name
addr VARCHAR(255) NOT NULL -- Address
);

-- Create Relationship Table for Many-to-Many Relationship with Quantity

CREATE TABLE Book_Customer (
bno INT NOT NULL, -- Foreign Key to [Link]
cid INT NOT NULL, -- Foreign Key to [Link]
quantity INT NOT NULL, -- Quantity Purchased
PRIMARY KEY (bno, cid),
CONSTRAINT FK_Book FOREIGN KEY (bno) REFERENCES Book(bno) ON DELETE
CASCADE,
CONSTRAINT FK_Customer FOREIGN KEY (cid) REFERENCES Customer(cid) ON
DELETE CASCADE
);

-- Insert Sample Records for Book Table

INSERT INTO Book VALUES(1, 'Database Management', 'Mr. Gadhave', 150.00, 2019)
INSERT INTO Book VALUES(2, 'Operating Systems', 'Ms. Patil', 200.00, 2020)
INSERT INTO Book VALUES(3, 'Data Structures', 'Mr. Talore', 300.00, 2018)
INSERT INTO Book VALUES(4, 'Algorithms', 'Dr. Joshi', 250.00, 2021)
INSERT INTO Book VALUES(5, 'Computer Networks', 'Ms. Shah', 180.00, 2019)
INSERT INTO Book VALUES(6, 'Artificial Intelligence', 'Dr. Naik', 400.00, 2022)
INSERT INTO Book VALUES(7, 'Machine Learning', 'Mr. Gadhave', 500.00, 2020)
INSERT INTO Book VALUES(8, 'Cyber Security', 'Ms. Kulkarni', 350.00, 2021)
INSERT INTO Book VALUES(9, 'Software Engineering', 'Mr. Talore', 220.00, 2019)
INSERT INTO Book VALUES(10, 'Cloud Computing', 'Dr. Pandit', 280.00, 2020)

-- Insert Sample Records for Customer Table

INSERT INTO Customer VALUES(1, 'Rahul Sharma', 'Mumbai', '[Link]@[Link]')
INSERT INTO Customer VALUES(2, 'Priya Desai', 'Pune', '[Link]@[Link]')
INSERT INTO Customer VALUES(3, 'Amit Joshi', 'Mumbai', '[Link]@[Link]')
INSERT INTO Customer VALUES(4, 'Rohit Verma', 'Delhi', '[Link]@[Link]')
INSERT INTO Customer VALUES(5, 'Neha Kulkarni', 'Pune', '[Link]@[Link]')
INSERT INTO Customer VALUES(6, 'Sonia Gupta', 'Kolkata', '[Link]@[Link]')
INSERT INTO Customer VALUES(7, 'Rajiv Mehta', 'Bangalore', '[Link]@[Link]')
INSERT INTO Customer VALUES(8, 'Anjali Pandey', 'Mumbai', '[Link]@[Link]')
INSERT INTO Customer VALUES(9, 'Suresh Patil', 'Pune', '[Link]@[Link]')
INSERT INTO Customer VALUES(10, 'Rekha Nair', 'Chennai', '[Link]@[Link]')

-- Insert Sample Records for Book_Customer Relationship Table

INSERT INTO Book_Customer VALUES(1, 1, 2)
INSERT INTO Book_Customer VALUES(2, 2, 1)
INSERT INTO Book_Customer VALUES(3, 3, 4)
INSERT INTO Book_Customer VALUES(4, 4, 1)
INSERT INTO Book_Customer VALUES(5, 5, 3)
INSERT INTO Book_Customer VALUES(6, 6, 1)
INSERT INTO Book_Customer VALUES(7, 7, 5)
INSERT INTO Book_Customer VALUES(8, 8, 2)
INSERT INTO Book_Customer VALUES(9, 9, 6)
INSERT INTO Book_Customer VALUES(10, 10, 3)

-- SQL Queries

-- 1. Display the name of book whose author is “Mr. Gadhave”.

SELECT btitle
FROM Book
WHERE author = 'Mr. Gadhave';

-- 2. Add column EMailId into customer table.

ALTER TABLE Customer ADD EMailId VARCHAR(100);

-- Q4 Queries

-- 1. Display customer details from 'Mumbai'.

SELECT *
FROM Customer
WHERE addr = 'Mumbai';

-- 2. Display author-wise details of books.

SELECT author, COUNT(*) AS book_count, SUM(price) AS total_price
FROM Book
GROUP BY author;

-- 3. Display customer name that has purchased more than 3 books.

SELECT [Link]
FROM Customer c
JOIN Book_Customer bc ON [Link] = [Link]
WHERE [Link] > 3;

-- 4. Display book names having price between 100 and 200 and published year is 2019.
SELECT btitle
FROM Book
WHERE price BETWEEN 100 AND 200 AND year_pub = 2019;

-- 5. Update the title of the book to “DBMS” whose author is “Mr. Talore”.
UPDATE Book
SET btitle = 'DBMS'
WHERE author = 'Mr. Talore';
IMPORTANT QUESTIONS

1 Define Attribute and Tuple.

2 Define Data and information.
3 Define Normalization.
4 Explain the use of AVG () with example.
5 What is Constraint? List types of Constraints.
6 What is E-R Model? Give Example.
7 What is Nested Query?
8 Explain any four data types in SQL.
9 Explain CREATE TABLE command with syntax and example.
10 Explain First Normal Form.
11 Explain use of LIKE operator.
12 Explain use of ORDER BY clause.
13 What is DBMS? Explain applications of DBMS.
14 What is Derived Attribute?
15 What is SQL? Enlist two types of SQL Commands.
16 List and Explain users of DBMS.
17 Primary Key with example.
18 What is SQL? Explain DDL & DML Commands.
19 What is Ternary Relationship? Explain with suitable diagram.
20 Explain the following SQL commands with syntax and example:
i) INSERT ii) ALTER TABLE
21 Consider the following Entities and Relationships & solve the queries:
Department (d_no, d_name, d_Loc)
Employee (eno ,ename ,designation ,salary, doj)
Relation between Department and Employee is One to Many
Constraint: Primary Key, ename should not be NULL, salary must be greater than 0.
• Display employee details who have salary more than 50,000.
• Display the name of employee who is ‘Manager’ of “Account” Department
22 Consider the following Entities and Relationships & solve the queries:
Hospital (hno ,hname , city, Est_year, addr
Doctor (dno , dname , addr, Speciality
The relationship between Hospital and Doctor is one - to – Many
Constraints: - Primary Key
• Display the specialty of the doctors who are working in “Ruby” hospital.
• Display the names of doctors who are working in “City Heart” Hospital and city is
“Silvassa”
23 Consider the following Entities and Relationships & solve the queries:
Sales_order (ordNo, ordDate )
Client (clientNo, ClientName, addr)
The relationship between Client & Sales_order is one-to-many.
Constraints: - Primary Key, ordDate should not be NULL.
• Add column amount into Sales_order table with data type int.
• Delete the details of the clients whose names start with ‘A’ character.
24 Consider a trucking company which is responsible for picking up Shipments for
warehouses
of retail chain and deliver the shipments to the individual store location. A truck may
carry
several shipments in a single trip and deliver it to multiple stores. Draw an ER-diagram
and
convert it into relational model.
25 Construct an E-R diagram for a car insurance company that has a set of
customers. Each customer owns one or more cars. Each car is associated with zero to any
number of recorded accidents.
26 In a nursery, the plants are sold to the customers. These plants are Flowering and
Non-flowering only. Nutrients are given to the plant with some quantity. Nutrients
include
Pesticides, Watering and Manure.
27 In a car sales management system, customer purchases a car. Many cars has similar
model. Services are provided to each model. Each car gets a job card and which contains
spare types. These spare types have spare parts. Job cards were prepared by the advisor.
Draw ER diagram and design the relational database.