SQL
By: Toan Nguyen
Download
• Download the software at
http://dev.mysql.com/downloads/mysql/4.0.html
• During the installation
– Skip sign up for fast installation
– It will run Configuration Wizard right after installation
automatically
– If the service won’t start, press cancel, then run Configuration
Wizard manually again
- Use default setting unless you know what you are
doing
- Modify Security Setting this is option.
Basic Structure of SQL
• Select
List the attributes that you want to used in the
result of the query.
• From
Where you look for the relations to be
evaluation.
• Where
Consist of predicate involving attributes of the
relations that appear in the from clause
Projection in SQL
To eliminate some of the components of the
chosen tuples , we can project the relation
produced by an SQL query onto some of
its attributes. In place of the * of the
SELECT clause, we may list some of the
attributes of the relation mentioned in the
FROM clause.
Example
Movie (title, year, length, inColor, studioName,
producerC#)
• Select title, length
From Movie
Where studioName = ‘Disney’ AND year = 1990
title length
Selection in SQL
• The selection operator of relational
algebra, and much more, is available
through the WHERE clause of SQL. We
may build expressions by comparing
values using the six common comparison
operators: =, , , =
Example
• SELECT title
FROM Movie
WHERE (year>1970 OR length
• Uses keywords DESC for descending and ASC for
ascending.
Example
SELECT *
FROM Movie
WHERE studioName = ‘Disney’ AND year =
1990
ORDER BY length, title;
Subqueries
• In SQL, a query that is part of another
query is call subquery. Subqueries can
have subqueries, and so on, down as
many level as we wish. There are a
number of other ways that subqueries can
be used:
• Subqueries can return a single constant,
and this constant can be compared with
another value in a WHERE clause
• Subqueries can return relations that can
be used in various ways in WHERE
clauses.
• Subqueries can have their relations
appear in FROM clauses, just like any
stored relation can.
Example
• SELECT name
• FROM MovieExec
• WHERE cert# =
• (SELECT producerC#
• FROM Movie
• WHER title = ‘Star Wars’
• );
Unions, Intersections, and
Differences
• Uses unions to find the two relations that
are not the same between the two queries.
• Uses intersections to find the two relations
that are the same in both queries.
• Uses differences to find the relation in one
queries but not in the other.
Example
• Unions = A U B
• Intersections = A חB
=C
• Differences = A - B
A C B
Example
• Unions
SELECT title, year FROM Movie
UNION ALL
SELECT movieTitle AS title, movieYear
AS year FROM StarsIn;
• Intersections
INTERSECT ALL
• Differences
EXCEPT ALL
Aggregate Functions
• These functions take a collection of
values as input and return a single value.
SQL uses five aggregation operators:
1) Average: avg
2) Minimum: min
3) Maximum: max
4) Total: sum
5) Count: count
Null Values
• Null value is uses to indicate absence
information about the value of an attribute.
• Null value is uses to represent the value
that are unknown, value that are
inapplicable, and value that are withheld.
References
• Course Textbook
a First Course in Database System
(Jeffrey D. Ullman and Jenifer Widom)