What is SQL?
The Structured Query Language is used in manipulating data stored in Relational Database Management Systems (RDBMS). SQL provides commands
through which data can be extracted, sorted, updated, deleted and inserted. SQL has the full support of ANSI (American National Standards
Institute), which has laid down certain rules for the language.
SQL can be used with any RDBMS such as MySQL, mSQL, PostgresSQL, Oracle, Microsoft SQL Server, Access, Sybase, Ingres etc. All the important and
common sql statements are supported by these RDBMS, however, each has its own set of proprietary statements and extensions.
SQL Data Manipulation Language (DM): SQL (Structured Query Language) is a syntax for executing queries. But the
SQL Data Manipulation Language (DM): SQL (Structured Query Language) is a syntax for executing queries. But the SQL language also includes a syntax to update, insert, and delete records.
Joins and Keys
Sometimes we have to select data from two tables to make our result complete. We have to perform a join.
Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. The purpose is to bind data together, across tables, without repeating all of the data in every table.
In the "Employees" table below, the "Employee_ID" column is the primary key, meaning that no two rows can have the same Employee_ID. The Employee_ID distinguishes two persons even if they have the same name.
When you look at the example tables below, notice that:
- The "Employee_ID" column is the primary key of the "Employees" table
- The "Prod_ID" column is the primary key of the "Orders" table
- The "Employee_ID" column in the "Orders" table is used to refer to the persons in the "Employees" table without using their names
Employees:
| Employee_ID | Name |
| 01 | Hansen, Ola |
| 02 | Svendson, Tove |
| 03 | Svendson, Stephen |
| 04 | Pettersen, Kari |
Orders:
| Prod_ID | Product | Employee_ID |
| 234 | Printer | 01 |
| 657 | Table | 03 |
| 865 | Chair | 03 |
Referring to Two Tables
We can select data from two tables by referring to two tables, like this:
Example
Who has ordered a product, and what did they order?
| SELECT
Employees.Name, Orders.Product
FROM Employees, Orders WHERE Employees.Employee_ID=Orders.Employee_ID |
Result
| Name | Product |
| Hansen, Ola | Printer |
| Svendson, Stephen | Table |
| Svendson, Stephen | Chair |
Example
Who ordered a printer?
| SELECT
Employees.Name
FROM Employees, Orders WHERE Employees.Employee_ID=Orders.Employee_ID AND Orders.Product='Printer' |
Result
| Name |
| Hansen, Ola |
Using Joins
OR we can select data from two tables with the JOIN keyword, like this:
Example INNER JOIN
Syntax
| SELECT field1,
field2, field3
FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield |
Who has ordered a product, and what did they order?
| SELECT
Employees.Name, Orders.Product
FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID |
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.
Result
| Name | Product |
| Hansen, Ola | Printer |
| Svendson, Stephen | Table |
| Svendson, Stephen | Chair |
Example LEFT JOIN
Syntax
| SELECT field1,
field2, field3
FROM first_table LEFT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield |
List all employees, and their orders - if any.
| SELECT
Employees.Name, Orders.Product
FROM Employees LEFT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID |
The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.
Result
| Name | Product |
| Hansen, Ola | Printer |
| Svendson, Tove | |
| Svendson, Stephen | Table |
| Svendson, Stephen | Chair |
| Pettersen, Kari |
Example RIGHT JOIN
Syntax
| SELECT field1,
field2, field3
FROM first_table RIGHT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield |
List all orders, and who has ordered - if any.
| SELECT Employees.Name, Orders.Product
FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID |
The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.
Result
| Name | Product |
| Hansen, Ola | Printer |
| Svendson, Stephen | Table |
| Svendson, Stephen | Chair |
Example
Who ordered a printer?
| SELECT Employees.Name
FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID WHERE Orders.Product = 'Printer' |
Result
| Name |
| Hansen, Ola |
Create a Database
CREATE DATABASE database_name
Create a Table
CREATE TABLE Person
(
LastName varchar,
FirstName varchar,
Address varchar,
Age int
)
The data type specifies what type of data the column can hold. The table below contains the most common data types in SQL:
| Data Type | Description |
| integer(size) int(size) smallint(size) tinyint(size) |
Hold integers only. The maximum number of digits are specified in parenthesis. |
| decimal(size,d) numeric(size,d) |
Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d". |
| char(size) | Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. |
| varchar(size) | Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. |
| date(yyyymmdd) | Holds a date |
Create Index
Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries.
Note: Updating a table containing indexes takes more time than updating a table without, this is because the indexes also need an update. So, it is a good idea to create indexes only on columns that are often used for a search.
A Unique Index
Creates a unique index on a table. A unique index means that two rows cannot have the same index value.
| CREATE UNIQUE INDEX
index_name
ON table_name (column_name) |
The "column_name" specifies the column you want indexed.
A Simple Index
Creates a simple index on a table. When the UNIQUE keyword is omitted, duplicate values are allowed.
| CREATE INDEX
index_name
ON table_name (column_name) |
The "column_name" specifies the column you want indexed.
Example
This example creates a simple index, named "PersonIndex", on the LastName field of the Person table:
| CREATE INDEX
PersonIndex
ON Person (LastName) |
If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name:
| CREATE INDEX
PersonIndex
ON Person (LastName DESC) |
If you want to index more than one column you can list the column names within the parentheses, separated by commas:
| CREATE INDEX
PersonIndex
ON Person (LastName, FirstName) |
Drop Index
You can delete an existing index in a table with the DROP statement.
| DROP INDEX table_name.index_name |
Delete a Database or Table
To delete a database:
| DROP DATABASE database_name |
To delete a table (the table structure, attributes, and indexes will also be deleted):
| DROP TABLE table_name |
Alter Table
The ALTER TABLE statement is used to add or drop columns in an existing table.
ALTER TABLE table_name
ADD column_name datatype
ALTER TABLE table_name
DROP COLUMN column_name
Note: Some database systems don't allow the dropping of a column in a database table (DROP COLUMN column_name).
Person:
| LastName | FirstName | Address |
| Pettersen | Kari | Storgt 20 |
Example
To add a column named "City" in the "Person" table:
| ALTER TABLE Person ADD City varchar(30) |
Result:
| LastName | FirstName | Address | City |
| Pettersen | Kari | Storgt 20 |
Example
To drop the "Address" column in the "Person" table:
| ALTER TABLE Person DROP COLUMN Address |
Result:
| LastName | FirstName | City |
| Pettersen | Kari |
Function Syntax
The syntax for built-in SQL functions is:
SELECT function(column) FROM table
Types of Functions
There are several basic types and categories of functions in SQL. The basic types of functions are:
- Aggregate Functions
- Scalar functions
Aggregate functions
Aggregate functions operate against a collection of values, but return a single value.
Note: If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause!!
"Persons" table (used in most examples)
| Name | Age |
| Hansen, Ola | 34 |
| Svendson, Tove | 45 |
| Pettersen, Kari | 19 |
Aggregate functions in MS Access
| Function | Description |
| AVG(column) | Returns the average value of a column |
| COUNT(column) | Returns the number of rows (without a NULL value) of a column |
| COUNT(*) | Returns the number of selected rows |
| FIRST(column) | Returns the value of the first record in the specified field |
| LAST(column) | Returns the value of the last record in the specified field |
| MAX(column) | Returns the highest value of a column |
| MIN(column) | Returns the lowest value of a column |
| STDEV(column) | |
| STDEVP(column) | |
| SUM(column) | Returns the total sum of a column |
| VAR(column) | |
| VARP(column) |
Aggregate functions in SQL Server
| Function | Description |
| AVG(column) | Returns the average value of a column |
| BINARY_CHECKSUM | |
| CHECKSUM | |
| CHECKSUM_AGG | |
| COUNT(column) | Returns the number of rows (without a NULL value) of a column |
| COUNT(*) | Returns the number of selected rows |
| COUNT(DISTINCT column) | Returns the number of distinct results |
| FIRST(column) | Returns the value of the first record in the specified field |
| LAST(column) | Returns the value of the last record in the specified field |
| MAX(column) | Returns the highest value of a column |
| MIN(column) | Returns the lowest value of a column |
| STDEV(column) | |
| STDEVP(column) | |
| SUM(column) | Returns the total sum of a column |
| VAR(column) | |
| VARP(column) |
Scalar functions
Scalar functions operate against a single value, and return a single value based on the input value.
Useful Scalar Functions in MS Access
| Function | Description |
| UCASE(c) | Converts a field to upper case |
| LCASE(c) | Converts a field to lower case |
| MID(c,start[,end]) | Extract characters from a text field |
| LEN(c) | Returns the length of a text field |
| INSTR(c) | Returns the numeric position of a named character within a text field |
| LEFT(c,number_of_char) | Return the left part of a text field requested |
| RIGHT(c,number_of_char) | Return the right part of a text field requested |
| ROUND(c,decimals) | Rounds a numeric field to the number of decimals specified |
| MOD(x,y) | Returns the remainder of a division operation |
| NOW() | Returns the current system date |
| FORMAT(c,format) | Changes the way a field is displayed |
| DATEDIFF(d,date1,date2) | Used to perform date calculations |
SELECT AVG(column) FROM table
SELECT AVG(Age) FROM Persons WHERE Age>20
SELECT COUNT(Age) FROM Persons
SELECT FIRST(Age) AS lowest_age
FROM Persons
ORDER BY Age
GROUP BY
GROUP BY... was added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called, and without the GROUP BY function it was impossible to find the sum for each individual group of column values.
The syntax for the GROUP BY function is:
| SELECT column,SUM(column) FROM table GROUP BY column |
GROUP BY Example
This "Sales" Table:
| Company | Amount |
| W3Schools | 5500 |
| IBM | 4500 |
| W3Schools | 7100 |
And This SQL:
| SELECT Company, SUM(Amount) FROM Sales |
Returns this result:
| Company | SUM(Amount) |
| W3Schools | 17100 |
| IBM | 17100 |
| W3Schools | 17100 |
The above code is invalid because the column returned is not part of an aggregate. A GROUP BY clause will solve this problem:
| SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company |
Returns this result:
| Company | SUM(Amount) |
| W3Schools | 12600 |
| IBM | 4500 |
HAVING CLAUSE
HAVING... was added to SQL because the WHERE keyword could not be used against aggregate functions (like SUM), and without HAVING... it would be impossible to test for result conditions.
The syntax for the HAVING function is:
| SELECT
column,SUM(column) FROM table
GROUP BY column HAVING SUM(column) condition value |
This "Sales" Table:
| Company | Amount |
| W3Schools | 5500 |
| IBM | 4500 |
| W3Schools | 7100 |
This SQL:
| SELECT
Company,SUM(Amount) FROM Sales
GROUP BY Company HAVING SUM(Amount)>10000 |
Returns this result
| Company | SUM(Amount) |
| W3Schools | 12600 |
The SELECT INTO Statement
The SELECT INTO statement is most often used to create backup copies of tables or for archiving records.
Syntax
| SELECT column_name(s)
INTO newtable [IN externaldatabase]
FROM source |
Make a Backup Copy
The following example makes a backup copy of the "Persons" table:
| SELECT * INTO Persons_backup FROM Persons |
The IN clause can be used to copy tables into another database:
| SELECT Persons.*
INTO Persons IN 'Backup.mdb'
FROM Persons |
If you only want to copy a few fields, you can do so by listing them after the SELECT statement:
| SELECT LastName,
FirstName INTO Persons_backup
FROM Persons |
You can also add a where clause. The following example creates a "Persons_backup" table with two columns (FirstName and LastName) by extracting the persons who lives in "Sandnes" from the "Persons" table:
| SELECT LastName, Firstname INTO Persons_sandnes
FROM Persons |
Selecting data from more than one table is also possible. The following example creates a new table "Empl_Ord_backup" that contains data from the two tables Employees and Orders:
| SELECT Employees.Name,Orders.Product
INTO Empl_Ord_backup FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID |