.


:

:



,



( , )

:

































 

 

 

 
FAQ

indicator NULL SQL

⇐ 40414243444546474849 ⇒



, 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;

, "", , city = NULL. , .

INDICATOR

indicator NULL. UPDATE INSERT SELECT. indicator , NULL . , NULL city comm, , , indicator i_a i_b ; :

EXEC SQL INSERT INTO Salespeople
VALUES (:Id_num,:salesperson,:loc:i_a,:comm:i_b);

indicator , . SQL , . VARCHAR LONG ( C). , , . indicator, , , , , , .

, indicator > 0, < 0.

SQL , . SQL , . SQL , , , , , , . ANSI SQL : , , , PL/I. , . SQL, :

SQL EXEC SQL , .

, SQL, SQL , .

, SQL.

, INTO, , .

, . ( ), ( ), ( ). , FETCH , .

. , , (VIEW); , ORDER BY UNION, . .

, , UPDATE DELETE WHERE CURRENT OF. DELETE UPDATE , .

SQLCODE , SQL. SQL.

SQL , , SQLCODE = 100. , SQLCODE , . , SQLCODE = 0.

WHENEVER , , SQLCODE = 100 ( ) SQLCODE (SQLERROR). (GOTO <label>) - (). .

indicator. indicator SQL, () INDICATOR.

indicator = 0. SQL NULL , indicator, indicator . , NULL SQL .

indicator NULL SQL INSERT UPDATE. , , - , .

SQL

: , , . , , ( ). , , . , .

, , , -, , . , , ; , .

1. , snum cnum , , . , snum . , (ANSI , , , (.. , cnums , ). , , .

2. , ANSI , . ?

3. , city , .01 , .01 , -. , , , .02 , , , , , .02. , , , . , city comm NULL , , SQL. : .

(. A .)


A

1

cnum

rating

. .

, , .

2

( )

( DML)

SQL

3

SELECT onum, amt, odate
FROM Orders;

SELECT *
FROM Customers
WHERE snum = 1001;

SELECT city, sname, snum, comm
FROM Salespeople;

SELECT rating, cname
FROM Customers
WHERE city = 'SanJose';

SELECT DISTINCT snum
FROM Orders;

4

SELECT *
FROM Orders
WHERE amt > 1000;

SELECT sname, city
FROM Salespeople
WHERE city = 'London' AND comm >.10;

SELECT *
FROM Customers
WHERE rating > 100 OR city = 'Rome';

SELECT *
FROM Customers
WHERE NOT rating < = 100 OR city = 'Rome';

SELECT *
FROM Customers
WHERE NOT (rating < = 100 AND city < > 'Rome');

.

onumamtodatecnumsnum
3001 18.69 10/03/1990 2008 1007
3003 767.19 10/03/1990 2001 1001
3005 5160.45 10/03/1990 2003 1002
3009 1713.23 10/04/1990 2002 1003
3007 75.75 10/04/1990 2004 1002
3008 4723.00 10/05/1990 2006 1001
3010 1309.95 10/06/1990 2004 1002
3011 9891.88 10/06/1990 2006 1001

onumamtodatecnumsnum
3001 18.69 10/03/1990 2008 1007
3003 767.19 10/03/1990 2001 1001
3006 1098.16 10/03/1990 2008 1007
3009 1713.23 10/04/1990 2002 1003
3007 75.75 10/04/1990 2004 1002
3008 4723.00 10/05/1990 2006 1001
3010 1309.95 10/06/1990 2004 1002
3011 9891.88 10/06/1990 2006 1001

SELECT *
FROM Salespeople;

5

SELECT *
FROM Orders
WHERE odate IN (10/03/1990,10/04/1990);

SELECT *
FROM Orders
WHERE odate BETWEEN 10/03/1990 AND 10/04,1990;

SELECT *
FROM Customers
WHERE snum IN (1001,1004);

SELECT *
FROM Customers
WHERE cname BETWEEN 'A' AND 'H';

: ASCII Hoffman - H. G, Giovanni Grass. G Z, , , .

SELECT *
FROM Customers
WHERE cname LIKE 'C%';

SELECT *
FROM Orders
WHERE amt <> 0 AND (amt IS NOT NULL);

SELECT *
FROM Orders
WHERE NOT (amt = 0 OR amt IS NULL);

6

SELECT COUNT(*)
FROM Orders
WHERE odate = 10/03/1990;

SELECT COUNT (DISTINCT city)
FROM Customers;

SELECT cnum, MIN (amt)
FROM Orders
GROUP BY cnum;

SELECT MIN (cname)
FROM Customers
WHERE cname LIKE 'G%';

SELECT city, MAX (rating)
FROM Customers
GROUP BY city;

SELECT odate, count (DISTINCT snum)
FROM Orders
GROUP BY odate;

7

SELECT onum, snum, amt *.12
FROM Orders;

SELECT 'For the city ', city, ', the highest rating is ', MAX (rating)
FROM Customers
GROUP BY city;

SELECT rating, cname, cnum
FROM Customers
ORDER BY rating DESC;

SELECT odate, SUM (amt)
FROM Orders
GROUP BY odate
ORDER BY 2 DESC;

8

SELECT onum, cname
FROM Orders, Customers
WHERE Customers.cnum = Orders.cnum;

SELECT onum, cname, sname
FROM Orders, Customers, Salespeople
WHERE Customers.cnum = Orders.cnum AND Salespeople.snum = Orders.snum;

SELECT cname, sname, comm
FROM Salespeople, Customers
WHERE Salespeople.snum = Customers.snum AND comm *.12;

SELECT onum, comm * amt
FROM Salespeople, Orders, Customers
WHERE rating > 100 AND
Orders.cnum = Customers.cnum AND
Orders.snum = Salespeople.snum;

9

SELECT first.sname, second.sname
FROM Salespeople first, Salespeople second
WHERE first.city = second.city AND first.sname < second.sname;

.

SELECT cname, first.onum, second.onum
FROM Orders first, Orders second, Customers
WHERE first.cnum = second.cnum AND
first.cnum = Customers.cnum AND
first.onum < second.onum;

, .

SELECT a.cname, a.city
FROM Customers a, Customers b
WHERE a.rating = b.rating AND b.cnum = 2001;

10

SELECT *
FROM Orders
WHERE cnum = (SELECT cnum
FROM Customers
WHERE cname = 'Cisneros');

SELECT *
FROM Orders
WHERE cnum IN (SELECT cnum
FROM Customers
WHERE cname = 'Cisneros');

SELECT DISTINCT cname, rating
FROM Customers, Orders
WHERE amt > (SELECT AVG (amt)
FROM Orders)
AND Orders.cnum = Customers.cnum;

SELECT snum, SUM (amt)
FROM Orders
GROUP BY snum
HAVING SUM (amt) > (SELECT MAX (amt)
FROM Orders);

11

SELECT cnum, cname
FROM Customers outer
WHERE rating = (SELECT MAX (rating)
FROM Customers inner
WHERE inner.city = outer.city);

:

SELECT snum, sname
FROM Salespeople main
WHERE city IN (SELECT city
FROM Customers inner
WHERE inner.snum <> main.snum);

:

SELECT DISTINCT first.snum, sname
FROM Salespeople first, Customers second
WHERE first.city = second.city AND first.snum <> second.snum;

, , : - . . , city , snums . , , , . , .

12

SELECT *
FROM Salespeople first
WHERE EXISTS (SELECT *
FROM Customers second
WHERE first.snum = second.snum AND rating = 300);

SELECT a.snum, sname, a.city, comm
FROM Salespeople a, Customers b
WHERE a.snum = b.snum AND b.rating = 300;

SELECT *
FROM Salespeople a
WHERE EXISTS (SELECT *
FROM Customers b
WHERE b.city = a.city AND a.snum <> b.snum);

SELECT *
FROM Customers a
WHERE EXISTS (SELECT *
FROM Orders b
WHERE a.snum = b.snum AND a.cnum <> b.cnum)

13

SELECT *
FROM Customers
WHERE rating >= ANY (SELECT rating
FROM Customers
WHERE snum = 1002);

cnumcnamecityratingsnum
2002 Giovanni Rome 200 1003
2003 Liu San Jose 200 1002
2004 Grass Berlin 300 1002
2008 Cisneros SanJose 300 1007

SELECT *
FROM Salespeople
WHERE city <> ALL (SELECT city
FROM Customers);

SELECT *
FROM Salespeople
WHERE NOT city = ANY (SELECT city
FROM Customers);

SELECT *
FROM Orders
WHERE amt > ALL (SELECT amt
FROM Orders a, Customers b
WHERE a.cnum = b.cnum AND b.city = 'London');

SELECT *
FROM Orders
WHERE amt > (SELECT MAX (amt)
FROM Orders a, Customers b
WHERE a.cnum = b.cnum AND b.city = 'London');

14

SELECT cname, city, rating, 'High Rating'
FROM Customers
WHERE rating >= 200

UNION

SELECT cname, city, rating, ' Low Ratlng'
FROM Customers
WHERE rating < 200;

SELECT cname, city, rating, 'High Rating'
FROM Customers
WHERE rating >= 200

UNION

SELECT cname, city, rating, ' Low Rating'
FROM Customers
WHERE NOT rating >= 200;

. , "Low Rating" , "High Rating" .

SELECT cnum, cname
FROM Customers a
WHERE 1 < (SELECT COUNT (*)
FROM Orders b
WHERE a.cnum = b.cnum)

UNION

SELECT snum, sname
FROM Salespeople a
WHERE 1 < (SELECT COUNT (*)
FROM Orders b
WHERE a.snum = b.snum)

ORDER BY 2;

SELECT snum
FROM Salespeople
WHERE city = 'San Jose'

UNION

(SELECT cnum
FROM Customers
WHERE city = 'San Jose'

UNION ALL

SELECT onum
FROM Orders
WHERE odate = 10/03/1990);

15

INSERT INTO Salespeople (city, cname, comm, cnum)
VALUES ('San Jose', 'Blanco', NULL, 1100);

DELETE FROM Orders WHERE cnum = 2006;

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;

, .

17

CREATE TABLE Customers
(cnum integer,
cname char(10),
city char(10),
rating integer,
snum integer);

CREATE INDEX Datesearch ON Orders(odate);

( , .)

CREATE UNIQUE INDEX Onumkey ON Orders(onum);

CREATE INDEX Mydate ON Orders(snum, odate);

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)));

19

CREATE TABLE Cityorders
(onum integer NOT NULL PRIMARY KEY,
amt decimal,
cnum integer,
snum integer,
city char (15),
FOREIGN KEY (onum, amt, snum) REFERENCES Orders (onum, amt, snum),
FOREIGN KEY (cnum, city) REFERENCES Customers (cnum, city));

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);

25

EXEC SQL BEGIN DECLARE SECTION;
SQLCODE: integer;
{ }
cnum: integer;
snum: integer;
custnum: integer;
salesnum: integer;
EXEC SQL END DECLARE SECTION;

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 BEGIN DECLARE SECTION;
SQLCODE: integer;
odernum: integer;
cnum: integer;
snum: integer;
custnum: integer;
salesnum: integer;
EXEC SQL END DECLARE SECTION;

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 Customers;

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; { }


B

SQL

, SQL ANSI, , .

, .

( 10) .

, - .

, , .

, .

ANSI

ANSI ( ):

TEXT .

CHAR (CHARACTER) , . , . , , 'text'. ('') (').

: , , , , .

EXACT NUMERIC

DEC (DECIMAL) ; , . : . . , . . , , . . = 0 .

NUMERIC DECIMAL , .

INT (INTEGER) . DECIMAL, , , 0. ( , ).

SMALLINT INTEGER, , , , ( ) , INTEGER.

APPROXIMATE NUMERIC

FLOAT 10 . , .

REAL , FLOAT, , . .

DOUBLE PRECISION (DOUBLE) , REAL, , , DOUBLE PRECISION REAL.

⇐ 40414243444546474849 ⇒


:

: 2016-07-29; !; : 466 |

:

:

, .
==> ...

1856 - | 1655 -


© 2015-2024 lektsii.org - -

: 0.148 .