HOW PL/SQL GETS EXECUTED

http://comsci.liu.edu/~vasilaky/db2/lec1.htm

the PL/SQL engine on the server where it is compiled. The named PL/SQL
block is compiled only at the time of its creation, or if it has been
changed. The compilation process includes syntax checking, binding,
and p-code generation.

Oracle guarantees a read-consistent view
of the data. Until that point, all data that has been inserted or updated
will be held in memory and only be available to the current user. The
rows that have been changed will be locked by the current user and will
not be available for updating to other users until the locks have been re-leased.

table scan and indexes

http://msdn.microsoft.com/en-us/library/aa224773(v=sql.80).aspx

Nth Highest Salary in Oracle

http://www.oratable.com/nth-highest-salary-in-oracle/

First things first: The question is ambiguous!

Let’s say this is your data:

Name	Salary
KING	5000
FORD	3000
SCOTT	3000
JONES	2975
BLAKE	2850
CLARK	2850
ALLEN	1600

Who is second – FORD or SCOTT or both?

What will you say about JONES’s salary – is it the 3rd highest salary, or the 4th highest?

If you are looking for the set of people earning the Nth highest salary, with no gaps in case of ties, then JONES should be ranked 3rd, after KING [5000, 1st], followed by FORD and SCOTT [both 3000, 2nd].

If you are looking for exact ranks with gaps if there are ties, then JONES is the 4th highest paid employee, as there are 3 people earning more than him – KING, FORD and SCOTT. In this system of ranking, FORD and SCOTT are 2nd jointly and no employee is 3rd.

This is how your ranks will look, in the 2 cases:

Name Salary Rank KING 5000 1 FORD 3000 2 SCOTT 3000 2 JONES 2975 3 BLAKE 2850 4 CLARK 2850 4 ALLEN 1600 5 Name Salary Rank KING 5000 1 FORD 3000 2 SCOTT 3000 2 JONES 2975 4 BLAKE 2850 5 CLARK 2850 5 ALLEN 1600 7

Scenario 1: No gaps in case of ties Scenario 2: Gaps in case of ties

Once you have your question sorted out -

(a) Set of people earning the Nth highest salary, with continuous ranks if there are ties, OR

(b) Set of people earning the Nth highest salary, with skipped rank numbers if there are ties

Then you can proceed to writing the queries.

Scenario 1: DENSE_RANK () for Nth highest row, no gaps in case of ties

The analytic function dense_rank() will rank the rows with no gaps in ranking sequence if there are ties.

The ranks are calculated as: SQL> select ename

2        ,sal 3        ,dense_rank() over (order by sal desc) ranking 4  from   emp;  ENAME             SAL    RANKING ---------- ---------- ---------- KING             5000          1 FORD             3000          2 SCOTT            3000          2 JONES            2975          3 CLARK            2850          4 BLAKE            2850          4 ALLEN            1600          5

Wrap a filter around and pick out the Nth highest salary, say the 4th highest salary.

SQL> select *

2  from 3  ( 4    select ename 5          ,sal 6          ,dense_rank() over (order by sal desc) ranking 7    from   emp 8  ) 9  where ranking = 4 -- Replace 4 with any value of N 10  /  ENAME             SAL    RANKING ---------- ---------- ---------- BLAKE            2850          4 CLARK            2850          4

The 4th position has a tie between BLAKE and CLARK.

Scenario 2: RANK () for Nth highest row, gaps in case of ties

The analytic function rank() will rank the rows with gaps in ranking sequence if there are ties.

The ranks are calculated as:

SQL> select ename

2        ,sal 3        ,rank() over (order by sal desc) ranking 4  from   emp;  ENAME             SAL    RANKING ---------- ---------- ---------- KING             5000          1 FORD             3000          2 SCOTT            3000          2 JONES            2975          4 CLARK            2850          5 BLAKE            2850          5 ALLEN            1600          7 TURNER           1500          8

Wrap a filter around and pick out the Nth highest salary, say the 4th highest salary.

SQL> select *

2  from 3  ( 4    select ename 5          ,sal 6          ,rank() over (order by sal desc) ranking 7    from   emp 8  ) 9  where ranking = 4 -- Replace 4 with any value of N 10  /  ENAME             SAL    RANKING ---------- ---------- ---------- JONES            2975          4

A different answer from the previous query, as there is no rank 3 because of the tied 2nd place.

Closing Notes

The requirement to “find Nth highest row” is incomplete, until the following questions are also answered:

1. Can the result match more than one value? If not, on what basis should the one record be chosen if there is a tie?
2. How should the subsequent records be ranked in case of ties – contiguously or with gaps?

Depending on the answer for (2), DENSE_RANK (for contiguous) or RANK (for gaps) can be used. Depending on the answer for (1), extra filter criteria can be applied to the SQL.

There are other approaches for calculating the Nth highest row, too. The next is a non-analytic approach, which works the same way as the RANK query (gaps for ties).

SQL> select ename 2       , sal 3  from emp a 4  where 3 = ( select count(*) -- Replace 3 with any value of (N - 1) 5              from emp b 6              where b.sal > a.sal) 7  /  ENAME             SAL ---------- ---------- JONES            2975

However, tests have shown the analytics approach to be more efficient than the non-analytics one for Nth highest or Top-N type of queries.

sample SQL

1. which year did most people join the company

select to_char(hiredate,'YYYY')

from employee

group by to_char(hiredate, 'YYYY')

where count(empid)=select max(count(empid)) from employee group by to_char(hiredate,'YYYY')

2. list out the employees who earn more than average salary of their department

select *

from employee e1

group by dept_id

having salary>(select avg(salary) from employee e2

where e1.dept_id = e2.dept_id )

3. find nth maximum salary

select ename

from employee a

where &N =( select count(distinct(sal)) from employee b where a.sal < b.sal)

N = 5

4. select duplicate records in the employee table

select * from employee e

where e.empno in(select b.empno from employee b group by b.empno

having count(b.empno)<1)

and a.rowid!=min(b.rowid)

5. list the length of service the employee (n years and m months)

select ename,

to_char(trunc(months_between(sysdate,hiredate)/12)) || 'year' ||

to_char(trunk(months_between(systdate,hiredate)/12)) || 'month'

from employee

SQL constraints

Constraints are used to limit the type of data that can go into a table.

Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

MySQL:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

add UNIQUE
ALTER TABLE Persons
ADD UNIQUE (P_Id)

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE Persons
DROP INDEX uc_PersonID

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

.... to be continued

SQL syntax

http://www.w3schools.com/sql/sql_intro.asp

RDBMS stands for Relational Database Management System.

SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The query and update commands form the DML part of SQL:

• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are:

• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

SELECT column_name(s)

FROM table_name

ORDER BY column_name(s) ASC|DESC

INSERT INTO Persons

VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')

INSERT INTO Persons (P_Id, LastName, FirstName)

VALUES (5, 'Tjessem', 'Jakob')

UPDATE Persons

SET Address='Nissestien 67', City='Sandnes'

WHERE LastName='Tjessem' AND FirstName='Jakob'

DELETE FROM Persons

WHERE LastName='Tjessem' AND FirstName='Jakob'

Delete All Rows

DELETE FROM table_name

or

DELETE * FROM table_name

----------------------------------

SQL TOP Clause

Not all database systems support the TOP clause.

sybase support top, oracle use rownum

SQL Server

SELECT TOP number|percent column_name(s)

FROM table_name

MySQL Syntax

SELECT column_name(s)

FROM table_name

LIMIT number

Oracle Syntax

SELECT column_name(s)

FROM table_name

WHERE ROWNUM <= number

----------------------------

SELECT * FROM Persons
WHERE City LIKE '%tav%'

SELECT * FROM Persons
WHERE City NOT LIKE '%tav%'

---------

SQL Alias Syntax for Tables

SELECT column_name(s)
FROM table_name
AS alias_name

SQL Alias Syntax for Columns

SELECT column_name AS alias_name
FROM table_name

------

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.

• JOIN: Return rows when there is at least one match in both tables
• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
• FULL JOIN: Return rows when there is a match in one of the tables

----------

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

The FULL JOIN keyword return rows when there is a match in one of the tables.

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

-------------

The SQL UNION operator combines two or more SELECT statements.

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

The UNION command selects only distinct values.

UNION ALL will include everything

SELECT E_Name FROM Employees_Norway
UNION ALL

---

create DB

CREATE DATABASE database_name

create table

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

SELECT E_Name FROM Employees_USA

create tables

CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

--

CREATE a table from another table

http://www.techonthenet.com/sql/tables/create_table2.php

CREATE TABLE new_table
AS (SELECT * FROM old_table);

CREATE TABLE suppliers
AS (SELECT *
FROM companies
WHERE id > 1000);

This would create a new table called suppliers that included all columns from the companies table.

CREATE TABLE suppliers
AS (SELECT companies.id, companies.address, categories.cat_type
FROM companies, categories
WHERE companies.id = categories.id
AND companies.id > 1000);

This would create a new table called suppliers based on columns from both the companies and categories tables.

create a table from another table without copying any values from the old table?

CREATE TABLE new_table
AS (SELECT * FROM old_table WHERE 1=2);

find duplicated records

SELECT id, email, count(email) FROM users GROUP BY email HAVING count(email) > 1

self join

SELECT DISTINCT t1.id, t1.email FROM users t1, users t2 WHERE t1.email = t2.email AND t1.id <> t2.id

--

select id, count(*) as number_records

from table

group by id having count(*)>1

find 5th highest salary

select min(salary) as high5

from employee

where salary in (select distinct top 5 salary

from employee

order by salary

desc )

--

select sal from ( select sal,dense_rank() over(order by sal desc ) dr from emp ) where dr = 5 and rownum = 1

--

select sal from (select rownum rn,b.sal from ( select unique sal from emp a order by sal desc) b) where rn=5

---

Find second highest salary

select max(salary)

from employee

where salary not in(select max(salary) from employee)

database normalization

http://support.microsoft.com/kb/283878

jdbc driver type

http://en.wikipedia.org/wiki/JDBC_driver

http://www.jdbc-tutorial.com/jdbc-driver-types.htm

update/insert/delete views

http://sql-plsql.blogspot.com/2009/03/insert-update-delete-views.html

http://decipherinfosys.wordpress.com/2007/01/21/32/

Database management interview questions

http://www.techinterviews.com/database-management-interview-questions

1. What is a Cartesian product? What causes it?

Expected answer:
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join. It is causes by specifying a table in the FROM clause without joining it to another table.

2. What is an advantage to using a stored procedure as opposed to passing an SQL query from an application.

Expected answer:
A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database.

3. What is the difference of a LEFT JOIN and an INNER JOIN statement?

Expected answer:
A LEFT JOIN will take ALL values from the first declared table and matching values from the second declared table based on the column the join has been declared on. An INNER JOIN will take only matching values from both tables

4. When a query is sent to the database and an index is not being used, what type of execution is taking place?

Expected answer:
A table scan.

5. What are the pros and cons of using triggers?

Expected answer:
A trigger is one or more statements of SQL that are being executed in event of data modification in a table to which the trigger belongs.

Triggers enhance the security, efficiency, and standardization of databases.
Triggers can be beneficial when used:
– to check or modify values before they are actually updated or inserted in the database. This is useful if you need to transform data from the way the user sees it to some internal database format.
– to run other non-database operations coded in user-defined functions
– to update data in other tables. This is useful for maintaining relationships between data or in keeping audit trail information.
– to check against other data in the table or in other tables. This is useful to ensure data integrity when referential integrity constraints aren’t appropriate, or when table check constraints limit checking to the current table only.

6. What are the pros and cons of using stored procedures. When would you use them?

7. What are the pros and cons of using cursors? When would you use them?