# data[].attributes.body
SELECT CNAME FROM CUSTOMER C

INNNER JOIN PORDER P

ON C.CID=P.CID

ORDER BY CNAME;

SELCECT CNAME FROM CUSTOMER

WHERE CID IN

(SELECT CID FROM PORDER)

ORDER BY CNAME;

# data[].attributes.body
1b)

SELECT SUM (PPRICE) FROM PORDER

WHERE SCODE IN (

SELECT SCODE FROM STATUS

WHERE SDESC = '已送達');

# data[].attributes.body
Select PRODUCT.NAME, CATEGORY.NAME From PRODUCT LEFT OUTER JOIN CATEGORY ON PRODUCT.CID= CATEGORY.CID;

# data[].attributes.body
查找學生的ID以及學生的課室。

# data[].attributes.body
select MNAME from MANAGER M

inner join EMPLOYEE E on M.MHKID=E.MHKID

group by MHKID having count(*)<10;

# data[].attributes.body
找出擁有超過八本書本的作者

# data[].attributes.body
SELECT BOOKNAME FROM BOOK

INNER JOIN AUTHOR

ON BOOK.AUTHORID=AUTHOR.AUTHORID

WHERE ( PAGE<200 )

AND AUTHORID IN(SELECT AUTHORID FROM AUTHOR WHERE YEAR(DOB)<1960) ;

# data[].attributes.body
SELECT X.ENAME, Y.ENAME

FROM DEPARTMENT D

INNER JOIN EMPLOYEE X ON D.DID = X.DID

INNER JOIN EMPLOYEE Y ON D.MID = Y.EID

# data[].attributes.body
SELECT ENAME FROM EMPLOYEE

WHERE GENDER = 'F'

GROUP BY DID

HAVING COUNT (*) >= 30 ;

# data[].attributes.body
找出1995上映動作片的男演員

# data[].attributes.body
子查詢寫法：

SELECT PNAME FROM PRODUCT WHERE PID IN

(SELECT PID FROM SELL WHERE SID

IN (SELECT SID FROM SHOP WHERE SNAME = '哈利五金' or SNAME = '金厨‘));

Inner join 寫法：

SELECT PNAME FROM SELL SE

INNER JOIN PRODUCT P

ON SE.PID = P.PID

INNER JOIN SHOP SH

ON SE.SID = SH.SID

WHERE SH.SNAME = '哈利五金' or SNAME = '金厨‘;

# data[].attributes.body
SELECT ENAME, DOE FROM EMPLOYEE ORDER BY DOE

UNION

SELECT MNAME, DOE FROM MANAGER ORDER BY DOE;

# data[].attributes.body
create index student_index on STUDENT(SNAME);

# data[].attributes.body
找出沒有同時購買服裝和傢俬類別的客戶

# data[].attributes.body
吳老師 小明

吳老師 小德

杜老師 小格

毛老師 小物

步老師

# data[].attributes.body
SELECT PNAME FROM PRODUCT

LEFT JOIN SELL ON PRODUCT.PID = SELL.PID

where PRODUCT.PID = NULL;

或

SELECT PNAME FROM PRODUCT

LEFT JOIN SELL ON PRODUCT.PID = SELL.PID

where PRODUCT.PID IS NULL;

# data[].attributes.body
SELECT AUTHORNANE

FROM AUTHOR

WHERE AUTHORID IN

(SELECT AUTHORID FROM BOOK

GROUP BY AUTHORID

HAVING SUM ( PAGE ) >

( SELECT AVG ( PAGE )

FROM BOOK ) ) ;

# data[].attributes.body
select ANAME from ACTOR a , FILM f , ROLE r where (a.AID=r.AID and f.FID=r.FID) and FTITLE=‘怪獸工資' intersect select ANAME from ACTOR a , FILM f , ROLE r where (a.AID=r.AID and f.FID=r.FID) and FTITLE=‘怪獸中學' );

# data[].attributes.body
select C.NAME, count(*) from PRODUCT P, CATEGORY C

where (P.CID=C.CID) and (PRICE<=100)

group by P.CID having count(*)>=10;

# data[].attributes.body
SELECT TNAME FROM CLASS WHERE CNAME IN (SELECT CNAME FROM STUDENT WHERE SCORE>= 80);

# data[].attributes.body
select A1.ANAME, A2.ANAME from ACTOR A1, ACTOR A2

where A1.AGE-A2.AGE>=20 and GENDER='F';

# data[].attributes.body
SELECT X.ENAME, Y.ENAME

FROM DEPARTMENT D

INNER JOIN EMPLOYEE X ON D.EID = X.EID

INNER JOIN EMPLOYEE Y ON D.MID = Y.EID

# data[].attributes.body
select ANAME from ACTOR a , FILM f , ROLE r where (a.AID=r.AID and f.FID=r.FID) and FTITLE=‘怪獸工資' intersect select ANAME from ACTOR a , FILM f , ROLE r where (a.AID=r.AID and f.FID=r.FID) and FTITLE=‘怪獸中學' );

# data[].attributes.body
SELECT A1.ANAME AS Female_Actor_1,

A2.ANAME AS Female_Actor_2,

ABS(A1.AGE - A2.AGE) AS Age_Difference

FROM ACTOR A1, ACTOR A2

WHERE A1.GENDER = 'F'

AND A2.GENDER = 'F'

AND A1.ANAME <> A2.ANAME

AND ABS(A1.AGE - A2.AGE) > 20;

或

select A1.ANAME, A2.ANAME from ACTOR A1, ACTOR A2

where A1.AGE-A2.AGE>=20 and GENDER='F';

# data[].attributes.body
select ANAME from ACTOR a , FILM f , ROLE r where (a.AID=r.AID and f.FID=r.FID) and FTITLE=‘怪獸工資' intersect select ANAME from ACTOR a , FILM f , ROLE r where (a.AID=r.AID and f.FID=r.FID) and FTITLE=‘怪獸中學' );