Thursday, February 2, 2017

Does Anti-virus software slow down your machine ?


If you are about to disable your Anti-virus and other security related plugins/software on your machine because it slows down your machine, this post is for you.

github.com/beefproject/beef is the The Browser Exploitation Framework(BeEF) available with default installation of Kali Linux. A black hat hacker could refer it's hook.js JavaScript file in any page, which if opened on victim's browser, hacker has the full control on victim's browser to execute commands beyond imagination e.g. getting all the browser cookies, extensions information, control web cam, pop-ups to enter passwords while browsing legitimate websites and so on. For complete list refer to github.com/beefproject/beef/tree/master/modules.

How BeEF tool works:
 It keeps on hitting BeEF server from victim's client browser to get updated version of hook.js (interval defined by Config yaml at server) and execute it, when server side Utility of BeEF server attacks, say attacker run command give me webcam, a different version of hook.js is fetched to victim in next periodic call from browser to BeEF server:  github.com/beefproject/beef/blob/master/modules/browser/webcam/command.js in this example. And victim client post results to attacker as per new hook.js given to it.

 You may verify such actions on client machine using F12 ( Developer Tool) > Network tab in most of the browsers like chrome,IE, Mozilla etc or using fiddler.

How to defeat this tool:

1. If this hook.js periodic hit is blocked on victim's browser and notify victim in popup, its a temporary fix. The best place for this stuff to do could have been a utility running on client machine/browser code itself/extension to browser.

2. You may use unhook code in your browser extension very similar to github.com/beefproject/beef/blob/91cc7ed873f26a4d633f6306b34aa6af06932d49/modules/browser/unhook/command.js ( The tool use the code mentioned to stop watching (unhook) the victim.
3. Another method could be blocking the Attacker's domain altogether as done by
chrome.google.com/webstore/detail/vegan/longcaclchhmdpgcdjicmaghmpbdidlj (Vegan Chrome extension)

4. "utility running on client machine" called anti-virus have updated definition to let the system aware of these type of attacks. For example
Windows default anti-virus is aware of many attack vectors BeEF tool uses like Exploit: JS/Aimesu.A, Trojan: Win32/Spursint.F!cl , Exploit: JS/ShellCode.gen

Bottom line is you must not disable your Anti-virus software, just because it slows down your machine. Above all, you must not open malicious websites which may have been using hooks from plenty of such exploitation tools. Might be, the tool they are using is custom made and no anti-virus software is yet aware of attack vectors and how it works!! Typically, websites which offer you pirated content cost you much more than you could think of.

When it comes to government organizations and financial institutes, there is a reason they block everything else than trusted web addresses and domains. Even the most sophisticated technologies may be vulnerable to Cross-Site Scripting (XSS) attacks and attacker could inject just hooks in supposedly secured websites.

Wednesday, December 14, 2016

Get your location on google maps and find distance from a hosted IP/domain name.

Purpose

A lot of times, I am connected to internet in public place and don't know from where internet signals are coming, I may visit Get Your Location in the main navigation and track down internet signal source. This page in the application takes user's location from browser and gives driving direction to the location, which is identified by his IP address used to access internet.

Sometimes, I don't want to open a website from xxxx country, country name of hosting itself limelight authenticity of a website. I could visit Search Domain or IP Location in the main navigation to track down hosting location of an IP/domainName. This page in the application takes user's location from browser (source) and gives driving direction to the location, which is identified by IP address given as input / IP address of the domainName (Destination)

Code

myservice.js: This contains the main angular custom services built driving controllers in visitorlocation.js , searchip.js and contactus.js 
Custom Services in the js file myservice.js -->
geoipService- Talks to external geoIP service to get IP and details like latitude/longitude.
googleService- Talks to external Google map API's , reference for which is added in index.html.
myGeolocation-is used to get latitude and longitude of user's browser.
See comments section (click here) in code for further details.
index.html: This is the single page, which loads the entire html based on controller requested. This app may be very well termed as a single page app containing only one page - index.html which loads required components. Default load for index.html is introduction.html.
introduction.html: [current html you are viewing] This view simply welcomes a user. It also list details of the project and its components. There is no associated controller for this view.
visitorlocation.html: This view is associated with controller in visitorlocation.js. Actions in this controller achieved with help of services-
See comments section (click here) in code for further details.
searchip.html: This view is associated with controller in searchip.js. 
User enter a domainName or IP address. This value is processed in the function callTheMagic further on click of search. 
Actions in this function achieved with help of services-
See comments section (click here) in code for further details.
contactus.html: This view is associated with controller in contactus.js. It gives driving directions to Fort Hays State University.
myapp.js: The routes are defined here.
Weakness
Loading wheels would have been basic requirement not catered here.

Languages / Libraries
JQuery (2.1.4), AngularJS (1.4.2) with Angular-route (1.4.2) for single page app backbone.
freegeoip.net for location by IP.
maps.googleapis.com to get user's current device/browser location , and detect/display driving directions.
Bootstrap (3.3.5) with sb-admin-2 theme, font-awesome, metisMenu for desktop/mobile friendly UI.
Demo
Test with saucelabs on Linux + Opera 12: https://youtu.be/5udO_3X9cR0
Test with saucelabs on iOS 10.0 + iPhone 6s: https://youtu.be/VqfjTC6AjJw

Test with saucelabs on iOS 9.3 + iPhone 4s : https://youtu.be/udfi3afwPkY

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