View the step-by-step solution to:

DROP DROP DROP DROP DROP DROP DROP DROP TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE CUSTOMERS; ORDERS; PUBLISHER; AUTHOR; BOOKS; ORDERITEMS;...

Log on to SQL*PLUS and perform the following assessment lab tasks:
1.While logged on, describe the user_constraints and user_cons_columns tables (these are two of the many examples of the many tables Oracle maintains for each user). Keep this information available as you complete the next few steps.
2.Set the line size to 130.
3.Open Notepad or your favorite text editor. Write a script to do the following: a.Spool your work to a file (named ecom_constraints.txt).
b.Improve the legibility of your output by incorporating some column formatting.
c.Execute two queries against the Oracle tables’ customers and orders.
1.List the customer numbers and names of all individuals who have purchased books in the fitness category.
2.Identify the book written by an author with the last name of Adams. Perform the search using the author name.

4.For the script that has been written for you: a.Type the script excerpt in Notepad.
b.Save the script as ecom_constraints.sql.
c.Execute the script.
-- ecom_constraints.sql compiles constraint-related information from the data dictionary for each
-- user. Results are formatted and spooled to a:ecom_constraints. Check the path of your spool.
spool a:ecom_constraints.sql
SET pagesize 60
COLUMN constraint_name FORMAT A20
COLUMN constraint_type FORMAT A20
COLUMN r_constraint_name FORMAT A20
COLUMN table_name FORMAT A15
COLUMN column_name FORMAT A20
COLUMN position FORMAT 99
SELECT constraint_name, constraint_type, r_constraint_name, table_name
FROM user_constraints
WHERE constraint_name not like 'SYS%';
SELECT constraint_name, table_name, column_name, position
FROM user_cons_columns
WHERE constraint_name not like 'SYS%';

Review the results of these queries from your spooled output file. In particular, output from the user_constraints table provides the relational information you will need (i.e., primary keys include "PK" in their names, and foreign keys include "FK" in their names). The column and table on which the constraints are defined can be identified from the user_cons_columns output.
DROP TABLE CUSTOMERS; DROP TABLE ORDERS; DROP TABLE PUBLISHER; DROP TABLE AUTHOR; DROP TABLE BOOKS; DROP TABLE ORDERITEMS; DROP TABLE BOOKAUTHOR; DROP TABLE PROMOTION; Create table Customers (Customer# NUMBER(4) PRIMARY KEY, LastName VARCHAR2(10), FirstName VARCHAR2(10), Address VARCHAR2(20), City VARCHAR2(12), State VARCHAR2(2), Zip VARCHAR2(5), Referred NUMBER(4)); INSERT INTO CUSTOMERS VALUES (1001, 'MORALES', 'BONITA', 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', NULL); INSERT INTO CUSTOMERS VALUES (1002, 'THOMPSON', 'RYAN', 'P.O. BOX 9835', 'SANTA MONICA', 'CA', '90404', NULL); INSERT INTO CUSTOMERS VALUES (1003, 'SMITH', 'LEILA', 'P.O. BOX 66', 'TALLAHASSEE', 'FL', '32306', NULL); INSERT INTO CUSTOMERS VALUES (1004, 'PIERSON', 'THOMAS', '69821 SOUTH AVENUE', 'BOISE', 'ID', '83707', NULL); INSERT INTO CUSTOMERS VALUES (1005, 'GIRARD', 'CINDY', 'P.O. BOX 851', 'SEATTLE', 'WA', '98115', NULL); INSERT INTO CUSTOMERS VALUES (1006, 'CRUZ', 'MESHIA', '82 DIRT ROAD', 'ALBANY', 'NY', '12211', NULL); INSERT INTO CUSTOMERS VALUES (1007, 'GIANA', 'TAMMY', '9153 MAIN STREET', 'AUSTIN', 'TX', '78710', 1003); INSERT INTO CUSTOMERS VALUES (1008, 'JONES', 'KENNETH', 'P.O. BOX 137', 'CHEYENNE', 'WY', '82003', NULL); INSERT INTO CUSTOMERS VALUES (1009, 'PEREZ', 'JORGE', 'P.O. BOX 8564', 'BURBANK', 'CA', '91510', 1003); INSERT INTO CUSTOMERS VALUES (1010, 'LUCAS', 'JAKE', '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314', NULL); INSERT INTO CUSTOMERS VALUES (1011, 'MCGOVERN', 'REESE', 'P.O. BOX 18', 'CHICAGO', 'IL', '60606', NULL); INSERT INTO CUSTOMERS VALUES (1012, 'MCKENZIE', 'WILLIAM', 'P.O. BOX 971', 'BOSTON', 'MA', '02110', NULL); INSERT INTO CUSTOMERS VALUES (1013, 'NGUYEN', 'NICHOLAS', '357 WHITE EAGLE AVE.', 'CLERMONT', 'FL', '34711', 1006); INSERT INTO CUSTOMERS VALUES (1014, 'LEE', 'JASMINE', 'P.O. BOX 2947', 'CODY', 'WY', '82414', NULL); INSERT INTO CUSTOMERS VALUES (1015, 'SCHELL', 'STEVE', 'P.O. BOX 677', 'MIAMI', 'FL', '33111', NULL); INSERT INTO CUSTOMERS VALUES (1016, 'DAUM', 'MICHELL', '9851231 LONG ROAD', 'BURBANK', 'CA', '91508',
Background image of page 1
1010); INSERT INTO CUSTOMERS VALUES (1017, 'NELSON', 'BECCA', 'P.O. BOX 563', 'KALMAZOO', 'MI', '49006', NULL); INSERT INTO CUSTOMERS VALUES (1018, 'MONTIASA', 'GREG', '1008 GRAND AVENUE', 'MACON', 'GA', '31206', NULL); INSERT INTO CUSTOMERS VALUES (1019, 'SMITH', 'JENNIFER', 'P.O. BOX 1151', 'MORRISTOWN', 'NJ', '07962', 1003); INSERT INTO CUSTOMERS VALUES (1020, 'FALAH', 'KENNETH', 'P.O. BOX 335', 'TRENTON', 'NJ', '08607', NULL); Create Table Orders (Order# NUMBER(4) PRIMARY KEY, Customer# NUMBER(4), OrderDate DATE, ShipDate DATE, ShipStreet VARCHAR2(18), ShipCity VARCHAR2(15), ShipState VARCHAR2(2), ShipZip VARCHAR2(5)); INSERT INTO ORDERS VALUES (1000,1005,'31-MAR-05','02-APR-05','1201 ORANGE AVE', 'SEATTLE', 'WA', '98114'); INSERT INTO ORDERS VALUES (1001,1010,'31-MAR-05','01-APR-05', '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314'); INSERT INTO ORDERS VALUES (1002,1011,'31-MAR-05','01-APR-05','58 TILA CIRCLE', 'CHICAGO', 'IL', '60605'); INSERT INTO ORDERS VALUES (1003,1001,'01-APR-05','01-APR-05','958 MAGNOLIA LANE', 'EASTPOINT', 'FL', '32328'); INSERT INTO ORDERS VALUES (1004,1020,'01-APR-05','05-APR-05','561 ROUNDABOUT WAY', 'TRENTON', 'NJ', '08601'); INSERT INTO ORDERS VALUES (1005,1018,'01-APR-05','02-APR-05', '1008 GRAND AVENUE', 'MACON', 'GA', '31206'); INSERT INTO ORDERS VALUES (1006,1003,'01-APR-05','02-APR-05','558A CAPITOL HWY.', 'TALLAHASSEE', 'FL', '32307'); INSERT INTO ORDERS VALUES (1007,1007,'02-APR-05','04-APR-05', '9153 MAIN STREET', 'AUSTIN', 'TX', '78710'); INSERT INTO ORDERS VALUES (1008,1004,'02-APR-05','03-APR-05', '69821 SOUTH AVENUE', 'BOISE', 'ID', '83707'); INSERT INTO ORDERS VALUES (1009,1005,'03-APR-05','05-APR-05','9 LIGHTENING RD.', 'SEATTLE', 'WA', '98110'); INSERT INTO ORDERS VALUES (1010,1019,'03-APR-05','04-APR-05','384 WRONG WAY HOME', 'MORRISTOWN', 'NJ', '07960'); INSERT INTO ORDERS VALUES (1011,1010,'03-APR-05','05-APR-05', '102 WEST LAFAYETTE', 'ATLANTA', 'GA', '30311'); INSERT INTO ORDERS VALUES (1012,1017,'03-APR-05',NULL,'1295 WINDY AVENUE', 'KALMAZOO', 'MI', '49002'); INSERT INTO ORDERS
Background image of page 2
Show entire document
Sign up to view the entire interaction

Top Answer

When you write command set linesize 130 and then enter you will not see any visible output... View the full answer

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online