DATABASE MANAGEMENT II

Dr. Bilkisu L. Muhammad-Bello

Week 4 Database Management II

1
 SQL Basics & Simple
Queries
Dr. Bilkisu Larai Muhammad-Bello

2
Week 4 Database Management II
 Access Control

Statement Description
GRANT Grants users access privileges
REVOKE Removes users access privileges

3
Week 4 Database Management II
 Data Definition Language

Statement Description
CREATE TABLE Adds a new table to the database
DROP TABLE Removes a table from the database
ALTER TABLE Changes the structure of the existing table
CREATE VIEW Adds a new view to the database
DROP VIEW Removes a view from the database
CREATE INDEX Builds an index for a column
DROP INDEX Removes the index for a column
CREATE SCHEMA Adds a new schema to the database
DROP SCHEMA Removes a schema from the database
CREATE DOMAIN Adds a new data value domain
ALTER DOMAIN Changes a domain definition
DROP DOMAIN Removes a domain from the database

4
Week 4 Database Management II
 Data Manipulation Language

Statement Description
INSERT Adds new rows of data or tuples to the database
DELETE Removes rows of data or tuples from the
database
UPDATE Modifies existing database data

5
Week 4 Database Management II
 Data Querying/Retrieval

Statement Description
SELECT Retrieves data from the database

6
Week 4 Database Management II
 Anatomy of a SQL statement

Every SQL statement begins with a keyword that describes

what the statement does.
This is called a verb.
Examples: CREATE, DELETE, INSERT, DROP, and ROLLBACK.
The SQL statement continues with one or more clauses
which may specify the data to be acted on by the statement
or provide more details about what the statement is
supposed to do.
Every clause also begins with a keyword such as FROM,
WHERE, HAVING, GROUPBY etc.
7
Week 4 Database Management II
 Anatomy of a SQL statement (cont.)

Some clauses are optional while others are required.

Also, the content and structure of the clauses vary.
While many clauses contain table or column names,
others may contain keywords, constants or
expressions.
The ANSI/ISO SQL standard specifies the SQL keywords
that are used as verbs and in statement clauses which
cannot be used to name database objects such as
tables and columns.
8
Week 4 Database Management II
 Table Names
Ideally, tables names should be short but descriptive e.g. STUDENTS,
CUSTOMERS, OFFICES etc.
The choice of table names is usually up to the database designer or
developer in departmental or personal databases.
However, in large corporate databases, there may be standards
specified for naming tables in order to avoid conflicts in table names.
A qualified table name specifies both the name of the table’s owner
and the name of the table separated by a period (.).
For example, Information Technology department could access the
STUDENTS table owned by SEN by using the qualified table name
SEN.STUDENTS

10
Week 4 Database Management II
 Column Names

When a column name is specified in a SQL statement, SQL can

normally determine from the context which column is intended.
However, if the statement involves two columns with the same
name from two different tables, a qualified column name must
be used to unambiguously identify the column intended.
For example, a column named CITY in the OFFICES table has the
qualified column name OFFICES.CITY

11
Week 4 Database Management II
 Data Types
Integers: INT (len), INTEGER (len)
Decimal numbers: NUMERIC (precision, scale), DECIMAL (precision,
scale), DEC (precision, scale)
Floating Point numbers: FLOAT (precision)
Fixed-length character strings: CHAR (len), CHARACTER (len)
Variable-length character strings: VARCHAR (len), CHAR VARYING (len)
Money amounts: MONEY, CURRENCY
Dates and times: DATE, TIME (precision), TIMESTAMP (precision)
Boolean data
Long text
Unstructured byte streams
12
Week 4 Database Management II
 Constants (String, Date and Time, and Symbolic)
Character data are enclosed in single quotes (‘….’) as specified by
the ANSI/ISO standard, e.g. ‘Minna’ ‘Micheal, Mary J.’ etc.
Constant data values for dates, times, and time intervals are also
specified as string constants in SQL products that support date/time
data. However, the format of these constants varies from one DBMS
to the next.
The SQL language also includes special symbolic constants that
return data values maintained in the DBMS. For example, the
symbolic constant CURRENT_DATE returns the value of the
current date and can be used in queries.
Constant date values are also used in expressions.
14
Week 4 Database Management II
 Expressions

Expressions are used in the SQL language to calculate values that

are retrieved from a database or values used to search the
database.
For example,
SELECT City
FROM OFFICES
WHERE Target > (1.1*Sales) + 10000.00
Four arithmetic operators can be used in expressions: addition(+),
subtraction(-), multiplication(*), and division(/).
Parentheses are also used to form more complicated expression
15
Week 4 Database Management II
 Built-In Functions

Built-in functions provide data type conversion facilities

in most SQL implementations.
For example, the built-in functions MONTH( ) and
YEAR( ) take a DATE or TIMESTAMP value as INPUT
and return an integer that is the month or year portion
on the value.
Built-in functions are also used for reformatting data.

16
Week 4 Database Management II
 Simple Queries

Simple queries are queries that retrieve data from a single table
in the database.
The SELECT statement is used to express SQL queries which could
be simple or complex.

18
Week 4 Database Management II
 The SELECT Statement

The SELECT statement is the most powerful and complex of the

SQL statements.
There are many options afforded by the SELECT statement and
it’s possible to start simple and then work up to more complex
queries.
The SELECT statement retrieves data from the database and
returns it in form of a query result.
The keywords SELECT, FROM and WHERE make up the basic
SELECT statement.
The full form of the SELECT statement consists of six clauses

19
Week 4 Database Management II
 The full form SELECT Statement

SELECT [ALL|DISTINCT] select-item (column-list,

constant, expression)
FROM table-specification (source tables)
WHERE search –condition (desired rows)
GROUP BY grouping-column (summary query)
HAVING search-condition (desired groups)
ORDER BY sort-specification

20
Week 4 Database Management II
 DRIVER

NAME DOB

Jim Smith 11 Jan 1980

Bob Smith 23 Mar 1981

Bob Jones 3 Dec 1986

22
Week 4 Database Management II
 CAR

REGNO MAKE COLOUR PRICE OWNER

F611 AAA FORD RED 12000 Jim Smith

J111 BBB SKODA BLUE 11000 Jim Smith

A155 BDE MERCEDES BLUE 22000 Bob Smith

K555 GHT FIAT GREEN 6000 Bob Jones

SC04 BFE SMART BLUE 13000

23
Week 4 Database Management II
 select-item

Column-list, constant, expression

Calculated Columns
Selecting all columns (SELECT *)

24
Week 4 Database Management II
 Simple SELECT cont.

SELECT regno from CAR;

REGNO
F611 AAA
J111 BBB
A155 BDE
K555 GHT
SC04 BFE

26
Week 4 Database Management II
 Simple SELECT cont.

SELECT colour,owner from CAR;

COLOUR OWNER
RED Jim Smith
BLUE Jim Smith
BLUE Bob Smith
GREEN Bob Jones
BLUE

27
Week 4 Database Management II
 Duplicate Rows (DISTINCT)

If a query includes the primary key of a table in the select list,

then every row of the query result will be unique.
However, if the primary key is not included, duplicate rows can
occur.
Duplicate rows of query results can be eliminated by inserting
the keyword DISTINCT in the SELECT statement.
The keyword ALL can also be specified to explicitly indicate
that duplicate rows are to be included in the query result.

28
Week 4 Database Management II
 Duplicate Rows
Find all the colours used in cars.

SELECT colour from car;

COLOUR
RED
BLUE
BLUE
GREEN
BLUE

29
Week 4 Database Management II
 DISTINCT Example

SELECT DISTINCT colour from car;

COLOUR
RED
BLUE
GREEN

30
Week 4 Database Management II
 Formatting
SPACES do not matter
NEWLINES do not matter
Good practice to put ; at the end of the query.
CASE (except between single quotes) does not matter.
These are all valid:

SELECT REGNO FROM CAR;

SElecT regno
From Car
;

31
Week 4 Database Management II
 Comments
To give you the ability to make notes in queries you are allowed to
have comments.
Comments are not executed
A comment starts with -- and ends with a newline
They are only permitted within a query.

SELECT regno -- The registration number

FROM car -- The car storage table
;

32
Week 4 Database Management II
 Row Selection (WHERE Clauses)

The WHERE clause is used to specify the rows you want to

retrieve in a table.
The WHERE clause consists of the keyword WHERE, followed by
the search condition that specifies the rows to be retrieved.

33
Week 4 Database Management II
 Simple Rule

A simple rule might be to look for a car with a colour of RED.

The rule would be colour = 'RED'

SELECT regno FROM CAR SELECT regno from CAR

WHERE colour = 'RED'
REGNO
REGNO
F611 AAA
F611 AAA
J111 BBB
A155 BDE
K555 GHT
SC04 BFE

35
Week 4 Database Management II
 Note
Things between quotes is CASE SENSITIVE.
‘RED’ is not the same as ‘Red’ or ‘red’

Rules which mention fields – they can be used if they appear on

the SELECT line or not.

REGNO COLOUR
SELECT regno from CAR
F611 AAA RED
WHERE colour = 'RED'

36
Week 4 Database Management II
 Search Conditions

SQL offers a rich set of search conditions that allows the

specification of different kinds of queries efficiently and naturally.
There are five basic search conditions (called predicates):
The Comparison Test (=, <>, <, <=, >, >=)
The Range Test (BETWEEN)
The Set Membership Test (IN)
The Pattern Matching Test (LIKE)
The NULL Value Test (IS NULL)

37
Week 4 Database Management II
 The Comparison Test (=, <>, <, <=, >, >=)

Most common search condition used in a SQL query.

SQL computes and compares the value of one expression to the
value of another expression for each row of data.
The expressions can be a column name, a constant, or a complex
arithmetic expression.
When SQL compares the values of the two expressions in the
comparison test, three results can occur:
If the result of the comparison is true, the test yields a TRUE result
If the result of the comparison is false, the test yields a FALSE result.
If either of the two expressions produces a NULL value, the comparison
yields a NULL result.
38
Week 4 Database Management II
 Comparisons (Examples)
Valid comparisons include =,!=,<>,<,<=,>,>=
Colour = ‘RED’ The colour must be red
Colour != ‘RED’ The colour is not red
Colour <> ‘Red’ Same as !=
Price > 10000 More than 10000
Price >= 10000 More than or equal to 10000
Price < 10000 Cheaper than 10000
Price <=10000 Cheaper or the same as 10000
Numbers – You can say ‘10000’ or 10000. Strings always have quotes…

39
Week 4 Database Management II
 DATE Comparison
Date comparisons can be tricky
You can use all the normal comparators with dates.

SELECT name,dob SELECT name,dob from driver

from driver where DOB = ‘3 Jan 1986’

NAME DOB NAME DOB

Jim Smith 11 Jan 1980
Bob Jones 3 Jan 1986
Bob Smith 23 Mar 1981
Bob Jones 3 Dec 1986

40
Week 4 Database Management II
 DATE Comparison cont.
The tricky part with dates is remembering that dates get bigger as
you move into the future.
DATE1>DATE2 indicates DATE1 is in the future after DATE2.

SELECT name,dob from driver

WHERE DOB >= ‘1 Jan 1981’

NAME DOB
Bob Smith 23 Mar 1981
Bob Jones 3 Dec 1986

41
Week 4 Database Management II
 DATE Syntax

It must be in quotes
Each DBMS handles dates in a slightly different way
Dates like ‘1 Jan 2003’ does not work quite well with some
implementations.
Oracle permits dates like ‘1-Jan-2003’ and ‘1-Jan-03’
MySQL permits dates like '1980-01-11’
You must always specify a day and a month. If you do not the
DBMS will report an error.

42
Week 4 Database Management II
 The Range Test (BETWEEN)

Checks whether a data value lies between two specified values.

It involves three SQL expressions. The first expression defines the
value to be tested; the second and third defines the low and
high ends of the range to be checked.
The data types of the three expressions must be comparable.
The negated version of the range test (NOT BETWEEN) checks
for values that fall outside the range.
The range test syntax is as follows:
test-expression [NOT] BETWEEN low-expression AND high-
expression

43
Week 4 Database Management II
 The Range Test Example
Find all drivers born between 1995 and 1999
SELECT name,dob from driver
WHERE DOB between ‘1 Jan 1985’ and ’31 Dec 1999’

Between works for other things, not just dates…

SELECT regno from CAR
where price between 5000 and 10000;

44
Week 4 Database Management II
 The Set Membership Test (IN)

Checks whether a data value matches one of a list of

target values.
The negated version of the set membership test (NOT
IN), is used to check whether the data value does not
match any of the target values.
The set membership test syntax is as follows:
test-expression [NOT] IN (constants)

45
Week 4 Database Management II
 IN - Example
When you have a list of OR, all on the same attribute, then IN
could be a simpler way:

Rather Than:
SELECT regno,make FROM car
WHERE make = ‘SKODA’ or make = ‘SMART’

Have
SELECT regno,make FROM car
WHERE make in (‘SKODA’,’SMART’);

46
Week 4 Database Management II
 The Pattern Matching Test (LIKE)
Checks to see whether the data value in a column matches a
specified pattern.
The pattern is a string that could include one or more wildcard
characters including the percent sign (%), the underscore (_).
The percent sign (%) wildcard matches any sequence of zero or
more characters while the underscore (_) wildcard matches a single
character.
Strings that do not match a pattern can also be located using the
(NOT LIKE) form of the pattern matching test. The syntax is as
follows:
column-name [NOT] LIKE pattern
47
Week 4 Database Management II
 Pattern Matching Test Examples
Name LIKE ‘Jim Smith’ e.g. Jim Smith
Name LIKE ‘_im Smith’ e.g. Tim Smith
Name LIKE ‘___ Smith’ e.g. Bob Smith
Name LIKE ‘% Smith’ e.g. Frank Smith
Name LIKE ‘% S%’ e.g. Brian Smart
Name LIKE ‘Bob %’ e.g. Bob Martin
Name LIKE ‘%’ i.e. match anyone

LIKE is more expensive than =

If you are not using wildcards, always use = rather than LIKE.

48
Week 4 Database Management II
 The NULL Value Test (IS NULL)
NULL indicates that something has no value
It is not a value, and you cannot use normal comparison operators.
Wrong: SELECT regno from car where owner = NULL
Wrong: SELECT regno from car where owner = ‘NULL’

Used to explicitly check for NULL values in a search condition.

The negated form of the NULL value test (IS NOT NULL) is used
to find rows that do not contain a NULL value.
The NULL value test syntax is as follows:
column-name IS[NOT]NULL

49
Week 4 Database Management II
 Null Test Example

SELECT regno from car REGNO

SC04 BFE
WHERE OWNER is null

REGNO
SELECT regno from car F611 AAA
J111 BBB
WHERE OWNER is not null
A155 BDE
K555 GHT
SC04 BFE

50
Week 4 Database Management II
 Query Results

The result of a SQL query is always a table of data, just like the
tables in the database.
The number of columns in the query result corresponds to the
number of items in the SELECT clause, while the number of rows
depends on the number of rows that meet the search criteria
(WHERE clause).
Query results can be a single value in some cases. These results
are still a table consisting of one column and one row.
It is also possible for a query to produce zero rows of query
results. The query result is still a table although an empty table.

51
Week 4 Database Management II
 Query Results (NULL)

SQL support for missing data also extends to query results.

For example, if the data item in the database has a NULL value,
the NULL value also appears in the query result.

The fact that SQL query always produce a table of data is

very important as it suffices that query results can be
stored back into the database as a table and can be the
target of further queries.

52
Week 4 Database Management II
 Logical Operators

Combining rules in a single WHERE clause would be useful

AND and OR allow us to do this
NOT also allows us to modify rule behaviour

When these are combined together, problems in rule ordering can

occur.
This is solved using parentheses.

53
Week 4 Database Management II
 AND, OR and NOT Operators

The AND and OR operators are used to filter records based on

more than one condition:
The AND operator displays a record if all the conditions separated
by AND are TRUE.
The OR operator displays a record if any of the conditions
separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT
TRUE.

54
Week 4 Database Management II
 Multiple AND rules - Example

You can have as many rules as you like “ANDed” together.

For example:

SELECT regno
FROM car
WHERE colour = ‘BLUE’
AND regno like ‘%5%’
AND owner like ‘Bob %’
;

55
Week 4 Database Management II
 OR - Example
OR is like ‘either’. So long as one of the rules is true then the filter is
true.
Looks for cars which are EITHER red or blue…

SELECT regno,colour from CAR

WHERE colour = ‘RED’ OR colour = ‘BLUE’

REGNO COLOUR
F611 AAA RED
J111 BBB BLUE
A155 BDE BLUE
SC04 BFE BLUE
56
Week 4 Database Management II
 NOT

NOT inverts the rule it is put in front of:

WHERE colour = ‘RED’
This could be inverted as:
WHERE colour != ‘RED’
WHERE NOT colour = ‘RED’

NOT is not really useful in this example, but comes into

its own in more complex rulesets.

57
Week 4 Database Management II
 Precedence
Precedence is the order in which the rules are evaluated and
combined together.
It is NOT in the order they are written.
Rules are combined together firstly at AND, then OR, and finally at
NOT.
Consider : Find a car that has a 5 in reg and is either red or blue.

SELECT regno,colour from car

WHERE colour = ‘RED’ -- Line 1
OR colour = ‘BLUE’ -- Line 2
AND regno LIKE ‘%5%’ -- Line 3
58
Week 4 Database Management II
 Brackets
Rewrite as:
SELECT regno,colour from car
WHERE (colour = ‘RED’
OR colour = ‘BLUE’ )
AND regno LIKE ‘%5%’

Might be clearer as:

SELECT regno,colour from car
WHERE ( colour = ‘RED’ OR colour = ‘BLUE’ )
AND regno LIKE ‘%5%’

59
Week 4 Database Management II
 Sorting Query Results (ORDER BY Clause)

Used in the SELECT statement to sort the results of a query.

Sorting can be requested in an ascending or descending sequence
based on any item in the select list.
SQL sorts data in ascending sequence by default. To request
sorting in descending sequence, the keyword DESC is used.
If the column of the query result to be used for sorting is a
calculated column, a column number is used instead of a column
name. The syntax is as follows:
ORDER BY column-name{column-number} ASC{DESC}

60
Week 4 Database Management II
 ASCending order
Sort by alphabetical or numeric order: ASC
ORDER BY … ASC is the default.

SELECT make from car

ORDER BY make;
MAKE
FORD
FIAT
MERCEDES
SKODA
SMART
62
Week 4 Database Management II
 DESCending order
Sort by reverse alphabetical or numeric order: DESC
ORDER BY … DESC must be selected.

SELECT make from car

ORDER BY make DESC;

MAKE
SMART
SKODA
MERCEDES
FIAT
FORD
63
Week 4 Database Management II
 Multi Column Sort
ORDER BY can take multiple columns.

SELECT make, colour FROM car

ORDER BY colour, make;

MAKE COLOUR
SKODA BLUE
SMART BLUE
MERCEDES BLUE
FIAT GREEN
FORD RED
64
Week 4 Database Management II
 Rules for Single-Table Query Processing

Single-table queries are simple and usually easy to understand by

just reading the SELECT statement.
The following rules describe the procedure for generating the
results of a SQL query that includes the clauses described in the
SELECT statement.

65
Week 4 Database Management II
 Rules for Single-Table Query Processing
Start with the table named in the FROM clause
If there is a WHERE clause, apply its search condition to each row of the
table, retaining only those rows for which the search condition is TRUE,
and discarding those rows for which the search condition is FALSE or
NULL.
For each remaining row, calculate the value of each item in the select list
to produce a single row of query results. For each column reference, use
the value of the column in the current row.
If SELECT DISTINCT is specified, eliminate any duplicate rows of the
query results that were produced.
If there is an ORDER BY clause, sort the query as specified.
The rows generated by these steps comprise the query results.
66
Week 4 Database Management II
 SELECT - Order of Evaluation
SELECT [DISTINCT] column_name 5,6 eliminate unwanted data
FROM label_list 1 Cartesian Product
[WHERE condition ] 2 eliminate unwanted rows
[GROUP BY column_list 3 group rows
[HAVING condition ]] 4 eliminate unwanted groups
[ORDER BY column_list[DESC]] 7 sort rows

The last four components are optional.

67
Week 4 Database Management II