dokuwiki

kurs:rank

admin (admin@undisclosed.example.com) — 2024-03-05T15:29:57+0200

SELECT * FROM ( SELECT employee_id, last_name, salary, RANK() OVER (ORDER BY salary DESC) EMPRANK FROM employees) where emprank > 10 and emprank < 13 -- WHERE emprank = 2; with cte_emp_dep as ( SELECT emp.first_name, emp.last_name, emp.salary, dep.department_name FROM employees emp left join departments dep on emp.department_id = dep.department_id where salary < 9000 ), cte_sal_bin as ( select cte_emp_dep.*, trunc(salary, -3) sal_bin from cte_emp_dep ), ct…

kurs:correlated_subquery

admin (admin@undisclosed.example.com) — 2022-03-15T10:39:03+0200

with cte_multiple_last_names as ( select last_name, count(*) c from hr.employees group by last_name having count(*) > 1 order by c desc ) select employee_id, first_name, last_name from hr.employees where last_name in (select last_name from cte_multiple_last_names) select emp.first_name, emp.last_name from hr.employees emp where (select count(last_name) from hr.employees emp_sub where emp_sub.last_name = emp.last_name) > 1


select first_name, last_name,…



kurs:pivot - created
admin (admin@undisclosed.example.com) — 2022-03-14T14:24:08+0200
with cte_quartal as (
select 
    employee_id,
    employee_id emp_id,
    last_name,
    to_char(hire_date, 'Q') quartal
from hr.employees
)
select * from cte_quartal
  pivot
  (
   count(employee_id) for quartal in (
     '1' q1,
     '2' q2,
     '3' q3,
     '4' q4)
  )


kurs:oracle-advanced-sql
admin (admin@undisclosed.example.com) — 2022-03-14T14:23:09+0200
RDBMS

DB Sprachen

Fragen Dezember 2018

3rd party software

bind variablen in sqlplus

einfache selects

CTE Common Table Expression - With Clause

Relationen

Data Dictionary Views

bind und substitution variablen

sessions + transactionen

indices

case

datum

oracle outer joins

joins

joins - subsetvergleich

beispiele

any all

rownum limit

overlaps

sets Gruppenoperatoren

metadata

aggregat/summenfunktionen

listagg

rank

pivot

group by Beispiele

eine Tabelle mehrfach verwenden

se…


kurs:listagg - created
admin (admin@undisclosed.example.com) — 2022-03-14T14:04:24+0200
select 
  department_id,
  listagg(last_name, '; ') within group (order by employee_id) emp_list
from hr.employees
  group by department_id;


kurs:rdbms
admin (admin@undisclosed.example.com) — 2022-03-08T15:44:00+0200
Relationale Datenbanksysteme

	*  MySQL
	*  PostgreSQL
	*  MS SQL Server
	*  Oracle
	*  DB2
	*  sqlite

SQL (DML)

Client

	*  SQL Developer
	*  Toad
	*  SQLplus
	*  Tableau
	*  R
	*  SPSS

<== Netzwerk (port 1521) ==>

Server - Oracle


kurs:db_sprachen - created
admin (admin@undisclosed.example.com) — 2022-03-08T13:38:37+0200
DML Data Manipulation Language - Insert, Update, Delete & Select (Merge)

Datenbankobjekte werden mit DDL manipuliert - Data Definition Language


kurs:data_dictionary_views - created
admin (admin@undisclosed.example.com) — 2022-03-07T13:33:12+0200
-- select * from user_objects where object_type = 'TABLE';
select * from all_tables where owner = 'HR';
select * from user_tab_columns;


kurs:relationen
admin (admin@undisclosed.example.com) — 2022-03-07T13:22:42+0200
Tabelle
 
* Person
  * id (synthetisch und bedeutungslos)
  * SVNr
  * Name
  * Geburtsdatum
  (* Familienstand (pseudo enum) (V, W, L, ...) => Normalisiert)
  * familienstand_id 
  (* Status (K  G L))
1 (Person) : n (email)
one to many

	*  Email
		*


kurs:cte_common_table_expression_-_with_clause
admin (admin@undisclosed.example.com) — 2022-03-07T11:12:44+0200
with cte_emp_gehalt as (
SELECT 
    employee_id "Employee ID",
    first_name || ' ' || last_name fullname,
    salary,
    commission_pct,
    hire_date,
    (salary + salary * coalesce(commission_pct, 0)) * 1.04  gehalt
from hr.employees 
order by 
  gehalt desc,
  commission_pct asc,
  salary desc, 
  fullname asc 
),
cte_emp_gehalt_lt_12k as (
select * from 
  cte_emp_gehalt where gehalt > 12000
)
select * from cte_emp_gehalt_lt_12k


wiki:zend2.tar.gz - created
mh (mh@undisclosed.example.com) — 2015-01-13T15:45:19+0200



zend2.tar.gz - created
mh (mh@undisclosed.example.com) — 2015-01-13T15:43:51+0200



postgres:world.sql - created
mh (mh@undisclosed.example.com) — 2013-08-22T10:38:25+0200