SQL

This will be Question 2.1 in the finals as from November 2018.

There are several ways to run the SQL code.

• We could use a data module, but I have not seen it being used in a Final paper yet. However the Exam Guidelines 2018 indicates that learners must be able to manipulate data with or without a data module.
• Note that the database will be linked to the files for the exam. I have found that we have better luck keeping the link if we close Delphi completely and then open the program by clicking on the .dpr.
• For the PAT remember to show your learners to delete the path when connecting the database to the ADO connection. This will allow their program to work on any computer regardless of the path.
• There are several options to add the SQL code. Make your learners aware of all the methods as every paper I have seen, makes use of a different method. All the code will be given except the SQL statement in blue below that will be replaced with an empty string.

It is important to code the SQL statement in the correct order. Here is a tip to remember the order.

Here are a few SQL tips to the learners:

• It is possible to add two single quotes around the values of text data types in a condition but I find learners have more success using the double quotes instead. For example: 'Select * from tblLearner where Surname = "Smith"';
• SQL is not case sensitive. The following will produce the same output as the statement above: 'Select * from tblLearner where Surname = "smith"'; Note that it is "space" sensitive which often causes no output.
• Even though it is possible using quotes to add variables for text data types, I encourage my learners to use QuotedStr. This makes it easier for them to find their mistakes.
• It is important to read your question and look at the screenshot to see which fields need to be displayed and in which order. In the previous years having SQL as question, a learner received a mark for the correct fields, no more and no less, and having them in the same order as in the screenshot / question.
• Like can be used on a DateTime data type but I find using the Year, Month and Day functions easier to use than Like on these fields. Therefore we only use Like on Text data type fields. When using a condition with DateTime use a # around the value and match the format of the date as in your computer's system tray.
• Functions often needed on calculated fields are:

1. Round(Calculation,1) to round off to the second parameter
2. Format(Calculation,"Currency") which will display the values in South African Rands.

• Remember when you have a calculated field to add AS and the field name you would like to display.
• In the WHERE clause learners are often asked to determine the month, day or year of a DateTime data type. Even though it is possible with a like it is easier to use the functions Day, Month and Year functions in SQL. You can extract these values only from fields that are of DateTime data types. They will return an integer value. For example: where Month(PaymentDate) = 3 which will display all the dates where the month is March.
• When reading from multiple tables remember to add the where to indicate the relationship in the table. I teach my learners to add the relationship first after the where, followed by an AND if there is a condition too. For example: 'Select * from tblLearner, tblSchool where tblLearner.SchoolID = tblSchool.SchoolID AND ...
• If you are reading from multiple tables and the name of a field appears in both tables, remember to add one of the names of the table if you have to display this field. It does not matter which one you are adding as they will contain the same value. For example: Select LearnerName, tblSchool.SchoolID from tblLearner, tblSchool where tblLearner.SchoolID = tblSchool.SchoolID OR use Select LearnerName, tblLearner.SchoolID from tblLearner, tblSchool where tblLearner.SchoolID = tblSchool.SchoolID.
• The field name does not need to be the same name to link the two fields. If they are not the same you do not need to precede the field name with the name of the table. For example: Select * from tblLearner, tblSchool where School = SchoolID
• It is optional to use an alias for the table names when reading from both tables. I find it unnecessary to add the extra code.
• Learners find it difficult to work out when they should use Group By. There are some clues in the question to look out for:
  1. One field contains an aggregate function (sum, min, max, count, avg) and other field/s are also displayed. They usually ask only one extra field to be displayed but in the Supplementary 2014 Q 1.5 there were more than one field that needed to be displayed.
  2. If the question indicates that the aggregate function needs to be calculated PER "group" or FOR EACH "group" this is also a clue to use a Group by. For Example: Calculate the number of cities per province OR Calculate the total paid for each customer.
  3. Note that one can group by and read from both tables as was asked in the Exemplar of 2018 Q 2.1.4.
  4. Group by will automatically also sort the data according to the field you are displaying in ascending order.
  5. The group by will be on the field/s that are displayed and NOT on the aggregate function. For example: 'Select Province, count(*) as [Number of cities] from tblSA Group by Province'
  6. Having goes hand in hand with Group by. Having will be used on the aggregate function of a statement containing Group by. It is almost like a where for an aggregate functions. For example: Select Province, count(*) as [Number of cities] from tblSA Group by Province Having count(*) >50'
• For Insert you need to note the following:
  1. You do not need to qry.Last; to insert a new record.
  2. Foreign key's always need a value.
  3. Primary key's always need a value except when the primary key is an auto number. If it is an auto number Delphi will allocate the number itself and you should not write code to add it.
  4. If the insert asked to use hard code to insert the primary key and you click on the insert method twice, you will get an error since you are creating a duplicate primary key.

Past Papers to Practice:

• Exemplar 2018
• All Question 1 papers from Final 2008 to Supplementary 2014.