MEMULAI ORACLE 10G

TULISAN INI HANYA BERISI SCRIPT TANPA PENJELASAN, HASIL DOKUMENTASI PEMBELAJARAN PEMILIK BLOG.

Microsoft Windows [Version 6.1.7600]

Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Asus>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Kam Des 15 09:05:39 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: purwanto

Enter password:

Connected to:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> SELECT TABLE_NAME FROM USER_TABLES;

TABLE_NAME

------------------------------

BARANG

DATA

SQL> DESC DATA;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ID                                                 VARCHAR2(4)

 JUMLAH                                             NUMBER(3)

SQL> SELECT*FROM DATA;

ID       JUMLAH

---- ----------

01           10

01           20

01           10

01           50

02           50

02          110

02          130

02           10

03           60

9 rows selected.

SQL> SELECT COUNT(ID) FROM DATA;

 COUNT(ID)

----------

         9

SQL> SELECT DISTINC(JUMLAH) FROM DATA;

SELECT DISTINC(JUMLAH) FROM DATA

       *

ERROR at line 1:

ORA-00904: "DISTINC": invalid identifier

SQL> SELECT ID FROM DATA WHERE JUMLAH>100;

ID

----

02

02

SQL> SELECT ID,JUMLAH FROM DATA WHERE JUMLAH>100;

ID       JUMLAH

---- ----------

02          110

02          130

SQL> SELECT ID,JUMLAH FROM DATA HAVING SUM(JUMLAH)>200 GROUP BY ID;

SELECT ID,JUMLAH FROM DATA HAVING SUM(JUMLAH)>200 GROUP BY ID

          *

ERROR at line 1:

ORA-00979: not a GROUP BY expression

SQL> SELECT ID,JUMLAH FROM DATA HAVING SUM(JUMLAH)>200;

SELECT ID,JUMLAH FROM DATA HAVING SUM(JUMLAH)>200

       *

ERROR at line 1:

ORA-00937: not a single-group group function

SQL> SELECT ID FROM DATA HAVING SUM(JUMLAH)>200 GROUP BY ID

  2  ;

ID

----

02

SQL> SELECT ID,SUM(JUMLAH) FROM DATA HAVING SUM(JUMLAH)>200 GROUP BY ID;

ID   SUM(JUMLAH)

---- -----------

02           300

SQL> SELECT ID,AVG(JUMLAH) FROM DATA HAVING AVG(JUMLAH)>(SELECT AVG(JUMLAH)FROM DATA);

SELECT ID,AVG(JUMLAH) FROM DATA HAVING AVG(JUMLAH)>(SELECT AVG(JUMLAH)FROM DATA)

       *

ERROR at line 1:

ORA-00937: not a single-group group function

SQL> SELECT ID,AVG(JUMLAH) FROM DATA HAVING AVG(JUMLAH)>(SELECT AVG(JUMLAH)FROM DATA)

  2  GROUP BY ID;

ID   AVG(JUMLAH)

---- -----------

02            75

03            60

SQL> SELECT ID,AVG(JUMLAH),(SELECT AVG(JUMLAH) FROM DATA)AS RATA FROM DATA HAVING AVG(JUMLAH)>(SELECT AVG(JUMLAH)FROM DATA) GROUP BY ID;

ID   AVG(JUMLAH)       RATA

---- ----------- ----------

02            75         50

03            60         50

SQL> SELECT ID,AVG(JUMLAH)AS DIATAS_RATA,(SELECT AVG(JUMLAH) FROM DATA)AS RATA FROM DATA HAVING AVG(JUMLAH)>(SELECT

 AVG(JUMLAH)FROM DATA)

  2  GROUP BY ID;

ID   DIATAS_RATA       RATA

---- ----------- ----------

02            75         50

03            60         50

SQL>

ORACLE TIPE DATA

SQL> CREATE TABLE ORANG(

  2  NAMA VARCHAR2(50),

  3  ALAMAT VARCHAR2(100),

  4  JENIS_KELAMIN VARCHAR2(1) CHECK(JENIS_KELAMIN IN('L','P')),

  5  TGL_LAHIR DATE,

  6  NO_TELP CHAR(12)

  7  );

Table created.

SQL> DESC ORANG;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------

 NAMA                                               VARCHAR2(50)

 ALAMAT                                             VARCHAR2(100)

 JENIS_KELAMIN                                      VARCHAR2(1)

 TGL_LAHIR                                          DATE

 NO_TELP                                            CHAR(12)

SQL>

SQL> INSERT INTO ORANG VALUES('ELLEN','BANTUL JOGJAKARTA','P',TO_DATE('1989-12-09','YYYY-MM-DD'),'082237800223');

1 row created.

ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD'

 

C:\Users\Asus>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Jum Des 16 08:38:39 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: purwanto

Enter password:

Connected to:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select*from data;

ID       JUMLAH

---- ----------

01           10

01           20

01           10

01           50

02           50

02          110

02          130

02           10

03           60

9 rows selected.

SQL> select max(jumlah)from data;

MAX(JUMLAH)

-----------

        130

SQL> select avg(jumlah)from data;

AVG(JUMLAH)

-----------

         50

SQL> select avg(jumlah) from data group by id;

AVG(JUMLAH)

-----------

       22,5

         75

         60

SQL> select id,avg(jumlah)as diatasrata,(select avg(jumlah)from data)as rata_rat

a from data group by id having avg(jumlah)>(select avg(jumlah)from data);

ID   DIATASRATA  RATA_RATA

---- ---------- ----------

02           75         50

03           60         50

SQL> select id,avg(jumlah)as dibawahrata,(select avg(jumlah)from data)as rata_ra

ta from data group by id having avg(jumlah)<(select avg(jumlah)from data);

ID   DIBAWAHRATA  RATA_RATA

---- ----------- ----------

01          22,5         50

SQL>  CREATE TABLE EMPLOYEE(NAMA CHAR(20),

  2   TANGGAL_LAHIR DATE);

SQL> INSERT INTO EMPLOYEE VALUES('ELLEN',TO_DATE('1993-09-12','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO EMPLOYEE VALUES('PURWANTO',TO_DATE('1988-11-11','YYYY-MM-DD'));

1 row created.

SQL> select nama,tanggal_lahir,extract (day from tanggal_lahir) as hari, extract(month from tanggal_lahir) as bulan, extract(year from anggal_lahir

)as tahun from employee;

NAMA                 TANGGAL_LA       HARI      BULAN      TAHUN

-------------------- ---------- ---------- ---------- ----------

ELLEN                12-09-1993         12          9       1993

PURWANTO             11-11-1988         11         11       1988

SQL>

SQL> select*from employee where extract(year from tanggal_lahir)='1988';

NAMA                 TANGGAL_LA

-------------------- ----------

PURWANTO             11-11-1988

SQL>