Previous | Home | Next

SELECT AND FROM

If you want to use SQL to retrieve data, your query should minimally contain the keywords SELECT and FROM. The example below is a very basic SQL query.

SELECT *
FROM CREATOR ;

Firstly, remember that all SQL queries should end in a semicolon (;). This semicolon indicates that the SQL query is complete and that it is ready to be processed.

The FROM clause mentions the table that contains the data that you are interested in. As can be seen, the FROM clause in the query above refers to the table CREATOR. Secondly, the SELECT statement specifies the columns that need to be displayed. Note that in this example, no actual columns are mentioned. Instead, an asterisk is used, which indicates that all the columns need to be shown. This first query will simply return the entire table. You will receive each row and every colomn in the table CREATOR.

In certain situations, you may not be interested in every single column. Instead of using an asterisk, you can also select the names of the specific column that you are interested in. The result of the query will only contain the columns that you mention after SELECT. The selected colomns are collectively referred to as the select list. The following query will return only the first name and the last names of all the creators.

SELECT NAME_LAST, NAME_FIRST
FROM CREATOR ;

WHERE

These first two queries have returned all the records in the table. Sometimes, however, you may only want to see those records that match a certain criterion. To specify such a condition, you can use the keyword WHERE. If this keyword is used, every row will be considered separately, and evaluated against the condition that has been included. If the result of the evaluation is "true", the row will be selected. This way, you filter the original table. Only those rows that we are interested in will be displayed in the result of the query.

To formulate the conditions in the WHERE clause, you can use the following operators:

=	equals
<> or !=	not equals
>	greater than
>=	greater than or equal to
<=	less than or equal to
LIKE	String comparison

You can combine any number of criteria using AND or OR. If you combine AND and OR, you need to use brackets in order to indicate the order in which the conditions should be considered. There are no fixed priority rules in SQL.

The query in the following example will return only those creators who were born after 1800:

SELECT NAME_LAST, NAME_FIRST
FROM CREATOR
WHERE YEAR_OF_BIRTH > 1800 ;

The following example demonstates the usage of the LIKE operator.

SELECT NAME_FIRST, NAME_LAST
FROM CREATOR
WHERE NAME_LAST LIKE 'B%';

This statement will select the records in which the column named "NAME_LAST" contains a value which starts with "B". The percentage symbol (%) is used as a wildcard. It represents zero or more random characters. This query, for instance, will return both 'Bruegel The Elder' and 'Baudelaire'.

DISTINCT

DISTINCT is a keywords that is used to select only the unique values in the result. Sometimes, there may be a degree of repetition in a table. Using DISTINCT will discard the duplicate records for the columns that you refer to in the SELECT clause.

SELECT DISTINCT COUNTRY_BORN
FROM CREATOR ;

This query will return only the unique country codes from the table CREATOR. The table contains several 'creators' from Italy, but the country code 'IT' will occur only once in the result.

Exercises

Select the treasures that were produced after 1800. Display only the title and the year
Make a list of all the names of the names that have been entered in the table CITY.
Select the creators whose first name starts with the letter "W"
Select the distinct subject codes in the table TREASURE

SQL Tutorial

SELECT AND FROM