A group to discuss, share and develop the new Higher Computing Science course.
Des Hamilton.
Des HamiltonIs it the assumption that SQL at Higher will be done using MS Access?
It certainly looks that way given the examples in the course specification document. I’m referring to examples where a query is based on the result of a previous query – eg “find resorts with hotels that have the highest star rating” (appendix 10). The examples seem to assume MS Access since a ‘query 2’ is based on the result of a ‘query 1’.
In MySQL, the same examples would require sub-queries. For example:
SELECT hotelName,
starRating,
mealPlan,
pricePersonNight
FROM hotel
WHERE pricePersonNight < (SELECT AVG(pricePersonNight)
FROM hotel)
ORDER BY pricePersonNight DESC;
The specimen paper has a similar assumption (q14(b) which describes a query based on a saved query).
The Choral Shield coursework task is certainly more difficult using a sub-query in MySQL than a saved query in MS Access and one assumes that a similar type of problem will appear in the new coursework.
Should we all be using MS Access then?
chaloveHi ya,
If you are moving on to Advanced Higher then probably not. I’m just finishing the update to the SCHOLAR materials for Higher Databases and they are written so that you can complete the activities in either MySQL/MariaDB or Microsoft Access.
Yes, the materials do make use of some very limited sub-queries but the SQL in the Higher specimen paper is fairly straightforward and the sub-queries in the Scholar materials are introduced in a non-complex way (I hope!).
It’s important the the tasks are consistent across all platforms – we shouldn’t be making use of saved queries or visual query editing in Microsoft Access. That takes away from the pupils ability to “Read and Understand” SQL.
Charlie
Des HamiltonCharlie
My point was that because the specimen paper and the course specification are written assuming MS Access SQL then candidates are likely to be disadvantaged if they use MySQL instead. Qu 14(b) in the specimen paper is structured to suit MS Access SQL with its use of Query 1 and Query 2 and the layout of the query design where Query 2 is based on the saved Query 1. For the sake of consistency, it’s my view that this structure and design layout which makes us of a saved query cannot be used in the exam paper.
The sub-query in MySQL is straightforward as is the one for the sample coursework task. The sample coursework assumes MS Access where Qu 1c(ii) asks “Implement two SQL statements that will produce this output”. I see the marking scheme notes that a sub-query might be used, with the instruction to “still award marks”.
chaloveHi Des,
Yep! Totally agree. The use of Query 1 and Query 2 is not consistent with standard SQL. Questions written in that manner are not appropriate and should not, in my view, be in a paper. Better to follow known conventions and use sub-query were necessary BUT I would suggest that there is enough going on with calculation, aggregation and grouping with Higher that doing to the level of sub-queries should be held for Advanced Higher in the main.
Charlie
Peter ThoresenPages 38 and 58 of the course specification specify that queries should be based on “tables and queries” – so multiple-query or subclause tasks are within the course specification.
Designs are exemplified in Appendix 10 examples 11/12 (pages 93/94). Of course, these designs are platform independent.
Q14b of the specimen exam paper only requires the DESIGN of the second query, so should not be a problem.
Appendix 11 examples 11/12 (pages 102/103) give “solutions”, with no indication as to how the first stage queries are saved (obviously through the use of Access’ interface rather than SQL). Importantly, it also states: “A subclause may be used to combine the two queries. Using subclauses is beyond the scope of the Higher course and will not be assessed.”
The specimen coursework task does require the implementation of a multiple-query task, although the use of subclauses is allowed in the marking scheme. It should not be necessary to use subclauses – sqlite and MySQL allow the implementation of multiple-query tasks using the SQL “CREATE VIEW” command.
I have checked that these two queries work in sqliteStudio:
CREATE VIEW HighestDosage
AS
SELECT Max(dosage) as maxDosage FROM Drug
SELECT patientID, datePrescribed
FROM PrescribedDrug, Drug, HighestDosage
WHERE PrescribedDrug.drugID = Drug.drugID
AND dosage = maxDosage
https://www.tutorialspoint.com/sqlite/sqlite_views.htm
https://dev.mysql.com/doc/refman/5.7/en/view-syntax.html
chaloveThanks Peter,
I get that is how the exam question and example 12 in the CAS is laid out. My point is that the “SQL” used in Query 2 of the CAS example is invalid – the use of [Find Average Per Person] is not standard SQL, it’s a Microsoft Access “feature”. And the CREATE VIEW solution, technically correct, uses a CREATE statement which is not in the Course Assessment Specification so should not be expected of candidates.
The use of sub-queries would be acceptable using the detail from the Course Assessment Specification e.g.
SELECT title, year, gross/budget AS “Ratio”
FROM movie
WHERE year = 2001 AND gross/budget = (
SELECT MAX(gross/budget)
FROM movie
WHERE year = 2001
)
It’s good to discuss this as I think there could be some greater clarity in DDD content.
Charlie
Chris WilsonInteresting discussion and very helpful…
My personal opinion here is that although MS Access may be popular in schools, it’s a bit of a fringe player in the commercial world (and further/higher education) and although it has some “features” as Charlie put it, I’d prefer we stick to the core Computing Science of SQL. After all, SQL changed the online world we all live in and depend upon, plus it’s a really easy language to learn.
I agree with Charlie, “Create View” statements are not part of the course specification. No mention of sub-queries either.
I think there’s a huge amount of scope in this area for good questions in both assignments and question papers at Higher level and if we can focus on solving problems without using software-specific features then there’s every chance we might even get this right first time for our pupils.
To anyone out there who has not yet tried teaching pupils MySQL with something like EasyPHP running on machines you should seriously try it out. Less pointing and clicking to learn, more time spent learning and writing SQL queries that get them marks in both assignments and question papers I reckon.
Plenty more mileage yet in this discussion methinks…
Regards
Chris
Des HamiltonP 102 of the specification document has. “The result of a stored query can be used within another query. The stored answer table is considered as a table by the database engine and any value generated by the query can be used as part of the WHERE clause in a second query”. This is not standard SQL. MS Access is not part of the course and I make my point again that exam questions cannot assume that candidates use Access.
The suggestion of using CREATE, while interesting, isn’t valid since that is outside the scope of the course. Standard SQL will require the use of sub-queries.
The wording in the DDD section of the specification is not that clear. If I am to teach
“Exemplify a design of a solution to a query:
tables and queries”
and this design is then implemented in SQL, then I’ll need to be using Access (and that’s the assumption given by what’s written in the examples in the specification document and the question in the specimen paper); the implication for the implementation in SQL is that I can’t use standard SQL.
This is a problem. Fair assessment is surely only possible if there is clarity in specification and independence in choice of platform.
Paul GardnerHi,
there is a standard SQL technique that works here without having to CREATE VIEW or use a traditional subquery (i.e. a query in the WHERE clause). You can use an ‘inline view’. E.g.
select patientid, dateprescribed, drug.drugid, dosage
from prescribeddrug, drug, (select max(dosage) as maxdose from drug) as iv
where prescribeddrug.drugid = drug.drugid
and dosage = iv.maxdose
order by patientid;
An SQL query, enclosed in brackets, in the FROM clause, is treated like a table. Its column names can be aliased (e.g. maxdose) for clarity and to avoid ambiguity, and the query can be given a name (e.g. iv), so that it can be referred to in other parts of the query.
This technique dates back to SQL92 and should work in modern databases. I’ve just tested it in Access (2016) and SQLite3. It’s interesting to see how the Access translates it from SQL into the GUI Design view.
Paul
Des HamiltonThis has been a very helpful discussion. I’ve shown below the alternative solutions offered so far.
SELECT patientid,
datePrescribed
FROM prescribedDrug,
drug
WHERE drug.drugid = prescribedDrug.drugid
AND dosage = (SELECT Max(dosage)
FROM drug);
================================
CREATE VIEW maxdosage
AS
SELECT Max(dosage) AS maxDrugDosage
FROM drug;
SELECT patientid,
datePrescribed
FROM prescribedDrug,
drug,
maxdosage
WHERE drug.drugid = prescribedDrug.drugid
AND dosage = maxDrugDosage;
================================
SELECT patientid,
datePrescribed
FROM prescribeddrug,
drug,
(SELECT Max(dosage) AS maxDrugDosage
FROM drug) AS maxDosage
WHERE drug.drugid = prescribedDrug.drugid
AND dosage = maxDrugDosage;
================================
Peter, on reflection, creating a view more closely matches the use in the specification and support notes of a Query 1 and a Query 2 at the design stage. I like it for that reason. The problem of course is that creating a view isn’t in the course specification. But then, neither is the use of a sub-query.
Paul, your solution is helpful in that it makes it clearer that the query is based on two tables and a query. Having the sub-query in the WHERE clause makes that less clear. Again, there is a link to the way things are done in the course specification.
My dilemma is that I’m not sure what to expect in an exam question. If the specimen paper is a reliable guide, then there is every chance that candidates will be asked to write two queries (ie a Query1 and a Query2). If candidates create a view in the course assignment, how will that be marked?
I want to use MySQL and MySQL Workbench and be reassured that candidates will not be disadvantaged.
You must be logged in to reply to this topic.