Writing SELECT SQL Queries - PowerPoint PPT Presentation

1 / 17
About This Presentation
Title:

Writing SELECT SQL Queries

Description:

Writing SELECT SQL Queries Nigel Beacham n.beacham_at_abdn.ac.uk www.abdn.ac.uk/~edu196 based on materials by Dr Yaji Sripada Dept. of Computing Science, University of ... – PowerPoint PPT presentation

Number of Views:131
Avg rating:3.0/5.0
Slides: 18
Provided by: srip153
Category:

less

Transcript and Presenter's Notes

Title: 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.
Write a Comment
User Comments (0)
About PowerShow.com