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.

The following SQL statement will select all the records in the "Persons" table: SELECT * FROM
Person

• 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 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 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

EXAMPLE: SELECT DISTINCT City FROM Persons

SQL WHERE Clause

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

SELECT column_name(s) FROM table_name WHERE column_name operator value

EXAMPLE: SELECT * FROM Persons WHERE City='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: SELECT * FROM Persons WHERE FirstName='Tove'

For numeric values, this is correct: SELECT * FROM Persons WHERE Year=1965

Operators Allowed in the WHERE Clause

The following operators can be used:

SQL AND & OR Operators

Are used to filter records based on more than one condition.

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

EXAMPLE: SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson'

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

EXAMPLE: SELECT * FROM Persons WHERE FirstName='Tove' OR FirstName='Ola'

Combining AND & OR EXAMPLE: SELECT * FROM Persons WHERE LastName='Svendson' AND
(FirstName='Tove' OR FirstName='Ola')

SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set.

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.

EXAMPLE: SQL ORDER BY Syntax SELECT column_name(s) FROM table_name ORDER BY column_name(s)
ASC|DESC

ORDER BY DESC we want to sort the persons descending by their last name.

EXAMPLE: SELECT statement: SELECT * FROM Persons ORDER BY LastName DESC.

SQL INSERT INTO Statement

Is used to insert new records in a table.

Is used to insert a new row in a table.

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

EXAMPLE: INSERT INTO Persons VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')

SQL UPDATE Statement

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

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

EXAMPLE: UPDATE Persons SET Address='Nissestien 67', City='Sandnes' WHERE LastName='Tjessem'

AND FirstName='Jakob'

SQL DELETE Statement

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

Statement is used to delete rows in a table.

EXAMPLE: DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob'

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

EXAMPLE: DELETE FROM table_name or DELETE * FROM table_name

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

EXAMPLE: SELECT TOP number|percent column_name(s) FROM table_name

MySQL Syntax: SELECT column_name(s) FROM table_name LIMIT number

SELECT * FROM Persons LIMIT 5

Oracle Syntax: SELECT column_name(s) FROM table_name WHERE ROWNUM <= number Example
SELECT * FROM Persons WHERE ROWNUM <=5

SQL LIKE Operator

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

EXAMPLE: SELECT * FROM Persons WHERE City LIKE 's%' (NOTE: anything will do, ikaw mag dedecide
ng like pattern, hindi lang sya specific na letter “S”) The "%" sign can be used to define wildcards
(missing letters in the pattern) both before and after the pattern.

SQL Wildcards

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

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.

Using the % Wildcard

SELECT * FROM Persons WHERE City LIKE 'sa%'

Using the _ Wildcard

SELECT * FROM Persons WHERE FirstName LIKE '_la'

Using the [charlist] Wildcard

SELECT * FROM Persons WHERE LastName LIKE '[bsp]%'

SQL IN Operator

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

EXAMPLE: SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)

EXAMPLE: SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen')

SQL BETWEEN Operator

The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The
BETWEEN operator selects a range of data between two values. The values can be numbers, text, or
dates.

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

EXAMPLE: SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'

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.

EXAMPLE: SELECT column_name(s) FROM table_name AS alias_name.

EXAMPLE: SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po

WHERE p.LastName='Hansen' AND p.FirstName='Ola'
SQL Joins

SQL joins are used 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.

Different SQL JOINs

• 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

SQL INNER JOIN Keyword

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

EXAMPLE: SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON

table_name1.column_name=table_name2.column_name (NOTE: INNER JOIN is the same as JOIN)

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

EXAMPLE: SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON

table_name1.column_name=table_name2.column_name

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

EXAMPLE: SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON

table_name1.column_name=table_name2.column_name

SQL FULL JOIN Keyword

The FULL JOIN keyword return rows when there is a match in one of the tables.
EXAMPLE: SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON
table_name1.column_name=table_name2.column_name

SQL UNION Operator

The SQL UNION operator combines 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.

EXAMPLE: SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM

table_name2

EXAMPLE: SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA

SQL SELECT INTO Statement

The SQL SELECT INTO statement can be used to create backup copies of tables. 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.

We can select all columns into the new table: SELECT * INTO new_table_name [IN externaldatabase]
FROM old_tablename

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

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 'Backup.mdb' 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 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 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 Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons
INNER JOIN Orders ON Persons.P_Id=Orders.P_Id