SQL Practice: University

Table of contents

1. 1. Selecting from one table

   0/33

   Review your basic SQL knowledge! Practice using SELECT and WHERE to filter data from a single table.

   List of exercises Start now
   1. Introduction
      1. Introduction
      2. Get to know the university database
   2. SELECT
      1. Selecting from a single table - recap
      2. All courses
      3. Lecturers last names
      4. How many hours?
      5. Exam needed?
   3. WHERE – numbers
      1. WHERE - recap
      2. Lecturer with id = 8
      3. Best students
      4. Students with an ID other than 1
      5. Under 3 ECTS points
   4. WHERE – text
      1. Working with text values - recap
      2. Students who got an A
      3. Emails ending with .co.uk
      4. Students whose last names start with C
      5. Students' names ending with 'arton'
      6. An ebay email address
   5. WHERE – NULL
      1. NULL in WHERE - recap
      2. Courses without learning paths
      3. Courses with learning paths
      4. Unknown grades
      5. Who's taken the exam?
   6. WHERE – Boolean
      1. Boolean columns - recap
      2. Final exams required?
      3. Project required?
      4. Passed or failed?
   7. WHERE – Logical Operators
      1. Logical condition - recap
      2. Midterm and final grades
      3. Certain degrees only
      4. All data except one record
      5. They got fewer than 50 points
      6. Congratulations

2. 2. JOIN

   0/31

   Practice your skills in joining multiple tables using various types of JOINs.

   List of exercises Buy course
   1. INNER JOIN
      1. INNER JOIN - Recap
      2. Students and their grades
      3. Lecturers who taught Programming courses
      4. Lecturers across course editions
      5. Students who didn't pass
   2. Multiple JOINs
      1. Multiple JOINs - Recap
      2. Course editions throughout the years
      3. Course editions and lecturers
      4. Students and courses
      5. Lecturers who taught Hally Gallemore
   3. Other JOIN types
      1. Other JOIN Types - Recap
      2. All lecturers and course editions
      3. Lecturers and the course editions
      4. Students enrolled in courses and the ones who didn’t
      5. Courses without a course edition
   4. Multiple JOINs of different types
      1. Multiple JOINs of different types - Recap
      2. Lecturers, course editions and course IDs
      3. Highly educated lecturers
      4. Everything but the maths
      5. Students who didn’t pass the course (yet?)
   5. Self-joins
      1. Self-joins - Recap
      2. Semesters and years
      3. Students who started at the same time
      4. Employees with the same degrees
      5. Students with the same grades
   6. Non-equi JOIN
      1. Non-equi JOINs - Recap
      2. Students with different start dates
      3. Pairs of students
      4. Unique pairs
      5. Paired with younger students
      6. Congratulations

3. 3. ORDER BY

   0/22

   Practice using ORDER BY and sorting the results of the queries.

   List of exercises Buy course
   1. ORDER BY one column
      1. Introduction
      2. Lecturers sorted
      3. Students sorted by birth date
      4. Courses sorted by titles
      5. Semester starting dates sorted
   2. ORDER BY multiple columns
      1. ORDER BY multiple columns - Recap
      2. Lecturers by degree and last name
      3. Students by start_date and birth_date
      4. Students by ID and final_grade
      5. Learning path and lecture hours
   3. ORDER BY + WHERE
      1. WHERE and ORDER BY - Recap
      2. Fall semesters ordered chronologically
      3. Programming courses, sorted by two categories
      4. PhDs only
      5. Students starting together, sorted by their full names
   4. ORDER BY + WHERE + JOIN
      1. JOINs and ORDER BY - Recap
      2. Top students by final grade
      3. Courses taken by Noel Durrand
      4. Students who failed their courses
      5. Sara-ann's course editions, starting from the most recent
      6. Projects sorted chronologically by lecturer
      7. Congratulations

4. 4. GROUP BY

   0/24

   Test your knowledge of grouping in SQL. Practice using aggregate functions and GROUP BY.

   List of exercises Buy course
   1. Aggregate functions
      1. Introduction
      2. The number of students
      3. The number of unique start dates
      4. The average midterm grade
      5. Total number of Programming ECTS points
      6. Maximum and minimum lecture hours
   2. GROUP BY one column
      1. Recap
      2. Number of lecturers with each degree
      3. Number of course editions for each student
      4. Number of students for each grade
      5. Total number of lecture hours for each learning path
      6. The number of ECTS points each student can get
   3. GROUP BY multiple columns
      1. Recap
      2. Number of students – then and now
      3. Number of students with the same grade
      4. Youngest student in each course edition
      5. Average students' final grade
      6. Lecturers' hours across terms
   4. HAVING
      1. Recap
      2. Course editions with an average final grade over 50
      3. Students who enrolled for more than one course edition
      4. Most popular courses
      5. Lecturers who failed at least 5 students
      6. Congratulations

5. 5. Other SQL Features

   0/29

   Practice using COUNT(), SUM(), MAX(), MIN() and SQL's mathematical operators. Test your knowledge of NULLs and subqueries.

   List of exercises Buy course
   1. Calculations
      1. Introduction
      2. Total number of hours I
      3. Students' progress
      4. Total number of hours II
      5. Average grade
      6. Difference between grades
   2. NULL
      1. NULLs - Recap
      2. Students with unknown final grades
      3. Courses with some unknown information
      4. Final grades unknown
      5. Passing without a grade?
   3. Subqueries
      1. Recap - simple subqueries
      2. Recap - complex subqueries
      3. Students younger than Noel Durrand
      4. Grades higher than or equal to the average
      5. Courses with more lecture hours
      6. Lots of courses
   4. Correlated subqueries
      1. Correlated subqueries - Recap
      2. Number of students with same grades or above
      3. Above the average
      4. Who passed (and who didn't)
      5. Higher than the average... again!
   5. Set operators
      1. Set operations - Recap
      2. List of students and lecturers
      3. Statistical analysis
      4. Tutorial and lecture hours for each learning path
      5. Common rows
      6. Common rows excluded
      7. Congratulations

6. 6. Advanced queries

   0/17

   Write some more advanced SQL queries using your combined knowledge on SELECT, WHERE, different kinds of JOINs, GROUP BY, HAVING, ORDER BY, set operators, and subqueries.

   List of exercises Buy course
   1. Advanced queries
      1. Introduction
   2. Advanced queries – set 1
      1. Students with unknown results
      2. Students' and lecturers' emails
      3. Number of students per lecturer I
      4. Number of students per lecturer II
   3. Advanced queries – set 2
      1. Lecturers with the same degree
      2. Number of course editions in each term
      3. Results across multiple course editions
   4. Advanced queries – set 3
      1. Grades given to Sibbie McGrotty
      2. Lecturers' hours
      3. Minimum passing final grade
      4. Student with the highest grade
   5. Advanced queries – set 4
      1. Course editions with more students
      2. Above-average students
      3. Average grades across different semesters
      4. Highest grades in the same edition
      5. Congratulations

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Obtain the Certificate Learn more