database management quiz questions

Which aggregate function would you use (in the SELECT clause) if you wanted to know the total number of magicians in the Magician table who are from Albuquerque? SUM TOTAL MAX COUNT

COUNT

Let's assume that you wrote a query that has the following four clauses: SELECT, FROM, WHERE, ORDER BY. The order in which the four SQL query clauses are executed is _____ _____ _____ _____. (separate your answers by commas.)

FROM, WHERE, SELECT, ORDER BY

When joining tables... You do not need to have a FROM 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. The foreign key and primary key must be matched in the WHERE clause.

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

* in an SQL SELECT statement means: 0 or more characters List all columns Many Exactly 1 character

List all columns

To override precedence of AND, OR, and NOT, use: Curly brackets Square brackets Precedence cannot be overridden Parentheses

Parentheses

Which of the following is not a clause in a SELECT query? SORT BY WHERE GROUP BY SELECT

SORT BY

Which of the following WHERE clauses would include only those records in the output where the value in the Destination field is something OTHER than Norman? WHERE Destination <> 'Norman' WHERE Destination =/= 'Norman' WHERE Destination != 'Norman' WHERE Destination NOT 'Norman'

WHERE Destination <> 'Norman'

Which of the following WHERE clauses would include only those records from the Customer table where the customer's last name (the LastName field) starts with Y and the customer's birthday (DOB field) falls some time after 1994? WHERE (LastName IS 'Y%') AND (DOB >= '1994-01-01') WHERE LastName STARTS WITH 'Y' AND DOB >= '1/1/1994' WHERE LastName LIKE 'Y%' AND DOB > '1994-12-31' WHERE LastName LIKE 'Y' AND DOB > 1994-12-31

WHERE LastName LIKE 'Y%' AND DOB > '1994-12-31'

Which of the following WHERE clauses would include only those records in the output for which the value of UniformNumber is 1, 3, 5, or 7? WHERE UniformNumber IN (1, 3, 5, 7) WHERE UniformNumber NOT IN (2, 4, 6, 8) WHERE UniformNumber < 8 WHERE UniformNumber = 1, 3, 5, 7

WHERE UniformNumber IN (1, 3, 5, 7)

Using a database that includes information about every opera ever written, I want to write a query that displays information only about those operas that were written prior to the year 1825. To do this, I would use which of the following? @ operator SORT BY clause HAVING clause WHERE clause

WHERE clause

When would you want to use an outer JOIN? 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 join three or more tables, but aren't sure which fields represent the primary and foreign keys. When you want to create a virtual table that can be used to compare values in the WHERE clause.

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? It is not possible to join three tables within the same query. All tables need to have aliases to be referenced appropriately. You need to have at least three expressions in the WHERE clause to match the appropriate keys there. You create a "virtual table" that includes matching records from all three tables. The join itself should be given an alias.

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

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 MeetingName, MeetingLength FROM Meeting WHERE MeetingID IN ( SELECT TOP 10 MeetingID, MeetingName, MeetingLength FROM Meeting ORDER BY MeetingLength) ORDER BY MeetingLength DESC; SELECT Animal, PenDescription FROM ( SELECT * FROM Animal A, Pen P WHERE A.PenID = P.PenID) ORDER BY Animal; SELECT VideoID, VideoDescription. C.CatName (FROM SELECT * FROM Video, Cat WHERE V.CatID = C.CatID);

all of them

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 A, Category C, Writer W FROM A AS Article, C AS Category, W AS Writer FROM Article JOIN Category ON Article.CategoryID = Category.CategoryID JOIN Writer ON Article.ArticleID = Writer.WriterID

FROM Article A, Category C, Writer W

Assuming no fields/keys are misnamed, which of the following multiple-table queries would you expect to run without errors? 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.MountainID = Location.LocID ORDER BY Location.LocID; 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, 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;

Assuming that the table and fields referenced are valid, which of the following queries have appropriate syntax and would work without error? SELECT PlayerName, Gender, Position WHERE Position = 'Seeker' SORT BY PlayerName ASC; SELECT PlayerName, Position, Height FROM Roster WHERE Height > 63 ORDER BY Position; SELECT PlayerName, Hometown FROM Roster WHERE Hometown IS 'Pittsburgh' SELECT PlayerNumber, PlayerName, Weight WHERE Weight > 100 FROM Roster;

SELECT PlayerName, Position, Height FROM Roster WHERE Height > 63 ORDER BY Position;

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

SUM(ListPrice*Quantity)