AI InterviewSearch…Search problems, topics, or pages…Ctrl KGet started
Skip to content

01 Foundations / Checkpoint

Checkpoint — Foundations

25 min · +25 XP · 3 exercises · 12 questions

Exercises0/3
Quiz · 70% to clear12 q

This checkpoint covers the whole module: what a relational database is — tables, typed columns, rows without order, primary and foreign keys — and what the DBMS does; SQL as a declarative language and its statement families; MySQL beside PostgreSQL, SQLite and SQL Server; the basic SELECT; and the logical order in which a query's clauses are evaluated.

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

  • What are a row, a column and a table's schema, and why does a table have no row order?
  • What two properties must a primary key have, and what does a foreign key point at?
  • Name the five statement families and one statement from each.
  • Why does SELECT name city FROM students run without an error, and what does it return?
  • How do MySQL, SQL Server and the SQL standard each take the first three rows?
  • How do you join two strings in MySQL, and what does || do there?
  • In what order are FROM, WHERE, SELECT, ORDER BY and LIMIT evaluated, and what follows for aliases?
  • What is wrong with SELECT * in application code?

The three queries are a whole table read back in a fixed order, a list of chosen columns with a literal and a computed column beside them, and a filtered list ordered by a computed alias with a tie-breaker.

Common questions

Why can a column alias be used in ORDER BY but not in WHERE?

The clauses are evaluated in a logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. WHERE runs before SELECT has created the alias, so the alias is unknown there; ORDER BY runs after, so it can use it.

What makes a column a good primary key?

Its value must be unique in every row and never NULL, and it should never change, because other tables store it as a foreign key. A surrogate integer id assigned by the database meets all three, which is why it is the usual choice.

What does 'a' || 'b' return in MySQL?

It returns 0, because in MySQL's default mode || is the logical OR operator and both strings convert to the number 0. Use CONCAT('a', 'b') to join strings in MySQL; || concatenates in PostgreSQL, SQLite and Oracle.

AI InterviewGet started

SQL Basics Quiz: Databases, SELECT and Query Order Practice

Test yourself: 12 questions and three queries on relational tables, keys, SQL statement families, MySQL dialects, SELECT and the order clauses run in.

  • Course: SQL study plan
  • Module: Foundations
  • Kind: Checkpoint — cleared at 70%
  • Reading time: 25 min
  • Runtime: MySQL 8 syntax on SQLite 3.49

Checkpoint — Foundations is the checkpoint that closes the Foundations module: a graded quiz and whole-program exercises, passed at 70%.

Instructions

This checkpoint covers the whole module: what a relational database is — tables, typed columns, rows without order, primary and foreign keys — and what the DBMS does; SQL as a declarative language and its statement families; MySQL beside PostgreSQL, SQLite and SQL Server; the basic SELECT; and the logical order in which a query's clauses are evaluated.

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

  • What are a row, a column and a table's schema, and why does a table have no row order?
  • What two properties must a primary key have, and what does a foreign key point at?
  • Name the five statement families and one statement from each.
  • Why does SELECT name city FROM students run without an error, and what does it return?
  • How do MySQL, SQL Server and the SQL standard each take the first three rows?
  • How do you join two strings in MySQL, and what does || do there?
  • In what order are FROM, WHERE, SELECT, ORDER BY and LIMIT evaluated, and what follows for aliases?
  • What is wrong with SELECT * in application code?

The three queries are a whole table read back in a fixed order, a list of chosen columns with a literal and a computed column beside them, and a filtered list ordered by a computed alias with a tie-breaker.

Common questions

Why can a column alias be used in ORDER BY but not in WHERE?

The clauses are evaluated in a logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. WHERE runs before SELECT has created the alias, so the alias is unknown there; ORDER BY runs after, so it can use it.

What makes a column a good primary key?

Its value must be unique in every row and never NULL, and it should never change, because other tables store it as a foreign key. A surrogate integer id assigned by the database meets all three, which is why it is the usual choice.

What does 'a' || 'b' return in MySQL?

It returns 0, because in MySQL's default mode || is the logical OR operator and both strings convert to the number 0. Use CONCAT('a', 'b') to join strings in MySQL; || concatenates in PostgreSQL, SQLite and Oracle.

Exercises

Read the catalogue in order

The table courses:

course_idcodetitlecredits
30CS301Databases4
10CS101Programming3
20CS201Data Structures4

Return every column of every course, ordered by course_id.

Output: course_id | code | title | credits.

course_id | code | title | credits
10 | CS101 | Programming | 3
20 | CS201 | Data Structures | 4
30 | CS301 | Databases | 4

A report row per student

The table students:

student_idnamebranchcredits
2RaviECE18
1AshaCSE20

Return, in this order: student_id, name, the contact hours credits * 15 as contact_hours, and the text active as status. Order by student_id.

Output: student_id | name | contact_hours | status.

student_id | name | contact_hours | status
1 | Asha | 300 | active
2 | Ravi | 270 | active

Big orders first

The table orders:

order_idcustomeramountdiscount
1Asha1200200
2Ravi4500
3Meera800100
4Dev1100100

Return order_id, customer and the net amount amount - discount as net, only for orders whose net is greater than 500, ordered by net descending, ties by order_id.

Output: order_id | customer | net.

order_id | customer | net
1 | Asha | 1000
4 | Dev | 1000
3 | Meera | 700

In this module: Foundations

← How a query runs — the logical order of the clauses · Columns and aliases — naming what a query returns →