Documentation SQL

SQL Syntax

Get the first names of employees who were hired in 2000.

SELECT 
    first_name
FROM
    employees
WHERE
    YEAR(hire_date) = 2000;

The SELECT first_name, FROM employees, and WHERE are clauses in the SQL
statement. Some clauses are mandatory e.g., the SELECT and FROM clauses whereas others are optional such as the WHERE clause.

SQL commands

SQL uses the semicolon (;) to mark the end of a command.
Each command is composed of tokens that can be literals, keywords, identifiers, or expressions. Tokens are separated by space, tabs, or newlines.

SELECT 
    first_name, last_name
FROM
    employees;

DELETE FROM employees 
WHERE
    hire_date < '1990-01-01';

Literals

are explicit values which are also known as constants.
SQL provides three kinds of literals: string, numeric, and binary.

# String literal consists of one or more alphanumeric characters surrounded by single quotes
'John'
'1990-01-01'
'50'

SQL is case sensitive with respect to string literals, so the value ‘John’ is not the same as ‘JOHN’.

Keywords

SELECT, INSERT, UPDATE, DELETE, and DROP

Identifiers

refer to specific objects in the database such as tables, columns, indexes, etc.

-- statements are equivalent

Select  * From employees;

SELECT * FROM EMPLOYEES;

select * from employees;

SELECT * FROM employees;

Comments

A comment is denoted by two consecutive hyphens ( –) that allow you to comment the remaining line.

SELECT 
    employee_id, salary
FROM
    employees
WHERE
    salary < 3000;-- employees with low salary

To document the code that can span multiple lines, use the multiline C-style notation

/* increase 5% for employees whose salary is less than 3,000 */
UPDATE employees 
SET 
    salary = salary * 1.05
WHERE
    salary < 3000;

–

Section 2: Querying Data

Introduction to SQL SELECT statement

SELECT statement selects data from one or more tables

Syntax

from table_name, select data from these columns (select_list)

SELECT 
    select_list
FROM
    table_name;

1) SQL SELECT – selecting data from all columns example

FROM all the columns of the table

SELECT * FROM table_name;

2) SQL SELECT – selecting data from specific columns

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    hire_date
FROM
    employees;

3) SQL SELECT – performing a simple calculation

SELECT 
    first_name, 
    last_name, 
    salary, 
    salary * 1.05
FROM
    employees;
    -- salary * 1.05 adds 5% to the salary of every employee.

To assign an expression or a column an alias, you specify the AS keyword followed by the column alias

expression AS column_alias

SELECT statement uses the new_salary as the column alias for the salary * 1.05 expression

SELECT 
    first_name, 
    last_name, 
    salary, 
    salary * 1.05 AS new_salary
FROM
    employees;

+-------------+-------------+----------+------------+
| first_name  | last_name   | salary   | new_salary |
+-------------+-------------+----------+------------+
| Steven      | King        | 24000.00 | 25200.0000 |
| Neena       | Kochhar     | 17000.00 | 17850.0000 |
| Lex         | De Haan     | 17000.00 | 17850.0000 |

–

Section 3: Sorting Data

sort the data by one or more columns in the ascending and/or descending order.

SELECT 
    select_list
FROM
    table_name
ORDER BY 
    sort_expression [ASC | DESC];

First, place the ORDER BY clause after the FROM clause.
The database will evaluate the SELECT statement with the ORDER BY * clause in the following order: FROM > SELECT > ORDER BY.
Second, specify a sort expression after the ORDER BY clause. The sort expression specifies the sort criteria.
Third, use ASC option to sort the result set by the sort expression in ascending order and DESC to sort the result set by the sort expression in the descending order.

SELECT 
    select_list
FROM
    table_name
ORDER BY 
    sort_expression_1 [ASC | DESC],
    sort_expression_2 [ASC | DESC];

Example

SELECT
    employee_id,
    first_name,
    last_name,
    hire_date,
    salary
FROM
    employees
ORDER BY
    first_name;

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         121 | Adam        | Fripp       | 1997-04-10 |  8200.00 |
|         115 | Alexander   | Khoo        | 1995-05-18 |  3100.00 |
|         103 | Alexander   | Hunold      | 1990-01-03 |  9000.00 |
|         193 | Britney     | Everett     | 1997-03-03 |  3900.00 |
|         104 | Bruce       | Ernst       | 1991-05-21 |  6000.00 |
|         179 | Charles     | Johnson     | 2000-01-04 |  6200.00 |
|         109 | Daniel      | Faviet      | 1994-08-16 |  9000.00 |
|         105 | David       | Austin      | 1997-06-25 |  4800.00 |
|         114 | Den         | Raphaely    | 1994-12-07 | 11000.00 |
|         107 | Diana       | Lorentz     | 1999-02-07 |  4200.00 |
...

NOTE: need to check the difference here

SELECT
    employee_id,
    first_name,
    last_name,
    hire_date,
    salary
FROM
    employees
ORDER BY
    first_name DESC;

ORDER BY clause to sort employees by salary from high to low

SELECT
    employee_id,
    first_name,
    last_name,
    hire_date,
    salary
FROM
    employees
ORDER BY
    salary DESC;

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         100 | Steven      | King        | 1987-06-17 | 24000.00 |
|         101 | Neena       | Kochhar     | 1989-09-21 | 17000.00 |
|         102 | Lex         | De Haan     | 1993-01-13 | 17000.00 |
|         145 | John        | Russell     | 1996-10-01 | 14000.00 |
|         146 | Karen       | Partners    | 1997-01-05 | 13500.00 |

in ascending order

SELECT
    employee_id,
    first_name,
    last_name,
    hire_date,
    salary
FROM
    employees
ORDER BY
    hire_date;

in descending order

SELECT
    employee_id,
    first_name,
    last_name,
    hire_date,
    salary
FROM
    employees
ORDER BY
    hire_date DESC;

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         100 | Steven      | King        | 1987-06-17 | 24000.00 |
|         200 | Jennifer    | Whalen      | 1987-09-17 |  4400.00 |
|         101 | Neena       | Kochhar     | 1989-09-21 | 17000.00 |

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         179 | Charles     | Johnson     | 2000-01-04 |  6200.00 |
|         113 | Luis        | Popp        | 1999-12-07 |  6900.00 |
|         119 | Karen       | Colmenares  | 1999-08-10 |  2500.00 |

–

Section 4: Filtering Data

DISTINCT – show you how to remove duplicates from the result set.

SELECT DISTINCT
    column1, column2, ...
FROM
    table1;

DISTINCT only removes the duplicate rows from the result set. It doesn’t delete duplicate rows in the table.
If you use one column after the DISTINCT operator, the DISTINCT operator uses values in that column to evaluate duplicates.
If you use two or more columns, the DISTINCT will use the combination of values in those columns to evaluate the duplicate.
If you want to select two columns and remove duplicates in one column, you should use the GROUP BY clause instead.
1) Using SQL DISTINCT operator on one column example
selects the salary data from the salary column of the employees table and sorts them from high to low:

SELECT 
    salary
FROM
    employees
ORDER BY salary DESC;

=> The result set has some duplicates.

SELECT 
    DISTINCT salary
FROM
    employees
ORDER BY salary DESC;

Now the result set doesn’t contain any duplicate salary values.

2) Using SQL DISTINCT operator on multiple columns example

SELECT
    job_id,
    salary
FROM
    employees
ORDER BY
    job_id,
    salary DESC;

OUTPUT
+--------+----------+
| job_id | salary   |
+--------+----------+
|      1 |  8300.00 |
|      2 | 12000.00 |
|      3 |  4400.00 |
|      4 | 24000.00 |
|      5 | 17000.00 |
|      5 | 17000.00 |

ich glaub das stimmt was nicht. salary ist in keiner Order. Should look more like this

job_id  salary
 24000
 17000
 17000

some duplicate rows e.g., job id 5 salary 17000. It means that there are two employees with the same job id and salary. to remove the duplicate values in job id and salary

SELECT DISTINCT
    job_id,
    salary
FROM
    employees
ORDER BY
    job_id,
    salary DESC;

SQL DISTINCT and NULL

NULL means unknown or missing data.

SELECT DISTINCT phone_number
FROM employees
ORDER BY phone_number;

=> Use DISTINCT operator in the SELECT clause to remove duplicate rows from the result set.

–

SQL LIMIT & OFFSET clause
- to limit the number of rows returned from a query.
- constrain a number of rows returned by a query using the LIMIT and OFFSET clause

SELECT 
    column_list
FROM
    table1
ORDER BY column_list
LIMIT row_count OFFSET offset;

The LIMIT row_count determines the number of rows (row_count) returned by the query.
The OFFSET offset clause skips the offset rows before beginning to return the rows. If you omit it, the query will return the row_count rows from the first row returned by the SELECT clause.
When you use the LIMIT clause, it is important to use an ORDER BY clause to ensure the order of rows in the result set.
LIMIT clause is available only in some database systems only such as MySQL, PostgreSQL, SQLite, Sybase SQL Anywhere, and HSQLDB. If you use SQL Server, you can use the SELECT TOP instead. => LIMIT & OFFSET is not SQL standard.

SELECT 
    employee_id, 
    first_name, 
    last_name
FROM
    employees
ORDER BY 
    first_name;

# adding now LIMIT, after it has been order makes it readable

SELECT 
    employee_id, 
    first_name, 
    last_name
FROM
    employees
ORDER BY 
    first_name
LIMIT 5;   

SELECT 
    employee_id, first_name, last_name
FROM
    employees
ORDER BY first_name
LIMIT 5 OFFSET 3;

# it will drop the first three and then display the next five

Using SQL LIMIT to get the top N rows with the highest or lowest value

-- get the top five employees with the highest salaries
SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM
    employees
ORDER BY 
    salary DESC
LIMIT 5;

To get the top five employees with the lowest salary, you sort the employees by salary in the ascending order instead.
Getting the rows with the Nth highest value

-- to get employees who have the 2nd highest salary
SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM
    employees
ORDER BY 
    salary DESC
LIMIT 1 OFFSET 1;

query will fail if there are two employees who have the same highest salary.
there is a subquery verison to this.
FETCH
- learn how to skip N rows in a result set before starting to return any rows.
- The OFFSET FETCH clause allows you to skip N first rows in a result set before starting to return any rows (similar function to the LIMIT clause).
sql-fetch
Syntax

OFFSET offset_rows { ROW | ROWS }
FETCH { FIRST | NEXT } [ fetch_rows ] { ROW | ROWS } ONLY

Example

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM employees
ORDER BY 
    salary DESC
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY;

the ORDER BY clause sort the employees by salary from high to low. The OFFSET clause skips zero rows and the FETCH clause returns the first row.

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM employees
ORDER BY 
    salary DESC
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

sorts the employees by salary, skips the first five employees with the highest salary, and fetches the next five ones.

–

WHERE Clause
- filter data based on specified conditions.

SELECT 
    column1, column2, ...
FROM
    table_name
WHERE
    condition;

Example

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

SELECT
    employee_id,
    first_name,
    last_name,
    department_id
FROM
    employees
WHERE
    department_id = 5
ORDER BY
    first_name;

–

Comparison operators
- learn how to use the comparison operators in the WHERE clause.

–

Logical operators
- introduce the logical operators and how to use them to test for the truth of a condition.

–

AND operator
- combine multiple Boolean expressions using the AND logical operator.

–

OR operator
- show you how to use another logical operator OR to combine multiple Boolean expressions.

–

BETWEEN Operator
- guide you to use the BETWEEN operator to select data within a range of values.

–

IN Operator
- show you how to use the IN operator to check whether a value is in the list of values.

–

LIKE Operator
- query data based on a specified pattern.

–

IS NULL Operator
- introduce the NULL concepts and show you how to check whether an expression is NULL or not.

–

NOT operator
- show you how to negate a Boolean expression by using the NOT operator.

–

Section 5: Conditional Expressions

add if-then-else logic to the SQL statements

–

Section 6: Joining Multiple Tables

–

Section 7: Aggregate Functions

–

Section 8: Grouping Data

–

Section 9: SET Operators

–

Section 10. Subquery

–

Section 11: Modifying data

–

Section 12: Working with table structures

–

Section 13: Constraints

SQL in the cmd line

finder » folder » ‘open in terminal’

>> sqlite3
>> sqlite> .open HR.db
>> SELECT COUNT(*) FROM employees WHERE salary IS NOT NULL;

SELECT COUNT(*) FROM EVSUV_20242023 WHERE Year IS NOT NULL;
# OUTPUT
70

# To see a list of all available tables in your SQLite database 
# using the sqlite3 command-line interface
sqlite> .tables
EVSUV_20242023

My QA Projects

QA Projects I was involded.