Standards and implementation references
The official ISO standard for SQL is 9075. The first version is 9075:1999 and the latest is 9075:2011. The standard is published in about ten volumes. (Not all are republished with each revision.) They are available on-line and are very expensive.
Implementations of SQL can differ significantly from the ISO standard. It is a good idea to look at them. Also, these standards are free and largely readable.
Data manipulation
SELECT
The SELECT
returns a table which
will be printed to the console.
We are following
the SELECT
reference for sqlite3
in these examples.
Start with an uninteresting case.
SELECT 343 ;
Print a table
SELECT * FROM Course ;
Some single table statements
Do a Π (projection) operator.
SELECT cName, instructor FROM Course ;
It is possible to use a table alias to give a name to an instance of the table. The name is assigned to a row, not to a table.
SELECT C.cName, C.instructor FROM Course AS C ;
Do a ρ (renaming) operator. The attribute is renamed in the returned table as a column alias. This may not be noticeable in the printout of the table.
SELECT C.cName AS course, C.instructor AS teacher FROM Course AS C ;
Do a σ (selection) operator.
SELECT C.cName as course FROM Course AS C WHERE C.number >= 300 ;
Printing in order
You can control the order in which answers are printed.
SELECT C.dept, C.number FROM Course C ORDER BY C.number desc, C.dept ;
From bag to set
Usually you want to avoid duplicate duplicate printing of of tuples.
SELECT distinct C.dept FROM Course C WHERE C.instructor = 'Reiser' ;
Join operators
There are several join and join-like operators supported by sqlite3.
The Cartesian project
First there is the × (product) which usually generates a large number or tuples.
SELECT * FROM Course, Department ;
If you replace the comma in the above statement with
either CROSS JOIN
, INNER JOIN
or JOIN
you get the same result.
Although the CROSS JOIN
can be used to provide
an optimization
hint to the query processor, there is no compelling reason
for using either CROSS JOIN
or INNER JOIN
in CSCI 343.
The θ joint
There are two ways to do the θ join in SQL. This one is the more popular.
SELECT C.name, D.office FROM Course C, Department D WHERE C.number >= 300 and D.name <> 'CSCI' ;
But you can also use the following, which adds a new keyword for no particularly good reason.
SELECT C.name, D.office FROM Course C JOIN Department D ON C.number >= 300 WHERE D.name <> 'CSCI' ;
The natural join
SQL does have a ⋈ (natural join) operator called NATURAL JOIN
.
This operator only matches tuples with shared values on common
attributes.
However, many many SQL programmers prefer to use the USING
keyword with
the unadorned JOIN
operator.
In any case, here are three SQL statements for listing the ID’s of all people who are both
teaching and taking courses.
SELECT F.ID FROM Faculty F JOIN Student S USING(ID) ;
SELECT ID FROM Faculty NATURAL JOIN Student ;
SELECT F.ID FROM Faculty F, Student S WHERE F.ID = S.ID ;
Finally, look at a useful example where the same table is used twice.
SELECT C1.dept, C1.number, C2.dept, C2.number FROM Course C1, Course C2 WHERE C1.courseID = C2.courseID and C1.dept < C2.dept ;
Dealing with NULL values
Tuples in SQL can contain NULL
values.
There is a special Python-like operator for testing for NULL
values.
SELECT C.dept, C.number, C.section FROM Course C WHERE C.room is NULL ;
SQL also has a special way of dealing with NULL
values in
logical operators.
The following WHERE
can never hold when a room is unassinged.
SELECT C.dept, C.number, C.section FROM Course C WHERE C.room is NULL and C.dept = 'CSCI' ;
However, this WHERE
will hold for Computer Science courses.
SELECT C.dept, C.number, C.section FROM Course C WHERE C.room is NULL or C.dept = 'CSCI' ;
sqlite does not have a
RIGHT OUTER JOIN
operator,
so the LEFT OUTER JOIN
and
the OUTER JOIN
are really identical
We refer you to Wikipedia for an explanation of this operator.
Set operations
SQL also supports the union, intersection and difference operations of rational operator.
Just connect SELECT
statements (before any ORDER BY
)
with the keywords UNION
, INTERSECT
or EXCEPT
.
You should really only use these operators when the two tables have
the same attribute names.
Aggregation
There are a class of operators that do not closely fit the relational model but are very useful because they allow the grouping of similar values.
SELECT C.dept, count(*) FROM Course C GROUP BY C.dept ;
When you use aggregation, you must make sure the values you select are unique. The following query has a problem.
SELECT C.dept, C.room, count(*) FROM Course C GROUP BY C.dept ;
This would be better.
SELECT C.dept, C.room, count(*) FROM Course C GROUP BY C.dept, C.room ;
There are five aggregate function in SQL.
avg
— averagecount
— number of rowsmax
— maximunmin
— minimumsum
— sum
The use of aggregate functions is not
restricted to GROUP BY
.
Also the disinct
option can be useful with count
.
SELECT count(distinct C.dept) FROM Courses C;
Testing the group
There is one more option that allows a restriction to be placed on the group.
SELECT C.number, count(distinct *) FROM Courses C WHERE C.dept = 'CSCI' GROUP BY C.number HAVING count(distinct *) <> 0 ;
Subqueries
It is possible, and very useful, to use subqueries in SQL
SELECT
.
Subqueries in the WHERE
Suppose you wanted to list the courses with the largest enrollment. Here are some ways to do this.
SELECT C.dept, C.number FROM Course C WHERE C.enrollment IN (SELECT MAX(CX.enrollment) FROM Course CX) ;
SELECT C.dept, C.number FROM Course C WHERE C.enrollment >= ALL (SELECT CX.enrollment FROM Course CX) ;
SELECT C.dept, C.number FROM Course C WHERE not C.enrollment < ANY (SELECT CX.enrollment FROM Course CX) ;
SELECT C.dept, C.number FROM Course C WHERE not exists(SELECT * FROM Course CX WHERE CX.enrollment > C.enrollment) ;
Notice that many of these queries depend on some new logical operators that
are applied to relations, such as EXISTS
and
IN
. All of the usual comparison operators can be used
with ALL
, ANY
and the reduntant SOME
.
Subqueries in the FROM
Subqueies may also be used in the FROM
clause where
they become temporary tables.
SELECT C.dept, C.number FROM Course C, (SELECT MAX(CT.enrollment) as maxE FROM Course CT) as Cmax WHERE C.enrollment = Cmax.maxE ;
Subqueries in the SELECT
Finally subqueries can be used in the SELECT
clause itself but only if they return only one value.
It is hard to come up with a useful example for this.
SELECT (SELECT COUNT(*) FROM Course) ;
Updating the table
You can insert named rows.
INSERT INTO Courses VALUES ('CSCI', 343, .... ) ;
You can insert lots of rows.
I INSERT INTO AllowedTime(Dept, Room, Time) SELECT 'CSCI', 'RRO223', M.Time FROM MeetingTime M ;
You can update all rows matching a condition.
UPDATE Course SET EnrollmentLimit = EnrollmentLimit + 10 WHERE Department = 'CSCI' ;
You can delete rows matching a condition.
DELETE FROM Course WHERE EnrollmentLimit < 8 ;
You can delete every row.
DELETE FROM Course ;