Creating Databases and Tables

# CHAPTER 4

Creating Databases and Tables #

1. Introduction #

2. Learning Objectives #

• Differentiate between DDL and DML in SQL.

• Write a CREATE DATABASE statement.

• Select an active database using the USE command.

• Write a CREATE TABLE statement with columns and basic data types.

• Delete databases and tables using the DROP command.

3. DDL vs DML #

• DDL (Data Definition Language): Commands that build the house. (CREATE, ALTER, DROP). They define the structure (Tables, Columns).

• DML (Data Manipulation Language): Commands that put furniture in the house. (INSERT, SELECT, UPDATE, DELETE). They handle the actual data rows.

*In this chapter, we are exclusively focusing on DDL.*

4. Creating a Database #

-- Create a brand new database
CREATE DATABASE school_system;

*(Note: In SQL, two dashes -- are used to write comments that the engine ignores).* *(Note 2: Every SQL command MUST end with a semicolon ;)*

5. Selecting the Database (USE) #

-- Tell the server we want to work inside the school_system database
USE school_system;

*(Note: If you are using PostgreSQL/pgAdmin, you select the database by clicking it in the visual sidebar rather than using the USE command).*

6. Creating a Table #

-- Create a table to store Student information
CREATE TABLE students (
    id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    enrollment_date DATE
);

Breaking it down:

• id INT: A column for the student ID number.

• firstname VARCHAR(50): A column for text, with a maximum limit of 50 characters.

• enrollmentdate DATE: A column strictly formatted for calendar dates.

7. Destroying Architecture (DROP) #

-- DANGER: This permanently deletes the table AND all data inside it!
DROP TABLE students;

-- DANGER: This permanently deletes the entire database!
DROP DATABASE school_system;

*Warning: There is no "Undo" or "Recycle Bin" in SQL. Once you execute a DROP command, the data is gone forever.*

8. Mini Project: Student Management Database #

CREATE DATABASE university_db;
USE university_db;

-- Table 1: Teachers
CREATE TABLE teachers (
    teacher_id INT,
    full_name VARCHAR(100),
    department VARCHAR(50)
);

-- Table 2: Courses
CREATE TABLE courses (
    course_id INT,
    course_title VARCHAR(100),
    credits INT
);

*(We have successfully created the structural schema! The tables currently have 0 rows of data in them).*

9. Common Mistakes #

• Forgetting the Semicolon: If you type CREATE DATABASE test CREATE TABLE users(id INT), SQL will crash. It thinks it is one giant sentence. You must separate commands with a semicolon ;.

• Missing Commas in Table Creation: Inside the CREATE TABLE () parentheses, every column definition MUST be separated by a comma, EXCEPT the very last one!

10. Best Practices #

• Snake Case Naming: Avoid spaces and capital letters in table and column names. Use snakecase.

• *Good:* CREATE TABLE studentdata

11. Exercises #

1. 1. What SQL command is used to select an active database before creating tables?

1. 2. What is the highly dangerous SQL command used to permanently delete an entire table and its structure?

12. SQL Challenges #

CREATE DATABASE ecommerce_db;
USE ecommerce_db;
CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(100),
    price INT
);

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the difference between DDL (Data Definition Language) and DML (Data Manipulation Language). Provide two examples of commands for each.

• Q: Why does SQL enforce strict Data Types during the CREATE TABLE process? What architectural advantage does this provide over a standard Excel spreadsheet?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #