Current Online Members: 0
Page Hit Count:
+000339968


News Update:
Datawarehouse/Database

Getting started with Basic SQL

Learning SQL is the base for any BI-DWH project implementation. One can easily validate and fix the report that was created by Cognos Report Author using SQL only if he/she knows.
Introduction

SQL stands for Structured Query Language.

SQL is a language that enables you to work with a database. Using SQL, you can insert records, update records, and delete records. You can also create new database objects such as databases and tables.

Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of SQL.

SQL Syntax

Generally, a SQL statement begins by stating what to do (for example, "SELECT"), then states which object to do it to (for example, using the "FROM" clause).

SELECT ...
FROM

... WHERE GROUP BY ... HAVING ORDER BY ...

Data Manipulation Language (DML)

DML enables you to work with the data that goes into the database. DML is used to insert, select, update, and delete records in the database. Many of your SQL statements will begin with one of the following commands:

SELECT - Retrieves data from the database
INSERT - Inserts new data into the database
UPDATE - Updates existing data in the database
DELETE - Deletes existing data from the database

Data Definition Language (DDL)

You may also occasionally need to create or drop a table or other datbase object. SQL enables you to do this programatically using DDL.

CREATE DATABASE - Creates a new database
ALTER DATABASE - Modifies the database
DROP DATABASE - Drops (deletes) a database
CREATE TABLE - Creates a new table
ALTER TABLE - Modifies the table
DROP TABLE - Drops (deletes) a table

SQL SELECT...FROM...WHERE
The SELECT statement is probably the most commonly used in SQL. It simply retrieves data from the database.
A Simple SELECT statement:
SELECT * FROM EMP;
Lets have a look at the table the statement is trying to select data from:

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7369

SMITH

CLERK

7902

12/17/1980

800

20

7499

ALLEN

SALESMAN

7698

2/20/1981

1600

300

30

7521

WARD

SALESMAN

7698

2/22/1981

1250

500

30

7566

JONES

MANAGER

7839

4/2/1981

2975

20

7654

MARTIN

SALESMAN

7698

9/28/1981

1250

1400

30

7698

BLAKE

MANAGER

7839

5/1/1981

2850

30

7782

CLARK

MANAGER

7839

6/9/1981

2450

10

7788

SCOTT

ANALYST

7566

4/19/1987

3000

20

7839

KING

PRESIDENT

11/17/1981

5000

10

7844

TURNER

SALESMAN

7698

9/8/1981

1500

0

30

7876

ADAMS

CLERK

7788

5/23/1987

1100

20

7900

JAMES

CLERK

7698

12/3/1981

950

30

7902

FORD

ANALYST

7566

12/3/1981

3000

20

7934

MILLER

CLERK

7782

1/23/1982

1300

10

This SQL SELECT statement is attempting to retrieve all columns from a table called Individual.
How do we know it is trying to select all columns? Because it is using an asterisk (*). This is a quick way of selecting all columns - it's much easier than writing out the names of all columns (especially if there are a lot of columns).
SQL WHERE
Using the WHERE clause, you can filter out only those records that satisfy a given condition.
SQL WHERE Syntax

Example: For retrieving alternate records...(Odd Records)

SELECT * FROM EMP WHERE ROWID IN (SELECT DECODE(MOD(ROWNUM,2),1,ROWID) FROM EMP);

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7369

SMITH

CLERK

7902

12/17/1980

800

20

7521

WARD

SALESMAN

7698

2/22/1981

1250

500

30

7654

MARTIN

SALESMAN

7698

9/28/1981

1250

1400

30

7782

CLARK

MANAGER

7839

6/9/1981

2450

10

7839

KING

PRESIDENT

11/17/1981

5000

10

7876

ADAMS

CLERK

7788

5/23/1987

1100

20

7902

FORD

ANALYST

7566

12/3/1981

3000

20

AND Operator

The AND operator filters the query to only those records that satisfy both the first condition and the second condition.

Example:

SELECT * FROM EMP
WHERE ENAME = 'SMITH' AND JOB = 'CLERK'

OR Operator

The OR operator filters the query to only those records that satisfy either one or the other condition.

Example:

SELECT * FROM EMP
WHERE ENAME = 'SMITH' OR JOB = 'CLERK'
SQL Order By
Using a SQL SELECT statement can retreive many hundreds or even thousands of records. In some cases you might find it useful to sort the records by a given column. For example, when selecting records from the Individual table, you might like to sort them by the LastName column.
Ascending Order
By default, ORDER BY sorts the column in ascending order.
SQL statement
SELECT * FROM EMP ORDER BY ENAME ASC
Result

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7876

ADAMS

CLERK

7788

5/23/1987

1100

20

7499

ALLEN

SALESMAN

7698

2/20/1981

1600

300

30

7698

BLAKE

MANAGER

7839

5/1/1981

2850

30

7782

CLARK

MANAGER

7839

6/9/1981

2450

10

7566

JONES

MANAGER

7839

4/2/1981

2975

20

7654

MARTIN

SALESMAN

7698

9/28/1981

1250

1400

30

Descending Order

If you want highest values to appear first, you can use the DESC keyword.
SQL statement
SELECT * FROM EMP ORDER BY ENAME DESC
Result

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7654

MARTIN

SALESMAN

7698

9/28/1981

1250

1400

30

7566

JONES

MANAGER

7839

4/2/1981

2975

20

7782

CLARK

MANAGER

7839

6/9/1981

2450

10

7698

BLAKE

MANAGER

7839

5/1/1981

2850

30

7499

ALLEN

SALESMAN

7698

2/20/1981

1600

300

30

7876

ADAMS

CLERK

7788

5/23/1987

1100

20

Sorting by Multiple Columns
You can sort by multiple columns by stating each column in the ORDER BY clause, separating each column name with a comma. SQL will first order the results by the first column, then the second, and so on for as many columns that are included in the ORDER BY clause.
SQL statement
SELECT * FROM EMP ORDER BY ENAME, JOB
SQL Distinct
Once a table starts getting a lot of data in it, some columns will contain duplicate values. For example, many Individuals share first names and surnames. Most of the time this isn't a problem. But sometimes you will want to find out how many unique values there are in a table. To do this you can use the DISTINCT keyword.
SQL statement
SELECT * FROM EMP ORDER BY ENAME, JOB
SELECT DISTINCT(ENAME) FROM EMP
Result

ENAME

ADAMS

ALLEN

BLAKE

CLARK

JONES

MARTIN

SQL In
The SQL IN operator assists you in providing multiple values in your WHERE clause. This can provide very useful when you need to compare your value to a list of values. Often this list could be the result of a query from another table.
SQL Syntax
SELECT * FROM table_name
WHERE column_name IN (value1,value2,...)
Example
SELECT * FROM EMP WHERE EName IN ('SMITH','WARD','CLARK','FORD')
Result

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7369

SMITH

CLERK

7902

12/17/1980

800

20

7521

WARD

SALESMAN

7698

2/22/1981

1250

500

30

7782

CLARK

MANAGER

7839

6/9/1981

2450

10

7902

FORD

ANALYST

7566

12/3/1981

3000

20

SQL Join

When you perform a SQL join, you specify one column from each table to join on. These two columns contain data that is shared across both tables.

You can use multiple joins in the same SQL statement to query data from as many tables as you like.
Join Types
INNER JOIN
This will only return rows when there is at least one row in both tables that match the join condition.

Syntax

SELECT * FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name

LEFT OUTER JOIN (or LEFT JOIN)

This will return rows that have data in the left table (left of the JOIN keyword), even if there's no matching rows in the right table.

Syntax

SELECT * FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name = table_name2.column_name

RIGHT OUTER JOIN (or RIGHT JOIN)
This will return rows that have data in the right table (right of the JOIN keyword), even if there's no matching rows in the left table.

Syntax

SELECT * FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name = table_name2.column_name

FULL OUTER JOIN (or FULL JOIN)
This will return all rows, as long as there's matching data in one of the tables.

Syntax

SELECT * FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name
With Examples - SQL Inner Join
Use this when you only want to return rows that have matching data in both the left and right table.
SQL Statement
SELECT * FROM Employee AS EMP
INNER JOIN Publisher AS PUB
ON EMP.Id = PUB.EmpId
WHERE EMP.EmpId = '3'

Source Tables

Left Table

Id

FirstName

LastName

Designation

1

Saravanan

Vajjiravel

Sr. Software Engineer

2

Gowri P

Sankari

Sr. Software Engineer

3

Sahana

GS

Nursery

4

Chandrasekhar

Sriramoju

Sr. Software Engineer

5

Shashi

Kumar

Sr. QA - Test Lead

Right Table

EmpId

Access

1

Administrator

2

Contributor

3

Consumer

4

Contributor

10

Contributor

Result

Id

FirstName

LastName

Designation

EmpId

Access

3

Sahana

GS

Nursery

3

Consumer

With Examples - SQL Outer Join
Left Outer Join
Use this when you only want to return rows that have matching data in the left table, even if there's no matching rows in the right table.
SQL Statement
SELECT * FROM Employee AS EMP
LEFT JOIN Publisher AS PUB
ON EMP.Id = PUB.EmpId
Result

Id

FirstName

LastName

Designation

EmpId

Access

1

Saravanan

Vajjiravel

Sr. Software Engineer

1

Administrator

2

Gowri P

Sankari

Sr. Software Engineer

2

Contributor

3

Sahana

GS

Nursery

3

Consumer

4

Chandrasekhar

Sriramoju

Sr. Software Engineer

4

Contributor

5

Shashi

Kumar

Sr. QA - Test Lead

NULL

NULL

Right Outer Join
Use this when you only want to return rows that have matching data in the right table, even if there's no matching rows in the left table.
SQL Statement
SELECT * FROM Employee AS EMP
RIGHT JOIN Publisher AS PUB
ON EMP.Id = PUB.EmpId
Result

Id

FirstName

LastName

Designation

EmpId

Access

1

Saravanan

Vajjiravel

Sr. Software Engineer

1

Administrator

2

Gowri P

Sankari

Sr. Software Engineer

2

Contributor

3

Sahana

GS

Nursery

3

Consumer

4

Chandrasekhar

Sriramoju

Sr. Software Engineer

4

Contributor

NULL

NULL

NULL

NULL

10

Contributor

Full Outer Join
Use this when you want to all rows, even if there's no matching rows in the right table.

SQL Statement

SELECT * FROM Employee AS EMP
FULL JOIN Publisher AS PUB
ON EMP.Id = PUB.EmpId
Result

Id

FirstName

LastName

Designation

EmpId

Access

1

Saravanan

Vajjiravel

Sr. Software Engineer

1

Administrator

2

Gowri P

Sankari

Sr. Software Engineer

2

Contributor

3

Sahana

GS

Nursery

3

Consumer

4

Chandrasekhar

Sriramoju

Sr. Software Engineer

4

Contributor

5

Shashi

Kumar

Sr. QA - Test Lead

NULL

NULL

NULL

NULL

NULL

NULL

10

Contributor

SQL Insert
The SQL INSERT command allows you to insert a record into a table in your database. As with the SELECT syntax, the INSERT syntax is quite straight forward.
SQL statement
INSERT INTO Individual
VALUES ( '6', 'Benny', 'Hill', 'hillbenny' )
SQL Update
The SQL UPDATE statement allows you to update an existing record in the database.
The UPDATE command uses a WHERE clause. If you don't use a WHERE clause, all rows will be updated. In fact, the syntax for a basic UPDATE statement is very similar to a SELECT statement.
SQL statement
UPDATE Individual
SET UserName = 'funnyman'
WHERE IndividualId = '6'
Updating Multiple Fields
To update multiple fields, separate each field assignment with a comma.
SQL statement
UPDATE Individual
SET UserName = 'getserious', FirstName = 'Onetree'
WHERE IndividualId = '6'
SQL Delete
The SQL DELETE statement allows you to delete a record from the database.
The DELETE command uses a WHERE clause. If you don't use a WHERE clause, all rows in the table will be deleted. Again, as with the UPDATE statement, the syntax for a basic DELETE statement is similar to a SELECT statement.
SQL statement
DELETE
FROM Individual
WHERE IndividualId = '6'
SQL Create Database

You can create a database using the CREATE DATABASE command.

SQL syntax
CREATE DATABASE database_name
Example Code
This statement creates a database called "Payroll". Because no arguments have been specified, the database data files and transaction logs will be created automatically in the default location.
CREATE DATABASE Payroll
SQL Create Table
You create a table using the CREATE TABLE command.
SQL syntax
CREATE TABLE table_name
(column_name_1 datatype,
column_name_2 datatype,
...
)
Example Code
CREATE TABLE Individual
(IndividualId int,
FirstName Varchar(255),
LastName Varchar(255),
UserName Char(10)
)
SQL Create Index
Indexes can be created against a table to make searches more efficient. A database index is similar to an index of a book - a book index allows you to find information without having to read through the whole book. A database index enables the database application to find data quickly without having to scan the whole table.
Indexes can have a slight impact on performance so you should only create indexes against tables and columns that will be frequently searched against. For example, if users of your application often search against the LastName field then that field is a great candidate for an index.
You create an index using the CREATE INDEX command.
SQL syntax
CREATE INDEX index_name
ON table_name (column_name)
Example Code
CREATE INDEX IndividualIndex
ON Individual (LastName)
SQL Alter Table
Use Alter command to modify the existing table.
Add a Column - SQL syntax
ALTER TABLE table_name
ADD column_name datatype
Example Code
ALTER TABLE Individual
ADD age int
Change the Datatype - SQL syntax
ALTER TABLE table_name
ALTER COLUMN column_name datatype
Example Code
ALTER TABLE Individual
ALTER COLUMN age numeric
Drop a Column - SQL syntax
'Dropping' a column means removing or deleting that column.
ALTER TABLE table_name
DROP COLUMN column_name
Example Code
ALTER TABLE Individual
DROP COLUMN age

Was the information posted in this article helpful?
173 74