Showing posts with label Database Programming with SQL. Show all posts
Showing posts with label Database Programming with SQL. Show all posts

Wednesday, March 8, 2017

Create database and content using psql





This post is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html


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:


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');

INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'DVD',1);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'VHS',1);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'DVD',2);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'VHS',2); 
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'DVD',3);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'VHS',3);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'DVD',4);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'VHS',4);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'DVD',5);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'VHS',5);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'DVD',6);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'VHS',6);

INSERT INTO customers(customer_id, last_name, first_name,  home_phone, address, city, state, email,  cell_phone)
VALUES(customer_id_seq.NEXTVAL,'Palombo', 'Lisa', '716-270-2669', '123 Main St', 'Buffalo', 'NY', 'palombo@ecc.edu', '716-555-1212');
INSERT INTO customers(customer_id, last_name, first_name,  home_phone, address, city, state, email,  cell_phone)
VALUES(customer_id_seq.NEXTVAL,'Bee', 'Cole', '407-555-8234', '123 Main St', 'Orlando', 'FL', 'cbee@edu.edu', NULL);
INSERT INTO customers(customer_id, last_name, first_name,  home_phone, address, city, state, email,  cell_phone)
VALUES(customer_id_seq.NEXTVAL,'Twee', 'Zoe', '709-867-5309', '1009 Oliver Avenue', 'Boston', 'MA', NULL, NULL);
INSERT INTO customers(customer_id, last_name, first_name,  home_phone, address, city, state)
VALUES(customer_id_seq.NEXTVAL,'Rampal', 'Amarjeet', '408-809-1212', '1212 Down Trail', 'Buffalo', 'NY');
INSERT INTO customers(customer_id, last_name, first_name,  home_phone, address, city, state)
VALUES(customer_id_seq.NEXTVAL,'Tulli', 'Manoj', '404-200-3011', '3011 Great Rd', 'Atlanta', 'GA');
INSERT INTO customers(customer_id, last_name, first_name,  home_phone, address, city, state)
VALUES(customer_id_seq.NEXTVAL,'Hare Krishna', 'Goswami', '415-503-4444', '405 Church St.', 'Lawrenceville', 'GA');



INSERT INTO rental_history(media_id, rental_date, customer_id,  return_date)
VALUES(94, TO_DATE('09/09/1967','DD/MM/YYYY'), 106, TO_DATE('16/09/1967','DD/MM/YYYY') );
INSERT INTO rental_history(media_id, rental_date, customer_id,  return_date)
VALUES(94, TO_DATE('20/06/1968','DD/MM/YYYY'), 104, NULL );
INSERT INTO rental_history(media_id, rental_date, customer_id,  return_date)
VALUES(92, TO_DATE('19-SEP-2010','DD-MON-YYYY'), 101, TO_DATE('20-SEP-2010','DD-MON-YYYY') );
INSERT INTO rental_history(media_id, rental_date, customer_id,  return_date)
VALUES(92, TO_DATE('25-SEP-2010','DD-MON-YYYY'), 101, NULL );



--6. Create an index on the last_name column of the Customers table.
CREATE INDEX ctr_lat_nae_idx
 on customers(last_name);

--7. Create a synonym called TU for the TITLE_UNAVAIL view.
CREATE  SYNONYM tu
FOR title_unavail;


Now each step of script output verification:



  

Number
Elapsed
Statement
Feedback
Rows
1
0
DROP VIEW title_unavail
ORA-00942: table or view does not exist

2
0.01
DROP TABLE rental_history PURGE
ORA-00942: table or view does not exist

3
0
DROP TABLE customers PURGE
ORA-00942: table or view does not exist

4
0.01
DROP TABLE media PURGE
ORA-00942: table or view does not exist

5
0
DROP TABLE star_billings PURGE
ORA-00942: table or view does not exist

6
0.01
DROP TABLE movies PURGE
ORA-00942: table or view does not exist

7
0
DROP TABLE actors PURGE
ORA-00942: table or view does not exist

8
0.01
DROP SEQUENCE customer_id_seq
ORA-02289: sequence does not exist

9
0
DROP SEQUENCE title_id_seq
ORA-02289: sequence does not exist

10
0
DROP SEQUENCE media_id_seq
ORA-02289: sequence does not exist

11
0.01
DROP SEQUENCE actor_id_seq
ORA-02289: sequence does not exist

12
0
DROP SYNONYM  tu
ORA-01434: private synonym to be dropped does not exist

13
0.02
CREATE TABLE  actors    ( actor_id NUMBER(10,0) CONSTRAINT a
Table created.
0
14
0.02
CREATE TABLE  movies    ( title_id NUMBER(10,0) CONSTRAINT m
Table created.
0
15
0.01
CREATE TABLE  star_billings    ( actor_id NUMBER(10,0) CO
Table created.
0
16
0.02
CREATE TABLE  media    ( media_id NUMBER(10,0) CONSTRAINT m
Table created.
0
17
0.02
CREATE TABLE  customers    ( customer_id NUMBER(10,0) CONST
Table created.
0
18
0.01
CREATE TABLE  rental_history    ( media_id NUMBER(10,0) CON
Table created.
0
19
0.01
CREATE OR REPLACE VIEW title_unavail ("movie title", "media
View created.
0
20
0.01
CREATE SEQUENCE customer_id_seq START WITH 101
Sequence created.
0
21
0
CREATE SEQUENCE title_id_seq
Sequence created.
0
22
0.01
CREATE SEQUENCE media_id_seq START WITH 92
Sequence created.
0
23
0
CREATE SEQUENCE actor_id_seq START WITH 1001
Sequence created.
0
24
0.01
INSERT INTO actors(actor_id, stage_name, first_name, last_na
1 row(s) inserted.
1
25
0
INSERT INTO actors(actor_id, stage_name, first_name, last_na
1 row(s) inserted.
1
26
0
INSERT INTO actors(actor_id, stage_name, first_name, last_na
1 row(s) inserted.
1
27
0.01
INSERT INTO actors(actor_id, stage_name, first_name, last_na
1 row(s) inserted.
1
28
0
INSERT INTO movies(title_id, title , description ,  rating ,
1 row(s) inserted.
1
29
0.01
INSERT INTO movies(title_id, title , description ,  rating ,
1 row(s) inserted.
1
30
0
INSERT INTO movies(title_id, title , description ,  rating ,
1 row(s) inserted.
1
31
0
INSERT INTO movies(title_id, title , description ,  rating ,
1 row(s) inserted.
1
32
0
INSERT INTO movies(title_id, title , description ,  rating ,
1 row(s) inserted.
1
33
0
INSERT INTO movies(title_id, title , description ,  rating ,
1 row(s) inserted.
1
34
0
INSERT INTO star_billings(actor_id, title_id , comments) VAL
1 row(s) inserted.
1
35
0.01
INSERT INTO star_billings(actor_id, title_id , comments) VAL
1 row(s) inserted.
1
36
0
INSERT INTO star_billings(actor_id, title_id , comments) VAL
1 row(s) inserted.
1
37
0
INSERT INTO star_billings(actor_id, title_id , comments) VAL
1 row(s) inserted.
1
38
0
INSERT INTO star_billings(actor_id, title_id , comments) VAL
1 row(s) inserted.
1
39
0.01
INSERT INTO star_billings(actor_id, title_id , comments) VAL
1 row(s) inserted.
1
40
0
INSERT INTO media(media_id, format , title_id) VALUES(media_
1 row(s) inserted.
1
41
0
INSERT INTO media(media_id, format , title_id) VALUES(media_
1 row(s) inserted.
1
42
0.01
INSERT INTO media(media_id, format , title_id) VALUES(media_
1 row(s) inserted.
1
43
0
INSERT INTO media(media_id, format , title_id) VALUES(media_
1 row(s) inserted.
1
44
0
INSERT INTO media(media_id, format , title_id) VALUES(media_
1 row(s) inserted.
1
45
0
INSERT INTO media(media_id, format , title_id) VALUES(media_
1 row(s) inserted.
1
46
0.01
INSERT INTO media(media_id, format , title_id) VALUES(media_
1 row(s) inserted.
1
47
0
INSERT INTO media(media_id, format , title_id) VALUES(media_
1 row(s) inserted.
1
48
0
INSERT INTO media(media_id, format , title_id) VALUES(media_
1 row(s) inserted.
1
49
0
INSERT INTO media(media_id, format , title_id) VALUES(media_
1 row(s) inserted.
1
50
0.01
INSERT INTO media(media_id, format , title_id) VALUES(media_
1 row(s) inserted.
1
51
0
INSERT INTO media(media_id, format , title_id) VALUES(media_
1 row(s) inserted.
1
52
0
INSERT INTO customers(customer_id, last_name, first_name,  h
1 row(s) inserted.
1
53
0.01
INSERT INTO customers(customer_id, last_name, first_name,  h
1 row(s) inserted.
1
54
0
INSERT INTO customers(customer_id, last_name, first_name,  h
1 row(s) inserted.
1
55
0
INSERT INTO customers(customer_id, last_name, first_name,  h
1 row(s) inserted.
1
56
0
INSERT INTO customers(customer_id, last_name, first_name,  h
1 row(s) inserted.
1
57
0.01
INSERT INTO customers(customer_id, last_name, first_name,  h
1 row(s) inserted.
1
58
0
INSERT INTO rental_history(media_id, rental_date, customer_i
1 row(s) inserted.
1
59
0
INSERT INTO rental_history(media_id, rental_date, customer_i
1 row(s) inserted.
1
60
0.01
INSERT INTO rental_history(media_id, rental_date, customer_i
1 row(s) inserted.
1
61
0
INSERT INTO rental_history(media_id, rental_date, customer_i
1 row(s) inserted.
1
62
0.01
CREATE INDEX ctr_lat_nae_idx  on customers(last_name)
Index created.
0
63
0
CREATE  SYNONYM tu FOR title_unavail
Synonym created.
0


01 Working with DDL Statements - Output

DESC rental_history;

DESCRIBE customers;

DESCRIBE media;

DESCRIBE star_billings;


DESC movies;

DESC actors;




02_ Creating and Managing Constraints- Output

SELECT chld.table_name "Subject", chldcols.column_name "Subject Column Name", chld.constraint_name "constraint_name in Subject",  chld.constraint_type  "constraint_type in Subject", chld.search_condition  "search_condition in Subject", prnt.table_name "Parent of FK",  prntcols.column_name "Parent's Column Name", prnt.constraint_name "Parent PK"
FROM user_constraints chld LEFT OUTER JOIN user_constraints prnt ON  chld.r_constraint_name = prnt.constraint_name
LEFT OUTER JOIN user_cons_columns chldcols ON chld.constraint_name = chldcols.constraint_name
LEFT OUTER JOIN user_cons_columns prntcols ON prnt.constraint_name = prntcols.constraint_name
WHERE chld.table_name = UPPER('table_name');
ACTORS:

MOVIES:

STAR_BILLINGS:

MEDIA:

CUSTOMERS:

RENTAL_HISTORY:

03_ Creating and Managing Views-Output

(After 5. Add the data to the tables.)

SELECT * FROM title_unavail;



04_ Working with Sequences (Indexes and Synonyms)-Output

Output of  SELECT * FROM user_sequences WHERE sequence_name = UPPER('squence_name');

·         Use a sequence to generate PKs for CUSTOMER_ID in CUSTOMERS table Begin at 101 and increment by 1
CREATE SEQUENCE customer_id_seq
START WITH 101;

·         Use a sequence to generate PKs for TITLE_ID in MOVIES table Begin at 1 and increment by 1
CREATE SEQUENCE title_id_seq;


·         Use a sequence to generate PKs for MEDIA_ID in MEDIA table Begin at 92 and increment by 1
CREATE SEQUENCE media_id_seq
START WITH 92;


·         Use a sequence to generate PKs for ACTOR_ID in ACTOR table Begin at 1001 and increment by 1
CREATE SEQUENCE actor_id_seq
START WITH 1001;





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



SELECT * FROM actors;




SELECT * FROM movies;










SELECT * FROM star_billings;

SELECT * FROM media;



SELECT * FROM customers;




SELECT * FROM rental_history;



6. Create an index on the last_name column of the Customers table. 


SELECT ucm.index_name, ucm.column_name, ucm.column_position, uix.uniqueness
FROM user_indexes uix INNER JOIN user_ind_columns ucm ON uix.index_name = ucm.index_name
WHERE ucm.table_name = 'CUSTOMERS' AND ucm.column_name = 'LAST_NAME' ;




7. Create a synonym called TU for the TITLE_UNAVAIL view.


Note: I didn’t have privileges to create public synonym:
ORA-01031: insufficient privileges
So I created private
SELECT * FROM tu;


SELECT * FROM user_synonyms WHERE table_NAME = UPPER('title_unavail');