МОДИФИЦИРОВАНИЕ ПРЕДСТАВЛЕНИЙ

Дата добавления: 2013-12-23; просмотров: 573; Нарушение авторских прав

ЗАПРОСЫ ПО НЕСКОЛЬКИМ ТАБЛИЦАМ

ИМЕНА ТАБЛИЦ И СТОЛБЦОВ

Salespeople.snum

Salespeople.city

Orders.odate

СОЗДАНИЕ ОБЬЕДИНЕНИЯ

SELECT Customers.cname, Salespeople.sname,

Salespeople.city

FROM Salespeople, Customers

WHERE Salespeople.city = Customers.city

=========================

cname cname city

------- -------- ----

Hoffman Peel London

Liu Serres San Jose

Cisneros Serres San Jose

Hoffman Motika London

Clemens Motika London

===========================

Þ ОБЪЕДИНЕНИЕ ТАБЛИЦ ЧЕРЕЗ СПРАВОЧНУЮ ЦЕЛОСТНОСТЬ

SELECT Customers.cname, Salespeople.sname,

FROM Salespeople, Customers

WHERE Salespeople.snum = Customers.snum

==================

cname sname

------- --------

Hoffman Peel

Giovanni Axelrod

Liu Serres

Grass Serres

Clemens Peel

Cisneros Rifkin

Pereira Motika

===================

Þ ОБЪЕДИНЕНИЕ, ОСНОВАННОЕ НА НЕРАВЕНСТВЕ

SELECT sname, cname

FROM Salespeople, Customers

WHERE sname < cname AND rating < 200;

==============

sname cname

-------- -------

Peel Pereira

Motika Pereira

Axelrod Hoffman

Axelrod Clemens

Axelrod Pereira

===============

* ОБЪЕДИНЕНИЕ БОЛЕЕ ДВУХ ТАБЛИЦ

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;

=========================

onum cname cnum snum

------ ------- ----- -----

3001 Cisneros 2008 1007

3002 Pereira 2007 1004

3006 Cisneros 2008 1007

3009 Giovanni 2002 1003

3007 Grass 2004 1002

3010 Grass 2004 1002

=========================

DELETE

INSERT

UPDATE

UPDATE Salesown SET city = 'Palo Alto' WHERE snum = 1004;

UPDATE Salesown SET comm = 0.20 WHERE snum = 1004;

CREATE VIEW Highratings AS SELECT cnum, rating FROM Customers WHERE rating = 300 WITH CHECK OPTION; CREATE VIEW Myratings AS SELECT * FROM Highratings; UPDATE Myratings SET rating = 200 WHERE cnum = 2004; CREATE VIEW Londonstaff AS SELECT * FROM Salespeople WHERE city = 'London'; SELECT * FROM Londonstaff WHERE comm > 0.12;

SELECT * FROM Salespeople WHERE city = 'London' AND comm > 0.12;

CREATE VIEW Ratingcount (rating, number) AS SELECT rating, COUNT (*) FROM Customers GROUP BY rating; SELECT * FROM Ratingcount WHERE number = 3; SELECT rating, COUNT (*) FROM Customers WHERE COUNT (*) = 3 GROUP BY rating; SELECT rating, COUNT (*) FROM Customers GROUP BY rating; HAVING COUNT (*) = 3; CREATE VIEW Totalforday AS SELECT odate, COUNT (DISTINCT cnum), COUNT (DISTINCT snum), COUNT (onum), AVG (amt), SUM (amt) FROM Orders GROUP BY odate;

SELECT * FROM Totalforday;

CREATE VIEW Nameorders AS SELECT onum, amt, a.snum, sname, cname FROM Orders a, Customers b, Salespeople c WHERE a.cnum = b.cnum AND a.snum = c.snum;

SELECT * FROM Nameorders WHERE sname = 'Rifkin'; ======================================onum amt snum sname cname----- ------ ----- ------ -------3001 18.69 1007 Rifkin Cisneros 3006 1098.16 1007 Rifkin Cisneros ======================================

SELECT a.sname, cname, amt * comm FROM Nameorders a, Salespeople b WHERE a.sname = 'Axelrod' AND b.snum = a.snum; ====================================== onum amt snum sname cname ----- ------ ----- ------- ----- 3001 18.69 1007 Rifkin Cisneros 3006 1098.16 1007 Rifkin Cisneros ======================================

CREATE VIEW Elitesalesforce AS SELECT b.odate, a.snum, a.sname, FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt) FROM Orders c WHERE c.odate = b.odate);

CREATE VIEW Bonus AS SELECT DISTINCT snum, sname FROM Elitesalesforce a WHERE 10 < = (SELECT COUNT (*) FROM Elitesalestorce b WHERE a.snum = b.snum); SELECT * FROM Bonus;

<== предыдущая лекция	\|	следующая лекция ==>
Комбинация текста, значений поля, и функций	\|	Дизайн упаковки