Answered step by step
Verified Expert Solution
Question
1 Approved Answer
using SQL Server, write SQL queries for each problem in file below. you need to attach/create both Northwind & PVC databases using the instructions given
using SQL Server, write SQL queries for each problem in file below.
you need to attach/create both Northwind & PVC databases using the instructions given
Use Northwind database: 1. Write a query that deletes one of the customers in the Northwind DB. The customer with the ID of 'ERNSH' is no longer our customer and so the database should reflect this change. 2. Northwind has recently experienced a large increase in sales. Write a query that shows that one new order has been completed for a new customer; the company name is CONAR and the address is 6900W Thousand Oaks Blvd, Thousand Oaks, CA 91360 , USA. As the Order has not yet been shipped, no shipping info is yet to be added to the order record. [Hint: check if identity is set to "on" for the PK in any tables you are inserting data to see how to handle data for the PK]. 3. One Northwind supplier has recently notified the management that they were in the process to change their corporate names. Bigfoot is going international and so they decided their name should reflect this change. Bigfoot Breweries is now to be named Bigfoot International. Your job as an analyst at Northwind is to update the database to reflect these changes. Use PVC database: 1. Modify the PRODUCT_T table by adding a column labeled "QtyonHand" that can be used to track the finished goods inventory. You need to decide on appropriate datatype. Explain your datatype choice (use a comment in SQL Server). 2. Enter sample data of your own choosing into the PRODUCT_T table. Include a sample value for each attribute in the table. [Hint: check if identity is set to "on" for the PK to see how to handle data for the PK]. Also, test the modification you did in Q1 above and update the QtyonHand for the product "Computer Desk" to 100 units. 3. Add an order to the ORDER T table for a customer "California Closets" headquartered at 12500 W Olsen Rd. Thousand Oaks, CA 91360. As you enter the new order, make sure to include a sample value for each attribute in the ORDER_T table. [Hint: check if identity is set to "on" for the PK in any tables you are inserting data to see how to handle data for the PK]. 4. Add sample data to the table SUPPLIES_T. Be sure to add a value into each column of any table you are adding data to. [Hint: check if identity is set to "on" for the PK in any tables you are inserting data to see how to handle data for the PK]. 5. Delete the Customer named "Home Furnishings" from CUSTOMER_T table. Discuss whether this is possible. [You may need to alter some table definitions so that this deletion is allowed]Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started