Посчитайте количество городов в которых нет продавцов
Перейти к содержимому

Посчитайте количество городов в которых нет продавцов

Тесты по SQL с ответами

2. Имеются элементы запроса: 1. SELECT employees.name, departments.name; 2. ON employees.department_id=departments.id; 3. FROM employees; 4. LEFT JOIN departments. В каком порядке их нужно расположить, чтобы выполнить поиск имен всех работников со всех отделов?

3. Как расшифровывается SQL?

+ structured query language

— strict question line

— strong question language

4. Запрос для выборки всех значений из таблицы «Persons» имеет вид:

— SELECT ALL Persons

+ SELECT * FROM Persons

5. Какое выражение используется для возврата только разных значений?

6. Для подсчета количества записей в таблице «Persons» используется команда:

— COUNT ROW IN Persons

+ SELECT COUNT(*) FROM Persons

— SELECT ROWS FROM Persons

7. Наиболее распространенным является тип объединения:

8. Что возвращает запрос SELECT * FROM Students?

+ Все записи из таблицы «Students»

— Рассчитанное суммарное количество записей в таблице «Students»

— Внутреннюю структуру таблицы «Students»

9. Запрос «SELECT name ___ Employees WHERE age ___ 35 AND 50» возвращает имена работников, возраст которых от 35 до 50 лет. Заполните пропущенные места в запросе.

тест 10. Какая агрегатная функция используется для расчета суммы?

11. Запрос для выборки первых 14 записей из таблицы «Users» имеет вид:

+ SELECT * FROM Users LIMIT 14

— SELECT * LIMIT 14 FROM Users

— SELECT * FROM USERS

12. Выберите верное утверждение:

— SQL чувствителен к регистру при написании запросов

— SQL чувствителен к регистру в названиях таблиц при написании запросов

— SQL нечувствителен к регистру

13. Заполните пробелы в запросе «SELECT ___, Сountry FROM ___ », который возвращает имена заказчиков и страны, где они находятся, из таблицы «Customers».

14. Запрос, возвращающий все значения из таблицы «Countries», за исключением страны с имеет вид:

— SELECT * FROM Countries EXP >

+ SELECT * FROM Countries WHERE ID !=8

— SELECT ALL FROM Countries LIMIT 8

15. Напишите запрос для выборки данных из таблицы «Customers», где условием является проживание заказчика в городе Москва

+ SELECT * FROM Customers WHERE City=”Moscow”

— SELECT City=”Moscow” FROM Customers

— SELECT Customers WHERE City=”Moscow”

16. Напишите запрос, возвращающий имена, фамилии и даты рождения сотрудников (таблица «Employees»). Условие – в фамилии содержится сочетание «se».

— SELECT FirstName, LastName, BirthDate from Employees WHERE LastName=“se”

— SELECT * from Employees WHERE LastName like “_se_”

+ SELECT FirstName, LastName, BirthDate from Employees WHERE LastName like “%se%”

17. Какая функция позволяет преобразовать все буквы в выбранном столбце в верхний регистр?

18. Напишите запрос, позволяющий переименовать столбец LastName в Surname в таблице «Employees».

— RENAME LastName into Surname FROM Employees

+ ALTER TABLE Employees CHANGE LastName Surname varchar(50)

— ALTER TABLE Surname(LastName) FROM Employees

19. Для создания новой виртуальной таблицы, которая базируется на результатах сделанного ранее SQL запроса, используется команда:

— CREATE VIRTUAL TABLE

тест-20. В таблице «Emlpoyees» содержатся данные об именах, фамилиях и зарплате сотрудников. Напишите запрос, который изменит значение зарплаты с 2000 на 2500 для сотрудника с >

— SET Salary=2500 FROM Salary=2000 FOR FROM Employees

— ALTER TABLE Employees Salary=2500 FOR >

+ UPDATE Employees SET Salary=2500 WHERE >

21. К какому результату приведет выполнение запроса DROP DATABASE Users?

+ Полное удаление базы данных «Users»

— Блокировка на внесение изменений в базу данных «Users»

— Удаление таблицы «Users» из текущей базы данных

22. В таблице «Animals» базы данных зоопарка содержится информация обо всех обитающих там животных, в том числе о лисах: red fox, grey fox, little fox. Напишите запрос, возвращающий информацию о возрасте лис.

— SELECT %fox age FROM Animals

+ SELECT age FROM Animals WHERE Animal LIKE «%fox»

— SELECT age FROM %Fox.Animals

23. Что возвращает запрос SELECT FirstName, LastName, Salary FROM Employees Where Salary<(Select AVG(Salary) FROM Employees) ORDER BY Salary DESC?

— Имена, фамилии и зарплаты сотрудников, значения которых соответствуют среднему значению среди всех сотрудников

— Имена, фамилии сотрудников и их среднюю зарплату за весь период работы, с выполнением сортировки по убыванию

+ Имена, фамилии и зарплаты сотрудников, для которых справедливо условие, что их зарплата ниже средней, с выполнением сортировки зарплаты по убыванию

24. Напишите запрос, возвращающий значения из колонки «FirstName» таблицы «Users».

+ SELECT FirstName FROM Users

— SELECT * FROM Users.FirstName

25. Напишите запрос, возвращающий информацию о заказчиках, проживающих в одном из городов: Москва, Тбилиси, Львов.

— SELECT Moscow, Tbilisi, Lvov FROM Customers

+ SELECT * FROM Customers WHERE City IN (‘Moscow’, ‘Tbilisi’, ‘Lvov’)

— SELECT City IN (‘Moscow’, ‘Tbilisi’, ‘Lvov’) FROM Customers

26. Какая команда используется для объединения результатов запроса без удаления дубликатов?

27. Оператор REVOKE предназначен для:

— Предоставления пользователю или группе пользователей прав на осуществление определенных операций;

— Задавания пользователю или группе пользователей запрета, который является приоритетным по сравнению с разрешением;

+ Отзыва у пользователя или группы пользователей выданных ранее разрешений

28. Для чего в SQL используются aliases?

+ Для назначения имени источнику данных в запросе при использовании выражения в качестве источника данных или для упрощения структуры запросов

— Для переименования полей

— Для более точного указания источника данных, если в базе данных содержатся таблицы с одинаковыми названиями полей

29. Напишите запрос, который будет возвращать значения городов из таблицы «Countries».

— SELECT * FROM Countries WHERE >

+ SELECT City FROM Countries

тест_30. Имеются элементы запроса: 1. ORDER BY Name; 2. WHERE Age В каком порядке их нужно расположить, чтобы выполнить поиск имен и фамилий студентов в возрасте до 19 лет с сортировкой по имени?

31. Для чего в SQL используется оператор PRIVILEGUE?

— Для наделения суперпользователя правами администратора

— Для выбора пользователей с последующим наделением их набором определенных прав

+ Такого оператора не существует

32. Напишите запрос, который будет возвращать текущую дату.

33. Какой оператор используется для выборки значений в пределах заданного диапазона?

SQL. Занимательные задачки

Вот уже более 3-х лет я преподаю SQL в разных тренинг центрах, и одним из моих наблюдений является то, что студенты осваивают и понимают SQL лучше, если ставить перед ними задачу, а не просто рассказывать о возможностях и теоретических основах.

В этой статье я поделюсь с вами своим списком задач, которые я даю студентам в качестве домашнего задания и над которыми мы проводим разного рода брейнстормы, что приводит к глубокому и четкому пониманию SQL.

SQL (ˈɛsˈkjuˈɛl; англ. structured query language — «язык структурированных запросов») — декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных, управляемой соответствующей системой управления базами данных. Подробнее…

Почитать об SQL можно из разных источников.
Данная статья не преследует цели обучить вас SQL с нуля.

Итак, поехали.

Будем использовть всем известную схему HR в Oracle с ее таблицами (Подробнее):

Отмечу что мы будем рассматривать только задачи на SELECT. Тут нет задач на DML и DDL.

Задачи

Restricting and Sorting Data

Таблица Employees. Получить список с информацией обо всех сотрудниках

Решение

SELECT * FROM employees 

Таблица Employees. Получить список всех сотрудников с именем ‘David’
Решение

SELECT * FROM employees WHERE first_name = 'David'; 

Таблица Employees. Получить список всех сотрудников с job_id равным ‘IT_PROG’
Решение

SELECT * FROM employees WHERE job_id = 'IT_PROG' 

Таблица Employees. Получить список всех сотрудников из 50го отдела (department_id) с зарплатой(salary), большей 4000

Решение

SELECT * FROM employees WHERE department_id = 50 AND salary > 4000; 

Таблица Employees. Получить список всех сотрудников из 20го и из 30го отдела (department_id)
Решение

SELECT * FROM employees WHERE department_id = 20 OR department_id = 30; 

Таблица Employees. Получить список всех сотрудников у которых последняя буква в имени равна ‘a’
Решение

SELECT * FROM employees WHERE first_name LIKE '%a'; 

Таблица Employees. Получить список всех сотрудников из 50го и из 80го отдела (department_id) у которых есть бонус (значение в колонке commission_pct не пустое)

Решение

SELECT * FROM employees WHERE (department_id = 50 OR department_id = 80) AND commission_pct IS NOT NULL; 

Таблица Employees. Получить список всех сотрудников у которых в имени содержатся минимум 2 буквы ‘n’
Решение

SELECT * FROM employees WHERE first_name LIKE '%n%n%'; 

Таблица Employees. Получить список всех сотрудников у которых длина имени больше 4 букв
Решение

SELECT * FROM employees WHERE first_name LIKE '%_____%'; 

Таблица Employees. Получить список всех сотрудников у которых зарплата находится в промежутке от 8000 до 9000 (включительно)

Решение

SELECT * FROM employees WHERE salary BETWEEN 8000 AND 9000; 

Таблица Employees. Получить список всех сотрудников у которых в имени содержится символ ‘%’
Решение

SELECT * FROM employees WHERE first_name LIKE '%\%%' ESCAPE '\'; 

Таблица Employees. Получить список всех ID менеджеров
Решение

SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL; 

Таблица Employees. Получить список работников с их позициями в формате: Donald(sh_clerk)
Решение

SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees; 

Using Single-Row Functions to Customize Output

Таблица Employees. Получить список всех сотрудников у которых длина имени больше 10 букв

Решение

SELECT * FROM employees WHERE LENGTH (first_name) > 10; 

Таблица Employees. Получить список всех сотрудников у которых в имени есть буква ‘b’ (без учета регистра)

Решение

SELECT * FROM employees WHERE INSTR (LOWER (first_name), 'b') > 0; 

Таблица Employees. Получить список всех сотрудников у которых в имени содержатся минимум 2 буквы ‘a’
Решение

SELECT * FROM employees WHERE INSTR (LOWER (first_name),'a',1,2) > 0; 

Таблица Employees. Получить список всех сотрудников зарплата которых кратна 1000
Решение

SELECT * FROM employees WHERE MOD (salary, 1000) = 0; 

Таблица Employees. Получить первое 3х значное число телефонного номера сотрудника если его номер в формате ХХХ.ХХХ.ХХХХ

Решение

SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number FROM employees WHERE phone_number LIKE '___.___.____'; 

Таблица Departments. Получить первое слово из имени департамента для тех у кого в названии больше одного слова

Решение

SELECT department_name, SUBSTR (department_name, 1, INSTR (department_name, ' ')-1) first_word FROM departments WHERE INSTR (department_name, ' ') > 0; 

Таблица Employees. Получить имена сотрудников без первой и последней буквы в имени
Решение

SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name FROM employees; 

Таблица Employees. Получить список всех сотрудников у которых последняя буква в имени равна ‘m’ и длинной имени большей 5ти

Решение

SELECT * FROM employees WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5; 

Таблица Dual. Получить дату следующей пятницы
Решение

SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL; 

Таблица Employees. Получить список всех сотрудников которые работают в компании больше 17 лет
Решение

SELECT * FROM employees WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17; 

Таблица Employees. Получить список всех сотрудников у которых последня цифра телефонного номера нечетная и состоит из 3ех чисел разделенных точкой

Решение

SELECT * FROM employees WHERE MOD (SUBSTR (phone_number, -1), 2) != 0 AND INSTR (phone_number,'.',1,3) = 0 AND INSTR (phone_number,'.',1,2) > 0; 

Таблица Employees. Получить список всех сотрудников у которых в значении job_id после знака ‘_’ как минимум 3 символа но при этом это значение после ‘_’ не равно ‘CLERK’

Решение

SELECT * FROM employees WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3 AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK'; 

Таблица Employees. Получить список всех сотрудников заменив в значении PHONE_NUMBER все ‘.’ на ‘-‘
Решение

SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number FROM employees; 

Using Conversion Functions and Conditional Expressions

Таблица Employees. Получить список всех сотрудников которые пришли на работу в первый день месяца (любого)

Решение

SELECT * FROM employees WHERE TO_CHAR (hire_date, 'DD') = '01'; 

Таблица Employees. Получить список всех сотрудников которые пришли на работу в 2008ом году
Решение

SELECT * FROM employees WHERE TO_CHAR (hire_date, 'YYYY') = '2008'; 

Таблица DUAL. Показать завтрашнюю дату в формате: Tomorrow is Second day of January
Решение

SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info FROM DUAL; 

Таблица Employees. Получить список всех сотрудников и дату прихода на работу каждого в формате: 21st of June, 2007

Решение

SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date FROM employees; 

Таблица Employees. Получить список работников с увеличенными зарплатами на 20%. Зарплату показать со знаком доллара

Решение

SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary FROM employees; 

Таблица Employees. Получить список всех сотрудников которые приши на работу в феврале 2007го года.
Решение

SELECT * FROM employees WHERE hire_date BETWEEN TO_DATE ('01.02.2007', 'DD.MM.YYYY') AND LAST_DAY (TO_DATE ('01.02.2007', 'DD.MM.YYYY')); SELECT * FROM employees WHERE to_char(hire_date,'MM.YYYY') = '02.2007'; 

Таблица DUAL. Вывезти актуальную дату, + секунда, + минута, + час, + день, + месяц, + год
Решение

SELECT SYSDATE now, SYSDATE + 1 / (24 * 60 * 60) plus_second, SYSDATE + 1 / (24 * 60) plus_minute, SYSDATE + 1 / 24 plus_hour, SYSDATE + 1 plus_day, ADD_MONTHS (SYSDATE, 1) plus_month, ADD_MONTHS (SYSDATE, 12) plus_year FROM DUAL; 

Таблица Employees. Получить список всех сотрудников с полными зарплатами (salary + commission_pct(%)) в формате: $24,000.00

Решение

SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary FROM employees; 

Таблица Employees. Получить список всех сотрудников и информацию о наличии бонусов к зарплате (Yes/No)

Решение

SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus FROM employees; 

Таблица Employees. Получить уровень зарплаты каждого сотрудника: Меньше 5000 считается Low level, Больше или равно 5000 и меньше 10000 считается Normal level, Больше иои равно 10000 считается High level

Решение

SELECT first_name, salary, CASE WHEN salary < 5000 THEN 'Low' WHEN salary >= 5000 AND salary < 10000 THEN 'Normal' WHEN salary >= 10000 THEN 'High' ELSE 'Unknown' END salary_level FROM employees; 

Таблица Countries. Для каждой страны показать регион в котором он находится: 1-Europe, 2-America, 3-Asia, 4-Africa (без Join)

Решение

SELECT country_name country, DECODE (region_id, 1, 'Europe', 2, 'America', 3, 'Asia', 4, 'Africa', 'Unknown') region FROM countries; SELECT country_name country, CASE region_id WHEN 1 THEN 'Europe' WHEN 2 THEN 'America' WHEN 3 THEN 'Asia' WHEN 4 THEN 'Africa' ELSE 'Unknown' END region FROM countries; 

Reporting Aggregated Data Using the Group Functions

Таблица Employees. Получить репорт по department_id с минимальной и максимальной зарплатой, с ранней и поздней датой прихода на работу и с количествов сотрудников. Сорировать по количеству сотрудников (по убыванию)

Решение

 SELECT department_id, MIN (salary) min_salary, MAX (salary) max_salary, MIN (hire_date) min_hire_date, MAX (hire_date) max_hire_Date, COUNT (*) count FROM employees GROUP BY department_id order by count(*) desc; 

Таблица Employees. Сколько сотрудников имена которых начинается с одной и той же буквы? Сортировать по количеству. Показывать только те где количество больше 1

Решение

SELECT SUBSTR (first_name, 1, 1) first_char, COUNT (*) FROM employees GROUP BY SUBSTR (first_name, 1, 1) HAVING COUNT (*) > 1 ORDER BY 2 DESC; 

Таблица Employees. Сколько сотрудников которые работают в одном и тоже отделе и получают одинаковую зарплату?

Решение

SELECT department_id, salary, COUNT (*) FROM employees GROUP BY department_id, salary HAVING COUNT (*) > 1; 

Таблица Employees. Получить репорт сколько сотрудников приняли на работу в каждый день недели. Сортировать по количеству

Решение

SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*) FROM employees GROUP BY TO_CHAR (hire_Date, 'Day') ORDER BY 2 DESC; 

Таблица Employees. Получить репорт сколько сотрудников приняли на работу по годам. Сортировать по количеству

Решение

SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*) FROM employees GROUP BY TO_CHAR (hire_date, 'YYYY'); 

Таблица Employees. Получить количество департаментов в котором есть сотрудники
Решение

SELECT COUNT (COUNT (*)) department_count FROM employees WHERE department_id IS NOT NULL GROUP BY department_id; 

Таблица Employees. Получить список department_id в котором работают больше 30 сотрудников
Решение

 SELECT department_id FROM employees GROUP BY department_id HAVING COUNT (*) > 30; 

Таблица Employees. Получить список department_id и округленную среднюю зарплату работников в каждом департаменте.

Решение

 SELECT department_id, ROUND (AVG (salary)) avg_salary FROM employees GROUP BY department_id; 

Таблица Countries. Получить список region_id сумма всех букв всех country_name в котором больше 60ти
Решение

 SELECT region_id FROM countries GROUP BY region_id HAVING SUM (LENGTH (country_name)) > 60; 

Таблица Employees. Получить список department_id в котором работают работники нескольких (>1) job_id
Решение

 SELECT department_id FROM employees GROUP BY department_id HAVING COUNT (DISTINCT job_id) > 1; 

Таблица Employees. Получить список manager_id у которых количество подчиненных больше 5 и сумма всех зарплат его подчиненных больше 50000

Решение

 SELECT manager_id FROM employees GROUP BY manager_id HAVING COUNT (*) > 5 AND SUM (salary) > 50000; 

Таблица Employees. Получить список manager_id у которых средняя зарплата всех его подчиненных находится в промежутке от 6000 до 9000 которые не получают бонусы (commission_pct пустой)

Решение

 SELECT manager_id, AVG (salary) avg_salary FROM employees WHERE commission_pct IS NULL GROUP BY manager_id HAVING AVG (salary) BETWEEN 6000 AND 9000; 

Таблица Employees. Получить максимальную зарплату из всех сотрудников job_id которыз заканчивается на слово ‘CLERK’

Решение

SELECT MAX (salary) max_salary FROM employees WHERE job_id LIKE '%CLERK'; SELECT MAX (salary) max_salary FROM employees WHERE SUBSTR (job_id, -5) = 'CLERK'; 

Таблица Employees. Получить максимальную зарплату среди всех средних зарплат по департаменту
Решение

 SELECT MAX (AVG (salary)) FROM employees GROUP BY department_id; 

Таблица Employees. Получить количество сотрудников с одинаковым количеством букв в имени. При этом показать только тех у кого длина имени больше 5 и количество сотрудников с таким именем больше 20. Сортировать по длинне имени

Решение

 SELECT LENGTH (first_name), COUNT (*) FROM employees GROUP BY LENGTH (first_name) HAVING LENGTH (first_name) > 5 AND COUNT (*) > 20 ORDER BY LENGTH (first_name); SELECT LENGTH (first_name), COUNT (*) FROM employees WHERE LENGTH (first_name) > 5 GROUP BY LENGTH (first_name) HAVING COUNT (*) > 20 ORDER BY LENGTH (first_name); 

Displaying Data from Multiple Tables Using Joins

Таблица Employees, Departaments, Locations, Countries, Regions. Получить список регионов и количество сотрудников в каждом регионе

Решение

 SELECT region_name, COUNT (*) FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) JOIN countries c ON (l.country_id = c.country_id) JOIN regions r ON (c.region_id = r.region_id) GROUP BY region_name; 

Таблица Employees, Departaments, Locations, Countries, Regions. Получить детальную информацию о каждом сотруднике:
First_name, Last_name, Departament, Job, Street, Country, Region

Решение

SELECT First_name, Last_name, Department_name, Job_id, street_address, Country_name, Region_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) JOIN countries c ON (l.country_id = c.country_id) JOIN regions r ON (c.region_id = r.region_id); 

Таблица Employees. Показать всех менеджеров которые имеют в подчинении больше 6ти сотрудников
Решение

 SELECT man.first_name, COUNT (*) FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id) GROUP BY man.first_name HAVING COUNT (*) > 6; 

Таблица Employees. Показать всех сотрудников которые ни кому не подчиняются
Решение

SELECT emp.first_name FROM employees emp LEFT JOIN employees man ON (emp.manager_id = man.employee_id) WHERE man.FIRST_NAME IS NULL; SELECT first_name FROM employees WHERE manager_id IS NULL; 

Таблица Employees, Job_history. В таблице Employee хранятся все сотрудники. В таблице Job_history хранятся сотрудники которые покинули компанию. Получить репорт о всех сотрудниках и о его статусе в компании (Работает или покинул компанию с датой ухода)
Пример:
first_name | status
Jennifer | Left the company at 31 of December, 2006
Clara | Currently Working

Решение

SELECT first_name, NVL2 ( end_date, TO_CHAR (end_date, 'fm""Left the company at"" DD ""of"" Month, YYYY'), 'Currently Working') status FROM employees e LEFT JOIN job_history j ON (e.employee_id = j.employee_id); 

Таблица Employees, Departaments, Locations, Countries, Regions. Получить список сотрудников которые живут в Europe (region_name)

Решение

 SELECT first_name FROM employees JOIN departments USING (department_id) JOIN locations USING (location_id) JOIN countries USING (country_id) JOIN regions USING (region_id) WHERE region_name = 'Europe'; SELECT first_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) JOIN countries c ON (l.country_id = c.country_id) JOIN regions r ON (c.region_id = r.region_id) WHERE region_name = 'Europe'; 

Таблица Employees, Departaments. Показать все департаменты в которых работают больше 30ти сотрудников

Решение

SELECT department_name, COUNT (*) FROM employees e JOIN departments d ON (e.department_id = d.department_id) GROUP BY department_name HAVING COUNT (*) > 30; 

Таблица Employees, Departaments. Показать всех сотрудников которые не состоят ни в одном департаменте

Решение

SELECT first_name FROM employees e LEFT JOIN departments d ON (e.department_id = d.department_id) WHERE d.department_name IS NULL; SELECT first_name FROM employees WHERE department_id IS NULL; 

Таблица Employees, Departaments. Показать все департаменты в которых нет ни одного сотрудника
Решение

SELECT department_name FROM employees e RIGHT JOIN departments d ON (e.department_id = d.department_id) WHERE first_name IS NULL; 

Таблица Employees. Показать всех сотрудников у которых нет ни кого в подчинении
Решение

SELECT man.first_name FROM employees emp RIGHT JOIN employees man ON (emp.manager_id = man.employee_id) WHERE emp.FIRST_NAME IS NULL; 

Таблица Employees, Jobs, Departaments. Показать сотрудников в формате: First_name, Job_title, Department_name.
Пример:
First_name | Job_title | Department_name
Donald | Shipping | Clerk Shipping

Решение

SELECT first_name, job_title, department_name FROM employees e JOIN jobs j ON (e.job_id = j.job_id) JOIN departments d ON (d.department_id = e.department_id); 

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в 2005ом году но при это сами эти работники устроились на работу до 2005 года

Решение

SELECT emp.* FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id) WHERE TO_CHAR (man.hire_date, 'YYYY') = '2005' AND emp.hire_date < TO_DATE ('01012005', 'DDMMYYYY'); 

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в январе месяце любого года и длина job_title этих сотрудников больше 15ти символов

Решение

SELECT emp.* FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id) JOIN jobs j ON (emp.job_id = j.job_id) WHERE TO_CHAR (man.hire_date, 'MM') = '01' AND LENGTH (j.job_title) > 15; 

Using Subqueries to Solve Queries

Таблица Employees. Получить список сотрудников с самым длинным именем.

Решение

SELECT * FROM employees WHERE LENGTH (first_name) = (SELECT MAX (LENGTH (first_name)) FROM employees); 

Таблица Employees. Получить список сотрудников с зарплатой большей средней зарплаты всех сотрудников.

Решение

SELECT * FROM employees WHERE salary > (SELECT AVG (salary) FROM employees); 

Таблица Employees, Departments, Locations. Получить город в котором сотрудники в сумме зарабатывают меньше всех.

Решение

SELECT city FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) GROUP BY city HAVING SUM (salary) = ( SELECT MIN (SUM (salary)) FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) GROUP BY city); 

Таблица Employees. Получить список сотрудников у которых менеджер получает зарплату больше 15000.
Решение

SELECT * FROM employees WHERE manager_id IN (SELECT employee_id FROM employees WHERE salary > 15000) 

Таблица Employees, Departaments. Показать все департаменты в которых нет ни одного сотрудника
Решение

SELECT * FROM departments WHERE department_id NOT IN (SELECT department_id FROM employees WHERE department_id IS NOT NULL); 

Таблица Employees. Показать всех сотрудников которые не являются менеджерами
Решение

SELECT * FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL) 

Таблица Employees. Показать всех менеджеров которые имеют в подчинении больше 6ти сотрудников
Решение

SELECT * FROM employees e WHERE (SELECT COUNT (*) FROM employees WHERE manager_id = e.employee_id) > 6; 

Таблица Employees, Departaments. Показать сотрудников которые работают в департаменте IT
Решение

SELECT * FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT'); 

Таблица Employees, Jobs, Departaments. Показать сотрудников в формате: First_name, Job_title, Department_name.
Пример:
First_name | Job_title | Department_name
Donald | Shipping | Clerk Shipping

Решение

SELECT first_name, (SELECT job_title FROM jobs WHERE job_id = e.job_id) job_title, (SELECT department_name FROM departments WHERE department_id = e.department_id) department_name FROM employees e; 

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в 2005ом году но при это сами эти работники устроились на работу до 2005 года

Решение

SELECT * FROM employees WHERE manager_id IN (SELECT employee_id FROM employees WHERE TO_CHAR (hire_date, 'YYYY') = '2005') AND hire_date < TO_DATE ('01012005', 'DDMMYYYY'); 

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в январе месяце любого года и длина job_title этих сотрудников больше 15ти символов

Решение

SELECT * FROM employees e WHERE manager_id IN (SELECT employee_id FROM employees WHERE TO_CHAR (hire_date, 'MM') = '01') AND (SELECT LENGTH (job_title) FROM jobs WHERE job_id = e.job_id) > 15; 

На этом пока всё.

Надеюсь, задачи были интересными и увлекательными.
Буду по возможности дополнять этот список задач.
Также буду рад любым замечаниям и предложениям.

P.S.: Если кому то в голову придет интересная задача на SELECT, пишите в коментариях, добавлю в список.

  • Занимательные задачки
  • Oracle
  • SQL
  • Учебный процесс в IT

14 наиболее часто используемых запросов SQL на собеседовании (вопрос/ответ)

10 наиболее часто используемых запросов SQL (вопрос/ответ)

В этой статье мы даем некоторые примеры запросов SQL, которые часто задают, когда вы идете на собеседование по программированию, имеющий от одного-двух лет опыта в этой области. Идете ли вы на Java разработчика, QA, BA, поддерживаете профессиональные проекты или любую другую техническую позицию, там могут ожидать от вас ответ на основные вопросы из базы данных и SQL. Также очевидно, что, если вы работаете более 1 года на любом проекте есть хороший шанс, что вы столкнетесь с обработкой базы данных, написание запросов SQL для вставки, обновления, удаления и выбора записей. Один простой, но эффективный способ проверить навыки кандидата в SQL, задавая эти типы простых запросов. Они не являются ни очень сложными и не очень большими, но все же они охватывают все ключевые концепции, которые программист должен знать о SQL.

Эти запросы проверят ваши SQL навыки на работу, как внутреннее и внешнее соединение, фильтрация записей с помощью условий WHERE и HAVING, группировка записей с помощью предложения GROUP BY, вычисление суммы, среднего и подсчета записей с помощью совокупной функции как AVG(), SUM () и COUNT (), поиск записей с помощью символов подстановки в операторе LIKE, поиска записей в связанном с использованием BETWEEN и IN, запросы DATE и TIME и т.д. Если вы столкнулись с каким — либо интересным запросом в SQL или у вас есть какие — либо проблемы и поиск решения, вы можете разместить его здесь на благо каждого.

SQL Query. Вопросы и ответы

Вопрос 1: SQL-запрос, чтобы найти вторую самую высокую зарплату работника

Ответ: Есть много способов найти вторую самую высокую зарплату работника в SQL, вы можете использовать либо в SQL объединение или подзапрос, чтобы решить эту проблему. Вот SQL-запрос с использованием подзапроса:

select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );

См как найти вторую самую высокую зарплату в SQL с большим количеством способов решения этой проблемы.


Вопрос 2: SQL-запрос, чтобы найти максимальную зарплату от каждого отдела.

Ответ: Вы можете найти максимальную зарплату для каждого отдела, группируя все записи по DeptId, а затем с помощью функции MAX(), чтобы вычислить максимальную зарплату в каждой группе или каждого отдела.

SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID.

Эти вопросы становятся более интересными, если Интервьюер попросит вас напечатать название отдела вместо отдела ID, в этом случае, вам нужно соединить таблицу Employee с Department с использованием внешнего ключа DeptID, убедитесь, что вы используете LEFT or RIGHT OUTER JOIN включая отделов без каких — либо сотрудников. Вот запрос

SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID GROUP BY DeptName;

В этом запросе мы использовали RIGHT OUTER JOIN, потому что нам нужно название отдела из таблицы Department, который находится на правой стороне JOIN, даже если нет никакого упоминания о dept_id в таблице сотрудников.

Вопрос 3: Написать SQL — запрос для отображения текущей даты.

Ответ: SQL имеет встроенную функцию под названием GetDate(), которая возвращает текущую временную метку. Это будет работать в Microsoft SQL Server, другие производители, такие как Oracle и MySQL также имеет эквивалентные функции.

SELECT GetDate();


Вопрос 4: Напишите SQL запрос, чтобы проверить дату в передаваемом запросе, является ли дата данного формата или нет .

Ответ: SQL имеет функцию IsDate(), которая используется для проверки переданное значение, является дата или не указанного формата, он возвращает 1 (истина) или 0 (ложь), соответственно. Помните IsDate() является функцией MSSQL и он может не работать на Oracle, MySQL или любой другой базе данных, но не было бы что — то подобное.

SELECT ISDATE('1/08/13') AS "MM/DD/YY";

Она возвращает 0, потому что прошло дата не в правильном формате.


Вопрос 5: Напишите SQL запрос, чтобы напечатать имя отдельного работника у которого поле DOB составляет от 01/02/1965 до 31/11/1970.

Ответ: Этот SQL-запрос является сложным, но вы можете использовать BETWEEN, чтобы получить все записи, у которых дата выпадает между двумя датами.

SELECT DISTINCT EmpName FROM Employees WHERE DOB BETWEEN ‘01/02/1965’ AND ‘31/11/1970’;

Вопрос 6: написать SQL запрос, где найти количество сотрудников в зависимости от пола , чье поле DOB между 01/02/1965 до 31/11/1970.

SELECT COUNT(*), sex from Employees WHERE DOB BETWEEN '01/02/1965' AND '31/11/1970' GROUP BY sex;

Вопрос 7: Напишите SQL запрос, чтобы найти сотрудника, чья зарплата равна или превышает 20000.

SELECT EmpName FROM Employees WHERE Salary>=20000;

Вопрос 8: Напишите SQL запрос, чтобы найти имя сотрудника, чье имя начинается с ‘M’

SELECT * FROM Employees WHERE EmpName like 'M%';

Вопрос 9: Найти все записи о сотрудниках, содержащие слово «Роман», независимо от того, был ли он, как РОМАН, Роман, или роман.

SELECT * from Employees WHERE UPPER(EmpName) like '%РОМАН%';

Вопрос 10: Напишите SQL запрос, чтобы найти год от даты.

Ответ: Вот как вы можете найти год от даты в SQL Server 2008

SELECT YEAR(GETDATE()) as "Год";

Вопрос 11: Написать SQL — запрос для поиска дубликатов строк в базе данных? а затем написать SQL запрос, чтобы удалить их?
Ответ: Вы можете использовать следующий запрос для выбора различных записей:

SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno = b.empno)
DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno = b.empno);

Вопрос 12: Существует таблица, которая содержит два столбца Student и Marks, вам нужно найти всех студентов, чьи оценки являются больше, чем средние оценки, т.е. список студентов выше среднего.

Ответ: Этот запрос можно записать с помощью подзапросов, как показано ниже:

SELECT student, marks from table where marks > (SELECT AVG(marks) from table)

SQL Schema Interview Questions


Вопрос 13: Как вы находите всех сотрудников, которые являются также менеджерами?
Вы дали стандартную таблицу сотрудников с дополнительной колонкой mgr_id , которая содержит идентификатор сотрудника менеджера.

Ответ: Вы должны знать о автообъединение, чтобы решить эту проблему. В автообъединение, вы можете присоединить два экземпляра одной и той же таблицы, чтобы выяснить дополнительные подробности, как показано ниже

SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;

Запрос покажет имя сотрудника и имя менеджера в два столбца, например

Один из последующих изменит этот запрос, чтобы включать в себя сотрудников, которые не имеют статус менеджера. Для того, чтобы решить, что, вместо этого использовать inner join, просто используйте left outer join, он также будет включать сотрудников без менеджеров.

Вопрос 14: У вас есть композитный индекс из трех колонок, как вы обеспечиваете значение двух столбцов в WHERE на выборку? Будет ли индекс использоваться для этой операции?

Например, если индекс находится на EmpId, EmpFirstName и EmpSecondName и вы пишете запрос типа

SELECT * FROM Employee WHERE EmpId=2 and EmpFirstName='Radhe'

Если данные в две колонки являются вторичными индексами столбца, то индекс не будет ссылаться, но если данные 2 колонок содержат первичный индекс (первый столбец при создании индекса), то индекс будет ссылаться. В этом случае индекс будет использоваться, поскольку EmpId и EmpFirstName являются первичные столбцы.

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

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Подсчет данных при помощи запроса

В данной статье описано использование агрегатной функции для суммирования данных в результатах запроса. В ней также кратко описывается использование других агрегатных функций, например COUNT и AVG, для подсчета или вычисления средних значений в результирующем наборе записей. Кроме того, в этой статье рассматривается использование строки итогов — возможности Access, позволяющий суммировать данные без изменения структуры запросов.

Выберите нужное действие

  • Общее представление о способах суммирования данных
  • Подготовка примера данных
  • Суммирования данных с помощью строки итогов
  • Вычисление общих итогов с помощью запроса
  • Вычисление итогов по группе с помощью итогового запроса
  • Суммирование данных из нескольких групп с помощью перекрестного запроса
  • Справочные сведения об агрегатных функциях

Общее представление о способах суммирования данных

Просуммировать числовой столбец в запросе можно с помощью агрегатной функции. Агрегатные функции выполняют вычисления со столбцами данных и возвращают единственное значение. В Access существует множество агрегатных функций, включая Sum, Count, Avg (для вычисления среднего значения), Min и Max. Суммирование данных производится путем добавления в запрос функции Sum, подсчет данных — путем использования функции Count и т. д.

Кроме того, в Access предусмотрено несколько способов добавления функции Sum и других агрегатных функций в запрос. Вы можете:

  • Открыть запрос в режиме таблицы и добавить строку итогов. Строка итогов — возможность Access, позволяющая использовать агрегатные функции в одном или нескольких столбцах в результатах запроса без необходимости изменять его структуру.
  • Создать итоговый запрос. Итоговый запрос вычисляет промежуточные итоги для групп записей, а строка итогов — общие итоги для одного или нескольких столбцов (полей) данных. Например, если вы хотите вычислить промежуточную сумму всех продаж по городам или по кварталам, следует использовать итоговый запрос для группировки записей по нужной категории, а затем просуммировать все объемы продаж.
  • Создать перекрестный запрос. Перекрестный запрос — это особый тип запросов, отображающий результаты в сетке, напоминающей лист Excel. Перекрестные запросы суммируют значения и затем группируют их по двум наборам фактов — вдоль боковой стороны (заголовки строк) и в верхней части (заголовки столбцов). Например, вы можете использовать перекрестный запрос для отображения итоговых значений продаж для каждого города за последние три года, как показано в таблице:

Санкт-Петербург

Примечание: Ниже в разделах этой статьи подробно описано применение функции Sum, однако следует помнить, что вы можете использовать другие агрегатные функции в строках итогов и запросах. Дополнительные сведения об использовании других агрегатных функций см. ниже в разделе Справочные сведения об агрегатных функциях.

Дополнительные сведения о способах использования других агрегатных функций см. в разделе Отображение итогов по столбцу в таблице.

В следующих разделах описано, как добавить строку итогов, использовать итоговый запрос для суммирования данных и перекрестный запрос, вычисляющий промежуточные итоги по группам и интервалам времени. Помните, что многие агрегатные функции работают только с данными в полях, имеющих определенный тип данных. Например, функция SUM работает только с типами данных "Число", "Действительное" и "Денежный". Дополнительные сведения о типах данных, требуемых для каждой функции, см. ниже в разделе Справочные сведения об агрегатных функциях.

Общие сведения о типах данных см. в статье Изменение типа данных для поля.

Подготовка примера данных

В инструкциях этой статьи приводятся таблицы с примерами данных. Они помогают понять, как работают агрегатные функции. Вы можете добавить примеры таблиц в новую или существующую базу данных.

Сделать это в Access можно несколькими способами. Вы можете ввести данные вручную, скопировать каждую таблицу в редактор электронных таблиц (такой как Excel) и импортировать листы в Access или же вставить данные в текстовый редактор, например Блокнот, и импортировать их из созданных текстовых файлов.

В пошаговых инструкциях этого раздела объясняется, как вводить данные вручную на пустой лист, а также как копировать примеры таблиц в редактор электронных таблиц и затем импортировать их в Access. Дополнительные сведения о создании и импорте текстовых данных см. в статье Импорт данных или связывание с данными текстового файла.

В пошаговых инструкциях в этой статье используются приведенные ниже таблицы. Создайте пример данных на их основе:

Игры и головоломки

DVD-диски и фильмы

Модели для сборки, хобби

Наименование товара

Эксперименты с C# (игра для всей семьи)

Игры и головоломки

Схема реляционной базы данных

Волшебная микросхема (500 деталей)

Игры и головоломки

Игры и головоломки

Компьютерные маньяки и мифические животные

Упражнение для компьютерных мониторов! DVD-диск!

DVD-диски и фильмы

Неуловимая летающая пицца

Внешний дисковод гибких дисков 5.25'' (масштаб 1:4)

Модели для сборки, хобби

Недвижущаяся фигурка бюрократа

Модели для сборки, хобби

Таблица Заказы

Дата заказа

Дата отгрузки

Город назначения

Стоимость доставки

Таблица Сведения о заказах

Идентификатор заказа

Наименование товара

Цена за единицу

Недвижущаяся фигурка бюрократа

Упражнение для компьютерных мониторов! DVD-диск!

Компьютерные маньяки и мифические животные

Неуловимая летающая пицца

Внешний дисковод гибких дисков 5.25'' (масштаб 1:4)

Схема реляционной базы данных

Схема реляционной базы данных

Примечание: Помните, что в типичной базе данных таблица "Сведения о заказах" будет содержать только поле "Код товара" (без поля "Наименование товара"). В данном примере поле "Наименование товара" используется для упрощения восприятия данных.

Ввод примеров данных вручную

  1. на вкладке Создание в группе Таблицы нажмите кнопку Таблица. Access добавит в базу данных новую пустую таблицу.

Примечание: Эту операцию следует выполнять только при необходимости добавить таблицу в базу данных. При открытии новой пустой базы данных это действие не требуется.

Если вы не хотите вводить данные вручную, можно выполнить указанные ниже действия для копирования информации в файл электронной таблицы, а затем импортировать данные из него в Access.

Создание листов с примерами данных

  1. Запустите редактор электронных таблиц и создайте пустой файл. Если вы используете Excel, по умолчанию создается пустая книга.
  2. Скопируйте первый пример таблицы и вставьте его на первый лист, начиная с первой ячейки.
  3. Присвойте листу имя с помощью функций редактора электронных таблиц. Оно должно совпадать с именем примера таблицы. Например, если пример называется Категории, присвойте листу это же имя.
  4. Повторяйте шаги 2 и 3, чтобы скопировать каждый пример таблицы на пустой лист и переименовать этот лист.

Примечание: К файлу электронной таблицы может понадобиться добавить листы. Сведения о том, как сделать это, см. в справке редактора электронных таблиц.

Создание таблиц базы данных на основе листов

  1. На вкладке Внешние данные в группе Импорт щелкните Excel. -или- Нажмите кнопку Дополнительно, а затем выберите редактор электронных таблиц из списка. Откроется диалоговое окно Внешние данные — лист .
  2. Нажмите кнопку Обзор, откройте файл электронной таблицы, созданный на предыдущих этапах, и нажмите кнопку ОК. Откроется окно мастера импорта электронных таблиц.
  3. По умолчанию мастер выбирает первый лист в книге (в этом примере — лист Клиенты), и данные из этого листа появляются в нижней части страницы мастера. Нажмите кнопку Далее.
  4. На следующей странице мастера выберите Первая строка содержит названия столбцов, а затем нажмите кнопку Далее.
  5. Если нужно, вы можете изменить имена полей и типы данных или пропустить некоторые поля, воспользовавшись текстовыми полями и списками в группе Параметры поля. В противном случае нажмите кнопку Далее.
  6. Оставьте параметр автоматически создать ключ выбранным и нажмите кнопку Далее.
  7. По умолчанию Access использует имя листа для новой таблицы. Оставьте это имя или введите другое, а затем нажмите кнопку Готово.
  8. Повторите шаги с 1 по 7 для каждого листа книги Excel, чтобы создать для него таблицу.

Переименование полей первичного ключа

Примечание: При импорте листов Access автоматически добавляет в каждую таблицу столбец первичного ключа и по умолчанию присваивает ему имя "Код" и тип данных "Счетчик". В этом разделе даны инструкции по переименованию полей первичного ключа. Это позволяет четко определить все поля в запросе.

  1. В области навигации щелкните правой кнопкой мыши каждую таблицу, созданную на предыдущем шаге, и выберите команду Конструктор.
  2. Для каждой таблицы найдите поле первичного ключа. По умолчанию Access присваивает каждому полю имя Код.
  3. В столбце Имя поля каждого поля первичного ключа добавьте имя таблицы. Например, можно переименовать поле "ИД" в таблице "Категории" в "ИД категории", а поле таблицы "Заказы" — в "ИД заказа". В таблице "Сведения о заказе" переименуем поле в "Подробный ИД". В таблице "Товары" переименуем поле в "ИД товара".
  4. Сохраните изменения.

В последующих ссылках на примеры таблиц в данной статье указываются поля первичного ключа, переименованные, как описано выше.

Суммирования данных с помощью строки итогов

Чтобы добавить в запрос строку итогов, откройте его в режиме таблицы, добавьте строку, а затем выберите нужную агрегатную функцию, например Sum, Min, Max или Avg. В этом разделе объясняется, как создать простой запрос на выборку и добавить строку итогов. Не обязательно использовать примеры таблиц, представленные в предыдущем разделе.

Создание простого запроса на выборку

  1. На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
  2. Дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе. Выбранные таблицы отображаются в виде окон в верхней части конструктора запросов.
  3. Дважды щелкните поля таблицы, которые вы хотите использовать в запросе. Можно включить поля, содержащие описательные данные, например имена и описания, но следует обязательно добавить поле, содержащее числовые или денежные значения. Каждое поле отображается в ячейке в бланке запроса.
  4. Нажмите кнопку Выполнить для выполнения запроса. Результаты запроса будут отображены в режиме таблицы.
  5. При необходимости переключитесь в Конструктор и скорректируйте запрос. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Конструктор. После этого можно изменить запрос, добавив или удалив поля таблицы. Чтобы удалить поле, выберите столбец в бланке запроса и нажмите клавишу DELETE.
  6. Сохраните запрос.

Добавление строки итогов

  1. Убедитесь в том, что запрос открыт в режиме таблицы. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Режим таблицы. -или- Дважды щелкните запрос в области навигации. Запрос будет выполнен, а его результаты будут загружены в таблицу.
  2. На вкладке Главная в группе Записи нажмите кнопку Итоги. В таблице появится новая строка Итог.
  3. В строке Итог щелкните ячейку в поле, по которому вы хотите вычислить сумму, и выберите в списке функцию Sum.

Скрытие строки итогов

  • На вкладке Главная в группе Записи нажмите кнопку Итоги.

Дополнительные сведения об использовании строки итогов см. в разделе Отображение итогов по столбцу в таблице.

Вычисление общих итогов с помощью запроса

Общие итоги — это сумма по всем значениям столбца. Можно вычислять нескольких типов общих итогов, включая:

  • Простой общий итог, суммирующий значения одного столбца. Например, можно вычислить общую стоимость доставки.
  • Вычисляемый общий итог, суммирующий значения нескольких столбцов. Например, можно вычислить суммы продаж путем умножения цены нескольких товаров на число заказанных товаров, а затем просуммировав результирующие значения.
  • Общий итог за исключением нескольких записей. Например, можно вычислить сумму продаж только по последней пятнице.

В следующих разделах описано, как вычислить каждый из типов общих итогов. В инструкциях используются таблицы "Заказы" и "Сведения о заказах".

Таблица "Заказы"

Идентификатор заказа

Дата заказа

Дата отгрузки

Город назначения

Стоимость доставки

Таблица "Сведения о заказах"

Код сведений

Идентификатор заказа

Наименование товара

Цена за единицу

Недвижущаяся фигурка бюрократа

Упражнение для компьютерных мониторов! DVD-диск!

Компьютерные маньяки и мифические животные

Неуловимая летающая пицца

Внешний дисковод гибких дисков 5.25'' (масштаб 1:4)

Схема реляционной базы данных

Схема реляционной базы данных

Вычисление простого общего итога

  1. На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
  2. Дважды щелкните таблицу, которую вы хотите использовать в запросе. При использовании примера данных нужно дважды щелкнуть таблицу "Заказы". Таблица появится в окне в верхней части конструктора запросов.
  3. Дважды щелкните поле, для которого вы хотите найти сумму. Убедитесь, что поле имеет тип данных "Число" или "Денежный". При попытке суммировать значения в нечисловых полях, например в текстовом поле, Access выводит сообщение об ошибке Несоответствие типов данных в выражении условия отбора. Если вы используете пример данных, дважды щелкните столбец "Стоимость доставки". Вы можете добавить дополнительные числовые поля, если хотите вычислить для них общие итоги. Итоговый запрос может вычислять общие итоги для нескольких столбцов.
  4. На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги. В бланке появится строка Итог, а в ячейке столбца "Стоимость доставки" будет указано Группировка.
  5. Измените значение в ячейке строки Итог строки на Sum.
  6. Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить .

Совет: Обратите внимание, что Access добавит "СуммаOf" в начало имени поля, которое вы суммируете. Чтобы изменить заголовок столбца на более осмысленный, например "Всего доставки", переключиться обратно в конструктор и щелкните в строке "Поле" столбца "Стоимость доставки" в сетке конструктора. Поместите курсор рядом с искомой стоимостью доставки и введите слова Total Shippingи двоеточие, например: Total Shipping: Shipping Fee.

Вычисление общего итога за исключением нескольких записей

  1. На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
  2. Дважды щелкните таблицы "Заказы" и "Сведения о заказах".
  3. Добавьте поле "Дата заказа" из таблицы "Заказы" в первый столбец на бланке запроса.
  4. В строке Условие отбора первого столбца введите Date() -1. Это выражение исключает записи с текущей датой из вычисляемого итогового значения.
  5. Затем создайте столбец, который вычисляет объем продаж по каждой транзакции. Введите в строку Поле второго столбца на бланке следующее выражение: Общее значение продаж: (1-[Сведения о заказах].[Скидка]/100)*([Сведения о заказах].[Цена за единицу]*[Сведения о заказах].[Количество]) Убедитесь, что выражение ссылается на поля с типами данных "Число" или "Денежный". Если оно ссылается на поля с другими типами данных, то при попытке выполнения запроса появится сообщение Несоответствие типов данных в выражении условия отбора.
  6. На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги. В бланке появится строка Итог, а в первом и втором столбцах будет указано Группировка.
  7. Во втором столбце измените значение в ячейке строки Итог строки на Sum. Функция Sum суммирует отдельные показатели продаж.
  8. Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить .
  9. Сохраните запрос с именем Продажи за день.

Примечание: При следующем открытии запроса в Конструкторе можно заметить небольшие изменения в значениях, указанных для строк Поле и Итог в столбце "Общее значение продаж". Выражение заключено в функцию Sum, а в строке Итог выводится Выражение вместо функции Sum.

Вычисление итогов по группе с помощью итогового запроса

В этом разделе описано, как создать итоговый запрос для вычисления промежуточных итогов по группам данных. Помните, что по умолчанию итоговый запрос может включать только поле или поля, содержащие данные, по которым выполняется группировка, например поле "Категории", а также поля со значениями, которые вы хотите просуммировать, например поле "Продажи". Итоговые запросы не могут включать другие поля, описывающие значения внутри категории. Если вы хотите отобразить эти описательные данные, создайте второй запрос на выборку, объединяющий поля из итогового запроса с полями, содержащими дополнительные данные.

В этом разделе описано, как создавать итоговые запросы и запросы на выборку, если требуется определить объем продаж для каждого товара. В качестве примера используются следующие таблицы:

Таблицы "Товары"

Наименование товара

Эксперименты с C# (игра для всей семьи)

Игры и головоломки

Схема реляционной базы данных

Волшебная микросхема (500 деталей)

Игры и головоломки

Компьютерные маньяки и мифические животные

Упражнение для компьютерных мониторов! DVD-диск!

DVD-диски и фильмы

Неуловимая летающая пицца

Внешний дисковод гибких дисков 5.25'' (масштаб 1:4)

Модели для сборки, хобби

Недвижущаяся фигурка бюрократа

Модели для сборки, хобби

Таблица "Сведения о заказах"

Код сведений

Идентификатор заказа

Наименование товара

Цена за единицу

Недвижущаяся фигурка бюрократа

Упражнение для компьютерных мониторов! DVD-диск!

Компьютерные маньяки и мифические животные

Неуловимая летающая пицца

Внешний дисковод гибких дисков 5.25'' (масштаб 1:4)

Схема реляционной базы данных

Схема реляционной базы данных

Следующие действия также предполагают наличие отношения "один-ко-многим" между полями "Код товара" в таблицах "Заказы" и "Сведения о заказах" с таблицей "Заказы" на стороне "один" данного отношения.

Создание итогового запроса

  1. На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
  2. Выберите нужные таблицы и нажмите кнопку "Добавить". Таблица появится в виде окна в верхней части конструктора запросов. При использовании примеров таблиц, указанных выше, добавьте таблицы "Товары" и "Сведения о заказах".
  3. Дважды щелкните поля таблицы, которые вы хотите использовать в запросе. Как правило, в запрос добавляются только поле группы и поле значений. Однако вместо поля значения можно использовать вычисление — в следующих действиях объясняется, как это сделать.
    1. Добавьте поле "Категория" из таблицы "Товары" в бланк запроса.
    2. Создайте столбец, вычисляющий объемы продаж для каждой транзакции, введя во второй столбец бланка запроса следующее выражение: Общее значение продаж: (1-[Сведения о заказах].[Скидка]/100)*([Сведения о заказах].[Цена за единицу]*[Сведения о заказах].[Количество]) Убедитесь, что поля, на которые ссылается выражение, имеют типы данных "Число" или "Денежный". Если оно ссылается на поля с другими типами данных, то при попытке переключения в режим таблицы появится сообщение об ошибке Несоответствие типов данных в выражении условия отбора.
    3. На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги. В бланке появится строка Итог, в первом и втором столбцах которой будет указано Группировка.
    4. Во втором столбце измените значение в строке Итог строки на Sum. Функция Sum суммирует отдельные показатели продаж.
    5. Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить .
    6. Оставьте запрос открытым, чтобы использовать его в следующем разделе. Использование условий в итоговом запросе Запрос, созданный в предыдущем разделе, включает все записи в базовых таблицах. Он не исключает никакие заказы при вычислении итогов и отображает итоги для всех категорий. Если вам нужно исключить некоторые записи, можно добавить условия в запрос. Например, вы можете пропустить транзакции с суммой менее 100 ₽ или вычислить итоги только по некоторым категориям товаров. В этом разделе описано использование трех типов условий:
    7. Условия, игнорирующие некоторые группы при вычислении итогов. Например, можно вычислить итоги только для категорий товаров "Видеоигры", "Картины и рамы" и "Спортивное снаряжение".
    8. Условия, скрывающие некоторые итоговые значения после их вычисления. Например, можно отобразить только итоговые значения свыше 150 000 ₽.
    9. Условия, исключающие некоторые записи при вычислении итогового значения. Например, можно исключить отдельные транзакции, в которых значение (Цена за единицу * Количество) меньше 100 ₽. На примере следующих действий объясняется, как добавить условия одно за другим и как это повлияет на результаты запроса. Добавление условий в запрос
    10. Откройте запрос из предыдущего раздела в Конструкторе. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Конструктор. -или- Щелкните правой кнопкой мыши запрос в области навигации и выберите Конструктор.
    11. В строке Условия отбора столбца "Код категории" введите =Куклы Or Спортивное снаряжение or Картины и рамы.
    12. Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить .
    13. Вернитесь в Конструктор и в строке Условия отбора столбца "Общее значение продаж" введите >100.
    14. Выполните запрос, чтобы просмотреть результаты, а затем переключитесь в Конструктор.
    15. Теперь добавьте условия для исключения отдельных транзакций, сумма которых меньше 100 ₽. Для этого необходимо добавить еще один столбец.

    Примечание: Невозможно указать третье условие для столбца "Общее значение продаж". Любое условие, указанное для этого столбца, будет применено к итоговому значению, а не к отдельным значениям.

    Примечание: При следующем открытии запроса в Конструкторе можно заметить небольшие изменения в бланке. Во втором столбце выражение в строке Поле будет заключено в функцию Sum, а в строке Итоги будет указано Выражение вместо функции Sum.

    Суммирование данных из нескольких групп с помощью перекрестного запроса

    Перекрестный запрос — это особый тип запросов, отображающий результаты в сетке, напоминающей лист Excel. Перекрестные запросы суммируют значения и затем группируют их по двум наборам фактов — один набор вдоль боковой стороны (заголовки строк) и второй набор вдоль верхней части (заголовки столбцов). На рисунке показана часть набора результатов для примера перекрестного запроса.

    Помните, что перекрестный запрос не всегда заполняет все поля в наборе результатов, потому что таблицы, используемые в запросе, не всегда содержат значения для всех возможных точек данных.

    При создании перекрестного запроса обычно включаются данные из нескольких таблиц, и всегда включаются три типа данных: данные, используемые в качестве заголовков строк, данные, используемые в качестве заголовков столбцов и значения, которые вы хотите просуммировать или с которыми необходимо произвести другие вычисления.

    Инструкции в данном разделе предполагают использование следующих примеров таблиц:

    Таблица "Заказы"

    Дата заказа

    Дата отгрузки

    Город назначения

    Стоимость доставки

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *