====== Requêtes SQL ======

====== SELECT ======

<code sql>
SELECT champ1, champ2
    FROM table
    WHERE condition
    ORDER BY champ1 ASC, champ2 DESC;
</code>

Doc : [[http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_10002.htm#i2129937|Oracle]]


===== Gérer valeur NULL =====

Utiliser la fonction standard COALESCE : 

<code sql>
-- Téléphone fixe, ou mobile sinon
SELECT COALESCE (cli.telephonefixe, cli.telephonemobile) AS telephone
FROM clients
</code>

Doc : [[https://en.wikibooks.org/wiki/Structured_Query_Language/SELECT:_IS_NULL_Predicate#Coalesce()_and_Similar_Functions|Wikibooks]], [[https://docs.microsoft.com/fr-fr/sql/t-sql/language-elements/coalesce-transact-sql|MS SQL]]

Chez Oracle il existe aussi une fonction non standard Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm|NVL]]

===== Switch Case =====

<code sql>
SELECT
    CASE 
        WHEN prix<10 THEN 'Moins de 10 €'
        WHEN prix<20 THEN 'Entre 10 € et 20 €'
        WHEN prix<40 THEN 'Entre 20 € et 40 €'
        ELSE 'Plus de 40 €'
    END
FROM produits;
</code>

Doc : [[http://docs.oracle.com/cd/B13789_01/server.101/b10759/expressions004.htm|Oracle]]

===== Portion de chaîne =====

**Oracle :**
 
<code sql>
SELECT
	-- Ne garder que les 3e et 4e caractères du code
	SUBSTR(codecoloris, 3, 2) AS ancien_coloris,
FROM produits_coloris
</code>

Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm|Oracle]]

**MS SQL Server :**

<code sql>
SELECT
	-- Séparer heures et minutes
	SUBSTRING (a.actvissalheurearr, 1, 2) + ':' + SUBSTRING (a.actvissalheurearr, 3, 2) AS "Heure"
FROM ACTEVISITESALARIE a
</code>

Doc : https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver15

Pour le faire selon une expression régulière : [[http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions116.htm|REGEXP_SUBSTR chez Oracle]], [[http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP|SUBSTRING w/ POSIX Regular Expressions chez PostgreSQL]]

Portion avant et après un caractères séparateur :
<code sql>
SELECT
	SUBSTR(pam.codeelementligne, 0                                       , INSTR(pam.codeelementligne, '|', 1, 1)-1), 
	SUBSTR(pam.codeelementligne, INSTR(pam.codeelementligne, '|', 1, 1)+1, LENGTH(pam.codeelementligne))
FROM xanaka.lignes_perimetre_action_mkt pam
</code>
===== Transtypages =====

==== Nombre -> texte ====

Fonctions CAST ou TO_CHAR

<code sql>
SELECT
	CAST(nombre AS VARCHAR2(255)) 	AS idtexte,
FROM latable
</code>
<code sql>
SELECT
	TO_CHAR(nombre, 'FM990.09') 	AS idtexte,
FROM latable
-- 139,95 -> 139.95
--   7    ->   7.0
</code>

Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions016.htm#i1269136|Oracle CAST]] ou [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions181.htm|TO_CHAR]]

==== Date -> texte ====

=== Oracle ===

<code sql>
SELECT
	TO_CHAR(jour, 'dd/mm/yyyy HH24:MI')
FROM latable
-- Donne par exemple : '04/07/2012 13:37'
</code>

Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm#i1009324|Oracle]] et [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#sthref419|formats]]

=== MySQL ===

<code sql>
select DATE_FORMAT(d.date_der_exe,'%Y%m%d%H%i%S')
from latable
-- Donne par exemple : '20160719010041
</code>

Doc : [[https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format|MySQL]]


=== MS SQL Server ===

<code sql>
select CONVERT(varchar, LOGDate, 103)  
from latable
-- Donne par exemple : '28/03/2020'
</code>

Doc : [[https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#date-and-time-styles|CAST and CONVERT (Transact-SQL)]]
==== Date -> numéro de semaine ====

<code sql>
SELECT jour, TO_CHAR(jour, 'iw')
FROM latable
-- Donne par exemple : 2015-04-28 => 18
</code>


==== DateTime -> Date ====

=== Microsoft SQL Server ===

<code sql>
SELECT cast([DateTime] As Date) AS jour
FROM table
</code>

Source : https://stackoverflow.com/a/923322


==== Texte -> date ====

=== Oracle ===

Fonction TO_DATE

<code sql>
SELECT * FROM latable
WHERE datedebut = TO_DATE('2012-02-15', 'yyyy-mm-dd')
</code>

Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm#i1003589|Oracle]]

=== Microsoft SQL Server ===

CAST('17/07/2019 00:00:00' AS date)
<code sql>
SELECT * FROM latable
WHERE TOTAL_DATE = CAST('17/07/2019 00:00:00' AS date)
</code>

Ou en précisant le format :
<code sql>
SELECT * FROM latable
WHERE date = CONVERT(date, '20210825', 112)
</code>
Doc : https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#g-using-cast-and-convert-with-datetime-data


=== MySQL ===

<code sql>
SELECT STR_TO_DATE("2022/03/25", "%Y/%m/%d") 
</code>

Doc : [[https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date| STR_TO_DATE(str,format)]] et [[https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format|formats]]


===== IN =====

<code sql>
SELECT *
FROM clients
WHERE email IN ('test1@example.com', 'test2@example.org', 'test3@example.net')
</code>

Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions013.htm#i1050801|Oracle]]

==== Avec dédoublonnage ====

S'il y a plusieurs comptes pour un email, n'extraire que le compte le plus récent :
<code sql>
SELECT *
FROM  clients
WHERE codeclient IN (
    SELECT MAX(codeclient)
    FROM clients
    WHERE email IN ('test1@example.com', 'test2@example.org', 'test3@example.net')
    GROUP BY email
);
</code>


===== Agrégation =====

<code sql>
SELECT department_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY department_id;
</code>

Doc : [[http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_10002.htm#i2066419|Oracle]]

===== Plus petite valeur entre 2 colonnes =====

<code sql>
SELECT LEAST(prd.datemodification, art.datemodification)
FROM articles art
LEFT JOIN produits prd
	ON art.codeproduit = prd.codeproduit
</code>

Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions075.htm|Oracle]]


===== Plus grande valeur entre 2 colonnes =====

<code sql>
SELECT GREATEST(stk.stock, 0) AS stock,
FROM stock stk
</code>

Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions060.htm|Oracle]]

Pour la plus petite valeur, utiliser la fonction ''LEAST''

<code sql>
SELECT LEAST(datemodification, datecreation)
FROM matable
</code>

Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions075.htm|Oracle 10g]]

===== Plus grande valeur entre plusieurs lignes =====

Par exemple, ne garder d'une table que la ligne la plus récente pour chacun des utilisateurs...

<code sql>
WITH nom_alias AS (
	SELECT *
		, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY datecreation DESC) AS position
	FROM table_a_filtrer
)
SELECT
	 ...
FROM
	nom_alias f
INNER JOIN
	 ...
WHERE
	f.position = 1 -- uniquement dernier document
	AND ...
ORDER BY ...
</code>


====== INSERT ======

<code sql>
INSERT INTO employees (employee_id, last_name, email,  hire_date, job_id, salary, commission_pct) 
   VALUES (207, 'Gregory', 'pgregory@oracle.com', sysdate, 'PU_CLERK', 1.2E3, NULL);
</code>

Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm|Oracle]]

===== Depuis une sous-requête =====

Si la table existe déjà, sinon utiliser [[#creer_table_a_partir_d_une_requete]]

<code sql>
INSERT INTO bonuses
   SELECT employee_id, salary*1.1 
   FROM employees
   WHERE commission_pct > 0.25; 
</code>

Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#i2126076|Oracle]]


===== Plusieurs enregistrements en une requête =====

<code sql>
INSERT ALL
   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;
</code>


====== UPDATE ======

<code sql>
UPDATE unetable
	SET champ = valeur
	WHERE champ = valeur
</code>

Plusieurs champs, depuis une sous-requête :
<code sql>
UPDATE tableA a 
    SET (champA1, champA2) = 
        (SELECT champB1, champB2 
          FROM tableB b 
          WHERE a.bid = b.id) 
    WHERE bid = 123; 
</code>

Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10007.htm#i2189756|Oracle]]


===== Remplacer caractères =====


Remplacer les points-virgules par des points dans une chaîne de caractères :
<code sql>
UPDATE unetable
SET champ = REPLACE(champ , ';', '.')
WHERE champ LIKE '%;%'
</code>

Doc : [[http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions153.htm#SQLRF00697|Oracle]]
====== DELETE ======

<code sql>
DELETE FROM bonus
WHERE sales_amt < quota;
</code>

Doc : [[http://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems014.htm|Oracle]]

===== Avec jointures =====

Avec Oracle, impossible d'utiliser des jointures comme habituellement. Voici la syntaxe à utilise :

<code sql>
DELETE FROM refint.ecom_cmd_lignes_int l 
WHERE EXISTS (
  SELECT 1 FROM refint.ecom_cmd_entete_int t 
  WHERE t.num_commande = l.num_commande
	  AND t.idlot = l.idlot
	  AND t.etat_integration IS NULL
)

-- Pour supprimer les lignes suivantes :
SELECT * FROM refint.ecom_cmd_lignes_int l
INNER JOIN refint.ecom_cmd_entete_int t
ON t.num_commande = l.num_commande
	AND t.idlot = l.idlot
WHERE t.etat_integration IS NULL
</code>


====== CREATE TABLE ======

===== Clé primaire composée =====

<code sql>
CREATE TABLE tmp_photos_articles (
    codeinternearticle NUMBER(8),
    numvue NUMBER(1),
    cheminphoto varchar2(128),
    CONSTRAINT tmp_photos_articles_pk PRIMARY KEY (codeinternearticle, numvue)
);
</code>

Doc : [[http://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_7002.htm|Oracle]]

===== Créer table à partir d'une requête =====

Testé avec postgresql 9 et Oracle 10g :

<code sql>
CREATE TABLE nom_table AS
    SELECT
        champ1 AS nom_colonne1,
        champ2 AS nom_colonne2,
        champ3 AS nom_colonne3
    FROM 
        tables_source
    WHERE
        ...
</code>

Doc : [[http://www.postgresql.org/docs/9.0/static/sql-createtableas.html|PostgreSQL]]

Testé avec M$ SQL Server :

<code sql>
SELECT *
INTO destination
FROM source;
</code>

Doc : https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql
====== ALTER TABLE ======

<code sql>
ALTER TABLE nom_table
   ADD (nouveau1  NUMBER(6,0) DEFAULT 10267 NOT NULL,
        nouveau2  VARCHAR2(3)
   ); 
</code>

====== MSSQL : lier un utilisateur à une connexion ======

<code sql>
USE NomDelaBase
GO

ALTER USER Utilisateur WITH LOGIN = Connexion
GO
</code>

====== MSSQL : copier des tables d'un schéma à un autre ======

> Use the Generate Scripts Wizard in SSMS for generating the script for constraints and indexes.
>
>Follow these steps:
>
>1. Open SSMS
>2. Expand databases and select the database->tasks->generate scripts to launch the GSW
>3. click next and set "Script Check Constraints" to true, "Script Indexes " to true, "ScriptDependencies" to true and set whatever is needed.
>4. Select the tables for which the script is needed
>5. Click next ->next->finish
>
>This generates the script for table with indexes, constraints etc.
>
>More information: http://msdn.microsoft.com/en-us/library/ms178078.aspx

Source : https://social.msdn.microsoft.com/Forums/en-US/17a16dc4-dcf1-4de8-baba-6949c55a4c5d/copy-constraints-and-indexes-from-one-database-to-another?forum=transactsql

====== MSSQL : trouver les verrous sur une table ======

<code sql>
SELECT 
	object_name(object_id) AS objet_bloque, 
    lock.resource_type,
    db.name AS db_name,
    lock.resource_associated_entity_id,
    lock.request_mode,
    lock.request_session_id,
    task.blocking_session_id,
    task.*
FROM sys.dm_tran_locks lock
INNER JOIN sys.partitions p ON p.hobt_id= lock.resource_associated_entity_id
INNER JOIN sys.databases db ON db.database_id = lock.resource_database_id
LEFT JOIN sys.dm_os_waiting_tasks as task
    ON lock.lock_owner_address = task.resource_address
LEFT JOIN sys.dm_os_tasks AS STasks
    ON STasks.task_address = task.waiting_task_address
WHERE resource_database_id = 20 AND object_name(object_id) = 'NOM_DE_LA_TABLE'
</code>


====== Documentations ======

  * PostgreSQL 9.1 : http://docs.postgresql.fr/9.1/
  * Oracle 
    * 11g : https://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm
    * 10g : https://docs.oracle.com/cd/B19306_01/server.102/b14200/toc.htm
  * Microsoft SQL Server :
    * 2017 : https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-2017