SQL TUTORIALS

Answer Key

1. CREATE TABLE employees(id INT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, hire_date DATE, wage FLOAT NOT NULL);
2. CREATE TABLE customers(id INT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, phone_num CHAR(10) NOT NULL UNIQUE, address VARCHAR(250), city VARCHAR(100), state CHAR(2), zip CHAR(5));
3. CREATE TABLE categories(id INT PRIMARY KEY, name VARCHAR(25) NOT NULL, description VARCHAR(100));
4. CREATE TABLE colors(id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, description VARCHAR(100));
5. CREATE TABLE inventory(id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, description VARCHAR(250) NOT NULL, quantity INT NOT NULL, price FLOAT NOT NULL, name_brand VARCHAR(5) NOT NULL, category_id INT NOT NULL, color_id INT NOT NULL, FOREIGN KEY (category_id) REFERENCES categories(id), FOREIGN KEY (color_id) REFERENCES colors(id));
6. DROP TABLE colors;
7. ALTER TABLE customers ADD COLUMN age INT;
8. ALTER TABLE inventory ADD CONSTRAINT name_brand_chk CHECK (name_brand IN('yes', 'no', 'unknown'));
9.