The Structure of SQL Queries

Reading material

Elmasri&Navathe Chapter 8.2.1 (pages 251-254), 8.3.6 (pages 274-275)

An SQL query presents what knowledge is needed from the database. Execution of a query produces a a result table. The result table is like any database table, except for the following features.

it does not have a name
it exists only for a moment and vanishes when it has been read
it's rows can be ordered

A query specifies its result table completely. This means that the
result table has no separate table definition.

An SQL query consists of the following parts

select row specification	Specifies both the structure of the resulting rows and the data, based on which the rows are formed. This part is necessary
from tables_used	Identifies the tables to be queried about ( the tables whose data is to be included in the result). This part is necessary.
where conditions	Defines the row combinations used as the basis in building the result rows. This part is needed if rows must be selected or joined on some criteria. Part is optional.
group by grouping criteria	Defines how the rows of the basis should be grouped for constructing the result. Part is optional.
having group inclusion conditions	Specifies which groups of rows are used in computing the result. Part is optional.
order by sorting criteria	Specifies the order of the result rows.Part is optional.

The following query fetches the name and e-mail address of each customer that may pay in credit and has an e-mail address. There will be one row in the result for each such customer and the rows are sorted by name..

 select Name, Email
 from Customer
 where PaymentCategory='credit'  and
     Email is not null
 order by Name;

In simple non-grouped queries one result row is constructed for each row or combination of rows that satisfies the conditions expressed in the Where-part of the query. The values in a result row are computed from the values of the corresponding basis row.

In SQL it is also possible to query for summary information. The following query fetches the number of customers.

 select count(*) from customer;

When summaries are made over the whole material, the result will contain only one row.

Summaries may be made also on group basis. The following query gives the number of customers in each area.

 select areacode, count(*) from customer
 group by areacode;

When summaries are produced on group basis the result will contain one row for each group.

Task:

Consider the following queries on the example database

a) select name from deliveryBoy;

b) select count(*) from deliveryBoy;

c) select deliveryArea, count(*)

from deliveryBoý

group by deliveryArea;

Which of these queries will give the biggest result, when the number of rows is counted.