Question
MYSQL
Chapter 5 How to insert, update, and delete data Exercises appropriate SELECT statement. 1. Write an INSERT statement that adds this row to the Categories table: Brass category name Code the INSERT statement so MySQL automatically generates the category_id column. Write an UPDA TE statement that modifies the row you just added to the Categories table. This statement should change the product_name column to "Woodwinds", and it should use the category id column to identify the row. Write a DELETE statement that deletes the row you added to the Categories table in exercise 1. This statement should use the category_id column to identify the row 2. 3. 4. Write an INSERT statement that adds this row to the Products table: product id category id: product_code product name: description: list price: The next automatically generated ID 4 dgx 640 Yamaha DGX 640 88-Key Digital Piano Long description to come. 799.99 discount percent Today's date/time. date added Use a column list for this statement. Write an UPDATE statement that modifies the product you added in exercise 4. This statement should change the discount-percent column from 0% to 35%. Write a DELETE statement that deletes the record with category_id 4 from the Categories table. 5. 6. When you execute this statement, it will produce an error since the category has related rows in the Products table. To fix that, precede the DELETE statement with another DELETE statement that deletes all products in this category. (Remember that to code two or more statements in a script, you must end each statement with a semicolon.)
Explanation / Answer
1
In order to take advantage of the auto-incrementing capability of the column, do not supply a value for that column when inserting rows. The database will supply a value for you.
INSERT INTO Categories( category_name)
VALUES ( 'Brass' );
2
UPDATE Categories SET product_name = 'woodwinds'
where category_id in ( select distinct category_id from categories where category_name='Brass' );
3
DELETE FROM Categories
where category_id in ( select distinct category_id from categories where category_name='Brass' )
4
INSERT INTO Products( category_id,product_code,product_name,
description,list_price,discount_percent,date_added)
VALUES ( 4,'dgx_640','yamaha DGX 640 88-key Digital Piano',
'long description to come',799.99,0,NOW());
5
UPDATE products SET discount_percent= 35
where product_code ='dgx_640';
6.
delete from products where category_id =4;
delete from categories where category_id =4;
