CS403 Study Guide

Unit 9: Basic Select Statements

9a. construct basic queries using SQL

  • What is a basic query?
  • What SQL statement is used for basic queries?
  • Describe, in general terms, common basic queries and their corresponding SQL parameters, keywords, and clauses used in the SELECT statement.

Queries are user requests for information from the database. A query is formed from the SELECT and/or the JOIN statements. Think of the database as a large electronic filing cabinet of related data. It is assumed we are familiar with the organization of the filing cabinet, so that we can request needed information. Thus, we must be able to identify the tables, columns, and records, and related data, that constitute what we need, transform it into parameters, clauses, and keywords for a SELECT statement. Unit 9 discusses the SELECT statement. 

Sometimes the information we need spans several tables, and retrieving the information involves multiple queries. It is usually more efficient to use a JOIN, which creates a virtual table that incorporates data from several tables and then use one or a few SELECT statements to retrieve the needed information. Unit 10, the next Unit, discusses the JOIN statement.

A basic query is a request for information from a single table. The SELECT statement is used for basic queries. A basic query asks for values for a subset of records from a table, where the subset is determined by the values of a specific column. For example, using the sample Student schema, Student(StudentId, StudentName, Department, AdmitDate), which we defined at the end of the discussion for 8c, a basic query could ask for dates in which students were admitted. The basic query is:

SELECT AdmitDate FROM Students; 

If 10 students were admitted on the same data, that date would be repeated 10 times.

SELECT DISTINCT AdmitDate FROM Students;

lists a date only once.

The WHERE clause selects records that satisfy a specified condition. Records can be grouped into categories using the GROUP BY clause, and sorted in a specified order using the ORDER BY clause.

Within the SELECT statement, complicated expressions can be used. Arithmetic and logical expressions can be used as a column heading and in the WHERE clause to filter the records. Logical operators, for example, comparison operators, result in 'true' or 'false' and can be used to specify conditions to filter desired records in a table. In addition to the typical comparison operators, there are set operators IN, BETWEEN, SOME, ALL, EXISTS, that can do additional filtering of records.

Information sought via queries is often large and results are output as a report. The retrieved records can be sorted or gathered into groups using the SORT BY or GROUP By clauses, respectively. Then, functions including arithmetic, aggregate (such as SUM), and character type functions (for example, UPPER that converts a character string to uppercase), can be used in expressions to further filter records and to summarize the values in a column – or count the number of total records filtered or get subtotals for subsets of the filtered records.

Given that SQL is a programming language for finding and retrieving information from large databases, it is no surprise that SELECT has numerous features, specified using parameters, keywords, clauses, operators, and functions, that support user queries. In addition, since information is often spread across tables, the SELECT statement is recursive (it can contain subqueries). An inner SELECT statement within an outer SELECT statement, which is called nesting of queries. A subquery can be inside a SELECT clause, or a FROM clause, or a WHERE clause. (Note: a SELECT clause is the set of parameters that a user specifies immediately after 'SELECT'.) Lastly, SELECT can be used in other commands also namely, INSERT, DELETE, UPDATE.

Do not forget the best preparation for the final exam includes trying out the SELECT statement features on the sample database you defined and updated in the SQL Sandbox.

 

Unit 9 Vocabulary

This vocabulary list includes terms that might help you with the review items above and some terms you should be familiar with to be successful in completing the final exam for the course. 

Try to think of the reason why each term is included.

  • Basic query
  • SELECT FROM
  • SELECT WHERE
  • Parameters
  • Keywords
  • Clauses
  • DISTINCT
  • GROUP BY
  • ORDER BY
  • Arithmetic operator
  • Logical operator
  • Arithmetic expression
  • Logical expression
  • Arithmetic function
  • Aggregate function
  • Character function
  • Recursion/subquery/nested queries
  • Inner and outer SELECT