10 February: Starting with Middleware

The missing INSERT

INSERT INTO AllowedTime(Dept, Room, Time)
SELECT 'CSCI', 'RRO223', M.Time
FROM MeetingTime M ;

SQL on the board

What was this about?

SELECT C1.Dept, C2.Dept, C1.MaxE
FROM (SELECT F.inst, SUM(F.Enrollment*F.CH) as Fstudent
      FROM Course F
      WHERE F.CH > 0
      GROUP BY F.inst) AS Fload, Course FX
WHERE FX.inxt

This should be ok, but isn’t the same problem.

SELECT C.inst, SUM(C.Enrollment*C.CH) as CHG
FROM Course C
GROUP BY C.inst
ORDER BY CHG DESC ;

This looks ok, but the inner SELECT doesn’t add anything.

SELECT FCHG.inst, FCHG.CHG
FROM (SELECT C.inst, SUM(C.Enrollment*C.CH) as CHG
      FROM Course C
      GROUP BY C.inst) FCHG ;

This looks bad. FCHG is an instance, not a table.

SELECT FCHG.inst, FCHG.CHG
FROM (SELECT C.inst, SUM(C.Enrollment*C.CH) as CHG
      FROM Course C
      GROUP BY C.inst) FCHG
WHERE FCHG.CHG > 0.9 * (SELECT MAX(F.CGH) FROM FCHG F) ;

This will probably work.

SELECT FCHG.inst, FCHG.CHG
FROM (SELECT C.inst, SUM(C.Enrollment*C.CH) as CHG
      FROM Course C
      GROUP BY C.inst) FCHG,
     (SELECT MAX(SUM(C.Enrollment*C.CH)) as MaxCGH
      FROM Course C
      GROUP BY C.inst) MCHG
WHERE FCHG.CHG > 0.9 * MCHG.MaxCGH ;

Middleware

C SQLite3 examples

The C/C++ interface to SQLite is described in An Introduction to the SQLite C/C++ Interface; however, you might want to start with the quickstart documentation.

Python SQLite3 examples

These are documented in the description of the sqlite3 module. It follows Python’s standard database API.

Java JDBC

This example uses several of the standard Java JDBC classes where are documented in on-line tutorials of the JDBC. Several JDBC classes were used.

These programs were first tested using the sqlite-jdbc driver of Taro Saito. However because this library does not support the PreparedStatement, they were also tested using the MySQL JDBC driver.