POS410 Final Exam
Name:
Questions 1-22 are worth ½ point each. Questions 23-31 are worth 1 point each. The total point value of the exam is 20 points. The exam is open book, open notes, but it must be an individual effort. Post your completed exam in the Assignments area by 11:59 pm on Monday, October 24.
1. SQL stands for:
a. Sequential Query Language
b. System Query Language
c. Structured Query Logic
d. Structured Query Language
e. none of the above
ANSWER:
2. SQL uses the relational data model. True / False
ANSWER:
3. Standard SQL is case-sensitive. True / False
ANSWER:
4. In SQL you can query at most two tables at a time. True / False
ANSWER:
5. An SQL query where you query more than one table at the same time is called a join. True / False
ANSWER:
6. A LEFT OUTER JOIN selects
a. only records that are left out of both tables
b. only records that are left out of the table on the left
c. only records that are left out of the table on the right
d. all the records in the table on the left and matching records in the table on the right
e. all the records in the table on the right and matching records in the table on the left
ANSWER:
7. SQL was originally developed by:
a. IBM
b. Microsoft
c. Oracle
d. the World Wide Web Consortium (W3C)
e. none of the above
ANSWER:
8. Oracle developed the first commercial version of SQL. True / False
ANSWER:
9. A(n) ______clause is like a WHERE clause for groups.
ANSWER:
10. SQL is:
a. a query language
b. a data manipulation language
c. a data definition language
d. a data control language
e. all of the above
f. none of the above
ANSWER:
11. A ______clause filters records.
a. SELECT
b. FROM
c. WHERE
d. GROUP BY
e. HAVING
f. ORDER BY
g. none of the above
ANSWER:
12. An UPDATE statement is used in SQL for the purpose of :
a. providing a status report
b. getting an updated copy of the SQL interpreter from the vendor
c. modifying the value of one or more fields in a table
d. all of the above
e. none of the above
ANSWER:
13. A ______clause filters groups.
a. SELECT
b. FROM
c. WHERE
d. GROUP BY
e. HAVING
f. ORDER BY
g. none of the above
ANSWER:
14. The INSERT INTO command can be used to:
a. add a record to a table
b. copy one or more records from one table to another table
c. both (a) and (b)
d. none of the above
ANSWER:
15. UPDATE is an example of a SQL ______command.
a. data control language (DCL)
b. data definition language (DDL)
c. data manipulation language (DML)
d. none of the above
ANSWER:
16. A(n) ______is a query that retrieves data from two or more tables in a coordinated manner.
a. SQL injection
b. dual injection
c. ERD
d. join
e. compound key
f. composite key
g. none of the above
ANSWER:
17. Take a look at the following query and indicate why it would generate an error.
SELECT lname, fname, age
WHERE age > 20
ORDER BY lname
a. You cannot use a ‘>’ sign in a WHERE clause.
b. ORDER BY can only be used with aggregate functions.
c. There is no FROM clause.
d. The WHERE clause should really be a HAVING clause.
e. None of the above
ANSWER:
18. Which of the following clauses might be used in a SQL DML (data manipulation language) statement?
a. CREATE
b. printf
c. UPDATE
d. <?xml version="1.0">
e. none of the above
ANSWER:
19. We can use a WHERE clause in a join to tell the SQL interpreter how two tables are related.
True / False
ANSWER:
20. Assuming the Employees table has the following structure, which of the WHERE clauses shown below is more resource-intensive than the others?
Employees
------
EmployeeID (Text 7, PK)
LastName (Text 40)
FirstName (Text 40)
Salary (NUMERIC)
a. WHERE EmployeeID = 'TH74940'
b. WHERE Salary > 25000.00
c. WHERE LastName = 'Kamakawiwoole'
d. WHERE LastName LIKE '*Lee*'
ANSWER:
21. Commercial versions of SQL typically contain:
a. C programming language support
b. more commands than standard SQL
c. more data types than standard SQL
d. extensions
e. none of the above
f. (b), (c) and (d)
ANSWER:
22. Use a(n) ______clause to sort query results.
a. COLLATE BY
b. SORT BY
c. SEQUENCE BY
d. GROUP BY
e. ORDER BY
f. none of the above
ANSWER:
23. Write the SQL statement needed to create a table named tblAirports, with the following columns:
AirportID (Text 3), AirportName (Text 32), City (Text 32), StateCode (Text 2)
ANSWER:
24. Using the table created in #23, write a SQL statement to insert a record for Honolulu International Airport. Its AirportID is HNL and its StateCode is HI.
ANSWER:
25. Using the table created in #23, write a SQL query to list all of the records in the table, sorted by AirportName.
ANSWER:
26. Explain how a WHERE clause and a HAVING clause differ. What is each clause meant to filter?
ANSWER:
Use the following ERD for Questions 27-31.
27. Write an SQL query that lists each company's name, ID, and website, sorted by company name.
ANSWER:
28. Write an SQL query that lists contacts for companies in Paris, France. List each matching contact's CompanyName, LastName, FirstName, WorkPhone, EmailName, and LastMeetingDate. Sort the results by CompanyName, LastName, and FirstName.
ANSWER:
29. Write an SQL query that displays the FirstName, LastName, and WorkPhone of every contact that, according to the TicklerDate field in the Tickler table, you must call on October 24, 2011, i.e., WHERE TicklerDate = #2/24/2011#.
ANSWER:
30. Notice in the ERD that the cardinality of the relationship between the Company table and the ShipTo table is one-to-many. Why is that?
ANSWER:
31. Notice in the ERD that CompanyID is not the primary key in the ShipTo table, but it is the primary key in the Company table. Why isn’t CompanyID the primary key of the ShipTo table?
ANSWER: