–schemas and Samples
@’o:\db-sample-schemas-master\mksample.sql‘;
–TABLESPACE
CREATE TABLESPACE tbs_custom_tablespace
DATAFILE ‚O:\customs\test1.dbf‘ SIZE 3G ONLINE;
CREATE USER test1 IDENTIFIED BY Pa$$w0rd DEFAULT TABLESPACE tbs_custom_tablespace;
ALTER USER pop DEFAULT TABLESPACE tbs_custom_tablespace
QUOTA 3G on tbs_custom_tablespace ;
–QUOTA UNLIMITED ON USERS;
–create user
CREATE USER pop
IDENTIFIED BY Pa$$w0rd
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users ;
–GRANT
GRANT CONNECT to pop;
GRANT CREATE TABLE to pop;
Grant UPDATE ON HR.employees to pop;
Grant select any table to pop;
GRANT DELETE ANY Table to pop;
GRANT CONNECT TO mike_bondzio
connect warenhaus/Pa$$w0rd
DROP USER mike_bondzio CASCADE;
SHOW USER;
rollback ;
commit;
drop table meinecountries;
–Inhalt einer Tabelle l?schen
TRUNCATE TABLE meinemitarbeiter;
DELETE * FROM warenhaus.lagerbestahnd
where;
DELETE FROM warenhaus.Artikelkategorie WHERE ID=5
DESCRIBE HR.employees;
— view views table
select * from user_views;
–13012021 creat PRIMARY KEY
CREATE TABLE kuenstler (
kuenstler_id INTEGER CONSTRAINT PK_kuenstler PRIMARY KEY
, typ VARCHAR2(15) CONSTRAINT VerName_NN NOT NULL
, kuenstler_name VARCHAR(50);
CREATE TABLE Auftritt (
kuenstlerid INTEGER
, VeranstlID INTEGER
, CONSTRAINT PK_Auftritt PRIMARY KEY (kuenstlerid, VeranstlID));
ALTER TABLE GENDER ADD CONSTRAINT UQ_GenderID UNIQUE (genderid);
— create FOREIGN KEY
ALTER TABLE besuche ADD CONSTRAINT FK_Besuche_Veranstaltung
FOREIGN KEY (VeranstID) REFERENCES veranstaltung (VeranstID);
alter table waren.Artikel add constraint FK_Artikelkategorie
FOREIGN KEY (kategorie_ID)
REFERENCES waren.Artikelkategorie (ART_ID);
— Ein Attribut der einer Tabelle hinzuf?gen
ALTER TABLE warenhaus.lagerbestand ADD eingelagertam DATE;
— Ein Attribut aus einer Tabelle l?schen
ALTER TABLE warenhaus.lagerbestand DROP COLUMN eingelagertam;
Alter Table Artikel rename column id to Art_ID;
ALTER TABLE warenhaus.artikel
MODIFY ARTIKELNR DEFAULT SEQ_PK_Artikel.nextval ;
create table waren.Artikel (
Artikel_ID number(38) CONSTRAINT PK_Artikel_nn PRIMARY Key
,AR_Name varchar2(50)
,Preis number (7,2) constraint preis_s not null
,kategorie_ID number
, constraint FK_kategorie Foreign KEY (kategorie_ID) REFERENCES waren.Artikelkategorie (ART_ID)
);
CREATE TABLE Gehalt(
gehalt_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
gehalt DECIMAL(9,2) NOT NULL,
m_nr int UNIQUE NOT NULL,
CONSTRAINT fk_gehalt_mitarbeiter_m_nr FOREIGN KEY (m_nr)
REFERENCES Mitarbeiter(m_nr) ON DELETE CASCADE
);
CREATE TABLE meineMitarbeiter (
id NUMBER(5) CONSTRAINT PK_meineMitarbeiter PRIMARY KEY
, vorname VARCHAR(50) CONSTRAINT vorname_nn NOT NULL
, nachname VARCHAR(50) CONSTRAINT nachname_nn NOT NULL
, GebDatum DATE
, geschlecht CHAR(1 CHAR) CONSTRAINT CK_gender CHECK (geschlecht IN (‚M‘, ‚W‘, ‚D‘) )
);
— Erstellen einer Tabelle mit IDENTITY-Spalte
CREATE TABLE test500 (id NUMBER GENERATED ALWAYS AS IDENTITY START WITH 50 INCREMENT BY 5 , name VARCHAR(50));
–INSERT
INSERT INTO kuenstler (kuenstler_id, typ, kuenstler_name )
VALUES (1, ‚Musiker‘, ‚Karl Kunze‘);
INSERT INTO Arbeiten VALUES (18316, ‚p2‘,
null, TO_DATE(‚2019-06-01‘, ‚YYYY-MM-DD‘));
–INSERT from table
INSERT INTO meinecountries (country_i , Landname)
SELECT COUNTRY_ID , COUNTRY_NAME
FROM HR.countries
where REGION_ID = 1 or REGION_ID = 2 or REGION_ID = 4;
–oder
INSERT INTO meinecountries (country_i , Landname)
SELECT COUNTRY_ID , COUNTRY_NAME
FROM HR.countries
where REGION_ID in (1,2,4);
— select
SELECT * FROM HR.EMPLOYEES;
SELECT SALARY , COMMISSION_PCT
, SALARY *1.10 „Monatsgehalt 10%“
,SALARY * 1.10*12 „Jahresgehalt 10%“
, SALARY + 1000 „EZMONEY“
, SALARY +1000*12 „JahresEZMONEY“
, SALARY * 2.25 „Monatsgehalt +25%“
, SALARY * 2.25*12 „Jahresgehalt +25%“
, SALARY *0.67 „Monatsgehalt -33%“
, SALARY *0.67*12 „Jahresgehalt -33%“
FROM HR.EMPLOYEES
WHERE COMMISSION_PCT IS NULL;
select *
from sh.products
where prod_category in (‚Photo‘ ,’Hardware‘ ,’Electronics‘);
— Abfrage mit non-equi Join
SELECT *
FROM HR.Employees e
INNER JOIN HR.DEPARTMENTS d ON (e.department_id BETWEEN 10 AND 80)
AND (e.department_id=d.department_id);
select * from HR.departments a
INNER JOIN HR.locations b on a.location_id=b.location_id
where manager_ID is null;
SELECT a.menge+(a.menge*10/100) as plus_10_pro , a.menge , c.ar_name , b.name
from waren.Lagerbestand a
INNER JOIN waren.lager b on a.lager_id=b.lagerid
INNER JOIN WAREN.artikel c on a.artikelnr_id=c.artikel_id
SELECT a.name, a.preis
FROM warenhaus.artikel a
INNER JOIN warenhaus.lager l ON l.name<>a.name;
ORDER BY menge DESC ; –asc — oder nummer — 3
–LEFT OUTER JOIN — right OUTER JOIN –full jion — between and — = — <>
SELECT *
FROM SH.Customers c
LEFT OUTER JOIN SH.sales s ON s.cust_id=c.cust_id
WHERE cust_postal_code BETWEEN 45000 AND 80000;
–selfe join in the same table
SELECT
a.employee_id
,a.first_name ||‘ ‚|| a.last_name as name
,b.first_name ||‘ ‚|| b.last_name as Manager_n
FROM HR.employees a
inner join HR.employees b on a.manager_id=b.employee_id
order by 1;
SELECT
employees.employee_id
,employees.first_name AS „TabelleEmployee FirstName“
, manager.first_name AS „TabelleManager Firstname“
, employees.manager_id AS „TabelleEmployee Manager_ID“
, manager.employee_id AS „TabelleManager ID“
FROM HR.Employees employees
inner JOIN HR.EMPLOYEES manager ON manager.employee_id=employees.manager_id
order by 1;
select * from sh.products where prod_category=’Photo‘ or prod_category=’Hardware‘ or prod_category=’Electronics‘ ;
select * from sh.products where prod_category in (‚Photo‘ ,’Hardware‘ ,’Electronics‘);
— || — concat
SELECT cust_FIRST_NAME || ‚ ‚ || cust_LAST_NAME
FROM SH.CUSTOMERS
UNION ALL
SELECT FIRST_NAME || ‚ ‚ || LAST_NAME
FROM HR.EMPLOYEES;
SELECT employee_id , first_name, JOB_ID
, last_name, first_name || ‚ ‚ || last_name || ‚ verdient im Monat ‚ || salary AS FULLNAME , hire_date, hire_date + 14
, salary AS Monatsgehalt, salary * 1.05*12 AS Jahresgehalt, CONCAT(first_name || ‚ ‚ , last_name) AS concat
, 25
FROM HR.EMPLOYEES;
— und Verkettungen von Strings mit der ISO-Funktion CONCAT
SELECT employee_id , first_name, JOB_ID
, last_name, hire_date
, salary AS Monatsgehalt, salary * 1.05*12 AS Jahresgehalt, CONCAT(first_name , last_name) AS concat
, ‚Mitarbeiter‘
FROM HR.EMPLOYEES;
SELECT CONCAT(first_name , last_name) AS concat
, salary AS Monatsgehalt, (salary * COALESCE(commission_pct,6, null,5, 0) + salary)*12 AS gesamt
FROM HR.EMPLOYEES;
–full outer JOIN — cross join — inner join — with aliases
SELECT a.first_name||‘ ‚|| a.last_name , b.cust_first_name ||‘ ‚|| b.cust_last_name
from HR.employees a
full outer JOIN SH.customers b on a.employee_id=b.cust_id;
–UNION — MINUS — –union all
SELECT cust_FIRST_NAME || ‚ ‚ || cust_LAST_NAME
FROM SH.CUSTOMERS
UNION
SELECT FIRST_NAME || ‚ ‚ || LAST_NAME
FROM HR.EMPLOYEES;
— Bilden der Schnittmenge zweier Mengen
SELECT Country_name
FROM HR.countries
INTERSECT
SELECT country_name
FROM SH.Countries;
–distinct
SELECT distinct PRod_category FROM SH.Products
—sys
SELECT * FROM USER_SYS_PRIVS;
SELECT sysdate FROM DUAL
SELECT sys_context(‚USERENV‘, ‚SID‘)
FROM DUAL;
–Einfügen von Daten in die Tabelle Mit_erweiter in der Spalte wohnort
UPDATE Mit_erweiter
SET wohnort = ‚München‘
WHERE m_nr in (25348, 2581);
–update
UPDATE HR.EMPLOYEEs SET first_name = ‚Steven_test%‘ WHERE employee_id=128;
UPDATE HR.EmPLOYEES SET last_name=’King‘, email=UPPER(’sking‘) WHERE employee_id=100;
UPDATE warenhaus.lagerbestand SET MENGE = NULL
WHERE lagerid =1;
–order
ORDER BY
ORDER BY first_name ASC, salary DESC;
— L?schen von Datens?tzen
DELETE FROM warenhaus.lagerbestand
WHERE lagerid=4 AND platzid=1 AND Artikelnr=1;
— Eine Sequenz erstellen
CREATE SEQUENCE myseq1
START WITH 50 — Startwert
INCREMENT By 5 — Erh?hung
MAXVALUE 100 — Maximalwert
MINVALUE 50 — Minimalwert
CYCLE — Bei erreichen des Maximalwertes wird wieder von vorne begonnen
CACHE 2 — Anzahl der Nummern welche im Arbeitsspeicher vorgehalten werden;
— Abfragen des n?chsten Wertes einer Sequenz
SELECT name, myseq3.nextval FROM warenhaus.artikel;
— Abfragen des aktuellen Wertes einer Sequenz
SELECT name, myseq3.currval as zahl FROM warenhaus.artikel;
SELECT myseq2.NextVal, myseq2.currval FROM DUAL;
/*
Aufgabe 4.13
Nennen Sie alle Mitarbeiter, deren Personalnummer weder 10102 noch 9031 ist.
*/
SELECT m_nr , m_name from PROJEKT_DB.mitarbeiter where m_nr <> 10102 and m_nr <> 9031 ;
/*
Aufgabe 4.16
Nennen Sie die Personalnummer aller Mitarbeiter,
die Projektleiter sind und vor oder nach 2018 in ihren Projekten eingestellt wurden.
*/
SELECT * from PROJEKT_DB.arbeiten
SELECT m_nr from PROJEKT_DB.arbeiten where aufgabe = ‚Projektleiter‘
and einst_dat not between ‚01.01.18‘ and ‚31.12.18‘ ;
SELECT m_nr from PROJEKT_DB.arbeiten where aufgabe = ‚Projektleiter‘
and einst_dat < ‚01.01.2018‘ andeinst_dat > ‚31.12.2018‘ ;
/*
Aufgabe 1:
Schreiben Sie eine Abfrage die Ihnen alle Mitarbeiter (Tabelle HR.Employees)
anzeigt die mit einem A anfangen gefolgt von einen Buchstaben und als letzten
Buchstaben ein r haben(last_name).
*/
SELECT * FROM hr.employees ;
SELECT last_name
FROM hr.employees where last_name LIKE (‚A%_e‘) ;
/*
Aufgabe 2:
Fügen Sie der Tabelle warenhaus.Artikelkategorie ein Attribut mit der Bezeichnung „Beschreibung ein“
hinzu. Diese Attribut soll, wenn kein expliziter Wert übergeben wird, den Wert „Keine Beschreibung vorhanden“
enthalten.
*/
select * from warenhaus.Artikelkategorie
ALTER TABLE warenhaus.Artikelkategorie
MODIFY Beschreibung varchar2(50) DEFAULT ‚Keine Beschreibung vorhanden‘;
————————————–
UPDATE warenhaus.Artikelkategorie SET Beschreibung = ‚Keine Beschreibung vorhanden‘ ; — alle Zeile
/*
Aufgabe 3:
Ändern Sie das Attribut aus Aufgabe 2 so ab, dass dort kein Null eingetragen werden kann
*/
ALTER TABLE warenhaus.Artikelkategorie modify Beschreibung varchar2(100) CONSTRAINT Beschreibung_ein not null ;
————————————-
/*Aufgabe_1 — Ermitteln Sie die Durchschittspreise(prod_list_price)
der Produktkategorien (prod_category_id)
der Tabelle SH.Products.
Es sollen nur Kategorien deren Durchschnitt größer 121.68 ist. */
–SELECT * FROM SH.products;
SELECT
prod_category_id
,avg (prod_list_Price)
FROM SH.products
GROUP by prod_category_id
HAVING avg (prod_list_Price)> 121.68 ;
—– mit round
SELECT
prod_category_id
,round (avg (prod_list_Price),2)
FROM SH.products
GROUP by prod_category_id
HAVING avg (prod_list_Price)> 121.68 ;
——————————————————
/*-Aufgabe_2–Lassen Sie sich das teuerste Produkt
(SH.Products)(prod_list_price)
aus jeder Kategorie (prod_category_id) anzeigen. Sortieren Sie das
Ergebnis nach dem Preis absteigend*/
SELECT
prod_category_id
,Max (prod_list_Price) mp
FROM SH.products
GROUP by prod_category_id
ORDER BY mp desc ;
————————————————
/*Aufgabe 3:
Zählen Sie aus der Tabelle SH.Customer die Anzahl der weiblichen wie mänlichen Kunden(cust_gender)
die nach 1970(cust_year_of_birth) geboren wurden, verheiratet sind(cust_marital_status)
oder keine Angabe in cust_marital_status eingetragen haben.*/
–SELECT * FROM SH.Customers ;
Aufgabe 3:
SELECT
cust_gender
,count(cust_gender)
FROM SH.Customers
where cust_year_of_birth > ‚1970‘ and (cust_marital_status = ‚married‘ or cust_marital_status is null)
group by cust_gender ;
———————————————
/*Aufgabe 4:
Schreiben Sie eine Abfrage die ihnen das durchschnittliche Kreditlimit
SH.Customers(Cust_credit_limit) aller Regionen SH.Countries(Country_region)
und die Anzahl der Kunden in der Region anzeigt. Sie benötigen einen Join
zwischen den Tabellen SH.Customers und SH.Counries. Wer möchte darf (ist kein muss!!)
versuchen die Ausgabe des AVG auf 3 Stellen nach dem Komma zu begrenzen.
*/
–SELECT * FROM SH.Customers ;
–SELECT * FROM SH.Countries ;
Aufgabe 4
SELECT
b.Country_region
, avg (a.Cust_credit_limit)
,count (a.cust_id)
FROM SH.Customers a
inner join SH.Countries b on a.country_id=b.country_id
GROUP BY b.Country_region
–Aufgabe 4 mit round
SELECT
b.Country_region
, round (avg (a.Cust_credit_limit),3)
,count (a.cust_id)
FROM SH.Customers a
inner join SH.Countries b on a.country_id=b.country_id
GROUP BY b.Country_region
———————–
/*Aufgabe 4.38
Nennen Sie Personalnummer und Name des Mitarbeiters mit der kleinsten Personalnummer*/
select m_nr, m_name
from Mitarbeiter
where m_nr =
(
SELECT min(m_nr)
from Mitarbeiter
);
——————————
/*Aufgabe 1:
Aufgabe 1:
Schreiben Sie eine Abfrage die Ihnen aus der Tabelle SH.Customers
die Informationen cust_id, cust_first_name und cust_last_name in einer Spalte
das Geschlecht CUst_Gender und CUST_Marital_Status anzeigt. Für das Geschlecht soll
bei M mänlich und bei W weiblich für alles andere soll divers ausgegeben werden.
Beim Attribut CUST_Marital_Status sollen folgende Ausgaben erfolgen:
‚Widowed‘ -> ‚verwitwet‘
‚married‘ -> ‚verheiratet‘
‚divorced‘ -> ‚geschieden‘
‚Married‘ -> ‚verheiratet‘
’single‘ -> ‚ledig‘
Das Ergebis soll nach CUST_Marital_Status(Familienstatus) und den Geschlecht absteigend sortiert weden*/
select * FROM SH.Customers;
select
cust_id
,cust_first_name ||‘ ‚||cust_last_name as Fullname
,case CUst_Gender
when ‚M‘ then ‚mänlich‘
when ‚F‘ then ‚weiblich‘
ELSE ‚Keine Angabe‘
end as Gender
,case CUST_Marital_Status
when ‚married‘ then ‚Verheiratet‘
when ’single‘ then ‚ledig‘
when ‚divorced‘ then ‚geschieden‘
— when ‚Widowed‘ then ‚Widowed‘
else ‚Keine Angabe‘
end as Familienstatus
FROM SH.Customers
ORDER by Familienstatus desc , Gender desc
;
/*
Aufgabe 2
Sorgen Sie dafür das Sie mit der Abfrage aus Aufgabe 1 die nachfolgende Abfrage durchführen können.
Achten Sie darauf das in VIEW keine Order BY Klauseln benutzt werden dürfen:
Geben Sie die Anzahl der jeweiligen Geschlechter und Familistati aus.
Das Ergebnis soll nach Gender absteigend sortiert werden.*/
create or REPLACE view sh_customer_f as
select
cust_id
,cust_first_name ||‘ ‚||cust_last_name as Fullname
,case CUst_Gender
when ‚M‘ then ‚mänlich‘
when ‚F‘ then ‚weiblich‘
ELSE ‚divers‘
end as Gender
,case CUST_Marital_Status
when ‚married‘ then ‚Verheiratet‘
when ’single‘ then ‚ledig‘
when ‚divorced‘ then ‚geschieden‘
when ‚Widowed‘ then ‚Widowed‘
else ‚Keine Angabe‘
end as Familienstatus
FROM SH.Customers
;
select * from sh_customer_f
select
count(*)
,gender
,Familienstatus
FROM sh_customer_f
group by Familienstatus , Gender
ORDER by Gender desc
;
———————————————-
— Gruppierung mit Filterung im HAVING mit logischen Operatoren
SELECT department_id,ROUND (AVG(salary), 2) as avg
, SUM(salary) as sum
, MIN (salary) as min
, MAX (salary) AS max
, COUNT(*) as count
FROM HR.Employees
GROUP BY department_id
HAVING SUM(salary) > 10000 AND MIN (salary) > 7000
ORDER BY department_id, sum;
— 1. Schritt
SELECT MIN(SALARY)
FROM HR.EMPLOYEES;
— Jemand anderes ändert das Einkommen von TJ
UPDATE HR.EMPLOYEES SET SALARY = 2300 WHERE employee_id=132;
— 2. Schritt
SELECT *
FROM HR.EMPLOYEES
WHERE salary =2100;
— Alles in einer Abfrage(Besser!)
SELECT employee_id
, first_name
, last_name
, department_id
, salary
FROM HR.EMPLOYEES
WHERE salary IN ( SELECT MIN(SALARY)
FROM HR.EMPLOYEES
GROUP BY department_id
);
— Unterabfrage die nur einen Wert zurückliefert
SELECT employee_id
, first_name
, last_name
, department_id
, salary
FROM HR.EMPLOYEES
WHERE salary = ( SELECT MIN(SALARY)
FROM HR.EMPLOYEES
);
— Einfache Unterabfrage mit NOT IN
SELECT *
FROM HR.departments
WHERE department_id NOT IN (
SELECT department_id
FROM HR.employees
WHERE department_id IS NOT NULL
);
— Unterabfrage mit mehreren Spalten
SELECT employee_id
, first_name
, last_name
, department_id
, salary
FROM HR.EMPLOYEES
WHERE (salary, department_id)
IN ( SELECT MIN(SALARY), department_id
FROM HR.EMPLOYEES
GROUP BY department_id
);
SELECT *
FROM HR.departments
WHERE department_id NOT IN (200);
SELECT employee_id
, first_name
, last_name
, department_id
, salary
FROM HR.EMPLOYEES
WHERE salary IN ( SELECT MIN(SALARY)
FROM HR.EMPLOYEES
GROUP BY department_id
);
— Abfrage mit CASE
SELECT first_name
, last_name
, job_id
, salary
, CASE job_id
WHEN ‚IT_PROG‘ THEN salary*1.5
WHEN ‚FI_ACCOUNT‘ THEN salary*1.25
WHEN ‚AD_PRES‘ THEN salary-salary
ELSE salary+500
END AS NewSal
, department_id
FROM HR.EMPLOYEES;
— Anlegen einer VIEW
CREATE VIEW newSal
AS
SELECT first_name
, last_name
, job_id
, salary
, CASE job_id
WHEN ‚IT_PROG‘ THEN salary*1.5
WHEN ‚FI_ACCOUNT‘ THEN salary*1.25
WHEN ‚AD_PRES‘ THEN salary-salary
ELSE salary+500
END AS NewSal
, department_id
FROM HR.EMPLOYEES;
— Noch eine View
CREATE OR REPLACE VIEW v_MINMAXSalary
AS
SELECT
department_id AS Bueronummer
, MIN(salary) AS MINSAL
, max (salary) AS MAXSAL
FROM HR.EMPLOYEES
GROUP BY department_id;
— Abfragen einer VIEW
SELECT *
FROM Newsal
WHERE newsal > 15000;
SELECT * FROM v_MINMAXSalary;
— Abfragen einer View mit Join
SELECT *
FROM Newsal
INNER JOIN HR.Departments d ON d.department_id=newsal.department_id
INNER JOIN HR.locations l ON d.location_id=l.location_id
WHERE newsal > 15000;
— Noch eine View
CREATE OR REPLACE VIEW v_test3
AS
SELECT Last_name
, department_name
, country_id
FROM Newsal
INNER JOIN HR.Departments d ON d.department_id=newsal.department_id
INNER JOIN HR.locations l ON d.location_id=l.location_id
WHERE newsal > 15000;
— Eine View Löschen
DROP VIEW Newsal;
SELECT *
FROM v_test3;
— Abfrage aller benutzerdefinierten Views
SELECT * from user_views;
— Erstellen einer VIEW mit Referenz auf nicht vorhandem Objekt
— praktische Relevanz fraglich….
CREATE FORCE VIEW v_test4
AS
SELECT vorname
FROM v_test4444;
— Abfrage möglich
SELECT * FROM v_test4;
CREATE SEQUENCE test2
START WITH 0
INCREMENT BY 100;
—
SELECT *
FROM SH.CUSTOMERS
ORDER BY cust_id
OFFSET 1 ROWS FETCH NEXT 100 ROWS ONLY;
SELECT *
FROM SH.CUSTOMERS
ORDER BY cust_id
FETCH FIRST 1 PERCENT ROWS ONLY;
— Zeigen Sie die 2 Topgehälter an
SELECT *
FROM HR.EMPLOYEES
ORDER BY salary DESC NULLS LAST
FETCH FIRST 2 ROWS WITH TIES;
SELECT cust_id, cust_first_name, cust_credit_limit
FROM SH.CUSTOMERS
WHERE CUST_id BETWEEN 1 AND 1500
ORDER BY cust_credit_limit DESC
FETCH FIRST 18 ROWS WITH TIES;
— auswertende CASE
SELECT employee_id, first_name, last_name,job_id, salary
, CASE
WHEN job_id=’IT_PROG‘ AND salary<=5000 THEN salary*1.5
WHEN job_id=’IT_PROG‘ AND salary >5000 THEN salary*1.05
WHEN job_id=’FI_ACCOUNT‘ THEN salary *1.6
END AS newsal
FROM HR.EMPLOYEES;
SELECt * FROM hr.employees;
–create Table mit select from other one
CREATE TABLE ihk.Filmarchiv (
FILMID number(38) CONSTRAINT PK_archiv_id PRIMARY Key
,TITEL varchar2(100)
, ERSCHEINUNGSJAHR number
,SPIELDAUERMINUTEN number
);
—–oder
CREATE TABLE ihk.Filmarchiv ( FILMID
,TITEL
, ERSCHEINUNGSJAHR
,SPIELDAUERMINUTEN
)
as
SELECT
FILMID
,TITEL
, ERSCHEINUNGSJAHR
,SPIELDAUERMINUTEN
from IHK.FILM
where 1=2
;
—oder
CREATE TABLE ihk.Filmarchiv 0 ( select * from IHK.FILM
where 1=2 ) ;
ALTER TABLe ihk.Filmarchiv drop column Preis;
;
— insert daten satz von Table zu Table
INSERT INTO ihk.Filmarchiv (FILMID,TITEL,ERSCHEINUNGSJAHR,SPIELDAUERMINUTEN )
select FILMID,TITEL,ERSCHEINUNGSJAHR,SPIELDAUERMINUTEN from IHK.FILM
where ERSCHEINUNGSJAHR < 1950;
— Delete befehl löscht nur die Daten Satze
DELETE IHK.FILM — Table Name
where film.ERSCHEINUNGSJAHR < 1950 ;
— select mi count und inner join — right join (erset table die ich im Befehl schreibe , left ist die zweite table)
SELECT
p.personid
,p.name
,p.nachname
,count (filmid) as AnzahlFilme
FROM ihk.PERSON p
inner join ihk.PERSON_EIGENSCHAFT_FILM f on f.personid=p.personid
inner join ihk.eigenschaft e on e.eigenschaftid=f.eigenschaftid
where e.bezeichnung=’Schauspieler‘
group by p.personid,p.name ,p.nachname;
—
CREATE USER IHK2
IDENTIFIED BY Pa$$w0rd
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;
GRANT CONNECT TO IHK2;
GRANT CREATE TABLE TO IHK2;
CONNECT IHK2/Pa$$w0rd;
CREATE TABLE Person (PersId INT, Nachname VARCHAR(50), Vorname VARCHAR(50), Strasse VARCHAR(50), PLZ VARCHAR(50), Ort VARCHAR(50));
CREATE TABLE Zugang (RaumID INT, PerID INT, ZeitVon CHAR(5), ZeitBis CHAR(5));
CREATE TABLE Raum (RaumID INT, Typ VARCHAR(50), GebId INT, MerkID INT);
CREATE TABLE Gebaeude (GebId INT, Bezeichnung VARCHAR(50), Strasse VARCHAR(50), PLZ VARCHAR(50), Ort VARCHAR(50));
CREATE TABLE Merkmal (MerkID INT, Merkmal VARCHAR(50));
INSERT INTO Person VALUES (101, ‚Müller‘, ‚Max‘, ‚Müllerweg 1‘, ‚52335‘, ‚Köln‘);
INSERT INTO Person VALUES (202, ‚Meier‘, ‚Willi‘, ‚Testweg 12‘, ‚43333‘, ‚Dortmund‘);
INSERT INTO Person VALUES (404, ‚Wester‘, ‚Klaus‘, ‚Hauptstr. 13‘, ‚55667‘, ‚Köln‘);
INSERT INTO Zugang VALUES (1,101,’08:00′, ’10:00′);
INSERT INTO Zugang VALUES (1,202,’10:00′, ’14:00′);
INSERT INTO Zugang VALUES (2,101,’14:00′, ’18:00′);
INSERT INTO Zugang VALUES (5,202,’08:00′, ’18:00′);
INSERT INTO Raum VALUES(1, ‚Besprechungsraum‘, 2,1);
INSERT INTO Raum VALUES(2, ‚Labor‘, 2, 2);
INSERT INTO Raum VALUES(3, ‚Labor‘, 1, 2);
INSERT INTO Raum VALUES(4, ‚Labor‘, 1, 2);
INSERT INTO Raum VALUES(5, ‚Besprechungsraum‘, 1, 1 );
INSERT INTO Raum VALUES(6, ‚Labor‘, 3,2);
INSERT INTO Raum VALUES(7, ‚Labor‘, 3,2);
INSERT INTO Raum VALUES(8, ‚Labor‘,3,2);
INSERT INTO Raum VALUES(8, ‚Labor‘,3,NULL);
INSERT INTO Gebaeude VALUES(1, ‚Forschung H‘,’Heinrich-Hertz-Str. 12′, ‚50501‘, ‚Köln‘);
INSERT INTO Gebaeude VALUES(2, ‚Forschung U‘, ‚Heinrich-Hertz-Str. 12‘, ‚50501‘,’Köln‘);
INSERT INTO Gebaeude VALUES(3, ‚Forschung I‘, ‚Heinrich-Hertz-Str. 12‘, ‚50501‘,’Köln‘);
INSERT INTO Gebaeude VALUES(4, ‚Verwaltung‘, ‚Heinrich-Hertz-Str. 12‘, ‚50555‘,’Köln‘);
INSERT INTO Merkmal VALUES (1, ‚Fingerabdruck‘);
INSERT INTO Merkmal VALUES (2, ‚Iris‘);
————————————————
SELECT * FROM ihk2.Gebaeude;
SELECT * FROM ihk2.Raum;
–Aufgabe A
SELECT
*
FROM ihk2.Raum a
right join ihk2.Gebaeude b on a.gebid=b.gebid
ORDER by a.gebid
— besser Ales Muster Lösung
SELECT
a.* , b.*
FROM ihk2.Raum a
right join ihk2.Gebaeude b on a.gebid=b.gebid
ORDER by b.Bezeichnung asc , a.typ asc
—————————
SELECT * FROM ihk2.Zugang;
SELECT * FROM ihk2.Person;
–Aufgabe b
SELECT *
FROM ihk2.Person a
left join ihk2.Zugang b on a.persid=b.perid
********************************************
SELECT * FROM ihk2.merkmal;
SELECT * FROM ihk2.raum;
–Aufgabe c
SELECT
count(a.raumid) Anzahlraeume , b.merkmal
FROM ihk2.raum a
right join ihk2.merkmal b on a.Merkid=b.Merkid
group by a.Merkid,b.merkmal;
————————–
SELECT * FROM ihk2.Zugang;
SELECT * FROM ihk2.Person;
–Aufgabe d
SELECT b.nachname , b.vorname , a.raumid ,a.zeitvon , a.zeitbis
FROM ihk2.Zugang a
inner join ihk2.Person b on a.perid=b.persid
where nachname=’Müller‘ and vorname=’Max‘
;
——
SELECT * FROM ihk.Person;
–Aufgabe e
SELECT * FROM ihk2.Person
where plz >=50000 and plz <=59999
;
/*Für den Mitarbeiter Jens Müllerwurde inderTabelle Fehlzeit einDatensatz falsch erfasst:
Statt einer zweitägigen ,Dienstreise‘ vom17.bis18.1.2017 wurde versehentlich eineintägiger „Urlaub“ fürden
I7.t’1.201 7eingetragen (siehe Tabelle Fehkeii.
Er$ellen SieeineSQl-Anweisung, mitderdieKonehur durchgeführt werden kann*/
connect IHK/Pa$$w0rd;
SELECT * From ihk.fehlzeit;
UPDATE ihk.fehlzeit SET fehltage=2
, grund=’Dienstreise‘
, bis_datum=’18.11.2017′
WHERE fz_id=4;
——
drop user ihk3 cascade;
CREATE USER IHK3
IDENTIFIED BY Pa$$w0rd
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;
GRANT CONNECT TO IHK3;
GRANT CREATE TABLE TO IHK3;
CONNECT IHK3/Pa$$w0rd;
CREATE TABLE Mitarbeiter (MA_Id NUMBER
, nachname VARCHAR2(25)
, Vorname varchar2(50)
, Geburtstag DATE
, Tagesarbeitszeit NUMBER (2)
);
CREATE TABLE Fehlzeit (FZ_ID NUMBER
, MA_ID NUMBER
, von_Datum DATE
, bis_Datum DATE
, Grund VARCHAR2(20)
, Fehltage NUMBER(3)
);
INSERT INTO mitarbeiter VALUES (811, ‚Müller‘, ‚Jens‘, ‚14.04.1982‘, 8);
INSERT INTO mitarbeiter VALUES (812, ‚Scholz‘, ‚Birgit‘, ‚23.08.1964‘, 4);
INSERT INTO mitarbeiter VALUES (815, ‚Schmidt‘, ‚Ulrich‘, ‚02.11.1957‘, 8);
INSERT INTO mitarbeiter VALUES (817, ‚Storck‘, ‚Hans‘, ‚14.11.1990‘,6);
INSERT INTO mitarbeiter VALUES (841, ‚Ullmann‘, ‚Franz‘, ‚21.12.1959‘, 8);
INSERT INTO mitarbeiter VALUES (902, ‚Sorge‘, ‚Susanne‘, ‚02.03.1952‘,8);
INSERT INTO Fehlzeit VALUES (1, 811, ‚18.04.2017‘, ‚23.04.2017‘, ‚Urlaub‘, 4);
INSERT INTO Fehlzeit VALUES (2, 902, ‚18.04.2017‘, ‚08.05.2017‘, ‚Urlaub‘, 14);
INSERT INTO Fehlzeit VALUES (3, 811, ‚19.06.2017‘, ‚20.06.2017‘, ‚Urlaub‘, 2);
INSERT INTO Fehlzeit VALUES (4, 811, ‚17.11.2017‘, ‚17.11.2017‘, ‚Urlaub‘, 1);
INSERT INTO Fehlzeit VALUES (5, 904, ‚31.12.2017‘, ‚31.12.2017‘, ‚Urlaub‘, 1);
INSERT INTO Fehlzeit VALUES (6, 904, ‚01.01.2018‘, ‚09.01.2018‘, ‚Urlaub‘, 6);
——————————————————-
SELECT * From ihk3.fehlzeit;
SELECT * From ihk3.Mitarbeiter;
UPDATE ihk3.fehlzeit SET fehltage=2
, grund=’Dienstreise‘
, bis_datum=’18.11.2017′
WHERE fz_id=4;
SELECT
m.ma_id, sum(f.fehltage),vorname , nachname
FROM ihk3.Mitarbeiter m
RIGHT JOIN IHK3.fehlzeit f on m.ma_id=f.ma_id
GROUP by m.ma_id,vorname , nachname
——-
/*Aufgabe 1:
Aufgabe 1:
Schreiben Sie eine Abfrage die Ihnen aus der Tabelle SH.Customers
die Informationen cust_id, cust_first_name und cust_last_name in einer Spalte
das Geschlecht CUst_Gender und CUST_Marital_Status anzeigt. Für das Geschlecht soll
bei M mänlich und bei W weiblich für alles andere soll divers ausgegeben werden.
Beim Attribut CUST_Marital_Status sollen folgende Ausgaben erfolgen:
‚Widowed‘ -> ‚verwitwet‘
‚married‘ -> ‚verheiratet‘
‚divorced‘ -> ‚geschieden‘
‚Married‘ -> ‚verheiratet‘
’single‘ -> ‚ledig‘
Das Ergebis soll nach CUST_Marital_Status(Familienstatus) und den Geschlecht absteigend sortiert weden*/
select * FROM SH.Customers;
select
cust_id
,cust_first_name ||‘ ‚||cust_last_name as Fullname
,case CUst_Gender
when ‚M‘ then ‚mänlich‘
when ‚F‘ then ‚weiblich‘
ELSE ‚Keine Angabe‘
end as Gender
,case CUST_Marital_Status
when ‚married‘ then ‚Verheiratet‘
when ’single‘ then ‚ledig‘
when ‚divorced‘ then ‚geschieden‘
— when ‚Widowed‘ then ‚Widowed‘
else ‚Keine Angabe‘
end as Familienstatus
FROM SH.Customers
ORDER by Familienstatus desc , Gender desc
;
/*
Aufgabe 2
Sorgen Sie dafür das Sie mit der Abfrage aus Aufgabe 1 die nachfolgende Abfrage durchführen können.
Achten Sie darauf das in VIEW keine Order BY Klauseln benutzt werden dürfen:
Geben Sie die Anzahl der jeweiligen Geschlechter und Familistati aus.
Das Ergebnis soll nach Gender absteigend sortiert werden.*/
create or REPLACE view sh_customer_f as
select
cust_id
,cust_first_name ||‘ ‚||cust_last_name as Fullname
,case CUst_Gender
when ‚M‘ then ‚mänlich‘
when ‚F‘ then ‚weiblich‘
ELSE ‚divers‘
end as Gender
,case CUST_Marital_Status
when ‚married‘ then ‚Verheiratet‘
when ’single‘ then ‚ledig‘
when ‚divorced‘ then ‚geschieden‘
when ‚Widowed‘ then ‚Widowed‘
else ‚Keine Angabe‘
end as Familienstatus
FROM SH.Customers
;
————————-
select * from sh_customer_f
select
count(*)
,gender
,Familienstatus
FROM sh_customer_f
group by Familienstatus , Gender
ORDER by Gender desc
;
—
SELECT
count(*) as COUNT
,MAX (salary)
,MIN(salary)
,avg (salary)
,sum(salary)
FROM HR.employees
SELECT
department_id
,count(*)
FROM HR.employees
Group by department_id;
SELECT COUNT(*) AS COUNT
, COUNT (commission_pct) As CountAttribut
, MAX (salary) AS MaxWert
, MIN (salary) AS MINWERT
, AVG (salary) AS DURCHSCHNITT
, SUM (salary) AS Summe
FROM HR.EMPLOYEES;
SELECT
department_id
,count(*)
FROM HR.employees
Group by department_id;
/*Schreiben Sie eine Abfrage die Ihnen alle Departments anzeigt
wo das Gesamteinkommen innerhalb des Departments über 10000 liegt*/
SELECT * FROM HR.employees
SELECT * FROM HR.departments
SELECT
department_id
,sum(salary)
FROM HR.employees
having sum(salary)>10000
Group by department_id;
——————————————–
/*Schreiben Sie eine Abfrage die Ihnen alle Departments anzeigt
wo das Gesamteinkommen innerhalb des Departments über 10000 liegt mit department_name */
SELECT
a.department_id
,sum(a.salary)
,b.department_name
FROM HR.employees a
join HR.departments b on a.department_id = b.department_id
having sum(a.salary)>10000
Group by a.department_id ,b.department_name
order by sum(a.salary);
——————————-
/*Aufgabe 4.37
Nennen Sie die kleinste Personalnummer der Mitarbeiter.*/
select * from PROJEKT_DB.mitarbeiter;
SELECT
min (M_nr)
FROM PROJEKT_DB.mitarbeiter ;
/*Aufgabe 4.38
Nennen Sie Personalnummer und Name des Mitarbeiters mit der kleinsten Personalnummer*/
select m_nr, m_name
from Mitarbeiter
where m_nr =
(
SELECT min(m_nr)
from Mitarbeiter
);
———————–
/*Aufgabe 4.40
Berechnen Sie die Summe der finanziellen Mittel aller Projekte.*/
–select* from PROJEKT_DB.projekt;
select sum (mittel) from PROJEKT_DB.projekt;
/*Aufgabe 4.43
Finden Sie heraus, wieviele Mitarbeiter in jedem Projekt arbeiten.
*/
–select* from PROJEKT_DB.arbeiten;
select pr_nr
,COUNT(m_nr)
from PROJEKT_DB.arbeiten
group by pr_nr
— Aufgabe 4.42 — Besprechen!!
— Finden Sie heraus, wie viele verschiedene Aufgaben in jedem Projekt ausgeuebt werden.
— Nullwerte sollen nicht angezeigt werden!
select pr_nr, count(aufgabe) AS anzahl
from arbeiten
group by pr_nr;
select pr_nr, count(DISTINCT aufgabe) AS anzahl
from arbeiten
group by pr_nr;
SELECT * FROM arbeiten
WHERE pr_nr=’p2′;
–VErschachtelte Gruppenfunktion
SELECT MIN(AVG(salary)) —-Minimal Durchschnitt der Einkommen
FROM HR.Employees
GROUP BY department_id;
———————-
— Abfrage mit Aggregatsfunktionen
SELECT COUNT(*) AS COUNT — Z?hlt alle Zeilen des Ergebnisses
, COUNT (commission_pct) As CountAttribut — Z?hlt nur Zeilen mit einem vorhandenen Wert
, MAX (salary) AS MaxWert — Gibt den minimalen Wert des Attributes zur?ck
, MIN (salary) AS MINWERT — Gibt den maximalen Wert des Attributes zur?ck
, ROUND(AVG (salary),2) AS DURCHSCHNITT — Gibt den Durchschnitt zur?ck
, SUM (salary) AS Summe — Gibt die Summe zur?ck
FROM HR.EMPLOYEES;
— Beispiel mit GROUP BY
SELECT manager_id, MIN(salary) AS minsalary
FROM HR.employees
GROUP BY manager_id
ORDER By minsalary;
DESC HR.Employees;
SELECT First_name, last_name, MIN(salary)
FROM HR.EMPLOYEES
GROUP BY First_name, last_name;
INSERT INTO HR.Employees (Employee_id, first_name, last_name, email, hire_date, Job_id, salary )
VALUES (600, ‚Britney‘, ‚Everett‘, ‚bal‘, SYSDATE, ‚IT_PROG‘, 1900 );
WHERE salary = ( SELECT MIN(salary) FROM HR.EMPLOYEES)
WHERE commission_pct IS NOT NULL;
SELECT department_id
, job_id
, manager_id
, salary
, COUNT(*)
FROM HR.EMPLOYEES
GROUP BY department_id, job_id, manager_id, salary
HAVING COUNT(*) > 1
ORDER BY department_id;
SELECT department_id
, job_id
, manager_id
, salary
, COUNT(manager_id)
FROM HR.EMPLOYEES
GROUP BY department_id, job_id, manager_id, salary
HAVING COUNT(manager_id) > 1
ORDER BY department_id;
SELECT first_name
, COUNT(manager_id)
FROM HR.EMPLOYEES
WHERE first_name <> ‚Alexander‘
GROUP BY first_name
HAVING COUNT(manager_id) > 1
ORDER BY first_name;
SELECT SUM (salary + (SALARY * COALESCE(commission_pct, 0)))
, AVG(salary)
, MAX (salary)
, COUNT(*)
FROM HR.EMPLOYEES
WHERE department_id=30;
SELECT e.department_id
, SUM (Salary)
FROM HR.employees e
GROUP By e.department_id
HAVING SUM(Salary) > 10000;
SELECT e.department_id
, d.department_name
, SUM (Salary)
FROM HR.employees e
INNER JOIN HR.departments d on e.department_id=d.DEPARTMENT_ID
GROUP By e.department_id, d.department_name
HAVING SUM(Salary) > 10000;
SELECt department_id, SUM (SALARY)
FROM HR.Employees
GROUP BY department_id
HAVING SUM(salary) > 10000;
SELECT * FROM HR.EMPLOYEES WHERE department_id=50 AND job_id=’SH_CLERK‘ AND manager_id=124;
SELECT *
FROM HR.EMPLOYEES
WHERE salary = (SELECT MAX(salary) FROM HR.EMPLOYEES);
— Aufgabe 4.34
SELECT aufgabe
FROM arbeiten
GROUP BY aufgabe;
— Oder
SELECT DISTINCT Aufgabe
from arbeiten;
— Aufgabe 4.37
select min(m_nr)
from Mitarbeiter;
— Aufgabe 4.38
select m_nr, m_name
from Mitarbeiter
where m_nr =
(
SELECT min(m_nr)
from Mitarbeiter
);
— Aufgabe 4.40
select sum(mittel)
from Projekt;
— Aufgabe 4.43
select pr_nr, count(m_nr)
from arbeiten
group by pr_nr;
— Aufgabe 4.42 — Besprechen!!
— Finden Sie heraus, wie viele verschiedene Aufgaben in jedem Projekt ausgeuebt werden.
— Nullwerte sollen nicht angezeigt werden!
select pr_nr, count(aufgabe) AS anzahl
from arbeiten
group by pr_nr;
select pr_nr, count(DISTINCT aufgabe) AS anzahl
from arbeiten
group by pr_nr;
SELECT * FROM arbeiten
WHERE pr_nr=’p2′;
—
INSERT INTO warenhaus.lager (lagerid, parentlager, strasse, hausnr, plz, ort, Bundesland, Name)
VALUES (5,3, ‚Gustafweg‘, 6, 11255, ‚Ortname‘, ‚HH‘, ‚Lokallager Hamburg II‘);
UPDATE warenhaus.lager set parentlager = 3 WHERE lagerid=4;
———————-
DROP TABLE uebungSelfJoin;
CREATE TABLE uebungSelfJoin (
KatID NUMBER GENERATED ALWAYS AS IDENTITY START WITH 100 INCREMENT BY 2 PRIMARY KEY
,Kat_Name VARCHAR2(50)
, ueber_Kat_ID NUMBER CONSTRAINT FK REFERENCES uebungSelfJoin (KATID));
INSERt INTO uebungSelfJoin (Kat_Name, ueber_Kat_ID) VALUES ( ‚Spielwaren‘, NULL);
INSERt INTO uebungSelfJoin (Kat_Name, ueber_Kat_ID) VALUES (‚Elektronik‘, NULL);
INSERt INTO uebungSelfJoin (Kat_Name, ueber_Kat_ID) VALUES (‚Bekleidung‘, NULL);
INSERt INTO uebungSelfJoin(Kat_Name, ueber_Kat_ID) VALUES (‚Sportartikel‘, NULL);
INSERt INTO uebungSelfJoin(Kat_Name, ueber_Kat_ID) VALUES (‚Holzspielzeug‘, 100);
INSERt INTO uebungSelfJoin(Kat_Name, ueber_Kat_ID) VALUES (‚Geselschaftsspiele‘, 100);
INSERt INTO uebungSelfJoin(Kat_Name, ueber_Kat_ID) VALUES (‚Modellbau‘, 100);
INSERt INTO uebungSelfJoin(Kat_Name, ueber_Kat_ID) VALUES (‚Fernseher und Heimkino‘, 102);
INSERt INTO uebungSelfJoin(Kat_Name, ueber_Kat_ID) VALUES (‚DVD-Player‘, 114);
INSERt INTO uebungSelfJoin(Kat_Name, ueber_Kat_ID) VALUES (‚Blue-Ray-Player‘, 114);
INSERt INTO uebungSelfJoin(Kat_Name, ueber_Kat_ID) VALUES (‚LCD-Fernseher‘, 114);
INSERt INTO uebungSelfJoin(Kat_Name, ueber_Kat_ID) VALUES (‚LCD-Fernseher bis 40″‚, 120);
INSERt INTO uebungSelfJoin(Kat_Name, ueber_Kat_ID) VALUES (‚LCD-Fernseher ab 40″ bis 50″‚, 120);
INSERt INTO uebungSelfJoin(Kat_Name, ueber_Kat_ID) VALUES (‚LCD-Fernseher über 60″‚, 120);
SELECT
a.katid,a.kat_name,a.ueber_kat_id,b.kat_name
FROM uebungSelfJoin a
inner join uebungSelfJoin b on a.ueber_kat_id=b.katid;
————————————————
DROP USER projekt_db CASCADE;
CREATE USER projekt_db
IDENTIFIED BY Pa$$w0rd
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;
GRANT CONNECT TO projekt_db;
GRANT CREATE TABLE TO projekt_db;
connect projekt_db/Pa$$w0rd
— Anlegen der Projekt-Datenbank
— PK – und FL-Vereinbarungen erfolgen, da die Anzahl an Tabellen überschaubar ist, innerhalb des
— CREATE-TABLE-Statements
CREATE TABLE Abteilung (
abt_nr char(4) NOT NULL,
abt_name char(20) NOT NULL,
stadt char(15),
CONSTRAINT pk_abteilung PRIMARY KEY (abt_nr)
);
CREATE TABLE Projekt (
pr_nr char(4) NOT NULL,
pr_name char(25) NOT NULL,
mittel float,
CONSTRAINT pk_projekt PRIMARY KEY (pr_nr)
);
CREATE TABLE Mitarbeiter (
m_nr integer NOT NULL,
m_name char(20) NOT NULL,
m_vorname char(20) NOT NULL,
abt_nr char(4),
CONSTRAINT pk_mitarbeiter PRIMARY KEY (m_nr)
);
CREATE TABLE Arbeiten (
m_nr integer NOT NULL,
pr_nr char(4) NOT NULL,
aufgabe char(15),
einst_dat date,
CONSTRAINT pk_arbeiten PRIMARY KEY(m_nr, pr_nr)
);
/* Daten der Tabelle Projekt */
INSERT INTO Projekt VALUES (‚p1‘, ‚Apollo‘, 120000.0);
INSERT INTO Projekt VALUES (‚p2‘, ‚Gemini‘, 95000.0);
INSERT INTO Projekt VALUES (‚p3‘, ‚Merkur‘, 186500.0);
/* Daten der Tabelle Abteilung */
INSERT INTO Abteilung VALUES (‚a1′,’Beratung‘,’München‘);
INSERT INTO Abteilung VALUES (‚a2′,’Diagnose‘,’München‘);
INSERT INTO Abteilung VALUES (‚a3′,’Freigabe‘,’Stuttgart‘);
/* Daten der Tabelle Mitarbeiter */
INSERT INTO Mitarbeiter VALUES(25348,’Keller‘,’Hans‘,’a3′);
INSERT INTO Mitarbeiter VALUES(10102,’Huber‘,’Petra‘,’a3′);
INSERT INTO Mitarbeiter VALUES(18316,’Müller‘,’Gabriele‘,’a1′);
INSERT INTO Mitarbeiter VALUES(29346,’Probst‘,’Andreas‘,’a2′);
INSERT INTO Mitarbeiter VALUES(9031,’Meier‘,’Rainer‘,’a2′);
INSERT INTO Mitarbeiter VALUES(2581,’Kaufmann‘,’Brigitte‘,’a2′);
INSERT INTO Mitarbeiter VALUES(28559,’Mozer‘,’Sibille‘,’a1′);
/* Daten der Tabelle Arbeiten */
INSERT INTO Arbeiten VALUES (10102, ‚p1‘,
‚Projektleiter‘, TO_DATE(‚2018-10-01‘, ‚YYYY-MM-DD‘));
INSERT INTO Arbeiten VALUES (10102, ‚p3‘,
‚Gruppenleiter‘, TO_DATE(‚2019-01-01‘, ‚YYYY-MM-DD‘));
INSERT INTO Arbeiten VALUES (25348, ‚p2‘,
‚Sachbearbeiter‘, TO_DATE(‚2018-02-15‘, ‚YYYY-MM-DD‘));
INSERT INTO Arbeiten VALUES (18316, ‚p2‘,
null, TO_DATE(‚2019-06-01‘, ‚YYYY-MM-DD‘));
INSERT INTO Arbeiten VALUES (29346, ‚p2‘,
null, TO_DATE(‚2017-12-15‘, ‚YYYY-MM-DD‘));
INSERT INTO Arbeiten VALUES (2581, ‚p3‘,
‚Projektleiter‘, TO_DATE(‚2019-10-15‘, ‚YYYY-MM-DD‘));
INSERT INTO Arbeiten VALUES (9031, ‚p1‘,
‚Gruppenleiter‘, TO_DATE(‚2019-04-15‘, ‚YYYY-MM-DD‘));
INSERT INTO Arbeiten VALUES (28559, ‚p1‘,
null, TO_DATE(‚2018-04-15‘, ‚YYYY-MM-DD‘));
INSERT INTO Arbeiten VALUES (28559, ‚p2‘,
‚Sachbearbeiter‘, TO_DATE(‚2018-02-01‘, ‚YYYY-MM-DD‘));
INSERT INTO Arbeiten VALUES (9031, ‚p3‘,
‚Sachbearbeiter‘, TO_DATE(‚2018-11-15‘, ‚YYYY-MM-DD‘));
INSERT INTO Arbeiten VALUES (29346, ‚p1‘,
‚Sachbearbeiter‘, TO_DATE(‚2019-04-01‘, ‚YYYY-MM-DD‘));
/*Anlegen der Fremdschlüsselbeziehungen*/
ALTER TABLE Mitarbeiter
ADD CONSTRAINT fk_mitarbeiter_abteilung FOREIGN KEY(abt_nr)
REFERENCES abteilung(abt_nr);
ALTER TABLE Arbeiten ADD CONSTRAINT fk_arbeiten_mitarbeiter FOREIGN KEY (m_nr)
REFERENCES mitarbeiter(m_nr);
ALTER TABLE Arbeiten ADD CONSTRAINT fk_arbeiten_projekt FOREIGN KEY (pr_nr)
REFERENCES projekt(pr_nr);
/*
Ab hier sind die Tabellen optional
CREATE TABLE Gehalt(
gehalt_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
gehalt DECIMAL(9,2) NOT NULL,
m_nr int UNIQUE NOT NULL,
CONSTRAINT fk_gehalt_mitarbeiter_m_nr FOREIGN KEY (m_nr)
REFERENCES Mitarbeiter(m_nr) ON DELETE CASCADE
);
INSERT INTO Gehalt (m_nr, gehalt) VALUES (2581, 3000);
INSERT INTO Gehalt (m_nr, gehalt) VALUES (9031, 4000);
INSERT INTO Gehalt (m_nr, gehalt) VALUES (10102, 3500);
INSERT INTO Gehalt (m_nr, gehalt) VALUES (18316, 3000);
INSERT INTO Gehalt (m_nr, gehalt) VALUES (25348, 1000);
INSERT INTO Gehalt (m_nr, gehalt) VALUES (28559, 6000);
INSERT INTO Gehalt (m_nr, gehalt) VALUES (29346, 5000);
— Die Tabelle Miterweiter entspricht der Tabelle Mitarbeiter, ergänzt jedoch einen Wohnort
— Diese Tabelle wird nicht in das ERM integriert (Grund Redundanz)!
— Die Tabelle dient Demonstrationszwecken (Outerjoin, Selfjoin u.a.)
CREATE TABLE Mit_erweiter (
m_nr NUMBER NOT NULL,
m_name char(20) NOT NULL,
m_vorname char(20) NOT NULL,
abt_nr char(4),
wohnort char(25)
)
— Einfügen der Daten aus der Mitarbeiter-Tabelle
INSERT INTO Mit_erweiter VALUES(25348,’Keller‘,’Hans‘,’a3′,NULL)
INSERT INTO Mit_erweiter VALUES(10102,’Huber‘,’Petra‘,’a3′,NULL)
INSERT INTO Mit_erweiter VALUES(18316,’Müller‘,’Gabriele‘,’a1′,NULL)
INSERT INTO Mit_erweiter VALUES(29346,’Probst‘,’Andreas‘,’a2′,NULL)
INSERT INTO Mit_erweiter VALUES(9031,’Meier‘,’Rainer‘,’a2′,NULL)
INSERT INTO Mit_erweiter VALUES(2581,’Kaufmann‘,’Brigitte‘,’a2′,NULL)
INSERT INTO Mit_erweiter VALUES(28559,’Mozer‘,’Sibille‘,’a1′,NULL)
–Einfügen von Daten in die Tabelle Mit_erweiter in der Spalte wohnort
UPDATE Mit_erweiter
SET wohnort = ‚München‘
WHERE m_nr in (25348, 2581)
UPDATE Mit_erweiter
SET wohnort = ‚Augsburg‘
WHERE m_nr in (29346, 9031)
UPDATE Mit_erweiter
SET wohnort = ‚Landshut‘
WHERE m_nr = 10102
UPDATE Mit_erweiter
SET wohnort = ‚Rosenheim‘
WHERE m_nr = 18316
UPDATE Mit_erweiter
SET wohnort = ‚Ulm‘
WHERE m_nr = 28559
CREATE TABLE Umsatz (
umsatz_nr NUMBER NOT NULL,
m_nr NUMBER NOT NULL,
datum date NOT NULL,
umsatz float NOT NULL,
CONSTRAINT pk_umsatz PRIMARY KEY (umsatz_nr)
)
— FK
ALTER TABLE Umsatz
ADD CONSTRAINT fk_umsatz_mitarbeiter FOREIGN KEY (m_nr)
REFERENCES Mitarbeiter (m_nr)
INSERT INTO Umsatz VALUES(1,10102,’01.10.2018′, 500)
INSERT INTO Umsatz VALUES(2,10102,’02.10.2018′, 500)
INSERT INTO Umsatz VALUES(3,10102,’01.11.2018′, 500)
INSERT INTO Umsatz VALUES(4,10102,’01.11.2018′, 5000)
INSERT INTO Umsatz VALUES(5,10102,’02.11.2018′, 500)
INSERT INTO Umsatz VALUES(6,10102,’09.12.2018′, 500)
INSERT INTO Umsatz VALUES(7,10102,’10.12.2018′, 500)
INSERT INTO Umsatz VALUES(8,10102,’23.12.2018′, 5000)
INSERT INTO Umsatz VALUES(9,10102,’28.12.2018′, 500)
INSERT INTO Umsatz VALUES(10,10102,’01.01.2019′, 5001)
INSERT INTO Umsatz VALUES(11,25348,’15.02.2018′, 1500)
INSERT INTO Umsatz VALUES(12,25348,’16.02.2018′, 1500)
INSERT INTO Umsatz VALUES(13,25348,’17.02.2018′, 1500)
INSERT INTO Umsatz VALUES(14,25348,’01.05.2018′, 1500)
INSERT INTO Umsatz VALUES(15,25348,’02.05.2018′, 15000)
INSERT INTO Umsatz VALUES(16,25348,’11.10.2018′, 15000)
INSERT INTO Umsatz VALUES(17,25348,’01.02.2019′, 150000)
INSERT INTO Umsatz VALUES(18,25348,’01.03.2019′, 1500)
INSERT INTO Umsatz VALUES(19,25348,’01.04.2019′, 15)
INSERT INTO Umsatz VALUES(20,25348,’01.05.2019′, 150)
INSERT INTO Umsatz VALUES(21,2581,’01.05.2019′, 100000)
*/
—
/*
Ändern Sie die Tabelle Artikel ab un fügen Sie derm
Attribut ArtikeNr eine Identity hinzu
*/
commit;
ROLLBACK;
SELECT * FROM warenhaus.artikel;
ALTER TABLE warenhaus.artikel ADD (Artike_Nr NUMBER (10) GENERATED ALWAYS as IDENTITY );
——————————————————–
ALTER TABLE warenhaus.artikel ADD id number
ALTER TABLE warenhaus.artikel DROP COLUMN Artike_Nr;
ALTER TABLE warenhaus.artikel DROP COLUMN id;
ALTER TABLE warenhaus.artikel MODIFY id NUMBER (15) GENERATED ALWAYS AS IDENTITY;
/*
Aufgabe 1:
Schreiben Sie eine Abfrage die Ihnen alle Mitarbeiter (Tabelle HR.Employees)
anzeigt die mit einem A anfangen gefolgt von einen Buchstaben und als letzten
Buchstaben ein r haben(last_name).
*/
SELECT * FROM hr.employees ;
SELECT last_name
FROM hr.employees where last_name LIKE (‚A%_e‘) ;
/*
Aufgabe 2:
Fügen Sie der Tabelle warenhaus.Artikelkategorie ein Attribut mit der Bezeichnung „Beschreibung ein“
hinzu. Diese Attribut soll, wenn kein expliziter Wert übergeben wird, den Wert „Keine Beschreibung vorhanden“
enthalten.
*/
select * from warenhaus.Artikelkategorie
ALTER TABLE warenhaus.Artikelkategorie
MODIFY Beschreibung varchar2(50) DEFAULT ‚Keine Beschreibung vorhanden‘;
————————————–
UPDATE warenhaus.Artikelkategorie SET Beschreibung = ‚Keine Beschreibung vorhanden‘ ; — alle Zeile
——————————————–
INSERT INTO warenhaus.Artikelkategorie VALUES (5,’Egal‘,“ )
INSERT INTO warenhaus.Artikelkategorie VALUES (6,’Egalv2′,“ )
DELETE FROM warenhaus.Artikelkategorie WHERE ID=5
/*
Aufgabe 3:
Ändern Sie das Attribut aus Aufgabe 2 so ab, dass dort kein Null eingetragen werden kann
*/
ALTER TABLE warenhaus.Artikelkategorie modify Beschreibung varchar2(100) CONSTRAINT Beschreibung_ein not null ;
/*
Aufgabe 4:
Fragen Sie aus der Tabelle SH.Products alle eindeutigen Kategorien(prod_Category) und
Unterkategorien (prod_sub_category) inklusive der Beschreibungen
ab.
*/
SELECT * FROM SH.Products;
SELECT * FROM warenhaus.Artikelkategorie
SELECT prod_category, prod_category_desc
FROM SH.Products
UNION
SELECT prod_subcategory, prod_subcategory_desc
FROM SH.Products;
————————————–
SELECT DISTINCT
prod_Category , prod_subcategory_desc
FROM SH.Products;
/*
Aufgabe 5:
Fügen Sie die Informationen aus Aufgabe 4 in die Tabelle warenhaus.Artikelkategorie ein.
*/
INSERT INTO warenhaus.Artikelkategorie (id , Name , beschreibung )
SELECT DISTINCT prod_category_id, prod_category , sh.products.prod_category_desc
FROM SH.Products ;
—————————————————————————–
CREATE SEQUENCE PK_warenhaus_Artikelkategorie
START WITH 20
INCREMENT BY 1;
ALTER TABLE warenhaus.Artikelkategorie MODIFY id DEFAULT PK_warenhaus_Artikelkategorie.nextval;
INSERT INTO warenhaus.Artikelkategorie (name, beschreibung)
SELECT prod_category, prod_category_desc
FROM SH.Products
UNION
SELECT prod_subcategory, prod_subcategory_desc
FROM SH.Products
————————————————————————
INSERT INTO warenhaus.Artikelkategorie (id , Name , beschreibung )
SELECT DISTINCT prod_id, prod_category , sh.products.prod_category_desc
FROM SH.Products ;
————————————
rollback;
alter table warenhaus.Artikelkategorie modify id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY:
DESCRIBE SH.Products;
DESCRIBE warenhaus.Artikelkategorie;
delete from warenhaus.Artikelkategorie
where id >4
/*
Aufgabe 6:
Fügen Sie alle Artikel aus der Tabelle SH.Products in die Tabelle warenhaus.artikel ein. Achten Sie
darauf das die Artikel der korrekten Artikelkategorie zugeordnet werden.
*/
SELECT * FROM warenhaus.artikel;
SELECT * FROM WARENHAUS.artikelkategorie;
SELECT * FROM SH.Products;
INSERT INTO warenhaus.artikel (name, preis, artikelnr, ARTIKELKATEGORIE)
SELECT prod_name, prod_list_price, prod_id, id
FROM WARENHAUS.artikelkategorie wa
JOIN SH.Products sp ON wa.name=sp.prod_category;
—————————————–
INSERT INTO warenhaus.artikel (artikelnr , Name, artikelkategorie ,preis )
SELECT DISTINCT prod_ID , prod_NAME , prod_category_id ,prod_list_price
FROM SH.Products
/*
Aufgabe 7:
Erhöhen Sie die Preise aller Artikel aus der Artikelkategorie „Peripherals and Accessories“ um 25%
*/
SELECT * FROM warenhaus.artikel ;
UPDATE warenhaus.artikel SET preis=preis*1.25
WHERE Artikelkategorie = 37;
–oder
UPDATE warenhaus.artikel SET preis=preis*1.25
WHERE Artikelkategorie = ( SELECT id
FROM warenhaus.artikelkategorie
WHERE name = ‚Peripherals and Accessories‘
);
————————————————————–
SELECT
prod_id ,
prod_name ,
prod_category,
prod_list_price*1.25
FROM SH.Products
where prod_category=’Peripherals and Accessories‘;
oder
update SH.Products set prod_list_price = prod_list_price*1.25
where prod_category=’Peripherals and Accessories‘;
SELECT
prod_id ,
prod_name ,
prod_category,
prod_list_price
FROM SH.Products
where prod_category=’Peripherals and Accessories‘;
rollback;
/*
Aufgabe 8:
Erstellen Sie die Tabellen für die Entitäten ArtikelRabatt, Rabataktion und Filiale. Übernehmen Sie alle
Attribute aus dem ER-Diagramm. Sorgen Sie bei allen Attributen, wo es sinnvoll Erscheint, für die entsprechenden
Constraints(Primär-/Fremdschlüssel, CHECK, NOT NULL bzw. DEFAULT).
*/
create table warenhaus.Rabattaktion (
rabatt_id INTEGER CONSTRAINT pk_ra PRIMARY KEY
, Startdatum date CONSTRAINT date_1 not null
, Enddatum date CONSTRAINT date_2 not null
, Name VARCHAR2(50) CONSTRAINT name_nn NOT NULL
);
–drop table warenhaus.Rabattaktion;
create table warenhaus.Filiale (
Filiale_id INTEGER CONSTRAINT pk_f PRIMARY KEY
, LagerID number(20)
, Strasse varchar2(100)
, Hausnr varchar2(10)
, PLZ VARCHAR2(10)
, Ort VARCHAR2(50 CHAR)
);
alter table warenhaus.Filiale add constraint FK_lager_1 Foreign Key (LagerID)
references warenhaus.lager (lagerid);
create table warenhaus.ArtikelRabatt (
artikelNr integer
, RabattId NUMBER
, redPreis NUMBER(10,2)
, Rabatt NUMBER(2,2)
, CONSTRAINT PK_artikel_rabattid PRIMARY KEY (artikelnr, rabattid)
, CONSTRAINT CK_rabatt CHECK (Rabatt BETWEEN 0 AND 75)
, CONSTRAINT CK_Preis CHECK (redPreis > 0)
, CONSTRAINT FK_ArtikelRabattArtikel FOREIGN KEY (artikelNr)
REFERENCES warenhaus.artikel (artikelnr)
);
————————————————————————————————
create table warenhaus.Rabattaktion (
rabatt_id INTEGER CONSTRAINT pk_ra PRIMARY KEY,
Startdatum date CONSTRAINT date_1 not null ,
Enddatum date CONSTRAINT date_2 not null ,
Rabatt_prozent number(2,2)
);
–drop table warenhaus.Rabattaktion;
–drop table warenhaus.Filiale ;
–drop table ArtikelRabatt ;
create table warenhaus.Filiale (
Filiale_id INTEGER CONSTRAINT pk_f PRIMARY KEY
,LagerID number(20)
,Adresse varchar(50)
);
alter table warenhaus.Filiale add constraint FK_lager_1 Foreign Key (LagerID) references warenhaus.lager (lagerid);
create table ArtikelRabatt (
art_id integer
,art_name varchar(50)
, rabatt_aktion Integer
);
alter table warenhaus.ArtikelRabatt add constraint FK_artikel Foreign Key (art_id) references warenhaus.Artikel (artikelnr);
—
CREATE DIRECTORY ext_data AS ‚O:\externadatei‘;
SELECT * from all_DIRECTORIES ;
DROp TABLE kundenextern;
CREATE TABLE KundenExtern (Kundennr NUMBER
, Title VARCHAR2(10)
, Vorname VARCHAR2(100 CHAR)
, Nachname VARCHAR2(100 CHAR)
, Strasse VARCHAR2(100 CHAR)
, Stadt VARCHAR2(100 CHAR)
, Bundesland VARCHAR2(50 CHAR)
, PLZ VARCHAR2(50 CHAR)
, email VARCHAR2(150 CHAR)
, kreditkarte VARCHAR2(50 CHAR)
, KreditkartenNr VARCHAR2(50 CHAR)
) ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_data
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
SKIP 1
CHARACTERSET AL32UTF8
LOGFILE ext_data: ‚kunden_import.log‘
BADFILE ext_data: ‚kunden.bad‘
FIELDS TERMINATED BY ‚,‘ (
Kundennr CHAR
, Title CHAR(10)
, Vorname CHAR(100)
, Nachname CHAR(100)
, Strasse CHAR(100)
, Stadt CHAR(100)
, Bundesland CHAR(50)
, PLZ CHAR(50)
, email CHAR(150)
, kreditkarte CHAR(50)
, KreditkartenNr CHAR(50)
)
)LOCATION(‚kunden2.txt‘)
)REJECT LIMIT UNLIMITED;
—to create table from externe Data
CREATE TABLE KundenExtern2 AS
SELECT DISTINCT *
FROM KundenExtern;
SELECT * from all_DIRECTORies ;
SELECT * FROM KundenExtern2;
describe KundenExtern2;
drop table KundenExtern2 ;
drop table KundenExtern;
——-
SELECT * FROM EXTERNEDATEN;
UPDATE EXTERNEDATEN SET menge=5000 WHERE artikelnr=110;
ALTER TABLE EXTERNEDATEN ADD CONSTRAINT PK_P PRIMARY KEY (Lagerid, platzid, artikelnr);
SELECT * FROM ALL_DIRECTORIES;
— Diese sollten als SYSDBA augeführt werden
CREATE DIRECTORY ext_data AS ‚O:\externe Daten‘;
GRANT READ,WRITE On DIRECTORY ext_data TO mike_bondzio;
———————————————————
SELECT * FROM kundenextern;
CREATE TABLE kundenimport
AS SELECT * FROM kundenextern;
SELECT * FROM kundenimport;
–UPDATE kundenextern SET Title=’Frau‘ WHERE TITLE=’Mrs.‘;
–UPDATE kundenimport SET Title=’Frau‘ WHERE TITLE=’Mrs.‘;
— Eine Testtabelle für Datentypen
— DROP TABLE datentypen;
CREATE TABLE datentypen (ID NUMBER
, id2 INTEGER
, id3 NUMBER (15)
, name1 VARCHAR2(50)
, name2 VARCHAR(50)
, name3 CHAR (100 CHAR)
, name4 CHAR
, name5 VARCHAR2 (1) );
–Anzeige von Datentypen der Tabellenspalten
SELECT * FROM all_tab_columns
WHERE Owner = ‚MIKE_BONDZIO‘
AND TABLE_NAME=’DATENTYPEN‘;
— Zeilenfunktion
SELECT NVL(100,200) FROM DUAL;
SELECT NVL(NULL,200) FROM DUAL;
SELECT NVL(NULL,’Ist Null‘) FROM DUAL;
SELECT NVL(‚Test‘,’Ist Null‘) FROM DUAL;
SELECT NVL (“, ‚Ist NULL‘) FROM DUAL; — etwas speziel
— Das geht Aufgrund der Konvertierung des Wertes
— commission_pct zu einem CHAR
SELECT employee_id
, salary
, NVL(TO_CHAR(commission_pct), ‚Kein Bonus‘)
FROM HR.Employees;
— Geht nicht, Datentypen sind inkompatibel
SELECT employee_id
, salary
, NVL(commission_pct, 0)
FROM HR.Employees;
— NVL2 prüft den ersten Parameter auf NULL
— wenn der erste Parameter nicht NULL ist wird der Wert des zweiten Parameters zurückgebeben
— wenn der erste Parameter NULL ist wird der Wert des dritten Parameters ausgegeben
SELECT NVL2(‚Test‘, 1, 2) FROM DUAL;
SELECT NVL2(NULL, 1, 2) FROM DUAL;
— Auch hier ist es wichtig auf die Datentypen zu achten
— sind die Datentypen inkompatibel kommt es zu einen Fehler
SELECT NVL2(commission_pct, commission_pct, ‚Keinen Bonus‘)
FROM HR.EMployees;
SELECT NVL2(TO_CHAR(commission_pct), TO_CHAR( commission_pct ), ‚Keinen Bonus‘)
FROM HR.EMployees;
SELECT salary
, commission_pct
, NVL2(commission_pct, salary*commission_pct, salary+500 )
FROM HR.EMployees;
— Die Zeilenfunktion INSTR mit unterschiedlichen Aufrufen
SELECT e.*, INSTR(first_name, ‚Ma‘)
FROM HR.employees e;
SELECT * FROM
HR.EMployees
WHERE INSTR(first_name, ‚Ma‘) <>0;
SELECT first_name
, INSTR(first_name, ‚Ma‘, 2 )
FROM HR.employees e
WHERE INSTR(first_name, ‚Ma‘, 2) <> 0;
SELECT first_name
, INSTR(first_name, ‚Jose‘, 1, 2 )
FROM HR.employees e;
–UPDATE hr.employees set first_name =’Jose Jose‘ WHERE first_name=’Jose Manuel‘
— Konvertierung eines String in eine Zahl
SELECT TO_NUMBER(‚451,65€‘, ‚999D99L‘) + 55.60 FROM DUAL;
— Geht nicht da Konvertierungsfehler
SELECT ‚451,65€‘ + 55.60 FROM DUAL;
— Length
SELECT LENGTHB (cust_first_name) AS Bytewert
,LENGTH (cust_first_name) AS Länge
FROM SH.Customers
WHERE LENGTHB(cust_first_name)<>LENGTH(cust_first_name);
SELECT artikelnr
,LENGTHB (name) AS Bytewert
,LENGTH (name) AS Länge
FROM warenhaus.artikel
WHERE LENGTHB(name)<>LENGTH(name);
–UPDATE WARENHAUS.artikel SET name = name || ‚?‘
–WHERE ARTikelNR=1;
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY– NUR HIER KÖNNEN WIR SPALTENALIAS-NAMEN VERWENDEN!!!!!!!!!!!!!!!!!!!!!!!!!!!
—-
SELECT employee_id
, salary
, NVL(commission_pct, ‚492,51‘)
FROM HR.Employees;
—
alt + nummern ? Ú?? ¾ ~? ? ñ k ? ? ?
SELECT e.employee_id, e.job_id, e.salary, e.department_id
FROM HR.Employees e
WHERE salary > ( SELECT avg(a.salary)
FROM hr.employees a
WHERE a.department_id=e.department_id);
SELECT e.employee_id, e.job_id, e.salary, e.department_id
FROM HR.Employees e
WHERE salary > ( SELECT avg(a.salary)
FROM hr.employees a
WHERE a.department_id=e.department_id);
— Inner View
SELECT *
FROM ( SELECT department_id, AVG(salary)
FROM HR.EMployees
GROUP BY department_id ) a
INNER JOIN HR.EMployees e ON e.department_id=a.department_id;
CREATE OR REPLACE VIEW avg_sal_dept
AS
SELECT department_id
, AVG(salary) salavg
FROM HR.EMployees
GROUP BY department_id
;
SELECT *
FROM avg_sal_dept a
INNER JOIN HR.EMployees e ON e.department_id=a.department_id;
SELECT avg(a.salary)
FROM hr.employees a
WHERE a.department_id=90;
—–
— eine einfache View erstellen
SHOW USER;
CREATE OR REPLACE VIEW v_view
AS
SELECT employee_id
, last_name
, first_name || ‚ ‚ || last_name AS FULLNAME
, job_id
, salary
, hire_date
, department_id
FROm HR.Employees
WHERE department_id IN ( 30,90,100,70, 260)
–WITH CHECK OPTION
;
–Updates mit Check-Option
UPDATE v_view SET department_id=260
WHERE employee_id IN(113,110,116);
–Updates mit Check-Option geht nicht da
— nicht der Where-Klausel entspricht
UPDATE v_view SET department_id=10
WHERE employee_id IN(113,110,116);
— INSERT über VIEW
INSERT INTO v_view (last_name, hire_date, email, employee_id, job_id, department_id)
VALUES (‚Test2‘, SYSDATE – 365, ‚asjdhaäs‘, ‚999‘, ‚PR_REP‘, 260 );
— DELETE über VIEW
— Löschen von Datensätzen ist nur
— möglich für Datensätze die auch in der VIEW sichtbar sind!
DELETE v_view WHERE employee_id=103;
DELETE HR.Employees WHERE employee_id = 950;
— INSERT über View
INSERT INTO v_view (Employee_id, Job_id, last_name, hire_date, email)
VALUES (900, ‚FI_ACCOUNT‘, ‚Bondzio‘, SYSDATE, ‚asjkdgö‘);
— Datensätze in der View ändern
UPDATE v_view set salary=17000
WHERE employee_id=700;
UPDATE v_view set salary=1700
WHERE employee_id=500;
— Abfragen der Metadaten einner VIEW
SELECT * FROm user_views;
DESC HR.EMPLOYEES;
— Versuch eines Updates auf ein „virtuelles Attribut“
— (kombination von first_name und last_name)
UPDATE v_view SET fullname = ‚Dies wird ein Test‘;
— Neuen Benutzer anlegen
CONNECT sys/Pa$$w0rd as sysdba
CREATE USER myViewUser
IDENTIFIED BY Pa$$w0rd
DEFAULT TABLESPACE USERS
QUOTA 1M ON USERS;
— Erweitern von Quota für Nutzer
–ALTER USER myViewUser
— QUOTA 10M ON USERS;
GRANT CONNECT TO myViewUser;
GRANT SELECT ON mike_bondzio.v_view TO myViewUser;
GRANT CREATE TABLE TO myViewUser;
— Abfrage von Daten aus View
CONNECT myViewUser/Pa$$w0rd;
SELECT * FROM mike_bondzio.v_view;
SELECT * FROM HR.EMPLOYEES;
DROP TABLE quotaTest;
–CREATE TABLE quotaTest ( test CHAR(2000), test1 varchar2(2000));
CREATE TABLE quotaTest ( test CHAR(2000));
SELECT * FROM quotaTest;
— Hier ein Skrip hin
— PL/SQL anonymer Block
BEGIN
FOR i IN 1 .. 50000
LOOP
INSERT INTO quotaTest VALUES (‚1‘);
dbms_output.put_line(‚Anzahl der Ausführungen: ‚ || i);
END LOOP;
END;
— Ende PL/SQL Block
— komplexe Views
CREATE OR REPLACE VIEW v_group_avg
AS
SELECT department_id
, ROUND(AVG(salary),2) AS AVG
FROM HR.EMPLOYEES
GROUP BY department_id;
— Versuch Update über komplexe View
SELECT * FROM v_group_avg
WHERE department_id > 90;
— Abfrage ob bei einer View DML-Anweisungen
— angewendet werden können
SELECT * FROM user_updatable_columns
WHERE table_name IN (SELECT view_name FROM user_views)
— Eine View mit Join
CREATE OR REPLACE VIEW v_join
AS
SELECT d.department_name, employee_id, d.department_id
FROM HR.EMPLOYEES e
JOIN hr.departments d ON d.department_id=e.department_id;
SELECT * FROM v_join;
DESC HR.Departments;
DESC HR.EMPLOYEES;
— CTE
WITH emp
AS
(
SELECT department_id, AVG(salary) as avg
FROm HR.employees
GROUP BY department_id
)SELECT * FROM HR.Departments d
INNER JOIN emp e on e.department_id=d.department_id;
WITH empavg
AS
(
SELECT department_id, AVG(salary) as avg
FROm HR.employees
GROUP BY department_id
)SELECT * FROm HR.EMPLOYEES he
INNER JOIN empavg e ON e.department_id=he.department_id
WHERE salary > e.avg;
/*WITH empavg
AS
(
SELECT department_id, AVG(salary) as avg
FROm HR.employees
GROUP BY department_id
),
emploc
AS
( SELECT l.location_id FROm hr.locations l INNER JOIN HR.departments d ON l.location_id=d.location_id )
SELECT * FROm HR.EMPLOYEES he
INNER JOIN empavg e ON e.department_id=he.department_id
INNER JOIN emploc el ON el.location_id<>he.department_id
WHERE salary > e.avg;
*/
— Inner VIEW
set autotrace on;
SELECT *
FROM (
SELECT department_id, AVG(salary) as avg
FROm HR.employees
–WHERE department_id IN(30,60,90,110)
GROUP BY department_id
–HAVING AVG(salary) > 10000
) dept_avg_salary
INNER JOIN hr.employees e on dept_avg_salary.department_id=e.department_id;
— korrelierte Unterabfrage
set autotrace on;
SELECT e.employee_id, e.job_id, e.salary, e.department_id
FROM HR.Employees e
WHERE salary > ( SELECT avg(a.salary)
FROM hr.employees a
WHERE a.department_id=e.department_id);
SELECT * FROM warenhaus.lagerbestand;
SELECt * FROM WARENHAUS.artikel;
–externe Tabellen
— Muss als SYS ausgeführt werden!!!
–DROP DIRECTORY ext_data;
CREATE DIRECTORY ext_data AS ‚O:\externe Daten‘;
–CREATE DIRECTORY ext_data1 AS ‚O:\extere Daten‘;
GRANT READ,WRITE ON DIRECTORY ext_data TO mike_bondzio;
——————————————————–
CREATE TABLE externeDaten ( lagerid CHAR(5)
, platzid CHAR(5)
, ArtikelNr CHAR(5)
, Menge CHAR(10)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_data
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
SKIP 1
CHARACTERSET AL32UTF8
LOGFILE ext_data: ‚import.log‘
BADFILE ext_data: ‚badrows.txt‘
FIELDS TERMINATED BY ‚,‘ (
lagerid CHAR(5)
, platzid CHAR(5)
, ArtikelNr CHAR(5)
, Menge CHAR(10)
)
) LOCATION (‚import.csv‘, ‚import1.csv‘ )
)REJECT LIMIT UNLIMITED;
CREATE TABLE ext1 AS
SELECT DISTINCT *
FROM externedaten;
SELECT * FROM ext1;
DROP TABLE externeDaten;
SELECT SUBSTR(‚NAME: Max Mustermann‘, 7) FROM DUAL;
SELECT SUBSTR(‚NAME: Max Mustermann‘, (SELECT INSTR (‚NAME: Max Mustermann‘, ‚Mu‘) FROM DUAL)) FROM DUAL;
SELECT SUBSTR(‚NAME: Max Mustermann‘, INSTR(‚NAME: Max Mustermann‘, ‚Ma‘)) FROM DUAL;
SELECT SUBSTR(‚NAME: Karl Mustermann‘, INSTR(‚Name:‘, ‚:‘) + 2) FROM DUAL;
SELECT SUBSTR(‚Fullname: Max Mustermann‘, INSTR(‚Fullname:‘, ‚:‘) + 2) FROM DUAL;
SELECT INSTR(‚Fullname:‘, ‚:‘) FROM DUAL;
CREATE TABLE namen (name varchar2(50));
INSERT INTO namen VALUES (‚NAME: Karl Mustermann‘);
INSERT INTO namen VALUES (‚Fullname: Mike Bondzio‘);
INSERT INTO namen VALUES (‚Fullname: Gustav Bondzio‘);
————————————————————–
SELECT name
, SUBSTR(name, INSTR(name, ‚:‘)+2, 4)
FROM namen;
SELECT ‚Test :‘ || NULL FROM DUAL;
SELECT 6 + NULL FROM DUAL;
— CONCAT ->“nicht so Geil“ da nur 2 Argumente übergeben werden können
SELECT CONCAT (‚A‘, ‚B‘) FROM DUAL;
SELECT CONCAT (‚Teil A‘, CONCAT(‚Teil B‘, ‚Teil C‘)) As Concat FROM DUAL;
SELECT CONCAT (CONCAT(first_name, ‚ ‚), last_name) FROM HR.Employees;
SELECT CONCAT (first_name || ‚ ‚, last_name) FROM HR.Employees;
— So macht man es produktiv
SELECT first_name || ‚ ‚ || last_name ||‘ ‚ || ‚Salary: ‚ || salary
FROM HR.Employees;
— Abfrage von oben mit Concat
SELECT CONCAT(first_name,(CONCAT(‚ ‚,CONCAT(last_name,CONCAT(‚ ‚,CONCAT(‚Salary: ‚, salary))))))
FROM HR.Employees;
— UPPER gibt den String in Großbuchstaben zurück
— Lower in Kleinbuchstaben
SELECT UPPER(first_name)
, LOWER (first_name)
FROM HR.EMPLOYEES;
— INITCAP schreibt jeden Anfangsbuchstaben jedes Wortes groß
SELECT INITCAP(‚ich bin ein test‘) FROM DUAL;
–UPDATE HR.Employees SET first_name=INITCAP(‚mike bondzio‘) WHERE employee_id=100;
–SELECT * FROM HR.Employees WHERE employee_id=100;
SELECT last_name
, RPAD(last_name, 15, ‚.‘)
, salary
, LPAD(salary, 10, ‚*‘)
FROM HR.EMPLOYEES;
SELECT TRIM(‚ Test ‚) as T
, LTRIM (‚ Test ‚) As LT
, RTRIM (‚ Test ‚) As RT
FROM DUAL;
SELECT LTRIM (‚…………..Test……….‘, ‚.‘) As LT
, RTRIM (‚…………..Test……….‘, ‚.‘) As RT
FROM DUAL;
SELECT LTRIM(RTRIM (‚…………..Test……….‘, ‚.‘), ‚.‘) as T FROM DUAL;
SELECT LTRIM(RTRIM (‚TesT‘, ‚T‘), ‚T‘) FROM DUAL;
— SUBSTR
SELECT SUBSTR(‚NAME: Max Mustermann‘, 7, 3) as Name
FROM DUAL;
SELECT SUBSTR(‚Fullname: Max Mustermann‘, 11) FROM DUAL;
CREATE TABLE namen (name varchar2(50));
INSERT INTO namen VALUES (‚NAME: Karl Mustermann‘);
INSERT INTO namen VALUES (‚Fullname: Mike Bondzio‘);
INSERT INTO namen VALUES (‚Fullname: Gustav Bondzio‘);
SELECT name
, SUBSTR(name, INSTR(name, ‚:‘)+2)
FROM namen;
SELECT SUBSTR(‚NAME: Karl Mustermann‘, INSTR(‚Name:‘, ‚:‘) + 2) FROM DUAL;
SELECT SUBSTR(‚Fullname: Max Mustermann‘, INSTR(‚Fullname:‘, ‚:‘) + 2) FROM DUAL;
SELECT INSTR(‚Fullname:‘, ‚:‘) FROM DUAL;
SELECT SUBSTR(‚Mike Bondzio‘, -7) FROM DUAL;
SELECT name
, SUBSTR(name, INSTR(name, ‚:‘)+2,-1)
FROM namen;
———————————
SELECT TO_DATE (‚01.12.2021‘) FROM DUAL;
SELECT DATE ‚2021-12-01‘ FROM DUAL;
select to_date(11051364,’mmddyyyy‘) from dual;
SELECT TO_DATE (’01-dez-2021′,’dd.month.yyyy‘) FROM DUAL;
SELECT TO_DATE (’01-12-2021′,’dd.mm.yyyy‘) FROM DUAL;
SELECT TO_DATE (’01-12-2021′,’dd.mm.yyyy‘) FROM DUAL;
ALTER SESSION SET NLS_DATE_FORMAT=’dd.month.yyyy‘;
ALTER SESSION SET NLS_DATE_FORMAT=’dd.mm.yyyy‘;
ALTER SESSION SET NLS_language=’GERMAN‘;
SELECT * FROM nls_session_parameters ;
SELECT TO_CHAR(DATE ‚2020-07-07‘, ‚“Heute ist „FMDay, „der „dd“of “ Month yyyy‘) As Text FROM DUAL;
SELECT *
FROM NLS_DATABASE_PARAMETERS;
SELECT dbp.parameter as Parameter
, dbp.value as Datenbankeinstellung
, sp.value AS Sessioneinstellung
FROM NLS_DATABASE_PARAMETERS dbp
INNER JOIN nls_session_parameters sp ON sp.parameter=dbp.parameter;
CREATE TABLE namen (name varchar2(50));
INSERT INTO namen VALUES (‚NAME: Karl Mustermann‘);
INSERT INTO namen VALUES (‚Fullname: Mike Bondzio‘);
INSERT INTO namen VALUES (‚Fullname: Gustav Bondzio‘);
SELECT name
, SUBSTR(name, INSTR(name, ‚ ‚, 1 ,1) +1,(INSTR(name, ‚ ‚,1,2)-INSTR(name, ‚ ‚, 1 ,1))) AS Vorname
FROM namen;
SELECT e.employee_id
, e.first_name
, e.last_name
, d.department_name
, d.manager_id
, l.city
FROM HR.Employees e
RIGHT JOIN hr.Departments d on e.department_id=d.department_id
INNER JOIN HR.locations l on d.location_id=l.location_id;
— Natural Join
SELECT *
FROM HR.EMPLOYEES
NATURAL JOIN HR.Departments;
— Konvertierung von Daten
— TO_CHAR
— TO_NUMBER
— TO_DATE
— Konvertiert eine Zeichenkette in ein Datum
SELECT DATE ‚2021-01-01‘ FROM DUAL;
SELECT TO_DATE (‚01.12.2021‘, ‚dd.mm.yyyy‘) FROM DUAL;
SELECT TO_DATE (‚13.12.2021‘, ‚dd.mm.yyyy‘) FROM DUAL;
SELECT TO_DATE (’13-12-2021′, ‚dd.mm.yyyy‘) FROM DUAL;
SELECT TO_DATE (’13-JAN-2021′, ‚dd.mon.yyyy‘) FROM DUAL;
SELECT TO_DATE (’13-01-2021′, ‚dd.mm.yyyy‘) FROM DUAL;
select to_date(11051364,’mmddyyyy‘) from dual;
— TO_CHAR Datum zu String
SELECT TO_CHAR(DATE ‚2021-01-01‘, ‚day.mon.year‘) FROM DUAL; — Tag Monat(kurz) Jahr
SELECT TO_CHAR(DATE ‚2020-06-30‘, ‚ddd‘) FROM DUAL; — Tag im Jahr
SELECT TO_CHAR(DATE ‚2020-07-07‘, ‚Q‘) FROM DUAL; — Quartal
SELECT TO_CHAR(DATE ‚2020-12-07‘, ‚IW‘) FROM DUAL; — Woche vom Jahr 1-53
SELECT TO_CHAR(DATE ‚2020-01-07‘, ‚WW‘) FROM DUAL; — Rechnung der Woche anhand von Tagen (KW1 vom 01-07 )
SELECT TO_CHAR(DATE ’55-07-07′, ‚RR‘) FROM DUAL; — Gibt nur das Jahr zurück, Achtung Werte von 50-99
–werden als 19hundert interpretiert
SELECT TO_CHAR(DATE ‚1921-07-07‘, ‚CC‘) FROM DUAL; — gibt das Jahrtausend zurück
SELECT TO_CHAR(DATE ‚2020-07-07‘, ‚“Heute ist „Day, „der „dd“ of „Month yyyy‘) As Text FROM DUAL — Ausgabe mit Textbausteinen
UNION
SELECT TO_CHAR(DATE ‚2020-07-07‘, ‚FM“Heute ist „Day, „der „dd“ of „Month yyyy‘) As Text FROM DUAL;
SELECT salary FROM HR.Employees;
SELECT * FROM nls_session_parameters; — Anzeige der aktuellen Session-Einstellungen
ALTER SESSION SET NLS_ISO_CURRENCY=’Switzerland‘ — Änderung einer Session-Einstellung ;
ALTER SESSION SET NLS_LANGUAGE=’German‘ — Änderung einer weiteren Session-Einstellung;
— TO_CHAR Zahl zu String
SELECT TO_CHAR(salary, ‚$999‘) FROM HR.EMPLOYEES; — Maske zu kurz für Werte
SELECT TO_CHAR(salary, ‚C00999‘) FROM HR.EMPLOYEES; — Ausgabe mit nicht optionalen Stellen 0
SELECT TO_CHAR(salary, ‚C99999‘) FROM HR.EMPLOYEES; — Ausgabe mit optionalen Stellen 9
SELECT TO_CHAR(salary, ‚L99999‘) FROM HR.EMPLOYEES; — Ausgabe mit Währungssymbol
SELECT TO_CHAR(salary, ’99G999D99L‘) FROM HR.EMPLOYEES;
SELECT TO_CHAR(salary, ‚RM‘) FROM HR.EMPLOYEES; — römische Zahlen
SELECT TO_CHAR(salary, ‚XXXX‘)
, salary
FROM HR.EMPLOYEES; — hexadezimal
— TO_NUMBER
SELECT TO_NUMBER(‚$05000‘, ‚$00000‘) FROM DUAL;
SELECT TO_NUMBER(‚$125‘, ‚$999‘) FROM HR.EMPLOYEES; — Maske zu kurz für Werte
SELECT TO_NUMBER(‚CHF12985‘, ‚C00999‘) FROM HR.EMPLOYEES; — Ausgabe mit nicht optionalen Stellen 0
SELECT TO_NUMBER(‚CHF55465‘, ‚C99999‘) FROM HR.EMPLOYEES; — Ausgabe mit optionalen Stellen 9
SELECT TO_NUMBER(‚€85600‘, ‚L99999‘) FROM HR.EMPLOYEES; — Ausgabe mit Währungssymbol
SELECT TO_NUMBER(‚55.800,60€‘, ’99G999D99L‘) FROM HR.EMPLOYEES;
–SELECT TO_NUMBER(’15‘, ‚XX‘)
— , salary
–FROM HR.EMPLOYEES; — hexadezimal
— Abfrage der Datenbank und Session Einstellungen
SELECT dbp.parameter as Parameter
, dbp.value as Datenbankeinstellung
, sp.value AS Sessioneinstellung
FROM NLS_DATABASE_PARAMETERS dbp
FULL JOIN nls_session_parameters sp ON sp.parameter=dbp.parameter;
/* Schreiben Sie eine Abfrage die ihnen die Anzahl der Verkäufe anzeigt*/
SELECT customer_id
, TO_CHAR(order_date, ‚Q‘) Quartal
, COUNT(*)
FROM OE.Orders
GROUP BY customer_id, TO_CHAR(order_date, ‚Q‘)
ALTER SESSION SET nls_language=’GERMAN‘;
ALTER SESSION SET nls_date_format=’dd.mm.yyyy‘;
ALTER SESSION SET nls_date_format=’dd.Mon.yyyy‘;
select * from nls_session_parameters;
select Months_between(sysdate,’01.01.1′) from dual ;
select round(Months_between(sysdate,’01.01.1′),2) from dual ;
select trunc(Months_between(sysdate,’01.01.1′),2) from dual ;
select trunc(Months_between(‚01.01.1‘ ,sysdate),2) from dual ;
select add_Months(sysdate,3*12) from dual ;
select last_day (sysdate) from dual;
select last_day (’01-02-2016′) from dual;
/*Schreiben SIe eine SQL-Anweisung
welche Ihnen den ersten Sonntag im Monat Mai im Jahr 2260 anzeigt.*/
select next_day (‚30.04.2260′ , ’sonntag‘ ) from dual;
select next_day (‚30.04.2260‘ , 7 ) from dual;
—
SELECT SYSDATE , CURRENT_TIMESTAMP FROM DUAL;
SELECT TO_CHAR(DATE ‚2020-07-07‘, ‚FM“Heute ist „Day, „der „dd“ of „Month yyyy‘) As Text FROM DUAL;
SELECT TO_CHAR(CURRENT_DATE, ‚DD/MM/YYYY HH:MI:SS‘) AS currentdate_time
FROM dual;
/*—–Aufgabe 1
–SELECT * FROM HR.employees;
Schreiben Sie eine Abfrage die Ihnen folgendes für jeden Mitarbeiter aus der Tabelle HR.Employees liefert:
„Der Vorgesetze von NeenaKochharistSteven Trans1. Der Gehaltsunterschied im Jahr zwischen
Manager und Angestellten, inkl. Bonus entspricht 84000Taler.“*/
SELECT * FROM HR.employees;
SELECT ‚Der Vorgesetze von‘ ||‘ ‚||first_name|| ‚ist‘ AS Text FROM HR.employees;
—————–
SELECT ‚Der Vorgesetze von ‚ || emid.first_name ||‘ ‚|| emid.last_name ||‘ ist ‚ || manid.first_name ||‘ ‚|| manid.last_name||
‚Der Gehaltsunterschied im Jahr zwischen Manager und Angestellten, inkl. Bonus entspricht ‚
FROM HR.employees emid
inner join HR.employees manid on emid.manager_id=manid.employee_id;
——–
SELECT ‚Der Vorgesetze von ‚ || emp.first_name || ‚ ‚ || emp.last_name || ‚ ist ‚ ||
mgr.first_name || ‚ ‚ || mgr.last_name || ‚. Der Gehaltsunterschied im Jahr ‚ ||
‚zwischen Manager und Angestellten, inkl. Bonus entspricht ‚ ||
((mgr.salary+(mgr.salary * NVL(mgr.commission_pct,0)))*12-(emp.salary+(emp.salary * NVL(emp.commission_pct,0)))*12)
|| ‚ Taler.‘ AS text
FROM HR.Employees emp
INNER JOIN HR.Employees mgr ON emp.manager_id=mgr.employee_id;
/*–Aufgabe 2:
Schreiben Sie eine SQL-Anweisung die Ihnen das folgende Ergebnis liefert
(Verwenden Sie einen INNER VIEW oder eine korrelierte Unterabfrage):*/
SELECT emid.employee_id , emid.first_name , emid.last_name , emid.manager_id,manid.employee_id as ManagerID , manid.first_name , manid.last_name
FROM HR.employees emid
inner join HR.employees manid on emid.manager_id=manid.employee_id;
–oder
select emp.employee_id,
emp.first_name,
emp.last_name,
emp.manager_id,
mgr.managerid,
manager_first_name,
manager_last_name
from (select employee_id as managerid ,
first_name as manager_first_name,
last_name as manager_last_name
from hr.employees) mgr
join hr.employees emp on emp.manager_id = mgr.managerid
—oder
/*—-/*
Aufgabe 3(Arbeiten Sie mit Case)
Schreiben Sie eine SQL-Anweisung die Ihnen folgende Ausgabe erzeugt. Sie benötigen das Skript welches Ihnen die Tabelle „Aufgabe3“ erstellt :*/
SELECT employee_id,
CASE
WHEN TO_NUMBER(TO_CHAR(sysdate, ‚yyyy‘)) – TO_NUMBER(TO_CHAR(hire_date, ‚yyyy‘)) BETWEEN 10 AND 19
THEN ‚Mind. 10 aber weniger als 20‘
WHEN TO_NUMBER(TO_CHAR(sysdate, ‚yyyy‘)) – TO_NUMBER(TO_CHAR(hire_date, ‚yyyy‘)) >= 20
THEN ‚mind. 20 Jahre‘
WHEN TO_NUMBER(TO_CHAR(sysdate, ‚yyyy‘)) – TO_NUMBER(TO_CHAR(hire_date, ‚yyyy‘)) < 5
THEN ‚weniger als 5 Jahre‘
END AS text
, hire_date
FROm aufgabe3;
— Lösung Aufgabe 2 Arbeitsblatt
SELECT emp.employee_id
, emp.first_name
, emp.last_name
, emp.manager_id
, mgr.managerid
, mgr.manager_first_name
, mgr.manager_last_name
FROM ( SELECT employee_id as managerid
, first_name AS manager_first_name
, last_name AS manager_last_name
FROM HR.Employees) mgr
INNER JOIN HR.Employees emp ON emp.manager_id=mgr.managerid;
— korrelierte Unterabfrage
SELECT emp.first_name, emp.last_name, ( SELECT first_name || ‚ ‚ || last_name
FROM HR.Employees
WHERE employee_id=emp.manager_id)
FROM HR.EMployees emp;
— Datumsfunktionen
ALTER SESSION SET nls_language=’GERMAN‘;
ALTER SESSION SET NLS_date_format=’dd.mm.yyyy‘
SELECt * FROM nls_session_parameters;
SELECT ROUND(MONTHS_BETWEEN(SYSDATE, ‚01.01.1‘),2) FROM DUAL; — Round rundet mathematisch
SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, ‚01.01.1‘),2) FROM DUAL; — Trunc rundet immer ab
SELECT MONTHS_BETWEEN(‚01.01.1‘, SYSDATE) FROM DUAL;– Liefert eine negative Zahl
— ADD_MONTHS
SELECT SYSDATE AS Datum
, CURRENT_TIMESTAMP AS TS
FROM DUAL;
SELECT ADD_MONTHS (CURRENT_TIMESTAMP, 48) FROM DUAL;
/*
DATE
Ein date bei Oracle enthält wie beschrieben immer auch die Uhrzeit. Der SQL-Standard definiert die Datentypen date, datetime und time mit entsprechendem Inhalt. Dass Oracles date-Datentyp einen Zeitanteil enthält, ist historisch gewachsen
und kann mit Rücksicht auf bestehende Anwendungen nun auch nicht mehr geändert werden.
Der Datentyp date ist grundsätzlich sekundengenau, hat aber keinerlei Vorstellung
von Zeitzonen, Sommerzeit oder etwas Ähnlichem. Das früheste Datum, das Sie einstellen können, ist der 1. Januar 4713 v. Chr., was astronomisch dem Jahr –4712 entspricht und als Grundlage für die Berechnung des julianischen Kalenders verwendet
wird. Demzufolge können Sie mit der Formatmaske J aus einem Datum auch das julianische Datum entnehmen (zumindest den Tag), das heißt die Anzahl der Tage seit dem 01.01.–4712 (astronomisch) bis zum Datum.
——————————————————————————————————————————–
Datumstypen sind, abweichend vom SQL-Standard, bei Oracle historisch bedingt
anders definiert als in vielen anderen Datenbanken. Lediglich die Datentypen
timestamp und interval sind standardkonform, bei date müssen Sie die Besonderheiten der Oracle-Implementierung
*/
— Last Day -> Gibt den letzen Tag eines Monats zurück
SELECT LAST_DAY (SYSDATE) FROm DUAL;
SELECT LAST_DAY (‚01.02.2020‘) FROm DUAL;
SELECT LAST_DAY (DATE ‚2020-01-01‘) Januar FROM DUAL
UNION
SELECT LAST_DAY (DATE ‚2020-02-01‘) Februar FROM DUAL
UNION
SELECT LAST_DAY (DATE ‚2020-03-01‘) März FROM DUAL
UNION
SELECT LAST_DAY (DATE ‚2020-04-01‘) April FROM DUAL
UNION
SELECT LAST_DAY (DATE ‚2020-05-01‘) Mai FROM DUAL
UNION
SELECT LAST_DAY (DATE ‚2020-06-01‘) Juni FROM DUAL
UNION
SELECT LAST_DAY (DATE ‚2020-07-01‘) Juli FROM DUAL
UNION
SELECT LAST_DAY (DATE ‚2020-08-01‘) August FROM DUAL
UNION
SELECT LAST_DAY (DATE ‚2020-09-01‘) September FROM DUAL
UNION
SELECT LAST_DAY (DATE ‚2020-10-01‘) Oktober FROM DUAL
UNION
SELECT LAST_DAY (DATE ‚2020-11-01‘) November FROM DUAL
UNION
SELECT LAST_DAY (DATE ‚2020-12-01‘) Dezember FROM DUAL;
— NEXT_DAY -> Gibt den nächsten definierten Wochentag
— eines Datums zurück
— Nächsten Wochentag anzeigen
SELECT NEXT_DAY (SYSDATE, ‚MON‘) FROM DUAL;
SELECT NEXT_DAY(SYSDATE,7) FROM DUAL;
SELECT NEXT_DAY (SYSDATE, ‚Donnerstag‘) FROM DUAL;
SELECT NEXT_DAY(SYSDATE, ‚Freitag‘) FROM DUAL;
SELECT hire_date
, NEXT_DAY(hire_date, ‚Freitag‘)
FROM HR.Employees
SELECT TO_CHAR(DATE ‚1991-11-05‘, ‚FM“Heute ist „Day, „der „dd“ of „Month yyyy‘) As Text
FROM DUAL; — Ausgabe mit Textbausteinen
SELECT first_name as name
FROM HR.EMPLOYEES
WHERE employee_id=100
UNION
SELECT Country_name as name1
FROm HR.Countries
WHERE country_id=’BE‘
ORDEr BY name;
/*
Schreiben SIe eine SQL-Anweisung
welche Ihnen den ersten Sonntag im Monat Mai im Jahr 2260 anzeigt.
*/
select next_day (‚30.04.2260′ , ’sonntag‘ ) from dual;
select NEXT_DAY ((last_day(date ‚2260-04-01‘)), ‚Sonntag‘)from dual;
/*
Aufgabe 5:
Lassen Sie sich das Attribut Einkommen der Tabelle Attribut als Zahl ausgeben Bezieht sich auf die Tabelle Aufgabe3 aus Aufgabe 3:
*/
SELECT * FROM aufgabe3;
SELECT TO_NUMBER(TRIM(EINKOMMEN) ,’L99G999D99′) as einkom FROM aufgabe3;
/*aufgabe4
Schreiben Sie eine Abfrage die ihnen die Anzahl der Verkäufe pro Quartal anzeigt Zu benutzende Tabelle: OE.Orders*/
—-
SELECT customer_id
, TO_CHAR(order_date, ‚Q‘) AS Quartal
, COUNT(*) AS Anzahl_verkäufe
FROM oe.orders
GROUP BY customer_ID,TO_CHAR(order_date, ‚Q‘);
SELECT * from OE.orders;
—–
Select Trunc (Round (155.50, -2), -1)From Dual;
Select Round (155.50, -2)From Dual;
Select Trunc (200, -1)From Dual;
Select Trunc (255, -2)From Dual;
——
CREATE USER Musik
IDENTIFIED BY Pa$$w0rd
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users ;
CREATE DIRECTORY ext_data AS ‚O:\externadatei‘;
CREATE TABLE Musik.Datenbasis (Album VARCHAR(50 CHAR)
, Erscheinungsdatum char(12 CHAR)
, Interpret VARCHAR(50 CHAR)
, verkaufteStückzahlen INTEGER
, Titel VARCHAR(50 CHAR)
, TitelDauer char(10 CHAR)
, Genre VARCHAR(50 CHAR)
, Gruendung_gebdatum INTEGER
, aufloesung_verstorben INTEGER
) ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_data
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
SKIP 1
CHARACTERSET AL32UTF8
LOGFILE ext_data: ‚Datenbasis_l.log‘
BADFILE ext_data: ‚Datenbasis_b.bad‘
FIELDS TERMINATED BY ‚,‘
OPTIONALLY ENCLOSED BY ‚“‚ (
Album char(100 )
, Erscheinungsdatum char(100 )
, Interpret char(100 )
, verkaufteStückzahlen char(100 )
, Titel char(100 )
, TitelDauer char(100 )
, Genre char(100 )
, Gruendung_gebdatum char(100 )
, aufloesung_verstorben char(100 )
)
)LOCATION(‚Datenbasis.txt‘)
)REJECT LIMIT UNLIMITED;
SELECT * From Musik.Datenbasis ;
–Drop table Datenbasis;
–Table Genre
–drop table Musik.Genre;
SELECT * From Musik.Genre ;
CREATE TABLE Musik.Genre (genre_id number CONSTRAINT Genre_id_key Primary key
, genre_name char(50));
INSERT INTO Musik.Genre VALUES (1,’Artrock‘);
INSERT INTO Musik.Genre VALUES (2,’Pop‘);
INSERT INTO Musik.Genre VALUES (3,’Rock‘);
INSERT INTO Musik.Genre VALUES (4,’AOR‘);
INSERT INTO Musik.Genre VALUES (5,’Latin Rock‘);
INSERT INTO Musik.Genre VALUES (6,’Alternative Rock‘);
INSERT INTO Musik.Genre VALUES (7,’Bluesrock‘);
INSERT INTO Musik.Genre VALUES (8,’Hard Rock‘);
INSERT INTO Musik.Genre VALUES (9,’Reggae‘);
INSERT INTO Musik.Genre VALUES (10,’Progressive Rock‘);
INSERT INTO Musik.Genre VALUES (11,’Post-Grunge‘);
INSERT INTO Musik.Genre VALUES (12,’Heavy Metal‘);
INSERT INTO Musik.Genre VALUES (13,’Soul‘);
INSERT INTO Musik.Genre VALUES (14,’Pop-Rock‘);
INSERT INTO Musik.Genre VALUES (15,’Country‘);
INSERT INTO Musik.Genre VALUES (16,’R and B‘);
INSERT INTO Musik.Genre VALUES (17,’Funk‘);
INSERT INTO Musik.Genre VALUES (18,’Trash Metal‘);
INSERT INTO Musik.Genre VALUES (19,’Softrock‘);
INSERT INTO Musik.Genre VALUES (20,’Country-Rock‘);
—————
–drop table Musik.INTERPRET;
SELECT * FROM Musik.INTERPRET ;
–Table INTERPRET
CREATE TABLE Musik.INTERPRET AS
SELECT DISTINCT Interpret,Gruendung_gebdatum,aufloesung_verstorben
From Musik.Datenbasis ;
Alter table Musik.INTERPRET add INTERPRET_ID number GENERATED ALWAYS AS IDENTITY START WITH 1 INCREMENT BY 1 ;
Alter table Musik.INTERPRET add CONSTRAINT INTERPRET_id_key Primary key (INTERPRET_ID) ;
—
—SELECT * FROM Musik.Titels;
—Table Titels
–drop table Musik.Titels ;
CREATE TABLE Musik.Titels AS
SELECT DISTINCT Titel,TitelDauer,Album_ID,INTERPRET_ID
From Musik.Datenbasis d
inner join Musik.Album a on a.album=d.album ;
ALTER TABLE Musik.Titels ADD CONSTRAINT FK_Titels
FOREIGN KEY (Album_ID) REFERENCES Musik.Album (Album_ID);
–Table Musik.Album
CREATE TABLE Musik.Album AS
SELECT DISTINCT album,Erscheinungsdatum,verkaufteStückzahlen,INTERPRET_ID
FROM Musik.Datenbasis d
inner join Musik.INTERPRET i on i.interpret=d.interpret
–SELECT * From Musik.Album;
–drop table Musik.Album;
Alter table Musik.Album add Album_ID number GENERATED ALWAYS AS IDENTITY START WITH 1 INCREMENT BY 1 ;
Alter table Musik.album add CONSTRAINT album_id_key Primary key (album_id) ;
Alter table Musik.album add CONSTRAINT INTERPRET_ID_fk FOREIGN KEY (INTERPRET_ID) REFERENCES Musik.INTERPRET(INTERPRET_ID);
—–Create die Helfe Table
–drop TaBLE Musik.ALbum_Genre ;
SELECT * FROM Musik.ALbum_Genre ;
CREATE TABLE Musik.ALbum_Genre ( Album_id number
,Genre_ID number);
Alter table Musik.ALbum_Genre add CONSTRAINT Album_ID_fk FOREIGN KEY (Album_id) REFERENCES Musik.Album (Album_ID);
Alter table Musik.ALbum_Genre add CONSTRAINT GenreA_fk FOREIGN KEY (Genre_id) REFERENCES Musik.genre (genre_id );
–SELECT * FROM Musik.ALbum_Genre ;
–SELECT * From Musik.Genre ;
–SELECT * From Musik.Album;
INSERT INTO Musik.ALbum_Genre VAlUES (1,3);
INSERT INTO Musik.ALbum_Genre VAlUES (2,2);
INSERT INTO Musik.ALbum_Genre VAlUES (3,10);
INSERT INTO Musik.ALbum_Genre VAlUES (3,1);
INSERT INTO Musik.ALbum_Genre VAlUES (4,9);
INSERT INTO Musik.ALbum_Genre VAlUES (5,8);
INSERT INTO Musik.ALbum_Genre VAlUES (5,7);
INSERT INTO Musik.ALbum_Genre VAlUES (6,3);
INSERT INTO Musik.ALbum_Genre VAlUES (6,20);
INSERT INTO Musik.ALbum_Genre VAlUES (7,15);
INSERT INTO Musik.ALbum_Genre VAlUES (7,3);
INSERT INTO Musik.ALbum_Genre VAlUES (8,2);
INSERT INTO Musik.ALbum_Genre VAlUES (9,16);
INSERT INTO Musik.ALbum_Genre VAlUES (9,2);
INSERT INTO Musik.ALbum_Genre VAlUES (9,17);
INSERT INTO Musik.ALbum_Genre VAlUES (9,3);
–delete FROM Musik.ALbum_Genre where Album_id=6;
SELECT * From Musik.Datenbasis
inner join Musik.Datenbasis_Album a on a.album=datenbasis.album ;
—–
SELECT * from Musik.Album a
inner join Musik.Titels t on t.album_id=a.album_id
inner join Musik.INTERPRET i on i.interpret_id=a.interpret_id
–inner join Musik.Genre g on g.genre_id=a.genre_id
order by a.album_id ;
—–
TRUNCATE TABLE KUNDENIMPORT;
— DROP TABLE imp_Kunden;
— ALTER TABLE ext_kunden DROP CONSTRAINT SYS_C008239
CREATE TABLE imp_Kunden (
id NUMBER
, Anrede VARCHAR2(50)
, Vorname VARCHAR2(50)
, Nachname VARCHAR2(100)
,Strasse VARCHAR2(150)
, Stadt VARCHAR2(50)
, Bundesland VARCHAR2(50)
, PLZ VARCHAR2(50)
, Email VARCHAR2(100)
, Kreditkarte VARCHAR2(50)
, Kreditkartennr VARCHAR(50)
) ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_data
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
SKIP 1
CHARACTERSET AL32UTF8
LOGFILE ext_data: ‚kunden.log‘
BADFILE ext_data : ‚kunden.bad‘
FIELDS TERMINATED BY ‚,‘ OPTIONALLY ENCLOSED BY ‚“‚
(
id CHAR(150)
, Anrede CHAR(150)
, Vorname CHAR(150)
, Nachname CHAR(100)
,Strasse CHAR(150)
, Stadt CHAR(50)
, Bundesland CHAR(50)
, PLZ CHAR(50)
, Email CHAR(100)
, Kreditkarte CHAR(50)
, Kreditkartennr CHAR(50)
)
)LOCATION(‚kunden_windows.txt‘)
) REJECT LIMIT UNLIMITED;
SELECT * FROM imp_kunden;
CREATE TABLE EXT_Kunden (id NUMBER PRIMARY KEY
, Anrede VARCHAR2(50)
, Vorname VARCHAR2(50)
, Nachname VARCHAR2(100)
,Strasse VARCHAR2(150)
, Stadt VARCHAR2(50)
, Bundesland VARCHAR2(50)
, PLZ VARCHAR2(50)
, Email VARCHAR2(100)
, Kreditkarte VARCHAR2(50)
, Kreditkartennr VARCHAR(50)
);
INSERT INTO ext_kunden
SELECT * FROM imp_kunden;
SELECT * FROM ext_kunden
WHERE Nachname=’Möller‘;
SELECT nachname, rowid
FROM ext_kunden k
WHERE ID=500;
— Erstellen eines Index
— Index für Nachname
CREATE INDEX idx_ext_kunden_nachname
ON ext_kunden INCLUDE ( nachname );
— INdex für Bundesland
CREATE INDEX idx_ext_kunden_bundesland
ON ext_kunden INCLUDE ( bundesland );
— Index auf Bundesland und Nachname
CREATE INDEX idx_ext_kunden_bundesland_nachname
ON ext_kunden INCLUDE ( bundesland DESC, nachname ASC );
SELECT * FROM ext_kunden
WHERE bundesland=’Freistaat Bayern‘ AND Nachname=’Möller‘ ;
SELECT * FROM ext_kunden
WHERE Nachname=’Möller‘ AND bundesland=’Freistaat Bayern‘;
— Für den Abfrageoptimierer unsichtbare Indices
— werden aber vom RDBMS weiter gepflegt!!
ALTER INDEX idx_ext_kunden_bundesland_nachname INVISIBLE;
ALTER INDEX idx_ext_kunden_nachname INVISIBLE;
ALTER INDEX idx_ext_kunden_bundesland INVISIBLE;
ALTER INDEX idx_ext_kunden_bundesland_nachname VISIBLE;
—
CREATE INDEX idx_UPPER_vorname ON ext_kunden INCLUDE(UPPER(vorname));
— Abfrage mit Hinweis für den Abfrageoptimierer
— welcher Index benutzt werden soll
SELECT * /*+ index (idx_UPPER_vorname) */
FROM ext_kunden
WHERE UPPER(vorname)=UPPER(‚Thomas‘);
SELECT *
FROM ext_kunden
WHERE UPPER(vorname)=UPPER(‚Thomas‘);
CREATE TABLE test454 (id NUMBER PRIMARY KEY USING INDEX(CREATE INDEX test1 on test454(id))
, NACHname varchar2(100) );
— Alle Indices anzeigen
SELECT * FROM sys.all_indexes
WHERE table_owner=’MIKE_BONDZIO‘;
— DML
ROLLBACK;
UPDATE ext_kunden SET vorname=’Klaus‘
WHERE id=500;
UPDATE ext_kunden SET vorname=’Klaus‘,nachname=’Mustermann‘
WHERE id=500;
UPDATE ext_kunden SET vorname=’Klaus‘, kreditkarte=’American Express‘
WHERE vorname=’Klaus‘;
UPDATE (SELECT email, nachname FROM ext_kunden)
SET email=(SELECT email FROM HR.Employees WHERE employee_id=101)
, nachname=’Änderung‘
WHERE nachname=’Abend‘;
SELECT * FROM ext_kunden
WHERE nachname=’Änderung‘;
DELETE FROM ext_kunden
WHERE nachname=’Änderung‘;
DELETE ext_kunden
WHERE nachname=’Änderung‘;
DELETE FROM (SELECT * FROM hr.employees, hr.departments)
WHERE hr.departments.department_id = 100;
DELETE FROM ext_kunden
WHERE id between 5 AND 45000;
INSERT INTO ext_kunden (id, vorname, nachname) VALUES (60000, ‚Mike‘, ‚Muster‘);
INSERT INTO ext_kunden
SELECT * FROM Ext_kunden;
SELECT * FROM ext_kunden;
SELECT * FROM ext_kunden
WHERE vorname=’Stephanie‘;
CREATE INDEX idx_test on ext_kunden INCLUDE (vorname)