Урок 64 (5)

1.

2.

CREATE TABLE instruments (
id INT primary key auto_increment,
name VARCHAR(50) not null,
type VARCHAR(50),
data DATE,
num_string INT default 0 check (num_string>=0),
materials VARCHAR(20),
producer VARCHAR(50),
num_instr INT,
price INT,
sales INT default 0,
data_begin DATE,
data_end DATE default (current_date),
unique (name,materials));
INSERT INTO instruments VALUES
(1, 'Домбра', 'Щипковый', '2018-12-25', 2, 'Сосна', 'Турдугулов Ж.', 5, 200000, default, NULL, NULL),
(2, 'Кобыз', 'Смычковый', '2017-12-25', 2, 'Береза', 'Абрамкин В.’, 3, 150000, default, NULL, NULL),
(3, 'Жетыген', 'Щипковый', '2018-04-15', 7, 'Клен', 'ТОО Лира', 6, 180000, default, NULL, default),
(4, 'Сыбызгы', 'Духовой', '2018-10-07', 0, 'Бамбук', 'Завод Юпитер', 15, 20000, 30, '2018-03-01', '2018-03-31'),
(5, 'Дабыл', 'Ударный', '2018-11-03', 0, 'Береза', 'Завод Юпитер', 9, 60000, default, NULL, default),
(6, 'Шертер', 'Щипковый', '2018-07-11', 2, 'Орех', 'ТОО Лира', 12, 100000, 40, '2018-03-15', '2018-03-30'),
(7, 'Сазсырнай', 'Духовой', '2017-09-22', 0, 'Глина', 'Sapaly Saz', 35, 15000, 20, '2018-04-15', '2018-05-10'),
(8, 'Домбра', 'Щипковый', '2018-12-25', 2, 'Бамбук', 'Sapaly Saz', 21, 150000, 28, NULL, default),
(9, 'Кобыз', 'Смычковый', '2017-12-25', 2, 'Сосна', 'Абрамкин В.', 3, 110000, default, NULL, default),
(10, 'Шертер', 'Щипковый', '2018-07-11', 2, 'Береза', 'Sapaly Saz', 17, 90000, default, NULL, default);
SELECT * FROM instruments;

3.

SELECT * FROM instruments;

4.

SELECT * FROM instruments order by name;

5.

SELECT * FROM instruments order by data desc;

6.

SELECT * FROM instruments where type='Щипковый' order by price;
SELECT * FROM instruments where name like 'Д%';

7.

SELECT name, materials FROM instruments where materials like '%на';
SELECT * FROM instruments where price between 100000 and 200000
limit 5;

8.

SELECT name, count(*) as name_count from instruments group by name;
SELECT name, num_instr, count(*) as name_count from instruments group by
name, num_instr;

9.

SELECT name, count(*) as name_count, min(price)
as min_price, max(price) as max_price,
sum(num_instr*price) as total_sum from
instruments group by name order by total_sum asc;

10.

SELECT * FROM instruments WHERE price>(SELECT AVG(price)
FROM instruments);

11.

Практическая работа
1. Получить данные упорядоченные по названию инструментов по алфавиту и по
дате изготовления в обратном порядке.
2. Вывести все записи инструментов с наименьшей ценой, но дороже 20000 из 5
самых дешевых.
3. Вывести названия и материалы инструментов, в названии материала которых есть
буква «е».
4. Найти все щипковые инструменты, у которых количество струн (num_string)
больше 0, а название инструмента начинается на букву «Д» или «Ш».
Отсортировать их по названию производителя в алфавитном порядке.
5. Вывести инструменты, в названии которых есть буква «а», и при этом они
сделаны из материала, начинающегося на «Б».
6. Посчитать общую сумму инструментов в наличии (num_instr) для каждого
названия инструмента. Столбец с суммой назвать Всего_в_наличии.
7. Для каждого типа инструментов найти самую высокую и самую низкую цену. Дать
столбцам понятные названия.
8. Вывести названия, цены и скидки тех щипковых инструментов, количество
которых превышает среднее количество на складе

12.

Решение:
1.SELECT *from instruments order by name, data_end desc;
2.SELECT *from instruments where PRICE > 20000 order by PRICE limit 5;
3.SELECT name, materials from instruments where name like '%е%’;
4.SELECT * from instruments where num_string>0 and (name like 'Д%' or
name like 'Ш%') order by producer desc;
5.SELECT * from instruments where name like '%а%' and materials like
'Б%’;
6.SELECT name, SUM(num_instr) AS Всего_в_наличии FROM instruments
GROUP BY name;
7.SELECT type, MAX(price) AS max_price, MIN(price) AS min_price FROM
instruments GROUP BY type;
8.SELECT name, price, sales FROM instruments WHERE type='Щипковый'
and num_instr>(SELECT AVG(num_instr) FROM instruments);