Nov 1
aside
old homework
sqlsoup answers to this week's homework - and a bit of raw SQL answers -
and more practice questions for those who want 'em :
SELECT MAX(shipments) as shipments, name FROM (
SELECT COUNT(1) AS shipments, e.name AS name
FROM Employee AS e
INNER JOIN Shipment as s ON e.employee_id = s.manager_id
GROUP BY e.employee_id
);
Last time we talked about embedded SELECT, and had some discussion of whether or not SQL was a programming language.
Well:
sqlite> SELECT x*y FROM ((SELECT 2+3 as x), (SELECT 5+3 as y));
40
or if you like, an entire program :
--- The question is ...
--- Is SQL a programming language?
--- Well, can you translate this into Python code ?
DROP TABLE IF EXISTS Stuff;
CREATE TABLE Stuff (name TEXT PRIMARY KEY, value TEXT);
INSERT INTO Stuff VALUES('a', "");
INSERT INTO Stuff VALUES('b', "");
UPDATE Stuff SET value="test" WHERE name="a";
UPDATE Stuff SET value = (
SELECT (CASE WHEN x > 4 THEN "yes" ELSE "no" END)
FROM (SELECT z+y AS x FROM ((SELECT 9/3 AS y), (SELECT 4 AS z))))
WHERE name="b";
-- Returns collection ("test", "yes")
SELECT value FROM Stuff WHERE name IN ("a", "b");
NOW we're having fun.
Most of you had trouble with the RESTful assignment.
So let's discuss :
Is cs.marlboro.edu RESTful?
Here's a few other APIs :
Do any of them look RESTful ?
new homework
... is posted
more PHP + MySQL
Links
