Karpova_bazy_dannyh

OUTER , FULL, LEFT, RIGHT, .

R1, . - ,

FROM SELECT.

92 5. SQL.

EXEMPLARE (IN ISBN YES_NO NUM_READER DATE_IN DATE_OUT)

BOOKS , ,

:

ISBN ,

TITL , AUT0R ,

COAUTOR , YEARIZD , PAGES ,

READER , -

:

NUM_READER ,

NAME_READER ;

ADRESS - ,

H00M_PH0NE , WORK_PHQNE , BIRTH_DAY .

EXEMPLARE

. :

YES_HO

,

NUM_READER 6, , Null .

93

DATE_IN , , ,

DATE_OUT - , .

, , -

NULL. -

,

READER EXEMPLARE, - , EXEMPLARE INV -

NULL.

SELECT READER NAME_READER EXEMPLARE INV

FROM READER RIGHT JOIN EXEMPLARE ON READER NUM_READER=EXEMPLARE NUM_READER

LEFT JOIN

(READER NATURAL JOIN EXEMPLARE) USING (ISBN)

,

,

.

:

SELECT -

UNION

SELECT -

UNION

SELECT -

, , -

, .

, , «» «». -

:

. , ,

,

.

.

, , «»:

UNION

SELECT READER NAME_READER FROM READER EXEMPLARE.BOOKS

WHERE EXEMPLARE.NUM_READER= READER NUM_READER AND EXEMPLARE.ISBN = BOOKS ISBN AND

BOOKS TITLE =

, -

, ALL .

-

:

SELECT -

UNION ALL

SELECT

UNION ALL

SELECT -

WHERE

, -

.

SELECT DISTINCT READER.NAME_READER

FROM READER.EXEMPLARE.BOOKS

95

WHERE EXEMPIARE NUM_READER= READER NUM RTADER AND EXENPLARE ISBN = BOOKS ISBN AND

BOOKS TITLE = OR

BOOKS TITLE = "" OR BOOKS TITLE = ""

UNION -

ORDER BY,

, ORDER BY SELECT-.

: -

DELETE, -

INSERT () UPDATE.

.

.

INSERT :

INSERT INTO _ [(<>)] VALUES (<>)

.

, -

. , BOOKS:

INSERT INTO BOOKS (ISBN,TITL.AUT0R.COAUTOR)R.YEARIZO PAGES) VALUES (5-88782-290-2 IBM PC ". ". . "."" 2000.816)

, ,

COAUT0R,

VALUES. , ,

KILL.

, ,

, -

:

INSERT INTO BOOKS VALUES ("5-88782-290-2"

" I PC " ". '."" 2000.816)

.

, , o - , .

, -

:

INSERT INTO BOOKS ( ISON TITL AUTOR YEARIZD PAGES) VALUES ("5 88782 290 2" IBM PC

. " 2000 816)

INSERT INTO READER (NAME_READER ADRESS HO0M_ PHONE BIRTH_DAY) SELECT (NAME_STUDENT ADRESS HOOM_ PHONE BIRTH_DAY)

FROM STUDENT

,

. , ,

,

.

- - , .

97

:

DELETE FROM [WHERE ]

, ,

, . , -

, .

, ,

:

DELETE FROM R2

WHERE = . .

WHERE . ,

, -

,

. ,

,

, , .

SQL ,

.

DELETE FROM R2 WHERE R2 IN

(SELECT Rl 0 FROM Rl

WHERE = 2 OR IS NULL GROOP BY R1

HAVING UNT(*) >= 2

DELETE, ,

, ,

,

, ,

.

DELETE FROM R2

WHERE R2 IN

(SELECT R1

FROM (R2 NATURAL INNER JOIN R3) LEFT JOIN R1 USING ( WHERE = 2 OR IS NULL

GROOP BY Rl

HAVING COUNT(*) >=2

99

:

R5

-

.

:

UPDATE R5

SET R5 = 50% WHERE R5 IN

(SELECT R1 FROM R1

WHERE R1 =5 GROOP BY R1 HAVING COUNT(*)=3)

25%:

UPDATE R5

SET R5 = 25% WHERE R5 ) IN

(SELECT R1 FROM R1

WHERE R1 NOT IN (SELECT A FROM R1 A

WHERE <=3 OR IS NULL) GR0OP Y R1

HAVING C0UNT(*)>=2 )

:

UPDATE R5

SET R5 =0% WHERE R5 IN

(SELECT Rl FROM R1

WHERE Rl >=4 AND Rl NOT IN SELECT A

FROM RI A

WHERE <= 3 OR IS HULL) )

:

UPDATE R5

SET R5 =100%

WHERE R5 IN