MIS 3353 - Multi-Table SQL & Compounding Data

Given the ERD above, what should go to the GROUP BY clause for the following query to run properly? What is the average price of shoes per manufacturer? SELECT ManufacturerName AS Manufacturer, AVG(Price) AS Average_Price FROM SimplifiedSales - Nothing. The query will run properly without the GROUP BY clause. - GROUP BY Price - GROUP BY ManufacturerName - GROUP BY Manufacturer

GROUP BY ManufacturerName

Which of the following, based strictly on syntax, would NOT run correctly in SQL Server? (Select any/all that apply.) - SELECT V.VesselID, T.TripID, T.Destination, T.Duration FROM Vessel V, Trip T WHERE V.TripID = T.TripID AND T.Duration > (AVG(Duration) FROM T.Trip) ORDER BY V.VesselID; - SELECT Animal, PenDescription FROM ( SELECT * FROM Animal A, Pen P WHERE A.PenID = P.PenID) ORDER BY Animal; - SELECT MeetingName, MeetingLength FROM MeetingWHERE MeetingID IN ( SELECT TOP 10 MeetingID, MeetingName, MeetingLength FROM Meeting ORDER BY MeetingLength) ORDER BY MeetingLength DESC; - SELECT VideoID, VideoDescription. C.CatName( FROM SELECT * FROM Video, Cat WHERE V.CatID = C.CatID);

All of the above (Select all choices)

Considering only the FROM clause of a query, which of the following would successfully assign aliases to tables? - FROM Article A, Category C, Writer W - FROM Article JOIN Category ON Article.CategoryID = Category.CategoryID JOIN Writer ON Article.ArticleID = Writer.WriterID - FROM Article = A, Category = C, Writer = W - FROM A AS Article, C AS Category, W AS Writer

FROM Article A, Category C, Writer W

What is the order of execution of the SELECT statement? - SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY - FROM, SELECT, WHERE, GROUP BY, HAVING, ORDER BY - FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY - SELECT, GROUP BY, WHERE, HAVING, FROM, ORDER BY

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY

When joining tables... - You do not need to have a FROM clause. - It is necessary to match the foreign key in one table to the primary key in another within the query. - The foreign key and primary key must be matched in the WHERE clause. - You can't join more than two tables in any one query.

It is necessary to match the foreign key in one table to the primary key in another within the query.

Given the above ERD, what should go to the GROUP BY clause for the following query to run properly? What is the total sales for NIKE products? SELECT sum(price) AS Total_Revenue FROM SimplifiedSales WHERE ManufacturerName = 'Nike' - GROUP BY Total_Revenue - GROUP BY Sum - Nothing. We do not need the GROUP BY clause in this case - GROUP BY Price

Nothing. We do not need the GROUP BY clause in this case

Assuming no fields/keys are misnamed, which of the following multiple-table queries would you expect to run without errors? - SELECT Mountain.MountainID, Location.LocID, Mountain.GPSLat, Mountain.GPSLon, Location.StateName FROM Mountain JOIN Location ON Mountain.MountainID = Mountain.LocID ORDER BY Location.LocID; - SELECT Mountain.MountainID, Location.LocID, Mountain.GPSLat, Mountain.GPSLon, Location.StateName FROM Mountain.MountainID = Location.LocID ORDER BY Location.LocID; - SELECT Mountain.GPSLat, Mountain.GPSLon, Location.StateName FROM Mountain, Location WHERE Mountain.LocID = Location.LocID ORDER BY Location.LocID; - SELECT Mountain.MountainID, Location.LocID, Mountain.GPSLat, Mountain.GPSLon, Location.StateName FROM Mountain, Location WHERE Mountain.MountainID = Location.LocID ORDER BY Location.LocID;

SELECT Mountain.GPSLat, Mountain.GPSLon, Location.StateName FROM Mountain, Location WHERE Mountain.LocID = Location.LocID ORDER BY Location.LocID;

The best aggregate function clause to use to determine the total amount each customer spent on shoes would be the following: - MAX(ListPrice*Quantity) - Sum(ListPrice) - SUM(ListPrice*Quantity) - COUNT(Distinct(ListPrice*Quantity))

SUM(ListPrice*Quantity)

When would you want to use an outer JOIN? - When you want to create a virtual table that can be used to compare values in the WHERE clause. - When you want to join three or more tables, but aren't sure which fields represent the primary and foreign keys. - When you want to output the records from two or more tables that have no matching keys between them. - When you want to output all records from one table and records from a second table that have matching keys with records from the first.

When you want to output all records from one table and records from a second table that have matching keys with records from the first.

When joining three tables within the same query, which of the following is true? - You need to have at least three expressions in the WHERE clause to match the appropriate keys there. - It is not possible to join three tables within the same query. - The join itself should be given an alias. - You create a "virtual table" that includes matching records from all three tables. - All tables need to have aliases to be referenced appropriately.

You create a "virtual table" that includes matching records from all three tables.