· 4 years ago · Jun 05, 2021, 03:14 PM
1/******************************************************************************************************************
2'The Office' sample schema
3Reddit URL: https://www.reddit.com/r/webdev/comments/nsbr3l/am_i_using_too_many_sql_joins_in_my_api_query/
4
5Platform: SQL Server
6******************************************************************************************************************/
7
8
9-- #############################
10-- Create tables
11-- #############################
12
13
14/**************************************
15 EMPLOYEE
16**************************************/
17DROP TABLE IF EXISTS oEmployee;
18GO
19CREATE TABLE oEmployee (
20 ID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
21 [name] VARCHAR(127)
22);
23GO
24
25/**************************************
26 CUSTOMER
27**************************************/
28DROP TABLE IF EXISTS oCustomer;
29GO
30CREATE TABLE oCustomer (
31 ID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL
32 , [name] VARCHAR(127)
33);
34GO
35
36
37/**************************************
38 DEPARTMENT
39**************************************/
40DROP TABLE IF EXISTS oDepartment;
41GO
42CREATE TABLE oDepartment (
43 ID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
44 [name] VARCHAR(127)
45);
46GO
47
48/**************************************
49 JOB
50**************************************/
51DROP TABLE IF EXISTS oJob;
52GO
53CREATE TABLE oJob (
54 ID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL
55 , title VARCHAR(127)
56 , department_id INT
57);
58GO
59
60/**************************************
61 OFFICE
62**************************************/
63DROP TABLE IF EXISTS oOffice;
64GO
65CREATE TABLE oOffice (
66 ID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
67 [name] VARCHAR(127)
68);
69GO
70
71
72/**************************************
73 JOB OFFICE
74**************************************/
75DROP TABLE IF EXISTS oJobOffice;
76GO
77CREATE TABLE oJobOffice (
78 ID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL
79 , office_id INT NOT NULL
80 , job_id INT NOT NULL
81);
82GO
83
84
85/**************************************
86 EMPLOYEE CUSTOMER
87**************************************/
88DROP TABLE IF EXISTS oEmpCust;
89GO
90CREATE TABLE oEmpCust (
91 employee_id INT NOT NULL
92 , customer_id INT NOT NULL
93 , job_office_id INT NOT NULL
94);
95GO
96
97
98-- #############################
99-- Add table constraints.
100-- #############################
101
102ALTER TABLE oJob
103ADD CONSTRAINT fk_tmp_deptid
104 FOREIGN KEY (department_id)
105 REFERENCES oDepartment (ID)
106 ON DELETE CASCADE
107 ON UPDATE CASCADE
108
109
110ALTER TABLE oJobOffice
111ADD CONSTRAINT fk_tmp_offceid FOREIGN KEY (office_id)
112 REFERENCES oOffice (ID)
113 ON DELETE CASCADE
114 ON UPDATE CASCADE
115 , CONSTRAINT fk_tmp_jobid FOREIGN KEY (job_id)
116 REFERENCES oJob (ID)
117 ON DELETE CASCADE
118 ON UPDATE CASCADE
119
120
121ALTER TABLE oEmpCust
122ADD CONSTRAINT pk_ix_empcust PRIMARY KEY (employee_id, customer_id, job_office_id)
123 , CONSTRAINT fk_tmp_empid FOREIGN KEY (employee_id)
124 REFERENCES oEmployee (ID)
125 ON DELETE CASCADE
126 ON UPDATE CASCADE
127 , CONSTRAINT fk_tmp_custid FOREIGN KEY (customer_id)
128 REFERENCES oCustomer (ID)
129 ON DELETE CASCADE
130 ON UPDATE CASCADE
131 , CONSTRAINT fk_tmp_jobdeptid FOREIGN KEY (job_office_id)
132 REFERENCES oJobOffice (ID)
133 ON DELETE CASCADE
134 ON UPDATE CASCADE
135
136
137
138-- ###################################
139-- Insert sample data into each table
140-- ###################################
141INSERT oEmployee VALUES ('Jim Halpert'), ('Karen Filippelli')
142INSERT oCustomer VALUES ('Scranton Law Offices'), ('Anthracite Heritage Museum'), ('Electric City Trolley Station')
143INSERT oOffice VALUES ('Scranton'), ('Utica')
144INSERT oDepartment VALUES ('Sales')
145INSERT oJob VALUES ('Account Representative', 1), ('Sr. Account Representative', 1)
146
147
148
149-- ###################################
150-- Insert all combinatons of jobs and
151-- offices in to oJobOffice table.
152-- ###################################
153INSERT oJobOffice(office_id, job_id)
154SELECT
155 O.ID AS [office_id]
156, J.ID AS [job_id]
157FROM oOffice AS O
158, oJob AS J
159
160
161
162-- ############################################################
163-- Insert known data (e.g - who is a rep for which company)
164-- into oEmpCust table.
165-- ############################################################
166INSERT oEmpCust
167VALUES (1, 1, 3)
168, (1, 2, 4)
169, (2, 2, 2)
170, (2, 3, 2)
171
172
173-- ############################################################
174-- Create a VIEW to make life easier.
175-- * You can use WHERE clauses just like a normal table.
176--
177-- Note: You cannot create pre-sorted view.
178-- ############################################################
179DROP VIEW IF EXISTS vw_CustomerEmployee;
180GO
181
182CREATE VIEW vw_CustomerEmployee
183AS
184 SELECT
185 C.[name] AS "customer_name"
186 , E.[name] AS "employee_name"
187 , J.[title] AS "job_title"
188 , O.[name] AS "office_name"
189 FROM oEmpCust AS EC
190 JOIN oCustomer AS C ON C.ID = EC.customer_id
191 JOIN oEmployee AS E ON E.ID = EC.employee_id
192 JOIN oJobOffice AS JO ON JO.ID = EC.job_office_id
193 LEFT JOIN oJob AS J ON J.ID = JO.job_id
194 LEFT JOIN oOffice AS O ON O.ID = JO.office_id
195;
196GO
197
198
199-- ############################################################
200-- Query view for records where office name is 'Utica'.
201-- ############################################################
202SELECT *
203FROM vw_CustomerEmployee
204WHERE office_name IN ('Utica');
205GO
206