SQL · Theory

Database vs Schema

Theory

268/439

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.

Exam tip

MySQL: CREATE SCHEMA = CREATE DATABASE. Postgres: different.

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.

Short notes

DefMySQL: DATABASE = SCHEMA.
RuleOther RDBMS: schema is inside a database.
TrapOne definition for every product.

-- 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

Database vs Schema — sample query

Short notes

Questions

Database vs Schema

Database vs Schema — sample query

Short notes

Questions