forked from ninagurh/hiringtest
-
Notifications
You must be signed in to change notification settings - Fork 0
/
1_carDealershipQuestion.sql
116 lines (103 loc) · 3.66 KB
/
1_carDealershipQuestion.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
--- SQLIZE AT: https://sqlize.online/s/XR
DROP TABLE IF EXISTS departments CASCADE;
DROP TABLE IF EXISTS employees CASCADE;
DROP TABLE IF EXISTS cars CASCADE;
DROP TABLE IF EXISTS customers CASCADE;
DROP TABLE IF EXISTS sales CASCADE;
-- schema
CREATE TABLE departments (
id SERIAL NOT NULL,
Name VARCHAR(25) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE employees (
id INT NOT NULL,
FName VARCHAR(35) NOT NULL,
LName VARCHAR(35) NOT NULL,
PhoneNumber VARCHAR(11),
ManagerId INT,
DepartmentId INT NOT NULL,
Salary INT NOT NULL,
HireDate timestamp NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (ManagerId) REFERENCES Employees(id),
FOREIGN KEY (DepartmentId) REFERENCES Departments(id)
);
CREATE TABLE customers (
id SERIAL NOT NULL,
FName VARCHAR(35) NOT NULL,
LName VARCHAR(35) NOT NULL,
Email varchar(100) NOT NULL,
PhoneNumber VARCHAR(11),
PreferredContact VARCHAR(5) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE cars (
id SERIAL NOT NULL,
Model varchar(50) NOT NULL,
Color varchar(50) NOT NULL,
TotalCost INT NOT NULL,
PRIMARY KEY(id),
UNIQUE(model,color,totalcost)
);
-- data
INSERT INTO departments
(id, Name)
VALUES
(1, 'HR'),
(2, 'Sales'),
(3, 'Tech')
;
INSERT INTO employees
(id, FName, LName, PhoneNumber, ManagerId, DepartmentId, Salary, HireDate)
VALUES
(1, 'James', 'Smith', 1234567890, NULL, 1, 1000, '01-01-2002'::timestamp without time zone),
(2, 'John', 'Johnson', 2468101214, '1', 1, 400, '03-03-2005'::timestamp without time zone),
(3, 'Michael', 'Williams', 1357911131, '1', 2, 600, '12-05-2009'::timestamp without time zone),
(4, 'Johnathon', 'Smith', 1212121212, '2', 1, 500, '07-07-2016'::timestamp without time zone),
(5, 'William', 'Morris', 5555555555, '1', 2, 800, '08-05-2009'::timestamp without time zone);
INSERT INTO customers
(id, FName, LName, Email, PhoneNumber, PreferredContact)
VALUES
(1, 'William', 'Jones', '[email protected]', '3347927472', 'PHONE'),
(2, 'David', 'Miller', '[email protected]', '2137921892', 'EMAIL'),
(3, 'Richard', 'Davis', '[email protected]', NULL, 'EMAIL'),
(4, 'Elon', 'Musk', '[email protected]', NULL, 'EMAIL'),
(5, 'John', 'Dutton', '[email protected]', NULL, 'EMAIL')
;
INSERT INTO cars
(id, Model, Color, TotalCost)
VALUES
('1', 'Ford F-150', 'Black', '230'),
('2', 'Ford F-150', 'Silver', '200'),
('3', 'Ford Mustang', 'Red', '350'),
('4', 'Tesla Model 3', 'Blue', '325'),
('5', 'Ford F-350', 'Brown', '500')
;
-- DEPARTMENTS
SELECT * FROM departments;
-- EMPLOYEES
SELECT * FROM employees;
-- CUSTOMERS
SELECT * FROM customers;
-- CARS
SELECT * FROM cars;
-- TASK 1 GOAL:
-- The car dealership owner wants to set a markup on the TotalCost of cars, and be able
-- to track the number of cars in inventory.
--
-- The markup percentage per model should be as follows:
-- Ford F series (any size) = 15% Tesla = 25% Ford Mustang = 10%
-- A markup percentage is required to be defined per vehicle after your changes
-- and a default markup percentage of 10% should be defined for any future cars.
--
-- There is currently an inventory of 5 for each model on the showroom floor.
--
-- Write the required SQL Commands to ALTER the above tables and insert/update
-- data to support the new requirements.
--
-- TASK 1.5 GOAL:
-- Write an "INSERT" Command that will add inventory as it is received from shippers.
-- Use a Blue Ford Mustang that costs the dealership $350 as your insert example.
--
-- Q/A: How would you handle the insert if the color was 'Red' instead?