SQL Assignment 1st Phase (Chapter
1-4)
///////////////////CREATING
CUSTOMER_DIM TABLE////////////////////////////////////
CREATE TABLE customer_dim
( customer_sk INT NOT NULL PRIMARY KEY,
customer_number NUMBER(3)
customer_name CHAR (50),
customer_street_address CHAR (50),
customer_zip_code NUMBER (5),
customer_city CHAR (30),
customer_state CHAR (2),
effective_date DATE,
expiry_date DATE );
///////////////////CREATING PRODUCT_DIM TABLE//////////////////////////////////////
CREATE TABLE product_dim
( product_sk INT NOT NULL PRIMARY KEY,
product_code NUMBER,
product_name CHAR (30),
product_category CHAR (30),
effective_date DATE,
expiry_date DATE );
//////////////////////CREATING ORDER_DIM TABLE/////////////////////////////////////
CREATE TABLE order_dim
( order_sk INT NOT NULL PRIMARY KEY,
order_number INT,
effective_date DATE,
expiry_date DATE );
////////////////////////////CREATING DATE_DIM TABLE////////////////////////////////
CREATE TABLE date_dim
( date_sk INT NOT NULL PRIMARY KEY,
Booking_date DATE,
month_name CHAR (9),
month NUMBER (2),
quarter NUMBER (1),
year NUMBER (4),
effective_date DATE,
expiry_date DATE );
///////////////////////////CREATING SALES_ORDER_FACT TABLE/////////////////////////
CREATE TABLE sales_order_fact
( order_sk NUMBER,
customer_sk NUMBER,
product_sk NUMBER,
order_date_sk NUMBER,
order_amount DECIMAL (10, 2));
/////////////////////////////CUSTOMER_DIM///////////////////////////////////////
DROP SEQUENCE auto_incr;
CREATE SEQUENCE auto_incr
START WITH 1
INCREMENT BY 1
CACHE 100;
INSERT INTO customer_dim VALUES(auto_incr.nextval, 1,'Big Customers','7500 Louise Dr.','17050','Mechanicsburg','PA',CURRENT_DATE,to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO customer_dim VALUES(auto_incr.nextval, 2,'Small Stores','2500 Woodland St.','17055','Pittsburgh','PA',CURRENT_DATE,to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO customer_dim VALUES(auto_incr.nextval, 3,'Medium Retailers','1111 Ritter Rd.','17055','Pittsburgh','PA',CURRENT_DATE,to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO customer_dim VALUES(auto_incr.nextval, 4,'Good Companies','9500 Scott St.','17050','Mechanicsburg','PA',CURRENT_DATE,to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO customer_dim VALUES(auto_incr.nextval, 5,'Wonderful Shops','3333 Rossmoyne Rd.','17050','Mechanicsburg','PA',CURRENT_DATE,to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO customer_dim VALUES(auto_incr.nextval, 6, 'Loyal Clients', '7070 Ritter Rd.', '17055','Pittsburgh','PA',CURRENT_DATE,to_date('9999-12-31','YYYY-MM-DD'));
//////////////////////////////////CHAPTER:2 CUSTOMER_DIM////////////////////////////////////
UPDATE customer_dim SET customer_name='Really Large Customers' WHERE customer_number=1;
INSERT INTO customer_dim VALUES(auto_incr.nextval,7,'Distinguished Partners','9999 Scott St.','17050','Mechanicsburg','PA',CURRENT_DATE+1,to_date('9999-12-31','YYYY-MM-DD'));
/////////////////////////////////////PRODUCT_DIM////////////////////////////////////
DROP SEQUENCE pro_auto;
CREATE SEQUENCE pro_auto
START WITH 1
INCREMENT BY 1
CACHE 10;
INSERT INTO product_dim VALUES(pro_auto.nextval,1,'Hard Disk','Storage',current_date+2,TO_DATE('9999-12 31','YYYY-MM-DD'));
INSERT INTO product_dim VALUES(pro_auto.nextval,2,'Floppy Drive','Storage',current_date+2,TO_DATE('9999-12 31','YYYY-MM-DD'));
INSERT INTO product_dim VALUES(pro_auto.nextval,3,'LCD Panel','Monitor',current_date+2,TO_DATE('9999-12 31','YYYY-MM-DD'));
INSERT INTO product_dim VALUES(pro_auto.nextval,1,'Hard Disk','Storage',current_date+2,current_date+3);
/////////////////////////////////////Chapter 3////////////////////////////////////////////////////
///////////////////////////////////////ORDER_DIM///////////////////////////////////////////////////
DROP SEQUENCE order_auto;
CREATE SEQUENCE order_auto
START WITH 1
INCREMENT BY 1
CACHE 10;
INSERT INTO order_dim VALUES (order_auto.nextval, 1, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 2, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 3, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 4, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 5, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 6, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 7, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 8, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 9, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 10,CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
///////////////////////////////////////DATE_DIM/////////////////////////////////////////////////////////
DROP SEQUENCE date_auto;
CREATE SEQUENCE date_auto
START WITH 1
INCREMENT BY 1
CACHE 10;
INSERT INTO date_dim VALUES (date_auto.nextval, to_date('2007-02-05','YYYY-MM-DD'), 'February', 02, 01, 2007, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
/////////////////////////////////////////////////SALES_ORDER_FACT///////////////////////////////////////////////////
INSERT INTO sales_order_fact VALUES (1, 1, 2, 1, 1000);
INSERT INTO sales_order_fact VALUES (2, 2, 3, 1, 1000);
INSERT INTO sales_order_fact VALUES (3, 3, 4, 1, 4000);
INSERT INTO sales_order_fact VALUES (4, 4, 2, 1, 4000);
INSERT INTO sales_order_fact VALUES (5, 5, 3, 1, 6000);
INSERT INTO sales_order_fact VALUES (6, 1, 4, 1, 6000);
INSERT INTO sales_order_fact VALUES (7, 2, 2, 1, 8000);
INSERT INTO sales_order_fact VALUES (8, 3, 3, 1, 8000);
INSERT INTO sales_order_fact VALUES (9, 4, 4, 1, 10000);
INSERT INTO sales_order_fact VALUES (10, 5, 2, 1, 10000);
///////////////////////////////////////////////////////////////////////////
CHAPTER 3
///////////////////////////////////////////////////////////////////////////
SQL> select * from sales_order_fact;
ORDER_SK CUSTOMER_SK PRODUCT_SK ORDER_DATE_SK ORDER_AMOUNT
---------- ----------- ---------- ------------- ------------
1 1 2 1 1000
2 2 3 1 1000
3 3 4 1 4000
4 4 2 1 4000
5 5 3 1 6000
6 1 4 1 6000
7 2 2 1 8000
8 3 3 1 8000
9 4 4 1 10000
10 5 2 1 10000
11 1 2 2 20000
12 2 3 2 25000
13 3 4 2 30000
14 4 2 2 35000
15 5 3 2 40000
16 1 4 2 45000
16 rows selected.
///////////////////////////////////////////////////////////////////////////
SQL> select sum(order_amount) total_amount from sales_order_fact;
TOTAL_AMOUNT
------------
253000
///////////////////////////////////////////////////////////////////////////
SQL> select avg(order_amount) average_amount from sales_order_fact;
AVERAGE_AMOUNT
--------------
15812.5
///////////////////////////////////////////////////////////////////////////
SQL> SELECT * FROM SALES_ORDER_FACT;
ORDER_SK CUSTOMER_SK PRODUCT_SK ORDER_DATE_SK ORDER_AMOUNT
---------- ----------- ---------- ------------- ------------
1 1 2 1 1000
2 2 3 1 1000
3 3 4 1 4000
4 4 2 1 4000
5 5 3 1 6000
6 1 4 1 6000
7 2 2 1 8000
8 3 3 1 8000
9 4 4 1 10000
10 5 2 1 10000
11 1 2 2 20000
12 2 3 2 25000
13 3 4 2 30000
14 4 2 2 35000
15 5 3 2 40000
16 1 4 2 45000
16 rows selected.
SQL> SELECT * FROM CUSTOMER_DIM;
CUSTOMER_SK CUSTOMER_NUMBER CUSTOMER_NAME CUSTOMER_STREET_ADDRESS CUSTOMER_ZIP_CODE
----------- --------------- -------------------------------------------------- ---------------------
CUSTOMER_CITY CU EFFECTIVE EXPIRY_DA
------------------------------ -- --------- ---------
1 1 Really Large Customers 7500 Louise Dr. 17050
Mechanicsburg PA 30-AUG-13 31-DEC-99
2 2 Small Stores 2500 Woodland St. 17055
Pittsburgh PA 30-AUG-13 31-DEC-99
3 3 Medium Retailers 1111 Ritter Rd. 17055
Pittsburgh PA 30-AUG-13 31-DEC-99
4 4 Good Companies 9500 Scott St. 17050
Mechanicsburg PA 30-AUG-13 31-DEC-99
5 5 Wonderful Shops 3333 Rossmoyne Rd. 17050
Mechanicsburg PA 30-AUG-13 31-DEC-99
6 6 Loyal Clients 7070 Ritter Rd. 17055
Pittsburgh PA 30-AUG-13 31-DEC-99
7 7 Distinguished Partners 9999 Scott St. 17050
Mechanicsburg PA 31-AUG-13 31-DEC-99
7 rows selected.
SQL> SELECT CUSTOMER_NUMBER,
2 SUM(ORDER_AMOUNT) TOTAL_SUM_ORDER_AMOUNT FROM
3 SALES_ORDER_FACT A,CUSTOMER_DIM B WHERE
4 A.CUSTOMER_SK=B.CUSTOMER_SK
5 GROUP BY CUSTOMER_NUMBER;
CUSTOMER_NUMBER TOTAL_SUM_ORDER_AMOUNT
--------------- ----------------------
1 72000
2 34000
4 49000
5 56000
3 42000
////////////////////////////////////////////////////////////////////////////////////////////////
SQL> SELECT CUSTOMER_NUMBER,
2 SUM(ORDER_AMOUNT) TOTAL_SUM_ORDER_AMOUNT FROM
3 SALES_ORDER_FACT A,CUSTOMER_DIM B WHERE
4 A.CUSTOMER_SK=B.CUSTOMER_SK AND CUSTOMER_NUMBER=1
5 GROUP BY CUSTOMER_NUMBER;
CUSTOMER_NUMBER TOTAL_SUM_ORDER_AMOUNT
--------------- ----------------------
1 72000
//////////////////////////////////////////////////////////////////////////////////////////////////
SQL> SELECT * FROM PRODUCT_DIM;
PRODUCT_SK PRODUCT_CODE PRODUCT_NAME PRODUCT_CATEGORY EFFECTIVE EXPIRY_DA
---------- ------------ ------------------------------ ------------------------------ --------- ----
1 1 Hard Disk Storage 01-SEP-13 31-DEC-99
2 2 Floppy Drive Storage 01-SEP-13 31-DEC-99
3 3 LCD Panel Monitor 01-SEP-13 31-DEC-99
4 1 Hard Disk Storage 01-SEP-13 02-SEP-13
SQL> SELECT * FROM SALES_ORDER_FACT;
ORDER_SK CUSTOMER_SK PRODUCT_SK ORDER_DATE_SK ORDER_AMOUNT
---------- ----------- ---------- ------------- ------------
1 1 2 1 1000
2 2 3 1 1000
3 3 4 1 4000
4 4 2 1 4000
5 5 3 1 6000
6 1 4 1 6000
7 2 2 1 8000
8 3 3 1 8000
9 4 4 1 10000
10 5 2 1 10000
11 1 2 2 20000
12 2 3 2 25000
13 3 4 2 30000
14 4 2 2 35000
15 5 3 2 40000
16 1 4 2 45000
16 rows selected.
SQL> SELECT PRODUCT_NAME,
2 SUM(ORDER_AMOUNT) TOTAL_AMOUNT_PRODUCT FROM
3 SALES_ORDER_FACT A, PRODUCT_DIM B WHERE
4 A.PRODUCT_SK=B.PRODUCT_SK
5 GROUP BY PRODUCT_NAME;
PRODUCT_NAME TOTAL_AMOUNT_PRODUCT
------------------------------ --------------------
Floppy Drive 78000
Hard Disk 95000
LCD Panel 80000
/////////////////////////////////////////////////////////////////////////////////
SQL> SELECT * FROM SALES_ORDER_FACT;
ORDER_SK CUSTOMER_SK PRODUCT_SK ORDER_DATE_SK ORDER_AMOUNT
---------- ----------- ---------- ------------- ------------
1 1 2 1 1000
2 2 3 1 1000
3 3 4 1 4000
4 4 2 1 4000
5 5 3 1 6000
6 1 4 1 6000
7 2 2 1 8000
8 3 3 1 8000
9 4 4 1 10000
10 5 2 1 10000
11 1 2 2 20000
12 2 3 2 25000
13 3 4 2 30000
14 4 2 2 35000
15 5 3 2 40000
16 1 4 2 45000
16 rows selected.
SQL> SELECT TABLE_NAME FROM USER_TABLES;
TABLE_NAME
------------------------------
CUSTOMER_DIM
PRODUCT_DIM
ORDER_DIM
DATE_DIM
SALES_ORDER_FACT
CUSTOMER_DIM_COPY
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
COUNTRIES
EMPLOYEES
JOB_HISTORY
13 rows selected.
SQL> SELECT * FROM DATE_DIM
2 ;
DATE_SK BOOKING_D MONTH_NAM MONTH QUARTER YEAR EFFECTIVE EXPIRY_DA
---------- --------- --------- ---------- ---------- ---------- --------- ---------
1 05-FEB-07 February 2 1 2007 30-AUG-13 31-DEC-99
2 06-FEB-07 February 2 1 2007 30-AUG-13 31-DEC-99
SQL> SELECT CUSTOMER_NUMBER,PRODUCT_CODE,
2 SUM(ORDER_AMOUNT) SUM_OF_ORDER_AMOUNT
3 FROM SALES_ORDER_FACT A, CUSTOMER_DIM B, PRODUCT_DIM C WHERE
4 A.CUSTOMER_SK=B.CUSTOMER_SK AND A.PRODUCT_SK=C.PRODUCT_SK
5 GROUP BY CUSTOMER_NUMBER,PRODUCT_CODE;
CUSTOMER_NUMBER PRODUCT_CODE SUM_OF_ORDER_AMOUNT
--------------- ------------ -------------------
4 1 10000
5 2 10000
2 3 26000
1 2 21000
3 1 34000
4 2 39000
1 1 51000
2 2 8000
3 3 8000
5 3 46000
10 rows selected.
///////////////////////////////////////////////////////////////////////////////////////////////
SQL> SELECT BOOKING_DATE,
2 SUM (ORDER_AMOUNT),
3 COUNT (*)
4 FROM SALES_ORDER_FACT A,
5 DATE_DIM B
6 WHERE
7 A.ORDER_DATE_SK=B.DATE_SK
8 GROUP BY BOOKING_DATE
9 ORDER BY BOOKING_DATE;
BOOKING_D SUM(ORDER_AMOUNT) COUNT(*)
--------- ----------------- ----------
05-FEB-07 58000 10
06-FEB-07 195000 6
///////////////////////////////////////////////////////////////////////////////////////////////
SQL> SELECT YEAR, PRODUCT_NAME, CUSTOMER_CITY,
2 SUM (ORDER_AMOUNT),
3 COUNT (*)
4 FROM
5 SALES_ORDER_FACT A,
6 DATE_DIM B,
7 PRODUCT_DIM C,
8 CUSTOMER_DIM D
9 WHERE
10 A.ORDER_DATE_SK=B.DATE_SK
11 AND A.PRODUCT_SK=C.PRODUCT_SK
12 AND A.CUSTOMER_SK=D.CUSTOMER_SK
13 GROUP BY YEAR,PRODUCT_NAME, CUSTOMER_CITY
14 ORDER BY YEAR,PRODUCT_NAME, CUSTOMER_CITY;
YEAR PRODUCT_NAME CUSTOMER_CITY
---------- ------------------------------ ------------------------------
SUM(ORDER_AMOUNT) COUNT(*)
----------------- ----------
2007 Floppy Drive Mechanicsburg
70000 5
2007 Floppy Drive Pittsburgh
8000 1
2007 Hard Disk Mechanicsburg
61000 3
YEAR PRODUCT_NAME CUSTOMER_CITY
---------- ------------------------------ ------------------------------
SUM(ORDER_AMOUNT) COUNT(*)
----------------- ----------
2007 Hard Disk Pittsburgh
34000 2
2007 LCD Panel Mechanicsburg
46000 2
2007 LCD Panel Pittsburgh
34000 3
6 rows selected.
////////////////////////////////////////////////////////////////////////////////////////////////
SQL> SELECT
2 PRODUCT_NAME
3 ,MONTH_NAME
4 ,YEAR
5 ,SUM (ORDER_AMOUNT)
6 ,COUNT(*)
7 FROM
8 SALES_ORDER_FACT A
9 ,PRODUCT_DIM B
10 ,DATE_DIM C
11 WHERE
12 A.PRODUCT_SK = B.PRODUCT_SK
13 AND A.ORDER_DATE_SK = C.DATE_SK
14 GROUP BY
15 PRODUCT_NAME
16 ,PRODUCT_CATEGORY
17 ,MONTH_NAME
18 ,YEAR
19 HAVING PRODUCT_CATEGORY = 'STORAGE'
20 ORDER BY
21 YEAR
22 ,MONTH_NAME;
PRODUCT_NAME MONTH_NAM YEAR SUM(ORDER_AMOUNT) COUNT(*)
------------------------------ --------- ---------- ----------------- ----------
Floppy Drive February 2007 78000 6
Hard Disk February 2007 95000 5
/////////////////////////////////////////////////////////////////////////////////////////////////
SQL> SELECT
2 CUSTOMER_CITY,
3 QUARTER,
4 YEAR,
5 SUM(ORDER_AMOUNT),
6 COUNT(ORDER_SK)
7 FROM
8 SALES_ORDER_FACT A,
9 CUSTOMER_DIM B
10 ,DATE_DIM C
11 WHERE
12 A.CUSTOMER_SK=B.CUSTOMER_SK
13 AND A.ORDER_DATE_SK=C.DATE_SK
14 GROUP BY
15 CUSTOMER_CITY,
16 QUARTER,
17 YEAR
18 HAVING CUSTOMER_CITY = 'Mechanicsburg'
19 ORDER BY
20 YEAR,
21 QUARTER;
CUSTOMER_CITY QUARTER YEAR SUM(ORDER_AMOUNT)
------------------------------ ---------- ---------- -----------------
COUNT(ORDER_SK)
---------------
Mechanicsburg 1 2007 177000
10
////////////////////////////////////////////////////////////////////////////////////////////////////
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 MONTH_NAME,YEAR,
3 PRODUCT_NAME,
4 SUM(ORDER_AMOUNT)
5 ,COUNT(*)
6 FROM
7 SALES_ORDER_FACT A,
8 PRODUCT_DIM B,
9 DATE_DIM C
10 WHERE
11 A.PRODUCT_SK=B.PRODUCT_SK
12 AND A.ORDER_DATE_SK=C.DATE_SK
13 GROUP BY
14 MONTH_NAME,
15 YEAR, PRODUCT_NAME
16 HAVING SUM (ORDER_AMOUNT) >= 75000
17 ORDER BY
18 MONTH_NAME,
19 YEAR,
20* PRODUCT_NAME
21 ;
MONTH_NAM YEAR PRODUCT_NAME SUM(ORDER_AMOUNT) COUNT(*)
--------- ---------- ------------------------------ ----------------- ----------
February 2007 Floppy Drive 78000 6
February 2007 Hard Disk 95000 5
February 2007 LCD Panel 80000 5
////////////////////////////////////////////////////////////////////////////////////////////////////////
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 CUSTOMER_NUMBER,
3 YEAR, COUNT (*)
4 FROM
5 SALES_ORDER_FACT A,
6 CUSTOMER_DIM B,
7 DATE_DIM C
8 WHERE
9 A.CUSTOMER_SK=B.CUSTOMER_SK
10 AND A.ORDER_DATE_SK=C.DATE_SK
11 GROUP BY
12 CUSTOMER_NUMBER,
13 YEAR
14 HAVING COUNT (*)>1
15* AND (12-TO_CHAR(MAX(BOOKING_DATE),'MM'))<12
16 ;
CUSTOMER_NUMBER YEAR COUNT(*)
--------------- ---------- ----------
2 2007 3
3 2007 3
1 2007 4
4 2007 3
5 2007 3
/////////////////////////////////////////////////////////////////////////////////////////////////////
CREATE TABLE customer_dim
( customer_sk INT NOT NULL PRIMARY KEY,
customer_number NUMBER(3)
customer_name CHAR (50),
customer_street_address CHAR (50),
customer_zip_code NUMBER (5),
customer_city CHAR (30),
customer_state CHAR (2),
effective_date DATE,
expiry_date DATE );
///////////////////CREATING PRODUCT_DIM TABLE//////////////////////////////////////
CREATE TABLE product_dim
( product_sk INT NOT NULL PRIMARY KEY,
product_code NUMBER,
product_name CHAR (30),
product_category CHAR (30),
effective_date DATE,
expiry_date DATE );
//////////////////////CREATING ORDER_DIM TABLE/////////////////////////////////////
CREATE TABLE order_dim
( order_sk INT NOT NULL PRIMARY KEY,
order_number INT,
effective_date DATE,
expiry_date DATE );
////////////////////////////CREATING DATE_DIM TABLE////////////////////////////////
CREATE TABLE date_dim
( date_sk INT NOT NULL PRIMARY KEY,
Booking_date DATE,
month_name CHAR (9),
month NUMBER (2),
quarter NUMBER (1),
year NUMBER (4),
effective_date DATE,
expiry_date DATE );
///////////////////////////CREATING SALES_ORDER_FACT TABLE/////////////////////////
CREATE TABLE sales_order_fact
( order_sk NUMBER,
customer_sk NUMBER,
product_sk NUMBER,
order_date_sk NUMBER,
order_amount DECIMAL (10, 2));
/////////////////////////////CUSTOMER_DIM///////////////////////////////////////
DROP SEQUENCE auto_incr;
CREATE SEQUENCE auto_incr
START WITH 1
INCREMENT BY 1
CACHE 100;
INSERT INTO customer_dim VALUES(auto_incr.nextval, 1,'Big Customers','7500 Louise Dr.','17050','Mechanicsburg','PA',CURRENT_DATE,to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO customer_dim VALUES(auto_incr.nextval, 2,'Small Stores','2500 Woodland St.','17055','Pittsburgh','PA',CURRENT_DATE,to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO customer_dim VALUES(auto_incr.nextval, 3,'Medium Retailers','1111 Ritter Rd.','17055','Pittsburgh','PA',CURRENT_DATE,to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO customer_dim VALUES(auto_incr.nextval, 4,'Good Companies','9500 Scott St.','17050','Mechanicsburg','PA',CURRENT_DATE,to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO customer_dim VALUES(auto_incr.nextval, 5,'Wonderful Shops','3333 Rossmoyne Rd.','17050','Mechanicsburg','PA',CURRENT_DATE,to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO customer_dim VALUES(auto_incr.nextval, 6, 'Loyal Clients', '7070 Ritter Rd.', '17055','Pittsburgh','PA',CURRENT_DATE,to_date('9999-12-31','YYYY-MM-DD'));
//////////////////////////////////CHAPTER:2 CUSTOMER_DIM////////////////////////////////////
UPDATE customer_dim SET customer_name='Really Large Customers' WHERE customer_number=1;
INSERT INTO customer_dim VALUES(auto_incr.nextval,7,'Distinguished Partners','9999 Scott St.','17050','Mechanicsburg','PA',CURRENT_DATE+1,to_date('9999-12-31','YYYY-MM-DD'));
/////////////////////////////////////PRODUCT_DIM////////////////////////////////////
DROP SEQUENCE pro_auto;
CREATE SEQUENCE pro_auto
START WITH 1
INCREMENT BY 1
CACHE 10;
INSERT INTO product_dim VALUES(pro_auto.nextval,1,'Hard Disk','Storage',current_date+2,TO_DATE('9999-12 31','YYYY-MM-DD'));
INSERT INTO product_dim VALUES(pro_auto.nextval,2,'Floppy Drive','Storage',current_date+2,TO_DATE('9999-12 31','YYYY-MM-DD'));
INSERT INTO product_dim VALUES(pro_auto.nextval,3,'LCD Panel','Monitor',current_date+2,TO_DATE('9999-12 31','YYYY-MM-DD'));
INSERT INTO product_dim VALUES(pro_auto.nextval,1,'Hard Disk','Storage',current_date+2,current_date+3);
/////////////////////////////////////Chapter 3////////////////////////////////////////////////////
///////////////////////////////////////ORDER_DIM///////////////////////////////////////////////////
DROP SEQUENCE order_auto;
CREATE SEQUENCE order_auto
START WITH 1
INCREMENT BY 1
CACHE 10;
INSERT INTO order_dim VALUES (order_auto.nextval, 1, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 2, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 3, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 4, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 5, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 6, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 7, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 8, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 9, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
INSERT INTO order_dim VALUES (order_auto.nextval, 10,CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
///////////////////////////////////////DATE_DIM/////////////////////////////////////////////////////////
DROP SEQUENCE date_auto;
CREATE SEQUENCE date_auto
START WITH 1
INCREMENT BY 1
CACHE 10;
INSERT INTO date_dim VALUES (date_auto.nextval, to_date('2007-02-05','YYYY-MM-DD'), 'February', 02, 01, 2007, CURRENT_DATE, to_date('9999-12-31','YYYY-MM-DD'));
/////////////////////////////////////////////////SALES_ORDER_FACT///////////////////////////////////////////////////
INSERT INTO sales_order_fact VALUES (1, 1, 2, 1, 1000);
INSERT INTO sales_order_fact VALUES (2, 2, 3, 1, 1000);
INSERT INTO sales_order_fact VALUES (3, 3, 4, 1, 4000);
INSERT INTO sales_order_fact VALUES (4, 4, 2, 1, 4000);
INSERT INTO sales_order_fact VALUES (5, 5, 3, 1, 6000);
INSERT INTO sales_order_fact VALUES (6, 1, 4, 1, 6000);
INSERT INTO sales_order_fact VALUES (7, 2, 2, 1, 8000);
INSERT INTO sales_order_fact VALUES (8, 3, 3, 1, 8000);
INSERT INTO sales_order_fact VALUES (9, 4, 4, 1, 10000);
INSERT INTO sales_order_fact VALUES (10, 5, 2, 1, 10000);
///////////////////////////////////////////////////////////////////////////
CHAPTER 3
///////////////////////////////////////////////////////////////////////////
SQL> select * from sales_order_fact;
ORDER_SK CUSTOMER_SK PRODUCT_SK ORDER_DATE_SK ORDER_AMOUNT
---------- ----------- ---------- ------------- ------------
1 1 2 1 1000
2 2 3 1 1000
3 3 4 1 4000
4 4 2 1 4000
5 5 3 1 6000
6 1 4 1 6000
7 2 2 1 8000
8 3 3 1 8000
9 4 4 1 10000
10 5 2 1 10000
11 1 2 2 20000
12 2 3 2 25000
13 3 4 2 30000
14 4 2 2 35000
15 5 3 2 40000
16 1 4 2 45000
16 rows selected.
///////////////////////////////////////////////////////////////////////////
SQL> select sum(order_amount) total_amount from sales_order_fact;
TOTAL_AMOUNT
------------
253000
///////////////////////////////////////////////////////////////////////////
SQL> select avg(order_amount) average_amount from sales_order_fact;
AVERAGE_AMOUNT
--------------
15812.5
///////////////////////////////////////////////////////////////////////////
SQL> SELECT * FROM SALES_ORDER_FACT;
ORDER_SK CUSTOMER_SK PRODUCT_SK ORDER_DATE_SK ORDER_AMOUNT
---------- ----------- ---------- ------------- ------------
1 1 2 1 1000
2 2 3 1 1000
3 3 4 1 4000
4 4 2 1 4000
5 5 3 1 6000
6 1 4 1 6000
7 2 2 1 8000
8 3 3 1 8000
9 4 4 1 10000
10 5 2 1 10000
11 1 2 2 20000
12 2 3 2 25000
13 3 4 2 30000
14 4 2 2 35000
15 5 3 2 40000
16 1 4 2 45000
16 rows selected.
SQL> SELECT * FROM CUSTOMER_DIM;
CUSTOMER_SK CUSTOMER_NUMBER CUSTOMER_NAME CUSTOMER_STREET_ADDRESS CUSTOMER_ZIP_CODE
----------- --------------- -------------------------------------------------- ---------------------
CUSTOMER_CITY CU EFFECTIVE EXPIRY_DA
------------------------------ -- --------- ---------
1 1 Really Large Customers 7500 Louise Dr. 17050
Mechanicsburg PA 30-AUG-13 31-DEC-99
2 2 Small Stores 2500 Woodland St. 17055
Pittsburgh PA 30-AUG-13 31-DEC-99
3 3 Medium Retailers 1111 Ritter Rd. 17055
Pittsburgh PA 30-AUG-13 31-DEC-99
4 4 Good Companies 9500 Scott St. 17050
Mechanicsburg PA 30-AUG-13 31-DEC-99
5 5 Wonderful Shops 3333 Rossmoyne Rd. 17050
Mechanicsburg PA 30-AUG-13 31-DEC-99
6 6 Loyal Clients 7070 Ritter Rd. 17055
Pittsburgh PA 30-AUG-13 31-DEC-99
7 7 Distinguished Partners 9999 Scott St. 17050
Mechanicsburg PA 31-AUG-13 31-DEC-99
7 rows selected.
SQL> SELECT CUSTOMER_NUMBER,
2 SUM(ORDER_AMOUNT) TOTAL_SUM_ORDER_AMOUNT FROM
3 SALES_ORDER_FACT A,CUSTOMER_DIM B WHERE
4 A.CUSTOMER_SK=B.CUSTOMER_SK
5 GROUP BY CUSTOMER_NUMBER;
CUSTOMER_NUMBER TOTAL_SUM_ORDER_AMOUNT
--------------- ----------------------
1 72000
2 34000
4 49000
5 56000
3 42000
////////////////////////////////////////////////////////////////////////////////////////////////
SQL> SELECT CUSTOMER_NUMBER,
2 SUM(ORDER_AMOUNT) TOTAL_SUM_ORDER_AMOUNT FROM
3 SALES_ORDER_FACT A,CUSTOMER_DIM B WHERE
4 A.CUSTOMER_SK=B.CUSTOMER_SK AND CUSTOMER_NUMBER=1
5 GROUP BY CUSTOMER_NUMBER;
CUSTOMER_NUMBER TOTAL_SUM_ORDER_AMOUNT
--------------- ----------------------
1 72000
//////////////////////////////////////////////////////////////////////////////////////////////////
SQL> SELECT * FROM PRODUCT_DIM;
PRODUCT_SK PRODUCT_CODE PRODUCT_NAME PRODUCT_CATEGORY EFFECTIVE EXPIRY_DA
---------- ------------ ------------------------------ ------------------------------ --------- ----
1 1 Hard Disk Storage 01-SEP-13 31-DEC-99
2 2 Floppy Drive Storage 01-SEP-13 31-DEC-99
3 3 LCD Panel Monitor 01-SEP-13 31-DEC-99
4 1 Hard Disk Storage 01-SEP-13 02-SEP-13
SQL> SELECT * FROM SALES_ORDER_FACT;
ORDER_SK CUSTOMER_SK PRODUCT_SK ORDER_DATE_SK ORDER_AMOUNT
---------- ----------- ---------- ------------- ------------
1 1 2 1 1000
2 2 3 1 1000
3 3 4 1 4000
4 4 2 1 4000
5 5 3 1 6000
6 1 4 1 6000
7 2 2 1 8000
8 3 3 1 8000
9 4 4 1 10000
10 5 2 1 10000
11 1 2 2 20000
12 2 3 2 25000
13 3 4 2 30000
14 4 2 2 35000
15 5 3 2 40000
16 1 4 2 45000
16 rows selected.
SQL> SELECT PRODUCT_NAME,
2 SUM(ORDER_AMOUNT) TOTAL_AMOUNT_PRODUCT FROM
3 SALES_ORDER_FACT A, PRODUCT_DIM B WHERE
4 A.PRODUCT_SK=B.PRODUCT_SK
5 GROUP BY PRODUCT_NAME;
PRODUCT_NAME TOTAL_AMOUNT_PRODUCT
------------------------------ --------------------
Floppy Drive 78000
Hard Disk 95000
LCD Panel 80000
/////////////////////////////////////////////////////////////////////////////////
SQL> SELECT * FROM SALES_ORDER_FACT;
ORDER_SK CUSTOMER_SK PRODUCT_SK ORDER_DATE_SK ORDER_AMOUNT
---------- ----------- ---------- ------------- ------------
1 1 2 1 1000
2 2 3 1 1000
3 3 4 1 4000
4 4 2 1 4000
5 5 3 1 6000
6 1 4 1 6000
7 2 2 1 8000
8 3 3 1 8000
9 4 4 1 10000
10 5 2 1 10000
11 1 2 2 20000
12 2 3 2 25000
13 3 4 2 30000
14 4 2 2 35000
15 5 3 2 40000
16 1 4 2 45000
16 rows selected.
SQL> SELECT TABLE_NAME FROM USER_TABLES;
TABLE_NAME
------------------------------
CUSTOMER_DIM
PRODUCT_DIM
ORDER_DIM
DATE_DIM
SALES_ORDER_FACT
CUSTOMER_DIM_COPY
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
COUNTRIES
EMPLOYEES
JOB_HISTORY
13 rows selected.
SQL> SELECT * FROM DATE_DIM
2 ;
DATE_SK BOOKING_D MONTH_NAM MONTH QUARTER YEAR EFFECTIVE EXPIRY_DA
---------- --------- --------- ---------- ---------- ---------- --------- ---------
1 05-FEB-07 February 2 1 2007 30-AUG-13 31-DEC-99
2 06-FEB-07 February 2 1 2007 30-AUG-13 31-DEC-99
SQL> SELECT CUSTOMER_NUMBER,PRODUCT_CODE,
2 SUM(ORDER_AMOUNT) SUM_OF_ORDER_AMOUNT
3 FROM SALES_ORDER_FACT A, CUSTOMER_DIM B, PRODUCT_DIM C WHERE
4 A.CUSTOMER_SK=B.CUSTOMER_SK AND A.PRODUCT_SK=C.PRODUCT_SK
5 GROUP BY CUSTOMER_NUMBER,PRODUCT_CODE;
CUSTOMER_NUMBER PRODUCT_CODE SUM_OF_ORDER_AMOUNT
--------------- ------------ -------------------
4 1 10000
5 2 10000
2 3 26000
1 2 21000
3 1 34000
4 2 39000
1 1 51000
2 2 8000
3 3 8000
5 3 46000
10 rows selected.
///////////////////////////////////////////////////////////////////////////////////////////////
SQL> SELECT BOOKING_DATE,
2 SUM (ORDER_AMOUNT),
3 COUNT (*)
4 FROM SALES_ORDER_FACT A,
5 DATE_DIM B
6 WHERE
7 A.ORDER_DATE_SK=B.DATE_SK
8 GROUP BY BOOKING_DATE
9 ORDER BY BOOKING_DATE;
BOOKING_D SUM(ORDER_AMOUNT) COUNT(*)
--------- ----------------- ----------
05-FEB-07 58000 10
06-FEB-07 195000 6
///////////////////////////////////////////////////////////////////////////////////////////////
SQL> SELECT YEAR, PRODUCT_NAME, CUSTOMER_CITY,
2 SUM (ORDER_AMOUNT),
3 COUNT (*)
4 FROM
5 SALES_ORDER_FACT A,
6 DATE_DIM B,
7 PRODUCT_DIM C,
8 CUSTOMER_DIM D
9 WHERE
10 A.ORDER_DATE_SK=B.DATE_SK
11 AND A.PRODUCT_SK=C.PRODUCT_SK
12 AND A.CUSTOMER_SK=D.CUSTOMER_SK
13 GROUP BY YEAR,PRODUCT_NAME, CUSTOMER_CITY
14 ORDER BY YEAR,PRODUCT_NAME, CUSTOMER_CITY;
YEAR PRODUCT_NAME CUSTOMER_CITY
---------- ------------------------------ ------------------------------
SUM(ORDER_AMOUNT) COUNT(*)
----------------- ----------
2007 Floppy Drive Mechanicsburg
70000 5
2007 Floppy Drive Pittsburgh
8000 1
2007 Hard Disk Mechanicsburg
61000 3
YEAR PRODUCT_NAME CUSTOMER_CITY
---------- ------------------------------ ------------------------------
SUM(ORDER_AMOUNT) COUNT(*)
----------------- ----------
2007 Hard Disk Pittsburgh
34000 2
2007 LCD Panel Mechanicsburg
46000 2
2007 LCD Panel Pittsburgh
34000 3
6 rows selected.
////////////////////////////////////////////////////////////////////////////////////////////////
SQL> SELECT
2 PRODUCT_NAME
3 ,MONTH_NAME
4 ,YEAR
5 ,SUM (ORDER_AMOUNT)
6 ,COUNT(*)
7 FROM
8 SALES_ORDER_FACT A
9 ,PRODUCT_DIM B
10 ,DATE_DIM C
11 WHERE
12 A.PRODUCT_SK = B.PRODUCT_SK
13 AND A.ORDER_DATE_SK = C.DATE_SK
14 GROUP BY
15 PRODUCT_NAME
16 ,PRODUCT_CATEGORY
17 ,MONTH_NAME
18 ,YEAR
19 HAVING PRODUCT_CATEGORY = 'STORAGE'
20 ORDER BY
21 YEAR
22 ,MONTH_NAME;
PRODUCT_NAME MONTH_NAM YEAR SUM(ORDER_AMOUNT) COUNT(*)
------------------------------ --------- ---------- ----------------- ----------
Floppy Drive February 2007 78000 6
Hard Disk February 2007 95000 5
/////////////////////////////////////////////////////////////////////////////////////////////////
SQL> SELECT
2 CUSTOMER_CITY,
3 QUARTER,
4 YEAR,
5 SUM(ORDER_AMOUNT),
6 COUNT(ORDER_SK)
7 FROM
8 SALES_ORDER_FACT A,
9 CUSTOMER_DIM B
10 ,DATE_DIM C
11 WHERE
12 A.CUSTOMER_SK=B.CUSTOMER_SK
13 AND A.ORDER_DATE_SK=C.DATE_SK
14 GROUP BY
15 CUSTOMER_CITY,
16 QUARTER,
17 YEAR
18 HAVING CUSTOMER_CITY = 'Mechanicsburg'
19 ORDER BY
20 YEAR,
21 QUARTER;
CUSTOMER_CITY QUARTER YEAR SUM(ORDER_AMOUNT)
------------------------------ ---------- ---------- -----------------
COUNT(ORDER_SK)
---------------
Mechanicsburg 1 2007 177000
10
////////////////////////////////////////////////////////////////////////////////////////////////////
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 MONTH_NAME,YEAR,
3 PRODUCT_NAME,
4 SUM(ORDER_AMOUNT)
5 ,COUNT(*)
6 FROM
7 SALES_ORDER_FACT A,
8 PRODUCT_DIM B,
9 DATE_DIM C
10 WHERE
11 A.PRODUCT_SK=B.PRODUCT_SK
12 AND A.ORDER_DATE_SK=C.DATE_SK
13 GROUP BY
14 MONTH_NAME,
15 YEAR, PRODUCT_NAME
16 HAVING SUM (ORDER_AMOUNT) >= 75000
17 ORDER BY
18 MONTH_NAME,
19 YEAR,
20* PRODUCT_NAME
21 ;
MONTH_NAM YEAR PRODUCT_NAME SUM(ORDER_AMOUNT) COUNT(*)
--------- ---------- ------------------------------ ----------------- ----------
February 2007 Floppy Drive 78000 6
February 2007 Hard Disk 95000 5
February 2007 LCD Panel 80000 5
////////////////////////////////////////////////////////////////////////////////////////////////////////
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 CUSTOMER_NUMBER,
3 YEAR, COUNT (*)
4 FROM
5 SALES_ORDER_FACT A,
6 CUSTOMER_DIM B,
7 DATE_DIM C
8 WHERE
9 A.CUSTOMER_SK=B.CUSTOMER_SK
10 AND A.ORDER_DATE_SK=C.DATE_SK
11 GROUP BY
12 CUSTOMER_NUMBER,
13 YEAR
14 HAVING COUNT (*)>1
15* AND (12-TO_CHAR(MAX(BOOKING_DATE),'MM'))<12
16 ;
CUSTOMER_NUMBER YEAR COUNT(*)
--------------- ---------- ----------
2 2007 3
3 2007 3
1 2007 4
4 2007 3
5 2007 3
/////////////////////////////////////////////////////////////////////////////////////////////////////
SQL Assignment 2nd Phase (Chapters
5-8)
///////////////////////////////////////////////////////////////////////////////
/////////////////////CHAPTER - 5//////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////
/////////////////////CHAPTER - 5//////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////
CREATING
SALES_ORDER TABLE
SQL> CREATE TABLE SALES_ORDER
2 (ORDER_NUMBER INT,
3 CUSTOMER_NUMBER INT,
4 PRODUCT_CODE INT,
5 ORDER_DATE DATE,
6 ENTRY_DATE DATE,
7 ORDER_AMOUNT DECIMAL (10,2));
Table created.
///////////////INSERTING DATA INTO ORDER_DIM TABLE///////////////////
SQL> INSERT INTO order_dim VALUES (order_auto.nextval, 17, CURRENT_DATE, to_date('9999-12-31','yyyy-
mm-dd'));
1 row created.
SQL> INSERT INTO order_dim VALUES (order_auto.nextval, 18, CURRENT_DATE, to_date('9999-12-31','yyyy-
mm-dd'));
1 row created.
SQL> INSERT INTO order_dim VALUES (order_auto.nextval, 19, CURRENT_DATE, to_date('9999-12-31','yyyy-
mm-dd'));
1 row created.
SQL> INSERT INTO order_dim VALUES (order_auto.nextval, 20, CURRENT_DATE, to_date('9999-12-31','yyyy-
mm-dd'));
1 row created.
//////////////////INSERTING DATA INTO DATE_DIM TABLE/////////////////////////
SQL> INSERT INTO DATE_DIM VALUES (DATE_AUTO.NEXTVAL, TO_DATE('2007-02-06','yyyy-mm-dd'), 'February',
2, 1, 2007, CURRENT_DATE, TO_DATE('9999-12-31','yyyy-mm-dd'));
1 row created.
//////////////////INSERTING DATA INTO SALES_ORDER TABLE/////////////////////
SQL> INSERT INTO SALES_ORDER VALUES (17, 1, 1, TO_DATE('2007-02-06','yyyy-mm-dd'), TO_DATE('2007-02-
06','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (18, 2, 1, TO_DATE('2007-02-06','yyyy-mm-dd'), TO_DATE('2007-02-
06','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (19, 3, 1, TO_DATE('2007-02-06','yyyy-mm-dd'), TO_DATE('2007-02-
06','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (20, 4, 1, TO_DATE('2007-02-06','yyyy-mm-dd'), TO_DATE('2007-02-
06','yyyy-mm-dd'), 4000);
1 row created.
////////////////INSERTING THE DATA INTO SALES_ORDER_FACT MANUALLY//////////
SQL> INSERT INTO sales_order_fact VALUES(17, 1, 3, 3, 1000);
1 row created.
SQL> INSERT INTO sales_order_fact VALUES(18, 2, 3, 3, 1000);
1 row created.
SQL> INSERT INTO sales_order_fact VALUES(19, 3, 3, 3, 4000);
1 row created.
SQL> INSERT INTO sales_order_fact VALUES(20, 4, 3, 3, 4000);
1 row created.
SQL> SELECT * FROM SALES_ORDER_FACT;
ORDER_SK CUSTOMER_SK PRODUCT_SK ORDER_DATE_SK ORDER_AMOUNT
---------- ----------- ---------- ------------- ------------
17 1 3 3 1000
18 2 3 3 1000
19 3 3 3 4000
20 4 3 3 4000
1 1 2 1 1000
2 2 3 1 1000
3 3 4 1 4000
4 4 2 1 4000
5 5 3 1 6000
6 1 4 1 6000
7 2 2 1 8000
ORDER_SK CUSTOMER_SK PRODUCT_SK ORDER_DATE_SK ORDER_AMOUNT
---------- ----------- ---------- ------------- ------------
8 3 3 1 8000
9 4 4 1 10000
10 5 2 1 10000
11 1 2 2 20000
12 2 3 2 25000
13 3 4 2 30000
14 4 2 2 35000
15 5 3 2 40000
16 1 4 2 45000
20 rows selected.
/////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////CHAPTER 6//////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////
/////////////////////////INSERTING THE DATA INTO DATE_DIM/////////////////////
SQL> INSERT INTO DATE_DIM VALUES
2 (DATE_AUTO.NEXTVAL, CURRENT_DATE,
3 TO_CHAR(CURRENT_DATE,'month'),
4 TO_CHAR(CURRENT_DATE,'dd'),
5 TO_CHAR(CURRENT_DATE,'Q'),
6 TO_CHAR(CURRENT_DATE,'YYYY'),
7 TO_DATE('1000-01-01','yyyy-mm-dd'),
8 TO_DATE('9999-12-31','yyyy-mm-dd'));
1 row created.
//////////////////////INSERTING DATA FROM SALES_ORDER TO DATE_DIM////////////////////
SQL> INSERT INTO DATE_DIM
2 SELECT ORDER_NUMBER,ORDER_DATE,
3 TO_CHAR(ORDER_DATE,'month'),
4 TO_CHAR(ORDER_DATE,'dd'),
5 TO_CHAR(ORDER_DATE,'q'),
6 TO_CHAR(ORDER_DATE,'yyyy'),
7 TO_DATE('1000-01-01','yyyy-mm-dd'),TO_DATE('9999-12-31','yyyy-mm-dd')
8 FROM sales_order
9 WHERE ORDER_DATE
10 NOT IN
11 (SELECT booking_date FROM date_dim);
0 rows created.
/////////////////////INSERTING DATA INTO SALES_ORDER//////////////////////
SQL> INSERT INTO sales_order VALUES (21, 1, 3, to_date('2007-02-07','yyyy-mm-dd'), to_date('2007-02-
07','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO sales_order VALUES (22, 2, 3, to_date('2007-02-08','yyyy-mm-dd'), to_date('2007-02-
08','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO sales_order VALUES (23, 3, 3, to_date('2007-02-09','yyyy-mm-dd'), to_date('2007-02-
09','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO sales_order VALUES (24, 4, 3, to_date('2007-02-10','yyyy-mm-dd'), to_date('2007-02-
10','yyyy-mm-dd'), 4000);
1 row created.
////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////CHAPTER 7//////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////
///////////////////DELETING ROWS FROM ALL THE TABLES/////////////////////
SQL> TRUNCATE table customer_dim;
Table truncated.
SQL> TRUNCATE table product_dim;
Table truncated.
SQL> TRUNCATE table order_dim;
Table truncated.
SQL> TRUNCATE table date_dim;
Table truncated.
SQL> TRUNCATE table sales_order_fact;
Table truncated.
SQL> TRUNCATE table sales_order;
Table truncated.
//////////////////INSERTING DATA INTO DATE_DIM////////////////////////
SQL> INSERT INTO DATE_DIM VALUES (date_auto.nextval, to_date('2005-03-01','YYYY-MM-DD'),
2 TO_CHAR(TO_DATE('2005-03-01','YYYY-MM-DD'),'MONTH'),
3 TO_CHAR(TO_DATE('2005-03-01','YYYY-MM-DD'),'mm'),
4 TO_CHAR(TO_DATE('2005-03-01','YYYY-MM-DD'),'Q'),
5 TO_CHAR(TO_DATE('2005-03-01','YYYY-MM-DD'),'YYYY'),
6 TO_DATE('1000-01-01','yyyy-mm-dd'),
7 TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> UPDATE date_dim set Booking_date = Booking_date +1;
1 row updated.
////////////////INSERTING DATA INTO CUSTOMER_DIM///////////////////////
SQL> DROP SEQUENCE AUTO_INCR;
Sequence dropped.
SQL> CREATE SEQUENCE AUTO_INCR
2 START WITH 1
3 INCREMENT BY 1
4 CACHE 100;
Sequence created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 1,'Really Large Customers','7500 Louise Dr.'
,'17050','Mechanicsburg','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'))
;
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 2,'Small Stores','2500 Woodland St.','17055'
,'Pittsburgh','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 3,'Medium Retailers','1111 Ritter Rd.','1705
5','Pittsburgh','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 4,'Good Companies','9500 Scott St.','17050',
'Mechanicsburg','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 5,'Wonderful Shops','3333 Rossmoyne Rd.','17
050','Mechanicsburg','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 6,'Loyal Clients', '7070 Ritter Rd.', '17055
','Pittsburgh','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 7,'Distinguished Partners','9999 Scott St.',
'17050','Mechanicsburg','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
//////////////////////INSERT DATA INTO PRODUCT_DIM//////////////////////////
SQL> DROP SEQUENCE PRO_AUTO;
Sequence dropped.
SQL> CREATE SEQUENCE PRO_AUTO
2 START WITH 1
3 INCREMENT BY 1
4 CACHE 10;
Sequence created.
SQL> INSERT INTO PRODUCT_DIM VALUES(PRO_AUTO.NEXTVAL,1,'Hard Disk Drive','Storage',TO_DATE('2005-03-
01','YYYY-MM-DD'),TO_DATE('9999-12 31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO PRODUCT_DIM VALUES(PRO_AUTO.NEXTVAL,2,'Floppy Drive','Storage',TO_DATE('2005-03-01'
,'YYYY-MM-DD'),TO_DATE('9999-12 31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO PRODUCT_DIM VALUES(PRO_AUTO.NEXTVAL,3,'LCD Panel','Monitor',TO_DATE('2005-03-01','Y
YYY-MM-DD'),TO_DATE('9999-12 31','YYYY-MM-DD'));
1 row created.
///////////////////////INSERTING DATA INTO SALES_ORDER/////////////////////////
SQL> INSERT INTO SALES_ORDER VALUES (1, 1, 1, TO_DATE('2005-02-01','yyyy-mm-dd'), TO_DATE('2005-02-0
1','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (2, 2, 2, TO_DATE('2005-02-10','yyyy-mm-dd'), TO_DATE('2005-02-1
0','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (3, 3, 3, TO_DATE('2005-03-01','yyyy-mm-dd'), TO_DATE('2005-03-0
1','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (4, 4, 1, TO_DATE('2005-04-15','yyyy-mm-dd'), TO_DATE('2005-04-1
5','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (5, 5, 2, TO_DATE('2005-05-20','yyyy-mm-dd'), TO_DATE('2005-05-2
0','yyyy-mm-dd'), 6000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (6, 6, 3, TO_DATE('2005-07-30','yyyy-mm-dd'), TO_DATE('2005-07-3
0','yyyy-mm-dd'), 6000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (7, 7, 1, TO_DATE('2005-09-01','yyyy-mm-dd'), TO_DATE('2005-09-0
1','yyyy-mm-dd'), 8000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (8, 1, 2, TO_DATE('2005-11-10','yyyy-mm-dd'), TO_DATE('2005-11-1
0','yyyy-mm-dd'), 8000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (9, 2, 3, TO_DATE('2006-01-05','yyyy-mm-dd'), TO_DATE('2006-01-0
5','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (10, 3, 1,TO_DATE('2006-02-10','yyyy-mm-dd'), TO_DATE('2006-02-1
0','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (11, 4, 2,TO_DATE('2006-03-15','yyyy-mm-dd'), TO_DATE('2006-03-1
5','yyyy-mm-dd'), 2000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (12, 5, 3,TO_DATE('2006-04-20','yyyy-mm-dd'), TO_DATE('2006-04-2
0','yyyy-mm-dd'), 2500);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (13, 6, 1,TO_DATE('2006-05-30','yyyy-mm-dd'), TO_DATE('2006-05-3
0','yyyy-mm-dd'), 3000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (14, 7, 2,TO_DATE('2006-06-01','yyyy-mm-dd'), TO_DATE('2006-06-0
1','yyyy-mm-dd'), 3500);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (15, 1, 3,TO_DATE('2006-07-15','yyyy-mm-dd'), TO_DATE('2006-07-1
5','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (16, 2, 1,TO_DATE('2006-08-30','yyyy-mm-dd'), TO_DATE('2006-08-3
0','yyyy-mm-dd'), 4500);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (17, 3, 2,TO_DATE('2006-09-05','yyyy-mm-dd'), TO_DATE('2006-09-0
5','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (18, 4, 3,TO_DATE('2006-10-05','yyyy-mm-dd'), TO_DATE('2006-10-0
5','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (19, 5, 1,TO_DATE('2007-01-10','yyyy-mm-dd'), TO_DATE('2007-01-1
0','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (20, 6, 2,TO_DATE('2007-02-20','yyyy-mm-dd'), TO_DATE('2007-02-2
0','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (21, 7, 3,TO_DATE('2007-02-28','yyyy-mm-dd'), TO_DATE('2007-02-2
8','yyyy-mm-dd'), 4000);
1 row created.
//////////////////INSERTING DATA INTO ORDER_DIM///////////////////////
SQL>
SQL> DROP SEQUENCE ORDER_AUTO;
Sequence dropped.
SQL> CREATE SEQUENCE ORDER_AUTO
2 START WITH 1
3 INCREMENT BY 1
4 CACHE 10;
Sequence created.
SQL> INSERT INTO ORDER_DIM SELECT ORDER_AUTO.NEXTVAL, ORDER_NUMBER,ORDER_DATE, TO_DATE('9999-12-31'
,'YYYY-MM-DD') FROM SALES_ORDER
2 WHERE ORDER_DATE >= TO_DATE('2005-03-01','YYYY-MM-DD')
3 AND ORDER_DATE < TO_DATE('2007-02-28','YYYY-MM-DD');
18 rows created.
///////////////////////INSERTING DATA INTO SALES_ORDER_FACT///////////////////
SQL> INSERT INTO SALES_ORDER_FACT
2 SELECT ORDER_SK, CUSTOMER_SK, PRODUCT_SK, DATE_SK, ORDER_AMOUNT
3 FROM SALES_ORDER A, ORDER_DIM B, CUSTOMER_DIM C, PRODUCT_DIM D,DATE_DIM E
4 WHERE
5 A.ORDER_NUMBER = B.ORDER_NUMBER
6 AND A.CUSTOMER_NUMBER = c.CUSTOMER_NUMBER
7 AND A.PRODUCT_CODE = D.PRODUCT_CODE
8 AND a.order_date >= to_date('2005-03-01','YYYY-MM-DD')
9 AND a.order_date < to_date('2007-02-28','YYYY-MM-DD');
18 rows created.
SQL> SELECT ORDER_NUMBER, CUSTOMER_NAME,PRODUCT_NAME,BOOKING_DATE,ORDER_AMOUNT
2 FROM SALES_ORDER_FACT A, CUSTOMER_DIM B, PRODUCT_DIM C, ORDER_DIM D, DATE_DIM
3 WHERE A.CUSTOMER_SK = B.CUSTOMER_SK
4 AND A.PRODUCT_SK = C.PRODUCT_SK
5 AND A.ORDER_SK = D.ORDER_SK
6 AND A.ORDER_DATE_SK=E.DATE_SK;
ORDER_NUMBER CUSTOMER_NAME
------------ --------------------------------------------------
PRODUCT_NAME BOOKING_D ORDER_AMOUNT
------------------------------ --------- ------------
3 Medium Retailers
LCD Panel 02-MAR-05 4000
4 Good Companies
Hard Disk Drive 02-MAR-05 4000
5 Wonderful Shops
Floppy Drive 02-MAR-05 6000
ORDER_NUMBER CUSTOMER_NAME
------------ --------------------------------------------------
PRODUCT_NAME BOOKING_D ORDER_AMOUNT
------------------------------ --------- ------------
6 Loyal Clients
LCD Panel 02-MAR-05 6000
7 Distinguished Partners
Hard Disk Drive 02-MAR-05 8000
8 Really Large Customers
Floppy Drive 02-MAR-05 8000
ORDER_NUMBER CUSTOMER_NAME
------------ --------------------------------------------------
PRODUCT_NAME BOOKING_D ORDER_AMOUNT
------------------------------ --------- ------------
9 Small Stores
LCD Panel 02-MAR-05 1000
10 Medium Retailers
Hard Disk Drive 02-MAR-05 1000
11 Good Companies
Floppy Drive 02-MAR-05 2000
ORDER_NUMBER CUSTOMER_NAME
------------ --------------------------------------------------
PRODUCT_NAME BOOKING_D ORDER_AMOUNT
------------------------------ --------- ------------
12 Wonderful Shops
LCD Panel 02-MAR-05 2500
13 Loyal Clients
Hard Disk Drive 02-MAR-05 3000
14 Distinguished Partners
Floppy Drive 02-MAR-05 3500
ORDER_NUMBER CUSTOMER_NAME
------------ --------------------------------------------------
PRODUCT_NAME BOOKING_D ORDER_AMOUNT
------------------------------ --------- ------------
15 Really Large Customers
LCD Panel 02-MAR-05 4000
16 Small Stores
Hard Disk Drive 02-MAR-05 4500
17 Medium Retailers
Floppy Drive 02-MAR-05 1000
ORDER_NUMBER CUSTOMER_NAME
------------ --------------------------------------------------
PRODUCT_NAME BOOKING_D ORDER_AMOUNT
------------------------------ --------- ------------
18 Good Companies
LCD Panel 02-MAR-05 1000
19 Wonderful Shops
Hard Disk Drive 02-MAR-05 4000
20 Loyal Clients
Floppy Drive 02-MAR-05 4000
18 rows selected.
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////CHAPTER - 8/////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
////////////////////INSERTION AND UPDATION IN EXISTING TABLE/////////////////
SQL> INSERT INTO order_dim ( order_sk, order_number, effective_date, expiry_date)
2 SELECT order_auto.nextval, order_number,order_date, to_date('9999-12-31','YYYY-MM-DD')
3 FROM sales_order
4 WHERE entry_date = CURRENT_DATE;
0 rows created.
SQL> INSERT INTO sales_order_fact
2 SELECT order_sk, customer_sk, product_sk, date_sk, order_amount
3 FROM sales_order a, order_dim b, customer_dim c, product_dim d, date_dim e
4 WHERE
5 a.order_number = b.order_number
6 AND a.customer_number = c.customer_number
7 AND a.order_date >= c.effective_date
8 AND a.order_date <= c.expiry_date
9 AND a.product_code = d.product_code
10 AND a.order_date >= d.effective_date
11 AND a.order_date <= d.expiry_date
12 AND a.order_date = e.booking_date
13 AND a.entry_date = CURRENT_DATE
14 ;
0 rows created.
SQL> update customer_dim set CUSTOMER_STREET_ADDRESS='7777 Ritter Rd.' where CUSTOMER_NUMBER=6;
1 row updated.
SQL> update customer_dim set customer_name='Distinguished Agencies' where CUSTOMER_NUMBER=7;
1 row updated.
SQL> INSERT INTO customer_dim VALUES(auto_incr.nextval, 8,'Subsidiaries','10000 Wetline Blvd.','1705
5','Pittsburgh','PA',to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO product_dim VALUES(pro_auto.nextval,4,'Keyboard','Peripheral',
2 to_date('2005-03-01','YYYY-MM-DD'),
3 TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO sales_order VALUES (22, 1, 1, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO sales_order VALUES (23, 2, 2, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 2000);
1 row created.
SQL> INSERT INTO sales_order VALUES (24, 3, 3, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 3000);
1 row created.
SQL> INSERT INTO sales_order VALUES (25, 4, 4, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO sales_order VALUES (26, 5, 2, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO sales_order VALUES (27, 6, 2, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 3000);
1 row created.
SQL> INSERT INTO sales_order VALUES (28, 7, 3, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 5000);
1 row created.
SQL> INSERT INTO sales_order VALUES (29, 8, 4, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 7000);
1 row created.
SQL> INSERT INTO sales_order VALUES (30, 1, 1, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO sales_order VALUES (31, 2, 2, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 2000);
1 row created.
SQL> INSERT INTO sales_order VALUES (32, 3, 3, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO sales_order VALUES (33, 4, 4, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 6000);
1 row created.
SQL> INSERT INTO sales_order VALUES (34, 5, 1, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 2500);
1 row created.
SQL> INSERT INTO sales_order VALUES (35, 6, 2, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 5000);
1 row created.
SQL> INSERT INTO sales_order VALUES (36, 7, 3, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 7500);
1 row created.
SQL> INSERT INTO sales_order VALUES (37, 8, 4, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 1000);
SQL> CREATE TABLE SALES_ORDER
2 (ORDER_NUMBER INT,
3 CUSTOMER_NUMBER INT,
4 PRODUCT_CODE INT,
5 ORDER_DATE DATE,
6 ENTRY_DATE DATE,
7 ORDER_AMOUNT DECIMAL (10,2));
Table created.
///////////////INSERTING DATA INTO ORDER_DIM TABLE///////////////////
SQL> INSERT INTO order_dim VALUES (order_auto.nextval, 17, CURRENT_DATE, to_date('9999-12-31','yyyy-
mm-dd'));
1 row created.
SQL> INSERT INTO order_dim VALUES (order_auto.nextval, 18, CURRENT_DATE, to_date('9999-12-31','yyyy-
mm-dd'));
1 row created.
SQL> INSERT INTO order_dim VALUES (order_auto.nextval, 19, CURRENT_DATE, to_date('9999-12-31','yyyy-
mm-dd'));
1 row created.
SQL> INSERT INTO order_dim VALUES (order_auto.nextval, 20, CURRENT_DATE, to_date('9999-12-31','yyyy-
mm-dd'));
1 row created.
//////////////////INSERTING DATA INTO DATE_DIM TABLE/////////////////////////
SQL> INSERT INTO DATE_DIM VALUES (DATE_AUTO.NEXTVAL, TO_DATE('2007-02-06','yyyy-mm-dd'), 'February',
2, 1, 2007, CURRENT_DATE, TO_DATE('9999-12-31','yyyy-mm-dd'));
1 row created.
//////////////////INSERTING DATA INTO SALES_ORDER TABLE/////////////////////
SQL> INSERT INTO SALES_ORDER VALUES (17, 1, 1, TO_DATE('2007-02-06','yyyy-mm-dd'), TO_DATE('2007-02-
06','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (18, 2, 1, TO_DATE('2007-02-06','yyyy-mm-dd'), TO_DATE('2007-02-
06','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (19, 3, 1, TO_DATE('2007-02-06','yyyy-mm-dd'), TO_DATE('2007-02-
06','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (20, 4, 1, TO_DATE('2007-02-06','yyyy-mm-dd'), TO_DATE('2007-02-
06','yyyy-mm-dd'), 4000);
1 row created.
////////////////INSERTING THE DATA INTO SALES_ORDER_FACT MANUALLY//////////
SQL> INSERT INTO sales_order_fact VALUES(17, 1, 3, 3, 1000);
1 row created.
SQL> INSERT INTO sales_order_fact VALUES(18, 2, 3, 3, 1000);
1 row created.
SQL> INSERT INTO sales_order_fact VALUES(19, 3, 3, 3, 4000);
1 row created.
SQL> INSERT INTO sales_order_fact VALUES(20, 4, 3, 3, 4000);
1 row created.
SQL> SELECT * FROM SALES_ORDER_FACT;
ORDER_SK CUSTOMER_SK PRODUCT_SK ORDER_DATE_SK ORDER_AMOUNT
---------- ----------- ---------- ------------- ------------
17 1 3 3 1000
18 2 3 3 1000
19 3 3 3 4000
20 4 3 3 4000
1 1 2 1 1000
2 2 3 1 1000
3 3 4 1 4000
4 4 2 1 4000
5 5 3 1 6000
6 1 4 1 6000
7 2 2 1 8000
ORDER_SK CUSTOMER_SK PRODUCT_SK ORDER_DATE_SK ORDER_AMOUNT
---------- ----------- ---------- ------------- ------------
8 3 3 1 8000
9 4 4 1 10000
10 5 2 1 10000
11 1 2 2 20000
12 2 3 2 25000
13 3 4 2 30000
14 4 2 2 35000
15 5 3 2 40000
16 1 4 2 45000
20 rows selected.
/////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////CHAPTER 6//////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////
/////////////////////////INSERTING THE DATA INTO DATE_DIM/////////////////////
SQL> INSERT INTO DATE_DIM VALUES
2 (DATE_AUTO.NEXTVAL, CURRENT_DATE,
3 TO_CHAR(CURRENT_DATE,'month'),
4 TO_CHAR(CURRENT_DATE,'dd'),
5 TO_CHAR(CURRENT_DATE,'Q'),
6 TO_CHAR(CURRENT_DATE,'YYYY'),
7 TO_DATE('1000-01-01','yyyy-mm-dd'),
8 TO_DATE('9999-12-31','yyyy-mm-dd'));
1 row created.
//////////////////////INSERTING DATA FROM SALES_ORDER TO DATE_DIM////////////////////
SQL> INSERT INTO DATE_DIM
2 SELECT ORDER_NUMBER,ORDER_DATE,
3 TO_CHAR(ORDER_DATE,'month'),
4 TO_CHAR(ORDER_DATE,'dd'),
5 TO_CHAR(ORDER_DATE,'q'),
6 TO_CHAR(ORDER_DATE,'yyyy'),
7 TO_DATE('1000-01-01','yyyy-mm-dd'),TO_DATE('9999-12-31','yyyy-mm-dd')
8 FROM sales_order
9 WHERE ORDER_DATE
10 NOT IN
11 (SELECT booking_date FROM date_dim);
0 rows created.
/////////////////////INSERTING DATA INTO SALES_ORDER//////////////////////
SQL> INSERT INTO sales_order VALUES (21, 1, 3, to_date('2007-02-07','yyyy-mm-dd'), to_date('2007-02-
07','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO sales_order VALUES (22, 2, 3, to_date('2007-02-08','yyyy-mm-dd'), to_date('2007-02-
08','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO sales_order VALUES (23, 3, 3, to_date('2007-02-09','yyyy-mm-dd'), to_date('2007-02-
09','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO sales_order VALUES (24, 4, 3, to_date('2007-02-10','yyyy-mm-dd'), to_date('2007-02-
10','yyyy-mm-dd'), 4000);
1 row created.
////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////CHAPTER 7//////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////
///////////////////DELETING ROWS FROM ALL THE TABLES/////////////////////
SQL> TRUNCATE table customer_dim;
Table truncated.
SQL> TRUNCATE table product_dim;
Table truncated.
SQL> TRUNCATE table order_dim;
Table truncated.
SQL> TRUNCATE table date_dim;
Table truncated.
SQL> TRUNCATE table sales_order_fact;
Table truncated.
SQL> TRUNCATE table sales_order;
Table truncated.
//////////////////INSERTING DATA INTO DATE_DIM////////////////////////
SQL> INSERT INTO DATE_DIM VALUES (date_auto.nextval, to_date('2005-03-01','YYYY-MM-DD'),
2 TO_CHAR(TO_DATE('2005-03-01','YYYY-MM-DD'),'MONTH'),
3 TO_CHAR(TO_DATE('2005-03-01','YYYY-MM-DD'),'mm'),
4 TO_CHAR(TO_DATE('2005-03-01','YYYY-MM-DD'),'Q'),
5 TO_CHAR(TO_DATE('2005-03-01','YYYY-MM-DD'),'YYYY'),
6 TO_DATE('1000-01-01','yyyy-mm-dd'),
7 TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> UPDATE date_dim set Booking_date = Booking_date +1;
1 row updated.
////////////////INSERTING DATA INTO CUSTOMER_DIM///////////////////////
SQL> DROP SEQUENCE AUTO_INCR;
Sequence dropped.
SQL> CREATE SEQUENCE AUTO_INCR
2 START WITH 1
3 INCREMENT BY 1
4 CACHE 100;
Sequence created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 1,'Really Large Customers','7500 Louise Dr.'
,'17050','Mechanicsburg','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'))
;
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 2,'Small Stores','2500 Woodland St.','17055'
,'Pittsburgh','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 3,'Medium Retailers','1111 Ritter Rd.','1705
5','Pittsburgh','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 4,'Good Companies','9500 Scott St.','17050',
'Mechanicsburg','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 5,'Wonderful Shops','3333 Rossmoyne Rd.','17
050','Mechanicsburg','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 6,'Loyal Clients', '7070 Ritter Rd.', '17055
','Pittsburgh','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES(AUTO_INCR.NEXTVAL, 7,'Distinguished Partners','9999 Scott St.',
'17050','Mechanicsburg','PA',TO_DATE('2005-03-01','YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
//////////////////////INSERT DATA INTO PRODUCT_DIM//////////////////////////
SQL> DROP SEQUENCE PRO_AUTO;
Sequence dropped.
SQL> CREATE SEQUENCE PRO_AUTO
2 START WITH 1
3 INCREMENT BY 1
4 CACHE 10;
Sequence created.
SQL> INSERT INTO PRODUCT_DIM VALUES(PRO_AUTO.NEXTVAL,1,'Hard Disk Drive','Storage',TO_DATE('2005-03-
01','YYYY-MM-DD'),TO_DATE('9999-12 31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO PRODUCT_DIM VALUES(PRO_AUTO.NEXTVAL,2,'Floppy Drive','Storage',TO_DATE('2005-03-01'
,'YYYY-MM-DD'),TO_DATE('9999-12 31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO PRODUCT_DIM VALUES(PRO_AUTO.NEXTVAL,3,'LCD Panel','Monitor',TO_DATE('2005-03-01','Y
YYY-MM-DD'),TO_DATE('9999-12 31','YYYY-MM-DD'));
1 row created.
///////////////////////INSERTING DATA INTO SALES_ORDER/////////////////////////
SQL> INSERT INTO SALES_ORDER VALUES (1, 1, 1, TO_DATE('2005-02-01','yyyy-mm-dd'), TO_DATE('2005-02-0
1','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (2, 2, 2, TO_DATE('2005-02-10','yyyy-mm-dd'), TO_DATE('2005-02-1
0','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (3, 3, 3, TO_DATE('2005-03-01','yyyy-mm-dd'), TO_DATE('2005-03-0
1','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (4, 4, 1, TO_DATE('2005-04-15','yyyy-mm-dd'), TO_DATE('2005-04-1
5','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (5, 5, 2, TO_DATE('2005-05-20','yyyy-mm-dd'), TO_DATE('2005-05-2
0','yyyy-mm-dd'), 6000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (6, 6, 3, TO_DATE('2005-07-30','yyyy-mm-dd'), TO_DATE('2005-07-3
0','yyyy-mm-dd'), 6000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (7, 7, 1, TO_DATE('2005-09-01','yyyy-mm-dd'), TO_DATE('2005-09-0
1','yyyy-mm-dd'), 8000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (8, 1, 2, TO_DATE('2005-11-10','yyyy-mm-dd'), TO_DATE('2005-11-1
0','yyyy-mm-dd'), 8000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (9, 2, 3, TO_DATE('2006-01-05','yyyy-mm-dd'), TO_DATE('2006-01-0
5','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (10, 3, 1,TO_DATE('2006-02-10','yyyy-mm-dd'), TO_DATE('2006-02-1
0','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (11, 4, 2,TO_DATE('2006-03-15','yyyy-mm-dd'), TO_DATE('2006-03-1
5','yyyy-mm-dd'), 2000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (12, 5, 3,TO_DATE('2006-04-20','yyyy-mm-dd'), TO_DATE('2006-04-2
0','yyyy-mm-dd'), 2500);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (13, 6, 1,TO_DATE('2006-05-30','yyyy-mm-dd'), TO_DATE('2006-05-3
0','yyyy-mm-dd'), 3000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (14, 7, 2,TO_DATE('2006-06-01','yyyy-mm-dd'), TO_DATE('2006-06-0
1','yyyy-mm-dd'), 3500);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (15, 1, 3,TO_DATE('2006-07-15','yyyy-mm-dd'), TO_DATE('2006-07-1
5','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (16, 2, 1,TO_DATE('2006-08-30','yyyy-mm-dd'), TO_DATE('2006-08-3
0','yyyy-mm-dd'), 4500);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (17, 3, 2,TO_DATE('2006-09-05','yyyy-mm-dd'), TO_DATE('2006-09-0
5','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (18, 4, 3,TO_DATE('2006-10-05','yyyy-mm-dd'), TO_DATE('2006-10-0
5','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (19, 5, 1,TO_DATE('2007-01-10','yyyy-mm-dd'), TO_DATE('2007-01-1
0','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (20, 6, 2,TO_DATE('2007-02-20','yyyy-mm-dd'), TO_DATE('2007-02-2
0','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (21, 7, 3,TO_DATE('2007-02-28','yyyy-mm-dd'), TO_DATE('2007-02-2
8','yyyy-mm-dd'), 4000);
1 row created.
//////////////////INSERTING DATA INTO ORDER_DIM///////////////////////
SQL>
SQL> DROP SEQUENCE ORDER_AUTO;
Sequence dropped.
SQL> CREATE SEQUENCE ORDER_AUTO
2 START WITH 1
3 INCREMENT BY 1
4 CACHE 10;
Sequence created.
SQL> INSERT INTO ORDER_DIM SELECT ORDER_AUTO.NEXTVAL, ORDER_NUMBER,ORDER_DATE, TO_DATE('9999-12-31'
,'YYYY-MM-DD') FROM SALES_ORDER
2 WHERE ORDER_DATE >= TO_DATE('2005-03-01','YYYY-MM-DD')
3 AND ORDER_DATE < TO_DATE('2007-02-28','YYYY-MM-DD');
18 rows created.
///////////////////////INSERTING DATA INTO SALES_ORDER_FACT///////////////////
SQL> INSERT INTO SALES_ORDER_FACT
2 SELECT ORDER_SK, CUSTOMER_SK, PRODUCT_SK, DATE_SK, ORDER_AMOUNT
3 FROM SALES_ORDER A, ORDER_DIM B, CUSTOMER_DIM C, PRODUCT_DIM D,DATE_DIM E
4 WHERE
5 A.ORDER_NUMBER = B.ORDER_NUMBER
6 AND A.CUSTOMER_NUMBER = c.CUSTOMER_NUMBER
7 AND A.PRODUCT_CODE = D.PRODUCT_CODE
8 AND a.order_date >= to_date('2005-03-01','YYYY-MM-DD')
9 AND a.order_date < to_date('2007-02-28','YYYY-MM-DD');
18 rows created.
SQL> SELECT ORDER_NUMBER, CUSTOMER_NAME,PRODUCT_NAME,BOOKING_DATE,ORDER_AMOUNT
2 FROM SALES_ORDER_FACT A, CUSTOMER_DIM B, PRODUCT_DIM C, ORDER_DIM D, DATE_DIM
3 WHERE A.CUSTOMER_SK = B.CUSTOMER_SK
4 AND A.PRODUCT_SK = C.PRODUCT_SK
5 AND A.ORDER_SK = D.ORDER_SK
6 AND A.ORDER_DATE_SK=E.DATE_SK;
ORDER_NUMBER CUSTOMER_NAME
------------ --------------------------------------------------
PRODUCT_NAME BOOKING_D ORDER_AMOUNT
------------------------------ --------- ------------
3 Medium Retailers
LCD Panel 02-MAR-05 4000
4 Good Companies
Hard Disk Drive 02-MAR-05 4000
5 Wonderful Shops
Floppy Drive 02-MAR-05 6000
ORDER_NUMBER CUSTOMER_NAME
------------ --------------------------------------------------
PRODUCT_NAME BOOKING_D ORDER_AMOUNT
------------------------------ --------- ------------
6 Loyal Clients
LCD Panel 02-MAR-05 6000
7 Distinguished Partners
Hard Disk Drive 02-MAR-05 8000
8 Really Large Customers
Floppy Drive 02-MAR-05 8000
ORDER_NUMBER CUSTOMER_NAME
------------ --------------------------------------------------
PRODUCT_NAME BOOKING_D ORDER_AMOUNT
------------------------------ --------- ------------
9 Small Stores
LCD Panel 02-MAR-05 1000
10 Medium Retailers
Hard Disk Drive 02-MAR-05 1000
11 Good Companies
Floppy Drive 02-MAR-05 2000
ORDER_NUMBER CUSTOMER_NAME
------------ --------------------------------------------------
PRODUCT_NAME BOOKING_D ORDER_AMOUNT
------------------------------ --------- ------------
12 Wonderful Shops
LCD Panel 02-MAR-05 2500
13 Loyal Clients
Hard Disk Drive 02-MAR-05 3000
14 Distinguished Partners
Floppy Drive 02-MAR-05 3500
ORDER_NUMBER CUSTOMER_NAME
------------ --------------------------------------------------
PRODUCT_NAME BOOKING_D ORDER_AMOUNT
------------------------------ --------- ------------
15 Really Large Customers
LCD Panel 02-MAR-05 4000
16 Small Stores
Hard Disk Drive 02-MAR-05 4500
17 Medium Retailers
Floppy Drive 02-MAR-05 1000
ORDER_NUMBER CUSTOMER_NAME
------------ --------------------------------------------------
PRODUCT_NAME BOOKING_D ORDER_AMOUNT
------------------------------ --------- ------------
18 Good Companies
LCD Panel 02-MAR-05 1000
19 Wonderful Shops
Hard Disk Drive 02-MAR-05 4000
20 Loyal Clients
Floppy Drive 02-MAR-05 4000
18 rows selected.
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////CHAPTER - 8/////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
////////////////////INSERTION AND UPDATION IN EXISTING TABLE/////////////////
SQL> INSERT INTO order_dim ( order_sk, order_number, effective_date, expiry_date)
2 SELECT order_auto.nextval, order_number,order_date, to_date('9999-12-31','YYYY-MM-DD')
3 FROM sales_order
4 WHERE entry_date = CURRENT_DATE;
0 rows created.
SQL> INSERT INTO sales_order_fact
2 SELECT order_sk, customer_sk, product_sk, date_sk, order_amount
3 FROM sales_order a, order_dim b, customer_dim c, product_dim d, date_dim e
4 WHERE
5 a.order_number = b.order_number
6 AND a.customer_number = c.customer_number
7 AND a.order_date >= c.effective_date
8 AND a.order_date <= c.expiry_date
9 AND a.product_code = d.product_code
10 AND a.order_date >= d.effective_date
11 AND a.order_date <= d.expiry_date
12 AND a.order_date = e.booking_date
13 AND a.entry_date = CURRENT_DATE
14 ;
0 rows created.
SQL> update customer_dim set CUSTOMER_STREET_ADDRESS='7777 Ritter Rd.' where CUSTOMER_NUMBER=6;
1 row updated.
SQL> update customer_dim set customer_name='Distinguished Agencies' where CUSTOMER_NUMBER=7;
1 row updated.
SQL> INSERT INTO customer_dim VALUES(auto_incr.nextval, 8,'Subsidiaries','10000 Wetline Blvd.','1705
5','Pittsburgh','PA',to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO product_dim VALUES(pro_auto.nextval,4,'Keyboard','Peripheral',
2 to_date('2005-03-01','YYYY-MM-DD'),
3 TO_DATE('9999-12-31','YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO sales_order VALUES (22, 1, 1, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO sales_order VALUES (23, 2, 2, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 2000);
1 row created.
SQL> INSERT INTO sales_order VALUES (24, 3, 3, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 3000);
1 row created.
SQL> INSERT INTO sales_order VALUES (25, 4, 4, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO sales_order VALUES (26, 5, 2, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO sales_order VALUES (27, 6, 2, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 3000);
1 row created.
SQL> INSERT INTO sales_order VALUES (28, 7, 3, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 5000);
1 row created.
SQL> INSERT INTO sales_order VALUES (29, 8, 4, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 7000);
1 row created.
SQL> INSERT INTO sales_order VALUES (30, 1, 1, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 1000);
1 row created.
SQL> INSERT INTO sales_order VALUES (31, 2, 2, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 2000);
1 row created.
SQL> INSERT INTO sales_order VALUES (32, 3, 3, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 4000);
1 row created.
SQL> INSERT INTO sales_order VALUES (33, 4, 4, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 6000);
1 row created.
SQL> INSERT INTO sales_order VALUES (34, 5, 1, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 2500);
1 row created.
SQL> INSERT INTO sales_order VALUES (35, 6, 2, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 5000);
1 row created.
SQL> INSERT INTO sales_order VALUES (36, 7, 3, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 7500);
1 row created.
SQL> INSERT INTO sales_order VALUES (37, 8, 4, to_date('2007–03–01','yyyy-mm-dd'), to_date('2007–03–
01','yyyy-mm-dd'), 1000);
SQL Assignment Chapter 9 - Job
Scheduling
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////CHAPTER
- 9 JOB SCHEDULING////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SQL Assignment - (Chapter 10-11)
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////CHAPTER-10//////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////ADDITION OF COLUMN INTO CUSTOMER_DIM/////////////////////
SQL> ALTER TABLE customer_dim
2 ADD (shipping_address CHAR(50) ,
3 shipping_zip_code INT,
4 shipping_city CHAR (30),
5 shipping_state CHAR (2));
Table altered.
////////////ADDITION OF COLUMN ORDER_QUANTITY INTO SALES_ORDER//////////////
SQL> ALTER TABLE SALES_ORDER
2 ADD ORDER_QUANTITY INT;
Table altered.
///////////DELETION OF COLUMN FROM TABLE////////////////////////////
SQL> ALTER TABLE SALES_ORDER DROP COLUMN ORDER_QUANTITY;
Table altered.
////////////INSERTION OF DATA IN ORDER_DIM//////////////////////////
SQL> INSERT INTO ORDER_DIM(ORDER_SK,ORDER_NUMBER,EFFECTIVE_DATE,EXPIRY_DATE)
2 SELECT ORDER_AUTO.NEXTVAL,ORDER_NUMBER,ORDER_DATE,TO_DATE('9999-12-
31','YYYY-MM-DD')
3 FROM SALES_ORDER
4 WHERE ENTRY_DATE = CURRENT_DATE;
0 rows created.
////////////////INSERTION OF DATA INTO SALES_ORDER_FACT////////////////
SQL> INSERT INTO SALES_ORDER_FACT
2 SELECT ORDER_SK, CUSTOMER_SK,PRODUCT_SK,DATE_SK,ORDER_AMOUNT
3 FROM SALES_ORDER A,ORDER_DIM B,CUSTOMER_DIM C,PRODUCT_DIM D,DATE_DIM E
4 WHERE
5 A.ORDER_NUMBER = B.ORDER_NUMBER
6 AND A.CUSTOMER_NUMBER = C.CUSTOMER_NUMBER
7 AND A.ORDER_DATE >= C.EFFECTIVE_DATE
8 AND A.ORDER_DATE <= C.EXPIRY_DATE
9 AND A.PRODUCT_CODE= D.PRODUCT_CODE
10 AND A.ORDER_DATE >= D.EFFECTIVE_DATE
11 AND A.ORDER_DATE <= D.EXPIRY_DATE
12 AND A.ORDER_DATE = E.BOOKING_DATE
13 AND A.entry_date = CURRENT_DATE;
0 rows created.
////////////////INSERTION OF DATA INTO CUSTOMER_DIM//////////////////////
SQL> TRUNCATE TABLE CUSTOMER_DIM;
Table truncated.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 1,'Really Large
Customers','7500 Louise Dr.
','17050','Mechanicsburg','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'7500 Louise Dr.', '1
7050', 'Mechanicsburg', 'PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 2,'Small Stores','2500
Woodland St.','17055
','Pittsburgh','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'2500 Woodland St.','
17055','Pittsburgh','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 3,'Medium
Retailers','1111 Ritter Rd.','170
55','Pittsburgh','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'1111 Ritter Rd.','17
055','Pittsburgh','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 4,'Good
Companies','9500 Scott St.','17050'
,'Mechanicsburg','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'9500 Scott St.','170
50','Mechanicsburg','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 5,'Wonderful
Shops','3333 Rossmoyne Rd.','1
7050','Mechanicsburg','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'3333 Rossmoyne Rd.',
'17050','Mechanicsburg','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 6,'Loyal Clients',
'7070 Ritter Rd.', '1705
5','Pittsburgh','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'7070 Ritter Rd.', '1
7055','Pittsburgh','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 7,'Distinguished
Partners','9999 Scott St.'
,'17050','Mechanicsburg','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'9999 Scott St.','170
50','Mechanicsburg','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 8,'Subsidiaries',
'10000 Wetline Blvd.', '1
7055','Pittsburgh','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'10000 Wetline Blvd.'
, '17055','Pittsburgh','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 9,'Online
Distributors','2323 Louise Dr.','
17055','Pittsburgh','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'2323 Louise Dr.','17
055','Pittsburgh','PA');
1 row created.
//////////////////INSERTION OF DATA INTO SALES_ORDER//////////////////////
SQL> ALTER TABLE sales_order
2 ADD order_quantity INT
3 ;
Table altered.
SQL> INSERT INTO sales_order VALUES (38, 1, 1, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 1000,10);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (39, 2, 2, TO_DATE('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 2000,20);
1 row created.
SQL> INSERT INTO sales_order VALUES (40, 3, 3, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 4000,40);
1 row created.
SQL> INSERT INTO sales_order VALUES (41, 4, 4, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 6000,60);
1 row created.
SQL> INSERT INTO sales_order VALUES (42, 5, 1, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 2500,25);
1 row created.
SQL> INSERT INTO sales_order VALUES (43, 6, 2, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 5000,50);
1 row created.
SQL> INSERT INTO sales_order VALUES (44, 7, 3, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 7500,75);
1 row created.
SQL> INSERT INTO sales_order VALUES (45, 8, 4, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 1000,10);
1 row created.
SQL> INSERT INTO sales_order VALUES (46, 9, 1, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 1000,10);
1 row created.
////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////CHAPTER 11////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////ADDITION OF COLUMN IN DATE_DIM//////////////
SQL> ALTER TABLE date_dim
2 ADD promo_ind CHAR(1)
3 ;
Table altered.
////////////////CREATING PROMO_SCHEDULE_STG TABLE///////////////
SQL> CREATE TABLE PROMO_SCHEDULE_STG
2 (PROMO_CODE VARCHAR(3),
3 PROMO_NAME VARCHAR(20),
4 PROMO_START_DATE DATE,
5 PROMO_LAST_DATE DATE);
Table created.
SQL> insert into promo_schedule_stg values('SO','Special Offer',to_date('2007-
04-01','yyyy-mm-dd'),
to_date('2007-04-10','yyyy-mm-dd'));
1 row created.
SQL> insert into promo_schedule_stg values('DP','Disk Promotion',to_date('2007
-05-05','yyyy-mm-dd')
,to_date('2007-05-20','yyyy-mm-dd'));
1 row created.
SQL> insert into promo_schedule_stg values('MS','Month Special',to_date('2007-
06-01','yyyy-mm-dd'),
to_date('2007-06-30','yyyy-mm-dd'));
1 row created.
SQL> insert into promo_schedule_stg values('MS','Monitor Promotion',to_date
('2007-07-10','yyyy-mm-d
d'),to_date('2007-07-15','yyyy-mm-dd'));
1 row created.
SQL> insert into promo_schedule_stg values('BS','Back to School',to_date('2007
-08-10','yyyy-mm-dd')
,to_date('2007-08-30','yyyy-mm-dd'));
1 row created.
///////////UPDATING DATE_DIM DATA FROM PROMO_SCHEDULE_STG TABLE////////////
SQL> UPDATE DATE_DIM A SET A.PROMO_IND='Y'
2 WHERE A.BOOKING_DATE IN (SELECT B.PROMO_START_DATE
3 FROM PROMO_SCHEDULE_STG B WHERE
4 A.BOOKING_DATE >= B.PROMO_START_DATE
5 AND A.BOOKING_DATE <= B.PROMO_LAST_DATE);
0 rows updated.
///////////////////////////////////////////////CHAPTER-10//////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////ADDITION OF COLUMN INTO CUSTOMER_DIM/////////////////////
SQL> ALTER TABLE customer_dim
2 ADD (shipping_address CHAR(50) ,
3 shipping_zip_code INT,
4 shipping_city CHAR (30),
5 shipping_state CHAR (2));
Table altered.
////////////ADDITION OF COLUMN ORDER_QUANTITY INTO SALES_ORDER//////////////
SQL> ALTER TABLE SALES_ORDER
2 ADD ORDER_QUANTITY INT;
Table altered.
///////////DELETION OF COLUMN FROM TABLE////////////////////////////
SQL> ALTER TABLE SALES_ORDER DROP COLUMN ORDER_QUANTITY;
Table altered.
////////////INSERTION OF DATA IN ORDER_DIM//////////////////////////
SQL> INSERT INTO ORDER_DIM(ORDER_SK,ORDER_NUMBER,EFFECTIVE_DATE,EXPIRY_DATE)
2 SELECT ORDER_AUTO.NEXTVAL,ORDER_NUMBER,ORDER_DATE,TO_DATE('9999-12-
31','YYYY-MM-DD')
3 FROM SALES_ORDER
4 WHERE ENTRY_DATE = CURRENT_DATE;
0 rows created.
////////////////INSERTION OF DATA INTO SALES_ORDER_FACT////////////////
SQL> INSERT INTO SALES_ORDER_FACT
2 SELECT ORDER_SK, CUSTOMER_SK,PRODUCT_SK,DATE_SK,ORDER_AMOUNT
3 FROM SALES_ORDER A,ORDER_DIM B,CUSTOMER_DIM C,PRODUCT_DIM D,DATE_DIM E
4 WHERE
5 A.ORDER_NUMBER = B.ORDER_NUMBER
6 AND A.CUSTOMER_NUMBER = C.CUSTOMER_NUMBER
7 AND A.ORDER_DATE >= C.EFFECTIVE_DATE
8 AND A.ORDER_DATE <= C.EXPIRY_DATE
9 AND A.PRODUCT_CODE= D.PRODUCT_CODE
10 AND A.ORDER_DATE >= D.EFFECTIVE_DATE
11 AND A.ORDER_DATE <= D.EXPIRY_DATE
12 AND A.ORDER_DATE = E.BOOKING_DATE
13 AND A.entry_date = CURRENT_DATE;
0 rows created.
////////////////INSERTION OF DATA INTO CUSTOMER_DIM//////////////////////
SQL> TRUNCATE TABLE CUSTOMER_DIM;
Table truncated.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 1,'Really Large
Customers','7500 Louise Dr.
','17050','Mechanicsburg','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'7500 Louise Dr.', '1
7050', 'Mechanicsburg', 'PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 2,'Small Stores','2500
Woodland St.','17055
','Pittsburgh','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'2500 Woodland St.','
17055','Pittsburgh','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 3,'Medium
Retailers','1111 Ritter Rd.','170
55','Pittsburgh','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'1111 Ritter Rd.','17
055','Pittsburgh','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 4,'Good
Companies','9500 Scott St.','17050'
,'Mechanicsburg','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'9500 Scott St.','170
50','Mechanicsburg','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 5,'Wonderful
Shops','3333 Rossmoyne Rd.','1
7050','Mechanicsburg','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'3333 Rossmoyne Rd.',
'17050','Mechanicsburg','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 6,'Loyal Clients',
'7070 Ritter Rd.', '1705
5','Pittsburgh','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'7070 Ritter Rd.', '1
7055','Pittsburgh','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 7,'Distinguished
Partners','9999 Scott St.'
,'17050','Mechanicsburg','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'9999 Scott St.','170
50','Mechanicsburg','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 8,'Subsidiaries',
'10000 Wetline Blvd.', '1
7055','Pittsburgh','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'10000 Wetline Blvd.'
, '17055','Pittsburgh','PA');
1 row created.
SQL> INSERT INTO CUSTOMER_DIM VALUES (AUTO_INCR.NEXTVAL, 9,'Online
Distributors','2323 Louise Dr.','
17055','Pittsburgh','PA',
2 to_date('2005-03-01','YYYY-MM-DD'),to_date('9999-12-31','YYYY-MM-
DD'),'2323 Louise Dr.','17
055','Pittsburgh','PA');
1 row created.
//////////////////INSERTION OF DATA INTO SALES_ORDER//////////////////////
SQL> ALTER TABLE sales_order
2 ADD order_quantity INT
3 ;
Table altered.
SQL> INSERT INTO sales_order VALUES (38, 1, 1, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 1000,10);
1 row created.
SQL> INSERT INTO SALES_ORDER VALUES (39, 2, 2, TO_DATE('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 2000,20);
1 row created.
SQL> INSERT INTO sales_order VALUES (40, 3, 3, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 4000,40);
1 row created.
SQL> INSERT INTO sales_order VALUES (41, 4, 4, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 6000,60);
1 row created.
SQL> INSERT INTO sales_order VALUES (42, 5, 1, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 2500,25);
1 row created.
SQL> INSERT INTO sales_order VALUES (43, 6, 2, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 5000,50);
1 row created.
SQL> INSERT INTO sales_order VALUES (44, 7, 3, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 7500,75);
1 row created.
SQL> INSERT INTO sales_order VALUES (45, 8, 4, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 1000,10);
1 row created.
SQL> INSERT INTO sales_order VALUES (46, 9, 1, to_date('2007-03-02','yyyy-mm-
dd'), to_date('2007-03-
02','yyyy-mm-dd'), 1000,10);
1 row created.
////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////CHAPTER 11////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////ADDITION OF COLUMN IN DATE_DIM//////////////
SQL> ALTER TABLE date_dim
2 ADD promo_ind CHAR(1)
3 ;
Table altered.
////////////////CREATING PROMO_SCHEDULE_STG TABLE///////////////
SQL> CREATE TABLE PROMO_SCHEDULE_STG
2 (PROMO_CODE VARCHAR(3),
3 PROMO_NAME VARCHAR(20),
4 PROMO_START_DATE DATE,
5 PROMO_LAST_DATE DATE);
Table created.
SQL> insert into promo_schedule_stg values('SO','Special Offer',to_date('2007-
04-01','yyyy-mm-dd'),
to_date('2007-04-10','yyyy-mm-dd'));
1 row created.
SQL> insert into promo_schedule_stg values('DP','Disk Promotion',to_date('2007
-05-05','yyyy-mm-dd')
,to_date('2007-05-20','yyyy-mm-dd'));
1 row created.
SQL> insert into promo_schedule_stg values('MS','Month Special',to_date('2007-
06-01','yyyy-mm-dd'),
to_date('2007-06-30','yyyy-mm-dd'));
1 row created.
SQL> insert into promo_schedule_stg values('MS','Monitor Promotion',to_date
('2007-07-10','yyyy-mm-d
d'),to_date('2007-07-15','yyyy-mm-dd'));
1 row created.
SQL> insert into promo_schedule_stg values('BS','Back to School',to_date('2007
-08-10','yyyy-mm-dd')
,to_date('2007-08-30','yyyy-mm-dd'));
1 row created.
///////////UPDATING DATE_DIM DATA FROM PROMO_SCHEDULE_STG TABLE////////////
SQL> UPDATE DATE_DIM A SET A.PROMO_IND='Y'
2 WHERE A.BOOKING_DATE IN (SELECT B.PROMO_START_DATE
3 FROM PROMO_SCHEDULE_STG B WHERE
4 A.BOOKING_DATE >= B.PROMO_START_DATE
5 AND A.BOOKING_DATE <= B.PROMO_LAST_DATE);
0 rows updated.
