Informatik - Wiki

Syntax

1 - Benutzer erstellen

CREATE USER
username IDENTIFIED BY passwort;

2 - Passwort ändern

ALTER USER
username IDENTIFIED BY neuespw;

3 - Zuweisung von Tablespace und Quota

CREATE TABLESPACE
tablespacename
DATAFILE 'C:OracleProduct10.2.0oradataorcl ablespacename01.DBF'
SIZE 200M;

4 - Berechtigungen weitergeben

GRANT
privilege -- z.B. create session
TO username; -- oder rolename

5 - Objektberechtigungen weitergeben

GRANT ALL -- oder andere Privilegien
privname_priv
ON objektname
TO privileg_empfänger;

6 - Systemberechtigungen weitergeben

GRANT
create session, create table,
create sequence, create view
TO username;

7 - Berechtigungen zurücknehmen

REVOKE berechtigung
berechtigungs_name
ON
objekt_name
FROM user user_name;

8 - Synonym erstellen

CREATE SYNONYM
syn_name
FOR synonym_ziel;

9 - Kopien von Tabellen erstellen

CREATE TABLE
kopie_tabx
AS SELECT * FROM tabx;

10 - Weitere Berechtigungsvergabe erstellen

????

11 - Zeile einfügen

INSERT INTO
tabellenname (spalte_1, spalte_2)
VALUES (wert_1, wert_2);

12 - Rolle erstellen

CREATE ROLE rolename;

13 - Berechtigungen an Rolle weitergeben

GRANT
CREATE SESSION,
CREATE TABLE,
CREATE VIEW,
CREATE SYNONYM,
ALTER SESSION
TO rolename;

14 - View erstellen

CREATE OR REPLACE VIEW
MIT
AS (SELECT
MIT_NAME Name, Mit_Vorname Vorname, Geschlecht Sex
FROM
Mitarbeiter);

15 - Joins

????

Begriffe

DML = Data Manipulation Language
-> insert, update, delete, merge

DDL = Data Definition Language
create, drop, alter, rename, truncate

DCL = Data Control Language
->grant, revoke

transaction
->commit, rollback, savepoint

Beispiele

Select * from USER_SYS_Privs



wieviel Role hab ich?

Select * from USER_Role_privs



Benutzer Richard mit Kennwort geheim erstellen:

CREATE USER Richard IDENTIFIED By geheim;

Benutzername*: A-Z (groß/klein egal), 0-9, Sonderzeichen _ # $

Kennwort*: A-Z (groß/klein egal), 0-9, Sonderzeichen _ # $

*Muss mit Buchstabe beginnen, dann erst Zahlen/Sonderzeichen





Sucht Benutzername Dora und zeigt Benutzername an.

Select USERNAME from DBA_USERS where USERNAME =‘DORA‘;



Sucht und zeigt alle attribute von Benutzer Dora an.

Select * from DBA_USERS where USERNAME=‘DORA‘;



Like ‘DORA’ = sucht nach allem was dora enthält z. B. Dora, Millerdora, Doraschmitt, etc.
= 'DORA' = sucht nach allem was = dora ist.



Erstellen der Role EDV

CREATE ROLE EDV;



Zuweisen von Systemberechtigungen der Role EDV.

GRANT CREATE SESSION,

CREATE TABLE,

CREATE VIEW,

CREATE SYNONYM,

ALTER SESSION

To EDV;



Zuweisen der Role EDV an den Benutzer Dora.

GRANT EDV TO DORA;



Tabelspace erstellen workshoprichi in dem Pfad 'C:Oracleproduct10.2.0oradataorclworkshoprichi01.DBF':

CREATE TABLESPACE workshoprichi

DATAFILE 'C:Oracleproduct10.2.0oradataorclworkshoprichi01.DBF'

SIZE 200M;



Eigenschaften des Benutzer Dora geändert, dass er nur 5M von workshoprichi

nutzt in der temporären tabelle Temp

ALTER USER DORA

DEFAULT TABLESPACE workshoprichi

TEMPORARY TABLESPACE Temp

QuOTA 5M ON workshoprichi;



Verbinden:

connect name/kennwort

connect Dora/geheim

conn Dora/geheim





varchar 2000 zeichen

varchar2 4000 zeichen (Angabe der Länge erforderlich!)



Erstelle Tabelle ABTEILUNG,

Überschrift: AbteilungsID (2stellig),

Erstellung von Constraints /Einschränkungen(Primary Key[Primärer Schlüssel], UNIQUE[eindeutige])

CREATE TABLE ABTEILUNG

(Abt_id Number(2) CONSTRAINT ABT_idpk Primary Key,

Abt_name varchar2(40) CONSTRAINT Abt_NAME_UQ UNIQUE,

Abt_ort varchar2(35));





Select * from Abteilung; Zeigt was in Tabelle Abteilung drin steht

Describe Abteilung Zeigt die Tabellenüberschrift in der Tabelle Abteilung

Desc Abteilung siehe Describe Abteilung

Select * from USER_TABLES; zeigt die Tabellen des Users An

Select * from USER_CONSTRAINTS; zeigt die Constraints an.





INSERT INTO ABTEILUNG

VALUES (10, 'EDV', 'Bonn');

INSERT INTO ABTEILUNG

VALUES (20, 'Finance','Köln');

INSERT INTO ABTEILUNG (Abt_name, Abt_id, Abt_ort) --(Wenn die Reihenfolge nicht Bekannt is)

VALUES ('HR',30,'Berlin');



CREATE TABLE Mitarbeiter -- erstellt Tabelle MITARBEITER

(Mitarbeiter_id Number(3) Constraint Mit_id_PK Primary Key,

Mit_name varchar2(40) Constraint Name_NN NOT NULL,

Mit_vorname varchar2(40),

geschlecht varchar2(1) Constraint Sex_ck check (geschlecht in ('M','m','W','w')),

email varchar2(60) UNIQUE,

Abt_id Number(2) Constraint Abt_id_fk REFERENCES

ABTEILUNG (Abt_id) on delete CASCADE);



Benennt MITARBEITER_ID in der Tabelle MITARBEITER in MIT_ID um

alter table mitarbeiter rename column mitarbeiter_id to mit_id



Constrains

R = Reference

C = Not Null (NN) und Check

P = Primary Key

U = Unique



INSERT INTO Mitarbeiter values

(1,'Müller','Hans','M','hmüller@web.de',10);

INSERT INTO Mitarbeiter values

(2,'Schmidt','Dirk','M','dschmidt@web.de',20);

INSERT INTO Mitarbeiter values

(3,'Müller','Rita','w','rmüller@gmx.de',10);

INSERT INTO Mitarbeiter values

(4,'Mustermann','Magda','w','mmustermann@mustermail.de',10);

INSERT INTO Mitarbeiter values (MIT_NAME,MIT_VORNAME,GESCHLECHT,EMAIL,MIT_ID,ABT_ID)

('Markus','Fischer','m','marfi@web.de',5,20);

INSERT INTO Mitarbeiter values

(6,'Reinholds','Siegfried','M','reinsieg@rs-sol.de',20);

INSERT INTO Mitarbeiter values

(7,'Föhlich','Sebastian','M','sb@web.de',10);







INSERT INTO Mitarbeiter values

(&id,'&name','&vorname','&sex','&email','&Abt_id);



Select * from MITARBEITER order bei MIT_ID -> Nach Mitarbeiter_ID geordnet anzeigen







JOIN



equiJoin = ansi 93

OuterJoin =

NonequiJoin =

SelfJoin =

JoinOn = ansi 99

NATUREL Join =

Join USING = ansi 99 (Abt_ID = Abt_id)

fulloutenJoin =

LeftouterJoin =

RichtouterJoin =





Tabelle Mitarbeiter und Abteilung verbinden.



Select Mit_name, Mit_vorname, Abt_name, Abt_ort

from Mitarbeiter, Abteilung

where Mitarbeiter.Abt_id = Abteilung.Abt_id

order by Mit_name;



oder



Select Mit_name, Mit_vorname, Abt_name,Abt_ort

from Mitarbeiter Join Abteilung

on (Mitarbeiter.abt_id=Abteilung.Abt_id)

order by Mit_name;



USING

Select Mit_name, Mit_vorname, Abt_name,Abt_ort

from Mitarbeiter Join Abteilung

USING (Abt_id)

order by Mit_name;



Select Mit_name, Mit_vorname,Abt_name,Abt_ort

from Mitarbeiter NATURAL JOIN Abteilung;



Select Mit_name, Mit_vorname, Abt_name, Abt_ort

from Mitarbeiter NATURAL JOIN Abteilung;

insert into Abteilung

values (40,'co','KREFELD');

insert into Mitarbeiter

values (11,'Basel','Dieter','M','Basel@udo.de', NULL);







Select Mit_name, Mit_vorname, Abt_name, Abt_ort

from Mitarbeiter Full outer Join Abteilung

ON (Mitarbeiter.Abt_ID = Abteilung.Abt_id)

order by 1, 2 Desc;



Abteilung



Mitarbeiter


ABT_ID

ABT_NAME

ABT_ORT


MIT_ID

MIT_NAME

ABT_ID

10

EDV

Bremen


1

Müller

10

20

Fi

Bonn


2

Schmidt

10

30

CO

Köln


3

Meyer

20

40

MAN

KA


4

Peter

30





weitere Beispiele





select Mit_Name, Mit_Vorname, Abt_Name, Abt_Ort from Abteilung left outer join Mitarbeiter on (Mitarbeiter.Abt_ID=Abteilung.Abt_ID) order by 1,2 desc;



select Mit_Name, Mit_Vorname, Abt_Name, Abt_Ort from Mitarbeiter, Abteilung where Mitarbeiter.Abt_ID = Abteilung.Abt_ID;

select Mit_Name, Mit_Vorname, Abt_Name, Abt_Ort from Mitarbeiter, Abteilung where Mitarbeiter.Abt_ID (+)= Abteilung.Abt_ID;

select Mit_Name, Mit_Vorname, Abt_Name, Abt_Ort from Mitarbeiter, Abteilung where Mitarbeiter.Abt_ID = Abteilung.Abt_ID(+);



ALTER TABLE Mitarbeiter ADD Gehalt number (7,2);

ALTER TABLE Mitarbeiter ADD chef number (3);



Set NULL NULL; // zeigt, daß es NULL ist

Select * From Mitarbeiter;

Set NULL ' '; // gibt ein Leerzeichen aus, Inhalt aber immer noch NULL !

Select * From Mitarbeiter;



ALTER TABLE MITARBEITER ADD CONSTRAINT Gehalt_Grenze CHECK (Gehalt BETWEEN 5000 and 10000); // falscher Wert

ALTER TABLE MITARBEITER DROP CONSTRAINT Gehalt_Grenze // Constraint löschen

ALTER TABLE MITARBEITER ADD CONSTRAINT Gehalt_Grenze CHECK (Gehalt BETWEEN 5000 and 16000); // Constraint neu anlegen











Select Mit_ID from Mitarbeiter;

Update Mitarbeiter Set Gehalt = 7500 where Mit_ID = 1;

Update Mitarbeiter Set Gehalt = 7200, chef =1 where Mit_ID = 2;

Update Mitarbeiter Set Gehalt = 5100, chef = 2 where Mit_ID = 3;

Update Mitarbeiter Set Gehalt = 4200 where Mit_ID = 4;

Update Mitarbeiter Set Gehalt = 3500 where Mit_ID = 5;

Update Mitarbeiter Set Gehalt = 2100 where Mit_ID = 6;

Update Mitarbeiter Set Gehalt = 2000 where Mit_ID = 7;

Update Mitarbeiter Set Gehalt = 1000 where Mit_ID = 8;



// übernimmt kein Gehalt <5000, also nochmal:

Select Mit_ID from Mitarbeiter;

Update Mitarbeiter Set Gehalt = 7500 where Mit_ID = 1;

Update Mitarbeiter Set Gehalt = 7200, chef =1 where Mit_ID = 2;

Update Mitarbeiter Set Gehalt = 5100, chef = 2 where Mit_ID = 3;

Update Mitarbeiter Set Gehalt = 5000 where Mit_ID = 4;

Update Mitarbeiter Set Gehalt = 5001 where Mit_ID = 5;

Update Mitarbeiter Set Gehalt = 5001 where Mit_ID = 6;

Update Mitarbeiter Set Gehalt = 5001 where Mit_ID = 7;

Update Mitarbeiter Set Gehalt = 5001 where Mit_ID = 8;

Commit;



Update Mitarbeiter set Gehalt =7500 // gilt für gesamte Spalte

Rollback // Transaktion mit ROLLBACK rückgängig gemacht.







CREATE TABLE Gehalt_Stufe (grade varchar2(1),Min_Gehalt number(7,2),Max_Gehalt number(7,2));

Insert into Gehalt_Stufe values ('A',5000,6900); // Fehler wird später korrigiert, siehe unten

Insert into Gehalt_Stufe values ('B',7000,8900); // ***1

Insert into Gehalt_Stufe values ('C',9000,11999);

Insert into Gehalt_Stufe values ('D',12000,16000);



Select Mit_name "Mitarbeiter Name", Mit_Vorname "Mitarbeiter Vorname", grade "Gehalt Klasse" from Mitarbeiter, Gehalt_Stufe where Gehalt between Min_Gehalt AND Max_Gehalt order by "Mitarbeiter Name" DESC;



Select Mit_name as Mitarbeiter Name, Mit_Vorname as Mitarbeiter Vorname, grade as Gehalt Klasse from Mitarbeiter, Gehalt_Stufe where Gehalt between Min_Gehalt AND Max_Gehalt order by "Mitarbeiter Name" DESC; // Fehler, Alias enthält Leerzeichen. Nochmal:

Select Mit_name as MitarbeiterName, Mit_Vorname as MitarbeiterVorname, grade as GehaltKlasse from Mitarbeiter, Gehalt_Stufe where Gehalt between Min_Gehalt AND Max_Gehalt order by MitarbeiterName DESC;



column "GehaltKlasse" Format A14; // Ausgabe gilt nur für diese session !

// alpha-nummerisch 14 Zeichen.

Select Mit_name as MitarbeiterName, Mit_Vorname as MitarbeiterVorname, grade as GehaltKlasse from Mitarbeiter, Gehalt_Stufe where Gehalt between Min_Gehalt AND Max_Gehalt order by MitarbeiterName DESC;



// Korrektur (siehe oben ***1):

update gehalt_stufe set max_gehalt = 6999 where grade = 'A';

update gehalt_stufe set max_gehalt = 8999 where grade = 'B'



// selfjoin:

// verknüpft Tabelle mit sich selbst. Erstellt bis Semikolon temporäre Kopien von Mitarbeiter mit den // Namen Angestellte und Boss. Diese sind danach nicht mehr ansprechbar.

select Angestellte.Mit_Name ||' Arbeitet für '|| Boss.Mit_Name from Mitarbeiter Angestellte, Mitarbeiter Boss where Angestellte.Chef = Boss.Mit_ID; //Ausgabe wer für wen arbeitet.

select * from Mitarbeiter cross join Abteilung; // Schlechtes Bsp. Spuckt Müll- // Verknüpfungen aus



Select concat ('Hello ','world')from dual // concat ist auch ein Java-Befehl

// hängt “world” hinter “Hello” an!

// dual ist eine dummy-Tabelle !

Select 625*32 from dual // 20000



Select concat (concat ('Hello ','world'), 'blahblah' ) from dual // verknüpft vonn innen nach aussen



Zu Übungen pdf (professioneller Einstieg in Oracle 9i SQL S.211 ; ab S.694 Lösungen für Kapitel 4 + weiter Lösungen von Herrn Dhiab. Dafür anderer vorbereiteter Benutzer: HR ; Passwort: hr Erst mal angucken: select * from cat;

1. Erstellen Sie eine Abfrage, um Nachnamen, Abteilungsnummern und Abteilungsnamen

aller Angestellten anzuzeigen.

select last_name, Departments.department_ID, Department_name from employees join departments on (employees.department_ID=Departments.Department_ID)

Oder

select last_name, department_ID, Department_name from employees join departments using (department_ID)

Oder

select e.last_name, e.department_ID, d.Department_name from employees e, departments D where D.department_ID= e.Department_ID;

2. Erstellen Sie eine eindeutige Liste aller Job-Kennungen in Abteilung 80. Geben Sie in der

Ausgabe auch den Standort der Abteilung an.


SELECT DISTINCT job_id, location_id

FROM employees, departments

WHERE employees.department_id = departments.department_id

AND employees.department_id = 80;


Oder: // Beachte: DISTINCT unterdrückt doppelte Einträge in Ausgabe


select distinct E.job_ID, D.location_ID from employees e, Departments D where D.Department_ID = e.Department_ID;

And e.Department_ID = 80;


3. Erstellen Sie eine Abfrage, um für alle provisionsberechtigten Angestellten den Nachnamen,

den Abteilungsnamen, die Standortnummer und die Stadt anzuzeigen.


SELECT e.last_name, d.department_name, d.location_id, l.city

FROM employees e, departments d, locations l

WHERE e.department_id = d.department_id

AND

d.location_id = l.location_id

AND e.commission_pct IS NOT NULL;



// commission = Provision Beachte: englisch <--> deutsch: www.leo.org


Oder


select e.last_name, d.department_name, d.location_ID, L.city from Employees e, Departments D, Locations L where D.department_ID = e.Department_ID AND D.Location_ID = L.Location_ID AND e.commission_pct IS NOT NULL


oder


select e.last_name, d.department_name, d.location_ID, L.city from Employees e join Departments D on (e.Department_ID=d.department_ID) join locations L on (D.Location_ID=L.Location_ID) where e.commission_pct IS NOT NULL



4. Zeigen Sie für alle Angestellten, deren Nachname ein klein geschriebenes a enthält, den

Nachnamen und den Abteilungsnamen an. Speichern Sie die SQL-Anweisung in einer Textdatei

mit dem Namen lab4_4.sql.


SELECT last_name, department_name

FROM employees, departments

WHERE employees.department_id = departments.department_id

AND last_name LIKE '%a%';


Oder


select last_name, department_name from employees join departments on (departments.department_ID=employees.Department_ID) where last_name like '%a%';


oder


select last_name, department_name from departments join employees using (department_ID) where upper (Last_name) like upper ('%a%');


// alle die ein “a” oder „A“ enthalten. Keine 100%ige Lösung für diese Frage (… ein klein geschriebenes a enthält,…).


//Einschub: select upper ('abc'), lower('ABC'), initcap('abc def');

// ABC abc Abc Def






5. Erstellen Sie eine Abfrage, um für alle Angestellten, die in Toronto arbeiten, den Nachnamen,

die Job-Kennung, die Abteilungsnummer und den Abteilungsnamen anzuzeigen.


SELECT e.last_name, e.job_id, e.department_id,

d.department_name

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

JOIN locations l

ON (d.location_id = l.location_id)

WHERE LOWER(l.city) = 'toronto';


Oder


select e.last_name, e.job_ID, d.department_name, d.department_ID from Departments d, employees e, locations l where e.department_ID = d.department_ID AND d.location_ID = L.location_ID AND INITCAP (L.city) = initcap('Toronto');




6. Zeigen Sie den Nachnamen und die Angestelltennummer jedes Angestellten zusammen mit dem

Nachnamen und der Nummer seines Managers an. Nennen Sie die Spalten Employee, Emp#,

Manager und Mgr#. Speichern Sie die SQL-Anweisung in einer Textdatei mit dem Namen

lab4_6.sql.


SELECT w.last_name "Employee", w.employee_id "EMP#",

m.last_name "Manager", m.employee_id "Mgr#"

FROM employees w join employees m

ON (w.manager_id = m.employee_id);


Oder


select w.last_name "Employee", w.employee_ID "Emp#", a.last_name "Manager", a.employee_ID "Mgr#" From employees w, employees a where w.manager_ID = a.employee_ID;




7. Ändern Sie lab4_6.sql, um alle Angestellten anzuzeigen, einschließlich "King", dem kein

Manager zugeordnet ist. Speichern Sie die SQL-Anweisung in einer Textdatei mit dem Namen

lab4_7.sql. Führen Sie die Abfrage der Datei lab4_7.sql. aus.


SELECT w.last_name "Employee", w.employee_id "EMP#",

m.last_name "Manager", m.employee_id "Mgr#"

FROM employees w

LEFT OUTER JOIN employees m

ON (w.manager_id = m.employee_id);



8. Erstellen Sie eine Abfrage, die den Nachnamen und die Abteilungsnummer jedes Angestellten

sowie alle Angestellten, die in seiner Abteilung arbeiten, anzeigt. Geben Sie den Spalten

geeignete Namen.


SELECT e.department_id department, e.last_name employee,

c.last_name colleague

FROM employees e JOIN employees c

ON (e.department_id = c.department_id)

WHERE e.employee_id <> c.employee_id

ORDER BY e.department_id, e.last_name, c.last_name;

9. Zeigen Sie die Struktur der Tabelle JOB_GRADES an. Erstellen Sie eine Abfrage, die den

Namen, die Job-Kennung, den Abteilungsnamen, das Gehalt und die Gehaltsstufe aller

Angestellten anzeigt.


DESC JOB_GRADES

SELECT e.last_name, e.job_id, d.department_name,

e.salary, j.grade_level

FROM employees e, departments d, job_grades j

WHERE e.department_id = d.department_id

AND e.salary BETWEEN j.lowest_sal AND j.highest_sal;


-- OR


SELECT e.last_name, e.job_id, d.department_name,

e.salary, j.grade_level

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

JOIN job_grades j

ON (e.salary BETWEEN j.lowest_sal AND j.highest_sal);



10. Erstellen Sie eine Abfrage, um den Namen und das Einstellungsdatum aller Angestellten

anzuzeigen, die nach dem Angestellten Davies eingestellt wurden.


SELECT e.last_name, e.hire_date

FROM employees e, employees davies

WHERE davies.last_name = 'Davies'

AND davies.hire_date < e.hire_date


-- OR


SELECT e.last_name, e.hire_date

FROM employees e JOIN employees davies

ON (davies.last_name = 'Davies')

WHERE davies.hire_date < e.hire_date;



11. Zeigen Sie für alle Angestellten, die vor ihren Managern eingestellt wurden, den Namen und das

Einstellungsdatum sowie den Namen und das Einstellungsdatum der zugehörigen Manager an.

Nennen Sie die Spalten Employee, Emp Hired, Manager und Mgr Hired.


SELECT w.last_name, w.hire_date, m.last_name, m.hire_date

FROM employees w, employees m

WHERE w.manager_id = m.employee_id

AND w.hire_date < m.hire_date;


-- OR


SELECT w.last_name, w.hire_date, m.last_name, m.hire_date

FROM employees w JOIN employees m

ON (w.manager_id = m.employee_id)

WHERE w.hire_date < m.hire_date;

noch mehr Beispiele

ALTER TABLE Mitarbeiter ADD CONSTRAINT chef_FK Foreign key (chef) References Mitarbeiter (Mit_ID)



describe Gehalt_stufe;

select * from Gehalt_stufe;

update gehalt_stufe set grade ='A' where grade= 'B';

select * from Gehalt_stufe;

rollback;

ALTER TABLE Gehalt_stufe ADD CONSTRAINT grade_PK Primary Key(GRADE);

select * from Gehalt_stufe; // hier sieht man erst mal nichts Besonderes / kein Hinweis.

select * from User_Constraints; // aber hier !

ALTER TABLE Gehalt_Stufe ADD constraint Gehalt_Grenz check (Min_Gehalt between 5000 AND 18000); // Gehalt_Grenze wurde schon mal verwendet, deshalb Gehalt_Grenz.

update gehalt_Stufe set grade ='A'; // FEHLER in Zeile 1:
//ORA-00001: Unique Constraint (mustermann.GRADE_PK) verletzt

Update gehalt_Stufe set min_gehalt = 3000; // FEHLER in Zeile 1:
//ORA-02290: CHECK-Constraint (mustermann.GEHALT_GRENZ) //verletzt

Commit;

delete from Abteilung; // 4 Zeilen wurden gelöscht.

Rollback // Transaktion mit ROLLBACK rückgängig gemacht.

Select * from User_constraints;

ALTER TABLE Mitarbeiter DROP CONSTRAINT Abt_ID_FX;

ALTER TABLE Mitarbeiter ADD CONSTRAINT Abt_ID_FK Foreign Key (Abt_ID) References Abteilung

(Abt_ID);

delete from Abteilung; // geht jetzt auch nicht mehr : FEHLER in Zeile 1:
//ORA-02292: Integritäts-Constraint (mustermann.ABT_ID_FK) verletzt - untergeordneter //Datensatz gefunden



insert into Mitarbeiter values(14,'Müller','Hans','M','abc@web.de',79,NULL,NULL); // gewollter // Fehler: FEHLER in Zeile 1:
ORA-02291: Integritäts-Constraint (mustermann.ABT_ID_FK) verletzt - übergeordneter Schlüssel nicht gefunden kommt, weil es keine Abteilung 79 gibt !!



Gruppenfunktionen:

Select count(*), MIN(Gehalt), MAX(Gehalt), AVG(Gehalt) from Mitarbeiter;

//oder auch

Select count(Mit_ID), MIN(Gehalt), MAX(Gehalt), AVG(Gehalt) from Mitarbeiter;

// mit Aliasen (altenative Spaltenüberschriften) ! :

Select count(Mit_ID) Anzahl_Mitarbeiter , MIN(Gehalt) minimales_Gehalt, MAX(Gehalt)maximales_Gehalt, AVG(Gehalt)durchschnittliches_Gehalt from Mitarbeiter;



SELECT ABT_ID FROM MItarbeiter; // spuckt Müll aus

SELECT COUNT (DISTINCT Abt_ID) FROM MItarbeiter;

SELECT COUNT (Mit_ID), Abt_ID FROM Mitarbeiter Group BY Abt_ID;




Select count(Mit_ID) Anzahl_Mitarbeiter , MIN(Gehalt) minimales_Gehalt,

MAX(Gehalt)maximales_Gehalt, AVG(Gehalt)durchschnittliches_Gehalt from Mitarbeiter group

by(Abt_ID) Order by Min(Gehalt);

//oder :

Select count(Mit_ID) Anzahl_Mitarbeiter , MIN(Gehalt) minimales_Gehalt,

MAX(Gehalt)maximales_Gehalt, AVG(Gehalt)durchschnittliches_Gehalt from Mitarbeiter where

Abt_ID in (10,30) group by(Abt_ID) order by Min(Gehalt); // geht natürlich auch mit (10,20)



Select count(Mit_ID) Anzahl_Mitarbeiter , MIN(Gehalt) minimales_Gehalt,

MAX(Gehalt)maximales_Gehalt, AVG(Gehalt)durchschnittliches_Gehalt from Mitarbeiter group

by(Abt_ID) having AVG(Gehalt)>5000 order by Min(Gehalt); // having kommt nach group by





Select Abt_ID, Sum(Gehalt) From Mitarbeiter; // funktioniert nicht weil Spalte aufgerufen wird

// müsste man mit-gruppieren. Also:

Select Abt_ID, Sum(Gehalt), Gehalt From Mitarbeiter group by (Abt_ID, Gehalt);

Select Abt_ID, Sum(Gehalt) From Mitarbeiter group by (Abt_ID); //schönere Ausgabe



Select min(Mit_Name), max(Mit_Name) from Mitarbeiter; // zeigt alphabetisch ersten und letzten // Mitarbeiter



Select min(AVG(Gehalt)) from Mitarbeiter group by Abt_ID; // zuerst avg aller abteilungen und //davon dann der kleinste wert

Select Max(Gehalt)-Min(Gehalt) from Mitarbeiter; // ein Ergebnis (Differenz)



select Mit_Name, Mit_Vorname, Gehalt

from Mitarbeiter

where gehalt = (select max(Gehalt) from Mitarbeiter);

// Unterabfragen kann man in mehrere Ebenen nach unten entwickeln, man

//sollte aber nicht mehr als drei Ebenen nach unten gehen, möglich sind unendlich viele. Zuerst wird

//die unterste Ebene abgefragt und das Ergebnis eine Ebene nach oben gegeben. Hat die unterste

//Ebene kein Ergebnis, so ist die gesamte Abfrage ungültig.



select Mit_Name, Mit_Vorname, Gehalt from Mitarbeiter where gehalt >ANY (select max(Gehalt)

from Mitarbeiter group by Abt_ID); // Bestverdiener der Abt



select Mit_Name, Mit_Vorname, Gehalt from Mitarbeiter where gehalt >ALL (select max(Gehalt)

from Mitarbeiter group by Abt_ID);

//Es gibt niemanden der mehr verdient als das maximale Einkommen. Er müsste sich ja selbst

übertreffen Meldung: Es wurden keine Zeilen ausgewählt





select Mit_Name, Mit_Vorname, Gehalt from Mitarbeiter where gehalt >ANY (select avg(Gehalt)

from Mitarbeiter group by Abt_ID); //wer verdient mehr als der Durchschnitt



7. Ändern Sie lab4_6.sql, um alle Angestellten anzuzeigen, einschließlich "King", dem kein

Manager zugeordnet ist. Speichern Sie die SQL-Anweisung in einer Textdatei mit dem Namen

lab4_7.sql. Führen Sie die Abfrage der Datei lab4_7.sql. aus.


SELECT w.last_name "Employee", w.employee_id "EMP#",

m.last_name "Manager", m.employee_id "Mgr#"

FROM employees w

LEFT OUTER JOIN employees m

ON (w.manager_id = m.employee_id);


ODER


select m.last_name "Employee", M.Employee_ID "Emp#", B.Last_name "manager", B.Employee_ID "Emp#" From employees M, Employees B where M.manager_ID=B.employee_ID(+);



8. Erstellen Sie eine Abfrage, die den Nachnamen und die Abteilungsnummer jedes Angestellten

sowie alle Angestellten, die in seiner Abteilung arbeiten, anzeigt. Geben Sie den Spalten

geeignete Namen.


SELECT e.department_id department, e.last_name employee,

c.last_name colleague

FROM employees e JOIN employees c

ON (e.department_id = c.department_id)

WHERE e.employee_id <> c.employee_id

ORDER BY e.department_id, e.last_name, c.last_name;


ODER


Select M.department_ID "Department", M.Last_name, b.Last_name "Kollege" from employees M, employees b where M.employee_ID = b.employee_ID ORDER by M.Department_ID, M.last_name;



9. Zeigen Sie die Struktur der Tabelle JOB_GRADES an. Erstellen Sie eine Abfrage, die den

Namen, die Job-Kennung, den Abteilungsnamen, das Gehalt und die Gehaltsstufe aller

Angestellten anzeigt.


DESC JOB_GRADES // Alternative : describe JOB_GRADES;

SELECT e.last_name, e.job_id, d.department_name,

e.salary, j.grade_level

FROM employees e, departments d, job_grades j

WHERE e.department_id = d.department_id

AND e.salary BETWEEN j.lowest_sal AND j.highest_sal;


-- OR


SELECT e.last_name, e.job_id, d.department_name,

e.salary, j.grade_level

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

JOIN job_grades j

ON (e.salary BETWEEN j.lowest_sal AND j.highest_sal);


ODER


SELECT last_name, job_ID, department_ID, salary, grade from employees join departments USING(department_ID) join job_grades ON Salary BETWEEN Lowest_sal AND Highest_sal;



10. Erstellen Sie eine Abfrage, um den Namen und das Einstellungsdatum aller Angestellten

anzuzeigen, die nach dem Angestellten Davies eingestellt wurden.


SELECT e.last_name, e.hire_date

FROM employees e, employees davies

WHERE davies.last_name = 'Davies'

AND davies.hire_date < e.hire_date


-- OR


SELECT e.last_name, e.hire_date

FROM employees e JOIN employees davies

ON (davies.last_name = 'Davies')

WHERE davies.hire_date < e.hire_date;


ODER


SELECT e.Last_name, e.hire_date from employees e, employees d where upper (d.last_name) = upper ('Davies') AND d.hire_date<e.hire_date;


ODER


Select last_name, hire_date from Employees where hire_date>(Select hire_date from employees where upper (last_name) IN('DAVIES'));



Einschub - Wer verdient mehr als der Durchschnitt in seiner eigenen Abteilung?


Select e.last_name, e.first_name, e.salary from employees e, (select avg(salary), department_id from employees group by department_ID) outer where e.salary >any (select avg(salary) from employees group by department_ID) and e.department_ID=outer.department_ID;




11. Zeigen Sie für alle Angestellten, die vor ihren Managern eingestellt wurden, den Namen und das

Einstellungsdatum sowie den Namen und das Einstellungsdatum der zugehörigen Manager an.

Nennen Sie die Spalten Employee, Emp Hired, Manager und Mgr Hired.


SELECT w.last_name, w.hire_date, m.last_name, m.hire_date

FROM employees w, employees m

WHERE w.manager_id = m.employee_id

AND w.hire_date < m.hire_date;


-- OR


SELECT w.last_name, w.hire_date, m.last_name, m.hire_date

FROM employees w JOIN employees m

ON (w.manager_id = m.employee_id)

WHERE w.hire_date < m.hire_date;

noch viel mehr Beispiele

CREATE VIEW Mit AS (SELECT Mit_NAME, Mit_Vorname, Geschlecht from Mitarbeiter);

Select * From Mit;

UPDATE Mit SET Mit_Name ='Schröder' where Mit_Name ='Basel'; // ändert nicht nur view sondern // ganze Tabelle Mitarbeiter

commit;

Select * From Mitarbeiter; // gleiche Änderung sichtbar



CREATE OR REPLACE VIEW MIT AS (SELECT MIT_NAME Name, Mit_Vorname Vorname, Geschlecht Sex from Mitarbeiter);

Select * From Mit;



CREATE OR REPLACE VIEW MIT AS (SELECT MIT_NAME Name, Mit_Vorname Vorname, Geschlecht Sex from Mitarbeiter) with READ ONLY; // nur noch Leseberechtigung



Select * From Mit;

Delete From Mit; // geht nicht, da nur lesen



CREATE OR REPLACE VIEW Abt_10_20 AS (Select Mit_Name, Mit_Vorname, Abt_ID FROM Mitarbeiter where Abt_ID in (10,20)) with check option; // Versuch fkt nicht s.u.

Versuch:

Update Abt_10_20 Set Abt_ID = 30 where Abt_ID = 10; // FEHLER in Zeile 1:
// ORA-01402: Verletzung der WHERE-Klausel einer View WITH CHECK OPTION



CREATE OR REPLACE VIEW My_view AS (SELECT Max(Gehalt)Max, Min(Gehalt)Min from Mitarbeiter group by Abt_ID); // nicht veränderbar über DML-Anweisung

Select * From My_view; // mal gucken ;)

Update My_VIEW Set Min = 7770; // FEHLER in Zeile 1:
//ORA-01732: Datenmanipulationsoperation auf dieser View nicht zulässig

CREATE VIEW my_next_view AS(SELECT Mit_Name, Mit_Vorname, Abt_name, Abt_ort From Mitarbeiter Natural Join Abteilung); // View wurde erstellt.

// auch eine komplexer View. Änderungen nicht möglich, da diese neue Liste eine Zusammenstellung // aus Spalten verschiedener Tabellen ist. Diverse Mussfelder wie z.B. ID fehlen !

select * from my_next_view; // guck !

select * from user_views; // zeigt alle views an! Aber Inhalt gekürzt auf 80Zeichen

set long 3000; // speichert bis zu 2GB in einem Feld / hier 3000 Zeichen.

select * from user_views; // man sieht mehr Text



Synonym:

CREATE SYNONYM My_Syno for Mitarbeiter; // macht Sinn engl. de

Select * from My_Syno;

Update My_Syno SET Mit_Name = 'Hanspaul' where Mit_ID = 2; // NAME ÄNDERN !



Objektprivilegien:

  • Table

  • View

  • Synonym



Nicht ausführen ! :

DROP VIEW My_View; // löscht Nicht ausführen !

DROP TABLE My_Table; // löscht Nicht ausführen !

DROP SYNONYM My_Syno; // löscht Nicht ausführen !



Über System/oracle:

CREATE USER USER_TR1 IDENTIFIED BY a; // TR1,TR2,TR3 sollten nur einmal vorkommen

CREATE USER USER_TR2 IDENTIFIED BY a; // von mir so gewählt weil meine Initialien + Zahl

Select USERNAME FROM DBA_USERS; // spuckt alle user aus (meine auch) !

Select USERNAME FROM DBA_USERS where USERNAME LIKE '%TR%'; // zeigt nur meine !



CREATE ROLE My_Role; // Rolle wurde erstellt.

GRANT CREATE SESSION TO My_Role; // Rolle darf sich anmelden (sonst nichts) !

// Benutzerzugriff (Grant) wurde erteilt.

GRANT My_Role To User_TR1,USER_TR2,USER_TR3; // alle 3 bekommen die Rolle!

// Benutzerzugriff (Grant) wurde erteilt.

GRANT CREATE TABLE TO My_Role; // Die Rolle darf jetzt Tabellen erstellen

Select * from DBA_Role_Privs; // zeigt Benutzer + zugewiesene Rollen



Weiter mit Besitzer-Namen von Mitarbeiter

Select * from cat; // man sieht TABLE, VIEW und SYNONYM



Grant select, insert on my_view to USER_TR1; //Benutzerzugriff (Grant) wurde erteilt.

Grant select, update on Mitarbeiter to USER_TR2; //Benutzerzugriff (Grant) wurde erteilt.

Grant select on my_syno to USER_TR3; //Benutzerzugriff (Grant) wurde erteilt.



connect USER_TR1/a; //umgemeldet von mustermann zu USER_TR1

Select * from mustermann.My_View; // mustermann ist in diesem Falle Besitzer der // Tabelle My_View



connect USER_TR2/a; // umgemeldet von USER_TR1 zu USER_TR2

Select * from mustermann.Mitarbeiter; // aktuellen Zustand noch mal angucken

UPDATE mustermann.Mitarbeiter set Gehalt = 7000 where Mit_ID IN (2,3); // Gehaltänderung für ID 2&3

Select * from mustermann.Mitarbeiter; // Änderung sichtbar

delete from mustermann.Mitarbeiter; // Nicht ausreichende Berechtigungen

update besitzer.Mitarbeiter set Abt_ID = 20; // würde fkt, aber nicht getestet





connect Besitzer/Passwort;

Grant Delete on Mitarbeiter to User_TR1 with grant option;

// Bekommt delete-Befehl und darf diese Funktion weitergeben durch Befehlteil WITH GRANT // OPTION. Wenn er das nicht können soll, muss man den Teil einfach weglassen

Revoke Delete on Mitarbeiter from USER_TR1; // Benutzerzugriff wurde aufgehoben (Revoke).



System/oracle

connect System/oracle;

GRANT CREATE USER TO mustermann WITH ADMIN OPTION;

// Die Systemberechtigung CREATE USER für einen User (hier: mustermann) erteilen



Grant CREATE USER TO USER_TR3 with admin option; // geben

Revoke CREATE USER FROM USER_TR3; // und zurück nehmen

ALTER USER USER_TR1 IDENTIFIED BY *******; // ****** = neues Passwort



Das Lesezeichen rollback:

connect Besitzer/Passwort; // zu Besitzer von Mitarbeiter

Select * from Mitarbeiter;

insert into Mitarbeiter values (17,'Martha','Peter','M','Mar@web.de',10,7000,NULL);

Select * from Mitarbeiter;

SAVEPOINT transaction_1; // Savepoint wurde angelegt.

update MItarbeiter set Mit_Name = 'Klaus' where Mit_ID = 7;

Select * from Mitarbeiter;

SAVEPOINT transaction_2; // Savepoint wurde angelegt.

DELETE FROM MItarbeiter where Mit_ID = 3;

Select * FROM Mitarbeiter;

SAVEPOINT transaction_3; // Savepoint wurde angelegt.

DELETE from Mitarbeiter;

Select * from Mitarbeiter;

SAVEPOINT transaction_4; // Savepoint wurde angelegt.

Select * from Mitarbeiter; // Es wurden keine Zeilen ausgewählt (Liste leer)

Rollback to Transaction_4;

Select * from Mitarbeiter; // immernoch leer, nach 4 ist nichts passiert.

Rollback to Transaction_3;

Select * from Mitarbeiter; // wieder Inhalt da !

Rollback to Transaction_2;

Select * from Mitarbeiter; // Mitarbeiter mit ID3 wieder da !

Rollback to Transaction_1;

Select * from Mitarbeiter; // ID7 hat wieder alten Nachnamen

Rollback;

Select * from Mitarbeiter; // ID17 wieder weg = rollback zum letzten commit



Wenn ich disconnecte oder mich regulär abmelde macht das System ein auto-commit !!!

Das heißt bei rollback geht es zurück zum letzten commit bzw bis zur Anmeldung !



CREATE TABLE Kopie_Mitarbeiter AS Select * From Mitarbeiter; // erstelle Kopie von // Mitarbeiter. Die beiden tabellen haben den gleichen Inhalt, aber nichts mit einander zu tun !

Geht auch vom System aus:

connect System/oracle;

CREATE TABLE Mitarbeiter_Kopie AS Select * From mustermann.Mitarbeiter;



13. Übung – Lösungen

1. Welches Privileg muss einem Benutzer erteilt werden, damit er sich auf dem Oracle-Server

anmelden kann? Handelt es sich dabei um ein System- oder ein Objektprivileg?


Systemprivileg CREATE SESSION

// GRANT CREATE SESSION TO USER




2. Welches Privileg muss einem Benutzer erteilt werden, damit er Tabellen erstellen kann?


CREATE TABLE-Privileg

// CREATE TABLE Systemprivileg

// GRANT CREATE TABLE TO USER / Systemprivileg




3. Wer kann Privilegien für eine Tabelle, die Sie erstellt haben, an andere Benutzer vergeben?


Privilegien können von Ihnen und jeder Person vergeben werden, denen Sie diese

Privilegien mit der Option WITH GRANT OPTION erteilt haben.

// (System kann das auch)



4. Sie sind ein DBA. Sie erstellen zahlreiche Benutzer, die dieselben Systemprivilegien erhalten

sollen. Was können Sie verwenden, um sich diese Aufgabe zu erleichtern?


Eine Rolle, die die Systemprivilegien enthält und den Benutzern zugewiesen wird




5. Mit welchem Befehl können Sie Ihr Passwort ändern?


Mit der ALTER USER-Anweisung

// ALTER USER User_Name IDENTIFIED BY *******; // ****** = neues Passwort




6. Gewähren Sie einem anderen Benutzer Zugriff auf Ihre Tabelle DEPARTMENTS. Lassen Sie sich

von diesem Benutzer das Privileg zur Abfrage seiner Tabelle DEPARTMENTS erteilen.


Team 2 führt die GRANT-Anweisung aus.

GRANT select

ON departments

TO <user1>;

Team 1 führt die GRANT-Anweisung aus.

GRANT select

ON departments

TO <user2>;

Hierbei gilt: user1 ist der Name von Team 1, und user2 ist der Name

von Team 2.






7. Fragen Sie alle Zeilen in Ihrer Tabelle DEPARTMENTS ab.


SELECT *

FROM departments;



8. Fügen Sie Ihrer Tabelle DEPARTMENTS eine neue Zeile hinzu. Team 1 fügt die Abteilung

"Education" mit der Abteilungsnummer 500 hinzu. Team 2 fügt die Abteilung "Human

Resources" mit der Abteilungsnummer 510 hinzu. Fragen Sie die Tabelle des jeweils anderen

Teams ab.


Team 1 führt diese INSERT-Anweisung aus:

// Describe departments; // gucken wieviele und welche Spalten vorhanden sind

// GRANT SELECT ON DEPARTMENT TO TEAM2;

INSERT INTO departments(department_id, department_name)

VALUES (500, 'Education');

COMMIT;

// Select * from Team2.Departments;


Team 2 führt diese INSERT-Anweisung aus:

// Describe departments; // gucken wieviele und welche Spalten vorhanden sind

// GRANT SELECT ON DEPARTMENT TO TEAM1;

INSERT INTO departments(department_id, department_name)

VALUES (510, 'Administration');

COMMIT;

// Select * from Team1.Departments;




9. Erstellen Sie ein Synonym für die Tabelle DEPARTMENTS des anderen Teams.


(// CREATE SYNONYM My_Synonym for DEPARTMENTS;)


Team 1 erstellt ein Synonym namens team2.

CREATE SYNONYM team2

FOR <user2>.DEPARTMENTS;

Team 2 erstellt ein Synonym namens team1.

CREATE SYNONYM team1

FOR <user1>. DEPARTMENTS; // völlig OK !!!




10. Fragen Sie alle Zeilen in der Tabelle DEPARTMENTS des anderen Teams ab, und verwenden Sie

dabei Ihr Synonym.


Team 1 führt diese SELECT-Anweisung aus:

SELECT * FROM team2;

Team 2 führt diese SELECT-Anweisung aus:

SELECT * FROM team1;





11. Fragen Sie die View USER_TABLES im Data Dictionary ab, um Informationen über die Tabellen

anzuzeigen, die Ihnen gehören.


SELECT table_name // und nicht mehr, kein *

FROM user_tables;






12. Fragen Sie die View ALL_TABLES im Data Dictionary ab, um Informationen über alle Tabellen

anzuzeigen, auf die Sie zugreifen können. Schließen Sie die Tabellen aus, die Ihnen gehören.


SELECT table_name, owner

FROM all_tables // alle die ich sehen darf

WHERE owner <> <your account>;




13. Entziehen Sie dem anderen Team das SELECT-Privileg.


Team 1 entzieht das Privileg.

REVOKE select

ON departments

FROM user2;

Team 2 entzieht das Privileg.

REVOKE select

ON departments

FROM user1;




14. Löschen Sie die Zeile, die Sie in Schritt 8 in die Tabelle DEPARTMENTS eingefügt haben, und

speichern Sie die Änderungen.


Team 1 führt diese DELETE-Anweisung aus:

DELETE FROM departments

WHERE department_id = 500;

COMMIT;

Team 2 führt diese DELETE-Anweisung aus:

DELETE FROM departments

WHERE department_id = 510;

COMMIT;



top