Structured Query Language

CHAPTER 4 Hands-On Assignments 1. Modify the following SQL command so that the Rep_ID column is the PRIMARY KEY for the table and the default value of Y is assigned to the Comm column. ( The Comm column indicates whether the sales representative earns commission.) CREATE TABLE store_reps (rep_ID NUMBER(5), last VARCHAR2(15), first VARCHAR2(10), comm CHAR(1) ); Answer: CREATE TABLE store_reps (rep_ID NUMBER(5), last VARCHAR2(15), first VARCHAR2(10), comm char(1) DEFAULT 'Y', CONSTRAINT "REP_ID" PRIMARY KEY ("REP_ID")); 2.Change the STORE_REPS table so that NULL values can’t be entered in the name columns (First and Last). Answer: alter table store_reps modify (last constraint store_reps_last_nn Not null, first constraint store_reps_first_nn Not null); 3.Change the STORE_REPS table so that only a Y or N can be entered in the Comm column. Answer: alter table store_reps add constraint store_reps_comm_ck check (comm IN ('Y','N')); 4.Add a column named Base_salary with a datatype of NUMBER(7,2) to the STORE_REPS table. Ensure that the amount entered is above zero. Answer: alter table store_reps add (Base_salary Number(7,2)) add constraint store_reps_Base_salary_ck check (Base_salary > 0); 5.Create a table named BOOK_STORES to include the columns listed in the following chart: Answer: create table Book_stores (Store_ID Number(8), Name Varchar2(30), contact varchar2(30), Rep_ID varchar2(5), constraint book_stores_store_ID_pk Primary key (Store_ID), constraint book_stores_name_uk Unique (name)); 6.Add a constraint to make sure the Rep_ID value entered in the BOOK_STORES table is a Column Name Datatype Constraint Comments Store_ID NUMBER(8) PRIMARY KEY column Name VARCHAR2(30) Should be UNIQUE and NOT NULL Contact VARCHAR2(30) Rep_ID VARCHAR2(5) valid value contained in the STORE_REPS table. The Rep_ID columns of both tables were initially created as different datatypes. Does this cause an error when adding the constraint? Yes. Make table modifications as needed so that you can add the required constraint. Answer: alter table book_stores modify (rep_ID number(5)) add constraint book_stores_Rep_ID_fk foreign key (Rep_ID) References Store_Reps (Rep_ID); 7.Change the constraint created in Assignment #6 so that associated rows of the BOOK_STORES table are deleted automatically if a row in the STORE_REPS table is deleted. Answer: alter table book_stores add constraint book_stores_Rep_ID_fk foreign key (Rep_ID) References Store_Reps (Rep_ID) ON DELETE CASCADE; 8.Create a table named REP_CONTRACTS containing the columns listed in the following chart. A composite PRIMARY KEY constraint including the Rep_ID, Store_ID, and Quarter columns should be assigned. In addition,

All courses in CIST

Popular Documents