Zomalex | Data and AI Training

SQL Intermediate Pre-Course Questionnaire

The SQL Intermediate course assumes that attendees have mastered the SQL basics, covered in the SQL foundation course. Here are a few questions. You should only join the intermediate course if you can answer Yes to all questions. You may be asked to answer these at the start of the course.

Question 1

Can you explain exactly and precisely what this SQL statement does?

SELECT
 ps.PatientId
 , ps.AdmittedDate
 , ps.DischargeDate
 , ps.Hospital
 , ps.Ward
 , DATEDIFF(DAY, ps.AdmittedDate, ps.DischargeDate) + 1 AS LengthOfStay
FROM
 PatientStay ps
WHERE
 ps.Hospital IN ( 'Oxleas', 'PRUH' )
 AND ps.AdmittedDate BETWEEN '2024-02-01' AND '2024-02-28'
 AND ps.Ward LIKE '%Surgery'
ORDER BY
 ps.AdmittedDate DESC
 , ps.PatientId DESC;

Question 2

Can you explain exactly and precisely what this SQL statement does?

SELECT
 ps.Hospital
 , COUNT(*) AS NumberOfAdmissions
 , SUM(ps.Tariff) AS TotalTariff
FROM
 PatientStay ps
GROUP BY
 ps.Hospital
HAVING
 COUNT(*) > 10
ORDER BY
 NumberOfAdmissions DESC;

Question 3

Can you write a SQL statement based on the instructions below? Assume that the database contains an airports table with the appropriate columns.

List airports. Show the following columns: ident, iata_code, name, latitude_deg, longitude_deg
Filter to those airports of large_airports type that have a latitude between 49 and 54 degrees
Order from the most northern airports to most southern airports