CREATE TABLE CATEGORY (
  CatCode VARCHAR(2),
  CatDesc VARCHAR(10)
);
--2. Create a new table containing these four columns: Emp#, Lastname, Firstname, and
--Job_class. The table name should be EMPLOYEES. The Job_class column should be able
--to store character strings up to a maximum length of four, but the column values shouldn’t
--be padded if the value has less than four characters. The Emp# column contains a numeric
--ID and should allow a five-digit number. Use column sizes you consider suitable for the
--Firstname and Lastname columns.
CREATE TABLE EMPLOYEES (
  Emp# NUMBER(5),
  Lastname VARCHAR(50),
  Firstname VARCHAR(50),
  Job_class CHAR(4)
);

--3. Add two columns to the EMPLOYEES table. One column, named EmpDate, contains the
--date of employment for each employee, and its default value should be the system date.
--The second column, named EndDate, contains employees’ date of termination.
ALTER TABLE EMPLOYEES ADD(EmpDate DATE DEFAULT SYSDATE,EndDate DATE);

--4. Modify the Job_class column of the EMPLOYEES table so that it allows storing a maximum
--width of two characters.
ALTER TABLE EMPLOYEES MODIFY Job_class VARCHAR2(2);

--5. Delete the EndDate column from the EMPLOYEES table.
ALTER TABLE EMPLOYEES DROP COLUMN EndDate;

--6. Rename the EMPLOYEES table as JL_EMPS.
RENAME EMPLOYEES TO JL_EMPS;

--7. Create a new table containing these four columns from the existing BOOKS table: ISBN,
--Cost, Retail, and Category. The name of the ISBN column should be ID, and the other
--columns should keep their original names. Name the new table BOOK_PRICING.
CREATE TABLE BOOK_PRICING AS
SELECT ISBN AS ID, Cost, Retail, Category
FROM BOOKS;

--8. Mark the Category column of the BOOK_PRICING table as unused. Verify that the column
--is no longer available.
ALTER TABLE BOOK_PRICING SET UNUSED (Category);
DESC BOOK_PRICING;

--9. Truncate the BOOK_PRICING table, and then verify that the table still exists but no longer
--contains any data.
TRUNCATE TABLE BOOK_PRICING;
SELECT * FROM BOOK_PRICING;
--10. Delete the BOOK_PRICING table permanently so that it isn’t moved to the recycle bin.
--Delete the JL_EMPS table so that it can be restored. Restore the JL_EMPS table and
--verify that it’s available again.
DROP TABLE BOOK_PRICING PURGE;
DROP TABLE JL_EMPS;
FLASHBACK TABLE JL_EMPS TO BEFORE DROP;
SELECT *FROM JL_EMPS;


--Chapter 4
--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));CREATE TABLE store_reps
CREATE TABLE store_reps(
rep_ID NUMBER(5),
last VARCHAR2(15),
first VARCHAR2(10),
comm CHAR(1) DEFAULT 'Y');

--2. Change the STORE_REPS table so that NULL values can’t be entered in the name
--columns (First and Last).
ALTER TABLE store_reps
MODIFY (last CONSTRAINT store_reps_lastname_nn NOT NULL,
first CONSTRAINT store_reps_firstname_nn NOT NULL);

--3. Change the STORE_REPS table so that only a Y or N can be entered in the Comm
--column.
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.
ALTER TABLE store_reps
ADD (Base_salary NUMBER(7,2) CHECK (base_salary > 0));

--5. Create a table named BOOK_STORES to include the columns listed in the following chart.
CREATE TABLE BOOK_STORES(
store_id NUMBER(8),
name VARCHAR2(30) NOT NULL,
contact VARCHAR2(30),
rep_id VARCHAR2(5),
CONSTRAINT bookstores_store_id_pk PRIMARY KEY (store_id),
CONSTRAINT bookstores_name_uk UNIQUE (name));


--6. Add a constraint to make sure the Rep_ID value entered in the BOOK_STORES table is a
--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? Make table modifications as needed so that you can add the required
--constraint.
--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)

--yea it cause a error when adding the constraint
ALTER TABLE BOOK_STORES
MODIFY (rep_id NUMBER(5))
ADD CONSTRAINT 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.
ALTER TABLE BOOK_STORES
ADD CONSTRAINT 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, FOREIGN KEY constraints should be assigned to
--both the Rep_ID and Store_ID columns.
--Column Name Datatype
--Store_ID NUMBER(8)
--Name NUMBER(5)
--Quarter CHAR(3)
--Rep_ID NUMBER(5)
CREATE TABLE REP_CONTRACTS(
Store_ID NUMBER(8),
Name NUMBER(5),
Quarter CHAR(3),
Rep_ID NUMBER(5),
CONSTRAINT rep_contracts_pk PRIMARY KEY (store_id, rep_id, quarter),
CONSTRAINT rep_contracts_store_id_fk FOREIGN KEY (store_id)
REFERENCES book_stores (store_id),
CONSTRAINT rep_contracts_rep_id_fk FOREIGN KEY (rep_id)
REFERENCES store_reps (rep_id));

--9. Produce a list of information about all existing constraints on the STORE_REPS table.
SELECT constraint_name, constraint_type, search_condition, r_constraint_name
FROM user_constraints
WHERE table_name = 'STORE_REPS';

--10. Issue the commands to disable and then enable the CHECK constraint on the Base_salary
--column.
ALTER TABLE store_reps
DISABLE CONSTRAINT base_salary_ck;
ALTER TABLE store_reps
ENABLE CONSTRAINT base_salary_ck;