SQL| Online Course| SQL Database| Programming| MYSQL| Server

Master the Language of Data

SQL is the standard language for storing, manipulating, and retrieving data in databases. Learn how to harness its power.

SQL Query Editor

SELECT 
    customers.name, 
    SUM(orders.amount) AS total_spent
FROM customers
JOIN orders 
    ON customers.id = orders.customer_id
WHERE orders.date >= '2023-01-01'
GROUP BY customers.name
HAVING SUM(orders.amount) > 1000
ORDER BY total_spent DESC
LIMIT 10;

Query Result (10 rows)

Name

Acme Corp

TechGiant Inc

Global Services

Total Spent

$8,942.50

$7,215.30

$5,621.75

What is SQL?

Structured Query Language: The foundation of data management

The Language of Databases

SQL (Structured Query Language) is a standardized programming language specifically designed for managing and manipulating relational databases. First developed in the 1970s, SQL has become the universal language for database management systems (DBMS).

What SQL Can Do:

Do You Know?

Despite being over 50 years old, SQL remains the dominant database language, with modern variants like NoSQL databases often incorporating SQL-like query capabilities.

Relational Model

Data organized in tables with relationships between them, enabling complex data structures.

Data Integrity

Enforces rules to maintain accuracy and consistency of data through constraints.

Standardized

ANSI/ISO standard ensures consistency across different database systems.

Powerful Queries

Complex data retrieval with filtering, sorting, and aggregation capabilities.

Essential SQL Commands

The building blocks of database interaction

Data Definition Language (DDL)

Commands that define and modify database structures like tables and indexes.

Creates a new database object like a table, view, or index.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

Modifies an existing database object.

ALTER TABLE employees
ADD email VARCHAR(100);

deletes an existing database

DROP TABLE employees;

deletes an existing database

TRUNCATE TABLE employees;

Data Manipulation Language (DML)

Commands that manipulate data stored within database objects.

Retrieves data from one or more tables.

SELECT name, department, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

Creates a new database object like a table, view, or index.

INSERT INTO employees (id, name, department, salary)
VALUES (101, 'John Smith', 'Marketing', 65000.00);

Creates a new database object like a table, view, or index.

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Marketing';

Deletes the records from the table

DELETE FROM employees
WHERE department = 'Sales' 
AND salary < 30000;

Data Control Language (DCL)

Commands that control access to data within the database.

Gives specific privileges to users.

GRANT SELECT, INSERT ON employees
TO user_role;

Modifies an existing database object.

REVOKE DELETE ON employees
FROM user_role;

Transaction Control Language (TCL)

Commands that manage changes made by DML statements.

Saves all changes made in the current transaction.

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

Modifies an existing database object.

BEGIN;
UPDATE inventory SET quantity = quantity - 10;
-- Oops, that's wrong!
ROLLBACK;

deletes an existing database

BEGIN;
UPDATE products SET price = price * 1.05;
SAVEPOINT price_update;
DELETE FROM products WHERE stock = 0;
ROLLBACK TO price_update;
COMMIT;

Practical examples for real-world scenarios

Scenario: Retrieving Customer Information

Find all customers from New York with a credit limit over $10,000, sorted by name.

-- Table structure
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50),
    state VARCHAR(2),
    credit_limit DECIMAL(10,2)
);

-- The query
SELECT customer_id, name, credit_limit
FROM customers
WHERE state = 'NY'
AND credit_limit > 10000
ORDER BY name;

Result:

Working with Joins

Scenario: Retrieving Customer Information

Retrieve all orders with customer details, including orders without assigned customers.

-- Table structures
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- The query using LEFT JOIN
SELECT o.order_id, o.order_date, o.total_amount,
       c.customer_id, c.name, c.email
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;

Result:

Aggregation and Grouping

Scenario: Sales Analysis by Product Category

Calculate total sales, average price, and number of orders for each product category.

-- Table structures
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- The aggregation query
SELECT p.category,
       COUNT(DISTINCT oi.order_id) AS total_orders,
       SUM(oi.quantity * oi.price) AS total_sales,
       AVG(p.price) AS average_price,
       MAX(p.price) AS highest_price
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.category
HAVING SUM(oi.quantity * oi.price) > 10000
ORDER BY total_sales DESC;

Result:

Popular SQL Database Systems

Comparing the most widely used SQL database management systems

MySQL

The world’s most popular open-source relational database, known for its reliability, ease of use, and performance.

Best for: Web applications, small to medium businesses

Owned by: Oracle Corporation

PostgreSQL

A powerful, open-source object-relational database system with a strong reputation for reliability, feature robustness, and performance.

Best for: Complex queries, data warehousing

Maintained by: PostgreSQL Global Development Group

SQL Server

Microsoft’s enterprise-class relational database management system, offering comprehensive data management and business intelligence tools.

Best for: Enterprise applications, Windows environments

Owned by: Microsoft

Oracle Database

A multi-model database management system known for its reliability in handling large-scale enterprise applications and data warehousing.

Best for: Large enterprises, mission-critical applications

Owned by: Oracle Corporation

SQLite

A self-contained, serverless, zero-configuration, transactional SQL database engine designed to be embedded into applications.

Master the Language of Data

SQL Query Editor

What is SQL?

The Language of Databases

What SQL Can Do:

Do You Know?

Relational Model

Data Integrity

Standardized

Powerful Queries

Essential SQL Commands

Data Definition Language (DDL)

CREATE

ALTER

drop

TRUNCATE

Data Manipulation Language (DML)

SELECT

INSERT

UPDATE

DELETE

Data Control Language (DCL)

GRANT

REVOKE

Transaction Control Language (TCL)

COMMIT

ROLLBACK

SAVEPOINT

Practical examples for real-world scenarios

Scenario: Retrieving Customer Information

Working with Joins

Scenario: Retrieving Customer Information

Result:

Aggregation and Grouping

Scenario: Sales Analysis by Product Category

Popular SQL Database Systems

MySQL

PostgreSQL

SQL Server

Oracle Database

SQLite

Additional Services

Quick Links