Database vs Schema — SQL Technical Interview Questions & Answers

Database vs Schema

Database vs Schema

  • In MySQL, CREATE DATABASE and CREATE SCHEMA are the same statement. ‘Schema’ in MySQL = that database. In SQL Server / Postgres, a schema is a namespace inside a database (dbo, public). Say: ‘In MySQL they are synonyms; in other products they are not.’
  • Trap: copying Postgres schema answers into a MySQL viva without that sentence.
  • On the example next to this theory: Database vs Schema: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Technical Interview Tip: MySQL: CREATE SCHEMA = CREATE DATABASE. Postgres: different.

Code Examples for Database vs Schema

Database vs Schema — sample query

-- Database vs Schema
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Database vs Schema: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

How to Explain Database vs Schema to an Interviewer

Are DATABASE and SCHEMA the same in MySQL?

Yes, synonyms Not true in every RDBMS