Hej Habr!
I mer än 3 år har jag undervisat i SQL på olika utbildningscentra, och en av mina observationer är att eleverna behärskar och förstår SQL bättre om de får en uppgift, och inte bara pratar om möjligheterna och teoretiska grunder.
I den här artikeln kommer jag att dela med mig av min lista med uppgifter som jag ger eleverna som läxor och som vi genomför olika slags brainstorms på, vilket leder till en djup och tydlig förståelse av SQL.
SQL (ˈɛsˈkjuˈɛl; eng. structured query language) är ett deklarativt programmeringsspråk som används för att skapa, modifiera och hantera data i en relationsdatabas som hanteras av ett lämpligt databashanteringssystem.
Du kan läsa om SQL från olika .
Den här artikeln är inte avsedd att lära dig SQL från början.
Låt oss gå.
Vi kommer att använda det välkända i Oracle med dess tabeller ():
Jag noterar att vi endast kommer att överväga uppgifter på SELECT. Det finns inga uppgifter på DML och DDL.
uppgifter
Begränsa och sortera data
Anställda bord. Få en lista med information om alla anställda
beslutet
SELECT * FROM employees
Anställda bord. Få en lista över alla anställda som heter 'David'
beslutet
SELECT *
FROM employees
WHERE first_name = 'David';
Anställda bord. Få en lista över alla anställda med job_id lika med 'IT_PROG'
beslutet
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Anställda bord. Få en lista över alla anställda från 50:e avdelningen (department_id) med en lön (lön) större än 4000
beslutet
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Anställda bord. Få en lista över alla anställda från den 20:e och från den 30:e avdelningen (department_id)
beslutet
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Anställda bord. Få en lista över alla anställda vars sista bokstav i deras namn är "a"
beslutet
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Anställda bord. Få en lista över alla anställda från 50:e och från 80:e avdelningen (department_id) som har en bonus (värdet i kolumnen commission_pct är inte tomt)
beslutet
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Anställda bord. Få en lista över alla anställda vars namn innehåller minst 2 bokstäver "n"
beslutet
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Anställda bord. Få en lista över alla anställda vars namn är längre än 4 bokstäver
beslutet
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Anställda bord. Få en lista över alla anställda vars lön är mellan 8000 och 9000 (inklusive)
beslutet
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Anställda bord. Få en lista över alla anställda vars namn innehåller symbolen '%'
beslutet
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Anställda bord. Få en lista över alla chefs-ID:n
beslutet
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Anställda bord. Få en lista över anställda med deras positioner i formatet: Donald(sh_clerk)
beslutet
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Använda enradsfunktioner för att anpassa utdata
Anställda bord. Få en lista över alla anställda vars namn är längre än 10 bokstäver
beslutet
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Anställda bord. Få en lista över alla anställda som har bokstaven 'b' i sitt namn (okänsligt för skiftlägen)
beslutet
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Anställda bord. Få en lista över alla anställda vars namn innehåller minst 2 bokstäver "a"
beslutet
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Anställda bord. Få en lista över alla anställda vars lön är en multipel av 1000
beslutet
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Anställda bord. Få det första 3-siffriga numret av den anställdes telefonnummer om hans nummer är i formatet ХХХ.ХХХ.ХХХХ
beslutet
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Avdelningstabell. Få det första ordet från avdelningsnamnet för de med mer än ett ord i namnet
beslutet
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Anställda bord. Få medarbetarnamn utan första och sista bokstaven i namnet
beslutet
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Anställda bord. Få en lista över alla anställda vars sista bokstav i namnet är lika med 'm' och längden på namnet är större än 5
beslutet
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Dubbelt bord. Få datumet nästa fredag
beslutet
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Anställda bord. Få en lista över alla anställda som har varit på företaget i över 17 år
beslutet
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Anställda bord. Få en lista över alla anställda vars sista siffra i telefonnumret är udda och består av 3 nummer separerade med en punkt
beslutet
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Anställda bord. Få en lista över alla anställda vars job_id-värde efter '_'-tecknet har minst 3 tecken, men detta värde efter '_' är inte lika med 'CLERK'
beslutet
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Anställda bord. Få en lista över alla anställda genom att ersätta alla '.' i värdet PHONE_NUMBER på '-'
beslutet
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Använda konverteringsfunktioner och villkorliga uttryck
Anställda bord. Få en lista över alla anställda som kom till jobbet den första dagen i månaden (alla)
beslutet
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Anställda bord. Få en lista över alla anställda som kom till jobbet 2008
beslutet
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
DUBBLA bord. Visa morgondagens datum i formatet: Imorgon är det andra dagen i januari
beslutet
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Anställda bord. Få en lista över alla anställda och deras startdatum i formatet: 21 juni 2007
beslutet
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Anställda bord. Få en lista över anställda med höjda löner med 20 %. Visa lön med dollartecken
beslutet
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Anställda bord. Få en lista över alla anställda som kom till jobbet i februari 2007.
beslutet
SELECT *
FROM employees
WHERE hire_date BETWEEN TO_DATE ('01.02.2007', 'DD.MM.YYYY')
AND LAST_DAY (TO_DATE ('01.02.2007', 'DD.MM.YYYY'));
SELECT *
FROM employees
WHERE to_char(hire_date,'MM.YYYY') = '02.2007';
DUBBLA bord. Exportera aktuellt datum, + sekund, + minut, + timme, + dag, + månad, + år
beslutet
SELECT SYSDATE now,
SYSDATE + 1 / (24 * 60 * 60) plus_second,
SYSDATE + 1 / (24 * 60) plus_minute,
SYSDATE + 1 / 24 plus_hour,
SYSDATE + 1 plus_day,
ADD_MONTHS (SYSDATE, 1) plus_month,
ADD_MONTHS (SYSDATE, 12) plus_year
FROM DUAL;
Anställda bord. Få en lista över alla anställda med full lön (lön + commission_pct(%)) i formatet: $24,000.00 XNUMX
beslutet
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Anställda bord. Få en lista över alla anställda och information om tillgängligheten av lönebonus (Ja/Nej)
beslutet
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Anställda bord. Få lönenivån för varje anställd: Mindre än 5000 anses låg nivå, större än eller lika med 5000 och mindre än 10000 anses normal nivå, större än eller lika med 10000 anses hög nivå
beslutet
SELECT first_name,
salary,
CASE
WHEN salary < 5000 THEN 'Low'
WHEN salary >= 5000 AND salary < 10000 THEN 'Normal'
ELSE 'High'
END salary_level
FROM employees;
Tabell för länder. För varje land, visa regionen där det är beläget: 1-Europa, 2-Amerika, 3-Asien, 4-Afrika (utan medlemskap)
beslutet
SELECT country_name country,
DECODE (region_id,
1, 'Europe',
2, 'America',
3, 'Asia',
4, 'Africa',
'Unknown')
region
FROM countries;
SELECT country_name
country,
CASE region_id
WHEN 1 THEN 'Europe'
WHEN 2 THEN 'America'
WHEN 3 THEN 'Asia'
WHEN 4 THEN 'Africa'
ELSE 'Unknown'
END
region
FROM countries;
Rapportera aggregerad data med hjälp av gruppfunktionerna
Anställda bord. Få en rapport per department_id med lägsta och maxlön, tidiga och sena ankomstdatum och antal anställda. Sortera efter antal anställda (desc)
beslutet
SELECT department_id,
MIN (salary) min_salary,
MAX (salary) max_salary,
MIN (hire_date) min_hire_date,
MAX (hire_date) max_hire_Date,
COUNT (*) count
FROM employees
GROUP BY department_id
order by count(*) desc;
Anställda bord. Hur många anställda vars namn börjar på samma bokstav? Sortera efter kvantitet. Visa endast de där siffran är större än 1
beslutet
SELECT SUBSTR (first_name, 1, 1) first_char, COUNT (*)
FROM employees
GROUP BY SUBSTR (first_name, 1, 1)
HAVING COUNT (*) > 1
ORDER BY 2 DESC;
Anställda bord. Hur många anställda arbetar på samma avdelning och får samma lön?
beslutet
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Anställda bord. Få en rapport över hur många anställda som anställdes varje dag i veckan. Sortera efter kvantitet
beslutet
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Anställda bord. Få en rapport om hur många anställda som anställdes per år. Sortera efter kvantitet
beslutet
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Anställda bord. Få antalet avdelningar som har anställda
beslutet
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Anställda bord. Få lista över avdelnings-id med mer än 30 anställda
beslutet
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Anställda bord. Få en lista över avdelnings-id och den avrundade medellönen för anställda på varje avdelning.
beslutet
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Tabell för länder. Få en lista över region_id summan av alla bokstäver i alla country_names där fler än 60
beslutet
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Anställda bord. Få en lista över avdelnings-id där anställda av flera (>1) jobb-id arbetar
beslutet
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Anställda bord. Få en lista över manager_id vars antal underordnade är större än 5 och summan av alla löner för hans underordnade är större än 50000
beslutet
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Anställda bord. Få en lista över manager_id vars genomsnittliga lön för alla hans underordnade är mellan 6000 och 9000 som inte får bonusar (commission_pct är tom)
beslutet
SELECT manager_id, AVG (salary) avg_salary
FROM employees
WHERE commission_pct IS NULL
GROUP BY manager_id
HAVING AVG (salary) BETWEEN 6000 AND 9000;
Anställda bord. Få maxlönen från alla anställda job_id som slutar med ordet "CLERK"
beslutet
SELECT MAX (salary) max_salary
FROM employees
WHERE job_id LIKE '%CLERK';
SELECT MAX (salary) max_salary
FROM employees
WHERE SUBSTR (job_id, -5) = 'CLERK';
Anställda bord. Få maxlönen bland alla medellöner för avdelningen
beslutet
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Anställda bord. Få antalet anställda med samma antal bokstäver i deras namn. Visa samtidigt endast de vars namn är längre än 5 och antalet anställda med samma namn är fler än 20. Sortera efter namnlängd
beslutet
SELECT LENGTH (first_name), COUNT (*)
FROM employees
GROUP BY LENGTH (first_name)
HAVING LENGTH (first_name) > 5 AND COUNT (*) > 20
ORDER BY LENGTH (first_name);
SELECT LENGTH (first_name), COUNT (*)
FROM employees
WHERE LENGTH (first_name) > 5
GROUP BY LENGTH (first_name)
HAVING COUNT (*) > 20
ORDER BY LENGTH (first_name);
Visa data från flera tabeller med Joins
Tabellanställda, avdelningar, platser, länder, regioner. Få en lista över regioner och antalet anställda i varje region
beslutet
SELECT region_name, COUNT (*)
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
JOIN countries c ON (l.country_id = c.country_id)
JOIN regions r ON (c.region_id = r.region_id)
GROUP BY region_name;
Tabellanställda, avdelningar, platser, länder, regioner. Få detaljerad information om varje anställd:
Förnamn, Efternamn, Avdelning, Jobb, Gata, Land, Region
beslutet
SELECT First_name,
Last_name,
Department_name,
Job_id,
street_address,
Country_name,
Region_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
JOIN countries c ON (l.country_id = c.country_id)
JOIN regions r ON (c.region_id = r.region_id);
Anställda bord. Visa alla chefer som har fler än 6 anställda
beslutet
SELECT man.first_name, COUNT (*)
FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)
GROUP BY man.first_name
HAVING COUNT (*) > 6;
Anställda bord. Visa alla anställda som inte rapporterar till någon
beslutet
SELECT emp.first_name
FROM employees emp
LEFT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE man.FIRST_NAME IS NULL;
SELECT first_name
FROM employees
WHERE manager_id IS NULL;
Anställda tabell, Job_history. Medarbetartabellen lagrar alla anställda. Tabellen Job_history lagrar anställda som lämnat företaget. Få en rapport om alla anställda och deras status i företaget (Anställd eller lämnat företaget med avgångsdatum)
Exempel:
förnamn | status
jennifer | Lämnade företaget den 31 december 2006
Clara | Arbetar för närvarande
beslutet
SELECT first_name,
NVL2 (
end_date,
TO_CHAR (end_date, 'fm""Left the company at"" DD ""of"" Month, YYYY'),
'Currently Working')
status
FROM employees e LEFT JOIN job_history j ON (e.employee_id = j.employee_id);
Tabellanställda, avdelningar, platser, länder, regioner. Få en lista över anställda som bor i Europa (region_name)
beslutet
SELECT first_name
FROM employees
JOIN departments USING (department_id)
JOIN locations USING (location_id)
JOIN countries USING (country_id)
JOIN regions USING (region_id)
WHERE region_name = 'Europe';
SELECT first_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
JOIN countries c ON (l.country_id = c.country_id)
JOIN regions r ON (c.region_id = r.region_id)
WHERE region_name = 'Europe';
Bordsanställda, avdelningar. Visa alla avdelningar med fler än 30 anställda
beslutet
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Bordsanställda, avdelningar. Visa alla anställda som inte finns på någon avdelning
beslutet
SELECT first_name
FROM employees e
LEFT JOIN departments d ON (e.department_id = d.department_id)
WHERE d.department_name IS NULL;
SELECT first_name
FROM employees
WHERE department_id IS NULL;
Bordsanställda, avdelningar. Visa alla avdelningar utan anställda
beslutet
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Anställda bord. Visa alla anställda som inte har några underordnade
beslutet
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Tabellanställda, jobb, avdelningar. Visa anställda i formatet: First_name, Job_title, Department_name.
Exempel:
förnamn | jobbtitel | Avdelningsnamn
Donald | frakt | Kontorist Frakt
beslutet
SELECT first_name, job_title, department_name
FROM employees e
JOIN jobs j ON (e.job_id = j.job_id)
JOIN departments d ON (d.department_id = e.department_id);
Anställda bord. Få en lista över anställda vars chefer fick jobb 2005, men samtidigt fick dessa arbetare själva jobb före 2005
beslutet
SELECT emp.*
FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE TO_CHAR (man.hire_date, 'YYYY') = '2005'
AND emp.hire_date < TO_DATE ('01012005', 'DDMMYYYY');
Anställda bord. Få en lista över anställda vars chefer fick jobb i januari månad något år och längden på jobbtiteln för dessa anställda är mer än 15 tecken
beslutet
SELECT emp.*
FROM employees emp
JOIN employees man ON (emp.manager_id = man.employee_id)
JOIN jobs j ON (emp.job_id = j.job_id)
WHERE TO_CHAR (man.hire_date, 'MM') = '01' AND LENGTH (j.job_title) > 15;
Använda underfrågor för att lösa frågor
Anställda bord. Få en lista över anställda med det längsta namnet.
beslutet
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Anställda bord. Få en lista över anställda med en lön som är högre än genomsnittslönen för alla anställda.
beslutet
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Tabellanställda, avdelningar, platser. Få den stad där anställda tjänar minst totalt.
beslutet
SELECT city
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
GROUP BY city
HAVING SUM (salary) =
( SELECT MIN (SUM (salary))
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
GROUP BY city);
Anställda bord. Få en lista över anställda vars chef får en lön på mer än 15000 XNUMX.
beslutet
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Bordsanställda, avdelningar. Visa alla avdelningar utan anställda
beslutet
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Anställda bord. Visa alla anställda som inte är chefer
beslutet
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Anställda bord. Visa alla chefer som har fler än 6 anställda
beslutet
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Bordsanställda, avdelningar. Visa medarbetare som arbetar på IT-avdelningen
beslutet
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Tabellanställda, jobb, avdelningar. Visa anställda i formatet: First_name, Job_title, Department_name.
Exempel:
förnamn | jobbtitel | Avdelningsnamn
Donald | frakt | Kontorist Frakt
beslutet
SELECT first_name,
(SELECT job_title
FROM jobs
WHERE job_id = e.job_id)
job_title,
(SELECT department_name
FROM departments
WHERE department_id = e.department_id)
department_name
FROM employees e;
Anställda bord. Få en lista över anställda vars chefer fick jobb 2005, men samtidigt fick dessa arbetare själva jobb före 2005
beslutet
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2005')
AND hire_date < TO_DATE ('01012005', 'DDMMYYYY');
Anställda bord. Få en lista över anställda vars chefer fick jobb i januari månad något år och längden på jobbtiteln för dessa anställda är mer än 15 tecken
beslutet
SELECT *
FROM employees e
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE TO_CHAR (hire_date, 'MM') = '01')
AND (SELECT LENGTH (job_title)
FROM jobs
WHERE job_id = e.job_id) > 15;
Det var allt tills vidare.
Jag hoppas att uppgifterna var intressanta och spännande.
Jag kommer att lägga till den här listan så mycket som möjligt.
Jag kommer också att vara glad för alla kommentarer och förslag.
PS: Om någon kommer på en intressant uppgift på SELECT, skriv i kommentarerna, jag lägger till den i listan.
Tack.
Källa: will.com