Unit 3: Relational Model and Structured
Query Language
1. Introduction to SQL and Structure of Relational
Databases
Structured Query Language (SQL) is a standard, non-procedural language for accessing
and manipulating relational databases. Users specify what data they want to retrieve or
modify, and the database management system (DBMS) determines how to perform the task.
A relational database consists of a collection of tables (relations), where each table has a
unique name.
● Rows (Tuples): Represent a single data record within a table.
● Columns (Attributes): Represent a characteristic or property of the data records. Each
column has a specific data type (domain).
2. SQL Data Definition Language (DDL)
DDL commands are used to define and manage the database schema.
● CREATE TABLE: Defines a new table with its attributes and their data types.
CREATE TABLE department (
dept_name VARCHAR(20),
building VARCHAR(15),
budget NUMERIC(8,2),
PRIMARY KEY (dept_name)
);
● DROP TABLE: Deletes an entire table, including its structure and data.
DROP TABLE department;
● ALTER TABLE: Modifies the structure of an existing table.
○ Add a column:
ALTER TABLE department ADD manager_id VARCHAR(5);
○ Drop a column:
ALTER TABLE department DROP COLUMN manager_id;
Integrity Constraints
Integrity constraints are rules enforced on data columns to ensure data accuracy and
consistency.
● NOT NULL: Ensures that a column cannot have a NULL value.
● PRIMARY KEY: Uniquely identifies each record in a table. It must contain unique values
and cannot contain NULL values.
● UNIQUE: Ensures that all values in a column are different. It allows one NULL value.
● FOREIGN KEY: A key used to link two tables together. It is a field (or collection of fields)
in one table that refers to the PRIMARY KEY in another table. This enforces referential
integrity.
○ Cascading Actions:
■ ON DELETE CASCADE: If a row in the parent table is deleted, the corresponding
rows in the child table will also be deleted.
■ ON UPDATE CASCADE: If the primary key value in the parent table is updated,
the foreign key values in the child table will also be updated.
● CHECK: Ensures that all values in a column satisfy a specific condition.
CREATE TABLE instructor (
ID CHAR(5),
name VARCHAR(20) NOT NULL,
salary NUMERIC(8,2),
CHECK (salary > 0.0),
PRIMARY KEY (ID)
);
3. Basic Structure of SQL Queries
The basic structure of an SQL query involves three main clauses: SELECT, FROM, and WHERE.
SELECT attribute_list
FROM table_list
WHERE predicate;
● SELECT: Specifies the columns (attributes) to be returned in the result set. Corresponds
to the projection operation in relational algebra.
○ Use DISTINCT to eliminate duplicate rows.
○ Use * to select all columns.
● FROM: Specifies the table(s) from which to retrieve the data. Corresponds to the
Cartesian product if multiple tables are listed.
● WHERE: Filters records based on a specified condition (predicate). Corresponds to the
selection operation in relational algebra.
Example: Find the names of all instructors in the 'Comp. Sci.' department with a salary greater
than 70,000.
SELECT name
FROM instructor
WHERE dept_name = 'Comp. Sci.' AND salary > 70000;
4. Additional Basic Operations
● ORDER BY: Sorts the result set in ascending (ASC) or descending (DESC) order.
SELECT name, salary
FROM instructor
ORDER BY salary DESC;
● String Operations: The LIKE operator is used in a WHERE clause to search for a
specified pattern in a column.
○ %: Matches any sequence of zero or more characters.
○ _: Matches any single character.
-- Finds instructors whose name contains 'in'
SELECT name FROM instructor WHERE name LIKE '%in%';
● Rename Operation (AS): Assigns a temporary, more readable name to a table or a
column in a query.
SELECT name, salary * 12 AS annual_salary
FROM instructor;
5. Set Operations
SQL supports set operations to combine the results of two or more SELECT statements.
● UNION: Combines the result sets and removes duplicate rows. UNION ALL includes
duplicates.
● INTERSECT: Returns only the rows that appear in both result sets. INTERSECT ALL
includes duplicates.
● EXCEPT (or MINUS in some databases): Returns rows from the first query that are not
present in the second query. EXCEPT ALL considers duplicates.
Example: Find courses that ran in Fall 2009 and in Spring 2010.
(SELECT course_id FROM section WHERE semester = 'Fall' AND year = 2009)
INTERSECT
(SELECT course_id FROM section WHERE semester = 'Spring' AND year = 2010);
6. Null Values
A NULL value in a table is a value in a field that appears to be blank. It signifies that the value
is unknown or does not exist.
● Standard arithmetic operations involving NULL result in NULL.
● To check for NULL values, use the IS NULL or IS NOT NULL operators.
SELECT name FROM instructor WHERE salary IS NULL;
7. Aggregate Functions
Aggregate functions perform a calculation on a set of values and return a single value.
● COUNT(): Returns the number of rows.
● SUM(): Returns the total sum of a numeric column.
● AVG(): Returns the average value of a numeric column.
● MIN(): Returns the smallest value.
● MAX(): Returns the largest value.
GROUP BY Clause: Groups rows that have the same values into summary rows. It is often
used with aggregate functions.
HAVING Clause: Filters the results of a GROUP BY query based on a condition applied to the
aggregate function results.
Example: Find the average salary for each department with an average salary greater than
42,000.
SELECT dept_name, AVG(salary)
FROM instructor
GROUP BY dept_name
HAVING AVG(salary) > 42000;
8. Nested Subqueries
A subquery is a query nested inside another query. They are used to perform operations that
require more than one step.
● Set Membership (IN, NOT IN): Checks if a value is present in the results of a subquery.
Example: Find courses offered in Fall 2009 but not in Spring 2010.
SELECT course_id
FROM section
WHERE semester = 'Fall' AND year = 2009 AND
course_id NOT IN (SELECT course_id
FROM section
WHERE semester = 'Spring' AND year = 2010);
● Set Comparison (SOME, ALL): Compares a value to a set of values returned by a
subquery.
9. Modification of the Database (DML)
Data Manipulation Language (DML) commands are used to manage data within schema
objects.
● INSERT: Adds new rows of data into a table.
INSERT INTO course (course_id, title, dept_name, credits)
VALUES ('CS-101', 'Intro to CS', 'Comp. Sci.', 4);
● UPDATE: Modifies existing records in a table.
UPDATE instructor
SET salary = salary * 1.05
WHERE salary < 70000;
● DELETE: Removes existing records from a table.
DELETE FROM instructor
WHERE dept_name = 'Finance';
10. Join Expressions
Joins are used to combine rows from two or more tables based on a related column between
them.
● INNER JOIN (or NATURAL JOIN): Returns records that have matching values in both
tables.
● LEFT OUTER JOIN: Returns all records from the left table, and the matched records from
the right table. The result is NULL from the right side if there is no match.
● RIGHT OUTER JOIN: Returns all records from the right table, and the matched records
from the left table.
● FULL OUTER JOIN: Returns all records when there is a match in either the left or right
table.
Example: Retrieve instructor names and the courses they teach.
SELECT [Link], S.course_id
FROM instructor AS T JOIN teaches AS S ON [Link] = [Link];
11. Views
A view is a virtual table based on the result-set of an SQL statement. It contains rows and
columns, just like a real table, but it is not stored permanently.
● Creating a View:
CREATE VIEW faculty AS
SELECT ID, name, dept_name
FROM instructor;
● Using a View: A view can be queried just like a regular table.
SELECT name FROM faculty WHERE dept_name = 'Biology';
Views can be used to simplify complex queries, provide a level of security by restricting
access to data, and present data in a different perspective.
12. Accessing SQL from a Programming Language
SQL statements can be embedded within general-purpose programming languages like Java,
C++, or Python. This allows applications to interact with a database.
● Embedded SQL: SQL statements are written directly into the host language code,
prefixed with a special keyword (e.g., EXEC SQL). A preprocessor converts these
statements into function calls to a database library.
● Dynamic SQL: Allows applications to construct and submit SQL queries at runtime,
providing greater flexibility.
● APIs (e.g., JDBC, ODBC): Provide a standard set of functions for applications to
connect to a database, execute queries, and process results, without embedding SQL
directly into the code. This is the more common and modern approach.