10 SQL Statements That Can Handle 90% of Tasks

Structured Query Language (SQL) is a powerful tool for managing and querying relational databases. Whether you’re a beginner or an experienced data professional, there are certain SQL statements that you’ll find yourself using over and over again. In this article, we’ll cover 10 essential SQL statements that can handle 90% of your database tasks, along with code examples.

1. Introduction

Why SQL is Important

SQL is the standard language for interacting with relational databases. It allows you to perform various tasks, from retrieving data to modifying database structures. Understanding SQL is essential for anyone working with data, as it provides a foundation for data analysis, reporting, and application development.

2. SELECT Statement

Retrieving Data

The SELECT statement is used to retrieve data from one or more tables. You can specify which columns to retrieve and add conditions to filter the results.

-- Retrieve all columns from a table
SELECT * FROM employees;

-- Retrieve specific columns
SELECT first_name, last_name FROM employees;

-- Add conditions to filter the results
SELECT product_name, price FROM products WHERE price > 50;

3. INSERT INTO Statement

Adding New Data

The INSERT INTO statement allows you to add new rows of data to a table.

-- Insert a single row
INSERT INTO customers (first_name, last_name, email) VALUES ('John', 'Doe', '[email protected]');

-- Insert multiple rows
INSERT INTO orders (order_date, total_amount) VALUES
    ('2023-01-15', 150.00),
    ('2023-01-16', 220.50),
    ('2023-01-17', 75.25);

4. UPDATE Statement

Modifying Existing Data

The UPDATE statement is used to modify existing data in a table.

-- Update a single row
UPDATE products SET price = 25.99 WHERE product_id = 101;

-- Update multiple rows
UPDATE employees SET manager_id = 105 WHERE department = 'Sales';

5. DELETE Statement

Removing Data

The DELETE statement is used to remove rows from a table.

-- Delete a single row
DELETE FROM customers WHERE customer_id = 201;

-- Delete all rows that meet a condition
DELETE FROM orders WHERE order_date < '2023-01-15';

6. CREATE TABLE Statement

Creating New Tables

The CREATE TABLE statement is used to create a new table with specified columns and data types.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    price DECIMAL(10, 2)
);

7. ALTER TABLE Statement

Modifying Tables

The ALTER TABLE statement allows you to modify an existing table by adding, modifying, or deleting columns.

-- Add a new column
ALTER TABLE employees ADD COLUMN hire_date DATE;

-- Modify column data type
ALTER TABLE customers ALTER COLUMN phone_number VARCHAR(15);

8. DROP TABLE Statement

Deleting Tables

The DROP TABLE statement is used to delete an existing table and all its data.

-- Delete a table
DROP TABLE products;

9. WHERE Clause

Filtering Data

The WHERE clause is used to filter rows based on specified conditions.

-- Retrieve products with a price greater than 50
SELECT product_name, price FROM products WHERE price > 50;

-- Retrieve employees in the Sales department
SELECT first_name, last_name FROM employees WHERE department = 'Sales';

10. JOIN Clause

Combining Data from Multiple Tables

The JOIN clause is used to combine rows from two or more tables based on a related column between them.

-- Retrieve customer names and their orders
SELECT c.first_name, c.last_name, o.order_date
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id;

11. GROUP BY Clause

Aggregating Data

The GROUP BY clause is used to group rows that have the same values in specified columns, often used with aggregate functions like SUM and COUNT.

-- Calculate total sales per product
SELECT product_id, SUM(quantity * price) AS total_sales
FROM order_details
GROUP BY product_id;

12. Conclusion

Mastering the Basics

These 10 SQL statements cover the majority of tasks you’ll encounter when working with relational databases. By understanding and becoming proficient with these statements, you’ll have a solid foundation for database management and data manipulation. SQL is a versatile language, and as you continue to explore its capabilities, you’ll unlock even more powerful ways to work with data.

SQL Fundamentals

Thank you for your time and interest! 🚀 You can find even more content at SQL Fundamentals 💫

Stackademic

Thank you for reading until the end. Before you go:

• Please consider clapping and following the writer! 👏
• Follow us on Twitter(X), LinkedIn, and YouTube.
• Visit Stackademic.com to find out more about how we are democratizing free programming education around the world.