MODULE 2

PART B
1.A sales office maintains a database to keep track of its salespersons and their business trips.
The database consists of the following information:
 Each salesperson is identified by their Social Security Number (SSN), and details
such as their name, the year they started, and their department number are recorded.
 Every time a salesperson goes on a business trip, a record is created. This includes the
SSN of the salesperson taking the trip, the city of origin, the destination city, the
departure date, the return date, and a unique Trip ID.
 For each trip, the company tracks various expenses. Each expense entry includes the
Trip ID, the account to which the expense is charged, and the amount spent.
Specify the following queries in SQL on the above scenario and explain the query.
(i) Create the tables named Salesperson, Trip, and Expense, including the required fields..
(ii)Provide the details (all attributes of TRIP) for trips that exceeded $2000in expenses.
(iii) Print the SSN of salesman who looks trip to ‘Honolulu’
(iv)Print the trip expenses incurred by the salesman with SSN=’234-56-7890’.

Explain create command, insert command and select command with syntax. Insert minimum 5 values
in each table.
(i) Create the tables named Salesperson, Trip, and Expense, including the required fields.
CREATE TABLE Salesperson (SSN CHAR(11) PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
StartYear INT,
DeptNo INT );
CREATE TABLE Trip ( TripID INT PRIMARY KEY,
SSN CHAR(11),
OriginCity VARCHAR(50),
DestinationCity VARCHAR(50),
DepartureDate DATE,
ReturnDate DATE,
FOREIGN KEY (SSN) REFERENCES Salesperson(SSN));
CREATE TABLE Expense (
ExpenseID INT PRIMARY KEY,
TripID INT,
Account VARCHAR(30),
Amount DECIMAL(10,2),
FOREIGN KEY (TripID) REFERENCES Trip(TripID));
(ii) Provide the details (all attributes of TRIP) for trips that exceeded $2000in expenses.
SELECT *
FROM Trip T
WHERE (SELECT SUM([Link])
FROM Expense E
WHERE [Link] = [Link]) > 2000;

(iii) Print the SSN of salesman who looks trip to ‘Honolulu’

SELECT DISTINCT SSN
FROM Trip
WHERE DestinationCity = 'Honolulu';

(iv)Print the trip expenses incurred by the salesman with SSN=’234-56-7890’.

SELECT TripID,
(SELECT SUM([Link])
FROM Expense E
WHERE [Link] = [Link]) AS TotalExpenses
FROM Trip T
WHERE [Link] = '234-56-7890';

2. Examine how various types of Join operations are utilized within a database and evaluate their
specific use cases. How would you differentiate between these Join types, and in what scenarios
would each be most effectively applied? Provide detailed examples.

Joins
Join is an operation that combines the rows of two or more tables based on related columns
between them. The main purpose of join is to retrieve the data from multiple tables in other
words Join is used to perform multi-table queries. It is denoted by ⨝.
Syntax
R3 <- ⨝(R1) <join_condition> (R2)
where R1 and R2 are two relations to be joined and R3 is a relation that will hold the result of
the join operation.
Example
Temp <- ⨝(student) [Link]=[Link](Exam)
where S and E are aliases of the student and exam respectively.
JOIN Example
Table 1 – Student
Table 2 - Student_Course

Both these tables are connected by one common key (column) i.e. ROLL_NO.
We can perform a JOIN operation using the given relational algebra:

Student ⨝ Student_course
Output:

ROLL_NO NAME ADDRESS PHONE AGE COURSE_ID

1 HARSH DELHI xxxxxxxxxx 18 1
2 PRATIK BIHAR xxxxxxxxxx 19 2
3 PRIYANK SILIGURI xxxxxxxxxx 20 2
A
4 DEEP RAMNAGAR xxxxxxxxxx 18 3
5 SAPTARHI KOLKATA xxxxxxxxxx 19 1

Types of Join
There are many types of Joins in SQL. Depending on the use case, you can use different
types of SQL JOIN clauses. Here are the frequently used SQL JOIN types:
1. Inner Join
Inner Join is a join operation in DBMS that combines two or more tables based on related
columns and returns only rows that have matching values among tables.

Inner Join
 TYPES:

 Conditional join
 Equi Join
 Natural Join
(a) Conditional Join
Conditional join or Theta join is a type of inner join in which tables are combined based on
the specified condition.
In conditional join, the join condition can include <, >, <=, >=, ≠ operators in addition to the
'=' operator.

Example: Suppose two tables A and B

Table A Table B
R S T U
10 5 10 12
7 20 17 6

A ⨝ S<T B
Output
R S T U
10 5 10 12
Explanation: This query joins the table A, B and projects attributes R, S, T, U were the
condition S < T is satisfied.

(b) Equi Join

Equi Join is a type of inner join where the join condition uses the equality operator ('=')
between columns.
Example: Suppose there are two tables Table A and Table C

Table A Table C
Column A Column B Column A Column B
a a a a
a b a c

A ⨝ [Link] B = [Link] B (C)

Output
Column A Column B
a a
Explanation: The data value "a" is available in both tables Hence we write that "a" is the
table in the given output.

(c) Natural Join

Natural join is a type of inner join in which we do not need any comparison operators. In
natural join, columns should have the same name and domain. There should be at least one
common attribute between the two tables.
Example: Suppose there are two tables Table A and Table B
Table A Table B
Number Square Number Cube
2 4 2 8
3 9 3 27

A⨝B
Output
Number Square Cube
2 4 8
3 9 27
Explanation - Column Number is available in both tables Hence we write the "Number
column once " after combining both tables.

2. Outer Join
Outer join is a type of join that retrieves matching as well as non-matching records from
related tables. There are three types of outer join
 Left outer join
 Right outer join
 Full outer join
Number Cube
(a) Left Outer Join
2 8
It is also called left join. This type of outer join retrieves all records
3 27
from the left table and retrieves matching records from the right
table. 5 125
Example: Suppose there are two tables Table A and Table B
Table A Table B
Number Square
2 4
3 9
4 16

A⟕B
Output
Number Square Cube
2 4 8
3 9 27
4 16 NULL
Explanation: Since we know in the left outer join we take all the columns from the left table
(Here Table A) In the table A we can see that there is no Cube value for number 4. so we
mark this as NULL.

(b) Right Outer Join

It is also called a right join. This type of outer join retrieves all records from the right table
and retrieves matching records from the left table. And for the record which doesn't lies in
Left table will be marked as NULL in result Set.
Right Outer Join
Example: Suppose there are two tables Table A and Table B
A⟖B
Number Square Cube
2 4 8
Output: 3 9 27
5 NULL 125
Explanation: Since we know in the right outer join we take all the columns from the right
table (Here Table B) In table A we can see that there is no square value for number 5. So we
mark this as NULL.

(c) Full Outer Join

FULL JOIN creates the result set by combining the results of both LEFT JOIN and RIGHT
JOIN. The result set will contain all the rows from both tables. For the rows for which there is
no matching, the result set will contain NULL values.
Example: Table A and Table B are the same as in the left outer join
A⟗B
Output:
Number Square Cube
2 4 8
3 9 27
4 16 NULL
5 NULL 125
Explanation: Since we know in full outer join we take all the columns from both tables (Here
Table A and Table B) In the table A and Table B we can see that there is no Cube value for
number 4 and No Square value for 5 so we mark this as NULL.

3. Apply relational algebra concepts to perform the operations: Select, Project, Cartesian Product,
Union, and Set Difference using appropriate relations. Demonstrate each operation with a clear
expression and example result to show understanding of set-based query processing.
Ref slide no:15-26

4. Examine the effectiveness of using aggregate functions in SQL to summarize datasets. Provide an
example to support your evaluation.

Aggregate Functions in SQL

Aggregate functions in SQL are built-in functions that operate on a set of values (a column
or expression) and return a single summarized value. They are often used with GROUP BY
to categorize data.
Common aggregate functions & behavior
 COUNT(*) — number of rows in the group (counts NULLs because it counts rows).
 COUNT(col) — number of non-NULL values in col.
 COUNT(DISTINCT col) — number of distinct non-NULL values.
 SUM(expr) — sum of numeric values (NULLs ignored).
 AVG(expr) — arithmetic mean; internally SUM(expr)/COUNT(expr)(NULLs
ignored).
 MIN(expr), MAX(expr) — smallest / largest non-NULL value.
COUNT()
 Definition: Returns the number of rows in a table or the number of non-NULL values
in a column.
 Usage: Useful to find the size of a dataset, number of transactions, or non-empty
values.

Example Query:
SELECT COUNT(*) AS TotalSales,
COUNT(Amount) AS NonNullAmounts
FROM Sales;

Result:

TotalSales NonNullAmounts
5 5

2. SUM()
 Definition: Returns the total sum of numeric values.
 Usage: Commonly used to calculate revenue, salary totals, or stock quantities.

Example Query:
SELECT SUM(Amount) AS TotalRevenue
FROM Sales;

Result:
TotalRevenue
158000

3. AVG()
 Definition: Returns the average (mean) of numeric values.
 Usage: Useful for finding average salary, average marks, or average sales.

Example Query:
SELECT AVG(Amount) AS AverageSale
FROM Sales;

Result:
AverageSale
31600

4. MIN()
 Definition: Returns the smallest value in a column.
 Usage: Helpful in identifying the least sales amount, minimum salary, or lowest
marks.

Example Query:

SELECT MIN(Amount) AS SmallestSale

FROM Sales;
 Result:
SmallestSale
15000

5. MAX()
 Definition: Returns the largest value in a column.
 Usage: Used to find highest salary, maximum marks, or top sales amount.

Example Query:

SELECT MAX(Amount) AS BiggestSale FROM Sales;

Result:

BiggestSale
60000

Effectiveness of Aggregate Functions in SQL

1. Data Summarization
 Aggregate functions reduce large volumes of raw data into small, interpretable
results.
 Example: Instead of looking at 1 million sales rows, SUM() gives the total revenue
directly.

2. Better Decision-Making
 Functions like AVG() and SUM() provide performance indicators (average
revenue, total salary, etc.).
 Example: An HR manager can quickly check AVG(Salary) to know if salaries
meet company standards.

3. Efficient Analysis
 SQL’s built-in aggregate functions are optimized at the database level, making them
faster than calculating totals/averages manually in application code.
 Example: COUNT(*) efficiently returns row counts even in huge datasets.

4. Group-wise Insights
 When used with GROUP BY, aggregate functions show category-wise summaries.
 Example: SUM(Sales) per region gives insights into which region performs best.

5. Handling NULL Values

 Most aggregate functions ignore NULLs, which prevents misleading results.
 Example: AVG(Salary) ignores missing salaries to give a correct average.
5. a. Simplify the Nested subqueries with an example
b. Illustrate different types of views in SQL with appropriate examples .

Nested Subqueries
A subquery is a query written inside another SQL query. When one query is placed inside
another query, it is called a nested subquery.
- The inner query executes first and passes its result to the outer query.
- Nested subqueries are commonly used in the WHERE, HAVING, or FROM clauses.

Characteristics

1. A nested subquery may return a single value, multiple values, or even a table.
2. They can be classified as:
- Single-row subquery → Returns one value.
- Multiple-row subquery → Returns multiple values.
- Multiple-column subquery → Returns multiple columns.
3. They are enclosed in parentheses ( ).
4. They are executed from inside out (inner query runs first).

Advantages

• Allows step-by-step problem solving (break complex logic into parts).

• Provides a logical structure by separating tasks.
• Useful when filtering based on results of another query.

Example 1 – Single-row Subquery

Problem: Find employees whose salary is greater than the average salary.
SQL:
SELECT emp_name, salary
FROM Employees
WHERE salary > (
SELECT AVG(salary)
FROM Employees
);

Example 2 – Multiple-row Subquery

Problem: Find employees who work in departments located in New York.

SQL:
SELECT emp_name
FROM Employees
WHERE dept_id IN (
 SELECT dept_id
FROM Departments
WHERE location = 'New York'
);

Example 3 – Nested Subquery in FROM Clause

Problem: Display employees whose salary is above the average of each department.
SQL:
SELECT emp_name, salary
FROM Employees e
JOIN (
SELECT dept_id, AVG(salary) AS avg_sal
FROM Employees
GROUP BY dept_id
)d
ON e.dept_id = d.dept_id
WHERE [Link] > d.avg_sal;

SQL Views
A view in SQL is a saved SQL query that acts as a virtual table. Unlike regular tables, views do not
store data themselves. Instead, they dynamically generate data by executing the SQL query defined in
the view each time it is accessed.
It can fetch data from one or more tables and present it in a customized format, allowing developers
to:
• Simplify Complex Queries: Encapsulate complex joins and conditions into a single object.
• Enhance Security: Restrict access to specific columns or rows.
• Present Data Flexibly: Provide tailored data views for different users.
Example:
StudentDetails:
Create StudentDetails table
CREATE TABLE StudentDetails (
S_ID INT PRIMARY KEY,
NAME VARCHAR(255),
ADDRESS VARCHAR(255)
);
INSERT INTO StudentDetails (S_ID, NAME, ADDRESS)
VALUES
(1, 'Harsh', 'Kolkata'),
(2, 'Ashish', 'Durgapur'),
 (3, 'Pratik', 'Delhi'),
(4, 'Dhanraj', 'Bihar'),
(5, 'Ram', 'Rajasthan');
Output:
S_ID Name Address
1 Harsh Kolkata
2 Ashish Durgapur
3 Pratik Delhi
4 Dhanraj Bihar
5 Ram Rajsthan
StudentMarks:
Create StudentMarks table
CREATE TABLE StudentMarks (
ID INT PRIMARY KEY,
NAME VARCHAR(255),
Marks INT,
Age INT
);

INSERT INTO StudentMarks (ID, NAME, Marks, Age)

VALUES
(1, 'Harsh', 90, 19),
(2, 'Suresh', 50, 20),
(3, 'Pratik', 80, 19),
(4, 'Dhanraj', 95, 21),
(5, 'Ram', 85, 18);
ID Name Marks Age
1 Harsh 90 19
2 Suresh 50 20
3 Pratik 80 19
4 Dhanraj 95 21
5 Ram 85 18
CREATE VIEWS in SQL
We can create a view using CREATE VIEW statement. A View can be created from a single table or
multiple tables.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;
Key Terms:
• view_name: Name for the View
• table_name: Name of the table
• condition: Condition to select rows
Example 1: Creating a Simple View from a Single Table
Example 1.1: In this example, we will create a View named DetailsView from the table
StudentDetails.
Query:
CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;
Use the below query to retrieve the data from this view
SELECT * FROM DetailsView;
Output:
Name Address
Harsh Kolkata
Ashish Durgapur
Pratik Delhi
Dhanraj Bihar
Example 1.2: Here, we will create a view named StudentNames from the table StudentDetails.
Query:
CREATE VIEW StudentNames AS
SELECT S_ID, NAME
FROM StudentDetails
ORDER BY NAME;
If we now query the view as,
SELECT * FROM StudentNames;
Output:
S_ID Name
2 Ashish
4 Dhanraj
1 Harsh
3 Pratik
5 Ram
Creating a View From Multiple Tables
In this example we will create a View MarksView that combines data from bothtables StudentDetails
and StudentMarks. To create a View from multiple tables we can simply include multiple tables in the
SELECT statement.
Query:
CREATE VIEW MarksView AS
SELECT [Link], [Link], [Link]
FROM StudentDetails, StudentMarks
WHERE [Link] = [Link];
To display data of View MarksView:
SELECT * FROM MarksView;
Output:
Name Address Marks
Harsh Kolkata 90
Pratik Delhi 80
Dhanraj Bihar 95
Ram Rajsthan 85

Managing Views: Listing, Updating, and Deleting

1. Listing all Views in a Database
We can list all the Views in a database, using the SHOW FULL TABLES statement or using the
information_schema table. A View can be created from a single table or multiple tables
2. Deleting a View
SQL allows us to delete an existing View. We can delete or drop View using the DROP statement.
Syntax:DROP VIEW view_name;
Example: DROP VIEW MarksView;
3. Updating a View Definition
If we want to update the existing data within the view, use the UPDATE statement.
UPDATE view_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
Eg:

CREATE OR REPLACE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note: Not all views can be updated using the UPDATE statement.

Rules to Update Views in SQL:

Certain conditions need to be satisfied to update a view. If any of these conditions are not met, the
view can not be updated.
1. The SELECT statement which is used to create the view should not include GROUP BY
clause or ORDER BY clause.
2. The SELECT statement should not have the DISTINCT keyword.
3. The View should have all NOT NULL values.
4. The view should not be created using nested queries or complex queries.
5. The view should be created from a single table. If the view is created using multiple tables
then we will not be allowed to update the view.
Advanced Techniques with Views
1. Updating Data Through Views
We can use the CREATE OR REPLACE VIEW statement to add or replace fields from a view If we
want to update the view MarksView and add the field AGE to this View from StudentMarks Table, we
can do this by:
Example:
CREATE OR REPLACE VIEW MarksView AS
SELECT [Link], [Link], [Link],
[Link]
FROM StudentDetails, StudentMarks
WHERE [Link] = [Link];
If we fetch all the data from MarksView now as:
SELECT * FROM MarksView;
Output:
Name Address Marks Age
Harsh Kolkata 90 19
Pratik Delhi 80 19
Dhanraj Bihar 95 21
Ram Rajasthan 85 18
2. Inserting Data into Views
We can insert a row in a View in the same way as we do in a table. We can use the INSERT INTO
statement of SQL to insert a row in a View. In the below example, we will insert a new row in the
View DetailsView which we have created above in the example of "creating views from a single
table".
Example:
INSERT INTO DetailsView(NAME, ADDRESS)
VALUES("Suresh","Gurgaon");
If we fetch all the data from DetailsView now as,
SELECT * FROM DetailsView;
Output:
Name Address
Harsh Kolkata
Ashish Durgapur
Pratik Delhi
Dhanraj Bihar
Suresh Gurgaon
3. Deleting a row from a View
Deleting rows from a view is also as simple as deleting rows from a table. We can use the DELETE
statement of SQL to delete rows from a view. Also deleting a row from a view first deletes the row
from the actual table and the change is then reflected in the view. In this example, we will delete the
last row from the view DetailsView which we just added in the above example of inserting rows.
Example:
DELETE FROM DetailsView
WHERE NAME="Suresh";
If we fetch all the data from DetailsView now as,
SELECT * FROM DetailsView;
Output:
Name Address
Harsh Kolkata
Ashish Durgapur
Pratik Delhi
Dhanraj Bihar
4. WITH CHECK OPTION Clause
The WITH CHECK OPTION clause in SQL is a very useful clause for views. It applies to an
updatable view. It is used to prevent data modification (using INSERT or UPDATE) if the condition in
the WHERE clause in the CREATE VIEW statement is not satisfied.
If we have used the WITH CHECK OPTION clause in the CREATE VIEW statement, and if the
UPDATE or INSERT clause does not satisfy the conditions then they will return an error. In the below
example, we are creating a View SampleView from the StudentDetails Table with a WITH CHECK
OPTION clause.
Example:
CREATE VIEW SampleView AS
SELECT S_ID, NAME
FROM StudentDetails
WHERE NAME IS NOT NULL
WITH CHECK OPTION;
In this view, if we now try to insert a new row with a null value in the NAME column then it will give
an error because the view is created with the condition for the NAME column as NOT NULL. For
example, though the View is updatable then also the below query for this View is not valid:
INSERT INTO SampleView(S_ID)
VALUES(6);

6. A government transport department maintains a database to manage information about

vehicles, the people who own them, and ownership details. The database includes the
following relations:
 The Vehicle table stores details of each registered vehicle. Every vehicle has a unique
registration number (reg_no), along with its make (brand/model) and colour.
 The Person table stores information about individuals who may own one or more
vehicles. Each person has a unique employee number (eno), along with their name
and address.
 The Owner table establishes a relationship between persons and the vehicles they
own. It records which person (eno) owns which vehicle (reg_no).
Provide expressions in relational algebra to answer the following queries and explain the
expression also.
(i) List the names of persons who do not own any car.
(ii) List the names of persons who own only Maruti Cars.
(iii) List details of the Person who have all the Vehicles.
(iv) List eno of person who do not have Vehicle number DB2003
Schema / notation
 Vehicle(reg_no, make, colour)
 Person(eno, name, address)
 Owner(eno, reg_no) — associates people with the vehicles they own
 σ = Select (filter rows)
 π = Project (keep columns)
 ⋈ = Natural join (or equijoin; specify condition if needed)
 − = Set difference
 ÷ = Division (R ÷ S returns all values of the left relation's attributes that are associated
with every tuple of S)
 ρ = Rename (used when needed)

Explain the operations.

(i) List the names of persons who do not own any car

π_name(Person) − π_name(Person ⋈ Owner)

Take all person names, subtract those who appear in Owner.

(ii) List the names of persons who own only Maruti Cars

π_name(Person ⋈ Owner ⋈ Vehicle) − π_name(σ_{make ≠ 'Maruti'}(Person ⋈ Owner ⋈

Vehicle))

From all owners, remove those who own at least one non-Maruti. Remaining persons own only
Maruti cars.

(iii) List details of the Person who have all the Vehicles

Person ⋈ (Owner ‚ π_reg_no(Vehicle))

Use division: select persons whose owned vehicles cover all reg_no values in Vehicle.

(iv) List eno of person who do not have Vehicle number DB2003

π_eno(Person) − π_eno(σ_{reg_no = 'DB2003'}(Owner))

Take all person enos, subtract those who own DB2003.