TULISAN INI ADALAH PRAKTIK DARI PEMILIK BLOG , BELUM SEMPAT MEMBERIKAN PENJELASAN HANYA SEKEDAR DOKUMENTASI.
MEMULAI FIREBIRD
MEMULAI FIREBIRD
Firebird basic
C:\Program
Files\firebird\firebird_2_1\bin>ISQL
Use CONNECT or
CREATE DATABASE to specify a database
SQL> CREATE DATABASE 'D:\CENTRAL\DATA.fdb' user 'sysdba'
password 'masterkey'
SQL> show database;
Database: D:\CENTRAL\DATA.fdb
Owner:
SYSDBA
PAGE_SIZE 4096
Number of DB pages allocated = 196
Sweep interval = 20000
Forced Writes are ON
Transaction - oldest = 5
Transaction - oldest active = 6
Transaction - oldest snapshot = 6
Transaction - Next = 15
ODS = 11.1
Default Character set: NONE
SQL> connect 'D:\CENTRAL\DATA.fdb' user 'sysdba' password
'masterkey';
Commit current
transaction (y/n)?y
Committing.
Database: 'D:\CENTRAL\DATA.fdb', User: sysdba
SQL> show
tables;
There are no
tables in this database
SQL> CREATE
TABLE DATA(ID CHAR(2), JUMLAH INT);
SQL> SHOW
TABLES;
DATA
SQL> INSERT INTO DATA VALUES('01',10);
SQL> INSERT INTO DATA VALUES('01',20);
SQL> INSERT INTO DATA VALUES('01',30);
SQL> INSERT INTO DATA VALUES('01',50);
SQL> INSERT INTO DATA VALUES('02',60);
SQL> INSERT INTO DATA VALUES('03',60);
SQL> INSERT INTO DATA VALUES('03',70);
SQL> INSERT INTO DATA VALUES('02',70);
SQL> INSERT INTO DATA VALUES('06',70);
SQL> INSERT INTO DATA VALUES('05',70);
SQL> INSERT INTO DATA VALUES('05',60);
SQL> INSERT INTO DATA VALUES('05',90);
SQL> SELECT*FROM DATA
ID JUMLAH
====== ============
01 10
01 20
01 30
01 50
02 60
03 60
03 70
02 70
06 70
05 70
05 60
05 90
SQL> CREATE TABLE tab( t time, d date, ts timestamp );
SQL> INSERT INTO tab(t,d,ts) VALUES ('14:59:23',
'2007-12-31', '2007-12-31 14:59');
SQL> SHOW TABLES;
DATA TAB
SQL> SELECT*FROM TAB;
T D TS
============= =========== =========================
14:59:23.0000 2007-12-31
2007-12-31 14:59:00.0000
SQL> SELECT
CAST(t as varchar(13)), CAST(d as varchar(10)), CAST(ts as varchar(24))
CON> FROM TAB;
CAST
CAST CAST
============= ========== ========================
14:59:23.0000 2007-12-31 2007-12-31 14:59:00.0000
SQL> SELECT D,EXTRACT(MONTH FROM D) FROM TAB;
D EXTRACT
=========== =======
2007-12-31 12
SQL> select avg(jumlah) from data;
AVG
=====================
55
SQL> select avg(jumlah) from data group by id;
AVG
=====================
27
65
65
73
70
SQL> select max(jumlah) from data;
MAX
============
90
SQL> select id,avg(jumlah) from data having
avg(jumlah)>(select avg(jumlah) from data) group by id;
Statement failed, SQLCODE = -104
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 83
-group
SQL>
select id,avg(jumlah) from data group by id
having avg(jumlah)>(select avg(jumlah) from data) ;
ID AVG
====== =====================
02
65
03
65
05
73
06
70
SQL> select
id,(select avg(jumlah)from data),avg(jumlah) from data group by id having avg(jumlah)>(select
avg(jumlah) from data) ;
ID
AVG AVG
====== ===================== =====================
02
55 65
03
55 65
05 55 73
06
55 70
SQL> select id,(select avg(jumlah)from data)as rata-rata,avg(jumlah)
from data group by id having avg(jumlah)>(select avg(jumlah) from data) ;
Statement failed, SQLCODE = -104
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 47
--
SQL> select
id,(select avg(jumlah)from data)as rata_rata,avg(jumlah)as diatas_rata from
data group by id having avg
(jumlah)>(select
avg(jumlah) from data) ;
ID
RATA_RATA DIATAS_RATA
====== ===================== =====================
02
55 65
03
55 65
05
55 73
06
55 70
SQL> select
id,(select avg(jumlah)from data)as rata_rata,avg(jumlah)as dibawah_rata from
data group by id having avg(jumlah)<(select avg(jumlah) from data) ;
ID
RATA_RATA DIBAWAH_RATA
====== ===================== =====================
01
55 27
SQL> select
avg(jumlah) from data where id='01';
AVG
=====================
27
SQL>
SQL> CREATE
TABLE EMPLOYEE(NAMA CHAR(20),
CON>
TANGGAL_LAHIR DATE);
SQL> SHOW TABLES;
DATA EMPLOYEE
TAB
SQL> INSERT INTO EMPLOYEE
VALUES('ELLEN','1993-09-12');
SQL> INSERT INTO EMPLOYEE VALUES('ELLEN
AP','1993-04-12');
SQL> INSERT INTO EMPLOYEE
VALUES('PURWANTO','1988-11-11');
SQL> SELECT*FROM EMPLOYEE
CON> ;
NAMA
TANGGAL_LAHIR
==================== =============
ELLEN
1993-09-12
ELLEN AP
1993-04-12
PURWANTO
1988-11-11
SQL> SELECT
*FROM EMPLOYEE WHERE EXTRACT(MONTH FROM TANGGAL_LAHIR)='11';
NAMA
TANGGAL_LAHIR
==================== =============
PURWANTO
1988-11-11
SQL> SELECT
*FROM EMPLOYEE WHERE EXTRACT(MONTH FROM TANGGAL_LAHIR)='11' OR EXTRACT(MONTH
FROM TANGGAL_LAHIR)='04';
NAMA
TANGGAL_LAHIR
==================== =============
ELLEN AP
1993-04-12
PURWANTO
1988-11-11
SQL> SELECT
*FROM EMPLOYEE WHERE EXTRACT(DAY FROM TANGGAL_LAHIR)='11' ;
NAMA TANGGAL_LAHIR
==================== =============
PURWANTO
1988-11-11
SQL> SELECT
*FROM EMPLOYEE WHERE EXTRACT(DAY FROM TANGGAL_LAHIR)='11' AND EXTRACT(MONTH
FROM TANGGAL_LAHIR)='11' ;
NAMA
TANGGAL_LAHIR
==================== =============
PURWANTO
1988-11-11
SQL> SELECT
*FROM EMPLOYEE WHERE EXTRACT(YEAR FROM TANGGAL_LAHIR)='1993' ;
NAMA
TANGGAL_LAHIR
==================== =============
ELLEN
1993-09-12
ELLEN AP 1993-04-12
SQL>
SQL> SELECT
EXTRACT(YEAR FROM TANGGAL_LAHIR) AS TAHUN,EXTRACT(MONTH FROM TANGGAL_LAHIR) AS
BULAN,EXTRACT(DAY FROM TANGGAL_LAHIR) AS TANGGAL FROM EMPLOYEE;
TAHUN BULAN TANGGAL
======= ======= =======
1993 9
12
1993 4
12
1988 11
11
No comments:
Post a Comment