SQL Queries Syntax | Top 20 SQL Commands and Statements

SQL Syntax

When working with databases, SQL (Structured Query Language) is the tool used to manage and manipulate data. SQL has a specific structure, known as syntax, which outlines how queries must be written to communicate with the database. This syntax follows certain rules and conventions to ensure the database understands your commands.

While SQL is flexible with letter casing, writing keywords in uppercase is a common practice for better readability. Understanding SQL syntax is essential for performing operations like retrieving, inserting, and updating data reliably and consistently.

SQL Statements

To perform tasks on data stored in a database, you use SQL commands written in a specific format known as SQL syntax. This syntax is a set of structured rules that ensures the database understands the instructions. SQL statements are made up of easy-to-read keywords that guide the database on what to do. For example, you can retrieve data from a table, add new entries, or modify existing records using these commands.

Here’s an example: The following SQL statement retrieves all records from a table called “Customers”:

SELECT * FROM Customers;

In this tutorial, we will review each of the key SQL statements used to maintain and work with data in a database. We will also discuss how to use straightforward, understandable commands to obtain, add, update, and delete data. In conclusion, you will have a thorough understanding of SQL statement functionality.

Database Tables

Similar to a spreadsheet, a database table stores data in rows and columns. Each row represents a record (like information about a person or product), and each column represents a specific detail about that record (like name, age, or price).

For example, in a table called “Customers,” each row could store details about a different customer, and the columns might include “Name,” “Email,” “Phone Number,” “Country,” “Postal Code,” and “City.” Tables organize data in a structured way, so it’s easy to search, manage, and analyze.

Here’s a simple representation of a database table for customers, including the specified columns:

In this table:

• Customer ID is a unique number assigned to each customer.
• Customer Name is the name of the company or individual.
• The phone number provides a way to contact the customer directly.
• The city provides additional details about the customer’s location.

This structure helps you easily organize and access customer information.

Case Sensitivity

Case sensitivity refers to whether or not uppercase and lowercase letters are treated as different characters. In some programming languages and systems, including certain databases, it matters if you write a word in uppercase or lowercase.

For example, in a case-sensitive system:

• “Customer” and “customer” would be considered two different names.

In contrast, both versions would be treated the same in a case-insensitive system.

In SQL, the case sensitivity can depend on your database system. Generally, SQL keywords (like SELECT, INSERT, etc.) are not case-sensitive, meaning you can write them in any combination of uppercase or lowercase letters. However, the names of tables and columns may be case-sensitive in some databases, so it’s good practice to be consistent with how you write them.

Should there be a semicolon after a SQL statement?

Yes, a semicolon is often used at the end of SQL statements to indicate the end of the command. It helps the database understand where one statement finishes and another begins. While some database systems allow you to omit the semicolon for single statements, it’s a good practice to include it, especially when writing multiple statements in a script.

Most Important SQL Commands and Statements

• SELECT
• SELECT DISTINCT
• WHERE
• INSERT INTO
• UPDATE
• DELETE
• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• JOIN 
• INSERT JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• GROUP BY
• HAVING
• ORDER BY
• LIMIT
• LIKE
• NULL

“Let’s discuss each statement one by one, providing its syntax and an example for each.”

Select

SELECT is a statement that retrieves data from a database. It functions similarly to requesting specific information from a database. You have the option of extracting every row or just specific columns of data from a table.

Syntax

SELECT column1, column2, … FROM table_name;

• Column1, Column2, …: The particular columns you wish to get;
• Table_Name: The name of the table containing the data.

Example

SELECT Name, City FROM Customers;

This query will show the “Name” and “City” columns from the “Customers” table.

Explanation

In this example, the database will return a list of customer names and the cities they are from. If you want to get all the columns, you can use SELECT *, which means selecting everything from the table. The SELECT statement is one of the most commonly used commands in SQL because it’s how you pull data to view or analyze.

SELECT DISTINCT

The SELECT DISTINCT statement is used to get unique values from a column, removing any duplicates. It helps when you only want to see each item once, even if it appears multiple times in the data.

Syntax

SELECT DISTINCT column1, column2, … FROM table_name;

• Column1, Column2, …: The columns from which you want to retrieve unique values.
• Table_Name: The table where the data is stored.

Example

SELECT DISTINCT City FROM Customers;

This query will show each city only once from the “Customers” table, even if multiple customers are from the same city.

Explanation

In this example, the database will return a list of cities without repeating any city that appears more than once. The SELECT DISTINCT command is useful when you want to filter out duplicates and get a clean list of unique entries from your data.

WHERE

The WHERE clause is used to filter records and get only the data that matches a certain condition. It helps you narrow down your search to find specific information.

Syntax

SELECT column1, column2, … FROM table_name WHERE condition;

• Column1, Column2, …: The columns you want to retrieve.
• Table_Name: The table where the data is stored.
• Condition: The specific condition that the records must meet.

Example

SELECT * FROM Customers WHERE Country = ‘USA’;

This query will show all customers who are from the USA.

Explanation

In this example, the WHERE clause filters the data so that only customers whose “Country” is “USA” will be shown. The WHERE clause is useful when you only need records that meet a specific condition, like filtering by a country, age, or any other detail in the data.

INSERT INTO

The INSERT INTO statement is used to add new data (or records) to a table. It’s like filling in a new row in your database with the information you want to store.

Syntax

INSERT INTO table_name (column1, column2, …) 

VALUES (value1, value2, …);

• Table_Name: The name of the table where you are adding the new data.
• Column1, Column2, …: The specific columns where you want to add the data.
• Value1, Value2, …: The actual data you want to insert.

Example

INSERT INTO Customers (Name, City, Country) 

VALUES (‘Alice’, ‘New York’, ‘USA’);

This query will add a new customer, Alice, from New York, USA, to the “Customers” table.

Explanation

In this example, a new row is added to the “Customers” table with the values provided for “Name,” “City,” and “Country.” The INSERT INTO statement is useful when you want to input new records into your database, like adding new customers, orders, or any other type of data.

UPDATE

The UPDATE statement is used to modify existing data in a table. It allows you to change values in one or more columns for specific records.

Syntax

UPDATE table_name 

SET column1 = value1, column2 = value2, …

WHERE condition;

• Table_Name: The table where the data needs to be updated.
• Column1, Column2, …: The columns you want to update.
• Value1, Value2, …: The new values you want to set.
• Condition: The specific condition to find the records that need to be updated.

Example

UPDATE Customers 

SET City = ‘Los Angeles’ 

WHERE Name = ‘Alice’;

This query updates the city of the customer named Alice to “Los Angeles.”

Explanation

In this example, the UPDATE statement changes the “City” column for the customer named Alice. The WHERE clause ensures that only Alice’s record is updated. The UPDATE command is useful when you need to change information in your database, like updating an address, a price, or any other detail for specific records.

DELETE

The DELETE statement is used to remove data from a table. It allows you to delete specific records that meet a certain condition.

Syntax

DELETE FROM table_name 

WHERE condition;

• Table_Name: The table from which you want to delete records.
• Condition: The specific condition to find the records that need to be deleted.

Example

DELETE FROM Customers 

WHERE Name = ‘Alice’;

This query will remove the customer named Alice from the “Customers” table.

Explanation

The DELETE statement removes Alice’s record from the table in this example. The WHERE clause ensures that only the record matching the condition (Name = ‘Alice’) is deleted. The DELETE command is helpful when you need to clean up your database by removing unnecessary or outdated data. Without a WHERE clause, all records in the table would be deleted, so it’s important to be careful when using it!

CREATE TABLE

The CREATE TABLE statement is used to make a new table in a database. A table is where you store your data, and it consists of columns (for different types of information) and rows (for each data entry).

Syntax

CREATE TABLE table_name (

  column1 datatype,

  column2 datatype,

  …

);

• Table_Name: The name you want to give the new table.
• Column1, Column2, …: The names of the columns you want to create in the table.
• Datatype: The type of data that will be stored in each column (e.g., text, number, date).

Example

CREATE TABLE Customers (

  CustomerID INT,

  Name VARCHAR(255),

  City VARCHAR(100),

  Country VARCHAR(100)

);

This query creates a table called “Customers” with four columns: “CustomerID” (a number), “Name” (text up to 255 characters), “City” (text up to 100 characters), and “Country” (text up to 100 characters).

Explanation

The CREATE TABLE statement creates a table where you can store customer data in this example. Each column is defined by the specific type of data it will hold. The CustomerID column stores numbers, while the other columns store text. The CREATE TABLE command is essential when setting up a database because it defines where and how your data will be organized.

ALTER TABLE

The ALTER TABLE statement is used to make changes to an existing table. You can add new columns, change existing columns, or even remove columns from the table.

Syntax

ALTER TABLE table_name 

ADD column_name datatype;

• Table_Name: The name of the table you want to modify.
• Column_Name: The new column you want to add.
• Datatype: The type of data that will be stored in the new column.

Example

ALTER TABLE Customers 

ADD Email VARCHAR(255);

This query adds a new column called “Email” to the “Customers” table, where you can store email addresses.

Explanation

In this example, the ALTER TABLE statement modifies the “Customers” table by adding a new ” Email ” column.”  The VARCHAR(255) means the column will store text with up to 255 characters. You can also use ALTER TABLE to change the type of a column, rename a column, or delete a column. It’s a useful command when you need to adjust your table structure after it’s been created.

DROP TABLE

The DROP TABLE statement is used to completely delete a table from the database. Once you drop a table, all the data and the table structure are permanently removed.

Syntax

DROP TABLE table_name;

• Table_Name: The name of the table you want to delete.

Example

DROP TABLE Customers;

This query will remove the “Customers” table and all the data stored in it from the database.

Explanation

In this example, the DROP TABLE statement deletes the entire “Customers” table from the database. This means all the data in the table will be lost permanently, and the table itself will no longer exist. Use the DROP TABLE command carefully because once a table is dropped, it cannot be recovered unless you have a backup.

JOIN

The JOIN statement is used to combine data from two or more tables in a database based on a common column between them. It helps you see related data from different tables in one result.

Syntax

SELECT columns

FROM table1

JOIN table2

ON table1.column_name = table2.column_name;

• Table 1 and Table 2: The tables you want to join.
• Column_Name: The common column in both tables that links them.

Example

SELECT Customers. Name, Orders.OrderID

FROM Customers

JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

This query combines the “Customers” and “Orders” tables, displaying the customers’ names together with their order IDs.

Explanation

In this example, the “Customers” and “Orders” tables are connected using the JOIN statement, which uses the shared field “CustomerID.” This allows you to see which customer made which order. The JOIN is useful when you need to combine related information from different tables, such as linking customer details with their order history. Without using JOIN, you’d have to look at each table separately, which can be inefficient.

INSERT JOIN

The INSERT JOIN combines the INSERT and JOIN statements to add data into a table by selecting values from another table that are related through a common column. This is useful when you want to insert data based on existing relationships between tables.

Syntax

INSERT INTO table1 (column1, column2, …)

SELECT columnA, columnB, …

FROM table2

JOIN table3

ON table2.common_column = table3.common_column;

• Table 1: The table where you want to insert data.
• Table 2, Table 3: The tables you are selecting data from.
• Common_Column: The column that links the tables (relationship).

Example

INSERT INTO Orders (CustomerID, ProductID)

SELECT Customers.CustomerID, Products.ProductID

FROM Customers

JOIN Products

ON Customers.Country = Products.Country;

This query inserts data into the “Orders” table by selecting customer and product information from the “Customers” and “Products” tables based on matching countries.

Explanation

In this example, the INSERT JOIN adds new data to the “Orders” table by selecting information from the “Customers” and “Products” tables. The JOIN part ensures the data is connected by a common attribute (Country in this case), so it only inserts relevant data. This is useful when adding data that depends on existing relationships, such as assigning products to customers based on their location.

LEFT JOIN

The LEFT JOIN statement is used to combine rows from two tables based on a related column. It retrieves all the records from the left table and the matched records from the right table. If there is no match, the result will include NULL values for the columns from the right table.

Syntax

SELECT columns

FROM table1

LEFT JOIN table2

ON table1.common_column = table2.common_column;

• Table 1: The left table from which you want to retrieve all records.
• Table 2: The right table from which you want to retrieve matched records.
• Common_Column: The column that links the two tables.

Example

SELECT Customers. Name, Orders.OrderID

FROM Customers

LEFT JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

The “Customers” table contains all of the customers’ names, while the “Orders” table contains each customer’s order ID. A customer’s name will still appear even if they haven’t placed any orders; however, the OrderID will be displayed as NULL. 

Explanation

Whether a customer has placed any orders or not, the LEFT JOIN statement in this example ensures that they are all listed. The customer’s name will appear in the result, but the OrderID will be NULL if they haven’t placed an order.  The LEFT JOIN is helpful when you want to see all records from the primary table (Customers) and any related information from the secondary table (Orders), even if some records don’t have matching entries. This helps identify customers who may need follow-up for potential orders.

RIGHT JOIN

The RIGHT JOIN statement is used to combine rows from two tables based on a related column. It retrieves all the records from the right table and the matched records from the left table. If there is no match, the result will include NULL values for the columns from the left table.

Syntax

SELECT columns

FROM table1

RIGHT JOIN table2

ON table1.common_column = table2.common_column;

• Table 1: The left table from which you want to retrieve matched records.
• Table 2: The right table from which you want to retrieve all records.
• Common_Column: The column that links the two tables.

Example

SELECT Customers. Name, Orders.OrderID

FROM Customers

RIGHT JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

This query retrieves all order IDs from the “Orders” table along with the corresponding customer names from the “Customers” table. If an order does not have a matching customer, the OrderID will appear, but the customer name will show as NULL.

Explanation

In this example, the RIGHT JOIN statement ensures that every order is listed, regardless of whether there is a matching customer. If there is an order without an associated customer, the result will show the OrderID, but the Customer Name will be NULL. The RIGHT JOIN is useful when you want to see all records from the secondary table (in this case, Orders) and any related information from the primary table (Customers), even if some records don’t have matching entries. This helps identify orders that may need clarification regarding their customers.

FULL JOIN

The FULL JOIN (or FULL OUTER JOIN) statement combines rows from two tables based on a related column. It retrieves all records from both the left and right tables. If there is a match between the two tables, it shows the combined data; if there is no match, it includes NULL values for the columns from the table that don’t have a game.

Syntax

SELECT columns

FROM table1

FULL JOIN table2

ON table1.common_column = table2.common_column;

• Table 1: The first table from which you want to retrieve records.
• Table 2: The second table from which you want to retrieve records.
• Common_Column: The column that links the two tables.

Example

SELECT Customers. Name, Orders.OrderID

FROM Customers

FULL JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

This query retrieves all customer names from the “Customers” table and all order IDs from the “Orders” table. If a customer has not placed any orders, their name will still appear with a NULL OrderID. Conversely, if there are orders without matching customers, those OrderIDs will be listed with a NULL customer name.

Explanation

In this example, the FULL JOIN statement ensures that you get every record from both the “Customers” and “Orders” tables. If there is a customer with no orders, their name will appear with a NULL value for OrderID. Similarly, if there are orders that do not have a corresponding customer, those OrderIDs will show with a NULL value for the customer name. The FULL JOIN is useful when you want to see a complete overview of data from both tables, including cases where there are unmatched records. This can help identify customers without orders and without associated customers, providing a comprehensive view of the data.

GROUP BY

The GROUP BY statement is used to arrange identical data into groups. It is often used with aggregate functions (like COUNT, SUM, AVG) to perform calculations on each group of data. This helps summarize information and understand data trends.

Syntax

SELECT column1, aggregate_function(column2)

FROM table_name

GROUP BY column1;

• Column 1: The column by which you want to group the data.
• Aggregate_Function: A function that performs a calculation on a column (e.g., COUNT, SUM, AVG).

Example

SELECT City, COUNT(CustomerID) AS NumberOfCustomers

FROM Customers

GROUP BY City;

This query counts the number of customers in each city and displays the results grouped by city.

Explanation

In this example, the GROUP BY statement groups the records in the “Customers” table by the “City” column. The COUNT(CustomerID) function counts how many customers are in each city. The result will show each city along with the number of customers from that city.

Using GROUP BY is useful when you want to see summarized data, such as how many entries belong to each category or group. It helps in analyzing patterns and making data-driven decisions, like identifying which cities have the most customers.

HAVING

The HAVING clause filters groups created by the GROUP BY statement. It allows you to specify conditions on aggregated data, restricting the results based on the results of aggregate functions (like COUNT, SUM, AVG).

Syntax

SELECT column1, aggregate_function(column2)

FROM table_name

GROUP BY column1

HAVING condition;

• Column 1: The column by which you want to group the data.
• Aggregate_Function: A function that performs a calculation on a column (e.g., COUNT, SUM, AVG).
• Condition: A condition that filters the groups based on the results of the aggregate function.

Example

SELECT City, COUNT(CustomerID) AS NumberOfCustomers

FROM Customers

GROUP BY City

HAVING COUNT(CustomerID) > 5;

This query counts the number of customers in each city and only shows cities that have more than 5 customers.

Explanation

In this example, the HAVING clause is applied after the GROUP BY statement. The query groups the records in the “Customers” table by the “City” column and counts the number of customers in each city. The HAVING COUNT(CustomerID) > 5 condition filters the results, so only cities with more than 5 customers are displayed.

Using HAVING is essential when you want to filter groups based on aggregate results. It helps in focusing on significant data points, such as identifying cities that have a substantial customer base, allowing for more targeted analysis and decision-making.

ORDER BY

The ORDER BY statement sorts the result set of a query based on one or more columns. You can arrange the data in ascending order (from smallest to largest) or descending order (from largest to smallest). By default, the sorting is done in ascending order.

Syntax

SELECT columns

FROM table_name

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

• Columns: These are the columns you want to retrieve from the table.
• Column1, Column2: The columns by which you want to sort the data.
• ASC: Sorts the data in ascending order (this is optional and used by default).
• DESC: Sorts the data in descending order.

Example

SELECT Name, City, Age

FROM Customers

ORDER BY Age DESC;

This query retrieves the names, cities, and ages of customers and sorts the results by age in descending order, showing the oldest customers first.

Explanation

The ORDER BY statement sorts the records in the “Customers” table based on this example’s “Age” column. By using DESC, the query arranges the customers from oldest to youngest. If you wanted to sort in ascending order, you could use ASC or leave it out altogether.

Using ORDER BY is useful when you want to present data in a specific order, such as ranking customers by age, listing products by price, or organizing sales data by date. This makes it easier to analyze and understand the information.

LIMIT

The LIMIT clause is used to specify the maximum number of records to return in a query. This is helpful when you only want to see a subset of the data, like the first few rows of a large result set.

Syntax

SELECT columns

FROM table_name

LIMIT number;

• Columns: These are the columns you want to retrieve from the table.
• Number: The maximum number of records you want to return.

Example

SELECT Name, City

FROM Customers

LIMIT 5;

This query retrieves the names and cities of the first 5 customers from the “Customers” table.

Explanation

The LIMIT clause restricts the output to only the first 5 records from the result set in this example. If the “Customers” table has many entries, using LIMIT allows you to quickly see a sample of the data without overwhelming you with too much information.

Using LIMIT is particularly useful in scenarios where you want to preview data, such as displaying a list of recent transactions, showing top-selling products, or paginating results in an application. It helps improve performance and user experience by reducing the amount of data returned in a single query.

LIKE

The LIKE operator is used in SQL to search for a specified pattern in a column. It is often used with the WHERE clause to filter results based on partial matches, including wildcards representing one or more characters.

Syntax

SELECT columns

FROM table_name

WHERE column_name LIKE pattern;

• Columns: These are the columns you want to retrieve from the table.
• column_name: The column where you want to search for the pattern.
• pattern: The pattern you want to match, using wildcards.

Wildcards

• %: Represents zero or more characters.
• _: Represents a single character.

Example

SELECT Name

FROM Customers

WHERE Name LIKE ‘A%’;

This query retrieves the names of all customers whose names start with the letter “A.”

Explanation

In this example, the WHERE clause uses the LIKE operator to filter the results from the “Customers” table. The pattern ‘A%’ specifies that we want names that start with “A” followed by any number of characters (including none). As a result, the query will return names like “Alice,” “Adam,” and “Aaron.”

Using LIKE is helpful when you need to search for specific patterns in text fields, such as finding names, email addresses, or any other string data. It provides flexibility in querying data by allowing for partial matches rather than requiring exact matches.

NULL

In SQL, NULL represents a missing or unknown value in a database. It is not the same as an empty string or a zero; it simply means that no value exists in that field. You can use NULL to indicate that a particular piece of data is not available or has not been entered.

Syntax

To check for NULL values, you typically use the IS NULL or IS NOT NULL condition in a query.

SELECT columns

FROM table_name

WHERE column_name IS NULL;

Example

SELECT Name, Age

FROM Customers

WHERE Age IS NULL;

This query retrieves the names and ages of customers whose age is not specified (i.e., NULL).

Explanation

In this example, the IS NULL condition checks the “Age” column in the “Customers” table for any records where the age has not been entered. If a customer’s age is missing, the query will return that record.

Using NULL is important for data integrity and accurate reporting. It allows you to distinguish between records with no data and those with actual values, helping you to manage and analyze your data effectively. For instance, knowing which customers haven’t provided their age can inform follow-up actions or data-cleaning efforts.

Conclusion about SQL Langage Syntax

SQL syntax provides a structured way to communicate with databases and perform various operations on data. Understanding the basic syntax rules is essential for writing effective queries, whether you’re retrieving, updating, or managing data. SQL statements like SELECT, INSERT, UPDATE, and DELETE are built on these syntax principles, allowing users to manipulate data efficiently. Clauses such as WHERE, GROUP BY, and ORDER BY allow you to focus your search and obtain the precise data you require.  By mastering SQL syntax, you can unlock the full potential of database management and make data-driven decisions more effectively.

FAQS –  SQL is Command