Normal Forms

First Normal Form

First Normal Form eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship.

Two rules follow this definition:

• Each table has a primary key made of one or several fields and uniquely identifying each record
• Each field is atomic, it does not contain more than one value

For instance, assuming a table WAGON to follow each wagon in every station.

wagon_id    integer  (ex. 101)
description string   (ex. 'wagon_type, empty_weight, capacity, designer, design_date')
state       string   (ex. 'under repair')
timestamp   datetime (ex. '22/12/2008 17:37')
station     string   (ex. 'New York Grand Central')

The primary key is (wagon_id, timestamp).
This table is not in 1NF because "description" is not atomic. To move it in 1NF we have to split "description" field in its components:

wagon_id     integer
wagon_type   string
empty_weight number
capacity     number
designer     string
design_date  datetime
state        string
timestamp    datetime
station      string

[edit]Second Normal Form

Second Normal Form eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key.

In our example, "wagon_type", "empty_weight", "capacity"... only depends on "wagon_id" but not on "timestamp" field of the primary key, so this table is not in 2NF. In order to reach 2NF, we have to split the table in two in the way that each field of each table depends on all the fields of each primary key:

wagon_id     integer
wagon_type   string
empty_weight number
capacity     number
designer     string
design_date  datetime

wagon_id     integer
timestamp    datetime
state        string
station      string

[edit]Third Normal Form

Third Normal Form eliminates functional dependencies on non-key fields by putting them in a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key.

In our example, in the first table it is most likely that "empty_weight", "capacity", "designer" and "design_date" depend on "wagon_type", so we have to split this table in two:

wagon_id     integer
wagon_type   string

wagon_type   string
empty_weight number
capacity     number
designer     string
design_date  datetime

Now our example with its 3 tables is in 3NF.

[edit]Fourth Normal Form

Fourth Normal Form separates independent multi-valued facts stored in one table into separate tables.

In the last table of our example, it is clear that "empty_weight" and "capacity" are interesting for every day wagon management whereas "designer" and "design_date" for historical purpose. So we can think they will never be used at the same time and have to split the table in two to fit the 4NF.

wagon_type   string
empty_weight number
capacity     number

wagon_type   string
designer     string
design_date  datetime

[edit]Fifth Normal Form

Fifth Normal Form breaks out data redundancy that is not covered by any of the previous normal forms.

Read more ...

Inner Join , Outer Join, ............?

Contents [hide]
·         1 Introduction ·         2 Inner JOIN ·         3 Outer JOIN ·         4 Additional Notes related to JOIN ·         5 Cross JOIN ·         6 Self-JOIN

Introduction

In this article, we’ll see the basic concepts of SQL JOINs. In the later part of the article, we’ll focus on the advanced subject of Self-JOIN and some interesting observations on how inner JOIN can be simulated using left JOIN. The author has tried his best to amalgamate various topics in a single concept.

The JOIN keyword is used in a SQL statement to query data from two or more tables based on a relationship between certain columns in these tables.

Inner JOIN

A JOIN that displays only rows that have a match in both the JOINed tables is known as inner JOIN.  This is the default type of JOIN in the Query and View Designer.

Outer JOIN

A JOIN that includes rows even if they do not have related rows in the joined table is an Outer JOIN.  You can create three different outer JOINs to specify the unmatched rows to be included:

Left Outer JOIN: In Left Outer JOIN, all rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.

Right Outer JOIN: In Right Outer JOIN, all rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause, are included. Unmatched rows in the left table are not included.

Full Outer JOIN: In Full Outer JOIN, all rows in all the joined tables are included, whether they are matched or not.

Additional Notes related to JOIN

The following are three classic examples to demonstrate the cases where Outer JOIN is useful. You must have noticed several instances where developers write query as given below.

1.SELECT t1.*

2.FROM Table1 t1

3.WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)

4.GO

The query demonstrated above can be easily replaced by Outer JOIN. Indeed, replacing it by Outer JOIN is the best practice. The query that generates the same result as above is shown here using Outer JOIN and WHERE clause in JOIN.

1./* LEFT JOIN - WHERE NULL */

2.SELECT t1.*,t2.*

3.FROM Table1 t1

4.LEFT JOIN Table2 t2 ON t1.ID = t2.ID

5.WHERE t2.ID IS NULL

The above example can also be created using Right Outer JOIN.

NOT Inner JOIN

Remember, the term Not Inner JOIN does not exist in database terminology. However, when full Outer JOIN is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner JOIN. This JOIN will show all the results that were absent in Inner JOIN.

Cross JOIN

A cross JOIN devoid of a WHERE clause produces the Cartesian product of the tables involved in the JOIN. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. One common example is when a company lists all its products in a pricing table to compare each product with others prices.

Self-JOIN

In this particular case, one table JOINs to itself with one or two aliases to stave off confusion. A self-JOIN can be of any type, as long as the joined tables are the same. A self-JOIN is unique in the sense that it involves a relationship with only one table. A common example is when a company has a hierarchal reporting structure whereby a member of staff reports to another member. Self-JOIN can either be an Outer JOIN or an Inner JOIN.

Self-JOIN is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values of the same column. Self-JOIN is a JOIN in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-JOIN can either be an inner JOIN or an outer JOIN. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data type of the inter-related columns must be of the same type or cast to the same type.

Now, think of a situation where all the data you require is contained within a single table, but data needed to extract is related to each other in the table itself. Examples of this type of data relate to employee information, where the table may have both an employee’s ID number for each record and also a field that displays the ID number of an employee’s supervisor or manager. To retrieve the data, it is mandatory for the tables to relate/JOIN to itself.

Another example that can be tried on SQL SERVER 2005 sample database AdventureWorks is to find products that are supplied by more than one vendor. Please refer to the sample database for table structure.

Note:

Before we continue further let me make it very clear that INNER JOIN should be used where it cannot be used and simulating INNER JOIN using any other JOINs will degrade the performance. If there are scopes to convert any OUTER JOIN to INNER JOIN, it should be done with priority.

Run the following two scripts and observe the result-set. It will be identical.

01.USE AdventureWorks

02.GO

03./* Example of INNER JOIN */

04.SELECT p.ProductID, piy.ProductID

05.FROM Production.Product p

06.INNER JOIN Production.ProductInventory piy ON piy.ProductID = p.ProductID

07.GO

08./* Example of LEFT JOIN simulating INNER JOIN */

09.SELECT p.ProductID, piy.ProductID

10.FROM Production.Product p

11.LEFT JOIN Production.ProductInventory piy ON 1 = 1

12.WHERE piy.ProductID = p.ProductID

13.GO   

After gazing at the identical result the first question that cropped up in my mind was - what is behind the scene plan? Looking at the actual execution plan of the query it is quite evident that even if LEFT JOIN is used in SQL Server Query Optimizer, it converts to INNER JOIN since results are the same and performance is better.

Looking at the above scenario it makes me ponder how smart Query Optimizer Engine is and how it might be saving innumerable performance-related issues for sub-optimal queries.

Now let us try to grasp the cause of LEFT JOIN acting as INNER JOIN. When 1= 1 is used in ON clause it is always true and converts LEFT JOIN to CROSS JOIN. However, when WHERE condition’s effect is applied to the above CROSS JOIN it produces a result similar to INNER JOIN in our case. SQL Server Query Optimizer interprets this in advance and uses INNER JOIN right away.

I think a good question to ask in an interview would be -“How to write an OUTER JOIN that will give you the exact result, execution plan and performance as INNER JOIN?”

If there is any other explanation apart from what I have given or if you want to share a similar example then please get in touch with me at my email addresspinal@sqlauthority.com or can search SQL Solutions at http://search.sqlauthority.com.

Read more ...

Oracle FAQ's

         **** SUBQUERIES & MULTIPLE SUBQUERIES ****
ROWNUM
=>1a. Select FIRST n records from a table

    select * from emp where rownum <= &n;


=>1b. Select LAST n records from a table

    select * from emp minus select * from emp
    where rownum <= (select count(*) - &n from emp);


=>2a. Select * from emp where ROWNUM >= 1; --- what happens=>

    ## returns all rows of table


=>2b. Select * from emp where ROWNUM > 1; --- what happens=>

    ## gives ?NO ROWS SELECTED?


=>2c. Select * from emp where ROWNUM > 0; --- what happens=>

    ## returns all rows of table


=>2d. Select * from emp where ROWNUM = 1; --- what happens=>

    ## Yes, this returns the 1st row


=>2e. Select * from emp where ROWNUM > 5; --- what happens=>

    ## gives ?NO ROWS SELECTED?


=>2f. Select * from emp where ROWID is null; --- what happens=>

    ## gives 'NO ROWS SELECTED'


=>2g. Select * from emp where ROWID is not null; --- what happens=>


    ## Returns all rows from the table.
Decode()
=>3. To select ALTERNATE(EVEN NUMBERED) records from a table

    select * from emp where rowid in (select
    decode(mod(rownum,2),0,rowid,
                null) from emp);

    ## here in the DECODE function,
    1st argument (mod(rownum,2)) is expression to check,
    2nd argument (0) is a search value,
    3rd argument (rowid) is to return the even rows if expr returns 0,
    4th argument (null) is to return no rows if expr does not return 0.


=>3a. To select ALTERNATE(ODD NUMBERED) records from a table

    select * from emp where rowid in (select
    decode(mod(rownum,2),0,null,
            rowid) from emp);


=>4. If sal >= 2000, increment salary by 1000                             else return sal from emp table

    select ename,empno,job,sal,
    decode(mod(sal,2000),sal,sal,
            sal+1000) salinc from emp;


n AND nth MAXIMUM & MINIMUM SALARIES
=>5. Find the 3rd MAX salary in the emp table

    select distinct sal from emp a
    where 3 = (select count(distinct sal) from emp b
    where a.sal <= b.sal);    -- sals in descending order

    ## The count() function in the subquery is set to return the count of each distinct salaries         arranged in descending order (since <= is used here (i.e for max '<=' is used)). So when         it reaches the 3rd record (i.e count is 3) its corresponding sal will be returned finally.

    for eg.,

    emp a            emp b
    4000            4000
    3000            3000
    2500            2500
    1800            1800


=>6. Find the 3rd MIN salary in the emp table

    select distinct sal from emp a
    where 3 = (select count(distinct sal) from emp b
    where a.sal >= b.sal);     -- sals in ascending order


=>7. Find 3 MAX salaries in the emp table

    select distinct sal from emp a
    where 3 >= (select count(distinct sal) from emp b
    where a.sal <= b.sal) order by a.sal desc;


=>8. Find 3 MIN salaries in the emp table

    select distinct sal from emp a
    where 3 >= (select count(distinct sal) from emp b
    where a.sal >= b.sal);


=>9. Find the nth MAX date from emp table

    select distinct hiredate from emp a
    where &n = (select count(distinct to_char(hiredate,'ddd'))
    from emp b where a.hiredate <= b.hiredate);
            or
    select distinct hiredate from emp a where
    3 = (select count(distinct hiredate) from emp b where
    a.hiredate <= b.hiredate);

    ## here no need of converting date to char datatype as in 1st query


=>10. Delete DUPLICATE records based on deptno

    delete from emp a where rowid !=
    (select max(rowid) from emp b where
    a.deptno=b.deptno);

    ## this query will retain all the records which are having unique deptno that are having maximum rowid and delete the rest duplicate ones ie., which are having repeated deptnos.

=>11. Select DISTINCT RECORDS from emp table

    select * from emp a where
    rowid = (select max(rowid) from emp b where
    a.empno=b.empno);

    ## here we can have 'in' also instead of '='






                 **** JOINS ****
OUTER JOIN
=>1. List employees' names and their managers' names (USING OUTER JOIN)

    select lo.ename "EMP NAMES", hi.ename "MGR NAMES" from
    emp lo, emp hi where lo.mgr = hi.empno(+);

    ## where (we can find the empno against each manager) each lo.mgr is a manager of a hi.empno
            or
    select a.empno,a.ename,a.mgr,b.ename from emp a, emp b where
    b.ename = (select b.ename from emp b where a.mgr = b.empno) ;
            or
    select a.empno,a.ename,a.mgr,b.ename from emp a, emp b where
    where a.mgr = b.empno ;


=>2. List Dept no., Dept name for all the departments in which there are no employees in the department (REPEATED QUESTION NO(3) BELOW UNDER ?INTERVIEW QUESTIONS?)

    select empno,ename,b.deptno,dname from emp a, dept b
    where a.deptno(+) = b.deptno and empno is null;    -- (1)

    ## (1) gives empno and ename as null also
                or
                
    select * from dept where deptno not in (select deptno from emp); -- (2)
                or

    select * from dept a where
    not exists (select * from emp b where
    a.deptno = b.deptno); -- (3)

    ## (2) & (3) GIVE THE SAME RESULT as (1)


=>3. List the count of number of employees in each department (REPEATED QUESTION NO(4) BELOW UNDER ?INTERVIEW QUESTIONS?)

    select count(EMPNO), b.deptno, dname from emp a, dept b
    where a.deptno(+)=b.deptno
    group by b.deptno,dname;

    ## NOTE : Here if we give count(*) it counts as 1 for deptno = 40. So we must give     count(empno) only for our required correct output.


=>4a. Creating a table 'parts' with some constraints

    create table parts(
    partno number(2),
    partname char(15),
    alt_partno number(2) references parts(partno),
    constraint pky primary key(partno));

    ## 1st record insertion shd be null for alt_partno

    eg. insert into parts values (09,'refil',null);


=>4b. List all partnames and their alternate partnames

    select a.partname "PARTNAME" ,b.partname "ALT PARTNAME" from
    parts a, parts b where
    b.partno(+) = a.alt_partno


=>5. Details of employees along with their location (EQUI JOIN)

    select empno,ename,a.deptno,b.dname,b.loc
    from emp a, dept b where a.deptno = b.deptno;


=>6. Details of ALLEN along with his location

    select empno,ename,a.deptno,b.dname,b.loc
    from emp a, dept b where a.deptno=b.deptno
    and ename = 'ALLEN';


=>7. List down the employees working in CHICAGO

    select empno,ename,loc from
    emp a, dept b where a.deptno=b.deptno and
    loc = 'CHICAGO';


=>8. List down details of employees getting sal < that of ALLEN

    select b.empno, b.ename,b.deptno,b.sal
    from emp a, emp b where a.ename = 'ALLEN'
    and b.sal <= a.sal;
            or
    select empno,ename,deptno,sal from emp
    where sal < (select sal from
    emp where ename = 'ALLEN');

                **** VIEWS ****
    
    ## information on table views can be obtained from the table
    called USER_VIEWS ##

SINGLE TABLE VIEWS

    SQL> create view empv1 as select * from emp;

.To create a view and insert - ( To insert a record in a view, the view should consist of the NOT NULL (MANDATORY) column of the base table)

    SQL>create view emp_view as select ename,job,sal from
        emp where sal <1500;

        ## view created

    SQL>insert into emp_view values('xxx','clerk',1000);

    SQL> insert into emp_view values('xxx','clerk',1000)
    2 *
    3 ERROR at line 1:
    4 ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert


CREATE VIEWS WITH THE CHECK OPTIONS

    SQL> create or replace view empview as select
        sal,empno,ename from emp where sal>1500;

    ## in the above case no record can be inserted into the table through the view EMPVIEW if the value of sal<1500, however the record can be inserted into the table directly.

    SQL> create or replace view empview sal,empno,ename,dname
    as select sal,empno,ename,dname from emp a, dept b where
    a.deptno=b.deptno and sal >1500 with check option;

    ## information on the check option can be obtained from the table called ALL_VIEWS

To create a view with 'with check option' and updating

    SQL>create view emp_view as select ename,job,sal
        from emp where sal <1500 with check option;

        ## View created.

    SQL>update emp_view set sal=sal + 1000;
        update emp_view set sal=sal + 1000
                *
        ERROR at line 1:
        ORA-01402: view WITH CHECK OPTION where-clause violation

MULTI TABLE VIEWS

    SQL>create or replace view empview
        as select empno,ename,a.deptno,dname from
        emp a, dept b where a.deptno=b.deptno;

    ## in complex table views no update,insert, etc.(i.e. DML commands) will be applicable


    SQL>create view emp_dept_view as
        select empno,ename,a.deptno,dname from
        emp a,dept b where a.deptno=b.deptno;

    SQL>create view dept_grp_view as
        select deptno,max(sal) "max sal" from
        emp group by deptno;


CREATE VIEW FROM A NON EXISTING TABLE USING 'FORCE' KEYWORD

    SQL> create FORCE view v1 as select * from aaa;

        Warning: View created with compilation errors.


CREATING VIEWS WITH COLUMNS RENAMED

    create or replace view empview (emp_number,emp_name,salary,dept_name)
    as select empno,ename,sal,dname from emp a, dept b where
    a.deptno=b.deptno;

ALTERING VIEWS

    alter view empview compile;

    ## this command is useful if the table was altered using the command "alter table ...." , the command now regenerates the view ###

TO DROP A VIEW

    drop view empview ;

                **** SET OPERATORS ****
1. MINUS

    SQL> select job from emp where deptno=20 MINUS
        select job from emp where deptno=30;
            or
    SQL> select distinct                
        job from emp where deptno=20 and
        job not in (select job from emp where deptno=30);

        ## Here if we don't give distinct in subquery then repeated jobs may be displayed if job             is repeated in the dept=20.


2. UNION

    SQL> select job from emp where deptno=10 UNION
        select job from emp where deptno=20;
            or
    SQL> select distinct job from emp where deptno in(10,20);
            or
    SQL> select distinct job from emp where deptno=10 or deptno=20;


3. UNION ALL

    SQL> select job from emp where deptno=10 UNION ALL
        select job from emp where deptno=20;
            or
    SQL> select job from emp where deptno in(10,20);


4. INTERSECT

    SQL> select job from emp where deptno=20 INTERSECT
        select job from emp where deptno=30;
            or
    SQL> select distinct job from emp where deptno=20
        and job in (select distinct job from emp where deptno=30);

            **** INT QUESTIONS (QUERIES) ****
=>1. Produce the following report:                                dept no        dept name        avg_sal of dept

    select b.deptno "dept no",dname "dept name",avg(sal) "avg sal of dept"
    from emp a, dept b where a.deptno = b.deptno
    group by b.deptno, dname;


=>2. Produce the above report for sal > 2000

    select b.deptno "dept no",dname "dept name",avg(sal) "avg sal of dept"
    from emp a, dept b where a.deptno = b.deptno
    group by b.deptno, dname having avg(sal) > 2000;


=>3. List dept no., Dept name for all the departments in which there are no employees in the department (REPEATED QUESTION NO.(2) AT TOP UNDER ?JOINS?)

    select empno,ename,b.deptno,dname from emp a, dept b
    where a.deptno(+) = b.deptno and empno is null;    -- (1)

    ## (1) gives empno and ename as null also
            or
    select * from dept where deptno not in (select deptno from emp); -- (2)
                or
    select * from dept a where
    not exists (select * from emp b where
    a.deptno = b.deptno); -- (3)

    ## (2) & (3) GIVE THE SAME RESULT as (1)


=>4. List the count of number of employees in each department (REPEATED QUESTION NO(3) AT TOP UNDER ?JOINS?)

    select count(EMPNO), b.deptno, dname from emp a, dept b
    where a.deptno(+)=b.deptno
    group by b.deptno,dname;

    ## NOTE : Here if we give count(*) it counts as 1 for deptno = 40. So we must give count(empno) only for our required correct output.


=>5. List ename,sal,new salary with 25% rise in existing sal for those employees who have present sal< 2000 and are from departments 10 & 20

    select ename, deptno, sal, sal+(sal*.25) " new sal" from emp
    where sal < 2000 and deptno in (10,20);


=>6. List empno,ename,sal,manager's name, manager's sal for all employees who earn more than their manager

    select a.empno empno, a.ename name, a.sal "emp sal",
    b.ename "mgr name", b.sal "mgr sal" from emp a, emp b
    where a.mgr = b.empno and a.sal > b.sal;


=>7. List the name and salary of all the employees working in all the departments who earn more salary than everybody in deptno=20

     select ename, deptno, sal from emp where
    sal > (select max(sal) from emp where deptno=30);


=>8. Copy only the structure(not records) from an existing table while creating a table

    create table empp as select * from emp where 1= 2;

    ## Here 1=2 is a false condition hence copies only the structure of emp to empp table(but not the records)


            **** SOME QUESTIONS ****

=>1. Create referential integrity on emp table with respect to deptno on deptno of dept table

     alter table emp add (constraint emp_fk foreign key(deptno)
    references dept(deptno));


=>2. Create a check to ensure that only ename and job is entered in uppercase

     alter table emp1 add (constraint nm_upp check(ename = upper(ename)),
    constraint job_upp check(job = upper(job)));


=>3. List all employees who have joined before the 'president'

     select empno, ename, hiredate from emp1 where hiredate < (select hiredate
    from emp1 where job = 'PRESIDENT');


=>4. List all employees who have joined after 12th march 1982

     select * from emp where hiredate > '12-MAR-82';


=>5. Assuming hiredate as birthdate of an employee list hiredate and retirement of all employees

     select empno,ename,hiredate,
    to_number(to_char(hiredate,'yy')) + 58 "RETIREMENT DATE" from
    emp; /* not correct */


=>6. List empno,ename,'Manager' or 'Staff', sal with an employee appearing only once in the list. Print 'Manager' for a manager and 'Staff' for non-managers

     select empno,ename,sal, decode(job,'MANAGER','Manager','Staff')
    from emp ;


=>7. List the empno,ename,sal of the first 5 highest paid employees. (Do not use the rownum or rowid functions. Use only std SQL features)

     select max(sal) from emp where sal <
    (select max(sal) from emp ) <
    (select max(sal) from emp where sal <
    (select max(sal) from emp where sal <
    (select max(sal) from emp ))));


=>8. List the command to produce the following effect on the dept table

    before        after
    deptno        deptno
    10        10
    12        20
    17        20
    22        30
    26        30
    30        30
    32        40

    update dept set deptno = round(deptno+5,-1) where mod(deptno,10)!=0;


=>9. If the salary of Clerks does not fall in the range 0-1000 then display 'Not Valid' against the clerks' names

     select empno,ename,sal,decode(sign(sal), sign(1000-sal),'Within Range','Not Valid')
    from emp where job = 'CLERK';

    ## select empno,sal,sign(sal),sign(sal-1000) from emp


=>10. Find all departments which have more than 3 employees

    select deptno,count(empno) from emp
    group by deptno having count(empno) >= 3;

********************************************************************************

********************************************************************************
1. Creation of a new table

     Create table dept (dept number (2), dname char(20));


2. Creation of a table from another table

     create table emp1 as select * from emp;


3. Copy of structure only from another table

    create table emp1 as select * from emp where 1=2

    ## (1=2 is a false condition hence only the stucture is copied)


4. To insert at macro level into a table *

     insert into emply values (&no,'&name',&dept,'&job','&dob','&jn_dt',&pay);


5. To append data from one table to another

     insert into emp1 select * from emp;


6. Creation of constraints on a new table

    create table emp1
    (empno number(5) primary key,
    ename varcha2(20) not null,
    sal number(10,2) check (sal>1000),
    phone number(6) unique);

    ## here the constraint name is alloted by oracle


7. Creation of contraints on a new table with contraint names

     create table emp1 (empno number(5) constraint pk primary key,
    ename varcha2(20) constraint nt not null,
    sal number(10,2) constraint ch check (sal>1000),
    phone number(6) constraint un unique);

    ## if constraint name is not provided by the owner the system by default assigns a contraint name in the format "SYS_C-----" where ----- is a continuous number


8. Creation of constraint with "REFERENCES" Table called DEPT should be existing where the column Called deptno with primary key constraint

     create table emp1
    (empno number(5) constraint pk primary key,
    ename varcha2(20) constraint nt not null,
    deptno NUMBER(2) references dept(deptno) [on delete cascade],
    sal number(10,2) constraint ch check (sal>1000),
    sex char constraint ck (sex in ('M','F')),
    phone number(6) constraint un unique);

    ## the syntax in square brackets is optional, i.e deleting any row in DEPT TABLE will delete all related rows in the dependent table


9. Creation of constraint on an existing table (egs)

    SQL>alter table emp add (constraint FK foreign key (deptno)
        references dept(deptno) [on delete cascade]);

    SQL>alter table emp add constraint pk primary key(empno);

    SQL>alter table emp1 add (constraint pk primary key(empno),
        constraint fk1 foreign key (mgr) references emp1(empno)
        on delete cascade);

    SQL> alter table emp add constraint pk primary key(empno,phone)

        ## pk creates a composite primary key on two fields

    SQL>delete from emp1 where empno = (select empno from emp1
        where ename = 'KING');


10. To drop a constraint on a table

     alter table dept1 drop constraint pk;

    ## refer constraint name in table called USER_CONSTRAINTS


11. To drop the main tables, its constraints with its referencing tables

    drop table dept cascade constraints ;

    ## drops all referential integrity constraints referring to the keys in the dropped table also , does     drop the dependent tables.


12. Creation of table with DEFAULT values

     create table emp1
    (empno number(5) primary key,
    ename varcha2(20) not null,
    sal number(10,2) default 3500,
    phone number(6) unique);

    ## Strictly speaking DEFAULT is not a constraint since this information will not be available in the user_constraints table

    ## DEFAULT clause is applicable to tables with more than one column only

    ##To find out DEFAULT setup values on a table, check in table called "USER_TABS_COLUMNS?

    ## to find the information on only primary key and foreign keys check in table called "USER_CONS_COLUMNS "


13 To modify default clause on a column

    alter table emp modify (sal default null);


14. Cascade constraints - to only drop constraints and its references that are related in other dependent tables.

     alter table dept drop constraint pk cascade;

    ## here any table referencing this table DEPT for pk will drop automatically the constraint on all dependent tables


15. To disable/enable a constraint on a table

    SQL>alter table dept disable constraint pk ;

        ## here pk is the primary key of the column

    SQL> alter table dept enable constraint nt;

        ## here nt is the not null of the column


16. Display of data with spaces in the alias names

    select deptno "Dept no " from dept1;


17. To obtain the retirement date for any person

     select add_months(last_day('&dob'),58*12)
    "Retirement Date" from dual;/* not correct*/

select to_char(hiredate,'dd-mon-yyyy'),
to_char(add_months(last_day(hiredate),58*12),'dd-mon-yyyy')
from emp;

##the above query works correctly dealing with year 2000.

18. To obtain the manager's name for a person in the emp table (REFER JOINS-?OUTER JOIN)

     select a.empno,a.ename,a.mgr,b.ename from emp a, emp b where
    b.ename = (select b.ename from emp b where a.mgr = b.empno) ;
            or
    select a.empno,a.ename,a.mgr,b.ename from emp a, emp b where
    where a.mgr = b.empno ;

19. To find the no. of months left in service

     select months_between(add_months(last_day('16-MAY-64'),58*12),
    to_date(sysdate)) "No.Of months" from dual;
            or
     select months_between(to_date('31-MAY-2022','DD-MON-YYYY'),
    to_date(sysdate)) from dual;

## here in to_date(sysdate) no need to give ?to_date?
PSEUDOCOLUMNS

20. To select only the 1st row from any table

    select * from emp where rownum =1;

    ## only rownum operates with the literal 1 for the 1st row for eg. "where rownum = 5" is wrong


21. To display the hierarchial order of emp table

     select level, ename EMPNAME, job
    from emp start with job = 'PRESIDENT' connect by prior empno=mgr;


22. To select from table with subquieries

    select * from emp where ename =
    (select ename from emp where empno = '7566');


    ## here the subquery returns only one value ( or row) if the SQ returns more than one row then an error message occurs


23. To select from table where the SQ returns more than one row

    SQL>select * from emp where sal in
    (select sal from emp where deptno = 20);

    ## for multiple rows returned the SQ must be preceded with the clause "IN" or "NOT IN" or "ANY" or "ALL"

    ## the "=" clause will yield error

    SQL> select * from emp where exists (select deptno from deptno)

    ## if the SQ following 'exists' returns atleast one row then the main query returns all the rows

    SQL> select dept.deptno,dept.dname from dept where
    dept.deptno = (select emp.deptno,dept.dname from emp,dept
    where emp.deptno!=dept.deptno);


24. Select with more than one column

     select * from emp where (job,deptno) in
    (select job,deptno from emp where sal>1000);


25. Select with Multiple sqs

     select * from emp where empno in
    (select empno from emp where deptno =
    (select deptno from dept where dname='SALES'));


26. To select 1st 'm' rows and last 'n' rows from a table

     select * from emp where rownum <= &m union
    (select * from emp minus
    (select * from emp where rownum <=
    (select count(*) - &n from emp)));


27. To select a range of rows from a table say starting from rownum = 's' and say 'r' rows

=>=>=>    select * from emp where rownum <=(s+r) intersect
    (select * from emp minus
    (select * from emp where rownum <=
    (select count(*)-(s+r) from emp))) ;

    ## say rownum 5 to 8

     select * from emp where rownum <=8 intersect
    (select * from emp minus
    (select * from emp where rownum <=
    (select count(*)-13 from emp))) ;


28a. Select with group by and having clause - where clause is also applicable

     select sum(sal),deptno,nvl(sum(comm),0) from emp1 group by deptno ;

28b. Selecting avg(sal) individual average sal of various departments

     select a.deptno,dname,avg(sal) from emp a, dept b
    where a.deptno=b.deptno
    group by a.deptno,dname;


28c. Conditional group by

     select a.deptno,dname,avg(sal) from emp a, dept b
    where a.deptno=b.deptno
    group by a.deptno,dname having avg(sal) >2000;

    ## in a group by clause always the "selected columns" should be listed in the columns following the group by clause


29. Order by clause with ascending/descending

    select * from emp where sal > 2000 order by sal desc, ename asc;


30. EQUI JOIN conditions i.e. '=' operator

     select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

31. OUTER JOIN conditions (REFER JOINS-OUTER JOINS)

    ##to select all departments

    select b.deptno,dname,empno,ename from dept b,emp a where
    b.deptno=a.deptno(+) and b.deptno in (select b.deptno from dept);

    ##to select only departments where no employees are present

     select b.deptno,dname,empno,ename from dept b,emp a where
    b.deptno=a.deptno(+) and b.deptno in (select b.deptno from dept)
    and empno is null;
                or
    select deptno,dname from dept where deptno not in (select deptno
    from emp);
                or
    select deptno,dname from dept a where not exists
    (select * from emp b where a.deptno = b.deptno)


32. SELF JOIN conditions applicable only for common tables

    SQL> select a.ename,a.job, b.ename from emp a, emp b where a.empno=b.empno;

    SQL> select a.empno "Emp No",a.ename "Emp Name", a.mgr "Mgr No",
    b.ename "Mgr Name" from emp a, emp b where
    b.ename = (select ename from emp where a.mgr=empno);


33. NON_EQUI join conditions

    select sal,empno from emp,dept where dept.deptno=emp.deptno
    and sal between 1000 and 3000;


****INDEXES****


    ## information on indexes is available in the table called USER_INDEXES

Creating indexes on tables

    create index empndx on emp (empno asc, sal desc, mgr desc);

    ## Only a maximum of 16 columns can be included in the index column this is same as primary key where only 16 columns can be included at a time


create an UNIQUE INDEXES

     create unique index empuniq on emp (empno,mgr,sal);


To drop an index

    drop index empuniq;


How to disable an index

    ## actually no index can be disabled, only an index usage can be avoided through an SQL statement

    ## for if there is an index table called empndx on the empno then the following SQL fools the index

    select * from emp where empno + 1 - 1 = 7902;

    ## here actually there is no index by name empno+1-1 and hence can be avoided

    ****SEQUENCES ****

To create a sequence which is also a database object

    ## always the 2 psuedocolumns - nextval & currval are affiliated with sequences

    create sequence empno
    increment by 2
    start with 100
    maxvalue 500
    minvalue 150 - gives error
    cycle
    cache 25; -- here 25 is the ready memory of numbers

    ## START WITH cannot be less than MINVALUE

To select from sequences

    ## always once the sequence is created to know the current val first use "select empno.nextval from dual" and then use the command "select empno.currval/empno.nextval from dual"

    SQL> select empno.nextval from dual;

    SQL> select empno.currval from dual;


To alter the sequence

    alter sequence empno increment by .....


How to generate a sequence without using the sequences

     select max(empno)+1 from emp;

If the sequence is for the very first instance then

    select nvl(max(empno),0)+1 from dual
    update emp set empno = nvl(max(empno),0)+1 where

    ## information on the sequences can be obtained from the table called USER_SEQUENCES

To get the listing of sequences already created

    select * from cat; -- catalogue table

    ## synonym table name for cat is user_catalog;


    **** CREATION OF CLUSTERS ****

    SQL> create cluster clust (deptno number(2));

    SQL> alter cluster clust (deptno number(2));

    SQL> alter table emp1 (empno number(5), ename char(10), deptno number(2))
        cluster clust(deptno);

    SQL> Create table emp2
        (empno number(4) primary key,
        ename varchar2(20) constraint ch1 check(ename = upper(ename)),
        mgr number(4) references emp2(empno),
        comm number(7,2),
        job varchar2(15) constraint ch2 check(job = upper(job)),
        deptno number(2) not null);


=>=>=> delete from emp where rowid = (select * from emp minus
(select * from emp where rownum <= (select max(rownum)-14 from emp)))

Read more ...