MySQL-Practice-Data/create-tables.sql at main · shubhamsingh119/MySQL-Practice-Data · GitHub

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
 /*

 Create the cars table with the following columns:
  * id - SERIAL PRIMARY KEY
  * brand - TEXT
  * model - TEXT
  * year - INTEGER
  * price INTEGER,
  * color TEXT,
  * condition INTEGER,
  * sold BOOLEAN

 */

 CREATE TABLE IF NOT EXISTS cars (
    id SERIAL PRIMARY KEY,
    brand TEXT,
    model TEXT,
    year INTEGER,
    price INTEGER,
    color TEXT,
    condition INTEGER,
    sold BOOLEAN
  );

  /*
	Create the dealerships table
		Include these columns:
			id - serial primary key
			city - text - NOT NULL
			state - char(2) - NOT NULL
			established - date - NOT NULL
*/

CREATE TABLE IF NOT EXISTS dealerships (
  id SERIAL PRIMARY KEY,
  city TEXT NOT NULL,
  state CHAR(2) NOT NULL,
  established DATE NOT NULL
);

/*
	Create the staff table
		Include these columns:
			id - serial primary key
			dealership_id - INTEGER NOT NULL REFERENCES dealerships(id)
			name - text - NOT NULL
			role - text - NOT NULL
*/

CREATE TABLE IF NOT EXISTS staff (
  id SERIAL PRIMARY KEY,
  dealership_id INTEGER NOT NULL REFERENCES dealerships(id),
  name TEXT NOT NULL,
  role TEXT NOT NULL
);

/*
	Create the sold_cars table
		Include these columns:
			id - serial primary key - NOT NULL
			cars_id - integer - NOT NULL - foreign key referencing cars(id)
			seller - integer - NOT NULL - foreign key referencing staff(id)
			sold_date DATE - NOT NULL
			sold_price INTEGER - NOT NULL
*/

CREATE TABLE IF NOT EXISTS sold_cars (
  id SERIAL PRIMARY KEY,
  cars_id INTEGER NOT NULL REFERENCES cars(id),
  seller INTEGER NOT NULL REFERENCES staff(id),
  sold_date DATE NOT NULL,
  sold_price INTEGER NOT NULL
);