Description
Are you available to help me with this assignment? I attached a PDF with the assignment instructions.
Unformatted Attachment Preview
Purchase answer to see full attachment
Explanation & Answer
Attached. Please let me know if you have any questions or need revisions.
Question 1
a)
SELECT distinct staff.FName as "FName", staff.LName as "LName",
program.PName as "Program", COUNT(student.BannerID) as "Students"
FROM staff
JOIN advisor
ON staff.BannerID=advisor.staffBannerID
JOIN student
ON student.BannerID=advisor.studentBannerID
JOIN program
ON program.PCode=staff.PCode
GROUP by staff.FName, staff.LName;
b)
I. Yes, it returns it will return Paul Carter.
II. No, it returns the name of those students who have been assigned an advisor i.e. James,
Anita and Jane have been assigned an advisor.
III. No, it will not return any record.
Question 2
a.
b.
CREATE TABLE sales (
sid int not null,
salesDate date not null,
PRIMARY KEY (sid)
);
CREATE TABLE inStoreSales (
sid int not null,
storeName varchar(30),
CONSTRAINT FK_SalesinStore FOREIGN KEY (sid)
REFERENCES sales(sid)
);
CREATE TABLE inOnlineSales (
sid int not null,
browserName varchar(30) not null,
operatingSystem varchar(30),
CONSTRAINT FK_SalesOnline FOREIGN KEY (sid)
REFERENCES sales(sid)
);
CREATE TABLE prod...