Writing Something

string cnnStr = "Server=.;Database=master;Trusted_Connection=Yes;";
 
private void Form1_Load(object sender, EventArgs e)
{            
    SqlDataAdapter da = new SqlDataAdapter("SELECT [name] FROM [master].[sys].[databases]", cnnStr);
 
    DataTable dt = new DataTable();
    da.Fill(dt);
 
    cbDatabases.DataSource = dt;
    cbDatabases.DisplayMember = "name";
    cbDatabases.ValueMember = "name";
}
 
private void cbDatabases_SelectedIndexChanged(object sender, EventArgs e)
{
    ComboBox mycb = (ComboBox)sender;
    if (!string.IsNullOrEmpty(mycb.Text) && mycb.Text != "System.Data.DataRowView")
    {
        SqlDataAdapter da = new SqlDataAdapter(string.Format(@"USE {0}; SELECT DISTINCT OBJECT_NAME(id) as ad,id FROM syscomments WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1 AND OBJECT_NAME(id) LIKE '%{1}%';", mycb.Text.Trim(), txtCriteria.Text.Trim()), cnnStr);
 
        DataTable dt2 = new DataTable();
        da.Fill(dt2);
 
        lbSp.DataSource = dt2;
        lbSp.DisplayMember = "ad";
        lbSp.ValueMember = "id";
    }
}
 
private void lbSp_SelectedIndexChanged(object sender, EventArgs e)
{
    ListBox myLb = (ListBox)lbSp;
    if (myLb.Text != "System.Data.DataRowView")
    {
        SqlDataAdapter da = new SqlDataAdapter(string.Format(@"USE {0}; SELECT [text] FROM syscomments WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1 AND OBJECT_NAME(id) = '{1}';", cbDatabases.Text.Trim(), myLb.Text), cnnStr);
 
        DataTable dt2 = new DataTable();
        da.Fill(dt2);
 
        txtSpText.Text = string.Empty;
 
        for (int i = 0; i < dt2.Rows.Count; i++)
        {
            txtSpText.Text += dt2.Rows[i][0].ToString();                    
        }
    }
}
 
private void txtCriteria_TextChanged(object sender, EventArgs e)
{
    SqlDataAdapter da = new SqlDataAdapter(string.Format(@"USE {0}; SELECT DISTINCT OBJECT_NAME(id) as ad,id FROM syscomments WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1 AND (OBJECT_NAME(id) LIKE '%{1}%' OR [text] LIKE '%{1}%');", cbDatabases.Text.Trim(), txtCriteria.Text.Trim()), cnnStr);
 
    DataTable dt2 = new DataTable();
    da.Fill(dt2);
 
    lbSp.DataSource = dt2;
    lbSp.DisplayMember = "ad";
    lbSp.ValueMember = "id";
}

DROP TABLE emp2;
 
SELECT original_name,
     operation,
     droptime
FROM recyclebin;
 
flashback TABLE emp2 TO before DROP;
 
--external tables
--c:\deneme diye bir klasör olustur içine
--emp.dat diye dosya olustur virgülle ayrilmis
CREATE directory deneme AS 'C:\deneme';
 
GRANT READ WRITE ON directory deneme TO hr;
 
CREATE TABLE oldemp (fname char(25), lname char(25))
organization external (type oracle_loader DEFAULT directory deneme 
access parameters (
records delimited BY newline 
nobadfile 
nologfile 
FIELDS terminated BY ','
fname position (1:20) char,
lname position (22:41) char))
location ('emp.dat'))
parallel 5
reject LIMIT 200;
 
--type oracle_loader > sadece okuma islerinde kullaniyoruz
--type oracle_datapump > import export islerinde datapump kullancagiz
 
--meta data sorgulari
SELECT * FROM dictionary WHERE table_name LIKE '%TABLE%';
SELECT * FROM dict;
 
SELECT * FROM user_tables;
SELECT * FROM user_objects;
SELECT * FROM user_views;
SELECT * FROM user_tab_columns;
 
SELECT * FROM all_tables;
 
SELECT * FROM dba_tables;
 
--performansla ilgili meta data gelir
SELECT * FROM v$database;
SELECT * FROM v$instance;
 
SELECT * FROM user_tab_comments;
 
comment ON TABLE employees IS 'Employee information';
 
UPDATE dept
SET manager_id = DEFAULT
WHERE departmen_id=10;
 
INSERT ALL 
  INTO sal_history VALUES(empid,hiredate,sal)
  INTO mgr_history VALUES(empid,mgr,sal)
  SELECT employee_id empid, hire_date hiredatei salary sal, manager_id mgr
  FROM employees
  WHERE employee_id > 200;  
 
INSERT ALL 
  when hiredate < '01-JAN-95' THEN
    INTO emp_history VALUES(empid,hiredate,sal)      
  when comm IS NOT NULL then
    INTO emp_sales VALUES(empid,comm,sal) 
SELECT employee_id empid, hire_date hiredatei salary sal, commission_pct comm
FROM employees;  
 
INSERT first 
  when hiredate < '01-JAN-95' THEN
    INTO emp_history VALUES(empid,hiredate,sal)      
  else
    INTO emp_sales VALUES(empid,comm,sal) 
SELECT employee_id empid, hire_date hiredatei salary sal, commission_pct comm
FROM employees;
 
merge INTO emp a USING (employees) b
ON (a.employee_id == b.employee_id)
when matched then UPDATE SET
a.first_name = b.first_name,
a.last_name = b.last_name,
......
DELETE WHERE (b.commission_pct IS NOT NULL)
when NOT matched then
INSERT VALUES (b.employee_id,b.first_name,b.last_name....);
 
UPDATE emp3 SET salary = salary * 1.30
WHERE employee_id =107;
 
commit;
 
--salary alaninda yapilan degisiklikleri görebiliriz
SELECT salary FROM emp3
  version BETWEEN scn minvalue AND maxvalue
WHERE employee_id = 107;
 
--5 dakika öncesini göster
SELECT salary FROM emp3 AS of timestamp sysdate-5/1440
WHERE employee_id =100;
 
ALTER session SET time_zone = '-2:00';
 
ALTER system SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
 
SELECT dbtimezone, sessiontimezone, current_date, 
          current_timestamp, localtimestamp 
FROM dual;
 
SELECT extract(year FROM sysdate) FROM dual;
 
SELECT tz_offset('US/Eastern') FROM dual;
 
SELECT from_tz(timestamp '2000-07-12 08:00:00','Australia/North') FROM dual;
 
SELECT to_timestamp('2000-07-12 08:00:00','YYYY-MM-DD HH:MI:SS') FROM dual;
 
SELECT hire_date, to_yminterval('01-02'), to_dsinterval('100 10:00:00')
FROM employees;
 
UPDATE employees e
SET department_name = (SELECT department_name 
  FROM departments d
  WHERE d.department_id == e.department_id);
 
SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name 
          FROM departments d
          WHERE e.department_id = d.department_id);
 
SELECT employee_id, last_name
FROM employees OUTER
WHERE EXISTS (SELECT 'X'
              FROM employees
              WHERE manager_id = OUTER.employee_id);
 
SELECT employee_id, last_name
FROM employees OUTER
WHERE NOT EXISTS (SELECT 'X'
              FROM employees
              WHERE manager_id = OUTER.employee_id);
 
UPDATE emp3 e
SET department_name = (SELECT department_name
                        FROM departments d
                        WHERE e.department_id = d.department_id);
 
DELETE FROM emp3 e
WHERE employee_id = (SELECT employee_id
                      FROM emp_history
                      WHERE employee_id = e.employee_id);
 
 
WITH summary AS
  (SELECT d.department_name dname,
     SUM(e.salary) total_dep
   FROM departments d,
     employees e
   WHERE e.department_id = d.department_id GROUP BY d.department_name)
  SELECT dname,
  total_dep
FROM summary
WHERE total_dep >
  (SELECT SUM(total_dep) *1 / 8
   FROM summary)
ORDER BY total_dep DESC

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;