I'm new in using MySQL and I have to do a project where I should implement 6 queries within the database that I have created. The queries that should be implemented are union query, aggregation query, nested query, and three join query. Here is the script for the database.
create database solarsystem;
use solarsystem;
CREATE TABLE PvArrays (
ArrayIdNo varchar(255),
Date DATE,
Time TIME,
Location varchar(255),
PowPV float (10),
EnergyPV float (10),
IPV float (10),
VoltPV float (10),
PRIMARY KEY (ArrayIdNo, Date, Time)
);
CREATE TABLE SunnyBoyInverter (
SerialNo varchar(255),
Date DATE,
Time TIME,
PowAC float (10),
IAC float (10),
VoltAC float (10),
CO2Saved float (10),
FreqAC float (10),
Energy float (10),
PRIMARY KEY (SerialNo, Date, Time)
);
CREATE TABLE SunnyIslandInverter (
SerialNo varchar(255),
Date DATE,
Time TIME,
PowAC float (10),
FreqAC float(10),
IAC float (10),
VoltAC float (10),
PRIMARY KEY (SerialNo, Date, Time)
);
CREATE TABLE BatteryBank (
IdNo varchar(255),
Date DATE,
Time TIME,
BattCurr float (10),
BattVolt float (10),
PRIMARY KEY (IdNo, Date, Time)
);
CREATE TABLE Loads (
LoadId varchar(255),
Date DATE,
Time TIME,
Power float (10),
Energy float (10),
PRIMARY KEY (LoadId, Date, Time)
);
INSERT INTO PvArrays ( ArrayIdNo, Date, Time, Location, PowPV, EnergyPV, IPV, VoltPV)
VALUES ('ENGR_PV', '2011-3-13', '0:00', 'ENGR_BLDG', NULL, NULL, NULL, NULL);
INSERT INTO PvArrays ( ArrayIdNo, Date, Time, Location, PowPV, EnergyPV, IPV, VoltPV)
VALUES ('ENGR_PV', '2011-3-13', '0:05', 'ENGR_BLDG', NULL, NULL, NULL, NULL);
INSERT INTO PvArrays ( ArrayIdNo, Date, Time, Location, PowPV, EnergyPV, IPV, VoltPV)
VALUES ('ENGR_PV', '2011-3-13', '0:10', 'ENGR_BLDG', NULL, NULL, NULL, NULL);
INSERT INTO PvArrays ( ArrayIdNo, Date, Time, Location, PowPV, EnergyPV, IPV, VoltPV)
VALUES ('ENGR_PV', '2011-3-13', '0:15', 'ENGR_BLDG', NULL, NULL, NULL, NULL);
INSERT INTO PvArrays ( ArrayIdNo, Date, Time, Location, PowPV, EnergyPV, IPV, VoltPV)
VALUES ('ENGR_PV', '2011-3-13', '0:20', 'ENGR_BLDG', NULL, NULL, NULL, NULL);
INSERT INTO BatteryBank (IdNo, Date, Time, BattCurr, BattVolt)
VALUES ('1', '2011-3-13', '00:00:00', 0, 48.7);
INSERT INTO BatteryBank (IdNo, Date, Time, BattCurr, BattVolt)
VALUES ('1', '2011-3-13', '00:05:00', 0, 48.7);
INSERT INTO BatteryBank (IdNo, Date, Time, BattCurr, BattVolt)
VALUES ('1', '2011-3-13', '00:10:00', 0, 48.7);
INSERT INTO BatteryBank (IdNo, Date, Time, BattCurr, BattVolt)
VALUES ('1', '2011-3-13', '00:15:00', 0, 48.7);
INSERT INTO BatteryBank (IdNo, Date, Time, BattCurr, BattVolt)
VALUES ('1', '2011-3-13', '00:20:00', 0, 48.7);
INSERT INTO SunnyBoyInverter (SerialNo, Date, Time, PowAC, IAC, VoltAC, CO2Saved, FreqAC, Energy)
VALUES ('2000785467', '2011-3-13', '00:00:00', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO SunnyBoyInverter (SerialNo, Date, Time, PowAC, IAC, VoltAC, CO2Saved, FreqAC, Energy)
VALUES ('2000785468', '2011-3-13', '00:05:00', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO SunnyBoyInverter (SerialNo, Date, Time, PowAC, IAC, VoltAC, CO2Saved, FreqAC, Energy)
VALUES ('2000785469', '2011-3-13', '00:10:00', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO SunnyBoyInverter (SerialNo, Date, Time, PowAC, IAC, VoltAC, CO2Saved, FreqAC, Energy)
VALUES ('2000785470', '2011-3-13', '00:15:00', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO SunnyBoyInverter (SerialNo, Date, Time, PowAC, IAC, VoltAC, CO2Saved, FreqAC, Energy)
VALUES ('2000785471', '2011-3-13', '00:20:00', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO SunnyIslandInverter (SerialNo, Date, Time, PowAC, FreqAC, IAC, VoltAC)
VALUES ('1256002422', '2011-3-13', '00:00:00', 42, 60, 2, 34);
INSERT INTO SunnyIslandInverter (SerialNo, Date, Time, PowAC, FreqAC, IAC, VoltAC)
VALUES ('1256002422', '2011-3-13', '00:05:00', 42, 60, 2, 34);
INSERT INTO SunnyIslandInverter (SerialNo, Date, Time, PowAC, FreqAC, IAC, VoltAC)
VALUES ('1256002422', '2011-3-13', '00:10:00', 42, 60, 2, 34);
INSERT INTO SunnyIslandInverter (SerialNo, Date, Time, PowAC, FreqAC, IAC, VoltAC)
VALUES ('1256002422', '2011-3-13', '00:15:00', 42, 60, 2, 34);
INSERT INTO SunnyIslandInverter (SerialNo, Date, Time, PowAC, FreqAC, IAC, VoltAC)
VALUES ('1256002422', '2011-3-13', '00:20:00', 43, 60, 2, 34);
INSERT INTO Loads (LoadId, Date, Time, Power, Energy)
VALUES ('ENGR_Load', '2011-3-13', '00:00:00', NULL, NULL);
INSERT INTO Loads (LoadId, Date, Time, Power, Energy)
VALUES ('ENGR_Load', '2011-3-13', '00:05:00', NULL, NULL);
INSERT INTO Loads (LoadId, Date, Time, Power, Energy)
VALUES ('ENGR_Load', '2011-3-13', '00:10:00', NULL, NULL);
INSERT INTO Loads (LoadId, Date, Time, Power, Energy)
VALUES ('ENGR_Load', '2011-3-13', '00:15:00', NULL, NULL);
INSERT INTO Loads (LoadId, Date, Time, Power, Energy)
VALUES ('ENGR_Load', '2011-3-13', '00:20:00', NULL, NULL);
Here are the queries that I implemented but got rejected by the professor
// UNION QUERY
(SELECT ArrayIdNo AS Id, Date AS Date
FROM pvarrays
UNION
(SELECT LoadId AS Id, Date AS Date
FROM loads)
ORDER BY Date, ID;
// INTERSECTION QUERY
SELECT * FROM pvarrays
WHERE EXISTS
(SELECT * FROM loads
WHERE loads.Date = pvarrays.Date AND loads.Time = pvarrays.Time);
// DIFFERENCE QUERY
SELECT DISTINCT sunnyboyinverter, Date AS Date
FROM sunnyboyinverter
WHERE NOT EXISTS
(SELECT sunnyislandinverter.Date
FROM sunnyislandinverter
WHERE sunnyislandinverter.Date
IN (SELECT sunnyboyinverter.Time
FROM sunnyboyinverter
WHERE sunnyboyinverter.Time = sunnyboyinverter.Date));
// AGGREGATION QUERY
SELECT ArrayIdNo, Date, AVG(Time)
FROM pvarrays
GROUP BY ArrayIdNo;
// NESTED QUERIES
SELECT COUNT(*), SUM(Time), MAX(Time), AVG(Time)
FROM pvarrays
WHERE pvarrays.Location = pvarrays.ArrayIdNo
NOT IN (SELECT Location = "ENGR_PV")
GROUP BY ArrayIdNo
ORDER BY AVG(Time);
//QUERY WITH THREE JOINS
SELECT * FROM pvarrays
LEFT JOIN loads ON pvarrays.Date = loads.Date
LEFT JOIN sunnyboyinverter ON pvarrays.Date = sunnyboyinverter.Date;
Thanks in advance for your help.