These Exercises All Use The Create My Guitar Shop Script

These exercises all use the create_my_guitar_shop.sq l (Links to an external site.)

These exercises all use the create_my_guitar_shop.sq l (Links to an external site.) . Write the code for each and save the code (each code segment identified and separated) in Notepad. Submit your completed assignment via Canvas. To test whether a table has been modified correctly as you do these exercises, you can write and run an appropriate SELECT statement.

1) Write an INSERT statement that adds this row to the Categories table: category_name: Brass and the code should generate the category_id automatically.

2) Write an UPDATE statement that modifies the row you just added to the Categories table, changing the category_name to “Woodwinds” and using the category_id to identify the row.

3) Write a DELETE statement that deletes the row you added in exercise 1, using category_id to identify the row.

Paper For Above instruction

These exercises all use the create my guitar shop sq l Links to an external site

These exercises all use the create_my_guitar_shop.sq l (Links to an external site.)

This set of exercises focuses on fundamental SQL data manipulation operations—namely INSERT, UPDATE, and DELETE—within the context of a guitar shop database. Specifically, these operations target the Categories table, which classifies different musical instrument categories sold by the shop. The tasks require writing SQL statements to add a new category, modify it, and eventually delete it, exhibiting understanding of table data management and key identification through primary keys such as category_id.

In practice, you'll first insert a new row into the Categories table with a category name of 'Brass'. The category_id should be auto-generated, reflecting an auto-incrementing primary key behavior in MySQL. Next, you'll update this newly added row to change its category_name to 'Woodwinds', utilizing the category_id to precisely target the row. Lastly, you'll delete the same row, again referencing it via the category_id, to remove it from the table, maintaining data integrity and demonstrating deletion operations based on primary keys.

Solution: SQL Statements for Data Manipulation

1. Insert Statement

INSERT INTO Categories (category_name)

VALUES ('Brass');

This INSERT statement adds a new category called 'Brass' to the Categories table. The category_id column is assumed to be set as AUTO_INCREMENT, so it will automatically assign a unique identifier for this new record.

2. Update Statement

UPDATE Categories

SET category_name = 'Woodwinds'


WHERE category_id = LAST_INSERT_ID();

In this step, we change the category_name to 'Woodwinds' for the row we just inserted. Using LAST_INSERT_ID() ensures we are targeting the exact row based on the auto-generated category_id from the previous insert. Note that in practice, you might want to store this category_id in a variable if executing multiple statements in a script environment.

3. Delete Statement

DELETE FROM Categories

WHERE category_id = LAST_INSERT_ID();

Finally, we delete the record we previously added and updated by referencing the category_id via LAST_INSERT_ID(), removing the 'Woodwinds' category from the table. This demonstrates proper deletion based on the primary key.

Additional Notes

It is important to execute these statements in sequence to ensure that the correct row is targeted at each step. When using a scripting environment or a database client, capturing and reusing the auto-generated ID is best practice. In an actual scenario, especially when multiple users or scripts are accessing the database, explicit variables or transaction controls might be necessary to safeguard data integrity.

References

  • MySQL Documentation. (2023). https://dev.mysql.com/doc/
  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Addison-Wesley.

Related Assignments