SQL « Różności …

Różności …

7 maja 2014

JOINS

Zaszufladkowany do: Bez kategorii,SQL — Tagi: — Jacek @ 07:43

references = foreign key

equijoin = inner join

9 kwietnia 2014

regular expressions

Zaszufladkowany do: Programowanie,SQL — Tagi: — Jacek @ 13:34

http://docs.oracle.com/cd/E11882_01/appdev.112/e17125/adfns_regexp.htm#ADFNS232

Precedence:

1. Arithmetic (pemdas)
2. Concat
3. Comparisons
4. is null, like, between
5 not between
6. not equal
7. not
8. AND
9. OR

12 marca 2014

case decode

Zaszufladkowany do: SQL — Tagi: — Jacek @ 07:58

4 marca 2014

Oracle read consistency.

Zaszufladkowany do: Oracle,Programowanie,SQL — Tagi: , — Jacek @ 06:44

Read consistency is an automatic implementation. It keeps a partial copy of the database in
the undo segments. The read-consistent image is constructed from the committed data in the
table and the old data that is being changed and is not yet committed from the undo segment.
When an insert, update, or delete operation is made on the database, the Oracle server takes
a copy of the data before it is changed and writes it to an undo segment.
All readers, except the one who issued the change, see the database as it existed before the
changes started; they view the undo segment’s “snapshot” of the data.

The relationship between the EMPLOYEES and DEPARTMENTS tables is
an equijoin; that is, values in the DEPARTMENT_ID column in both tables must be equal.
Often, this type of join involves primary and foreign key complements.
Note: Equijoins are also called simple joins or inner joins.

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM
employees e, departments d
WHERE e.department_id = d.department_id;

SELECT d.department_id, d.department_name, l.city
FROM
departments d, locations l
WHERE
d.location_id = l.location_id
AND d.department_id IN (20, 50);

NATURAL JOIN
SELECT department_id, department_name,
location_id, city
FROM
departments NATURAL JOIN locations ;

USING
SELECT employee_id, last_name,
location_id, department_id
FROM
employees JOIN departments USING (department_id) ;

ON
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM
employees e JOIN departments d
ON
(e.department_id = d.department_id);

NONEQUIJOINS
SELECT e.last_name, e.salary, j.grade_level
FROM
employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;

SELECT e.last_name, e.salary, j.grade_level
FROM
employees e JOIN job_grades j
ON
e.salary BETWEEN j.lowest_sal AND j.highest_sal;

The outer join operator is the plus sign (+)
SELECT e.last_name, e.department_id, d.department_name
FROM
employees e, departments d
WHERE e.department_id(+) = d.department_id ;

SELECT e.last_name, d.department_id, d.department_name
FROM
employees e RIGHT OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;

Self join
SELECT worker.last_name || ‘ works for ‘
|| manager.last_name
FROM
employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

UNDO
(5 minut temu)

select * from testable as of timestamp (SysDate – 5/1440)

select * from testtable as of timestamp(’08-Aug-2011 5:00:00 PM’,'DD-MON-YYYY HH:MI:SS AM’);

flashback table testtable to before drop;

17 lutego 2014

joins

Zaszufladkowany do: Bazy danych,Oracle,Programowanie,SQL — Tagi: — Jacek @ 06:30

NATURAL JOIN

INNER JOINS

SELECT employee_id, last_name,
department_name
FROM
hr.employees JOIN hr.departments
USING (department_id) ;

SELECT l.city, d.department_name
FROM
locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;

SELF JOIN
Sometimes you need to join a table to itself. To find the name of each employee’s manager,
you need to join the EMPLOYEES table to itself, or perform a self-join.

SELECT worker.last_name emp, manager.last_name mgr
FROM
employees worker JOIN employees manager
ON
(worker.manager_id = manager.employee_id);

NONEQUIJOINS


To return the unmatched rows, you can use
an OUTER join. An OUTER join returns all rows that satisfy the join condition and also returns
some or all of those rows from one table for which no rows from the other table satisfy the join
condition.
There are three types of OUTER joins:
•LEFT OUTER
•RIGHT OUTER
•FULL OUTER

SELECT e.last_name, e.department_id, d.department_name
FROM
employees e LEFT OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;

This query retrieves all the rows in the EMPLOYEES table, which is the left table, even if there
is no match in the DEPARTMENTS table.

SELECT e.last_name, d.department_id, d.department_name
FROM
employees e RIGHT OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;

This query retrieves all the rows in the DEPARTMENTS table, which is the table at the right,
even if there is no match in the EMPLOYEES table.

SELECT e.last_name, d.department_id, d.department_name
FROM
employees e FULL OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;

This query retrieves all rows in the EMPLOYEES table, even if there is no match in the
DEPARTMENTS table. It also retrieves all rows in the DEPARTMENTS table, even if there is no
match in the EMPLOYEES table.

——— CARTESIAN PRODUCT ————

SELECT last_name, department_name
FROM
employees CROSS JOIN departments ;

13 lutego 2014

group_by

Zaszufladkowany do: Bazy danych,Programowanie,SQL — Tagi: — Jacek @ 06:57

group by

AVG
COUNT
MAX
MIN
STDDEV
SUM
VARIANCE

12 lutego 2014

NVL NULL, to_date, to_char

Zaszufladkowany do: Oracle,SQL — Tagi: , — Jacek @ 06:47

to_char

23 listopada 2011

SQL – duplicates

Zaszufladkowany do: SQL — Tagi: — Jacek @ 12:06

Here’s a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:

SELECT email,
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

You could also use this technique to find rows that occur exactly once:

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )
Źródło: http://www.petefreitag.com/item/169.cfm

18 października 2011

(SQL) select with translation table

Zaszufladkowany do: Programowanie,SQL — Tagi: — Jacek @ 10:03

CREATE TABLE HR.JS_TAB1(MIASTO VARCHAR2(20), ULICA VARCHAR2(20));

SELECT * FROM HR.JS_TAB1;

CREATE TABLE HR.JS_TRANS1(ULICA_Z VARCHAR2(30), ULICA_NA VARCHAR(30));

SELECT * FROM HR.JS_TRANS1;

INSERT INTO HR.JS_TAB1 VALUES(‘GDYNIA’,'NAGIETKOWA’);
INSERT INTO HR.JS_TAB1 VALUES(‘GDYNIA’,'SWIETOJANSKA’);
INSERT INTO HR.JS_TAB1 VALUES(‘GDANSK’,'KOLOBRZESKA’);
INSERT INTO HR.JS_TAB1 VALUES(‘GDANSK’,'MALBORSKA’);

INSERT INTO HR.JS_TRANS1 VALUES (‘KOLOBRZESKA’,'CHLOPSKA’);
INSERT INTO HR.JS_TRANS1 VALUES (‘SWIETOJANSKA’,'WLADYSLAWA IV’);

SELECT MIASTO, ULICA FROM HR.JS_TAB1;

SELECT MIASTO,
Nvl((SELECT ULICA_NA FROM HR.JS_TRANS1 TRAN1 WHERE TAB1.ULICA = TRAN1.ULICA_Z),TAB1.ULICA)
FROM HR.JS_TAB1 TAB1;

28 grudnia 2010

Oracle notes – PL/SQL basics

Zaszufladkowany do: Bazy danych,Oracle,PL/SQL,SQL — Tagi: — Jacek @ 12:16

PL/SQL Procedural Language SQL
anonymous blocks – bez nazwy
procedures/functions – stored in data dictionary
packages – zgrupowane procedury i funkcje

Data Types
- scalar (binary_integer, integer, string …)
- composite (nested tables, varrays, record …)

r employees%rowtype;

nested tables of numbers:

TYPE – określamy jakiego jest typu
linia 5 – konstruktor –  przypisujemy konstruktor do zmiennej (na razie on nie istnieje)
tworzymy go przez extend. W lini 7 tworzenie slotu dla zmiennych.

- reference – procedury mogą odwoływać się do refcursors.

Anonymous blocks:

od DECLARE do BEGIN – deklarujemy zmienne
od BEGIN do EXCEPTION – kod
:=” (przypisanie wartości)
anchor data type – można przypisać typ danych do zmiennej na podstawie tabeli (linia 6)

Procedures:

tu są nasze procedury
select * from user_source;
select text from user_source where name = ‘nazwa_procedury’;

Zamiast DECLARE (jak w anoymouns blocks) jest AS. Pomiędzy AS a BEGING deklarujemy zmienne.
Wywołanie takiej procedury:
SQL> BEGIN
SQL> give_raises(1.5);
SQL> END;
SQL> /
lub:
exec give_raises(1.5);

CURSOR – to jest SELECT wewnątrz bloku PL/SQL który może zwrócić dużą liczbę rekordów.
Jeżeli SELECT może mieć więcej niż jeden wynik trzeba używać CUROSR’ów.

CURSOR – deklaracja kursora – i następnie SELECT
Cursor zostaje wykonane w kodzie (po BEGIN)
rec – nazwa zmiennej IN in nazwa kursora
count – metoda należąca do nested tables … count równe 1,2,3 …

Te wartości które mamy w tablicy możemy przkazać np do innej procedury.

Cursor teraz zawiera employee_id, hire_data
Aby zrobic updaty na cursorze  trzeba go zrobic FOR UPDATE
WHER CURRENT OF EMP_CUR – praca na aktualnych danych w kursorze

LOOPS

EXCEPTION HANDLING

crate or replace procedure add_emp(eid number, last varchar2, first varchar2, email varchar2, hire_date date)
as
hire_date_excepition exception;
email_exception exception;
begin
if hire date > sysdate then
raise hird_date_exception;
end if;
if email is not null and email not like ‘%@%.%’ then
raise email_exception;
end if;

— jakis kod dodajacy pracownika

exception
when hire_date_exception then
dbms_output.put_line(‘wrong date’);
when email_exception then
dbms_output.put_line(‘wrong email format’);
end;
/

exception – tu się zaczyna blok exception
Wywołujemy procedurę:

exec add_emp(500,’Joe’,'Jule’,'joe@serwer.com’,sysdate + 2)

i zwraca błąd, że jest zła data.

Oracle notki, constraints, sequences, views

Zaszufladkowany do: Bazy danych,Oracle,PL/SQL,SQL — Tagi: — Jacek @ 11:56
alter table person add constraint pk_person primary key(id);
compound primary key – złożony – na więcej niż jednej kolumnie.
create table citeis(city varchar(30), state char2(2), constraint pk_cities primary key(city,state));
obydwie kolumny będą widziane jako NOT NULL – ale to wcale nie znaczy że są primary_keys

select * from USER_CONSTRAINTS;
select * from DBA_CONSTRAINTS;

aby dowiedzieć się jakie są naprawdę constraints nałożone na daną tebelę.
select * from USER_CONS_COLUMNS;
select * from DBA_CONS_COLUMNS;

aby sprawdzić na jakich kolumnach są nałożone constraints.
select constraint_name, column_name, position
from USER_CONS_COLUMNS
where constraint_name in(select constraint_name from user_constraints where table_name = ‘CITIES’);

sprawdzenie domyśnej tablespace:
slect default_tablespace from user_users;
slect default_tablespace from dba_users;

create table as statement
create table COLORADO_PERSON as select * from PERSON where st = ‘CO’;

select * from USER_TAB_COLUMNS; – aby dowiedzieć się jak są zdefiniowane kolumny w danej tabeli;

UNIC CONSTRAINT

taki sam jak primary key ale dozwolone są NULL
alter table PERSON add constraint UK_PERSON_USERNAME unique(USERNAME);
alter table PERSON add constraint ck_person_gender check(gender in(‘M’,'F’));

FOREIGN CONSTRAINT – pozwala znaleźć releacje pomiędzy dwoma tablicami

jedna tablica to child druga to parent
dodajemy foreign constraint to child table:
alter table person add constraint fk_person_cities foreign key(city,state) references cities(city, state);

tabela cities jest tu tabelą parent i tu są zdefiniowane miasta i stany. Przy próbie wpisu to tabeli person miasta i stanu którego nie ma w tabeli cities otrzymamy błąd.

select a.constraint_name, constraint_type, column_name, r_constraint_name
from user_constraints a, user_cons_clumns b
where a.constraint_name = b.constraint_name
and a.table_name = ‘PERSON’;

Wyłączenie danego CONSTRAINT:
alter table PERSON disable constraint fk_person_cities;
alter table PERSON enable constraint fk_person_cities;

alter table users add constraint fk_users_person
foreign key(username) references person(username)
deferrable initially deferred;

SEQUENCE

autoincrement, przechowuje liczby
create sequence username_seq start with 10 increment by 1;
select * from user sequences;
update users set id = username_seq.nextval;
select username_seq.currval from dual;

VIEWS:

create or replace view person_view(name) as
select last || ‘,’ || first
from person
order by last, first;
create or replace view person_view as
select last,first,city,st
from person
order by last,first;

W drugim wypadku kolumny dokładnie odpowiadają nazwom kolumn w tabeli dlatego ten widok można wykorzystać do updateowania tabeli.

select * from USER_VIEWS;
select * from DBA_VIEWS;
select * from DBA_OBJECTS;

Oracle notki – installation.

Zaszufladkowany do: Bazy danych,Oracle,PL/SQL,SQL — Tagi: — Jacek @ 11:29
database configuration assistant:
c:> dbca (w windowsach)
aby zapisać konfigurację bazy danych w windowsach i ewentualnie puszczać tworzenie instancji automatycznie:
c:\database> setup -record -destinationFile c:\database\basic.rsp (jest to tzw. response file)
uruchomi się instaler, ale na samym końcu nie nalży instalować tylko dać cancel
potem:
c:\database> setup -silent -responseFile c:\\database\basic.rsp
tworzenie bazy danych od podstaw:
najpierw należy stworzyć serwis:
oradim -new -sid test
potem należy utworzyć plik inittest.ora
#inittest.ora
DB_NAME=test
INSTANCE_NAME=test.johnson
CONTROL_FILES = (“c:\oracle\ctrlfiles\control01.ctl”, “c:\oracle\ctrlfiles\control02.ctl”, “c:\oracle\ctrlfiles\control03.ctl”)
BACKGROUND_DUMP_DEST = c:\oracle\product\10.2.0\admin\bdump
USER_DUMP_DEST = c:\oracle\product\10.2.0\admin\udump
DB_FILES = 1000
UNDO_MANAGEMENT = auto
UNDO_TABLESPACE=undo
SHARED_POOL_SIZE=75m  (jest używane do przechowywanie poleceń sql)
potem uruchamiamy
sqlplus / as sysdba
sql> startup nomount pfile=’c:\oracle\product\10.2.0\db_1\database\inittest.ora’
pfile to parameter file
potem należy stworzyć plik do utworzenia samej bazy danych: create.sql
– create.sql
CREATE DATABASE test
MAXLOGFILES 32
MAXDATAFILES 500
DATAFILE ‘c:\baza\system01.dbf’ SIZE 325M REUSE          (reuse – znaczy, że jeżeli plik już jest to zostanie nadpisany)
SYSAUX DATAFILE ‘c:\baza\sysaux01.dbf’ SIZE 300M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
UNDO TABLESPACE “UNDO” DATAFILE ‘c:\baza\undo01.dbf’
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
logfile ‘c:\baza\redo01.log’ size 100m reuse,
‘c:\baza\redo02.log’ size 100m reuse,
‘c:\baza\redo03.log’ size 100m reuse;
potem:
sql> @create.sql

Oracle – notki, SQL, PL/SQL

Zaszufladkowany do: Bazy danych,Oracle,PL/SQL,Programowanie,SQL — Tagi: , , — Jacek @ 11:16
sql> host – aby dostać się do promptu hosta
c:> echo %oracle_sid% lub echo $oracle_sid$
c:> exit
sql>
po zalaogowaniu można wydać komendę:
sql> define
i wyświetlą się parametry informujące o tym jako kto jesteśmy podłączeni
będąc podłączonym można podłączyć się jako inny user
sql> connect sys as sysdba
Sprawdzenie jakie tabele są w schemacie użytkownika:
sql> select table_name from user_tables;
sql> desc ps$ (opis tabeli)
sql> set pagesize 200
sql> / (ponowne uruchomienie polecenia z buforu)
sql> l lub list – wyswietla to co jest w buforze
w buforze jest tylko ostatnie polecenie
jeżeli chcemy coś zmienić w buforze:
sql> 2
podajemy numer linii w której chcemy zrobić zmianę
sql> c/name/surename
i określamy (c – change) co na co chcemy zamienić
SQL – Structured Query Language
określenie w jakim formacie ma zostać wyświetlana data;
sql> alter session set nls_date_format=’mm/dd/yyyy’;
można określić jak inne dane mają być wyświetlane:
sql> col sal fromat $999,999.00
sql> col ename heading ‘Employee|Name’
Jeżeli chcemy coś dodać do bufora:
sql> 1
sql> a ,nazwisko
aby format danej kolumny był taki jak jakiejś innej można:
sql> col comm like sal heading “Commision”
aby kolumna była szersza:
sql> col job format a15
wyszyszczenie formatowania:
sql> col comm clear
lub
sql> clear columns
jeżeli chcemy zachować ostatnią komendę z bufora:
sql> save plik.sql
uruchomienie
sql> @plik.sql
zapis wyników:
sql> spool plik.txt
sql> spool off
sql> host
c:> type plik.txt
c:> exit
sql> spool plik.txt append
można robić zmiany bufora w edytorze:
ed
i wyświetlić/zdefiniować edytor
define _editor
define _editor=c:\notepad
Jeżeli chcemy wstawić zmienną do zapytania
… where job=’&p’
sql> undefine p
aby wyświetlać po stronie:
sql> set pause on
sql> set pagesize 35
Plik z ustawieniami środowiska startowy:
SQLPATH=c:\oracle\product
tam trzeba umieścić plik:
login.sql
sql> help index
sql> help define
zmiana hasła
sql> passw user1
Na serwerze aplikacji można uruchomić isqlplus, który pozwala na dostęp do bazy danych przez przeglądarkę internetową:
c:> isqlplusctl start
potem w przeglądarce
http://localhost:5560/isqlplus

Strona startowa: www.jaceksen.pl