??? ???????????SQL - PowerPoint PPT Presentation

About This Presentation

Title:

??? ???????????SQL

Description:

Title: PowerPoint Presentation Last modified by: ypb Created Date: 1/1/1601 12:00:00 AM Document presentation format: Other titles – PowerPoint PPT presentation

Number of Views:27

Avg rating:3.0/5.0

Slides: 75

Provided by: educ5480

Category:

more less

Transcript and Presenter's Notes

Title: ??? ???????????SQL

1
??? ???????????SQL

3.1 SQL??
3.2??????(DDL)
3.3 SQL?????(DML)
3.4 SQL?????(DML)
3.5??
3.6??????(DCL)
3.7???SQL??
3.8 ????(T-SQL)

2
3.1 SQL??

SQL(Standard/Structured Query Language)????????
1986?10?,???????(American National Standard
Institute ANSI)???????ANSI X3.135-1986,???????(Int
ernational Organization for Standardization
ISO)????????SQL-86
1989?ANSI??????ANSI X3.135-1989 ,ISO ??SQL-89
1999?,ISO?? SQL-1999(SQL99,SQL3)
2003?,ISO?? SQL-2003

3.1.1 SQL?????
????(DDL Data Definition/Description Language )
??????????,????????????
????(DML Data Manipulation Language)
???????,?????????????
????(DCL Data Control Language )
????????????????
???SQL(ESQL)
????????SQL???

3.1.2 SQL?????
?????DDL?DML?DCL???,??????
???????????????????????????
?????????????
???????????????????????
????,????,?????11????
DDLcreate drop alter
DMLselect insert delete update
DCLgrant revoke commit rollback

5
3.2??????(DDL)

3.2.1???????????
???????
CREATE TABLE lt??gt (lt??gtlt????gt??????,lt??gtlt????gt
??????... ...,lt?????????gt)
?CREATE TABLE S(
S CHAR(5) NOT NULL UNIQUE,
SN CHAR(20),
SA INT,
SD CHAR(3),
PRIMARY KEY (S)
)

?????
ALTER TABLE lt??gt
ADD lt???gtlt????gt??????
DROP lt???????gt
MODIFY lt??gtlt????gt
?
ALTER TABLE S ADD SCome DATE
?ALTER TABLE S MODIFY SA SMALLINT
?ALTER TABLE S DROP UNIQUE(S)
?????
DROP TABLE lt??gt
?
DROP TABLE S

3.2.2???????
???????
CREATE UNIQUECLUSTER INDEX lt???gt
ON lt??gt(lt??1gtlt??gt,lt??2gtlt??gt... ...)
lt??gt???ACS?DESC
?
CREATE UNIQUE INDEX S_S ON S(S)
CREATE UNIQUE INDEX C_C ON C(C)
CREATE UNIQUE INDEX SC_S_C ON SC(S ASC,C
DESC)
???????
DROP INDEX lt??gt.lt???gt
DROP INDEX S.S_S

8
3.3 SQL?????(DML)

???????
?A1,A2,.....An(?F (R1R2... ...Rn))
SQL ??
SELECT A1,A2,......An
FROM R1,R2,... ...Rm
WHERE F

????
SELECT ALLDISTINCT lt?????1gt ,lt?????2gt ...
...
FROM lt??????1gt ,lt??????2gt... ...
WHERE lt?????gt
GROUP BY lt?????1gt,lt?????2gt HAVING lt?????gt
ORDER BY lt?????1gt ASCDESC, lt?????2gt
ASCDESC

????
1)??WHERE?????FROM??????/??? ?????????(??)
2)??SELECT???????????????? ??,?????(??)
3)??GROUP??,?????lt?????gt??? ?,?lt?????gt??????????,
? ?????????????
4)??GROUP???HAVING??,?????,? ????????
5)??ORDER??,?????lt?????1gt?? ???????

3.3.1????
?? S(S,SN,SS,SA,SD)
C(C,CN,CP,CR)
SC(S,C,GR)
????????,?????
????
SELECT S,SN FROM S
????
SELECT FROM STUDENT
???????
SELECT SN,2005-SA FROM S

?????????,?????
???????
SELECT DISTINCT SD FROM S
?????????
????lt?lt ?gt?gt??ltgt
SELECT SN,SA FROM S WHERE SDCS
SELECT FROM S WHERE SAlt20
????BETWEEN... AND
SELECT FROM S WHERE SA BETWEEN 20 AND 21
????IN
SELECT FROM S WHERE SD IN (CS,IS,MA)
????LIKE,????\
SELECT FROM S WHERE S LIKE TB
SELECT FROM S WHERE SN LIKE ?_
???????IS NULL
SELECT FROM SC WHERE GR IS NULL
??????
SELECT FROM S WHERE SDCS AND SAlt20

?
13
?

??????
ORDER BY lt?????gt ASCDESC
SELECT FROM SC WHERE C3 ORDER BY GR DESC
???(??)??
COUNT ?SUM?AVG?MAX?MIN
SELECT COUNT() FROM S
SELECT COUNT(DISTINCT S) FROM SC
SELECT AVG(GR) FROM SC WHERE S95001
SELECT MAX(GR) FROM SC WHERE C1
????GROUP BY
SELECT C,COUNT(C) FROM SC GROUP BY C
SELECT S FROM SC GROUP BY S HAVING COUNT() gt3
????gt3???????

14
?

3.3.2????
?????????? ????
SELECT S.,SC. FROM S,SC WHERE S.S SC.S
????
???????????
SELECT f.C, s.CP FROM C f,C s WHERE f.CPs.C

C CN CP CR
1 DB 5 4
2 MA 2
3 IS 1 4
4 OS 6 3
5 DataStruct 7 4
6 DataProcess 2
7 PASCAL 6 4
C CN P CR
1 DB 5 4
2 MA 2
3 IS 1 4
4 OS 6 3
5 DataStruct 7 4
6 DataProcess 2
7 PASCAL 6 4
15
?

???
???????????,??????????????(????)
SELECT S,SN,SS,SA,SD,C,GR FROM S, SC
WHERE S.S SC.S (T-SQL?? SYBASE)
SELECT S,SN,SS,SA,SD,C,GR FROM S, SC
WHERE S.S SC.S() (PL/SQL??ORACLE)
SELECT S,SN,SS,SA,SD,C,GR
FROM S LEFT OUTER JION SC ON S.SSC.S
(MYSQL MSSQL)

16
?

??????
???????2????90????????
SELECT S.S ,SN FROM S,SC
WHERE S.S SC.S AND SC.C2 AND SC.GRgt90
????????????????
SELECT S.S,SN,C.CN,SC.GR from S,SC,C
WHERE S.S SC.S AND SC.C C.C

17
?

3.3.3????
?IN??????
??????????????
SELECT S,SN,SD FROM S WHERE SD IN
(SELECT SD FROM S WHERE SN??)
????????????
SELECT s1.S, s1.SN, s1.SD FROM S s1, S s2
WHERE s1.SD s2.SD AND s2.SN??

18
?

??????????MA????????
SELECT S, SN FROM S WHERE S IN
(SELECT S FROM SC WHERE C IN
(SELECT C FROM C WHERE CNMA) )
????????????
SELECT S,SN FROM S ,SC,C
WHERE S.S SC.S AND SC.C C.C
AND C.CNMA

?????????
??????????????,????????
?(??????????)
SELECT S,SN FROM S WHERE SD
(SELECT SD FROM S WHERE CN??)

?ANY?ALL????(?????????)
???????IS?????????????
SELECT S,SN FROM S WHERE SA lt ANY
(SELECT SA FROM S WHERE SDIS)
AND SDltgtIS
ORDER BY SA DESC
???
SELECT S,SN FROM S WHERE SA lt
(SELECT MAX(SA) FROM S WHERE SDIS)
AND SD ltgt IS
ORDER BY SA DESC

???????IS??????????????
SELECT S,SN FROM S WHERE SA lt ALL
(SELECT SA FROM S WHERE SDIS)
AND SDltgtIS
???
SELECT S,SN FROM S WHERE SA lt
(SELECT MIN(SA) FROM S WHERE SDIS)
AND SD ltgt IS
ORDER BY SA DESC

?EXISTS????(???????,???Ture?False)
???????????1?????
SELECT SN FROM S WHERE EXISTS
(SELECT FROM SC WHERE S S.S AND C 1)
?????????????????????,???????????(corelated
subquery)?
??????
SELECT SN FROM S,SC WHERE S.S SC.S AND C
1

SQL???(?x)p,?????(?x(p))
?????????????,????????????
SELECT SN FROM S WHERE NOT EXISTS
(SELECT FROM C WHERE NOT EXISTS
(SELECT FROM SC WHERE C C.C AND S S.S))
p-gtq?????p?q
??????????S001??????????
?p??S001???y q??x???y
(?y)(p-gtq)?y((p-gtq)) ?y((p?q)) ?y(p?q))
??????????,??S001????x????
SELECT SN FROM S WHERE NOT EXISTS(
SELECT FROM SC SC2 WHERE S S001 AND
NOT EXISTS (SELECT FROM SC WHERE C SC2.C
AND S S.S))

3.3.4????
??????????????,INTERSECT ,UNION,MINUS
??????????????19????
SELECT FROM S WHER SDCS
UNION SELECT FROM S WHERE SAlt19
???
SELECT FROM S WHERE SDCS OR SA lt19
?????????C01?C02?????
SELECT S FROM SC WHERE CC01
UNION SELECT S FROM SC WHERE CC02
???
SELECT S FROM SC WHERE C IN (C01,C02)

???????????C01?C02?????
SELECT S FROM SC WHERE CC01
INTERSECT
SELECT S FROM SC WHERE CC02(?ORACLE)
???
SELECT S FROM SC WHERE C C01 AND S IN
(SELECT S FROM SC WHERE C C02)
?????????C01????C02??????
SELECT S FROM SC WHERE CC01
MINUS
SELECT S FROM SC WHERE CC02(?ORACLE)
???
SELECT S FROM SC WHERE CC01AND
S NOT IN (SELECT S FROM SC WHERE CC02)

26
3.4 SQL?????(DML)

3.4.1????
??????
??
INSERT INTO lt??gt (lt??1gt , lt??2gt......)
VALUES(lt??1gt,lt??2gt......)
INSERT INTO S VALUES(S001,??,?,18,IS)
???????
??
INSERT INTO lt??gt (lt??1gt , lt??2gt......)lt???gt
????????????C01?????
INSERT INTO SC SELECT S,C01,null FROM S

3.4.2????
??
UPDATE lt??gt SET lt??gtlt???gt, lt??gt
lt???gt...... WHERE lt??gt
?????????
???S001?????22?
UPDATE S SET SA22 WHERE S S001
????????
??????????1?
UPDATE S SET SASA1

?????????
????????????????
UPDATE SC SET GR0
WHERE CS (SELECT SD FROM S WHERE S
SC.S) (?????)
???DBMS????join??????,?SYBASE
UPDATE SC set GR0 from S where S.SSC.S
and SDCS
? UPDATE SC set GR0 where S in
(SELECT S from S where SDCS)
????????????
?????S?SC????S??
???????????,??????

3.4.3????
??
DELETE FROM lt??gt WHERE lt??gt
?????????
?????S001???
DELETE FROM S WHERE SS001
????????
???????????
DELETE FROM SC
?????????
?????????????????
DELETE FROM SC WHERE CS(
SELECT SD FROM S WHERE SSC.S) (?????)
DELETE from SC where S in
(SELECT S from S where SDCS) (??????)

30
3.5??

????????,?????????
3.5.1????
????
??
CREATE VIEW lt???gt (lt??1gt,lt??2gt......)
AS lt???gt WITH CHECK OPTION
???????????
????????????,??????
?????????????????
????????????

WITH CHECK OPTION ?????????????????
?????????????????????,??????????????????,?????,??
?????
???????
CREATE VIEW IS_S AS
SELECT S, SN , SA FROM S
WHERE SDIS
????????C1????????
CREATE VIEW IS_S1 (S, SN ,GR) AS
SELECT S.S ,SN ,GR FROM S, SC
WHERE S.S SC.S AND S.SDIS
AND SC.C C1

??????????
?????C1??????90???????
CREATE VIEW IS_S2 AS
SELECT S, SN, GR FROM IS_S1 WHERE GRgt90
????????
???????????????
CREATE VIEW BT_S(S,SN,SB)AS
SELECT S, SN,2003-SA FROM S
????????????????
CREATE VIEW S_G(S,AVG_GR)AS
SELECT S, AVG(GR) FROM SC GROUP BY S

??????????
CREATE VIEW S_F(S,SN,SS,SA,SD)AS
SELECT FROM S WHERE SS?
????S?????????,???????????????
????
??
DROP VIEW lt???gt
??????IS_S
DROP VIEW IS_S

3.5.2????
????????????????????????(View Resolution)
SELECT S,SA FROM IS_S WHERE SA lt20
???
SELECT S ,SA FROM S WHERE SDIS AND SA lt20
SELECT FROM S_G WHERE AVG_GRgt90
???
SELECT S, AVG(GR) FROM SC WHERE AVG(GR)gt90 GROUP
BY S (??)
SELECT S, AVG(GR) FROM SC GROUP BY S HAVING
AVG(GR)gt90 (??)

3.5.3????
?????
????????????S001?????????
UPDATE IS_S SET SN?? WHERE SS001
?????
UPDATE S SET SN?? WHERE SS001AND SDIS
?????
?????????????
INSERT INTO IS_S VALUES (S001,??,20)
????
INSERT INTO S VALUES (S001,??,NULL,20,IS)

?????
?????????????
DELETE FROM IS_S WHERE SS001
????
DELETE FROM S WHERE SS001AND SDIS
?????/????????????
??
UPDATE S_G SET AVG_GR80 WHERE SS001
(??)

??????????
?????????????
?????????????,???DELETE
??????????
?????GROUP BY??
?????DISTINCT??
?????????,?????????????????
??????????????

3.5.4?????
??????????
????????????????
???????????????????
??S(S, SN, SS, SA, SD)?????
SX(S,SN, SS, SA), SY(S, SD)
????????????????
CREATE VIEW S AS
SELECT SX.S, SN, SS, SA, SD
FROM SX, SY WHERE SX.S SY.S
????????????

39
3.6??????(DCL)

3.6.1??
??
GRANT ALL PRIVILEGESlt??gt,lt??gt... ...
ON lt????gt lt???gt
TO PUBLIClt??gt,lt??gt... ...
WITH GRANT OPTION
??
GRANT SELECT ON TABLE S TO USER1
GRANT ALL Privileges ON TABLE S, C TO U2,U3
GRANT SELECT ON TABLE SC TO PUBLIC
GRANT UPDATE(SD),SELECT ON TABLE S TO U4
GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION
GRANT CREATETAB ON DATABASE S_C TO U8

3.6.2????
??
REVOKE ALL PRIVILEGESlt??gt,lt??gt... ...
ON lt????gt lt???gt
FROM PUBLIClt??gt,lt??gt... ...
??
REVOKE SELECT ON TABLE SC FROM PUBLIC
REVOKE UPDATE(SD),SELECT ON TABLE S FROM U4
REVOKE INSERT ON TABLE SC FROM U5

41
3.7 ???SQL??

SQL???????,??????????,??????????SQL???????.
?SQL??(Embedded SQL )????(????)???,????SQL??(??ESQ
L)?

3.7.1???SQL?????
??ESQL???,DBMS?????????
???
????????????SQL
ESQL????EXEC SQL ltSQL ??gt
ESQL???????????
?????
?????
???????DDL?DML?DCL???SQL??
?????????????????

3.7.2???SQL???????????
?????????????????????
??????SQL???????
????SQL??????
?SQL??????????????????
?????SQLCA??????????
? SQL???
SQLCA(SQL Communication Area)???????,????
EXEC SQL INCLUDE SQLCA
SQLCODE??????SQL?????

? ???
????ESQL??????????????????
???????????????
????
?????????????(BEGIN DECLARE SECTION?END DECLARE
SECTION)
???SQL???????????
?SQL???,??????,??????????
?????????????????????????

? ??
????SQL????????,??????????
????SQL?????
SQL?????????????
??????????????SQL??????????
SQL?????????????????????-gtSQL????????DBMS??SQLCA-gt
????SQLCA??????,??????????
SQL????????????????????

3.7.3??????SQL??
?????
??????
??????
?????????SELECT??
?CURRENT???UPDATE??
?CURRENT???DELETE??
INSERT??

1 )?????
EXEC SQL INCLUDE SQLCA
EXEC SQL BEGIN DECALRE SECTION
EXEC SQL END DECALRE SECTION
2 )??????
EXEC SQL CREATE TABLE S(
S char(10), SN char(10), SS char(2),
SA int, SD char(5))
EXEC SQL DROP TABLE
???????????????
EXEC SQL DROP TABLE tablename (??)

3) ??????
EXEC SQL GRANT SELECT ON TABLE S TO U1
4) ?????????SELECT??
??
EXEC SQL SELECT ALLDISTINCT lt?????1gt
,lt?????2gt ... ... INTO lt???1gt lt????1gt
,lt???1gt lt????1gt......
FROM lt??????1gt ,lt??????2gt... ...
WHERE lt?????gt
GROUP BY lt?????1gt,lt?????2gt
HAVING lt?????gt
ORDER BY lt?????1gt ASCDESC,
lt?????2gt ASCDESC

?
EXEC SQL SELECT S, SN INTO sno, sn
FROM S WHERE S GivenSno
??
into?where?having???????????,???????
??????NULL?,???????????-1,??????
??????????????,?DBMS?sqlcode??100,??????0?
???????,????,SQLCA???????

5 )?CURRENT???UPDATE??
???C01????
EXEC SQL UPDATE SC SET GRGRRaise
WHERE C C01
??????????
EXEC SQL UPDATE SC SET GRnewgr
WHERE S S001
?????????????
Grid-1
EXEC SQL UPDATE SC SET GRnewgr grid WHERE
S IN (SELECT S FROM S WHERE SDCS)
??
EXEC SQL UPDATE SC SET GRNULL WHERE
S IN (SELECT S FROM S WHERE SDCS)

6 )?CURRENT???DELETE??
?
??????????
EXEC SQL DELETE FROM SC WHERE S IN
(SELECT S FROM S WHERE SNsname)
??
EXEC SQL DELETE FROM SC WHERE sname
(SELECT SN FROM S WHERE S.S SC.S)

7 )INSERT??
?
???????????
grid-1
EXEC SQL INSERT INTO SC
VALUES (sno, cno, gr grid)
??
EXEC SQL INSERT INTO SC (S, C)
VALUES (sno, cno)

3.7.4?????SQL??
????????
??????????SELECT??
CURRENT???UPDATE??
CURRENT???DELETE??

1 )??????????SELECT??
???SELECT??????????????????????????
????
?????????,??????
EXEC SQL DECLARE lt???gt CURSOR FOR ltSELECT ??gt
???????????,????????,???????????
EXEC SQL OPEN lt???gt
?????????????
EXEC SQL FETCH lt???gt
INTO lt???gtlt????gt,lt???gtlt????gt......
????????????
EXEC SQL CLOSE lt???gt

???????????????
EXEC SQL INCLEDE SQLCA //?????
EXEC SQL BEGIN DECLARE SECTION
VARCHAR depname5
VARCHAR HSno10
VARCHAR HSname10
VARCHAR HSex2
int HSage
EXEC SQL END DECLARE SECTION

... ...
gets(depname)
... ...
EXEC SQL DECLARE SX CURSOR FOR //????
SELECT S , SN ,SS , SA FROM S
WHERE SD depname
EXEC SQL OPEN SX //????
WHILE (1)
EXEC SQL FETCH SX INTO Hsno,Hsname,Hsex,HSage
//?????????????
if(sqlca.sqlcode!SUCCESS)break
printf(s,s,s,d\n, Hsno, Hsname, Hsex,
HSage)
... ...
EXEC SQL CLOSE SX //????
depname????????????,????????

2) CURRENT???UPDATE?DELETE??
????
????
EXEC SQL DECLARE lt???gt CURSOR FOR ltSELECT??gt FOR
UPDATE OF lt??gt
OPEN??
FETCH??
??????????,????DELETE?UPDATE,????WHERE CURRENT OF
lt???gt
????CLOSE??

????????,????????
... ...
EXEC SQL INCLEDE SQLCA
EXEC SQL BEGIN DECLARE SECTION
VARCHAR depname5
VARCHAR HSno10
VARCHAR HSname10
VARCHAR HSex2
int HSage
EXEC SQL END DECLARE SECTION
.... ...

gets(depname)
... ...
EXEC SQL DECLARE SX CURSOR FOR
SELECT S , SN ,SS , SA FROM S
WHERE SD depname
FOR UPDATE OF SA
EXEC SQL OPEN SX
WHILE (1)
EXEC SQL FETCH SX INTO Hsno, Hsname,
Hsex, Hsage
if(sqlca.sqlcode!SUCCESS)break

printf(s,s,s,d\n, Hsno, Hsname, Hsex,
HSage)
printf(UPDATE(U) or DELETE(D) or NO(N)?\n)
scanf(c,op)
if(opU)
printf(Input new age)
scanf(d,newage)
EXEC SQL UPDATE S SET SAnewage WHERE CURRENT
OF SX
else if(OPD)
EXEC SQL DELETE FROM S WHERE CURRENT OF SX
else continue
... ...
EXEC SQL CLOSE SX

3.7.5??SQL??(?SYBASE?ESQL??)
????????????????????SQL?? ,????????
SQL????
?????
???????
SQL?????????

??????????? (??????)
??????????????
??
EXEC SQL EXECUTE IMMEDIATE host_variable
string
?
EXEC SQL BEGIN DECLARE SECTION
CS_CHAR sqlstring200
EXEC SQL END DECLARE SECTION
char cond150
strcpy(sqlstring,"update titles set
priceprice1.10 where ")
printf("Enter search condition")
scanf("s", cond)
strcat(sqlstring, cond)
EXEC SQL EXECUTE IMMEDIATE sqlstring

63
3.8 ????(T-SQL)

??
create procedure owner.procedure_name
(_at_parameter_name datatype defaultoutput
, _at_parameter_name datatype defaultoutput..
.)
with recompile
as ltSQL_statementsgt
????
???
begin
ltstatement blockgt
end

??
?_at_????????,?_at__at_????????
????DECLARE
_at__at_rowcount ???????
_at__at_sqlstatus??Fetch???
????
if logical_expression
statements
else
if logical_expression
statements

????
while boolean_expression
statement
break
statement
continue
????
label
goto label
???
return integer_expression

????
print format_string _at_local_variable
_at__at_global_variable , arg_list
select _at_local_variable _at__at_global_variable
??
execute _at_return_status server.database.
owner.procedure_name
_at_parameter_name value _at_parameter_name
_at_variable output
,_at_parameter_name value_at_parameter_name
_at_variable output...
with recompile

?????,???????,??C01??,???1,???2
CREATE PROCEDURE get_gr _at_sno varchar(10), _at_GR
OUTPUT int
AS
DECLARE _at_cno varchar(5)
BEGIN
SELECT _at_cnoC,_at_GRGR FROM S WHERE S _at_sno
IF (_at_cno C01)THEN
select _at_GR_at_GR1
ELSE
select _at_GR_at_GR2
END
??
declare _at_gr int
execute get_gr s001,_at_gr output
select _at_gr ?print _at_gr

68
(No Transcript)
69
S
S SN SS SA SD
95001 ?? ? 20 CS
95002 ?? ? 19 IS
95003 ?? ? 18 MA
95004 ?? ? 18 IS
SC
C
C CN CP CR
1 DB 5 4
2 MA 2
3 IS 1 4
4 OS 6 3
5 DataStruct 7 4
6 DataProcess 2
7 PASCAL 6 4
S C GR
95001 1 92
95001 2 85
95001 3 88
95002 2 90
95002 3 80
70
SELECT S,SN FROM S
S SN
95001 ??
95002 ??
95003 ??
95004 ??
SELECT SN,2005-SA FROM S
SELECT DISTINCT SD FROM S
SN 2005-SA
?? 1985
?? 1986
?? 1987
?? 1987
SD
CS
IS
MA
IS
71
SELECT FROM SC WHERE C3 ORDER BY GR DESC
S C GR
95001 3 88
95002 3 80
SELECT C,COUNT(C) FROM SC GROUP BY C
C COUNT(C)
1 1
2 2
3 2
SELECT S FROM SC GROUP BY S HAVING COUNT() gt3
S
95001
72
SELECT S.,SC. FROM S,SC WHERE S.S SC.S
S.S SN SS SA SD SC.S C GR
95001 ?? ? 20 CS 95001 2 85
95001 ?? ? 20 CS 95001 1 92
95001 ?? ? 20 CS 95001 3 88
95002 ?? ? 19 IS 95002 2 90
95002 ?? ? 19 IS 95002 3 80
SELECT f.C, s.CP FROM C f,C s WHERE f.CPs.C
f.C s.CP
1 7
3 5
5 6
73
SELECT S,SN,SS,SA,SD,C,GR FROM S LEFT OUTER
JION SC ON S.SSC.S
S SN SS SA SD C GR
95001 ?? ? 20 CS 2 85
95001 ?? ? 20 CS 1 92
95001 ?? ? 20 CS 3 88
95002 ?? ? 19 IS 2 90
95002 ?? ? 19 IS 3 80
95003 ?? ? 18 MA null null
95004 ?? ? 18 IS null null
74
SELECT S.S ,SN FROM S,SC WHERE S.S SC.S AND
SC.C2 AND SC.GRgt90
S SN SC.C SC.GR
95002 ?? 2 90
SELECT S.S,SN,C.CN,SC.GR from S,SC,C WHERE S.S
SC.S AND SC.C C.C
S.S SN C.C C.CN SC.GR
95001 ?? 1 DB 92
95001 ?? 2 MA 85
95001 ?? 3 IS 88
95002 ?? 2 MA 90
95002 ?? 3 IS 80

Write a Comment

User Comments (0)