CS403: Introduction to Modern Database Systems

Review the following pages in this tutorial: SELECT with DISTINCT (under SQL SELECT STATEMENT), WHERE CLAUSE, and SQL AGGREGATE FUNCTIONS. 

DISTINCT is a clause of the Select statement, which specifies that the data retrieved from the database be distinct. Suppose a student database contained data on student computer science courses, and the desired data are the names of all the students taking a computer science course. If a student was taking 2 computer science courses, there would be 2 course records in the database, each having that students name. The DISTINCT clause specifies that the student name be returned just once, not twice.

The SELECT statement can be used to find specific data in specific records. It can also be used to search or browse a database. The WHERE clause is used to specify conditions on the data that is queried. Fewer conditions result in more data found; more conditions result in less data being found.

Logical operators, such as AND, NOT, are used to combine conditions to place more restrictions on the data desired, and, thus, filter out more undesired data. The SQL DML language manipulates data from a database. It is used to sort data, arrange data, or perform calculations on the data. Aggregate functions are built in DML functions used in the Select statement to summarize an amount of data, and return a calculated or summary value, instead of returning all of the data found. For example, COUNT, AVERAGE, SUM, MAX, and MIN are aggregate functions. Note that a function takes arguments, written in parentheses after the function name.

There are several SQL languages widely used in practice. PostgreSQL is one of those. This resource includes the PostgreSQL description of logical operators and their use with the SELECT statement.

LIKE is typically used to find all records that have a column value that matches a specific pattern. Special characters are used to specify the format of the pattern.

BETWEEN is used to find all records that have a specific column value in a specified range.

Sometimes, a column value may not have to be in a given range but rather in a given set of values. The IN statement is used to specify a set of values that a column value must be in.

The TOP clause limits the number of rows returned by a Select query. TOP is not supported by all SQL languages. Others use LIMIT or ROWNUM.

Read "Limiting result rows" to learn how various SQL implementations restrict the number of rows returned by the SELECT command.

The ORDER BY clause is used to sort the records resulting from a query according to the values of a column or set of columns.