Thursday, October 21, 2010

SQL programme


Columns

A column contains data representing a specific characteristic of the records in the table. Columns are also known as fields or attributes.

Relationships

A relationship is a link between two tables (i.e, relations). Relationships make it possible to find data in one table that pertains to a specific record in another table.

Datatypes

Each of a table's columns has a defined datatype that specifies the type of data that can exist in that column. For example, the FirstName column might be defined as varchar(20), indicating that it can contain a string of up to 20 characters. Unfortunately, datatypes vary widely between databases.

imary Keys

Most tables have a column or group of columns that can be used to identify records. For example, an Employees table might have a column called EmployeeID that is unique for every row. This makes it easy to keep track of a record over time and to associate a record with records in other tables.

Foreign Keys

Foreign key columns are columns that link to primary key columns in other tables, thereby creating a relationship. For example, the Customers table might have a foreign key column called SalesRep that links to EmployeeID, the primary key in the Employees table.

Relational Database Management System

A Relational Database Management System (RDBMS), commonly (but incorrectly) called a database, is software for creating, manipulating, and administering a database. For simplicity, we will often refer to RDBMSs as databases.

Popular Databases

Commercial Databases

Oracle

Oracle is the most popular relational database. It runs on both Unix and Windows. It used to be many times more expensive than SQL Server and DB2, but it has come down a lot in price.

SQL Server

SQL Server is Microsoft's database and, not surprisingly, only runs on Windows. It has only a slightly higher market share than Oracle on Windows machines. Many people find it easier to use than Oracle.

DB2

IBM's DB2 was one of the earliest players in the database market. It is still very commonly used on mainframes and runs on both Windows and Unix.

Popular Open Source Databases

MySQL

Because of its small size, its speediness, and its very good documentation, MySQL has quickly become the most popular open source database. MySQL is available on both Windows and Unix, but it lacks some key features such as support for stored procedures.

PostgreSQL

Until recently, PostgreSQL was the most popular open source database until that spot was taken over by MySQL. PostgreSQL now calls itself "the world's most advanced Open Source database software." It is certainly a featureful and robust database management system and a good choice for people who want some of the advanced features that MySQL doesn't yet have. PostgreSQL does not yet natively support Windows, but it is supposed to in the upcoming 7.5 release.

Valid Object References

  • server.database.owner.object
  • server.database..object
  • server..owner.object
  • server...object
  • database.owner.object
  • database..object
  • owner.object
  • object

SQL Statements

Database Manipulation Language (DML)

DML statements are used to work with data in an existing database. The most common DML statements are:
  • SELECT
  • INSERT
  • UPDATE
  • DELETE

Database Definition Language (DDL)

DDL statements are used to structure objects in a database. The most common DDL statements are:
  • CREATE
  • ALTER
  • DROP

Database Control Language (DCL)

DCL statements are used for database administration. The most common DCL statements are:
  • GRANT
  • DENY (SQL Server Only)
  • REVOKE

Relational Database Basics Conclusion

We have covered a little bit of the history of SQL, how databases work, and the common SQL statements. Now we will get into learning how to work with SQL.
To continue to learn SQL go to the top of this page and cli

The SELECT statement is used to retrieve data from tables. SELECT statements can be used to perform simple tasks such as retrieving records from a single table or complicated tasks such as retrieving data from multiple tables with record grouping and sorting. In this lesson, we will look at several of the more basic ways to retrieve data from a single table.

Introduction to the Northwind Database

The Northwind database is a sample database used by Microsoft to demonstrate the features of some of its products, including SQL Server and Microsoft Access. The database contains the sales data for Northwind Traders, a fictitious specialty foods export-import company.
Although the code taught in this class is not specific to Microsoft products, we use the Northwind database for many of our examples because many people are already familiar with it and because there are many resources for related learning that make use of the same database.
The diagram below shows the table structure of the Northwind database.
The Northwind database has additional tables, but we will only be using the ones shown above. In this lesson, we will explore some of these tables.

Some Basics

Comments

The standard SQL comment is two hyphens (--). However, some databases use other forms of comments as shown in the table below.
SQL Comments
-- # /* */
Example -- Comment # Comment /* Comment */
ANSI YES NO NO
SQL Server YES NO YES
Oracle YES NO YES
MySQL YES YES YES
The code sample below shows some sample comments.

Code Sample: SimpleSelects/Demos/Comments.sql

-- Single-line comment
/*
 Multi-line comment used in:
   -SQL Server
   -Oracle
   -MySQL
*/

Whitespace and Semi-colons

Whitespace is ignored in SQL statements. Multiple statements are separated with semi-colons. The two statements in the sample below are equally valid.

Code Sample: SimpleSelects/Demos/WhiteSpace.sql

SELECT * FROM Employees;

SELECT *
FROM Employees;

Case Sensitivity

SQL is not case sensitive. It is common practice to write reserved words in all capital letters. User-defined names, such as table names and column names may or may not be case sensitive depending on the operating system used.

SELECTing All Columns in All Rows

The following syntax is used to retrieve all columns in all rows of a table.
Syntax
SELECT table.*
FROM table;

 -- OR

SELECT *
FROM table;

Code Sample: SimpleSelects/Demos/SelectAll.sql

--Retrieve all columns in the Region table
SELECT *
FROM Region;
Code Explanation
The above SELECT statement will return the following results:
As you can see, the Region table has only two columns, RegionID and RegionDescription, and four rows.

Exercise: Exploring the Tables




Duration: 10 to 20 minutes.
In this exercise, you will explore all the data in the Northwind database by selecting all the rows of each of the tables.
  1. Select all columns of all rows from the tables below.
  2. The number of records that should be returned is indicated in parentheses next to the table name.
    1. Categories (8)
    2. Customers (91)
    3. Employees (9)
    4. Orders (830)
    5. Products (77)
    6. Shippers (3)
    7. Suppliers (29)
Where is the solution?

SELECTing Specific Columns

The following syntax is used to retrieve specific columns in all rows of a table.
Syntax
SELECT table_name.column_name, table_name.column_name
FROM table;

 -- OR

SELECT column, column
FROM table;

Code Sample: SimpleSelects/Demos/SelectCols.sql

/*
Select the FirstName and LastName columns from the Employees table.
*/
SELECT FirstName, LastName
FROM Employees;
Code Explanation
The above SELECT statement will return the following results:

Exercise: SELECTing Specific Columns




Duration: 5 to 15 minutes.
In this exercise, you will practice selecting specific columns from tables in the Northwind database.
  1. Select CategoryName and Description from the Categories table.
  2. Select ContactName, CompanyName, ContactTitle and Phone from the Customers table.
  3. Select EmployeeID, Title, FirstName, LastName, and Region from the Employees table.
  4. Select RegionID and RegionDescription from the Region table.
  5. Select CompanyName, Fax, Phone and HomePage from the Suppliers table.
Where is the solution?

Sorting Records

The ORDER BY clause of the SELECT statement is used to sort records.

Sorting By a Single Column

To sort by a single column, simply name that column in the ORDER BY clause.
Syntax
SELECT column, column
FROM table
ORDER BY column;
Note that columns in the ORDER BY clause do not have to appear in the SELECT clause.

Code Sample: SimpleSelects/Demos/OrderBy1.sql

/*
 Select the FirstName and LastName columns from the Employees table. 
 Sort by LastName.
*/

SELECT FirstName, LastName
FROM Employees
ORDER BY LastName;
Code Explanation
The above SELECT statement will return the following results:

Sorting By Multiple Columns

To sort by multiple columns, comma-delimit the column names in the ORDER BY clause.
Syntax
SELECT column, column
FROM table
ORDER BY column, column;

Code Sample: SimpleSelects/Demos/OrderBy2.sql

/*
Select the Title, FirstName and LastName columns from the Employees table.
Sort first by Title and then by LastName.
*/

SELECT Title, FirstName, LastName
FROM Employees
ORDER BY Title, LastName;
Code Explanation
The above SELECT statement will return the following results:

Sorting By Column Position

It is also possible to sort tables by the position of a column in the SELECT list. To do so, specify the column numbers in the ORDER BY clause.
Syntax
SELECT column, column
FROM table
ORDER BY column_position, column_position;

Code Sample: SimpleSelects/Demos/OrderBy3.sql

/*
Select the Title, FirstName and LastName columns from the Employees table.
Sort first by Title (position 1) and then by LastName (position 3).
*/

SELECT Title, FirstName, LastName
FROM Employees
ORDER BY 1,3;
Code Explanation
The above SELECT statement will return the same results as the previous query:

Ascending and Descending Sorts

By default, when an ORDER BY clause is used, records are sorted in ascending order. This can be explicitly specified with the ASC keyword. To sort records in descending order, use the DESC keyword.
Syntax
SELECT column, column
FROM table
ORDER BY column_position DESC, column_position ASC;

Code Sample: SimpleSelects/Demos/OrderBy4.sql

/*
 Select the Title, FirstName and LastName columns from the Employees table.
 Sort first by Title in ascending order and then by LastName 
 in descending order.
*/

SELECT Title, FirstName, LastName
FROM Employees
ORDER BY Title ASC, LastName DESC;
Code Explanation
The above SELECT statement will return the following results:

Exercise: Sorting Results




Duration: 5 to 15 minutes.
In this exercise, you will practice sorting results in SELECT statements.
  1. Select CategoryName and Description from the Categories table sorted by CategoryName.
  2. Select ContactName, CompanyName, ContactTitle, and Phone from the Customers table sorted by Phone.
  3. Create a report showing employees' first and last names and hire dates sorted from newest to oldest employee.
  4. Create a report showing Northwind's orders sorted by Freight from most expensive to cheapest. Show OrderID, OrderDate, ShippedDate, CustomerID, and Freight.
  5. Select CompanyName, Fax, Phone, HomePage and Country from the Suppliers table sorted by Country in descending order and then by CompanyName in ascending order.
Where is the solution?

The WHERE Clause and Operator Symbols

The WHERE clause is used to retrieve specific rows from tables. The WHERE clause can contain one or more conditions that specify which rows should be returned.
Syntax
SELECT column, column
FROM table
WHERE conditions;
The following table shows the symbolic operators used in WHERE conditions.
SQL Symbol Operators
Operator Description
= Equals
<> Not Equal
> Greater Than
< Less Than
>= Greater Than or Equal To
<= Less Than or Equal To
Note that non-numeric values (e.g, dates and strings) in the WHERE clause must be enclosed in single quotes. Examples are shown below.

Checking for Equality

Code Sample: SimpleSelects/Demos/Where-Equal.sql

/*
Create a report showing the title and the first and last name
of all sales representatives.
*/

SELECT Title, FirstName, LastName
FROM Employees
WHERE Title = 'Sales Representative';
Code Explanation
The above SELECT statement will return the following results:

Checking for Inequality

Code Sample: SimpleSelects/Demos/Where-NotEqual.sql

/*
Create a report showing the first and last name of all employees
excluding sales representatives.
*/

SELECT FirstName, LastName
FROM Employees
WHERE Title <> 'Sales Representative';
Code Explanation
The above SELECT statement will return the following results:

Exercise: Using the WHERE clause to check for equality or inequality

Duration: 5 to 15 minutes.
In this exercise, you will practice using the WHERE clause to check for equality and inequality.
  1. Create a report showing all the company names and contact names of Northwind's customers in Buenos Aires.
  2. Create a report showing the product name, unit price and quantity per unit of all products that are out of stock.
  3. Create a report showing the order date, shipped date, customer id, and freight of all orders placed on May 19, 1997.
    • Oracle users will have to use following date format: 'dd-mmm-yyyy' (e.g, '19-May-1997').
    • MySQL users will have to use following date format: 'yyyy-mm-dd' (e.g, '1997-05-19').
  4. Create a report showing the first name, last name, and country of all employees not in the United States.
Where is the solution?

Checking for Greater or Less Than

The less than (<) and greater than (>) signs are used to compare numbers, dates, and strings.

Code Sample: SimpleSelects/Demos/Where-GreaterThanOrEqual.sql

/*
Create a report showing the first and last name of all employees whose
last names start with a letter in the last half of the alphabet.
*/

SELECT FirstName, LastName
FROM Employees
WHERE LastName >= 'N';
Code Explanation
The above SELECT statement will return the following results:

Exercise: Using the WHERE clause to check for greater or less than

Duration: 5 to 15 minutes.
In this exercise, you will practice using the WHERE clause to check for values greater than or less than a specified value.
  1. Create a report that shows the employee id, order id, customer id, required date, and shipped date of all orders that were shipped later than they were required.
  2. Create a report that shows the city, company name, and contact name of all customers who are in cities that begin with "A" or "B."
  3. Create a report that shows all orders that have a freight cost of more than $500.00.
  4. Create a report that shows the product name, units in stock, units on order, and reorder level of all products that are up for reorder.
Where is the solution?

Checking for NULL

When a field in a row has no value, it is said to be NULL. This is not the same as having an empty string. Rather, it means that the field contains no value at all. When checking to see if a field is NULL, you cannot use the equals sign (=); rather, use the IS NULL expression.

Code Sample: SimpleSelects/Demos/Where-Null.sql

/*
Create a report showing the first and last names of
all employees whose region is unspecified.
*/

SELECT FirstName, LastName
FROM Employees
WHERE Region IS NULL;
Code Explanation
The above SELECT statement will return the following results:

Code Sample: SimpleSelects/Demos/Where-NotNull.sql

/*
Create a report showing the first and last names of all
employees who have a region specified.
*/

SELECT FirstName, LastName
FROM Employees
WHERE Region IS NOT NULL;
Code Explanation
The above SELECT statement will return the following results:

Exercise: Checking for NULL

Duration: 5 to 15 minutes.
In this exercise, you will practice selecting records with fields that have NULL values.
  1. Create a report that shows the company name, contact name and fax number of all customers that have a fax number.
  2. Create a report that shows the first and last name of all employees who do not report to anybody.
Where is the solution?

WHERE and ORDER BY

When using WHERE and ORDER BY together, the WHERE clause must come before the ORDER BY clause.

Code Sample: SimpleSelects/Demos/Where-OrderBy.sql

/*
Create a report showing the first and last name of all employees whose 
last names start with a letter in the last half of the alphabet.
Sort by LastName in descending order.
*/

SELECT FirstName, LastName
FROM Employees
WHERE LastName >= 'N'
ORDER BY LastName DESC;
Code Explanation
The above SELECT statement will return the following results:

Exercise: Using WHERE and ORDER BY Together

Duration: 5 to 15 minutes.
In this exercise, you will practice writing SELECT statements that use both WHERE and ORDER BY.
  1. Create a report that shows the company name, contact name and fax number of all customers that have a fax number. Sort by company name.
  2. Create a report that shows the city, company name, and contact name of all customers who are in cities that begin with "A" or "B." Sort by contact name in descending order.
Where is the solution?

The WHERE Clause and Operator Words

The following table shows the word operators used in WHERE conditions.
SQL Word Operators
Operator Description
BETWEEN Returns values in an inclusive range
IN Returns values in a specified subset
LIKE Returns values that match a simple pattern
NOT Negates an operation

The BETWEEN Operator

The BETWEEN operator is used to check if field values are within a specified inclusive range.

Code Sample: SimpleSelects/Demos/Where-Between.sql

/*
Create a report showing the first and last name of all employees
whose last names start with a letter between "J" and "M". 
*/

SELECT FirstName, LastName
FROM Employees
WHERE LastName BETWEEN 'J' AND 'M';

-- The above SELECT statement is the same as the one below.

SELECT FirstName, LastName
FROM Employees
WHERE LastName >= 'J' AND LastName <= 'M';
Code Explanation
The above SELECT statements will both return the following results:
Note that a person with the last name "M" would be included in this report.

The IN Operator

The IN operator is used to check if field values are included in a specified comma-delimited list.

Code Sample: SimpleSelects/Demos/Where-In.sql

/*
Create a report showing the title of courtesy and the first and
last name of all employees whose title of courtesy is "Mrs." or "Ms.". 
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy IN ('Ms.','Mrs.');

-- The above SELECT statement is the same as the one below

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy = 'Ms.' OR TitleOfCourtesy = 'Mrs.';
Code Explanation
The above SELECT statements will both return the following results:

The LIKE Operator

The LIKE operator is used to check if field values match a specified pattern.

The Percent Sign (%)

The percent sign (%) is used to match any zero or more characters.

Code Sample: SimpleSelects/Demos/Where-Like1.sql

/*
Create a report showing the title of courtesy and the first
and last name of all employees whose title of courtesy begins with "M". 
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy LIKE 'M%';
Code Explanation
The above SELECT statement will return the following results:

The Underscore (_)

The underscore (_) is used to match any single character.

Code Sample: SimpleSelects/Demos/Where-Like2.sql

/*
Create a report showing the title of courtesy and the first and
last name of all employees whose title of courtesy begins with "M" and
is followed by any character and a period (.).
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy LIKE 'M_.';
Code Explanation
The above SELECT statement will return the following results:

Wildcards and Performance

Using wildcards can slow down performance, especially if they are used at the beginning of a pattern. You should use them sparingly.

The NOT Operator

The NOT operator is used to negate an operation.

Code Sample: SimpleSelects/Demos/Where-Not.sql

/*
Create a report showing the title of courtesy and the first and last name
of all employees whose title of courtesy is not "Ms." or "Mrs.".
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE NOT TitleOfCourtesy IN ('Ms.','Mrs.');
Code Explanation
The above SELECT statement will return the following results:

Exercise: More SELECTs with WHERE

Duration: 5 to 15 minutes.
In this exercise, you will practice writing SELECT statements that use WHERE with word operators.
  1. Create a report that shows the first and last names and birth date of all employees born in the 1950s.
  2. Create a report that shows the product name and supplier id for all products supplied by Exotic Liquids, Grandma Kelly's Homestead, and Tokyo Traders. Hint: you will need to first do a separate SELECT on the Suppliers table to find the supplier ids of these three companies.
  3. Create a report that shows the shipping postal code, order id, and order date for all orders with a ship postal code beginning with "02389".
  4. Create a report that shows the contact name and title and the company name for all customers whose contact title does not contain the word "Sales".
Where is the solution?

Checking Multiple Conditions

AND

AND can be used in a WHERE clause to find records that match more than one condition.

Code Sample: SimpleSelects/Demos/Where-And.sql

/*
Create a report showing the first and last name of all
sales representatives whose title of courtesy is "Mr.".
*/

SELECT FirstName, LastName
FROM Employees
WHERE Title = 'Sales Representative'
 AND TitleOfCourtesy = 'Mr.';
Code Explanation
The above SELECT statement will return the following results:

OR

OR can be used in a WHERE clause to find records that match at least one of several conditions.

Code Sample: SimpleSelects/Demos/Where-Or.sql

/*
 Create a report showing the first and last name and the city of all 
 employees who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City
FROM Employees
WHERE City = 'Seattle' OR City = 'Redmond';
Code Explanation
The above SELECT statement will return the following results:

Order of Evaluation

By default, SQL processes AND operators before it processes OR operators. To illustrate how this works, take a look at the following example.

Code Sample: SimpleSelects/Demos/Where-AndOrPrecedence.sql

/*
 Create a report showing the first and last name of all sales 
 representatives who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City, Title
FROM Employees
WHERE City = 'Seattle' OR City = 'Redmond'
 AND Title = 'Sales Representative';
Code Explanation
The above SELECT statement will return the following results:
Notice that Laura Callahan is returned by the query even though she is not a sales representative. This is because this query is looking for employees from Seattle OR sales representatives from Redmond.
This can be fixed by putting the OR portion of the clause in parentheses.

Code Sample: SimpleSelects/Demos/Where-AndOrPrecedence2.sql

/*
 Create a report showing the first and last name of all sales 
 representatives who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City, Title
FROM Employees
WHERE (City = 'Seattle' OR City = 'Redmond')
 AND Title = 'Sales Representative';
Code Explanation
The parentheses specify that the OR portion of the clause should be evaluated first, so the above SELECT statement will return the same results minus Laura Callahan.
If only to make the code more readable, it's a good idea to use parentheses whenever the order of precedence might appear ambiguous.

Exercise: Writing SELECTs with Multiple Conditions

Duration: 5 to 15 minutes.
In this exercise, you will practice writing SELECT statements that filter records based on multiple conditions.
  1. Create a report that shows the first and last names and cities of employees from cities other than Seattle in the state of Washington.
  2. Create a report that shows the company name, contact title, city and country of all customers in Mexico or in any city in Spain except Madrid.

Simple SELECTs Conclusion

In this lesson of the SQL tutorial, you have learned a lot about creating reports with SELECT. However, this is just the tip of the iceberg. SELECT statements can get a lot more powerful and, of course, a lot more complicated.
To continue to learn SQL go to the top of this page and click on the next lesson in this SQL Tutorial's Table of Contents.
Last updated on 2009-05-11

Checking for Greater or Less Than

The less than (<) and greater than (>) signs are used to compare numbers, dates, and strings.

Code Sample: SimpleSelects/Demos/Where-GreaterThanOrEqual.sql

/*
Create a report showing the first and last name of all employees whose
last names start with a letter in the last half of the alphabet.
*/

SELECT FirstName, LastName
FROM Employees
WHERE LastName >= 'N';
Code Explanation
The above SELECT statement will return the following results:

Exercise: Using the WHERE clause to check for greater or less than

Duration: 5 to 15 minutes.
In this exercise, you will practice using the WHERE clause to check for values greater than or less than a specified value.
  1. Create a report that shows the employee id, order id, customer id, required date, and shipped date of all orders that were shipped later than they were required.
  2. Create a report that shows the city, company name, and contact name of all customers who are in cities that begin with "A" or "B."
  3. Create a report that shows all orders that have a freight cost of more than $500.00.
  4. Create a report that shows the product name, units in stock, units on order, and reorder level of all products that are up for reorder.
Where is the solution?

Checking for NULL

When a field in a row has no value, it is said to be NULL. This is not the same as having an empty string. Rather, it means that the field contains no value at all. When checking to see if a field is NULL, you cannot use the equals sign (=); rather, use the IS NULL expression.

Code Sample: SimpleSelects/Demos/Where-Null.sql

/*
Create a report showing the first and last names of
all employees whose region is unspecified.
*/

SELECT FirstName, LastName
FROM Employees
WHERE Region IS NULL;
Code Explanation
The above SELECT statement will return the following results:

Code Sample: SimpleSelects/Demos/Where-NotNull.sql

/*
Create a report showing the first and last names of all
employees who have a region specified.
*/

SELECT FirstName, LastName
FROM Employees
WHERE Region IS NOT NULL;
Code Explanation
The above SELECT statement will return the following results:

Exercise: Checking for NULL

Duration: 5 to 15 minutes.
In this exercise, you will practice selecting records with fields that have NULL values.
  1. Create a report that shows the company name, contact name and fax number of all customers that have a fax number.
  2. Create a report that shows the first and last name of all employees who do not report to anybody.
Where is the solution?

WHERE and ORDER BY

When using WHERE and ORDER BY together, the WHERE clause must come before the ORDER BY clause.

Code Sample: SimpleSelects/Demos/Where-OrderBy.sql

/*
Create a report showing the first and last name of all employees whose 
last names start with a letter in the last half of the alphabet.
Sort by LastName in descending order.
*/

SELECT FirstName, LastName
FROM Employees
WHERE LastName >= 'N'
ORDER BY LastName DESC;
Code Explanation
The above SELECT statement will return the following results:

Exercise: Using WHERE and ORDER BY Together

Duration: 5 to 15 minutes.
In this exercise, you will practice writing SELECT statements that use both WHERE and ORDER BY.
  1. Create a report that shows the company name, contact name and fax number of all customers that have a fax number. Sort by company name.
  2. Create a report that shows the city, company name, and contact name of all customers who are in cities that begin with "A" or "B." Sort by contact name in descending order.
Where is the solution?

The WHERE Clause and Operator Words

The following table shows the word operators used in WHERE conditions.
SQL Word Operators
Operator Description
BETWEEN Returns values in an inclusive range
IN Returns values in a specified subset
LIKE Returns values that match a simple pattern
NOT Negates an operation

The BETWEEN Operator

The BETWEEN operator is used to check if field values are within a specified inclusive range.

Code Sample: SimpleSelects/Demos/Where-Between.sql

/*
Create a report showing the first and last name of all employees
whose last names start with a letter between "J" and "M". 
*/

SELECT FirstName, LastName
FROM Employees
WHERE LastName BETWEEN 'J' AND 'M';

-- The above SELECT statement is the same as the one below.

SELECT FirstName, LastName
FROM Employees
WHERE LastName >= 'J' AND LastName <= 'M';
Code Explanation
The above SELECT statements will both return the following results:
Note that a person with the last name "M" would be included in this report.

The IN Operator

The IN operator is used to check if field values are included in a specified comma-delimited list.

Code Sample: SimpleSelects/Demos/Where-In.sql

/*
Create a report showing the title of courtesy and the first and
last name of all employees whose title of courtesy is "Mrs." or "Ms.". 
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy IN ('Ms.','Mrs.');

-- The above SELECT statement is the same as the one below

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy = 'Ms.' OR TitleOfCourtesy = 'Mrs.';
Code Explanation
The above SELECT statements will both return the following results:

The LIKE Operator

The LIKE operator is used to check if field values match a specified pattern.

The Percent Sign (%)

The percent sign (%) is used to match any zero or more characters.

Code Sample: SimpleSelects/Demos/Where-Like1.sql

/*
Create a report showing the title of courtesy and the first
and last name of all employees whose title of courtesy begins with "M". 
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy LIKE 'M%';
Code Explanation
The above SELECT statement will return the following results:

The Underscore (_)

The underscore (_) is used to match any single character.

Code Sample: SimpleSelects/Demos/Where-Like2.sql

/*
Create a report showing the title of courtesy and the first and
last name of all employees whose title of courtesy begins with "M" and
is followed by any character and a period (.).
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy LIKE 'M_.';
Code Explanation
The above SELECT statement will return the following results:

Wildcards and Performance

Using wildcards can slow down performance, especially if they are used at the beginning of a pattern. You should use them sparingly.

The NOT Operator

The NOT operator is used to negate an operation.

Code Sample: SimpleSelects/Demos/Where-Not.sql

/*
Create a report showing the title of courtesy and the first and last name
of all employees whose title of courtesy is not "Ms." or "Mrs.".
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE NOT TitleOfCourtesy IN ('Ms.','Mrs.');
Code Explanation
The above SELECT statement will return the following results:

Exercise: More SELECTs with WHERE

Duration: 5 to 15 minutes.
In this exercise, you will practice writing SELECT statements that use WHERE with word operators.
  1. Create a report that shows the first and last names and birth date of all employees born in the 1950s.
  2. Create a report that shows the product name and supplier id for all products supplied by Exotic Liquids, Grandma Kelly's Homestead, and Tokyo Traders. Hint: you will need to first do a separate SELECT on the Suppliers table to find the supplier ids of these three companies.
  3. Create a report that shows the shipping postal code, order id, and order date for all orders with a ship postal code beginning with "02389".
  4. Create a report that shows the contact name and title and the company name for all customers whose contact title does not contain the word "Sales".
Where is the solution?

Checking Multiple Conditions

AND

AND can be used in a WHERE clause to find records that match more than one condition.

Code Sample: SimpleSelects/Demos/Where-And.sql

/*
Create a report showing the first and last name of all
sales representatives whose title of courtesy is "Mr.".
*/

SELECT FirstName, LastName
FROM Employees
WHERE Title = 'Sales Representative'
 AND TitleOfCourtesy = 'Mr.';
Code Explanation
The above SELECT statement will return the following results:

OR

OR can be used in a WHERE clause to find records that match at least one of several conditions.

Code Sample: SimpleSelects/Demos/Where-Or.sql

/*
 Create a report showing the first and last name and the city of all 
 employees who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City
FROM Employees
WHERE City = 'Seattle' OR City = 'Redmond';
Code Explanation
The above SELECT statement will return the following results:

Order of Evaluation

By default, SQL processes AND operators before it processes OR operators. To illustrate how this works, take a look at the following example.

Code Sample: SimpleSelects/Demos/Where-AndOrPrecedence.sql

/*
 Create a report showing the first and last name of all sales 
 representatives who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City, Title
FROM Employees
WHERE City = 'Seattle' OR City = 'Redmond'
 AND Title = 'Sales Representative';
Code Explanation
The above SELECT statement will return the following results:
Notice that Laura Callahan is returned by the query even though she is not a sales representative. This is because this query is looking for employees from Seattle OR sales representatives from Redmond.
This can be fixed by putting the OR portion of the clause in parentheses.

Code Sample: SimpleSelects/Demos/Where-AndOrPrecedence2.sql

/*
 Create a report showing the first and last name of all sales 
 representatives who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City, Title
FROM Employees
WHERE (City = 'Seattle' OR City = 'Redmond')
 AND Title = 'Sales Representative';
Code Explanation
The parentheses specify that the OR portion of the clause should be evaluated first, so the above SELECT statement will return the same results minus Laura Callahan.
If only to make the code more readable, it's a good idea to use parentheses whenever the order of precedence might appear ambiguous.

Exercise: Writing SELECTs with Multiple Conditions

Duration: 5 to 15 minutes.
In this exercise, you will practice writing SELECT statements that filter records based on multiple conditions.
  1. Create a report that shows the first and last names and cities of employees from cities other than Seattle in the state of Washington.
  2. Create a report that shows the company name, contact title, city and country of all customers in Mexico or in any city in Spain except Madrid.

Simple SELECTs Conclusion

In this lesson of the SQL tutorial, you have learned a lot about creating reports with SELECT. However, this is just the tip of the iceberg. SELECT statements can get a lot more powerful and, of course, a lot more complicated.

Calculated Fields

Calculated fields are fields that do not exist in a table, but are created in the SELECT statement. For example, you might want to create FullName from FirstName and LastName.

Concatenation

Concatenation is a fancy word for stringing together different words or characters. SQL Server, Oracle and MySQL each has its own way of handling concatenation. All three of the code samples below will return the following results:
In SQL Server, the plus sign (+) is used as the concatenation operator.

Code Sample: AdvancedSelects/Demos/Concatenate-SqlServer.sql

-- Select the full name of all employees. SQL SERVER.

SELECT FirstName + ' ' + LastName
FROM Employees;
In Oracle, the double pipe (||) is used as the concatenation operator.

Code Sample: AdvancedSelects/Demos/Concatenate-Oracle.sql

-- Select the full name of all employees. Oracle.

SELECT FirstName || ' ' || LastName
FROM Employees;
MySQL does this in yet another way. There is no concatenation operator. Instead, MySQL uses the CONCAT() function (see footnote).

Code Sample: AdvancedSelects/Demos/Concatenate-MySQL.sql

-- Select the full name of all employees. MySQL.
SELECT CONCAT(FirstName, ' ', LastName)
FROM Employees;
Note that concatenation only works with strings. To concatenate other data types, you must first convert them to strings. (see footnote)

Mathematical Calculations

Mathematical calculations in SQL are similar to those in other languages.
Mathematical Operators
Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulus

Code Sample: AdvancedSelects/Demos/MathCalc.sql

/*
If the cost of freight is greater than or equal to $500.00,
it will now be taxed by 10%. Create a report that shows the
order id, freight cost, freight cost with this tax for all
orders of $500 or more.
*/

SELECT OrderID, Freight, Freight * 1.1
FROM Orders
WHERE Freight >= 500;
Code Explanation
The above SELECT statement will return the following results:

Aliases

You will notice in the examples above that the calculated columns have the header "(No column name)". The keyword AS is used to provide a named header for the column.

Code Sample: AdvancedSelects/Demos/Alias.sql

SELECT OrderID, Freight, Freight * 1.1 AS FreightTotal
FROM Orders
WHERE Freight >= 500;
Code Explanation
As you can see, the third column now has the title "FreightTotal".

Exercise: Calculating Fields




Duration: 10 to 20 minutes.
In this exercise, you will practice writing SELECT statements with calculated fields.
  1. Create a report that shows the unit price, quantity, discount, and the calculated total price using these three fields.
    • Note for SQL Server users only: You will be using the Order Details table. Because this table name has a space in it, you will need to put it in double quotes in the FROM clause (e.g, FROM "Order Details").
  2. Write a SELECT statement that outputs the following.
Where is the solution?

Aggregate Functions and Grouping

Aggregate Functions

Aggregate functions are used to calculate results using field values from multiple records. There are five common aggregate functions.
Common Aggregate Functions
Aggregate Function Description
COUNT() Returns the number of rows containing non-NULL values in the specified field.
SUM() Returns the sum of the non-NULL values in the specified field.
AVG() Returns the average of the non-NULL values in the specified field.
MAX() Returns the maximum of the non-NULL values in the specified field.
MIN() Returns the minimum of the non-NULL values in the specified field.

Code Sample: AdvancedSelects/Demos/Aggregate-Count.sql

-- Find the Number of Employees

SELECT COUNT(*) AS NumEmployees
FROM Employees;
Code Explanation
Returns 9.

Code Sample: AdvancedSelects/Demos/Aggregate-Sum.sql

-- Find the Total Number of Units Ordered of Product ID 3

/******************************
SQL Server
******************************/
SELECT SUM(Quantity) AS TotalUnits
FROM "Order Details"
WHERE ProductID=3;

/******************************
Oracle and MySQL
******************************/
SELECT SUM(Quantity) AS TotalUnits
FROM Order_Details
WHERE ProductID=3;
Code Explanation
Returns 328.

Code Sample: AdvancedSelects/Demos/Aggregate-Avg.sql

-- Find the Average Unit Price of Products

SELECT AVG(UnitPrice) AS AveragePrice
FROM Products;
Code Explanation
Returns 28.8663.

Code Sample: AdvancedSelects/Demos/Aggregate-MinMax.sql

-- Find the Earliest and Latest Dates of Hire

SELECT MIN(HireDate) AS FirstHireDate,
 MAX(HireDate) AS LastHireDate
FROM Employees;
Code Explanation
The above SELECT statement will return April 1, 1992 and November 15, 1994 as the FirstHireDate and LastHireDate, respectively. The date format will vary from database to database.

Grouping Data

GROUP BY

With the GROUP BY clause, aggregate functions can be applied to groups of records based on column values. For example, the following code will return the number of employees in each city.

Code Sample: AdvancedSelects/Demos/Aggregate-GroupBy.sql

--Retrieve the number of employees in each city

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY City;
The above SELECT statement will return the following results:

HAVING

The HAVING clause is used to filter grouped data. For example, the following code specifies that we only want information on cities that have more than one employee.

Code Sample: AdvancedSelects/Demos/Aggregate-Having.sql

/*
 Retrieve the number of employees in each city 
 in which there are at least 2 employees.
*/

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY City
HAVING COUNT(EmployeeID) > 1;
The above SELECT statement will return the following results:

Order of Clauses

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

Code Sample: AdvancedSelects/Demos/Aggregate-OrderOfClauses.sql

/*
 Find the number of sales representatives in each city that contains 
 at least 2 sales representatives. Order by the number of employees.
*/

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
WHERE Title = 'Sales Representative'
GROUP BY City
HAVING COUNT(EmployeeID) > 1
ORDER BY NumEmployees;
The above SELECT statement will return the following results:

Grouping Rules

  • Every non-aggregate column that appears in the SELECT clause must also appear in the GROUP BY clause.
  • You may not use aliases in the HAVING clause.(see footnote)
  • You may use aliases in the ORDER BY clause.
  • You may only use calculated fields in the HAVING clause.
  • You may use calculated field aliases or actual fields in the ORDER BY clause.

Exercise: Working with Aggregate Functions




Duration: 10 to 20 minutes.
In this exercise, you will practice working with aggregate functions.
  1. Create a report that returns the following from the Order_Details table. The report should only return rows for which TotalUnits is less than 200.
  2. Create a report that returns the following from the Products table. The report should only return rows for which the average unit price of a product is greater than 70.
  3. Create a report that returns the following from the Orders table. NumOrders represents the number of orders placed by a certain customer. Only return rows where NumOrders is greater than 15.
Query number 2 above has something strange about it. It is, in fact, a ridiculous query. Why? Try to get the exact same results without using an aggregate function.
Where is the solution?

Selecting Distinct Records

The DISTINCT keyword is used to select distinct combinations of column values from a table. For example, the following example shows how you would find all the distinct cities in which Northwind has employees.

Code Sample: AdvancedSelects/Demos/Distinct.sql

/*
Find all the distinct cities in which Northwind has employees.
*/

SELECT DISTINCT City
FROM Employees
ORDER BY City
DISTINCT is often used with aggregate functions. The following example shows how DISTINCT can be used to find out in how many different cities Northwind has employees.

Code Sample: AdvancedSelects/Demos/Distinct-Count.sql

/*
Find out in how many different cities Northwind has employees.
*/

SELECT COUNT(DISTINCT City) AS NumCities
FROM Employees

Built-in Data Manipulation Functions

In this section, we will discuss some of the more common built-in data manipulation functions. Unfortunately, the functions differ greatly between databases, so you should be sure to check your database documentation when using these functions.
The tables below show some of the more common math, string, and date functions.

Common Math Functions

Common Math Functions
Description SQL Server Oracle MySQL
Absolute value ABS ABS ABS
Smallest integer >= value CEILING CEIL CEILING
Round down to nearest integer FLOOR FLOOR FLOOR
Power POWER POWER POWER
Round ROUND ROUND ROUND
Square root SQRT SQRT SQRT
Formatting numbers to two decimal places CAST(num AS decimal(8,2)) TO_CHAR(num,'9.00') FORMAT(num,2) or CAST(num AS decimal(8,2))

Code Sample: AdvancedSelects/Demos/Functions-Math1.sql

/*
Select freight as is and
freight rounded to the first decimal (e.g, 1.150 becomes 1.200)
from the Orders tables
*/

SELECT Freight, ROUND(Freight,1)  AS ApproxFreight
FROM Orders;
Code Explanation
The above SELECT statement will return the following results (not all rows shown):

Code Sample: AdvancedSelects/Demos/Functions-Math2.sql

/*
Select the unit price as is and
unit price as a decimal with 2 places to the right of the decimal point
from the Products tables
*/
/******************************
SQL Server and MySQL
******************************/
SELECT UnitPrice, CAST(UnitPrice AS Decimal(8,2))
FROM Products;

/******************************
Oracle
******************************/
SELECT UnitPrice, TO_CHAR(UnitPrice,'999.99')
FROM Products;
Code Explanation
The above SELECT statement will return the following results (not all rows shown):
Note that you would round to a whole number by passing 0 as the second parameter: ROUND(field,0); and to the tens place by passing -1: ROUND(field,-1).

Common String Functions

Common String Functions
Description SQL Server Oracle MySQL
Convert characters to lowercase LOWER LOWER LOWER
Convert characters to uppercase UPPER UPPER UPPER
Remove trailing blank spaces RTRIM RTRIM RTRIM
Remove leading blank spaces LTRIM LTRIM LTRIM
Substring SUBSTRING SUBSTR SUBSTRING

Code Sample: AdvancedSelects/Demos/Functions-String1.sql

/*
Select first and last name from employees in all uppercase letters
*/
SELECT UPPER(FirstName), UPPER(LastName)
FROM Employees;
Code Explanation
The above SELECT statement will return the following results:

Code Sample: AdvancedSelects/Demos/Functions-String2.sql

-- Select the first 10 characters of each customer's address

/******************************
SQL Server and MySQL
******************************/
SELECT SUBSTRING(Address,1,10)
FROM Customers;

/******************************
Oracle
******************************/
SELECT SUBSTR(Address,1,10)
FROM Customers;
Code Explanation
The above SELECT statement will return the following results (not all rows shown):

Common Date Functions

Common Date Functions
Description SQL Server Oracle MySQL
Date addition DATEADD (use +) DATE_ADD
Date subtraction DATEDIFF (use -) DATEDIFF
Convert date to string DATENAME TO_CHAR DATE_FORMAT
Convert date to number DATEPART TO_NUMBER(TO_CHAR) EXTRACT
Get current date and time GETDATE SYS_DATE NOW

Code Sample: AdvancedSelects/Demos/Functions-Date1.sql

-- Find the hiring age of each employee

/******************************
SQL Server
******************************/
SELECT LastName, BirthDate, HireDate, DATEDIFF(year,BirthDate,HireDate) AS HireAge
FROM Employees
ORDER BY HireAge;

/******************************
Oracle
******************************/
SELECT  LastName, BirthDate, HireDate, FLOOR((HireDate - BirthDate)/365.25) AS HireAge
FROM Employees
ORDER BY HireAge;

/******************************
MySQL
******************************/
-- Find the hiring age of each employee
-- in versions of MySQL prior to 4.1.1
SELECT LastName, BirthDate, HireDate, YEAR(HireDate)-YEAR(BirthDate) AS HireAge
FROM Employees;

-- In MySQL 4.1.1 and later, DATEDIFF() returns the number of days between
-- two dates. You can then divide and floor to get age.
SELECT LastName, BirthDate, HireDate, FLOOR(DATEDIFF(HireDate,BirthDate)/365) AS HireAge
FROM Employees
ORDER BY HireAge;
Code Explanation
The above SELECT statement will return the following results in SQL Server:
And like this in Oracle:
Note for SQL Server users: SQL Server is subtracting the year the employee was born from the year (s)he was hired. This does not give us an accurate age. We'll fix this in an upcoming exercise.

Code Sample: AdvancedSelects/Demos/Functions-Date2.sql

-- Find the Birth month for every employee

/******************************
SQL Server
******************************/
SELECT FirstName, LastName, DATENAME(month,BirthDate) AS BirthMonth
FROM Employees
ORDER BY DATEPART(month,BirthDate);

/******************************
Oracle
******************************/
SELECT FirstName, LastName, TO_CHAR(BirthDate,'MONTH') AS BirthMonth
FROM Employees
ORDER BY TO_NUMBER(TO_CHAR(BirthDate,'MM'));

/******************************
MySQL
******************************/
SELECT FirstName, LastName, DATE_FORMAT(BirthDate, '%M') AS BirthMonth
FROM Employees
ORDER BY EXTRACT(MONTH FROM BirthDate);
Code Explanation
The above SELECT statement will return the following results:

Exercise: Data Manipulation Functions




Duration: 10 to 20 minutes.
In this exercise, you will practice using data manipulation functions.
  1. Create a report that shows the units in stock, unit price, the total price value of all units in stock, the total price value of all units in stock rounded down, and the total price value of all units in stock rounded up. Sort by the total price value descending.
  2. SQL SERVER AND MYSQL USERS ONLY: In an earlier demo, you saw a report that returned the age of each employee when hired. That report was not entirely accurate as it didn't account for the month and day the employee was born. Fix that report, showing both the original (inaccurate) hire age and the actual hire age. The result will look like this.
  3. Create a report that shows the first and last names and birth month (as a string) for each employee born in the current month.
  4. Create a report that shows the contact title in all lowercase letters of each customer contact.

Advanced SELECTs Conclusion

In this lesson of the SQL tutorial, you have continued to use SELECT to create reports from data stored in a single table. In the next lesson, you will learn to create reports from data in multiple tables.

Footnotes

  1. We'll look at functions more in Built-in Data Manipulation Functions.
  2. Conversion is covered briefly in Built-in Data Manipulation Functions.
  3. MySQL allows usage of aliases in the HAVING clause, but you may want to avoid this to keep your code as cross-database compatible as possible.

Using CASE

CASE functions contain one or more WHEN clauses as shown below.
Syntax
--OPTION 1
SELECT CASE column
   WHEN VALUE THEN RETURN_VALUE
   WHEN VALUE THEN RETURN_VALUE
   WHEN VALUE THEN RETURN_VALUE
   WHEN VALUE THEN RETURN_VALUE
   ELSE RETURN_VALUE
  END
 AS ColumnName
FROM table

--OPTION 2
SELECT CASE
   WHEN EXPRESSION THEN RETURN_VALUE
   WHEN EXPRESSION THEN RETURN_VALUE
   WHEN EXPRESSION THEN RETURN_VALUE
   WHEN EXPRESSION THEN RETURN_VALUE
   ELSE RETURN_VALUE
  END
 AS ColumnName
FROM table

Code Sample: Case/Demos/Case.sql

/*
Create a report showing the customer ID and company name,
employee id, firstname and lastname, and the order id
and a conditional column called "Shipped" that displays "On Time"
if the order was shipped on time and "Late" if the order was shipped late.
*/

SELECT c.CustomerID, c.CompanyName, e.EmployeeID, e.FirstName, e.LastName, OrderID,
 (CASE
  WHEN ShippedDate < RequiredDate
   THEN 'On Time'
   ELSE 'Late'
   END) AS Shipped
FROM Orders o
 JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
 JOIN Customers c ON (c.CustomerID = o.CustomerID)
ORDER BY Shipped;
Code Explanation
The above SELECT statement will return the following results (not all rows shown).

Code Sample: Case/Demos/Case-GroupBy.sql

/*
Create a report showing the customer ID and company name,
employee id, firstname and lastname, and the order id
and a conditional column called "Shipped" that displays "On Time"
if the order was shipped on time and "Late" if the order was shipped late.
*/

SELECT e.FirstName, e.LastName, COUNT(o.OrderID) As NumOrders,
 (CASE
  WHEN o.ShippedDate < o.RequiredDate
   THEN 'On Time'
   ELSE 'Late'
   END)
  AS Shipped
FROM Orders o
 JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
GROUP BY e.FirstName, e.LastName,
 (CASE
  WHEN o.ShippedDate < o.RequiredDate
   THEN 'On Time'
   ELSE 'Late'
   END)
ORDER BY e.LastName, e.FirstName, NumOrders DESC;
Code Explanation
The above SELECT statement will return the following results.
Notice how the GROUP BY clause contains the same CASE statement that is in the SELECT clause. This is required because all non-aggregate columns in the SELECT clause must also be in the GROUP BY clause and the GROUP BY clause cannot contain aliases defined in the SELECT clause.

Exercise: Working with CASE




Duration: 10 to 15 minutes.
In this exercise you will practice using CASE.
  1. Create a report that shows the company names and faxes for all customers. If the customer doesn't have a fax, the report should show "No Fax" in that field as shown below.

Conditional Processing with CASE Conclusion

In this lesson of the SQL tutorial, you learned about using CASE to output different values in reports based on data contained in table fields.


INSERT

To insert a record into a table, you must specify values for all fields that do not have default values and cannot be NULL.
Syntax
INSERT INTO table
(columns)
VALUES (values);
The second line of the above statement can be excluded if all required columns are inserted and the values are listed in the same order as the columns in the table. We recommend you include the second line all the time though as the code will be easier to read and update and less likely to break as the database is modified.

Code Sample: InsertsUpdatesDeletes/Demos/Insert.sql

-- Insert a New Employee

/******************************
Both of the inserts below will work in SQL Server

Oracle
******************************/
INSERT INTO Employees
(LastName, FirstName, Title, TitleOfCourtesy,
 BirthDate, HireDate, Address, City, Region,
 PostalCode, Country, HomePhone, Extension)
VALUES ('Dunn','Nat','Sales Representative','Mr.','19-Feb-1970',
 '15-Jan-2004','4933 Jamesville Rd.','Jamesville','NY',
 '13078','USA','315-555-5555','130');

/******************************
MySQL
******************************/
INSERT INTO Employees
(LastName, FirstName, Title, TitleOfCourtesy,
 BirthDate, HireDate, Address, City, Region,
 PostalCode, Country, HomePhone, Extension)
VALUES ('Dunn','Nat','Sales Representative','Mr.','1970-02-19',
 '2004-01-15','4933 Jamesville Rd.','Jamesville','NY',
 '13078','USA','315-555-5555','130');
Code Explanation
If the INSERT is successful, the output will read something to this effect:
(1 row(s) affected)

Exercise: Inserting Records




Duration: 5 to 15 minutes.
In this exercise, you will practice inserting records.
  1. Insert yourself into the Employees table.
    • Include the following fields: LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, City, Region, PostalCode, Country, HomePhone, ReportsTo
  2. Insert an order for yourself in the Orders table.
    • Include the following fields: CustomerID, EmployeeID, OrderDate, RequiredDate
  3. Insert order details in the Order_Details table.
    • Include the following fields: OrderID, ProductID, UnitPrice, Quantity, Discount
Where is the solution?

UPDATE

The UPDATE statement allows you to update one or more fields for any number of records in a table. You must be very careful not to update more records than you intend to!
Syntax
UPDATE table
SET field = value,
 field = value,
 field = value
WHERE conditions;

Code Sample: InsertsUpdatesDeletes/Demos/Update.sql

-- Update an Employee

UPDATE Employees
SET FirstName = 'Nathaniel'
WHERE FirstName = 'Nat';
Code Explanation
If the UPDATE is successful, the output will read something to this effect:
(1 row(s) affected)

DELETE

The DELETE statement allows you to delete one or more records in a table. Like with UPDATE, you must be very careful not to delete more records than you intend to!
Syntax
DELETE FROM Employees
WHERE conditions;

Code Sample: InsertsUpdatesDeletes/Demos/Delete.sql

-- Delete an Employee

DELETE FROM Employees
WHERE FirstName = 'Nathaniel';
Code Explanation
If the DELETE is successful, the output will read something to this effect:
(1 row(s) affected)

Exercise: Updating and Deleting Records




Duration: 5 to 15 minutes.
In this exercise, you will practice updating and deleting records.
  1. Update your record in the Employees table to include some Notes.
  2. Raise the unit price of all products in the Products table by 10% for all products that are out of stock. This should affect 5 rows.
  3. Try to delete yourself from the Employees table. Could you?
  4. If you were not allowed to delete yourself from the Employees table, figure out what other records you have to delete so that you can.

Inserting, Updating and Deleting Records Conclusion

In this lesson of the SQL tutorial, you have learned how to insert, update, and delete records. Remember to be careful with updates and deletes. If you forget or mistype the WHERE clause, you could lose a lot of data.


Data Types

All major relational database management systems allow DBAs to specify the type of data a field can contain. The table below shows the most commonly used data types specified in ANSI SQL.
ANSI SQL Data Types
Data Type Description
Character A specific number of one-byte characters.
Character varying Up to a specific number of one-byte characters.
National character A specific number of two-byte characters.
National character varying Up to a specific number of two-byte characters.
Integer A whole number between -2^32 and +2^32 (2,147,483,648).
Smallint A whole number between -2^15 and +2^15 (32,768).
Decimal A floating-point number between -10^38+1 and 10^38+1.
Real An extremely large positive or negative floating-point number often stored using scientific notation with the potential for some relatively small loss of accuracy.
Date A date.
Time A time.
Unfortunately, different RDBMSs use different terms for these data types. The terms for SQL Server, Oracle and MySQL are shown below. You'll notice that there is some variation.
ANSI SQL Data Types
Data type SQL Server Oracle MySQL
Character char(n) (see footnote) char(n) char(n)
Character varying varchar(n) varchar(n) varchar(n)
National character nchar(n) char(n) nchar(n)
National character varying nvarchar(n) varchar(n) nvarchar(n)
Integer int int int
Smallint smallint smallint smallint
Decimal decimal(n1,n2) (see footnote) decimal(n1,n2) decimal(n1,n2)
Real real real real
Date datetime date date
Time datetime date time

Creating Tables

Now that you understand data types, you're ready to learn how to create a table. Note that you must have the proper database permissions to do so.
Syntax
CREATE TABLE table_name
(
 column_name data_type,
 column_name data_type,
 column_name data_type,
 more columns...
)

NULL Values

As we have discussed, in some cases a value may not be set for a specific field in a specific record. In this case, that field contains NULL. In the CREATE TABLE statement, the default for each column is to allow for null values. This can be stated explicitly with the NULL flag or it can be changed with the NOT NULL flag.
Syntax
CREATE TABLE table_name
(
 column_name data_type NOT NULL,
 column_name data_type NULL,
 column_name data_type NOT NULL,
 more columns...
)

Primary Keys

Every table should have a primary key, which is a field or group of fields that can be used to uniquely identify specific records in the table. Primary keys can be specified in two ways as shown below.
Syntax
CREATE TABLE table_name
(
 column_name data_type PRIMARY KEY,
 column_name data_type null,
 column_name data_type not null,
 more columns...
)
Syntax
CREATE TABLE table_name
(
 column_name_pk data_type,
 column_name data_type null,
 column_name data_type not null,
 more columns...,
 CONSTRAINT PK_table_name PRIMARY KEY (column_name_pk)
)
When a table uses a group of fields for the primary key, the key is called a composite primary key. In this case, you must specify the primary key at the end of the CREATE TABLE statement as shown below.
Syntax
CREATE TABLE table_name
(
 column_name_pk1 data_type,
 column_name_pk2 data_type,
 column_name data_type not null,
 more columns...,
 CONSTRAINT PK_table_name PRIMARY KEY (column_name_pk1,column_name_pk2)
)

Foreign Keys

Foreign keys reference the primary key of another table, thereby creating a relationship between those two tables. They can be specified using the two methods shown below.
Syntax
CREATE TABLE table_name
(
 column_name data_type PRIMARY KEY,
 column_name data_type null,
 column_name_fk data_type FOREIGN KEY REFERENCES table2_name(column_name_pk),
 more columns...
)
Syntax
CREATE TABLE table_name
(
 column_name data_type PRIMARY KEY,
 column_name data_type null,
 column_name_fk data_type,
 more columns...,
 CONSTRAINT FK_table_name FOREIGN KEY REFERENCES table2_name(column_name_pk)
)
The code samples below show how to create duplicates of the Employees, Customers and Orders tables.

Code Sample: Tables/Demos/CreateTable.sql

CREATE TABLE Employees2
(
 EmployeeID int NOT NULL PRIMARY KEY,
 LastName nvarchar(20) NOT NULL,
 FirstName nvarchar(10) NOT NULL,
 Title nvarchar(30) NULL,
 TitleOfCourtesy nvarchar(25) NULL,
 BirthDate datetime NULL,
 HireDate datetime NULL,
 Address nvarchar(60) NULL,
 City nvarchar(15) NULL,
 Region nvarchar(15) NULL,
 PostalCode nvarchar(10) NULL,
 Country nvarchar(15) NULL,
 HomePhone nvarchar(24) NULL,
 Extension nvarchar(4) NULL,
 ReportsTo int FOREIGN KEY REFERENCES Employees(EmployeeID)
)

CREATE TABLE Customers2
(
 CustomerID nchar(5) PRIMARY KEY,
 CompanyName nvarchar(40) NOT NULL,
 ContactName nvarchar(30) NULL,
 ContactTitle nvarchar(30) NULL,
 Address nvarchar(60) NULL,
 City nvarchar(15) NULL,
 Region nvarchar(15) NULL,
 PostalCode nvarchar(10) NULL,
 Country nvarchar(15) NULL,
 Phone nvarchar(24) NULL,
 Fax nvarchar(24) NULL
)

CREATE TABLE Orders2
(
 OrderID int PRIMARY KEY,
 CustomerID nchar(5) FOREIGN KEY REFERENCES Customers2(CustomerID),
 EmployeeID int FOREIGN KEY REFERENCES Employees2(EmployeeID),
 OrderDate datetime NULL,
 RequiredDate datetime NULL,
 ShippedDate datetime NULL,
 ShipVia int NULL,
 Freight decimal(8,2) NULL
)
Code Explanation
Notice that all three tables have primary keys and that the Orders2 table has two foreign keys referencing the other two tables.
This demo uses SQL Server data types.

Exercise: Creating Tables




Duration: 30 to 40 minutes.
In this exercise you will create duplicates of the Products, Categories and Suppliers tables in the Northwind database.
  1. Open a new query window and write the three CREATE TABLE queries to create Products2, Categories2 and Suppliers2. Note that order matters and the order shown below is not correct. Set data types as you see appropriate.
    • Products2 with the following fields:
      • ProductID
      • ProductName
      • SupplierID
      • CategoryID
      • QuantityPerUnit
      • UnitPrice
      • UnitsInStock
      • UnitsOnOrder
      • ReorderLevel
    • Categories2 with the following fields:
      • CategoryID
      • CategoryName
    • Suppliers2 with the following fields:
      • SupplierID
      • CompanyName
      • ContactName
      • ContactTitle
      • Address
      • City
      • Region
      • PostalCode
      • Country
      • Phone
      • Fax
  2. Run the query to see if it executes. You can test it by select rows from your new tables. Of course, they won't have any, but you shouldn't get an error.
Where is the solution?

Adding and Dropping Columns

The syntax for adding a column to a table is shown below:
Syntax
ALTER TABLE table_name
ADD column_name data_type
Note that you will either have to allow for NULL values or set a default value for the new column.
The syntax for dropping a column to a table is shown below:
Syntax
ALTER TABLE table_name
DROP COLUMN column_name
Note that you cannot drop primary key columns.

Renaming Tables

The syntax for renaming tables is different in different RDBMSs.

SQL Server

SQL Server uses a built-in stored procedure for renaming tables:
Syntax
sp_rename table_name, new_table_name

Oracle and MySQL

Oracle and MySQL use the RENAME command for renaming tables:
Syntax
RENAME table_name TO new_table_name

Dropping Tables

Dropping tables is scarily easy; however, there are data integrity checks to make sure you don't drop tables that are referenced by other tables.
Syntax
DROP TABLE table_name

Creating and Modifying Tables Conclusion

In this lesson of the SQL tutorial, you have learned how to create and modify table structures, how to set data types and how to create relationships between tables using primary and foreign keys.

Footnotes

  1. n = number of characters
  2. n1 = total number of digits; n2 = number of digits allowed after the decimal point

Creating Views

To create views, simply write your SELECT query and wrap it in a CREATE VIEW statement as shown below.
Syntax
CREATE VIEW view_name AS
SELECT statement goes here...
Here is an example showing how to create a view from a complicated query we've seen earlier.

Code Sample: Views/Demos/CreateView.sql

--create view showing detailed order information
CREATE VIEW vwOrderInfo AS
SELECT o.OrderID, o.OrderDate, o.RequiredDate, o.ShippedDate, c.CompanyName, e.FirstName, e.LastName
FROM Orders o
 JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
 JOIN Customers c ON (c.CustomerID = o.CustomerID);

--Select all records from view that were shipped after the required date
SELECT *
FROM vwOrderInfo
WHERE RequiredDate < ShippedDate
Code Explanation
You can see that the process of creating the view is simple. You should also notice how easy it is to get information on specific orders that includes relevant data from the Employees and Customers table.
Note that the ORDER BY clause is usually not allowed in views.

Dropping Views

Don't like your view? Dropping it is easy enough:
Syntax
DROP VIEW view_name

Benefits of Views

Views have the following benefits:
  • Security - Views can be made accessible to users while the underlying tables are not directly accessible. This allows the DBA to give users only the data they need, while protecting other data in the same table.
  • Simplicity - Views can be used to hide and reuse complex queries.
  • Column Name Simplication or Clarification - Views can be used to provide aliases on column names to make them more memorable and/or meaningful.
  • Stepping Stone - Views can provide a stepping stone in a "multi-level" query. For example, you could create a view of a query that counted the number of sales each salesperson had made. You could then query that view to group the sales people by the number of sales they had made.

Exercise: Creating a View




Duration: 15 to 25 minutes.
In this exercise you will create a view showing the number of sales by salesperson.
  1. Open a new query window and create a view that gets the following data:
    • Employee first name, last name and id
    • Number of orders that employee has made (as NumOrders)
  2. Write a select statement that shows how many employees have made different numbers of orders rounded to the nearest 10. Your results should look like this:

Views Conclusion

In this lesson of the SQL tutorial, you learned the purpose and benefits of views and how to create and drop them.

Stored procedures are essentially functions that you can create in the database and reuse. What's neat about them from a usability standpoint is that they can take input parameters and then return a result.

Creating Stored Procedures

Stored procedures are created with the CREATE PROCEDURE statement. The syntax is shown below.
Syntax
CREATE PROCEDURE procedure_name
@param data_type = default_value,
@param data_type = default_value,
@param data_type = default_value
AS
-- statements for procedure here
The demo below shows a script that creates a simplified version of a SalesByCategory stored procedure that ships with SQL Server 2000's Northwind sample database.

Code Sample: StoredProcedures/Demos/StoredProc.sql

CREATE PROCEDURE SalesByCategory
 @CategoryName nvarchar(15),
 @OrdYear int = 1998
AS

SELECT ProductName, SUM(OD.Quantity * (1-OD.Discount) * OD.UnitPrice) AS TotalPurchase
FROM "Order Details" od, Orders o, Products p, Categories c
WHERE od.OrderID = o.OrderID
 AND od.ProductID = p.ProductID
 AND p.CategoryID = c.CategoryID
 AND c.CategoryName = @CategoryName
 AND DATEPART(year,OrderDate) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName

--Call stored procedure
EXEC SalesByCategory 'Seafood', 1997
Code Explanation
The procedure has two parameters defined @CategoryName, which is required, and @OrdYear, which has a default value of 1998.
The stored procedure can be called with the EXEC command:
EXEC SalesByCategory 'Seafood', 1997.
The stored procedure shown in the previous demo returns a result set; however, stored procedures can also be used to insert, update, or delete data. Using conditional processing, it is possible to check parameters to make sure the input parameters are valid. It is also possible to run multiple queries and return multiple results with a single stored procedure. That advanced material is beyond the scope of this course. But even the simple stored procedure shown here is valuable as it makes it easy to get a sales report for a specific category of goods without writing a full SELECT query.

Dropping Stored Procedures

Dropping stored procedures is easy enough:
Syntax
DROP PROCEDURE stored_procedure_name

Exercise: Creating a Stored Procedure




Duration: 20 to 30 minutes.
In this exercise you will create a stored procedure that returns a sales report for a given time period for a given employee.
  1. Open a new query window and create a spSalesReport stored procedure that returns first name, last name, number of orders (NumOrders) and revenue for a given employee for a given time period. The stored procedure should take four required input parameters:
    1. @StartOrderDate
    2. @EndOrderDate
    3. @SpFirstName
    4. @SpLastName
  2. Test the stored procedure by running: 
    EXEC spSalesReport '11/1/1997','12/1/1997', 'Steven', 'Buchanan'
Create a second stored procedure called spSalesReport2 that is the same except that the @SpFirstName and @SpLastName parameters default to empty strings. When called like:
EXEC spSalesReport2 '11/1/1997','12/1/1997', 'Steve'
... it should return the results for Steven Buchanan.
When called with no parameters, it should return result for all the salespeople.
Where is the solution?

Benefits of Stored Procedures

Stored procedures have many benefits, including:
  • Speed - Stored procedures are pre-compiled, so the execution plan doesn't have to be figured out each time they're called. This can result in a significant performance improvement.
  • Code reuse and abstraction - Stored procedures often involve complex code. It's nice that this code doesn't have to be rewritten over and over again and that even entry-level SQL programmers can make use of it with a simple stored procedure call.
  • Security - Permissions can be granted for stored procedures while being restricted for the underlying tables. This allows the DBA to provide a method for SQL programmers and report writers to access and/or manipulate data in a safe way.
  • Reduced traffic between client and server - Since the bulk of the query is already stored on the server and only the relatively short stored procedure call has to get sent, traffic to the server is decreased.

Stored Procedures Conclusion

In this lesson of the SQL tutorial, you learned the purpose and benefits of stored procedures and how to create, use and drop them.

Relational Database Design

Most popular database management systems are relational systems, and are usually referred to as Relational Database Management Systems (RDBMS). What this means is that their databases can contain multiple tables, some (or all) of which are related to each other.
For example, consider the following screenshot:
Multiple database tables
In this example, the database has 20 tables. Each table serves a specific purpose. This enables us to organize our data much better. It can also help us with the integrity of our data.
Using the example above, the Individual table can hold data that is strictly about the individual. The City table can hold a list of all cities. If we want to know which city each individual lives, we could store a "pointer" in the Individual table to that city in the City table.
MS Access Relationships
The above example demonstrates the relationship between the Individual table and the City table. The individuals in the "Individual" table live in cities that are defined in the "City" table. Therefore, we can cross-reference each "Individual" record with a "City" record.

How Does This Work?

Firstly, in the City table, each record has a unique identifier. A unique identifier is a value that is unique to each record. This identifier can be as simple as an incrementing number. So, in our City table, the first record has a number of 1, the second record has a number of 2, and so on.
Secondly, when entering each individual into the Individual table, instead of writing out the full city name in that table, we only need to add the city's unique identifier. In this case, the unique identifier is a number, so we enter this number into the "CityId" column of the "Individual" table.
The following screenshots demonstrate this:
Relational Databases Example 2

Relational Databases Example 3
So, by looking at both tables, we can determine that Homer lives in Sydney, Barney lives in Cairns, and both Ozzy and Fred live in Osaka. At this stage, nobody in our database lives in Queenstown or Dunedin.

Primary Keys and Foreign Keys

Primary keys and foreign keys are terms that you will become very familiar with when designing databases. These terms describe what role each of the columns play in their relationship with each other.
The column that contains the unique identifier is referred to as the Primary Key. So, in our City table, the primary key is the CityId column.
A foreign key is the column in the other table that points to the primary key. Therefore, the CityId column in the Individual table is a foreign key to the CityId column in the City table.

About SQL

SQL stands for Structured Query Language.
SQL is a language that enables you to work with a database. Using SQL, you can insert records, update records, and delete records. You can also create new database objects such as databases and tables. And you can drop (delete) them.
More advanced features include creating stored procedures (self contained scripts), views (pre-made queries), and setting permissions on database objects (such as tables, stored procedures, and views).
Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of SQL. Different database vendors have their own variations of the language.
Having said this, to be in compliance with the ANSI standard, they need to at least support the major commands such as DELETE, INSERT, UPDATE, WHERE etc. Also, you will find that many vendors have their own extensions to the language - features that are only supported in their database system.
Furthermore, transact-SQL is an extension to the ANSI standard and provides extra functionality.

Using SQL

If you need to build a website with a database providing the content, you will generally need the following:
  • A server side scripting language (i.e. ColdFusion, PHP, ASP/.NET)
  • A database query language (i.e. SQL)
  • A client side markup language and style sheets (i.e. HTML/CSS)
Although SQL can be quite involved, you can achieve a lot with a handful of SQL statements. When using SQL on a website, you will often find yourself either selecting a record, inserting a record, updating a record, or deleting a record. Fortunately, SQL has commands for performing each of these actions.
The next lesson covers the SQL syntax basics.
The SQL syntax is quite an easy one to grasp. Most of the actions you need to perform are done with a SQL statement.
Generally, a SQL statement begins by stating what to do (for example, "SELECT"), then states which object to do it to (for example, using the "FROM" clause).
SELECT * FROM Individual
It may also have a condition added to the end (for example, with a WHERE clause).
SELECT * FROM Individual
WHERE FirstName = 'Homer'
SQL is not case sensitive - the above examples could just have easily used all lowercase or all uppercase. Different programmers have their own preferences. For readability purposes, many SQL programmers prefer to use uppercase for SQL commands and lowercase for everything else.
The SQL syntax allows you to include line breaks at logical points without it breaking the statement. For example, the above example could have been written all on one line - or across 4 lines.
Also, some database systems require that you use a semicolon at the end of each SQL statement (although this tutorial doesn't use the semicolon).

DML & DDL

SQL is divided into two main categories; Data Manipulation Language (DML), and Data Definition Language (DDL). An explanation follows.

Data Manipulation Language (DML)

DML enables you to work with the data that goes into the database. DML is used to insert, select, update, and delete records in the database. Many of your SQL statements will begin with one of the following commands:
  • SELECT - Retrieves data from the database
  • INSERT - Inserts new data into the database
  • UPDATE - Updates existing data in the database
  • DELETE - Deletes existing data from the database

Data Definition Language (DDL)

You may also occasionally need to create or drop a table or other datbase object. SQL enables you to do this programatically using DDL.
Examples of DDL commands:
  • CREATE DATABASE - Creates a new database
  • ALTER DATABASE - Modifies the database
  • DROP DATABASE - Drops (deletes) a database
  • CREATE TABLE - Creates a new table
  • ALTER TABLE - Modifies the table
  • DROP TABLE - Drops (deletes) a table
These are just some of the object classes that can be defined using DDL. As you can probably guess, the syntax is generally the same for any object, although, each object will have properties specific to that object class.
As you can see, the SQL syntax is quite simple. It is also very powerful syntax - you can do a lot of damage with one line of code!
The SELECT statement is probably the most commonly used in SQL. It simply retrieves data from the database.
Lets have a look at a simple SELECT statement:
SELECT * FROM Individual
This SQL SELECT statement is attempting to retrieve all columns from a table called Individual.
How do we know it is trying to select all columns? Because it is using an asterisk (*). This is a quick way of selecting all columns - it's much easier than writing out the names of all columns (especially if there are a lot of columns).
Of course, this SQL SELECT statement assumes that there is a table called Individual. If there wasn't, an error would be generated.
Lets have a look at the table the statement is trying to select data from:
IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike
Because our select statement asks to display all columns and all records, we would see the following:
IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike

Select from Multiple Tables

You can select from more than one table at a time. To do this, simply separate each table with a comma. You should also qualify any references to columns by placing the table name in front, separated by a dot.
We have another table called Occupation, which contains the individual's occupation.
OccupationId IndividualId JobTitle
1 1 Engineer
2 2 Accountant
3 3 Cleaner
4 4 Attorney
5 5 Sales Executive

SQL statement

We will select from both the Individual table and the Occupation table. We will qualify any column names by prefixing them with its table's name and a dot.
SELECT * FROM Individual, Occupation
WHERE Individual.FirstName = 'Homer'

Result

IndividualId FirstName LastName UserName OccupationId IndividualId JobTitle
1 Fred Flinstone freddo 1 1 Engineer
2 Homer Simpson homey 2 2 Accountant
3 Homer Brown notsofamous 3 3 Cleaner
4 Ozzy Ozzbourne sabbath 4 4 Attorney
5 Homer Gain noplacelike 5 5 Sales Executive

Displaying Less Columns

If you don't need every column to be displayed you can single out just the columns you're interested in. It's good programming practice to do this - the more columns your program has to return, the more it will impact its performance.
To only display those columns you're interested in, simply replace the asterisk (*) with a comma separated list of the column names.

SQL statement

SELECT IndividualId, LastName, UserName FROM Individual
WHERE FirstName = 'Homer'

Result

IndividualId LastName UserName
2 Simpson homey
3 Brown notsofamous
5 Gain noplacelike


SQL Where



In the previous lesson, we used a SQL SELECT statement to retrieve all records from a database table. This is fine if we want to see every record, but what if we were only interested in some records? For example, what if we were only interested in individuals whose first name is "Homer"?
We could use the WHERE clause.
Using the WHERE clause, you can filter out only those records that satisfy a given condition.

SQL WHERE Syntax

SELECT * FROM table_name
WHERE column_name = 'criteria'

Example

SQL WHERE Statement

SELECT * FROM Individual
WHERE FirstName = 'Homer'

Source Table

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike

Result

Given there are 3 people with the first name of "Homer", the results will look like this:
IndividualId FirstName LastName UserName
2 Homer Simpson homey
3 Homer Brown notsofamous
5 Homer Gain noplacelike

Multiple Conditions

You can filter records based on more than one condition using operators. Two common operators are the AND and OR operators.

AND Operator

The AND operator filters the query to only those records that satisfy both the first condition and the second condition.
SELECT * FROM Individual
WHERE FirstName = 'Homer'
AND LastName = 'Brown'

Result

IndividualId FirstName LastName UserName
3 Homer Brown notsofamous

OR Operator

The OR operator filters the query to only those records that satisfy either one or the other condition.
SELECT * FROM Individual
WHERE FirstName = 'Homer'
OR LastName = 'Ozzbourne'

Result

IndividualId FirstName LastName UserName
2 Homer Simpson homey
3 Homer Brown notsofamous
5 Homer Gain noplacelike
4 Ozzy Ozzbourne sabbath


SQL Order By


Using a SQL SELECT statement can retreive many hundreds or even thousands of records. In some cases you might find it useful to sort the records by a given column. For example, when selecting records from the Individual table, you might like to sort them by the LastName column.

SQL statement

SELECT * FROM Individual
ORDER BY LastName

Source Table

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike

Result

IndividualId FirstName LastName UserName
3 Homer Brown notsofamous
1 Fred Flinstone freddo
5 Homer Gain noplacelike
4 Ozzy Ozzbourne sabbath
2 Homer Simpson homey

Descending Order

By default, ORDER BY sorts the column in ascending order - that is, from lowest values to highest values. You could also explicitly state this using the ASC keyword, but it's not necessary.
If you want highest values to appear first, you can use the DESC keyword.

SQL statement

SELECT * FROM Individual
ORDER BY LastName DESC

Result

IndividualId FirstName LastName UserName
2 Homer Simpson homey
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike
1 Fred Flinstone freddo
3 Homer Brown notsofamous

Sorting By Multiple Columns

You can sort by multiple columns by stating each column in the ORDER BY clause, separating each column name with a comma. SQL will first order the results by the first column, then the second, and so on for as many columns that are included in the ORDER BY clause.

SQL statement

SELECT * FROM Individual
ORDER BY FirstName, LastName

Result

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
3 Homer Brown notsofamous
5 Homer Gain noplacelike
2 Homer Simpson homey
4 Ozzy Ozzbourne sabbath

SQL Top


In the preceeding lessons on the SELECT statement, the examples have returned all records that have matched our SELECT criteria. This is great if you want to look at every record, but, what if you only want to look at the first few records?
Sounds like you need the SQL TOP clause.
The TOP clause allows us to specify how many rows to return. This can be useful on very large tables when there are thousands of records. Returning thousands of records can impact on performance, and if you are working with a production database, this could have an adverse impact on the users.
Note: The SQL TOP clause is Transact-SQL, and not part of ANSI SQL. Therefore, depending on your database system, you may not be able to use this clause.

SQL statement

SELECT TOP 3 * FROM Individual

Source Table

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike

Result

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous

Specifying a Percentage

You have the option of specifying a percentage of the result set instead of an absolute value. You do this with the PERCENT keyword.

SQL statement

SELECT TOP 40 PERCENT * FROM Individual

Result

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey

SQL TOP and the ORDER BY clause

If you are using the TOP clause along with the ORDER BY clause, the TOP clause is applied to the ordered result set.
Therefore, if we add an ORDER BY to the above query, we end up with something like this:

SQL statement

SELECT TOP 40 PERCENT * FROM Individual
ORDER BY LastName DESC

Result

IndividualId FirstName LastName UserName
2 Homer Simpson homey
4 Ozzy Ozzbourne sabbath

SQL Distinct



Once a table starts getting a lot of data in it, some columns will contain duplicate values. For example, many Individuals share first names and surnames. Most of the time this isn't a problem. But sometimes you will want to find out how many unique values there are in a table. To do this you can use the DISTINCT keyword.

SQL statement

SELECT DISTINCT(FirstName) FROM Individual

Source Table

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike

Result

Using the DISTINCT keyword, all customers with a name of "Homer" are counted as one.
FirstName
Fred
Homer
Ozzy

SQL In

The SQL IN operator assists you in providing multiple values in your WHERE clause. This can provide very useful when you need to compare your value to a list of values. Often this list could be the result of a query from another table.

SQL Syntax

SELECT * FROM table_name
WHERE column_name IN (value1,value2,...)

Example

SQL statement

SELECT * FROM Individual
WHERE LastName IN ('Simpson','Ozzbourne','Flinstone')

Source Table

Id FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike

Result

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
4 Ozzy Ozzbourne sabbath
You might have noticed that this returns the same result as the following SQL statement:
SELECT * FROM Individual
WHERE LastName = 'Simpson'
OR LastName = 'Ozzbourne'
OR LastName = 'Flinstone'
Yes, we could just have easily used that statement but the SQL IN statement is more concise.

SQL IN and Subqueries

Now, where the SQL IN operator becomes really useful is when you need to compare a value against the result of another query.
For example, lets say we have another table called "Publisher". This table contains users who are allowed to contribute to the website via an administration console. All users in the Publisher table are also in the Individual table, but not all users in the Individual table are in the Publisher table.

Source Tables

Individual Table
IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike
Publisher Table
IndividualId AccessLevel
1 Administrator
2 Contributor
3 Contributor
4 Contributor
Our task is to return a list of usernames from all publishers who have an access level of "Contributor".
You may notice that the usernames are in the Individual table but the access level is stored in the Publisher table. Also, there could potentially be many contributors. This is a good candidate for the SQL IN operator!

SQL statement

SELECT UserName FROM Individual
WHERE IndividualId IN
(SELECT IndividualId
FROM Publisher
WHERE AccessLevel = 'Contributor')

Result

UserName
homey
notsofamous
sabbath
In this example there aren't many records in the Publisher table, but imagine if there were thousands - the IN statement is great for this sort of thing.

SQL Alias

In SQL, an alias is a name that you give a table. This can make it easier to work with table names - especially when they are long. You could name the alias anything, but usually you'd make it short.
You may be thinking "a table already has a name, why give it another one?". Well, there are some good reasons for creating an alias. The main reasons are:
  • Queries can sometimes get very long. Aliases can make your query easier to read.
  • You may find yourself referencing the same table name over and over again - this will occur if you're working with multiple tables and you need to refer to columns from those tables. It can be annoying to have to write the whole name all the time - especially if it's a long one.
  • You may need to work with multiple instances of the same table, for example, a self join. If you're not familiar with joins, they are covered later in this tutorial.
As mentioned, an alias could be anything. For example, if you have a table called Individual you could give it an alias of i. Another table called IndividualProductPurchase could have an alias of, say, ipp

Alias Syntax

SELECT * FROM table_name AS alias_name

Example SQL Statement

SELECT o.JobTitle FROM Individual AS i, Occupation AS o
WHERE i.FirstName = 'Homer'
ORDER BY o.JobTitle

SQL Join

The SQL JOIN refers to using the JOIN keyword in a SQL statement in order to query data from two tables.
When you perform a SQL join, you specify one column from each table to join on. These two columns contain data that is shared across both tables.
You can use multiple joins in the same SQL statement to query data from as many tables as you like.

Join Types

Depending on your requirements, you can do an "inner" join or an "outer" join. These are different in a subtle way
  • INNER JOIN: This will only return rows when there is at least one row in both tables that match the join condition.
  • LEFT OUTER JOIN (or LEFT JOIN): This will return rows that have data in the left table (left of the JOIN keyword), even if there's no matching rows in the right table.
  • RIGHT OUTER JOIN (or RIGHT JOIN): This will return rows that have data in the right table (right of the JOIN keyword), even if there's no matching rows in the left table.
  • FULL OUTER JOIN (or FULL JOIN): This will return all rows, as long as there's matching data in one of the tables.

Join Syntax

Inner Join: 
SELECT * FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name
Left Join: 
SELECT * FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name = table_name2.column_name
Right Join: 
SELECT * FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name = table_name2.column_name
Full Join: 
SELECT * FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name

Example Inner Join Statement

SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
Note: We could use table aliases instead of the full table name. This will keep our statement shorter. For example:
SELECT * FROM Individual AS Ind
INNER JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId

SQL Inner Join

As discussed in the previous lesson, you should use the SQL INNER JOIN when you only want to return records where there is at least one row in both tables that match the join condition.

Example SQL statement

SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
WHERE Individual.IndividualId = '2'

Source Tables

Left Table
Id FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike
Right Table
IndividualId AccessLevel
1 Administrator
2 Contributor
3 Contributor
4 Contributor
10 Administrator

Result

IndividualId FirstName LastName UserName IndividualId AccessLevel
2 Homer Simpson homey 2 Contributor

SQL Outer Join

This lesson covers both the left outer join, the right outer join, and the full outer join, and explains the differences between them. There are some occasions where you would need to use a left outer join or a right outer join, and others where you would need a full outer join. The join type you use will depend on the situation and what data you need to return.

Left Outer Join

Use this when you only want to return rows that have matching data in the left table, even if there's no matching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS Ind
LEFT JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table
Id FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike
Right Table
IndividualId AccessLevel
1 Administrator
2 Contributor
3 Contributor
4 Contributor
10 Administrator

Result

IndividualId FirstName LastName UserName IndividualId AccessLevel
1 Fred Flinstone freddo 1 Administrator
2 Homer Simpson homey 2 Contributor
3 Homer Brown notsofamous 3 Contributor
4 Ozzy Osbourne sabbath 4 Contributor
5 Homer Gain noplacelike NULL NULL

Right Outer Join

Use this when you only want to return rows that have matching data in the right table, even if there's no matching rows in the left table.

Example SQL statement

SELECT * FROM Individual AS Ind
RIGHT JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table
Id FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike
Right Table
IndividualId AccessLevel
1 Administrator
2 Contributor
3 Contributor
4 Contributor
10 Administrator

Result

IndividualId FirstName LastName UserName IndividualId AccessLevel
1 Fred Flinstone freddo 1 Administrator
2 Homer Simpson homey 2 Contributor
3 Homer Brown notsofamous 3 Contributor
4 Ozzy Osbourne sabbath 4 Contributor
NULL NULL NULL NULL 10 Administrator

Full Outer Join

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

Example SQL statement

SELECT * FROM Individual AS Ind
FULL JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table
Id FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike
Right Table
IndividualId AccessLevel
1 Administrator
2 Contributor
3 Contributor
4 Contributor
10 Administrator

Result

IndividualId FirstName LastName UserName IndividualId AccessLevel
1 Fred Flinstone freddo 1 Administrator
2 Homer Simpson homey 2 Contributor
3 Homer Brown notsofamous 3 Contributor
4 Ozzy Osbourne sabbath 4 Contributor
5 Homer Gain noplacelike NULL NULL
NULL NULL NULL NULL 10 Administrator

SQL Insert

Up until now, this tutorial has covered the SELECT statement and variations on it. We are now about to learn a new statement - the INSERT statement.
The SQL INSERT command allows you to insert a record into a table in your database. As with the SELECT syntax, the INSERT syntax is quite straight forward.

SQL statement

INSERT INTO Individual
VALUES ( '6', 'Benny', 'Hill', 'hillbenny' )

Source Table

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike

Result

Now if we do a SELECT on the Individual table, we can see the new record added to the bottom of the result set.
IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike
6 Benny Hill hillbenny
See - nothing to it!
Now, it's important to note that the INSERT statement is used only when you want to add a new record to the table. If you want to update an existing record, use an UPDATE statement. The UPDATE command is described in the next lesson

SQL Update

The SQL UPDATE statement allows you to update an existing record in the database.
The UPDATE command uses a WHERE clause. If you don't use a WHERE clause, all rows will be updated. In fact, the syntax for a basic UPDATE statement is very similar to a SELECT statement.

SQL statement

UPDATE Individual
SET UserName = 'funnyman'
WHERE IndividualId = '6'

Source Table

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike
6 Benny Hill hillbenny

Result

Now if we select this record, we can see the updated value.
IndividualId FirstName LastName UserName
6 Benny Hill funnyman

Updating Multiple Fields

To update multiple fields, separate each field assignment with a comma.

SQL statement

UPDATE Individual
SET UserName = 'getserious', FirstName = 'Onetree'
WHERE IndividualId = '6'

Result

IndividualId FirstName LastName UserName
6 Onetree Hill getserious

SQL Delete

The SQL DELETE statement allows you to delete a record from the database.
The DELETE command uses a WHERE clause. If you don't use a WHERE clause, all rows in the table will be deleted. Again, as with the UPDATE statement, the syntax for a basic DELETE statement is similar to a SELECT statement.

SQL statement

DELETE
FROM Individual
WHERE IndividualId = '6'

Source Table

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike
6 Benny Hill funnyman

Result

Now if we select all records from the table, we see that record 6 has been deleted.
IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplaceliike

SQL Functions

SQL has a number of functions to assist you in your database programming.
Functions are a self contained script/program built for a specific purpose. Generally, the value returned by a function will depend on the context in which it is being used. Often, a SQL function will be used within a query and this is what provides it with it's context.
Transact-SQL provides 3 different types of functions:
Rowset Functions These return an object that can be used in place of a table reference in a SQL statement
Aggregate Functions
Perform a calculation on a set of values and return a single value. Aggregate functions can be used in the following:
  • The select list of a SELECT statement
  • A COMPUTE or COMPUTE BY clause
  • A HAVING clause
Scalar Functions
These return a single value from a single value. Scalar functions are categorized as follows:
  • Configuration Functions
  • Cursor Functions
  • Date and Time Functions
  • Mathematical Functions
  • Metadata Functions
  • Security Functions
  • String Functions
  • System Functions
  • System Statistical Functions
  • Text and Image Functions
On top of these functions, different database vendors have their own built-in functions for their products. Also, most products enable programmers to program their own User Defined Functions. For information on proprietary functions, you should consult the vendor's documentation.
In the next lesson you will learn about one of the more commonly used SQL functions - the COUNT function.

SQL Count

A commonly used aggregate function in SQL is COUNT. COUNT returns the number of rows that match the given criteria.

COUNT(*)

If we only want to see how many records are in a table (but not actually view those records), we could use COUNT(*). COUNT(*) returns everything - including null values and duplicates.

SQL statement

SELECT COUNT(*) FROM Individual

Source Table

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike
6 Bono
u2

Result

6

COUNT(column name)

If we want to see how many non-null values are in a given column, we use COUNT(column name) where column name is the name of the column we want to test.

SQL statement

SELECT COUNT(LastName) FROM Individual

Source Table

Id FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike
6 Bono
u2

Result

5

Combining COUNT & DISTINCT

If we only want to see how many unique names are in the table, we could nest the DISTINCT inside a COUNT function.

SQL statement

SELECT COUNT(DISTINCT(FirstName)) FROM Individual

SQL Create

Most database systems provide a way for you to create database objects via a WYSIWYG interface. For example, Microsoft's SQL Server has Enterprise Manager. The Enterprise Manager gives you a kind of graphical representation of your database system. You can browse through your databases, view the tables and their contents etc.
Despite having a tool like Enterprise Manager to make these tasks easier, there can be good reasons for wanting to perform some of these tasks programatically. Possible reasons could include:
  • Your application allows users to create objects on the fly.
  • You have multiple environments (for example development, staging, production). It's much easier, and less error prone, to run a script against 3 environments than to open up Enterprise Manager and repeat the steps in 3 environments - especially if you have lots of changes.
  • You don't have access to Enterprise Manager (or your database system's equivalent).
Fortunately, SQL has a number of CREATE commands that enable you to programatically create database objects including the database, its tables and more.
Here are the CREATE commands supported by SQL Server:
  • CREATE ACTION
  • CREATE CACHE
  • CREATE CELL CALCULATION
  • CREATE CUBE
  • CREATE DATABASE
  • CREATE DEFAULT
  • CREATE FUNCTION
  • CREATE INDEX
  • CREATE MEMBER
  • CREATE MINING MODEL
  • CREATE PROCEDURE
  • CREATE RULE
  • CREATE SCHEMA
  • CREATE SET
  • CREATE STATISTICS
  • CREATE TABLE
  • CREATE TRIGGER
  • CREATE UNIQUE CLUSTERED INDEX
  • CREATE VIEW
We're not going to cover all these here but, over the next few lessons, we'll cover some of the most common CREATE commands

SQL Create Database

You can create a database using the CREATE DATABASE command.

SQL syntax

CREATE DATABASE database_name

Example Code

This statement creates a database called "Payroll". Because no arguments have been specified, the database data files and transaction logs will be created automatically in the default location.
CREATE DATABASE Payroll

Adding Arguments

There are a number of optional arguments that you can supply with the CREATE DATABASE command. You should check your database system's documentation for the specific arguments supported and their usage, but here's an example of supplying arguments when creating a database using Microsoft's SQL Server.

Example Code

In this example, we are supplying the name and location of the database's data file and transaction log. We are also specifying the initial size of these files (with the SIZE argument), the maximum size it can grow to (with the MAXSIZE argument) and the growth increment of each file (using the FILEGROWTH) argument. 
USE master
GO
CREATE DATABASE Payroll
ON
( NAME = Payroll_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\payrolldat.mdf',
   SIZE = 20MB,
   MAXSIZE = 70MB,
   FILEGROWTH = 5MB )
LOG ON
( NAME = 'Payroll_log',
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\payroll.ldf',
   SIZE = 10MB,
   MAXSIZE = 40MB,
   FILEGROWTH = 5MB )
GO

SQL Create Table

You create a table using the CREATE TABLE command.

SQL syntax

CREATE TABLE table_name
(column_name_1 datatype,
column_name_2 datatype,
...
)

Example

CREATE TABLE Individual
(IndividualId int,
FirstName Varchar(255),
LastName Varchar(255),
UserName Char(10)
)

Result

This results in an empty table. You can now use an INSERT statement to add data to the table.
IndividualId FirstName LastName UserName




Data Types

You'll notice we explicitly stated the data type in our CREATE TABLE statement. This is because, when you create a column, you need to tell the database what type of data it can hold.
The exact data types and how their expressed differs with each database system and vendor, but you'll find that generally, there will be support for fixed length strings (eg char), variable length strings (eg varchar), date/time values (eg datetime), numbers and integers (eg, bigint, int, smallint, tinyint, numeric).
The following base data types are available in SQL Server 2000.
bigint Binary bit char cursor
datetime Decimal float image int
money Nchar ntext nvarchar real
smalldatetime Smallint smallmoney text timestamp
tinyint Varbinary Varchar uniqueidentifier
You may need to consult your database system's documentation if you're unsure of which data type to use or how it is expressed in that system.

SQL Create Index

Indexes can be created against a table to make searches more efficient. A database index is similar to an index of a book - a book index allows you to find information without having to read through the whole book. A database index enables the database application to find data quickly without having to scan the whole table.
Indexes can have a slight impact on performance so you should only create indexes against tables and columns that will be frequently searched against. For example, if users of your application often search against the LastName field then that field is a great candidate for an index.
You create an index using the CREATE INDEX command.

SQL syntax

CREATE INDEX index_name
ON table_name (column_name)

Example

CREATE INDEX IndividualIndex
ON Individual (LastName)

SQL Alter Table

In an earlier lesson, we created a table with the CREATE TABLE command. In this lesson, we will modify the table using the ALTER TABLE command.

Add a Column

SQL syntax

ALTER TABLE table_name
ADD column_name datatype

Example SQL Statement

ALTER TABLE Individual
ADD age int

Change the Datatype

SQL syntax

ALTER TABLE table_name
ALTER COLUMN column_name datatype

Example SQL Statement

ALTER TABLE Individual
ALTER COLUMN age numeric

Drop a Column

'Dropping' a column means removing or deleting that column.

SQL syntax

ALTER TABLE table_name
DROP COLUMN column_name

Example SQL Statement

ALTER TABLE Individual
DROP COLUMN age

Relational Database tutorial

The experiments database
Datatabase are used to store data in a structured way. Let's explain the basics with a small example.

1. The Data

Assume I want to store the results of experiments performed at the lab. I group the data in 4 tables. Each row contains the information about 1 specific item or entity,  the columns represent the information elements.
experiments
ID 
name
creation
who 
p 
comment
1
my first parallel program
22/2/2002 
jan
4
just a test
2
parallel genetic algorithm
22/3/2002 
jan
8

3
parallel curve measurements
25/3/2002 
erik
8
speedup curve 
p is the number of processors of the parallel experiment. The who column is a person selected from the users table.
users
name 
function 
jan
assistant
johan
assistant
erik
professor
andy student
variables
name
isParallel 
explanation
Ts
FALSE
sequential processing time
Tp
TRUE
parallel processing time
Tpart
TRUE
partitioning time
Tsync
TRUE
synchronisation time
Tcomm 
TRUE
communication time
these are all variables that are measured in an experiment.
measurements
experimentID
variable
value
1
Ts
101.5
1
Tp
51.2
1
Tpart
5.3
1
Tsync
10.2
1
Tcomm
12.2
2
Ts
12356.5
2
Tp
8562.6
2
Tpart
256
2
Tsync
5023.3
2
Tcomm
2036.6
3
Ts
2305.6
3
Tp
805.6
3
Tpart
56.2
3
Tsync
12.6
3
Tcomm
98.5
finally, for each experiment, all variables are measured and stored in the database.
 
 

2. Entity-Relationship Diagram

The definition of the database is represented by the ERD:
For each table we list the columns or attributes. The attributes annotated with a * represent the primary key of the table, meaning that the value of the key is unique for each row (no doubles), it identifies an entity. Attributes with a - are optional, meaning that they don't need to be filled in, the others attributes are mandatory.
Another important feature in relational databases is the relationship between the tables. In the experiments table, the who attribute is a member of the users table. This connects both tables, what is indicated with . Moreover, we denote the cardinality of the relationship. Here, one user will have 0, 1 or more experiments, indicated at the left side of the connection. On the other hand, 1 experiment is performed by exactly 1 user, this is indicated at the right side of the connection. The experiments table is connected with the measurements table with a one-to-many relationship, but one experiment must have at least 1 measurement. So the connection symbol is .
 
 
 

3. Database creation

The Database Definition file ddt.txt  with CREATE SQL commands.
Upload this file with the mysql monitor (in a console or terminal):
    mysql -h info9 -u guest ExperimentsExample < ddt.txt
    • info9 is the network address of our database server
    • you need to log in with a user, use guest (no password is necessary, when logging in from our computers)
    • ExperimentsExample is the name of the database
    • the <  indicates the file has used as input for mysql
 You don't have to redo this, I already created the database. Note that for each primary key, we created a unique index.
Check the definition of the database with mysqlshow:
    mysqlshow -h info9 -u guest ExperimentsExample
=> you get all the tables
    mysqlshow -h info9 -u guest ExperimentsExample experiments
=> the definition of the table experiments
 

4. Database access

Add the data with the INSERT statements. First start the mysql monitor
    mysql -h info9 -u guest ExperimentsExample
mySQL> INSERT INTO users VALUES ('john', 'student');
Remark that the ; is to end the query and send it to the database.
All INSERT commands to fill the database.
Also try the SELECT statements:
mySQL> SELECT * FROM experiments\g
 
 

5. Database access in C

mySQL provides a mySQL API library to access the database.
Let's illustrate this with an example program, download the following files:
Create the Makefile with qmake:
qmake experiments.pro
Make the executable:
make all
Run it:
experiments
Study the code of experiments.cc,  change the query, etc.
 

6. Data analysis

Once you have added data to the database, it is ready to be analysed for reporting, Microsoft Excel is a good tool herefore.
But, how get the data in Excel?
  1. Put your query in a file.
  2. Start msql with the query file as input and an empty result file as output:
    • mysql -h info9 -u guest ExperimentsExample < example_query.txt > example_result.txt
  3. Open the result file in Excel, it will use a Wizard to convert the text file into rows and columns
    • Step 1:
      • Original data type: or you choose Delimited or Fixed width, both will work
      • Start import at row 9
    • Step 2:
      • if you have chosen 'Delimited', you should choose space as delimiter
      • otherwise, indicate the columns (put the '|' in a seperated column)
    • Step 3: here you could for example skip the columns with the '|'





Posted by at 7 comments:
Subscribe to: Posts (Atom)