Unit 08 · Practice
Practice
The boxes on this page run on a MySQL server of your own, separate from the one in this unit's lessons. It starts with no databases. Each part of the questions works in a database of its own, which the part's first question has you create.
Every box on the page runs on the same server, and whatever you run stays, even after you reload the page. There's no reset button. If something goes wrong, fix it with SQL, the way you would in Workbench: DELETE rows that shouldn't be there, UPDATE the ones that are wrong, or DROP a table you need to make again.
The Your databases panel lists your databases, and the one in use is open to show its tables and their columns. Clicking another database uses it, the same as running USE. To see the rows in a table, run a SELECT in any box.
Run runs everything in the box. If you select part of the script first, the button changes to Run selection and runs only that part. Under the box, each statement gets a line saying what happened to it. A script stops at its first error.
You keep the records for a small animal shelter. Create a database named shelter, and USE it. Then create a table in it named Animals, with these columns:
| Column | Holds |
|---|---|
| AnimalID | a whole number |
| Name | text, up to 50 characters. Every animal has one. |
| Species | text, up to 30 characters. Every animal has one. |
| Age | a whole number |
Put these three animals into Animals.
| AnimalID | Name | Species | Age |
|---|---|---|---|
| 1 | Biscuit | Dog | 3 |
| 2 | Pepper | Cat | 5 |
| 3 | Juniper | Rabbit | 1 |
A restaurant booking service keeps its records in a database named dining. Create it, and USE it. Then create a table in it named Guests, for rows like these:
| ID | FirstName | LastName | Phone |
|---|---|---|---|
| 1 | Nathaniel | Mandrake | (111) 222 3333 |
| 2 | David | Martinez | (333) 444 5555 |
| 3 | Valentine | Wiggin | (666) 777 8888 |
Choose the data types yourself, and use constraints for these rules from the booking service:
- Guests are looked up by their ID, and no two guests share one.
- Every guest has a first name and a last name.
- A guest doesn't have to give a phone number.
Put these three guests into Guests.
| ID | FirstName | LastName | Phone |
|---|---|---|---|
| 1 | Nathaniel | Mandrake | (111) 222 3333 |
| 2 | David | Martinez | (333) 444 5555 |
| 3 | Valentine | Wiggin | (666) 777 8888 |
The booking service also keeps its restaurants, and the reservations guests make at them. Create a table named Restaurants and a table named Reservations in dining, for rows like these:
Restaurants
| ID | Name |
|---|---|
| 1 | Bob's Gourmet Macaroni |
| 2 | Cheese Louise Dinner |
Reservations
| ID | GuestID | RestaurantID | ReservationDateTime |
|---|---|---|---|
| 1 | 3 | 2 | 2024-01-02 19:00:00 |
| 2 | 1 | 1 | 2024-01-03 09:30:00 |
Choose the data types yourself, and use constraints for these rules from the booking service:
- Restaurants and reservations are each looked up by their ID, and no two share one.
- Every restaurant has a name.
- A reservation is for one guest at one restaurant. The database has to refuse a reservation for a guest or a restaurant it doesn't have.
Put these rows into Restaurants and Reservations.
Restaurants
| ID | Name |
|---|---|
| 1 | Bob's Gourmet Macaroni |
| 2 | Cheese Louise Dinner |
Reservations
| ID | GuestID | RestaurantID | ReservationDateTime |
|---|---|---|---|
| 1 | 3 | 2 | 2024-01-02 19:00:00 |
| 2 | 1 | 1 | 2024-01-03 09:30:00 |
| 3 | 2 | 1 | 2024-01-03 10:00:00 |
| 4 | 3 | 2 | 2024-01-04 17:30:00 |
To put an apostrophe inside text, type it twice: 'Bob''s Gourmet Macaroni'.
A coworker suggests making Guests.Phone UNIQUE, so that no two guests can have the same phone number. Which of these would that stop?
A car dealership keeps its customers, and the cars it sells, in a database of its own. Questions 9 to 14 are things that happen at the dealership once that database is set up. Handle each one without CREATE or INSERT, by changing the tables and rows that are already there.
The script below creates the dealership's two tables and puts in their rows. Add two lines at the top that create a database named dealership and start using it, then run the script.
The dealership has decided to start tracking the color of each car. Name the new column Color.
There was a mistake when processing a recent order. The Honda Civic was actually sold to John Smith.
Hannah Brooks has decided to return her Tesla Model 3. The dealership has agreed to take it back.
Due to a special promotion, the dealership has decided to reduce the price of the Ford Mustang by $2000.
Kwame has decided to close their account. Data privacy laws require the company to permanently remove their information.
Once question 11 is correct, Hannah Brooks asks the dealership to close her account too. Run the DELETE in the box. Why did it fail?
When you're done, download your submission file and upload it to this unit's practice assignment in Canvas. It carries a summary of your progress and the record of every attempt you made on this page. You can submit as many times as you like; the last one counts.
Attempts are saved in this browser. To keep going on another computer, download your submission here and resume from it there.