Base de dades de treball: HR. Consultes simples

De wikijoan
Salta a la navegació Salta a la cerca

T1. sample database

thunbnail

T2: secció 1, 2, 3 i 4 del tutorial

Per executr online els exemples:

Les consultes executades a classe (que són les que hi ha al tutorial que estem seguint), corresponents a aquestes seccions del tutorial:

/*
$ cd /home/joan/DAM_M02_2122/UF2/HR
$ mysql -h localhost -u alumne -pkeiL2lai --verbose HR < ./apunts_sqltutorial.sql > sortida.txt

Section 1: Introduction to SQL
================================
https://www.sqltutorial.org/what-is-sql/

What is SQL – give you a brief overview of the SQL language and its popular dialects.
SQL Syntax – provide you with the syntax of the SQL language.
SQL Sample Database – introduce you to an HR sample database.
*/

/*
Section 2: Querying Data
=================================
https://www.sqltutorial.org/sql-select/

SELECT Statement – show you how to query data from a single table by using the simplest form of the SELECT statement.

*/

/*
SELECT 
    select_list
FROM
    table_name;

SELECT * FROM table_name;
*/

/*
Besides the SELECT and FROM clauses, the SELECT statement can contain many other clauses such as

 WHERE – for filtering data based on a specified condition.
 ORDER BY – for sorting the result set.
 LIMIT – for limiting rows returned.
 JOIN – for querying data from multiple related tables.
 GROUP BY – for grouping data based on one or more columns.
 HAVING – for filtering groups.
*/

SELECT * FROM employees;

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    hire_date
FROM
    employees;

SELECT employee_id, first_name, last_name, hire_date FROM employees;

# diferències entre MySQL, SQL Server, SQLite:
SELECT 
    employee_id,
    first_name,
    last_name,
    FLOOR(DATEDIFF('2021-01-01', hire_date) / 365) YoS
FROM
    employees;

/*
Section 3: Sorting Data
================================
https://www.sqltutorial.org/sql-order-by/

ORDER BY Clause – sort the data by one or more columns in the ascending and/or descending order.
*/

/*
SELECT 
    column1, column2
FROM
    table_name
ORDER BY column1 ASC , 
         column2 DESC;
*/

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

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

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

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

/*
Section 4: Filtering Data
================================
https://www.sqltutorial.org/sql-distinct/

DISTINCT  – show you how to remove duplicates from the result set.
LIMIT – constrain a number of rows returned by a query using the LIMIT and OFFSET clause.
FETCH – learn how to skip N rows in a result set before starting to return any rows.
WHERE Clause – filter data based on specified conditions.
Comparison operators – learn how to use the comparison operators including greater than, greater than or equal, less than, less than or equal, equal, and not equal to form the condition 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.
*/

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

SELECT salary
FROM employees
ORDER BY salary DESC;

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC;

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

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

# valors nuls
SELECT DISTINCT phone_number
FROM employees;

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

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

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;

# en mysql es pot fer d'una forma més curta:
SELECT employee_id, first_name, last_name
FROM employees
ORDER BY first_name
LIMIT 3 , 5;

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

# el 2n salari més alt el trobarem de la següent manera:
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 , 1;

# per saber qui són les persones que cobren 17000:
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary = 17000;

# podem combinar totes dues. Això són les subqueries (subconsultes):
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary = (SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 , 1);

/*
SELECT 
    column1, column2, ...
FROM
    table
WHERE
    condition;
*/

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;

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

SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE hire_date >= '1999-01-01'
ORDER BY hire_date DESC;

SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE YEAR (hire_date) = 1999
ORDER BY hire_date DESC;

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

# compte amb el NULL que funciona de manera diferent:
SELECT employee_id, first_name, last_name, phone_number
FROM employees
WHERE phone_number = NULL;

SELECT employee_id, first_name, last_name, phone_number
FROM employees
WHERE phone_number IS NULL;

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

SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id <> 8 AND department_id <> 10
ORDER BY first_name , last_name; 

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

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 10000 AND department_id = 8
ORDER BY salary DESC;

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary < 10000
ORDER BY salary DESC;

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary >= 9000
ORDER BY salary;

T3: aplicació BDQuest

Select employees.png

Fem una explicació a classe del funcionament de l'aplicació web. Com que és la primera vegada que aquesta aplicació s'utilitza a classe, els alumnes reportaran els problmemes d'usabilitat que segur que hi haurà, i s'intentarà incorporar tots les millores proposades.

T4: continuem amb la teoria bàsica de SQL (secció 4)

SELECT 
first_name, last_name, salary
FROM
employees
WHERE
salary > 5000 AND salary < 7000
ORDER BY salary;

SELECT 
first_name, last_name, salary
FROM
employees
WHERE
salary = 7000 OR salary = 8000
ORDER BY salary;

SELECT 
first_name, last_name, phone_number
FROM
employees
WHERE
phone_number IS NULL
ORDER BY first_name , last_name;

SELECT 
first_name, last_name, salary
FROM
employees
WHERE
salary BETWEEN 9000 AND 12000
ORDER BY salary;    

SELECT 
first_name, last_name, department_id
FROM
employees
WHERE
department_id IN (8, 9)
ORDER BY department_id;

SELECT 
employee_id, first_name, last_name
FROM
employees
WHERE
first_name LIKE 'jo%'
ORDER BY first_name;

SELECT 
employee_id, first_name, last_name
FROM
employees
WHERE
first_name LIKE '_h%'
ORDER BY first_name;

# The ALL operator compares a value to all values in another value set. The ALL operator must be preceded by a comparison operator and followed by a subquery.

SELECT 
first_name, last_name, salary
FROM
employees
WHERE
salary >= ALL (SELECT salary FROM employees WHERE department_id = 8)
ORDER BY salary DESC;

# The ANY operator compares a value to any value
SELECT 
first_name, last_name, salary
FROM
employees
WHERE
salary > ANY(SELECT AVG(salary) FROM employees GROUP BY department_id)
ORDER BY first_name , last_name; 

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 employees
WHERE YEAR (hire_date) >=1997 AND YEAR (hire_date) <= 1998;

SELECT first_name, last_name, hire_date
FROM employees
WHERE YEAR (hire_date) = 1997 OR YEAR (hire_date) = 1998
ORDER BY first_name, last_name;

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

/*If you don’t use the parentheses, the query will retrieve employees who joined the company in 1997 and worked in department id 3 or employees who joined the company in 1998 regardless of departments.

This is because the database system evaluates the OR operator after the AND operator.
Conclusió: s'han d'utilitzar els parèntesi
*/
SELECT first_name, last_name, hire_date, department_id
FROM employees
WHERE
department_id = 3 AND YEAR (hire_date) = 1997 OR YEAR (hire_date) = 1998
ORDER BY first_name, last_name;

SELECT first_name, last_name, hire_date
FROM employees
WHERE
YEAR (hire_date) = 2000
OR YEAR (hire_date) = 1999
OR YEAR (hire_date) = 1990;

# millor fer:
SELECT first_name, last_name, hire_date
FROM employees
WHERE YEAR (hire_date) IN (1990, 1999, 2000)

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

# el between és equivalent a:
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary >= 2500 AND salary <= 2900
ORDER BY salary;

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

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;

SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE hire_date NOT BETWEEN '1989-01-01' AND '1992-12-31'
ORDER BY hire_date;

SELECT employee_id, first_name, last_name, year(hire_date) joined_year
FROM employees
WHERE year(hire_date) BETWEEN 1990 and 1993    
ORDER BY hire_date;

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

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

# primer fem una select senzilla
SELECT department_id
FROM departments
WHERE
department_name = 'Marketing' OR department_name = 'Sales'

# i ara aprofitem la select anterior per fer una subquery
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id IN (SELECT 
department_id FROM departments
WHERE department_name = 'Marketing' OR department_name = 'Sales')

SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE 'Da%';

SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE '%er';

SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE '%an%';

# seguit de dos caràcters:
SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE 'Jo__';

SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE '%are_';

SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE 'S%' AND first_name NOT LIKE 'Sh%'
ORDER BY first_name;

# no funciona això:
SELECT employee_id, first_name, last_name, phone_number
FROM employees
WHERE phone_number = NULL;

# s'ha de fer així:
SELECT employee_id, first_name, last_name, phone_number
FROM employees
WHERE phone_number IS NULL;

SELECT employee_id, first_name, last_name, phone_number
FROM employees
WHERE phone_number IS NOT NULL;

SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id NOT IN (1, 2, 3)
ORDER BY first_name;

SELECT first_name, last_name
FROM employees
WHERE first_name NOT LIKE 'D%'
ORDER BY first_name;

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary NOT BETWEEN 3000 AND 5000
ORDER BY salary;

SELECT employee_id, first_name, last_name
FROM employees e
WHERE NOT EXISTS (
   SELECT employee_id FROM dependents d WHERE d.employee_id = e.employee_id
);

/*
SELECT
inv_no AS invoice_no,
amount,
due_date AS 'Due date',
cust_no 'Customer No'
FROM invoices;
*/

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

# dóna error, perquè encara no sap què és new_salary:
SELECT first_name, last_name, salary * 1.1 AS new_salary
FROM employees
WHERE new_salary > 5000

# però aquesta sí que funciona:
SELECT first_name, last_name, salary * 1.1 AS new_salary
FROM employees
ORDER BY new_salary


creat per Joan Quintana Compte, novembre 2021