SQL入门

SELECT [DISTINCT] <select_list>
FROM <table_or_subquery> [JOIN ... ON ...]
WHERE <row_conditions>
GROUP BY <group_cols>
HAVING <group_conditions>
ORDER BY <sort_exprs>
LIMIT <n> [OFFSET <m>];

SELECT Name
FROM Professors

SELECT Level
FROM Professors

SELECT DISTINCT Level
FROM Professors

SELECT Name, Level
FROM Professors
ORDER BY Level ASC;

SELECT Name
FROM Professors
LIMIT 3

SELECT Name
FROM Professors
WHERE Level = 'C4'

SELECT Name AS Prof_name
FROM Professors

SELECT s.Name, a.LectureNr
FROM Students s, attend a
WHERE s.StudNr = a.StudNr AND
	  s.Name = 'Fichte'

SELECT COUNT(*)
FROM Professors

SELECT Level, COUNT(*)
FROM Professors
GROUP BY Level;

GROUP BY CUBE(a,b,c)

SELECT a, b, s
FROM cube_table
WHERE c IS NULL;

GROUP BY ROLLUP(a,b,c)

SELECT Level, COUNT(*)
FROM Professors
GROUP BY Level
HAVING COUNT(*) > 3;

SELECT Name
FROM Students
WHERE Semester < (SELECT AVG(Semester)
				  FROM Students)

SELECT Name
FROM Students, (SELECT AVG(Semester) AS avg_semester
				  FROM Students) avg
WHERE Semester < avg_semester

WITH 名字(可选列名列表) AS (
  一条查询
)
SELECT ...
FROM 名字;

WITH C4Prof AS (
  SELECT *
  FROM Professors
  WHERE Level = 'C4'
)
SELECT Name, Room
FROM C4Prof;

WITH Professors ( persnr , name , paygrade , room , salary , taxclass ) as
(
VALUES 	(2125 , ' Sokrates ' , ' C4 ' ,226 ,85000 ,1) ,
		(2126 , ' Russel ' , ' C4 ' ,232 ,80000 ,3) ,
		(2127 , ' Kopernikus ' , ' C3 ' ,310 ,65000 ,5) ,
		(2128 , ' Aristoteles ' , ' C4 ' ,250 ,85000 ,1) ,
		(2133 , ' Popper ' , ' C3 ' ,52 ,68000 ,1) ,
		(2134 , ' Augustinus ' , ' C3 ' ,309 ,55000 ,5) ,
		(2136 , ' Curie ' , ' C4 ' ,36 ,95000 ,3) ,
		(2137 , ' Kant ' , ' C4 ' ,7 ,98000 ,1)
)
SELECT persnr , name , paygrade , room , salary , taxclass
FROM Professors;

SELECT Name
FROM Students, (SELECT AVG(Semester) AS avg_semester
				  FROM Students) avg
WHERE Semester < avg_semester

WITH avg AS (SELECT AVG(Semester) AS avg_semester
			 FROM Students)
SELECT Name
FROM Students, avg
WHERE Semester < avg_semester

WITH examination ( MatrNr , CourseNr , PersNr , Grade ) as (
  SELECT * FROM pruefen
  UNION
  VALUES (29120 ,0 ,0 ,3.0) , (29555 ,0 ,0 ,2.0) ,
         (29555 ,0 ,0 ,1.3) , (29555 ,0 ,0 ,1.0)
) ,
grades ( Name , MatrNr , Semester , Grade ) as (
  SELECT s . name , s . matrnr , semester , avg ( Grade )
  FROM studenten s , examination p
  WHERE s . matrnr = p. matrnr
  GROUP BY s. name , s. matrnr , semester
)

CREATE TABLE Students (
  StudNr   INT,
  Name     VARCHAR(50),
  Semester INT
);

CREATE TABLE Professors (
  PersNr INT PRIMARY KEY,
  Name   VARCHAR(50) NOT NULL,
  Level  VARCHAR(2),
  Room   INT
);

CREATE TABLE C4_Professors AS
SELECT PersNr, Name, Room
FROM Professors
WHERE Level = 'C4';

CREATE TEMP TABLE tmp_top_students AS
SELECT StudNr, AVG(Grade) AS avg_grade
FROM test
GROUP BY StudNr;

<窗口函数>(...) OVER (
  PARTITION BY ...
  ORDER BY ...
  <frame>
)

SELECT s.name,
     l.Title,
     COUNT(*) OVER (PARTITION BY a.StudNr) AS total_attended_lectures
FROM Students s, Lectures l, attend a
WHERE s.StudNr = a.StudNr
  AND l.LectureNr = a.LectureNr;

SELECT s.name,
       l.Title,
       SUM(1) OVER (PARTITION BY a.StudNr) AS total_attended_lectures
FROM Students s, Lectures l, attend a
WHERE s.StudNr = a.StudNr
  AND l.LectureNr = a.LectureNr;

SELECT s.name, t.Grade, rank()OVER ( ORDER BY t.Grade ASC)
FROM Students s, test t
WHERE s.StudNr = t.StudNr

WITH RECURSIVE 名字(列...) AS (
  -- 1. 基础部分（base case / anchor）
  SELECT ...

  UNION [ALL]

  -- 2. 递归部分（recursive step）
  SELECT ...
  FROM 名字 ...   -- 这里引用自己
  ...
)
SELECT ...
FROM 名字;

WITH RECURSIVE nums(n) AS (
  SELECT 1
    
  UNION ALL
    
  SELECT n + 1
  FROM nums
  WHERE n < 5
)
SELECT * 
FROM nums;

SELECT Title
FROM Lectures
WHERE LectureNr IN (
    SELECT Predecessor
    FROM require r, Lectures l
    WHERE r.Successor = l.LectureNr
  	  AND  l.Title = 'Der Wiener Kreis'
)

select v1.Predecessor
from require v1,
     ...
     require vn_minus_1,
     require vn,
     require v
where v1.Predecessor = v2.Successor and
      ...
      vn_minus_1.Predecessor = vn.Successor and
      vn.Predecessor = v.LectureNr and
      v.Title = 'Der Wiener Kreis';

WITH RECURSIVE TransPrereq (Prereq, Successor) AS (
     -- 递归的基础查询
    SELECT Predecessor, Successor
    FROM require

    UNION ALL

    -- 递归部分 (Prereq -> ... -> Successor)
    SELECT t.Prereq, r.Successor
    FROM TransPrereq t, require r
    WHERE t.Successor = r.Predecessor
)
SELECT Title
FROM Lectures
WHERE LectureNr IN (
    SELECT Prereq
    FROM TransPrereq t, Lectures l
    WHERE t.Successor = l.LectureNr
  	  AND  l.Title = 'Der Wiener Kreis'
)

WITH recursive singleDirection (a ,b ) as (
  SELECT *
  FROM ( VALUES (1 ,2) , (2 ,4) , (1 ,3) , (3 ,4) , (3 ,5) , (2 ,5) ,
                (5 ,6) , (4 ,6) , (6 ,7) ) as graph
) ,
undirectedGraph as (
  SELECT *
  FROM singleDirection
  UNION ALL
  SELECT b , a
  FROM singleDirection
)

WITH recursive singleDirection (a ,b ) as (
  SELECT *
  FROM ( VALUES (1 ,2) , (2 ,4) , (1 ,3) , (3 ,4) , (3 ,5) , (2 ,5) ,
                (5 ,6) , (4 ,6) , (6 ,7) ) as graph
) ,
undirectedGraph as (
  SELECT *
  FROM singleDirection
  UNION ALL
  SELECT b , a
  FROM singleDirection
) ,
hull (a , b) as (
  SELECT * FROM undirectedGraph
  UNION
  SELECT fst .a , snd . b
  FROM hull fst , undirectedGraph snd
  WHERE fst . b = snd .a
)
SELECT *
FROM hull
WHERE a = 6
  and b = 1;

WITH recursive singleDirection (a , b) as (
  SELECT *
  FROM ( VALUES (1 ,2) , (2 ,4) , (1 ,3) , (3 ,4) , (3 ,5) , (2 ,5) ,
                (5 ,6) , (4 ,6) , (6 ,7) ) as graph
) ,
undirectedGraph (a , b) as (
  SELECT *
  FROM singleDirection
  UNION ALL
  SELECT b , a
  FROM singleDirection
) ,
hull (a , b , dist ) as (
  SELECT a , b , 1
  FROM undirectedGraph
  UNION ALL
  SELECT fst .a , snd .b , dist + 1
  FROM hull fst , undirectedGraph snd
  WHERE fst . b = snd .a
    and dist <= 4
)
SELECT min ( dist )
FROM hull
WHERE a = 6
  and b = 1;