1. Write an INSERT statement that adds this row to the Categories table: CategoryName: Brass Code...
1. Write an INSERT statement that adds this row to the Categories table:
CategoryName: Brass
Code the INSERT statement so SQL Server automatically generates the value for the CategoryID column.
2. Write an UPDATE statement that modifies the row you just added to the Categories table. This statement should change the Category Name column to “Woodwinds”, and it should use the CategoryID column to identify the row.
3.Write an INSERT statement that adds this row to the Products
table:
ProductID: The next automatically generated ID
CategoryID: 4
ProductCode: dgx_640
ProductName: Yamaha DGX 640 88-Key Digital Piano
Description: Long description to come.
ListPrice: 799.99
DiscountPercent: 0
DateAdded: Today’s date/time (note: be sure to use a function, not
hard-coded).
Use a column list for this statement.
4. Write a DELETE statement that deletes the row in the Categories table that has an ID of 4. 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.
5. Write an UPDATE statement that modifies the Customers table. Change the password column to “reset” for every customer in the table.
6. Modify the UPDATE statement in #5 to change the password column to “ reset ” for only the customer with an email address of [email protected]
7. Write a DELETE statement that deletes rows from the Products table for products that do not have any orders for that Products.
Homework Answers
Add Answer to:
1. Write an INSERT statement that adds this row to the
Categories table:
CategoryName: Brass
Code...
Write a script that creates and calls a stored procedure named spInsertProduct that inserts a row...
Write a script that creates and calls a stored procedure named spInsertProduct that inserts a row into the Products table. This stored procedure should accept five parameters. One parameter for each of these columns: CategoryID, ProductCode, ProductName, ListPrice, and DiscountPercent. This stored procedure should set the Description column to an empty string, and it should set the DateAdded column to the current date. If the value for the ListPrice column is a negative number, the stored procedure should raise an...
3. Write a script that adds rows to the database that you created in exercise 2....
3. Write a script that adds rows to the database that you created in exercise 2. Add two rows to the Users and Products tables. Add three rows to the Downloads table: one row for user 1 and product 2; one row for user 2 and product 1; and one row for user 2 and product 2. Use the SYSDATE function to insert the current date into the download_date column. Use the sequences created in the previous exercise to get...
SQL Homework exercises: 1. Write INSERT statements that add two rows to the Members table for...
SQL Homework exercises: 1. Write INSERT statements that add two rows to the Members table for member IDs 1 and 2, two rows to the Groups table for group IDs 1 and 2, and three rows to the Group_Membership table: one row for member 1 and group 2; one for member 2 and group 1; and one for member 2 and group 2. Then, write a SELECT statement that joins the three tables and retrieves the group name, member last...
2. Write a script that implements the following design: In the Downloads table, the user_id and...
2. Write a script that implements the following design: In the Downloads table, the user_id and product_id columns are the foreign keys. Create these tables in the ex schema. Create the sequences for the user_id, download_id, and product_id columns. Include a PL/SQL script to drop the table or sequence if it already exists. Include any indexes that you think are necessary. 3. Write a script that adds rows to the database that you created in exercise 2. Add two rows...
this is sql developer question I just need the format of how to answer question! Create...
this is sql developer question I just need the format of how to answer question! Create sequences (2) that can be used to number the member ID and group ID values starting with 3 (since you already have 1 and 2). Write an INSERT statement that adds another row to the Groups table, make up a group name. Use the NEXTVAL pseudo column to get the value for the next group ID from the sequence that you created in #5....
5. Write a SELECT statement that returns these columns from the Orders table: OrderID OrderDate ShipDate...
5. Write a SELECT statement that returns these columns from the Orders table: OrderID OrderDate ShipDate Return only the rows where the ShipDate column contains a null value. 6. Write a SELECT statement that returns all columns from the Addresses table State is equal to CA 7. Write a SELECT statement that returns all columns from the Products table Product name has a "ru" in its name 8. Write a SELECT statement that returns these columns from the Customers table...
Table structure: administrators = admin_id, email_address, password, first_name, last_name categories = category_id, category_name customers = customer_id,...
Table structure:
administrators = admin_id, email_address,
password, first_name, last_name
categories = category_id, category_name
customers = customer_id, email_address,
password, first_name, last_name, shipping_address_id,
billing_address_id
order_items = item_id, order_id, product_id,
item_price, discount_amount, quantity
orders = order_id, customer_id, order_date,
ship_amount, tax_amount, ship_date, ship_address_id, card_type,
card_number, card_expires, billing_address_id
products = product_id, category_id,
product_code, product_name, description, list_price,
discount_percent, date_added
5. Write a SELECT statement that uses regular expression functions to get the username and domain name parts of the email addresses in the Administrators table. Return...
What happens when you try to compile and run the following code? String query = "INSERT...
What happens when you try to compile and run the following code? String query = "INSERT INTO Invoices (InvoiceDate InvoiceTotal) " + "VALUES ('2/10/01', '443.55')"; Statement statement = connection.createStatement(); statement.executeUpdate(query); a. A SQLException is thrown at runtime because the executeUpdate method can’t accept a String object. b. A SQLException is thrown at runtime because the SQL statement is coded incorrectly. c. An error occurs at compile time because the SQL statement is coded incorrectly. d. This code compiles and...
#6 Write a SELECT statement that returns these columns: The count of the number of orders...
#6 Write a SELECT statement that returns these columns: The count of the number of orders in the Orders table The sum of the tax_amount columns in the Orders table Write a SELECT statement that returns one row for each category that has products with these columns: The category_name column from the Categories table The count of the products in the Products table The list price of the most expensive product in the Products table Sort the result set so...
Learning Objectives: Learn to define constraints on tables Lean to define primary keys and foreign keys...
Learning Objectives: Learn to define constraints on tables Lean to define primary keys and foreign keys Exercise In Lab 09, we will continue the practice of creating tables. You will need to write SQL statements for the tasks specified below. After you complete them, submit your SQL script (in one *.sql file) to Blackboard by 11:59pm Nov. 16 (Friday). Include your name as a comment in the SQL script submitted. The creation statement for each table is worth one point,...
5. Write a SELECT statement that returns these columns from the Orders table: OrderID OrderDate ShipDate Return only the rows where the ShipDate column contains a null value. 6. Write a SELECT statement that returns all columns from the Addresses table State is equal to CA 7. Write a SELECT statement that returns all columns from the Products table Product name has a "ru" in its name 8. Write a SELECT statement that returns these columns from the Customers table...
Table structure:
administrators = admin_id, email_address,
password, first_name, last_name
categories = category_id, category_name
customers = customer_id, email_address,
password, first_name, last_name, shipping_address_id,
billing_address_id
order_items = item_id, order_id, product_id,
item_price, discount_amount, quantity
orders = order_id, customer_id, order_date,
ship_amount, tax_amount, ship_date, ship_address_id, card_type,
card_number, card_expires, billing_address_id
products = product_id, category_id,
product_code, product_name, description, list_price,
discount_percent, date_added
5. Write a SELECT statement that uses regular expression functions to get the username and domain name parts of the email addresses in the Administrators table. Return...
Most questions answered within 3 hours.
-
A telephone survey uses a random digit dialing machine to call
subjects. The random digit dialing...
asked 30 minutes ago -
How can having too little or too much of a certain
protein cause problems for an...
asked 1 hour ago -
Assume a muscle has a PCSA = 20 cm2 and Lo = 12 cm. Assume it...
asked 3 hours ago -
What is the yield to maturity of a ten-year, $1,000 bond with a
5.2% coupon rate...
asked 4 hours ago -
A mass m = 5 kg is tied on one end of a rope and is...
asked 4 hours ago -
The Average sales price of single-family houses in Charlotte is
$210,000 with a standard deviation of...
asked 4 hours ago -
Target Costing
Laser Impressions, Inc., manufactures color laser printers.
Model J20 presently sells for $225 and...
asked 4 hours ago -
a bottle cap manufacturer with four machines and six operators
wants to see if variation in...
asked 5 hours ago -
State Farm Insurance studies show that in Colorado, 55% of the
auto insurance claims submitted for...
asked 6 hours ago -
Complete the following reactions which form ethers (A
and B) and cyclic ethers (C-E) as major...
asked 6 hours ago -
in a perfectly elastic collision what is the velocity of ball A
if the original direction...
asked 6 hours ago -
PLEASE ANSWER ALL
1) The pressure of the atmosphere decreases with increasing
altitude in the
Choose...
asked 7 hours ago