SQL Operations

Dialect note: Commands such as SHOW DATABASES, USE, DATABASE(), DESCRIBE, and LIMIT use MySQL-style syntax.

1. Core SQL Operations

1.1 List All Databases

SHOW DATABASES;

Theory: Shows all databases available in your SQL server.

1.2 Create a Database

CREATE DATABASE database_name;

Theory: Creates a new empty database where tables and data will be stored.

1.3 Connect to a Database

USE database_name;

Theory: Switches your session to a specific database so that all queries run inside it.

1.4 Check Current Database

SELECT DATABASE();

Theory: Returns the name of the database you are currently connected to.

2. SQL Languages (Categories)

DDL — Data Definition Language

Purpose: Defines or modifies the structure of databases and tables.

Examples: CREATE, ALTER, DROP.

DML — Data Manipulation Language

Purpose: Inserts, updates, or deletes data.

Examples: INSERT, UPDATE, DELETE.

DQL — Data Query Language

Purpose: Retrieves data from tables.

Example: SELECT.

DCL — Data Control Language

Purpose: Permissions & access control.

TCL — Transaction Control Language

Purpose: Manages transactions (savepoint, rollback).

3. Table Operations (DDL)

3.1 Create a Table

CREATE TABLE orders (
  order_id INT,
  order_date DATETIME,
  order_customer_id INT,
  order_status VARCHAR(45)
);

Theory: Creates a table with specified columns and data types.

3.2 Drop a Table

DROP TABLE table_name;

Theory: Permanently deletes a table and all its data.

3.3 List All Tables

SHOW TABLES;

Theory: Displays all tables present in the connected database.

3.4 Describe Table Structure

DESCRIBE table_name;

Theory: Shows column names, types, constraints, and nullability.

4. Insert, Update, Delete (DML)

4.1 INSERT – Add Records

INSERT INTO orders VALUES (1,'2013-07-25',11599,'CLOSED');

Theory: Adds new rows to a table.

Note: Number of values must match number of columns.

Errors You Learned:

• Adding a string to an INT column → error
• Adding a long string to a short VARCHAR → “data too long”
• Invalid date formats → date parsing errors

5. DELETE – Remove Records

DELETE FROM employees;

Theory:

Deletes rows from a table.

• Without WHERE = deletes all rows
• With WHERE = deletes only matching rows

Example:

DELETE FROM employees WHERE employee_id = 10;

6. UPDATE – Modify Existing Data

(Not in your examples, but important)

UPDATE customers
SET customer_city = 'Austin'
WHERE customer_id = 1;

Theory: Updates specific columns of existing records.

7. SELECT (DQL)

7.1 Select All Rows

SELECT * FROM customers;

Theory: Fetches all columns and rows from the table.

7.2 Select Specific Columns (With Alias)

SELECT customer_fname AS firstname, customer_city FROM customers;

Theory:

• Fetch only needed columns
• Rename using AS to improve readability

7.3 LIMIT – Restrict Number of Rows

SELECT * FROM customers LIMIT 5;

Theory: Returns only first N rows.

Useful for testing, previewing, pagination.

7.4 ORDER BY – Sorting Records

SELECT * FROM customers ORDER BY customer_fname DESC;

Theory:

Sorts result rows based on a column.

• ASC → ascending (default)
• DESC → descending

Example:

ORDER BY salary DESC LIMIT 10;

Meaning: show top 10 highest paid employees.

8. Creating Customers Table + Insert Examples

Create Table

CREATE TABLE retail_db.customers (
  customer_id INT,
  customer_fname VARCHAR(45),
  customer_lname VARCHAR(45),
  customer_email VARCHAR(45),
  customer_phone VARCHAR(45),
  customer_street VARCHAR(255),
  customer_city VARCHAR(45),
  customer_state VARCHAR(45),
  customer_zipcode VARCHAR(45)
);

Insert Records (Simple)

INSERT INTO customers VALUES
(1,'Richard','Hernandez','rich@gmail.com','9191919191','6303 Heather Plaza','Brownsville','TX','78521');

Insert With Column Names (Good Practice)

INSERT INTO customers (
  customer_fname,
  customer_id,
  customer_lname,
  customer_email,
  customer_phone,
  customer_city,
  customer_state,
  customer_zipcode
) VALUES ('Mary',2,'Barrett','mary@gmail.com','8181818181','Littleton','CO','80126');

Theory:

Using column names avoids errors when table structure changes.

9. ORDER BY + LIMIT Example Explanation

Query

SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

Meaning

• ORDER BY salary DESC → sort employees by salary (highest first)
• LIMIT 10 → show only top 10 results

This is commonly used for:

• Top 10 customers
• Highest salaries
• Latest orders
• Most expensive products