{"id":36671,"date":"2019-10-31T22:13:01","date_gmt":"2019-10-31T19:13:01","guid":{"rendered":"https:\/\/prohoster.info\/blog\/sql-zanimatelnye-zadachki\/"},"modified":"2019-10-31T22:13:01","modified_gmt":"2019-10-31T19:13:01","slug":"sql-zanimatelnye-zadachki","status":"publish","type":"post","link":"https:\/\/prohoster.info\/it\/blog\/novosti-interneta\/sql-zanimatelnye-zadachki","title":{"rendered":"SQL. Problemi interessanti","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Ciao, Habr!<\/p>\n<p>Da oltre 3 anni insegno SQL in vari centri di formazione, e una delle mie osservazioni \u00e8 che gli studenti apprendono e comprendono meglio SQL se gli vengono posti dei compiti, piuttosto che limitarsi a raccontare le possibilit\u00e0 e le basi teoriche.<\/p>\n<p>In questo articolo condivider\u00f2 con voi la mia lista di compiti che assegno agli studenti come esercizi a casa su cui facciamo diversi brainstorming, il che porta a una comprensione profonda e chiara di SQL.<\/p>\n<p><img decoding=\"async\" alt=\"SQL. Problemi interessanti\" src=\"\/wp-content\/uploads\/2019\/07\/283f472e0c382c0621ffc14e2ac06881.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nSQL (\u02c8\u025bs\u02c8kju\u02c8\u025bl; inglese: structured query language \u2014 \u00ablinguaggio di interrogazione strutturata\u00bb) \u00e8 un linguaggio di programmazione dichiarativo utilizzato per creare, modificare e gestire dati in un database relazionale controllato dal corrispondente sistema di gestione dei database. <noindex><a rel=\"nofollow\" href=\"https:\/\/ru.wikipedia.org\/wiki\/SQL\">Maggiori informazioni\u2026 <\/a><\/noindex><\/p>\n<p>Puoi leggere di SQL in diversi <noindex><a rel=\"nofollow\" href=\"https:\/\/www.google.com\/search?q=SQL\">sorgenti<\/a><\/noindex>.<br \/>\nQuesto articolo non ha l'obiettivo di insegnarti SQL da zero.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><br \/>\nDunque, andiamo.<\/p>\n<p>Useremo la noto <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/oracle\/db-sample-schemas\/tree\/master\/human_resources\">schema HR<\/a><\/noindex> in Oracle con le sue tabelle (<noindex><a rel=\"nofollow\" href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/12.2\/comsc\/HR-sample-schema-table-descriptions.html\">Scopri di pi\u00f9<\/a><\/noindex>):<\/p>\n<p><img decoding=\"async\" alt=\"SQL. Problemi interessanti\" src=\"\/wp-content\/uploads\/2019\/07\/b518c0bedd0d7bbeaedbcc4bd95079cd.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nNota che considereremo solo compiti basati su SELECT. Non ci sono compiti su DML e DDL.<\/p>\n<h2>Attivit\u00e0<\/h2>\n<p>\n<b>Limitare e Ordinare i Dati<\/b><\/p>\n<p>Tabella Employees. Ottieni un elenco con le informazioni su tutti i dipendenti. <br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT * FROM employees\n<\/code><\/pre>\n<p>\u0422\u0430\u0431\u043b\u0438\u0446\u0430 Employees. \u041f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0432\u0441\u0435\u0445 \u0441\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a\u043e\u0432 \u0441 \u0438\u043c\u0435\u043d\u0435\u043c &#8216;David&#8217;<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE first_name = 'David';\n<\/code><\/pre>\n<p>\u0422\u0430\u0431\u043b\u0438\u0446\u0430 Employees. \u041f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0432\u0441\u0435\u0445 \u0441\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a\u043e\u0432 \u0441 job_id \u0440\u0430\u0432\u043d\u044b\u043c &#8216;IT_PROG&#8217;<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE job_id = 'IT_PROG';\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottieni l'elenco di tutti i dipendenti del 50\u00b0 reparto (department_id) con stipendio (salary) maggiore di 4000<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE department_id = 50 AND salary &gt; 4000;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottieni l'elenco di tutti i dipendenti del 20\u00b0 e del 30\u00b0 reparto (department_id)<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE department_id = 20 OR department_id = 30;\n<\/code><\/pre>\n<p>\u0422\u0430\u0431\u043b\u0438\u0446\u0430 Employees. \u041f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0432\u0441\u0435\u0445 \u0441\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a\u043e\u0432 \u0443 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u044f\u044f \u0431\u0443\u043a\u0432\u0430 \u0432 \u0438\u043c\u0435\u043d\u0438 \u0440\u0430\u0432\u043d\u0430 &#8216;a&#8217;<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE first_name LIKE '%a';\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottieni l'elenco di tutti i dipendenti del 50\u00b0 e dell'80\u00b0 reparto (department_id) che hanno un bonus (valore nella colonna commission_pct non vuoto)<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE (department_id = 50 OR department_id = 80)\n       AND commission_pct IS NOT NULL;\n<\/code><\/pre>\n<p>\u0422\u0430\u0431\u043b\u0438\u0446\u0430 Employees. \u041f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0432\u0441\u0435\u0445 \u0441\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a\u043e\u0432 \u0443 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u0432 \u0438\u043c\u0435\u043d\u0438 \u0441\u043e\u0434\u0435\u0440\u0436\u0430\u0442\u0441\u044f \u043c\u0438\u043d\u0438\u043c\u0443\u043c 2 \u0431\u0443\u043a\u0432\u044b &#8216;n&#8217;<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE first_name LIKE '%n%n%';\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottieni l'elenco di tutti i dipendenti i cui nomi superano le 4 lettere<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE first_name LIKE '%_____%';\n<\/code><\/pre>\n<p>Tabella Employees. Ottieni un elenco di tutti i dipendenti il cui stipendio \u00e8 compreso tra 8000 e 9000 (incluso)<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE salary BETWEEN 8000 AND 9000;\n<\/code><\/pre>\n<p>\u0422\u0430\u0431\u043b\u0438\u0446\u0430 Employees. \u041f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0432\u0441\u0435\u0445 \u0441\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a\u043e\u0432 \u0443 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u0432 \u0438\u043c\u0435\u043d\u0438 \u0441\u043e\u0434\u0435\u0440\u0436\u0438\u0442\u0441\u044f \u0441\u0438\u043c\u0432\u043e\u043b &#8216;%&#8217;<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE first_name LIKE '%%%' ESCAPE '';\n<\/code><\/pre>\n<p>Tabella Employees. Ottieni un elenco di tutti gli ID dei manager<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT DISTINCT manager_id\n  FROM employees\n WHERE manager_id IS NOT NULL;\n<\/code><\/pre>\n<p>Tabella Employees. Ottieni un elenco dei dipendenti con le loro posizioni nel formato: Donald(sh_clerk)<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;\n<\/code><\/pre>\n<p>\n<b>Utilizzo di funzioni a riga singola per personalizzare l'output<\/b><\/p>\n<p>Tabella Employees. Ottieni un elenco di tutti i dipendenti il cui nome \u00e8 lungo pi\u00f9 di 10 lettere<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE LENGTH (first_name) &gt; 10;\n<\/code><\/pre>\n<p>\u0422\u0430\u0431\u043b\u0438\u0446\u0430 Employees. \u041f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0432\u0441\u0435\u0445 \u0441\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a\u043e\u0432 \u0443 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u0432 \u0438\u043c\u0435\u043d\u0438 \u0435\u0441\u0442\u044c \u0431\u0443\u043a\u0432\u0430 &#8216;b&#8217; (\u0431\u0435\u0437 \u0443\u0447\u0435\u0442\u0430 \u0440\u0435\u0433\u0438\u0441\u0442\u0440\u0430)<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE INSTR (LOWER (first_name), 'b') &gt; 0;\n<\/code><\/pre>\n<p>\u0422\u0430\u0431\u043b\u0438\u0446\u0430 Employees. \u041f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0432\u0441\u0435\u0445 \u0441\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a\u043e\u0432 \u0443 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u0432 \u0438\u043c\u0435\u043d\u0438 \u0441\u043e\u0434\u0435\u0440\u0436\u0430\u0442\u0441\u044f \u043c\u0438\u043d\u0438\u043c\u0443\u043c 2 \u0431\u0443\u043a\u0432\u044b &#8216;a&#8217;<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE INSTR (LOWER (first_name),'a',1,2) &gt; 0;\n<\/code><\/pre>\n<p>Tabella Employees. Ottieni un elenco di tutti i dipendenti il cui stipendio \u00e8 un multiplo di 1000<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE MOD (salary, 1000) = 0;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottieni le prime tre cifre del numero di telefono del dipendente se il suo numero \u00e8 nel formato XXX.XXX.XXXX<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number\n  FROM employees\n WHERE phone_number LIKE '___.___.____';\n<\/code><\/pre>\n<p>Tabella Dipartimenti. Ottieni la prima parola dal nome del dipartimento per coloro che hanno un nome con pi\u00f9 di una parola<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT department_name,\n       SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)\n           first_word\n  FROM departments\n WHERE INSTR (department_name, ' ') &gt; 0;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottieni i nomi dei dipendenti senza la prima e l'ultima lettera del nome<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name\n  FROM employees;\n<\/code><\/pre>\n<p>\u0422\u0430\u0431\u043b\u0438\u0446\u0430 Employees. \u041f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0432\u0441\u0435\u0445 \u0441\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a\u043e\u0432 \u0443 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u044f\u044f \u0431\u0443\u043a\u0432\u0430 \u0432 \u0438\u043c\u0435\u043d\u0438 \u0440\u0430\u0432\u043d\u0430 &#8216;m&#8217; \u0438 \u0434\u043b\u0438\u043d\u043d\u043e\u0439 \u0438\u043c\u0435\u043d\u0438 \u0431\u043e\u043b\u044c\u0448\u0435\u0439 5\u0442\u0438<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name) &gt; 5;\n<\/code><\/pre>\n<p>Tabella Dual. Ottieni la data del prossimo venerd\u00ec<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottieni un elenco di tutti i dipendenti che lavorano in azienda da pi\u00f9 di 17 anni<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE MONTHS_BETWEEN (SYSDATE, hire_date) \/ 12 &gt; 17;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottieni un elenco di tutti i dipendenti il cui ultimo numero di telefono \u00e8 dispari e composto da 3 cifre separate da un punto<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA *\n  DA impiegati\n DOVE     MOD (SUBSTR (numero_di_telefono, -1), 2) != 0\n       E INSTR (numero_di_telefono,'.',1,3) = 0;\n<\/code><\/pre>\n<p>\u0422\u0430\u0431\u043b\u0438\u0446\u0430 Employees. \u041f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0432\u0441\u0435\u0445 \u0441\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a\u043e\u0432 \u0443 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u0432 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0438 job_id \u043f\u043e\u0441\u043b\u0435 \u0437\u043d\u0430\u043a\u0430 &#8216;_&#8217; \u043a\u0430\u043a \u043c\u0438\u043d\u0438\u043c\u0443\u043c 3 \u0441\u0438\u043c\u0432\u043e\u043b\u0430 \u043d\u043e \u043f\u0440\u0438 \u044d\u0442\u043e\u043c \u044d\u0442\u043e \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u043f\u043e\u0441\u043b\u0435 &#8216;_&#8217; \u043d\u0435 \u0440\u0430\u0432\u043d\u043e &#8216;CLERK&#8217;<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA *\n  DA impiegati\n DOVE     LUNGHEZZA (SUBSTR (job_id, INSTR (job_id, '_') + 1)) &gt; 3\n       E SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';\n<\/code><\/pre>\n<p>\u0422\u0430\u0431\u043b\u0438\u0446\u0430 Employees. \u041f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0432\u0441\u0435\u0445 \u0441\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a\u043e\u0432 \u0437\u0430\u043c\u0435\u043d\u0438\u0432 \u0432 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0438 PHONE_NUMBER \u0432\u0441\u0435 &#8216;.&#8217; \u043d\u0430 &#8216;-&#8216;<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA numero_di_telefono, REPLACE (numero_di_telefono, '.', '-') nuovo_numero_di_telefono\n  DA impiegati;\n<\/code><\/pre>\n<p>\n<b>Utilizzando Funzioni di Conversione ed Espressioni Condizionali<\/b><\/p>\n<p>Tabella Impiegati. Ottieni l'elenco di tutti i dipendenti che sono stati assunti il primo giorno del mese (qualsiasi)<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA *\n  DA impiegati\n DOVE TO_CHAR (data_assunzione, 'DD') = '01';\n<\/code><\/pre>\n<p>Tabella Impiegati. Ottieni l'elenco di tutti i dipendenti assunti nel 2008<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA *\n  DA impiegati\n DOVE TO_CHAR (data_assunzione, 'YYYY') = '2008';\n<\/code><\/pre>\n<p>Tabella DUAL. Mostra la data di domani nel formato: Domani \u00e8 il secondo giorno di gennaio<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA TO_CHAR (SYSDATE, 'fm\"\"Domani \u00e8 \"\"Ddspth \"\"giorno di\"\" Mese') info\n  DA DUAL;\n<\/code><\/pre>\n<p>Tabella Impiegati. Ottieni l'elenco di tutti i dipendenti e la data di assunzione di ciascuno nel formato: 21st di Giugno, 2007<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA nome, TO_CHAR (data_assunzione, 'fmddth \"\"di\"\" Mese, YYYY') data_assunzione\n  DA impiegati;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottieni l'elenco dei lavoratori con un aumento del 20% degli stipendi. Mostra lo stipendio con il simbolo del dollaro<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary\n  FROM employees;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottieni l'elenco di tutti i dipendenti assunti a febbraio 2007.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE hire_date BETWEEN TO_DATE ('01.02.2007', 'DD.MM.YYYY')\n                     AND LAST_DAY (TO_DATE ('01.02.2007', 'DD.MM.YYYY'));\n\nSELECT *\n  FROM employees\n WHERE to_char(hire_date,'MM.YYYY') = '02.2007'; \n<\/code><\/pre>\n<p>Tabella DUAL. Restituisci la data attuale, + secondo, + minuto, + ora, + giorno, + mese, + anno<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT SYSDATE                          now,\n       SYSDATE + 1 \/ (24 * 60 * 60)     plus_second,\n       SYSDATE + 1 \/ (24 * 60)          plus_minute,\n       SYSDATE + 1 \/ 24                 plus_hour,\n       SYSDATE + 1                      plus_day,\n       ADD_MONTHS (SYSDATE, 1)          plus_month,\n       ADD_MONTHS (SYSDATE, 12)         plus_year\n  FROM DUAL;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottieni l'elenco di tutti i dipendenti con stipendio totale (salary + commission_pct(%)) nel formato: $24,000.00<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary\n  FROM employees;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottieni l'elenco di tutti i dipendenti e le informazioni sulla presenza di bonus sullo stipendio (S\u00ec\/No)<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus\n  FROM employees;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottenere il livello salariale di ogni dipendente: Meno di 5000 \u00e8 considerato basso, Maggiore o uguale a 5000 e meno di 10000 \u00e8 considerato normale, Maggiore o uguale a 10000 \u00e8 considerato alto.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT first_name,\n       salary,\n       CASE\n           WHEN salary = 5000 AND salary &lt; 10000 THEN &#039;Normale&#039;\n           ELSE &#039;Alto&#039;\n       END salary_level\n  FROM employees;\n<\/code><\/pre>\n<p>Tabella Paesi. Mostrare per ogni paese la regione in cui si trova: 1-Europa, 2-America, 3-Asia, 4-Africa (senza Join)<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT country_name country,\n       DECODE (region_id,\n               1, 'Europa',\n               2, 'America',\n               3, 'Asia',\n               4, 'Africa',\n               'Sconosciuto')\n           region\n  FROM countries;\n\nSELECT country_name\n           country,\n       CASE region_id\n           WHEN 1 THEN 'Europa'\n           WHEN 2 THEN 'America'\n           WHEN 3 THEN 'Asia'\n           WHEN 4 THEN 'Africa'\n           ELSE 'Sconosciuto'\n       END\n           region\n  FROM countries;\n<\/code><\/pre>\n<p>\n<b>Reporting Dati Aggregati Utilizzando le Funzioni di Gruppo<\/b><\/p>\n<p>Tabella Dipendenti. Ottenere un report per department_id con il salario minimo e massimo, con la data di assunzione pi\u00f9 antica e pi\u00f9 recente e con il numero di dipendenti. Ordinare per numero di dipendenti (in modo decrescente).<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">  SELECT department_id,\n         MIN (salary) min_salary,\n         MAX (salary) max_salary,\n         MIN (hire_date) min_hire_date,\n         MAX (hire_date) max_hire_Date,\n         COUNT (*) count\n    FROM employees\nGROUP BY department_id\norder by count(*) desc;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Quanti dipendenti hanno nomi che iniziano con la stessa lettera? Ordinare per quantit\u00e0. Mostrare solo quelli dove la quantit\u00e0 \u00e8 maggiore di 1<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT SUBSTR(first_name, 1, 1) first_char, COUNT (*)\n    FROM employees\nGROUP BY SUBSTR(first_name, 1, 1)\n  HAVING COUNT (*) &gt; 1\nORDER BY 2 DESC;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Quanti dipendenti lavorano nello stesso dipartimento e guadagnano lo stesso stipendio?<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT department_id, salary, COUNT (*)\n    FROM employees\nGROUP BY department_id, salary\n  HAVING COUNT (*) &gt; 1;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottenere un report di quanti dipendenti sono stati assunti ogni giorno della settimana. Ordinare per quantit\u00e0<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT TO_CHAR(hire_Date, 'Day') day, COUNT (*)\n    FROM employees\nGROUP BY TO_CHAR(hire_Date, 'Day')\nORDER BY 2 DESC;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottenere un report di quanti dipendenti sono stati assunti per anni. Ordinare per quantit\u00e0<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT TO_CHAR(hire_date, 'YYYY') year, COUNT (*)\n    FROM employees\nGROUP BY TO_CHAR(hire_date, 'YYYY');\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottenere il numero di dipartimenti in cui ci sono dipendenti<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT COUNT(COUNT(*)) department_count\n    FROM employees\n   WHERE department_id IS NOT NULL\nGROUP BY department_id;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottenere l'elenco di department_id in cui lavorano pi\u00f9 di 30 dipendenti<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">  SELECT department_id\n    FROM employees\nGROUP BY department_id\n  HAVING COUNT (*) &gt; 30;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottenere un elenco di department_id e lo stipendio medio arrotondato dei dipendenti in ciascun dipartimento. <br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">  SELECT department_id, ROUND(AVG(salary)) avg_salary\n    FROM employees\nGROUP BY department_id;\n<\/code><\/pre>\n<p>Tabella Paesi. Ottenere un elenco di region_id con la somma totale dei caratteri di tutti i country_name che superano i 60.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">  SELECT region_id\n    FROM countries\nGROUP BY region_id\n  HAVING SUM(LENGTH(country_name)) &gt; 60;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottenere un elenco di department_id in cui lavorano dipendenti con diversi (&gt;1) job_id.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">  SELECT department_id\n    FROM employees\nGROUP BY department_id\n  HAVING COUNT(DISTINCT job_id) &gt; 1;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottenere un elenco di manager_id che hanno pi\u00f9 di 5 subordinati e la somma di tutti gli stipendi dei loro subordinati supera 50000.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">  SELECT manager_id\n    FROM employees\nGROUP BY manager_id\n  HAVING COUNT(*) &gt; 5 AND SUM(salary) &gt; 50000;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottenere un elenco di manager_id la cui media salariale di tutti i suoi subordinati \u00e8 compresa tra 6000 e 9000 e che non ricevono bonus (commission_pct vuoto).<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">  SELECT manager_id, AVG(salary) avg_salary\n    FROM employees\n   WHERE commission_pct IS NULL\nGROUP BY manager_id\n  HAVING AVG(salary) BETWEEN 6000 AND 9000;\n<\/code><\/pre>\n<p>\u0422\u0430\u0431\u043b\u0438\u0446\u0430 Employees. \u041f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u043c\u0430\u043a\u0441\u0438\u043c\u0430\u043b\u044c\u043d\u0443\u044e \u0437\u0430\u0440\u043f\u043b\u0430\u0442\u0443 \u0438\u0437 \u0432\u0441\u0435\u0445 \u0441\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a\u043e\u0432 job_id \u043a\u043e\u0442\u043e\u0440\u044b\u0437 \u0437\u0430\u043a\u0430\u043d\u0447\u0438\u0432\u0430\u0435\u0442\u0441\u044f \u043d\u0430 \u0441\u043b\u043e\u0432\u043e &#8216;CLERK&#8217;<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT MAX(salary) max_salary\n  FROM employees\n WHERE job_id LIKE '%CLERK';\n\nSELECT MAX(salary) max_salary\n  FROM employees\n WHERE SUBSTR(job_id, -5) = 'CLERK';\n<\/code><\/pre>\n<p>Tabella Employees. Ottenere il salario massimo tra tutte le medie salariali per departmento<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">  SELECT MAX (AVG (salary))\n    FROM employees\nGROUP BY department_id;\n<\/code><\/pre>\n<p>Tabella Employees. Ottenere il numero di dipendenti con lo stesso numero di lettere nel nome. Mostrare solo coloro che hanno un nome lungo pi\u00f9 di 5 e un numero di dipendenti con quel nome superiore a 20. Ordinare per lunghezza del nome<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">  SELECT LENGTH (first_name), COUNT (*)\n    FROM employees\nGROUP BY LENGTH (first_name)\n  HAVING LENGTH (first_name) &gt; 5 AND COUNT (*) &gt; 20\nORDER BY LENGTH (first_name);\n\n  SELECT LENGTH (first_name), COUNT (*)\n    FROM employees\n   WHERE LENGTH (first_name) &gt; 5\nGROUP BY LENGTH (first_name)\n  HAVING COUNT (*) &gt; 20\nORDER BY LENGTH (first_name);\n<\/code><\/pre>\n<p>\n<b>Visualizzazione dei dati da pi\u00f9 tabelle utilizzando i join<\/b><\/p>\n<p>Tabella Employees, Departaments, Locations, Countries, Regions. Ottenere l'elenco delle regioni e il numero di dipendenti in ogni regione<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">  SELECT region_name, COUNT (*)\n    FROM employees e\n         JOIN departments d ON (e.department_id = d.department_id)\n         JOIN locations l ON (d.location_id = l.location_id)\n         JOIN countries c ON (l.country_id = c.country_id)\n         JOIN regions r ON (c.region_id = r.region_id)\nGROUP BY region_name;\n<\/code><\/pre>\n<p>Tabella Employees, Departaments, Locations, Countries, Regions. Ottenere informazioni dettagliate su ciascun dipendente:<br \/>\nFirst_name, Last_name, Departament, Job, Street, Country, Region<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA Nome,\n       Cognome,\n       Nome_del_Dipartimento,\n       ID_Lavoro,\n       Indirizzo,\n       Nome_del_Paese,\n       Nome_della_Regione\n  DA impiegati e\n       UNISCI dipartimenti d ON (e.id_dipartimento = d.id_dipartimento)\n       UNISCI posizioni l ON (d.id_posizione = l.id_posizione)\n       UNISCI paesi c ON (l.id_paese = c.id_paese)\n       UNISCI regioni r ON (c.id_regione = r.id_regione);\n<\/code><\/pre>\n<p>Tabella Dipendenti. Mostra tutti i manager che hanno sotto di s\u00e9 pi\u00f9 di 6 dipendenti<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">  SELEZIONA man.nome, CONTA (*)\n    DA impiegati emp UNISCI impiegati man ON (emp.id_manager = man.id_impiegato)\nGROUP BY man.nome\n  HAVENDO CONTA (*) &gt; 6;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Mostra tutti i dipendenti che non hanno sotto di s\u00e9 nessuno<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA emp.nome\n  DA impiegati emp\n       UNIONE SINISTRA impiegati man ON (emp.id_manager = man.id_impiegato)\n DOVE man.NOME \u00c8 NULL;\n\nSELEZIONA nome\n  DA impiegati\n DOVE id_manager \u00c8 NULL;\n<\/code><\/pre>\n<p>Tabella Dipendenti, Storico_Lavoro. Nella tabella Dipendenti sono conservati tutti i dipendenti. Nella tabella Storico_Lavoro sono conservati i dipendenti che hanno lasciato l'azienda. Ottenere un rapporto su tutti i dipendenti e sul loro stato in azienda (Lavora o ha lasciato l'azienda con data di uscita)<br \/>\nEsempio:<br \/>\nnome | stato<br \/>\nJennifer | Ha lasciato l'azienda il 31 dicembre 2006<br \/>\nClara | Attualmente Lavorando<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA nome,\n       NVL2 (\n           data_fine,\n           TO_CHAR (data_fine, 'fm\"\"Ha lasciato l'azienda il\"\" DD \"\"di\"\" Mese, YYYY'),\n           'Attualmente Lavorando')\n           stato\n  DA impiegati e UNISCI storico_lavoro j ON (e.id_impiegato = j.id_impiegato);\n<\/code><\/pre>\n<p>Tabella Dipendenti, Dipartimenti, Localit\u00e0, Paesi, Regioni. Ottieni l'elenco dei dipendenti che vivono in Europa (region_name)<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\"> SELECT first_name\n  FROM employees\n       JOIN departments USING (department_id)\n       JOIN locations USING (location_id)\n       JOIN countries USING (country_id)\n       JOIN regions USING (region_id)\n WHERE region_name = 'Europe';\n \n SELECT first_name\n  FROM employees  e\n       JOIN departments d ON (e.department_id = d.department_id)\n       JOIN locations l ON (d.location_id = l.location_id)\n       JOIN countries c ON (l.country_id = c.country_id)\n       JOIN regions r ON (c.region_id = r.region_id)\n WHERE region_name = 'Europe';\n<\/code><\/pre>\n<p>Tabella Dipendenti, Dipartimenti. Mostra tutti i dipartimenti in cui lavorano pi\u00f9 di 30 dipendenti<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT department_name, COUNT (*)\n    FROM employees e JOIN departments d ON (e.department_id = d.department_id)\nGROUP BY department_name\n  HAVING COUNT (*) &gt; 30;\n<\/code><\/pre>\n<p>Tabella Dipendenti, Dipartimenti. Mostra tutti i dipendenti che non appartengono a nessun dipartimento<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT first_name\n  FROM employees  e\n       LEFT JOIN departments d ON (e.department_id = d.department_id)\n WHERE d.department_name IS NULL;\n\nSELECT first_name\n  FROM employees\n WHERE department_id IS NULL;\n<\/code><\/pre>\n<p>Tabella Dipendenti, Dipartimenti. Mostra tutti i dipartimenti in cui non ci sono dipendenti<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT department_name\n  FROM employees  e\n       RIGHT JOIN departments d ON (e.department_id = d.department_id)\n WHERE first_name IS NULL;\n<\/code><\/pre>\n<p>Tabella Dipendenti. Mostra tutti i dipendenti che non hanno nessuno sotto di loro<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA man.first_name\n  DA employees emp\n       JOIN RIGHT employees man SU (emp.manager_id = man.employee_id)\n DOVE emp.FIRST_NAME \u00c8 NULL;\n<\/code><\/pre>\n<p>Tabella Employees, Jobs, Departments. Mostra i dipendenti nel formato: First_name, Job_title, Department_name.<br \/>\nEsempio:<br \/>\nFirst_name | Job_title | Department_name<br \/>\nDonald | Spedizione | Impiegato Spedizione<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA first_name, job_title, department_name\n  DA employees e\n       JOIN jobs j SU (e.job_id = j.job_id)\n       JOIN departments d SU (d.department_id = e.department_id);\n<\/code><\/pre>\n<p>Tabella Employees. Ottenere un elenco dei dipendenti i cui manager sono stati assunti nel 2005, ma questi dipendenti sono stati assunti prima del 2005.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA emp.*\n  DA employees emp JOIN employees man SU (emp.manager_id = man.employee_id)\n DOVE     TO_CHAR (man.hire_date, 'YYYY') = '2005'\n       E emp.hire_date &lt; TO_DATE (&#039;01012005&#039;, &#039;DDMMYYYY&#039;);\n<\/code><\/pre>\n<p>Tabella Employees. Ottenere un elenco dei dipendenti i cui manager sono stati assunti nel mese di gennaio di qualsiasi anno e la lunghezza del job_title di questi dipendenti \u00e8 superiore a 15 caratteri.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA emp.*\n  DA employees emp\n       JOIN employees man SU (emp.manager_id = man.employee_id)\n       JOIN jobs j SU (emp.job_id = j.job_id)\n DOVE TO_CHAR (man.hire_date, 'MM') = '01' E LENGTH (j.job_title) &gt; 15;\n<\/code><\/pre>\n<p>\n<b>Utilizzare le sottoquery per risolvere le query<\/b><\/p>\n<p>Tabella Employees. Ottenere un elenco dei dipendenti con il nome pi\u00f9 lungo.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA *\n  DA employees\n DOVE LENGTH (first_name) =\n       (SELEZIONA MAX (LENGTH (first_name)) DA employees);\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottenere un elenco dei dipendenti con uno stipendio superiore alla media degli stipendi di tutti i dipendenti.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE salary &gt; (SELECT AVG (salary) FROM employees);\n<\/code><\/pre>\n<p>Tabella Dipendenti, Dipartimenti, Luoghi. Ottenere la citt\u00e0 in cui i dipendenti guadagnano meno in totale.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT city\n    FROM employees e\n         JOIN departments d ON (e.department_id = d.department_id)\n         JOIN locations l ON (d.location_id = l.location_id)\nGROUP BY city\n  HAVING SUM (salary) =\n         (  SELECT MIN (SUM (salary))\n              FROM employees e\n                   JOIN departments d ON (e.department_id = d.department_id)\n                   JOIN locations l ON (d.location_id = l.location_id)\n          GROUP BY city);\n<\/code><\/pre>\n<p>Tabella Dipendenti. Ottenere un elenco dei dipendenti il cui manager guadagna pi\u00f9 di 15000.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE manager_id IN (SELECT employee_id\n                        FROM employees\n                       WHERE salary &gt; 15000)\n<\/code><\/pre>\n<p>Tabella Dipendenti, Dipartimenti. Mostra tutti i dipartimenti in cui non ci sono dipendenti<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM departments\n WHERE department_id NOT IN (SELECT department_id\n                               FROM employees\n                              WHERE department_id IS NOT NULL);\n<\/code><\/pre>\n<p>Tabella Dipendenti. Mostrare tutti i dipendenti che non sono manager.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees\n WHERE employee_id NOT IN (SELECT manager_id\n                             FROM employees\n                            WHERE manager_id IS NOT NULL)\n<\/code><\/pre>\n<p>Tabella Dipendenti. Mostra tutti i manager che hanno sotto di s\u00e9 pi\u00f9 di 6 dipendenti<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELECT *\n  FROM employees e\n WHERE (SELECT COUNT (*)\n          FROM employees\n         WHERE manager_id = e.employee_id) &gt; 6;\n<\/code><\/pre>\n<p>Tabella Dipendenti, Dipartimenti. Mostrare i dipendenti che lavorano nel dipartimento IT.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA *\n  DA impiegati\n DOVE department_id = (SELEZIONA department_id\n                          DA reparti\n                         DOVE department_name = 'IT');\n<\/code><\/pre>\n<p>Tabella Employees, Jobs, Departments. Mostra i dipendenti nel formato: First_name, Job_title, Department_name.<br \/>\nEsempio:<br \/>\nFirst_name | Job_title | Department_name<br \/>\nDonald | Spedizione | Impiegato Spedizione<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA first_name,\n       (SELEZIONA job_title\n          DA lavori\n         DOVE job_id = e.job_id)\n           job_title,\n       (SELEZIONA department_name\n          DA reparti\n         DOVE department_id = e.department_id)\n           department_name\n  DA impiegati e;\n<\/code><\/pre>\n<p>Tabella Employees. Ottenere un elenco dei dipendenti i cui manager sono stati assunti nel 2005, ma questi dipendenti sono stati assunti prima del 2005.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA *\n  DA impiegati\n DOVE     manager_id IN (SELEZIONA employee_id\n                            DA impiegati\n                           DOVE TO_CHAR (hire_date, 'YYYY') = '2005')\n       E hire_date &lt; TO_DATE (&#039;01012005&#039;, &#039;DDMMYYYY&#039;);\n<\/code><\/pre>\n<p>Tabella Employees. Ottenere un elenco dei dipendenti i cui manager sono stati assunti nel mese di gennaio di qualsiasi anno e la lunghezza del job_title di questi dipendenti \u00e8 superiore a 15 caratteri.<br \/>\n<b class=\"spoiler_title\">Soluzione<\/b><\/p>\n<pre><code class=\"sql\">SELEZIONA *\n  DA impiegati e\n DOVE     manager_id IN (SELEZIONA employee_id\n                            DA impiegati\n                           DOVE TO_CHAR (hire_date, 'MM') = '01')\n       E (SELEZIONA LENGTH (job_title)\n              DA lavori\n             DOVE job_id = e.job_id) &gt; 15;\n<\/code><\/pre>\n<p>\n<b>Questo \u00e8 tutto per ora.<\/b><\/p>\n<p>Spero che le sfide siano state interessanti e coinvolgenti. <br \/>\nCercher\u00f2 di aggiungere questo elenco di compiti quando possibile.<br \/>\nSarei anche felice di ricevere qualsiasi commento e suggerimento.<\/p>\n<p>P.S.: Se a qualcuno viene in mente un compito interessante su SELECT, scrivetelo nei commenti, lo aggiunger\u00f2 all'elenco.<\/p>\n<p>Grazie.<br \/>\n<br \/>Fonte: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/461567\/\">habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u0417\u0434\u0440\u0430\u0432\u0441\u0442\u0432\u0443\u0439, \u0425\u0430\u0431\u0440! \u0412\u043e\u0442 \u0443\u0436\u0435 \u0431\u043e\u043b\u0435\u0435 3-\u0445 \u043b\u0435\u0442 \u044f \u043f\u0440\u0435\u043f\u043e\u0434\u0430\u044e SQL \u0432 \u0440\u0430\u0437\u043d\u044b\u0445 \u0442\u0440\u0435\u043d\u0438\u043d\u0433 \u0446\u0435\u043d\u0442\u0440\u0430\u0445, \u0438 \u043e\u0434\u043d\u0438\u043c \u0438\u0437 \u043c\u043e\u0438\u0445 \u043d\u0430\u0431\u043b\u044e\u0434\u0435\u043d\u0438\u0439 \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0442\u043e, \u0447\u0442\u043e \u0441\u0442\u0443\u0434\u0435\u043d\u0442\u044b \u043e\u0441\u0432\u0430\u0438\u0432\u0430\u044e\u0442 \u0438 \u043f\u043e\u043d\u0438\u043c\u0430\u044e\u0442 SQL \u043b\u0443\u0447\u0448\u0435, \u0435\u0441\u043b\u0438 \u0441\u0442\u0430\u0432\u0438\u0442\u044c \u043f\u0435\u0440\u0435\u0434 \u043d\u0438\u043c\u0438 \u0437\u0430\u0434\u0430\u0447\u0443, \u0430 \u043d\u0435 \u043f\u0440\u043e\u0441\u0442\u043e \u0440\u0430\u0441\u0441\u043a\u0430\u0437\u044b\u0432\u0430\u0442\u044c \u043e \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044f\u0445 \u0438 \u0442\u0435\u043e\u0440\u0435\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0441\u043d\u043e\u0432\u0430\u0445. \u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u044f \u043f\u043e\u0434\u0435\u043b\u044e\u0441\u044c \u0441 \u0432\u0430\u043c\u0438 \u0441\u0432\u043e\u0438\u043c \u0441\u043f\u0438\u0441\u043a\u043e\u043c \u0437\u0430\u0434\u0430\u0447, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u044f \u0434\u0430\u044e [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":27465,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[702],"tags":[],"class_list":["post-36671","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-novosti-interneta"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 4.9.10 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u0417\u0434\u0440\u0430\u0432\u0441\u0442\u0432\u0443\u0439, \u0425\u0430\u0431\u0440! \u0412\u043e\u0442 \u0443\u0436\u0435 \u0431\u043e\u043b\u0435\u0435 3-\u0445 \u043b\u0435\u0442 \u044f \u043f\u0440\u0435\u043f\u043e\u0434\u0430\u044e SQL \u0432 \u0440\u0430\u0437\u043d\u044b\u0445 \u0442\u0440\u0435\u043d\u0438\u043d\u0433 \u0446\u0435\u043d\u0442\u0440\u0430\u0445, \u0438 \u043e\u0434\u043d\u0438\u043c \u0438\u0437 \u043c\u043e\u0438\u0445 \u043d\u0430\u0431\u043b\u044e\u0434\u0435\u043d\u0438\u0439 \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0442\u043e, \u0447\u0442\u043e \u0441\u0442\u0443\u0434\u0435\u043d\u0442\u044b \u043e\u0441\u0432\u0430\u0438\u0432\u0430\u044e\u0442 \u0438 \u043f\u043e\u043d\u0438\u043c\u0430\u044e\u0442 SQL \u043b\u0443\u0447\u0448\u0435, \u0435\u0441\u043b\u0438 \u0441\u0442\u0430\u0432\u0438\u0442\u044c \u043f\u0435\u0440\u0435\u0434 \u043d\u0438\u043c\u0438 \u0437\u0430\u0434\u0430\u0447\u0443, \u0430 \u043d\u0435 \u043f\u0440\u043e\u0441\u0442\u043e \u0440\u0430\u0441\u0441\u043a\u0430\u0437\u044b\u0432\u0430\u0442\u044c \u043e \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044f\u0445 \u0438 \u0442\u0435\u043e\u0440\u0435\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0441\u043d\u043e\u0432\u0430\u0445. \u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u044f \u043f\u043e\u0434\u0435\u043b\u044e\u0441\u044c \u0441 \u0432\u0430\u043c\u0438 \u0441\u0432\u043e\u0438\u043c \u0441\u043f\u0438\u0441\u043a\u043e\u043c \u0437\u0430\u0434\u0430\u0447, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u044f \u0434\u0430\u044e\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/it\/blog\/novosti-interneta\/sql-zanimatelnye-zadachki\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 4.9.10\" \/>\n\t\t<meta property=\"og:locale\" content=\"it_IT\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47SQL. \u0417\u0430\u043d\u0438\u043c\u0430\u0442\u0435\u043b\u044c\u043d\u044b\u0435 \u0437\u0430\u0434\u0430\u0447\u043a\u0438 | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u0417\u0434\u0440\u0430\u0432\u0441\u0442\u0432\u0443\u0439, \u0425\u0430\u0431\u0440! \u0412\u043e\u0442 \u0443\u0436\u0435 \u0431\u043e\u043b\u0435\u0435 3-\u0445 \u043b\u0435\u0442 \u044f \u043f\u0440\u0435\u043f\u043e\u0434\u0430\u044e SQL \u0432 \u0440\u0430\u0437\u043d\u044b\u0445 \u0442\u0440\u0435\u043d\u0438\u043d\u0433 \u0446\u0435\u043d\u0442\u0440\u0430\u0445, \u0438 \u043e\u0434\u043d\u0438\u043c \u0438\u0437 \u043c\u043e\u0438\u0445 \u043d\u0430\u0431\u043b\u044e\u0434\u0435\u043d\u0438\u0439 \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0442\u043e, \u0447\u0442\u043e \u0441\u0442\u0443\u0434\u0435\u043d\u0442\u044b \u043e\u0441\u0432\u0430\u0438\u0432\u0430\u044e\u0442 \u0438 \u043f\u043e\u043d\u0438\u043c\u0430\u044e\u0442 SQL \u043b\u0443\u0447\u0448\u0435, \u0435\u0441\u043b\u0438 \u0441\u0442\u0430\u0432\u0438\u0442\u044c \u043f\u0435\u0440\u0435\u0434 \u043d\u0438\u043c\u0438 \u0437\u0430\u0434\u0430\u0447\u0443, \u0430 \u043d\u0435 \u043f\u0440\u043e\u0441\u0442\u043e \u0440\u0430\u0441\u0441\u043a\u0430\u0437\u044b\u0432\u0430\u0442\u044c \u043e \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044f\u0445 \u0438 \u0442\u0435\u043e\u0440\u0435\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0441\u043d\u043e\u0432\u0430\u0445. \u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u044f \u043f\u043e\u0434\u0435\u043b\u044e\u0441\u044c \u0441 \u0432\u0430\u043c\u0438 \u0441\u0432\u043e\u0438\u043c \u0441\u043f\u0438\u0441\u043a\u043e\u043c \u0437\u0430\u0434\u0430\u0447, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u044f \u0434\u0430\u044e\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/it\/blog\/novosti-interneta\/sql-zanimatelnye-zadachki\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2019-10-31T19:13:01+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2019-10-31T19:13:01+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47SQL. Compiti interessanti | ProHoster","description":"Ciao, Habr! Da oltre 3 anni insegno SQL in diversi centri di formazione, e una delle mie osservazioni \u00e8 che gli studenti comprendono e padroneggiano meglio SQL se viene presentata loro una sfida, piuttosto che limitarsi a parlare delle possibilit\u00e0 e delle basi teoriche. In questo articolo condivider\u00f2 con voi la mia lista di compiti che propongo.","canonical_url":"https:\/\/prohoster.info\/it\/blog\/novosti-interneta\/sql-zanimatelnye-zadachki","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"it_IT","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47SQL. \u0417\u0430\u043d\u0438\u043c\u0430\u0442\u0435\u043b\u044c\u043d\u044b\u0435 \u0437\u0430\u0434\u0430\u0447\u043a\u0438 | ProHoster","og:description":"\u0417\u0434\u0440\u0430\u0432\u0441\u0442\u0432\u0443\u0439, \u0425\u0430\u0431\u0440! \u0412\u043e\u0442 \u0443\u0436\u0435 \u0431\u043e\u043b\u0435\u0435 3-\u0445 \u043b\u0435\u0442 \u044f \u043f\u0440\u0435\u043f\u043e\u0434\u0430\u044e SQL \u0432 \u0440\u0430\u0437\u043d\u044b\u0445 \u0442\u0440\u0435\u043d\u0438\u043d\u0433 \u0446\u0435\u043d\u0442\u0440\u0430\u0445, \u0438 \u043e\u0434\u043d\u0438\u043c \u0438\u0437 \u043c\u043e\u0438\u0445 \u043d\u0430\u0431\u043b\u044e\u0434\u0435\u043d\u0438\u0439 \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0442\u043e, \u0447\u0442\u043e \u0441\u0442\u0443\u0434\u0435\u043d\u0442\u044b \u043e\u0441\u0432\u0430\u0438\u0432\u0430\u044e\u0442 \u0438 \u043f\u043e\u043d\u0438\u043c\u0430\u044e\u0442 SQL \u043b\u0443\u0447\u0448\u0435, \u0435\u0441\u043b\u0438 \u0441\u0442\u0430\u0432\u0438\u0442\u044c \u043f\u0435\u0440\u0435\u0434 \u043d\u0438\u043c\u0438 \u0437\u0430\u0434\u0430\u0447\u0443, \u0430 \u043d\u0435 \u043f\u0440\u043e\u0441\u0442\u043e \u0440\u0430\u0441\u0441\u043a\u0430\u0437\u044b\u0432\u0430\u0442\u044c \u043e \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044f\u0445 \u0438 \u0442\u0435\u043e\u0440\u0435\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0441\u043d\u043e\u0432\u0430\u0445. \u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u044f \u043f\u043e\u0434\u0435\u043b\u044e\u0441\u044c \u0441 \u0432\u0430\u043c\u0438 \u0441\u0432\u043e\u0438\u043c \u0441\u043f\u0438\u0441\u043a\u043e\u043c \u0437\u0430\u0434\u0430\u0447, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u044f \u0434\u0430\u044e","og:url":"https:\/\/prohoster.info\/it\/blog\/novosti-interneta\/sql-zanimatelnye-zadachki","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T19:13:01+00:00","article:modified_time":"2019-10-31T19:13:01+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"36671","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-22 04:21:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 01:40:23","updated":"2026-01-22 04:21:19"},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts\/36671","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/comments?post=36671"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts\/36671\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/media\/27465"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/media?parent=36671"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/categories?post=36671"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/tags?post=36671"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}