test.sql

# filmler tablosunu yaratmak için

CREATE TABLE filmler (
  film_id NUMBER PRIMARY KEY,
  film_ismi VARCHAR2(140) NOT NULL,
  yili NUMBER(4) NOT NULL,
  yonetmen VARCHAR2(140) NOT NULL,
  turu VARCHAR2(140) NOT NULL,
  imdb_puani NUMBER(2, 1) NOT NULL
);

# filmler tablosuna veri girmek için

INSERT INTO filmler(film_id, film_ismi, yili, yonetmen, turu, imdb_puani)
VALUES (1, 'Monster Trucks', 2016, 'Chris Wedge', 'Animation', 5.4);

INSERT INTO filmler(film_id, film_ismi, yili, yonetmen, turu, imdb_puani)
VALUES (2, 'Interstellar', 2014, 'Christopher Nolan', 'Sci-fi', 8.6);

INSERT INTO filmler(film_id, film_ismi, yili, yonetmen, turu, imdb_puani)
VALUES (3, 'The Good, the Bad and the Ugly', 1966, 'Sergio Leone', 'Western', 8.9);

INSERT INTO filmler(film_id, film_ismi, yili, yonetmen, turu, imdb_puani)
VALUES (4, 'WALL·E', 2008, 'Andrew Stanton', 'Animation', 8.4);

INSERT INTO filmler(film_id, film_ismi, yili, yonetmen, turu, imdb_puani)
VALUES (5, 'Seven Samurai', 1954, 'Akira Kurosawa', 'Adventure', 8.7);

INSERT INTO filmler(film_id, film_ismi, yili, yonetmen, turu, imdb_puani)
VALUES (6, 'BAD MOVIE', 1954, 'BAD DIRECTOR', 'Adventure', 1.0);

INSERT INTO filmler(film_id, film_ismi, yili, yonetmen, turu, imdb_puani)
VALUES (7, 'Not good movie', 1954, 'NOT GOOD DIRECTOR', 'Comedy', 1.0);


# ...
 
CREATE TABLE uyeler (
  uye_id NUMBER PRIMARY KEY,
  isim VARCHAR2(140) NOT NULL,
  soyisim VARCHAR2(140) NOT NULL,
  email VARCHAR2(140),
  telefon VARCHAR2(140) NOT NULL,
  adres VARCHAR2(140) NOT NULL
);

INSERT INTO uyeler(uye_id, isim, soyisim, email, telefon, adres)
VALUES (1, 'Gizem', 'Yilmazturk', 'gy@mail.com', '555-555 5555', 'Istanbul');

INSERT INTO uyeler(uye_id, isim, soyisim, email, telefon, adres)
VALUES (2, 'Yusuf', 'Yalim', 'yy@mail.com', '555-555 5555', 'Istanbul');

INSERT INTO uyeler(uye_id, isim, soyisim, email, telefon, adres)
VALUES (3, 'Gunes', 'Erduydu', 'ge@mail.com', '555-555 5555', 'Istanbul');

INSERT INTO uyeler(uye_id, isim, soyisim, email, telefon, adres)
VALUES (4, 'Gizem', 'Kapci', 'gk@mail.com', '555-555 5555', 'Istanbul');
# ...


# stok tablosunu yaratıp veri girmek için
CREATE TABLE stok (
  stok_id NUMBER PRIMARY KEY,
  film_id NUMBER,
  CONSTRAINT fk_film FOREIGN KEY (film_id) REFERENCES filmler(film_id)
);

INSERT INTO stok(stok_id, film_id)
VALUES (1, 1);

INSERT INTO stok(stok_id, film_id)
VALUES (2, 1);

INSERT INTO stok(stok_id, film_id)
VALUES (3, 2);

INSERT INTO stok(stok_id, film_id)
VALUES (4, 3);

INSERT INTO stok(stok_id, film_id)
VALUES (5, 4);

INSERT INTO stok(stok_id, film_id)
VALUES (6, 2);

INSERT INTO stok(stok_id, film_id)
VALUES (7, 5);

INSERT INTO stok(stok_id, film_id)
VALUES (8, 6);

INSERT INTO stok(stok_id, film_id)
VALUES (9, 7);

# ...


CREATE TABLE odunc (
  odunc_id NUMBER PRIMARY KEY,
  odunc_alma_tarihi DATE NOT NULL,
  iade_tarihi DATE,
  stok_id NUMBER,
  uye_id NUMBER,
  CONSTRAINT fk_stok FOREIGN KEY (stok_id) REFERENCES filmler(film_id),
  CONSTRAINT fk_uye FOREIGN KEY (uye_id) REFERENCES filmler(film_id)
);

INSERT INTO odunc(odunc_id, odunc_alma_tarihi, stok_id, uye_id)
VALUES (1, TO_DATE('01/01/2017', 'dd/mm/yyyy'), 1, 1);

INSERT INTO odunc(odunc_id, odunc_alma_tarihi, stok_id, uye_id)
VALUES (2, TO_DATE('01/01/2017', 'dd/mm/yyyy'), 5, 3);

INSERT INTO odunc(odunc_id, odunc_alma_tarihi, stok_id, uye_id)
VALUES (3, TO_DATE('01/01/2017', 'dd/mm/yyyy'), 3, 2);


# ...


# ---------------------------------------------------------------------------------------------------
# Zizos bunlari teker, teker dene olur mu? NOLUR <3

# Örnek sorgular

# Animasyon Olan filmleri Göster -- SELECT ORNEGI
SELECT * FROM filmler WHERE turu = 'Animation';

# stoktaki filmlerin listesi -- SELECT, JOIN
SELECT * FROM stok 
JOIN filmler USING (film_id);

# stoktaki filmlerin adedi -- SELECT, JOIN
SELECT COUNT(*) AS FILM_ADEDI 
FROM stok 
JOIN filmler USING (film_id);

# filmleri turlere gore gruplayip adedini veren sorgu -- SELECT, GROUP BY
SELECT TURU, COUNT(film_id) AS film_adedi 
FROM FILMLER 
GROUP BY TURU;

# stokda aynı birden fazla aynı türden olan filmlerin sayısı -- SELECT, SUBQUERY, GROUP BY, HAVING 
SELECT subquery1.TURU, count(*) 
FROM (SELECT * FROM stok JOIN filmler USING (film_id)) subquery1 
GROUP BY subquery1.TURU 
HAVING COUNT(*) > 1;

# Iade edilmeye odunc listesi -- SELECT, JOIN O
SELECT * FROM ODUNC
JOIN STOK USING (STOK_ID)
JOIN FILMLER USING (FILM_ID)
JOIN UYELER USING (UYE_ID)
WHERE ODUNC.IADE_TARIHI IS NULL;

# iade edilme -- UPDATE
UPDATE ODUNC
SET iade_tarihi = TO_DATE('22/08/2017', 'dd/mm/yyyy')
WHERE odunc_id = 1;

# iade edilmis odunc listesi -- SELECT, JOIN
SELECT * FROM ODUNC
JOIN STOK USING (STOK_ID)
JOIN FILMLER USING (FILM_ID)
JOIN UYELER USING (UYE_ID)
WHERE ODUNC.IADE_TARIHI IS NOT NULL;

# ODUNC ALINMAMIŞ STOK LISTESI -- SELECT, SUBQUERY
SELECT * FROM STOK
JOIN FILMLER USING(FILM_ID)
WHERE STOK_ID NOT IN ( SELECT STOK_ID FROM ODUNC WHERE ODUNC.IADE_TARIHI IS NULL);

# FILM TURLERI -- SELECT, DISTINCT 
SELECT DISTINCT TURU FROM FILMLER;

# TURLERE GORE ORTALAMA PUAN -- SELECT, AVG
SELECT TURU, AVG(IMDB_PUANI) AS ORTALAMA FROM FILMLER GROUP BY TURU;

# ismi `Not good movie` olan puanını artır. -- UPDATE 
UPDATE FILMLER
SET  IMDB_PUANI = 3.5
WHERE FILM_ISMI = 'Not good movie';

# puani 3 ile 4 olan filmlerin ismini `Avarage movie` olarak degiştir -- UPDATE
UPDATE FILMLER 
SET FILM_ISMI = 'Avarage movie'
WHERE IMDB_PUANI BETWEEN 3 AND 4;

# ismi gizem olan kullaniclarin adresini `Ankara` yap -- UPDATE, SUBQUERY
UPDATE UYELER
SET ADRES = 'ANKARA'
WHERE UYE_ID IN (SELECT UYE_ID FROM UYELER WHERE ISIM = 'Gizem');

# puani 3'den az olan filmlerin puanini artir -- UPDATE
UPDATE FILMLER 
SET IMDB_PUANI = IMDB_PUANI + 1
WHERE IMDB_PUANI < 3;

# en düsük puanlı filmi sil -- DELETE, SUBQUERY
DELETE FROM FILMLER
WHERE IMDB_PUANI = (SELECT MIN(IMDB_PUANI) FROM FILMLER);

# en dusuk puanli filmi stoktan sil -- DELETE, SUBQUERY
DELETE FROM STOK
WHERE FILM_ID = (
  SELECT FILM_ID FROM FILMLER
  WHERE IMDB_PUANI = (SELECT MIN(IMDB_PUANI) FROM FILMLER)
);

# iade edilmis oduncleri sil -- DELETE
DELETE FROM ODUNC
WHERE IADE_TARIHI IS NOT NULL;

# ismi Gunes Erduydu olan uyeleri sil -- DELETE
DELETE FROM UYELER
WHERE ISIM = 'Gunes' AND SOYISIM = 'Erduydu';

# Komedi filmlerini sil -- DELETE
DELETE FROM FILMLER
WHERE TURU = 'Comedy';