SQL NOTES

1) Introduction to SQL

SQL is a standard language for accessing and manipulating databases

What is SQL?
 SQL stands for Structured Query Language
 SQL lets you access and manipulate databases
 SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

 SQL can execute queries against a database
 SQL can retrieve data from a database
 SQL can insert records in a database
 SQL can update records in a database
 SQL can delete records from a database
 SQL can create new databases
 SQL can create new tables in a database
 SQL can create stored procedures in a database
 SQL can create views in a database
 SQL can set permissions on tables, procedures, and views

SQL is a Standard - BUT....

Although SQL is an ANSI (American National Standards Institute) standard, there are many different
versions of the SQL language.
However, to be compliant with the ANSI standard, they all support at least the major commands (such
as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.
Note: Most of the SQL database programs also have their own proprietary extensions in addition to
the SQL standard!

Using SQL in Your Web Site

To build a web site that shows some data from a database, you will need the following:
 An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
 A server-side scripting language, like PHP or ASP
 SQL
 HTML / CSS

SQL NOTES Page 2

RDBMS
RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle,
MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects called tables.
A table is a collections of related data entries and it consists of columns and rows.

SQL NOTES Page 3

2) SQL Syntax

Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g.
"Customers" or "Orders"). Tables contain records (rows) with data.
Below is an example of a table called "Persons":
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The table above contains three records (one for each person) and five columns (P_Id, LastName,
FirstName, Address, and City).

SQL Statements
Most of the actions you need to perform on a database are done with SQL statements.
The following SQL statement will select all the records in the "Persons" table:
SELECT * FROM Persons
In this tutorial we will teach you all about the different SQL statements.

Keep in Mind That...

 SQL is not case sensitive

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.
Semicolon is the standard way to separate each SQL statement in database systems that allow more
than one SQL statement to be executed in the same call to the server.
We are using MS Access and SQL Server 2000 and we do not have to put a semicolon after each SQL
statement, but some database programs force you to use it.

SQL NOTES Page 4

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 define indexes (keys),
specify links between tables, and impose 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

SQL NOTES Page 5

3) SQL SELECT Statement

The SQL SELECT Statement

The SELECT statement is used to select data from a database.
The result is stored in a result table, called the result-set.

SQL SELECT Syntax

SELECT column_name(s)
FROM table_name
and
SELECT * FROM table_name
Note: SQL is not case sensitive. SELECT is the same as select.

An SQL SELECT Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to select the content of the columns named "LastName" and "FirstName" from the
table above.
We use the following SELECT statement:
SELECT LastName,FirstName FROM Persons
The result-set will look like this:
LastName FirstName
Hansen Ola
Svendson Tove
Pettersen Kari

SQL NOTES Page 6

SELECT * Example
Now we want to select all the columns from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
Tip: The asterisk (*) is a quick way of selecting all columns!
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Navigation in a Result-set
Most database software systems allow navigation in the result-set with programming functions, like:
Move-To-First-Record, Get-Record-Content, Move-To-Next-Record, etc.

SQL NOTES Page 7

4) SQL SELECT DISTINCT Statement

The SQL SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however,
sometimes you will want to list only the different (distinct) values in a table.
The DISTINCT keyword can be used to return only distinct (different) values.

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name(s)
FROM table_name

SELECT DISTINCT Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to select only the distinct values from the column named "City" from the table above.
We use the following SELECT statement:
SELECT DISTINCT City FROM Persons
The result-set will look like this:
City
Sandnes
Stavanger

SQL NOTES Page 8

5) SQL WHERE Clause

The WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion.

SQL WHERE Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

WHERE Clause Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to select only the persons living in the city "Sandnes" from the table above.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City='Sandnes'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes

Quotes Around Text Fields

SQL uses single quotes around text values (most database systems will also accept double quotes).
Although, numeric values should not be enclosed in quotes.
For text values:
This is correct:

SQL NOTES Page 9

SELECT * FROM Persons WHERE FirstName='Tove'

This is wrong:

SELECT * FROM Persons WHERE FirstName=Tove

For numeric values:
This is correct:

SELECT * FROM Persons WHERE Year=1965

This is wrong:

SELECT * FROM Persons WHERE Year='1965'

Operators Allowed in the WHERE Clause

With the WHERE clause, the following operators can be used:
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns

Note: In some versions of SQL the <> operator may be written as !=

SQL NOTES Page 10

6) SQL AND & OR Operators
The AND & OR operators are used to filter records based on more than one condition.

The AND & OR Operators

The AND operator displays a record if both the first condition and the second condition is true.
The OR operator displays a record if either the first condition or the second condition is true.

AND Operator Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to select only the persons with the first name equal to "Tove" AND the last name equal
to "Svendson":
We use the following SELECT statement:
SELECT * FROM Persons
WHERE FirstName='Tove'
AND LastName='Svendson'
The result-set will look like this:
P_Id LastName FirstName Address City
2 Svendson Tove Borgvn 23 Sandnes

SQL NOTES Page 11

OR Operator Example
Now we want to select only the persons with the first name equal to "Tove" OR the first name equal to
"Ola":
We use the following SELECT statement:
SELECT * FROM Persons
WHERE FirstName='Tove'
OR FirstName='Ola'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes

Combining AND & OR

You can also combine AND and OR (use parenthesis to form complex expressions).
Now we want to select only the persons with the last name equal to "Svendson" AND the first name
equal to "Tove" OR to "Ola":
We use the following SELECT statement:
SELECT * FROM Persons WHERE
LastName='Svendson'
AND (FirstName='Tove' OR FirstName='Ola')
The result-set will look like this:
P_Id LastName FirstName Address City
2 Svendson Tove Borgvn 23 Sandnes

SQL NOTES Page 12

7) SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set.

The ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by a specified column.
The ORDER BY keyword sort the records in ascending order by default.
If you want to sort the records in a descending order, you can use the DESC keyword.

SQL ORDER BY Syntax

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

ORDER BY Example
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Tom Vingvn 23 Stavanger
Now we want to select all the persons from the table above, however, we want to sort the persons by
their last name.
We use the following SELECT statement:
SELECT * FROM Persons
ORDER BY LastName
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
4 Nilsen Tom Vingvn 23 Stavanger

SQL NOTES Page 13

3 Pettersen Kari Storgt 20 Stavanger
2 Svendson Tove Borgvn 23 Sandnes

ORDER BY DESC Example

Now we want to select all the persons from the table above, however, we want to sort the persons
descending by their last name.
We use the following SELECT statement:
SELECT * FROM Persons
ORDER BY LastName DESC
The result-set will look like this:
P_Id LastName FirstName Address City
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Tom Vingvn 23 Stavanger
1 Hansen Ola Timoteivn 10 Sandnes

SQL NOTES Page 14

8) SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.

The INSERT INTO Statement

The INSERT INTO statement is used to insert a new row in a table.

SQL INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two forms.
The first form doesn't specify the column names where the data will be inserted, only their values:
INSERT INTO table_name
VALUES (value1, value2, value3,...)
The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

SQL INSERT INTO Example

We have the following "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to insert a new row in the "Persons" table.
We use the following SQL statement:
INSERT INTO Persons
VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')
The "Persons" table will now look like this:
P_Id LastName FirstName Address City

SQL NOTES Page 15

1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Johan Bakken 2 Stavanger

Insert Data Only in Specified Columns

It is also possible to only add data in specific columns.
The following SQL statement will add a new row, but only add data in the "P_Id", "LastName" and the
"FirstName" columns:
INSERT INTO Persons (P_Id, LastName, FirstName)
VALUES (5, 'Tjessem', 'Jakob')
The "Persons" table will now look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Johan Bakken 2 Stavanger
5 Tjessem Jakob

SQL NOTES Page 16

9) SQL UPDATE Statement
The UPDATE statement is used to update records in a table.

The UPDATE Statement

The UPDATE statement is used to update existing records in a table.

SQL UPDATE Syntax

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or
records that should be updated. If you omit the WHERE clause, all records will be updated!

SQL UPDATE Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Johan Bakken 2 Stavanger
5 Tjessem Jakob
Now we want to update the person "Tjessem, Jakob" in the "Persons" table.
We use the following SQL statement:
UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob'

SQL NOTES Page 17

The "Persons" table will now look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Johan Bakken 2 Stavanger
5 Tjessem Jakob Nissestien 67 Sandnes

SQL UPDATE Warning

Be careful when updating records. If we had omitted the WHERE clause in the example above, like
this:
UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'
The "Persons" table would have looked like this:
P_Id LastName FirstName Address City
1 Hansen Ola Nissestien 67 Sandnes
2 Svendson Tove Nissestien 67 Sandnes
3 Pettersen Kari Nissestien 67 Sandnes
4 Nilsen Johan Nissestien 67 Sandnes
5 Tjessem Jakob Nissestien 67 Sandnes

SQL NOTES Page 18

10) SQL DELETE Statement

The DELETE statement is used to delete records in a table.

The DELETE Statement

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value
Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or
records that should be deleted. If you omit the WHERE clause, all records will be deleted!

SQL DELETE Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Johan Bakken 2 Stavanger
5 Tjessem Jakob Nissestien 67 Sandnes
Now we want to delete the person "Tjessem, Jakob" in the "Persons" table.
We use the following SQL statement:
DELETE FROM Persons
SQL NOTES Page 19
WHERE LastName='Tjessem' AND FirstName='Jakob'
The "Persons" table will now look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Johan Bakken 2 Stavanger

Delete All Rows

It is possible to delete all rows in a table without deleting the table. This means that the table structure,
attributes, and indexes will be intact:
DELETE FROM table_name

or

DELETE * FROM table_name

Note: Be very careful when deleting records. You cannot undo this statement!

SQL NOTES Page 20

11) SQL Injection
An SQL Injection can destroy your database.

SQL in Web Pages

In the previous chapters, you have learned to retrieve (and update) database data, using SQL.

When SQL is used to display data on a web page, it is common to let web users input their own search
values.

Since SQL statements are text only, it is easy, with a little piece of computer code, to dynamically
change SQL statements to provide the user with selected data:

Server Code
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
The example above, creates a select statement by adding a variable (txtUserId) to a select string. The
variable is fetched from the user input (Request) to the page.

The rest of this chapter describes the potential dangers of using user input in SQL statements.

SQL Injection

SQL injection is a technique where malicious users can inject SQL commands into an SQL statement,
via web page input.

Injected SQL commands can alter SQL statement and compromise the security of a web application.

SQL NOTES Page 21

SQL Injection Based on 1=1 is Always True

Look at the example above, one more time.

Let's say that the original purpose of the code was to create an SQL statement to select a user with a
given user id.

If there is nothing to prevent a user from entering "wrong" input, the user can enter some "smart" in-
put like this:

UserId:

Server Result

SELECT * FROM Users WHERE UserId = 105 or 1=1

The SQL above is valid. It will return all rows from the table Users, since WHERE 1=1 is always true.

Does the example above seem dangerous? What if the Users table contains names and passwords?

The SQL statement above is much the same as this:

SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1
A smart hacker might get access to all the user names and passwords in a database by simply inserting
105 or 1=1 into the input box.

SQL Injection Based on ""="" is Always True

Here is a common construction, used to verify user login to a web site:

User Name:

Password:

Server Code
uName = getRequestString("UserName");
uPass = getRequestString("UserPass");

sql = "SELECT * FROM Users WHERE Name ='" + uName + "' AND Pass ='" + uPass + "'"

SQL NOTES Page 22

A smart hacker might get access to user names and passwords in a database by simply inserting " or
""=" into the user name or password text box.

The code at the server will create a valid SQL statement like this:

Result
SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""
The result SQL is valid. It will return all rows from the table Users, since WHERE ""="" is always true.

SQL Injection Based on Batched SQL Statements

Most databases support batched SQL statement, separated by semicolon.

Example
SELECT * FROM Users; DROP TABLE Suppliers
The SQL above will return all rows in the Users table, and then delete the table called Suppliers.

If we had the following server code:

Server Code
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
And the following input:

User id:

The code at the server would create a valid SQL statement like this:

Result
SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers
Parameters for Protection

Some web developers use a "blacklist" of words or characters to search for in SQL input, to prevent
SQL injection attacks.

This is not a very good idea. Many of these words (like delete or drop) and characters (like semicolons
and quotation marks), are used in common language, and should be allowed in many types of input.
SQL NOTES Page 23
(In fact it should be perfectly legal to input an SQL statement in a database field.)

The only proven way to protect a web site from SQL injection attacks, is to use SQL parameters.

SQL parameters are values that are added to an SQL query at execution time, in a controlled manner.

[Link] Razor Example

txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = @0";
[Link](txtSQL,txtUserId);
Note that parameters are represented in the SQL statement by a @ marker.

The SQL engine checks each parameter to ensure that it is correct for its column and are treated liter-
ally, and not as part of the SQL to be executed.

Another Example
txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
[Link](txtSQL,txtNam,txtAdd,txtCit);

You have just learned to avoid SQL injection. One of the top website vulnerabilities.

Examples

The following examples shows how to build parameterized queries in some common web languages.

SELECT STATEMENT IN [Link]:

txtUserId = getRequestString("UserId");
sql = "SELECT * FROM Customers WHERE CustomerId = @0";
command = new SqlCommand(sql);
[Link]("@0",txtUserID);
[Link]();
INSERT INTO STATEMENT IN [Link]:
txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
command = new SqlCommand(txtSQL);
[Link]("@0",txtNam);

SQL NOTES Page 24

[Link]("@1",txtAdd);
[Link]("@2",txtCit);
[Link]();
INSERT INTO STATEMENT IN PHP:
$stmt = $dbh->prepare("INSERT INTO Customers (CustomerName,Address,City)
VALUES (:nam, :add, :cit)");
$stmt->bindParam(':nam', $txtNam);
$stmt->bindParam(':add', $txtAdd);
$stmt->bindParam(':cit', $txtCit);
$stmt->execute();

13) SQL TOP Clause

The TOP Clause

The TOP clause is used to specify the number of records to return.
The TOP clause can be very useful on large tables with thousands of records. Returning a large number
of records can impact on performance.
Note: Not all database systems support the TOP clause.

SQL Server Syntax

SELECT TOP number|percent column_name(s)
FROM table_name

SQL SELECT TOP Equivalent in MySQL and Oracle

MySQL Syntax
SELECT column_name(s)
FROM table_name
LIMIT number

Example
SELECT *
FROM Persons
LIMIT 5

Oracle Syntax
SELECT column_name(s)
FROM table_name
SQL NOTES Page 25
WHERE ROWNUM <= number

Example
SELECT *
FROM Persons
WHERE ROWNUM <=5

SQL TOP Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Tom Vingvn 23 Stavanger
Now we want to select only the two first records in the table above.
We use the following SELECT statement:
SELECT TOP 2 * FROM Persons
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes

SQL TOP PERCENT Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Tom Vingvn 23 Stavanger
Now we want to select only 50% of the records in the table above.
We use the following SELECT statement:

SQL NOTES Page 26

SELECT TOP 50 PERCENT * FROM Persons
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes

14) SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

The LIKE Operator

The LIKE operator is used to search for a specified pattern in a column.

SQL LIKE Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

LIKE Operator Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to select the persons living in a city that starts with "s" from the table above.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE 's%'
The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the
pattern.

SQL NOTES Page 27

The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Next, we want to select the persons living in a city that ends with an "s" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE '%s'
The result-set will look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
Next, we want to select the persons living in a city that contains the pattern "tav" from the "Persons"
table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE '%tav%'
The result-set will look like this:
P_Id LastName FirstName Address City
3 Pettersen Kari Storgt 20 Stavanger
It is also possible to select the persons living in a city that NOT contains the pattern "tav" from the
"Persons" table, by using the NOT keyword.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City NOT LIKE '%tav%'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes

SQL NOTES Page 28

15) SQL Wildcards
SQL wildcards can be used when searching for data in a database.

SQL Wildcards
SQL wildcards can substitute for one or more characters when searching for data in a database.
SQL wildcards must be used with the SQL LIKE operator.
With SQL, the following wildcards can be used:
Wildcard Description
% A substitute for zero or more characters
_ A substitute for exactly one character
[charlist] Any single character in charlist
[^charlist] Any single character not in charlist

or

[!charlist]

SQL Wildcard Examples

We have the following "Persons" table:
P_Id LastName FirstName Address City

SQL NOTES Page 29

1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Using the % Wildcard

Now we want to select the persons living in a city that starts with "sa" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE 'sa%'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
Next, we want to select the persons living in a city that contains the pattern "nes" from the "Persons"
table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE '%nes%'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes

Using the _ Wildcard

Now we want to select the persons with a first name that starts with any character, followed by "la"
from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE FirstName LIKE '_la'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes

SQL NOTES Page 30

Next, we want to select the persons with a last name that starts with "S", followed by any character,
followed by "end", followed by any character, followed by "on" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName LIKE 'S_end_on'
The result-set will look like this:
P_Id LastName FirstName Address City
2 Svendson Tove Borgvn 23 Sandnes

Using the [charlist] Wildcard

Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the
"Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName LIKE '[bsp]%'
The result-set will look like this:
P_Id LastName FirstName Address City
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Next, we want to select the persons with a last name that do not start with "b" or "s" or "p" from the
"Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName LIKE '[!bsp]%'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes

SQL NOTES Page 31

16) SQL IN Operator

The IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.

SQL IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

IN Operator Example
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to select the persons with a last name equal to "Hansen" or "Pettersen" from the table
above.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen')
The result-set will look like this:

SQL NOTES Page 32

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

17) SQL BETWEEN Operator

The BETWEEN operator is used in a WHERE clause to select a range of data between two values.

The BETWEEN Operator

The BETWEEN operator selects a range of data between two values. The values can be numbers, text,
or dates.

SQL BETWEEN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

BETWEEN Operator Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes

SQL NOTES Page 33

3 Pettersen Kari Storgt 20 Stavanger
Now we want to select the persons with a last name alphabetically between "Hansen" and "Pettersen"
from the table above.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName
BETWEEN 'Hansen' AND 'Pettersen'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
Note: The BETWEEN operator is treated differently in different databases.
In some databases, persons with the LastName of "Hansen" or "Pettersen" will not be listed, because
the BETWEEN operator only selects fields that are between and excluding the test values).
In other databases, persons with the LastName of "Hansen" or "Pettersen" will be listed, because the
BETWEEN operator selects fields that are between and including the test values).
And in other databases, persons with the LastName of "Hansen" will be listed, but "Pettersen" will not
be listed (like the example above), because the BETWEEN operator selects fields between the test
values, including the first test value and excluding the last test value.
Therefore: Check how your database treats the BETWEEN operator.

Example 2
To display the persons outside the range in the previous example, use NOT BETWEEN:
SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Hansen' AND 'Pettersen'
The result-set will look like this:
P_Id LastName FirstName Address City
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

SQL NOTES Page 34

18) SQL Alias
With SQL, an alias name can be given to a table or to a column.

SQL Alias
You can give a table or a column another name by using an alias. This can be a good thing to do if you
have very long or complex table names or column names.
An alias name could be anything, but usually it is short.

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

SQL NOTES Page 35

Alias Example
Assume we have a table called "Persons" and another table called "Product_Orders". We will give the
table aliases of "p" an "po" respectively.
Now we want to list all the orders that "Ola Hansen" is responsible for.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons AS p,
Product_Orders AS po
WHERE [Link]='Hansen' AND [Link]='Ola'
The same SELECT statement without aliases:
SELECT Product_Orders.OrderID, [Link], [Link]
FROM Persons,
Product_Orders
WHERE [Link]='Hansen' AND [Link]='Ola'
As you'll see from the two SELECT statements above; aliases can make queries easier to both write
and to read.

Alias Example for Tables

The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the
Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" re-
spectively (Here we have used aliases to make the SQL shorter):

Example
SELECT [Link], [Link], [Link]
FROM Customers AS c, Orders AS o
WHERE [Link]="Around the Horn" AND [Link]=[Link];
The same SQL statement without aliases:

Example
SELECT [Link], [Link], [Link]
FROM Customers, Orders
WHERE [Link]="Around the Horn" AND
[Link]=[Link];
Aliases can be useful when:

 There are more than one table involved in a query

SQL NOTES Page 36

  Functions are used in the query

 Column names are big or not very readable

 Two or more columns are combined together

19) SQL Joins

SQL joins are used to query data from two or more tables, based on a relationship between certain
columns in these tables.

SQL JOIN
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a
relationship between certain columns in these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each
primary key value must be unique within the table. The purpose is to bind data together, across tables,
without repeating all of the data in every table.
Look at the "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows
can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:

SQL NOTES Page 37

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column
refers to the persons in the "Persons" table without using their names.
Notice that the relationship between the two tables above is the "P_Id" column.

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

20) SQL INNER JOIN Keyword

SQL INNER JOIN Keyword

The INNER JOIN keyword return rows when there is at least one match in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: INNER JOIN is the same as JOIN.

SQL INNER JOIN Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes

SQL NOTES Page 38

3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Now we want to list all the persons with any orders.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link]

The result-set will look like this:

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows
in "Persons" that do not have matches in "Orders", those rows will NOT be listed.

SQL NOTES Page 39

21) SQL LEFT JOIN Keyword

SQL LEFT JOIN Keyword

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).

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL LEFT JOIN Example

The "Persons" table:

SQL NOTES Page 40

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Now we want to list all the persons and their orders - if any, from the tables above.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link]

The result-set will look like this:

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove
The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches
in the right table (Orders).

SQL NOTES Page 41

22) SQL RIGHT JOIN Keyword

SQL RIGHT JOIN Keyword

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

SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL NOTES Page 42

SQL RIGHT JOIN Example
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Now we want to list all the orders with containing persons - if any, from the tables above.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link]

The result-set will look like this:

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
34764
The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no
matches in the left table (Persons).

SQL NOTES Page 43

23) SQL FULL JOIN Keyword

SQL FULL JOIN Keyword

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

SQL FULL JOIN Syntax

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

SQL NOTES Page 44

SQL FULL JOIN Example
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Now we want to list all the persons and their orders, and all the orders with their persons.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link]

The result-set will look like this:

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove
34764

The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the
right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are
SQL NOTES Page 45
rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.

24) SQL UNION Operator

The SQL UNION operator combines two or more SELECT statements.

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The
columns must also have similar data types. Also, the columns in each SELECT statement must be in the
same order.

SQL NOTES Page 46

SQL UNION Syntax
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
Note: The UNION operator selects only distinct values by default. To allow duplicate values, use
UNION ALL.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
PS: The column names in the result-set of a UNION are always equal to the column names in the first
SELECT statement in the UNION.

SQL UNION Example

Look at the following tables:
"Employees_Norway":
E_ID E_Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari
"Employees_USA":
E_ID E_Name
01 Turner, Sally
02 Kent, Clark
03 Svendson, Stephen
04 Scott, Stephen
Now we want to list all the different employees in Norway and USA.
We use the following SELECT statement:
SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA
The result-set will look like this:
E_Name

SQL NOTES Page 47

Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen
Note: This command cannot be used to list all employees in Norway and USA. In the example above
we have two employees with equal names, and only one of them will be listed. The UNION command
selects only distinct values.

SQL UNION ALL Example

Now we want to list all employees in Norway and USA:
SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA
Result
E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Svendson, Stephen
Scott, Stephen

SQL SELECT INTO Statement

The SQL SELECT INTO statement can be used to create backup copies of tables.

The SQL SELECT INTO Statement

The SELECT INTO statement selects data from one table and inserts it into a different table.
The SELECT INTO statement is most often used to create backup copies of tables.

SQL NOTES Page 48

SQL SELECT INTO Syntax
We can select all columns into the new table:
SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename
Or we can select only the columns we want into the new table:
SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename

SQL SELECT INTO Example

Make a Backup Copy - Now we want to make an exact copy of the data in our "Persons" table.
We use the following SQL statement:
SELECT *
INTO Persons_Backup
FROM Persons
We can also use the IN clause to copy the table into another database:
SELECT *
INTO Persons_Backup IN '[Link]'
FROM Persons
We can also copy only a few fields into the new table:
SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons

SQL SELECT INTO - With a WHERE Clause

We can also add a WHERE clause.
The following SQL statement creates a "Persons_Backup" table with only the persons who lives in the
city "Sandnes":
SELECT LastName,Firstname
INTO Persons_Backup
FROM Persons
WHERE City='Sandnes'

SQL NOTES Page 49

SQL SELECT INTO - Joined Tables
Selecting data from more than one table is also possible.
The following example creates a "Persons_Order_Backup" table contains data from the two tables
"Persons" and "Orders":
SELECT [Link],[Link]
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id

25) SQL CREATE DATABASE Statement

The CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database.

SQL CREATE DATABASE Syntax

CREATE DATABASE database_name

SQL NOTES Page 50

CREATE DATABASE Example
Now we want to create a database called "my_db".
We use the following CREATE DATABASE statement:
CREATE DATABASE my_db
Database tables can be added with the CREATE TABLE statement.

26) SQL CREATE TABLE Statement

The CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database.

SQL CREATE TABLE Syntax

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,

SQL NOTES Page 51

column_name3 data_type,
....
)
The data type specifies what type of data the column can hold. For a complete reference of all the
data types available in MS Access, MySQL, and SQL Server, go to our complete Data Types reference.

CREATE TABLE Example

Now we want to create a table called "Persons" that contains five columns: P_Id, LastName,
FirstName, Address, and City.
We use the following CREATE TABLE statement:
CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
The P_Id column is of type int and will hold a number. The LastName, FirstName, Address, and City
columns are of type varchar with a maximum length of 255 characters.
The empty "Persons" table will now look like this:
P_Id LastName FirstName Address City

The empty table can be filled with data with the INSERT INTO statement.

27) SQL Constraints

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).
We will focus on the following constraints:
 NOT NULL
 UNIQUE

SQL NOTES Page 52

  PRIMARY KEY
 FOREIGN KEY
 CHECK
 DEFAULT
The next chapters will describe each constraint in details.

28) SQL NOT NULL Constraint

By default, a table column can hold NULL values.

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.
The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert
SQL NOTES Page 53
a new record, or update a record without adding a value to this field.
The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL
values:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

29) SQL UNIQUE Constraint

SQL UNIQUE Constraint

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.

SQL NOTES Page 54

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per
table.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is
created:
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)
)
To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns,
use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
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)
)

SQL NOTES Page 55

SQL UNIQUE Constraint on ALTER TABLE
To create a UNIQUE constraint on the "P_Id" column when the table is already created, use the
following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD UNIQUE (P_Id)
To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns,
use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

To DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
DROP INDEX uc_PersonID
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

30) SQL PRIMARY KEY Constraint

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only one primary key.

SQL NOTES Page 56

SQL PRIMARY KEY Constraint on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:
MySQL:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple
columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

SQL PRIMARY KEY Constraint on ALTER TABLE

To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the
following SQL:
SQL NOTES Page 57
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple
columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must
already have been declared to not contain NULL values (when the table was first created).

To DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
DROP PRIMARY KEY
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

31) SQL FOREIGN KEY Constraint

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Let's illustrate the foreign key with an example. Look at the following two tables:
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes

SQL NOTES Page 58

2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 2
4 24562 1
Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy link between tables.
The FOREIGN KEY constraint also prevents that invalid data is inserted into the foreign key column,
because it has to be one of the values contained in the table it points to.

SQL FOREIGN KEY Constraint on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created:
MySQL:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple
columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders
SQL NOTES Page 59
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

SQL FOREIGN KEY Constraint on ALTER TABLE

To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created,
use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple
columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

To DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

SQL NOTES Page 60

32) SQL CHECK Constraint

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in
other columns in the row.

SQL NOTES Page 61

SQL CHECK Constraint on CREATE TABLE
The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is
created. The CHECK constraint specifies that the column "P_Id" must only include integers greater
than 0.
My SQL:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use
the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

SQL CHECK Constraint on ALTER TABLE

To create a CHECK constraint on the "P_Id" column when the table is already created, use the
following SQL:

SQL NOTES Page 62

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CHECK (P_Id>0)
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use
the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

To DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT chk_Person

33) SQL DEFAULT Constraint

SQL DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column.
The default value will be added to all new records, if no other value is specified.

SQL NOTES Page 63

SQL DEFAULT Constraint on CREATE TABLE
The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is
created:
My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
The DEFAULT constraint can also be used to insert system values, by using functions like
GETDATE():
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

SQL DEFAULT Constraint on ALTER TABLE

To create a DEFAULT constraint on the "City" column when the table is already created, use the
following SQL:
MySQL:
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

To DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:
MySQL:

SQL NOTES Page 64

ALTER TABLE Persons
ALTER City DROP DEFAULT
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

34) SQL CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables.
Indexes allow the database application to find data fast; without reading the whole table.

SQL NOTES Page 65

Indexes
An index can be created in a table to find data more quickly and efficiently.
The users cannot see the indexes, they are just used to speed up searches/queries.
Note: Updating a table with indexes takes more time than updating a table without (because the
indexes also need an update). So you should only create indexes on columns (and tables) that will be
frequently searched against.

SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax
for creating indexes in your database.

CREATE INDEX Example

The SQL statement below creates an index named "PIndex" on the "LastName" column in the
"Persons" table:
CREATE INDEX PIndex
ON Persons (LastName)
If you want to create an index on a combination of columns, you can list the column names within the
parentheses, separated by commas:
CREATE INDEX PIndex
ON Persons (LastName, FirstName)

35) SQL DROP INDEX, DROP TABLE, and DROP DATABASE

Indexes, tables, and databases can easily be deleted/removed with the DROP statement.

The DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

SQL NOTES Page 66

DROP INDEX Syntax for MS Access:
DROP INDEX index_name ON table_name

DROP INDEX Syntax for MS SQL Server:

DROP INDEX table_name.index_name

DROP INDEX Syntax for DB2/Oracle:

DROP INDEX index_name

DROP INDEX Syntax for MySQL:

ALTER TABLE table_name DROP INDEX index_name

The DROP TABLE Statement

The DROP TABLE statement is used to delete a table.
DROP TABLE table_name

The DROP DATABASE Statement

The DROP DATABASE statement is used to delete a database.
DROP DATABASE database_name

The TRUNCATE TABLE Statement

What if we only want to delete the data inside the table, and not the table itself?
Then, use the TRUNCATE TABLE statement:
TRUNCATE TABLE table_name

36) SQL ALTER TABLE Statement

The ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

SQL NOTES Page 67

SQL ALTER TABLE Syntax
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype
To delete a column in a table, use the following syntax (notice that some database systems don't allow
deleting a column):
ALTER TABLE table_name
DROP COLUMN column_name
To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name
ALTER COLUMN column_name datatype

SQL ALTER TABLE Example

Look at the "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to add a column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
ALTER TABLE Persons
ADD DateOfBirth date
Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type
specifies what type of data the column can hold. For a complete reference of all the data types
available in MS Access, MySQL, and SQL Server, go to our complete Data Types reference.
The "Persons" table will now like this:
P_Id LastName FirstName Address City DateOfBirth
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Change Data Type Example

Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.

SQL NOTES Page 68

We use the following SQL statement:
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year
Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two-digit or
four-digit format.

DROP COLUMN Example

Next, we want to delete the column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
ALTER TABLE Persons
DROP COLUMN DateOfBirth
The "Persons" table will now like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

37) SQL AUTO INCREMENT Field

Auto-increment allows a unique number to be generated when a new record is inserted into a table.

SQL NOTES Page 69

AUTO INCREMENT a Field
Very often we would like the value of the primary key field to be created automatically every time a
new record is inserted.
We would like to create an auto-increment field in a table.

Syntax for MySQL

The following SQL statement defines the "P_Id" column to be an auto-increment primary key field in
the "Persons" table:
CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new
record.
To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:
ALTER TABLE Persons AUTO_INCREMENT=100
To insert a new record into the "Persons" table, we will not have to specify a value for the "P_Id"
column (a unique value will be added automatically):
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')
The SQL statement above would insert a new record into the "Persons" table. The "P_Id" column
would be assigned a unique value. The "FirstName" column would be set to "Lars" and the
"LastName" column would be set to "Monsen".

Syntax for SQL Server

The following SQL statement defines the "P_Id" column to be an auto-increment primary key field in
the "Persons" table:
CREATE TABLE Persons

SQL NOTES Page 70

(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.
By default, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.
To specify that the "P_Id" column should start at value 10 and increment by 5, change the identity to
IDENTITY(10,5).
To insert a new record into the "Persons" table, we will not have to specify a value for the "P_Id"
column (a unique value will be added automatically):
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')
The SQL statement above would insert a new record into the "Persons" table. The "P_Id" column
would be assigned a unique value. The "FirstName" column would be set to "Lars" and the
"LastName" column would be set to "Monsen".

Syntax for Access

The following SQL statement defines the "P_Id" column to be an auto-increment primary key field in
the "Persons" table:
CREATE TABLE Persons
(
P_Id PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
The MS Access uses the AUTOINCREMENT keyword to perform an auto-increment feature.
By default, the starting value for AUTOINCREMENT is 1, and it will increment by 1 for each new record.
To specify that the "P_Id" column should start at value 10 and increment by 5, change the
autoincrement to AUTOINCREMENT(10,5).
To insert a new record into the "Persons" table, we will not have to specify a value for the "P_Id"
column (a unique value will be added automatically):
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')
The SQL statement above would insert a new record into the "Persons" table. The "P_Id" column

SQL NOTES Page 71

would be assigned a unique value. The "FirstName" column would be set to "Lars" and the
"LastName" column would be set to "Monsen".

Syntax for Oracle

In Oracle the code is a little bit more tricky.
You will have to create an auto-increment field with the sequence object (this object generates a
number sequence).
Use the following CREATE SEQUENCE syntax:
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10
The code above creates a sequence object called seq_person, that starts with 1 and will increment by
1. It will also cache up to 10 values for performance. The cache option specifies how many sequence
values will be stored in memory for faster access.
To insert a new record into the "Persons" table, we will have to use the nextval function (this function
retrieves the next value from seq_person sequence):
INSERT INTO Persons (P_Id,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')
The SQL statement above would insert a new record into the "Persons" table. The "P_Id" column
would be assigned the next number from the seq_person sequence. The "FirstName" column would
be set to "Lars" and the "LastName" column would be set to "Monsen".

38) SQL Views

A view is a virtual table.
This chapter shows how to create, update, and delete a view.

SQL NOTES Page 72

SQL CREATE VIEW Statement
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or
more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data
were coming from one single table.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Note: A view always shows up-to-date data! The database engine recreates the data, using the view's
SQL statement, every time a user queries a view.

SQL CREATE VIEW Examples

If you have the Northwind database you can see that it has several views installed by default.
The view "Current Product List" lists all active products (products that are not discontinued) from the
"Products" table. The view is created with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
We can query the view above as follows:
SELECT * FROM [Current Product List]
Another view in the Northwind sample database selects every product in the "Products" table with a
unit price higher than the average unit price:
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
We can query the view above as follows:
SELECT * FROM [Products Above Average Price]
Another view in the Northwind database calculates the total sale for each category in 1997. Note that
this view selects its data from another view called "Product Sales for 1997":
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]

SQL NOTES Page 73

GROUP BY CategoryName
We can query the view above as follows:
SELECT * FROM [Category Sales For 1997]
We can also add a condition to the query. Now we want to see the total sale only for the category
"Beverages":
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'

SQL Updating a View

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Now we want to add the "Category" column to the "Current Product List" view. We will update the
view with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

SQL Dropping a View

You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name

39) SQL Date Functions

SQL Dates
The most difficult part when working with dates is to be sure that the format of the date you are

SQL NOTES Page 74

trying to insert, matches the format of the date column in the database.
As long as your data contains only the date portion, your queries will work as expected. However, if a
time portion is involved, it gets complicated.
Before talking about the complications of querying for dates, we will look at the most important built-
in functions for working with dates.

MySQL Date Functions

The following table lists the most important built-in date functions in MySQL:
Function Description
NOW() Returns the current date and time
CURDATE() Returns the current date
CURTIME() Returns the current time
DATE() Extracts the date part of a date or date/time expression
EXTRACT() Returns a single part of a date/time
DATE_ADD() Adds a specified time interval to a date
DATE_SUB() Subtracts a specified time interval from a date
DATEDIFF() Returns the number of days between two dates
DATE_FORMAT() Displays date/time data in different formats

SQL Server Date Functions

The following table lists the most important built-in date functions in SQL Server:
Function Description
GETDATE() Returns the current date and time
DATEPART() Returns a single part of a date/time
DATEADD() Adds or subtracts a specified time interval from a date
DATEDIFF() Returns the time between two dates
CONVERT() Displays date/time data in different formats

SQL Date Data Types

MySQL comes with the following data types for storing a date or a date/time value in the database:

SQL NOTES Page 75

  DATE - format YYYY-MM-DD
 DATETIME - format: YYYY-MM-DD HH:MM:SS
 TIMESTAMP - format: YYYY-MM-DD HH:MM:SS
 YEAR - format YYYY or YY
SQL Server comes with the following data types for storing a date or a date/time value in the
database:
 DATE - format YYYY-MM-DD
 DATETIME - format: YYYY-MM-DD HH:MM:SS
 SMALLDATETIME - format: YYYY-MM-DD HH:MM:SS
 TIMESTAMP - format: a unique number
Note: The date types are chosen for a column when you create a new table in your database!
For an overview of all data types available, go to our complete Data Types reference.

SQL Working with Dates

You can compare two dates easily if there is no time component involved!
Assume we have the following "Orders" table:
OrderId ProductName OrderDate
1 Geitost 2008-11-11
2 Camembert Pierrot 2008-11-09
3 Mozzarella di Giovanni 2008-11-11
4 Mascarpone Fabioli 2008-10-29
Now we want to select the records with an OrderDate of "2008-11-11" from the table above.
We use the following SELECT statement:
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
The result-set will look like this:
OrderId ProductName OrderDate
1 Geitost 2008-11-11
3 Mozzarella di Giovanni 2008-11-11
Now, assume that the "Orders" table looks like this (notice the time component in the "OrderDate"
column):
OrderId ProductName OrderDate
1 Geitost 2008-11-11 13:23:44
2 Camembert Pierrot 2008-11-09 15:45:21
3 Mozzarella di Giovanni 2008-11-11 11:12:01
4 Mascarpone Fabioli 2008-10-29 14:56:59

SQL NOTES Page 76

If we use the same SELECT statement as above:
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
we will get no result! This is because the query is looking only for dates with no time portion.
Tip: If you want to keep your queries simple and easy to maintain, do not allow time components in
your dates!

SQL NOTES Page 77

40) SQL NULL Values
NULL values represent missing unknown data.
By default, a table column can hold NULL values.
This chapter will explain the IS NULL and IS NOT NULL operators.

SQL NULL Values

If a column in a table is optional, we can insert a new record or update an existing record without
adding a value to this column. This means that the field will be saved with a NULL value.
NULL values are treated differently from other values.
NULL is used as a placeholder for unknown or inapplicable values.
Note: It is not possible to compare NULL and 0; they are not equivalent.

SQL Working with NULL Values

Look at the following "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Stavanger
Suppose that the "Address" column in the "Persons" table is optional. This means that if we insert a
record with no value for the "Address" column, the "Address" column will be saved with a NULL value.
How can we test for NULL values?
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.

SQL IS NULL
How do we select only the records with NULL values in the "Address" column?
We will have to use the IS NULL operator:
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

SQL NOTES Page 78

The result-set will look like this:
LastName FirstName Address
Hansen Ola
Pettersen Kari
Tip: Always use IS NULL to look for NULL values.

SQL IS NOT NULL

How do we select only the records with no NULL values in the "Address" column?
We will have to use the IS NOT NULL operator:
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL
The result-set will look like this:
LastName FirstName Address
Svendson Tove Borgvn 23
In the next chapter we will look at the ISNULL(), NVL(), IFNULL() and COALESCE() functions.

SQL NOTES Page 79

41) SQL NULL Functions

SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions

Look at the following "Products" table:
P_Id ProductName UnitPrice UnitsInStock UnitsOnOrder
1 Jarlsberg 10.45 16 15
2 Mascarpone 32.56 23
3 Gorgonzola 15.67 9 20
Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values.
We have the following SELECT statement:
SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products
In the example above, if any of the "UnitsOnOrder" values are NULL, the result is NULL.
Microsoft's ISNULL() function is used to specify how we want to treat NULL values.
The NVL(), IFNULL(), and COALESCE() functions can also be used to achieve the same result.
In this case we want NULL values to be zero.
Below, if "UnitsOnOrder" is NULL it will not harm the calculation, because ISNULL() returns a zero if
the value is NULL:
SQL Server / MS Access
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products
Oracle
Oracle does not have an ISNULL() function. However, we can use the NVL() function to achieve the
same result:
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products
MySQL
MySQL does have an ISNULL() function. However, it works a little bit different from Microsoft's
ISNULL() function.
In MySQL we can use the IFNULL() function, like this:
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
or we can use the COALESCE() function, like this:
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products

SQL NOTES Page 80

SQL Data Types
Data types and ranges for Microsoft Access, MySQL and SQL Server.

Microsoft Access Data Types

Data type Description Storage
Text Use for text or combinations of text and numbers. 255 characters
maximum
Memo Memo is used for larger amounts of text. Stores up to 65,536
characters. Note: You cannot sort a memo field. However, they are
searchable
Byte Allows whole numbers from 0 to 255 1 byte
Integer Allows whole numbers between -32,768 and 32,767 2 bytes
Long Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes
Single Single precision floating-point. Will handle most decimals 4 bytes
Double Double precision floating-point. Will handle most decimals 8 bytes
Currency Use for currency. Holds up to 15 digits of whole dollars, plus 4 decimal 8 bytes
places. Tip: You can choose which country's currency to use
AutoNumber AutoNumber fields automatically give each record its own number, 4 bytes
usually starting at 1
Date/Time Use for dates and times 8 bytes
Yes/No A logical field can be displayed as Yes/No, True/False, or On/Off. In 1 bit
code, use the constants True and False (equivalent to -1 and 0). Note:
Null values are not allowed in Yes/No fields
Ole Object Can store pictures, audio, video, or other BLOBs (Binary Large OBjects) up to 1GB
Hyperlink Contain links to other files, including web pages
Lookup Wizard Let you type a list of options, which can then be chosen from a drop- 4 bytes
down list

SQL NOTES Page 81

MySQL Data Types
In MySQL there are three main types : text, number, and Date/Time types.
Text types:
Data type Description
CHAR(size) Holds a fixed length string (can contain letters, numbers, and special characters).
The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size) Holds a variable length string (can contain letters, numbers, and special
characters). The maximum size is specified in parenthesis. Can store up to 255
characters. Note: If you put a greater value than 255 it will be converted to a
TEXT type
TINYTEXT Holds a string with a maximum length of 255 characters
TEXT Holds a string with a maximum length of 65,535 characters
BLOB For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.) Let you enter a list of possible values. You can list up to 65535 values in an ENUM
list. If a value is inserted that is not in the list, a blank value will be inserted.

Note: The values are sorted in the order you enter them.

You enter the possible values in this format: ENUM('X','Y','Z')

SET Similar to ENUM except that SET may contain up to 64 list items and can store
more than one choice
Number types:
Data type Description
TINYINT(size) -128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be
specified in parenthesis
SMALLINT(size) -32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits
may be specified in parenthesis
MEDIUMINT(size) -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number
of digits may be specified in parenthesis
INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The
maximum number of digits may be specified in parenthesis
BIGINT(size) -9223372036854775808 to 9223372036854775807 normal. 0 to
18446744073709551615 UNSIGNED*. The maximum number of digits may be
specified in parenthesis

SQL NOTES Page 82

FLOAT(size,d) A small number with a floating decimal point. The maximum number of digits
may be specified in the size parameter. The maximum number of digits to the
right of the decimal point is specified in the d parameter
DOUBLE(size,d) A large number with a floating decimal point. The maximum number of digits
may be specified in the size parameter. The maximum number of digits to the
right of the decimal point is specified in the d parameter
DECIMAL(size,d) A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum
number of digits may be specified in the size parameter. The maximum number of
digits to the right of the decimal point is specified in the d parameter
*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative
to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of
a negative number.
Date types:
Data type Description
DATE() A date. Format: YYYY-MM-DD

Note: The supported range is from '1000-01-01' to '9999-12-31'

DATETIME() *A date and time combination. Format: YYYY-MM-DD HH:MM:SS

Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31

23:59:59'

TIMESTAMP() *A timestamp. TIMESTAMP values are stored as the number of seconds since the
Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MM:SS

Note: The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09

03:14:07' UTC

TIME() A time. Format: HH:MM:SS

Note: The supported range is from '-838:59:59' to '838:59:59'

YEAR() A year in two-digit or four-digit format.

Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-
digit format: 70 to 69, representing years from 1970 to 2069

*Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT
or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP
also accepts various formats, like YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or
YYMMDD.

SQL NOTES Page 83

SQL Server Data Types
Character strings:
Data type Description Storage
char(n) Fixed-length character string. Maximum 8,000 characters n
varchar(n) Variable-length character string. Maximum 8,000 characters
varchar(max) Variable-length character string. Maximum 1,073,741,824 characters
text Variable-length character string. Maximum 2GB of text data
Unicode strings:
Data type Description Storage
nchar(n) Fixed-length Unicode data. Maximum 4,000 characters
nvarchar(n) Variable-length Unicode data. Maximum 4,000 characters
nvarchar(max) Variable-length Unicode data. Maximum 536,870,912 characters
ntext Variable-length Unicode data. Maximum 2GB of text data
Binary types:
Data type Description Storage
bit Allows 0, 1, or NULL
binary(n) Fixed-length binary data. Maximum 8,000 bytes
varbinary(n) Variable-length binary data. Maximum 8,000 bytes
varbinary(max) Variable-length binary data. Maximum 2GB
image Variable-length binary data. Maximum 2GB
Number types:
Data type Description Storage
tinyint Allows whole numbers from 0 to 255 1 byte
smallint Allows whole numbers between -32,768 and 32,767 2 bytes
int Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes
bigint Allows whole numbers between -9,223,372,036,854,775,808 and 8 bytes
9,223,372,036,854,775,807
decimal(p,s) Fixed precision and scale numbers. 5-17
bytes
Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that

can be stored (both to the left and to the right of the decimal point). p

SQL NOTES Page 84

 must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the

right of the decimal point. s must be a value from 0 to p. Default value
is 0

numeric(p,s) Fixed precision and scale numbers. 5-17

bytes
Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that

can be stored (both to the left and to the right of the decimal point). p
must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the

right of the decimal point. s must be a value from 0 to p. Default value
is 0

smallmoney Monetary data from -214,748.3648 to 214,748.3647 4 bytes

money Monetary data from -922,337,203,685,477.5808 to 8 bytes
922,337,203,685,477.5807
float(n) Floating precision number data from -1.79E + 308 to 1.79E + 308. 4 or 8
bytes
The n parameter indicates whether the field should hold 4 or 8 bytes.
float(24) holds a 4-byte field and float(53) holds an 8-byte field.
Default value of n is 53.

real Floating precision number data from -3.40E + 38 to 3.40E + 38 4 bytes

Date types:
Data type Description Storage
datetime From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 8 bytes
milliseconds
datetime2 From January 1, 0001 to December 31, 9999 with an accuracy of 100 6-8 bytes
nanoseconds
smalldatetime From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute 4 bytes
date Store a date only. From January 1, 0001 to December 31, 9999 3 bytes
time Store a time only to an accuracy of 100 nanoseconds 3-5 bytes
datetimeoffset The same as datetime2 with the addition of a time zone offset 8-10
bytes
timestamp Stores a unique number that gets updated every time a row gets
created or modified. The timestamp value is based upon an internal

SQL NOTES Page 85

 clock and does not correspond to real time. Each table may have only
one timestamp variable
Other data types:
Data type Description
sql_variant Stores up to 8,000 bytes of data of various data types, except text, ntext, and
timestamp
uniqueidentifier Stores a globally unique identifier (GUID)
xml Stores XML formatted data. Maximum 2GB
cursor Stores a reference to a cursor used for database operations
table Stores a result-set for later processing

SQL NOTES Page 86

42) SQL Functions
SQL has many built-in functions for performing calculations on data.

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
 AVG() - Returns the average value
 COUNT() - Returns the number of rows
 FIRST() - Returns the first value
 LAST() - Returns the last value
 MAX() - Returns the largest value
 MIN() - Returns the smallest value
 SUM() - Returns the sum

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.
Useful scalar functions:
 UCASE() - Converts a field to upper case
 LCASE() - Converts a field to lower case
 MID() - Extract characters from a text field
 LEN() - Returns the length of a text field
 ROUND() - Rounds a numeric field to the number of decimals specified
 NOW() - Returns the current system date and time
 FORMAT() - Formats how a field is to be displayed
Tip: The aggregate functions and the scalar functions will be explained in details in the next chapters.

SQL NOTES Page 87

43) SQL AVG() Function

The AVG() Function

The AVG() function returns the average value of a numeric column.

SQL AVG() Syntax

SELECT AVG(column_name) FROM table_name

SQL AVG() Example

We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the average value of the "OrderPrice" fields.
We use the following SQL statement:
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
The result-set will look like this:
OrderAverage
950
Now we want to find the customers that have an OrderPrice value higher than the average OrderPrice
value.
We use the following SQL statement:
SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
The result-set will look like this:
Customer

SQL NOTES Page 88

Hansen
Nilsen
Jensen

44) SQL COUNT() Function

The COUNT() function returns the number of rows that matches a specified criteria.

SQL COUNT(column_name) Syntax

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of
the specified column:
SELECT COUNT(column_name) FROM table_name

SQL COUNT(*) Syntax

The COUNT(*) function returns the number of records in a table:
SELECT COUNT(*) FROM table_name

SQL COUNT(DISTINCT column_name) Syntax

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified
column:
SELECT COUNT(DISTINCT column_name) FROM table_name
Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

SQL COUNT(column_name) Example

We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to count the number of orders from "Customer Nilsen".

SQL NOTES Page 89

We use the following SQL statement:
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen'
The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders in
total:

CustomerNilsen
2

SQL COUNT(*) Example

If we omit the WHERE clause, like this:
SELECT COUNT(*) AS NumberOfOrders FROM Orders
The result-set will look like this:
NumberOfOrders
6
which is the total number of rows in the table.

SQL COUNT(DISTINCT column_name) Example

Now we want to count the number of unique customers in the "Orders" table.
We use the following SQL statement:
SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders
The result-set will look like this:
NumberOfCustomers
3
which is the number of unique customers (Hansen, Nilsen, and Jensen) in the "Orders" table.

SQL NOTES Page 90

45) SQL FIRST() Function

The FIRST() Function

The FIRST() function returns the first value of the selected column.

SQL FIRST() Syntax

SELECT FIRST(column_name) FROM table_name

SQL FIRST() Example

We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the first value of the "OrderPrice" column.
We use the following SQL statement:
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders
Tip: Workaround if FIRST() function is not supported:

SELECT OrderPrice FROM Orders ORDER BY O_Id LIMIT 1

The result-set will look like this:
FirstOrderPrice
1000

SQL NOTES Page 91

46) SQL LAST() Function

The LAST() Function

The LAST() function returns the last value of the selected column.

SQL LAST() Syntax

SELECT LAST(column_name) FROM table_name

SQL LAST() Example

We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the last value of the "OrderPrice" column.
We use the following SQL statement:
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders
Tip: Workaround if LAST() function is not supported:

SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1

The result-set will look like this:
LastOrderPrice
100

SQL NOTES Page 92

47) SQL MAX() Function

The MAX() Function

The MAX() function returns the largest value of the selected column.

SQL MAX() Syntax

SELECT MAX(column_name) FROM table_name

SQL MAX() Example

We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the largest value of the "OrderPrice" column.
We use the following SQL statement:
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
The result-set will look like this:
LargestOrderPrice
2000

SQL NOTES Page 93

48) SQL MIN() Function

The MIN() Function

The MIN() function returns the smallest value of the selected column.

SQL MIN() Syntax

SELECT MIN(column_name) FROM table_name

SQL MIN() Example

We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the smallest value of the "OrderPrice" column.
We use the following SQL statement:
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders
The result-set will look like this:
SmallestOrderPrice
100

SQL NOTES Page 94

49) SQL SUM() Function

The SUM() Function

The SUM() function returns the total sum of a numeric column.

SQL SUM() Syntax

SELECT SUM(column_name) FROM table_name

SQL SUM() Example

We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the sum of all "OrderPrice" fields".
We use the following SQL statement:
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders
The result-set will look like this:
OrderTotal

SQL NOTES Page 95

5700

50) SQL GROUP BY Statement

Aggregate functions often need an added GROUP BY statement.

The GROUP BY Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set
by one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

SQL GROUP BY Example

We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen

SQL NOTES Page 96

5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the total sum (total order) of each customer.
We will have to use the GROUP BY statement to group the customers.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
The result-set will look like this:
Customer SUM(OrderPrice)
Hansen 2000
Nilsen 1700
Jensen 2000
Nice! Isn't it? :)
Let's see what happens if we omit the GROUP BY statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
The result-set will look like this:
Customer SUM(OrderPrice)
Hansen 5700
Nilsen 5700
Hansen 5700
Hansen 5700
Jensen 5700
Nilsen 5700
The result-set above is not what we wanted.
Explanation of why the above SELECT statement cannot be used: The SELECT statement above has
two columns specified (Customer and SUM(OrderPrice). The "SUM(OrderPrice)" returns a single value
(that is the total sum of the "OrderPrice" column), while "Customer" returns 6 values (one value for
each row in the "Orders" table). This will therefore not give us the correct result. However, you have
seen that the GROUP BY statement solves this problem.

GROUP BY More Than One Column

We can also use the GROUP BY statement on more than one column, like this:
SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

SQL NOTES Page 97

51) SQL HAVING Clause

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate
functions.

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

SQL HAVING Example

We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen

SQL NOTES Page 98

5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find if any of the customers have a total order of less than 2000.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
The result-set will look like this:
Customer SUM(OrderPrice)
Nilsen 1700
Now we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500.
We add an ordinary WHERE clause to the SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

The result-set will look like this:

Customer SUM(OrderPrice)
Hansen 2000
Jensen 2000

SQL NOTES Page 99

52) SQL UCASE() Function

The UCASE() Function

The UCASE() function converts the value of a field to uppercase.

SQL UCASE() Syntax

SELECT UCASE(column_name) FROM table_name

Syntax for SQL Server

SELECT UPPER(column_name) FROM table_name

SQL UCASE() Example

We have the following "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to select the content of the "LastName" and "FirstName" columns above, and convert
SQL NOTES Page 100
the "LastName" column to uppercase.
We use the following SELECT statement:
SELECT UCASE(LastName) as LastName,FirstName FROM Persons
The result-set will look like this:
LastName FirstName
HANSEN Ola
SVENDSON Tove
PETTERSEN Kari

53) SQL LCASE() Function

The LCASE() Function

The LCASE() function converts the value of a field to lowercase.

SQL LCASE() Syntax

SELECT LCASE(column_name) FROM table_name

Syntax for SQL Server

SELECT LOWER(column_name) FROM table_name

SQL LCASE() Example

We have the following "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes

SQL NOTES Page 101

3 Pettersen Kari Storgt 20 Stavanger
Now we want to select the content of the "LastName" and "FirstName" columns above, and convert
the "LastName" column to lowercase.
We use the following SELECT statement:
SELECT LCASE(LastName) as LastName,FirstName FROM Persons
The result-set will look like this:
LastName FirstName
hansen Ola
svendson Tove
pettersen Kari

54) SQL MID() Function

The MID() Function

The MID() function is used to extract characters from a text field.

SQL MID() Syntax

SELECT MID(column_name,start[,length]) FROM table_name

Parameter Description
column_name Required. The field to extract characters from
start Required. Specifies the starting position (starts at 1)
length Optional. The number of characters to return. If omitted, the MID() function
returns the rest of the text

SQL MID() Example

We have the following "Persons" table:
SQL NOTES Page 102
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to extract the first four characters of the "City" column above.
We use the following SELECT statement:
SELECT MID(City,1,4) as SmallCity FROM Persons
The result-set will look like this:
SmallCity
Sand
Sand
Stav

55) SQL LEN() Function

The LEN() Function

The LEN() function returns the length of the value in a text field.

SQL LEN() Syntax

SELECT LEN(column_name) FROM table_name

SQL LEN() Example

We have the following "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to select the length of the values in the "Address" column above.
SQL NOTES Page 103
We use the following SELECT statement:
SELECT LEN(Address) as LengthOfAddress FROM Persons
The result-set will look like this:
LengthOfAddress
12
9
9

56) SQL ROUND() Function

The ROUND() Function

The ROUND() function is used to round a numeric field to the number of decimals specified.

SQL ROUND() Syntax

SELECT ROUND(column_name,decimals) FROM table_name

Parameter Description
column_name Required. The field to round.
decimals Required. Specifies the number of decimals to be returned.

SQL ROUND() Example

We have the following "Products" table:

SQL NOTES Page 104

Prod_Id ProductName Unit UnitPrice
1 Jarlsberg 1000 g 10.45
2 Mascarpone 1000 g 32.56
3 Gorgonzola 1000 g 15.67
Now we want to display the product name and the price rounded to the nearest integer.
We use the following SELECT statement:
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products
The result-set will look like this:
ProductName UnitPrice
Jarlsberg 10
Mascarpone 33
Gorgonzola 16

57) SQL NOW() Function

The NOW() Function

The NOW() function returns the current system date and time.

SQL NOW() Syntax

SELECT NOW() FROM table_name

SQL NOW() Example

We have the following "Products" table:
Prod_Id ProductName Unit UnitPrice
1 Jarlsberg 1000 g 10.45
2 Mascarpone 1000 g 32.56

SQL NOTES Page 105

3 Gorgonzola 1000 g 15.67
Now we want to display the products and prices per today's date.
We use the following SELECT statement:
SELECT ProductName, UnitPrice, Now() as PerDate FROM Products
The result-set will look like this:
ProductName UnitPrice PerDate
Jarlsberg 10.45 10/7/2008 11:25:02 AM
Mascarpone 32.56 10/7/2008 11:25:02 AM
Gorgonzola 15.67 10/7/2008 11:25:02 AM

58) SQL FORMAT() Function

The FORMAT() Function

The FORMAT() function is used to format how a field is to be displayed.

SQL FORMAT() Syntax

SELECT FORMAT(column_name,format) FROM table_name

Parameter Description
column_name Required. The field to be formatted.
format Required. Specifies the format.

SQL NOTES Page 106

SQL FORMAT() Example
We have the following "Products" table:
Prod_Id ProductName Unit UnitPrice
1 Jarlsberg 1000 g 10.45
2 Mascarpone 1000 g 32.56
3 Gorgonzola 1000 g 15.67
Now we want to display the products and prices per today's date (with today's date displayed in the
following format "YYYY-MM-DD").
We use the following SELECT statement:
SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products
The result-set will look like this:
ProductName UnitPrice PerDate
Jarlsberg 10.45 2008-10-07
Mascarpone 32.56 2008-10-07
Gorgonzola 15.67 2008-10-07

59) SQL Quick Reference

SQL Statement Syntax

AND / OR SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition
ALTER TABLE ALTER TABLE table_name
ADD column_name datatype

or

SQL NOTES Page 107

 ALTER TABLE table_name
DROP COLUMN column_name

AS (alias) SELECT column_name AS column_alias

FROM table_name

or

SELECT column_name
FROM table_name AS table_alias

BETWEEN SELECT column_name(s)

FROM table_name
WHERE column_name
BETWEEN value1 AND value2
CREATE DATABASE CREATE DATABASE database_name
CREATE TABLE CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,
...
)
CREATE INDEX CREATE INDEX index_name
ON table_name (column_name)

or

CREATE UNIQUE INDEX index_name

ON table_name (column_name)

CREATE VIEW CREATE VIEW view_name AS

SELECT column_name(s)
FROM table_name
WHERE condition
DELETE DELETE FROM table_name
WHERE some_column=some_value

or

DELETE FROM table_name

(Note: Deletes the entire table!!)

SQL NOTES Page 108

 DELETE * FROM table_name
(Note: Deletes the entire table!!)

DROP DATABASE DROP DATABASE database_name

DROP INDEX DROP INDEX table_name.index_name (SQL Server)
DROP INDEX index_name ON table_name (MS Access)
DROP INDEX index_name (DB2/Oracle)
ALTER TABLE table_name
DROP INDEX index_name (MySQL)
DROP TABLE DROP TABLE table_name
GROUP BY SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
IN SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
INSERT INTO INSERT INTO table_name
VALUES (value1, value2, value3,....)

or

INSERT INTO table_name

(column1, column2, column3,...)
VALUES (value1, value2, value3,....)

INNER JOIN SELECT column_name(s)

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

SQL NOTES Page 109

 RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
FULL JOIN SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LIKE SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
ORDER BY SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]
SELECT SELECT column_name(s)
FROM table_name
SELECT * SELECT *
FROM table_name
SELECT DISTINCT SELECT DISTINCT column_name(s)
FROM table_name
SELECT INTO SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_table_name

or

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_table_name

SELECT TOP SELECT TOP number|percent column_name(s)

FROM table_name
TRUNCATE TABLE TRUNCATE TABLE table_name
UNION SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION ALL SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
UPDATE UPDATE table_name
SET column1=value, column2=value,...
WHERE some_column=some_value
WHERE SELECT column_name(s)

SQL NOTES Page 110

 FROM table_name
WHERE column_name operator value

Highest Salary

Find highest salary sql server

o SELECT MAX(Salary) FROM Employee

Find 2nd highest salary sql server

o SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )

How to calculate highest salary:

Suppose that you are given the following simple database table called Employee that has 2 columns
named Employee ID and Salary:

Employee

Employee ID Salary

SQL NOTES Page 111

3 200

4 800

7 450

Write a SQL query to get the second highest salary from the table above. Also write a query to find
the nth highest salary in SQL, where n can be any number.

The easiest way to start with a problem like this is to ask yourself a simpler question first. So, let’s ask
ourselves how can we find the highest salary in a table? Well, you probably know that is actually really
easy – we can just use the MAX aggregate function:

select MAX(Salary) from Employee;

Remember that SQL is based on set theory

You should remember that SQL uses sets as the foundation for most of its queries. So, the question is
how can we use set theory to find the 2nd highest salary in the table above? Think about it on your
own for a bit – even if you do not remember much about sets, the answer is very easy to understand
and something that you might be able to come up with on your own.

Figuring out the answer to find the 2nd highest salary

What if we try to exclude the highest salary value from the result set returned by the SQL that we
run? If we remove the highest salary from a group of salary values, then we will have a new group of
values whose highest salary is actually the 2nd highest in theoriginal Employee table.

So, if we can somehow select the highest value from a result set thatexcludes the highest value, then
we would actually be selecting the 2nd highest salary value. Think about that carefully and see if you
can come up with the actual SQL yourself before you read the answer that we provide below. Here is a
small hint to help you get started: you will have to use the “NOT IN” SQL operator.

Solution to finding the 2nd highest salary in SQL

Now, here is what the SQL will look like:

SELECT MAX(Salary) FROM Employee

WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )

Running the SQL above would return us “450”, which is of course the 2nd highest salary in the

SQL NOTES Page 112

Employee table.

Subscribe to our newsletter for more free interview questions.

An explanation of the solution

The SQL above first finds the highest salary value in the Employee table using “(select MAX(Salary)
from Employee)”. Then, adding the “WHERE Salary NOT IN” in front basically creates a new set of
Salary values that does not include the highest Salary value. For instance, if the highest salary in the
Employee table is 200,000 then that value will be excluded from the results using the “NOT IN”
operator, and all values except for 200,000 will be retained in the results.

This now means that the highest value in this new result set will actually be the 2nd highest value in
the Employee table. So, we then select the max Salary from the new result set, and that gives us 2nd
highest Salary in the Employee table. And that is how the query above works.

An alternative solution using the not equals SQL operator

We can actually use the not equals operator – the “<>” – instead of the NOT IN operator as an
alternative solution to this problem. This is what the SQL would look like:

select MAX(Salary) from Employee

WHERE Salary <> (select MAX(Salary) from Employee )

How would you write a SQL query to find the Nth highest salary?

What we did above was write a query to find the 2nd highest Salary value in the Employee table. But,
another commonly asked interview question is how can we use SQL to find the Nth highest salary,
where N can be any number whether it’s the 3rd highest, 4th highest, 5th highest, 10th highest, etc?
This is also an interesting question – try to come up with an answer yourself before reading the one
below to see what you come up with.

The answer and explanation to finding the nth highest salary in SQL

Here we will present one possible answer to finding the nth highest salary first, and the explanation
of that answer after since it’s actually easier to understand that way. Note that the first answer we
present is actually not optimal from a performance standpoint since it uses a subquery, but we think
that it will be interesting for you to learn about because you might just learn something new about
SQL. If you want to see the more optimal solutions first, you can skip down to the sections that says
“Find the nth highest salary without a subquery” instead.

The SQL below will give you the correct answer – but you will have to plug in an actual value for N of
course. This SQL to find the Nth highest salary should work in SQL Server, MySQL, DB2, Oracle,
Teradata, and almost any other RDBMS:

SQL NOTES Page 113

SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT([Link]))
FROM Employee Emp2
WHERE [Link] > [Link])

How does the query above work?

The query above can be quite confusing if you have not seen anything like it before – pay special
attention to the fact that “Emp1″ appears in both the subquery (also known as an inner query) and
the “outer” query. The outer query is just the part of the query that is not the subquery/inner query –
both parts of the query are clearly labeled in the comments.

The subquery is a correlated subquery

The subquery in the SQL above is actually a specific type of subquery known as acorrelated subquery.
The reason it is called a correlated subquery is because the the subquery uses a value from the outer
query in it’s WHERE clause. In this case that value is the Emp1 table alias as we pointed out earlier. A
normal subquery can be runindependently of the outer query, but a correlated subquery can NOT be
run independently of the outer query. If you want to read more about the differences between
correlated and uncorrelated subqueries you can go here: Correlated vs Uncorrelated Subqueries.

The most important thing to understand in the query above is that the subquery is evaluated each and
every time a row is processed by the outer query. In other words, the inner query can not be
processed independently of the outer query since the inner query uses the Emp1 value as well.

Finding nth highest salary example and explanation

Let’s step through an actual example to see how the query above will actually execute step by step.
Suppose we are looking for the 2nd highest Salary value in our table above, so our N is 2. This means
that the query will look like this:

SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT([Link]))
FROM Employee Emp2
WHERE [Link] > [Link])

You can probably see that Emp1 and Emp2 are just aliases for the same Employee table – it’s like we
just created 2 separate clones of the Employee table and gave them different names.

Understanding and visualizing how the query above works

SQL NOTES Page 114

Let’s assume that we are using this data:

Employee

Employee ID Salary

3 200

4 800

7 450

For the sake of our explanation, let’s assume that N is 2 – so the query is trying to find the 2nd highest
salary in the Employee table. The first thing that the query above does is process the very first row of
the Employee table, which has an alias of Emp1.

The salary in the first row of the Employee table is 200. Because the subquery is correlated to the
outer query through the alias Emp1, it means that when the first row is processed, the query will
essentially look like this – note that all we did is replace [Link] with the value of 200:

SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT([Link]))
FROM Employee Emp2
WHERE [Link] > 200)

So, what exactly is happening when that first row is processed? Well, if you pay special attention to
the subquery you will notice that it’s basically searching for the count of salary entries in the
Employee table that are greater than 200. Basically, the subquery is trying to find how many salary
entries are greater than 200. Then, that count of salary entries is checked to see if it equals 1 in the
outer query, and if so then everything from that particular row in Emp1 will be returned.

Note that Emp1 and Emp2 are both aliases for the same table – Employee. Emp2 is only being used in
the subquery to compare all the salary values to the current salary value chosen in Emp1. This allows
us to find the number of salary entries (the count) that are greater than 200. And if this number is
equal to N-1 (which is 1 in our case) then we know that we have a winner – and that we have found
our answer.

But, it’s clear that the subquery will return a 2 when [Link] is 200, because there are clearly 2
salaries greater than 200 in the Employee table. And since 2 is not equal to 1, the salary of 200 will
clearly not be returned.

So, what happens next? Well, the SQL processor will move on to the next row which is 800, and the
resulting query looks like this:

SQL NOTES Page 115

SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT([Link]))
FROM Employee Emp2
WHERE [Link] > 800)

Since there are no salaries greater than 800, the query will move on to the last row and will of course
find the answer as 450. This is because 800 is greater than 450, and the count will be 1. More
precisely, the entire row with the desired salary would be returned, and this is what it would look like:
EmployeeID Salary
7 450

It’s also worth pointing out that the reason DISTINCT is used in the query above is because there may
be duplicate salary values in the table. In that scenario, we only want to count repeated salaries just
once, which is exactly why we use the DISTINCT operator.

A high level summary of how the query works

Let’s go through a high level summary of how someone would have come up with the SQL in the
first place – since we showed you the answer first without really going through the thought process
one would use to arrive at that answer.

Think of it this way – we are looking for a pattern that will lead us to the answer. One way to look at it
is that the 2nd highest salary would have just one salary that is greater than it. The 4th highest salary
would have 3 salaries that are greater than it. In more general terms, in order to find the Nth highest
salary, we just find the salary that has exactly N-1 salaries greater than itself. And that is exactly
what the query above accomplishes – it simply finds the salary that has N-1 salaries greater than itself
and returns that value as the answer.

Find the nth highest salary using the TOP keyword in SQL Server

We can also use the TOP keyword (for databases that support the TOP keyword, like SQL Server) to
find the nth highest salary. Here is some fairly simply SQL that would help us do that:

SELECT TOP 1 Salary

FROM (

SQL NOTES Page 116

 SELECT DISTINCT TOP N Salary
FROM Employee
ORDER BY Salary DESC
) AS Emp
ORDER BY Salary

To understand the query above, first look at the subquery, which simply finds the N highest salaries in
the Employee table and arranges them in descending order. Then, the outer query will actually
rearrange those values in ascending order, which is what the very last line “ORDER BY Salary” does,
because of the fact that the ORDER BY Default is to sort values in ASCENDING order. Finally, that
means the Nth highest salary will be at the top of the list of salaries, which means we just want the
first row, which is exactly what “SELECT TOP 1 Salary” will do for us!

Find the nth highest salary without using the TOP keyword

There are many other solutions to finding the nth highest salary that do not need to use the TOP
keyword, one of which we already went over. Keep reading for more solutions.

Find the nth highest salary in SQL without a subquery

The solution we gave above actually does not do well from a performance standpoint. This is because
the use of the subquery can really slow down the query. With that in mind, let’s go through some
different solutions to this problem for different database vendors. Because each database vendor
(whether it’s MySQL, Oracle, or SQL Server) has a different SQL syntax and functions, we will go
through solutions for specific vendors. But keep in mind that the solution presented above using a
subquery should work across different database vendors.

Find the nth highest salary in MySQL

In MySQL, we can just use the LIMIT clause along with an offset to find the nth highest salary. If that
doesn’t make sense take a look at the MySQL-specific SQL to see how we can do this:

SELECT Salary FROM Employee

ORDER BY Salary DESC LIMIT n-1,1

Note that the DESC used in the query above simply arranges the salaries in descending order – so
from highest salary to lowest. Then, the key part of the query to pay attention to is the “LIMIT N-1, 1″.
The LIMIT clause takes two arguments in that query – the first argument specifies the offset of the first
row to return, and the second specifies the maximum number of rows to return. So, it’s saying that
the offset of the first row to return should be N-1, and the max number of rows to return is 1. What
exactly is the offset? Well, the offset is just a numerical value that represents the number of rows from
the very first row, and since the rows are arranged in descending order we know that the row at an
offset of N-1 will contain the (N-1)th highest salary.

Find the nth highest salary in SQL Server

SQL NOTES Page 117

In SQL Server, there is no such thing as a LIMIT clause. But, we can still use the offset to find the nth
highest salary without using a subquery – just like the solution we gave above in MySQL syntax. But,
the SQL Server syntax will be a bit different. Here is what it would look like:

SELECT Salary FROM Employee

ORDER BY Salary DESC OFFSET N-1 ROW(S)
FETCH FIRST ROW ONLY

Note that I haven’t personally tested the SQL above, and I believe that it will only work in SQL Server
2012 and up. Let me know in the comments if you notice anything else about the query.

Find the nth highest salary in Oracle using rownum

Oracle syntax doesn’t support using an offset like MySQL and SQL Server, but we can actually use the
row_number analytic function in Oracle to solve this problem. Here is what the Oracle-specific SQL
would look like to find the nth highest salary:

select * from (
select Emp.*,
row_number() over (order by Salary DESC) rownumb
from Employee Emp
)
where rownumb = n; /*n is nth highest salary*/

The first thing you should notice in the query above is that inside the subquery the salaries are
arranged in descending order. Then, the row_number analytic function is applied against the list of
descending salaries. Applying the row_number function against the list of descending salaries means
that each row will be assigned a row number starting from 1. And since the rows are arranged in
descending order the row with the highest salary will have a 1 for the row number. Note that the row
number is given the alias rownumb in the SQL above.

This means that in order to find the 3rd or 4th highest salary we simply look for the 3rd or 4th row.
The query above will then compare the rownumb to n, and if they are equal will return everything in
that row. And that will be our answer!

Find the nth highest salary in Oracle using RANK

Oracle also provides a RANK function that just assigns a ranking numeric value (with 1 being the
highest) for some sorted values. So, we can use this SQL in Oracle to find the nth highest salary using
the RANK function:

SQL NOTES Page 118

select * FROM (
select EmployeeID, Salary
,rank() over (order by Salary DESC) ranking
from Employee
)
WHERE ranking = N;

The rank function will assign a ranking to each row starting from 1. This query is actually quite similar
to the one where we used the row_number() analytic function, and works in the same way as well.

80+ SQL Queries and Answers

able Name : Employee

Employee_id First_name Last_nam Salary Joining_date Departmen

SQL NOTES Page 119

 e t

1 John Abraham 1000000 01-JAN-13 12.00.00 AM Banking

2 Michael Clarke 800000 01-JAN-13 12.00.00 AM Insurance

3 Roy Thomas 700000 01-FEB-13 12.00.00 AM Banking

4 Tom Jose 600000 01-FEB-13 12.00.00 AM Insurance

5 Jerry Pinto 650000 01-FEB-13 12.00.00 AM Insurance

6 Philip Mathew 750000 01-JAN-13 12.00.00 AM Services

7 TestName1 123 650000 01-JAN-13 12.00.00 AM Services

8 TestName2 Lname% 600000 01-FEB-13 12.00.00 AM Insurance

Table Name : Incentives

Employee_ref_id Incentive_date Incentive_amount

1 01-FEB-13 5000

2 01-FEB-13 3000

3 01-FEB-13 4000

1 01-JAN-13 4500

2 01-JAN-13 3500

SQL NOTES Page 120

SQL Queries Interview Questions and Answers on "SQL Select"
1. Get all employee details from the employee table
Select * from employee
2. Get First_Name,Last_Name from employee table
Select first_name, Last_Name from employee

3. Get First_Name from employee table using alias name “Employee Name”
Select first_name Employee Name from employee

4. Get First_Name from employee table in upper case

Select upper(FIRST_NAME) from EMPLOYEE

5. Get First_Name from employee table in lower case

Select lower(FIRST_NAME) from EMPLOYEE

6. Get unique DEPARTMENT from employee table

select distinct DEPARTMENT from EMPLOYEE
Don't Miss - SQL and Database theory Interview Questions

7. Select first 3 characters of FIRST_NAME from EMPLOYEE

Oracle Equivalent of SQL Server SUBSTRING is SUBSTR, Query : select substr(FIRST_NAME,0,3) from
employee
SQL Server Equivalent of Oracle SUBSTR is SUBSTRING, Query : select substring(FIRST_NAME,0,3) from
employee

MySQL Server Equivalent of Oracle SUBSTR is SUBSTRING. In MySQL start position is 1, Query : select
substring(FIRST_NAME,1,3) from employee

8. Get position of 'o' in name 'John' from employee table

Oracle Equivalent of SQL Server CHARINDEX is INSTR, Query : Select instr(FIRST_NAME,'o') from
employee where first_name='John'

SQL Server Equivalent of Oracle INSTR is CHARINDEX, Query: Select CHARINDEX('o',FIRST_NAME,0)

from employee where first_name='John'

MySQL Server Equivalent of Oracle INSTR is LOCATE, Query: Select LOCATE('o',FIRST_NAME) from
employee where first_name='John'

9. Get FIRST_NAME from employee table after removing white spaces from right side
select RTRIM(FIRST_NAME) from employee

SQL NOTES Page 121

10. Get FIRST_NAME from employee table after removing white spaces from left side
select LTRIM(FIRST_NAME) from employee

11. Get length of FIRST_NAME from employee table

Oracle,MYSQL Equivalent of SQL Server Len is Length , Query :select length(FIRST_NAME) from
employee

SQL Server Equivalent of Oracle,MYSQL Length is Len, Query :select len(FIRST_NAME) from employee

12. Get First_Name from employee table after replacing 'o' with '$'
select REPLACE(FIRST_NAME,'o','$') from employee

13. Get First_Name and Last_Name as single column from employee table separated by a '_'
Oracle Equivalent of MySQL concat is '||', Query : Select FIRST_NAME|| '_' ||LAST_NAME from
EMPLOYEE

SQL Server Equivalent of MySQL concat is '+', Query : Select FIRST_NAME + '_' +LAST_NAME from
EMPLOYEE

MySQL Equivalent of Oracle '||' is concat, Query : Select concat(FIRST_NAME,'_',LAST_NAME) from

EMPLOYEE

14. Get FIRST_NAME ,Joining year,Joining Month and Joining Date from employee table
SQL Queries in Oracle, Select FIRST_NAME, to_char(joining_date,'YYYY') JoinYear ,
to_char(joining_date,'Mon'), to_char(joining_date,'dd') from EMPLOYEE

SQL Queries in SQL Server, select SUBSTRING (convert(varchar,joining_date,103),7,4) , SUBSTRING

(convert(varchar,joining_date,100),1,3) , SUBSTRING (convert(varchar,joining_date,100),5,2) from
EMPLOYEE

SQL Queries in MySQL, select year(joining_date),month(joining_date), DAY(joining_date) from

EMPLOYEE

15. Get all employee details from the employee table order by First_Name Ascending
Select * from employee order by FIRST_NAME asc

16. Get all employee details from the employee table order by First_Name descending
Select * from employee order by FIRST_NAME desc

17. Get all employee details from the employee table order by First_Name Ascending and Salary
descending
Select * from employee order by FIRST_NAME asc,SALARY desc

SQL NOTES Page 122

"SQL Where Condition" Interview Questions

18. Get employee details from employee table whose employee name is “John”
Select * from EMPLOYEE where FIRST_NAME='John'

19. Get employee details from employee table whose employee name are “John” and “Roy”
Select * from EMPLOYEE where FIRST_NAME in ('John','Roy')

20. Get employee details from employee table whose employee name are not “John” and “Roy”
Select * from EMPLOYEE where FIRST_NAME not in ('John','Roy')
"SQL Wild Card Search" Interview Questions

21. Get employee details from employee table whose first name starts with 'J'

Select * from EMPLOYEE where FIRST_NAME like 'J%'

22. Get employee details from employee table whose first name contains 'o'
Select * from EMPLOYEE where FIRST_NAME like '%o%'

23. Get employee details from employee table whose first name ends with 'n'

Select * from EMPLOYEE where FIRST_NAME like '%n'

"SQL Pattern Matching" Interview Questions
24. Get employee details from employee table whose first name ends with 'n' and name contains 4
letters
Select * from EMPLOYEE where FIRST_NAME like '___n' (Underscores)

25. Get employee details from employee table whose first name starts with 'J' and name contains 4
letters

Select * from EMPLOYEE where FIRST_NAME like 'J___' (Underscores)

26. Get employee details from employee table whose Salary greater than 600000

Select * from EMPLOYEE where Salary >600000

27. Get employee details from employee table whose Salary less than 800000

Select * from EMPLOYEE where Salary <800000

28. Get employee details from employee table whose Salary between 500000 and 800000

Select * from EMPLOYEE where Salary between 500000 and 800000

SQL NOTES Page 123

29. Get employee details from employee table whose name is 'John' and 'Michael'
Select * from EMPLOYEE where FIRST_NAME in ('John','Michael')

Interview Questions on "SQL DATE Functions"

30. Get employee details from employee table whose joining year is “2013”

SQL Queries in Oracle, Select * from EMPLOYEE where to_char(joining_date,'YYYY')='2013'

SQL Queries in SQL Server, Select * from EMPLOYEE where

SUBSTRING(convert(varchar,joining_date,103),7,4)='2013'

SQL Queries in MySQL, Select * from EMPLOYEE where year(joining_date)='2013'

31. Get employee details from employee table whose joining month is “January”
SQL Queries in Oracle, Select * from EMPLOYEE where to_char(joining_date,'MM')='01' or Select *
from EMPLOYEE where to_char(joining_date,'Mon')='Jan'

SQL Queries in SQL Server, Select * from EMPLOYEE where

SUBSTRING(convert(varchar,joining_date,100),1,3)='Jan'

SQL Queries in MySQL, Select * from EMPLOYEE where month(joining_date)='01'

32. Get employee details from employee table who joined before January 1st 2013
SQL Queries in Oracle, Select * from EMPLOYEE where JOINING_DATE
<to_date('01/01/2013','dd/mm/yyyy')

SQL Queries in SQL Server (Format - “MM/DD/YYYY”), Select * from EMPLOYEE where joining_date
<'01/01/2013'

SQL Queries in MySQL (Format - “YYYY-DD-MM”), Select * from EMPLOYEE where joining_date
<'2013-01-01'

33. Get employee details from employee table who joined after January 31st
SQL Queries in Oracle, Select * from EMPLOYEE where JOINING_DATE
>to_date('31/01/2013','dd/mm/yyyy')

SQL Queries in SQL Server and MySQL (Format - “MM/DD/YYYY”), Select * from EMPLOYEE where
joining_date >'01/31/2013'

SQL Queries in MySQL (Format - “YYYY-DD-MM”), Select * from EMPLOYEE where joining_date
>'2013-01-31'

SQL NOTES Page 124

35. Get Joining Date and Time from employee table

SQL Queries in Oracle, select to_char(JOINING_DATE,'dd/mm/yyyy hh:mi:ss') from EMPLOYEE

SQL Queries in SQL Server, Select convert(varchar(19),joining_date,121) from EMPLOYEE

SQL Queries in MySQL, Select CONVERT(DATE_FORMAT(joining_date,'%Y-%m-%d-%H:

%i:00'),DATETIME) from EMPLOYEE

36. Get Joining Date,Time including milliseconds from employee table

SQL Queries in Oracle, select to_char(JOINING_DATE,'dd/mm/yyyy HH:mi:[Link]') from EMPLOYEE .

Column Data Type should be “TimeStamp”

SQL Queries in SQL Server, select convert(varchar,joining_date,121) from EMPLOYEE

SQL Queries in MySQL, Select MICROSECOND(joining_date) from EMPLOYEE

37. Get difference between JOINING_DATE and INCENTIVE_DATE from employee and incentives
table

Select FIRST_NAME,INCENTIVE_DATE - JOINING_DATE from employee a inner join incentives B on

A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

38. Get database date

SQL Queries in Oracle, select sysdate from dual

SQL Queries in SQL Server, select getdate()

SQL Query in MySQL, select now()

"SQL Escape Characters" Interview Questions

SQL NOTES Page 125

39. Get names of employees from employee table who has '%' in Last_Name. Tip : Escape character
for special characters in a query.

SQL Queries in Oracle, Select FIRST_NAME from employee where Last_Name like '%?%%'

SQL Queries in SQL Server, Select FIRST_NAME from employee where Last_Name like '%[%]%'

SQL Queries in MySQL, Select FIRST_NAME from employee where Last_Name like '%\%%'

40. Get Last Name from employee table after replacing special character with white space
SQL Queries in Oracle, Select translate(LAST_NAME,'%',' ') from employee

SQL Queries in SQL Server and MySQL, Select REPLACE(LAST_NAME,'%',' ') from employee
"SQL Group By Query" Interview Questions and Answers

41. Get department,total salary with respect to a department from employee table.
Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by department

42. Get department,total salary with respect to a department from employee table order by total
salary descending
Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT order by
Total_Salary descending

SQL Queries Interview Questions and Answers on "SQL Mathematical Operations using Group By"

43. Get department,no of employees in a department,total salary with respect to a department

from employee table order by total salary descending

Select DEPARTMENT,count(FIRST_NAME),sum(SALARY) Total_Salary from employee group by

DEPARTMENT order by Total_Salary descending

44. Get department wise average salary from employee table order by salary ascending

select DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalary

asc

45. Get department wise maximum salary from employee table order by salary ascending

select DEPARTMENT,max(SALARY) MaxSalary from employee group by DEPARTMENT order by

MaxSalary asc

46. Get department wise minimum salary from employee table order by salary ascending

SQL NOTES Page 126

select DEPARTMENT,min(SALARY) MinSalary from employee group by DEPARTMENT order by
MinSalary asc

47. Select no of employees joined with respect to year and month from employee table

SQL Queries in Oracle, select to_char (JOINING_DATE,'YYYY') Join_Year,to_char (JOINING_DATE,'MM')

Join_Month,count(*) Total_Emp from employee group by to_char
(JOINING_DATE,'YYYY'),to_char(JOINING_DATE,'MM')

SQL Queries in SQL Server, select datepart (YYYY,JOINING_DATE) Join_Year,datepart

(MM,JOINING_DATE) Join_Month,count(*) Total_Emp from employee group by
datepart(YYYY,JOINING_DATE), datepart(MM,JOINING_DATE)

SQL Queries in MySQL, select year (JOINING_DATE) Join_Year,month (JOINING_DATE)

Join_Month,count(*) Total_Emp from employee group by year(JOINING_DATE),
month(JOINING_DATE)

48. Select department,total salary with respect to a department from employee table where total
salary greater than 800000 order by Total_Salary descending

Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT having

sum(SALARY) >800000 order by Total_Salary desc

Advanced SQL Queries Interview Questions and Answers

49. Select employee details from employee table if data exists in incentive table ?

select * from EMPLOYEE where exists (select * from INCENTIVES)

Explanation : Here "exists" statement helps us to do the job of If statement. Main query will get
executed if the sub query returns at least one row. So we can consider the sub query as "If condition"
and the main query as "code block" inside the If condition. We can use any SQL commands (Joins,
Group By , having etc) in sub query. This command will be useful in queries which need to detect an
event and do some activity.

50. How to fetch data that are common in two query results ?
select * from EMPLOYEE where EMPLOYEE_ID INTERSECT select * from EMPLOYEE where
EMPLOYEE_ID < 4
Explanation : Here "INTERSECT" command is used to fetch data that are common in 2 queries. In this
example, we had taken EMPLOYEE table in both the [Link] can apply INTERSECT command on
different tables. The result of the above query will return employee details of "ROY" because,
employee id of ROY is 3, and both query results have the information about ROY.

51. Get Employee ID's of those employees who didn't receive incentives without using sub query ?

SQL NOTES Page 127

select EMPLOYEE_ID from EMPLOYEE
MINUS
select EMPLOYEE_REF_ID from INCENTIVES
Explanation : To filter out certain information we use MINUS command. What MINUS Command odes
is that, it returns all the results from the first query, that are not part of the second query. In our
example, first three employees received the incentives. So query will return employee id's 4 to 8.

52. Select 20 % of salary from John , 10% of Salary for Roy and for other 15 % of salary from
employee table
SELECT FIRST_NAME, CASE FIRST_NAME WHEN 'John' THEN SALARY * .2 WHEN 'Roy' THEN SALARY
* .10 ELSE SALARY * .15 END "Deduced_Amount" FROM EMPLOYEE
Explanation : Here, we are using "SQL CASE" statement to achieve the desired results. After case
statement, we had to specify the column on which filtering is applied. In our case it is "FIRST_NAME".
And in then condition, specify the name of filter like John, Roy etc. To handle conditions outside our
filter, use else block where every one other than John and Roy enters.

53. Select Banking as 'Bank Dept', Insurance as 'Insurance Dept' and Services as 'Services Dept' from
employee table

SQL Queries in Oracle, SELECT distinct DECODE (DEPARTMENT, 'Banking', 'Bank Dept', 'Insurance',
'Insurance Dept', 'Services', 'Services Dept') FROM EMPLOYEE

SQL Queries in SQL Server and MySQL, SELECT case DEPARTMENT when 'Banking' then 'Bank Dept'
when 'Insurance' then 'Insurance Dept' when 'Services' then 'Services Dept' end FROM EMPLOYEE
Explanation : Here "DECODE" keyword is used to specify the alias name. In oracle we had specify,
Column Name followed by Actual Name and Alias Name as arguments. In SQL Server and MySQL, we
can use the earlier switch case statements for alias names.

54. Delete employee data from employee table who got incentives in incentive table

delete from EMPLOYEE where EMPLOYEE_ID in (select EMPLOYEE_REF_ID from INCENTIVES)

Explanation : Trick about this question is that we can't delete data from a table based on some
condition in another table by joining them. Here to delete multiple entries from EMPLOYEE table, we
need to use Subquery. Entries will get deleted based on the result of Subquery.

55. Insert into employee table Last Name with " ' " (Single Quote - Special Character)

Tip - Use another single quote before special character

Insert into employee (LAST_NAME) values ('Test''')

56. Select Last Name from employee table which contain only numbers

Select * from EMPLOYEE where lower(LAST_NAME)=upper(LAST_NAME)

Explanation : In order to achieve the desired result, we use "ASCII" property of the database. If we get
results for a column using Lower and Upper commands, ASCII of both results will be same for

SQL NOTES Page 128

numbers. If there is any alphabets in the column, results will differ.

57. Write a query to rank employees based on their incentives for a month

select FIRST_NAME,INCENTIVE_AMOUNT,DENSE_RANK() OVER (PARTITION BY INCENTIVE_DATE

ORDER BY INCENTIVE_AMOUNT DESC) AS Rank from EMPLOYEE a, INCENTIVES b where
a.EMPLOYEE_ID=b.EMPLOYEE_REF_ID
Explanation : In order to rank employees based on their rank for a month, "DENSE_RANK" keyword is
used. Here partition by keyword helps us to sort the column with which filtering is done. Rank is
provided to the column specified in the order by statement. The above query ranks employees with
respect to their incentives for a given month.

58. Update incentive table where employee name is 'John'

update INCENTIVES set INCENTIVE_AMOUNT='9000' where EMPLOYEE_REF_ID=(select EMPLOYEE_ID

from EMPLOYEE where FIRST_NAME='John' )
Explanation : We need to join Employee and Incentive Table for updating the incentive amount. But
for update statement joining query wont work. We need to use sub query to update the data in the
incentive table. SQL Query is as shown below.

"SQL Join" Interview Questions

59. Select first_name, incentive amount from employee and incentives table for those employees
who have incentives

Select FIRST_NAME,INCENTIVE_AMOUNT from employee a inner join incentives B on

A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

60. Select first_name, incentive amount from employee and incentives table for those employees
who have incentives and incentive amount greater than 3000
Select FIRST_NAME,INCENTIVE_AMOUNT from employee a inner join incentives B on
A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID and INCENTIVE_AMOUNT >3000

61. Select first_name, incentive amount from employee and incentives table for all employes even if
they didn't get incentives
Select FIRST_NAME,INCENTIVE_AMOUNT from employee a left join incentives B on
A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

62. Select first_name, incentive amount from employee and incentives table for all employees even
if they didn't get incentives and set incentive amount as 0 for those employees who didn't get
incentives.
SQL Queries in Oracle, Select FIRST_NAME,nvl(INCENTIVE_AMOUNT,0) from employee a left join
incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

SQL Queries in SQL Server, Select FIRST_NAME, ISNULL(INCENTIVE_AMOUNT,0) from employee a left

SQL NOTES Page 129

join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

SQL Queries in MySQL, Select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee a left join
incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

63. Select first_name, incentive amount from employee and incentives table for all employees who
got incentives using left join
SQL Queries in Oracle, Select FIRST_NAME,nvl(INCENTIVE_AMOUNT,0) from employee a right join
incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

SQL Queries in SQL Server, Select FIRST_NAME, isnull(INCENTIVE_AMOUNT,0) from employee a right
join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

SQL Queries in MySQL, Select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee a right

join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

64. Select max incentive with respect to employee from employee and incentives table using sub
query
SQL Queries in Oracle, select DEPARTMENT,(select nvl(max(INCENTIVE_AMOUNT),0) from INCENTIVES
where EMPLOYEE_REF_ID=EMPLOYEE_ID) Max_incentive from EMPLOYEE

SQL Queries in SQL Server, select DEPARTMENT,(select ISNULL(max(INCENTIVE_AMOUNT),0) from

INCENTIVES where EMPLOYEE_REF_ID=EMPLOYEE_ID) Max_incentive from EMPLOYEE

SQL Queries in SQL Server, select DEPARTMENT,(select IFNULL (max(INCENTIVE_AMOUNT),0) from

INCENTIVES where EMPLOYEE_REF_ID=EMPLOYEE_ID) Max_incentive from EMPLOYEE

"Top N Salary" SQL Interview Questions and Answers

65. Select TOP 2 salary from employee table

SQL Queries in Oracle, select * from (select * from employee order by SALARY desc) where rownum
<3

SQL Queries in SQL Server, select top 2 * from employee order by salary desc

SQL Queries in MySQL, select * from employee order by salary desc limit 2

66. Select TOP N salary from employee table

SQL Queries in Oracle, select * from (select * from employee order by SALARY desc) where rownum
<N + 1

SQL Queries in SQL Server, select top N * from employee

SQL NOTES Page 130

SQL Queries in MySQL, select * from employee order by salary desc limit N

67. Select 2nd Highest salary from employee table

SQL Queries in Oracle, select min(salary) from (select * from (select * from employee order by SALARY
desc) where rownum <3)

SQL Queries in SQL Server, select min(SALARY) from (select top 2 * from employee) a

SQL Queries in MySQL, select min(SALARY) from (select * from employee order by salary desc limit 2)
a

68. Select Nth Highest salary from employee table

SQL Queries in Oracle, select min(salary) from (select * from (select * from employee order by SALARY
desc) where rownum <N + 1)

SQL Queries in SQL Server, select min(SALARY) from (select top N * from employee) a

SQL Queries in MySQL, select min(SALARY) from (select * from employee order by salary desc limit N)
a

"SQL Union" Query Interview Questions

69. Select First_Name,LAST_NAME from employee table as separate rows

select FIRST_NAME from EMPLOYEE union select LAST_NAME from EMPLOYEE

70. What is the difference between UNION and UNION ALL ?

Both UNION and UNION ALL is used to select information from structurally similar tables. That means
corresponding columns specified in the union should have same data type. For example, in the above
query, if FIRST_NAME is DOUBLE and LAST_NAME is STRING above query wont work. Since the data
type of both the columns are VARCHAR, union is made possible. Difference between UNION and
UNION ALL is that , UNION query return only distinct values.

SQL Interview Questions on "SQL Table Scripts"

71. Write create table syntax for employee table

Oracle -CREATE TABLE EMPLOYEE (
EMPLOYEE_ID NUMBER,
FIRST_NAME VARCHAR2(20 BYTE),
LAST_NAME VARCHAR2(20 BYTE),
SALARY FLOAT(126),

SQL NOTES Page 131

JOINING_DATE TIMESTAMP (6) DEFAULT sysdate,
DEPARTMENT VARCHAR2(30 BYTE) )
SQL Server -CREATE TABLE EMPLOYEE(
EMPLOYEE_ID int NOT NULL,
FIRST_NAME varchar(50) NULL,
LAST_NAME varchar(50) NULL,
SALARY decimal(18, 0) NULL,
JOINING_DATE datetime2(7) default getdate(),
DEPARTMENT varchar(50) NULL)

72. Write syntax to delete table employee

DROP table employee;

73. Write syntax to set EMPLOYEE_ID as primary key in employee table

ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID)

74. Write syntax to set 2 fields(EMPLOYEE_ID,FIRST_NAME) as primary key in employee table

ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID,FIRST_NAME)

75. Write syntax to drop primary key on employee table

Alter TABLE EMPLOYEE drop CONSTRAINT EMPLOYEE_PK;

76. Write Sql Syntax to create EMPLOYEE_REF_ID in INCENTIVES table as foreign key with respect to
EMPLOYEE_ID in employee table

ALTER TABLE INCENTIVES ADD CONSTRAINT INCENTIVES_FK FOREIGN KEY (EMPLOYEE_REF_ID)

REFERENCES EMPLOYEE(EMPLOYEE_ID)

77. Write SQL to drop foreign key on employee table

ALTER TABLE INCENTIVES drop CONSTRAINT INCENTIVES_FK;

78. Write SQL to create Orcale Sequence

CREATE SEQUENCE EMPLOYEE_ID_SEQ START WITH 0 NOMAXVALUE MINVALUE 0 NOCYCLE NOCACHE

NOORDER;

SQL NOTES Page 132

79. Write Sql syntax to create Oracle Trigger before insert of each row in employee table

CREATE OR REPLACE TRIGGER EMPLOYEE_ROW_ID_TRIGGER

BEFORE INSERT ON EMPLOYEE FOR EACH ROW
DECLARE
seq_no number(12);
BEGIN
select EMPLOYEE_ID_SEQ.nextval into seq_no from dual ;
:new EMPLOYEE_ID :=seq_no;
END;
SHOW ERRORS;

80. Oracle Procedure81. Oracle View

An example oracle view script is given below

create view Employee_Incentive as select FIRST_NAME,max(INCENTIVE_AMOUNT)
INCENTIVE_AMOUNT from EMPLOYEE a, INCENTIVES b where a.EMPLOYEE_ID=b.EMPLOYEE_REF_ID
group by FIRST_NAME

82. Oracle materialized view - Daily Auto Refresh

CREATE MATERIALIZED VIEW Employee_Incentive

REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1 AS
select FIRST_NAME,INCENTIVE_DATE,INCENTIVE_AMOUNT from EMPLOYEE a, INCENTIVES b
where a.EMPLOYEE_ID=b.EMPLOYEE_REF_ID

83. Oracle materialized view - Fast Refresh on Commit

Create materialized view log for fast refresh. Following materialized view script wont get executed if
materialized view log doesn't exists

CREATE MATERIALIZED VIEW MAT_Employee_Incentive_Refresh

BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
select FIRST_NAME,max(INCENTIVE_AMOUNT) from EMPLOYEE a, INCENTIVES b
where a.EMPLOYEE_ID=b.EMPLOYEE_REF_ID group by FIRST_NAME
84. What is SQL Injection ?
SQL Injection is one of the the techniques uses by hackers to hack a website by injecting SQL
commands in data fields.

SQL Server - Common Interview Questions and Answers

SQL NOTES Page 133
1. Which TCP/IP port does SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.
2. What are the difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records in the table are physically
stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain
the data pages.
A non clustered index is a special type of index in which the logical order of the index does not match
the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist
of the data pages. Instead, the leaf nodes contain index rows.
3. What are the different index configurations a table can have?
A table can have one of the following index configurations:
No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes
4. What are different types of Collation Sensitivity?
Case sensitivity - A and a, B and b, etc.
Accent sensitivity
Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are treated differently, it is
called Kana sensitive.
Width sensitivity - A single-byte character (half-width) and the same character represented as a
double-byte character (full-width) are treated differently than it is width sensitive.

5. What is OLTP (Online Transaction Processing)?

In OLTP - online transaction processing systems relational database design use the discipline of data
modeling and generally follow the Codd rules of data normalization in order to ensure absolute data
integrity. Using these rules complex information is broken down into its most simple structures (a
table) where all of the individual atomic level elements relate to each other and satisfy the
normalization rules.
6. What's the difference between a primary key and a unique key?
Both primary key and unique key enforces uniqueness of the column on which they are defined. But
by default primary key creates a clustered index on the column, where are unique creates a
nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but
unique key allows one NULL only.
7. What is difference between DELETE and TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a
WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the
table after we run the truncate command.
TRUNCATE:
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only
the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and
SQL NOTES Page 134
so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because
TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE cannot be rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table

DELETE:
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition
and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE can be rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.
Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the
current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and
if session is closed, it can not be rolled back but DELETE can be rolled back.
8. When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has occurred. If a large amount of
deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to
take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
9. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
They specify a search condition for a group or an aggregate. But the difference is that HAVING can be
used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP
BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the
GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of
the GROUP BY function in a query.
10. What are the properties and different Types of Sub-Queries?
Properties of Sub-Query
A sub-query must be enclosed in the parenthesis.
A sub-query must be put in the right hand of the comparison operator, and
A sub-query cannot contain an ORDER-BY clause.
A query can contain more than one sub-query.
Types of Sub-Query
Single-row sub-query, where the sub-query returns only one row.
Multiple-row sub-query, where the sub-query returns multiple rows,. and
Multiple column sub-query, where the sub-query returns multiple columns
11. What is SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of
Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to
analyze later. For example, you can monitor a production environment to see which stored procedures
are hampering performances by executing too slowly.
Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too

SQL NOTES Page 135

large, you can filter them based on the information you want, so that only a subset of the event data is
collected. Monitoring too many events adds overhead to the server and the monitoring process and
can cause the trace file or trace table to grow very large, especially when the monitoring process takes
place over a long period of time.
12. What are the authentication modes in SQL Server? How can it be changed?
Windows mode and Mixed Mode - SQL and Windows. To change authentication mode in SQL Server
click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise
Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu
select SQL Server Configuration Properties, and choose the Security page.
13. Which command using Query Analyzer will give you the version of SQL server and operating
system?
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY
('edition').
14. What is SQL Server Agent?
SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It
is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the
implementation of tasks for the DBA, with its full- function scheduling engine, which allows you to
schedule your own jobs and scripts.

15. Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is
possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves.
Recursion can be defined as a method of problem solving wherein the solution is arrived at by
repetitively applying it to subsets of the problem. A common application of recursive logic is to
perform numeric computations that lend themselves to repetitive evaluation by the same processing
steps. Stored procedures are nested when one stored procedure calls another or executes managed
code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed
code references up to 32 levels.
16. What is Log Shipping?
Log shipping is the process of automating the backup of database and transaction log files on a
production SQL server, and then restoring them onto a standby server. Enterprise Editions only
supports log shipping. In log shipping the transactional log file from one server is automatically
updated into the backup database on the other server. If one server fails, the other server will have
the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that
it will automatically backup transaction logs throughout the day and automatically restore them on
the standby server at defined interval.
17. Name 3 ways to get an accurate count of the number of records in a table?
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
18. What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it
OFF?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and
literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers
cannot be quoted and must follow all Transact-SQL rules for identifiers.

SQL NOTES Page 136

19. What is the difference between a Local and a Global temporary table?
A local temporary table exists only for the duration of a connection or, if defined inside a compound
statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given
connection. When connection is closed, the data in the global temporary table disappears. However,
the table definition remains with the database for access when database is opened next time.
20. What is the STUFF function and how does it differ from the REPLACE function?
STUFF function is used to overwrite existing characters. Using this syntax, STUFF (string_expression,
start, length, replacement_characters), string_expression is the string that will have characters
substituted, start is the starting position, length is the number of characters in the string that are
substituted, and replacement_characters are the new characters interjected into the string. REPLACE
function to replace existing characters of all occurrences. Using the syntax REPLACE
(string_expression, search_string, replacement_string), where every incidence of search_string found
in the string_expression will be replaced with replacement_string.
21. What is PRIMARY KEY?
A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should
have a primary key constraint to uniquely identify each row and only one primary key constraint can
be created for each table. The primary key constraints are used to enforce entity integrity.
22. What is UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values
are entered. The unique key constraints are used to enforce entity integrity as the primary key
constraints.
23. What is FOREIGN KEY?
A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the
corresponding data values. A foreign key in one table points to a primary key in another table. Foreign
keys prevent actions that would leave rows with foreign key values when there are no primary keys
with that value. The foreign key constraints are used to enforce referential integrity.
24. What is CHECK Constraint?
A CHECK constraint is used to limit the values that can be placed in a column. The check constraints
are used to enforce domain integrity.
25. What is NOT NULL Constraint?
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints
are used to enforce domain integrity, as the check constraints.

26. How to get @@ERROR and @@ROWCOUNT at the same time?

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of
@@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-
checking statement then @@Error would get reset. To get @@error and @@rowcount at the same
time do both in same statement and store them in local variable.
SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
27. What is a Scheduled Jobs or What is a Scheduled Tasks?
Scheduled tasks let user automate processes that run on regular or predictable cycles. User can
schedule administrative tasks, such as cube processing, to run during times of slow business activity.
User can also determine the order in which tasks run by creating job steps within a SQL Server Agent
job. E.g. back up database, Update Stats of Tables. Job steps give user control over flow of execution. If

SQL NOTES Page 137

one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop
execution.
28. What are the advantages of using Stored Procedures?
Stored procedure can reduced network traffic and latency, boosting application performance.
Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing
server overhead.
Stored procedures help promote code reuse.
Stored procedures can encapsulate logic. You can change stored procedure code without affecting
clients.
Stored procedures provide better security to your data.
29. What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a
table that does not have a clustered index and, therefore, the pages are not linked by pointers. The
IAM pages are the only structures that link the pages in a table together. Unindexed tables are good
for fast storing of data. Many times it is better to drop all indexes from table and then do bulk of
inserts and to restore those indexes after that.

30. Can SQL Servers linked to other servers like Oracle?

SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link.
E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL
Server group.
31. What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the
structures same as source to destination. BULK INSERT command helps to import a data file into a
database table or view in a user-specified format.
32. How to implement one-to-one, one-to-many and many-to-many relationships while designing
tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary
and foreign key relationships. One-to-Many relationships are implemented by splitting the data into
two tables with primary key and foreign key relationships. Many-to-Many relationships are
implemented using a junction table with the keys from both the tables forming the composite primary
key of the junction table.
33. What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval
methods chosen by the SQL Server query optimizer for a stored procedure or ad- hoc query and is a
very useful tool for a developer to understand the performance characteristics of a query or stored
procedure since the plan is the one that SQL Server will place in its cache and use to execute the
stored procedure or query. From within Query Analyzer is an option called "Show Execution Plan"
(located on the Query drop-down menu). If this option is turned on it will display query execution plan
in separate window when query is ran again.

SQL NOTES Page 138