SQL Individual Class

 Sql Load data - E:\@udemy\SQL


SELECT * FROM employees;




SELECT 

    employee_id, 

    first_name, 

    last_name, 

    hire_date

FROM

    employees;




SELECT 

    first_name, 

    last_name, 

    salary, 

    salary * 1.05

FROM

    employees;


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


SELECT

employee_id,

first_name,

last_name,

hire_date,

salary

FROM

employees

ORDER BY

hire_date DESC;

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




SELECT 

    salary

FROM

    employees

ORDER BY salary DESC;


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

SQL DISTINCT operator

To remove duplicate rows from a result set, you use the DISTINCT operator in the SELECT



SELECT 

    DISTINCT salary

FROM

    employees

ORDER BY salary DESC;




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


SQL top clause

To top the number of rows returned by a select statement




SELECT TOP 3 * 

FROM

    employees1;

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

SQL FETCH clause


The OFFSET FETCH clause allows you to skip N first rows in a result set before starting to return any rows.


The ROW and ROWS, FIRST and NEXT are the synonyms. Therefore, you can use them interchangeably.

The offset_rows is an integer number which must be zero or positive. In case the offset_rows is greater than the number of rows in the result set, no rows will be returned.

The fetch_rows is also an integer number that determines the number of rows to be returned. The value of fetch_rows is equal to or greater than one.





SELECT 

    *

FROM employees1

ORDER BY 

    salary DESC

OFFSET 0 ROWS

FETCH NEXT 1 ROWS ONLY;


...........

OFFSET 1 ROWS

FETCH NEXT 1 ROWS ONLY;


....

OFFSET 1 ROWS

FETCH NEXT 1 ROWS ONLY;


.........

OFFSET 2 ROWS

FETCH NEXT 1 ROWS ONLY;



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



SQL WHERE clause

To select specific rows from a table, you use a WHERE clause in the SELECT statement


The following table shows the SQL comparison operators:

OperatorMeaning
=Equal to
<> (!=)Not equal to
<Less than
>Greater than
<=Less than or equal
>=Greater than or equal

SELECT
employee_id,
first_name,
last_name,
salary
FROM
employees
WHERE
salary > 14000
ORDER BY
salary DESC;


Equal to operator(=)

The equal to operator compares the equality of two expressions:

expression1 = expression2

Code language: SQL (Structured Query Language) (sql)

It returns true if the value of the left expression is equal to the value of the right expression; otherwise, it returns false.



SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    last_name = 'King';

Not equal to operator (<>)

The not equal to (<>) operator compares two non-null expressions and returns true if the value of the left expression is not equal to the right one; otherwise, it returns false.

expression1 <> expression2

Code language: SQL (Structured Query Language) (sql)

For example, the following statement returns all employees whose department id is not 8.

SELECT 
    employee_id, first_name, last_name, department_id
FROM
    employees
WHERE
    department_id <> 8


Greater than operator (>)

The greater than operator (>) compares two non-null expressions and returns true if the left operand is greater than the right operand; otherwise, the result is false.

expression1 > expression2

Code language: SQL (Structured Query Language) (sql)

For example, to find the employees whose salary is greater than 10,000, you use the greater than operator in the WHERE clause as follows:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary > 10000



Less than operator (<)

The less than operator compares two non-null expressions. The result is true if the left operand evaluates to a value that is lower than the value of the right operand; otherwise, the result is false.

The following shows the syntax of the less than operator:

expression1 < expression2

Code language: SQL (Structured Query Language) (sql)

For example, the statement below returns all employees whose salaries are less than 10,000:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary < 10000




Greater than or equal operator (>=)
The greater than or equal operator (>=) compares two non-null expressions. The result is true if the left expression evaluates to a value that is greater than the value of the right expression.
The following illustrates the syntax of the greater than or equal operator:
expression1 >= expression2

Code language: SQL (Structured Query Language) (sql)
For example, the following statement finds employees whose salaries are greater than or equal 9,000:
SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary >= 9000



SQL Logical Operators



ANDReturn true if both expressions are true
ANYReturn true if any one of the comparisons is true.
BETWEENReturn true if the operand is within a range
EXISTSReturn true if a subquery contains any rows
INReturn true if the operand is equal to one of the value in a list
LIKEReturn true if the operand matches a pattern
NOTReverse the result of any other Boolean operator.
ORReturn true if either expression is true


SELECT first_name, last_name, job_id, salary FROM employees WHERE job_id = 9 AND salary > 5000;


SELECT
first_name,
last_name,
hire_date
FROM
employees1
WHERE
YEAR (hire_date) >=1997
AND YEAR (hire_date) <= 1998;


---------

OR

SELECT
first_name,
last_name,
hire_date,
department_id
FROM
employees1
WHERE
department_id = 3
AND YEAR (hire_date) = 1997
OR YEAR (hire_date) = 1998
ORDER BY
first_name,
last_name;


Introduction to SQL BETWEEN operator

The BETWEEN operator is one of the logical operators in SQL. The BETWEEN operator checks if a value is within a range of values.

The syntax of the BETWEEN operator is as follows:

expression BETWEEN low AND high;
Code language: SQL (Structured Query Language) (sql)

The BETWEEN operator returns true if the expression is greater than or equal to ( >=) the low value and less than or equal to ( <=) the high value.

Technically, the BETWEEN is the equivalent to the following expression that uses the greater than or equal to (>=) and less than or equal to (<=) operators:

expression >= low AND expression <= high
Code language: SQL (Structured Query Language) (sql)

To compare a value with an exclusive range, you need to use the comparison operators less than (<) and greater than ( >).

NOT BETWEEN

To negate the result of the BETWEEN operator, you use the NOT operator:

expression NOT BETWEEN low AND high
Code language: SQL (Structured Query Language) (sql)

The NOT BETWEEN returns true if the expression is less than low or greater than (>) high; otherwise, it returns false.

Like the BETWEEN operator, you can rewrite the NOT BETWEEN operator using the less than (<) and greater than (>) operators with the OR operator as follows:

expression < low OR expression > high
Code language: SQL (Structured Query Language) (sql)

In practice, you often use the BETWEEN and NOT BETWEEN operator in the WHERE clause of the SELECT to select rows whose value of a column is within a specific range.

SQL BETWEEN operator examples

We’ll use the employees table from the sample database to illustrate how the BETWEEN operator works.

employees_table

1) Using the SQL BETWEEN opeator with numbers example

The following statement uses the BETWEEN operator to find all employees whose salaries are between 2,500 and 2,900:

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    salary BETWEEN 2500 AND 2900
ORDER BY 
	salary DESC;
Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+-------------+---------+
| employee_id | first_name | last_name   | salary  |
+-------------+------------+-------------+---------+
|         116 | Shelli     | Baida       | 2900.00 |
|         117 | Sigal      | Tobias      | 2800.00 |
|         126 | Irene      | Mikkilineni | 2700.00 |
|         118 | Guy        | Himuro      | 2600.00 |
|         119 | Karen      | Colmenares  | 2500.00 |
+-------------+------------+-------------+---------+

Notice that the result set includes the employees whose salaries are 2,500 and 2,900.

The following query returns the same result set as the above query. However, it uses comparison operators greater than or equal to (>=) and less than or equal to (<=) instead:

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    salary >= 2500 AND salary <= 2900
ORDER BY 
	salary;
Code language: SQL (Structured Query Language) (sql)

Try It

2) Using SQL NOT BETWEEN example

The following example uses the NOT BETWEEN operator to find all employees whose salaries are not in the range of 2,500 and 2,900:

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    salary NOT BETWEEN 2500 AND 2900
ORDER BY salary DESC;



3) Using SQL BETWEEN operator with a date ranges
The following example uses the BETWEEN operator to find all employees who joined the company between January 1, 1999, and December 31, 2000:
SELECT 
    employee_id, 
    first_name, 
    last_name, 
    hire_date
FROM
    employees
WHERE
    hire_date BETWEEN '1999-01-01' AND '2000-12-31'
ORDER BY 
	hire_date;

Introduction to SQL IN Operator

The IN is a logical operator in SQL. The IN operator returns true if a value is in a set of values or false otherwise.

The following illustrates the syntax of the IN operator:

expression IN (value1,value2,...)
Code language: SQL (Structured Query Language) (sql)

Technically, you can substitute the IN operator with the = and OR operators The condition that uses the IN operator can be rewritten using one or more OR operators as follows:

expression = value1 OR expression = value2 OR ...
Code language: SQL (Structured Query Language) (sql)

To negate the IN operator, you use the NOT operator:

expression NOT IN (value1, value2,...)
Code language: SQL (Structured Query Language) (sql)

The NOT IN operator returns true if the expression does not equal any values in the list or false otherwise.

To substitute the IN operator, you can use the != and AND operators as follows:

expression != value1 AND expression != value2 AND...
Code language: SQL (Structured Query Language) (sql)

Notice that if any value in the list (value1,value2,...) is null, the IN operator returns no rows.

In practice, you often use the IN and NOT IN operators in the  WHERE clause of the SELECT statement to select rows with a value in a set of values. Also, you’ll use the IN operator in subqueries.

SQL IN examples

We will use the  employees table in the sample database to demonstrate the functionality of the IN operator.

employees_table

The following example uses the IN operator to find employees with the job id is 8, 9, or 10:

SELECT
	employee_id,
	first_name,
	last_name,
	job_id
FROM
	employees
WHERE
	job_id IN (8, 9, 10)
ORDER BY
	job_id;

https://www.sqltutorial.org/sql-like/


https://www.sqltutorial.org/sql-is-null/


https://www.sqltutorial.org/sql-not/






















Comments

Post a Comment

Popular Posts