SQL TIPS for exams.
- STUDY the database first (at least 5 full minutes). Study both the data and the fields. Know how many records are in each table so that you can recognize any potential errors in the returned dataset. The better you understand the database, the better you will understand the questions.
- A lot of time gets wasted by not knowing the names of the tables and the fields. Loosen the staple on the exam paper so that you can see the table and its field names next to the actual question.
- Use the Bare Bones Approach. Get a very simple version of the query to run. Then re-read the question over and over again adding more and more detail as you focus on different aspects of the wording.
- When revising from your SQL notes, pay attention to the small things as well – the type of brackets, inverted commas, AND, OR, NOT, # # (for dates), & (for concatenation), commas and the wild card character (*).
- The character “&” joins text together (concatenation). It is not the logical operator “AND”. The logical operators in SQL are AND, OR and NOT
- Just because your query runs does not mean that you have fully answered the question. Re-read the question to make sure you get all the marks available. This is what your exam paper should look like
- There will always be UPDATE, DELETE and INSERT questions in every exam. These 3 questions are the easiest and count for roughly 20% of the marks so do not neglect them when preparing. Suggestion: When the run dialog box comes up (and therefore your action query is correct) don’t run them until you have completed all the other questions. That way you will not risk the integrity of your database if your query is incorrect.
- SQL can do maths eg “itemCost * 1.15” (this will increase the cost by 15%). This is a common question for an UPDATE question. Example (UPDATE items SET itemCost = itemCost * 1.15 WHERE itemID = 15;)
- Unlike Java, SQL uses = for comparing text eg WHERE lastName = “Lee”
- A complex query can be worth 8 marks. Never leave your answer blank if you don’t know exactly how to do it. If you can do half of the query you will get half of the marks.
- Pay attention to the words in bold. If one question has two different words in bold, you will probably have to use AND in order to comply with both keywords.
- “Now( )” is today, and is often used to calculate a persons age today. Eg INT ((NOW ( ) – dob) / 365.25) AS [Age] will give the age in complete years. (dob is date of birth)
- Aggregate functions like MIN, MAX, AVG, SUM and COUNT are usually used with GROUP BY.
- If you are told to use a heading for a field, this is a derived field that usually comes from an aggregate function in the select statement.
- GROUP BY without aggregate functions does the same thing as ORDER BY.
- Aggregate function AVG is usually used with the mathematical function ROUND to avoid ridiculous answers eg ROUND(AVG(cost), 2)) GROUP BY (category)
- Know how to generate a code/serial number using text methods (LEFT, LENGTH etc) joined to a random number that has a lower bound and an upper bound eg INT(RND(randomseed) * (upperbound – lowerbound) + lowerbound).
- When the WHERE condition is looking for a null value, we don’t use the equal sign; we use “is”. We say “WHERE itemCost is null”
- You can create your own fields via calculation, (derived field) or by joining fields together (concatenation) eg one: SELECT Name & ” ” Surname AS [Full Name] eg two: GROUP BY lastName & ” ” & firstName eg three:
- SQL string handling (LEFT, RIGHT, MID) does not use index numbers – instead it counts from 1. eg MID(name, 4, 5) – count 4 to start, count 5 for the end)
- When the WHERE condition uses DATE OR TIME, you must enclose the value with hashes eg WHERE time < #00:03:45# (where the time is smaller than 3 minutes, 45 seconds)
- You must know how to extract data from more than one table (joins) See your notes for details.
- The question will use various trigger words that will tell you which SQL reserved word to use. Here are a few . . .
Keyword triggers
- order (alphabetical order, numerical, descending, ascending) – use ORDER by
- number/the amount – use COUNT(*). Also COUNT(firstname)
- total – use SUM(fieldname)
- smallest – use MIN(fieldname)
- biggest/largest – use MAX(fieldname)
- average – use AVG(fieldname)
- each/per category – use GROUP BY
- a range of values – use BETWEEN _ AND
- a range of names / a list – use IN ( , , , )
- contains the word/is similar to – use LIKE ” * the word * ” (note wild cards and the inverted commas for text)
- unique values/no duplicates – use DISTINCT eg (SELECT DISTINCT(category))