SQL Lecture script

create table TABLE_NAME
    [(column_name [colum integfrity rule], column_name ...., ])]
    as select COLUMN_NAME [, column_name ... , ...] from TABLE_NAME;

drop table TABLE_NAME;

select user from dual;

select rownum, name from players;

select sysdate - pen_date from penalties;

select to_date('2022-01-25', 'YYYY-MM-DD') from dual;

select to_char(sysdate, 'DD-MM-YY') from dual;

select count(*) from dual; /* returns: 1*/

select * from dual;

/* Amount of players that are from Stratford */
select count(*) from players where TOWN = 'Stratford';

/* highest penalty */
select max(AMOUNT) from PENALTIES;

select PEN_DATE from PENALTIES;

select MCTOM.PENALTIES.PEN_DATE from MCTOM.PENALTIES;

select distinct PLAYERNO from PLAYERS

/*all players that are playing professionally*/
select * from PLAYERS where LEAGUENO is not null;

select emp.ENAME, dept.DNAME from EMP, DEPT
where EMP.DEPTNO = DEPT.DEPTNO;

select d.DEPTNO, e.ENAME, d.DNAME from EMP e, DEPT d
where e.DEPTNO = d.DEPTNO;

select EMP.ENAME, DEPT.DNAME from EMP NATURAL join DEPT;

/* it can be also be written as: */
select EMP.ENAME, DEPT.DNAME from EMP INNER join DEPT on EMP.DEPTNO = DEPT.DEPTNO;

select * from EMP e inner join DEPT d on e.DEPTNO = d.DEPTNO;

/* 1. Möglichkeit */
select distinct pl.PLAYERNO, pl.NAME
from PLAYERS pl inner join PENALTIES P
    on pl.PLAYERNO = P.PLAYERNO

/* 2. Möglichkeit */
select PLAYERNO, NAME
from PLAYERS
where exists (
    select * from PENALTIES
    where PLAYERS.PLAYERNO = PENALTIES.PLAYERNO);

select PLAYERNO, NAME
from PLAYERS
where PLAYERNO in (select PLAYERNO from PENALTIES);

/* Gesamtsumme */
select *
from (
    select pl.PLAYERNO, NAME, sum(P.AMOUNT) as AMOUNT
    from PLAYERS pl inner join PENALTIES P
        on pl.PLAYERNO = P.PLAYERNO
    group by pl.PLAYERNO, pl.NAME
    order by AMOUNT desc)
where ROWNUM <= 4;

/* Einzelne Strafen */
select * from (
    select pl.PLAYERNO, pl.NAME, P.AMOUNT
    from PLAYERS pl inner join PENALTIES P
        on pl.PLAYERNO = P.PLAYERNO order by AMOUNT desc)
where ROWNUM <= 4;

select *
from PARTS
connect by SUB = SUPER;

select *
from PARTS
connect by SUB = SUPER
start with SUPER = 'P3';

select rownum, level, SUB, SUPER, PRICE
from PARTS
connect by prior SUB = SUPER
start with SUPER = 'P3';

select lpad(' ', 8 * (level -1)) || level || '-' || SUPER || '-' || SUB
from PARTS
connect by prior SUB=SUPER
start with SUPER = 'P3';

UPDATE table_name SET column_name1 = expression | subquery
[, col_name2 = expression | subquery, ...]
[WHERE condition]

update PARTS
set PRICE = 100
where upper(SUB) = 'P5';

update PARTS
set PRICE = PRICE * 1.1
where upper(SUB) = 'P5';

update PARTS
set PRICE = PRICE * 0.9
where PRICE > 60;

update PARTS
set PRICE = PRICE * 1.2
where PRICE < (
    select avg(PRICE)
    from PARTS);

column_name .... [CONSTRAINT constraint_name] NOT NULL

create table teams (
    teamno number(2) constraint pk_teams primary key,
    playerno number(4),
    division varchar2(6)
);

create table teams (
    teamno number(2),
    playerno number(4),
    division varchar2(6),
    constraint pk_teams primary key(teamno)
);

...
SEX char(1) check(SEX in ('M', 'F', 'X')),
DATE_OF_BIRTH date check(year_of_birth <= year_joined),
...

alter table TABLE_NAME drop constraint CONSTRAINT_NAME;

select * from cat
/* oder */
select table_name from user_tables

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

SELECT cust_last_name,
   CASE credit_limit WHEN 100 THEN 'Low'
   WHEN 5000 THEN 'High'
   ELSE 'Medium' END
   FROM customers;