( .., ..)
, , . , , . , , . , , , .
SQL , .
, . 1, , . , , .
, , FROM , . , , . , , WHERE .
, . :
Salespeople.snum
Salespeople.city
Orders.odate
, , , SQL , , . , , . . , city.
(), Salespeople.city Customers.city, SQL .
, , , . . , ( 8.1):
SELECT Customers.cname, Salespeople.sname, Salespeople.city
FROM Salespeople, Customers
WHERE Salespeople.city = Customers.city;
=============== SQL Execution Log ============
| SELECT Customers.cname, Salespeople.sname, |
| Salespeople.city |
| FROM Salespeople, Customers |
| WHERE Salespeople.city = Customers.city |
| ============================================= |
| cname cname city |
| ------- -------- ---- |
| Hoffman Peel London |
| Clemens Peel London |
| Hoffman Motika London |
| Clemens Motika London |
| Liu Serres San Jose |
| Cisneros Serres San Jose |
=============================================
city , , . , , . , , , , , , .
SQL , , . , Peel , .
, city London, Peel . ( ).
. , . . snum. , 1. , . , , , :
SELECT Customers.cname, Salespeople.sname
FROM Customers, Salespeople
WHERE Salespeople.snum = Customers.snum;
8.2.
=============== SQL Execution Log ============
| 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
, , , snum . .
, ; snum, . , , .
, . , , WHERE (=). 'city='London' 'Salespeople.snum=Orders.snum' , .
, . , , . ( 8.3):
SELECT sname, cname
FROM Salespeople, Customers
WHERE sname < cname AND rating < 200;
=============== SQL Execution Log ============
| SELECT sname, cname |
| FROM Salespeople, Customers |
| WHERE sname < cname |
| AND rating < 200; |
| ============================================= |
| sname cname |
| -------- ------- |
| Peel Pereira |
| Motika Pereira |
| Axelrod Hoffman |
| Axelrod Clemens
. , , 200. , , , , , , , .
, . . ( 8.4):
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;
=============== SQL Execution Log ==============
| 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
, , , ( snum ), ( cnum snum ).
. , , . , , . : , . 9.
(. A .)
8 , .
, .
, . , , , .
, , , . , . , .
, . , SQL , .
, , , , .
, .
, , . , .
, , .
FROM . : , , .
, ( 9.1):
SELECT first.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating;
=============== SQL Execution Log ==============
| Giovanni Giovanni 200 |
| Giovanni Liu 200 |
| Liu Giovanni 200 |
| Liu Liu 200 |
| Grass Grass 300 |
| Grass Cisneros 300 |
| Clemens Hoffman 100 |
| Clemens Clemens 100 |
| Clemens Pereira 100 |
| Cisneros Grass 300 |
| Cisneros Cisneros 300 |
| Pereira Hoffman 100 |
| Pereira Clemens 100 |
| Pereira Pereira 100 |
===============================
. Interbase 'SECOND' , , , :
SELECT a.cname, b.cname, a.rating
FROM Customers a, Customers b
WHERE a.rating = b.rating;
, 9.1., , , , , .
, SQL , '' ''. , , , . FROM , .
, SELECT, FROM.
. SQL , , FROM .
, . , , , .
, , , SQL , .
, , . , , () .
, A B , A B . , Hoffman Clemens, Clemens Hoffman. Cisneros Grass, Liu Giovanni, . , , , Liu Liu.
, , , , . , , :
SELECT first.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating
AND first.cname < second.cname;
9.2.
=============== SQL Execution Log ==============
| SELECT first.cname, second.cname, first.rating |
| FROM Customers first, Customers second |
| WHERE first.rating = second.rating |
| AND first.cname < second.cname |
| =============================================== |
| cname cname rating |
| ------- --------- ------- |
| Hoffman Pereira 100 |
| Giovanni Liu 200 |
| Clemens Hoffman 100 |
| Clemens Pereira 100 |
| Cisneros Grass 300
Interbase 'second' - .
Hoffman Periera , . Periera Hoffman . , Hoffman , . , <= <.
, SQL . , , cnum snum .
, , , . :
SELECT first.onum, first.cnum, first.snum, second.onum, second.cnum, second.snum
FROM Orders first, Orders second
WHERE first.cnum = second.cnum
AND first.snum <> second.snum;
Interbase . .
, . , , , . , . , , cnum=2008 snum=1007, cnum. , - , snum, , . snum cnum , .
, , , , . , (. Interbase ). , , , a b, SELECT . ( 11).
, SELECT * .
, . , . , , , , .
( 9.3):
SELECT a.cnum, b.cnum, c.cnum
FROM Customers a, Customers b, Customers c
WHERE a.rating = 100 AND b.rating = 200 AND c.rating = 300;
=============== SQL Execution Log ==============
| cnum cnum cnum |
| ----- ------ ------ |
| 2001 2002 2004 |
| 2001 2002 2008 |
| 2001 2003 2004 |
| 2001 2003 2008 |
| 2006 2002 2004 |
| 2006 2002 2008 |
| 2006 2003 2004 |
| 2006 2003 2008 |
| 2007 2002 2004 |
| 2007 2002 2008 |
| 2007
, , 100, 200, 300. . GROUP BY ORDER BY, .
, FROM , SELECT. , , . , , Serres (snum 1002) ( 9.4):
SELECT b.cnum, b.cname
FROM Customers a, Customers b
WHERE a.snum = 1002
AND b.city = a.city;
=============== SQL Execution Log ============
| SELECT b.cnum, b.cname |
| FROM Customers a, Customers b |
| WHERE a.snum = 1002 |
| AND b.city = a.city; |
| ==============================================|
| cnum cname |
| ------ --------- |
| 2003 Liu |
| 2008 Cisneros |
| 2004 Grass |
=============================
a , snum = 1002. , , Serres. b , a; , b , a.
b a, . , Serres , , a . , Serres, Liu Grass. b (San Jose Berlin ) , , Liu Grass.
, . : . , ( 9.5):
SELECT sname, Salespeople.snum, first.cname, second.cname
FROM Customers first, Customers second, Salespeople
WHERE first.snum = second.snum
AND Salespeople.snum = first.snum
AND first.cnum < second.cnum;
=============== SQL Execution Log ==================
| SELECT cname, Salespeople.snum, first.cname |
| second.cname |
| FROM Customers first, Customers second, Salespeople |
| WHERE first.snum = second.snum |
| AND Salespeople.snum = first.snum |
| AND first.cnum < second.cnum; |
| ====================================================|
| cname snum cname cname |
|
. Interbase 'second' .
, , . . , ( , ). , , , .
, , , . SQL, 10 .
(. A .)
9, , . , ( ), , . , SQL, DISTINCT, . , HAVING , .
SQL . , , , , .
sname Motika, snum, . , ( 10.1):
SELECT *
FROM Orders
WHERE snum = (SELECT snum
FROM Salespeople
WHERE sname = 'Motika');
() , SQL ( ) WHERE. , , sname Motika, snum .
, , snum = 1004. SQL , , , snum = 1004
=============== SQL Execution Log ==============
| SELECT * |
| FROM Orders |
| WHERE snum = |
| (SELECT snum |
| FROM Salespeople |
| WHERE sname = 'Motika'); |
|=================================================|
| onum amt odate cnum snum |
| ----- ------- ---------- ----- ----- |
| 3002 1900.10 10/
. , , , . , , ( , snum), .
, Motika,
WHERE snum = 1004
, . , Motika , , .
, , . snum "WHERE city = London" "WHERE sname = Motika", . , .
( , 4), , , . , , , .
, ( ), , , . , , : ( 5 ).
, - :
SELECT *
FROM Orders
WHERE snum = (SELECT snum
FROM Salespeople
WHERE city = 'Barcelona');
Barcelona Rifkin, snum , , . . SQL , Barcelona , , .
DISTINCT, . , , Hoffman (cnum = 2001). , ( 10.2):
SELECT *
FROM Orders
WHERE snum = (SELECT DISTINCT snum
FROM Orders
WHERE cnum = 2001);
=============== SQL Execution Log ==============
| SELECT * |
| FROM Orders |
| WHERE snum = |
| (SELECT DISTINCT snum |
| FROM Orders |
| Where cnum = 2001); |
| =============================================== |
| onum amt odate cnum snum |
| ----- --------- --------- ------ ------- |
| 3003 767.19 10/03/1990 2001 1001 |
| 3008 4723.00 10
, snum Hoffman 1001, snum ( , Hoffman ). , , cnum snum. , , ( ) snum cnum. DISTINCT . , .
, , . cnum ( . 19) , , . , . , (SQL , , , - ; 22).
, , , , , , , . , .
, , ,
< > <> <>,
<> <> < > ,
<> <> <>.
, :
SELECT *
FROM Orders
WHERE (SELECT DISTINCT snum
FROM Orders
WHERE cnum = 2001) = snum;
ANSI , , ( , Interbase ). ANSI , .
, , , . , GROUP BY, . , , 4- ( 10.3):
SELECT *
FROM Orders
WHERE amt > (SELECT AVG (amt)
FROM Orders
WHERE odate = 10/04/1990);
Interbase :
SELECT *
FROM Orders
WHERE amt > (SELECT AVG (amt)
FROM Orders
WHERE odate = CAST('10/04/1990' AS DATE));
=============== SQL Execution Log ==============
| SELECT * |
| FROM Orders |
| WHERE amt > |
| (SELECT AVG (amt) |
| FROM Orders |
| WHERE odate = 01/04/1990); |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ----- |
| 3002 1900.10 10/03/1990 2007 1004 |
| 3005 5160,45 10/03/1990 2003 1002 |
| 3006 1098.19 10/03/1990 2008 1007 |
| 3009 1713.23 10/04/1990 2002 1003 |
| 3008 4723.00 10/05/1990 2006 1001 |
| 3010 1309.95 10/06/1990 2004 1002 |
| 3011 9891.88 10/06/1990 2006 1001 |
=======================================
4 1788.98 (1713.23 + 75.75) , = 894.49. amt .
, , , GROUP BY, . , , . GROUP BY HAVING , , . WHERE, . , ,
SELECT AVG (comm)
FROM Salespeople
GROUP BY city
HAVING city = 'London;
! , .
SELECT AVG (comm)
FROM Salespeople
WHERE city = 'London';
, , IN ( BETWEEN, LIKE, IS NULL ). , IN , , . IN , SQL . , , IN, , , ( 10.4):
SELECT *
FROM Orders
WHERE snum IN (SELECT snum
FROM Salespeople
WHERE city = 'London');
=============== SQL Execution Log ==============
| SELECT * |
| FROM Orders |
| WHERE snum IN |
| (SELECT snum |
| FROM Salespeople |
| WHERE city = 'London'); |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ------ |
| 3003 767.19 10/03/1990 2001 1001 |
| 3002 1900.10 10/03/1990 2007 1004 |
| 3006 1098.19 10/03/1990 2008 1007 |
| 3008 4723.00 10/05/1990 2006 1001 |
| 3011 9891.88 10/06/1990 2006 1001 |
===========================================
, , , , , , :
SELECT onum, amt, odate, cnum, Orders.snum
FROM Orders, Salespeople
WHERE Orders.snum = Salespeople.snum
AND Salespeople.city = 'London';
, , SQL .
snum city = 'London', , . snum=1001 snum=1004. , snum .
, , . , , .
, , , , , . , .
, IN, , . , (=), IN. , IN , . .
; , , . . , , :
SELECT onum, amt, odate
FROM Orders
WHERE snum = (SELECT snum
FROM Orders
WHERE cnum = 2001);
DISTINCT, IN (=), :
SELECT onum, amt, odate
FROM Orders
WHERE snum IN (SELECT snum
FROM Orders
WHERE cnum = 2001);
, ? , IN, . , , , . , (=), .
, , , . , , .
, , ( ) , =. IN , , .
, :
SELECT comm
FROM Salespeople
WHERE snum IN (SELECT snum
FROM Customers
WHERE city = 'London');
, 10.5, Peel (snum = 1001), . . , - . , IN .
=============== SQL Execution Log ==============
| SELECT comm |
| FROM Salespeople |
| WHERE snum IN |
| (SELECT snum |
| FROM Customers |
| WHERE city = 'London'); |
| =============================================== |
| comm |
| ------- |
|
, city , city .
SQL , FROM . , . , "city" WHERE , Customer.city ( city ).
FROM , SQL , . , , . , , .
, . , . , SELECT * . , EXISTS, 12.
, , , SELECT . , IN. , = ( 10.6):
SELECT *
FROM Customers
WHERE cnum = (SELECT snum + 1000
FROM Salespeople
WHERE sname = 'Serres');
=============== SQL Execution Log ============
| SELECT * |
| FROM Customers |
| WHERE cnum = |
| (SELECT snum + 1000 |
| WHERE Salespeople |
| WHERE sname = 'Serres' |
| ============================================= |
| cnum cname city rating snum |
| --
, cnum 1000, snum Serres. , sname ( UNIQUE INDEX, 17, UNIQUE, 18), . snum num , , .
HAVING. , GROUP BY HAVING. ( 10.7):
SELECT rating, COUNT (DISTINCT cnum)
FROM Customers
GROUP BY rating
HAVING rating > (SELECT AVG (rating)
FROM Customers
WHERE city = 'San Jose');
=============== SQL Execution Log ============
| SELECT rating,count (DISTINCT cnum) |
| FROM Customers |
| GROUP BY rating |
| HAVING rating > |
| (SELECT AVG (rating) |
| FROM Customers |
| WHERE city = 'San Jose' |
|===============================================|
| rating |
| -------- -------- |
| 300 2
San Jose. 300, , .
. , , . , IN, WHERE, HAVING .
, . 11, , . , 12 13, , , IN, , .
(. A .)
![]() | 10.11.2021 - 12:37: - Personalias -> WHO IS WHO - - _. 10.11.2021 - 12:36: - Conscience -> . ? - _. 10.11.2021 - 12:36: , , - Upbringing, Inlightening, Education -> ... - _. 10.11.2021 - 12:35: - Ecology -> - _. 10.11.2021 - 12:34: , - War, Politics and Science -> - _. 10.11.2021 - 12:34: , - War, Politics and Science -> . - _. 10.11.2021 - 12:34: , , - Upbringing, Inlightening, Education -> , - _. 10.11.2021 - 09:18: - New Technologies -> , 5G- - _. 10.11.2021 - 09:18: - Ecology -> - _. 10.11.2021 - 09:16: - Ecology -> - _. 10.11.2021 - 09:15: , , - Upbringing, Inlightening, Education -> - _. 10.11.2021 - 09:13: , , - Upbringing, Inlightening, Education -> - _. |
|
![]() |