Etiketler
Veritabanı Yönetim Sistemleri Dersi 2015-2016
Laboratuvar SQL çalışmaları
–Tüm personel kayıtlarını seçiniz.
SELECT*
FROM employees ;
–Özel kolonların seçimi, birleştirme ve artimetik işlemler
–Personelin adı ve soyadını isim kolonunda gösteriniz.
–Toplam maaşını bularak gösteriniz.
SELECT first_name ||’ ‘ ||last_name isim,
salary,
commission_pct,
salary+commıssıon_pct “Toplam Maaş”
FROM employees ;
–-Tarih aritmetiği (gün üzerinedir.)
SELECT TRUNC(sysdate-hire_date),
hire_date+10,
sysdate,
TRUNC(sysdate-TO_DATE(’01/01/1980′)),
TO_CHAR(sysdate,’Month’)
FROM employees;
–Mükerrer kayıtları eleme
–Count kullanımı
select distinct first_name,last_name from employees;
–Personel adetlerini bulunuz.
–Count kullanımı
SELECT COUNT( *) FROM employees;
–Toplam tekil değer sayısının count ile bulunması
SELECT COUNT(DISTINCT first_name||last_name) FROM employees;
–Özel bir kolon ve geri kalan kolonların tümüyle seçimi.
select salary,employees.* from employees;
–Kısıtlama yapmak
–İsmi ‘Ahmet’ olan kişileri bulunuz.
select * from employees
where first_name = ‘Ahmet’;
–Bölümü 100, (manager_id=100 veya emloyee_id 100’de büyük kişileri bulunuz.)
select * from employees
where department_id=100
AND (manager_id=100 OR employee_id>100);
–İsmi ahmet olmayanları bulunuz
select * from employees
where first_name <> ‘Ahmet’;
–İsmi ahmet olmayanları bulma ikinci yöntem
select * from employees
where first_name != ‘Ahmet’;
–Desen arama LIKE
–İsmi ‘A’ harfi ile başlayan personelleri bulunuz.
select * from employees
where first_name like ‘A%’,
–Desen arama escape karakter kullanımı
select * from employees
where first_name like ‘%,__\’ escape ‘\’;
–Tırnak kullanımı
select ”’Ahmet”in”’, ”” from dual;
–between operatörü
–Son 10 yılda işe girmiş olanlar.
SELECT *
FROM employees
WHERE hire_date BETWEEN sysdate-3650 AND sysdate;
–NULL Aritmetiği
–manager_id boş olmayanları bulunuz.
SELECT *
FROM employees
WHERE manager_id IS NOT NULL;
–Bir önceki örneğin alternatif çözümü (NVL fonksyonu)
SELECT *
FROM employees
WHERE NOT NVL(manager_id,0) =0;
–in operatörü
–90,100,60 birimlerinde çalışan personellerin bulunması
select department_id,e.*
from employees e
where department_id IN (90,100,60,NULL);
–NOT in sorun oluşturabilecek kullanım örneği
select department_id,e.*
from employees e
where department_id NOT IN (90,100,60,NULL);
–Unvanı SA_MAN veya SA_REP olan kişileri bulunuz (OR ve IN kullanarak)
select *from employees
where job_id IN (‘SA_MAN’,’SA_REP’);
select *from employees
where job_id = ‘SA_MAN’ OR job_id=’SA_REP’;
–Maaşı 5000’den büyük ve commisyon alamamış kişileri bulunuz.
select *from employees
where salary>5000 and commission_pct IS NULL;
–Maaşı 3000 ve 5000 aralığında olan ve 2006 yılında işe girmiş kişileri bulunuz.
select *from employees
where salary between 3000 AND 5000
and TO_CHAR(hire_date,’YYYY’)=’2006′;
–Geçmiş tarihlerde mart ayında işe giren kişileri bulunuz.
select *from employees
where TO_CHAR(hire_date,’MM’)=’03’;
— Toplam maaşı 10000 ‘i geçmeyen kişileri bulunuz. (Toplam Maaş = Maaş + Comm * maaş )
select *from employees
where (salary+(commission_pct*salary))<10000;
–3. harfi ‘n’ ve 4. harfi ‘a’ olan kişileri bulunuz.
select * from employees
where first_name like ‘__na%’;
–Geçmişte IT_PROG ve AC_ACCOUNT görevlerini yapmış kişilerin id’lerini bulunuz.
select * from job_history j
where job_id IN (‘IT_PROG’,’AC_ACCOUNT’)
–Sıralama işlemi
–Maaşı 10000’den büyük personelleri maaşa göre sıralayarak getiriniz.
SELECT first_name,(salary+(commission_pct*salary)) maas
FROM employees
WHERE maas>10000
ORDER BY maas
–Gruplama
–Personelleri birimlere göre gruplandırınız.
SELECT ‘Departman ID :’ ||department_id,job_id, SUM(salary), MIN(salary),
MAX(salary),COUNT(*)
from employees
GROUP BY department_id, job_id
ORDER BY department_id
–Tek grup
–En büyük ve toplam maaşı bulunuz.
SELECT MAX(salary), SUM(salary)
FROM employees
–Grupları kısıtlama
— Toplam olarak 100000 $ üzerinde maaş ödenenen birimleri bulunuz.
SElect department_id, SUM(salary)
from employees
GROUP BY department_id
HAVING SUM(salary) > 100000
ORDER BY department_id
–Grupları kısıtlamam
— Toplam olarak 100000 $ üzerinde maaş ödenenen birimleri bulunuz.
SElect department_id, SUM(salary)
from employees
GROUP BY department_id
HAVING SUM(salary) > 100000
ORDER BY department_id;
-İsmi çift olanların bulunması
select first_name,count(*)
from employees
group by first_name
having count(*)>1
order by count(*) desc;
Alt Sorgu örnekleri
–10 nolu birimin yöneticisinin id numarasının bulunması
select * from employees
where employee_ıd = ( 10 nolu birimin yöneticisinin id)
(10 nolu birimin yöneticisinin idsi)
select manager_id from departments
where department_id=10
–Bütünleşik çözüm
select * from employees
where employee_ıd = (select manager_id from departments
where department_id=10)
–‘South San Francisco’daki birimlerin ismi
select location_id from locations
where city=’South San Francisco’;
Bütünleşik Çözüm
select * from departments
where location_id=(select location_id from locations
where city=’South San Francisco’)
Ortalamanın üzerinde maaş alan kişilerin bulunması
select *from employees
where salary>(SELECT AVG(salary) FROM employees)
IT_PROG le aynı maaş ve komisyonu alan FI_ACCOUNT ünvanlı kişilerin buluması
select * from employees
where job_id=’FI_ACCOUNT’
AND (salary,NVL(commission_pct,0)) IN (SELECT salary,NVL(commission_pct,0)
FROM employees
WHERE job_id=’IT_PROG’)
–Birimlerdeki en yüksek ortalama maaşın değerini bulunuz.
select * from (select * from employees where job_id=’IT_PROG’)
select max(ortalama) from (SELECT department_id,AVG(salary) ortalama
FROM employees
GROUP BY department_id);
–İkinci çözüm
select max(AVG(salary)) from employees GROUP BY department_id ;
–Kendi birimi ortalaması üzerinde maaş alan kişiler
select * from employees ust
where salary>(SELECT AVG(salary)
FROM employees alt
WHERE alt.department_ıd=ust.department_ıd )
–Kendi amiriyle aynı birimde çalışan kişileri bulunuz.
select *from employees ust
where department_id=(SELECT department_id
FROM employees
WHERE employee_id=ust.manager_id);
–1700 nolu lokasyonda çalışan kişilerin isimlerini bulunuz.
select * from employees
where department_id IN (select department_id
FROM departments
WHERE location_id=1700)
–Seattle’da çalışan kişilerin isimlerini bulunuz.
select * from employees
where department_id IN (select department_id
FROM departments
WHERE location_id IN (select location_id
from locations
where city=’Seattle’))
–Kimsenin çalışmadığı birimleri bulunuz.
select * from departments
where department_id NOT IN (SELECT NVL(department_id,0) FROM employees);
–Lab 3
–Geçmişte «SA_REP» olarak çalışan personellerin isimlerini bulunuz.
SELECT *FROM employees
WHERE employee_id IN (SELECT employee_id
FROM job_history
WHERE job_id=’SA_REP’ )
–Birden fazla birim olan yerlerin isimlerini bulunuz.
SELECT * FROM locations
WHERE location_id IN (SELECT location_id
FROM departments
GROUP BY location_id
HAVING count(*)>1)
–En yüksek maaş alan 5 adet personeli bulunuz. (rownum kullanılacaktır.)
–Yanlış çözüm
SELECT * FROM (SELECT first_name,last_name,salary,rownum x
FROM employees
ORDER BY salary DESC)
where x<6
–Doğru çözüm
select rownum,first_name,last_name,salary
from (SELECT first_name,last_name,salary
FROM employees ORDER BY salary DESC )
WHERE rownum<6
–Ortalama maaşı en yüksek 5 birimi bulunuz.
SELECT * FROM departments
WHERE department_id IN
(SELECT department_id FROM (SELECT department_id,AVG(salary) ort_maas
FROM employees
GROUP BY department_id
ORDER BY ort_maas DESC)
WHERE rownum<6)
–Hiç iş değiştirmeyen personelleri bulunuz. (job_history)
SELECT * FROM employees
WHERE employee_id NOT IN (SELECT employee_id FROM job_history)
–Biriminde kendi ünvanında başka çalışan olmayan kişileri bulunuz.
SELECT * FROM employees a
WHERE ‘x’ NOT IN (SELECT ‘x’ FROM employees
WHERE employee_id<>a.employee_id
AND department_id=a.department_id
AND job_id=a.job_id)
–1700 Nolu mekanda yaşayan ve «Stock Manager» ünvanlı kişileri bulunuz.
SELECT * FROM employees a
WHERE department_id IN (SELECT department_id
FROM departments
WHERE location_id=1700)
AND job_id=(SELECT job_id
FROM jobs
WHERE job_title=’Accountant’)
–Seattle’ da çalışan kişileri bulunuz.
–Alt Sorgu kullanınız. (Exist)
SELECT *
FROM employees e
WHERE EXISTS (SELECT *
FROM departments d
WHERE e.department_id=d.department_id
aND EXISTS (SELECT *FROM locations l
WHERE l.location_id=d.location_id
AND city=’Seattle’))
–Klasik
SELECT *FROM employees e, departments d, locations l
WHERE e.department_id=d.department_id
AND l.location_id=d.location_id
AND city=’Seattle’
–ANSI
SELECT *
FROM employees e
JOIN departments d
ON e.department_id=d.department_id
JOIN locations l
ON l.location_id=d.location_id
WHERE city=’Seattle’
–ANSI
SELECT *
FROM employees e
INNER JOIN departments d USING (department_id)
INNER JOIN locations l USING (location_id)
NATURAL jOIN :
WHERE city=’Seattle’
SELECT *FROM employees e
NATURAL JOIN departments d
NATURAL JOIN locations l
WHERE city=’Seattle’
–Birimde çalışan kişilerin sayılarını birim isimleriyle bulunuz.
SELECT department_name,adet FROM departments,
(SELECT department_id,COUNT(*) adet
FROM employees e
GROUP BY department_id) dept_adet
WHERE departments.department_id=dept_adet.department_id
SELECT e.department_id,department_name,COUNT(*) adet
FROM employees e, departments d
WHERE e.department_id=d.department_id
GROUP BY e.department_id,department_name
ORDER BY adet desc
SELECT department_id,department_name,COUNT(*) adet
FROM employees e
INNER JOIN departments d
USING (department_id)
GROUP BY department_id,department_name
ORDER BY adet desc
–Her birimde en düşük maaş alan kişileri bulunuz.(birim_adi, personel_adi, maas)
SELECT * FROM employees emp,
(SELECT e.department_id,d.department_name,MIN(salary) en_dusuk_maas FROM employees e, departments d
WHERE e.department_id=d.department_id
GROUP BY e.department_id,department_name) min_dept
WHERE emp.department_id=min_dept.department_id
AND emp.salary=min_dept.en_dusuk_maas
vıew:
CREATE VIEW min_dept AS (SELECT
e.department_id,d.department_name,MIN(salary) en_dusuk_maas
FROM employees e, departments d
WHERE e.department_id=d.department_id
GROUP BY e.department_id,department_name)
SELECT * FROM min_dept, employees e
WHERE min_dept.department_id=e.department_id
AND salary=en_dusuk_maas
–Birimlerde unvanlara verilen ortalama maaşları birim ve unvan ismiyle gösteriniz.
SELECT e.department_id, d.department_name, j.job_id, j.job_title, MIN(salary)
FROM employees e, departments d, jobs j
WHERE e.department_id=d.department_id
AND e.job_id=j.job_id
GROUP BY e.department_id, d.department_name, j.job_id, j.job_title
–Çalıştığı birimin ve unvanın en düşük maaşı eşit olan personelleri bulunuz.
SELECT * FROM employees e,
(SELECT e.department_id,MIN(salary) en_dusuk_maas
FROM employees e
GROUP BY e.department_id) dd,
(SELECT e.job_id,MIN(salary) en_dusuk_maas
FROM employees e
GROUP BY e.job_id ) ud
WHERE e.department_id=dd.department_id
AND e.salary=dd.en_dusuk_maas
AND e.job_id=ud.job_id
AND e.salary=ud.en_dusuk_maas
–Maaşı 1000 TL üzerinde olan Seattle’daki personellerin maaşlarına %10 zam yapınız.
SELECT * FROM employees
WHERE department_id IN (SELECT department_id
FROM locations
WHERE city=’Seattle’)
AND salary>1000;
SELECT * FROM employees
WHERE department_id IN
(SELECT department_id FROM departments
WHERE location_id IN (SELECT location_id
FROM locations
WHERE city=’Seattle’))
AND salary>1000;
UPDATE employees
SET salary=salary*1.1
WHERE department_id IN
(SELECT department_id FROM departments
WHERE location_id IN (SELECT location_id
FROM locations
WHERE city=’Seattle’))
AND salary>1000;
–Personelleri bir önceki çalıştıkları işlere atayan(max) UPDATE işlemini yazınız.
UPDATE calisan c
SET job_id = (SELECT MAX(job_id)
FROM job_history jh
WHERE jh.employee_id=c.employee_id)
WHERE employee_id IN (SELECT employee_id
FROM job_history)
–Hareket Yönetimi giriş Örnekleri
UPDATE calisan
SET salary=8500
WHERE employee_id=100
commit
SELECT *
FROM calisan
WHERE employee_id=100
rollback
ilk malzeme maiktar(100), para (500)
–HArekt başlangıcı
INSERT satıs fisi 3, 150TL
UPDATE malzeme miktarı 97
UPDATE toplam para 650TL
–commit,
commit
SELECT *
FROM calisan
WHERE employee_id=100
FOR UPDATE NOWAIT
–Aşağıdaki konutların hangisi kalıcı olarak saklanır?
INSERT
UPDATE
COMMIT
UPDATE
SAVEPOINT A:
INSERT
UPDATE
ROLLBACK TO SAVEPOINT A
COMMIT:
— DDL KOMUTLARI- TABLO ÜZERİNDEKİ İŞLEMLER: CREATE, ALTER, DROP
— >>> Aşağıdaki sql kodları HR veritabanı içerisinde yazıldı. <<<
— Tablo Oluşturma örneği:
CREATE TABLE birimler (birim_id number(10),
birim_adi varchar2(30));
— Tablo Değiştirme örneği:
ALTER TABLE birimler ADD (manager_id number(3));
SELECT * FROM user_tables; –(hr user ile ilgili) database ile ilgili bilgi veren system
view
SELECT * FROM all_tables; — kendi oluşturdukları, erişim hakkı olan.
SELECT * FROM dba_tables; — databasedeki bütün tablolar.
SELECT * FROM user_tab_columns
WHERE table_name=’EMPLOYEES’;
— Örnek: Bütün tablolara GIREN_KULLANICI ekleyiniz. (Kod olarak oluştur. SQL sonucu
olarak kod.)
SELECT ‘ALTER TABLE ‘ || table_name || ‘ ADD (manager_id number(3));’,
table_name
FROM user_tables;
–DDL komutları Autocommittir. Bulunduğu transaction da commit edilir. İlave yetkiler
gerektirir.
UPDATE
INSERT
CREATE TABLE
–Tablodaki verilerin kalıcı olarak silinmesi:
TRUNCATE TABLE birimler; — Birimlerdeki her veriyi siler, tablo durur. Delete’e göre
daha hızlı. Autocommittir. Fazladan yetki ister.
CREATE TABLE calisanlar as (select * from employees);
–Tablo Silme örneği:
DROP TABLE employees; –Tabloyu siler. Foreign key constraints var. Silinemiyor.
Referansı var.
SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = ‘EMPLOYEES’;
SELECT * FROM DEPARTMENTS;
— Tablo kalıcı olarak silinir.
DROP TABLE employees CASCADE CONSTRAINT — Kodu çalıştırma.
–Constraint baştan oluşturma:
CREATE TABLE birimler (birim_id number(10),
birim_adi varchar2(30)
PRIMARY KEY (BIRIM_ID));
— Constraint sonradan oluşturma yöntemi:
ALTER TABLE “HR”.”EMPLOYEES” ADD CONSTRAINT “EMP_DEPT_FK” FOREIGN KEY
(“DEPARTMENT_ID”)
REFERENCES “HR”.”DEPARTMENTS” (“DEPARTMENT_ID”) ENABLE;
SELECT * FROM EMPLOYEES;
–Veri düzeyinde SELECT yapma hakkı verildi.
GRANT SELECT ON employees TO kullanici;
–Veri düzeyinde DELETE yapma hakkı verildi.
GRANT DELETE ON employees TO kullanici;
–-Başkasına hak verme hakkı.
GRANT SELECT ON employees TO kullanici WITH GRANT OPTION;
–Role Tanımlama: Grup halinde kullanıcılara hak verilmesi ve geri alınması için.
CREATE ROLE personel_giris;
—- >>> Aşağıdaki sql kodları SYSTEM veritabanı içerisinde yazıldı. <<<
CREATE USER kullanici
IDENTIFIED BY kullanici;
— Session izni
GRANT CREATE SESSION TO kullanici;
— Tablo oluşturma hakkı.
GRANT CREATE TABLE TO kullanici;
–Kullanıcının tablespacede hakkı olmalı. İlave.
ALTER USER kullanici quota 100M on “USERS”;
SELECT * FROM hr.EMPLOYEES;
CREATE ROLE personel_giris;
— personel_girisi’ne hr.employees tablosu üzerinde SELECT, INSERT, UPDATE, DELETE
işlemleri yapma hakkı veriliyor.
GRANT SELECT,INSERT,UPDATE,DELETE ON hr.EMPLOYEES TO personel_giris;
— personel_girisi’ne hr.departments tablosu üzerinde SELECT, INSERT, UPDATE,
DELETE işlemleri yapma hakkı veriliyor.
GRANT SELECT,INSERT,UPDATE,DELETE ON HR.departments TO personel_giris;
–personel_girisinden kullanici veritabanına hak tanımlanıyor.
GRANT personel_giris TO kullanici;
–Kimin neye hakkı olduğu görünüyor.
SELECT * FROM DBA_tab_prıvs
WHERE OWNER=’HR’
AND GRANTEE=’KULLANICI’;
–-Veritabanı sistemine kim bağlı.
SELECT user FROM DUAL;
–Veritabanı session durumları.
select * from v$session
where username=’HR’;
—- >>> Aşağıdaki sql kodları KULLANICI veritabanı içerisinde yazıldı. <<<
SELECT * from user_tables;
— Kullanıcı tablespace tablo oluşturabiliyor.
CREATE TABLE a (b number(7)) TABLESPACE “USERS”;
–Owner üzerinde veri hakları vardır.
SELECT * FROM a;
DELETE from a;
–Table’ı görme hakkı yok. Hak verilmesi gerekiyor.
SELECT * FROM hr.EMPLOYEES;
DELETE FROM hr.EMPLOYEES;
–Kendi tablosunu silebiliyor.
DROP TABLE a;
–Veritabanı sistemine kim bağlı olduğuna bakılıyor:
SELECT user FROM DUAL;
/*
Egitim kullanıcısını oluşturunuz. Şifre eğitim
Bu kullanıcıya sisteme bağlanma ve tablo oluşturma hakkı veriniz.
İk rolü oluşturunuz
Egitim kullanıcısından hr tablolarının tümüne erişmek için gerekli tüm yetkileri bir ik
rolüne veren scripti oluşturunuz.
İk rolünü egitm kullanıcısına atayınız.
Egitim kullanıcısı ile hr tablolarını sorgulayınız.
*/
CREATE USER egitim IDENTIFIED BY egitim;
GRANT CREATE SESSION, CREATE TABLE TO egitim;
CREATE ROLE ik;
select ‘GRANT ALL ON hr.’|| table_name ||’ TO ik;’ from all_tables
where owner=’HR’
GRANT ALL ON hr.REGIONS TO ik;
GRANT ALL ON hr.LOCATIONS TO ik;
GRANT ALL ON hr.DEPARTMENTS TO ik;
GRANT ALL ON hr.JOBS TO ik;
GRANT ALL ON hr.EMPLOYEES TO ik;
GRANT ALL ON hr.JOB_HISTORY TO ik;
GRANT ALL ON hr.BASKI_TURU TO ik;
…
GRANT ik to egitim ;
/*
Employee tablosundan çalışan tablosu oluşturunuz.
Çalışan tablosu tüm constraintlerini siliniz.
Çalışan hire date alanında sistem tarihinden önce olmaz kuralı ekleyiniz.
Calisan tablosu department_id alanı üzerine department tablosu department_id
alanına referans eden bir foreign key constraint oluşturunuz.
*/
CREATE TABLE isci AS (SELECT * FROM employees)
select ‘alter table isci drop constraint ‘|| constraint_name||’;’
from user_constraints
where table_name=’ISCI’
alter table isci drop constraint SYS_C007471;
alter table isci drop constraint SYS_C007472;
alter table isci drop constraint SYS_C007473;
alter table isci drop constraint SYS_C007474;
alter table isci drop constraint CK_HIRE_DATE;
select min(hire_date) from isci
ALTER TABLE isci
ADD CONSTRAINT ck_hire_date
CHECK (hire_date>=TO_DATE(’01/01/2015′,’DD/MM/YYYY’)) INITIALLY DEFERRED
NOT DEFERRABLE
UPDATE isci set hire_date =TO_DATE(’01/01/2000′,’DD/MM/YYYY’)
ALTER TABLE isci
ADD CONSTRAINT fk_isci_department_id
FOREIGN KEY (department_id)
REFERENCES departments(department_id)