Requêtes SQL

Requêtes faites le 27/10/2007

Commandes de base

SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
--------------------------------------
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100,101, 201);
--------------------------------------
SELECT *
FROM employees
WHERE first_name LIKE 'S%'; (prénoms qui commencent par S majuscule)
--------------------------------------
SELECT *
FROM employees
WHERE salary>1500 AND first_name LIKE '%a'; (prénoms qui finissent par a minuscule)
--------------------------------------
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;
--------------------------------------
SELECT *
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%man';
--------------------------------------
SELECT last_name, job_id
FROM employees
WHERE job_id
NOT IN ('it prog', 'ST_CLERK', 'SA_REP');
--------------------------------------
SELECT last_name, job_id
FROM employees
ORDER BY hire_date; (derniere commande de notre requête) (asc par défaut)
---------------------------------------
SELECT last_name, job_id
FROM employees
ORDER BY hire_date desc; (tri de façon décroissante)
---------------------------------------
Exercice
Afficher le salaire annuel des employés et ce salaire annuel s'il est augmenté de 10%.

SELECT last_name, salary, salary*12 AS "salaire_annuel", ((salary*12)/10)+salary*12 AS "augment_10pc"
FROM employees
WHERE (salary*12*1.1)>250000
ORDER BY salary desc;
--------------------------------------
SELECT last_name, salary, salary*12 AS "annsal", ((salary*12)/10)+salary*12 AS "augment_10pc"
FROM employees
WHERE (salary*12*1.1)>250000
ORDER BY "annsal";
-------------------------------------
SELECT last_name, salary, salary*12 annsal, ((salary*12)/10)+salary*12 augment_10pc
FROM employees
WHERE (salary*12*1.1)>250000
ORDER BY annsal;
-----------------------------------

Utilisation des fonctions monoligne afin de personnaliser la sortie
Les fonctions monoligne renvoient un résultat pour une ligne donnée.
Fonctions sur les dates (conversion entre le format char et le format date)

Fonctions de manipulation de la casse :
- LOWER (transforme toute la chaîne dans la cellule en minuscules)
- UPPER (transforme toute la chaîne dans la cellule en majuscules)
- INITCAP (transforme la première lettre dans la cellule en majuscules)

Fonctions de manipulation de caractères
- CONCAT
- SUBSTR
- LENGTH
- INSTR
- LPAD | RPAD (left pad | right pad : remonte la chaîne soit en partant de la gauche soit en partant de la droite)
- TRIM
- REPLACE

SELECT employee_id, last_name, department_id
FROM employees
WHERE Lower (last_name) = 'higgins'; (Affiche : 205 | Higgins | 110)

Fonction - Résultat
CONCAT('Hello', 'World') - HelloWorld
SUBSTR('HelloWorld',1,5) - Hello
LENGTH('HelloWorld') - 10
INSTR ('HelloWorld', 'W') - 6
LPAD(salary, 10, '*' ) - *****24000
RPAD(salary,10, '*' ) - 24000*****
REPLACE ('JACK and JUE', 'J', 'BL') - BLACK and BLUE
TRIM('H' FROM 'HelloWorld') - elloWorld

Exercice
Remonter les employés dont le nom contient a et dont la taille du prénom est supérieur à 5.

SELECT first_name, last_name, Length (first_name)
FROM employees
WHERE Length (first_name)>5 AND last_name LIKE '%a%' ;

Fonction - Résultat
ROUND(45.926, 2) - 45.93
TRUNC(45.926, 2) - 45.92
MOD(1600,300) - 100 (modulo)

SELECT Round (45.923,2), Round(45.923), Round (45.923, -1)
FROM dual;

Les dates

Le format de date par défaut est DD-MON-RR
SYSDATE est une fonction qui renvoie :
- La date
- L'heure

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;

MONTHS_BETWEEN
ADD_MONTHS ('11-JAN-94', 6) - '11-JUL-94'
NEXT_DAY
LAST_DAY - Dernier jour du mois
ROUND - Date arrondie
TRUNC - Date tronquée
TRUNC (SYSDATE, 'YEAR')

Fonctions de conversion

Conversion de type de données
-> Conversion implicite de type de données
-> conversion explicite de type de données

NUMBER > T0_CHAR > CHARACTER
CHARACTER > TO_NUMBER > NUMBER
CHARACTER > TO_DATE > DATE
DATE > TO_CHAR > CHARACTER

SELECT last_name,
To_Char(hire_date, 'DD Month YYYY')
AS HIREDATE
FROM employees

Exercice
Affichez votre date de naissance. Ex : 3 Janvier 1987 (03/01/1987)

SELECT TO_DATE ('03011987','DD MM YYYY')
from dual

Depuis combien de jours êtes vous nés ?

SELECT SYSDATE - TO_DATE ('03011987','DD MM YYYY')
from dual

Quel jour êtes-vous né ?

SELECT To_Char (TO_DATE ('03011987','DD MM YYYY'), 'day')
from dual

Exercice
On décide de donner aux employés 1,10 € de prime par jour d'ancienneté. Lister combien on doit donner à chaque employé, en commençant par l'employé le plus ancien.

SELECT first_name, last_name, hire_date, Round(SYSDATE - hire_date, 2) AS anciennete, Round((SYSDATE - hire_date)*1.1, 2) AS prime
FROM employees
ORDER BY hire_date