Writing Something

var deviceIphone = "iphone";
var deviceIpod = "ipod";
var deviceS60 = "series60";
var deviceSymbian = "symbian";
var deviceAndroid = "android";
var deviceWinMob = "windows ce";
 
var uagent = navigator.userAgent.toLowerCase();
 
function DetectMobile() {
    if (uagent.search(deviceIphone) > -1
        || uagent.search(deviceIpod) > -1
        || uagent.search(deviceS60) > -1
        || uagent.search(deviceSymbian) > -1
        || uagent.search(deviceWinMob) > -1
        || uagent.search(deviceAndroid) > -1)
        return true;
    else
        return false;
}
 
if (DetectMobile()) {
    alert("mobile browserdan bir ziyaret...");
}

CREATE VIEW vwsal AS
SELECT last_name lname,
     department_id deptid,
     salary sal
FROM employees
WHERE department_id = 50;
 
SELECT *
FROM vwsal;
 
UPDATE vwsal
SET sal = sal + 10;
 
CREATE OR REPLACE VIEW vwsal AS
SELECT last_name lname,
     department_id deptid,
     salary sal
FROM employees
WHERE department_id = 60;
 
CREATE OR REPLACE VIEW vwsal AS
SELECT last_name lname,
     department_id deptid,
     salary sal
FROM employees
WHERE department_id = 20 WITH CHECK OPTION;
--WITH CHECK OPTION where şartındaki kolonun değiştirlimemesini kontrol ediyo
 
CREATE OR REPLACE VIEW vwsal AS
SELECT last_name lname,
     department_id deptid,
     salary sal
FROM employees
WHERE department_id = 20 WITH READ ONLY;
--WITH READ ONLY sadece select için bir view
--raporlar genelde bu şekilde oluşturulur
 
 
DROP VIEW vwsal;
 
CREATE SEQUENCE dept_seq INCREMENT 
BY 10 START WITH 120 maxvalue 9999 nocache nocycle;
 
CREATE SEQUENCE dept_seq INCREMENT 
BY 10 START WITH 120 maxvalue 9999 cache 50 nocycle;
 
CREATE SEQUENCE seq;
 
INSERT
INTO departments
VALUES(seq.NEXTVAL,      'deneme',      100,      1000);
 
SELECT seq.currval
FROM dual;
 
 
CREATE synonym em FOR employees;
--tabloya nickname verdik
 
SELECT * FROM em;
 
 
CREATE INDEX dept_name ON dept(name);
 
 
SELECT * FROM dictionary;
SELECT * FROM dict;
 
 
--kullanıcı yaratmak
CREATE USER serdar IDENTIFIED BY sr
 
 
--session hakkı vermek
GRANT CREATE SESSION TO serdar;
 
 
--select hakkı vermek
GRANT SELECT ON hr.employees TO serdar;
 
 
--kendi şemasında tablo oluşturma hakkı
GRANT CREATE TABLE TO serdar;
 
--tüm şemalarda tablo oluşturma hakkı
GRANT CREATE any TABLE TO serdar;
 
--user kiilitlemek
ALTER USER serdar account LOCK;
 
--user kilitlenmişse açmak
ALTER USER serdar IDENTIFIED BY sr account UNLOCK;
 
 
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO demo;
 
 
--rol yaratmak
CREATE ROLE rol;
 
--role hak vermek
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE TO rol;
 
--user a rol vermek
GRANT rol TO serdar;
 
 
--sifre değiştirmek
ALTER USER serdar IDENTIFIED BY '123';
 
 
--verdiğim yetkiyi dağıtabilir şekilde ver, with grant option
GRANT SELECT ON employees TO serdar WITH GRANT OPTION;
 
 
--herkese yetki ver, to public
GRANT SELECT ON alice.departments TO public
 
 
--hak almak
REVOKE SELECT,INSERT ON departments FROM serdar cascade constraints;
--casced daha önce bu haklardan dağıtmışsa onlarıda alır...
 
--kolon ekle
ALTER TABLE employees ADD(kolon varchar2(20));
 
--kolon değiştir
ALTER TABLE employees MODIFY(kolon varchar2(30));
 
--kolon sil
ALTER TABLE employees DROP(kolon);
 
--kolonu kullanılamaz yapmak
ALTER TABLE employees
SET unused(salary);
--çok data olan bir kolon drop edildiğinde 2-3 dakika sürebilir
--unused anında kolonu kullanılamaz yapar
--kolon datası hala arkaplanda duruyordur ama getirilemez
--ikinci adımda drop ederek arkaplandanda sileriz
 
--constraint eklemek
ALTER TABLE emp2
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(manager_id)
REFERENCES emp2(employee_id);
 
ALTER TABLE emp2 MODIFY employee_id PRIMARY KEY;
 
ALTER TABLE emp2 MODIFY employee_id PRIMARY KEY deferrable initially deferred;
 
ALTER SESSION
SET constraints = immediate;
 
ALTER TABLE dept2
DROP PRIMARY KEY cascade;
 
ALTER TABLE emp2
disable CONSTRAINT emp_dt_fk;
 
ALTER TABLE emp2
enable CONSTRAINT emp_dt_fk;
 
ALTER TABLE emp2
DROP COLUMN employee_id cascade constraints;
 
ALTER TABLE emp2
RENAME COLUMN employee_id TO eid;
 
ALTER TABLE emp2
RENAME CONSTRAINT mktg_pk TO newmktg_pk;

commit;
 
INSERT
UPDATE
CREATE --create anında auto commit olur
DELETE
 
ROLLBACK; --create cümlesinin sonrasına dönebiliriz...
 
 
--for update, select ile gelen kolonları kilitliyor...
SELECT employee_id,
 salary,
 job_id
FROM employees
WHERE job_id = 'SA_REP'
FOR UPDATE
ORDER BY employee_id;
 
CREATE TABLE my_employee(
id NUMBER(4) NOT NULL,
last_name VARCHAR2(25),
first_name VARCHAR2(25),
userid VARCHAR2(8),
salary NUMBER(9,   2));
 
DESC my_employee;
 
INSERT INTO my_employee VALUES (1,'Patel','Ralph','rpatel',895);
 
INSERT INTO my_employee (id,last_name,first_name,userid,salary) VALUES
(2,'Dancs','Betty','bdancs',860);
 
INSERT INTO my_employee (id,last_name,first_name,userid,salary) VALUES
(&id,'&last_name','&first_name','&userid',&salary);
 
commit;
 
SELECT * FROM my_employee;
 
UPDATE my_employee
SET last_name ='Drexler'
WHERE id=3;
 
UPDATE my_employee
SET salary = 1000
WHERE salary<900;
 
SELECT * FROM my_employee;
 
DELETE FROM my_employee WHERE last_name='Dancs' AND first_name='Betty';
 
DELETE FROM my_employee
WHERE rowid='AAARW6AAEAAAAGPAAE';
 
commit;
 
SAVEPOINT A;
 
DELETE FROM my_employee;
 
ROLLBACK TO SAVEPOINT A;
 
commit;
 
INSERT INTO my_employee (id,last_name,first_name,userid,salary)
VALUES (&id,'&&last_name','&&first_name',LOWER(substr('&first_name',0,1))
|| LOWER(substr('&last_name',0,7)),&salary);
 
 
--number(3,2)  maximum 9.99 tutar
CREATE TABLE tbl(col1 NUMBER(3,   2) NOT NULL,   col2 VARCHAR2(20),
col3 DATE);
 
DESC tbl;
 
INSERT
INTO tbl
VALUES(1,   'Serdar',   sysdate);
 
SELECT *
FROM tbl;
 
CREATE TABLE tbl2(col1 NUMBER(3,   2) NOT NULL,   col2 VARCHAR2(20),
col3 DATE DEFAULT sysdate);
 
DESC tbl2;
 
INSERT
INTO tbl2 (col1,col2)
VALUES(1,   'Serdar');
 
SELECT * FROM tbl2;
 
 
-- data types
 
* varchar2              - değişken uzunluklu karakter tutmak içIN
* CHAR                  - sabit uzunluklu DATA tutmak içIN 
(CHAR VARCHAR a göre çok yer tutar ama daha performanslıdır.)
* NUMBER                - sayı tutar
* DATE                  - tarif tutar
* long                  - değişken uzunluklu karakter DATA MAX 2gb
* CLOB                  - değişken uzunluklu karakter DATA MAX 4gb
* raw AND long raw      - raw BINARY DATA
* BLOB                  - BINARY DATA (MAX 4gb)
* bfile                 - BINARY DATA bir klasörde tutulur pathi alana yazılır.
 
* rowid                 - her bir satır içIN oluşturulan sıra numarasıdır. 
 
 
--constraints
 
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
 
--
 
ALTER TABLE emp5 READ ONLY;
 
ALTER TABLE emp5 READ WRITE;

SELECT first_name,
  last_name,
  salary
FROM employees
WHERE salary >
  (SELECT salary
   FROM employees
   WHERE last_name = 'Abel')
;
 
 
SELECT first_name,
  last_name,
  salary
FROM employees
WHERE salary < ANY
  (SELECT salary
   FROM employees
   WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
 
 
SELECT first_name,
  last_name,
  salary
FROM employees
WHERE salary <
  (SELECT MAX(salary)
   FROM employees
   WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
 
 
SELECT first_name,
  last_name,
  salary
FROM employees
WHERE salary < ALL
  (SELECT salary
   FROM employees
   WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
 
 
SELECT first_name,
  last_name,
  salary
FROM employees
WHERE salary <
  (SELECT MIN(salary)
   FROM employees
   WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
 
 
SELECT employee_id,
  department_id
FROM job_history
WHERE employee_id = 200
UNION ALL
SELECT employee_id,
  department_id
FROM employees
WHERE employee_id = 200;
 
 
SELECT employee_id,
  department_id
FROM job_history INTERSECT
SELECT employee_id,
  department_id
FROM employees;
 
 
SELECT employee_id
FROM employees minus
SELECT employee_id
FROM job_history;
 
 
SELECT employee_id,
  department_id
FROM job_history
WHERE employee_id = 200
UNION ALL
SELECT employee_id,
  department_id
FROM employees
WHERE employee_id = 200;
 
 
SELECT employee_id,
  department_id
FROM job_history INTERSECT
SELECT employee_id,
  department_id
FROM employees;
 
 
SELECT employee_id
FROM employees minus
SELECT employee_id
FROM job_history;
 
 
SELECT location_id,
  department_name "department",
  to_char(NULL) "warehouse location"
FROM departments
UNION
SELECT location_id,
  to_char(NULL) "department",
  state_province "warehouse location"
FROM locations;
 
 
SELECT employee_id,
  job_id,
  salary,
  hire_date
FROM employees
UNION
SELECT employee_id,
  job_id,
  0,
  sysdate
FROM job_history;
 
 
SELECT department_id
FROM departments INTERSECT
SELECT department_id
FROM employees
WHERE job_id <>('ST_CLERK');
 
 
SELECT country_id,
  country_name
FROM countries minus
SELECT DISTINCT x.country_id,
  x.country_name
FROM locations l,
  countries x
WHERE l.country_id = x.country_id
 AND l.location_id IN
  (SELECT DISTINCT location_id
   FROM departments)
;
 
 
--insert tabloyu kilitler! update, delete satırı kilitler.
INSERT
INTO departments(department_id,   department_name,   manager_id,   location_id)
VALUES(280,   'Serdar',   100,   1700);
 
 
 
INSERT
INTO departments(department_id,   department_name)
VALUES(290,   'Serdar 2' );
 
 
ROLLBACK;
 
 
 
CREATE TABLE emp2 AS
SELECT *
FROM employees;
 
commit;
 
INSERT
INTO emp2
SELECT *
FROM emp2;
 
 
INSERT
INTO emp2(last_name,      salary,      email,      hire_date,      job_id)
SELECT last_name,
     salary,
     email,
     hire_date,
     job_id
FROM emp2;
 
 
UPDATE emp2
SET salary = 24000
WHERE employee_id = 100;
 
 
UPDATE emp2
SET department_id =
     (SELECT department_id
      FROM employees
      WHERE employee_id = 100)
WHERE job_id = 'AD_ASST';
 
 
DELETE FROM emp2
WHERE employee_id = 101;
 
 
TRUNCATE TABLE emp2;

SELECT department_id,
  job_id,
  AVG(salary)
FROM employees
WHERE SUBSTR(first_name,   0,   1) = 'A'
GROUP BY job_id,
  department_id HAVING AVG(salary) > 5000
ORDER BY department_id,
  job_id;
 
 
SELECT SUM(salary) AS
toplam,
  AVG(salary) AS
ortalama,
  MIN(salary) AS
mini,
  MAX(salary) AS
maxi,
  STDDEV(salary) AS
standartsapma,
  VARIANCE(salary) AS
karakok,
  COUNT(salary) AS
sayi
FROM employees;
 
 
SELECT AVG(nvl(commission_pct,   0)) AS
tumcomisyonlar,
  AVG(commission_pct) AS
nullolmayankomisyonlar
FROM employees;
 
 
SELECT ROUND(MAX(AVG(salary)))
FROM employees
GROUP BY department_id;
 
 
SELECT department_id,
  job_id,
  SUM(salary)
FROM employees
GROUP BY rollup(department_id,job_id) HAVING SUM(salary) > 10000
ORDER BY department_id;
 
 
SELECT department_id,
  job_id,
  SUM(salary)
FROM employees
GROUP BY cube(department_id,job_id) HAVING SUM(salary) > 10000
ORDER BY department_id;
 
 
SELECT COUNT(DISTINCT manager_id)
FROM employees;
 
 
SELECT COUNT(*) AS
"TOTAL",
  COUNT(
CASE to_char(hire_date,   'YY')
WHEN '95' THEN 1
END) "1995",
  COUNT(
CASE to_char(hire_date,   'YY')
WHEN '96' THEN 1
END) "1996",
  COUNT(
CASE to_char(hire_date,   'YY')
WHEN '97' THEN 1
END) "1997",
  COUNT(
CASE to_char(hire_date,   'YY')
WHEN '98' THEN 1
END) "1998"
FROM employees;
 
 
SELECT COUNT(*) AS
"TOTAL",
  SUM(decode(to_char(hire_date,   'YY'),   '95',   1,   0)) "1995",
  SUM(decode(to_char(hire_date,   'YY'),   '96',   1,   0)) "1996",
  SUM(decode(to_char(hire_date,   'YY'),   '97',   1,   0)) "1997",
  SUM(decode(to_char(hire_date,   'YY'),   '98',   1,   0)) "1998"
FROM employees;
 
 
SELECT job_id,
  SUM(
CASE department_id
WHEN 20 THEN salary
END) "Dept 20",
  SUM(
CASE department_id
WHEN 50 THEN salary
END) "Dept 50",
  SUM(
CASE department_id
WHEN 80 THEN salary
END) "Dept 80",
  SUM(
CASE department_id
WHEN 90 THEN salary
END) "Dept 90",
  SUM(salary)
FROM employees
GROUP BY job_id;
 
 
SELECT job_id,
  SUM(decode(department_id,   20,   salary,   0)) "Dept 20",
  SUM(decode(department_id,   50,   salary,   0)) "Dept 50",
  SUM(decode(department_id,   80,   salary,   0)) "Dept 80",
  SUM(decode(department_id,   90,   salary,   0)) "Dept 90"
FROM employees
GROUP BY job_id;
 
 
SELECT *
FROM employees minus
SELECT *
FROM employees
WHERE job_id = 'IT_PROG';
 
 
SELECT *
FROM user_constraints;
--R foreign key
--P primart key
--U unique
--C check
 
 
SELECT employees.last_name,
  employees.department_id,
  departments.department_name,
  locations.city
FROM employees 
JOIN departments ON employees.department_id = departments.department_id 
JOIN locations ON departments.location_id = locations.location_id;
 
 
SELECT e.last_name,
  e.department_id,
  d.department_name,
  l.city
FROM employees e,
  departments d,
  locations l
WHERE e.department_id = d.department_id
 AND d.location_id = l.location_id;
 
 
SELECT department_id,
  department_name,
  location_id,
  city
FROM departments NATURAL JOIN locations;
 
 
SELECT employee_id,
  last_name,
  location_id,
  department_id
FROM employees JOIN departments USING(department_id)
WHERE department_id = 50;
 
 
--and whereden daha performansli çalisir
SELECT e.employee_id,
  e.last_name,
  d.location_id,
  e.department_id
FROM employees e JOIN departments d ON(e.department_id = d.department_id)
 AND d.department_id = 50;
 
 
SELECT e.employee_id,
  e.last_name,
  m.last_name
FROM employees e JOIN employees m ON(e.manager_id = m.employee_id);
 
 
SELECT e.last_name,
  e.department_id,
  d.department_name
FROM employees e RIGHT
OUTER JOIN departments d ON(e.department_id = d.department_id);
 
 
SELECT e.last_name,
  e.department_id,
  d.department_name
FROM employees e,
  departments d
WHERE(e.department_id(+) = d.department_id);
--(+) right outher ise sol tarafa koyuyoruz artiyi
 
 
SELECT e.last_name,
  e.department_id,
  d.department_name
FROM employees e FULL
OUTER JOIN departments d ON(e.department_id = d.department_id);
 
 
SELECT e.last_name,
  e.department_id,
  d.department_name
FROM employees e,
  departments d
WHERE(e.department_id = d.department_id(+))
UNION
SELECT e.last_name,
  e.department_id,
  d.department_name
FROM employees e,
  departments d
WHERE(e.department_id(+) = d.department_id);
 
 
SELECT last_name, department_name
FROM employees CROSS JOIN departments;
 
 
SELECT last_name, department_name
FROM employees, departments;

SELECT  UPPER(substr(FIRST_NAME,0,1)) ||'. '|| initcap(LAST_NAME) AS "Özel Ad",
        concat(FIRST_NAME,LAST_NAME), 
        LENGTH(LAST_NAME),
        LOWER(LAST_NAME),        
        instr(LAST_NAME,'s'),
        lpad(SALARY, 6,'0'),
        rpad(EMAIL, 10,'-'),
        REPLACE(LAST_NAME,'i',' I '),
        TRIM('K' FROM LAST_NAME)
FROM EMPLOYEES
WHERE UPPER(last_name) = UPPER('KiNg')
  OR substr(JOB_ID,4)='PROG';
 
SELECT substr('SERDAR',1,3)
FROM DUAL;
 
SELECT round(45.926,2), round(45.926,-1), round(155.926,-2), 
           round(43.926,-1), round(45.926)
FROM DUAL;
 
SELECT trunc(45.926, 2), trunc(45.926), trunc(45.926,-1)
FROM DUAL;
 
SELECT SALARY, MOD(salary,1000), round(salary*1.18)
FROM EMPLOYEES;
 
SELECT sysdate+7,sysdate
FROM DUAL;
 
SELECT HIRE_DATE,round(sysdate-HIRE_DATE) AS GUNSAYISI,
                  round((sysdate-HIRE_DATE)/7) AS HAFTA
FROM EMPLOYEES
ORDER BY GUNSAYISI;
 
SELECT months_between(sysdate, HIRE_DATE), 
        add_months(HIRE_DATE,5),
        next_day(HIRE_DATE,'monday'),
        last_day(sysdate)    
FROM EMPLOYEES;
 
 
SELECT round(sysdate,'month'),
       round(sysdate,'year'),
        trunc(sysdate,'month'),
        trunc(sysdate,'year') 
FROM DUAL;
 
 
ALTER SESSION SET nls_date_format ='DD-MM-YYYY HH24:MI:SS';
ALTER system SET nls_date_format ='DD-MM-YYYY HH24:MI:SS';
 
SELECT to_char(sysdate,'FMDAY - MONTH - YEAR'),to_char(sysdate,'DAY-MONTH-YEAR')
FROM dual;
 
SELECT to_char(salary,'$99,999.99')
FROM employees;
 
SELECT sysdate,LOWER(to_char(sysdate,'fmddspth - month - year')) 
FROM dual;
 
SELECT last_name, to_char(hire_date,'dd - Mon - yyyy')
FROM employees
WHERE hire_date < to_date('01-Jan-90','dd-mon-rr');
 
SELECT nvl(to_char(commission_pct),'Komisyon Almıyor'), 
        nvl2(commission_pct,'Komisyon Alıyor','Komisyon Almıyor'),
        NULLIF(LENGTH(last_name),LENGTH(first_name)) AS dif,
        COALESCE(to_char(commission_pct),to_char(manager_id),'bişey yok')        
FROM employees;
 
SELECT last_name, job_id,
  CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
              WHEN 'ST_CLERK' THEN 1.15*salary
  ELSE salary
  END "CASEDEN DONEN SALARY", department_id
FROM employees;
 
SELECT last_name, job_id,
  decode(job_id,'IT_PROG',1.10*salary,
                'ST_CLERK',1.15*salary,
  salary)
  REVISED_SALARY
FROM employees;