SQL Syntax and Types of SQL Statements

Structured Query Language (SQL) is a powerful domain-specific language designed for managing, manipulating, and querying relational databases. It provides a standardized way for programmers and database administrators to interact with databases, allowing them to define, manipulate, and control data stored in a relational database management system (RDBMS).

SQL is essential for handling tasks such as data retrieval, insertion, updating, and deletion within a database.

This guide will provide you with the knowledge of SQL Syntax and types of SQL statements, needed to handle tasks like data retrieval, insertion, updating, and deletion within any database

SELECT Statement

The SELECT statement in SQL is fundamental for retrieving data from one or more tables. Its basic syntax is as follows:

--Basic Syntax

SELECT column1, column2, column3 ...
FROM table_name;

This simple SELECT statement retrieves specified columns from a table. Let’s break down the components:

• SELECT: Indicates the start of the SELECT statement.
• column1, column2, …: Specifies the columns you want to retrieve.
• FROM: Specifies the table from which to retrieve data.
• table_name: Represents the name of the table you’re querying.

Retrieving All Columns

If you want to retrieve all columns from a table, you can use the asterisk (*) wildcard:

SELECT *
FROM table_name;

This retrieves all columns from the specified table. Let’s see with an example –

-- Retrieve all columns from the 'products' table
SELECT *
FROM products;

Filtering Results with WHERE Clause

To filter results based on specific conditions in SQL, we use the WHERE clause:

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

The condition is an expression that determines which rows to include in the result set. Let’s see it with an example –

-- Retrieve orders with a total amount greater than $1000 from the 'orders' table
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > 1000;

Sorting Results with ORDER BY

You can sort the results in ascending or descending order using the ORDER BY clause:

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

ASC is for ascending order (default), and DESC is for descending order. Let’s see it with an example –

-- Retrieve product names and prices from the 'products' table, ordered by price in descending order
SELECT product_name, price
FROM products
ORDER BY price DESC;

Limiting Results with LIMIT/OFFSET

To limit the number of rows returned or skip a certain number of rows, you can use LIMIT and OFFSET:

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows
OFFSET offset_value;

These clauses are particularly useful for pagination and optimizing query performance. Let’s see it with an example –

-- Retrieve 10 employee names and salaries from the 'employees' table
SELECT employee_name, salary
FROM employees
LIMIT 10;

Now, let’s move on to: Update Statement.

Update Statement

The UPDATE statement in SQL is used to modify existing records in a table. The basic syntax is as follows:

--Basic Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• UPDATE: Specifies the start of the UPDATE statement.
• table_name: The name of the table to be updated.
• SET: Indicates the columns to be updated along with their new values.
• column1 = value1, column2 = value2, …: Specifies the columns and their new values.
• WHERE condition: Defines the condition to identify which records to update. If omitted, all records in the table will be updated.

Updating Single and Multiple Rows

You can update a single row or multiple rows at once by adjusting the WHERE clause. Here are examples for both scenarios:

Updating a Single Row:

-- Update the department for an employee with ID 101
UPDATE employees
SET department = 'Sales'
WHERE department = 'Marketing' and employee_id = 101; 

Updating Multiple Rows:

-- Update the department for all employees in the 'Marketing' department
UPDATE employees
SET department = 'Sales'
WHERE department = 'Marketing';

Using WHERE Clause for Conditional Updates

The WHERE clause is crucial for performing conditional updates. It allows you to specify which records should be updated based on certain conditions. Here’s an example:

-- Update the product price for all products with a stock quantity less than 50
UPDATE products
SET price = price * 1.1  -- let's Increase the price by 10%
WHERE stock_quantity < 50;

In this example, the WHERE clause ensures that only products with a stock quantity less than 50 will have their prices updated.

Using UPDATE with SELECT Statement

You can use the result of a SELECT statement to update records in another table. This is often useful when you want to update a column in a table based on the values derived from another table.

-- Update the discount percentage for products based on their category
UPDATE products
SET discount_percentage = 15
WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

In this example, the discount_percentage for products in the ‘Electronics’ category is being updated to 15%.

Using UPDATE with JOIN

You can also use the UPDATE statement with a JOIN operation to update records based on related information in another table.

-- Update the department manager for employees based on information from the 'departments' table
UPDATE employees
SET manager_id = managers.manager_id
FROM managers
JOIN departments ON employees.department_id = departments.department_id
WHERE employees.employee_id = 105;

In this example, the department manager for the employee with ID 105 is updated based on information from the ‘managers’ table and the relationship defined by the ‘departments’ table.

Now, let’s proceed to: Delete Statement

Delete Statement

The DELETE statement in SQL is used to remove one or more records from a table. The basic syntax is as follows:

--Basic Syntax

DELETE FROM table_name
WHERE condition;

• DELETE FROM: Specifies the start of the DELETE statement.
• table_name: The name of the table from which records will be deleted.
• WHERE condition: Optional. Defines the condition to identify which records to delete. If omitted, all records in the table will be deleted.

Deleting Single and Multiple Rows

You can delete a single row or multiple rows by adjusting the WHERE clause. Here are examples for both scenarios:

Deleting a Single Row:

-- Delete an employee with ID 102 from the 'employees' table
DELETE FROM employees
WHERE employee_id = 102;

Deleting Multiple Rows:

-- Delete all products with a stock quantity less than 10 from the 'products' table
DELETE FROM products
WHERE stock_quantity < 10;

Using WHERE Clause for Conditional Deletes

The WHERE clause is crucial for performing conditional deletes. It allows you to specify which records should be deleted based on certain conditions. Here’s an example:

-- Delete all orders that were placed before January 1, 2023, from the 'orders' table
DELETE FROM orders
WHERE order_date < '2024-01-01';

In this example, the WHERE clause ensures that only orders placed before January 1, 2023, will be deleted.

Going further now, let’s move on to: Create Table Statement

Create Table Statement

The CREATE TABLE statement in SQL is used to define a new table. The basic syntax is as follows:

--Basic Syntax

CREATE TABLE table_name (
   column1 datatype,
   column2 datatype,
   ...
   );

• CREATE TABLE: Specifies the start of the CREATE TABLE statement.
• table_name: The name of the table to be created.
• column1, column2, …: The columns of the table along with their data types.

Defining Columns and Data Types

Columns in a table must be defined along with their respective data types to specify the kind of data they can store. Here’s an example:

-- Create a 'customers' table with columns for customer information
CREATE TABLE customers (
   customer_id INT,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100),
   birthdate DATE
);

In this example, we create a table named ‘customers’ with columns such as customer_id, first_name, last_name, email, and birthdate, each with a specific data type.

Adding Constraints (Primary Key, Foreign Key, Unique, Check)

Constraints help define rules for the data stored in a table. Let’s look at examples for different types of constraints:

Primary Key:

-- Create a 'products' table with a primary key constraint on the 'product_id' column
CREATE TABLE products (
   product_id INT PRIMARY KEY,
   product_name VARCHAR(100),
   price DECIMAL(10, 2)
);

Foreign Key:

-- Create an 'order_items' table with a foreign key constraint referencing the 'products' table
CREATE TABLE order_items (
   order_item_id INT PRIMARY KEY,
   order_id INT,
   product_id INT,
   quantity INT,
   FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Unique Constraint:

-- Create a 'employees' table with a unique constraint on the 'email' column
CREATE TABLE employees (
   employee_id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100) UNIQUE,
   department_id INT
);

Check Constraint:

-- Create a 'students' table with a check constraint on the 'age' column
CREATE TABLE students (
   student_id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   age INT CHECK (age >= 18)
);

Example of Creating a Table

Now, let’s bring it all together in an example:

-- Create a 'orders' table with various columns and constraints
CREATE TABLE orders (
   order_id INT PRIMARY KEY,
   customer_id INT,
   order_date DATE,
   total_amount DECIMAL(12, 2),
   FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
   CHECK (total_amount >= 0)
);

In this example, we create a table named ‘orders’ with columns such as order_id, customer_id, order_date, and total_amount. The table includes a primary key constraint on order_id, a foreign key constraint referencing the ‘customers’ table, and a check constraint to ensure that the total_amount is non-negative.

Alter Table Statement

Adding Columns

The ALTER TABLE statement is used to modify an existing table. Adding columns is a common operation.

ALTER TABLE table_name
ADD COLUMN new_column_name datatype;

Here’s an example:

-- Add a new column 'phone_number' to the 'customers' table
ALTER TABLE customers
ADD COLUMN phone_number VARCHAR(15);

This adds a new column named ‘phone_number’ to the ‘customers’ table, allowing storage of customer phone numbers.

Modifying Columns

You can modify the data type or properties of an existing column.

ALTER TABLE table_name
ALTER COLUMN column_name new_datatype;

Example:

-- Modify the data type of the 'birthdate' column in the 'customers' table
ALTER TABLE customers
ALTER COLUMN birthdate TIMESTAMP;

This changes the data type of the ‘birthdate’ column from DATE to TIMESTAMP in the ‘customers’ table.

Dropping Columns

To remove a column from a table, you can use the DROP COLUMN clause.

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

-- Remove the 'phone_number' column from the 'customers' table
ALTER TABLE customers
DROP COLUMN phone_number;

This removes the ‘phone_number’ column from the ‘customers’ table.

Adding Constraints

You can add constraints to an existing table using the ALTER TABLE statement. Here’s an example of adding a unique constraint:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);

Example:

-- Add a unique constraint to the 'email' column in the 'employees' table
ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

This ensures that each email in the ’employees’ table is unique.

Removing Constraints

Conversely, you can remove constraints using the ALTER TABLE statement.

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example:

-- Remove the unique constraint on the 'email' column in the 'employees' table
ALTER TABLE employees
DROP CONSTRAINT unique_email;

This removes the unique constraint on the ’email’ column in the ’employees’ table.

Now, next is: Drop Table Statement.

Drop Table Statement

The DROP TABLE statement is used to permanently delete a table from the database. The basic syntax is as follows:

-- Basic Syntax

DROP TABLE table_name;

• DROP TABLE: Specifies the start of the DROP TABLE statement.
• table_name: The name of the table to be deleted.

Deleting a Table Permanently

To delete a table permanently, you can use the following syntax:

-- Permanently delete the 'old_customers' table
DROP TABLE old_customers;

This example permanently deletes the ‘old_customers’ table from the database.

But, be cautious when using the DROP TABLE statement, as it irreversibly removes the entire table and its data.

Going further now, let’s move on to: Create Database Statement.

Create Database Statement

The CREATE DATABASE statement in SQL is used to create a new database. The basic syntax is as follows:

-- Basic Syntax

CREATE DATABASE database_name;

• CREATE DATABASE: Specifies the start of the CREATE DATABASE statement.
• database_name: The name of the new database to be created.

Specifying Database Options

While creating a database, you can specify additional options such as character set and collation. Here’s an example:

-- Create a new database named 'company' with specific character set and collation
CREATE DATABASE company
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

In this example, the database named ‘company’ is created with the character set utf8mb4 and collation utf8mb4_general_ci.

Example of Creating a Database

Let’s bring it all together in an example:

-- Create a new database named 'bookstore' with default options
CREATE DATABASE bookstore;

This example creates a new database named ‘bookstore’ with default options. The database is initially empty and can be used to store tables, views, and other database objects.

Now, coming to: Drop Database Statement.

Drop Database Statement

The DROP DATABASE statement in SQL is used to permanently delete a database. The basic syntax is as follows:

-- Basic Syntax

DROP DATABASE database_name;

• DROP DATABASE: Specifies the start of the DROP DATABASE statement.
• database_name: The name of the database to be deleted.

Deleting a Database Permanently

To delete a database permanently, you can use the following syntax:

-- Permanently delete the 'old_database' database
DROP DATABASE old_database;

This example permanently deletes the ‘old_database’ database from the database server.

But, be cautious when using the DROP DATABASE statement, as it also irreversibly removes the entire database and all its contents.

let’s move to: Insert Into Statement.

Insert Into Statement

The INSERT INTO statement in SQL is used to add new records to a table. The basic syntax is as follows:

-- Basic Syntax

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

• INSERT INTO: Specifies the start of the INSERT INTO statement.
• table_name: The name of the table to which the data will be added.
• (column1, column2, …, columnN): Specifies the columns in the table where the data will be inserted.
• VALUES (value1, value2, …, valueN): Specifies the values to be inserted into the corresponding columns.

Inserting Single and Multiple Rows

Inserting a Single Row:

-- Insert a new employee into the 'employees' table
INSERT INTO employees (employee_id, first_name, last_name, department_id)
VALUES (105, 'John', 'Doe', 3);

Inserting Multiple Rows:

-- Insert multiple products into the 'products' table
INSERT INTO products (product_id, product_name, price)
VALUES
  (101, 'Laptop', 1200),
  (102, 'Smartphone', 800),
  (103, 'Tablet', 400);

Specifying Columns for Insert

You can explicitly specify the columns for which you’re providing values, allowing flexibility and clarity.

-- Insert a new order into the 'orders' table, specifying columns explicitly
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES (1001, 201, '2024-01-20', 1500.50);

Using VALUES and SELECT Clause

Using VALUES:

-- Insert a new product into the 'products' table using VALUES
INSERT INTO products (product_name, price)
VALUES ('Headphones', 50);

Using SELECT:

-- Insert data into the 'new_employees' table by selecting from the 'employees' table
INSERT INTO new_employees (employee_id, first_name, last_name, department_id)
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 4;

These examples illustrate how to use the INSERT INTO statement to add data to tables. Whether you’re inserting a single row, multiple rows, specifying columns explicitly, or using a SELECT clause, the INSERT INTO statement is fundamental for populating tables with data.

Going forward in the list is: Truncate Table Statement.

Truncate Table Statement

The TRUNCATE TABLE statement in SQL is used to remove all rows from a table, effectively resetting the table. The basic syntax is as follows:

-- Basic Syntax

TRUNCATE TABLE table_name;

• TRUNCATE TABLE: Specifies the start of the TRUNCATE TABLE statement.
• table_name: The name of the table from which all rows will be removed.

Removing All Rows from a Table

To remove all rows from a table using the TRUNCATE TABLE statement:

-- Remove all rows from the 'temp_data' table
TRUNCATE TABLE temp_data;

This example truncates the ‘temp_data’ table, removing all rows from the table while keeping its structure intact. Unlike the DELETE statement, which deletes rows one by one, TRUNCATE TABLE is more efficient for removing all rows from a table, especially when dealing with large datasets.

Coming next to: Describe Statement

Describe Statement

The DESCRIBE statement, also known as the DESC statement, is used to retrieve information about the structure of a table. The basic syntax varies among database systems, and in some databases like MySQL, it can be used as follows:

-- Basic Syntax

DESCRIBE table_name;

In other databases like PostgreSQL, you might use:

\d table_name;

• DESCRIBE table_name: Specifies the start of the DESCRIBE statement.
• table_name: The name of the table for which you want to retrieve structure information.

Retrieving Table Structure Information

To retrieve information about the structure of a table:

-- Retrieve information about the structure of the 'employees' table
DESCRIBE employees;

This example provides details about the columns, data types, and constraints of the ’employees’ table. The output typically includes information such as column name, data type, whether the column can be null, default values, and any constraints.

In PostgreSQL:

-- Retrieve information about the structure of the 'employees' table in PostgreSQL
\d employees;

Understanding how to use the DESCRIBE statement is valuable for exploring the structure of tables, especially when working with databases where you need to understand the details of columns and their properties.

let’s move to: Distinct Clause.

Distinct Clause

The DISTINCT clause in SQL is used to remove duplicate rows from the result set of a query. The basic syntax is as follows:

-- Basic Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;

• SELECT DISTINCT: Specifies the start of the SELECT statement with the DISTINCT clause.
• column1, column2, …: The columns for which you want to retrieve distinct values.
• FROM table_name: Specifies the table from which to retrieve the data.

Removing Duplicate Rows from Results

To retrieve distinct values from a specific column:

-- Retrieve distinct department names from the 'employees' table
SELECT DISTINCT department_name
FROM employees;

This example retrieves distinct department names from the ’employees’ table, eliminating duplicate entries.

-- Retrieve distinct product categories from the 'products' table
SELECT DISTINCT category
FROM products;

In this example, distinct product categories are retrieved from the ‘products’ table.

Lets move to: Commit Statement.

Commit Statement

The COMMIT statement in SQL is used to permanently save the changes made during the current transaction. The basic syntax is as follows:

-- Basic Syntax

COMMIT;

• COMMIT: Specifies the commit statement to save changes.

Confirming Changes Made in a Transaction

When multiple SQL statements are executed as part of a transaction, changes are not immediately permanent. The COMMIT statement is used to confirm and permanently save those changes.

-- Start a transaction
BEGIN TRANSACTION;

-- Update salary for an employee
UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

-- Insert a new employee
INSERT INTO employees (employee_id, first_name, last_name, department_id)
VALUES (105, 'Alice', 'Smith', 3);

-- Commit the changes, making them permanent
COMMIT;

In this example, the COMMIT statement is used to confirm and permanently save the changes made during the transaction, including updating the salary for an employee and inserting a new employee.

Rollback Statement

The ROLLBACK statement in SQL is used to undo changes made during the current transaction and restore the database to its state before the transaction began. The basic syntax is as follows:

-- Basic Syntax

ROLLBACK;

• ROLLBACK: Specifies the rollback statement to undo changes.

Undoing Changes Made in a Transaction

If an error occurs or if there’s a need to undo changes made during a transaction, the ROLLBACK statement is used.

-- Start a transaction
BEGIN TRANSACTION;

-- Update salary for an employee
UPDATE employees
SET salary = 65000
WHERE employee_id = 102;

-- Insert a new employee
INSERT INTO employees (employee_id, first_name, last_name, department_id)
VALUES (106, 'Bob', 'Johnson', 4);

-- An error occurs, undo changes made during the transaction
ROLLBACK;

In this example, the ROLLBACK statement is used to undo changes made during the transaction, including updating the salary for an employee and attempting to insert a new employee. This ensures that the database is rolled back to its state before the transaction began.

Lets move forward to: Index Statement

Create Index Statement

The CREATE INDEX statement in SQL is used to create an index on one or more columns of a table. The basic syntax is as follows:

-- Basic Syntax

CREATE INDEX index_name
ON table_name (column1, column2, ...);

• CREATE INDEX: Specifies the start of the CREATE INDEX statement.
• index_name: The name of the index to be created.
• ON table_name: Specifies the table on which the index is created.
• (column1, column2, …): The columns for which the index is created.

Creating Indexes on Single and Multiple Columns

Creating an Index on a Single Column:

-- Create an index named 'idx_employee_id' on the 'employee_id' column of the 'employees' table
CREATE INDEX idx_employee_id
ON employees (employee_id);

Creating an Index on Multiple Columns:

-- Create a composite index named 'idx_last_first_name' on the 'last_name' and 'first_name' columns of the 'employees' table
CREATE INDEX idx_last_first_name
ON employees (last_name, first_name);

In the first example, an index named ‘idx_employee_id’ is created on the ’employee_id’ column of the ’employees’ table. In the second example, a composite index named ‘idx_last_first_name’ is created on the ‘last_name’ and ‘first_name’ columns.

Drop Index Statement

The DROP INDEX statement in SQL is used to remove an existing index from a table. The basic syntax is as follows:

-- Basic Syntax

DROP INDEX index_name;

• DROP INDEX: Specifies the start of the DROP INDEX statement.
• index_name: The name of the index to be removed.

Removing Indexes from Tables

-- Drop the index named 'idx_employee_id' from the 'employees' table
DROP INDEX idx_employee_id;

This example removes the index named ‘idx_employee_id’ from the ’employees’ table.

Whether you’re querying data, modifying structures, or optimizing performance, these SQL principles will give you knowledge to efficiently manage databases and extract meaningful insights.

Explore more of our in-depth guides and resources to enhance your understanding of SQL – Visit Here.