11.05.2020 в 15:23

Упражнения по SQL - сложные запросы

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

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

Упражнения по SQL

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

Образцы таблиц и вставка демонстрационных данных

Шаг 1 (Создать таблицу)

Лучше создавать тестовые данные в отдельной базе данных. Итак, давайте сначала создадим базу данных для нашего тестирования.

CREATE database SQLTest;
USE SQLTest;

Здесь вы будете создавать две таблицы, а именно EMPLOYEE и DEPARTMENT. Упражнения будут вращаться вокруг них.

CREATE TABLE DEPARTMENT
(
   DEPTCODE   INT(10),
   DeptName   CHAR(30),
   LOCATION   VARCHAR(33)
);

CREATE TABLE EMPLOYEE
(
   EmpCode      INT(4),
   EmpFName     VARCHAR(15),
   EmpLName     VARCHAR(15),
   Job          VARCHAR(45),
   Manager      CHAR(4),
   HireDate     DATE,
   Salary       INT(6),
   Commission   INT(6),
   DEPTCODE     INT(2)
);

Шаг 2 (Изменить таблицу)

Теперь мы создали нужные таблицы SQL. Затем вы должны выполнить следующие команды, чтобы изменить структуру таблицы. Иногда очень полезно знать, как изменять существующие свойства таблицы.

ALTER TABLE DEPARTMENT
ADD PRIMARY KEY (DEPTCODE);

ALTER TABLE DEPARTMENT
CHANGE COLUMN DEPTCODE DEPTCODE INT(10) NOT NULL;

ALTER TABLE DEPARTMENT
CHANGE COLUMN DeptName DeptName CHAR(30) UNIQUE;

ALTER TABLE DEPARTMENT
CHANGE COLUMN LOCATION LOCATION VARCHAR(33) NOT NULL;

ALTER TABLE DEPARTMENT
CHANGE COLUMN DeptName DeptName VARCHAR(15) UNIQUE;

ALTER TABLE EMPLOYEE
ADD PRIMARY KEY (EmpCode);

ALTER TABLE EMPLOYEE
CHANGE COLUMN EmpCode EmpCode INT(4) NOT NULL;

ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (DEPTCODE)
REFERENCES DEPARTMENT(DEPTCODE);

ALTER TABLE EMPLOYEE
CHANGE COLUMN Salary Salary DECIMAL(6,2);

ALTER TABLE EMPLOYEE
ADD COLUMN DOB DATE
AFTER EmpLName;

ALTER TABLE EMPLOYEE
DROP COLUMN DOB;

Шаг 3 (заполнить таблицу)

Ниже инструкция INSERT заполнит вышеприведенные таблицы демонстрационными данными, которые вы можете использовать для выполнения запросов.

INSERT INTO DEPARTMENT VALUES (10, 'FINANCE', 'EDINBURGH'),
                              (20,'SOFTWARE','PADDINGTON'),
                              (30, 'SALES', 'MAIDSTONE'),
                              (40,'MARKETING', 'DARLINGTON'),
                              (50,'ADMIN', 'BIRMINGHAM');
                       
INSERT INTO EMPLOYEE  
VALUES (9369, 'TONY', 'STARK', 'SOFTWARE ENGINEER', 7902, '1980-12-17', 2800,0,20),
       (9499, 'TIM', 'ADOLF', 'SALESMAN', 7698, '1981-02-20', 1600, 300,30),    
       (9566, 'KIM', 'JARVIS', 'MANAGER', 7839, '1981-04-02', 3570,0,20),
       (9654, 'SAM', 'MILES', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
       (9782, 'KEVIN', 'HILL', 'MANAGER', 7839, '1981-06-09', 2940,0,10),
       (9788, 'CONNIE', 'SMITH', 'ANALYST', 7566, '1982-12-09', 3000,0,20),
       (9839, 'ALFRED', 'KINSLEY', 'PRESIDENT', 7566, '1981-11-17', 5000,0, 10),
       (9844, 'PAUL', 'TIMOTHY', 'SALESMAN', 7698, '1981-09-08', 1500,0,30),
       (9876, 'JOHN', 'ASGHAR', 'SOFTWARE ENGINEER', 7788, '1983-01-12',3100,0,20),
       (9900, 'ROSE', 'SUMMERS', 'TECHNICAL LEAD', 7698, '1981-12-03', 2950,0, 20),
       (9902, 'ANDREW', 'FAULKNER', 'ANAYLYST', 7566, '1981-12-03', 3000,0, 10),
       (9934, 'KAREN', 'MATTHEWS', 'SOFTWARE ENGINEER', 7782, '1982-01-23', 3300,0,20),
       (9591, 'WENDY', 'SHAWN', 'SALESMAN', 7698, '1981-02-22', 500,0,30),
       (9698, 'BELLA', 'SWAN', 'MANAGER', 7839, '1981-05-01', 3420, 0,30),
       (9777, 'MADII', 'HIMBURY', 'ANALYST', 7839, '1981-05-01', 2000, 200, NULL),
       (9860, 'ATHENA', 'WILSON', 'ANALYST', 7839, '1992-06-21', 7000, 100, 50),
       (9861, 'JENNIFER', 'HUETTE', 'ANALYST', 7839, '1996-07-01', 5000, 100, 50);

Упражнения по SQL для базовых и расширенных запросов

# 1 Создайте запрос, который отображает таблицы EMPFNAME, EMPLNAME, DEPTCODE, DEPTNAME, LOCATION из EMPLOYEE и DEPARTMENT. Убедитесь, что результаты в порядке возрастания на основе отдела EMPFNAME и LOCATION.

SELECT E.EMPFNAME, E.EMPLNAME, E.DEPTCODE,
       D.DEPTNAME, D.LOCATION
       FROM EMPLOYEE E, DEPARTMENT D
       WHERE E.DEPTCODE = D.DEPTCODE
       ORDER BY E.EMPFNAME, D.LOCATION;

# 2 Отображение EMPFNAME и «TOTAL SALARY» для каждого сотрудника

SELECT EMPFNAME, SUM(COMMISSION+SALARY) AS "TOTAL SALARY" FROM EMPLOYEE GROUP BY EMPCODE;

# 3 Отобразите MAX и 2nd MAX SALARY из таблицы EMPLOYEE.

SELECT
(SELECT MAX(SALARY) FROM EMPLOYEE) MAXSALARY,
(SELECT MAX(SALARY) FROM EMPLOYEE
WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE )) as 2ND_MAX_SALARY;

# 4 Показать общую сумму заработной платы, нарисованную аналитиком, работающим в отделе № 20

SELECT SUM(SALARY+COMMISSION) AS TOTALSALARY FROM EMPLOYEE
WHERE JOB = 'ANALYST' AND DEPTCODE = 20;

# 5 Рассчитать среднюю, минимальную и максимальную заработную плату группы сотрудников, имеющих работу ANALYST.

SELECT AVG(Salary) AS AVG_SALARY, MIN(Salary) AS MINSALARY, MAX(Salary) AS MAXSALARY
FROM EMPLOYEE WHERE Job = 'ANALYST';

Обратите внимание, что мы будем добавлять все больше и больше SQL-запросов в этот пост на основе ваших отзывов. Поэтому, пожалуйста, поделитесь своими вопросами с нами.

Источник:

user

#SQL

8180

Войти на сайт

Упражнения по SQL - сложные запросы