-- Baza do ćwiczeń: SELECT przed FROM (MariaDB)
-- Data przygotowania: 2026-03-16

DROP DATABASE IF EXISTS cwiczenia_select;
CREATE DATABASE cwiczenia_select
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_polish_ci;

USE cwiczenia_select;

-- 1) users: do DISTINCT, CONCAT, LOWER/UPPER, TRIM, SUBSTRING, REPLACE, IF, CASE, IFNULL/COALESCE
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name  VARCHAR(50) NOT NULL,
  email      VARCHAR(120) NOT NULL,
  phone      VARCHAR(40) NULL,
  nickname   VARCHAR(50) NULL,
  age        INT NULL,
  active     TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL
);

INSERT INTO users (first_name, last_name, email, phone, nickname, age, active, created_at) VALUES
(' Jan',  'Kowalski',   'Jan.Kowalski@Example.com', '600 100 200', NULL,     34, 1, '2026-01-05 10:15:00'),
('Anna',  'Nowak',      'anna.nowak@example.com',   NULL,          'Ania',   17, 1, '2026-01-06 08:00:00'),
('Piotr', 'Zieliński',  'piotr.zielinski@example.com','500 200 300',NULL,     18, 0, '2026-01-07 12:30:00'),
('Jan',   'Kowalski',   'jan.kowalski@example.com', '600100200',   'JK',     34, 1, '2026-01-08 09:45:00'),
('Ewa',   'Wiśniewska', 'ewa.wisniewska@example.com','700 333 444', NULL,     29, 1, '2026-02-01 16:20:00'),
('Ola',   '  Kaczmarek','ola.kaczmarek@example.com',' 800 111 222 ',NULL,     22, 1, '2026-02-10 11:11:11'),
('Tomek', 'Wójcik',     'tomek.wojcik@example.com',  NULL,          NULL,     NULL,1, '2026-03-01 14:00:00');

-- 2) products: do ROUND, CEIL/FLOOR, arytmetyki, ABS
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  price_net DECIMAL(10,4) NOT NULL,
  vat_rate DECIMAL(4,2) NOT NULL DEFAULT 0.23,
  created_at DATETIME NOT NULL
);

INSERT INTO products (name, price_net, vat_rate, created_at) VALUES
('Kawa 250g',     19.9900, 0.23, '2026-01-10 09:00:00'),
('Herbata 100g',   9.5000, 0.23, '2026-01-12 09:00:00'),
('Czekolada 90g',  6.6666, 0.23, '2026-01-20 10:00:00'),
('Mleko 1L',       3.3333, 0.05, '2026-02-02 07:30:00'),
('Chleb',          4.2000, 0.05, '2026-02-03 06:10:00'),
('Ser 200g',      12.3456, 0.05, '2026-02-04 12:00:00');

-- 3) accounts: do ABS (np. saldo ujemne), ROUND, IF
CREATE TABLE accounts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  balance DECIMAL(12,2) NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_accounts_user FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO accounts (user_id, balance, updated_at) VALUES
(1,  120.00, '2026-03-01 10:00:00'),
(2,  -15.49, '2026-03-01 10:00:00'),
(3,    0.00, '2026-03-05 18:00:00'),
(4, 9999.99, '2026-03-10 09:30:00'),
(5,  -0.01,  '2026-03-12 21:00:00');

-- 4) orders: do DATE(), TIME(), DATE_FORMAT()
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  total DECIMAL(12,4) NOT NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO orders (user_id, total, created_at) VALUES
(1,  24.5899, '2026-02-01 09:10:00'),
(1, 100.0000, '2026-02-01 19:45:30'),
(2,   9.5000, '2026-02-15 12:00:00'),
(4,  19.9900, '2026-03-01 08:08:08'),
(5,   3.3333, '2026-03-02 23:59:59');

-- 5) employees: do mini-przykładu z notatki + DISTINCT + CONCAT_WS + ROUND + IFNULL + DATE_FORMAT
CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name  VARCHAR(50) NOT NULL,
  salary     DECIMAL(12,4) NOT NULL,
  phone      VARCHAR(40) NULL,
  created_at DATETIME NOT NULL
);

INSERT INTO employees (first_name, last_name, salary, phone, created_at) VALUES
('Jan',   'Nowak',     5000.5555, NULL,          '2026-01-03 09:00:00'),
('Anna',  'Nowak',     5000.5555, '123 456 789', '2026-01-03 09:00:00'),
('Piotr', 'Kowalski',  7499.9950, '222333444',   '2026-02-20 15:15:15'),
('Ewa',   'Kowalska',  3200.1000, NULL,          '2026-03-10 10:10:10'),
('Ola',   'Zając',     3200.1050, '999 888 777', '2026-03-11 11:11:11');

-- 6) t: prosta tabela do (a+b)*c oraz NULLIF(status,'') i MOD(id,2)
CREATE TABLE t (
  id INT PRIMARY KEY AUTO_INCREMENT,
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL,
  status VARCHAR(20) NOT NULL
);

INSERT INTO t (a, b, c, status) VALUES
(1, 2, 3, ''),
(2, 2, 2, 'ok'),
(5, 0, 4, 'ok'),
(10, 5, 2, 'hold');

-- Opcjonalnie: widok "dla ćwiczeń" (nie jest konieczny)
-- CREATE VIEW v_users_basic AS
-- SELECT id, first_name, last_name, email, phone, nickname, age, active, created_at FROM users;