“SELECT * WHERE a=b FROM c



” “SELECT WHERE a=b FROM c ON *



” ?

, : SQL — , ( -!), - , .

, , , . :

SELECT members.firstname || ' ' || members.lastname
AS "Full Name"
FROM borrowings
INNER JOIN members
ON members.memberid=borrowings.memberid
INNER JOIN books
ON books.bookid=borrowings.bookid
WHERE borrowings.bookid IN (SELECT bookid
  FROM books
  WHERE stock>(SELECT avg(stock)
    FROM books))
GROUP BY members.firstname, members.lastname;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

! , , SQL . .

, , SQL , - SQL.

, SQL , PostgreSQL. MySQL .

1.

SQL , SELECT



, FROM



WHERE



. , , , , .

2.

, :

( )

. .

• "books" , , . .
• “members” — .
• “borrowings” . bookid
  
  
  
  “books”, memberid
  
  
  
  “members”. , .

3.

: (id) , “Dan Brown”

:

SELECT bookid AS "id", title
FROM books
WHERE author='Dan Brown';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

id	title
2	The Lost Symbol
4	Inferno

. , .

3.1 FROM —

, FROM



, .

FROM



, . ( ), , .

3.2 WHERE —

WHERE



- , . , author



— “Dan Brown”.

3.3 SELECT —

, , , . , SELECT



. AS



.

:

4. ()

( ) , , :

SELECT books.title AS "Title", borrowings.returndate AS "Return Date"
FROM borrowings JOIN books ON borrowings.bookid=books.bookid
WHERE books.author='Dan Brown';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

Title	Return Date
The Lost Symbol	2016-03-23 00:00:00
Inferno	2016-04-13 00:00:00
The Lost Symbol	2016-04-19 00:00:00

FROM



. , . “books”, “borrowings”. , .

borrowings JOIN books ON borrowings.bookid=books.bookid



— , , , "books" "borrowings", bookid



. :

, . , , . , « » 3.

.

, “Dan Brown”.

:

Step 1 — ? , “member” “books” “borrowings”. JOIN :

borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

.

2 — ? , — “Dan Brown”

WHERE books.author='Dan Brown'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

3 — ? , , , :

SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

! :

SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author='Dan Brown';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

First Name	Last Name
Mike	Willis
Ellen	Horton
Ellen	Horton

! ( ). .

5.

, . , .

, . , Ellen Horton , . :

SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name",
count(*) AS "Number of books borrowed"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author='Dan Brown'
GROUP BY members.firstname, members.lastname;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

First Name	Last Name	Number of books borrowed
Mike	Willis	1
Ellen	Horton	2

GROUP BY



. , , . ( ) , GROUP BY



. . count



, ( ). .

.

, GROUP BY



, . , SELECT



', , .

count



( ). sum



max



. , , , :

SELECT author, sum(stock)
FROM books
GROUP BY author;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

author	sum
Robin Sharma	4
Dan Brown	6
John Green	3
Amish Tripathi	2

sum



stock



.

6.

SQL-, . .

6.1

, . . , , . , , “Robin Sharma”, — :

SELECT *
FROM (
  SELECT author, sum(stock)
  FROM books
  GROUP BY author
) AS results
WHERE author='Robin Sharma';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

author	sum
Robin Sharma	4

6.2

, , , .

, , , . :

1. 3. :

SELECT author
FROM (
 SELECT author, sum(stock)
 FROM books
 GROUP BY author
) AS results
WHERE sum > 3;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

author
Robin Sharma
Dan Brown

: ['Robin Sharma', 'Dan Brown']

2. :

SELECT title, bookid
FROM books
WHERE author IN (
  SELECT author
  FROM (
    SELECT author, sum(stock)
    FROM books
    GROUP BY author
  ) AS results
  WHERE sum > 3);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

title	bookid
The Lost Symbol	2
Who Will Cry When You Die?	3
Inferno	4

, :

SELECT title, bookid
FROM books
WHERE author IN ('Robin Sharma', 'Dan Brown');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

6.3

, . , , , , . , .

, , , .

:

select avg(stock) from books;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

avg
3.000

3



.

, , :

SELECT *
FROM books
WHERE stock>(SELECT avg(stock) FROM books);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, :

SELECT *
FROM books
WHERE stock>3.000
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

bookid	title	author	published	stock
3	Who Will Cry When You Die?	Robin Sharma	2006-06-15 00:00:00	4

7.

, .

7.1 Update

UPDATE



. , SELECT



', SET



'.

, . :

UPDATE books
SET stock=0
WHERE author='Dan Brown';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

WHERE



, : . SELECT



, , SET



. , , .

7.2 Delete

DELETE



SELECT



UPDATE



. . SELECT



UPDATE



, WHERE



: , . , . , , , :

DELETE FROM books
WHERE author='Dan Brown';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

7.3 Insert

, , , INSERT



. :

INSERT INTO x
  (a,b,c)
VALUES
  (x, y, z);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

a



, b



, c



, x



, y



z



, , . , , .

. INSERT



, "books":

INSERT INTO books
  (bookid,title,author,published,stock)
VALUES
  (1,'Scion of Ikshvaku','Amish Tripathi','06-22-2015',2),
  (2,'The Lost Symbol','Dan Brown','07-22-2010',3),
  (3,'Who Will Cry When You Die?','Robin Sharma','06-15-2006',4),
  (4,'Inferno','Dan Brown','05-05-2014',3),
  (5,'The Fault in our Stars','John Green','01-03-2015',3);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

8.

, . . ? SELECT



, FROM



, WHERE



, GROUP BY



, .

:

SELECT members.firstname || ' ' || members.lastname AS "Full Name"

FROM borrowings
INNER JOIN members
ON members.memberid=borrowings.memberid
INNER JOIN books
ON books.bookid=borrowings.bookid

WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock>  (SELECT avg(stock) FROM books)  )

GROUP BY members.firstname, members.lastname;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, , .

:

Full Name
Lida Tyler

, . , , .