Lecture-5: SQL-III

Course CSB2204: Database Systems

Assoc. Prof. Mohammed Gadelrab

School of Artificial Intelligence & Data Management
Badr University in Assiut (BUA)
Outline

Join Types and Conditions
– Inner Join
– Outer Join

Expanded Query
– Group By and Having

Views

Transactions
Spring 2025 BUA-AI & DM School 2
 JOIN Types and Conditions

Join operations take two relations and return as a result another relation.

These additional operations are typically used as subquery expressions in the FROM clause.

Join condition – defines which tuples in the two relations match.

Join type – defines how tuples in each relation that do not match any tuple in the other relation
(based on the join condition) are treated.

Spring 2025 BUA-AI & DM School 3

 INNER JOIN
●
Default type of join in a joined table course relation:
●
Tuple is included in the result only if a matching tuple exists in the
other relation
course INNER JOIN prereq ON
course.course_id = prereq.course_id

Prereq relation:

●
What is the difference between inner join and a natural join?
– Natural Join joins two tables based on the same attribute name and
datatypes. The resulting table will contain all the attributes of both tables
but keep only one copy of each common column. 
course information is missing CS-347
– Inner Join joins two table on the basis of the column which is explicitly

prereq information is missing CS-315
specified in the ON clause. The resulting table will contain all the
attributes from both the tables including common column also.

Spring 2025 BUA-AI & DM School 4

 OUTER JOIN

An extension of the join operation that avoids loss of information.

Computes the join and then adds tuples form one relation that does not match tuples in the other
relation to the result of the join.

Uses null values for tuples that does not have a match in the other table.

Three forms of outer join:
– LEFT OUTER JOIN
– RIGHT OUTER JOIN
– FULL OUTER JOIN

Spring 2025 BUA-AI & DM School 5

 Left, Right and Full Outer Join
 Left Outer Join:
course relation:
course NATURAL LEFT JOIN prereq

 Right Outer Join:

course NATURAL RIGHT JOIN prereq Prereq relation:

 Full Outer Join:

course NATURAL FULL JOIN prereq 
course information is missing CS-347

prereq information is missing CS-315

Spring 2025 BUA-AI & DM School 6

 What is the difference between inner join and outer join?

INNER JOIN (versus OUTER JOIN):
– Default type of join in a joined table.
– Tuple is included in the result only if a matching tuple exists in the other
relation.

LEFT OUTER JOIN:
– Every tuple in left table must appear in result.
– If no matching tuple, padded with NULL values for attributes of right table.

RIGHT OUTER JOIN:
– Every tuple in right table must appear in result.
– If no matching tuple, padded with NULL values for attributes of left table.

Spring 2025 BUA-AI & DM School 7

Spring 2025 BUA-AI & DM School 8
EXPANDED Block Structure of SQL Queries

Spring 2025 BUA-AI & DM School 9

 GROUP BY

Partition relation into subsets of tuples
– Based on grouping attribute(s)
– Apply function to each such group independently
– GROUP BY clause:
●
Specifies grouping attributes
– COUNT (*) counts the number of rows in the group.
– The grouping attribute must appear in the SELECT clause.

– If the grouping attribute has NULL as a possible value, then a separate group is created for the
null value (e.g., null Dno in the above query).

Example of GROUP BY:

Spring 2025 BUA-AI & DM School 10

 HAVING Clause

HAVING clause
– Provides a condition to select or reject an entire group.

Example: For each project on which more than two employees
work, retrieve the project number, the project name, and the number
of employees who work on the project.

Spring 2025 BUA-AI & DM School 11

 Combining the WHERE and the HAVING Clause

The WHERE clause applies tuple by tuple whereas HAVING applies to entire group of tuples

Example: For each department that has more than five employees, retrieve the department number and
the number of its employees who are making more than $40,000.

✗ Incorrect Query: 
Query Syntax Order: 
Order of Execution:
SELECT Dno, COUNT(*)
How the query is How the database
FROM EMPLOYEE
WHERE salary >40000
written by the user: internally processes
GROUP BY Dno the query:
HAVING COUNT(*)>5; SELECT
FROM FROM
✔ Correct Query: WHERE WHERE
SELECT Dnumber, COUNT(*)
FROM DEPARTMENT, EMPLOYEE GROUP BY GROUP BY
WHERE Dnumber=Dno AND salary >40000 AND HAVING HAVING
(SELECT Dno ORDER BY SELECT
FROM EMPLOYEE
LIMIT ORDER BY
GROUP BY Dno
HAVING COUNT(*)>5); LIMIT

Spring 2025 BUA-AI & DM School 12

 Views

SQL View: A single table derived from other tables that are called the defining tables.

A view table, is considered to be a virtual table that is not necessarily populated.

For example, in some cases, it is not desirable for all users to see the entire logical model (that is, all the actual relations stored in
the database.)

A view provides a mechanism to hide certain data from the view of certain users.

Any relation that is not of the conceptual model but is made visible to a user as a “virtual relation” is called a view.

A view is defined using the create view statement which has the form

CREATE VIEW v AS < query expression >

– where <query expression> is any legal SQL expression. The view name is represented by v.

Once a view is defined, the view name can be used to refer to the virtual relation that the view generates.

View definition does not create a new relation by evaluating the query expression
– Rather, a view definition causes the saving of an expression; the expression is substituted into queries using the view.

Once a View is defined, SQL queries can use the View relation in the FROM clause

View is always up-to-date, this is the esponsibility of the DBMS and not the user

DROP VIEW command
Spring 2025 BUA-AI & DM School 13
 Examples of Views

A view of instructors without their salary
CREATE VIEW faculty AS
SELECT ID, name, dept_name
FROM instructor

Create a view of department salary totals
CREATE VIEW departments_total_salary(dept_name, total_salary) AS
SELECT dept_name, sum (salary)
FROM instructor
GROUP BY dept_name;


Views may be defined using other views:
CREATE VIEW physics_fall_2017 as
SELECT course.course_id, sec_id, building, room_number
FROM course, section
WHERE course.course_id = section.course_id
and course.dept_name = 'Physics'
and section.semester = 'Fall'
and section.year = '2017’;
CREATE VIEW physics_fall_2017_watson AS
SELECT course_id, room_number
FROM physics_fall_2017
WHERE building= 'Watson';

Spring 2025 BUA-AI & DM School 14

 Materialization of Views
●
Certain database systems allow view relations to be physically stored.
– Physical copy created when the view is defined.
– Such views are called Materialized view:
●
View materialization
– Physically create a temporary view table when the view is first queried
– Keep that table on the assumption that other queries on the view will follow
– Requires efficient strategy for automatically updating the view table when the base
tables are updated
●
Incremental update strategy for materialized views
– DBMS determines what new tuples must be inserted, deleted, or modified in a
materialized view table.
Spring 2025 BUA-AI & DM School 15
 Updating Materialized Views
●
If relations used in the query are updated, the materialized view
result becomes out of date
●
Need to maintain the view, by updating the view whenever the
underlying relations are updated.
●
Most SQL implementations allow updates only on simple views.
●
Multiple ways to handle materialization:
– immediate update strategy updates a view as soon as the base tables are
changed
– lazy update strategy updates the view when needed by a view query
– periodic update strategy updates the view periodically (in the latter
strategy, a view query may get a result that is not up-to-date). This is
commonly used in Banks, Retail store operations, etc.
Spring 2025 BUA-AI & DM School 16
 Transactions

A transaction consists of a sequence of query and/or update statements and is a “unit” of work

The SQL standard specifies that a transaction begins implicitly when an SQL statement is
executed.

The transaction must end with one of the following statements:
– Commit work. The updates performed by the transaction become permanent in the
database.
– Rollback work. All the updates performed by the SQL statements in the transaction are
undone.

Atomic transaction:
– either fully executed or rolled back as if it never occurred

Isolation from concurrent transactions

Spring 2025 BUA-AI & DM School 17

 ???

BUA-AI & DM School 18