Look_at_more:=True;
EXEC SQL OPEN CURSOR Londonsales;
while Look_at_more and SQLCODE = O do
begin
EXEC SQL FETCH Londonsales
INTO :id_num, :Salesperson, :loc, :comm;
writeln (id_num, Salesperson, loc, comm);
writeln ('Do you want to see more data? (Y/N)');
readln (response);
If response = 'N' then Look_at_more:=False;
end;
EXEC SQL CLOSE CURSOR Londonsales;
EXEC SQL DELETE FROM Customers
WHERE rating < (SELECT AVG (rating)
FROM Customers);
, , , , DELETE . :
EXEC SQL DECLARE Belowavg CURSOR FOR
SELECT *
FROM Customers
WHERE rating < (SELECT AVG (rating)
FROM Customers);
, :
EXEC SQL WHENEVER SQLERROR GOTO Error_handler;
EXEC SQL OPEN CURSOR Belowavg;
while not SOLCODE = 100 do
begin
EXEC SOL FETCH Belowavg INTO :a, :b, :c, :d, :e;
EXEC SOL DELETE FROM Customers
WHERE CURRENT OF Belowavg;
end;
EXEC SOL CLOSE CURSOR Belowavg;
WHERE CURRENT OF , DELETE , . , , DELETE , , , .
. , (. 21). , ORDER BY UNION, , , . , , . FETCH. UPDATE .
, =300, . :
EXEC SOL DECLARE CURSOR High_Cust AS
SELECT *
FROM Salespeople
WHERE snum IN (SELECT snum
FROM Customers
WHERE rating = 300);
:
EXEC SQL OPEN CURSOR High_cust;
while SQLCODE = 0 do
begin
EXEC SOL FETCH High_cust
INTO :id_num, :salesperson, :loc, :comm;
EXEC SQL UPDATE Salespeople
SET comm = comm + .01
WHERE CURRENT OF High_cust;
end;
EXEC SQL CLOSE CURSOR High_cust;
indicator 0. , NULL, indicator . indicator, , NULL. , city comm, , NOT NULL, SQL , i_a i_b.
, indicator. indicator, indicator.
:
EXEC SQL OPEN CURSOR High_cust;
while SQLCODE = O do
begin
EXEC SQL FETCH High_cust
INTO :id_num, :salesperson, :loc, :i_a, :comm INDlCATOR, :i_b;
If i_a >= 0 and i_b >= 0
then {no NULLs produced}
begin
EXEC SQL UPDATE Salespeople
SET comm = comm + .01
WHERE CURRENT OF Hlgh_cust
end {then}
else {one or both NULL}
begin
If i_a < 0 then writeln('salesperson ', id_num, ' has no city');
If i_b < 0 then writeln('salesperson ', id_num, ' has no commission')
end {else}
end; {while}
EXEC SQL CLOSE CURSOR High_cust;
, , INDICATOR , , , . , UPDATE NULL .
NULL , , , NULL .
: indicator , , WHERE SQL.
, .
INDICATOR NULL SQL
, indicator, , NULL SQL.
, , if ... then, indicator, =NULL. , -. , NULL city city, indicator i_city, city . , ; . , i_city . , :
If city = 'London' then comm := comm + .01
else comm := comm - .01
, city, 'London', . , , . SQL -:
EXEC SQL UPDATE Salespeople
SET comm = comm + .01
WHERE city = 'London';
EXEC SQL UPDATE Salespeople
SET comm = comm - .01;
WHERE city <> 'London';
, SQL .
city SQL NULL, , comm, , .
indicator, , , NULL :
If i_city >= O then
If city = 'London' then comm := comm + .01
else comm: = comm - .01;
UPDATE Customers
SET rating = rating + 100
WHERE city = 'Rome';
UPDATE Customers
SET snum = 1004
WHERE snum = 1002;
16
INSERT INTO Multicust
SELECT *
FROM Salespeople
WHERE 1 < (SELECT COUNT (*)
FROM Customers
WHERE Customers.snum = Salespeople.snum);
DELETE FROM Customers
WHERE NOT EXISTS (SELECT *
FROM Orders
WHERE cnum = Customers.cnum);
UPDATE Salespeople
SET comm = comm + (comm * .2)
WHERE 3000 < (SELECT SUM (amt)
FROM Orders
WHERE snum = Salespeople.snum);
, , 1.0 (100%):
UPDATE Salespeople
SET comm = comm + (comm * .2)
WHERE 3000 < (SELECT SUM (amt)
FROM Orders
WHERE snum = Salespeople.snum)
AND comm + (comm * .2) < 1.0;
CREATE UNIQUE INDEX Combination ON Customers(snum, rating);
18
CREATE TABLE Orders
(onum integer NOT NULL PRIMARY KEY,
amt decimal,
odate date NOT NULL,
cnum integer NOT NULL,
snum integer NOT NULL,
UNIOUE (snum, cnum));
CREATE TABLE Orders
(onum integer NOT NULL UNIQUE,
amt decimal,
odate date NOT NULL,
cnum integer NOT NULL,
snum integer NOT NULL,
UNIQUE (snum, cnum));
.
CREATE TABLE Salespeople
(snum integer NOT NULL PRIMARY KEY,
sname char(15) CHECK (sname BETWEEN 'AA' AND 'MZ'),
city char(15),
comm decimal NOT NULL DEFAULT = .10);
CREATE TABLE Orders
(onum integer NOT NULL,
amt decimal,
odate date,
cnum integer NOT NULL,
snum integer NOT NULL,
CHECK ((cnum > snum) AND (onum > cnum)));
CREATE TABLE Orders
(onum integer NOT NULL,
amt decimal,
odate date,
cnum integer NOT NULL,
snum integer,
prev integer,
UNIQUE (cnum, onum),
FOREIGN KEY (cnum, prev) REFERENCES Orders (cnum,onum));
20
CREATE VIEW Highratings
AS SELECT *
FROM Customers
WHERE rating = (SELECT MAX (rating)
FROM Customers);
CREATE VIEW Citynumber
AS SELECT city, COUNT (DISTINCT snum)
FROM Salespeople
GROUP BY city;
CREATE VIEW Nameorders
AS SELECT sname, AVG (amt), SUM (amt)
FROM Salespeople, Orders
WHERE Salespeople.snum = Orders.snum
GROUP BY sname;
CREATE VIEW Multcustomers
AS SELECT *
FROM Salespeople a
WHERE 1 < (SELECT COUNT (*)
FROM Customers b
WHERE a.snum = b.snum);
21
#1 , DISTINCT.
#2 , , GROUP BY.
#3 , #1, .
CREATE VIEW Commissions
AS SELECT snum, comm
FROM Salespeople
WHERE comm BETWEEN .10 AND .20
WITH CHECK OPTION;
CREATE TABLE Orders
(onum integer NOT NULL PRIMARY KEY,
amt decimal,
odate date DEFAULT VALUE = CURDATE,
snum integer,
cnum integer);
CREATE VIEW Entryorders
AS SELECT onum, amt, snum, cnum
FROM Orders;
22
GRANT UPDATE (rating) ON Customers TO Janet;
GRANT SELECT ON Orders TO Stephen WITH GRANT OPTION;
REVOKE INSERT ON Salespeople FROM Claire;
1:
CREATE VIEW Jerrysview
AS SELECT *
FROM Customers
WHERE rating BETWEEN 100 AND 500
WITH CHECK OPTION;
2:
GRANT INSERT, UPDATE ON Jerrysview TO Jerry;
1:
CREATE VIEW Janetsview
AS SELECT *
FROM Customers
WHERE rating = (SELECT MIN (rating)
FROM Customers);
2:
GRANT SELECT ON Janetsview TO Janet;
23
CREATE DBSPACE Myspace
(pctindex 15,
pctfree 40);
CREATE SYNONYM Orders FOR Diane.Orders;
24
SELECT a.tname, a.owner, b.cname, b.datatype
FROM SYSTEMCATOLOG a, SYSTEMCOLUMNS b
WHERE a.tname = b.tname AND
a.owner = b.owner AND
a.numcolumns > 4;
: - , , a b . .
SELECT tname, synowner, COUNT (ALL synonym)
FROM SYTEMSYNONS
GROUP BY tname, synowner;
SELECT COUNT (*)
FROM SYSTEMCATALOG a
WHERE numcolumns/2 < (SELECT COUNT (DISTINCT cnumber)
FROM SYSTEMINDEXES b
WHERE a.owner = b.tabowner AND a.tname = b.tname);
EXEC SQL DECLARE Wrong_Orders AS CURSOR FOR
SELECT cnum, snum
FROM Orders a
WHERE snum <> (SELECT snum
FROM Customers b
WHERE a.cnum = b.cnum);
{ SQL . , . }
EXEC SQL DECLARE Cust_assigns AS CURSOR FOR
SELECT cnum, snum
FROM Customers;
{
snum }
begin { }
EXEC SQL OPEN CURSOR Wrong_Orders;
while SQLCODE = 0 do { , Wrong_Orders }
begin
EXEC SQL FETCH Wrong_Orders INTO (:cnum, :snum);
if SQLCODE = 0 then
begin { Wrong_Orders , }
EXEC SQL OPEN CURSOR Cust_Assigns;
repeat
EXEC SQL FETCH Cust_Assigns INTO (:custnum, :salesnum);
until :custnum = :cnum;
{ FETCH ... Cust_Assigns , cnum, Wrong_Orders }
EXEC SQL CLOSE CURSOR Cust_assigns;
{ . salesnum. }
EXEC SQL UPDATE Orders
SET snum = :salesnum
WHERE CURRENT OF Wrong_Orders;
end; { SQLCODE = 0}.
end; { SQLCODE ... }
EXEC SQL CLOSE CURSOR Wrong_Orders;
end; { }
, , , onum , Wrong_Orders. UPDATE WHERE onum =:ordernum ( ordernum ), WHERE CURRENT Of Wrong_Orders.
EXEC SQL DECLARE Wrong_Orders AS CURSOR FOR
SELECT onum, cnum, snum
FROM Orders a
WHERE snum <> (SELECT snum
FROM Customers b
WHERE a.cnum = b.cnum);
EXEC SQL DECLARE Cust_assigns AS CURSOR FOR
SELECT cnum, snum
FROM Customer
s;
begin { }
EXEC SQL OPEN CURSOR Wrong_Orders;
while SQLCODE = 0 do { Wrong_Orders }
begin
EXEC SQL FETCH Wrong_Orders INTO (:odernum, :cnum, :snum);
if SQLCODE = 0 then
begin
EXEC SQL OPEN CURSOR Cust_Assigns;
repeat
EXEC SQL FETCH Cust_Assigns INTO (:custnum, :salesnum);
until :custnum = :cnum;
EXEC SQL CLOSE CURSOR Cust_assigns;
EXEC SQL UPDATE Orders
SET snum = :salesnum WHERE CURRENT OF Wrong_Orders;
end; { If SQLCODE = 0 }
end; { While SQLCODE ... do }
EXEC SQL CLOSE CURSOR Wrong_Orders;
end; { }
EXEC SQL BEGIN DECLARE SECTION;
SQLCODE : integer;
newcity : packed array[1..12] of char;
commnull : boolean;
citynull : boolean;
response : char;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE CURSOR Salesperson AS
SELECT * FROM SALESPEOPLE;
begln { main program }
EXEC SQL OPEN CURSOR Salesperson;
EXEC SQL FETCH Salesperson INTO (:snum, :sname, :city, :i_cit, :comm, :i_com);
{ }
while SQLCODE = 0 do { . }
begin
if i_com < 0 then commnull: = true;
if i_cit < 0 then citynull: = true;
{ , NULLS.}
if citynull
then begin
write (' city ', snum,
' ? (Y/N)');
{ city NULL . }
read (response);
{ . }
end { citynull }
else begin { citynull }
if not commnull then
{ -NULL }
begin
if city='London' then comm:=comm*.02*.02
else comm:=comm+.02;
end;
{ - commnull, begin end . }
write (' city ', snum, '', city,
' ? (Y/N)');
{ : , , , . }
read (response);
{ , citynull . }
end; { citynull}
if response = 'Y' then
begin
write (' city:');
read (newcity);
if not commnull then
{ -NULL . }
case newcity of:
begin
'Barcelona' : comm:= comm + .01,
'San Jose' : comm:= comm * .01
end; { commnull}
EXEC SQL UPDATE Salespeople
SET city = :newcity, comm = :comm:i_com
WHERE CURRENT OF Salesperson;
{ NULL comm . }
end; { = 'Y', <> 'Y', . }
EXEC SQL FETCH Salesperson INTO (:snum, :sname, :city, :i_clt, :comm, :l_com);
{ }
end; { SQLCODE = 0}
EXEC SQL CLOSE CURSOR Salesperson;
end; { }
