Главная страница

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


Скачать 138 Kb.
НазваниеЗанятие 63 (І семестр) Вид занятия Лабораторная работа (30) Тема Построение запросов к базе данных на языке sql (различных типов)
Дата15.05.2023
Размер138 Kb.
Формат файлаdoc
Имя файлаЛаб. раб 30 Построение запросов к базе данных на языке SQL (разл.doc
ТипЗанятие
#1131159
страница2 из 3
1   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' 



1   2   3


написать администратору сайта