Chapter 4 Single Table Queries

BTD210

Chapter 4 Single Table Queries

One of the most important features of a DBMS is its ability to answer a wide variety of questions concerning the data in the database. A query is a question represented in a way that the database can understand..

In SQL, you use the SELECT command to query the database. The basic form of the query is SELECT – FROM – WHERE. The SELECT clause lists the columns that you want to include in your query results. The FROM clause indicates which table(s) are being queried and the WHERE clause is used to list any restrictions or conditions that you want to apply to the data being retrieved.

There are no special formatting rules in SQL. Putting the FROM clause and WHERE clause on separate lines (when ‘WHERE’ is used) is only for purposes of making the query more readable and understandable.

Very straightforward queries will ask to:

a)  retrieve certain columns and all rows

b)  retrieve all columns and all rows (use an asterisk (*) to represent all columns, all rows)

Once the WHERE clause is included in the query, the inference is that some condition must be met. Among the conditions are:

a)  using compound conditions (AND or OR)

b)  the NOT condition

c)  the BETWEEN operator

d)  computed columns -note that a computed column does not exist in the database but can be computed using the data from existing columns…(+, -, *. / )

e)  using the LIKE operator – used when you cannot or don’t want to make an exact match…the LIKE operator uses one or more wildcard characters to test for a pattern match.

f)  Sorting – using the ORDER BY clause – to be used if the order in which you want to retrieve the data is important to you. Then, you can make specific requests of the databases.

g)  Using functions -special functions called aggregate function are used to calculate sums (SUM) , averages (AVG), counts (COUNT), minimums (MIN), maximums (MAX).

h)  Using the DISTINCT operator – helpful when us4ed in conjunction with the COUNT function because it eliminated duplicate values in the query results.

i)  Nesting queries – sometimes, obtaining the result you need requires two or more steps. It is possible to place one query inside another. The inner query is called a subquery. The subquery is evaluated first. After the subquery has been evaluated, the outer query can use the results of the subquery to find its results.

j)  Grouping – uses the GROUP BY clause and answers the query question by grouping rows that share some common characteristic.

k)  The HAVING clause – is used to restrict the groups that are included in the query

l)  NULLS – when dealing with NULLS, rather than using the = sign which is what you would normally expect, the correct format actually uses the IS NULL operator.

Distinction between HAVING and WHERE:

You use the WHERE clause to limit the rows that are included in a query’s result; the HAVING clause is used to limit the groups that are included in a query.