use pistachio;
CREATE TABLE customers(
    customerid int primary key identity,
    customer_name varchar(60),
    city varchar(60) check(city!='Перник'),
    address  varchar(100),
    email varchar(50) unique,
    age tinyint
);
INSERT INTO customers VALUES('Петър Цветанов', NULL, NULL, NULL, NULL);
INSERT INTO customers VALUES('Кирил Иванов', 'Враца', NULL, NULL, NULL);
INSERT INTO customers (customer_name, city, email) VALUES('Самул Велики', 'Перник', 'samito@gmail.com');
UPDATE customers
set age = 16

ALTER TABLE customers
WITH NOCHECK
add constraint CK_age check(age>17)

CREATE TABLE employees(
    employeeid int primary key identity,
    first_name varchar(60),
    last_name varchar(60),
    city varchar(60) check(LOWER(city) IN('варна','бургас')),
    age tinyint check(age BETWEEN 25 AND 35),
    address  varchar(100)
);

CREATE TABLE [pistachio].[dbo].[orders](
    customerid int REFERENCES customers(customerid),
    employeeid int REFERENCES employees(employeeid),
    order_date date DEFAULT getdate(),
    delivery_date date,

    constraint PK_orders primary key(customerid, employeeid, order_date),
    constraint CK_delivery_date check (datediff(day, order_date, delivery_date) < 5)
);