лр30. Лаб. раб 30 Построение запросов к базе данных на языке SQL (разл. Занятие 63 (І семестр) Вид занятия Лабораторная работа (30) Тема Построение запросов к базе данных на языке sql (различных типов)

• Название: Занятие 63 (І семестр) Вид занятия Лабораторная работа (30) Тема Построение запросов к базе данных на языке sql (различных типов)
• Дата: 15.05.2023
• Размер: 138 Kb.
• Имя файла: Лаб. раб 30 Построение запросов к базе данных на языке SQL (разл.doc
• Тип: Занятие; #1131159
• страница: 2 из 3

Задание 1

1. Выполните импорт базы данных Labа_30, которая находится в папке задания по лабораторной работе.

Вариант	1	2	3	4	5	6	7
Номер SQL-запроса	1, 46, 47, 92, 93	2, 45, 48, 91, 94	3, 44,49, 90, 95	4, 43, 50, 89, 96	5, 42, 51, 88, 93	6, 41, 52, 87, 94	7, 40, 53, 86, 95

Вариант	8	9	10	11	12	13	14
Номер SQL-запроса	8, 39, 54, 85, 96	9, 38, 55, 84, 93	10, 37, 56, 83, 94	11, 36, 57, 82, 95	12, 35, 58, 81, 96	13, 34, 59, 80, 93	14, 33, 60, 79, 94

Вариант	15	16	17	18	19	20	21
Номер SQL-запроса	15, 32, 61, 78, 95	16, 31, 62, 77, 96	17, 30, 63, 76, 93	18, 29, 64, 75, 94	19, 28, 65, 74, 95	20, 27, 66, 73, 96	21, 26, 67, 72, 93

Вариант	22	23
Номер SQL-запроса	22, 25, 68, 71, 94	23, 24, 69, 70, 95

Дать содержательную интерпретацию SQL-запросам, выполнить их, дать содержательную интерпретацию результатам выполнения 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 ASC
   
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 title 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
    GROUP 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 ta 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 t.title
    FROM titles t
    WHERE t.price>=
    (SELECT AVG(tt.price)
    FROM titles tt
    GROUP BY tt.pub_id
    HAVING t.pub_id=tt.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, city
    
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'