“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

, . , , .

SQLの考え方

1.

2.

3.