SQL Statements: Your Guide to Structured Query Language

Welcome to our comprehensive guide on SQL Statements! In this lesson, we'll be diving into the world of Structured Query Language, a powerful tool used for managing and manipulating databases.

🎯 What is SQL? SQL (Structured Query Language) is a language designed for managing relational databases. It's like a universal language that allows us to communicate with various types of databases.

Getting Started

Before we dive into SQL statements, let's first set up a simple SQL database. We'll create a database named myDB and a table named employees.

sql

CREATE DATABASE myDB;
USE myDB;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary DECIMAL(10, 2)
);

📝 Note: This table contains fields for an employee's ID, first name, last name, email, and salary. Each field has a specific data type: INT, VARCHAR, and DECIMAL.

SQL Statements Overview

SQL statements are divided into two main categories: Data Definition Language (DDL) and Data Manipulation Language (DML).

Data Definition Language (DDL)

DDL statements are used to create, modify, and delete database objects such as tables, views, and indexes. Here are some common DDL statements:

CREATE DATABASE: Create a new database.
CREATE TABLE: Create a new table.
ALTER TABLE: Modify the structure of an existing table.
DROP TABLE: Delete an existing table.

Data Manipulation Language (DML)

DML statements are used to retrieve, insert, update, and delete data from tables. Here are some common DML statements:

SELECT: Retrieve data from a table.
INSERT INTO: Insert new data into a table.
UPDATE: Update existing data in a table.
DELETE: Delete data from a table.

SELECT Statement

The SELECT statement is one of the most important SQL statements. It's used to retrieve data from one or more tables.

sql

SELECT column1, column2, ...
FROM table_name;

💡 Pro Tip: You can use the * wildcard to select all columns from a table.

WHERE Clause

The WHERE clause is used to filter the data returned by the SELECT statement.

sql

SELECT column1, column2, ...
FROM table_name
WHERE condition;

ORDER BY Clause

The ORDER BY clause is used to sort the data returned by the SELECT statement.

sql

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC | DESC];

Quiz

Practice Exercise

Create a new table customers with columns id, first_name, last_name, email, and age.
Insert some sample data into the customers table.
Retrieve all data from the customers table and sort it by the age column in ascending order.
Filter the data to only show customers with an age greater than 30.

Solutions can be found at the end of this lesson.

Conclusion

In this lesson, we've covered the basics of SQL statements, focusing on DDL and DML statements. We've also learned about the SELECT, WHERE, and ORDER BY clauses. Now, you're ready to dive deeper into SQL and start managing your own databases!

💡 Pro Tip: Practice is key when learning SQL. Try to work on real-world projects to solidify your understanding.


Solutions for Practice Exercise:

1.
```sql
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    age INT
);

sql

INSERT INTO customers (first_name, last_name, email, age)
VALUES ('John', 'Doe', 'john.doe@example.com', 32),
       ('Jane', 'Smith', 'jane.smith@example.com', 25),
       ('Alice', 'Johnson', 'alice.johnson@example.com', 28);

sql

SELECT * FROM customers ORDER BY age ASC;

sql

SELECT * FROM customers WHERE age > 30 ORDER BY age ASC;