Skip to content

Core Concepts

1. Database Basics

Think of a database as a digital filing cabinet. A Schema is simply the structural blueprint or design layout of your tables.
CREATE DATABASE: Sets up a brand new filing cabinet on your computer. Adding IF NOT EXISTS is a safety trick so your script won't crash if the cabinet is already there.
DROP DATABASE: Deletes the entire cabinet and everything inside it permanently.
Adding IF EXISTS: keeps things error-free if it's already missing.
USE: Tells SQL, "Open up this specific filing cabinet so I can work inside it."
SHOW DATABASES; / SHOW TABLES;: Quick lookups to see a list of all cabinets or individual folders you have created.
Pro-Tip for Software (like MySQL Workbench): When you type these in a workbench editor, remember to highlight the specific line you want to run before hitting execute, and click the Refresh button in the sidebar to see your new databases pop up!

2. Table Lifecycles (Drop vs. Truncate)

  • DROP TABLE: Wipes out the data rows and destroys the physical table design itself. The table ceases to exist.
  • TRUNCATE TABLE: The fast clean-up tool. It completely empties out all the data rows inside the table but leaves the empty grid structure intact so you can insert new records into it immediately.

3. Data Types & Rules (Constraints)

Columns need clear descriptions and strict rules so bad data can't sneak in.
INT: Stores standard whole numbers (like 101, 25, 50000).
VARCHAR(50): Variable Character text up to 50 letters long (like names or cities).
PRIMARY KEY: The ultimate unique identifier for a row (like a student roll number). It forces values to be unique and not null (never empty). If one column isn't enough, you can write PRIMARY KEY (ID, name) to stitch two columns together as a single composite key.
 NOT NULL: A rule saying you cannot leave this spot blank when entering data.
DEFAULT: An automated placeholder. If you don't mention a value (like an employee's salary), SQL automatically plugs in your default value (like 25000).
 CONSTRAINT CHECK: A logical security guard. For example, CHECK (age >= 18 AND city = "delhi") blocks any row from being saved if the person is too young or lives in a different city.

4. Linking Tables (Cascading Foreign Keys)

A Foreign Key links a column in a child table directly to a Primary Key in a parent table.
ON UPDATE CASCADE: In real life, if a company changes a customer's ID number in the main system, this rule automatically changes it across all order history logs slots instantly.
 ON DELETE CASCADE: If you delete a user's account, this rule automatically wipes out all their individual history logs so you aren't left with orphaned, broken data links.

5. Reading Data (The SELECT Statement Execution Order)

When you ask SQL to find data, the database engine ignores the physical layout you typed and reads your commands in a strict logical order:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

  7. LIMIT

  8. DISTINCT: Skips over duplicate rows to only show you completely unique individual values.

  9. Math Filters (WHERE): You can use basic math (+, -, *, /) alongside comparison rules (=, != for not equal, >, <).
  10. % (Modulus): This calculates the remaining balance after a division. In production environments, engineers write WHERE id % 2 = 0 to easily pull out even-numbered rows for processing systems.
  11. Data Set Filters: BETWEEN 80 AND 90 targets a closed range. IN ("delhi", "mumbai") acts as a quick checklist filter.
  12. Aggregate Functions: MIN(), MAX(), AVG(), and COUNT() summarize giant columns of data into a single descriptive number.
  13. GROUP BY vs HAVING: GROUP BY bundles similar matching rows together (like grouping students by their hometown city). Use WHERE to filter single rows before grouping happens. Use HAVING to filter summary results after grouping happens.
  14. ORDER BY: Sorts your data columns (ASC for ascending A-Z, DESC for descending Z-A).
  15. LIMIT: Prevents database overload by capping how many rows come back (e.g., LIMIT 3 only returns the first three rows).

6. Subqueries & Virtual Views

  • Subqueries: Nesting a query inside a query. SQL solves the inner statement first (like finding the class average score) and uses that answer to run the outer query (finding students who scored higher than that average).
  • VIEWS: A virtual shortcut. If you write a long, confusing query that you need to run every day, you can save it as a View. It doesn't duplicate any data on your disk; it just stores the recipe so you can query it like a standard table anytime.

7. Joining Tables & Nicknames (Aliases)

When you link tables together, your script uses the layout: FROM table1 AS short-table-name1 JOIN table2 AS short-table-name2 ON stn1.col = stn2.col

Giving your tables temporary short nicknames (AS short-table-name1) saves you from typing out giant table names over and over again when defining matching columns (ON stn1.col = stn2.col).

  • INNER JOIN: Only returns records where the ID matches perfectly in both tables.
  • LEFT JOIN: Keeps every row from your left table (short-table-name1), and pulls matching details from the right. If there's no match, it leaves the right columns blank (NULL).
  • RIGHT JOIN: Keeps every row from your right table (short-table-name2), pulling matches from the left.
  • UNION [FULL JOIN]: MySQL doesn't have a direct "Full Join" command keyword. To see everything from both tables combined, you copy-paste a LEFT JOIN query and a RIGHT JOIN query, and link them together with a UNION keyword.
  • Anti-Joins (IS NULL - The Opposite of Inner Join): An Inner Join only shows rows where things match. If you want to see the exact opposite—rows that have zero matches—you add WHERE short-table-name2.col IS NULL to your outer join. This lets you immediately track missing data links, like finding students who haven't registered for any classes.
  • Self Join: Joining a table to its own layout using shortcuts (FROM table AS a JOIN table AS b). This is crucial in the workplace for tracking hierarchy data inside a single table grid, like matching an employee's row to their supervisor's row.

8. Stacking Results (UNION vs UNION ALL)

  • UNION: Glues two query results vertically on top of each other and filters out any duplicate lines.
  • UNION ALL: Glues two results sets together directly without checking for duplicates. This is much faster in production because the system doesn't waste time sorting or deduplicating.

9. Managing Data (UPDATE & DELETE)

  • SET sql_safe_updates = 0;: Safety systems block updates that don't pinpoint a single specific Primary Key ID so you don't accidentally wipe out your database. Flipping this to 0 turns off that protection layer so you can run bulk updates.
  • UPDATE / SET / WHERE: Changes specific information inside existing rows.
  • DELETE FROM: Safely drops rows that match your strict criteria.

10. Modifying Table Design (ALTER TABLE)

If your application grows and you need to rewrite the structural design of your database blueprint without losing data, use ALTER:
ADD COLUMN / DROP COLUMN: Stitches on a brand new data column or cuts away an old one.
RENAME TO: Gives the table a completely new name.
CHANGE COLUMN: Renames a column and shifts its data type layout.
MODIFY: Redefines rules or properties (like default values) without renaming the column itself.

Troubleshooting & System Codes

  • Error Reference Numbers: If a query fails, SQL gives you a specific error number. For example, Error 1064 means a punctuation typo or grammar mistake occurred, while Error 1052 means two joined tables have the exact same column name and SQL doesn't know which one you want. You can instantly search these error numbers online to find the exact fix.
  • Common Typos: Double-check that you highlighted your text completely, ran USE database_name; at the start of your session, and spelled table names exactly right (forgetting a letter like typing FROM studen instead of FROM student1 will instantly stop your query).