HW5: Chapters 7&8 How to insert, update, and delete data Exercises To test wheth

Question

HW5: Chapters 7&8 How to insert, update, and delete data Exercises 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 Products table: product_id: 11 category_id: 4 product_code: dgx_640 product_name: Yamaha DGX 640 88-Key Digital Piano description: Long description to come. list_price: 799.99 discount_percent: 0 date_added: Today’s date/time. Use a column list for this statement.

2. Write an UPDATE statement that modifies the product you added in exercise 1. This statement should change the discount_percent column from 0% to 35%. Write a DELETE statement that deletes the row with category_id = 4 that in the Categories. 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.)

3. Write an INSERT statement that adds this row to the Customers table: customer_id: 9 email_address: rick@raven.com password: sesame first_name: Rick last_name: Raven Use a column list for this statement.

4. Write an UPDATE statement that modifies the Customers table. Change the password column to “secret” for the customer with an email address of rick@raven.com.

5. Write an UPDATE statement that modifies the Customers table. Change the password column to “reset” for every customer in the table.

6. Open the script named create_mgs_tables.sql that’s attached to HW5. Then, run this script. That should restore the data that’s in the database.

Chapter 8 How to work with data types and functions Exercises

1. Write a SELECT statement that returns these columns from the Products table: The list_price column A column that uses the TO_CHAR function to return the list_price column with currency formatting

2. Write a SELECT statement that returns these columns from the Products table: The date_added column A column that uses the CAST function to return the date_added column as the VARCHAR2 data type with 9 characters

3. Write a SELECT statement that returns these columns from the Products table: The list_price column The discount_percent column A column named discount_amount that uses the previous two columns to calculate the discount amount and uses the ROUND function to round the result so it has 2 decimal digits

4. Write a SELECT statement that returns these columns from the Orders table: The order_date column A column that uses the TO_CHAR function to return the four-digit year that’s stored in the order_date column A column that uses the TO_CHAR function to return the order_date column in this format: MON-DD-YYYY. In other words, use abbreviated months and separate each date component with dashes. A column that uses the TO_CHAR function to return the order_date column with only the hours and minutes on a 12-hour clock with an am/pm indicator A column that uses the TO_CHAR function to return the order_date column in this format: MM/DD/YY HH:SS. In other words, use two-digit months, days, and years and separate them by slashes. Use 2-digit hours and minutes on a 24-hour clock. And use leading zeros for all date/time components.

5. Write a SELECT statement that returns these columns from the Orders table: The card_number column The length of the card_number column The last four digits of the card_number column When you get that working right, add the columns that follow to the result set. This is more difficult because these columns require the use of functions within functions. A column that displays the last four digits of the card_number column in this format: XXXX-XXXX-XXXX-1234. In other words, use Xs for the first 12 digits of the card number and actual numbers for the last four digits of the number.

6. Write a SELECT statement that returns these columns from the Orders table: The order_id column The order_date column A column named approx_ship_date that’s calculated by adding 2 days to the order_date column The ship_date column A column named days_to_ship that shows the number of days between the order date and the ship date When you have this working, add a WHERE clause that retrieves just the orders for March 2012.

Explanation / Answer

HW5: Chapters 7&8 How to insert, update, and delete data Exercises 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 Products table: product_id: 11 category_id: 4 product_code: dgx_640 product_name: Yamaha DGX 640 88-Key Digital Piano description: Long description to come. list_price: 799.99 discount_percent: 0 date_added: Today’s date/time. Use a column list for this statement.

INSERT INTO PRODUCTs (product_id,category_id,product_code,product_name,description,list_price,discount_percent,date_added)
VALUES (11,4,'dgx_640','Yamaha DGX 640 88-Key Digital Piano','Long description to come',799.99,0,sysdate);

2. Write an UPDATE statement that modifies the product you added in exercise 1. This statement should change the discount_percent column from 0% to 35%.

UPDATE PRODUCTs SET discount_percent=35 where discount_percent=0;

3. Write a DELETE statement that deletes the row with category_id = 4 that in the Categories. 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.)

DELETE FROM products WHERE category_id=4;
DELETE FROM Categories WHERE category_id=4;

3. Write an INSERT statement that adds this row to the Customers table: customer_id: 9 email_address: rick@raven.com password: sesame first_name: Rick last_name: Raven Use a column list for this statement.

INSERT INTO Customers (customer_id,email_address,password,first_name,last_name)
VALUES (9,'rick@raven.com','sesame','Rick','Raven');

4. Write an UPDATE statement that modifies the Customers table. Change the password column to “secret” for the customer with an email address of rick@raven.com.

UPDATE Customers SET password='secret' where email_address='rick@raven.com';

5. Write an UPDATE statement that modifies the Customers table. Change the password column to “reset” for every customer in the table.

UPDATE Customers SET password='reset';

6. Open the script named create_mgs_tables.sql that’s attached to HW5. Then, run this script. That should restore the data that’s in the database.

Chapter 8 How to work with data types and functions Exercises
1. Write a SELECT statement that returns these columns from the Products table: The list_price column A column that uses the TO_CHAR function to return the list_price column with currency formatting

SELECT TO_CHAR(list_price,'$9,999.99') from products

2. Write a SELECT statement that returns these columns from the Products table: The date_added column A column that uses the CAST function to return the date_added column as the VARCHAR2 data type with 9 characters

SELECT CAST(date_added,varchar(9)) from products

3. Write a SELECT statement that returns these columns from the Products table: The list_price column The discount_percent column A column named discount_amount that uses the previous two columns to calculate the discount amount and uses the ROUND function to round the result so it has 2 decimal digits

SELECT list_price,discount_percent,ROUND((list_price*(100+discount_percent)/100),2) from products

4. Write a SELECT statement that returns these columns from the Orders table:
The order_date column
A column that uses the TO_CHAR function to return the four-digit year that’s stored in the order_date column
A column that uses the TO_CHAR function to return the order_date column in this format: MON-DD-YYYY. In other words, use abbreviated months and separate each date component with dashes.
A column that uses the TO_CHAR function to return the order_date column with only the hours and minutes on a 12-hour clock with an am/pm indicator
A column that uses the TO_CHAR function to return the order_date column in this format: MM/DD/YY HH:SS. In other words, use two-digit months, days, and years and separate them by slashes. Use 2-digit hours and minutes on a 24-hour clock. And use leading zeros for all date/time components.

SELECT order_date
, TO_CHAR(order_date,'YYYY') as order_year
, TO_CHAR(order_date,'MON-DD-YYYY') AS order_FMT_date
   , TO_CHAR(order_date,'HH:MI AM/PM') AS order_FMT_time
, TO_CHAR(order_date,'MM/DD/YYYY HH24:MI') AS order_date_time
FROM orders;

5. Write a SELECT statement that returns these columns from the Orders table:
The card_number column
The length of the card_number column
The last four digits of the card_number column When you get that working right, add the columns that follow to the result set. This is more difficult because these columns require the use of functions within functions. A column that displays the last four digits of the card_number column in this format: XXXX-XXXX-XXXX-1234. In other words, use Xs for the first 12 digits of the card number and actual numbers for the last four digits of the number.

SELECT card_number
, length(card_number) card_number_len
   , 'XXXX-XXXX-XXXX-'+RIGHT(card_number,4) masked_card_number
FROM orders;

6. Write a SELECT statement that returns these columns from the Orders table:
The order_id column
The order_date column
A column named approx_ship_date that’s calculated by adding 2 days to the order_date column
The ship_date column
A column named days_to_ship that shows the number of days between the order date and the ship date When you have this working,
add a WHERE clause that retrieves just the orders for March 2012.

SELECT order_id
, order_date
   , order_date+2 as approx_ship_date
   , ship_date
   , ship_date-order_date as days_to_ship
FROM orders
WHERE TO_CHAR(order_date,'MON-YYYY')='MAR-2012';

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.