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
