SQL Tutorial Where, order by, and or Not.
SQL Where
In SQL, the WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition. The syntax of the WHERE clause is as follows:
SELECT column1, column2, …
FROM table_name
WHERE condition;
Here, column1, column2, … are the fields that you want to retrieve from the table, table_name is the name of the table from which you want to retrieve the data, and condition is a filter that determines which rows to return.
The condition can be any valid expression that evaluates to true or false. For example:
SELECT *
FROM Employees
WHERE Salary > 50000;
In this case, the SQL statement will select all the columns from the “Employees” table where the “Salary” is greater than 50000.
You can use various operators in the WHERE clause such as:
= (equal to)
<> or != (not equal to)
< (less than)
> (greater than)
<= (less than or equal to)
>= (greater than or equal to)
AND, OR (logical operators)
IN (to specify multiple possible values)
NOT (negation operator)
LIKE (pattern matching)
It’s essential to note that the WHERE clause is not only used with the SELECT statement, but it can also be used with the UPDATE, DELETE, and INSERT INTO statements to apply conditions on the data being affected by these statements.
SQL order by
In SQL, the ORDER BY clause is used to sort the result set in either ascending or descending order based on one or more columns. The basic syntax for the ORDER BY clause is as follows:
SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;
Here, column1, column2, … are the fields based on which you want to sort the result set, table_name is the name of the table from which you want to retrieve the data, and ASC or DESC specifies whether to sort the result set in ascending or descending order, respectively.
For example:
SELECT *
FROM Employees
ORDER BY Salary DESC;
This SQL statement will select all the columns from the “Employees” table and sort the result set based on the “Salary” column in descending order.
You can also use the ORDER BY clause to sort based on multiple columns. For instance:
SELECT *
FROM Employees
ORDER BY Department, Salary DESC;
In this case, the result set will be sorted first by the “Department” column in ascending order and then by the “Salary” column in descending order.
It’s important to note that the ORDER BY clause should be the last clause in your SQL statement. If you use it with a SELECT statement, it will sort the result set of the query. However, it cannot be used with DELETE, INSERT INTO, or UPDATE statements.
SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;
Here, condition1, condition2, etc. are the conditions that you want to apply. The AND operator ensures that all the specified conditions must be true for a row to be included in the result set.
For example:
SELECT *
FROM Employees
WHERE Department = ‘Sales’ AND Salary > 50000;
This SQL statement will select all the columns from the “Employees” table where the “Department” is ‘Sales’ and the “Salary” is greater than 50000.
You can combine multiple conditions using the AND operator to create complex filtering criteria. It is essential to use parentheses to clarify the logic if you have multiple AND and OR conditions together.
Here is an example of using multiple conditions with the AND operator:
SELECT *
FROM Employees
WHERE (Department = ‘Sales’ AND Salary > 50000) AND (Age < 40);
In this case, the SQL statement will select all the columns from the “Employees” table where the “Department” is ‘Sales’, the “Salary” is greater than 50000, and the “Age” is less than 40.
The AND operator can be used in conjunction with other operators, such as OR, NOT, and so on, to create more complex filtering conditions in the WHERE clause of an SQL statement.
SQL or
In SQL, the OR operator is used to combine multiple conditions in a WHERE clause to retrieve records that fulfill at least one of the specified conditions. The syntax is as follows:
SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR condition3 …;
Here, condition1, condition2, etc. are the conditions that you want to apply. The OR operator ensures that if any of the specified conditions is true for a row, it will be included in the result set.
For example:
SELECT *
FROM Employees
WHERE Department = ‘Sales’ OR Salary > 50000;
This SQL statement will select all the columns from the “Employees” table where the “Department” is ‘Sales’ or the “Salary” is greater than 50000.
You can combine multiple OR conditions to create complex filtering criteria. It’s essential to use parentheses to specify the order of operations if you’re combining OR with AND.
Here is an example of using multiple OR conditions:
SELECT *
FROM Employees
WHERE (Department = ‘Sales’ OR Department = ‘Marketing’) OR Salary > 50000;
In this case, the SQL statement will select all the columns from the “Employees” table where the “Department” is either ‘Sales’ or ‘Marketing’, or the “Salary” is greater than 50000.
The OR operator can be used in combination with other logical operators, such as AND, NOT, and so on, to create complex filtering conditions in the WHERE clause of an SQL statement.
SQL not
In SQL, the NOT operator is used to negate a condition in the WHERE clause. It can be used in combination with other operators such as =, <, >, or with the SQL keywords like IN, LIKE, EXISTS, etc. The basic syntax is as follows:
SELECT column1, column2, …
FROM table_name
WHERE NOT condition;
Here, condition is the condition that you want to negate. The NOT operator ensures that the rows which do not satisfy the condition are included in the result set.
For example:
SELECT *
FROM Employees
WHERE NOT Department = ‘Sales’;
This SQL statement will select all the columns from the “Employees” table where the “Department” is not ‘Sales’.
You can also use the NOT operator with other operators like NOT LIKE or NOT IN. For example:
SELECT *
FROM Employees
WHERE NOT Salary > 50000;
This SQL statement will select all the columns from the “Employees” table where the “Salary” is not greater than 50000.
It’s important to note that the NOT operator can be used in combination with other logical operators such as AND and OR to create complex filtering conditions in the WHERE clause of an SQL statement.
SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR condition3 …;
Here, condition1, condition2, etc. are the conditions that you want to apply. The OR operator ensures that if any of the specified conditions is true for a row, it will be included in the result set.
For example:
SELECT *
FROM Employees
WHERE Department = ‘Sales’ OR Salary > 50000;
This SQL statement will select all the columns from the “Employees” table where the “Department” is ‘Sales’ or the “Salary” is greater than 50000.
You can combine multiple OR conditions to create complex filtering criteria. It’s essential to use parentheses to specify the order of operations if you’re combining OR with AND.
Here is an example of using multiple OR conditions:
SELECT *
FROM Employees
WHERE (Department = ‘Sales’ OR Department = ‘Marketing’) OR Salary > 50000;
In this case, the SQL statement will select all the columns from the “Employees” table where the “Department” is either ‘Sales’ or ‘Marketing’, or the “Salary” is greater than 50000.
The OR operator can be used in combination with other logical operators, such as AND, NOT, and so on, to create complex filtering conditions in the WHERE clause of an SQL statement.