Мартин Грубер - Понимание SQL

Глава 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.

1. 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.

1. 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.

1. SELECT * FROM Customers WHERE rating >=ANY

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

cnum cname city rating snum

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

Глава 20.

CREATE VIEW HighratingsAS 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)