Lanjutan_Postgres_Pak_niRwan...2

ahhh....sebel nih? di upload na LOLA (loading lama)bgt. Maf ya bu gambar na gak bisa ditampilin? bis na LOLA bgt ci? nah ni lanjutan dari postgres na...

NULL Handling
SELECT * FROM friend ORDER BY age;
SELECT * FROM friend WHERE age > 0 ORDER BY age DESC;
SELECT * FROM friend WHERE age <> 99 ORDER BY age DESC;
SELECT * FROM friend WHERE age IS NULL ORDER BY age DESC;

NULL Handling(2)

INSERT INTO friend VALUES ('Jack', 'Burger', NULL, NULL, 27);

SELECT * FROM friend WHERE city = state;

Null values and blank srings

CREATE TABLE nulltest (name CHAR(20), spouse CHAR(20));

INSERT INTO nulltest VALUES ('Andy', '');

INSERT INTO nulltest VALUES ('Tom', NULL);

SELECT * FROM nulltest ORDER BY name;

SELECT * FROM nulltest WHERE spouse = '';

SELECT * FROM nulltest WHERE spouse IS NULL;

NULL Handling

Using Default Values

CREATE TABLE account ( name CHAR(20), balance NUMERIC(16,2) DEFAULT 0, active CHAR(1) DEFAULT 'Y', created TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

INSERT INTO account (name) VALUES ('Federated Builders');

SELECT * FROM account;

Coloum Table

A. Memberi nama kolom pada hasil query

SELECT firstname AS buddy FROM friend ORDER BY buddy;

B. Melakukan komputasi pada kolom

SELECT 1 + 3 AS total;

C. Membuat Komentar

-- a single line comment test=> /* a multiline

Menggunakan AND dan OR

SELECT * FROM friend WHERE firstname = 'Sandy' AND lastname = 'Gleason';

SELECT * FROM friend WHERE state = 'NJ' OR state = 'PA' ORDER BY firstname;

RANGE OF VALUES

SELECT * FROM friend WHERE age >= 22 AND age <= 25 ORDER BY firstname;

SELECT * FROM friend WHERE age BETWEEN 22 AND 25 ORDER BY firstname;

LIKE COMPARISON

SELECT * FROM friend WHERE firstname LIKE 'D%' ORDER BY firstname;

CASE CLAUSE

SELECT * FROM friend WHERE firstname ~ '^S' ORDER BY firstname;

SELECT * FROM friend WHERE firstname ~ '^.e' ORDER BY firstname;

CASE CLAUSE(2)

-- firstname contains b, B, c, or C SELECT * FROM friend WHERE firstname ~* '[bc]' ORDER BY firstname;

-- firstname does not contain s or S SELECT * FROM friend WHERE firstname !~* 's' ORDER BY firstname;

CASE Clause Example

SELECT firstname, age, CASE WHEN age >= 21 THEN 'adult' ELSE 'minor' END FROM friend ORDER BY firstname;

DISTINCT PREVENTS duplicates

SELECT state FROM friend ORDER BY state;

DISTINCT ROW (Complex case Example)

SELECT firstname, state, CASE WHEN state = 'PA' THEN 'close' WHEN state = 'NJ' OR state = 'MD' THEN 'far' ELSE 'very far' END AS distance FROM friend ORDER BY firstname;

DISTINCT PREVENT DUPLICATES

SELECT state FROM friend ORDER BY state;

SELECT DISTINCT state FROM friend ORDER BY state;

FUNCTION AND OPERATION

A. Function

\df

\df int

\df upper

\dd upper

select upper('jacket');

B. Operation

\do

\do /

\do ^

\dd ^

DATE STYLE.....

Untuk perintah yang ada pada date ini mohon maaf sekali tidak bisa di coba pada postgres...gak tau kenapa error melulu...kayaknya emang gak mau di coba...tapi biar pada tau ajah ni script na...

> SHOW DATESTYLE;

NOTICE: DateStyle is ISO with US (NonEuropean) conventions SHOW VARIABLE

> SET DATESTYLE TO 'SQL, EUROPEAN';

SET VARIABLE

> SHOW DATESTYLE;

NOTICE: DateStyle is SQL with European conventions SHOW VARIABLE

> RESET DATESTYLE;

RESET VARIABLE

> SHOW DATESTYLE;

NOTICE: DateStyle is ISO with US (NonEuropean) conventions SHOW VARIABLE