From Customer;

Jeff Hardy

IST210 Lab 6. 7/31/09

1.selectcount(*)

from customer;

(1 row(s) affected)

selectcount(*)

from orderline;

(1 row(s) affected)

selectcount(*)

from orders;

(1 row(s) affected)

selectcount(*)

from product;

(1 row(s) affected)

selectcount(*)

from region;

(1 row(s) affected)

2.Create a view named “customers_in_pa” that will include all columns of the customer table but will only include the rows where a customer is in the state of Pennsylvania. (state = “PA”).

CREATEVIEW customers_in_pa AS

Select CustomerNo, CustomerName, City,State, Region_id

From Customer

WhereState='PA';

Command(s) completed successfully.

3. Insert the following data with the use of the view created in question # 2 with the INSERT INTO command.

Customerno Customername City State Region

12556 Brown Mfg.PittsburghPAEast

30123 Henry Corp.IndianaPA East

31345 Keystone Rental ColumbusOHMEast

INSERTINTO customers_in_pa

VALUES(12556,'Brown MFG','Pittsburgh','PA','East');

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__Customer__023D5A04'. Cannot insert duplicate key in object 'dbo.Customer'.

The statement has been terminated.

INSERTINTO customers_in_pa

VALUES(30123,'Henry Corp','Indiana','PA','East');

(1 row(s) affected)

INSERTINTO customers_in_pa

VALUES(31345,'Keystone Rental','Columbus','OH','MEast');

(1 row(s) affected

4. Why was the one entry unsuccessful in question # 3?

The entry was unsuccessful because there was already an entry in the table for this customer.

5.Create and execute a select statement using the view you created in question # 2 as the source relation in the FROM clause. Include all the columns of the view.

Select CustomerNo, CustomerName, City,State

From customers_in_pa

WhereState='PA';

CustomerNoCustomerNameCity State

------

10123T&B Machine Altoona PA

12256 Toy Engineering Erie PA

12556 Toy Engineering Erie PA

17800 Buyer Mfg Butler PA

30123 Henry Corp Indiana PA

(5 row(s) affected)

6. For the select in question # 5, did you receive all the records you successfully inserted in question # 3? If not, why? The only successful entry from question 3 that was displayed was Henry Group in Indiana PA. The other successful entry was not displayed because the state is OH not PA.

7. Create a view named “orderline_listprice” that will include all the columns of the ORDERLINE and the LISTPRICE column of the PRODUCT table. You will use a select statement within the create view statement that will join the records of the ORDERLINE table and the records of the PRODUCT table when the PRODUCTID columns are equal.

CREATEVIEW orderline_listprice AS

Select OrderLine.ProductID, OrderNo, Qty, UnitPrice, ShipDate, ListPrice

From OrderLine, Product

Where OrderLine.ProductID = Product.ProductID;

Command(s) completed successfully.

8. Create and execute a select statement that will use the view created in # 7 retrieving the rows of data where LISTPRICE is less than 11.00 and display all available columns.

Select ProductID, OrderNo, Qty, UnitPrice, ShipDate, ListPrice

From orderline_listprice

Where ListPrice 11.00;

ProductID OrderNoQtyUnitPriceShipDate ListPrice

------

Rectangle 801003 12 10.15 2009-01-01 00:00:00.000 10.15

Rectangle 801005 89 10.15 2009-01-02 00:00:00.000 10.15

Rectangle 801009 41 10.15 2009-01-03 00:00:00.000 10.15

Rectangle 801011 125 10.15 2009-01-04 00:00:00.000 10.15

Rectangle 801013 75 10.15 2009-01-05 00:00:00.000 10.15

Rectangle 801016 110 10.15 2009-01-06 00:00:00.000 10.15

Rectangle 801024 51 10.15 2009-01-10 00:00:00.000 10.15

Rectangle 801027 26 10.15 2009-01-11 00:00:00.000 10.15

Rectangle 801031 26 10.15 2009-01-15 00:00:00.000 10.15

Rectangle 801033 86 10.15 2009-01-18 00:00:00.000 10.15

Rectangle 801037 151 10.15 2009-01-22 00:00:00.000 10.15

(11 row(s) affected)