Emrah UYSAL www.emrahuysal.com 1

ORACLE DERS 3
SUBQUERIES(ALT SORGULAR)
Alt sorgular yani subqueries bir alt sorgudan dönen sonuçların üst tarafta bulunan sql cümlesine
referans olması ile oluşur.
Alt sorgular bize şu durumlarda lazım olur,örneğin x isimli bir personelin maaşından daha yüksek
maaşlı bir personelin bilgini bulmada,ortalama değerin altında yada üstündeki değerleri bulmada….
Syntax:
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
Alt sorgu mantığında ilk önce alt sorgu çalışır daha sonra üst sorgu çalışır.
SQL > SELECT last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE last_name = ’Abel’);
Yukarıdaki sorgu Abel’in maşından daha yüksek maaşlı personelin soyadlarını listelemektedir.
Single Row Subquery
Bu tür subquerylerde alt sorgudan tek kayıt döner ve karşılaştırma olarak aşağıdaki operatörler
kullanılır.
Emrah UYSAL
www.emrahuysal.com
1
SQL > SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141);
SQL > SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary >
(SELECT salary
FROM employees
WHERE employee_id = 143);
SQL > SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);
SQL > SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
Emrah UYSAL
www.emrahuysal.com
2
SQL > SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
ERROR at line 4:
ORA-01427: single-row subquery returns more than
one row
SQL > SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = ’Haas’);
no rows selected
MULTIPLE ROW SUBQUERY
Bu tür sorgular ise subqueryden dönen birden fazla kaydın üst sorgu atarfından yorumlanması ile
oluşur.Bu tür sorgularda aşağıdaki operatörler kullanılır.
SQL > SELECT last_name, salary, department_id
FROM employees
WHERE salary IN
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
Emrah UYSAL
www.emrahuysal.com
3
Aşağıdaki sorgu aslında mantık olarak yukarıdaki sorgu ile aynı işlevi görür,ama yukarıdaki sorguyu
kullanmak daha akıllıcadır.
SQL > SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (2500, 4200, 4400, 6000, 7000, 8300, 8600, 17000);
ANY OPERATÖRÜ
SQL > SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = ’IT_PROG’)
AND job_id <> ’IT_PROG’;
<ANY’nin anlamı maximumdan daha düşük
>ANY’nin anlamı minimumdan daha büyük.
ALL OPERATÖRÜ
SQL > SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL
(SELECT salary
FROM employees
WHERE job_id = ’IT_PROG’)
AND job_id <> ’IT_PROG’;
>ALL’nin anlamı maximumdan daha büyük
<ALL’nin anlamı minimumdan daha düşük.
Emrah UYSAL
www.emrahuysal.com
4
ALT SORGULARDAKİ NULL OPERATÖRÜ
Eğer alt sorgulardan null dönüyorsa üst tarafı negatif olarak etkileyeceği için bazen olumsuz sonuçlar
alabilemkteyiz.
SQL > SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM employees mgr);
no rows selected
Bu sorunu aşmak istiyorsak;
SQL > SELECT last_name FROM employees
WHERE employee_id NOT IN
(SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL);
TABLO MANİPLASYONLARI
İNSERT OPERATÖRÜ
İnsert operatörü genellikle tabloya yeni kayıt eklemek için kullanılmaktadır.
SYNTAX:
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
Emrah UYSAL
www.emrahuysal.com
5
SQL > INSERT INTO departments(department_id, department_name,
manager_id, location_id)
VALUES (70, ’Public Relations’, 100, 1700);
1 row created.
SQL > INSERT INTO departments (department_id,
department_name )
VALUES (30, ’Purchasing’);
1 row created.
SQL > INSERT INTO departments
VALUES (100, ’Finance’, NULL, NULL);
1 row created.
Yukarıdaki örnekte görüeleceği gibi eğer tablonun bütün alanları için kayıt eklenecekse tablo için kolon
isimlerini yazmaya gerek yoktur.
SQL > INSERT INTO employees (employee_id,
first_name, last_name,
email, phone_number,
hire_date, job_id, salary,
commission_pct, manager_id,
department_id)
VALUES (113,
’Louis’, ’Popp’,
’LPOPP’, ’515.124.4567’,
SYSDATE, ’AC_ACCOUNT’, 6900,
NULL, 205, 100);
1 row created.
SQL > SELECT employee_id, last_name, job_id, hire_date, commission_pct
FROM employees
WHERE employee_id = 113;
SQL > INSERT INTO employees
VALUES (114,
’Den’, ’Raphealy’,
’DRAPHEAL’, ’515.127.4561’,
TO_DATE(’FEB 3, 1999’, ’MON DD, YYYY’),
’AC_ACCOUNT’, 11000, NULL, 100, 30);
1 row created.
Emrah UYSAL
www.emrahuysal.com
6
BAŞKA BİR TABLODAN VERİ ÇEKMEK
Eğer başka bir tablodan veri almak istiyorsak aşağıdaki gibi sql cümlelerini yazmak yeterlidir.
SQL > INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE ’%REP%’;
4 rows created.
UPDATE OPERATÖRÜ
Update operatörü bir tablodaki kayıtları güncellemek yani yenileri ile değiştirmek için kullanılır.
SYNTAX:
UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];
SQL > UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
1 row updated.
SQL > UPDATE copy_emp
SET department_id = 110;
22 rows updated.
SQL > UPDATE employees
SET job_id = (SELECT job_id
FROM employees
WHERE employee_id = 205),
salary = (SELECT salary
FROM employees
WHERE employee_id = 205)
WHERE employee_id = 114;
1 row updated.
SQL > UPDATE copy_emp
SET department_id = (SELECT department_id
FROM employees
WHERE employee_id = 100)
WHERE job_id = (SELECT job_id
FROM employees
WHERE employee_id = 200);
1 row updated.
Emrah UYSAL
www.emrahuysal.com
7
Bazen iki tablo arasında constrant ilişkisi varsa sistem bu durumlara kızabilir.Bu durumlara dikkat
etmek gereklidir.
SQL > UPDATE employees
SET department_id = 55
WHERE department_id = 110;
UPDATE employees
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_DEPT_FK)
violated - parent key not found
DELETE OPERATÖRÜ
Delete operatörü ise tablodan kayıt silmek için kullanılmaktadır.
SYNTAX:
DELETE [FROM] table
[WHERE condition];
SQL > DELETE FROM departments
WHERE department_name = ’Finance’;
1 row deleted.
SQL > DELETE FROM copy_emp;
22 rows deleted.
SQL > DELETE FROM departments
WHERE department_id IN (30, 40);
2 rows deleted.
SQL > DELETE FROM employees
WHERE department_id =
(SELECT department_id
FROM departments
WHERE department_name LIKE ’%Public%’);
1 row deleted.
Update cümleciğinde olduğu gibi delete cümleciğinde de constraintler sorunlu olabilir.
SQL > DELETE FROM departments
WHERE department_id = 60;
DELETE FROM departments
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK)
violated - child record found
Emrah UYSAL
www.emrahuysal.com
8
INSERT CÜMLECĞİNDE SUBQUERY KULLANIMI
SQL > INSERT INTO
(SELECT employee_id, last_name,
email, hire_date, job_id, salary,
department_id
FROM employees
WHERE department_id = 50)
VALUES (99999, ’Taylor’, ’DTAYLOR’,
TO_DATE(’07-JUN-99’, ’DD-MON-RR’),
’ST_CLERK’, 5000, 50);
1 row created.
SQL > Select employee_id, last_name, email, hire_date,
job_id, salary, department_id
FROM employees
WHERE department_id = 50;
CHECK OPTION
SQL > INSERT INTO (SELECT employee_id, last_name, email,
hire_date, job_id, salary
FROM employees
WHERE department_id = 50 WITH CHECK OPTION)
VALUES (99998, ’Smith’, ’JSMITH’,
TO_DATE(’07-JUN-99’, ’DD-MON-RR’),
’ST_CLERK’, 5000);
INSERT INTO
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
Yukarıdaki sorguda dikkat ederseniz department_id = 50 ve with check option kullanıldığı için subqery
dönen sonuçlar vardır fakat select listesinde department_id yoktur bu durumda department_id değeri
Null oalrak dönebilir.Bu durumu engellemek için kullanılır.Hemde department_id değeri olsa bile değeri
muhakka 50 olmalıdır.
Emrah UYSAL
www.emrahuysal.com
9
DEFAULT OPERATÖRÜ
Tabloyu yaratırken bazı alanlar için default bir değer verilir.Tabloya her kayıt girildiğinde bu değer
kullanılsın istenebilir.
Böyle bir durumu kullanmak istiyorsak;
SQL > INSERT INTO departments
(department_id, department_name, manager_id)
VALUES (300, ’Engineering’, DEFAULT);
SQL > UPDATE departments
SET manager_id = DEFAULT WHERE department_id = 10;
MERGE OPERATÖRÜ
Merge operatörüde kullanışlı operatörlerden biridir.Bir tablo update’inde eklenecek kayıt tabloda varsa
update eder,yoksa insert eder.Yani update ve insert işlemini bir arada yapar.
SYNTAX:
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
SQL > MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
c.email = e.email,
c.phone_number = e.phone_number,
c.hire_date = e.hire_date,
c.job_id = e.job_id,
c.salary = e.salary,
c.commission_pct = e.commission_pct,
c.manager_id = e.manager_id,
c.department_id = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
e.email, e.phone_number, e.hire_date, e.job_id,
e.salary, e.commission_pct, e.manager_id,
e.department_id);
Emrah UYSAL
www.emrahuysal.com
10
SQL > SELECT *
FROM COPY_EMP;
no rows selected
SQL > MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
...
WHEN NOT MATCHED THEN
INSERT VALUES...;
SQL > SELECT *
FROM COPY_EMP;
20 rows selected.
DATABASE TRANSACTIONS
Database transactionsları commi yada rollback olarak adlandırılır.Yaptığımız işlemlerin kalıcı yada
yanlışlıkla yapılan ilemlerin geri alınması için kullanılır.Aynı şekilde save point mantığı kullanılarakta
yapılan işlemlerin belli sırada saklanması öngörülebilir.
UPDATE...
SAVEPOINT update_done;
Savepoint created.
INSERT...
ROLLBACK TO update_done;
Rollback complete.
Emrah UYSAL
www.emrahuysal.com
11
ROLLBACK VE COMMİT ÖNCESİ
•
•
Kullanıcı yaptığı değişiklikleri görebilir fakat diğer kullanıcılar yapılan değişiklikleri
göremeyecektir.
Yapılan değişiklikler tabloda Lock oluşturacaktır.Başka kullancılar bu tablo üzerinde değişiklik
yapmak istediklerinde Lock Error alacaklardır
COMMİT SONRASI
•
•
•
Kullanıcının yaptığı değişiklikleri tüm kullancıılar görebileceklerdir.
Tablo üzerinde bulunan lock kalkacaktır.
Bütün savepointler silinecektir.
SQL > DELETE FROM employees
WHERE employee_id = 99999;
1 row deleted.
SQL > INSERT INTO departments
VALUES (290, ’Corporate Tax’, NULL, 1700);
1 row inserted.
SQL > COMMIT;
Commit complete.
SQL > DELETE FROM departments
WHERE department_id IN (290, 300);
2 rows deleted.
SQL > UPDATE copy_emp
SET department_id = 80
WHERE employee_id = 206;
1 row updated.
SQL > COMMIT;
Commit Complete.
ROLLBACK SONRASI
•
•
Kullanıcının yaptığı değişiklikleri tüm kullancıılar görebileceklerdir.Yani aslında data
değişiklikleri geri alınacaktır.
Tablo üzerinde bulunan lock kalkacaktır.
SQL > DELETE FROM copy_emp;
22 rows deleted.
SQL > ROLLBACK;
Rollback complete.
SQL > DELETE FROM test;
Emrah UYSAL
www.emrahuysal.com
12
25,000 rows deleted.
SQL > ROLLBACK;
Rollback complete.
SQL > DELETE FROM test
WHERE id = 100;
1 row deleted.
SQL > SELECT *
FROM test
WHERE id = 100;
No rows selected.
SQL > COMMIT;
Commit complete.
CREATE TABLE KOMUTU
Syntax:
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);
SQL > CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
Table created.
SQL > DESCRIBE dept;
SQL > CREATE TABLE hr.admin_emp (
empno
NUMBER(5) PRIMARY KEY,
ename
VARCHAR2(15) NOT NULL,
ssn
NUMBER(9) ENCRYPT,
job
VARCHAR2(10),
mgr
NUMBER(5),
hiredate DATE DEFAULT (sysdate),
photo
BLOB,
sal
NUMBER(7,2),
hrly_rate NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),//11 G Virtual column
comm
NUMBER(7,2),
deptno
NUMBER(3) NOT NULL
CONSTRAINT admin_dept_fkey REFERENCES hr.departments
(department_id));
Oracle ‘da table yapıları 2 şekilde olmaktadır,
Emrah UYSAL
www.emrahuysal.com
13
İlk olarak kullanıcya ait olan “User Tables”,bu tablo yapısı kullanıcıya ait olan verileri depolar,ikinci
olarak ise “Data Dictionary” ler mevcuttur bunlar ise oracle ilk kurulduğunda oluşan sistem ve
kullanıcılar hakkıda işlevsel anlamda bize bilgi veren tablolardır.
Data dictionary viewları aşağıdaki gibidir.
SQL > SELECT table_name
FROM user_tables ;
SQL > SELECT DISTINCT object_type
FROM user_objects ;
SQL > SELECT *
FROM user_catalog ;
VERİ TİPLERİ
Emrah UYSAL
www.emrahuysal.com
14
•
•
•
•
Long alanlar tablo eğer subquery ile yaratıldıysa kopyalamazlar,
Long kolonlar group by ve order by yapısında kullanımazlar,
Her tabloda ancak 1 tane long alan kullanılabilir,
Long kolonlar için constraint tanımlanmaz,
SUBQUERY İLE TABLO YARATMAK
Syntax:
CREATE TABLE table
[(column, column...)]
AS subquery;
SQL > CREATE TABLE dept80
AS
SELECT employee_id, last_name,
salary*12 ANNSAL,
hire_date
FROM employees
WHERE department_id = 80;
Table created.
SQL > DESCRIBE dept80;
ALTER TABLE STATEMENT
•
•
•
•
Tabloya yeni bir kolon eklemek için,
Mevcut bir kolonu modifiye etmek için,
Yeni bir kolon için default value atamak için,
Kolon silmek için,
Kullanılabilmektedir.
ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]...);
Emrah UYSAL
www.emrahuysal.com
15
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);
ALTER TABLE table
DROP (column);
SQL > ALTER TABLE dept80
ADD (job_id VARCHAR2(9));
Table altered.
SQL > ALTER TABLE dept80
MODIFY (last_name VARCHAR2(30));
Table altered.
SQL > ALTER TABLE dept80
DROP COLUMN job_id;
Table altered.
ALTER TABLE table
SET UNUSED (column);
Yada
ALTER TABLE table
SET UNUSED COLUMN column;
ALTER TABLE table
DROP UNUSED COLUMNS;
SQL > ALTER TABLE dept80
SET UNUSED (last_name);
Table altered.
SQL > ALTER TABLE dept80
DROP UNUSED COLUMNS;
Table altered.
TABLO DROP
•
•
•
•
Tablo içindeki tüm datalar ve tablonun structer’ı drop edilir,
Bekleyen tüm transactionlar commit edilir,
İlgili indexler drop olur,
Drop table statementtan rollback yapılamaz,
SQL > DROP TABLE dept80;
SQL > DROP TABLE dept80 CASCADE CONSTRAINTS;
SQL > DROP TABLE admin_emp_dept PURGE; //Tablo çöp kutusuna atılmaz
Emrah UYSAL
www.emrahuysal.com
16
Table dropped.
RENAME TABLE
•
•
ilgili objelerin isimlerini değiştirmek için kullanılır,
bunu yapabilmek için tablonun sahibi olmak gerekir,
SQL > RENAME dept TO detail_dept;
SQL > ALTER TABLE test1 RENAME TO test;
Table renamed.
SQL > ALTER TABLE test RENAME COLUMN col2 TO description;
TABLE TRUNCATE
•
•
•
Tablodaki tüm kayıtlar hızlı bir şekilde silinir,
Tabloda kullanılan alan serbest bırakılır,
Rollback yapılamaz,
SQL > TRUNCATE TABLE detail_dept;
SQL > TRUNCATE CLUSTER detail_dept REUSE STORAGE;
Table truncated.
TABLE COMMENT
Tablolar için ileriki zamanlarda bu tablo ne işe yarıyordu düşüncesiyle yapılan işlemdir,
Syntax:
COMMENT ON TABLE table | COLUMN table.column
IS 'text';
SQL > COMMENT ON TABLE employees
IS ’Employee Information’
;
Comment created.
ÖRNEK UYGULAMALAR
SORU ? Maaşı en fazla olan personelin emsanno, ad, soyad ve maas alanlarını listeleyiniz.
SQL > SELECT EMSANNO, AD, SOYAD, MAAS FROM PERSONEL WHERE MAAS = (SELECT MAX
(MAAS) FROM PERSONEL);
SORU ? Her departmanda en az maaş alan personelin emsanno, ad, soyad ve maaş
alanlarını listeleyiniz.
SQL > SELECT DEPARTMAN, EMSANNO, AD, SOYAD, MAAS FROM PERSONEL WHERE MAAS IN
(SELECT MIN(MAAS) FROM PERSONEL GROUP BY DEPARTMAN);
Emrah UYSAL
www.emrahuysal.com
17
SORU ? 400 numaralı departmanda çalışan personelin ortalama maaşından daha fazla
ortalama maaşı olan departmanları listeleyiniz.
SQL > SELECT DEPARTMAN, AVG (MAAS) FROM PERSONEL GROUP BY DEPARTMAN HAVING
AVG(MAAS) > (SELECT AVG (MAAS) FROM PERSONEL WHERE DEPARTMAN = 400);
SORU ? 100 numaralı departmanda çalışan herhangi bir personelden daha fazla maaş alan
personelin emsanno, ad, soyad ve maas alanlarını listeleyiniz.
SQL > SELECT EMSANNO, AD, SOYAD, MAAS FROM PERSONEL WHERE MAAS > SOME (SELECT
DISTINCT MAAS FROM PERSONEL WHERE DEPARTMAN = 100);
SORU ? 100 numaralı departmanda çalışan her bir personelden daha fazla maaş alan
personelin emsanno, ad, soyad ve maas alanlarını listeleyiniz.
SQL > SELECT EMSANNO, AD, SOYAD, MAAS FROM PERSONEL WHERE MAAS > ALL (SELECT
DISTINCT MAAS FROM PERSONEL WHERE DEPARTMAN = 100);
Yada
SQL > SELECT EMSANNO, AD, SOYAD, MAAS FROM PERSONEL WHERE MAAS > (SELECT
MAX(MAAS) FROM PERSONEL WHERE DEPARTMAN = 100);
Emrah UYSAL
www.emrahuysal.com
18