SQL - Standard Test Data

-- Create database if needed
CREATE DATABASE IF NOT EXISTS company;
USE company;

-- Tables
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE
);

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  department_id INT NULL,
  salary DECIMAL(10,2) NULL,
  hired_at DATETIME NULL,
  CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Seed departments
INSERT INTO departments (name) VALUES
  ('Engineering'),
  ('HR'),
  ('Sales');

-- Seed employees
INSERT INTO employees (first_name, department_id, salary, hired_at) VALUES
  ('Ada', 1, 120000.00, '2021-01-10 10:00:00'),
  ('Bob', 1,  90000.00, '2021-05-05 09:30:00'),
  ('Chen',2,  80000.00, NULL),
  ('Dee', 3, 110000.00, '2022-03-01 12:00:00'),
  ('Eli', 1, 130000.00, '2020-11-20 15:15:00');

-- Create DB if needed
IF DB_ID(N'company') IS NULL BEGIN
  CREATE DATABASE company;
END
GO
USE company;
GO

IF OBJECT_ID('dbo.employees') IS NOT NULL DROP TABLE dbo.employees;
IF OBJECT_ID('dbo.departments') IS NOT NULL DROP TABLE dbo.departments;

CREATE TABLE dbo.departments (
  id INT IDENTITY(1,1) PRIMARY KEY,
  name NVARCHAR(80) NOT NULL UNIQUE
);

CREATE TABLE dbo.employees (
  id INT IDENTITY(1,1) PRIMARY KEY,
  first_name NVARCHAR(50) NOT NULL,
  department_id INT NULL,
  salary DECIMAL(10,2) NULL,
  hired_at DATETIME2 NULL,
  CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) REFERENCES dbo.departments(id)
);

INSERT INTO dbo.departments(name) VALUES (N'Engineering'), (N'HR'), (N'Sales');

INSERT INTO dbo.employees(first_name, department_id, salary, hired_at) VALUES
  (N'Ada',  1, 120000.00, '2021-01-10T10:00:00'),
  (N'Bob',  1,  90000.00, '2021-05-05T09:30:00'),
  (N'Chen', 2,  80000.00, NULL),
  (N'Dee',  3, 110000.00, '2022-03-01T12:00:00'),
  (N'Eli',  1, 130000.00, '2020-11-20T15:15:00');

-- From the sqlite3 CLI, open/create the file DB:
--   $ sqlite3 company.db

DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT NOT NULL,
  department_id INTEGER NULL,
  salary NUMERIC NULL,
  hired_at TEXT NULL, -- ISO8601
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

INSERT INTO departments(name) VALUES ('Engineering'), ('HR'), ('Sales');

INSERT INTO employees(first_name, department_id, salary, hired_at) VALUES
  ('Ada',  1, 120000.00, '2021-01-10 10:00:00'),
  ('Bob',  1,  90000.00, '2021-05-05 09:30:00'),
  ('Chen', 2,  80000.00, NULL),
  ('Dee',  3, 110000.00, '2022-03-01 12:00:00'),
  ('Eli',  1, 130000.00, '2020-11-20 15:15:00');