Copy Database — SQL Technical Interview Questions & Answers

Copy Database

Copy Database

  • MySQL has no COPY DATABASE keyword. You dump and restore: mysqldump college > college.sql then mysql college2 < college.sql after CREATE DATABASE college2.
  • For one table: CREATE TABLE students_bak AS SELECT * FROM students; That copies data, not always indexes/triggers. For a true clone, dump is safer.
  • Trap: thinking CREATE DATABASE college LIKE other; exists like CREATE TABLE LIKE. It does not.
  • On the example next to this theory: Copy Database: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Technical Interview Tip: mysqldump then mysql < file. Or CREATE TABLE new AS SELECT * FROM old.

Code Examples for Copy Database

Copy Database — sample query

-- Copy Database
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;

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

How to Explain Copy Database to an Interviewer

How do you copy a database?

mysqldump to a file CREATE DATABASE new; mysql < dump