SQL Subquery

Subquery is a query with in a query.

Syntax:-

select * from table_name where column_name in (select column from table_name)
Highlighted part is called Subquery.

Examples:- 
Consider below table structures.

DEPT table

Deptno	Deptname	Location
1	HR	London
2	Marketing	Newyork
5	Management	Bejing

EMP table

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find the details of all 'HR' employees.

Select * from EMP where deptno in (select deptno from DEPT  where deptname = 'HR');

Always Subquery will be executed first. If Subquery returns no data, main query also will not return any data.

2. Find the details of all 'Logistic' department employees if any.

Select * from EMP where deptno in (select deptno from DEPT  where deptname = 'Logistic');

The above query will not return any records because Subquery has no matching records 

3. Find the details of all employees earning lowest salary.

Select * from EMP where salary = (select min(salary) from EMP) ;  

4.  Find all employee details if there any Marketing dept

 Select * from EMP e where exists (select * from DEPT d where deptname = 'Marketing');   

Subquery returns more than one row error
Example 3 will return details of all employees earning lowest salary. While writing the subquery user forgot to mention min clause and submitted below query.

SQL> Select * from EMP where salary = (select salary from EMP) ;  

                                  *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

This error happened because main query expecting a single value from the Subquery but returning more than one row. When writing a single-row Subquery make sure it is returning single row.

SQL SOME operator

SOME operator is same as IN operator. This operator is rarely used.  

Read more about IN operator here

Syntax:

select columns from table_name where column_name = SOME ( values )

Examples:- 

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find all details of Bill, Solomon and Wendy employee. 

Select * from EMP where empname = SOME( 'Bill' , 'Solomon' ,'Wendy');

2. Find details of employee earning highest salary.

Select * from EMP where salary = SOME (select max(salary) from EMP) ;

SQL ANY operator

ANY operator is same as IN operator. This operator is rarely used in SQL.  

Read more about IN operator here

Syntax:

select columns from table_name where column_name = ANY ( values )

Examples:- 

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find all details of Bill, Solomon and Wendy employee. 

Select * from EMP where empname = ANY( 'Bill' , 'Solomon' ,'Wendy');

2. Find details of employee earning highest salary.

Select * from EMP where salary = ANY (select max(salary) from EMP) ;

SQL IN operator

IN operator uses to specify one or more values in where clause.

Syntax:

select columns from table_name where column_name IN ( values )

Examples:- 

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find all details of Bill, Solomon and Wendy employee. 

Select * from EMP where empname IN( 'Bill' , 'Solomon' ,'Wendy');

2. Find details of employee earning highest salary.

Select * from EMP where salary IN (select max(salary) from EMP) ;

3. Find details of employees earning less than highest salary.

Select * from EMP where salary NOT IN (select max(salary) from EMP) ;  

Not that NOT IN operator is a negation of IN operator. 

Installing Oracle 11g on Ubuntu

This article clearly describes installation of Oracle 11g release 2 on Ubuntu desktop
Installing oracle 11g on ubuntu

SQL COUNT function

COUNT function returns the total number from the expression. It is an example of aggregate functions.

Syntax:

1. select count(column_name) from table_name;
2. select count(*) from table_name;

Count(*) returns total number of rows.
Count(column_name) returns total number of rows have data. ie it ignores null columns.

Examples:-

Consider below EMP table structure

Empno Empname Manager Deptno 10 Bill 5 11 Solomon Bill 5 12 Susan Bill 5 13 Wendy Solomon 1 14 Benjamin Solomon 1 15 Tom Solomon 1 16 Henry Solomon 2 17 Robert Susan 2 18 Paul Solomon 2

1. Find the number of employees.

select count(*) from EMP;     

COUNT(*)

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

   9

2. Find the total number of managers.

select count(manager) from EMP;     

COUNT(MANAGER)

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

   8

3. Find all managers handling more than one employees.

select manager ,count(*) from EMP group by manager having count(*) > 1;

MANAGER  COUNT(*)
------------------ ----------
Bill                    2
Solomon         5

4. Find total number of managers.

select count(distinct manager) from EMP;

COUNT(DISTINCTMANAGER)
--------------------------------------------
    3

Related topics
AVG function
MAX Function

SQL AVG function

AVG function returns the average from the expression. It is an example of aggregate functions.

Syntax:

select avg(column_name) from table_name;

Examples:-

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find the average salary among the employees.

select avg(salary) from EMP;     

AVG(SALARY)

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

      9088.88889

2. Find the average salary among the employees in a department (Using where clause). 

select avg(salary) from EMP where deptno = 5;     

AVG(SALARY)

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

      10666.6667

3. Find the average salary in each department (Using group by clause). 

select deptno,avg(salary) from EMP group by deptno;     

 DEPTNO  AVG(SALARY)

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

 1              8033.33333
  2              8566.66667
  5              10666.6667

4. Find the all department details having average salary higher than 9000 (Using having by clause). 

select deptno,avg(salary) from EMP group by deptno having avg(salary) > 9000;


DEPTNO AVG(SALARY)
----------    ---------------------
 5             10666.6667

SQL MIN function

MIN function returns the minimum from the expression. It is an example of aggregate functions.

Syntax:

select min(column_name) from table_name;

Examples:-

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find the lowest salary among the employees.

select min(salary) from EMP;     

MIN(SALARY)

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

      7500

2. Find the lowest salary among the employees in a department (Using where clause). 

select min(salary) from EMP where deptno = 5;     

MIN(SALARY)

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

      10000

3. Find the lowest salary in each department (Using group by clause). 

select deptno,min(salary) from EMP group by deptno;     

 DEPTNO  MIN(SALARY)

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

 1               7500

 2               8500

 5               10000

 

SQL MAX function

MAX function returns the maximum from the expression. It is an example of aggregate functions.

Syntax:

select max(column_name) from table_name;

Examples:-

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find the highest salary among the employees.

select max(salary) from EMP;     

MAX(SALARY)

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

      12000

2. Find the highest salary among the employees in a department (Using where clause). 

select max(salary) from EMP where deptno = 1;     

MAX(SALARY)

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

      9000

3. Find the highest salary in each department (Using group by clause). 

select deptno,max(salary) from EMP group by deptno;     

 DEPTNO  MAX(SALARY)

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

1               9000

2               9500

5               12000

 

  

Database transaction and ACID properties

Database Transaction
A transaction is logical unit of work which has a complete status performed within a database management system. It should not be in an inconsistent status.

Example :-
Assume a user has a savings account having 4000 unit of currency. He withdraws 100 unit of currency from an ATM and this withdrawal is referred as a transaction.

1. User/Card/Account authentication.
2. Withdrawal request for 100 unit of currency.
3. Balance verification.
4. Dispenses the currency to user and update the balance to 3900.
5. Print receipt and exit.

Assume that a network error happened after Step 3. The transaction will not be completed. 

ACID properties
A database transaction must follow ACID properties.

A - Atomic
C - Consistent
 I -  Isolated
D - Durable    

Atomic :-
This property ensures the transaction either in complete status or nothing has started. There must not be any scenario like user received some currency amount and balance remains same or balance updated and failed to dispense the amount.

Consistent:-
Every transaction must be consistent.

Isolated:-
Every transaction must be isolated to avoid the locks or deadlocks. Deadlock is a condition when two or more users or processes waiting for the other to release a resource.

Durable:-
A transaction which has committed or saved must be there for permanently.  


Read more on how to install Oracle 11g on Ubuntu Linux here 

Oracle SQL Functions

Oracle database supplied with many in built SQL functions. Oracle recommends to use in built SQL function rather than using a user defined functions. It perform better than user defined functions. So try to utilize in built SQL functions as much as you can. Some of the SQL functions really helps while writing complex SQLs.     

Most used SQL functions can be categorized as

1. Aggregate functions - Returns a single result from a  group of rows.
Examples :- AVG, MAX, MIN, COUNT,SUM etc.


2. Numeric functions - Accepts numeric input and returns numeric result. It is also called as Mathematical functions.
Examples :- SIN, COS, TAN,  MOD, POWER, EXP etc.


3. Character functions - Returns character result from character input.
Examples :- CHR, LOWER, UPPER, TRIM etc.


4. Date functions - To manipulate date data.
Examples :- SYSDATE, ADD_MONTHS, TRUNC etc.


5. Conversion functions - To convert one data type to another data type
Examples :- TO_CHAR, TO_DATE, TO_NUMBER etc.


6. Analytical functions - It computes aggregate based on group of data
Examples :- RANK, DENSE_RANK, LAG etc.


Apart from these Oracle has few more functions like Collection functions, Large Object functions, XML functions, Data Mining functions, Hierarchical functions etc.

Some of the above listed functions which falls multiple categories. For example TRUNC is a Numeric as well as Date function. TRUNC(date) returns date value while TRUNC(number) returns numeric value.

Each individual SQL function explained in separate sections with examples.

DUAL table in Oracle

DUAL table is a special single row table present by default in Oracle Databases.

Structure of DUAL table
DUAL table has only one column called DUMMY as Varchar2(1) data type and value as X.


Examples
1) select 1 from DUAL;
    returns 1

2) select 1+2 from DUAL;
    returns 3

3) select sysdate from DUAL;
    returns system date of oracle database.

4) select `sql` from DUAL;
    returns`sql`

5) select user from DUAL;
    returns oracle user logged in.

6) select 
   (select empno from emp where empno = 10)
   from DUAL;
    returns 10

DUAL table can be used to test the SQL functions ( Both in-built and user defined functions)

7) select func_salary(10) from DUAL;
where func_salary is a user defined function returns a value when argument is 10.

8) select LOWER(`ORAclE`) from DUAL;
 returns`oracle` where LOWER is a in-built SQL function.

 DUAL table can be used in SQL and PL/SQL.

SELECT statement

SELECT statement has five major sections

1. SELECT key word 
2. column specification 
3. from key word
4. table or list of tables
5. where clause


Section 1, 2,3 and 4 are mandatory. 5 is optional. 


Explanation with examples  
Consider the table EMP with below structure

Empno	Name	DOB	Salary
10	Mark	12/31/1970	4000
20	Adam	11/20/1980	4500
30	Gary	02/03/1995	3500
40	Lisa	06/14/1960	6000

1) All records
Suppose user needs to see all records from EMP table. Below statement will pull all records from the table. 
select * from EMP;

Note :- 1) In SQL `*` means all columns. Here table has four columns.
            2) `;` is the terminator for the SQL statement.
            3) SQL is not case sensitive.

2) Selected columns
select empno,salary from EMP;
The above statement fetches all empno and salary columns from EMP table

3)  All records with where condition
select empno,salary from EMP where empno = 10;
 The above statement will fetch a record with empno as value 10

4)  All records using where condition and order by
select empno,salary from EMP where empno > 10 order by salary asc;
 The above statement will fetch all record with empno as value > 10 in a ascending order of salary

Select SQL statement has lot of features, some of them are pretty straight forward and some of them are really complex. Normally SQL cannot be learnt by just reading. You just need to practice SQLs with real examples. For that we need a database installed in an operating system.
Each database will have a client utility to manage SQLs. In Oracle Database supplied with  rwo SQL client utilities, SQL Plus and Oracle SQL Developer. SQL Plus is a command line utility and Oracle SQL Developer is a graphical user interface(GUI)

List of standalone tools available in the market.
1. TOAD - www.quest.com
2. Oracle SQL Developer - www.oracle.com
3. PL/SQL Developer - http://www.allroundautomations.com  
4. Advanced Query Tool(AQT) - www.querytool.com

What is Oracle Database ?

Oracle is the leading RDBMS (Relational Data Base Management System) available in the modern market. Oracle Software is produced and marketed by Oracle Corporation (www.oracle.com). Oracle Corporation has many other products based on Applications, Middleware, Server and Storage Systems etc. Recently Oracle has acquired Java and MySQL were MySQL is a open source database.

Major mile stones

* 1977 – Oracle founded
* 1983 – Oracle database 3
* 1992 – Oracle database 7
* 1997 – Oracle database 8
* 1999 – Oracle database 8i
* 2001 – Oracle database 9i
* 2003 – Oracle database 10g
* 2007 – Oracle database 11g

Oracle database 11g release 2 is the latest.

Read more on how to install Oracle 11g on Ubuntu Linux here 

What is ANSI SQL

ANSI means American National Standards Institute (http://www.ansi.org)  

Evolution of ANSI SQL

SQL is an integral part of many modern RDBMS like Oracle, DB2, Microsoft SQL Server, MySQL etc. Each vendor developed their own SQL syntaxes for their own products. All over the world many government, public and private agencies use different database products from different vendors and each develop system based on vendor dependant SQLs. So ANSI developed a common standard for SQL which runs in all modern vendor databases.            

Advantages

• Work in all modern databases.
• Easy to integrate different systems.
• Easy to migrate from one vendor database to another.
• Less maintenance cost.

Disadvantages 

• Relatively complex syntax compared to vendor SQLs.
• Higher development cost.

What is SQL

SQL (pronouncing as 'sequel') means Structured Query Language. It is a programming language to manage data in a DBMS or RDBMS. SQL is the back bone of any modern RDBMS.

History

SQL developed by IBM in 1970. In 1986 ANSI standardized the SQL.

SQL Statements

SELECT - Uses to fetches and displaying the data in a structured manner.

INSERT -  Uses to store data into the database.

UPDATE - Update or modify the stored data.

DELETE -  Removing the stored data.     

SELECT statement is the most widely and frequently used one. 

To explain the above statements we need to define a table first. Assume tat

EMP table stores employee details like Empno, Name, Age,

Structure of EMP Table

Empno	Name	Age
10	Mark	26
20	Adam	30
30	Gary	33
40	Lisa	27

There are 4 records in EMP table.


SELECT statement

Example :-  select * from EMP;     

Explanation :- 

The above statement will fetch all records from a table called EMP.

 

INSERT statement

Example :-  insert into EMP values(50,‘Lo‘,33);     

Explanation :- 

The above statement will insert one records into EMP table.

UPDATE statement

Example :-  update EMP set name = ‘Lopez‘ where Empno = 50;  

Explanation :- 

The above statement will update name column in EMP table.

Delete statement

Example :-  delete from EMP where Empno = 50;  

Explanation :- 

The above statement will update name column in EMP table.

We will discuss each SQL statements in a very detail manner.

Difference between DBMS and RDBMS

RDBMS is a super set of DBMS. Consolidated few differences here.



Microsoft Access is an example of  DBMS.
Oracle, Microsoft SQL Server, DB2 are few examples for RDBMS.