Database notes

What is 1st NF,2nd NF,3rd NF?

normalization:process of organizing data to minimize redundancy

1st NF: each table has PK.The values in each col. of a table are atomic

2nd NF: Redundant data across multiple rows should be moved to a new table. 

The tables must be related with each other by FK

3rd NF: Remove any attribute not directly and fully depend on the key to a separate table.

Patient No.	Name	Ward No.
123	Abc	10

Ward No.	Ward name
10	Bernard

Here Ward No. and Ward name are independent of Patient No. So, its better to put these columns in a separate table.

What is Functional Dependency?

                For any two attribute A and B, A is dependent on B if for a given value of B there is precisely one associated value of A. Ex. Employee surname is totally depend on empid. The opposite is not true.  

What is order by and group by.

     order by is used for ordering and group by is used in aggregate function.

How to remove duplicate entry in db?

     -rowid value uniquely identifies a row in the database

     -DELETE FROM test WHERE rowid NOT IN (SELECT max(rowid) FROM test GROUP BY col1);

what is tablespace in oracle?

                A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible. Tablespaces are where you store Oracle database objects such as tables, indexes and rollback segments.

Difference between oracle and sql server?

ORACLE	SQL-Server
EVERYTHING is a transaction and it is not permanent until you COMMIT	(by default) no transaction control.
PL/SQL	T-Sql
runs on many platform	runs on Windows only

What is diff b/w delete and truncate?

Delete	Truncate
remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed	removes all rows from a table
need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.	The operation cannot be rolled back.
when you delete the records from the database, database has to perform 2 actions. 1.delete from the database 2.write the deleted records into "rollback" segments.	Second activity not required

how to get 3rd highest salery using sql?

select min(empsalary) from (select * from emptable order by empsalary desc) where rownum<=3

                                8

What is SQL?

                SQL stands for 'Structured Query Language'.

What is SELECT statement?

      The SELECT statement lets you select a set of values from a table in a database. The values selected from the database table would depend on the various conditions that are specified in the SQL query.

How can you compare a part of the name rather than the entire name?

                SELECT * FROM people WHERE empname LIKE '%ab%'

                Would return a recordset with records consisting empname the sequence 'ab' in empname .

What is the INSERT statement?

                insert information into a database.

How do you delete a record from a database?

                remove records from a database.

How could I get distinct entries from a table?

                distinct values from a table in a database.

How to get the results of a Query sorted in any order?

                ORDER BY

                SELECT empname, age, city FROM emptable ORDER BY empname

How can I find the total number of records in a table?

                SELECT COUNT(*) FROM emp

What is GROUP BY?

                used for aggregate functions (like SUM)

What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.

Dropping        : (Table structure  + Data are deleted)

Truncating     : (Data alone deleted), Performs an automatic commit, Faster than delete

Delete           : (Data alone deleted), Doesn’t perform automatic commit

What are the Large object types suported by Oracle?

                Blob and Clob.

Difference between a "where" clause and a "having" clause.

                Having clause is used only with group functions whereas Where is used as a filtering criteria.

What's the difference between a primary key and a unique key?

PK	Unique Key
not null	can be null
creates clustered index on col.	creates non-clustered index

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

                allow row-by-row prcessing of the resultsets.

                Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.

in SQL procedures, you need to do the following:
    *Declare a cursor that defines a result set.
    *Open the cursor to establish the result set.

        *Fetch the data into local variables as needed from the cursor, one row at a time.

         *Close the cursor when done.

Defining a cursor:

CURSOR c1
IS
    SELECT emp_name,emp_age
    from emp_table

command that would be used to fetch the data from this cursor is:

FETCH c1 into cnumber;

Disadvantages of cursors:

1       *Each time you fetch a row from the cursor, it results in a network roundtrip, and where as a normal SELECT query makes only one rowundtrip, however large the result set is.

2       *Cursors are also costly because they require more resources and temporary storage (results in more IO operations).

What are triggers? How to invoke a trigger on demand?

                Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

    CREATE TRIGGER check_who_create_objects

    AFTER CREATE OR DROP ON SCHEMA

    BEGIN

INSERT INTO audit_table VALUES (USER || ' created an object on: ' || sysdate);

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR (num => -20000, msg => 'Cannot drop object');

     END;

Ref cursor: A ref cursor in Oracle PL/SQL is much like an ordinary PL/SQL cursor in that it acts as a pointer to the result set of the cursor with which it is associated. However, the difference is that a ref cursor can be assigned to different result sets whereas a cursor is always associated with the same result set. Cursors and ref cursors are not interchangeable.

What is a join and explain different types of joins.

                Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

    Types of joins:

                INNER JOINs,

                OUTER JOINs

                                LEFT OUTER JOINS

                                RIGHT OUTER JOINS

                                FULL OUTER JOINS

                CROSS JOINs.

What is a self join?

                Self join is just like any other join, except that two instances of the same table will be joined in the query.