Tuesday, November 22, 2016

50 Sample Project with Oracle Application Express

OracleFlix Online Media Rentals

If you find it difficult to understand, please go through links in below mentioned google sheet step by step and come back to this page:

https://drive.google.com/open?id=1DRCaFVvwh431AiA9_LHo4bVgz6jxXu-CEfht9P4qoyo

CUSTOMERS

MOVIES

MEDIA

RENTAL HISTORY

ACTORS

STAR_BILLINGS

ERD

Script Content

All the Activities, step 1 through 7, are performed and recorded in this script. This script is uploaded and executed in Oracle Application Express

--Drop view if exist

DROP VIEW title_unavail;

--Drop all tables if they exist

DROP TABLE rental_history PURGE;

DROP TABLE customers PURGE;

DROP TABLE media PURGE;

DROP TABLE star_billings PURGE;

DROP TABLE movies PURGE;

DROP TABLE actors PURGE;

--drop the sequences

DROP SEQUENCE customer_id_seq;

DROP SEQUENCE title_id_seq;

DROP SEQUENCE media_id_seq;

DROP SEQUENCE actor_id_seq;

-- drop the synonym

DROP SYNONYM tu;

--now create tables

--1. Create tables using the attached ERD. AND 2. Creating and Managing Constraints

CREATE TABLE actors

( actor_id NUMBER(10,0) CONSTRAINT atr_atr_id_pk PRIMARY KEY ,

stage_name VARCHAR2(40) CONSTRAINT atr_ste_nae_nn NOT NULL ,

first_name VARCHAR2(25) CONSTRAINT atr_fit_nae_nn NOT NULL ,

last_name VARCHAR2(25) CONSTRAINT atr_lat_nae_nn NOT NULL ,

birth_date DATE CONSTRAINT atr_bih_dae_nn NOT NULL

);

CREATE TABLE movies

( title_id NUMBER(10,0) CONSTRAINT mie_tte_id_pk PRIMARY KEY ,

title VARCHAR2(60) CONSTRAINT mie_tte_nn NOT NULL ,

description VARCHAR2(400) CONSTRAINT mie_dsn_nn NOT NULL ,

--rating VARCHAR2(4) CONSTRAINT atr_rig_chk CHECK (UPPER(rating) IN ('G', 'PG', 'R', 'PG13')),

rating VARCHAR2(4) CONSTRAINT mie_rig_chk CHECK ( rating IN ('G', 'PG', 'R', 'PG13')),

category VARCHAR2(20) CONSTRAINT mie_cey_chk CHECK ( category IN ('DRAMA', 'COMEDY', 'ACTION', 'CHILD', 'SCIFI', 'DOCUMENTARY')),

--category VARCHAR2(20) CONSTRAINT atr_cey_chk CHECK ( UPPER(category) IN ('DRAMA', 'COMEDY', 'ACTION', 'CHILD', 'SCIFI', 'DOCUMENTARY')),

release_date DATE CONSTRAINT mie_rle_dae_nn NOT NULL

);

CREATE TABLE star_billings

( actor_id NUMBER(10,0) CONSTRAINT str_big_atr_id_fk REFERENCES actors(actor_id) ,

title_id NUMBER(10,0) CONSTRAINT str_big_tte_id_fk REFERENCES movies(title_id) ,

comments VARCHAR2(40),

CONSTRAINT str_big_atr_id_tte_id_pk PRIMARY KEY (actor_id, title_id)

);

CREATE TABLE media

( media_id NUMBER(10,0) CONSTRAINT mda_mda_id_pk PRIMARY KEY ,

format VARCHAR2(3) CONSTRAINT mda_fmt_nn NOT NULL ,

title_id NUMBER(10,0) CONSTRAINT mda_tte_id_nn NOT NULL ,

CONSTRAINT mda_tte_id_fk FOREIGN KEY(title_id) REFERENCES movies(title_id)

);

CREATE TABLE customers

( customer_id NUMBER(10,0) CONSTRAINT ctr_ctr_id_pk PRIMARY KEY ,

last_name VARCHAR2(25) CONSTRAINT ctr_lat_nae_nn NOT NULL ,

first_name VARCHAR2(25) CONSTRAINT ctr_fit_nae_nn NOT NULL ,

home_phone VARCHAR2(12) CONSTRAINT ctr_hoe_phe_nn NOT NULL ,

address VARCHAR2(100) CONSTRAINT ctr_ads_nn NOT NULL ,

city VARCHAR2(30) CONSTRAINT ctr_cyy_nn NOT NULL ,

state VARCHAR2(2) CONSTRAINT ctr_ste_nn NOT NULL ,

email VARCHAR2(25),

cell_phone VARCHAR2(12)

);

CREATE TABLE rental_history

( media_id NUMBER(10,0) CONSTRAINT ral_hty_mda_id_fk REFERENCES media(media_id) ,

rental_date DATE DEFAULT SYSDATE ,

customer_id NUMBER(10,0) CONSTRAINT ral_hty_ctr_id_nn NOT NULL ,

return_date DATE ,

CONSTRAINT ral_hty_ctr_id_fk FOREIGN KEY(customer_id) REFERENCES customers(customer_id),

CONSTRAINT ral_hty_mda_id_ral_dae_pk PRIMARY KEY (media_id, rental_date)

);

--3. Create a view called TITLE_UNAVAIL

CREATE OR REPLACE VIEW title_unavail ("movie title", "media id") AS

SELECT movies.title, media.media_id

FROM

rental_history INNER JOIN media ON rental_history.media_id = media.media_id INNER JOIN movies ON media.title_id = movies.title_id

WHERE rental_history.return_date IS NULL

WITH READ ONLY;

--4. Create the following sequences to be used for primary key values

CREATE SEQUENCE customer_id_seq

START WITH 101;

CREATE SEQUENCE title_id_seq;

CREATE SEQUENCE media_id_seq

START WITH 92;

CREATE SEQUENCE actor_id_seq

START WITH 1001;

--5. Add the data to the tables. Be sure to use the sequences for the PKs

INSERT INTO actors(actor_id, stage_name, first_name, last_name, birth_date)

VALUES(actor_id_seq.NEXTVAL, 'Brad Pitt', 'William', 'Pitt', TO_DATE('18-DEC-1963','DD-MON-YYYY'));

INSERT INTO actors(actor_id, stage_name, first_name, last_name, birth_date)

VALUES(actor_id_seq.NEXTVAL, 'Amitabh Bachchan', 'Amit', 'Srivastav', TO_DATE('11-10-1942','DD-MM-YYYY'));

INSERT INTO actors(actor_id, stage_name, first_name, last_name, birth_date)

VALUES(actor_id_seq.NEXTVAL, 'Aamir Khan', 'Aamir', 'Hussain Khan', TO_DATE('14 March 1965','DD Month YYYY'));

INSERT INTO actors(actor_id, stage_name, first_name, last_name, birth_date)

VALUES(actor_id_seq.NEXTVAL, 'Akshay Kumar', 'Rajiv', 'Bhatia', TO_DATE('09/09/1967','DD/MM/YYYY'));

INSERT INTO movies(title_id, title , description , rating , category , release_date )

VALUES(title_id_seq.NEXTVAL, 'Fight Club', 'An insomniac office worker, looking for a way to change his life, crosses paths with a devil-may-care soap maker, forming an underground fight club that evolves into something much, much more.', 'R', 'DRAMA', TO_DATE('15 October 1999','DD Month YYYY'));

INSERT INTO movies(title_id, title , description , rating , category , release_date )

VALUES(title_id_seq.NEXTVAL, 'World War Z', 'Former United Nations employee Gerry Lane traverses the world in a race against time to stop the Zombie pandemic that is toppling armies and governments, and threatening to destroy humanity itself.', 'PG13', 'ACTION',

TO_DATE('21 June 2013','DD Month YYYY'));

INSERT INTO movies(title_id, title , description , rating , category , release_date )

VALUES(title_id_seq.NEXTVAL, 'The Curious Case of Benjamin Button', 'Tells the story of Benjamin Button, a man who starts aging backwards with bizarre consequences.', 'PG13', 'DRAMA',

TO_DATE('25 December 2008','DD Month YYYY'));

INSERT INTO movies(title_id, title , description , rating , category , release_date )

VALUES(title_id_seq.NEXTVAL, 'The Great Gatsby', 'A writer and wall street trader, Nick, finds himself drawn to the past and lifestyle of his millionaire neighbor, Jay Gatsby.', 'PG13', 'DRAMA',

TO_DATE('10 May 2013','DD Month YYYY'));

INSERT INTO movies(title_id, title , description , rating , category , release_date )

VALUES(title_id_seq.NEXTVAL, '3 Idiots', 'Two friends are searching for their long lost companion. They revisit their college days and recall the memories of their friend who inspired them to think differently, even as the rest of the world called them "idiots".', 'PG13', 'DRAMA',

TO_DATE('25 December 2009','DD Month YYYY'));

INSERT INTO movies(title_id, title , description , rating , category , release_date )

VALUES(title_id_seq.NEXTVAL, 'Baby', 'An elite counter-intelligence unit learns of a plot, masterminded by a maniacal madman. With the clock ticking, it''s up to them to track the terrorists'' international tentacles and prevent them from striking at the heart of India.', NULL, 'ACTION',

TO_DATE('23 January 2015','DD Month YYYY'));

INSERT INTO star_billings(actor_id, title_id , comments)

VALUES(1001,1, 'soap salesman named Tyler Durden');

INSERT INTO star_billings(actor_id, title_id , comments)

VALUES(1001,2, 'a former United Nations investigator');

INSERT INTO star_billings(actor_id, title_id , comments)

VALUES(1001,3, NULL);

INSERT INTO star_billings(actor_id, title_id , comments)

VALUES(1002,4, 'a Jewish friend and mentor of Gatsby''s');

INSERT INTO star_billings(actor_id, title_id , comments)

VALUES(1003,5, 'Phunsukh Wangdu');

INSERT INTO star_billings(actor_id, title_id , comments)

VALUES(1004,6, 'as Ajay Singh Rajput');