Skip to content
Notes
GitHub

Intermediate SQL

Relational Languages

用户只需要指出他们想要的答案，不需要指出是如何计算的，DBMS 负责高效的查询。

高端系统有一个复杂的查询优化器 query optimizer，可以重写查询并搜索最佳执行策略。

  • Data Manipulation Language
  • Data Definition Language
  • Data Control Language

Example Database

  • student (sid, name, login, age, gpa)
  • course (cid, name)
  • enrolled (sid, cid, grade)

Join

SELECT s.name
FROM
  enrolled AS e, student AS s
WHERE
  e.grade = 'A' AND e.cid = '15-721'
  AND e.sid = s.sid;
SELECT s.name
FROM
  enrolled AS e, student AS s
WHERE
  e.grade = 'A' AND e.cid = '15-721'
  AND e.sid = s.sid;

Aggregate

SELECT COUNT(login) AS cnt
FROM student
WHERE login LIKE '%@cs';
SELECT COUNT(login) AS cnt
FROM student
WHERE login LIKE '%@cs';
SELECT AVG(s.gpa), e.cid
FROM
  enrolled AS e, student AS s
WHERE e.sid = s.sid
GROUP BY e.cid;
SELECT AVG(s.gpa), e.cid
FROM
  enrolled AS e, student AS s
WHERE e.sid = s.sid
GROUP BY e.cid;

The GROUP BY will turn

e.sids.sidS.gpae.cid
53435534352.2515-721
53439534392.7015-721
56023560232.7515-826
59439594393.9015-826
53961539613.5015-826
58345583451.8915-445

into

AVG(s.gpa)e.cid
2.4615-721
3.3915-826
1.8915-445

String Operations

SELECT SUBSTRING(name, 1, 5) AS abbrv_name
FROM student WHERE sid = 53688;
SELECT SUBSTRING(name, 1, 5) AS abbrv_name
FROM student WHERE sid = 53688;
SELECT * FROM student AS s
WHERE UPPER(s.name) LIKE 'KAN%';
SELECT * FROM student AS s
WHERE UPPER(s.name) LIKE 'KAN%';

SQL standard says to use || operator to concatenate two or more strings together.

SELECT name FROM student
WHERE login = LOWER(name) || '@cs';
SELECT name FROM student
WHERE login = LOWER(name) || '@cs';

Date/Time

Postgre SQL
SELECT NOW();
Postgre SQL
SELECT NOW();
SQLite
SELECT CURRENT_TIMESTAMP;
SQLite
SELECT CURRENT_TIMESTAMP;

Output Redirection

INSERT INTO CourseIds
(SELECT DISTINCT cid FROM enrolled);
INSERT INTO CourseIds
(SELECT DISTINCT cid FROM enrolled);

Output Control

SELECT * FROM student
WHERE login LIKE '%@cs'
LIMIT 10 OFFSET 20;
SELECT * FROM student
WHERE login LIKE '%@cs'
LIMIT 10 OFFSET 20;