Oracle: UNION, MINUS, INTERSECTION

1. SELECT ename,sal,comm FROM emp
   where ename like 'A%'
   UNION
   SELECT ename,sal,comm FROM emp
   where ename like 'E%'


2. SELECT ename,sal,comm FROM emp
   where ename like 'A%'
   UNION ALL
   SELECT ename,sal,comm FROM emp
   where ename like 'A%'


3. SELECT ename,sal,comm FROM emp
   where ename like 'E%'
   MINUS
   SELECT ename,sal,comm FROM emp
   where ename like 'E%'
   AND Comm IS NULL


4. SELECT ename,sal,comm FROM emp
   where ename like 'A%'
   INTERSECTION
   SELECT ename,sal,comm FROM emp
   where ename like 'A%'
   and sal < 2000

Oracle: Group Functions, Group Clause, Having Clause:

1. SELECT COUNT(*) FROM emp;

2. SELECT COUNT(hiredate) FROM emp;

3. SELECT COUNT(2) FROM  emp;

4. SELECT SUM(sal) FROM  emp;

5. SELECT AVG(sal) FROM emp;

6. SELECT MIN(sal) FROM emp;

7. SELECT MAX(sal) FROM emp;

8. SELECT STDDEV(sal) FROM emp;

9. SELECT VARIANCE(sal) FROM emp;

10. SELECT COUNT(*) FROM emp
      WHERE SAL < 4000;

12. SELECT COUNT(*) FROM emp
      WHERE HIREDATE > '11-Apr-1990';

13. SELECT SUM(sal) FROM emp
      WHERE ename LIKE'%A%';

14. SELECT  MAX(AMOUNT) FROM  ORDER
      WHERE SNUM IN(1002,1007)

Oracle: Sub Queries

1. Single Line SubQueries
 SELECT * FROM EMP
 WHERE DEPTNO  = (SELECT DEPTNO
                                       FROM DEPT
                                    WHERE  DNAME = 'OPERATION')



2.Multi Line SubQueries 
1. SELECT * FROM EMP
 WHERE DEPTNO  IN (SELECT DEPTNO
                                       FROM DEPT
                                    WHERE  DNAME LIKE  'A%')



2. SELECT * FROM DEPT
 WHERE DEPTNO  IN (SELECT DEPTNO
                                       FROM EMP
                                    WHERE  ENAME LIKE  '%E%')

Oracle: DDL Statement

5. Alter, Drop, Delete, Update, Commit, Rollback

1.  DELETE  FROM  player
     WHERE sal BETWEEN 1 AND 100;

2. DELETE  FROM  player
     WHERE sal IN(1000,2000);

3.  ROLLBACK;

4.  COMMIT;

5.  DROP TABLE    TEST_0

6.  ALTER TABLE test_0
     ADD   add_name  VARCHAR2(30);

7.  ALTER TABLE test_0
     MODIFY add_name  VARCHAR2(20);

8.  ALTER TABLE test_0
     DROP COLUMN  add_name ;

9.  RENAME TEST_0 TO new_Table;

10. TRUNCATE TABLE test_0;

11. UPDATE test_0
      SET   add_name =  'Your Name'
      WHERE ROLLNO = 101;

12. UPDATE test_0
       SET   add_name =  'Your Name'

Oracle: Date Function

4. Date Function

1. SELECT SYSDATE FROM DUAL.;

2. SELECT TO_CHAR(SYSDATE  ,'DD:MON:YY HH:MI:SS' )  FROM DUAL.;

3. SELECT TO_CHAR(SYSDATE ,'DD/MON/YY HH:MI:SS' )  FROM DUAL.;

4. SELECT TO_CHAR(SYSDATE,'DD/MM/YY HH:MI:SS') FROM DUAL.;

5. SELECT TO_CHAR(SYSDATE ,'DD/MONTH/YYYY' ) FROM DUAL.;

6. SELECT TO_CHAR(SYSDATE  ,'MONTH' ) FROM DUAL.;

7. SELECT TO_CHAR(SYSDATE ,'YYYY' ) FROM DUAL.;

8. SELECT TO_CHAR(SYSDATE  ,'DD' ) FROM DUAL.;

9. SELECT TO_CHAR(SYSDATE,'Day') FROM DUAL.;

10. SELECT TO_CHAR(SYSDATE ,'DAY') FROM DUAL.;

11. SELECT TO_CHAR(SYSDATE ,'DAY MONTH YEAR') FROM DUAL.;

11. SELECT
    TO_CHAR(SYSDATE ,'DAY MONTH YEAR')
     FROM DUAL.;

12. SELECT ENAME
                      ,HIREDATE
                      ,ADD_MONTHS(HIREDATE,3)
         FROM EMP;

13. SELECT ENAME
                      ,HIREDATE
                      ,ADD_MONTHS(HIREDATE,-3)
                      ,TO_CHAR(ADD_MONTHS(HIREDATE,-3)
                                          ,'Month')
         FROM EMP;

14. SELECT ENAME
                      ,HIREDATE
                      ,LAST_DAY(HIREDATE)
                      ,LAST_DAY('11-APR-2015')
         FROM EMP;

15. SELECT ENAME
                      ,HIREDATE
                      ,MONTHS_BETWEEN('11-APR-2015','31-SEP-2015')         FROM EMP;

16. SELECT  NEXT_DAY('31-JAN-2015','SATURDAY')
         FROM  DUAL;

17. SELECT  NEXT_DAY('31-JAN-2015','SATURDAY') + 10
         FROM  DUAL;

18. SELECT  SYSDATE - 5
         FROM  DUAL;

Orace: Number Built Function

3. Number Function

1. SELECT ABS(-56) FROM DUAL;

2. SELECT ABS(+56) FROM DUAL;

3. SELECT MOD(56,6) FROM DUAL;

4. SELECT MOD(17,4) FROM DUAL;

5. SELECT MOD(23,2) FROM DUAL;

6. SELECT POWER(2,2) FROM DUAL;

7. SELECT POWER(5,3) FROM DUAL;

8. SELECT POWER(8,8) FROM DUAL;

9. SELECT SQRT(64) FROM DUAL;

10. SELECT SQRT(8) FROM DUAL;

11. SELECT SQRT(23) FROM DUAL;

12. SELECT SIGN(-98) FROM DUAL;

13. SELECT SIGN(98) FROM DUAL;

14. SELECT CEIL(86.50) FROM DUAL;

15. SELECT CEIL(86.23) FROM DUAL;

16. SELECT CEIL(86.75) FROM DUAL;

17. SELECT FLOOR(86.50)FROM DUAL;

18. SELECT FLOOR(86.23) FROM DUAL;

19. SELECT FLOOR(86.75) FROM DUAL;

20. SELECT ROUND(86.50)FROM DUAL;

21. SELECT ROUND(86.23) FROM DUAL;

22. SELECT ROUND(86.75) FROM DUAL;

23. SELECT TRUNC(86.50) FROM DUAL;

24. SELECT TRUNC(86.23) FROM DUAL;

25. SELECT TRUNC(86.75) FROM DUAL;

Oracle: String Function

2. String Function
1. SELECT  NAME
               ,LOWER(NAME)
               ,UPPER(NAME)
               ,INITCAP(NAME)
               ,ASCII(NAME)
               ,LENGTH(NAME)
               , CONCAT(LOWER(NAME),UPPER(NAME))
               ,LPAD(NAME,20,'*')
               ,RPAD(NAME,20,'*')
               ,LTRIM(NAME,'A')
               ,RTRIM(NAME,'E')
               ,REPLACE(NAME,'EE','I')
               ,SUBSTR(NAME,1,3)
               ,SUBSTR(NAME,2,5)
               ,SUBSTR(NAME,2,2)
FROM emp;

2. SELECT    CHR(34)  FROM DUAL;

1. LOWER (NAME)

;g bufcYV vksjsdy QaD’ku gS A bldk mi;ksx fLVªax dks vaxzsth ds dsfiVy ysVjksa dks Leky ysVj esa ifjofrZr djrk gSA

Oracle: IN, NOT IN, BETWEEN, NOT BETWEEN, Range Searching Query:

1. SELECT ENAME, SAL FROM EMP
WHERE  SAL  NOT BETWEEN 1000 AND 3500;

2. SELECT ENAME, SAL FROM EMP
WHERE  SAL NOT  IN(1000,3500,1100,1500);

3. SELECT ENAME, SAL FROM EMP
WHERE  SAL NOT  IN(1000,3500,1100,1500)
ORDER BY ENAME, SAL ASC;

4. SELECT ENAME, SAL FROM EMP
WHERE  SAL NOT  IN(1000,3500,1100,1500)
ORDER BY ENAME DESC;

5. SELECT ENAME, SAL FROM EMP
   WHERE  sal  > 3000
   AND  sal  < 4000;

6. SELECT ENAME, SAL FROM EMP
WHERE  sal  > 3000  OR sal  < 4000;

7. SELECT ENAME, SAL FROM EMP
WHERE  SAL BETWEEN 1000 AND 3500;

8. SELECT ENAME, SAL FROM EMP
WHERE  SAL  IN(1000,3500,1100,1500);

Oracle: Pattern Matching: Like Statement

Pattern Matching

1. SELECT ENAME, SAL FROM   EMP
WHERE ename LIKE   '%E%';

2. SELECT ENAME, SAL FROM   EMP
WHERE ename LIKE   'M%';

3. SELECT ENAME, SAL FROM   EMP
WHERE ename LIKE   '%M';

4. SELECT ENAME, SAL FROM   EMP
WHERE ename LIKE   'A%M';

5. SELECT ENAME, SAL FROM   EMP
WHERE ename LIKE   '__A%';

Oracle: Relational Operator

 Relational Operator 
1. SELECT ENAME, SAL FROM EMP
WHERE  sal < 3000;

2. SELECT ENAME, SAL FROM EMP
WHERE  sal > 3000;

3. SELECT ENAME, SAL FROM EMP
WHERE  sal = 3000;

4. SELECT ENAME, SAL FROM EMP
WHERE  sal <> 3000;

5. SELECT ENAME, SAL FROM EMP
WHERE  sal  <= 3000;

6. SELECT ENAME, SAL FROM EMP
WHERE  sal  >= 3000;

C++ "setf" using "basefield"

//5.  Program to print of Hexa Decimal, Octal, Decimal No.
int main()
{
    int x = 65;   
   cout.width(20);
   cout.setf(ios::hex,ios::basefield);
   cout<<"\n Hexa -> "<   cout.setf(ios::oct,ios::basefield);
   cout<<"\n Octal -> "<   cout.setf(ios::dec,ios::basefield);
   cout<<"\n Dec -> "<
}

C++ "setf" Scientific Application "floatfield"

//4.  Program shows the application scientific

int main()

{

   cout<   cout.width(20);

   cout.setf(ios::scientific,ios::floatfield);

   cout.fill('*');

   cout<

}

C++ "setf " Function

//2. Program shows the application of setf function right padding


int main()

{

   cout.width(20);

   cout.setf(ios::right,ios::adjustfield);

   cout.fill('*');

   cout<<"IT";

}

C ++ "setf" function for left padding

/* This file contains program related to console input and output */

#include 

#include

// 1. Program shows the application of setf function for left padding

int main()

{

   cout.width(20);

   cout.setf(ios::left,ios::adjustfield);

   cout.fill('*');

   cout<<"IT";

}

C++ Multilevel Inheritance

* C++ program to demonstrate example of multilevel inheritance.*/

Ref: Kamthane.

#include

using namespace std;

//Base Class : class A
class A
{
    private:
        int a;
    public:
        void get_a(int val_a)
        { 

           a=val_a; 

         }
       
       void disp_a(void)
        {  

           cout << "Value of a: " << a << endl;  

        }
};


class B: public A
{
    private:
        int b;
    public:
        void get_b(int val_a, int val_b)
        {

              //assign value of a by calling function of class A
            get_a(val_a);     
            b=val_b;
        }


        void disp_b(void)
        {
            //display value of a
            disp_a();
            cout << "Value of b: " << b << endl;
        }
};


//Here class C is derived class and B is Base class


class C: public B
{
    private:
        int c;
    public:
        //assign value of a from here
        void get_c(int val_a, int val_b,int val_c)
        {
            /*** Multilevel Inheritance ***/
            //assign value of a, b by calling function of class B and Class A
            //here Class A is inherited on Class B, and Class B in inherited on Class B
            get_b(val_a,val_b);
            c=val_c;
        }
       
        void disp_c(void)
        {


            disp_b(); //display value of a and b using disp_b()

            cout << "Value of c: " << c << endl;
        }
};

int main()
{
    //create object of final class, which is Class C


    C objC;


    objC.get_c(10,20,30);
    objC.disp_c();


    return 0;
}

C++ Single Level Inheritance

/*C++ program to demonstrate example of private simple inheritance.*/

Ref: Kamthane



#include
using namespace std;


class A
{   

    private:
        int a;
    protected:
        int x;      //can access by the derived class
    public:
        void setVal(int v)

        { 

           x=v; 

        }
};


class B:private A
{
    public:
        void printVal(void)
        {  

           //accessing public member function here //protected data member direct access here
            setVal(10);  

            cout << "value of x: " << x << endl;
        }
};


int main()
{   

       //derived class creation


        B objB; 
        objB.printVal();        return 0;

}

Java Practise Program List

1.  Practice - 1   - Premlimnary Level
2.  Practice  -2   - Advance Level

Jhansi Ki Rani | Rani Lakshmi bai | 3d Animation Song 2017 | Cordova Joy...

Dimensional Modeling Basics

2.1 Dimensional Modeling Basics    

 Dimensional modeling gets its name from the business dimensions we need to incorporate into the logical data model. It is a logical design technique to structure the business dimensions and the metrics that are analyzed along these dimensions. This modeling technique is intuitive for that purpose. The model has also proved to provide high performance for queries and analysis. The multidimensional information package diagram we have discussed is the foundation for the dimensional model. Therefore, the dimensional model consists of the specific data structures needed to represent the business dimensions. These data structures also contain the metrics or facts.

     To build a dimensional database, you start with a dimensional data model. The dimensional data model provides a method for making databases simple and understandable. You can conceive of a dimensional database as a database cube of three or four dimensions where users can access a slice of the database along any of its dimensions. To create a dimensional database, you need a model that lets you visualize the data.

     Suppose your business sells products in different markets and evaluates the performance over time. It is easy to conceive of this business process as a cube of data, which contains dimensions for time, products, and markets.   Shows this dimensional model. The various intersections along the lines of the cube would contain the measures of the business. The measures correspond to a particular combination of product, market, and time data.

FIGURE – 2.1: (A Dimensional Model of a Business That Has Time, Product and     Market Dimensions)

     Another name for the dimensional model is the star-join schema. The database designers use this name because the diagram for this model looks like a star with one central table around which a set of other tables are displayed. The central table is the only table in the schema with multiple joins connecting it to all the other tables. This central table is called the fact table and the other tables are called dimension tables. The dimension tables all have only a single join that attaches them to the fact table, regardless of the query.  shows a simple dimensional model of a business that sells products in different markets and evaluates business performance over time.

FIGURE – 2.2: (A Typical Dimensional Model)

2.2 Data Warehousing - Fact and Dimension Tables

     Data warehouses are built using dimensional data models which consist of fact and dimension tables. Dimension tables are used to describe dimensions; they contain dimension keys, values and attributes. For example, the time dimension would contain every hour, day, week, month, quarter and year that has occurred since you started your business operations. Product dimension could contain a name and description of products you sell, their unit price, color, weight and other attributes as applicable.

    Dimension tables are typically small, ranging from a few to several thousand rows. Occasionally dimensions can grow fairly large, however. For example, a large credit card company could have a customer dimension with millions of rows. Dimension table structure is typically very lean, for example customer dimension could look like following:

Table – 1 (Customer Dimension)

Customer_key

Customer_city

Customer_state

Customer_country

    

Although there might be other attributes that you store in the relational database, data warehouses might not need all of those attributes. For example, customer telephone numbers, email addresses and other contact information would not be necessary for the warehouse. Keep in mind that data warehouses are used to make strategic decisions by analyzing trends. It is not meant to be a tool for daily business operations. On the other hand, you might have some Reports that do include data elements that isn’t necessary for data analysis.

     Most data warehouses will have one or multiple time dimensions. Since the warehouse will be used for finding and examining trends, data analysts will need to know when each fact has occurred. The most common time dimension is calendar time. However, your business might also need a fiscal time dimension in case your fiscal year does not start on January 1st as the calendar year.

      Most data warehouses will also contain product or service dimensions since each business typically operates by offering either products or services to others. Geographically dispersed businesses are likely to have a location dimension.

      Fact tables contain keys to dimension tables as well as measurable facts that data analysts would want to examine. For example, a store selling automotive parts might have a fact table recording a sale of each item. The fact table of An educational entity could track credit hours awarded to students. A bakery could have a fact table that records manufacturing of various baked goods.

      Fact tables can grow very large, with millions or even billions of rows. It is important to identify the lowest level of facts that makes sense to analyze for your business this is often referred to as fact table "grain". For instance, for a Healthcare billing company it might be sufficient to track revenues by month; daily and hourly data might not exist  or might not be relevant. On the other hand, the assembly line warehouse analysts might be very concerned in number of defective goods that were manufactured each hour. Similarly a marketing data warehouse might be concerned by the activity of a consumer group with a specific income-level rather than purchases made by each individual.

2.2.1 Fact Table 

Facts:  A fact is a measurement captured from an event (transaction) in the marketplace. It is the raw materials for knowledge – observations.  A customer buys a product at a certain location at a certain time. When the intersection of these four dimensions occurs, a sale is made. The sale is describable as amount of dollars received, number of items sold, weight of goods shipped, etc. – a quantity that can be added to other sales similar in definition. Thus, a meaningful and measurable event of significance to the business occurs at the intersection point of business dimensions. It is the fact. We use the fact to represent a business measure. A data warehouse fact is defined as an intersection of the dimensions constituting the basic entities of the business transaction. It is not easy to show the intersection of more than three dimensions in a diagram, but facts in a data warehouse may originate from many dimensions.

Fact Table: A fact table is used in dimensional model in data warehouse design. A fact table is found at the center of a star schema or snowflake schema surrounded by dimension tables. A fact table consists of facts of a particular business process e.g., sales revenue by month by product. Facts are also known as measurements or metrics. A fact table record captures a measurement or a metric.

Example of fact table:

     In the schema below, we have fact table FACT_SALES that has a grain which gives us a number of units W sold by date, by store and by product. All other tables such as DIM_DATE, DIM_STORE and DIM_PRODUCT are dimensions tables. This schema is known as star schema.   

Here is overview of four steps to design a fact table described by Kimball:

1. Choosing business process to model – The first step is to decide what business process to model by gathering and understanding business needs and available data
2. Declare the grain – by declaring a grain means describing exactly what a fact table record represents
3. Choose the dimensions – once grain of fact table is stated clearly, it is time to determine dimensions for the fact table.
4. Identify facts – identify carefully which facts will appear in the fact table.

FIGURE – 2.3: (Fact Table with Star Scheme)

2.2.2 Dimensional Table 

     A dimension is a structure, often composed of one or more hierarchies, that categorizes data. Dimensional attributes help to describe the dimensional value. They are normally descriptive, textual values. Dimension tables are generally small in size as compared to fact table.

     Hierarchies and categories are included in the information packages for each dimension.  Let us examine the product dimension. Here, the product is the basic automobile. Therefore, we include the data elements relevant to product as hierarchies and categories. These would be model name, model year, package styling, product line, product category, exterior color, interior color, and first model year. Looking at the other business dimensions for the auto sales analysis, we summarize the hierarchies and categories for each dimension as follows:

1.      Product: Model name, model year, package styling, product line, product category, exterior Color, interior color, first model year.

2.      Dealer: Dealer name, city, state, single brand flag, date first operation.

3.      Customer demographics: Age, gender, income range, marital status, household size,

             Vehicles owned, home value, own or rent.

4.      Payment method: Finance type, term in months, interest rate, agent.

5.      Time: Date, month, quarter, year, day of week, day of month, season, holiday flag Let us go back to the hotel occupancy analysis. We have included three business dimensions. Let us list the possible hierarchies and categories for the three dimensions.

6.      Hotel: Hotel line, branch name, branch code, region, address, city, state, Zip Code, Manager, construction year, renovation year

7.      Room type: Room type, room size, number of beds, type of bed, maximum occupants, Suite, refrigerator, kitchenette

8.      Time: Date, day of month, day of week, month, quarter, year, holiday flag.