System Querrie Language

Week 4 – SQL Exercises

Student Name: ____________________________

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.

CREATE TABLE tblCustomers

(customer_id INTEGER NOT NULL,

address_type VARCHAR (15) NOT NULL,

customer name VARCHAR (20),

customer e-mail VARCHAR (30),

street address VARCHAR (25),

city VARCHAR (20),

state CHAR (2),

zipcode VARCHAR (9),

phone number NUMBER (10),

CONTRAINT tblCustomers_PK PRIMARY KEY (customer_id, address_type));

SELECT customer_id, address_type, customer name

FROM tblCustomers

WHERE state = ‘DE’;

SELECT zipcode, customer_id, customer name FROM tblCustomers

WHERE zipcode BETWEEN ‘19701’ AND ‘19702’;

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...