Salve Habr!
Plus quam 3 annos docui SQL in variis exercitationibus centris, et una observationum mearum est, quod studentes magistri et SQL intelligant melius, si munus detur, et non solum loqui de possibilitatibus et fundamentis theoreticis.
In hoc articulo, mecum communicabo cum te meo officiorum indice, quod studiosis ut congue sem et in quibus varia genera interretorum gerimus, quae altam et claram intelligentiam SQL ducit.
SQL (ΛΙsΛkjuΛΙl; lat. lingua interrogationis structa) programmatio declarativa est usus linguae ad creandum, modificandum et administrandum data in database relationi relationis rationi convenienti administrandi.
Legere potes de SQL ex diversis .
Articulus hic te SQL de integro docere non vult.
Ergo abeamus.
Utemur nota in Oracle cum tabulis ():
Noto quod tantum labores in SELECT. Nulla officia in DML et DDL nulla sunt.
tasks
Restricting et Sorting Data
Conductos mensae. Accipere album cum notitia de omnibus conductos operarios
arbitrium
SELECT * FROM employees
Conductos mensae. Accipe indicem omnium conductorum nomine David
arbitrium
SELECT *
FROM employees
WHERE first_name = 'David';
Conductos mensae. Indicem omnium conductorum cum job_id = "IT_PROG" posside
arbitrium
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Conductos mensae. Accipere summam omnium conductorum ex 50th department (department_id) cum salario (stipendio) major (IV)
arbitrium
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Conductos mensae. Indicem omnium conductorum 20 et ex 30 department (department_id)
arbitrium
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Conductos mensae. Accipe album omnium conductorum quorum ultima littera in eorum nomine est 'a'
arbitrium
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Conductos mensae. Indicem omnium operariorum ex 50th et ex 80th (department_id) qui bonum habent (valor in columna commission_pct vacua non est)
arbitrium
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Conductos mensae. Accipe album omnium conductorum quorum nomen saltem II litteris 'n'
arbitrium
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Conductos mensae. Accipere album omnium conductorum quorum nomen est longior quam IV litteris
arbitrium
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Conductos mensae. A album omnium conductorum quorum salarium est inter (VIII) et (IX) (inclusive)
arbitrium
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Conductos mensae. Accipe indicem omnium conductorum quorum nomen symbolum continet %'
arbitrium
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Conductos mensae. Indicem omnium procurator IDs
arbitrium
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Conductos mensae. Accipe album elit cum suis locis in forma: Donald (sh_clerk)
arbitrium
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Utens Single-Row functiones ad Mos output
Conductos mensae. Accipere album omnium conductorum quorum nomen est longior quam IV litteris
arbitrium
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Conductos mensae. Indicem omnium conductorum qui litteram 'b' habent in nomine (casu insensibili)
arbitrium
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Conductos mensae. Adepto index omnium conductorum quorum nomen saltem 2 litterae 'a'
arbitrium
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Conductos mensae. Indicem omnium conductorum, quorum salarium multiplex est 1000
arbitrium
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Conductos mensae. Accipere primum III digiti numeri molestie numerus telephonicus si eius numerus in forma .Π₯Π₯Π₯.Π₯Π₯Π₯Π₯
arbitrium
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Tabula Dicasterii. Accipe primum verbum a nomine Dicasterii cum pluribus quam uno verbo in nomine
arbitrium
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Conductos mensae. Get molestie nomina sine prima et ultima littera in nomine
arbitrium
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Conductos mensae. Omnes conductos habe indicem quorum ultima littera in nomine = m et nomen longitudo maior est quam 5
arbitrium
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Tabulae dualis. Get diem ex altera Veneris
arbitrium
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Conductos mensae. Accipe album omnium conductorum qui cum comitatu per XVII annos fuerunt
arbitrium
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Conductos mensae. A album omnium conductorum quorum ultimum digiti numeri telephonici est impar et ex III numeris separata a puncto
arbitrium
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Conductos mensae. Indicem omnium operariorum, quorum officium_id pretii post signum "_" habet, saltem 3 characteres habet, sed hic valor post "_" non est = "CLERK"
arbitrium
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Conductos mensae. Indicem omnium conductorum ponendo omnium ".' in PHONE_NUMBER valore on '-'
arbitrium
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Usus Conversion functiones et conditionales
Conductos mensae. Indicem omnium conductorum qui ad operandum prima die mensis (aliqua)
arbitrium
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Conductos mensae. Indicem omnium conductorum qui ad operandum MMVIII
arbitrium
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
Tabulae dualis. Monstra crastinum diem in forma: Cras est dies secundus mensis Ianuarii
arbitrium
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Conductos mensae. Indicem omnium conductorum et eorum initium date in forma: die 21 mensis Iunii anno 2007
arbitrium
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Conductos mensae. Accipe indicem conductorum auctis salariis per 20%. Stipendium cum pupa in signum ostende
arbitrium
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Conductos mensae. Indicem omnium conductorum qui in Februario MMVII laborandi venerunt.
arbitrium
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';
Tabulae dualis. Data currentis diei, + secundi, + minuti, + hora, + diei, mensis, anni+
arbitrium
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;
Conductos mensae. Indicem omnium conductorum cum pleno salario (salarium + commission_pct(%)) in forma: $24,000.00
arbitrium
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Conductos mensae. Accipe album omnium conductorum et informationem circa disponibilitate salarii bonuses (Yes/No)
arbitrium
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Conductos mensae. Gradus salarii cuiusque operarius: Minor quam (V) habetur inferior gradu, maior quam vel aequalis 5000 et minor quam 5000 habetur Normalis gradus, maior quam vel aequalis ad (M) habetur Altus gradus
arbitrium
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;
Mensam Regionum. Pro unaquaque regione, regionem in qua sita est monstrant: 1-Europe, 2-America, 3-Asia, 4-Africa (sine Join)
arbitrium
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;
Reporting Aggregated Data Using the Group functiones
Conductos mensae. Relationem accipies per department_id cum stipendio minimo et maximo, mane et sero adventus dies et numerus conductorum. Ordina numero conductorum (desc)
arbitrium
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;
Conductos mensae. Quot conductos quorum nomina eadem epistula incipiunt? Digerere per quantitatem. Ostende modo illos ubi maior numerus est quam 1
arbitrium
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;
Conductos mensae. Quot operarii in eadem parte operantur et idem salarium accipient?
arbitrium
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Conductos mensae. Renuntiatio accipiet quot conductos operarios singulis hebdomadis die. Sort by quantitate
arbitrium
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Conductos mensae. Renuntiatio in quot annos conducti conducti sunt. Sort by quantitate
arbitrium
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Conductos mensae. Accipere numerum departments quae habent conductos operarios
arbitrium
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Conductos mensae. Get album of department_id cum plus quam XXX employees
arbitrium
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Conductos mensae. Accipe indicem department_ids et salarium rotundum mediocris in unaquaque parte conductorum.
arbitrium
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Mensam Regionum. Accipe summam region_id omnium litterarum omnium country_names in quibus plus quam 60
arbitrium
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Conductos mensae. Adepto index department_id in quo ministrorum plurium (> I) job_id opus
arbitrium
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Conductos mensae. Accipe summam procuratoris, cuius numerus subordinatorum maior est quam 5 et summa omnium salariorum suorum subditorum maior est quam 50000
arbitrium
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Conductos mensae. Indicem procuratoris_id cuius mediocris salarium omnium subditorum est inter 6000 et 9000 qui bonus non recipiunt (commissio_pct vacua est)
arbitrium
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;
Conductos mensae. Accipe maximum salarium ab omnibus conductis job_id quod desinit cum verbo 'CLERK'
arbitrium
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';
Conductos mensae. Adepto maximum salarium omnibus mediocris salaria pro hac
arbitrium
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Conductos mensae. Numerum elit cum totidem litteris in nomine. Eodem tempore solos illos demonstrare quorum nomen longius est quam 5 et numerus conductorum eodem nomine plusquam XX.
arbitrium
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);
Ostendens Data ex Multiplicibus Tabulis Usura Joins
Tabula Employees, Dicasteria, Locations, regiones, regiones. Accipe indicem regionum et numerum conductorum in unaquaque regione
arbitrium
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;
Tabula Employees, Dicasteria, Locations, regiones, regiones. Ut quisque molestie felis de:
First_name, Last_name, Department, Job, Street, patria, Regio
arbitrium
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);
Conductos mensae. Ostende omnes mancipes qui plus quam VI conductos operarios
arbitrium
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;
Conductos mensae. Ostende omnibus conductos qui non referunt ad aliquem
arbitrium
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;
Conductos mensae, Job_history. Mensa Employee omnes conductos reponit. Tabulae Job_historiae thesaurorum conductorum qui societatem reliquerunt. Accipere famam super omnes conductos et status eorum in comitatu (adhibetur vel relicto societatis cum date profectionis)
exempli gratia:
first_name | status
jennifer | Discessit societas die 31 mensis Decembris anno 2006
Clara | Currently Opus
arbitrium
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);
Tabula Employees, Dicasteria, Locations, regiones, regiones. Indicem conductorum qui in Europa vivunt (region_name)
arbitrium
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';
Table Employees, Dicasteria. Ostende omnibus Dicasteriis cum plus quam XXX employees
arbitrium
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Table Employees, Dicasteria. Ostende omnibus conductos qui non sunt in aliqua department
arbitrium
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;
Table Employees, Dicasteria. Ostende omnia Dicasteria nihil conductos
arbitrium
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Conductos mensae. Ostende omnes conductos qui non habent subditos
arbitrium
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Table Employees, Jobs, Dicasteria. Ostendere operarios in forma: First_name, Job_title, Department_name.
exempli gratia:
first_name | job title | Department_name
Donald | shipping | Clericus Shipping
arbitrium
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);
Conductos mensae. Indicem conductorum quorum actoribus officium in 2005 obtinuit, sed eodem tempore ipsi opifices ante 2005 officium obtinuerunt.
arbitrium
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');
Conductos mensae. Indicem conductorum quorum administratores mense Ianuario anni alicuius officium obtinuit et longitudo job_title horum conductorum plus quam XV characteribus
arbitrium
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;
Per Subqueries solvere Queries
Conductos mensae. Accipe album elit cum longissimo nomine.
arbitrium
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Conductos mensae. Accipe indicem conductorum cum salario maiore quam mediocris salarii omnium operariorum.
arbitrium
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Tabula Employees, Dicasteria, Locations. Accipe urbem in qua conductos minimos mereri in summa.
arbitrium
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);
Conductos mensae. Accipe indicem conductorum quorum procurator salarium plus quam 15000 accipit.
arbitrium
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Table Employees, Dicasteria. Ostende omnia Dicasteria nihil conductos
arbitrium
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Conductos mensae. Ostende omnibus conductos qui non sunt managers
arbitrium
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Conductos mensae. Ostende omnes mancipes qui plus quam VI conductos operarios
arbitrium
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Table Employees, Dicasteria. Ostende operarios qui operantur in IT department
arbitrium
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Table Employees, Jobs, Dicasteria. Ostendere operarios in forma: First_name, Job_title, Department_name.
exempli gratia:
first_name | job title | Department_name
Donald | shipping | Clericus Shipping
arbitrium
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;
Conductos mensae. Indicem conductorum quorum actoribus officium in 2005 obtinuit, sed eodem tempore ipsi opifices ante 2005 officium obtinuerunt.
arbitrium
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');
Conductos mensae. Indicem conductorum quorum administratores mense Ianuario anni alicuius officium obtinuit et longitudo job_title horum conductorum plus quam XV characteribus
arbitrium
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;
Quod ut 'quia iam omnia.
Spero officia erant interesting et excitando.
Addam huic indice quam maxime.
Libenter etiam omnibus commentis et suggestionibus.
PS: Si quis ascenderit cum studio anulo in SELECT, scribe in comment, ad indicem addam.
Π‘ΠΏΠ°ΡΠΈΠ±ΠΎ.
Source: www.habr.com