--1
CREATE TABLE customers
(cust_no NUMBER(5) PRIMARY KEY,
 name CHAR(20) NOT NULL,
 address varchar2(40) NOT NULL,
 dob DATE,
 id_card_no NUMBER(13) UNIQUE,
 cust_type CHAR(1) CHECK(cust_type IN('A','B','C'))
);

--2
CREATE TABLE customer1
(cust_no NUMBER(5) ,
 name CHAR(20) NOT NULL,
 address varchar2(40) NOT NULL,
 dob DATE,
 id_card_no NUMBER(13) ,
 cust_type CHAR(1),

 PRIMARY KEY(cust_no),
 UNIQUE(id_card_no),
 CHECK(cust_type IN('A','B','C'))
);

--Foreign Key 1
CREATE TABLE order1
(ord_no NUMBER(5) PRIMARY KEY,
 ord_date DATE DEFAULT sysdate NOT NULL,
 amount NUMBER(9,2) NOT NULL,
 cust_no NUMBER(5) REFERENCES customers(cust_no)
);

--Foreign Key 2
CREATE TABLE order2
(ord_no NUMBER(5) ,
 ord_date DATE DEFAULT sysdate NOT NULL,
 amount NUMBER(9,2) NOT NULL,
 cust_no NUMBER(5),

 PRIMARY KEY(ord_no),
 FOREIGN KEY(cust_no) REFERENCES customer1(cust_no)
);

--Subquery
CREATE TABLE dept80
 AS
  SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
  FROM employees
  WHERE department_id = 80;

--Describe
DESC dept80;

--Alter Table (Add)
ALTER TABLE dept80
ADD(surname CHAR(20));

--Alter Table (Modify)
ALTER TABLE dept80
MODIFY(surname CHAR(30));

--Alter Table (Drop)
ALTER TABLE dept80
DROP(surname);

--Drop Table
DROP TABLE customers;

------------------------------------------------------------------------------------------------------------------------

SELECT * FROM departments;

--Insert1
INSERT INTO departments(department_id, department_name, manager_id, location_id)
VALUES (290, 'Public Relations', 100, 1700);

--Insert2
INSERT INTO departments
VALUES (330, 'IT Audit', 100, 1700);

--Insert3 (Value that not input will stay as null)
INSERT INTO departments(department_id, department_name)
VALUES (333, 'IT');

--Insert4
INSERT INTO departments
VALUES (444, 'IT Support', NULL, NULL);

SELECT * FROM employees;

--Insert (SYSDATE)
INSERT INTO employees
VALUES (300, 'Louis', 'Popp', 'EPOPP', '515.124.4567', sysdate, 'AC_ACCOUNT', 6900, NULL, 205, 110);

--Insert w/ Dialog Box
INSERT INTO departments
VALUES (&department_id,'&department_name',NULL,&location_id);

------------------------------------------------------------------------------------------------------------------------
--Excercise
CREATE TABLE student
 (id NUMBER(2),name varchar2(10),major varchar2(2),
  PRIMARY KEY(id),
  UNIQUE(name)
);

CREATE TABLE class
 (code varchar2(6),title varchar2(25),
  PRIMARY KEY(code),
  UNIQUE(title)
);

CREATE TABLE transcription
 (id NUMBER(2),code varchar2(6), grade NUMBER(2) NOT NULL,
  PRIMARY KEY(id,code),
  FOREIGN KEY(id) REFERENCES student(id),
  FOREIGN KEY(code) REFERENCES class(code),
  CHECK(grade BETWEEN 1 AND 10)
);

DROP TABLE transcription;

--Excercise(Edited w/Fail)
CREATE TABLE student
 (id NUMBER(2),name varchar2(10),"Major" varchar2(2),
  PRIMARY KEY(id),
  UNIQUE(name)
);

CREATE TABLE class
 ("Code" varchar2(6),"Title" varchar2(25),
  PRIMARY KEY("Code"),
  UNIQUE("Title")
);

CREATE TABLE transcription
 (id NUMBER(2),"Code" varchar2(6), "Grade(10)" NUMBER(2) NOT NULL,
  PRIMARY KEY(id,"Code"),
  FOREIGN KEY(id) REFERENCES student(id),
  FOREIGN KEY("Code") REFERENCES class("Code"),
  CHECK("Grade(10)" BETWEEN 1 AND 10)
);

DROP TABLE transcription;
DROP TABLE student;
DROP TABLE class;