SELECT [DISTINCT] targetlist --目标属性列表（distinct是可缺省选项，如果存在结果会消除重复元组）
FROM relation-list -- from子句 查询所涉及的表
WHERE qualification  -- 查询结果应该满足的条件（布尔表达式）

SELECT S.sname --查找姓名
FROM Sailors S,Reserves R  --定义别名
WHERE S.aid=R.sid AND R.bid=103 --预定103号船的信息（链接了水手信息和船的信息）

 SELECT S.sname
 FROM Sailors S
 WHERE S.sid IN (SELECT R.sid
                 FROM Reserves R
                 WHERE R.bid=103) --将子查询的结果作为父查询的条件

 SELECT S.sname
 FROM Sailors S
 WHERE EXISTS (SELECT* --exists：存在
               FROM Reserves R
               WHERE R.bid-103 AND  S.sid -R.sid)
               --子查询的S.sid就是外面查询的水手的sid

SELECT S.sid
FROM Sailors S,Reserves R
WHERE S.sid=R.sid --查找订过船的水手

SELECT S.age,age1=S.age-5,2*S.age AS age2  --对结果属性起别名 age1是结果-5 age2是2倍的age
FROM Ssilor S
WHERE S.sname LIKE 'B_%B'

SELECT S.sid
FROM Sailors S,Boats B1,Reserves R1,Boats B2,Reserves R2
WHERE S.sid=R1.sid AND R1.bid=B1.bid AND S.sid=R2.sid AND R2.bid=B2.bid
      AND(B1.color='red' AND B2.color='green'）
      /*订船信息中 同一个人定了红船的绿船的信息分别出现一次，这样就能找到
      但是这种方式会产生很多无用的排列组合，很麻烦*/

SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid-R.sid AND R.bid=B.bid
      AND B.color='red'
INTERSECT --表示集合的交
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
      AND B.color='green'
      /*用集合的交集来选择*/

 SELECT bid
 FROM Reserves R1
 WHERE bid NOT IN (
                   SELECT bid
                   FROM Reserves R2
                   WHERE R2.sid!=R1.sid)

 SELECT S.sname
 FROM Sailors S
 WHERE NOT EXISTS
           ((SELECT B.bid FROM Boats B)
           EXCEPT
           (SELECT R.bid FROM Reserves R
           WHERE R.sid=S.sid))--否定之否定的思想
           /*寻找一个水手不存在*/

 SELECT COUNT (*)
 FROM Sailors S --求s有多少个元组（有多少水手）

 SELECT COUNT (DISTINCT S.rating)
 FROM Sailors S
 WHERE S.sname='Bob'--求所有叫Bob的水手不同的级别有多少

 SELECT AVG (S.age)
 FROM Sailors S
 WHERE S.rating=10 --查找级别为10的水手的平均年龄

 SELECT AVG (DISTINCT S.age)
 FROM Sailors S
 WHERE S.rating-10 --查找级别为10的不同年龄的水手的平均年龄

 SELECT S.sname
 FROM SailorsS
 WHERE S.rating= (SELECT MAX(S2.rating)
                  FROM Sailors S2) --水手表中级别最大值的人的姓名

 SELECT DISTINCT] target-list
 FROM      relation-list
 WHERE     qualification
 GROUP BY  grouping-list
 HAVING    group-qualificatiom  --布尔表达式

 SELECT S.rating, MIN (S.age) AS minage
 FROM Sailors S
 WHERE S.age >=18 --年龄大于18
 GROUP BY S.rating--对级别分组
 HAVING COUNT (*) >1--数量要大于1（在每个组内进行计算）

 SELECT B.bid, COUNT () AS scount
 FROM Boats B, Reserves R
 WHERE R.bid-B.bid -- AND B.color='red'
 GROUP BY B.bid
 HAVING B.color='red' --在bid中不存在color属性

 SELECT Temp.rating
 FROM (SELECT S.rating, AVG (S.age) AS avgage
       FROM Sailors S
       GROUP BY S.rating) As Temp --这个查询就是对水手级别分组 对这个分组取值和平均年龄
 WHERE Temp.avgage =(SELECT MIN (Temp.avgage)
                     FROM Temp）

-- Students (name, school) Soldiers (name, service)
 CREATE VIEW prospects (name, school, service) AS SELECT name, school,
              CAST(NULL. AS Varchar(20))  -- 定义一个试图 由三个属性组成，AS后
                                          --是查询块（）然后加一列空列，使得并兼容
 FROM Students
 UNION
 SELECT name, CAST(NULL. AS Varchar(20)), service --同理也是补充一个空列
 FROM Soldiers;

 --Officers (name, status, rank, title)
 SELECT name, CASE status
     WHEN 1 THEN 'Active Duty' -- 用数字进行编码状态
     WHEN 2 THEN 'Reserve'
     WHEN 3 THEN 'Special Assignment'
     WHEN 4 THEN 'Retired'
 ELSE 'Unknown'
 END AS status
 FROM Officers ;

 -- Machines (serialno, type, year, hours used, accidents)
--求设备表中 求链锯的故障在总故障中的比例
 SELECT sum (CASE
                 WHEN type='chain saw'
                 THEN accidents
                 ELSE 0e0
             END)/ sum (accidents)
 FROM Machines;

 /* Teacher ( name, rank)
     Course (subject, enrollment, quarter, teacher)*/
 WITH
 innerjoin(name, rank, subject, enrollment) AS
         (SELECTt.name, t.rank, c.subject, c.enrollment
          FROM teachers AS t, courses AS
           WHERE t.name-c.teacher ANDc.quarter-Fall 96).--内连接（自然链接）
 teacher-only(name, rank) AS --没有授课计划的老手
     (SELECT name, rank
      FROM teachers
      EXCEPT ALL--减去
      SELECT name, rank
      FROM innerjoin),--有课程的老师
---------------------------------------------------------------
 SELECT name, rank, subject, enrollment
 FROM innerjoin --上面的innerjoin的结果
 UNION ALL--连上去
 SELECT name, rank,
 CAST (NULL AS Varchar(20)) AS subject,--补充空列
 CAST (NULL AS Integer) AS enrollment -- 补充空列
 FROM teacher-only  -- 取没课上的老老师
 UNION ALL
 SELECT CAST (NULL AS Varchar(20)) AS name, --补充空列
        CAST (NULL AS Varchar(20)) AS rank, --补充空列
        subject, enrollment
 FROM course-only; --这是一条sql语句

 WITH agents (name, salary) AS
 ((SELECT name, salary        --initial query
     FROM FedEmp WHERE manager='Hoover)
     UNION ALL
     (SELECTf.name, f.salary     --recursivequery
     FROM  agents  AS a, FedEmp AS f
     WHERE f.manager = a.name))
 SELECT name                      --final query

 WITH trips (destination, route, nsegs, totalcost) AS
   ((SELECT destination, CAST(destination AS varchar(20)),1, cost
   --直达的机场
 FROM flights                          --initial query
 WHERE origin='SFO')
 UNION ALL
 (SELECT f.destination,                --递归运算
         CAST(t.routel ||∵|| f.destination AS varchar(20)), --字符串拼接
         tnsegs+1, t.totalcost+f.cost
 FROM trips t, flights f
 WHERE t.destination-f.origin
         AND f.destinationc>'SFO'       --不能回归原位
         AND f.origino'JFK'       --目的地
         AND t.nsegs<=3))    --最多跳数

 EXEC SQL BEGIN DECLARE SECTION;
 char SNO[7];
 char GIVENSNO[7];
 char CNO[6];
 char GIVENCNO[6];
 float GRADE;
 short GRADEl; /*indicator of GRADE*/
 EXEC SQL END DECLARE SECTION:

 //CONNECT
 EXEC SQL. CONNECT :uid IDENTIFIED BY :pwd;
 //Execute DDL. or DML. Statements
 EXEC SQL INSERT INTO SC(SNO,CNO,GRADE) VALUES(SNO, CNO, GRADE);
 //Execute Query Statements
 EXEC SQL SELECT GRADE
   INTO :GRADE, :GRADEI
   FROM SC
   WHERE SNO=:GIVENSNO AND
   CNO-GIVENCNO;