Featured image

Table of Contents Link to heading

Why Query Link to heading

Now that we have got our tables storing data (records), let’s start asking questions (queries) about those data.

SELECT statement Link to heading

This statement is used to retrieve data from the database.

SELECT syntax:

SELECT <selectList> FROM <tableName> WHERE <conditions>;
  1. The SELECT clause defines the target list of attributes/values to be returned.
  2. The FROM keyword defines the tables used by the query to obtain the attribute values.
  3. The WHERE clause is included to determine which tuples should be retrieved.
  • It specifies the conditions that each tuple must match in order to be included in the final result.

Examples:

QueryPurpose
SELECT studentID, studentName FROM Student;Select specific attributes from all tuples from Student
SELECT * FROM Student;Select all attributes and all tuples from Student (display all records)
SELECT Student.* FROM Student;Select all attributes and all tuples from Student specifically.
SELECT * FROM Student WHERE StudentName = 'Henry Mai'Select all attributes and all tuples whose studentName value is ‘Henry Mai’

The SQL SELECT statement returns a new relation composed of the attributes used in the query.

For example:

SELECT example

WHERE condition Link to heading

The expression (condition) after this clause evaluates to TRUE, FALSE, or UNKNOWN for each row of data tested.

It is used in the search condition of WHERE clauses and HAVING clauses, the JOIN conditions of FROM clauses, and other constructs where a Boolean value is required.

Logical conditions are used:

  • Multiple conditions can be chained using AND/OR.
ConditionDescription
=Equal
<>Not equal
<Less than
>Greater than
<=Less than or equal to
>=Greater than or equal to
LIKEPartial matches (string comparison)
NOT LIKENot like the partial matches
IS NULLTest an attribute value is empty (e.g. NULL)
IS NOT NULLTest an attribute has a value (e.g. NOT NULL)

Note: LIKE condition

  • E.g. s LIKE ‘p’
  • p must be surrounded by quotes.
  • p may contain two special symbols:
    • _ = a single unknown character
    • % = zero or more unknown characters
      • Find the colours that have ‘r’ as the second letter and end in ’n’:
      • colourName LIKE ‘_r%n’ returns: Brown, Green.

BETWEEN and AND

... WHERE price BETWEEN 100 and 150;
... WHERE price >= 100 AND price <= 150;

DISTINCT keyword Link to heading

This keyword is used to remove duplicate records from a given table.

For example, if DISTINCT is not used, the results may contain duplicate values:

Distances example

AS keyword Link to heading

This keyword is used to rename an attribute or a table with an alias.

Such an alias only exists for the duration of the query.

AS syntax:

SELECT attr1 + attr2 + ... AS aliasName, ...
FROM TableName;

For example: The following SQL statement creates an alias named ‘address’ that combines four attributes (address, postalCode, city, and country):

SELECT studentName, address + ', ' + postalCode + ' ' + city AS address
FROM Student;

Note: it requires double quotation marks "" or square brackets [] if the alias name contains spaces:

SELECT studentName AS student, contactName AS [Student Contact]
FROM Customers;

ORDER BY keyword Link to heading

This keyword is used to re-order (sort) the output of the query set in ascending or descending order.

The result is set in ascending order by default, unless the DESC keyword is used.

ORDER BY syntax:

SELECT <selectList>
FROM TableName
ORDER BY attributeName [DESC];

For example: list the contents of the Automobile table in descending order of make and model (resort to the order of model if make is the same).

ORDER BY example

TOP keyword Link to heading

This keyword is used to limit the number of matching records to return based on the sort order.

It is mainly used with ordered records.

TOP syntax:

SELECT TOP number [PERCENT] attribute(s) | *
FROM TableName
[WHERE <condition>]
[ORDER BY <expression> [DESC]];

There are two types of values to specify after the Top keyword.

Use with a constant value Link to heading

Return the top X values from a given table.

TOP X example

Use with a percentage value Link to heading

Return a percentage of tuples from a given table.

For example, consider returning two per cent of the Student table which has 214 tuples. Two per cent of 214 tuples is a fraction value (4.28), SQL Server rounds it up to the next whole number - 5 tuples.

SELECT TOP 2 PERCENT
    studentID,
    lastName,
    firstName
FROM Student
WHERE lastName = 'Mai'
ORDER BY studentID;

Summary Link to heading

The SELECT statement is the most often used one to query the database.

It consists of 3 basic clauses: SELECT, FROM, and WHERE clauses.

  1. FROM – identifies the relation(s) to query over.
  2. WHERE – the conditions used to combine and filter the relations.
  3. SELECT – the attributes or data to be returned.

SQL syntax cheat sheet