KIT_BD-Язык SQL

. , , ,

city 3 London, Peel - & ,

. 0 !

* ( !).

+ (, # , , # (

. ' & ! % ! , !,

WHERE

#% ( = ). 'city = 'London' 'Salespeople.snum = Orders.snum ' -

, .

+ ( - % (, # !. '

, $ , # (. !

( #% ! :

SELECT sname, cname FROM Salespeople, Customers WHERE sname < cname AND rating < 200;

) . +

, & $ ,

& 200.

1 , ( #% . *

% ! ! . !

& :

SELECT onum, cname, Orders.cnum, Orders.snum FROM Salespeople, Customers,Orders WHERE Customers.city < > Salespeople.city AND Orders.cnum = Customers.cnum AND Orders.snum = Salespeople.snum;

9 ! , !, -

% !, ! % ( snum ),

- % # ( cnum snum *).

13. SQL - ",

13.1. ( )

' SQL $ INSERT. ' $ , INSERT

#% : INSERT INTO VALUES ( , . . .)

0, , *, #% : INSERT INTO Salespeople VALUES (1001, 'Peel', 'London', .12)

" DML ! , & ! , !, .

, , CREATE TABLE, ,

, , . ' ANSI, ! , 3 - , 2 + 1 - . 3,

, , ,

1, 2, .

, (NULL), . *,

% city Peel. 1 ! =NULL , #%

:

INSERT INTO Salespeople VALUES (1001, 'Peel', NULL, .12)

0 NULL - , , #

.

13.2.

1 , . ) #

. 2, 3 ! , %

: city, cname, cnum, %, :

INSERT INTO Customers (city, cnam , cnum) VALUES ('London', 'Honman', 2001)

rating snum - #. ) , - #. * # NULL ! - #. , ! % NULL , #,

# INSERT, .

13.3. ( *

1 INSERT, %

! #, . - , VALUES (

% ! ) #% :

INSERT INTO Londonstaff SELECT * FROM Salespeople WHERE city = 'London'

3 # - * city = "London" - % # # Londonstaff. - , Londonstaff

#% :

1.+ .

2.+ , # * ;

, , , , (

).

+ % , , ,

, *. Londonstaff - ,

*(Salespeople). , * ! ,

Londonstaff. 0 INSERT ! , ,

, , , . *, , $ # Daytotals,

% . 1

*, Daytotals $

*.

0 * $ !, , & ,

% #% ! INSERT :

INSERT INTO Daytotals (date, total) SELECT odate, SUM (amt) FROM Orders GROUP BY odate

* Daytotals - . " !,

% - , ,

! # - .

13.4.

# $ - DELETE. + ,

, . -

*, #% :

DELETE FROM Salespeople

+, . -

, , , . 2, Axelrod

, :

DELETE FROM Salespeople WHERE snum = 1003

' snum sname , &

#, ! # ! . ) ! # !

#. ", DELETE , ! :

DELETE FROM Salespeople WHERE city = 'London'

13.5. )

* % UPDATE % .

) UPDATE, SET, #%

, ! . 2, 200,

:

UPDATE Customers SET rating = 200

2 ! ! , UPDATE,

DELETE, . ' , ,

Peel, #% ! snum=1001:

UPDATE Customers SET rating = 200 WHERE snum = 1001

+, , ! $ ! % # UPDATE. * SET # , . ' !

# , . *, Motika & #, ! :

UPDATE Salespeople SET sname = 'Gibson',city = 'Boston',comm = .10 WHERE snum = 1004;

) Gibson, % & ! Motika ,

, Motika % # snum.

1 $ ! , , $

SET. ! , - "SET Salespeople.sname = Gibson" UPDATE, - "SET sname = Gibson".

13.6. * + ) ' )

1 SET UPDATE, , # !

. ' VALUES INSERT, !

; - .

*, . 1 #% :

UPDATE Salespeople SET comm = comm * 2

' , ! # SET,

% , - % # UPDATE. 1

, - # ., :

UPDATE Salespeople SET comm = comm * 2 WHERE city = 'London'

* SET - . + NULL , ,

!- ! . 0 , . NULL,

#% :

UPDATE customers SET rating = NULL WHERE city = 'London'

) ..