→
→
→
→
→
→
→
→
→
→
→
→
→
→
→
→
→
student(sid,name,login,gpa) enrolled(sid,cid,grade)
sid name login age gpa sid cid grade
53666 Kanye kayne@cs 39 4.0 53666 15-445 C
53688 Bieber jbieber@cs 22 3.9 53688 15-721 A
53655 Tupac shakur@cs 26 3.5 53688 15-826 B
53655 15-445 B
course(cid,name) 53666 15-721 C
cid name
15-445 Database Systems
15-721 Advanced Database Systems
15-826 Data Mining
15-823 Advanced Topics in Databases
→ AVG(col)
→ MIN(col)
→ MAX(col)
→ SUM(col)
→ COUNT(col)
SELECT
SELECT COUNT(login) AS cnt
FROM student WHERE login LIKE '%@cs'
SELECT
SELECT COUNT(login) AS cnt
FROM student WHERE login LIKE '%@cs'
SELECT COUNT(*) AS cnt
FROM student WHERE login LIKE '%@cs'
SELECT
SELECT COUNT(login) AS cnt
FROM student WHERE login LIKE '%@cs'
SELECT COUNT(*) AS cnt
FROM student WHERE login LIKE '%@cs'
SELECT COUNT(1) AS cnt
FROM student WHERE login LIKE '%@cs'
AVG(gpa) COUNT(sid)
SELECT AVG(gpa), COUNT(sid) 3.25 12
FROM student WHERE login LIKE '%@cs'
COUNT SUM AVG DISTINCT
COUNT(DISTINCT login)
10
SELECT COUNT(DISTINCT login)
FROM student WHERE login LIKE '%@cs'
AVG([Link]) [Link]
SELECT AVG([Link]), [Link] 3.5 ???
FROM enrolled AS e, student AS s
WHERE [Link] = [Link]
SELECT AVG([Link]), [Link]
FROM enrolled AS e, student AS s
WHERE [Link] = [Link]
GROUP BY [Link]
[Link] [Link] [Link] [Link]
53435 53435 2.25 15-721
53439 53439 2.70 15-721
56023 56023 2.75 15-826
59439 59439 3.90 15-826
53961 53961 3.50 15-826
58345 58345 1.89 15-445
SELECT AVG([Link]), [Link]
FROM enrolled AS e, student AS s
WHERE [Link] = [Link]
GROUP BY [Link]
[Link] [Link] [Link] [Link]
53435 53435 2.25 15-721 AVG([Link]) [Link]
53439 53439 2.70 15-721 2.46 15-721
56023 56023 2.75 15-826 3.39 15-826
59439 59439 3.90 15-826 1.89 15-445
53961 53961 3.50 15-826
58345 58345 1.89 15-445
SELECT
GROUP BY
X
SELECT AVG([Link]), [Link], [Link]
FROM enrolled AS e, student AS s
WHERE [Link] = [Link]
GROUP BY [Link]
SELECT
GROUP BY
X
SELECT AVG([Link]), [Link], [Link]
FROM enrolled AS e, student AS s
WHERE [Link] = [Link]
GROUP BY [Link],
[Link] [Link]
WHERE GROUP BY
X
SELECT AVG([Link]) AS avg_gpa, [Link]
FROM enrolled AS e, student AS s
WHERE [Link] = [Link]
AND avg_gpa > 3.9
GROUP BY [Link]
WHERE GROUP BY
X
SELECT AVG([Link]) AS avg_gpa, [Link]
FROM enrolled AS e, student AS s
WHERE [Link] = [Link]
GROUP
AND BY
avg_gpa
[Link]> 3.9
GROUP BYavg_gpa
HAVING [Link] > 3.9;
AVG([Link]) [Link]
3.75 15-415 avg_gpa [Link]
3.950000 15-721 3.950000 15-721
3.900000 15-826
WHERE UPPER(name) = UPPER('KaNyE')
WHERE name = "KaNyE"
LIKE
SELECT * FROM enrolled AS e
→ "%" WHERE [Link] LIKE '15-%'
→ "_" SELECT * FROM student AS s
WHERE [Link] LIKE '%@c_'
→
SELECT SUBSTRING(name,0,5) AS abbrv_name
FROM student WHERE sid = 53688
SELECT * FROM student AS s
WHERE UPPER([Link]) LIKE 'KAN%'
||
SELECT name FROM student
WHERE login = LOWER(name) || '@cs'
SELECT name FROM student
WHERE login = LOWER(name) + '@cs'
SELECT name FROM student
WHERE login = CONCAT(LOWER(name), '@cs')
DATE TIME
→
→
SELECT DISTINCT cid INTO CourseIds
FROM enrolled;
CREATE TABLE CourseIds (
SELECT DISTINCT cid FROM enrolled);
→ SELECT
→
INSERT INTO CourseIds
(SELECT DISTINCT cid FROM enrolled);
ORDER BY <column*> [ASC|DESC]
→
sid grade
SELECT sid, grade FROM enrolled 53123 A
WHERE cid = '15-721' 53334 A
ORDER BY grade 53650 B
53666 D
sid
SELECT sid FROM enrolled 53666
WHERE cid = '15-721' 53650
ORDER BY grade DESC, sid ASC 53123
53334
LIMIT <count> [offset]
→
→
SELECT sid, name FROM student
WHERE login LIKE '%@cs'
LIMIT 10
SELECT sid, name FROM student
WHERE login LIKE '%@cs'
LIMIT 20 OFFSET 10
SELECT name FROM student WHERE
sid IN (SELECT sid FROM enrolled)
SELECT name FROM student
WHERE ...
SELECT name FROM student
WHERE ...
SELECT sid FROM enrolled
WHERE cid = '15-445'
SELECT name FROM student
WHERE ...
sid IN (
SELECT sid FROM enrolled
WHERE cid = '15-445'
)
ALL
ANY
IN =ANY()
EXISTS
SELECT name FROM student
WHERE sid = ANY(
SELECT sid FROM enrolled
WHERE cid = '15-445'
)
SELECT (SELECT
name [Link]
student
FROM student AS S
WHERE sid
WHERE
= ANY(
[Link] = [Link]) AS sname
FROM
SELECT
enrolled
sid FROM
AS Eenrolled
WHERE
WHERE
cidcid
= '15-445'
= '15-445'
)
X
SELECT MAX([Link]), [Link]
FROM enrolled AS e, student AS s
WHERE [Link] = [Link];
SELECT sid, name FROM student
WHERE ...
SELECT sid, name FROM student
WHERE sid
...
SELECT sid FROM enrolled
SELECT sid, name FROM student sid name
WHERE sid
... => ALL( 53688 Bieber
SELECT sid FROM enrolled
)
SELECT sid, name FROM student
WHERE ... =>
sid
SELECT ALL(
sid, name FROM student
SELECT
WHERE sid IN (enrolled
sid FROM
) SELECT MAX(sid) FROM enrolled
)
SELECT sid, name FROM student
WHERE ... =>
sid
SELECT ALL(
sid, name FROM student
SELECT
WHEREsid FROM
sid IN enrolled
(
) SELECT sid, name FROM student
SELECT
WHEREMAX(sid)
sid IN (FROM enrolled
) SELECT sid FROM enrolled
ORDER BY sid DESC LIMIT 1
)
SELECT * FROM course
WHERE ...
sid cid grade
cid name
53666 15-445 C
15-445 Database Systems 53688 15-721 A
15-721 Advanced Database Systems 53688 15-826 B
15-826 Data Mining 53655 15-445 B
15-823 Advanced Topics in Databases 53666 15-721 C
SELECT * FROM course
WHERE NOT
... EXISTS(
)
SELECT * FROM course
WHERE NOT
... EXISTS(
SELECT * FROM enrolled
) WHERE [Link] = [Link]
)
cid name
15-823 Advanced Topics in Databases
SELECT ... FUNC-NAME(...) OVER (...)
FROM tableName
→ sid cid grade row_num
53666 15-445 C 1
→ ROW_NUMBER() 53688 15-721 A 2
53688 15-826 B 3
53655 15-445 B 4
→ RANK() 53666 15-721 C 5
SELECT *, ROW_NUMBER() OVER () AS row_num
FROM enrolled
OVER
cid sid row_number
15-445 53666 1
15-445 53655 2
PARTITION BY 15-721 53688 1
15-721 53666 2
15-826 53688 1
SELECT cid, sid,
ROW_NUMBER() OVER (PARTITION BY cid)
FROM enrolled
ORDER BY cid
ORDER BY
SELECT *,
ROW_NUMBER() OVER (ORDER BY cid)
FROM enrolled
ORDER BY cid
SELECT * FROM (
SELECT *,
RANK() OVER (PARTITION BY cid
ORDER BY grade ASC)
AS rank
FROM enrolled) AS ranking
WHERE [Link] = 1
→
WITH cteName AS (
SELECT 1
)
SELECT * FROM cteName
AS
WITH cteName (col1, col2) AS (
SELECT 1, 2
)
SELECT col1 + col2 FROM cteName
WITH cteSource (maxId) AS (
SELECT MAX(sid) FROM enrolled
)
SELECT name FROM student, cteSource
WHERE [Link] = [Link]
WITH RECURSIVE cteSource (counter) AS (
(SELECT 1)
UNION ALL
(SELECT counter + 1 FROM cteSource
WHERE counter < 10)
)
SELECT * FROM cteSource