Computer Science - SQL 2 Quiz
Enter your name, answer the questions, print this page, and hand it in.
Name:    Date:  
1. Which SELECT clause is required?
a. FROM c. WHERE
b. GROUP BY d. ORDER BY
2. Which is the correct SQL not equal symbol?
a. >< c. ==
b. <> d. =!
3. Which SELECT aggregate function will return the largest value in a column?
a. MIN() c. MAX()
b. SUM() d. COUNT(*)
 4. Which SELECT aggregate function will return the number of rows in a table?
a. MIN() c. MAX()
b. SUM() d. COUNT(*)
 5. Which SELECT clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns?
a. GROUP BY c. ORDER BY
b. HAVING d. BOOLEAN
6. Which SELECT clause allows you to specify conditions on the rows for each group - in other words, which rows should be selected will be based on the conditions you specify?
a. GROUP BY c. ORDER BY
b. HAVING d. BOOLEAN
7. Which SELECT clause will allow you to display the results of your query in a sorted order?
a. GROUP BY c. ORDER BY
b. HAVING d. BOOLEAN
8. Which concept allow you to use multiple WHERE conditions?
a. GROUP BY c. ORDER BY
b. HAVING d. BOOLEAN
9. Which SELECT operator can be used to join two or more conditions in the WHERE clause where BOTH SIDES MUST BE TRUE?
a. AND c. NOT
b. OR d. LIKE
10. Which SELECT operator can be used to join two or more conditions in the WHERE clause where EITHER SIDE MUST BE TRUE?
a. AND c. NOT
b. OR d. LIKE
11. Which mathematical SQL function used in SELECT will return the integer REMAINDER of x divided by y?
a. DIV(x,y) c. MOD(x,y)
b. REM(x,y) d. ROUND(x,y)
12. Which mathematical SQL function used in SELECT will returns the value of x ROUNDED to the number of decimal places specified by the value y?
a. DIV(x,y) c. MOD(x,y)
b. REM(x,y) d. ROUND(x,y)
For Questions 12 through 19:
  1. Use the following two tables to answer the next 7 questions
  2. Write syntactically correct SQL statements to answer the following
  3. You may use the sqlcourse2.com site to test your answers
  4. Make sure your answers fit with NO SCROLL BARS
  5. As a hint, I gave the number of records I found
table name field names
"customers" table customerid firstname lastname city state
"items_ordered" table customerid order_date item quantity price
13. How many purchases were there? COUNT the number of records in the items_ordered table: (1 record)
14. How many items were purchased in 1999? SUM the quantity in the items_ordered table: (1 record)
15. Display only items and prices that had different prices. GROUP BY items HAVING different min and max prices: (6 records)
16. Display the customers first and last names in alphabetical order. ORDER BY last then first name: (17 records)
17. Display all items purchased by customers who live in Arizona. Use WHERE to join the 2 tables by the common field. ORDER BY item: (13 records)
18. Display the last name, total of items purchased, total price of purchases, and average price of item. Use SUM and AVG: (11 records)
19. Display last name, state, and total price of purchases for customers who purchased more than $250. To limit a GROUP BY, use HAVING: (4 records)
Questions 20 through 22 deal with creating two web pages to read and write to a database called "membership.mdb" a table called "members". You will actually create two web pages: "memberLogon.htm" and "createMember.htm". Save all your work as normal and, when complete, copy to your "H:/ACCESS/" folder so I can look at, test, and grade your work.
20. Preparation:
  1. Put all your work, when complete, into your "H:/ACCESS/" folder
  2. Prefix your web pages with "css00" using your id number so I can search and copy easily
  3. Copy the Access database "membership.mdb" from the "Q:/ACCESS/" folder
  4. The database has one table called "members" with these fields:
    • ID
    • firstName
    • lastName
    • eMail
    • userName
    • password
21/22. Create logon web page:
  1. Create a web page called "css00memberLogon.htm"
  2. Add a text box labeled "User ID"
  3. Add a password text box labeled "Password"
  4. Add a command button to "Logon"
  5. Add a link to a "Create Membership" web page, to be designed next
  6. Create the JavaScript function so that when the user clicks on the "Logon" button, the program tests to see if the "ID" and "password" exist in the membership database. Build, alert(), and execute a SELECT command
  7. Your SELECT command should look something like this: "SELECT userName,password FROM members WHERE userName = 'mcorleone' AND password = 'godfather';"
  8. Use the "studentsSearch.htm" web page for hints (copy and paste)
  9. If the logon is accepted (the user exists in the database) display a welcome message
  10. If the logon is NOT accepted (the user does NOT exist) automatically link to a "Create Membership" web page
23/24. Create create member web page:
  1. Create a web page called "css00createMember.htm"
  2. Add text boxes and a password text box for all 6 fields in the table
  3. Add a command button to "Create Member"
  4. When the user clicks the "Create Member" button,
  5. Create a JavaScript function so that when the user clicks on the "Create Member" button, the program creates a new record in the table. Build, alert(), and execute an INSERT command
  6. Your INSERT command should look something like this: "INSERT INTO members VALUES(21,'James','Boothe','jboothe@mail.com','jboothe','fordstheatre');"
25. Are you tired of SQL yet?
a. Yes! c. Maybe So!
b. No? d. Would you repeat the question?