20 essential SQL commands and clauses

Posted on by

Data Manipulation: Reading and Modifying Data

1. SELECT

  • What it does: Retrieves data from one or more tables.

  • Why it’s important: It is the most fundamental and frequently used SQL command. Without it, you cannot view the information stored in your database.

  • Real-life example: An e-commerce site uses SELECT to display a list of all products currently in stock to a customer browsing the catalog.

2. INSERT INTO

  • What it does: Adds new rows of data to a table.

  • Why it’s important: It is the primary way new information enters your system.

  • Real-life example: When a new user creates an account on a social media app, an INSERT command saves their username, email, and password hash into the “Users” table.

3. UPDATE

  • What it does: Modifies existing data within a table.

  • Why it’s important: Data changes over time. This command ensures your database reflects the most current reality without having to delete and recreate records.

  • Real-life example: A banking application uses UPDATE to change a customer’s account balance after they make a withdrawal.

4. DELETE

  • What it does: Removes one or more rows from a table.

  • Why it’s important: Essential for removing obsolete, incorrect, or canceled records to save space and maintain data accuracy.

  • Real-life example: A healthcare system uses DELETE to remove an appointment record when a patient calls to cancel it.

Data Definition: Structuring the Database

5. CREATE

  • What it does: Builds a new database, table, index, or view.

  • Why it’s important: It sets up the actual architecture and containers where your data will live.

  • Real-life example: A developer launching a new blog uses CREATE TABLE to set up a structure with columns for “Post Title”, “Author”, “Publish Date”, and “Content”.

6. ALTER

  • What it does: Modifies the structure of an existing database object (like adding or dropping a column from a table).

  • Why it’s important: Business requirements evolve. ALTER lets you adapt your database schema without losing the existing data.

  • Real-life example: An HR platform adds a “Pronouns” column to an existing “Employee” table using the ALTER command.

7. DROP

  • What it does: Permanently deletes an entire database, table, or index, along with all its data.

  • Why it’s important: Used to clean up old, unused structures that are taking up server resources.

  • Real-life example: A company migrating to a completely new software system might use DROP TABLE on the legacy tables once the migration is fully verified.

8. TRUNCATE

  • What it does: Quickly removes all rows from a table, but leaves the table structure intact.

  • Why it’s important: It is much faster and uses fewer system resources than using DELETE to clear out a massive table.

  • Real-life example: A weather monitoring system uses TRUNCATE at midnight on the first of the month to clear out temporary daily logs after they’ve been archived elsewhere.

Filtering and Sorting: Refining Your Queries

9. WHERE

  • What it does: Filters records based on specific conditions.

  • Why it’s important: Databases contain millions of rows. WHERE allows you to isolate the exact specific data you need.

  • Real-life example: A customer service rep uses a WHERE clause to find an order using a specific tracking number (WHERE tracking_number = '12345').

10. ORDER BY

  • What it does: Sorts the result set of a query in ascending (ASC) or descending (DESC) order.

  • Why it’s important: Organizes raw data into a readable, logical format for the end user.

  • Real-life example: A real estate website uses ORDER BY price DESC to show the most expensive houses at the top of the search results.

11. GROUP BY

  • What it does: Groups rows that have the same values into summary rows (often used with aggregate functions like COUNT, MAX, SUM).

  • Why it’s important: Crucial for data analysis and generating reports.

  • Real-life example: A retail manager uses GROUP BY store_location alongside a SUM(sales) function to see total daily revenue for each branch.

12. HAVING

  • What it does: Filters records after they have been grouped by the GROUP BY clause (since WHERE cannot be used with aggregate functions).

  • Why it’s important: Allows you to set conditions on summarized data.

  • Real-life example: Using the retail example above, adding HAVING SUM(sales) > 10000 filters the report to only show store branches that made over $10,000 that day.

13. DISTINCT

  • What it does: Returns only distinct (different) values, removing duplicates from the result set.

  • Why it’s important: Useful for finding the unique categories or types of data within a massive column.

  • Real-life example: A marketing team uses SELECT DISTINCT country on their subscriber list to see exactly which countries their readers are from, without seeing the same country listed 5,000 times.

14. LIMIT (or TOP / FETCH FIRST)

  • What it does: Restricts the number of rows returned by a query.

  • Why it’s important: Improves performance by not loading unnecessary data, especially when you only need a sample or the “top X” results.

  • Real-life example: A gaming leaderboard query uses ORDER BY score DESC LIMIT 10 to display only the top 10 highest-scoring players.

Combining Data: Joins and Sets

15. INNER JOIN

  • What it does: Combines rows from two or more tables based on a related column between them, returning only records that have matching values in both tables.

  • Why it’s important: Relational databases split data into multiple tables to reduce redundancy. Joins are how you stitch that data back together into a meaningful picture.

  • Real-life example: An app combines a “Students” table and a “Classes” table using an INNER JOIN to generate a report showing only students who are actively enrolled in a class.

16. LEFT JOIN

  • What it does: Returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL from the right side.

  • Why it’s important: Vital for finding “orphaned” data or seeing a complete list of a primary entity regardless of whether they have secondary actions.

  • Real-life example: An e-commerce site uses a LEFT JOIN to show a list of all registered users, alongside their recent purchases. Users who haven’t bought anything yet still show up, but with a blank purchase history.

17. UNION

  • What it does: Combines the result sets of two or more SELECT statements into a single column output.

  • Why it’s important: Useful for consolidating similar data that lives in completely different tables.

  • Real-life example: A global company has a “North_America_Staff” table and a “Europe_Staff” table. They use UNION to pull a single, combined list of all employee email addresses.

Security and Transactions: Control and Safety

18. GRANT

  • What it does: Gives specific user accounts permission to perform certain actions (like SELECT or UPDATE) on specific database objects.

  • Why it’s important: The backbone of database security, ensuring users can only access or change what they are authorized to.

  • Real-life example: A database administrator uses GRANT SELECT ON payroll_table TO 'finance_team' so the finance department can read salary data, but cannot alter it.

19. COMMIT

  • What it does: Permanently saves all changes made during the current database transaction.

  • Why it’s important: Ensures that a series of interconnected database actions all succeed together before making them permanent, preventing partial data updates.

  • Real-life example: When you transfer money online, the database removes money from your account and adds it to another. COMMIT is called at the very end to finalize both actions simultaneously.

20. ROLLBACK

  • What it does: Undoes transactions that have not yet been saved to the database.

  • Why it’s important: Acts as a fail-safe. If an error occurs halfway through a complex multi-step update, ROLLBACK reverts the database to its previous stable state.

  • Real-life example: In the money transfer example above, if the system successfully deducts your money but the receiving bank’s server crashes before the money is deposited, ROLLBACK is triggered to refund your account as if the transaction never started.