CSC 2702

Introduction to SQL
Department of Computer Science
School of Natural Sciences
University of Zambia
Overview

• SQL's Impact on Relational Databases

• SQL is considered a major factor in the commercial success of relational
databases because it became a standard language for these databases
• This standardization reduced the cost and effort of migrating applications
between different relational DBMS products

• Relational DBMS and SQL Standardization

• While differences exist among various commercial relational DBMS packages,
adhering to the standard SQL features helps in minimizing compatibility
issues
• Users can write database application programs that can access data across
different relational DBMSs without changing the database sublanguage,
provided all DBMSs support standard SQL
Overview

• SQL and Relational Algebra

• SQL is a higher-level declarative language compared to relational algebra
• Relational algebra operations, important for understanding query processing
and optimization, require users to specify how the operations should be
executed
• SQL, on the other hand, allows users to specify what they want, leaving the
DBMS to decide how to execute the query

• SQL History and Evolution

• SQL originated at IBM Research and was called SEQUEL (Structured English
QUEry Language)
• Updates expanded the language to include features such as XML and object
database functionality
Overview

• SQL Language
• SQL is a comprehensive database language that includes both DDL (Data
Definition Language) and DML (Data Manipulation Language) features
• It allows defining schemas, creating and managing views, specifying constraints
(e.g., integrity, referential), security/authorization, and transaction controls
• SQL is also used for embedding in general-purpose programming languages like
Java or C/C++

• SQL Core and Extensions

• The later SQL standards (from SQL:1999 onwards) are divided into a core
specification and specialized extensions
• The core is required to be implemented by all SQL-compliant RDBMS vendors,
while extensions are optional and can be implemented for specific
applications such as data mining, OLAP, spatial data, and multimedia data
Overview

• We will look at
• SQL DDL commands for schema and table creation, along with basic data
types in SQL
• How to define basic constraints (e.g., key, referential integrity)
• Basic SQL constructs for retrieval queries
• SQL data manipulation commands for inserting, deleting, and updating data

• SQL is a powerful, comprehensive language for managing relational databases,

evolving over time to incorporate features for modern applications and specific
database needs

• It simplifies database interactions while providing flexibility and ensuring

portability across relational DBMS platforms
SQL Data Definition and Data Types

• SQL uses the terms table, row, and column to refer to the relational model's
relation, tuple, and attribute, respectively
• These terms are used interchangeably in discussions

• The main SQL command for data definition is the CREATE statement,
which is versatile and can be used to create:
• Schemas
• Tables (relations)
• Types
• Domains
• Other constructs such as views, assertions, and triggers
SQL Data Definition and Data Types - Schema and Catalog
Concepts in SQL
• SQL has a schema to group tables and other database constructs
• A schema has a name and an authorization identifier for the owner
• Schema elements include tables, types, constraints, views, and more

• Schema Creation Example: A schema called COMPANY owned by user

Jsmith can be created with:
• CREATE SCHEMA COMPANY AUTHORIZATION 'Jsmith’;

• Authorization: Only users with proper privileges can create schemas and
schema elements, which are granted by the system administrator or DBA
SQL Data Definition and Data Types - Schema and Catalog
Concepts in SQL
• SQL Catalog: A catalog is a collection of schemas, with a default schema
typically available for users upon login

• Each catalog includes INFORMATION_SCHEMA, which provides metadata

about all schemas and their elements in the catalog

• Integrity constraints (e.g., referential integrity) can only be enforced between

schemas within the same catalog, and elements like types can be shared
across schemas in the same catalog
SQL Data Definition and Data Types - The CREATE TABLE
Command in SQL
• Used to create tables or relations in an SQL DBMS

• Each attribute is defined with a name, a data type (domain), and optional
constraints (like NOT NULL)

• Key constraints (primary or foreign keys), entity integrity, and referential

integrity can be included in the CREATE TABLE statement or added later
using ALTER TABLE

• Tables created with the CREATE TABLE command are base tables—they
are physically stored in the DBMS

• Virtual tables can be created using the CREATE VIEW command, but
these may not be physically stored
SQL Data Definition and Data Types - The CREATE TABLE
Command in SQL
• The schema where a table resides can be specified explicitly
• CREATE TABLE COMPANY.EMPLOYEE

• Or implicitly
• CREATE TABLE EMPLOYEE

• Circular references (e.g., a foreign key that references the same table) and
references to tables that have not yet been created may cause errors

• In these cases, foreign key constraints can be added later using ALTER
TABLE to avoid issues during the initial table creation
SQL Data Definition and Data Types - The CREATE TABLE
Command in SQL
SQL Data Definition and Data Types - The CREATE TABLE
Command in SQL
SQL Data Definition and Data Types - Attribute Data Types
and Domains in SQL
• SQL offers a variety of data types for defining attributes in tables, such
as numeric, character, bit string, Boolean, date, and time types

• Numeric Data Types

• INTEGER or INT, SMALLINT: For integer values
• FLOAT, REAL, DOUBLE PRECISION: For floating-point numbers
• DECIMAL(i, j) or NUMERIC(i, j): For formatted numbers with i total digits and j
digits after the decimal
SQL Data Definition and Data Types - Attribute Data Types
and Domains in SQL
• Character String Data Types
• CHAR(n) or CHARACTER(n): Fixed-length strings of n characters
• VARCHAR(n) or CHARACTER VARYING(n): Variable-length strings with a maximum of n
characters
• CLOB(n): For large text values, where n can be in kilobytes (K), megabytes (M), or gigabytes
(G)

• Bit String Data Types

• BIT(n): Fixed-length bit strings of n bits
• BIT VARYING(n): Variable-length bit strings with a maximum of n bits
• BLOB(n): For large binary data, similar to CLOB, but for binary objects like images
• Boolean Data Type: Represents values TRUE, FALSE, and UNKNOWN (to account for NULL
values)
SQL Data Definition and Data Types - Attribute Data Types
and Domains in SQL
• Date and Time Types
• DATE: Formatted as YYYY-MM-DD
• TIME: Formatted as HH:MM:SS. It can include fractional seconds (e.g., TIME(i)
where i represents additional precision)
• TIME WITH TIME ZONE: Includes the time zone offset
• TIMESTAMP: Combines DATE and TIME, with optional precision and time zone

• Additional Types
• INTERVAL: Specifies intervals to increment/decrement date or time values
SQL Data Definition and Data Types - Attribute Data Types
and Domains in SQL
• Domains: A named data type used to simplify schema definitions and enforce
consistency. For example:
• CREATE DOMAIN SSN_TYPE AS CHAR(9);
• This domain can be reused in table definitions instead of specifying CHAR(9)
repeatedly

• User-Defined Types (UDTs)

• Created using CREATE TYPE for more complex data structures, often used in
conjunction with object-oriented database features
Specifying Constraints in SQL - Specifying Attribute
Constraints and Attribute Defaults
• SQL provides mechanisms to enforce constraints on attribute values and set
default values
• They allow for better control over the data being stored in a database,
ensuring data integrity and enforcing business rules directly at the schema
level

• NOT NULL Constraint

• Ensures that an attribute cannot have a NULL value
• This constraint is automatically applied to primary key attributes but can also
be used on other attributes where NULL values are not allowed
• Example: Dnumber INT NOT NULL;
Specifying Constraints in SQL - Specifying Attribute
Constraints and Attribute Defaults
• DEFAULT Values
• Assigns a default value to an attribute when a new tuple is inserted without
providing an explicit value for that attribute
• If no default value is specified, the attribute's default is NULL unless the attribute
has a NOT NULL constraint
• Example: Dept_name VARCHAR(30) DEFAULT 'General’;
Specifying Constraints in SQL - Specifying Attribute
Constraints and Attribute Defaults
• CHECK Constraints
• Restricts attribute values to satisfy specific conditions
• The CHECK clause can be applied directly to an attribute or within a CREATE
DOMAIN statement
• Example (Attribute Level): Dnumber INT NOT NULL CHECK (Dnumber > 0
AND Dnumber < 21);
• Example (Domain Level): CREATE DOMAIN D_NUM AS INTEGER CHECK
(D_NUM > 0 AND D_NUM < 21);
• Once a domain with a CHECK constraint is defined, it can be reused across
multiple tables, improving consistency and reducing redundancy.
Specifying Constraints in SQL - Specifying Key and
Referential Integrity Constraints
• SQL provides specific clauses within the CREATE TABLE statement to
define key and referential integrity constraints, which are crucial for
maintaining the consistency and integrity of the data

• Primary Key Constraint

• The PRIMARY KEY clause is used to specify one or more attributes as the
primary key of a table.
• If the primary key consists of a single attribute, it can be specified directly
after the attribute definition
• Example: Dnumber INT PRIMARY KEY,

• For composite primary keys, the PRIMARY KEY clause is placed at the end
of the attribute list
• Example: PRIMARY KEY (Dnumber, Dlocation)
Specifying Constraints in SQL - Specifying Key and
Referential Integrity Constraints
• Unique Key Constraint
• The UNIQUE clause ensures that all values in a specified column (or set of
columns) are unique across the relation
• Example: Dname VARCHAR(15) UNIQUE,

• Referential Integrity Constraint

• The FOREIGN KEY clause establishes a relationship between tables by linking
a column (or set of columns) in one table to the primary key in another table
• Example: FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)
Specifying Constraints in SQL - Specifying Key and
Referential Integrity Constraints
• Handling Referential Integrity Violations
• SQL provides options to specify what should happen when a referenced row is
updated or deleted
• The default action is RESTRICT, which rejects the update or delete operation if it
would violate the referential integrity constraint

• Other Options Include

1. CASCADE: Automatically updates or deletes the referencing rows when the
referenced row is updated or deleted
2. SET NULL: Sets the foreign key in the referencing rows to NULL if the
referenced row is deleted
3. SET DEFAULT: Sets the foreign key to a default value if the referenced row is
deleted or updated
Specifying Constraints in SQL - Specifying Key and
Referential Integrity Constraints
• Example: FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn)
ON DELETE SET NULL ON UPDATE CASCADE

• These constraints help ensure that the database remains consistent by

enforcing rules about how data can be related between tables

• For instance, referential integrity constraints prevent orphaned records by

ensuring that a foreign key always references an existing record in the
related table
Specifying Constraints in SQL - Giving Names to
Constraints
• Constraints can be given specific names using the CONSTRAINT keyword

• The constraint name must be unique within the schema

• Naming constraints is useful for identifying and managing constraints,

especially if they need to be dropped or replaced later

• Naming constraints is optional, but it provides clarity and ease of

reference
Specifying Constraints in SQL - Specifying Constraints on
Tuples Using CHECK
• These constraints apply to individual rows and are checked during row
insertion or modification

• Example: Ensuring a manager's start date is later than the department

creation date:
• CHECK (Dept_create_date <= Mgr_start_date);
Basic Retrieval Queries in SQL

• SELECT Statement
• The primary SQL command for retrieving data from a database

• Offers various options for retrieving data

• SQL vs. Relational Model

• In SQL, tables can have identical tuples (multiset or "bag" of tuples)

• In the formal relational model, tables are sets with no duplicate tuples

• SQL tables can be constrained to sets using key constraints or the DISTINCT option in the
SELECT statement

• Example Queries
• Example queries will be based on the schema in the following figures
Figures used for Example Queries
Figures used for Example Queries
Figures used for Example Queries
Basic Retrieval Queries in SQL - The SELECT-FROM-
WHERE Structure of Basic SQL Queries
• Basic Structure of SQL Queries
• SELECT: Specifies the attributes to retrieve (projection)
• FROM: Lists the tables involved in the query
• WHERE: Provides the conditions that must be met for tuples to be included
in the result (selection)

• SQL Logical Operators

• SQL uses =, <, <=, >, >=, and <> for comparisons
• These correspond to relational algebra and programming language operators
Basic Retrieval Queries in SQL - The SELECT-FROM-
WHERE Structure of Basic SQL Queries
• Example Query 0: Retrieve the birth date and address of employees named
'John B. Smith'
SELECT Bdate, Address
FROM EMPLOYEE
WHERE Fname = ‘John’ AND Minit = ‘B’ AND Lname = ‘Smith’;
Basic Retrieval Queries in SQL - The SELECT-FROM-
WHERE Structure of Basic SQL Queries
• Example Query 1: Retrieve the name and address of employees in the
'Research' department
SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dname = ‘Research’ AND Dnumber = Dno;
Basic Retrieval Queries in SQL - The SELECT-FROM-
WHERE Structure of Basic SQL Queries
• Example Query 2: For projects in 'Stafford', retrieve project details and the
manager's info
• SELECT Pnumber, Dnum, Lname, Address, Bdate
• FROM PROJECT, DEPARTMENT, EMPLOYEE
• WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Plocation = ‘Stafford’
Basic Retrieval Queries in SQL - Ambiguous Attribute
Names, Aliasing, Renaming, and Tuple Variables

• Example Query 1A: Retrieve the name and address of employees in the
'Research' department
SELECT Fname, EMPLOYEE.Name, Address
FROM EMPLOYEE, DEPARTMENT
WHERE DEPARTMENT.Name = ‘Research’ AND DEPARTMENT.Dnumber =
EMPLOYEE.Dnumber;
Basic Retrieval Queries in SQL - Ambiguous Attribute
Names, Aliasing, Renaming, and Tuple Variables

• Example Query 1’: Retrieve the name and address of employees in the
'Research' department
SELECT EMPLOYEE.Fname, EMPLOYEE.LName, EMPLOYEE.Address
FROM EMPLOYEE, DEPARTMENT
WHERE DEPARTMENT.DName = ‘Research’ AND DEPARTMENT.Dnumber =
EMPLOYEE.Dno;
Basic Retrieval Queries in SQL - Ambiguous Attribute
Names, Aliasing, Renaming, and Tuple Variables

• Example Query 1B: Retrieve the name and address of employees in the
'Research' department
SELECT E.Fname, E.LName, E.Address
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE D.DName = ‘Research’ AND D.Dnumber = E.Dno;
Basic Retrieval Queries in SQL - Unspecified WHERE
Clause and Use of the Asterisk
• Example Query 9:
SELECT Ssn
FROM EMPLOYEE;

• Example Query 10:

SELECT Ssn, Dname
FROM EMPLOYEE, DEPARTMENT;
Basic Retrieval Queries in SQL - Unspecified WHERE
Clause and Use of the Asterisk
• Example Q1C:
SELECT *
FROM EMPLOYEE
WHERE Dno = 5;

• Example Q10A:
SELECT *
FROM EMPLOYEE, DEPARTMENT;
Basic Retrieval Queries in SQL - Tables as Sets in SQL

• SELECT is the same as SELECT

ALL
• Example Q11 (a):
SELECT ALL Salary
FROM EMPLOYEE;

• DISTINCT in the SELECT clause

eliminates duplicate tuples
• Example Q11A (b):
SELECT DISTINCT Salary
FROM EMPLOYEE;
Basic Retrieval Queries in SQL - Tables as Sets in SQL

• SQL also has union (UNION), set difference (EXCEPT), and set intersection
(INTERSECT) operations

• Works just the same as set theory from mathematics

Basic Retrieval Queries in SQL - Tables as Sets in SQL

• Example Query 4: Make a list of all project numbers for projects that involve
an employee whose last name is ‘Smith’, either as a worker or as a manager of
the department that controls the project
( SELECT DISTINCT Pnumber

FROM PROJECT, DEPARTMENT, EMPLOYEE

WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = ‘Smith’ )

UNION

( SELECT DISTINCT Pnumber

FROM PROJECT, WORKS_ON, EMPLOYEE

WHERE Pnumber = Pno AND Essn = Ssn AND Lname = ‘Smith’ );

Basic Retrieval Queries in SQL - Substring Pattern Matching
and Arithmetic Operators
• Example Query 12: Retrieve all employees whose address is in Houston, Texas.
SELECT Fname, Lname

FROM EMPLOYEE

WHERE Address LIKE ‘%Houston,TX%’;

• Example Query 14. Retrieve all employees in department 5 whose salary is between
$30,000 and $40,000.
SELECT *

FROM EMPLOYEE

WHERE (Salary BETWEEN 30000 AND 40000) AND Dno = 5;

((Salary >= 30000) AND (Salary <= 40000)) is another way of writing it
Basic Retrieval Queries in SQL - Ordering of Query Results
• Example Query 15: Retrieve a list of employees and the projects they are working on,
ordered by department and, within each department, ordered alphabetically by last
name, then first name
• SELECT D.Dname, E.Lname, E.Fname, P.Pname
• FROM DEPARTMENT AS D, EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
• WHERE D.Dnumber = E.Dno AND E.Ssn = W.Essn AND W.Pno = P.Pnumber
• ORDER BY D.Dname, E.Lname, E.Fname;

• The default order is in ascending order of values - the keyword ASC can be used to
specify ascending order explicitly

• We can specify the keyword DESC if we want to see the result in a descending order
of values
• Example, if we want descending alphabetical order on Dname and ascending order on Lname,
Fname, can be written as ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC
Basic Retrieval Queries in SQL - Summary of Basic SQL
Retrieval Queries

Query Skeleton
SELECT <attribute list>
FROM <table list>
[ WHERE <condition> ]
[ ORDER BY <attribute list> ];
INSERT, DELETE, and UPDATE Statements in SQL - The
INSERT Command
• Example U1
INSERT INTO EMPLOYEE

VALUES ( ‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-12-30’, ’98 Oak Forest, Katy, TX’,
‘M’, 37000, ‘653298653’, 4 );

• Example U1A
INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)

VALUES (‘Richard’, ‘Marini’, 4, ‘653298653’);

INSERT, DELETE, and UPDATE Statements in SQL - The
INSERT Command
• Example U2
INSERT INTO EMPLOYEE (Fname, Lname, Ssn, Dno)
VALUES (‘Robert’, ‘Hatcher’, ‘980760540’, 2);

• U2 is rejected if referential integrity checking is provided by DBMS

• Example U2A:
INSERT INTO EMPLOYEE (Fname, Lname, Dno)
VALUES (‘Robert’, ‘Hatcher’, 5);

• U2A is rejected if NOT NULL checking is provided by DBMS

INSERT, DELETE, and UPDATE Statements in SQL - The
DELETE Command
• Example U4A: Delete zero tuples • Example U4C: Delete four tuples
DELETE FROM EMPLOYEE DELETE FROM EMPLOYEE
WHERE Lname = ‘Brown’; WHERE Dno = 5;

• Example U4B: Delete one tuple • Example U4D : Delete all tuples
DELETE FROM EMPLOYEE DELETE FROM EMPLOYEE;
WHERE Ssn = ‘123456789’;
INSERT, DELETE, and UPDATE Statements in SQL - The
UPDATE Command
• Example U5
UPDATE PROJECT

SET Plocation = ‘Bellaire’, Dnum = 5

WHERE Pnumber = 10;

• Example U6
UPDATE EMPLOYEE

SET Salary = Salary * 1.1

WHERE Dno = 5;
Practical Understanding of SQL

• Below are some useful practical tutorials useful for practising concepts
learnt during the lectures and also for your lab projects
• Mandatory reading and you should all complete at least one of them

• W3Schools SQL Tutorial

• An SQL tutorial that will teach you how to use SQL that is used in various relational
database management systems (RDBMSs)
• https://www.w3schools.com/sql/default.asp

• W3Schools MySQL Tutorial

• Same as above but with a focus on MySQL - a free widely used RDBMS ideal for both
small and large applications
• https://www.w3schools.com/MySQL/default.asp