--CASTed values
select cast(nummer AS bigint) from p_nummer
order by cast(nummer AS bigint) asc
begin
update p_nummer set nummer = cast((cast(nummer AS bigint)+10000) as varchar)
--commit
--rollback
select nummer from p_nummer
where p_id = 20615401
and nummer LIKE '%Test%'
order by nummer asc
extract(year FROM CURRENT_TIMESTAMP) - extract(year FROM w.datum) <= ?
--exists / not exists
select from where exists
select from where not exists group by having order by
select from where (not exists) group by having order by
select fro mwhere /(exists) group by having order by
select from where (exists) group by habing order by
select from where (exists) group by having order by
--correlated subquery
-- inner query checks existence condition for every row of outer query
select d.*
from dept d
where exists (select * from emp e where e.deptno = d.deptno)
-- gib mir alles aus d, wofür etwas in e existiert
-- oder: ..., wofür nichts in e existiert
--create dummy table
create table dual (
dummy varchar(1)
)
insert into dual values ('X')
select * from dual
-- TODO Print error messages on mssql
DECLARE @MaxAmount int = 16;
DECLARE @minAmount int = 1;
Raiserror('Total Amount should be less than %d and Greater than %d',@MaxAmount,@MinAmount)
declare @MaxAmount int, @MinAmount int
select @MaxAmount = 50, @MinAmount = 5
Raiserror('Total Amount should be less than %i and Greater than %i',16,1,@MaxAmount,@MinAmount)
--DECLARE @PromoStartTimestamp DATETIME
DECLARE @PromoStartTimestampString VARCHAR(50)
--SELECT @PromoStartTimestamp = PromoStartTimestamp From @promo
SELECT @PromoStartTimestampString = 'moin'
RAISERROR(N'Code not valid until %s'
,16
,1
,@PromoStartTimestampString);
DECLARE @a INT;
SET @a = 1;
DECLARE @S VARCHAR(100)= 'This is iteration no';
WHILE(@a < 5)
BEGIN
RAISERROR('%s:%d', 0, 1, @s, @a) WITH NOWAIT;
SET @a = @a + 1;
END;
--
-- creating, deleting and updating tables
--
create table Telefonbuch (
name varchar(10),
nummer varchar(50),
strasse varchar(50),
hausnr varchar(10)
);
create table employee (
empID varchar(10),
firstname varchar(50),
lastname varchar(50),
deptID varchar(10)
);
select * from employee
create table department (
deptID varchar(10),
name varchar(50)
);
select * from department
insert into employee values
('001','hans','mittermaier','1000'),
('002','franz','freigeber','1001'),
('003','rosi','mittermaier','1001'),
('004','elsa','frozen','1002')
delete from employee where empid = '001'
select * from employee
delete from employee where empid like '%00%'
insert into department values
('1000','chefetage'),
('1001','buchhaltung'),
('1002','marketing')
select * from department
select * from employee
natural join department
select * from employee, department
(inner) left right full outer joins:
the-complete-oracle-sql-certification-course
select * from person_mitarbeiter
alter table person_mitarbeiter
add column gehalt bigint
update person_mitarbeiter
set gehalt = 1000
where berufsbezeichnung = 'Kundendienst-Monteur'
update person_mitarbeiter
set gehalt = 2000
where berufsbezeichnung = 'Service-Techniker'
update person_mitarbeiter
set gehalt = 10000
where berufsbezeichnung = 'Bauleiter'
select avg(gehalt)
from person_mitarbeiter
--funktioniert, da über den gesamten table aggregiert wird
select avg(gehalt), berufsbezeichnung
from person_mitarbeiter
--funktioniert nicht, da keine grouping function für berufsbezeichnung angegeben ist
select avg(gehalt), berufsbezeichnung
from person_mitarbeiter
group by berufsbezeichnung
--funktioniert, es werden so viele rows wie es berufsbezeichnungen gibt zurückgegeben
-- LEFT JOIN
select g.d, p.id
from group as g
left join participation as p
ON p.group_id = g.id and p.user_id = 2
-- in diesem join sind die Felder der gejointen Tabelle (participation) null, wenn die AND-Bedingung am Schluß nicht erfüllt wird.
-- felder von links (table von dem gejoint wird) für die im join-Table nichts gefunden wird werden dennoch zurückgegeben
--textfeld nach regex überprüfen
SELECT field FROM table WHERE textfeld ~ 'REGEX';
-- ~ ist ein posix Operator für
-- ~ matches case sensitive
-- ~* matches case insensitive
-- !~ does not match case sensitive
-- !~* does not match case insensitive
--select statement
SELECT field(s) FROM table WHERE condition
--update statement
UPDATE table SET field = value WHERE condition;
-- ein Update kann auch mehrere rows auf einmal erwischen, genau wie ein SELECT
-- Aufbau eines loops
for [feld1],[feld2],[feld3] in
(SELECT anything FROM table WHERE condition)
loop
SELECT anything INTO variable FROM table WHERE condition
IF variable IS NULL THEN
--do something
END IF;
-- Aufbau einer Funktion
do $$
declare
--variablen-Deklarationsteil
begin
--Ausführungsteil
JOIN "Verbund"
Ergebnistabelle, deren Datensätze Attribute beider Tabellen entsprechend einer angegebenen
Verbundbedingung enthält.
ALTER PROCEDURE [dbo].[GetCurrentR]
AS
BEGIN
SET NOCOUNT ON;
-- das resultset beinhaltet nun keine zeilennummern
CREATE TABLE #OrderIDs (ID INT NOT NULL; ServiceID INT NOT NULL);
CREATE TABLE #ServiceIDs (ID INT NOT NULL);
-- insert into database column VALUES || SELECT
INSERT INTO #OrderIDs
SELECT o.OrderID ID, [ServiceID]
-- es war wohl mal üblich Tabellen und Spalten in eckige klammern zu setzen.
INNER JOIN RepairServices rs ON gs.ServiceID = rs.ServiceID
-- inner join - schnittmenge zweier mengen.
INNER JOIN Orders o ON gs.OrderID = o.OrderID
-- select table1.name, table2.adresse
-- from table1
-- inner join table2 on table1.customerID = table2.customerID;
-- gib mir dies und jenes,
-- aus tabelle1 und tabelle2,
-- wo in beiden tabellen id übereinstimmt.
-- single line comment
/* multi
line
comment */
print messages to console from a sql function:
drop table if exists test2;
create temp table test2 (
id1 numeric,
id2 numeric,
id3 numeric,
id4 numeric,
id5 numeric,
id6 numeric,
id7 numeric,
id8 numeric,
id9 numeric,
id10 numeric)
with (oids = false);
do
$do$
declare
i int;
begin
for i in 1..100000
loop
insert into test2 values (random(), i * random(), i / random(), i + random(), i * random(), i / random(), i + random(), i * random(), i / random(), i + random());
raise notice using message = 'moin';
end loop;
end;
$do$;
sql datum lesbar machen
TO_CHAR(TO_TIMESTAMP(processtimeopt / 1000), 'DD.MM.YYYY HH24:MI:SS')
Vergleich Postgres zu MSSQl:
CREATE OR REPLACE VIEW view_name AS .... GO
IF OBJECT_ID ('view_name ', 'V') IS NOT NULL DROP VIEW pds_mwst_typ;
GO
CREATE VIEW view_name AS .....
FOR pattern_part IN SELECT part FROM regexp_split_to_table(pattern, E) AS part
LOOP
...
END LOOP;
SET @index = 1
SET @len = LEN(@pattern_part)
WHILE @index <= @len BEGIN
SET @pattern_part = SUBSTRING(@pattern, @index, 1)
...
SET @index = @index + 1
END
FOREACH replaceSubString IN ARRAY divider LOOP
SELECT replace(query_result, replaceSubString, replaceSubString || special_char_in_value_marking_char) INTO query_result;
END LOOP;
DECLARE @Enumerator TABLE(id NVARCHAR(MAX));
...
INSERT INTO @Enumerator SELECT dp_divider FROM @divider;
WHILE exists(SELECT 1 FROM @Enumerator)
BEGIN
SELECT TOP 1 @replaceSubString = id FROM @Enumerator;
SELECT @query_result = replace(@query_result, @replaceSubString, @replaceSubString + @special_char_in_value_marking_char);
DELETE FROM @Enumerator WHERE id = @replaceSubString;
END
select
from
where
group by
having
order by
ein LEFT OUTER JOIN ohne angabe einer Joinbedingung gleicht dem Kreuzprodukt?
select from where group by having order by
select from where group by having order by
select from whrere group by having order by
select from whrer group by having order by
select from where groupby having order by
sselect from where group by having order by
select from where goup by having order by
select from where group by having order by
-- creating foreign keys
Inline without mentioning the target column:
CREATE TABLE tests
(
subject_id SERIAL,
subject_name text,
highestStudent_id integer REFERENCES students
);
Inline with mentioning the target column:
CREATE TABLE tests
(
subject_id SERIAL,
subject_name text,
highestStudent_id integer REFERENCES students (student_id)
);
Out of line inside the create table:
CREATE TABLE tests
(
subject_id SERIAL,
subject_name text,
highestStudent_id integer,
constraint fk_tests_students
foreign key (highestStudent_id)
REFERENCES students (student_id)
);
As a separate alter table statement:
CREATE TABLE tests
(
subject_id SERIAL,
subject_name text,
highestStudent_id integer
);
alter table tests
add constraint fk_tests_students
foreign key (highestStudent_id)
REFERENCES students (student_id);
drop table if exists auftrag;
create table auftrag (
dbid bigint primary key,
produkt_id bigint references produkt,
kunde varchar(21),
auftragsnr bigint
);
drop table if exists los;
create table los (
dbid bigint primary key,
auftrag_id bigint references auftrag,
losgroesse bigint,
losname varchar(21)
);
drop table if exists produkt;
create table produkt(
dbid bigint primary key,
technologie_id bigint references technologie,
produktname varchar(21)
);
drop table if exists technologie;
create table technologie(
dbid bigint primary key,
technologiename varchar(21)
);
drop table if exists arbeitsplan;
create table arbeitsplan (
dbid bigint primary key,
arbeitsplannr bigint
);
drop table if exists produkt2arbeitsplan;
create table produkt2arbeitsplan (
dbid bigint primary key,
produkt_id bigint references produkt,
arbeitsplan_id bigint references arbeitsplan
);
drop table if exists arbeitsschritt;
create table arbeitsschritt(
dbid bigint primary key,
bearbeitungszeit bigint,
arbeitsschrittkennung bigint
);
drop table if exists maschine;
create table maschine(
dbid bigint primary key,
maschinenkennung bigint
);
-- show all postgres tables in current database
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND
schemaname != 'information_schema';
select from where group by having order by
select from where grou pby habing order by
select from where group by having having having order by
--ermittle children rekursiv
CREATE OR REPLACE FUNCTION getsomethingdeep (dbid BIGINT)
RETURNS TABLE (id BIGINT) AS $$
WITH RECURSIVE somethingDeep(dbid) AS (
SELECT l.dbid
FROM some_thing l
WHERE l.dbid = $1
UNION ALL
SELECT l2.dbid
FROM somethingDeep
,some_thing l2
WHERE l2.parent_id = somethingDeep
)
SELECT *
FROM somethingDeep;$$
LANGUAGE 'sql';;