SQL 101

Key SQL Categories

• DDL (Data Definition Language): Commands that control the structure of a database and its objects.

  • CREATE: Creates databases, tables, indexes, etc.
  • ALTER: Modifies the structure of existing objects.
  • DROP: Deletes databases, tables, and other objects.
  • TRUNCATE: Quickly removes all data from a table.

• DQL (Data Query Language): Commands for retrieving and filtering data from databases.

  • SELECT: Fetches data from one or more tables.

• DML (Data Manipulation Language): Commands for changing data within tables.

  • INSERT: Adds new rows of data to a table.
  • UPDATE: Modifies existing data in a table.
  • DELETE: Removes rows from a table.

• DCL (Data Control Language): Commands to manage database permissions and user access.

  • GRANT: Provides specific permissions to users.
  • REVOKE: Removes permissions from users.

• TCL (Transaction Control Language): Commands for managing groups of SQL statements as a single unit (transactions).

  • COMMIT: Makes changes permanent in a database.
  • ROLLBACK: Reverts changes made during a transaction.
  • SAVEPOINT: Sets points within a transaction to potentially roll back to.

Operators

• Arithmetic Operators: (+, -, *, /) for calculations.
• Comparison Operators: (>, <, =, >=, <=, !=) for comparing values.
• Logical Operators: (AND, OR, NOT) for combining conditions.
• Other Operators:
  • LIKE: For pattern matching in strings.
  • IN: To check if a value exists within a set.
  • BETWEEN: To check if a value is within a range.

Functions

• Aggregate Functions: Perform calculations on multiple rows, returning a single result:
  • COUNT()
  • MIN()
  • MAX()
  • AVG()
  • SUM()
• String Functions: Manipulate text data:
  • UPPER()
  • LOWER()
  • CONCAT()
  • SUBSTRING()
  • TRIM()
• Date and Time Functions: Work with dates and times:
  • NOW()
  • CURDATE()
  • MONTH()
  • YEAR()
• Other Functions: Conversion functions, mathematical functions, etc.

Data Types

(Data types can vary slightly between different database systems)

• Numeric:
  • INT/INTEGER (whole numbers)
  • FLOAT/DECIMAL (numbers with decimal points)
• String:
  • CHAR (fixed-length strings)
  • VARCHAR (variable-length strings)
  • TEXT (larger text fields)
• Date and Time:
  • DATE
  • TIME
  • DATETIME
  • TIMESTAMP
• Boolean: (TRUE or FALSE)

How to Learn SQL

1. Choose a Resource: There are many excellent options:
   • Online Courses: Platforms like Codecademy, Udemy, Coursera, Khan Academy.
   • Interactive Tutorials: W3Schools, SQLZoo, TutorialsPoint.
   • Books: Books tailored to your preference.
2. Database Choice: Select a common database system to practice with:
   • MySQL
   • PostgreSQL
   • Microsoft SQL Server
   • SQLite
3. Practice, Practice, Practice:
   • Find datasets online or create your own simple tables.
   • Run queries, experiment, and solidify your understanding.