ÒªÇ󣺨C1. ʵѵÍØչѵÁ·¡¾ÆóÒµÃæÊÔÌâ¡¿
¨CΪ¸ÚλҵÎñÅàѵҵÎñ½¨Á¢3¸ö±í:
¨CS (S#,SN,SD,SA) S#,SN,SD,SA ·Ö±ð´ú±í¹¤ºÅ¡¢Ô±¹¤ÐÕÃû¡¢ËùÊôµ¥Î»¡¢Ô±¹¤ÄêÁä
¨CC (C#,CN ) C#,CN ·Ö±ð´ú±í¿Î³Ì±àºÅ¡¢¿Î³ÌÃû³Æ
¨CSC( S#,C#,G ) S#,C#,G ·Ö±ð´ú±í¹¤ºÅ¡¢ËùÑ¡Ð޵Ŀγ̱àºÅ¡¢³É¼¨
½âÌâ˼·£ºÏÂÃæÊÇ·Ö²½½âÎöµÄ Òª×Ðϸ¹Û²ì
CREATE TABLE S
(
S# VARCHAR2(10),
SN VARCHAR2(20),
SD VARCHAR2(20),
SA NUMBER(3)
)
¨CÀûÓà begin end ¿ÉÒÔÏòÆäÖмÓÈë¶àÌõÊý¾Ý
BEGIN
insert into S values(¡¯001¡ä,¡¯Jack¡¯,'IBM¡¯,20);
insert into S values(¡¯002¡ä,¡¯Lucy¡¯,'Microsoft¡¯,22);
insert into S values(¡¯003¡ä,¡¯Tom¡¯,'Sun¡¯,30);
insert into S values(¡¯004¡ä,¡¯Jane¡¯,'Asus¡¯,31);
END;
SELECT * FROM S
DROP TABLE S
¨C
CREATE TABLE C
(
C# VARCHAR2(10),
CN VARCHAR2(20)
)
DROP TABLE C
¨CÏòÆäÖвåÈëÊý¾Ý
BEGIN
insert into C values(¡®C001¡ä,¡¯C');
insert into C values(¡®C002¡ä,¡¯Java¡¯);
insert into C values(¡®C003¡ä,¡¯SQL¡¯);
END;
SELECT * FROM C
CREATE TABLE SC
(
S# VARCHAR2(10),
C# VARCHAR2(10),
G NUMBER(3,1)
)
DROP TABLE SC
BEGIN
insert into SC values(¡¯001¡ä,¡¯C001¡ä,50);
insert into SC values(¡¯001¡ä,¡¯C002¡ä,50);
insert into SC values(¡¯002¡ä,¡¯C002¡ä,70);
insert into SC values(¡¯003¡ä,¡¯C001¡ä,99);
insert into SC values(¡¯003¡ä,¡¯C002¡ä,52);
insert into SC values(¡¯003¡ä,¡¯C003¡ä,44);
insert into SC values(¡¯004¡ä,¡¯C003¡ä,54);
END;
SELECT * FROM SC
¨C¡¾ÎÊÌâÒ»¡¿Ê¹Óñê×¼SQLǶÌ×Óï¾ä²éѯѡÐÞÈ«²¿¿Î³ÌµÄÔ±¹¤ÐÕÃûºÍËùÊôµ¥Î»;
¨C·½°¸Ò»£º
SELECT SN,SD
FROM S,(SELECT COUNT(C#)AS NUM,S# FROM SC GROUP BY S#) T
WHERE T.S#=S.S# AND T.NUM=(SELECT COUNT(*) FROM C);
¨C·½°¸¶þ£º
¨C¡¾ÎÊÌâ¶þ¡¿ÁгöÓжþÃÅÒÔÉÏ(º¬Á½ÃÅ)²»¼°¸ñ¿Î³ÌµÄÔ±¹¤ÐÕÃû¼°Æäƽ¾ù³É¼¨;
¨C·½°¸Ò» £º
SELECT * FROM SC
SELECT SN AS ÐÕÃû,AVG_G AS ƽ¾ù³É¼¨ FROM S
INNER JOIN
(SELECT COUNT(G)AS NUM,AVG(G)AS AVG_G,S# FROM SC WHERE G<60 GROUP BY S#) T
ON S.S#=T.S#
WHERE NUM>=2
¨C¡¾ÎÊÌâÈý¡¿Áгö¼Èѧ¹ý¡±C001¡åºÅ¿Î³Ì£¬ÓÖѧ¹ý¡±C002¡åºÅ¿Î³ÌµÄËùÓÐÔ±¹¤ÐÕÃû;
SELECT * FROM SC
¨C·½°¸Ò»£º
SELECT SN,S# FROM S WHERE S# IN(
SELECT S# FROM SC WHERE C#=¡¯C001¡ä
INTERSECT
SELECT S# FROM SC WHERE C#=¡¯C002¡ä
)
¨C·½°¸¶þ£º
SELECT S# AS ±àºÅ,SN ÐÕÃû FROM S
WHERE S# IN
(
SELECT T1.S# FROM
(SELECT S# FROM SC WHERE C#=¡¯C001¡ä) T1
INNER JOIN
(SELECT S# FROM SC WHERE C#=¡¯C002¡ä) T2
ON T1.S#=T2.S#
)
¨C¡¾ÎÊÌâËÄ¡¿Áгö¡±C001¡åºÅ¿Î³É¼¨±È¡±C002¡åºÅ¿Î³É¼¨¸ßµÄËùÓÐÔ±¹¤µÄ¹¤ºÅ
¨C¼°Æ䡱C001¡åºÅ¿ÎºÍ¡±C002¡åºÅ¿ÎµÄ³É¼¨ .
SELECT * FROM SC
SELECT T1.S#,T1.G AS C001³É¼¨,T2.G AS C002³É¼¨
FROM (SELECT S#,G FROM SC WHERE C#=¡¯C001¡ä) T1 INNER JOIN
(SELECT S#,G FROM SC WHERE C#=¡¯C002¡ä) T2
ON T1.S#=T2.S#
WHERE T1.G>T2.G
×ܽ᣺һ°ãÔÚÃæÊÔµÄʱºò Îʵ½µÄ ¡°²éѯǰ¼¸ÃûµÄÔ±¹¤µÄÐÅÏ¢ ¡± ±È½Ï¶à Ò²ÊǸ߼¶²éѯÖÐÏà¶Ô½ÏÄÑµÄ ÒªÇø·Ö¿ª¡°²éѯǰ¼¸Ãû¡±ºÍ¡°²éѯǰ¼¸¸öÔ±¹¤ÐÅÏ¢¡±µÄÇø±ð¡ï
¡¾×¢½â¡¿£ºÒª·ÖÇå ÔÚOracleÖкÍÔÚSQL2005ÖÐ Á½¸ö²éѯµÄÖ÷ÒªÇø±ð¡£