Learn 10% SQL That Accounts for 90% Query

I just finished reading Data Crunching: Solve Everyday Problems Using Java, Python, and More , that I borrowed from the National Library. In Chapter 6, the author showed his readers "... meet the 10% of SQL that accounts for 90% of common use ...".

Here I am writing that 10% down in my blog so that next time I can refer to it and I am using SQLite database to illustrate all the steps. BTW, SQLite (SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine) is an extremely powerful database and definitely worth your time in learning it.

Here I am going to create the database table and populate it with data in my Cygwin environment:

$ uname -a
CYGWIN_NT-6.0 user-PC 1.5.25(0.156/4/2) 2008-06-12 19:34 i686 Cygwin

$ ls -l test.db
ls: cannot access test.db: No such file or directory

$ sqlite3.exe test.db
SQLite version 3.6.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE Person (
   ...> EmpId  INTEGER NOT NULL PRIMARY KEY,
   ...> FirstName TEXT NOT NULL,
   ...> LastName TEXT NOT NULL,
   ...> Rate  DECIMAL
   ...> );
sqlite> CREATE TABLE Assigned (
   ...> EmpId  INTEGER NOT NULL,
   ...> ProjId INTEGER,
   ...> StartDate DATE,
   ...> EndDate DATE
   ...> );
sqlite> CREATE TABLE Customer (
   ...> CustId INTEGER NOT NULL PRIMARY KEY,
   ...> ContactInfo TEXT
   ...> );
sqlite> CREATE TABLE Project (
   ...> ProjId INTEGER NOT NULL PRIMARY KEY,
   ...> ProjName TEXT,
   ...> CustId INTEGER,
   ...> StartDate DATE,
   ...> EndDate DATE
   ...> );
sqlite>
sqlite> INSERT INTO Person VALUES (3001,'Dave','Thomas',400);
sqlite> INSERT INTO Person VALUES (3002,'Andy','Hunt',400);
sqlite> INSERT INTO Person VALUES (4001,'Greg','Wilson',320);
sqlite> INSERT INTO Person VALUES (4002,'Grace','Hopper',500);
sqlite> INSERT INTO Person VALUES (4003,'Alan','Turing',500);
sqlite> INSERT INTO Person VALUES (4004,'Chunk','Babbage',125);
sqlite>
sqlite> INSERT INTO Project VALUES (904,'RubyMath',70043,'2004-05-01','2004-10-30');
sqlite> INSERT INTO Project VALUES (905,'DBBridge',70047,'2004-05-01','2004-10-30');
sqlite>
sqlite> INSERT INTO Customer VALUES (70043,'MegaCorp Inc.');
sqlite> INSERT INTO Customer VALUES (70047,"Deadlines 'R' Us");
sqlite> INSERT INTO Customer VALUES (70101,'UNiversity of Euphoria');
sqlite>
sqlite> INSERT INTO Assigned VALUES (3001,904,'2005-02-01','2005-02-28');
sqlite> INSERT INTO Assigned VALUES (3002,904,'2005-02-01','2005-03-15');
sqlite> INSERT INTO Assigned VALUES (4001,904,'2005-02-01','2005-03-21');
sqlite> INSERT INTO Assigned VALUES (4001,905,'2005-01-10','2005-02-22');
sqlite> INSERT INTO Assigned VALUES (4002,905,'2005-01-20','2005-04-01');
sqlite> INSERT INTO Assigned VALUES (4004,905,'2005-02-10','2005-03-31');
sqlite>
sqlite> .q

$ ls -l test.db
-rwxrwxrwx 1 user None 5120 Jan 27 20:18 test.db

Here are some of the SQL statements in doing join, nesting and negation. Basically they are trying to answer the following questions:

• Who is paying for the RubyMath project ?
• Get forenames and surnames of employees on RubyMath project
• Select people who are NOT assigned to the RubyMath project
• Select people who are assigned to exactly one project
• Find people in 904 or 905, but not both
• Find the most expensive contractors

$ sqlite3.exe test.db
SQLite version 3.6.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> -- Who is paying for the RubyMath project ?
sqlite> SELECT Customer.ContactInfo
   ...> FROM   Customer, Project
   ...> WHERE (Customer.CustId = Project.CustId)
   ...>   AND (Project.ProjName = 'RubyMath');
MegaCorp Inc.
sqlite>
sqlite>
sqlite> SELECT Customer.ContactInfo
   ...> FROM   Customer INNER JOIN Project
   ...> ON     Customer.CustId = Project.CustId
   ...> WHERE  Project.ProjName = 'RubyMath';
MegaCorp Inc.
sqlite>
sqlite>
sqlite> -- Get forenames and surnames of employees on RubyMath project
sqlite> SELECT Person.FirstName, Person.LastName
   ...> FROM   Person, Project, Assigned
   ...> WHERE (Person.EmpId = Assigned.EmpId)
   ...>   AND (Project.ProjName = 'RubyMath')
   ...>   AND (Assigned.ProjId = Project.ProjId);
Dave|Thomas
Andy|Hunt
Greg|Wilson
sqlite>
sqlite>
sqlite> -- Select people who are NOT assigned to the RubyMath project
sqlite> SELECT Person.FirstName, Person.LastName
   ...> FROM   Person
   ...> WHERE  Person.EmpId NOT IN
   ...>        (SELECT Assigned.EmpId
   ...>         FROM   Assigned, Project
   ...>         WHERE (Assigned.ProjId = Project.ProjId)
   ...>         AND   (Project.ProjName = 'RubyMath'));
Grace|Hopper
Alan|Turing
Chunk|Babbage
sqlite>
sqlite>
sqlite> -- Select people who are assigned to exactly one project
sqlite> SELECT Person.FirstName, Person.LastName
   ...> FROM   Person, Assigned
   ...> WHERE  (Person.EmpId = Assigned.EmpId)
   ...> AND    (Assigned.EmpId NOT IN
   ...>         (SELECT A.EmpId
   ...>          FROM   Assigned A, Assigned B
   ...>          WHERE (A.EmpId = B.EmpId)
   ...>          AND   (A.ProjId < B.ProjId)));
Dave|Thomas
Andy|Hunt
Grace|Hopper
Chunk|Babbage
sqlite>
sqlite>
sqlite> -- Find people in 904 or 905, but not both
sqlite> SELECT Person.FirstName, Person.LastName
   ...> FROM   Person, Assigned
   ...> WHERE  (Person.EmpId = Assigned.EmpId)
   ...> AND    ((Assigned.ProjId = 904) OR (Assigned.ProjId = 905))
   ...> AND    (Assigned.ProjId NOT IN
   ...>         (SELECT A.ProjId
   ...>          FROM Assigned A, Assigned B
   ...>          WHERE (A.ProjId = 904) AND (B.ProjId = 905)));
Greg|Wilson
Grace|Hopper
Chunk|Babbage
sqlite>
sqlite>
sqlite> -- Find the most expensive contractors
sqlite> SELECT Person.FirstName, Person.LastName
   ...> FROM Person
   ...> WHERE (Person.Rate NOT IN
   ...>        (SELECT A.Rate
   ...>         FROM Person A, Person B
   ...>         WHERE A.Rate < B.Rate));
Grace|Hopper
Alan|Turing
sqlite> .q

$

Here are the SQL statements in aggregation and views answering the following questions:

• Get the total rate for all consultants
• Find consultant who rate is above average

$ sqlite3.exe test.db
SQLite version 3.6.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> -- Get the total rate for all consultants
sqlite> SELECT Project.ProjName, SUM(Person.Rate)
   ...> FROM   Person, Assigned, Project
   ...> WHERE  (Person.EmpId = Assigned.EmpId)
   ...> AND    (Project.ProjId = Assigned.ProjId)
   ...> GROUP BY Assigned.ProjId;
RubyMath|1120
DBBridge|945
sqlite>
sqlite>
sqlite> -- view
sqlite> CREATE VIEW ProjAveRate AS
   ...>   SELECT Project.ProjId AS ProjId,
   ...>          AVG(Person.Rate) AS AveRate
   ...>   FROM   Person, Assigned, Project
   ...>   WHERE  (Person.EmpId = Assigned.EmpId)
   ...>   AND    (Project.ProjId = Assigned.ProjId)
   ...>   GROUP BY Assigned.ProjId;
sqlite>
sqlite>
sqlite> SELECT ProjAveRate.ProjId, Person.FirstName, Person.LastName, Person.Rate, ProjAveRate.AveRate
   ...> FROM   Person, Assigned, ProjAveRate
   ...> WHERE  (Person.EmpId = Assigned.EmpId)
   ...> AND    (Assigned.ProjId = ProjAveRate.ProjId)
   ...> AND    (Person.Rate > ProjAveRate.AveRate);
904|Dave|Thomas|400|373.333333333333
904|Andy|Hunt|400|373.333333333333
905|Greg|Wilson|320|315.0
905|Grace|Hopper|500|315.0
sqlite> .q

$

If you cannot get hold of the book, you may want to read the author's article - Top Ten Data Crunching Tips and Tricks.