MIS 3353 sql and graphs

In a company's database, a given table... -Usually consists of many sub-tables. -Should have multiple primary keys, since these are often changed. -Primarily consists of incremental, sequenced numbers. -Can include millions of records.

Can include millions of records.

Enabling different data points to relate to one another... Can only be handled through multivariate analysis. Is one of the core functions of a database. Is beyond the scope of an introductory database class. Requires the use of a GO statement.

Is one of the core functions of a database.

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. - You can't join more than two tables in any one 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.

What are the aggregate functions?

Sum, Avg, Count, Min, Max

Given the following two tables (Product, Manufacturer), which one of the following is an example of a foreign key? PRODUCT PRODUCTID MFRID NAME PRICE QTY 5006 2 Ball Peen Hammer 6.99 47 5007 1 5-lb. Mallet 9.99 11 5009 2 10-lb. Mallet 11.49 6 MANUFACTURER MFRID MFRNAME 1 Arcturus Tools 2 Nemesis Manufacturing Supply

The MFRID field in the Product table.

When using an Aggregate Function in SQL you must also...

use a GROUP BY for everything you used in your select statement *in this case, it would only be ManufacturerID*

Given the diagram below, which of the following statements is (or are) true? - There is a relationship between AType and Visitor - ATypeID is a foreign key in the Attraction table - The database is comprised of four fields. - The primary key for the Attraction table is AName. - Each table includes a foreign key.

ATypeID is a foreign key in the Attraction table

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? TOTAL SUM COUNT MAX

COUNT

Considering only the FROM clause of a query, which of the following would successfully assign aliases to tables? FROM A AS Article, C AS Category, W AS Writer FROM Article = A, Category = C, Writer = W 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

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

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

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 From,where, select, order by From,where,select,order by From, Where,Select,Order By FROM, WHERE, SELECT, ORDER BY From, Where, Select, Order By

FROM,WHERE,SELECT,ORDER BY

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 -GROUP BY Manufacturer -Nothing. The query will run properly without the GROUP BY clause. --GROUP BY ManufacturerName -GROUP BY Price

GROUP BY Manufacturer

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

List all columns

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' Nothing. We do not need the GROUP BY clause in this case GROUP BY Price GROUP BY Sum GROUP BY Total_Revenue

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

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

Parentheses

A company includes information about its customers in a database table called Customer. Each line within this table includes a customer's name, phone number, address, and date of birth. These lines are called... Records Columns Relationships Fields

Records

Databases... (Select all/any that apply.) - Rely on primary and foreign keys to relate records from different tables. - Can have no more than one primary key. - Generally only include one table. - Should only contain a few records before being split off into an additional database. - Organize data into categories, called "buckets", and sub-categories, called "nests".

Rely on primary and foreign keys to relate records from different tables.

A "primary key" is... Only necessary if there is a foreign key. Another word for "table". Required for each record. Repeated for similar records within a table.

Required for each record.

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.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;

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, 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;

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

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

SORT BY

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

SUM(ListPrice*Quantity)

When there is a relationship between records in two different tables... - The one side receives a key from the mandatory side. - The many side key is combined with the mandatory side key. - The mandatory side donates its key to the many side. - The mandatory side key is given to the many side and the many side key is given to the mandatory side.

The mandatory side donates its key to the many side

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 NOT 'Norman' -WHERE Destination =/= '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 LIKE 'Y%' AND DOB > '1994-12-31' WHERE LastName LIKE 'Y' AND DOB > 1994-12-31 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'

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 < 8 WHERE UniformNumber = 1, 3, 5, 7 WHERE UniformNumber NOT IN (2, 4, 6, 8) WHERE UniformNumber IN (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? -WHERE clause -@ operator -SORT BY clause -HAVING clause

WHERE clause

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. All tables need to have aliases to be referenced appropriately. 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.

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 VideoID, VideoDescription. C.CatName (FROM SELECT * FROM Video, Cat WHERE V.CatID = C.CatID); 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;

all of them