fundamentals/database/notes/07-groupby-functions-worksheet.md
2022-09-27 15:27:53 +01:00

1.7 KiB

Queries

Aggregation

  1. Get the maximum IQ in all students (Try without aggregation first).
  1. Get the maximum IQ in all students (With aggregation).
  1. Get the oldest batch from the batches table.
  1. Fetch the number of batches that start with the word Jedi.
  1. Get the average IQ of all students (Without using AVG)
  1. Get the average IQ of students in all batches.
  1. Find the average IQ of students in each batch.
  1. Find the total number of students in each batch.
  1. Get the total number of batches taught by each instructor.
  1. Find the average IQ of students in batches with batch ID 1 and 2.
  1. Find count of students that are part of batches that have average IQ greater than 120.

Built-in Functions

  1. Get the average IQ of all the students rounded to the nearest integer.

  1. Find all batches whose name is longer than 10 characters.

  1. Find all batches whose name's first 10 characters contains the string sher

  1. Get all batches that have started on a Sunday

  1. Get all batches that have been running for more than 10 years

  1. Print the name and the instructor's id for each batch. If no instructor is assigned, print NO INSTRUCTOR.

  1. Print the name and IQ of each student. If the IQ of the student is less than 100, print LOW IQ instead.

  1. For each student print the name and their IQ category.
IQ Category
< 100 LOW IQ
100 - 150 MEDIUM
> 150 HIGH