Writing SELECT SQL Queries

1
Writing SELECT SQL Queries

• Nigel Beacham
• n.beacham_at_abdn.ac.uk
• www.abdn.ac.uk/edu196
• based on materials by Dr Yaji Sripada

2
Disclaimer

• Writing SELECT queries is a skill you need a lot
  in your future courses and also in your jobs.
• Following some simple steps/guidelines in writing
  these queries will help you to develop this skill
  in a systematic way.
• These notes contain steps/guidelines for writing
  some types of SELECT queries
• Queries that are known to be difficult for
  learners
• These are informal notes and students should use
  them only in the initial stages of learning SQL
  until they develop their own procedure for
  writing SQL queries.
• Please feel free to modify them they are
  copyleft http//www.gnu.org/copyleft/WhatIsCopy
  left

3
SELECT Queries

• A SELECT query retrieves information from a
  database and structures them into a results table
• There are many types of SELECT queries as
  described in Ch6 CB.
• But we consider the following types
• Using aggregate functions
• Without Group By
• With Group By
• Subqueries
• Joins

4
Write SELECT Queries in 3 steps

• Step 1 Decide the tables you require for
  answering the query
• Step 2 Decide the columns you require in your
  results table
• Step 3 Decide the rows you require in your
  results table

5
Step1

• Decide the tables you require for answering the
  query
• Based on the information you want to show to the
  user, you select the tables which contain this
  information.
• At this stage you have information to write the
  FROM clause of your SELECT query
• Your query may look like (A and B are Tables)
• SELECT ltcolumns to be decidedgt
• FROM A,B
• lteverything else to be decidedgt

6
Step2

• Decide the columns you require in your results
  table
• Normally this is quite simple
• CASE1 The query specification contains a list of
  required columns.
• But in some cases this may be involved
• CASE2 If you need a calculated field in your
  results table (such as deposit calculated from
  monthly rent)
• CASE3 If you need aggregated information in your
  results table (such as count of staff, average
  salary etc)

7
Step2

• CASE1
• You simply list the required columns in the
  SELECT clause
• At this stage your query may look like (X,Yand Z
  are columns in A or B)
• SELECT X,Y,Z
• FROM A,B
• lteverything else to be decidedgt

8
Step2

• CASE2
• Initially list all the simple required columns
• In addition, compute the required calculated
  columns and set their names (headings) using AS
• At this stage your query may look like
• SELECT X,Y,Z2 AS ZDoubled
• FROM A,B
• lteverything else to be decidedgt

9
Step2

• CASE3
• Compute the required aggregated columns and set
  their names using AS
• Because you used aggregate functions in the
  column list YOU ARE NOT ALLOWED TO USE ANY OTHER
  UNAGREGATED COLUMNS IN THE COLUMN LIST
• At this stage your query may look like
• SELECT MAX(X),MIN(Y),AVG(Z)
• FROM A,B
• lteverything else still to be decidedgt

All columns are aggregated and size of the
results table is 1 row
10
Step2

• CASE3 continued
• You can show unaggregated columns in the SELECT
  clause only if you group their items using the
  GROUP BY clause
• At this stage your query may look like
• SELECT X,MIN(Y),AVG(Z)
• FROM A,B
• GROUP BY X
• lteverything else still to be decidedgt

Items in X are grouped, therefore X can be
included in the column list. Size of the results
table is equal to the number of groups of items
11
Step2

• CASE3 continued
• Sometimes you want to control the groups of items
  you show in the result table using HAVING
  condition
• At this stage your query may look like
• SELECT X, MIN(Y), AVG(Z)
• FROM A,B
• GROUP BY X
• HAVING ltSome-Conditiongt
• ltEverything else still to be decidedgt

HAVING is used only to restrict the groups shown
in the result table. WHERE is used to restrict
the rows shown in the result table
12
Step3

• Decide the rows you require in your results table
• This involves composing several conditional
  expressions each of which constrains the rows
  shown in the results table in some way
• Join all these conditional expressions using
  logical operators such as AND and OR in the WHERE
  clause
• There are three different kinds of conditional
  expressions you write
• CASE1 Conditional expressions specified in the
  query specification (such as show details for
  Aline Stewart)
• CASE2 Conditional expressions based on results
  from other query
• CASE3 Conditional expressions you require for
  joining tables

13
Step3

• CASE1
• Non-join conditions are usually written based on
  the constraints mentioned in the query
  specification
• At this stage your query may look like
• SELECT X, Y, Z
• FROM A,B
• WHERE X ltSome Valuegt
• AND Y ltSome Valuegt
• ltjoin conditions still to be specifiedgt

14
Step3

• CASE2
• Sometime non-join conditions involve checking
  values not specified by the user (As in CASE1)
  but present elsewhere in the database same
  table or other tables
• You need to first write a SELECT query to obtain
  this information
• You follow the same steps recommended here to
  write the subquery
• Once the subquery is ready you then use it as
  part of the conditional expression
• At this stage your query may look like
• SELECT X,Y,Z
• FROM A,B
• WHERE X ltSome-Valuegt
• AND Y (SELECT COUNT() FROM C)
• ltjoin conditions still to be specifiedgt

15
Step3

• CASE3
• Join conditions are decided based on the type of
  join you wish to perform (refer to the different
  types of joins from the lecture notes)
• The most common type of join is a natural join or
  inner join
• For every pair of tables you join, determine the
  links (foreign key links) between them
• Compose a join condition for each of the links
• At this stage your query is complete and may look
  like
• SELECT X, Y, Z
• FROM A,B
• WHERE A.ltPrimaryKeygtB.ltForeignKeygt
• AND X ltSome Valuegt
• AND Y ltSome Valuegt

16
Verification Tips

• After you write a query always try to hand run
  it to see if it retrieves the required
  information
• For this purpose, it is a good idea you imagine
  you are marking a query written by some other
  person to avoid the obvious conflict of interest
  between you as the author and you as the marker.

17
Appeal

• The cases covered in these steps are by no means
  complete
• You can add many more cases to these steps to
  include other types of SQL queries from Ch5 CB.
• If you manage to write additional cases into the
  3 step process described here please let me
  know - I might use them in the future.
• If you find any errors (of any kind) in these
  notes please let me know.
• I am happy to acknowledge your efforts on these
  notes.