2: Database Queries

Learning Goals

At the end of this Tutorial you will be able to:

Use SELECT statements with the WHERE clause and various operators to retrieve information from a table in an SQL database.

About CRUD database operations

So-called CRUD operations are the basis of working with databases. CRUD is an acronym that stands for the following:

Create: This refers to adding a new record to a table in a database. For example, a college could add a new student, a company a new staff member and an online service such as Instagram a new user.
Read: Reading one or more tables means retrfieving some information from them, typically for display on-screen. Of the four CRUD tasks, this is the only one that does not change the tables or the data they contain.
Update: When performing an update, you are not adding or deleting records, but changing their content in some way. If you change your login password, for example, your old password in a table that holds user information is updated by your new one.
Delete: This means removing one or more records from a table. It does not delete the table or the database in which the table is stored.

SQL queries and the SELECT keyword

In relational databases, the four CRUD tasks are performed using SQL statements. The most-commonly used SQL statement contains the SELECT keyword for reading (the ‘R’ in CRUD) information from tables.

Statements that contain the SELECT keyword are often called queries, as they allow you to ask questions of your database tables:

Show me all customers who live in County Wexford.
Show me all students who achieved a final grade of 80% or more.
Show me the top ten best-selling products on my e-commerce website.
Show me a list of my customers’ email addresses.

The simplest form a query in SQL is a wildcard query, which retrieves all information from all records in a table.

SELECT * FROM Customers;

As you can see, an SQL statement ends with a semi-colon.

W3 SCHOOLS EXERCISE: A sample SELECT wildcard (*) query.

Note that SQL is not a case sensitive language. Both the following statements work fine and produce the same output.

SELECT * FROM Customers;

SELECT * from customers;

In summary, the general form of a wildcard SELECT statement in SQL looks as follows:

SELECT * FROM table_name;

Selecting only certain columns

When you query a table with the SELECT statement, you will not always want to see every item of information from the records. For example:

Customers table: You might want to list only your customers' first names, last names and email addresses – and no other customer-related information. In this query, you are not interested in other details such as your customers' dates of birth and postal addresses.
Products table: A retail business might want to list only those products it stocks from a certain supplier. In this query, you are not interested in any other details about the products you sell – such as their colour or price.

In database tables, different types of information is stored in different columns. These columns will have names (without spaces) such as ProductID, EmailAddess, SalePrice, StudentNumber and so on.

Here is an example of a query that will show, for all customers in the Customers table, only the data stored in two of their columns: the Name column and the City column.

SELECT CustomerName, City FROM Customers;

W3 SCHOOLS EXERCISE: A sample SELECT query for specific columns.

In summary, the general form of this type of SELECT statement in SQL looks as follows:

SELECT column_name1, column_name2, column_name3 FROM table_name;

With such queries, you can include one, two or more columns. You must separate column names from each other by a comma (,).

You could even include all columns of the table you are querying. That would give you the same output as using a wildcard (*) query.

Selecting only certain rows (records)

As database tables grow larger, it becomes practical to query data on all records in a single operation. For example:

Irish state: The Department of Social Protection has a table of many millions of Personal Public Service Numbers (PPSNs). Everyone born in Ireland or who has worked or received a Social Welfare payment in Ireland is assigned one.
Amazon: This online retailer offers a catalogue of more than 12 million products.
Netflix: This content service has over 200 million paying subscribers worldwide.

You can use a WHERE clause in a SELECT query to retrieve only those records that meet a specific condition.

For example, the following SQL query selects only those customers in the Customers table located in the country of Mexico:

SELECT * FROM Customers WHERE Country='Mexico';

W3 SCHOOLS EXERCISE: A sample SELECT query with a WHERE clause.

In the above example, you were working with a string (text) value, so you needed to enclose the value with single or double quotes.

However, numeric fields should not be enclosed in quotes. See the sample query below.

SELECT * FROM Customers WHERE CustomerID=1;

W3 SCHOOLS EXERCISE: A SELECT query with a WHERE clause and a numeric value.

The WHERE clause with operators

The following operators can be used in the WHERE clause of a SELECT query.:

Operator	Description and example
=	Equal SELECT * FROM Customers WHERE CustomerID=1;
>	Greater than SELECT * FROM Products WHERE Price > 30;
<	Less than SELECT * FROM Products WHERE Price < 30;
>=	Greater than or equal SELECT * FROM Products WHERE Price >= 30;
<=	Less than or equal SELECT * FROM Products WHERE Price <= 30;
<>	Not equal SELECT * FROM Products WHERE Price <> 18;

In summary, the general form of a SELECT statement with a WHERE clause looks as follows:

SELECT column1, column2 FROM table_name WHERE condition;

The SQL AND, OR and NOT Operators

You can combine the WHERE clause with AND, OR, and NOT operators.

The AND and OR operators are used to retrieve records based on more than one condition:

The AND operator displays a record only if all the conditions joined by AND are TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.

Here is an example of the AND operator in use in a SQL query.

SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin';

W3 SCHOOLS EXERCISE: A SELECT query with an AND operator.

In summary, the general form of a SELECT statement with a WHERE clause and an AND operator looks as follows:

SELECT column1, column2 FROM table_name WHERE condition1 AND condition2;

Next, here is an example of an SQL query with the OR operator.

SELECT * FROM Customers WHERE City='Berlin' OR City='München';

W3 SCHOOLS EXERCISE: A SELECT query with an OR operator.

In summary, the general form of a SELECT statement with a WHERE clause and an OR operator looks as follows:

SELECT column1, column2 FROM table_name WHERE condition1 OR condition2;

The following SQL statement selects all fields from the "Customers" table where country is NOT "Germany":

SELECT * FROM Customers WHERE NOT Country='Germany';

W3 SCHOOLS EXERCISE: A SELECT query with a NOT operator.

You can also combine the AND, OR and NOT operators. The following SQL statement selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "München" (use parenthesis to form complex expressions):

SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');

The following SQL statement selects all fields from the "Customers" table where the country is NOT "Germany" and NOT "USA":

SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA';

In summary, the general form of a SELECT statement with a WHERE and a NOT operator looks as follows:

SELECT column1, column2 FROM table_name WHERE NOT condition;

The built-in SQL functions

Most SQL databases offer a range of built-in functions for performing common tasks, such as finding average or maximum and minimum values.

The MIN() function returns the smallest value of the selected column.

SELECT MIN(column_name) FROM table_name WHERE condition;

W3 SCHOOLS EXERCISE: A SELECT query with a MIN() function.

The MAX() function returns the largest value of the selected column.

SELECT MAX(column_name) FROM table_name WHERE condition;

W3 SCHOOLS EXERCISE: A SELECT query with a MAX() function.

Return to Contents.

Contents >