Module: CS1703
Lecture Topic: SQL (Part 1)
Week: 17
Structured Query Language (SQL)
-Main language for relational DBMs
Main characteristics:
-Relatively easy to learn
-Declarative, you specify what info you require rather than how to get it
(vs imperative/procedural languages like Java/Python.)
-Essentially free-format
-Consists of standard English words like SELECT, INSERT, and UPDATE
-Can be used by range of users.
Objectives of SQL:
Ideally database languages should let the user:
-create database and table structures
-perform basic tasks like adding (insert), amending (update), and removal (delete)
Must perform these tasks with minimal user effort
Must be portable-must conform to recognised standard (i.e. International standards-ISO)
SQL has two main components:
-DDL (Data definition Language): for defining DB structure and controlling access to data
-DML (Data Manipulation Language): for retrieving and updating data
Writing SQL Commands:
-SQL statement consists of reserved words and user-defined words
-Reserved words are a fixed part of SQL and must be spelled exactly as required and cannot
be split across lines (e.g SELECT, INSERT)
-User-defined words are made up by user and represent names of various database objects
such as tables, columns, views.
,Literals:
-Literals are constants used in SQL statements
-All non-numeric literals must be enclosed in single quotes (eg. ‘New York’)
-Numeric literals are not enclosed in quotes (eg. 650.00)
Example:
INSERT INTO BOOK (bTitle, bPRice)
VALUES(‘Hocus Pocus’, 5.99);
Example of a DB:
Underlines column names are primary keys, convention for representing a relational DB is
to give each table name followed by their column names in brackets
Table (Relation) Columns (Fields, Attributes)
DistributionCenter (dCenterNo, dStreet, dCity,
dState, dZipCode, mgrStaffNo)
Staff (staffNo, name, position, salary,
eMail, dCenterNo)
DVD (catalogNo, title, genre, rating)
Actor (actorNo, actorName)
DVDActor (actorNo, catalogNo, character)
:
SELECT: specifies which columns are to appear in output
FROM: specifies table(s) to be used
WHERE: filters rows subject to some conditions
GROUP BY: forms groups of rows with same column value
HAVING: filters groups subject to some condition.
ORDER BY: specifies the order of the output.
-Order of the clauses cannot be changed.
-Only SELECT & FROM are mandatory.
,“List the full details of all DVDS helds”
SELECT catalogNo, title, genre, rating
FROM DVD;
Or
SELECT *
FROM DVD;
Sometimes rows will have a null value.
“List rentals that have no return date specified”
SELECT staffNo, name, position, salary
FROM Staff
WHERE supervisorStaffNo is NULL;
Distinct keyword
-SELECT does not eliminate duplicate values by default
-Replace SELECT with SELECT DISTINCT
Calculated fields
-allows you to transform existing fields according to some arithmetic operation
SELECT staffNo, name, position, salary /12
FROM Staff;
Calculated fields: AS clause
-to give the column a meaningful name, we use the AS clause
SELECT staffNo, name, position, salary /12 AS monthlySalary
FROM Staff;
, Where clause
-Follows the FROM clause
-Only retrieve rows that satisfy some condition
-Five basic search conditions include:
Comparison (compares the value of one expression to the value of another)
range (test whether value falls within a specified range)
set membership (test whether the value of an expression equals one of a set of
values)
pattern match: test whether a string matches a specified pattern
Null: test whether a column has a unknown value
Common operators
= equals
<> is not equal to
> is greater than
< is less than
<= is less than or equal to
>= is greater than or equal to
Logical operators and brackets can be used to express more complex conditions
AND – both conditions must be satisfied
OR – one or both conditions must be satisfied
NOT – condition is false
Comparison search
“List all staff with a salary greater than $40,000”
SELECT staffNo, name, position, salary
FROM Staff
WHERE salary>40000
Range search condition
Lecture Topic: SQL (Part 1)
Week: 17
Structured Query Language (SQL)
-Main language for relational DBMs
Main characteristics:
-Relatively easy to learn
-Declarative, you specify what info you require rather than how to get it
(vs imperative/procedural languages like Java/Python.)
-Essentially free-format
-Consists of standard English words like SELECT, INSERT, and UPDATE
-Can be used by range of users.
Objectives of SQL:
Ideally database languages should let the user:
-create database and table structures
-perform basic tasks like adding (insert), amending (update), and removal (delete)
Must perform these tasks with minimal user effort
Must be portable-must conform to recognised standard (i.e. International standards-ISO)
SQL has two main components:
-DDL (Data definition Language): for defining DB structure and controlling access to data
-DML (Data Manipulation Language): for retrieving and updating data
Writing SQL Commands:
-SQL statement consists of reserved words and user-defined words
-Reserved words are a fixed part of SQL and must be spelled exactly as required and cannot
be split across lines (e.g SELECT, INSERT)
-User-defined words are made up by user and represent names of various database objects
such as tables, columns, views.
,Literals:
-Literals are constants used in SQL statements
-All non-numeric literals must be enclosed in single quotes (eg. ‘New York’)
-Numeric literals are not enclosed in quotes (eg. 650.00)
Example:
INSERT INTO BOOK (bTitle, bPRice)
VALUES(‘Hocus Pocus’, 5.99);
Example of a DB:
Underlines column names are primary keys, convention for representing a relational DB is
to give each table name followed by their column names in brackets
Table (Relation) Columns (Fields, Attributes)
DistributionCenter (dCenterNo, dStreet, dCity,
dState, dZipCode, mgrStaffNo)
Staff (staffNo, name, position, salary,
eMail, dCenterNo)
DVD (catalogNo, title, genre, rating)
Actor (actorNo, actorName)
DVDActor (actorNo, catalogNo, character)
:
SELECT: specifies which columns are to appear in output
FROM: specifies table(s) to be used
WHERE: filters rows subject to some conditions
GROUP BY: forms groups of rows with same column value
HAVING: filters groups subject to some condition.
ORDER BY: specifies the order of the output.
-Order of the clauses cannot be changed.
-Only SELECT & FROM are mandatory.
,“List the full details of all DVDS helds”
SELECT catalogNo, title, genre, rating
FROM DVD;
Or
SELECT *
FROM DVD;
Sometimes rows will have a null value.
“List rentals that have no return date specified”
SELECT staffNo, name, position, salary
FROM Staff
WHERE supervisorStaffNo is NULL;
Distinct keyword
-SELECT does not eliminate duplicate values by default
-Replace SELECT with SELECT DISTINCT
Calculated fields
-allows you to transform existing fields according to some arithmetic operation
SELECT staffNo, name, position, salary /12
FROM Staff;
Calculated fields: AS clause
-to give the column a meaningful name, we use the AS clause
SELECT staffNo, name, position, salary /12 AS monthlySalary
FROM Staff;
, Where clause
-Follows the FROM clause
-Only retrieve rows that satisfy some condition
-Five basic search conditions include:
Comparison (compares the value of one expression to the value of another)
range (test whether value falls within a specified range)
set membership (test whether the value of an expression equals one of a set of
values)
pattern match: test whether a string matches a specified pattern
Null: test whether a column has a unknown value
Common operators
= equals
<> is not equal to
> is greater than
< is less than
<= is less than or equal to
>= is greater than or equal to
Logical operators and brackets can be used to express more complex conditions
AND – both conditions must be satisfied
OR – one or both conditions must be satisfied
NOT – condition is false
Comparison search
“List all staff with a salary greater than $40,000”
SELECT staffNo, name, position, salary
FROM Staff
WHERE salary>40000
Range search condition
