SQL Introduction

SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.

Unfortunately, there are many different versions of the SQL language, but to be in compliance with the ANSI standard, they must support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others).

Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!


What is SQL?


SQL Data Manipulation Language (DML)

SQL (Structured Query Language) is a syntax for executing queries. But the SQL language also includes a syntax to update, insert, and delete records.

These query and update commands together form the Data Manipulation Language (DML) part of SQL:


SQL Data Definition Language (DDL)

The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.

The most important DDL statements in SQL are: 


SQL SELECT Statement

The SELECT statement is used to select data from a table.
The tabular result is stored in a result table (called the result-set).

 SELECT column_name(s) FROM table_name

Example: The database table "Persons":
LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger

 SELECT LastName,FirstName FROM Persons

result-set

LastName FirstName
Hansen Ola
Svendson Tove
Pettersen Kari

To select all columns within the "Persons" Table use:
 SELECT * FROM Persons

The result from a SQL query is stored in a result-set. Most database software systems allow navigation of the result set with programming functions, like: Move-To-First-Record, Get-Record-Content, Move-To-Next-Record, etc.

Programming functions like these are not a part of this tutorial. To learn about accessing data with function calls, please visit our ADO tutorial.


The SELECT DISTINCT Statement

The DISTINCT keyword is used to return only distinct (different) values.

Example: "Orders" table
Company OrderNumber
Sega 3412
W3Schools 2312
Trio 4678
W3Schools 6798

 SELECT DISTINCT Company FROM Orders

Result-Set:
Company
Sega
W3Schools
Trio

SQL WHERE Statement

The WHERE clause is used to specify a selection criterion.To conditionally select data from a table, a WHERE clause can be added to the SELECT statement.

 SELECT column_name(s) FROM table
 WHERE column_name (operator) value

Operators:

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 !=


From example "Persons" table:
 SELECT * FROM Persons WHERE City='Sandnes'

Result-Set:

LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes

Note: SQL uses single quotes around text values (most database systems will also accept double quotes).
Numeric values should NOT be enclosed in quotes.


The LIKE Condition

The LIKE condition is used to specify a search for a pattern in a column.

 SELECT column_name(s) FROM table_name
 WHERE column_name LIKE pattern

A percent sign "%" can be used to define wildcards (missing letters in the pattern) both before and after the pattern.


The following SQL statement will return persons with first names that start with the letter 'K':

 SELECT * FROM Persons
 WHERE FirstName LIKE 'K%'

The following SQL statement will return persons with first names that end with an 'e':

 SELECT * FROM Persons
 WHERE FirstName LIKE '%e'

The following SQL statement will return persons with first names that contain the pattern 'ga':

 SELECT * FROM Persons
 WHERE FirstName LIKE '%ga%'

SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new rows into a table.

 INSERT INTO table_name
 VALUES (value1, value2,...)

You can also specify the columns for which you want to insert data:

 INSERT INTO table_name (column1, column2,...)
 VALUES (value1, value2, ...)

Insert a New Row (Example: "Persons" Table)

LastName FirstName Address City
Gutierrez Hanna SanF 20 Millbrae

 INSERT INTO Persons
 VALUES ('Kunz','Harry','1944 Su','Dauin')

Insert Data in Specified Columns (Example: "Persons" Table)

 INSERT INTO Persons (Lastname,Address)
 VALUES ('Rudolf','Hessen 47')

Final Table Output: ("Persons" Table)
LastName FirstName Address City
Gutierrez Hanna SanF 20 Millbrae
Kunz Harry 1944 Su Dauin
Rudolf   Hessen 47  

SQL UPDATE Statement

The UPDATE statement is used to modify the data in a table.

 UPDATE table_name
 SET column_name = new_value
 WHERE column_name = some_value

Update one Column in a Row (latest "Persons" Table)

 UPDATE Persons SET Firstname = 'Rita'
 WHERE Lastname = 'Rudolf'

Update several Columns in a Row (latest "Persons" Table)

 UPDATE Persons
 SET
Address='Semmel 17', City='Frankfurt'
 WHERE Lastname = 'Rudolf'

Final Table Output: ("Persons" Table)
LastName FirstName Address City
Gutierrez Hanna SanF 20 Millbrae
Kunz Harry 1944 Su Dauin
Rudolf Rita Semmel 17 Frankfurt

SQL DELETE Statement

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

 DELETE FROM table_name
 WHERE column_name = some_value

Delete a Row (Example: "Persons" Table)

 DELETE FROM Persons
 WHERE Lastname = 'Rudolf'

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

Visit THIS LINK for a "Try it" of the use of SELECT statement