Creating Databases and Tables

# CHAPTER 4

Creating Databases and Tables #

1. Introduction #

2. Learning Objectives #

• Use the CREATE DATABASE command.

• Select an active database using the USE command.

• Understand the syntax of the CREATE TABLE command.

• Define basic columns and their constraints.

• Follow professional SQL naming conventions.

3. Creating and Selecting a Database #

Open your MySQL Command Line (mysql -u root -p) or use the SQL tab in phpMyAdmin and execute the following:

-- 1. Create the database
CREATE DATABASE school_system;

-- 2. Verify it was created
SHOW DATABASES;

-- 3. Tell MySQL you want to work inside this specific database
USE school_system;

*Note: If you forget the USE command, MySQL will throw an error when you try to create a table, because it won't know which database to put it in!*

4. Creating a Table #

Let's create a table to hold our students.

CREATE TABLE students (
    student_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    enrollment_date DATE
);

Breaking down the syntax:

• CREATE TABLE students: The command telling MySQL to build a table named 'students'.

• (...): The parentheses encapsulate all the column definitions.

• studentid INT: A column named 'studentid' that only accepts Integers (whole numbers).

• firstname VARCHAR(50): A column named 'firstname' that accepts Variable Characters (text/strings) up to a maximum of 50 characters long.

• enrollment_date DATE: A column that strictly accepts valid calendar dates (YYYY-MM-DD).

5. Verifying the Table Structure #

-- Show all tables in the current database
SHOW TABLES;

-- Describe the exact column structure of the 'students' table
DESCRIBE students;

*(The DESCRIBE command will print a neat textual representation of your columns and data types!)*

6. Dropping (Deleting) Databases and Tables #

-- Delete a table
DROP TABLE students;

-- Delete an entire database
DROP DATABASE school_system;

7. Mini Project: Build an Inventory Table #

CREATE DATABASE store;
USE store;

CREATE TABLE products (
    product_id INT PRIMARY KEY,       -- Uniquely identifies each row
    product_name VARCHAR(100),
    price DECIMAL(10, 2),             -- A decimal number (e.g., 99.99)
    in_stock BOOLEAN                  -- True or False
);

DESCRIBE products;

8. Common Mistakes #

• Forgetting Commas: Inside the CREATE TABLE parentheses, every column definition MUST be separated by a comma. But, critically, the *very last* column definition must NOT have a comma after it.

• Spaces in Names: Never put spaces in database names, table names, or column names. first name is invalid and will crash. Use underscores: firstname.

• Lowercase Everything: While SQL commands (like CREATE TABLE) are often written in UPPERCASE for readability, you should always name your actual databases, tables, and columns in lowercase (firstname, products).

10. Exercises #

1. 1. Write the SQL command to completely delete a database named oldtestdb.

1. 2. Write the SQL command to create a table named books with three columns: bookid (Integer), title (VARCHAR 150), and publishyear (Integer).

11. MCQ Quiz with Answers #

12. Interview Questions #

• Q: Explain the difference between Data Definition Language (DDL) and Data Manipulation Language (DML) in SQL. Give an example of a DDL command.

• Q: Why is it considered a terrible practice to use spaces or SQL reserved keywords when naming database columns?

13. FAQs #

14. Summary #

15. Next Chapter Recommendation #