SQL Key-words with the examples
Tags: database, sql, rdbms, beginners
Published: 2023-12-30 03:01:22

SQL (Structured Query Language) uses various keywords to perform operations on a database. 

Here are some of the most common SQL keywords:

### 1. SELECT

In SQL, the SELECT statement is used to retrieve data from one or more tables in a database. It allows you to specify the columns you want to retrieve, the table or tables from which to retrieve the data, and any conditions that must be met for the data to be included in the result set.

Here's a basic example:

Let's assume we have a simple table named employees with the following structure:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xfz43vr4obw5l7dvxnex.png)

i. Retrieve all columns for all employees

```
SELECT * FROM employees;
```

This query selects all columns (* means all columns) from the employees table. The result would look like this:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hc18n6lk98ydabf7ajrr.png)

ii. Retrieve specific columns for employees with a certain job title

```
SELECT employee_id, 
       first_name, 
       last_name 
FROM   employees 
WHERE  job_title = 'Developer'; 
```

This query selects only the employee_id, first_name, and last_name columns for employees whose job_title is 'Developer'. The result would be:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/94var3396ige5eh1lxqz.png)



### 2. FROM

In SQL, the FROM clause is used in the SELECT statement to specify the table or tables from which you want to retrieve data. The FROM clause is a mandatory part of the SELECT statement, as it indicates the source of the data you're querying. 

Here's a basic explanation and example:

```
SELECT column1, 
       column2, 
       ... from table_name; 
```
- column1, column2, ...: The columns you want to retrieve from the specified table.

- table_name: The name of the table from which you want to retrieve data.


### 3. WHERE

The WHERE clause is used in conjunction with the SELECT statement to filter the rows returned by a query. It allows you to specify a condition that must be met for a row to be included in the result set. 

Here's the basic syntax and an example:

```
SELECT column1, 
       column2, 
       ... 
FROM   table_name 
WHERE  condition;
```
- column1, column2, ...: The columns you want to retrieve from the specified table.

- table_name: The name of the table from which you want to retrieve data.

- condition: The condition that must be satisfied for a row to be included in the result set

**Example**
	Let's use the same employees table from the previous example and retrieve only those employees whose salary is greater than 45000.00:

```
SELECT employee_id, 
       first_name, 
       last_name, 
       salary 
FROM   employees 
WHERE  salary > 45000.00; 
```
In this example:

The FROM clause specifies the source table, which is employees.

The WHERE clause is used to filter the rows based on the condition salary > 45000.00.

The result of the query would look like this:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2tacng0t3b2dplr40h7m.png)

You can use various operators in the WHERE clause to specify different types of conditions, such as equality (=), inequality (<> or !=), greater than (>), less than (<), and more. You can also combine multiple conditions using logical operators such as AND and OR.


### 4. AND

The AND operator in SQL is used in the WHERE clause to combine multiple conditions. It ensures that a row is included in the result set only if it satisfies all the specified conditions. 

Here's the basic syntax and an example:

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

- table_name: The name of the table from which you want to retrieve data.

- condition1, condition2, ...: The conditions that must be satisfied for a row to be included in the result set. The AND operator is used to combine these conditions.

Let's consider the employees table again and retrieve employees who work in the Engineering department and have a salary greater than 40000.00:

```
SELECT employee_id, 
       first_name, 
       last_name, 
       salary 
FROM   employees 
WHERE  department_id = 1 
       AND salary > 40000.00; 
In this example:

```
The FROM clause specifies the source table, which is employees. The WHERE clause is used to filter the rows based on two conditions: department_id = 1 (employees in the Engineering department).
salary > 40000.00 (employees with a salary greater than 40000.00).

The result of the query would look like this:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/be8glnp26dxbw5ll557e.png)

### 5. OR

The OR operator is used in the WHERE clause to combine multiple conditions. It ensures that a row is included in the result set if it satisfies at least one of the specified conditions. Here's the basic syntax and an example

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

**Example**

```
SELECT employee_id, 
       first_name, 
       last_name, 
       department_id, 
       salary 
FROM   employees 
WHERE  department_id = 1 
        OR salary > 45000.00; 
```

The result of the query would look like this:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ozfvfddamuj4473a3n9t.png)


### 6. DISTINCT

The ‘DISTINCT’ keyword is used in the SELECT statement to eliminate duplicate rows from the result set. It returns only unique values in the specified columns. 

Here's the basic syntax and an example:

```
SELECT DISTINCT column1, 
                column2, 
                ... from table_name; 
```

- column1, column2, ...: The columns for which you want to retrieve distinct values.

- table_name: The name of the table from which you want to retrieve data.

Let's consider a simple orders table with information about customer orders:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mu0hezzzmh8yl3a5ou6j.png)

Now, let's use the DISTINCT keyword to retrieve unique product names from the orders table:

```
SELECT DISTINCT product_name 
FROM   orders; 
```

The result of the query would look like this:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8aap7emj847yqqpvmw6l.png)

In this example, the DISTINCT keyword ensures that only unique values in the product_name column are returned. Duplicate entries are eliminated from the result set.

You can use DISTINCT with multiple columns as well. For instance, if you want to retrieve unique combinations of customer_id and product_name:

```
SELECT DISTINCT customer_id, 
                product_name 
FROM   orders; 
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n82ky40zp0mby6t8rsbr.png)

In this case, the combination of customer_id and product_name is used to determine uniqueness, and duplicate combinations are removed from the result set.


### 7. HAVING

The HAVING clause in SQL is used to filter the results of a GROUP BY query based on aggregate conditions. While the WHERE clause filters rows before they are grouped, the HAVING clause filters groups after they have been formed. It is typically used with aggregate functions like SUM, COUNT, AVG, etc. 

Here's the basic syntax

```
SELECT   column1, 
         Aggregate_function(column2) 
FROM     table_name 
GROUP BY column1 
HAVING   Aggregate_function(column2) condition;

```
- column1: The column used for grouping.

- aggregate_function: An aggregate function applied to a column.

- table_name: The name of the table from which you want to retrieve data.

- condition: The condition applied to the result of the aggregate function.

Here's a simple example using the orders table:In this example

```
SELECT customer_id, 
       Count(order_id) AS order_count 
FROM   orders 
GROUP  BY customer_id 
HAVING Count(order_id) > 1; 
```

The GROUP BY clause groups the rows based on the customer_id column.
The HAVING clause filters out groups where the count of orders (COUNT(order_id)) is not greater than 1.

The result might look like this:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bz1bdbzcpuv5eqylli3b.png)

### 8. GROUP BY

The GROUP BY clause in SQL is used to group rows that have the same values in specified columns into summary rows, like "total" or "average" values. It is often used in combination with aggregate functions such as COUNT, SUM, AVG, MAX, or MIN. 

Here's the basic syntax

```
SELECT column1, 
       Aggregate_function(column2) 
FROM   table_name 
GROUP  BY column1; 
```
- column1: The column by which you want to group the result set.

- aggregate_function: An aggregate function applied to another column within each group.

- table_name: The name of the table from which you want to retrieve data.

Here's an example using the orders table

```
SELECT customer_id, 
       Count(order_id) AS order_count 
FROM   orders 
GROUP  BY customer_id; 
In this example:
```

- The GROUP BY clause groups the rows based on the customer_id 
column.

- The COUNT(order_id) is an aggregate function that counts the number of orders for each customer.

The result might look like this:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9y34was3sanpvgokbifa.png)

### 8. ORDER BY

ORDER BY clause is used to sort the result set of a query in ascending or descending order based on one or more columns. The basic syntax is as follows:

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

- column1, column2, ...: The columns you want to retrieve from the specified table.

- table_name: The name of the table from which you want to retrieve data.

-  ORDER BY: Specifies the sorting order.

-  column1, column2, ...: The columns by which you want to sort the result set.

-  [ASC | DESC]: Optional. Specifies the sort order, either ascending (ASC, the default) or descending (DESC).

Here's an example using the orders table

```
SELECT order_id,
       customer_id,
       product_name,
       order_date
FROM   orders
ORDER  BY order_date DESC,
          customer_id ASC; 
```

In this example:

- The ORDER BY clause is used to sort the result set.
The order_date DESC sorts the result set in descending order based on the order_date column.

-  The customer_id ASC sorts the result set in ascending order based on the customer_id column.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wa69252w5jgt2ed2qgwr.png)

In this result set, the rows are sorted first by order_date in descending order and then by customer_id in ascending order. You can adjust the ORDER BY clause based on your specific sorting requirements.

### 10. PARTITIONED BY

The PARTITION BY clause is used in the context of window functions in SQL. It allows you to divide the result set of a query into partitions to which the window function is applied separately. Window functions operate on a set of rows related to the current row, and the PARTITION BY clause helps define how those rows are grouped into partitions.

Here's the basic syntax:

```
SELECT column1,
       column2,
       Window_function(column3)
         OVER (
           partition BY column4
           ORDER BY column5)
FROM   table_name; 
```

- column1, column2: The columns you want to retrieve in the result set.

- window_function(column3): The window function applied to column3.

- PARTITION BY column4: Specifies the column by which you want to partition the result set.

- ORDER BY column5: Specifies the order within each partition.
Here's a simple example using the orders table to calculate the running total of orders for each customer, partitioned by the customer_id

```
SELECT order_id,
       customer_id,
       order_date,
       Sum(order_id)
         OVER (
           partition BY customer_id
           ORDER BY order_date) AS running_total
FROM   orders; 
```
In this example:

- The PARTITION BY customer_id clause divides the result set into partitions for each unique customer_id.

- The ORDER BY order_date clause specifies the order within each partition based on the order_date.

- The SUM(order_id) window function calculates the running total of order_id within each partition.

The result might look like this:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tgonf4u09tje3tldwwxa.png)

In this result set, the running total is calculated separately for each customer_id partition based on the order of order_date.

### 11. OVER

In SQL, the OVER clause is used in conjunction with window functions to define a window or a subset of rows within the result set over which the window function operates. The OVER clause consists of three main parts: PARTITION BY, ORDER BY, and ROWS or RANGE specification.

Here's a basic overview of the syntax:

```
SELECT   column1,
         column2,
         window_function(column3) OVER ( partition BY partition_column ORDER BY order_column rows BETWEEN n PRECEDING AND      m following) AS result_column
FROM     table_name;
```

- column1, column2: The columns you want to retrieve in the result set.

- window_function(column3): The window function applied to column3.

- PARTITION BY partition_column: Divides the result set into partitions based on the values in partition_column.

- ORDER BY order_column: Specifies the order within each partition based on the values in order_column.

- ROWS BETWEEN N PRECEDING AND M FOLLOWING: Optional clause that defines the window frame, specifying the range of rows used by the window function.

Here's an example using the orders table to calculate the running total of orders for each customer, ordered by order_date:

```
SELECT order_id,
       customer_id,
       order_date,
       SUM(order_id)
         over (
           PARTITION BY customer_id
           ORDER BY order_date ROWS BETWEEN unbounded preceding AND CURRENT ROW)
       AS
       running_total
FROM   orders; 
```
In this example:

- The PARTITION BY customer_id clause divides the result set into partitions for each unique customer_id.

- The ORDER BY order_date clause specifies the order within each partition based on the order_date.

- The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW clause defines the window frame, including all rows from the start of the partition to the current row.

The result might look like this:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ulrrph40rowqj3r27sbd.png)

In this result set, the running total is calculated separately for each customer_id partition based on the order of order_date. The window frame is defined to include all rows from the start of the partition to the current row.

### 12. JOIN / INNER JOIN

In SQL, the JOIN/ INNER JOIN operation is used to combine rows from two or more tables based on a related column between them. JOIN / INNER JOIN Returns only the rows where there is a match in both tables.

```
SELECT *
FROM   table1
       INNER JOIN table2
               ON table1.column_name = table2.column_name; 
```
Customer Table(customers)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xfc6ouil3hz87rt3a3rw.png)

Orders table(orders)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1drjfnnoeuc5xektrdo7.png)

Query using JOIN / INNER JOIN

```
SELECT customers.customer_id,
       customers.customer_name,
       orders.order_id,
       orders.order_date,
       orders.order_amount
FROM   customers
       INNER JOIN orders
               ON customers.customer_id = orders.customer_id; 
```
In this example:


The SQL query uses INNER JOIN to combine information from both tables based on the common column customer_id.

The result will include the customer ID, customer name, order ID, order date, and order amount for each customer who has placed an order

The expected output would be something like:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4mwwmqolrhtxee2cworz.png)


### 13. LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.

```
SELECT *
FROM   table1
       LEFT JOIN table2
              ON table1.column_name = table2.column_name; 
```
Let's consider a scenario where you want to retrieve all customers along with their orders, even if they haven't placed any orders. In this case, you would use a LEFT JOIN (or LEFT OUTER JOIN) to include all rows from the left table (customers) and matching rows from the right table (orders).

Query using LEFT JOIN (or LEFT OUTER JOIN)

```
SELECT customers.customer_id,
       customers.customer_name,
       orders.order_id,
       orders.order_date,
       orders.order_amount
FROM   customers
       LEFT JOIN orders
              ON customers.customer_id = orders.customer_id; 
```
In this example:

We have the same customers and orders tables with sample data as used in the previous examples.
	
The SQL query uses LEFT JOIN to retrieve all customers, along with details of their orders if they have placed any. If a customer hasn't placed any orders, the columns from the orders table will contain NULL values.

The expected output might look like this:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/92a7s7f1sz5z156bxaio.png)

In this result set, the first four rows show customers with their corresponding order details. The last row represents a customer (New Customer) who hasn't placed any orders, and the columns from the orders table contain NULL values for that row due to the use of LEFT JOIN.

### 14. RIGHT JOIN (or RIGHT OUTER JOIN)


Returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table.

Let's use the same example with customers and orders tables. This time, we'll use a RIGHT JOIN to retrieve all orders along with customer information, even if there are no matching customers:

Query using RIGHT JOIN (or RIGHT OUTER JOIN)

```
SELECT customers.customer_id,
       customers.customer_name,
       orders.order_id,
       orders.order_date,
       orders.order_amount
FROM   customers
       RIGHT JOIN orders
               ON customers.customer_id = orders.customer_id; 
```
In this example:

We have the same customers and orders tables with sample data.

The SQL query uses RIGHT JOIN to retrieve all orders, along with customer information if there is a matching customer. If there is no matching customer, the columns from the customers table will contain NULL values.

The expected output might look like this:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kxm1sudauf5gjwyuws2h.png)

In this result set, the first three rows show customers with their corresponding order details. The last row represents an order (order_id 104) for which there is no matching customer, and the columns from the customers table contain NULL values for that row due to the use of RIGHT JOIN.


### 15. FULL JOIN (or FULL OUTER JOIN):

Returns all rows when there is a match in either the left or the right table. If there is no match, NULL values are returned for columns from the table without a match.

```
SELECT *
FROM   table1
       FULL JOIN table2
              ON table1.column_name = table2.column_name; 
```
Let's use the same example with customers and orders tables to illustrate a FULL JOIN:
Query using FULL JOIN (or FULL OUTER JOIN)

```
SELECT customers.customer_id,
       customers.customer_name,
       orders.order_id,
       orders.order_date,
       orders.order_amount
FROM   customers
       FULL JOIN orders
              ON customers.customer_id = orders.customer_id; 
```

In this example:


We have the same customers and orders tables with sample data.

The SQL query uses FULL JOIN to retrieve all customers and all orders, combining them based on the common column customer_id. If there is no match for a particular row in either table, the columns from the table without a match will contain NULL values.

The expected output might look like this:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ofj22uexlkqq4c23mgun.png)

### 16. CROSS JOIN:

Returns the Cartesian product of the two tables, meaning every row from the first table is combined with every row from the second table.

```
SELECT *
FROM   table1
       CROSS JOIN table2; 
```

Here's an example using the same customers and orders tables:

```
SELECT customers.customer_id,
       customers.customer_name,
       orders.order_id,
       orders.order_date,
       orders.order_amount
FROM   customers
       CROSS JOIN orders; 
```
In this example:

We have the same customers and orders tables with sample data.

The SQL query uses CROSS JOIN to combine all rows from the customers table with all rows from the orders table.

The expected output might look like this:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r1wlz5wpd465bl9hhvnn.png)

In this result set, every customer is combined with every order, resulting in a total of 3 customers × 4 orders = 12 rows. Each row represents a combination of a customer and an order, forming the Cartesian product of the two tables.

### 17. COUNT(*):

The COUNT(*) function in SQL is used to count the number of rows in a table. It counts all rows, including those with NULL values in any column. 

Here's a basic example:

```
SELECT Count(*)
FROM   your_table; 
```

If you want to count the number of rows that meet a specific condition, you can include a WHERE clause:

```
SELECT Count(*)
FROM   your_table
WHERE  your_condition; 
```

For example, let's say you want to count the number of orders for a specific customer

```
SELECT Count(*)
FROM   orders
WHERE  customer_id = 1; 
```

The result of COUNT(*) is a single value representing the number of rows that satisfy the specified condition.

If you want to count the number of non-NULL values in a specific column, you can use COUNT(column_name) instead of COUNT(*):

```
SELECT Count(column_name)
FROM   your_table; 
```

For example, to count the number of non-NULL order dates in the orders table:

```
SELECT Count(order_date)
FROM   orders 
```

Keep in mind that COUNT(*) is generally more efficient than COUNT(column_name) because it doesn't have to check for NULL values. Use COUNT(column_name) when you specifically want to count non-NULL values in a particular column.

### 18. LAG(*):

The LAG function in SQL is a window function that allows you to access data from a previous row within the result set of a query. It's often used in scenarios where you want to compare the current row with the values of the preceding row.

Here's a basic syntax for using LAG:

```
LAG(column_name, offset, default_value) OVER (partition BY partition_column ORDER BY order_column)
```

column_name: The column whose value you want to access from the previous row.

- offset: The number of rows back from the current row. If not specified, it defaults to 1 (the previous row).

- default_value: (Optional) The value returned when the specified offset goes beyond the beginning of the partition.

Here's a simple example to illustrate the usage of LAG. Suppose you have a table named sales with columns sale_date and revenue, and you want to 

find the difference in revenue between the current day and the previous day:

```
SELECT sale_date,
       revenue,
       Lag(revenue, 1, 0)
         OVER (
           ORDER BY sale_date) AS previous_day_revenue
FROM   sales; 
```
In this example:

- revenue: The current day's revenue.

- LAG(revenue, 1, 0) OVER (ORDER BY sale_date): The revenue from the previous day. If there's no previous day, it defaults to 0.

- This query returns a result set with columns sale_date, revenue, and previous_day_revenue, where previous_day_revenue contains the revenue from the previous day for each row.

Keep in mind that the OVER clause is used to define the window within which the LAG function operates. In this case, it orders the rows by sale_date, but you can adjust it based on your specific requirements.

### 19. CASE WHEN(*):

The CASE statement in SQL is used to perform conditional logic within a query. It allows you to return different values or apply different expressions based on specified conditions. The basic syntax of the CASE statement is as follows:

```
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... [ELSE
else_result] END 
```
Here's a simple example using the CASE statement. Suppose you have a table employees with a column salary, and you want to categorise employees into different salary ranges:

```
SELECT employee_id, employee_name, salary,
 CASE
   WHEN salary < 50000 THEN 'Low Salary'
   WHEN salary >= 50000 AND salary < 100000 THEN 'Medium Salary'
   WHEN salary >= 100000 THEN 'High Salary'
   ELSE 'Unknown Salary'
   END AS salary_category
FROM employees;

```

In this example:

The CASE statement evaluates each condition in order.

- If the salary is less than 50000, it returns 'Low Salary'.

- If the salary is between 50000 (inclusive) and 100000 (exclusive), it returns 'Medium Salary'.

- If the salary is 100000 or greater, it returns 'High Salary'.

- If none of the conditions is met, it returns 'Unknown Salary'.

You can also use the CASE statement in conjunction with other clauses, such as WHERE or ORDER BY, to conditionally filter or sort your query results.

Here's an example of using CASE with ORDER BY:

```
SELECT employee_id, employee_name, salary FROM employees
ORDER BY
    CASE
        WHEN salary < 50000 THEN 1
        WHEN salary >= 50000 AND salary < 100000 THEN 2
        WHEN salary >= 100000 THEN 3
        ELSE 4
    END;

```
In this case, the result set is ordered based on the salary categories defined in the CASE statement.

### 20. DATEDIFF:

The DATEDIFF function is used in SQL to calculate the difference between two dates. The specific syntax and usage may vary slightly between different database systems, but the general idea is to provide two dates and specify the unit (such as days, months, or years) for which you want to calculate the difference.

Here's a basic example using DATEDIFF to calculate the difference in days between two dates:

```
SELECT Datediff(day, '2023-01-15', '2023-02-10') AS days_difference; 
```
In this example:

day is the unit for which we want to calculate the difference.
'2023-01-15' and '2023-02-10' are the two dates for which we want to find the difference.

The result will be the number of days between the two dates.
Keep in mind that the actual usage may vary depending on the database system you are using. 

For example, in MySQL, the syntax is:

```
SELECT Datediff('2023-02-10', '2023-01-15') AS days_difference; 
```

Here, the order of the dates is reversed compared to the previous example.

If you provide information about the specific database system you are working with, I can provide more targeted examples.

### 21. WITH:

The WITH clause allows you to define a temporary result set within a SELECT, INSERT, UPDATE, or DELETE statement, and then 
use that result set within the context of the main query.

Here's a basic example of using a CTE with the WITH clause:

```
WITH my_cte
     (
          column1,
          column2
     )
     AS
     (
            SELECT some_column1,
                   some_column2
            FROM   your_table
            WHERE  some_condition
     )
SELECT column1,
       column2
FROM   my_cte
WHERE  another_condition;
```
In this example:

my_cte is the name given to the Common Table Expression.
(column1, column2) specifies the columns that the CTE will include.

The SELECT statement inside the CTE defines the logic for generating the temporary result set.

The main query refers to the CTE and applies additional conditions if needed.

Common Table Expressions are useful for simplifying complex queries by breaking them down into more manageable parts. They can be particularly handy for recursive queries or for situations where you need to reuse a result set multiple times within a single query.

If you have a specific use case or scenario in mind, feel free to provide more details, and I can provide a more tailored example.

### 22. LIMIT:

In SQL, the LIMIT clause is used to restrict the number of rows returned by a query. It is often used in conjunction with the SELECT statement to control the result set size. 

The basic syntax is as follows:

```
SELECT column1,
       column2,
       …
FROM   your_table
LIMIT  number_of_rows_to_return; 
```
Here, number_of_rows_to_return is the maximum number of rows to be returned by the query. For example, to retrieve the first 10 rows from a table:

```
SELECT column1,
       column2,
       …
FROM   your_table
LIMIT  10; 
```

This query returns the first 10 rows from the result set.
It's worth noting that the actual behaviour of the LIMIT clause might differ between database systems. While many databases support LIMIT, some databases like Microsoft SQL Server use the TOP keyword instead.

### 23. OFFSET:

In SQL, the OFFSET clause is commonly used with the LIMIT clause to implement pagination. It is used to skip a specified number of rows from the beginning of the result set. 

The basic syntax is as follows:

```
SELECT column1,
       column2,
       …
FROM   your_table
ORDER  BY some_column
LIMIT  number_of_rows_to_return offset offset_value; 
```
Here:
- number_of_rows_to_return is the maximum number of rows to be returned.

- offset_value is the number of rows to skip before starting to return rows.

For example, to retrieve rows 11 to 20 from a table sorted by some_column:

```
SELECT column1,
       column2,
       …
FROM   your_table
ORDER  BY some_column
LIMIT  10 offset 10; 
```
This query skips the first 10 rows (OFFSET 10) and retrieves the next 10 rows (LIMIT 10).

Keep in mind that while many relational database systems support OFFSET and LIMIT, the exact syntax might vary. For instance, MySQL uses LIMIT and OFFSET, while PostgreSQL uses LIMIT and OFFSET as well but also supports a shorter syntax with LIMIT and FETCH FIRST

### 24. ROW_NUMBER():

ROW_NUMBER() is a window function in SQL that assigns a unique integer to each row within a partition of a result set. This function is commonly used for ranking or numbering rows based on a specified order. 

The basic syntax is as follows:

```
SELECT Row_number()
         OVER (
           partition BY partition_column
           ORDER BY order_column) AS row_num,
       column1,
       column2,
       …
FROM   your_table; 
```
Here's a breakdown of the components:

- ROW_NUMBER(): The window function that generates a unique number for each row within the specified window.

- OVER (PARTITION BY partition_column ORDER BY order_column): The window specification that defines how rows are partitioned and ordered. Rows with the same values in the partition_column will have their own numbering sequence, and the ORDER BY clause defines the order within each partition.

Here's a simple example. Suppose you have a table employees and you want to assign a unique number to each employee within their department based on their salary in descending order:

```
SELECT Row_number()
         OVER (
           partition BY department_id
           ORDER BY salary DESC) AS row_num,
       employee_id,
       employee_name,
       department_id,
       salary
FROM   employees; 
```
In this example:

PARTITION BY department_id: The numbering sequence restarts for each department.

ORDER BY salary DESC: The rows within each department are ordered by salary in descending order.

The result will include a column row_num representing the unique row number within each department.

Keep in mind that the ROW_NUMBER() function is part of the SQL standard and is supported by various relational database systems, including PostgreSQL, SQL Server, MySQL, and Oracle. However, syntax details or specific behaviours may vary slightly between database systems.

### 25. DESC & ASC:

In SQL, DESC and ASC are used in the ORDER BY clause to specify the sorting direction for columns. Here's a brief explanation of each:

**DESC (Descending Order):**

When used in the ORDER BY clause, DESC sorts the result set in descending order, meaning the values are arranged from the highest to the lowest

**Example:**

```
SELECT   column1,
         column2,
         …
FROM     your_table
ORDER BY column1 DESC,
         column2 DESC,
         ...;
```
**ASC (Ascending Order):**

ASC is used to sort the result set in ascending order, meaning the values are arranged from the lowest to the highest.

ASC is optional, as ascending order is the default if no sorting direction is specified.

**Example:**

```
SELECT   column1,
         column2,
         …
FROM     your_table
ORDER BY column1 ASC,
         column2 ASC,
         ...;

-- or simply
-- ORDER BY column1, column2, ...;
```
Here's a practical example using both DESC and ASC. Suppose you have a table employees with a salary column, and you want to retrieve the top 10 highest-paid and lowest-paid employees:

```
-- Top 10 highest-paid employees
SELECT employee_id,
       employee_name,
       salary
FROM   employees
ORDER  BY salary DESC
LIMIT  10;

-- Top 10 lowest-paid employees
SELECT employee_id,
       employee_name,
       salary
FROM   employees
ORDER  BY salary ASC
LIMIT  10; 
```
In the first query, ORDER BY salary DESC sorts the result set in descending order of salary, giving you the top 10 highest-paid employees. In the second query, ORDER BY salary ASC (or simply ORDER BY salary) sorts the result set in ascending order of salary, giving you the top 10 lowest-paid employees.

### 26. INSERT INTO:

Adds new rows of data to a table.

```
INSERT INTO table_name
            (column1,
             column2)
VALUES      (value1,
             value2); 
```

### 27. UPDATE:

Modifies existing data in a table.

```
UPDATE table_name
SET    column1 = value1
WHERE  CONDITION; 
```


### 28. DELETE:

Removes rows from a table based on a specified condition.

```
DELETE FROM table_name
WHERE  CONDITION; 
```

### 29. CREATE TABLE:

Defines / Creates a new table and its columns.

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


### 30. ALTER TABLE:

Modifies an existing table (e.g., adds or deletes columns).

```
ALTER TABLE table_name
  ADD column_name DATATYPE; 
```

### 31. DROP TABLE:

Deletes an existing table and its data.

```
DROP TABLE table_name; 
```

These keywords form the foundation of SQL queries and are used to interact with databases to retrieve, manipulate, and manage data.