2018-02-13
697
Дать содержательную интерпретацию SQL-запросам, выполнить их на SQL-сервере с использованием клиентских утилит ISQL/w или SQL-EM, дать содержательную интерпретацию результатам выполнения SQL-запросов.
1) SELECT au_lname, au_fname
FROM authors
2) SELECT au_lname, au_fname
FROM authors
ORDER BY au_lname
3) SELECT au_lname, au_fname
FROM authors
ORDER BY au_lname, au_fname
4) SELECT title_id, price, ytd_sales,
price*ytd_sales "ytd dollar sales"
FROM titles
ORDER BY price*ytd_sales
5) SELECT title_id, price, ytd_sales,
price*ytd_sales "ytd dollar sales"
FROM titles
ORDER BY price*ytd_sales DESC
6) SELECT title_id, type, ytd_sales
FROM titles
ORDER BY type ASC, ytd_sales DESC
7) SELECT AVG(price)
FROM titles
8) SELECT DISTINCT type
FROM titles
ORDER BY type ACS
9) SELECT DISTINCT city
FROM authors
ORDER BY city DESC
10) SELECT DISTINCT state
FROM authors
ORDER BY state
11) SELECT DISTINCT country
FROM publishers
ORDER BY country DESC
12) SELECT AVG(price), AVG(DISTINCT price)
FROM titles
13) SELECT *
FROM titles
14) SELECT au_lname, au_fname
FROM authors
WHERE state= "CA"
15) SELECT type, title_id, price
FROM titles
WHERE price*ytd_sales < advance
16) SELECT au_id, city, state
FROM authors
WHERE state= "CA" OR city= "Palo Alto"
17) SELECT title_id, price
FROM titles
WHERE price between $5 AND $15
18) SELECT title_id, price
FROM titles
WHERE type IN ("mod_cook", "trad_cook", "business")
19) SELECT au_lname, au_fname, city, state
FROM authors
|
|
|
WHERE city like "San%"
20) SELECT type, title_id, price
FROM titles
WHERE title_id like "B_2075"
21) SELECT type, title_id, price
FROM titles
WHERE title_id like "B[AUN]7832"
22) SELECT AVG(price) "AVG"
FROM titles
WHERE type= "business"
23) SELECT AVG(price) "avg" SUM(price) "sum"
FROM titles
WHERE type IN ("business", "mod_cook")
24) SELECT COUNT(*)
FROM authors
WHERE state= "CA"
25) SELECT COUNT(*)
FROM titles
WHERE LIKE "Co%s"
26) SELECT title
FROM titles
WHERE ytd_sales IS NULL
27) SELECT au_lname "Фамилия”, au_fname "Имя”
FROM authors
WHERE contract=1 AND phone LIKE "408____-__2_"
28) SELECT phone
FROM authors
WHERE address LIKE "%Broadway Av.%"
29) SELECT title, pubdate
FROM titles
WHERE pubdate>= "Jun 9 1991 12:00AM"
AND pubdate< "6/16/91"
30) SELECT type, AVG(price) "avg", SUM(price) "sum"
FROM titles
WHERE type IN ("business", "psychology")
GROUP BY type
31) SELECT type, pub_id, AVG(price) "avg", SUM(price) "sum"
FROM titles
WHERE type IN ("business", "mod_cook")
GROUP BY type, pub_id
32) SELECT type, AVG(price)
FROM titles
WHERE price>$11
GROUP BY type
HAVING AVG(price)>$19.7
33) SELECT au_id, COUNT(*)
FROM authors
GROUP BY au_id
HAVING COUNT(*)>1
34) SELECT type, MIN(price), MAX(price)
FROM titles
GROP BY type
ORDER BY type
35) SELECT type, MIN(price), MAX(price)
FROM titles
GROUP BY type
HAVING MAX(price)-MIN(price)>=3
36) SELECT state, COUNT(DISTINCT pub_id)
FROM publishers
GROUP BY state
37) SELECT pub_name, AVG(price) "avg",
COUNT(DISTINCT title_id) "count"
FROM titles t JOIN publishers p ON t.pub_id=p.pub_id
GROUP BY pub_name
38) SELECT type, (MIN(price)+MIN(price))/2, AVG(price)
FROM titles
GROUP BY type
HAVING type<> "UNDECIDED"
ORDER BY 2 DESC
39) SELECT type, MIN(pubdate), MAX(pubdate)
FROM titles
GROUP BY type
40) SELECT title, pub_name
FROM titles CROSS JOIN publishers
41) SELECT *
FROM titles, publishers
42) SELECT title, pub_name
FROM titles, publishers
WHERE titles.pub_id=publishers.pub_id
43) SELECT title, pub_name
FROM titles JOIN publishers
ON titles.pub_id=publishers.pub_id
44) SELECT *
FROM titles t, publishers p
WHERE t.pub_id=p.pub_id
45) SELECT t.*, pub_name
FROM titles t, publishers p
WHERE t.pub_id=p.pub_id
46) SELECT a.city, a.state
FROM authors a, publishers p
WHERE a.city=p.city AND a.state=p.state
47) SELECT au_lname, au_fname
FROM authors a JOIN titleauthor ON a.au_id=ta.au_id
JOIN titles t ON ta.title_id=t.title_id
|
|
|
WHERE au_lname LIKE "R%"
AND state IN ("CA", "TX", "NY", "OR", "UT")
AND (title LIKE "_h_ %" OR title LIKE "% _h_ %"
OR title LIKE "% _h_")
48) SELECT title, type
FROM authors a, titles t, titleauthor ta, publishers p
WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id
AND t.pub_id=p.pub_id AND p.city=a.city
49) SELECT au_lname, au_fname, title
FROM authors a, titles t, titleauthor ta, publishers p
WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id
AND t.pub_id=p.pub_id
AND ((p.country= ‘USA’ AND t.type=’popular_comp’)
OR (p.country=’France’ AND t.type=’psychology’))
50) SELECT au_lname, au_fname, city
FROM authors a, titles t, titleauthor ta
WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id
AND (city LIKE "[CPR]%" OR city LIKE "%San%")
AND (title LIKE "% the %" OR title LIKE "The %"
OR title LIKE "% a %" OR title LIKE "A %")
51) SELECT DISTINCT au_lname, au_fname
FROM authors a JOIN titleauthor ta ON a.au_id=ta.au_id
JOIN titles t ON ta.title_id=t.title_id
JOIN publishers p ON p.pub_id=t.pub_id
WHERE p.state= "CA"
ORDER BY au_lname, au_fname
52) SELECT pub_name
FROM publishers p JOIN titles t ON p.pub_id=t.pub_id WHERE $15>price AND type= "psychology"
ORDER BY pub_name
53) SELECT pub_name, AVG(price)
FROM titles t, publishers p
WHERE t.pub_id=p.pub_id
GROUP BY pub_name
54) SELECT pub_name, AVG(price)
FROM titles t JOIN publishers p ON t.pub_id=p.pub_id
GROUP BY pub_name
55) SELECT au_lname, au_fname, title
FROM authors a, titles t, titleauthor ta
WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id
AND type= "popular_comp"
56) SELECT au_lname, au_fname, title
FROM authors a JOIN titleauthor ta ON a.au_id=ta.au_id
JOIN titles t ON ta.title_id=t.title_id
WHERE type= "psychology"
57) SELECT au_lname, au_fname, pub_name, COUNT(*)
FROM authors a, titles t, titleauthor ta, publishers p
WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id
AND t.pub_id=p.pub_id
GROUP BY au_lname, au_fname, pub_name
58) SELECT MIN(price)
FROM titles t, publishers p
WHERE t.pub_id=p.pub_id
GROUP BY country
HAVING country=’USA’
59) SELECT pub_name, COUNT(*)
FROM titles t, publishers p
WHERE t.pub_id=p.pub_id
AND (type= ‘mod_cook’ OR type=’trad_cook’)
GROUP BY pub_name
60) SELECT pub_name, COUNT(*)
FROM publishers p, titles t
WHERE p.pub_id=t.pub_id AND price>$15
GROUP BY pub_name
ORDER BY pub_name DESC
61) SELECT title, COUNT(DISTINCT a.au_id)
FROM titles t JOIN titleauthor ta ON t.title_id=ta.title_id
JOIN authors a ON ta.au_id=a.au_id
JOIN publishers p ON p.pub_id=t.pub_id
GROUP BY title
62) SELECT state, COUNT(DISTINCT p.pub_id)
FROM publishers p JOIN titles t ON p.pub_id=t.pub_id
GROUP BY state
63) SELECT title
FROM titles
WHERE pub_id=
(SELECT pub_id
FROM publishers
WHERE pub_name= "Binnet & Hardley")
64) SELECT pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type= "business")
65) SELECT pub_name
FROM publishers p
WHERE EXISTS
(SELECT *
FROM titles t
WHERE p.pub_id=t.pub_id
AND type="popular_comp")
66) SELECT pub_name
FROM publishers p
WHERE NOT EXISTS
(SELECT *
FROM titles t
WHERE p.pub_id=t.pub_id
AND type="mod_cook")
67) SELECT pub_name
FROM publishers
WHERE pub_id NOT IN
(SELECT pub_id
FROM titles
WHERE type="psychology")
68) SELECT type, price
FROM titles
WHERE price < (SELECT AVG(price) FROM titles)
69) SELECT type, AVG(price)
FROM titles
GROUP BY type
HAVING AVG(price) < (SELECT AVG(price) FROM titles)
70) SELECT DISTINCT a.city, a.state
FROM authors a
WHERE NOT EXISTS
(SELECT *
FROM publishers p
WHERE a.city=p.city AND a.state=p.state)
71) SELECT DISTINCT p.city, p.state
FROM publishers p
WHERE NOT EXISTS
(SELECT *
FROM authors a
WHERE p.city=a.city AND p.state=a.state)
72) SELECT MIN(price)
FROM titles t
WHERE t.pub_id IN
(SELECT pub_id
FROM publishers
WHERE country=’USA’)
73) SELECT title, type, price
FROM titles
WHERE price>ALL
(SELECT price
FROM titles
WHERE type= "psychology")
74) SELECT COUNT(DISTINCT city)
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type= "psychology")
75) SELECT pub_name
FROM publishers p
WHERE 15>SOME
(SELECT price
FROM titles t
WHERE p.pub_id=t.pub_id
AND type= "trad_cook")
76) SELECT pub_name, state
FROM publishers
WHERE pub_id NOT IN
(SELECT pub_id
FROM titles)
77) SELECT title
FROM titles
WHERE pub_id NOT IN
(SELECT pub_id
FROM publishers)
78) SELECT title
FROM titles t
WHERE price>=
(SELECT AVG(price)
FROM titles tt, publishers pp
GROUP BY pub_id
HAVING t.pub_id=pp.pub_id)
79) SELECT au_lname, au_fname, price
FROM authors a, titles t, titleauthor ta, publishers p
WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id
AND t.pub_id=p.pub_id AND country=’USA’
AND price=
(SELECT MIN(price)
FROM titles tt, publishers pp
WHERE tt.pub_id=pp.pub_id
GROUP BY country
HAVING country=’USA’)
80) SELECT DISTINCT au_lname, au_fname
FROM authors a, titles t, titleauthor ta
WHERE a.au_id=ta.au_id AND ta.title_id IN
(SELECT title_id
FROM titles
WHERE ytd_sales=
(SELECT MAX(ytd_sales)
FROM titles))
81) SELECT DISTINCT a.city, a.state
FROM authors a
WHERE NOT EXISTS
(SELECT *
FROM publishers p
WHERE a.city=p.city AND a.state=p.state)
UNION SELECT DISTINCT p.city, p.state
FROM publishers p
WHERE NOT EXISTS
(SELECT *
FROM authors a
WHERE p.city=a.city AND p.state=a.state)
82) SELECT title, price
FROM titles t JOIN publishers p ON t.pub_id=p.pub_id
WHERE p.country= "USA" AND t.price=
(SELECT MAX(price)
FROM titles tt JOIN publishers pp
ON tt.pub_id=pp.pub_id
WHERE country= "USA")
83) SELECT pub_name, COUNT(*)
FROM titles t, publishers p
WHERE t.pub_id=p.pub_id
GROUP BY pub_name
HAVING COUNT(*)>=ALL
(SELECT COUNT(*)
FROM titles tt, publishers pp
WHERE tt.pub.id=pp.pub_id
GROUP BY pub_name)
84) SELECT pub_name, city, state, country
FROM publishers p
WHERE EXISTS
(SELECT *
FROM titles t
WHERE t.pub_id=p.pub_id)
AND 20>ALL
(SELECT price
FROM titles t
WHERE t.pub_id=p.pub_id
AND price IS NOT NULL)
85) SELECT state, SUM(price)
|
|
|
FROM titles t, publishers p
WHERE t.pub_id=p.pub_id
GROUP BY state
HAVING state NOT IN ("TN", "MA", "TX")
AND SUM(price)>
(SELECT SUM(price)
FROM titles tt, publishers pp
WHERE tt.pub.id=pp.pub_id
AND pp.city= "Boston")
86) SELECT pub_name, MIN(price)
FROM titles t, publishers p
WHERE t.pub_id=p.pub_id
GROUP BY pub_name
HAVING MIN(price)>=ALL
(SELECT MIN(price)
FROM titles tt JOIN publishers pp
ON tt.pub_id=pp.pub_id
GROUP BY pub_name)
87) SELECT *
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type= "psychology" AND pub_id IN
(SELECT pub_id
FROM publishers
WHERE country= "USA"
AND state<> "CA")
88) SELECT au_lname, au_fname
FROM authors a
WHERE a.au_id IN
(SELECT au_id
FROM titleauthor ta
WHERE ta.title_id IN
(SELECT title_id
FROM titles t
WHERE "CA"=SOME
(SELECT state
FROM publishers p
WHERE p.pub_id=t.pub_id)))
ORDER BY au_lname, au_fname
89) SELECT state, COUNT(*)
FROM publishers p
WHERE EXISTS
(SELECT *
FROM titles t
WHERE p.pub_id=t.pub_id)
AND $22>ALL
(SELECT price
FROM titles t
WHERE p.pub_id=t.pub_id
AND price IS NOT NULL)
GROUP BY state
ORDER BY state ASC
90) SELECT state
FROM publishers p1
GROUP BY state
HAVING COUNT(DISTINCT pub_name)=
(SELECT COUNT(*)
FROM publishers p2
WHERE EXISTS
(SELECT *
FROM titles t
WHERE p2.pub_id=t.pub_id)
AND $22.5>ALL
(SELECT price
FROM titles t
WHERE p2.pub_id=t.pub_id
AND price IS NOT NULL)
GROUP BY state
HAVING p1.state=p2.state)
91) SELECT p1.pub_id
FROM titles t1, publishers p1
WHERE t1.pub_id=p1.pub_id
GROUP BY p1.pub_id
HAVING COUNT(DISTINCT title)=
(SELECT COUNT(*)
FROM titles t2
WHERE t2.pub_id=p1.pub_id
AND EXISTS
(SELECT *
FROM titleauthor ta3, authors a3
WHERE ta3.au_id=a3.au_id
AND ta3.title_id=t2.title_id
AND a3.state IN
(SELECT state
FROM publishers p4
WHERE "business"=SOME
(SELECT type
FROM titles t5
WHERE p4.pub_id=
t5.pub_id))))
92) SELECT city, state
FROM authors
UNION SELECT city, state
FROM publishers
ORDER BY state, sity
93) SELECT city
FROM authors
UNION SELECT city
FROM publishers
94) SELECT state
FROM authors
UNION SELECT state
FROM publishers
95) SELECT city, state
FROM authors
WHERE state IS NOT NULL
UNION SELECT city, state
FROM publishers
WHERE state IS NOT NULL
ORDER BY city DESC, state ASC
96) SELECT state, MIN(price), MAX(price), AVG(price)
FROM authors a, titles t, titleauthor ta
WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id
GROUP BY state
HAVING state<> "CA"
