CSCI 343 SQL notes

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.

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 ;

For the class...

GUI’s for sqlite3