Dell Papaer

Week 4 – SQL Exercises

Student Name :_Cesalie Miller_____________________________

http://www.databasedev.co.uk/customer_orders_data_model.html

Use the data model shown above and create the following SQL statements:

SQL Exercises

1. Create a new table that will be used to track the tblCustomer address and contact information. The attributes associated with the table: customer_id, address_type street address, city, state, zipcode, phone number, customer name and e-mail address. Create a new table the attributes listed above. The primary key is customer_id, and address_type. It has a relationship with the customer table. Provide SQL statement below.

2. Create new table it will be used to identify the Sales associate gets the credit for the customer order. This attributes associated with table: Salesassoc_id, sales assoc_name, and sales location. The primary key is salesassoc_id. The table has a relationship with the order table Provide SQL statement below.

CREATE TABLE SalesAssociate_CustOrder

(

salesassoc_id BIGINT NOT NULL PRIMARY KEY,

salesassoc_name VARCHAR(64) NOT NULL,

sales_loc VARCHAR(64) NOT NULL,

order_id BIGINT NOT NULL

)

GO

ALTER TABLE SalesAssociate_CustOrder

ADD CONSTRAINT FK_SalesAssociateCustOrder_orderid_Order_id FOREIGN KEY (order_id) REFERENCES ORDER(order_id)

3. Create a query to select all data from the tblcustomer data. Provide the SQL statement below:

Column_tblcustomer,column_tblcustomer

FROM Table_name;

4. Create a basic query that will extract the following information from the tblProduct table: ingproductid, strproductname, chkinstock, Ingunitsinstock, ingshippingid. Provide the SQL statement below:

SELECT ingproductid, strproductname, chkinstock, Ingunitsinstock, ingshippingid

FROM tblProduct;

5. You need to find all customer orders placed between January 2011 and June 2011. Provide the SQL...