SQL: The Basic Syntax of SQL (Part I)

The basic syntax of SQL (Structured Query Language) involves the use of a set of keywords and commands to interact with a relational database. The primary commands include SELECT, INSERT, UPDATE, DELETE, and CREATE, among others. The SELECT command retrieves data from a database, while the INSERT command adds data to the database. The UPDATE command modifies existing data, while the DELETE command removes data from the database. The CREATE command creates new tables, views, and other database objects. SQL also uses clauses such as WHERE, ORDER BY, GROUP BY, and HAVING to filter and sort data. The syntax of SQL may vary slightly depending on the specific RDBMS (Relational Database Management System) being used, but the core commands and syntax remain the same across different platforms.

Syntax of SQL

Here is an overview of the basic syntax of SQL:

• Keywords: SQL keywords are reserved words that have a specific meaning in the language, such as SELECT, INSERT, UPDATE, DELETE, and WHERE.

• Clauses: Clauses are used to specify the criteria or conditions for a query, such as the columns to be selected, the values to be inserted, or the conditions to be met.

• Expressions: Expressions are used to specify the data to be manipulated or retrieved, such as column names, literal values, or functions.

• Operators: Operators are used to specifying the relationships between expressions or values, such as equality (=), inequality (!=), or greater than (>).

Here is an example of a simple SQL query:

SELECT name, email FROM customers WHERE country = 'United States';

This query retrieves the “name” and “email” columns from the “customers” table, where the “country” field is equal to ‘United States‘.

SQL SELECT Statement

The SELECT statement is one of the most commonly used SQL commands. It is used to retrieve data from a database table, and it can be used to specify which columns to retrieve, which rows to retrieve, and which conditions to use to filter the data.

Here is the basic syntax of a SELECT statement:

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

Or

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

Or

SELECT * FROM table_name;

• The SELECT clause specifies the columns to be retrieved. You can use an asterisk (*) to select all columns.

• The FROM clause specifies the table to retrieve data.

• The WHERE clause specifies the conditions to use to filter the data. If you omit the WHERE clause, all rows will be retrieved.

Here is an example of a SELECT statement:

SELECT id, name, email FROM customers WHERE country = 'United States';

This query retrieves the “id“, “name” and “email” columns from the “customers” table, where the “country” field is equal to ‘United States‘.

SQL DISTINCT Clause

The DISTINCT clause is used in SQL to return only unique values from a SELECT statement. It eliminates duplicate rows from the result set, and only returns one row for each unique set of values.

Here is the basic syntax of the DISTINCT clause:

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

The DISTINCT clause is used after the SELECT keyword, and it is followed by the list of columns to retrieve. You can specify one or more columns, or use an asterisk (*) to select all columns.

Here is an example of a SELECT statement with the DISTINCT clause:

SELECT DISTINCT country FROM customers;

This query retrieves the unique values from the “country” column in the “customers” table.

SQL WHERE Clause

The WHERE clause is used in SQL to specify conditions for a SELECT, INSERT, UPDATE, or DELETE statement. It is used to filter the data that is retrieved or modified, and it is used to specify the criteria that must be met in order for a row to be included in the result set.

Here is the basic syntax of the WHERE clause:

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

Or:

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

The WHERE clause is used after the FROM clause, and it is followed by a condition that specifies the criteria to be met. The condition can use any of the comparison operators, such as =, !=, >, or <, and it can use AND, OR, and NOT to combine multiple conditions.

Here is an example of a SELECT statement with the WHERE clause:

SELECT * FROM customers WHERE country = 'United States';

This query retrieves all columns and rows from the “customers” table where the “country” field is equal to ‘United States’.

SQL AND/OR Clause

The AND and OR clauses are used in SQL to combine multiple conditions in a WHERE clause. They allow you to specify multiple criteria for a SELECT, INSERT, UPDATE, or DELETE statement, and they are used to filter the data that is retrieved or modified.

Here is the basic syntax of the AND and OR clauses:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2;

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2;

The AND clause is used to specify that both conditions must be met, and the OR clause is used to specify that at least one of the conditions must be met.

Here are some examples of the AND and OR clauses:

SELECT * FROM customers WHERE country = 'United States' AND age > 30;

SELECT * FROM customers WHERE country = 'United States' OR country = 'Canada';

The first query retrieves all rows from the “customers” table where the “country” field is equal to ‘United States’ and the “age” field is greater than 30. The second query retrieves all rows from the “customers” table where the “country” field is equal to ‘United States’ or ‘Canada’.

SQL IN Clause

The IN clause is used in SQL to specify a list of values for a SELECT, INSERT, UPDATE, or DELETE statement. It allows you to specify multiple values for a WHERE clause condition, and it is used to filter the data that is retrieved or modified.

Here is the basic syntax of the IN clause:

SELECT column1, column2, ...
FROM table_name
WHERE column IN (value1, value2, ...);

The IN clause is used after the WHERE keyword, and it is followed by a list of values enclosed in parentheses. The list of values can be specified as a list of literal values, or as a subquery.

Here is an example of the IN clause:

SELECT * FROM customers WHERE country IN ('United States', 'Canada', 'Mexico');

This query retrieves all rows from the “customers” table where the “country” field is equal to ‘United States’, ‘Canada’, or ‘Mexico’.