Лабораторные задания типа А
Дать содержательную интерпретацию SQL-запросам и результатам выполнения SQL-запросов. 1) SELECT aujname, au_fname
FROM authors 2) SELECT aujname, au_fname
FROM authors
ORDER BY aujname 3) SELECT aujname, au_fname
FROM authors
ORDER BY aujname, au_fname 4) SELECT titleJd, price, ytd_sales, price*ytd_sales "ytd dollar sales" FROM titles ORDER BY price*ytd_sales 5) SELECT title Jd, price, ytd_sales, price*ytd_sales "ytd dollar sales" FROM titles ORDER BY price*ytd_sales DESC 6) SELECT title Jd, 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 aujname, au_fname
FROM authors
WHERE state="CA" 15)SELECT type, titlejd, price
FROM titles
WHERE price*ytd_sales < advance 16)SELECT aujd, city, state
FROM authors
WHERE state= "CA" OR city= "Palo Alto" 17)SELECT titlejd, price
FROM titles
WHERE price between $5 AND $15 18)SELECT titlejd, price
FROM titles
WHERE type IN ("mod_cook", "trad_cook", "business") 19)SELECT aujname, au_fname, city, state
FROM authors
WHERE city like "San%" 20)SELECT type, titlejd, price
FROM titles
WHERE titlejd like "B_2075" 21)SELECT type, titlejd, price
FROM titles
WHERE titlejd 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 aujname "Фамилия", au_fname "Имя"
FROM authors
WHERE contracts 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, pubjd, AVG(price) "avg", SUM(price) "sum"
FROM titles
WHERE type IN ("business", "mod_cook") GROUP BY type, pubjd 32)SELECT type, AVG(price)
FROM titles
WHERE price>$11 GROUP BY type HAVING AVG(price)>$19.7 33)SELECT aujd, COUNT(*)
FROM authors
GROUP BY aujd HAVING COUNT(*)>1 34)SELECT type, MIN(price), MAX(price)
FROM titles
GROUP BYtype 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 pubjd)
FROM publishers
GROUP BY state 37)SELECT pub_name, AVG(price) "avg",
COUNT(DISTINCT titlejd) "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"
ORDERBY2DESC 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 aujname, 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 aujname 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 aujname, au_fname, title
FROM authors a, titles t, titleauthor ta, publishers p
WHERE ta.titlejd=t.titlejd AND a.aujd=ta.aujd AND t.pubjd=p.pubjd AND ((p.country= 'USA' AND t.type='popular_comp') OR (p.country='France' AND t.type='psychology')) 50)SELECT aujname, au_fname, city
FROM authors a, titles t, titleauthor ta
WHERE ta.titlejd=t.titlejd AND a.aujd=ta.aujd 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 aujname, aujname
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 aujname, 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 aujname, au_fname, title
FROM authors a, titles t, titleauthor ta
WHERE ta.titlejd=t.titlejd AND a.aujd=ta.aujd AND type= "popular_comp" 56)SELECT aujname, au_fname, title
FROM authors a JOIN titleauthor ta ON a.aujd=ta.aujd
JOIN titles t ON ta.title_id=t.titlejd
WHERE type= "psychology" 57)SELECT aujname, 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 aujname, 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.pubjd)
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 pubjd IN (SELECT pubjd
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 pubjd NOT IN (SELECT pubjd
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.pubjd 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 pubjd IN (SELECT pubjd
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 ANDtype="trad_cook") 76)SELECT pub_name, state
FROM publishers
WHERE pubjd NOT IN (SELECT pubjd
FROM titles) 77)SELECT title
FROM titles
WHERE pubjd NOT IN (SELECT pubjd
FROM publishers) 78)SELECT title
FROM titles t
WHERE price>= (SELECT AVG(price)
FROM titles tt, publishers pp
GROUP BY pubjd
HAVING t.pub_id=pp.pub_id) 79)SELECT aujname, 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 aujname, aujtiame
FROM authors a, titles t, titleauthor ta
WHERE a.au_id=ta.au_id AND ta.title_id IN (SELECT titlejd
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 pubjd IN (SELECT pubjd FROM titles
WHERE type= "psychology" AND pubjd IN
(SELECT pubjd FROM publishers
WHERE country= "USA" AND state<> "CA") 88)SELECT aujname, au_fname
FROM authors a
WHERE a.aujd IN (SELECT aujd FROM titleauthor ta WHERE ta.titlejd IN
(SELECT titlejd FROM titles t WHERE "CA"=SOME
(SELECT state FROM publishers p WHERE p.pub_id=t.pub_id))) ORDER BY aujname, 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(DISTINCTtitle)= (SELECT COUNT(*) FROM titles t2 WHERE t2.pub_id=p1 .pub_id AND EXISTS (SELECT * FROM titleauthorta3, 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, M IN (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"
| 
