Introduction

Structured Query Language (SQL) is a powerful tool used for managing and manipulating data in relational databases. Developed by IBM, SQL is designed for querying, updating, and managing data efficiently. This guide will cover essential SQL concepts and queries with illustrative examples to help you understand how SQL operates in various scenarios.

Overview of SQL

SQL is a domain-specific language used for interacting with relational databases. It enables users to perform a wide range of operations, from simple data retrieval to complex manipulations. Popular database management systems such as MySQL, PostgreSQL, and SQLite rely on SQL for their querying capabilities.

Key SQL Queries and Operations

1. Display Existing Databases

To view all databases available in your SQL environment, use:

SHOW DATABASES;

Example Output:

Database Name
---------------
information_schema
mysql
performance_schema
testdb
sales
hr

2. Delete a Database

To remove a database named testdb, execute:

DROP DATABASE testdb;

Verify the deletion with:

SHOW DATABASES;

Example Output:

Database Name
---------------
information_schema
mysql
performance_schema
sales
hr

3. Create a New Database

To create a database named CustomerOrders, use:

CREATE DATABASE CustomerOrders;

Check the databases:

SHOW DATABASES;

Example Output:

Database Name
---------------
CustomerOrders
information_schema
mysql
performance_schema
sales
hr

4. Select a Database

Switch to the CustomerOrders database:

USE CustomerOrders;

5. Create a Table

Define a new table orders with columns for order ID, customer name, product, and order date:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    product VARCHAR(100),
    order_date DATE
);

6. Show Tables in the Database

List all tables in the current database:

SHOW TABLES;

7. Delete a Table

To drop the orders table:

DROP TABLE orders;

8. Insert Data into a Table

Add individual records to the orders table:

INSERT INTO orders (order_id, customer_name, product, order_date)
VALUES (101, 'Alice Johnson', 'Laptop', '2024-08-15');

INSERT INTO orders (order_id, customer_name, product, order_date)
VALUES (102, 'Bob Smith', 'Desk Chair', '2024-08-16');

9. Retrieve Data from a Table

Fetch all records from the orders table:

SELECT * FROM orders;

Example Output:

order_id | customer_name | product    | order_date
-----------------------------------------------------
101      | Alice Johnson | Laptop     | 2024-08-15
102      | Bob Smith     | Desk Chair | 2024-08-16

10. Not Null Constraint

Specify that a column should not accept null values:

CREATE TABLE products (
    product_id INT NOT NULL,
    product_name VARCHAR(100),
    price DECIMAL(10, 2) NOT NULL
);

11. Unique Constraint

Ensure unique values in a column:

CREATE TABLE employees (
    emp_id INT UNIQUE,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

Core SQL Concepts

Primary Key

A PRIMARY KEY ensures unique, non-null values for a column:

CREATE TABLE users (
    user_id INT NOT NULL,
    username VARCHAR(50),
    email VARCHAR(100),
    CONSTRAINT pk_user_id PRIMARY KEY (user_id)
);

Foreign Key

A FOREIGN KEY establishes a relationship between tables:

CREATE TABLE orders (
    order_id INT NOT NULL,
    customer_id INT,
    amount DECIMAL(10, 2),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Order By

Sort results in ascending or descending order:

SELECT * FROM orders ORDER BY order_date DESC;

Example Output:

order_id | customer_name | product    | order_date
-----------------------------------------------------
102      | Bob Smith     | Desk Chair | 2024-08-16
101      | Alice Johnson | Laptop     | 2024-08-15

Group By

Group results based on one or more columns:

SELECT product, COUNT(*) as total_orders
FROM orders
GROUP BY product;

Example Output:

product    | total_orders
---------------------------
Laptop     | 1
Desk Chair | 1

Join Operations

• LEFT JOIN: Returns all records from the left table and matched records from the right table: SELECT employees.name, departments.dept_name FROM employees LEFT JOIN departments ON employees.dept_id = departments.dept_id;
• RIGHT JOIN: Returns all records from the right table and matched records from the left table: SELECT employees.name, departments.dept_name FROM employees RIGHT JOIN departments ON employees.dept_id = departments.dept_id;
• INNER JOIN: Returns matching records from both tables: SELECT employees.name, departments.dept_name FROM employees INNER JOIN departments ON employees.dept_id = departments.dept_id;
• FULL JOIN: Returns all records from both tables: SELECT employees.name, departments.dept_name FROM employees FULL OUTER JOIN departments ON employees.dept_id = departments.dept_id; Note: FULL JOIN might not be supported in all SQL versions.

Self Join

Join a table with itself:

SELECT a.name AS employee, b.name AS manager
FROM employees a, employees b
WHERE a.manager_id = b.emp_id;

Where Clause

Filter results based on a condition:

SELECT * FROM orders WHERE amount > 100;

Having Clause

Filter groups based on an aggregate condition:

SELECT product, SUM(amount) as total_sales
FROM orders
GROUP BY product
HAVING SUM(amount) > 500;

Conclusion

Understanding SQL concepts and queries is crucial for managing and analyzing data effectively. By mastering these SQL operations and concepts, you can perform a wide range of data manipulation tasks and extract valuable insights from your databases.