AOIT Web Design

Test SQL

SQL TEST. 3 Grades total. ALL SQL MUST WORK FOR FULL CREDIT!

SQL Problems (#1-#20 5 points each; two grades)
Use the following table named CITY_TEMPS for the next five questions:

City State High Low
Phoenix Arizona 92 69
Tucson Arizona 92 60
Flagstaff Arizona 90 72
San Diego California 90 65
Albuquerque New Mexico 105 67



1. Write an SQL statement that searches for the cities where the high temperature was above 88 degrees.


2. Find all the unique cities in Arizona.


3. Find all the cities that had a low below 70 degrees.


4. Find all the cities that have the letters “an”.



5. Find the average High temperature.


Creating tables:
6. Create a table STUDENT_RECORDS with five columns, while using proper SQL, which will contain the following information: test grade, behavior, first period, second period, third period, fourth period, fifth period, sixth period.




7. Create a table PRODUCT with four columns, using proper SQL, which will contain the following information: product, price, weight, quantity.





Inserting information:
8. Insert your own information into STUDENT_RECORDS using proper SQL.



9. Insert the following information into PRODUCT:
a. Nintendo, $100, 2 lbs, 4



b. Hula Hoop, $3, .5 lbs, 5



c. Computer, $200, 7 lbs, 2




Updating information:
10. Update PRODUCT so that the price of the Nintendo is $145.



11. Update the table so that STUDENT_RECORDS has a column for a seventh period.



Deleting information:
12. Delete all products from PRODUCT that cost less than $75.


13. The STUDENT_RECORDS table has become useless. Delete it completely from the database.




Modeling Information:
14. You have been hired to create a database for the computers in this room. What are four vital pieces of information will you need to keep track of?


15. Pretend the database has already been created and is known as COMPUTER. What SQL code is needed to create your table?




Inserting Information:
16. Populate the table (you choose the name) with FOUR pieces of information.



Searching Information:
17. (Easiest question on the test) Display everything in the table.


SQL Functions:
18. Refer to the CITY_TEMP table on page 1. You need to find the city with the lowest low temperature. What SQL code do you need?

:
19. Refer to the CITY_TEMP table on page 1. How many cities are in the table?


20. Refer to the CITY_TEMP table on page 1. Select all the cities from New Mexico.

ESSAY 50 points each (1 grade):
21. Describe the steps in creating a database, tables, and columns. What are the steps in populating the table?



22. How do PHP and MySQL work together?

posted by Mr. Freer @ 8:10 AM