Rollup function in group by clause

Rollup function in group by clause

source: Need more

Create Table:
...............................................................................
create table Employee(
ID VARCHAR2(4 BYTE) NOT NULL,
First_Name VARCHAR2(10 BYTE),
Last_Name VARCHAR2(10 BYTE),
Start_Date DATE,
End_Date DATE,
Salary Number(8,2),
City VARCHAR2(10 BYTE),
Description VARCHAR2(15 BYTE)
);
.............................................................................
Insert values into table::
....................................................................................................................................
insert into Employee (ID, First_Name, Last_Name, Start_Date,End_Date,Salary,City,Description)
values ('01','Jason','Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer');



insert into Employee (ID, First_Name, Last_Name, Start_Date,End_Date,Salary,City,Description)
values ('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester');



insert into Employee (ID, First_Name, Last_Name, Start_Date,End_Date,Salary,City,Description)
values ('03','James','Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester');



insert into Employee (ID, First_Name, Last_Name, Start_Date,End_Date,Salary,City,Description)
values ('04','Celia','Rice',to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager');


insert into Employee (ID, First_Name, Last_Name, Start_Date,End_Date,Salary,City,Description)
values ('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester');


insert into Employee (ID, First_Name, Last_Name, Start_Date,End_Date,Salary,City,Description)
values ('06','Linda','Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester');


insert into Employee (ID, First_Name, Last_Name, Start_Date,End_Date,Salary,City,Description)
values ('07','David','Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York', 'Manager');


insert into Employee (ID, First_Name, Last_Name, Start_Date,End_Date,Salary,City,Description)
values ('08','James','Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester');
............................................................................................................................................

Operation :
...........................................................
1) Seclect * from employee;
...........................................................
Output:


ID FIRST_NAME LAST_NAME START_DATE END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- ----------- ----------- ---------- ---------- ---------------
01 Jason Martin 1996-JUL-25 2006-JUL-25 1234.56 Toronto Programmer
02 Alison Mathews 1976-MAR-21 1986-FEB-21 6661.78 Vancouver Tester
03 James Smith 1978-DEC-12 1990-MAR-15 6544.78 Vancouver Tester
04 Celia Rice 1982-OCT-24 1999-APR-21 2344.78 Vancouver Manager
05 Robert Black 1984-JAN-15 1998-AUG-08 2334.78 Vancouver Tester
06 Linda Green 1987-JUL-30 1996-JAN-04 4322.78 New York Tester
07 David Larry 1990-DEC-31 1998-FEB-12 7897.78 New York Manager
08 James Cat 1996-SEP-17 2002-APR-15 1232.78 Vancouver Tester

8 rows selected.

ROLLUP operation:
................................................................
SELECT city, SUM(salary)
FROM employee
GROUP BY ROLLUP(city) ;
..................................................................
Output :

CITY SUM(SALARY)
---------- -----------
New York 12220.56
Toronto 1234.56
Vancouver 19118.9
32574.02


To view one record from table:
.......................................................................................................................................................
SELECT ID, First_Name, Last_Name, Start_Date,End_Date,Salary,City,Description
FROM employee
WHERE ID=2;
...........................................................................................................................................................

C# Tips(Just Basic)

# What is Class, Object, Properties ?


@ Just follow the example:

Person - Class
Utpal, Tirtha (Just any name) - Object
Age, Height, Hair color - Properties

(( Any Person may be Utpal or Tirtha . And they have following properties Like as age, height, hair color etc))

@ How can you apply class , object , properties in C# (Just in console mode)

Remember:

1) Create a Class (Class include variable , methods etc [properties] )
2) Create a object by using class name.
[ Syntex: class name object name = new class name() ; ]
3) Access all properties, methods by using object
[ Syntex: object name . variable ; object name . method ; ]



Tips: Just open C# and apply this code in console mode ( you can copy the code & place it)

Example 1:

using System;

class utpal
{

static void Main(string[] args)
{
Console.WriteLine("A simple Console Program");
Console.Read();

}
}


Example 2 :

using System;

class Utpal
{

static void Main(string[] args)
{
person tirtha1 = new person();
tirtha1.info();
Console.Read();


}
}
class person
{
int age=10;
string name= "Tirtha ";

public void info()
{

Console.WriteLine(age);
Console.WriteLine(name);
}

}

ORACLE JOIN

Oracle Join


> Join Demo Tables

CREATE TABLE person (
person_id NUMBER(10),
first_name VARCHAR2(25) NOT NULL,
last_name VARCHAR2(25) NOT NULL,
title_1 VARCHAR2(5),
title_2 VARCHAR2(5))
PCTFREE 20;

ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
USING INDEX
PCTFREE 5;

CREATE TABLE person_role (
role_id VARCHAR2(1),
role_name VARCHAR2(20) NOT NULL);

ALTER TABLE person_role
ADD CONSTRAINT pk_role
PRIMARY KEY (role_id)
USING INDEX
PCTFREE 5;

CREATE TABLE person_role_ie (
person_role_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
role_id VARCHAR2(1) NOT NULL);

ALTER TABLE person_role_ie
ADD CONSTRAINT pk_person_role_ie
PRIMARY KEY (person_role_id)
USING INDEX
PCTFREE 5;

CREATE TABLE title (
title_abbrev VARCHAR2(5),
title_name VARCHAR2(20))
PCTFREE 0;

ALTER TABLE title
ADD CONSTRAINT pk_title
PRIMARY KEY (title_abbrev)
USING INDEX
PCTFREE 0;

ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_person
FOREIGN KEY (person_id)
REFERENCES person(person_id);

ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_role
FOREIGN KEY (role_id)
REFERENCES person_role(role_id);

ALTER TABLE person
ADD CONSTRAINT fk_person_title1
FOREIGN KEY (title_1)
REFERENCES title(title_abbrev);

ALTER TABLE person
ADD CONSTRAINT fk_person_title2
FOREIGN KEY (title_2)
REFERENCES title(title_abbrev);


Need more on : http://psoug.org/reference/joins.html

Objects & Classes in C#

source: http://www.csharphelp.com/2007/03/objects-classes-in-c/

In this article we will understand some of the concepts of object-oriented programming in C# like objects and classes. To read this article you must have C# programming basics.

NOTE: read the whole article because there aresome concepts you may will not get the best of it until you finish the article.And we will revisit all the concepts more than one time when I see it’sappropriate in future articles so don’t worry at all.

Introduction:

OOP stands for Object-Oriented Programming. OOP isrelatively a new way to program computer applications. Before OOP programmersused to creating computer applications using procedural-programming (orstructure-programming) but when OOP solved a lot of the problems of theprocedural-programming so almost all of the programmers and developers beganusing OOP languages. In procedural- programming all the program functionalitywritten in a few modules of code or maybe one module (depending on the program)and these modules depend on one another and maybe if you change a line of codeyou will have to rewrite the whole module again and maybe the whole program butin Object-Oriented Programming programmers write independent parts of a programcalled classes each class represent a part of the program functionality andthese classes can be assembled to form a program and when you need to changesome of the program functionality all what you have to do is to replace thetarget class which may contain a problem. So in OOP applications create by theuse of classes and these applications can contain any number of classes. Thatwill get us to discuss the Class and Object concept.

Classes and objects:

You may find it not easy to understand the class and objectstory but I will try to do my best explaining it. Actually the class and objectconcept is related to each other and some beginners don’t care aboutunderstanding it clear so I think they will have a hard times learning C#.

Object-Oriented concept takes the most of its functionalityfrom the real-life concepts. For example, I will discuss the concept of Classesand Objects of the world first and then you will understand the computer’sClasses and Objects before I even write anything about it.

World’s Classes and Objects:

In our world we have a classes and objects for thoseclasses. Everything in our world considered to be an object. For example,people are objects, animals are objects too, minerals are objects, everythingin the world are objects. Easy right ? but what about classes. In our world wehave to differentiate between objects that we are living with. So we mustunderstand that there are a classifications (this is how they get the nameand the concepts of the Class) for all of those objects. For example, I’man object, David is object too, Maria is another object so we are from apeople class (or type). I have a dog called Ricky so it’s an object, Myfriend’s dog called Doby is also an object so they are from a Dogs class (ortype). A third example, I have a computer Pentium III this is object, Myfriend have a computer Pentium IIII so this is another object and they are froma Computers class (or type). Now I think you got the concept of theClass and Object but let me crystallize it for you. In our world we have aclassifications for objects and every object must be from some classification.so a Class is a way for describing some properties and functionalities orbehaviors of a group of objects. In other words, The class considered to be atemplate for some objects. So maybe I will create a class called person so thisis a template of the functionality and the properties of persons. I explainedit by more than a way so wait until you see the first example and I think youwill grasp it completely.

Computer’s Classes and Objects:

Computer’s Classes discussion is similar to what you graspfrom the last section with some modifications to become computerized.

A C# Class Considered being the primary building block ofthe language. What I mean by the primary building block of the language is thatevery time you work with C# you will create Classes to form a program. We useClasses as a template to put the properties and functionalities or behaviors inone building block for some group of objects and after that we use thattemplate to create the objects we need. For example, We need to have personsobjects in our program so the first thing to do here is to create a Classcalled Person that contains all the functionalities or behaviors and propertiesof any person and after that we will use that Class or template to create asmany objects as we need. Creating object of a specific class type called”instance of the class”. Don’t worry if you didn’t grasp it 100% anddon’t worry if you don’t know what’s the Class and Object’s properties andfunctionalities or behaviors because we still in the beginning and until now Ididn’t give any code examples. So let’s take a brief of what’s the Class andwhat’s an object ?

The Class : Is abuilding block that contains the properties and functionalities that
describesome group of objects, We can create a class Person that contains:

1- The properties of any normal person on the earth like :Hair Color, Age, Height, Weight, Eyes Color.

2- The functionalities or behaviors of any normal person onthe earth like : Drink water, Eat, Go to the work and later we will see how wecan implement the functionalities or behaviors and properties.

There are 2 kinds of classes : The built-it classes thatcome with the .NET Framework and called Framework Class Library. And theprogrammer defined-classes which we create it.

The class contains data (in the form of variables andproperties) and behaviors (in the form of methods to process these data). Wewill understand this concept more later in the article.

When we declare a variable in a class we call it membervariables or instance variables. The name instance come from the fact that whenwe create an object we instance a class to create that object so instance of aclass means object of that class and instance variable means variable thatexists in that class.

The Object : It’s objectof some classification (or class, or type. All means the same thing) and whenyou create the object you can specify the properties of that object. What Imean here is me as an object can have a different properties (Hair Color, Age,Height, Weight) of you as another object. For example, I have a brown eyes andyou have a green eyes so when I create 2 objects I will specify a browncolor for my object’s Eyes Color property and I will specify a green color foryour object’s Eyes Color property.

So to complete my introduction to Classes we must discussProperties and Variables.

Properties and Variables:

Variables declared in a class store the data for eachinstance, What that means ? means that when you instantiate this class (thatis, When you create an object of this class) the object will allocate a memorylocations to store the data of its variables. Let’s take an example tounderstand it well.

class Person
{
public int Age;
public string HairColor;
}

This is our simple class which contains 2 variables. Don’tworry about public keyword now because we willtalk about it later . Now we will instantiate this class (that is, When youcreate an object of this class).

static void Main(string[]args)
{
Person Michael = newPerson();
Person Mary = new Person();

// Specify some values for the instance variables
Michael.Age = 20;
Michael.HairColor = “Brown”;
Mary.Age = 25;
Mary.HairColor = “Black”;
// print the console’s screen someof the variable’s values
Console.WriteLine(“Michael’s age = {0}, and Mary’s age= {1}”,Michael.Age,
Mary.Age);
Console.ReadLine();
}

So we begin our Main method by creating 2 objects of Persontype. After creating the 2 objects we initialize the instance variables forobject Michael and then for object Mary. Finally we print some values to theconsole. here when you create Michael object C# compiler allocate a memorylocation for the 2 instance variables to put the values there. Also the samething with Mary object the compiler will create 2 variables in the memory forMary object. So each object now contains a different data. Note that wedirectly accessed the variables and we put any values we want, Right ? so maybesomeone doesn’t like me will put in my object’s variable Age value of 120 yearsso I will not get any kind of jobs. But wait there are a solution for thisproblem. We will use properties.

Properties:

Properties is a way to access the variables of the class ina secure manner. Let’s see the same example using properties.

class Person
{
private int age;
private string hairColor;
public int Age
{
get
{
returnage;
}
set
{
if(value <= 65 && value >= 18)
{
age = value;
}
else
age = 18;
}
}
public string HairColor
{
get
{
return hairColor;
}
set
{
hairColor = value;
}
}
}

I made some modifications but please just care about the new2 properties that I created it here. So the property consists of 2 accessor.The get accessor which is responsibleof retrieving the variable value, And the set accessor which is responsible of modifying the variable’svalue. So The get accessor code is very simple wejust use the keyword return withthe variable name to return its value. so the following code:

get
{
return hairColor;
}

return the value stored in hairColor.

Note :the keyword value isa reserved keyword by C# (that is, reserved keywords means that these keywordsown only by C# and you can’t create it for other purpose. For example, Youcan’t create a variable called value .Ifyou did that C# compiler will generate an error and to make things easierVisual Studio.NET will color the reserved keywords to blue.)

Let’s put this code at work and after that discuss theset accessor.

static void Main(string[]args)
{
Person Michael = newPerson();
Person Mary = new Person()
;

// Specify some values for the instance variables
Michael.Age = 20;
Michael.HairColor = “Brown”;
Mary.Age = 25;
Mary.HairColor = “Black”;

// print the console’s screen some of the variable’s values
Console.WriteLine(“Michael’s age = {0}, and Mary’s age= {1}”,Michael.Age,
Mary.Age);
Console.ReadLine();
}

Here I created the same objects from last example themodifications that I used only properties to access the variable instead ofaccess it directly. Look at the following line of code

Michael.Age = 20;

When you assign a value to the property like that C# willuse the set accessor. The great thing withthe set accessor is that we can controlthe assigned value and test it and maybe change to in some cases. When youassign a value to a property C# change the value in a variable and you canaccess the variable’s value using the reserved keyword value exactly as I did in the example. Let’s see it again here.

set
{
if(value <= 65 && value >= 18)
{
age = value;
}
else
age = 18;
}

Here in the code I used if statement to test the assigned value because for some reasonI want any object of type Person to be in age between 18 and 65. Here I testthe value and if it in the range then simply I will store it in the variableage and it it’s not in the range I will put 18 as a value to age. It was just asimple example for the properties but there is a complete article about propertiessoon.

How we create objects and classes ?

We create classes by define it like that:

using the keyword class followedby the class name like that

class Person

then we open a left brace “{” and after we writeour methods and properties we close it by a right brace “}”. That’show we create a class. Let’s see how we create an instance of that class.

In the same way as we declare a variable of type int we create an object variable of Person type with some modifications:

int age;
Person Michael = new Person();

In the first line of code we specified integer variablecalled age. In the second line we specified first the type of Object we need tocreate followed by the object’s name followed by a reserved operator called new and we end by typing the class name again followed byparenthesis “()”.

Let’s understand it step-by-step. Specifying the class nameat the beginning tell the C# Compiler to allocate a memory location for thattype (C# compiler knows all the variables and properties and methods of theclass so it will allocate the right amount of memory). Then we followed theclass name by out object variable name that we want it. The rest of the code”=new Person();” callthe object’s constructor. We will talk about constructor later but for nowunderstand that the constructor is a way to initialize your object’s variablewhile you are creating it not after you create it. For example, The Michaelobject we created it in the last section can be written as following :

Person Michael = new Person(20, “Brown”);

here I specified the variable’s values in the parameter listso I initialized the variables while I’m creating the object. But for this codeto work we will need to specify the constructor in the Person class and I willnot do that here because constructor section will come in later articles. Ithink you got a good introduction about Classes and Objects not I will completein in my next article and I will talk about constructors and building blockscoping. I hope you got a new thing from my article.

---

About the Author:

Michael Youssef is 20 years old and he’sworking with Microsoft technologies since he was 17. He is working with VB,VB.NET, C#,ASP, ASP. NET, XML Web services, COM+, SQL Server 2000, Windows 2000Server, Active Directory, Networks design. He is MCSD.NET, MCDBA, MCSE, MCSAand he’s working now as a .NET Trainer in Egypt. Michael can be reached atmichaellabibat@hotmail.com .

Star schema in Oracle

Star schema in Oracle

SET SERVEROUT ON;

spool create_sample_star_schema.log;

/* Drop the Sample Tables */
DROP TABLE batting_fact;
DROP TABLE game_dim;
DROP SEQUENCE game_dim_seq;
DROP DIMENSION game_dim;
DROP TABLE team_dim;
DROP SEQUENCE team_dim_seq;
DROP DIMENSION team_dim;
DROP TABLE player_dim;
DROP SEQUENCE player_dim_seq;
DROP DIMENSION player_dim;
purge recyclebin;

/* Create the Sample Dimension Tables and Dimensions */

CREATE TABLE game_dim
(game_id NUMBER(30) NOT NULL,
game_no NUMBER(3) NOT NULL,
game_day DATE NOT NULL ,
game_month VARCHAR2(15) NOT NULL,
game_season NUMBER(4) NOT NULL);

CREATE SEQUENCE game_dim_seq;

CREATE DIMENSION game_dim
LEVEL game IS game_dim.game_no
LEVEL game_day IS game_dim.game_day
LEVEL game_month IS game_dim.game_month
LEVEL game_season IS game_dim.game_season
HIERARCHY game_rollup (
game CHILD OF
game_day CHILD OF
game_month CHILD OF
game_season);

DECLARE
lv_start_date DATE := TO_DATE ('01-APR-2007', 'DD-MON-YYYY');
lv_tmp_date DATE;
BEGIN
FOR j IN 1 .. 3
LOOP
lv_tmp_date := lv_start_date;

FOR i IN 1 .. 365
LOOP
INSERT INTO game_dim
VALUES (game_dim_seq.NEXTVAL, i, TRUNC (lv_tmp_date),
TRIM (TO_CHAR (lv_tmp_date, 'Month')),
TO_CHAR (lv_tmp_date, 'YYYY'));

COMMIT;
lv_tmp_date := TRUNC (lv_tmp_date) + 1;
END LOOP;

lv_start_date := TRUNC (lv_start_date) + 365;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
/

CREATE UNIQUE INDEX game_dim_pk ON game_dim(game_id);
ALTER TABLE game_dim ADD CONSTRAINT game_dim_pk PRIMARY KEY (game_id) RELY ENABLE NOVALIDATE;
CREATE BITMAP INDEX game_dim_game_no_bidx ON game_dim(game_no);
CREATE BITMAP INDEX game_dim_game_day_bidx ON game_dim(game_day);
CREATE BITMAP INDEX game_dim_game_month_bidx ON game_dim(game_month);
CREATE BITMAP INDEX game_dim_game_season_bidx ON game_dim(game_season);

CREATE TABLE team_dim
(team_id NUMBER(30) NOT NULL,
team_name VARCHAR2(20) NOT NULL,
team_division VARCHAR2(20) NOT NULL ,
team_league VARCHAR2(20) NOT NULL);

CREATE SEQUENCE team_dim_seq;

CREATE DIMENSION team_dim
LEVEL team IS team_dim.team_name
LEVEL team_division IS team_dim.team_division
LEVEL team_league IS team_dim.team_league
HIERARCHY team_rollup (
team CHILD OF
team_division CHILD OF
team_league );

INSERT INTO team_dim
VALUES (team_dim_seq.NEXTVAL, 'CHICAGO CUBS', 'MIDWEST', 'NATIONAL');
INSERT INTO team_dim
VALUES (team_dim_seq.NEXTVAL, 'HOUSTON ASTROS', 'MIDWEST', 'NATIONAL');
INSERT INTO team_dim
VALUES (team_dim_seq.NEXTVAL, 'ST. LOUIS CARDINALS', 'MIDWEST',
'NATIONAL');
INSERT INTO team_dim
VALUES (team_dim_seq.NEXTVAL, 'BOSTON RED SOX', 'EAST', 'AMERICAN');
INSERT INTO team_dim
VALUES (team_dim_seq.NEXTVAL, 'NEW YORK', 'EAST', 'AMERICAN');
COMMIT ;

CREATE UNIQUE INDEX team_dim_pk ON team_dim(team_id);
CREATE UNIQUE INDEX team_dim_uk ON team_dim(team_name,team_division,team_league);
ALTER TABLE team_dim ADD CONSTRAINT team_dim_pk PRIMARY KEY (team_id) RELY ENABLE NOVALIDATE;
ALTER TABLE team_dim ADD CONSTRAINT team_dim_uk UNIQUE (team_name,team_division,team_league) USING INDEX;
CREATE BITMAP INDEX team_dim_team_name_bidx ON team_dim(team_name);
CREATE BITMAP INDEX team_dim_team_division_bidx ON team_dim(team_division);
CREATE BITMAP INDEX team_dim_team_league_bidx ON team_dim(team_league);

CREATE TABLE player_dim
(player_id NUMBER(30) NOT NULL,
player_fname VARCHAR2(30) NOT NULL,
player_lname VARCHAR2 (30) NOT NULL ,
player_name VARCHAR2(65) NOT NULL,
player_bday DATE NOT NULL,
player_start_date DATE NOT NULL,
player_retire_date DATE);

CREATE SEQUENCE player_dim_seq;

CREATE DIMENSION player_dim
LEVEL player IS player_dim.player_id
ATTRIBUTE player DETERMINES
(player_dim.player_fname,
player_dim.player_lname,
player_dim.player_name,
player_dim.player_bday,
player_dim.player_start_date,
player_dim.player_retire_date);

INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Ryne', 'Sandberg', 'Ryne Sandberg',
TO_DATE ('23-JAN-1945', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1982', 'DD-MON-YYYY'),
TO_DATE ('23-AUG-2001', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'James', 'Colestock', 'James Colestock',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Jim', 'Colestock', 'Jim Colestock',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Jaime', 'Colestock', 'Jaime Colestock',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Rob', 'Colestock', 'Rob Colestock',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Larry', 'Colestock', 'Larry Colestock',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Bob', 'Colestock', 'Bob Colestock',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Bob', 'Dobalina', 'Bob Dobalina',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
COMMIT ;

CREATE UNIQUE INDEX player_dim_pk ON player_dim(player_id);
CREATE UNIQUE INDEX player_dim_uk ON player_dim(player_name,player_bday, player_start_date, player_retire_date);
ALTER TABLE player_dim ADD CONSTRAINT player_dim_pk PRIMARY KEY (player_id) RELY ENABLE NOVALIDATE;
ALTER TABLE player_dim ADD CONSTRAINT player_dim_uk UNIQUE (player_name,player_bday, player_start_date, player_retire_date) USING INDEX;
CREATE BITMAP INDEX player_dim_player_fname_bidx ON player_dim(player_fname);
CREATE BITMAP INDEX player_dim_player_lname_bidx ON player_dim(player_lname);
CREATE BITMAP INDEX player_dim_player_name_bidx ON player_dim(player_name);
CREATE BITMAP INDEX player_dim_player_bday_bidx ON player_dim(player_bday);
CREATE BITMAP INDEX player_dim_player_sdate_bidx ON player_dim(player_start_date);
CREATE BITMAP INDEX player_dim_player_rdate_bidx ON player_dim(player_retire_date);

/* Create the Sample Fact Table */

CREATE TABLE batting_fact
(
game_id NUMBER(30) NOT NULL,
player_id NUMBER(30) NOT NULL,
team_id NUMBER(30) NOT NULL,
at_bats NUMBER(2) DEFAULT 0 NOT NULL,
walks NUMBER(2) DEFAULT 0 NOT NULL,
strike_outs NUMBER(2) DEFAULT 0 NOT NULL,
"1B" NUMBER(2) DEFAULT 0 NOT NULL,
"2B" NUMBER(2) DEFAULT 0 NOT NULL,
"3B" NUMBER(2) DEFAULT 0 NOT NULL,
hrs NUMBER(2) DEFAULT 0 NOT NULL
);

INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 5, 1, 1, 1, 1, 1, 0
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'CHICAGO CUBS' AND pd.player_name = 'Ryne Sandberg';
COMMIT ;

INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 4, 0, 0, 1, 1, 1, 1
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'CHICAGO CUBS' AND pd.player_name = 'James Colestock';
COMMIT ;

INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 4, 0, 0, 1, 1, 1, 1
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'CHICAGO CUBS' AND pd.player_name = 'Jim Colestock';
COMMIT ;

INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 3, 0, 0, 0, 1, 1, 1
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'BOSTON RED SOX' AND pd.player_name = 'Jaime Colestock';
COMMIT ;
INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 2, 0, 0, 0, 0, 1, 1
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'BOSTON RED SOX' AND pd.player_name = 'Rob Colestock';
COMMIT ;
INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 2, 0, 0, 0, 0, 1, 1
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'CHICAGO CUBS' AND pd.player_name = 'Larry Colestock';
COMMIT ;
INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 3, 0, 0, 0, 1, 1, 1
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'HOUSTON ASTROS' AND pd.player_name = 'Bob Colestock';
COMMIT ;
INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 5, 0, 0, 0, 1, 1, 3
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'HOUSTON ASTROS' AND pd.player_name = 'Bob Dobalina';
COMMIT ;

CREATE UNIQUE INDEX batting_fact_pk ON batting_fact(game_id,player_id);
ALTER TABLE batting_fact ADD CONSTRAINT batting_fact_pk PRIMARY KEY (game_id,player_id) RELY ENABLE NOVALIDATE;
CREATE BITMAP INDEX batting_fact_gmid_fk_bidx ON batting_fact(game_id);
CREATE BITMAP INDEX batting_fact_plyrid_fk_bidx ON batting_fact(player_id);
CREATE BITMAP INDEX batting_fact_teamid_fk_bidx ON batting_fact(team_id);
CREATE BITMAP INDEX batting_fact_at_bats_bidx ON batting_fact(at_bats);
CREATE BITMAP INDEX batting_fact_walks_bidx ON batting_fact(walks);
CREATE BITMAP INDEX batting_fact_so_bidx ON batting_fact(strike_outs);
CREATE BITMAP INDEX batting_fact_1b_bidx ON batting_fact("1B");
CREATE BITMAP INDEX batting_fact_2b_bidx ON batting_fact("2B");
CREATE BITMAP INDEX batting_fact_3b_bidx ON batting_fact("3B");
CREATE BITMAP INDEX batting_fact_hrs_bidx ON batting_fact(hrs);

ALTER TABLE batting_fact ADD (
CONSTRAINT batting_fact_gmid_fk
FOREIGN KEY (game_id)
REFERENCES game_dim (game_id) RELY DISABLE NOVALIDATE);

ALTER TABLE batting_fact ADD (
CONSTRAINT batting_fact_plyrid_fk
FOREIGN KEY (player_id)
REFERENCES player_dim (player_id) RELY DISABLE NOVALIDATE);

ALTER TABLE batting_fact ADD (
CONSTRAINT batting_fact_teamid_fk
FOREIGN KEY (team_id)
REFERENCES team_dim (team_id) RELY DISABLE NOVALIDATE);

/* Analyze the Tables */
EXEC dbms_stats.gather_schema_stats(ownname=>'SH',options=>'GATHER EMPTY',estimate_percent=>.001, degree=>2, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 100');

/* Query the sizes of the tables */

select segment_name, round(sum(bytes)/1024/1024) "MB" from user_segments
where segment_name like ('BATTING%')
or segment_name like ('GAME%')
or segment_name like ('PLAYER%')
or segment_name like ('TEAM%')
group by segment_name
order by 2 desc;

spool off;