SQL Example Databases and Tables Auto Shop Database
Example
In the following example - Database for an auto shop business, we have a list of departments, employees, customers and customer cars. We are using foreign keys to create relationships between the various tables.
Live example: SQL fiddle
Relationships between tables
- Each Department may have 0 or more Employees
- Each Employee may have 0 or 1 Manager
- Each Customer may have 0 or more Cars
Departments
| Id | Name |
|---|---|
| 1 | HR |
| 2 | Sales |
| 3 | Tech |
SQL statements to create the table:
CREATE TABLE Departments (
Id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(25) NOT NULL,
PRIMARY KEY(Id)
);
INSERT INTO Departments
([Id], [Name])
VALUES
(1, 'HR'),
(2, 'Sales'),
(3, 'Tech')
;
Employees
| Id | FName | LName | PhoneNumber | ManagerId | DepartmentId | Salary | HireDate |
|---|---|---|---|---|---|---|---|
| 1 | James | Smith | 1234567890 | NULL | 1 | 1000 | 01-01-2002 |
| 2 | John | Johnson | 2468101214 | 1 | 1 | 400 | 23-03-2005 |
| 3 | Michael | Williams | 1357911131 | 1 | 2 | 600 | 12-05-2009 |
| 4 | Johnathon | Smith | 1212121212 | 2 | 1 | 500 | 24-07-2016 |
SQL statements to create the table:
CREATE TABLE Employees (
Id INT NOT NULL AUTO_INCREMENT,
FName VARCHAR(35) NOT NULL,
LName VARCHAR(35) NOT NULL,
PhoneNumber VARCHAR(11),
ManagerId INT,
DepartmentId INT NOT NULL,
Salary INT NOT NULL,
HireDate DATETIME NOT NULL,
PRIMARY KEY(Id),
FOREIGN KEY (ManagerId) REFERENCES Employees(Id),
FOREIGN KEY (DepartmentId) REFERENCES Departments(Id)
);
INSERT INTO Employees
([Id], [FName], [LName], [PhoneNumber], [ManagerId], [DepartmentId], [Salary], [HireDate])
VALUES
(1, 'James', 'Smith', 1234567890, NULL, 1, 1000, '01-01-2002'),
(2, 'John', 'Johnson', 2468101214, '1', 1, 400, '23-03-2005'),
(3, 'Michael', 'Williams', 1357911131, '1', 2, 600, '12-05-2009'),
(4, 'Johnathon', 'Smith', 1212121212, '2', 1, 500, '24-07-2016')
;
Customers
| Id | FName | LName | PhoneNumber | PreferredContact | |
|---|---|---|---|---|---|
| 1 | William | Jones | [email protected] | 3347927472 | PHONE |
| 2 | David | Miller | [email protected] | 2137921892 | |
| 3 | Richard | Davis | [email protected] | NULL |
SQL statements to create the table:
CREATE TABLE Customers (
Id INT NOT NULL AUTO_INCREMENT,
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)
);
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')
;
Cars
| Id | CustomerId | EmployeeId | Model | Status | Total Cost |
|---|---|---|---|---|---|
| 1 | 1 | 2 | Ford F-150 | READY | 230 |
| 2 | 1 | 2 | Ford F-150 | READY | 200 |
| 3 | 2 | 1 | Ford Mustang | WAITING | 100 |
| 4 | 3 | 3 | Toyota Prius | WORKING | 1254 |
SQL statements to create the table:
CREATE TABLE Cars (
Id INT NOT NULL AUTO_INCREMENT,
CustomerId INT NOT NULL,
EmployeeId INT NOT NULL,
Model varchar(50) NOT NULL,
Status varchar(25) NOT NULL,
TotalCost INT NOT NULL,
PRIMARY KEY(Id),
FOREIGN KEY (CustomerId) REFERENCES Customers(Id),
FOREIGN KEY (EmployeeId) REFERENCES Employees(Id)
);
INSERT INTO Cars
([Id], [CustomerId], [EmployeeId], [Model], [Status], [TotalCost])
VALUES
('1', '1', '2', 'Ford F-150', 'READY', '230'),
('2', '1', '2', 'Ford F-150', 'READY', '200'),
('3', '2', '1', 'Ford Mustang', 'WAITING', '100'),
('4', '3', '3', 'Toyota Prius', 'WORKING', '1254')
;
