SQL Important concepts

In SQL, a wildcard character is a symbol used within the LIKE clause to represent one or more
characters in a string. It allows you to perform flexible searches when you don’t know the exact string
you’re looking for. Here are the two primary wildcard characters used in SQL:

1. Percent (%): Represents zero, one, or multiple characters in a string.

o Example: LIKE 'A%' finds any string starting with "A" (e.g., "Alice," "Adam").

o Example: LIKE '%end' finds any string ending with "end" (e.g., "Weekend," "End").

2. Underscore (_): Represents a single character in a string.

o Example: LIKE 'J_n' finds any string with "J," followed by any character, and ending
with "n" (e.g., "Jon," "Jan").

o Example: LIKE '_at' finds any three-letter string ending with "at" (e.g., "Cat," "Mat").

These wildcards are especially useful for pattern matching in text fields, enabling you to search for
partial matches rather than exact matches

In SQL, ORDER BY and GROUP BY are both used for organizing and manipulating data, but they serve
different purposes. Here’s a breakdown of the key differences between them:

1. Purpose

 ORDER BY:
It is used to sort the result set of a query in a specific order, either ascending (ASC) or
descending (DESC).

o It affects the order of rows returned in the result.

o It does not change the actual data in the database.

 GROUP BY:
It is used to group rows that have the same values in specified columns into summary rows,
typically for aggregation purposes (e.g., COUNT(), SUM(), AVG(), MAX(), MIN()).

o It groups data before any aggregation operations are performed on it.

o It can be used with aggregate functions to perform calculations on grouped data.

2. Use Case

 ORDER BY:

o Use it when you need to arrange the result set in a particular order.

o It doesn’t change the structure of the data; it only sorts it.

 GROUP BY:

o Use it when you need to group rows based on common values, often to perform
aggregation.

o It is usually paired with aggregate functions like COUNT(), SUM(), AVG(), etc.

o Example: Grouping employees by department and counting the number of

3. How They Work

 ORDER BY:

o Sorts the results after all rows are selected from the database.

o It can be applied to individual columns or expressions in the SELECT clause.

 GROUP BY:

o Groups rows that have the same values into summary rows.

o It is generally applied to one or more columns to define the grouping criteria.

4. Syntax

 ORDER BY:

sql

Copy code

SELECT column1, column2

FROM table_name

ORDER BY column1 ASC, column2 DESC;

 GROUP BY:

sql

Copy code

SELECT column1, COUNT(*)

FROM table_name

GROUP BY column1;

5. Aggregation

 ORDER BY:

o Does not perform aggregation. It simply arranges the rows in a specified order.

 GROUP BY:
 o Often used with aggregation functions to summarize data.

o Example: If you want to calculate the total salary of employees in each department,
you would use GROUP BY to group the data by department and use SUM() to
calculate the total salary.

Example for Comparison

Consider a table employees:

employee_id name department salary

1 Alice HR 50000

2 Bob IT 60000

3 Charlie HR 55000

4 David IT 70000

Using ORDER BY

If you want to sort employees by salary in descending order:

sql

Copy code

SELECT name, salary

FROM employees

ORDER BY salary DESC;

Result:

name salary

David 70000

Bob 60000

Charlie 55000

Alice 50000

Here, the result is sorted by salary.

Using GROUP BY

If you want to group employees by department and get the total salary for each department:

sql

Copy code

SELECT department, SUM(salary) AS total_salary

FROM employees
GROUP BY department;

Result:

department total_salary

HR 105000

IT 130000

Here, the result is grouped by department and the salaries are summed for each department.

Key Differences

Aspect ORDER BY GROUP BY

Purpose Sort the result set Group rows based on column values

Operation Sorting Grouping and aggregation

Sorting data (ascending or Aggregating data (using functions like COUNT,

Affects The order of rows in the result set The number of rows (groups the data)

SQL Clauses Can be used in any query Used with aggregate functions

Aggregation Does not perform aggregation Used for aggregation (e.g., SUM(), COUNT())

Can They Be Used Together?

Yes! You can use both GROUP BY and ORDER BY in the same query. First, GROUP BY groups the data,
and then ORDER BY sorts the resulting groups.

Example:

sql

Copy code

SELECT department, SUM(salary) AS total_salary

FROM employees

GROUP BY department

ORDER BY total_salary DESC;

This will first group the employees by department, calculate the total salary for each department,
and then sort the departments in descending order of the total salary.

4o mini

------------------------------------------------------------------
The LIMIT clause in SQL is used to specify the maximum number of rows to return in a query result.
It's often used to retrieve a subset of data, especially when you only need a few records rather than
the entire dataset.

Syntax

sql

Copy code

SELECT column1, column2, ...

FROM table_name

WHERE condition

LIMIT number_of_rows;

 number_of_rows: The maximum number of rows you want to retrieve.

Example

Let's say you have a table called Employees with the following data:

id name salary

1 Alice 70000

2 Bob 80000

3 Charlie 75000

4 Diana 90000

5 Edward 85000

If you want to retrieve only the top 3 employees based on their salary, you could write:

sql

Copy code

SELECT name, salary

FROM Employees

ORDER BY salary DESC

LIMIT 3;

Output:

plaintext

Copy code

| name | salary |

| ------- | ------ |
| Diana | 90000 |

| Edward | 85000 |

| Bob | 80000 |

Using LIMIT with OFFSET

You can also use LIMIT with an OFFSET to skip a specific number of rows before starting to return
rows:

sql

Copy code

SELECT name, salary

FROM Employees

ORDER BY salary DESC

LIMIT 3 OFFSET 2;

In this example, it will skip the first 2 rows and then return the next 3 rows.

Compatibility Notes

 In MySQL, PostgreSQL, and SQLite, LIMIT is supported directly as shown.

 In SQL Server, you would use TOP instead of LIMIT.

 In Oracle, you would typically use ROWNUM or a FETCH clause with OFFSET.

-----------------------------------------------------------

# Write your MySQL query statement below

SELECT

CASE

WHEN COUNT(1) = 1 THEN num

ELSE NULL

END AS num

FROM MyNumbers

GROUP BY num

ORDER BY 1 DESC

LIMIT 1;

---------------------------------------------------------------------

The correct order of SQL clauses is:

2. FROM

3. JOIN (if any)

4. WHERE

5. GROUP BY

6. HAVING

7. ORDER BY

Aggregate functions like COUNT() should be used in the HAVING clause, not the WHERE clause, when
working with GROUP BY.

-----------------------------------------------------------------------------------------

In SQL, an ENUM is a data type that allows you to define a column with a predefined set of values.
It's useful when you want to restrict the values of a column to a specific list, like a set of options. For
example, a column that stores the status of an order might only accept values like 'Pending',
'Shipped', or 'Delivered'.

Here is how you define an ENUM column in a table:

sql

Copy code

CREATE TABLE Orders (

order_id INT,

status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled') NOT NULL

);

In this example:

 The status column can only contain one of the values 'Pending', 'Shipped', 'Delivered', or
'Cancelled'.

 The values in the ENUM are stored as numbers internally (starting from 1 for the first value),
but the values you see and work with are the strings you've defined.

Key Points:

 An ENUM ensures data integrity by limiting the possible values in a column.

 It is commonly used for columns that have a fixed set of values, like days of the week, status
codes, or user roles.
  In some databases (like MySQL), ENUM can be used with string values. However, not all SQL
databases (like PostgreSQL) have built-in ENUM support, though similar behavior can be
implemented using check constraints.

Example: Inserting Data

sql

Copy code

INSERT INTO Orders (order_id, status) VALUES (1, 'Shipped');

Example: Querying Data

sql

Copy code

SELECT * FROM Orders WHERE status = 'Pending';

If you try to insert a value that is not in the list of predefined options, SQL will throw an error.

---------------------------------------------------------------------------------------------

The syntax for DATE_SUB is:

sql

DATE_SUB(date, INTERVAL value unit)

 date: This is the starting date from which you want to subtract.

 INTERVAL value unit: This specifies the amount of time to subtract and the unit of time (like
DAY, MONTH, YEAR, etc.).

Example Usage

Here are a few examples demonstrating how to use DATE_SUB:

1. Subtracting Days:

sql

Copy code

SELECT DATE_SUB('2024-11-02', INTERVAL 7 DAY);

--------------------------------------------------------------------------------------------------------------------

In SQL, GROUP BY is a clause used to arrange identical data into groups. It’s often used with
aggregate functions like COUNT, SUM, AVG, MAX, and MIN to perform calculations on each group of
data, rather than on individual rows.

Basic Usage
The GROUP BY clause groups rows that have the same values in specified columns. Then, aggregate
functions can be applied to each group.

Example

Consider a Sales table:

product_id quantity price

1 10 5

2 20 15

1 15 5

2 10 15

If you want to calculate the total quantity sold per product_id, you would use GROUP BY:

SELECT product_id, SUM(quantity) AS total_quantity

FROM Sales

GROUP BY product_id;

Result:

product_id total_quantity

1 25

2 30

Explanation

1. GROUP BY product_id: Groups the rows by product_id.

2. SUM(quantity): Calculates the total quantity for each product_id group.

Key Points

 GROUP BY is often paired with aggregate functions.

 It collapses rows with the same values in specified columns into a single row per group.

 Each group can then have aggregate calculations applied to it.

To find or calculate some value in SQL, you typically use different types of queries depending on the
kind of value you're looking for. Below are several ways to find or calculate a value in SQL, depending
on your use case.

1. Finding a Specific Value in a Column

If you need to find a specific value in a column, you can use a SELECT query with a WHERE clause:

sql

Copy code
SELECT column_name

FROM table_name

WHERE column_name = value;

For example, if you want to find a specific employee's name from the employees table:

sql

Copy code

SELECT name

FROM employees

WHERE employee_id = 3;

This query will return the name of the employee with employee_id = 3.

2. Finding the Maximum or Minimum Value

If you want to find the maximum or minimum value in a column, you can use MAX() or MIN()
functions:

 Find the maximum salary:

sql

Copy code

SELECT MAX(salary) AS max_salary

FROM employees;

 Find the minimum salary:

sql

Copy code

SELECT MIN(salary) AS min_salary

FROM employees;

These queries will return the highest and lowest salary from the employees table, respectively.

3. Finding the Average Value

To calculate the average value of a column, you can use the AVG() function:

sql

Copy code

SELECT AVG(salary) AS average_salary

FROM employees;

This query calculates the average salary of all employees.

If you need to know the number of rows (records) in a table or based on a condition, use the
COUNT() function:

 Count total number of employees:

sql

Copy code

SELECT COUNT(*) AS total_employees

FROM employees;

 Count employees in a specific department:

sql

Copy code

SELECT COUNT(*) AS department_count

FROM employees

WHERE department = 'HR';

5. Finding a Value Using DISTINCT

If you want to find unique values in a column, you can use the DISTINCT keyword:

sql

Copy code

SELECT DISTINCT department

FROM employees;

This query will return a list of unique departments from the employees table.

6. Finding a Value Based on Multiple Conditions

You can find a value by specifying multiple conditions using the AND and OR operators:

sql

Copy code

SELECT name, salary

FROM employees

WHERE department = 'IT' AND salary > 50000;

This will find employees in the IT department whose salary is greater than 50,000.

7. Finding the Nth Highest Value

If you want to find the Nth highest value (e.g., the 2nd highest salary), you can use a subquery or
ROW_NUMBER() (if your database supports it):
  Using ROW_NUMBER() (for databases like PostgreSQL, SQL Server, etc.):

sql

Copy code

WITH RankedSalaries AS (

SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num

FROM employees

SELECT salary

FROM RankedSalaries

WHERE row_num = 2;

This query finds the second highest salary by assigning row numbers based on salary in descending
order.

 Using a subquery (for databases like MySQL, SQLite, etc.):

sql

Copy code

SELECT MAX(salary) AS second_highest_salary

FROM employees

WHERE salary < (SELECT MAX(salary) FROM employees);

This query finds the second highest salary by selecting the maximum salary that is less than the
highest salary.

8. Finding a Sum of Values

If you need to calculate the total sum of a column, you can use the SUM() function:

sql

Copy code

SELECT SUM(salary) AS total_salaries

FROM employees;

This will give you the total sum of all the salaries in the employees table.

9. Finding a Value Using Subqueries

If you need to find a value based on a result from another query, you can use a subquery:

sql

Copy code

SELECT name
FROM employees

WHERE salary = (SELECT MAX(salary) FROM employees);

This query finds the employee(s) who have the highest salary by using a subquery to find the
maximum salary first.

10. Using Variables or Expressions

You can also find or calculate values by performing calculations directly in the query:

sql

Copy code

SELECT salary * 12 AS annual_salary

FROM employees;

This will calculate the annual salary for each employee by multiplying the salary by 12.

Summary

To find a value in SQL, you typically use:

 SELECT statements with conditions (WHERE) to retrieve specific data.

 Aggregate functions like MAX(), MIN(), AVG(), COUNT(), and SUM() to calculate values.

 Subqueries for more complex queries, like finding the Nth highest value.