Основы языка SQL SQL SQL DDL

Основы языка SQL

SQL • SQL DDL - Data Definition Language • SQL DML - Data Manipulation Language

Data Manipulation Language • Основные операторы: • • • SELECT UNION INSERT UPDATE DELETE

SELECT • • SELECT [ ALL | DISTINCT ] { * | field-list } [ INTO var-list ] FROM table-name [alt-name ] [, table-name [alt-name]] * [WHERE search-condition ] [GROUP BY field[ , field]* ] [HAVING search-condition ] [ORDER BY sort-criteria ] • ALL - результирующее множество строк не факторизуется (эта опция используется по умолчанию); DISTINCT - результирующее множество строк факторизуется. •

Демо-база •

Пример 1 • Фамилии всех студентов. • SELECT name_st FROM student

Пример 2 • Все сведения о студентах (все поля из таблицы student). • SELECT * FROM student

Пример 3 • Чем будут отличаться следующие запросы: • SELECT ALL sex FROM student • и • SELECT DISTINCT sex FROM student

Агрегатные функции •

Пример 4 • Количество записей в таблице student. • SELECT COUNT(*) FROM student

Пример 5 • Cредний возраст студентов (в днях). • SELECT round(avg(sysdate - bdate)) • FROM student • Примечание: round, sysdate – функции не SQL, а окружения

Пример 6 • Дни рождений самого старшего и самого юного студентов. • SELECT MIN(bdate), MAX(bdate) FROM student.

Полезные функции и константы • DECODE(expression, temp 1, result 1[, temp 2, result 2[, …. ]], result) • NVL(expression, result) • EXTRACT([day|month|year] FROM bdate)) • NULL • TO_DATE(expression, format) • TO_CHAR(expression, format)

Пример 7 • Сумма отметок всех студентов. • SELECT SUM(NVL(mark, 0)) • FROM marks

Пример 8 • Про отметки – словами: • SELECT num_st, code, • DECODE(NVL(MARK, 0), • 1, ‘единица’, • 2, ‘двойка’, • 3, ‘тройка’, • 4, ‘четверка’, • 5, ‘пятерка’, • ‘отметка не определена’) • FROM marks •

Пример 9 • Студенты, у которых введен адрес. • SELECT * • FROM student • WHERE address IS NOT NULL

Упражнение 1 • Имена и телефоны всех студентов. • Select name_st, phone From students; • Дубатовка 341

Упражнение 2 • Названия курсов и фамилии преподавателей. • SELECT name_c, name_t FROM course; • Клочкова 341;

Упражнение 3 • Имена всех студентов с указанием тех, у кого сегодня день рождения. • SELECT name_st, • DECODE( – (extract(day From bdate) = extract(day from sysdate)) • AND – (extract(month from bdate) = extract(month from sysdate)), TRUE, ‘С днем рождения’, ‘’) congratulation • FROM student • Остроумов 341 • Примечание:

Упражнение 4 • Сколько есть групп одинаковых имен среди студентов? • Select Count(distinct name_st) from student • Михайлов 341

Упражнение 5 • Какая разница в возрасте самого юного и самого старшего студента? • Select (round((max(bdate)min(bdate))/365)) from student • Хаустова 341

Упражнение 6 • У кого день рождения ещё предстоит в этом году? • SELECT name_st FROM student WHERE EXTRACT (month FROM bdate)*100+ EXTRACT (day FROM bdate)>= EXTRACT( month FROM sysdate)*100+ EXTRACT(day FROM sysdate) • Гордова 341

Упражнение 7 • Сколько дней осталось до дня рождения каждого студента? • SELECT DECODE(SIGN (To_DATE(EXTRACT(DAY FROM bdate) || “/” || EXTRACT (MONTH FROM BDATE) || “/” Extract(year from sysdate), “dd/mm/yyyy”) To_DATE(EXTRACT(DAY FROM sysdate) || “/” || EXTRACT (MONTH FROM sysdate) || “/” Extract(year from sysdate), “dd/mm/yyyy”)), -1, To_DATE(EXTRACT(DAY FROM bdate) || “/” || EXTRACT (MONTH FROM BDATE) || “/” Extract(year from sysdate), “dd/mm/yyyy”) To_DATE(EXTRACT(DAY FROM sysdate) || “/” || EXTRACT (MONTH FROM sysdate) || “/” Extract(year from sysdate), “dd/mm/yyyy”) + 365, To_DATE(EXTRACT(DAY FROM bdate) || “/” || EXTRACT (MONTH FROM BDATE) || “/” Extract(year

Конструкт search-condition 1. 2. 3. 4. 5. 6. 7. 8. log. expression [ NOT ] BETWEEN expression 1 AND expression 2 field-name IS [ NOT ] NULL field-name [ NOT ] LIKE "string" [ ESCAPE "character" ] expression [ NOT ] IN ({ value-list!SELECT-statement}) expression relation-operator ( SELECT-statement ) [NOT ] EXISTS ( SELECT-statment ) expression relation-operator { ANY | ALL | SOME } ( SELECTstatment )

Пример 10 • Вся информация о студентах, родившихся после 01. 95. • SELECT * FROM student • WHERE bdate > to_date(’ 01/01/1995’, ‘dd/mm/yyyy’)

Пример 11 • Фамилии студентов, родившихся в интервале от 01. 95 до 01. 98. • SELECT name_st • FROM student • WHERE bdate BETWEEN to_date(’ 01/01/1995’, ‘dd/mm/yyyy’) • AND to_date(’ 01/01/1998’, ’dd/mm/yyyy’)

Пример 12 • Студенты с неопределенным днем рождения (забыли ввести значение этого поля). • SELECT name_st • FROM student WHERE bdate IS NULL

Пример 13 • Отличники (т. е. студенты, у которых любая отметка равна 5). • • • SELECT name_st FROM student WHERE 5 = ALL (SELECT mark FROM marks WHERE marks. num_st = student. num_st)

Упражнение 8 • • Все отметки студента с номером 55. SELECT mark FROM marks Where num_st = 55 Петров 341

Упражнение 9 • Фамилии и адреса студентов с номерами 23, 9996, 56, 89866, 7777. • SELECT name_st, address FROM students • Where num_st IN (23, 9996, 56, 89866, 7777) • Петров 341

Упражнение 10 • Имена студентов, получивших 5. • SELECT name_st FROM students • WHERE num_st IN(SELECT DISTINCT num_st FROM marks WHERE mark = 5) • Марганова 341

Упражнение 11 • Фамилии преподавателей, которые не ставили 2. • SELECT name_t FROM course WHERE • Code NOT IN (SELECT DISTINCT code FROM marks WHERE mark = 2) • Khandygo 341

Конструкт GROUP BY • Синтаксис: • [GROUP BY field[ , field]* ] • [HAVING search-condition] • Семантика: факторизация по значению поля (полей)

Пример 14 • Количество студентов и студенток (или количество записей в каждой группе по полю sex). • SELECT sex, COUNT(*) • FROM student GROUP BY sex

Пример 15 • Номера зачеток студентов со средним баллом большим, чем 4. 5. • SELECT num_st, AVG(mark) • FROM marks • GROUP BY num_st • HAVING AVG(mark) > 4. 5

Упражнение 12 • • • Сколько отметок у каждого студента? (номер студента, количество отметок) Select num_st, count(*) From marks Group by num_st Дерипаска 545

Упражнение 13 • Имена отличников (с использованием group by). • SELECT name_st FROM student • WHERE num_st in (SELECT num_st FROM marks GROUP BY num_st HAVING min(mark) = 5) • Кузенкова 545

Упражнение 14 • Для каждого кода курса выдать диапазон выставленных отметок. • SELECT name_c, (SELECT min(marks. mark) FROM marks WHERE course. code = marks. code), “-”, (SELECT max(marks. mark) FROM marks WHERE course. code = marks. code) FROM course Ерохин 545

Упражнение 15 • Выдать сведения об успеваемости студентов. • Например, 55 – отличник, • 122 – хорошист и т. п. • SELECT num_st, DECODE(min(mark), 5, ‘отличник’, 4, ‘хорошист’, 3, ‘троечник’, 2, ‘двоечник’) • FROM marks • GROUP BY num_st

Упражнение 16 • Выдать сведения о диапазоне отметок для студентов, у которых количество отметок больше 10. Например, • 55 : 3 – 5 • 123: 1 – 4 SELECT num_st, “: ”, MIN(mark), “-”, MAX(mark) FROM marks GROUP BY num_st HAVING Count(*) > 10; Дубатовка 341

Конструкт FROM • Синтаксис: • FROM table-name [alt-name ] • [, table-name [alt-name]] * • Семантика: • table-name в общем случае не просто таблица, а табличное выражение.

Пример 16 • Фамилии студентов и оценки. • SELECT name_st, mark • FROM student, marks • WHERE student. num_st = marks. num_st

Пример 17 • Фамилии студентов, оценки и названия курсов. • SELECT name_st, mark, name_c • FROM student, marks, course • WHERE student. num_st = marks. num_st • AND marks. code = course. code

Пример 18 • Все пары: Имя студента + Название курса • SELECT student. name_st, • course. name_c • FROM student, course • • • Иванов Алгебра Иванов Мат. Анализ Иванов История Сидоров Алгебра …

Пример 19 • Все пары - студент и студентка (вариант 1). • SELECT student_m. name_st, • student_f. name_st • FROM student_m, student_f • WHERE student_m. sex AND NOT student_f. sex

Пример 20 • Все пары - студент и студентка (вариант 2). • • • SELECT student_m. name_st, student_f. name_st FROM (select * from student where student. sex ) student_m, (select * from student where not student. sex ) student_f

Упражнение 17 • Названия курсов и отметки студента с номером 55. • SELECT name_c, mark • FROM courses, marks • WHERE (courses. code = marks. code AND marks. num_st = 55); • Дубатовка 341

Упражнение 18 • Названия курсов и количество 2 по соответствующим курсам. • • SELECT name_c, SUM(DECODE(mark, 2, 1, 0)) FROM courses, marks WHERE (courses. code = marks. code) GROUP BY name_c • Клочкова 341

Упражнение 19 • Имена студентов и их средние баллы. • SELECT name_st, num_st, AVG(mark) • FROM student, marks • WHERE student. num_st = marks. num_st • GROUP BY name_st, num_st; Китайгородская 341

Упражнение 20 • Краткая характеристика каждого студента. • Например, Иванов – отличник • Сидоров – двоечник • …. • SELECT name_st, num_st, decode(min(mark), 5, “Отличник”, 4, “Хорошист”, 3, “Троечник”, “Двоечник”) • FROM student, marks • WHERE student. num_st = marks. num_st • GROUP BY name_st, num_st; • Хаустова 341

Упражнение 21 • Сколько в базе отличников, хорошистов и т. п. по Алгебре и Геометрии? • • • SELECT status, count(*) FROM (SELECT name_st, num_st, decode(min(mark), 5, “Отличник”, 4, “Хорошист”, 3, “Троечник”, “Двоечник”) AS status FROM student, marks, courses WHERE student. num_st = marks. num_st AND (courses. name_c = “Алгебра” OR courses. name_c = “Геометрия”) AND marks. code = courses. code GROUP BY name_st, num_st) temp Group By status

Конструкт ORDER BY • Синтаксис: • ORDER BY { n | expression } { ASC | DESC } • [, { n | expression } { ASC | DESC }] * • где n - порядковый номер поля в операторе SELECT.

Пример 21 • Фамилии студентов и оценки, упорядоченные по убыванию оценок. • SELECT name_st, mark • FROM student, marks • WHERE student. num_st = marks. num_st • ORDER BY mark DESC

Пример 22 • Фамилии студентов и оценки, упорядоченные по возрастанию имен и по убыванию оценок. • • SELECT name_st, mark FROM student, marks WHERE student. num_st = marks. num_st ORDER BY name_st ASC, mark DESC

Пример 23 • Фамилии студентов, упорядоченные по среднему баллу. • • SELECT temp. name_st, avg(temp. mark) FROM (SELECT name_st, mark FROM student, marks WHERE student. num_st = marks. num_st) temp GROUP BY name_st ORDER BY 2 DESC

Полезный псевдостолбец ROWNUM • Первые три студента из лексикографически упорядоченного списка. • SELECT * • FROM (SELECT name_st • FROM student • ORDER BY name_st) • WHERE rownum <= 3

Упражнение 23 • Фамилии студентов, упорядоченные по возрастанию даты рождения. • SELECT name_st • FROM student • ORDER BY bdate ASC

Упражнение 24 • Названия курсов, упорядоченные по количеству полученных отметок. • SELECT name_c, COUNT(*) • FROM courses, marks • WHERE course. code = marks. code • GROUP BY name_c • ORDER BY 2 DESC • Чернявский 341

Упражнение 25 • Первые 10 студентов (по успеваемости). • • • SELECT * FROM (SELECT name_st, num_st, AVG(mark), SUM(mark) FROM student, marks WHERE student. num_st = marks. num_st GROUP BY name_st, num_st ORDER BY 3 DESC, 4 DESC ) • WHERE rownum <=10 • Петров 341

Упражнение 26 • Список студентов, упорядоченный по наступлению дня рождения. • SELECT name_st, 366 - (10000*(sysdate-bdate) MOD 3652425) FROM student • ORDER BY 2 • Гордова Карина 341

Оператор UNION • • • Синтаксис: SELECT-statement UNION [ ALL ] {SELECT-statement UNION-statement} [ ORDER BY sort-criteria] • Семантика: • Операнды должны иметь идентичную структуру. • Результат по умолчанию факторизуется!

Пример 24 • Номера зачеток студентов, у которых есть двойки и пятерки. • SELECT num_st FROM marks WHERE mark = 5 • UNION • SELECT num_st FROM marks WHERE mark = 2

Упражнение 27 • Сколько в базе студентов, рождения 1989, 1990 и 1991 года? • select count(*) from students where • Extract(year from bdate) in (1989, 1990, 1991) • Зареченский 341

Оператор INSERT • Синтаксис: • INSERT INTO table-name [(field-list)] • {VALUES(value-list)|SELECT-statement}

Пример 25 • Добавление нового студента. • INSERT INTO student (num_st, name_st) • VALUES (016, "Семенов")

Упражнение 27 • Добавьте в таблицу COURSE вторую часть для каждого курса (например, если была запись про Алгебру, добавить запись про Алгебру II). • INSERT INTO courses (code, name_c, name_t) SELECT code + (SELECT COUNT(*) FROM courses), NAME_C || ' II', NAME_T FROM courses • Остроумов 341

Оператор UPDATE • Синтаксис: • • UPDATE table-name SET field-name = { NULL | expression } [ , field-name = { NULL | expression }] * [WHERE search-condition ]

Пример 26 • Изменение номеров у всех студентов. • UPDATE student SET num_st = num_st + 100

Пример 27 • Изменение адреса у всех студентов с фамилией 'Иванов'. • UPDATE student • SET address = 'Красная пл. , дом 2' • WHERE name_st = 'Иванов'

Упражнение 28 • Добавьте к телефонам студентов префикс +7 812. • Update students set phone = TO_CHAR (666666 + num_st, ‘ 9999999’) • update students set phone = '+ 7 912' || phone • Khandygo 341

Оператор DELETE • Синтаксис: • DELETE FROM table-name • [ WHERE search-condition ]

Пример 28 • Удаление записей с отметками единицами. • DELETE FROM marks WHERE mark = 1

Пример 29 • Удаление всех записей из таблицы STUDENT. • DELETE FROM student.

Упражнение 29 • Удалите все записи о студентах-двоечниках. • DELETE FROM student WHERE num_st in (SELECT num_st FROM marks WHERE mark <= 2) • DELETE FROM marks WHERE num_st in (SELECT num_st FROM marks WHERE mark <= 2) • Кузенкова 545

Data Definition Language (DDL) • Основные операторы: • • • CREATE TABLE ALTER TABLE DROP TABLE CREATE VIEW DROP VIEW

Оператор CREATE TABLE • Синтаксис : • CREATE TABLE table-name • (field-name type [ options ] • [, field-name type [ options ] ] *)

Пример 30 • CREATE TABLE postgrad • ( • num_p INTEGER NOT NULL, • name VARCHAR 2(32), • address VARCHAR 2(64), • bdate DATE • )

Оператор CREATE VIEW • Синтаксис: • CREATE VIEW view-name [(field-list)] • AS SELECT-statement [ WITH CHECK OPTION ]

Упражнение 30 • Создайте представление <ОТЛИЧНИКИ>.

Упражнение 31 • Создайте представление из фамилий студентов и среднего балла.

Ограничения для редактируемых VIEW • нельзя использовать конструкты DISTINCT, GROUP BY, HAVING или агрегатные функции в соответствующем операторе SELECT • конструкт FROM в операторе SELECT может специфицировать только одну таблицу • нельзя использовать вложенные SELECT • если конструкт FROM специфицирует VIEW-таблицу, эта таблица должна быть, в свою очередь, редактируемой