Member Avatar for poloblue

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.

  • 3 Contributors
  • 2 Replies
  • 230 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by crishjeny

Recommended Answers

All 2 Replies

Member Avatar for debasisdas

Any specific reason, why your queries are rejected ?

Member Avatar for crishjeny

write here rejection sentence

commented: don't post for the sake of posting only. -3
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.