Luty « 2014 « Różności …

Różności …

21 lutego 2014

Set operators.

Zaszufladkowany do: Bez kategorii — Jacek @ 07:08

System edukacji w Polsce.

Zaszufladkowany do: Z sieci — Tagi: — Jacek @ 07:02

20 lutego 2014

subquery

Zaszufladkowany do: Bez kategorii — Jacek @ 06:09

Multiple row operators

SELECT last_name, salary, department_id
FROM
employees
WHERE salary IN (SELECT
MIN(salary)
FROM
employees
GROUP BY department_id);



17 lutego 2014

joins

Zaszufladkowany do: Bazy danych,Oracle,Programowanie,SQL — Tagi: — Jacek @ 06:30

NATURAL JOIN

INNER JOINS

SELECT employee_id, last_name,
department_name
FROM
hr.employees JOIN hr.departments
USING (department_id) ;

SELECT l.city, d.department_name
FROM
locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;

SELF JOIN
Sometimes you need to join a table to itself. To find the name of each employee’s manager,
you need to join the EMPLOYEES table to itself, or perform a self-join.

SELECT worker.last_name emp, manager.last_name mgr
FROM
employees worker JOIN employees manager
ON
(worker.manager_id = manager.employee_id);

NONEQUIJOINS


To return the unmatched rows, you can use
an OUTER join. An OUTER join returns all rows that satisfy the join condition and also returns
some or all of those rows from one table for which no rows from the other table satisfy the join
condition.
There are three types of OUTER joins:
•LEFT OUTER
•RIGHT OUTER
•FULL OUTER

SELECT e.last_name, e.department_id, d.department_name
FROM
employees e LEFT OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;

This query retrieves all the rows in the EMPLOYEES table, which is the left table, even if there
is no match in the DEPARTMENTS table.

SELECT e.last_name, d.department_id, d.department_name
FROM
employees e RIGHT OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;

This query retrieves all the rows in the DEPARTMENTS table, which is the table at the right,
even if there is no match in the EMPLOYEES table.

SELECT e.last_name, d.department_id, d.department_name
FROM
employees e FULL OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;

This query retrieves all rows in the EMPLOYEES table, even if there is no match in the
DEPARTMENTS table. It also retrieves all rows in the DEPARTMENTS table, even if there is no
match in the EMPLOYEES table.

——— CARTESIAN PRODUCT ————

SELECT last_name, department_name
FROM
employees CROSS JOIN departments ;

13 lutego 2014

group_by

Zaszufladkowany do: Bazy danych,Programowanie,SQL — Tagi: — Jacek @ 06:57

group by

AVG
COUNT
MAX
MIN
STDDEV
SUM
VARIANCE

12 lutego 2014

NVL NULL, to_date, to_char

Zaszufladkowany do: Oracle,SQL — Tagi: , — Jacek @ 06:47

to_char

10 lutego 2014

Zeszłego lata na Kaukazie …

Zaszufladkowany do: Podróże — Tagi: — Jacek @ 06:03

Strona startowa: www.jaceksen.pl