SQL: The Basic Syntax of SQL (Part II)

SQL BETWEEN Clause

The BETWEEN clause is used in SQL to specify a range of values for a SELECT, INSERT, UPDATE, or DELETE statement. It allows you to specify a range of 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 BETWEEN clause:

SELECT column1, column2, ...
FROM table_name
WHERE column BETWEEN value1 AND value2;

The BETWEEN clause is used after the WHERE keyword, and it is followed by two values that specify the range of values to include. The values can be numbers, dates, or strings, depending on the data type of the column.

Here is an example of the BETWEEN clause:

SELECT * FROM customers WHERE age BETWEEN 18 AND 25;

This query retrieves all rows from the “customers” table where the “age” field is between 18 and 25, inclusive.

SQL LIKE Clause

The LIKE clause is used in SQL to search for a specific pattern in a column. It is used with the WHERE clause to filter the data that is retrieved or modified, and it is often used with the % wildcard character to match any number of characters.

Here is the basic syntax of the LIKE clause:

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

The LIKE clause is used after the WHERE keyword, and it is followed by a pattern to search for. The pattern can include the % wildcard character to match any number of characters, or the _ wildcard character to match a single character.

Here is an example of the LIKE clause:

SELECT * FROM customers WHERE name LIKE '%son';

SQL ORDER BY Clause

The ORDER BY clause is used in SQL to sort the result set of a SELECT, INSERT, UPDATE, or DELETE statement. It allows you to specify the order in which rows are returned, and it is used to sort the data by one or more columns.

Here is the basic syntax of the ORDER BY clause:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ...;

The ORDER BY clause is used after the FROM clause, and it is followed by a list of columns to sort by. You can specify one or more columns, and you can use the ASC or DESC keyword to specify the sort order. ASC specifies ascending order (the default), and DESC specifies descending order.

Here is an example of the ORDER BY clause:

SELECT * FROM customers ORDER BY name ASC;

This query retrieves all rows from the “customers” table and sorts the result set by the “name” field in ascending order.

SQL GROUP BY Clause

The GROUP BY clause is used in SQL to group the result set of a SELECT, INSERT, UPDATE, or DELETE statement by one or more columns. It is often used in conjunction with aggregate functions (e.g., COUNT, SUM, AVG) to perform calculations on the grouped data.

Here is the basic syntax of the GROUP BY clause:

SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...;

The GROUP BY clause is used after the FROM clause, and it is followed by a list of columns to group by. You can specify one or more columns, and you can use the HAVING clause to specify conditions for the grouped data.

Here is an example of the GROUP BY clause:

SELECT country, COUNT(*) FROM customers GROUP BY country;

This query retrieves the “country” column and the count of rows for each unique value in the “country” column from the “customers” table. The result set is grouped by the “country” column, and the COUNT function is used to calculate the number of rows for each group.

SQL COUNT Clause

The COUNT clause is used in SQL to count the number of rows in a result set. It is often used with the GROUP BY clause to count the number of rows for each group, or with the WHERE clause to count the number of rows that meet a specific condition.

Here is the basic syntax of the COUNT clause:

SELECT COUNT(*)
FROM table_name
WHERE condition;

The COUNT clause is used after the SELECT keyword, and it is followed by an asterisk (*) to count all rows. The FROM clause specifies the table to retrieve data from, and the WHERE clause specifies the conditions to use to filter the data.

Here is an example of the COUNT clause:

SELECT COUNT(*) FROM customers WHERE country = 'United States';

This query counts the number of rows in the “customers” table where the “country” field is equal to ‘United States’.

SQL HAVING Clause

The HAVING clause is used in SQL to specify conditions for a SELECT, INSERT, UPDATE, or DELETE statement when used with the GROUP BY clause. It is used to filter the data that is grouped by the GROUP BY clause, and it is often used with aggregate functions (e.g., COUNT, SUM, AVG) to perform calculations on the grouped data.

Here is the basic syntax of the HAVING clause:

SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...
HAVING condition;

The HAVING clause is used after the GROUP BY 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 the HAVING clause:

SELECT country, COUNT(*) FROM customers GROUP BY country HAVING COUNT(*) > 5;

This query retrieves the “country” column and the count of rows for each unique value in the “country” column from the “customers” table. The result set is grouped by the “country” column, and the COUNT function is used to calculate the number of rows for each group. The HAVING clause filters the result set to include only groups where the count of rows is greater than 5.

SQL CREATE TABLE Statement

The CREATE TABLE statement is used in SQL to create a new table in a database. It is used to specify the structure of a table, including the column names, data types, and constraints.

Here is the basic syntax of the CREATE TABLE statement:

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

The CREATE TABLE statement is used to create a new table with the specified name. The table name is followed by a list of columns, each defined by a name, a data type, and any constraints.

Here is an example of the CREATE TABLE statement:

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  country VARCHAR(255) NOT NULL
);

This statement creates a new table named “customers” with four columns: “id“, “name“, “email“, and “country“. The “id” column is defined as an INT data type and is marked as the PRIMARY KEY (unique identifier). The “name“, “email“, and “country” columns are defined as VARCHAR data types and are marked as NOT NULL (cannot contain NULL values).