NJUGP50Y

1select employee_id, salary, salary * 1.04 as "Salary um 4% erhöht" from employees;
2select employee_id, salary from employees where salary < 4000;
3select employee_id, salary from employees where salary > 2000 and salary < 4000;
4select * from jobs order by max_salary desc;
5select * from employees where employee_id = 108 or manager_id = 108;
6select * from employees where last_name like 'G%';
7select sum(salary) as SummeGehälter, round((sum(salary)/count(salary)), 2) as Durchschnittsgehalt from employees;                                                     
8select round(avg(salary), 2), min(salary), max(salary) from employees where department_id = 80;
9select job_id, count(*) from employees group by job_id;
10select department_id, count(*) from employees where department_id is not null group by department_id;
11select job_id, count(*) from employees group by job_id having count(*) > 9;
12select department_id, round(avg(salary), 2), min(salary), max(salary) from employees group by department_id;
13select manager_id, count(*) from employees where department_id = 50 and manager_id != 100 group by manager_id;
14select department_id, round((avg(salary)/max(salary)), 2) * 100 || '%' as anteil from employees group by department_id;
15select max(round(avg(salary), 2)) from employees group by department_id;
16select employee_id from job_history group by employee_id having count(*) >= 2;
17select e.employee_id, e.first_name, e.last_name, l.city from employees e, departments d, locations l where e.department_id = d.department_id and d.location_id = l.location_id; 
18select l.city, count(*) from departments d, locations l where d.location_id = l.location_id group by l.city;
19select c.country_name, count(*) from locations l, countries c where l.country_id = c.country_id group by c.country_name;
20select e.employee_id, e.first_name, e.last_name from employees e, departments d where e.department_id = d.department_id and d.department_name = 'Sales';
21select department_name from departments d, locations l, countries c, regions r where d.location_id = l.location_id and l.country_id = c.country_id and c.region_id = r.region_id and r.region_name = 'Europe';
22select d.department_name, sum(salary) from employees e, departments d where e.department_id = d.department_id group by department_name;
23select r.region_name, count(*) from regions r, countries c where c.region_id = r.region_id group by r.region_name;
24select distinct r.region_name, job_title from jobs j, employees e, departments d, locations l, countries c, regions r where j.job_id = e.job_id and e.department_id = d.department_id and d.location_id = l.location_id and l.country_id = c.country_id and c.region_id = r.region_id order by region_name;
25select l.city, round(avg(e.salary), 2) from employees e, departments d, locations l where e.department_id = d.department_id and d.location_id = l.location_id group by l.city;
26select d.department_name, l.city from departments d right join locations l using (location_id);                                             
27select e2.first_name, e2.last_name from employees e1, employees e2 where e1.salary = e2.salary and e1.first_name = 'Douglas' and e1.last_name = 'Grant' and e1.employee_id != e2.employee_id;
28select e2.last_name as Mitarbeiter, e1.last_name as Vorgesetzter from employees e1, employees e2 where e1.employee_id = e2.manager_id;
29select e2.last_name from employees e1, employees e2 where e1.employee_id = e2.manager_id and e2.salary > e1.salary;
30select employee_id, last_name from employees e where exists (select * from employees where e.employee_id = manager_id);                                  
31select employee_id from employees where salary < (select avg(salary) from employees);
32select employee_id from employees where salary > (select salary from employees where first_name = 'Bill' and last_name = 'Gates');
33select department_name, avg(salary) from employees e, departments d where e.department_id = d.department_id group by department_name;
34select avg(salary) from employees;
35select department_name from (select department_name, avg(salary) durchschnitt from employees e, departments d where e.department_id = d.department_id group by department_name) where durchschnitt > (select avg(salary) from employees);
36select department_name, round((sum(salary)/(select sum(salary) from employees)*100), 2) Anteil from employees e, departments d where e.department_id = d.department_id group by department_name;
37select city from (select l.city, avg(salary) durchschnitt from employees e, departments d, locations l where e.department_id = d.department_id and d.location_id = l.location_id group by l.city) where durchschnitt > (select avg(salary) from employees);
38select job_id, min(salary) from employees group by job_id;
39select job_id, first_name, last_name from employees where (job_id, salary) in (select job_id, min(salary) from employees group by job_id);
40select country_name from countries c where not exists (select * from locations l, departments d where d.location_id = l.location_id and l.country_id = c.country_id);
41select job_id, avg(salary) from employees group by job_id;
42select job_id, avg(salary) from employees group by job_id having avg(salary) = 2780;
43select min(durchschnitt) from (select job_id, avg(salary) durchschnitt from employees group by job_id);
44select job_id from employees group by job_id having avg(salary) = (select min(durchschnitt) from (select job_id, avg(salary) durchschnitt from employees group by job_id));
45select first_name, last_name, salary from employees where job_id = (select job_id from employees group by job_id having avg(salary) = (select min(durchschnitt) from (select job_id, avg(salary) durchschnitt from employees group by job_id)));
46select distinct e.employee_id from employees e, job_history j where e.employee_id = j.employee_id;
47select department_id from departments minus select department_id from employees; 
48select city from locations where location_id in (select location_id from locations minus select location_id from departments);
49select employee_id, to_char(hire_date, 'ww-YYYY') from employees order by hire_date;
50select to_char(hire_date, 'YYYY'), count(*) from employees group by to_char(hire_date, 'YYYY') order by to_char(hire_date, 'YYYY');
51select first_name, last_name, phone_number, substr(first_name, 1, 1) || substr(last_name, 1, 1) || reverse(substr(reverse(phone_number), 1, 4)) as Loginkennung from employees;
52select employee_id, lpad(' ',salary/500,'*') from employees;
53select substr(last_name, 1, 1), count(*) from employees group by substr(last_name, 1, 1) order by substr(last_name, 1, 1);
54select employee_id, case  when (salary > 0 and salary < 4000) then 'LOW' when (salary > 4000 and salary < 8000) then 'MEDIUM' when salary > 8000 then 'HIGH' end from employees;
55insert into jobs values('SE_PROG', 'Seniorprogrammer', 10000, 15000);
56update employees set salary = salary + 100 where salary < (select avg(salary) from employees);
57update employees set salary = salary * 1.1 where employee_id in (select manager_id from employees);
58update employees e1 set salary = (select salary from employees e2 where e2.employee_id = e1.manager_id) * 0.9 where employee_id = 115;
59delete from abteilungen a where not exists (select * from employees e where e.department_id = a.department_id);
60update employees set job_id = 'SE_PROG' where employee_id = 103;
61alter table employees add Urlaubstage number(2);
62drop table zahlen;
63create table zahlen (zahl1 number(8,2));
64declare
65i number := 1;
66zahl number := 1;
67delta number := 2;
68begin
69    while i < 13
70    loop
71        insert into zahlen values(zahl);
72        commit;
73        zahl := zahl + delta;
74        i := i + 1;
75    end loop;
76end;
77/
78select * from zahlen;
79create or replace TRIGGER A84 
80BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES 
81FOR EACH ROW
82declare lohn employees.salary%type;
83begin
84select max_salary into lohn from jobs where job_id = :old.job_id;
85  if :new.salary > lohn
86  then
87  :new.salary := lohn;
88  end if;
89END;
90
91Cursor sind Speicherbereiche, die das Ergebnis einer Select-Anweisung beinhalten
92Prozeduren sind ausführbare Programme
93Prozedur steht in der DB, anonymer Block nicht
94referentielle Integrität: zu jedem Fremdschlüssel gebt es einen Primärschlüssel