Занятие 3

Цель работы: получить практические навыки по поиску данных в таблице базы данных с помощью языка запросов SQL

Пусть имеется база данных учебного заведения "dekanat", в которой имеется таблица "stud" со следующей структурой:

Скачайте архив с базой данных в формате SQL Server, распакуйте его и подключите базу данных на сервере.

Используя зарегистрированную БД и язык запросов SQL выполните поиск следующей информации:

Запрос 2. Отобрать ФИО всех учащихся с переименование столбцов

Запрос 3. Отобрать ФИО всех учащихся с переименованием столбцов и сортировкой по фамилии по возрастанию

Запрос 4. Отобрать ФИО и средний балл всех учащихся с переименованием столбцов и сортировкой по фамилии по возрастанию и среднему баллу по убыванию

Запрос 5. Отобрать группу, фамилию и имя студента. Отсортировать данные по группе по возрастанию, по фамилии по возрастанию, по имени по возрастанию

Запрос 6. Отобрать дату рождения, фамилию и имя учащихся. Отсортировать данные по дате рождения по убыванию

Запрос 10. Отобрать ФИО и размер стипендии. Стипендия рассчитывается по формуле: средний балл * 120

Запрос 11. Отобрать ФИО и размер стипендии (формулу см. выше). Отсортировать данные по стипендии по убыванию

Запрос12. Внесем изменения. Предположим, что стипендия начисляется по формуле: средний балл 5 - 1475, средний балл от 4 - 1300, во всех иных случаях стипендия 0. Отсортировать данные по стипендии по убыванию и по фамилии по возрастанию

Запрос 13. Пусть к 8 марта всем девушкам выдается премия 200, а всем парням к 23 февраля - 150. Вывести на экран фамилию, имя и размер премии. Отсортировать данные по фамилии по возрастанию

Запрос 14. Для каждого учащегося выведите фразу: Студент [фамилия] родился: [дата рождения]. Отсортируйте данные по фамилии по возрастанию

Запрос 15. Найти максимальный и минимальный средний балл по учебному заведению

Запрос 16. Найти количество студентов в учебном заведении и их общий средний балл. Вывести данные с поясняющим текстом

Запрос 17. Определить количество групп в учебном заведении

Запрос 18. Определить количество уникальных имен в учебном заведении

Запрос 19. Отобрать ФИО студентов, которые учатся в группе 38. Отсортировать данные по фамилии по возрастанию.

Запрос 20. Отобрать фамилию, средний балл и дату рождения студентов хорошистов и отличников

Запрос 21. Отобрать все данные студенток из группы 38

Запрос 22. Отобрать фамилию, имя и размер стипендии только тех, кто получает стипендию. Учтите, что стипендию получают студенты со средним баллом от 4 (формулу см. выше) и финансированием "Госзаказ"

Запрос 23. Отобрать ФИО, средний балл и дату рождения студентов из группы 38, женского пола и средним баллом 4 и выше

Запрос 24. Отобрать все данные студентов с фамилией на букву А. Отсортировать по группе по возрастанию и по дате рождения по убыванию

Запрос 25. Отобрать все данные студентов мужского пола, с фамилией на буквы В, Д, Р и обучающихся на сталевара (в имени группы есть аббревиатура МЧМ)

Запрос 26. Определить сколько студентов мужского пола обучается в учебном заведении

Запрос 27. Определить сколько студентов женского пола обучается на 4 и выше

Запрос 28. Найти максимальный и средний средний баллы студентов 38 группы

Запрос 29. Найти средний балл по учебному заведению среди студентов, которые обучаются на контрактной основе

Запрос 30. Определить общую сумму стипендии, которую получат студенты по учебному заведению. Учтите, что стипендию получают студенты со средним баллом от 4 (формулу см. выше)

Запрос 31. Определите количество парней и девушек по всему учебному заведению

Запрос 32. Определите количество парней и девушек по всему учебному заведению, которые учатся на бюджетной основе

Запрос 33. Определите сколько имен имеется в таблице и сколько студентов имеют эти имена

Запрос 34. Для каждой группы подсчитать количество студентов в ней

Запрос 35. Вывести список групп, у которых количество студентов больше 20 человек

Запрос 2.

Указания: удалите предыдущий запрос и введите команду

select fam as 'Фамилия',

imya as 'Имя',

otch as 'Отчество'

from stud

Запрос 3.

Указания: удалите предыдущий запрос и введите команду

select fam as 'Фамилия',

imya as 'Имя',

otch as 'Отчество'

from stud

order by fam

Запрос 4.

Указания: удалите предыдущий запрос и введите команду

select fam as 'Фамилия',

imya as 'Имя',

otch as 'Отчество',

srbal as 'Средний балл'

from stud

order by fam, srbal desc

Запрос 5.

Указания: удалите предыдущий запрос и введите команду

select grupa,

fam,

imya

from stud

order by grupa, fam, imya

Запрос 6.

Указания: удалите предыдущий запрос и введите команду

select datar,

fam,

imya

from stud

order by datar desc

Запрос 7.

Указания: удалите предыдущий запрос и введите команду

select distinct fam

from stud

Запрос 8.

Указания: удалите предыдущий запрос и введите команду

select distinct imya

from stud

Запрос 9.

Указания: удалите предыдущий запрос и введите команду

select distinct grupa

from stud

Запрос 10.

Указания: удалите предыдущий запрос и введите команду

select fam,

imya,

otch,

srbal*120 as stip

from stud

Запрос 11.

Указания: удалите предыдущий запрос и введите команду

select fam,

imya,

otch,

srbal*120 as stip

from stud

order by stip desc

Запрос12.

Указания: удалите предыдущий запрос и введите команду

select fam,

imya,

otch,

case when srbal=5 then 1475

when srbal>=4 then 1300

else 0 end as stip

from stud

order by stip desc, fam

Запрос 13.

Указания: удалите предыдущий запрос и введите команду

select fam,

imya,

case when pol='ж' then 200

else 150 end as prem

from stud

order by fam

Запрос 14.

Указания: удалите предыдущий запрос и введите команду

select 'Студент ', fam, ' родился: ', datar

from stud

order by fam

Запрос 15.

Указания: удалите предыдущий запрос и введите команду

select max(srbal) as maxbal,

min(srbal) as minbal

from stud

Запрос 16.

Указания: удалите предыдущий запрос и введите команду

select 'Всего студентов: ', count(*) as kolstud,

' их средний балл: ', avg(srbal) as sredbal

from stud

Запрос 17.

Указания: удалите предыдущий запрос и введите команду

select count(distinct grupa) as kolgrup

from stud

Запрос 18.

Указания: удалите предыдущий запрос и введите команду

select count(distinct imya) as kolimya

from stud

Запрос 19.

Указания: удалите предыдущий запрос и введите команду

select fam,

imya,

otch

from stud

where grupa like '38%'

order by fam

Запрос 20.

Указания: удалите предыдущий запрос и введите команду

select fam,

srbal,

datar

from stud

where srbal>=4

Запрос 21.

Указания: удалите предыдущий запрос и введите команду

select *

from stud

where grupa like '38%'

and pol = 'ж'

Запрос 22.

Указания: удалите предыдущий запрос и введите команду

select fam,

imya,

srbal,

case when srbal=5 then 1475

when srbal>=4 then 1300

else 0 end as stip

from stud

where srbal>=4

and finance='Госзаказ'

Запрос 23.

Указания: удалите предыдущий запрос и введите команду

select fam,

imya,

otch,

srbal,

datar

from stud

where grupa like '38%'

and pol='ж'

and srbal>=4

Запрос 24.

Указания: удалите предыдущий запрос и введите команду

select *

from stud

where fam like 'а%'

order by grupa, datar desc

Запрос 25.

Указания: удалите предыдущий запрос и введите команду

select *

from stud

where (fam like 'в%'

or fam like 'д%'

or fam like 'р%')

and pol='м'

and grupa like '%МЧМ%'

Запрос 26.

Указания: удалите предыдущий запрос и введите команду

select count(*)

from stud

where pol='м'

Запрос 27.

Указания: удалите предыдущий запрос и введите команду

select count(*)

from stud

where pol='ж'

and srbal>=4

Запрос 28.

Указания: удалите предыдущий запрос и введите команду

select max(srbal) as maxbal,

avg(srbal) as sredbal

from stud

where grupa like '38%'

Запрос 29.

Указания: удалите предыдущий запрос и введите команду

select avg(srbal) as sredbal

from stud

where finance='Контракт'

Запрос 30.

Указания: удалите предыдущий запрос и введите команду

select sum(case when srbal=5 then 1475

when srbal>=4 then 1300

else 0 end) as sumstip

from stud

Запрос 31.

Указания: в правой части окна запроса введите команду

select pol,

count(*) as kol_stud

from stud

group by pol

Для выполнения запроса щелкните на команде Выполнить. Если запрос выполнен верно, то в нижней части окна вы увидите результат его работы.

Запрос 32.

Указания: в правой части окна запроса перейдите на закладку "Редактор" и введите команду

select pol,

count(*) as kol_stud

from stud

where finance like 'Госзаказ'

group by pol

Запрос 33.

Указания: введите команду

select imya,

count(*) as kol_stud

from stud

group by imya

Запрос 34.

Указания: введите команду

select grupa,

count(*) as kol_stud

from stud

group by grupa

Запрос 35.

Указания: введите команду

select grupa,

count(*) as kol_stud

from stud

group by grupa

having count(*)>20

Скачайте архив с БД в формате MySQL. Подключите БД на сервер и выполните все запросы в этой СУБД.

Скачайте архив с БД в формате SQLite. Подключите БД и выполните все запросы в этой СУБД.