-- Задание 1: Таблица сотрудников (employees)
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
salary DECIMAL(10, 2),
bonus DECIMAL(10, 2)
);
INSERT INTO employees (id, name, salary, bonus) VALUES
(1, 'Alice', 50000, NULL),
(2, 'Bob', 60000, 5000),
(3, 'Charlie', 55000, NULL),
(4, 'David', 70000, 7000),
(5, 'Eve', 65000, 3000);
-- 1. Кто имеет NULL в bonus
SELECT id, name, salary, bonus,
CASE WHEN bonus IS NULL THEN 'No Bonus' ELSE 'Has Bonus' END AS bonus_status
FROM employees;
-- 2. Замена NULL на 0
SELECT id, name, salary, COALESCE(bonus, 0) AS bonus FROM employees;
-- Задание 2: Таблица товаров (products)
DROP TABLE IF EXISTS products;
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
name TEXT,
price DECIMAL(10,2)
);
INSERT INTO products (product_id, name, price) VALUES
(1, 'Product A', 10.00),
(2, 'Product B', NULL),
(3, 'Product C', 20.00),
(4, 'Product D', NULL);
-- 1. Только с ценой
SELECT * FROM products WHERE price IS NOT NULL;
-- 2. Без цены
SELECT * FROM products WHERE price IS NULL;
-- 3. Обновить NULL цен на среднюю цену
UPDATE products
SET price = (SELECT AVG(price) FROM products WHERE price IS NOT NULL)
WHERE price IS NULL;
-- Задание 3: Таблица клиентов (customers)
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name TEXT,
email TEXT
);
INSERT INTO customers (customer_id, name, email) VALUES
(1, 'Ivan', 'ivan@example.com'),
(2, 'Maria', NULL),
(3, 'Petr', 'petr@example.com'),
(4, 'Olga', NULL),
(5, 'Dmitry', 'ivan@example.com');
-- 1. У кого нет email
SELECT customer_id, name, email,
CASE WHEN email IS NULL THEN 'No Email' ELSE 'Has Email' END AS email_status
FROM customers;
-- 2. Повторяющиеся email (включая NULL как отдельную группу)
SELECT email, COUNT(*) AS count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
-- 3. Почему email = NULL не работает?
-- Потому что NULL — неизвестное значение. Нужно использовать IS NULL вместо = NULL
-- Задание 4: Таблица продаж (sales)
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
sale_id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2)
);
INSERT INTO sales (sale_id, customer_id, amount) VALUES
(1, 1, 100.00),
(2, 2, NULL),
(3, 3, 150.00),
(4, 4, 200.00),
(5, 1, NULL);
-- 1. Сумма продаж (NULL игнорируются)
SELECT SUM(amount) AS total_sales FROM sales;
-- 2. Средняя сумма (NULL игнорируются)
SELECT AVG(amount) AS average_sales FROM sales;
-- 3. Сумма с заменой NULL на 0
SELECT SUM(COALESCE(amount, 0)) AS total_with_nulls_as_zero FROM sales;
-- Задание 5: Таблица заказов (orders)
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
delivery_date TEXT
);
INSERT INTO orders (order_id, customer_id, delivery_date) VALUES
(1, 1, NULL),
(2, 2, '2024-05-10'),
(3, 3, NULL),
(4, 4, '2024-05-12');
-- 1. Заказы без даты доставки
SELECT * FROM orders WHERE delivery_date IS NULL;
-- 2. Обновить NULL на текущую дату (эмулируем, т.к. SQLite не поддерживает CURRENT_DATE в UPDATE)
UPDATE orders
SET delivery_date = DATE('now')
WHERE delivery_date IS NULL;
-- 3. Объяснение:
-- Сравнение delivery_date = '2024-05-10' не сработает для NULL,
-- потому что NULL нельзя сравнивать через =. Используй IS NULL / IS NOT NULL.
Telegram: DB developers channel