Writing SELECT SQL Queries

1. Writing SELECT SQL Queries Yaji Sripada Dept. of Computing Science, University of Aberdeen

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/#WhatIsCopyleft Dept. of Computing Science, University of Aberdeen

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 Ch5 C&B. • But we consider the following types • Using aggregate functions • Without Group By • With Group By • Subqueries • Joins Dept. of Computing Science, University of Aberdeen

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 Dept. of Computing Science, University of Aberdeen

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 <columns to be decided> FROM A,B <everything else to be decided>; Dept. of Computing Science, University of Aberdeen

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) Dept. of Computing Science, University of Aberdeen

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 <everything else to be decided> Dept. of Computing Science, University of Aberdeen

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,Z*2 AS ZDoubled FROM A,B <everything else to be decided> Dept. of Computing Science, University of Aberdeen

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 <everything else still to be decided> All columns are aggregated and size of the results table is 1 row Dept. of Computing Science, University of Aberdeen

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 <everything else still to be decided> 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 Dept. of Computing Science, University of Aberdeen

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 <Some-Condition> <Everything else still to be decided> 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 Dept. of Computing Science, University of Aberdeen

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 Dept. of Computing Science, University of Aberdeen

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 = <Some Value> AND Y = <Some Value> <join conditions still to be specified>; Dept. of Computing Science, University of Aberdeen

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 = <Some-Value> AND Y = (SELECT COUNT(*) FROM C) <join conditions still to be specified>; Dept. of Computing Science, University of Aberdeen

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.<PrimaryKey>=B.<ForeignKey> AND X = <Some Value> AND Y = <Some Value>; Dept. of Computing Science, University of Aberdeen

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. Dept. of Computing Science, University of Aberdeen

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 C&B. • 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. Dept. of Computing Science, University of Aberdeen