NULLS LAST keyword usage

-> HR.EMPLOYEES will be test table.
SQL> 
DESC HR.EMPLOYEES;

e.g.
To populate all data
SQL> 
SELECT * FROM HR.EMPLOYEES;

To manipulate data by "COMMISSION_PCT" column descending order.
SQL> 
  SELECT *
    FROM HR.EMPLOYEES
ORDER BY COMMISSION_PCT DESC;

-> This is wrong query cause "NULL" columns show on top. Therefore, "NULLS LAST" keyword should be used.
SQL>
  SELECT *
    FROM HR.EMPLOYEES
ORDER BY COMMISSION_PCT DESC NULLS LAST;

SQL PLUS system parameters usage (set/show)

To show parameter in detail
SQL> SHOW DEFINE;

To escape from substitution variable (&):
SQL> SET DEFINE OFF;

To enable DBMS_OUTPUT:
SQL> SET SERVEROUTPUT ON;

To see old and new values of variables:
SQL> SET VERIFY ON;

To calculate total execution time of SQL:
SQL> SET TIMING ON;

To print time on the SQL terminal:
SQL> SET TIME ON;

To see table columns name and types for a table:
SQL> DESC hr.employees@XE;

To see currently logged-in user:
SQL> SHOW user;

How to see TOP 10 SQL that consuming system resources highly?

e.g.
select * from
(
select * from v$sql
order by DISK_READS desc
)
where rownum < 11

options for order by:
RUNTIME_MEM, EXECUTIONS, DISK_READS, SORTS, ELAPSED_TIME

for old querries:
select * from dba_hist_sqltext where sql_id = '8j2hv3c6wskdy'

for SQL plan:
select * from V$SQL_PLAN where sql_id = '8j2hv3c6wskdy'

find the object location of SQL
select * from all_source where owner <> 'SYS' and upper(text) like '%ABC%'

LISTAGG examples

SQL>

select LISTAGG (last_name, ', ')  WITHIN GROUP (order by last_name)

from employees

SQL>

select LISTAGG (first_name || ' ' || last_name, ', ') WITHIN GROUP (order by first_name)

from employees

SQL>

select LISTAGG (first_name || ' ' || salary, ', ') WITHIN GROUP (order by salary desc)

from employees

SQL>

select LISTAGG (first_name || ' ' || salary || ' ' || NVL2(commission_pct, commission_pct, '0'), ', ') WITHIN GROUP (order by salary desc)

from employees

How to use rowid for deleting data?

If you desire delete data from joined tables such as log table, you may use rowid to prevent delete unwanted rows.

e.g.
delete from tableA where rowid in (select a.rowid from tableA a, tableB b where a.columnA = b.columnB);

Oracle SQL Functions List - Most Used

Single-Row Functions

CONCAT

• SELECT CONCAT('Alp','er') from dual; -- Alper

INITCAP

• SELECT INITCAP('alper') from dual; -- Alper

LOWER

• SELECT LOWER('AlpeR') from dual; -- alper

• SELECT UPPER('alPer') from dual; -- ALPER

LENGTH

• SELECT LENGTH('Alper') from dual; -- 5

SUBSTR

• SELECT SUBSTR('Alper',4,2) from dual; -- er
• SELECT SUBSTR('Alper',-2,2) from dual; -- er
• SELECT SUBSTR('Alper',1) from dual; -- Alper

INSTR

• SELECT INSTR('Alper') from dual; -- 5
• SELECT INSTR('Alper','A',1,1) from dual; -- 1
• SELECT INSTR('Alper','e',-2,1) from dual; -- 4

LPAD

• SELECT LPAD(1000000,10,'$' ) from dual; -- $$$1000000

RPAD

• SELECT RPAD(1000000,10,'$' ) from dual; -- 1000000$$$

REPLACE

• SELECT REPLACE('AliVeli','iVeli','per') from dual; -- Alper

TRIM

• SELECT TRIM('A l p e r') from dual; -- Alper
• SELECT TRIM(leading 'X' from 'XXXXAlper') from dual; -- Alper
• SELECT TRIM(trailing 'Y' from 'AlperYYY') from dual; -- Alper

How to modify "sql page size"?

SQL> SET PAGESIZE 100

Anymore, there will be 100 lines for each page, after executes SQL queries.

How to run sql query in shell script?

Please add these codes into your .sh file then you can run .sh file

e.g. 
ssh -q -T oracle@192.168.2.2 << EOF
sqlplus / as sysdba << EOFSQL
drop user ALPER_DB_USER cascade;
exit;
EOFSQL
EOF

How to select top 10 records in Oracle 11g and 12c?

In 11g:

As you now that TOP keyword does not exist in 11g, but you can use rownum keyword to query top records.

For example, you want to see top 10 salary records based on employees:

Run that SQL in your editor's SQL window.

Wrong one;
SELECT *
FROM (
 SELECT employee_id, first_name, last_name, salary
 FROM employees
 )
WHERE rownum < 11
ORDER BY salary DESC;

* ORDER BY runs at last so that it should be in sub query.

Correct one;
SELECT *
FROM (
 SELECT employee_id, first_name, last_name, salary
 FROM employees
 ORDER BY salary DESC
 )
WHERE rownum < 11;

In 12c:
With 12c, you may use special syntax to see top records that is "FETCH" syntax.

SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

Also, you may disregard first 5 records inside 10 records.

SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 5 ROWS 
FETCH NEXT 5 ROWS ONLY;

That's all. Bye :)

Tablo içi verilerden kurtulma

Tablolarımızdan tamamen kurtulmak yerine (DROP) tablonun içindeki verilerden kurtulmak isteyebiliriz. Bunun için TRUNCATE sql komutunu kullanabiliriz.

Kullanımı gayet basittir.

TRUNCATE komutunu çalıştırmadan önce tabloda var olan yabancıl anahtarların (foreign keys) silinmesi gerekmektedir.

TRUNCATE TABLE [tablo_ismi]

Örneğin örnek veritabanımızdan Öğrenciler tablosunun içindeki verileri silmek isteyelim.

TRUNCATE TABLE [Ogrenciler]

Herkese iyi çalışmalar. J

T-SQL’de Constraint Konusu (Kısıtlar)

T-SQL Constraint konusunu inceleyemeye çalışacağız.

Kısıtlar çeşitlerine göre 5 başlıkta incelenmektedir. Bunlar;

• NOT NULL
• DEFAULT
• UNIQUE
• CHECK
• PRIMARY AND FOREIGN KEY

Sırayla bu ifadeler nasıl kullanılıyor aşağıdaki tablo yardımıyla bakalım.

CREATE TABLE Sinavlar

(

SinavID int NOT NULL UNIQUE,

FK_OgrenciID int REFERENCES Ogrenciler (OgrenciID) NOT NULL,

FK_DersID int REFERENCES Ders(DersID) NOT NULL,

Notu int NOT NULL CHECK (Notu =>0 AND Notu <=100),

Tarih date DEFAULT GETDATE()

)

NOT NULL:

Tablo içindeki istenilen verilerin boş kalmasını engellemek için kullanılan bir kısıttır.

• Sınavlar tablosunda SinavID ve Notu sütunlarının değerinin boş kalmaması istendi.

Tablo oluşturduktan sonra da kısıtlarımızı yazabiliriz;

ALTER TABLE Sinavlar

ALTER COLUMN Notu int NOT NULL

DEFAULT:

Tablo içindeki istenilen verilere herhangi bir değer atanmamışsa otomatik olarak bir değer yazılması anlamı taşıyan bir kısıttır.

• Sınavlar tablosunda Tarih sütununun değeri, eğer bir değer atanmazsa o anda tabloya girilen kayıt (insert işlemi) anındaki tarih bilgisi olması istendi.

Tablo sonrası kısıt yazımı;

ALTER TABLE Sinavlar

ALTER COLUMN Tarih SET DEFAULT GETDATE()

İlgili kısıdı tablodan kaldırmak için;

ALTER TABLE Sinavlar

ALTER COLUMN Tarih DROP DEFAULT

UNIQUE:

Tablo içindeki istenilen verilerin diğer verilerden farklı olmasını sağlayan bir kısıttır.

• Sınavlar tablosunda SinavID sütununun değeri, benzersiz (unique) olması sağlandı.

Tablo sonrası kısıt yazımı;

ALTER TABLE Sinavlar

ADD CONSTRAINT uq_SinavID UNIQUE (SinavID)

İlgili kısıdı tablodan kaldırmak için;

ALTER TABLE Sinavlar

DROP CONSTRAINT uq_SinavID

CHECK:

Tablo içindeki istenilen verilerin belirli bir kural aralığında olmasını sağlayan bir kısıttır.

• Sınavlar tablosunda Notu sütunun değeri 0 ila 100 arasında olması sağlandı.

Tablo sonrası kısıt yazımı;

ALTER TABLE Sinavlar

ADD CONSTRAINT chk_Not CHECK (Notu=>0 AND Notu=<=100)

İlgili kısıdı tablodan kaldırmak için;

ALTER TABLE Sinavlar

DROP CONSTRAINT uq_chk_Not

PRIMARY KEY:

Tablo içindeki her bir kaydın farklılığını gösteren, her bir satıra bir kimlik kazandıran bir anahtardır. Her bir tablo için en fazla bir adet primary key (birincil anahtar) tanımlanabilir ve birincil anahtar sütunu boş değer (null) alamaz. Unique ile primary key yaptıkları aynıdır. Primary key tanımlandığında otomatik olarak unique kısıdı tabloya eklenir. Ayrıca unique kısıdı birincil anahtardan farklı olarak istenilen sayıda bir tabloda tanımlanabilir.

Tablo sonrası kısıt yazımı;

ALTER TABLE Sinavlar

ADD CONSTRAINT pk_SinavID PRIMARY KEY (SinavID)

İlgili kısıdı tablodan kaldırmak için;

ALTER TABLE Sinavlar

DROP CONSTRAINT pk_SinavID

FOREIGN KEY:

Tabloları birbirine bağlamak için kullanılan bir anahtardır. Türkçe olarak İkincil veya yabancı anahtar denilebilir.

• Sınavlar tablosunda FK_OgrenciID ve FK_DersID sütununları Ogrenciler ve Ders tablolarındaki birincil anahtarlar ile eşleşmektedir.

Tablo sonrası kısıt yazımı;

ALTER TABLE Sinavlar

ADD CONSTRAINT fk_OgrenciID FOREIGN KEY (FK_OgrenciID)

REFERENCES Ogrenciler(OgrenciID)

İlgili kısıdı tablodan kaldırmak için;

ALTER TABLE Sinavlar

DROP CONSTRAINT fk_OgrenciID

Herkese iyi çalışmalar. J

T-SQL’de köşeli parantez kullanımı

T-SQL veritabanına yeni tablo eklerken tablo isminde iki veya daha fazla kelimelik ise bunun birleştirilmesi veya aralarına bir karakter(nokta, alt, üst çizgi, vb.) konulması sorgu sırasında karışıklık çıkmaması adına istenir. İlla tablo ismim aralarında boşluk olsun derseniz üzülmeyin köşeli parantez size yardım edecektir. J

Örnek olarak veri tabanımızdaki tablolunun ismi Calisanlar Iletisim olsun ve bu tabloda olan çalışanların ad ve soyadlarını tek sütunda gösterelim. Yazacağımız T-SQL kodlar aşağıdaki gibidir;

--köşeli parantez kullanımı

SELECT Ad + ' ' + Soyad AS [Ad ve Soyadı]

FROM [Calisanlar Iletisim]

Not: Köşeli parantez, veritabanı, saklı yordam, tetikleyiciler, vb. tanımlanırken veya sorgu sonucunda sütun isminin gösteriminde de kullanılabilir.

İyi çalışmalar. J

T-SQL’de Karakter Dizileri İncelemesi

T-SQL iki çeşit karakter dizisi vardır; bunlar character strings ve unicode character strings olarak geçer.

Character Strings üç başlıkta toplanır; bunlar char, varchar ve text gibidir.

Unicode Strings de üç başlıkta toplanır; bunlar nchar, nvarchar ve ntext gibidir.

Görüldüğü üzere Unicode da yalnızca başına n harfi eklenmiştir. Aralarındaki tek fark böyle ifade edilmektedir. Unicode kavramını daha önceki yazılarımızda belirtmiştik. Tekrar üzerinden geçmek gerekirse, kısaca bize daha fazla karakter/harf desteği sağlamaktadır.

Sırayla bunların ne olduğuna bakalım.

Character strings;

Char:

Tanımı: Sabit uzunlukta karakter dizisi

Tanımlandırılması: char (n) (n = karakter sayısı)

Bellekteki Kapladığı Alan: n kadar byte yani her bir karakter veri tipi 1 byte (n= 1 ila 8000 byte arasında yer kaplar.)

Bellekte Alabileceği En Fazla Yer: 8000 byte yer kaplar.

Varchar:

Tanımı: Değişken uzunlukta karakter dizisi

Tanımlandırılması: varchar (n | max) (n = karakter sayısı)

Bellekteki Kapladığı Alan: n kadar byte + fazladan 2 byte (n = 1+ (+2) ila 8000 byte arasında yer kaplar.)

Bellekte Alabileceği En Fazla Yer: varchar (max), Yaklaşık 1 gigabyte eş değer 1073741824 karakter kadar.

Text:

Tanımı: Sabit uzunlukta karakter dizisi ve servis sayfa kodlamalarında kullanılıyor. Microsoft ileride ntext ve image tipleri gibi bu tipi de tuşlamak istiyor. J

Tanımlandırılması: text (n) (n = karakter sayısı)

Bellekteki Kapladığı Alan: n kadar byte (n = 1 ila 8000 byte arasında yer kaplar.)

Bellekte Alabileceği En Fazla Yer: 8000 byte

Unicode strings;

Nchar:

Tanımı: Sabit uzunlukta Unicode karakter dizisi

Tanımlandırılması: nchar (n) (n = karakter sayısı)

Bellekteki Kapladığı Alan: n kadar byte yani her bir karakter veri tipi 1 byte (n = 1 ila 4000 byte arasında yer kaplar.)

Bellekte Alabileceği En Fazla Yer: 4000 byte yer kaplar.

NVarchar:

Tanımı: Değişken uzunlukta Unicode karakter dizisi

Tanımlandırılması: nvarchar (n | max) (n = karakter sayısı)

Bellekteki Kapladığı Alan: n * 2 kadar byte + fazladan 2 byte (n = 1 (*2) + (+2) ila 4000 byte arasında yer kaplar.)

Bellekteki Alabileceği En Fazla Yer: nvarchar(max), Yaklaşık yarım gigabyte’a eş değer 536870912 karakter kadar.

NText:

Tanımı: Sabit uzunlukta karakter dizisi ve servis sayfa kodlamalarında kullanılıyor. Microsoft ileride text ve image tipleri gibi bu tipi de tuşlamak istiyor. J

Tanımlandırılması:  ntext (n) (n = karakter sayısı)

Bellekteki Kapladığı Alan: n * 2 kadar byte (n = 1 (*2) ila 4000 byte arasında yer kaplar.)

Bellekteki Alabileceği En Fazla Yer: 4000 byte arasında yer kaplar.

Herkese iyi çalışmalar. J

SQL’de Aggregate Values (Toplam, Küme Değerleri)

SQL’de toplam değer ile ilgili sorgu yaparken Max, Min, Count gibi fonksiyonlar kullanılır. Kullanımı oldukça basittir burada önemli bir nokta toplam değer ile ilişkili veri sütunun group by komutu ile gruplanmasıdır.

Örnek olarak yazacağımız sql sorgusuyla Calisanlar tablomuzdan her bir departmanın en yüksek maaşlar listelensin.

-- Departmanların sahip olduğu en yüksek maaşlar

SELECT MAX(Maas) AS En Yuksek Maas, Departman

FROM Calisanlar

GROUP BY Departman

Eğer sorgumuzda koşul olmasını istersek having komutu kullanmamız gerekir. Yine örnek olarak Calisanlar tablomuzdan her bir departmanın 50000’den büyük en yüksek maaşlar listelensin.

-- Departmanların sahip olduğu 50000’den büyük en yüksek maaşlar

SELECT MAX(Maas) AS En Yuksek Maas, Departman

FROM Calisanlar

GROUP BY Departman

HAVING MAX(Maas) > 50000

Bu yazımızda sql kodlarında sıkça kullanılan toplam değerler konusunu inceledik. İyi çalışmalar. J