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.
- Index to SQLite documentation
- MySQL Reference Manuals
SELECT returns a table which
will be printed to the console.
We are following
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' ;
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
JOIN you get the same result.
CROSS JOIN can be used to provide
hint to the query processor, there is no compelling reason
for using either
CROSS JOIN or
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
This operator only matches tuples with shared values on common
However, many many SQL programmers prefer to use the
USING keyword with
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
There is a special Python-like operator for testing for
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
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' ;
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,
LEFT OUTER JOIN and
OUTER JOIN are really identical
We refer you to Wikipedia for an explanation of this operator.
SQL also supports the union, intersection and difference operations of rational operator.
SELECT statements (before any
with the keywords
You should really only use these operators when the two tables have
the same attribute names.
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.
count— number of rows
The use of aggregate functions is not
disinct option can be useful with
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 ;
It is possible, and very useful, to use subqueries in SQL
Subqueries in the
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
IN. All of the usual comparison operators can be used
ANY and the reduntant
Subqueries in the
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
Finally subqueries can be used in the
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 ;