SQL Interview Preparation—Intermediate Level/

...

/

Answer: Creating a Database

Answer: Creating a Database

Find a detailed explanation of creation of a structured database by defining tables and their relationships.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- The query to create a database along with tables
CREATE DATABASE Organization;
USE Organization;
CREATE TABLE Employees (
    EmpID INT AUTO_INCREMENT PRIMARY KEY,
    EmpName VARCHAR(100),
    Salary DECIMAL(10, 2)
);
CREATE TABLE Skills (
    SkillID INT AUTO_INCREMENT PRIMARY KEY,
    EmpID INT,
    SkillName VARCHAR(100),
    FOREIGN KEY (EmpID) REFERENCES Employees(EmpID)
);
CREATE TABLE Projects (
    ProjectID INT AUTO_INCREMENT PRIMARY KEY,
    EmpID INT,
    SkillID INT,
    ProjectName VARCHAR(100),
    FOREIGN KEY (EmpID) REFERENCES Employees(EmpID),
    FOREIGN KEY (SkillID) REFERENCES Skills(SkillID)
);
SELECT DATABASE();
SHOW TABLES IN Organization;

Code explanation

The explanation of the solution code is given below:

  • Line 2: The CREATE DATABASE statement creates a new database named Organization.

  • Line 3: The USE statement selects the Organization database for running subsequent SQL queries.

  • Lines 5–9: This creates the Employees table with three columns: EmpID (primary key, auto-incremented), EmpName, and Salary.

  • Lines 11–16: This creates the Skills table with SkillID (primary key, auto-incremented), EmpID, and SkillName. A FOREIGN KEY constraint is set on EmpID, linking it to the EmpID in the Employees table.

  • Lines 18–25: This creates the Projects table with ProjectID (primary key, auto-incremented), EmpID, SkillID, and ProjectName. FOREIGN KEY constraints are set for EmpID and SkillID to reference the Employees and Skills tables, respectively.

  • Line 27: The SELECT query returns the name of the currently selected database.

  • Line 29: The SHOW TABLES command lists all tables in a specified database.

Recalling relevant concepts

We have covered the following concepts in this question:

  • CREATE DATABASE statement

  • CREATE TABLE statement

  • Checking for database existence

  • Checking for table existence

Let’s discuss the concepts used in the solution:

  • We use the CREATE DATABASE command followed by the desired database name to create a database. 

CREATE DATABASE DatabaseName;

  • We use the CREATE TABLE command and specify the column names, data types, and any constraints required to create a table.

CREATE TABLE TableName (
    ColumnName1 DATA_TYPE CONSTRAINTS,
    ColumnName2 DATA_TYPE CONSTRAINTS,
    ...
);

  • We use the SELECT DATABASE() command to retrieve for the name of the current database.

SELECT DATABASE();

  • We use the SHOW TABLES command with IN keyword followed by the database name to check for the existence of tables within the specified database.

SHOW TABLES IN DatabaseName;

Alternate solutions

Let’s discuss the alternate solutions for the same problem in this section:

The CREATE SCHEMA statement

In some SQL dialects, CREATE SCHEMA can be used interchangeably with CREATE DATABASE, but this is not consistent across all Database Management Systems (DBMS). CREATE DATABASE is the universal SQL command for creating a database across most DBMS. Let’s have a look at the following query:

MySQL
CREATE SCHEMA Organization;
USE Organization;
CREATE TABLE Employees (
    EmpID INT AUTO_INCREMENT PRIMARY KEY,
    EmpName VARCHAR(100),
    Salary DECIMAL(10, 2)
);
CREATE TABLE Skills (
    SkillID INT AUTO_INCREMENT PRIMARY KEY,
    EmpID INT,
    SkillName VARCHAR(100),
    FOREIGN KEY (EmpID) REFERENCES Employees(EmpID)
);
CREATE TABLE Projects (
    ProjectID INT AUTO_INCREMENT PRIMARY KEY,
    EmpID INT,
    SkillID INT,
    ProjectName VARCHAR(100),
    FOREIGN KEY (EmpID) REFERENCES Employees(EmpID),
    FOREIGN KEY (SkillID) REFERENCES Skills(SkillID)
);
SELECT DATABASE();
SHOW TABLES IN Organization;

SQL management tools

Various Database Management Systems (DBMS) provide graphical user interfaces that simplify the process of creating databases. Let’s take a quick look at how it works:

  • MySQL Workbench: Right-click on “Schemas” and select “Create Schema.”

  • SQL Server Management Studio (SSMS): Right-click on “Databases” and choose “New Database.”

  • pgAdmin (PostgreSQL): Right-click on “Databases,” then select “Create” followed by “Database.”

Scripting and automation

Scripting and automation make it easier to create databases quickly and reduce manual effort. Let’s take a look at a few options:

  • Shell or PowerShell scripts: It automates database creation programmatically.

  • Batch scripts: Use the command line to execute SQL commands. Let’s take a look at the example below:

mysql -u username -p password -e "CREATE DATABASE database_name;"

Note: This command connects to a MySQL server using the specified username, prompts for a password, and uses the -e option to execute the SQL command to create a new database with the given name directly from the command line.

Similar interview questions

Let’s discuss the variations of the questions in this section.

Create a new database

Imagine you’re setting up a new project. Write the SQL commands to create a database called ‘Educative,’ switch to it, and confirm you’re working in the right one by retrieving the name of the current database.

MySQL
/* Write your query below */

If you’re unsure how to do this, click the “Show Solution” button.

Data types in SQL

Attempt the following quiz to test your understanding of database creation concepts.

Technical Quiz
1.

(Select all that apply.) Which of the following are examples of widely used data types in traditional SQL databases for defining columns in a table? Multi-select

A.

Text-based types (e.g., TEXT, VARCHAR)

B.

Numeric types (e.g., INT, DECIMAL)

C.

Date and time types (e.g., DATE, TIMESTAMP)

D.

Graph types (e.g., Neo4j)

1 / 1

Permission for database creation

Attempt the following quiz to test your understanding of the database creation concepts.

Technical Quiz
1.

What permission is needed to create a new database in SQL?

A.

SELECT permission

B.

CREATE permission

C.

UPDATE permission

D.

ALTER permission

1 / 1

Key terms

We have covered the following key terms in the lesson:

SELECT

FOREIGN KEY

REFERENCES

CREATE DATABASE

USE

CREATE TABLE

SHOW TABLES

CREATE SCHEMA

IN