More Practice with MySQL

Recent labs have introduced the SQL queries: CREATE, INSERT, UPDATE, and Drop. Although these labs have provided familiarity with these statements for working with a database, the next work in the course will be building heavily on these SQL statements. Thus, before moving onward, this lab provides additional practice, to help students become reasonably comfortable working with MySQL tables.

Second-floor Rooms in Ford Hall

The goal of this lab is to create a table, called room, describing several characteristics of the Computer Science and Mathematics rooms on the second floor of Ford Hall. Specifically, for each room on the second floor of Ford Hall, a record is to hold these data:

• the room number (an int, the primary key, but not auto-incremented)
• the type of room (a character string of up to 15 characters, examples: office, classroom)
• number of computer workstations (an int)
• number of chairs (an int)
• number of projection devices

Important Notes for the Lab

• Recording this information could be done in a variety of ways. For this lab, please use the steps indicated, as a future lab will be based on these steps, but following different techniques.
• Throughout this lab, record your SQL statements in a separate file. The idea is that the file will contain exactly each SQL statement utilized in making the desired table. At the end of the lab, one could rebuild the table by
  • logging into mysql
  • specifying the database
  • placing the following line at the start of the file:

              drop table room;
            

  • select the entire file, and copy it into the mysql window

1. Table creation: Using a CREATE statement, establish a room table with the five fields listed above. (As noted above, when this statement works properly, copy it into your file of SQL statements for this lab.)

2. Rooms on the second floor of Ford Hall are numbered 201 through 224. Since most of the rooms are offices with 1 workstation, 3 chairs, and 0 projection devices:

   • Write an INSERT statement for room 201 with the default values. (Yes, the details are not necessarily right for this room, but the point now is to get the syntax right.)
   • Once the INSERT statement works properly for room 201, copy, use your editor to copy the same statement 23 more times, and change only the room number to 202, 203, ..., 224.

   With these statements created, copy the collection of 24 statements into the room table.

3. Write a DELETE statement to remove rooms 203, 205, and 222 from the list of rooms (as those rooms are not affiliated with computer science or mathematics). Also, remove room 221 (as no such room exists).

4. Modify classrooms 201, 202, 204, and 224, as follows:

   • All should have room type: classroom
   • Room 224 has 1 projector; rooms 201, 202, and 204 have 3 projectors.
   • Room 201 has 27 chairs, 202 has 38 chairs, 204 has 27 chairs, and 224 has 16 chairs.
   • Room 202 has 25 workstations,and room 224 has 8 workstations.

5. Modify room 209 as a "student research" room with 4 chairs, no projector, and 3 workstations.

6. Modify room 218 as an "administrative assistant" room with 4 chairs, no projector, and no workstations.

7. Modify room 220, so that it is a work room with no workstations, chairs, or projection equipment.

8. Be sure all of these adjustments are implemented and entered in the file of commands.

Disclaimer

All room descriptions above should be considered approximate and may or may not reflect actual facilities. These characteristics are identified for practice with database tables in this course; if you need definitive details about one or more rooms, a more careful review should be undertaken.

Work to be Handed In

• The file, containing the listing of all (corrected) commands utilized to create the final table (based on the outline of tasks above).
• A printout of the final version of the table, as generated by an appropriate SELECT statement.

created 9 February 2019 revised 10 February 2019