CUNY-Bridge---SQL/SQL-Week5_Final2.sql at Q1 · chrisgmartin/CUNY-Bridge---SQL · 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
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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
-- BuildingEnergy.sql

DROP TABLE IF EXISTS EnergyTypes;
DROP TABLE IF EXISTS Buildings;
DROP VIEW IF EXISTS BigEnergy;
DROP TABLE IF EXISTS EnergyCategories;
DROP VIEW IF EXISTS Energies;

CREATE TABLE EnergyCategories (
    CID INT PRIMARY KEY,
    category VARCHAR(100)
);

INSERT INTO EnergyCategories(CID, category)
VALUES(1, 'Fossil');
INSERT INTO EnergyCategories(CID, category)
VALUES(2, 'Renewable');

CREATE TABLE EnergyTypes (
  TID int,
    type_name varchar(100),
    CID int
);

INSERT INTO EnergyTypes(TID, type_name, CID)
VALUES(1, 'Electricity', 1);
INSERT INTO EnergyTypes(TID, type_name, CID)
VALUES(2, 'Gas', 1);
INSERT INTO EnergyTypes(TID, type_name, CID)
VALUES(3, 'Steam', 1);
INSERT INTO EnergyTypes(TID, type_name, CID)
VALUES(4, 'Fuel Oil', 1);
INSERT INTO EnergyTypes(TID, type_name, CID)
VALUES(5, 'Solar', 2);
INSERT INTO EnergyTypes(TID, type_name, CID)
VALUES(6, 'Wind', 2);


SELECT *
FROM EnergyCategories;

SELECT *
FROM EnergyTypes;

SELECT
EC.category AS Category,
ET.type_name AS Type
FROM EnergyCategories AS EC
LEFT JOIN EnergyTypes AS ET
ON EC.CID = ET.CID;


CREATE TABLE Buildings (
  BID int PRIMARY KEY,
    building varchar(100),
    TID int
);

INSERT INTO Buildings(BID, building, TID)
VALUES(1, 'Borough of Manhattan Community College', 5);
INSERT INTO Buildings(BID, building, TID)
VALUES(2, 'Borough of Manhattan Community College', 1);
INSERT INTO Buildings(BID, building, TID)
VALUES(3, 'Borough of Manhattan Community College', 3);
INSERT INTO Buildings(BID, building, TID)
VALUES(4, 'Crysler Building', 3);
INSERT INTO Buildings(BID, building, TID)
VALUES(5, 'Crysler Building', 1);
INSERT INTO Buildings(BID, building, TID)
VALUES(6, 'Empire State Building', 1);
INSERT INTO Buildings(BID, building, TID)
VALUES(7, 'Empire State Building', 3);
INSERT INTO Buildings(BID, building, TID)
VALUES(8, 'Empire State Building', 2);

#Adding new rows
INSERT INTO EnergyTypes(TID, type_name, CID)
VALUES(7, 'Geothermal', 2);
INSERT INTO Buildings(BID, building, TID)
VALUES(9, 'Bronx Lion House', 7);
INSERT INTO Buildings(BID, building, TID)
VALUES(10, 'Brooklyn Childrens Museum', 1);
INSERT INTO Buildings(BID, building, TID)
VALUES(11, 'Brooklyn Childrens Museum', 7);

SELECT *
FROM Buildings;

SELECT
b.building AS Building,
e.type_name AS 'Energy Tyoe',
c.category AS 'Energy Category'
FROM Buildings b
LEFT JOIN EnergyTypes e
ON b.TID = e.TID
LEFT JOIN EnergyCategories c
ON e.CID = c.CID
WHERE c.category = 'Renewable';


CREATE VIEW BigEnergy AS
SELECT
b.BID,
b.building,
e.TID,
e.type_name,
c.CID,
c.category
FROM Buildings b
LEFT JOIN EnergyTypes e
ON b.TID = e.TID
LEFT JOIN EnergyCategories c
ON e.CID = c.CID;

SELECT *
FROM BigEnergy;

SELECT
type_name AS Type,
COUNT(type_name) AS Frequency
FROM BigEnergy
GROUP BY type_name
ORDER BY Frequency DESC;


#9a
ALTER TABLE EnergyTypes
ADD FOREIGN KEY (CID)
REFERENCES EnergyCategories(CID);

SELECT *
FROM EnergyTypes;