Quis basisdata 1

Dita Tri Anggraini

1114013

TI D4 2A

Quis BasisData 1

·         Membuat table TR kode transaksi

create table tr_kodetransaksi

(c_trans number(5) primary key,

e_trans varchar2(50));

·         Mengisi data ke dalam TR kode transaksi

insert into tr_kodetransaksi (c_trans, e_trans)

values (1, 'setor');

insert into tr_kodetransaksi (c_trans, e_trans)

values (2, 'tarik');

insert into tr_kodetransaksi (c_trans, e_trans)

values (3, 'atm');

insert into tr_kodetransaksi (c_trans, e_trans)

values (4, 'pemindahan buku');

insert into tr_kodetransaksi (c_trans, e_trans)

values (5, 'koreksi plus');

insert into tr_kodetransaksi (c_trans, e_trans)

values (6, 'koreksi minus');

insert into tr_kodetransaksi (c_trans, e_trans)

values (7, 'bunga');

insert into tr_kodetransaksi (c_trans, e_trans)

values (8, 'administrasi biaya');

insert into tr_kodetransaksi (c_trans, e_trans)

values (9, 'pajak');

·         Membuat table nasabah

create table trans_job

(c_job varchar(10) primary key,

n_job varchar2(50));

insert into trans _job(c_job, n_job)

values ('Tel', 'teller');

insert into trans _job(c_job, n_job)

values ('MGR', 'manager');

insert into trans _job(c_job, n_job)

values ('SYS', 'system analis');

·         Membuat table tabungan

create table tm_tabungan

(I_TRANS VARCHAR2(20) primary key,

C_ TRANS NUMBER(5),

D_ TRANS DATE, 

V_DEBET varchar(25), 

V_KREDIT varchar(25), 

V_SALDO varchar(25), 

I_PETUGAS VARCHAR(10), 

I_REKENING VARCHAR(25));

·         Membuat foreign key tm_tabungan

alter table tm_tabungan add foreign key (c_trans) references tr_kodetransaksi (c_trans);

·         Membuat table petugas

create table tm_petugas

(i_petugas varchar(10) primary key,

n_petugas varchar2(50));

·         Memasukkan data petugas

insert into tm_petugas (i_petugas, n_petugas)

values (1, 'dita');

insert into tm_petugas (i_petugas, n_petugas)

values (2, 'mila');

·         Membuat foreign key tm_tabungan (i_petugas) ke  tm_petugas

alter table tm_tabungan add foreign key (i_petugas) references tm_petugas (i_petugas);

·         Membuat foreign key tm_tabungan (i_rekening) ke nasabah

alter table tm_tabungan add foreign key (i_rekening) references nasabah (i_rek);

·         Membuat table nasabah

create table nasabah

(I_REK VARCHAR(25) primary key,

N_NASABAH VARCHAR(50), 

A_NASABAH VARCHAR(50), 

D_LAHIR DATE, 

C_JOB VARCHAR(10), 

C_AGAMA VARCHAR(20)); 

·         Membuat foreign key nasabah (c_job) ke tr_job

alter table nasabah add foreign key (c_job) references tr_job (c_job);

·         Membuat table agama

create table agama

(c_agama varchar(20) primary key,

e_agama varchar2(50));

·         Memasukkan data pada table agama

insert into agama (c_agama,  e_agama)

values (1, 'islam');

insert into agama (c_agama, e_agama)

values (2, ‘hindu’);

·         Membuat foreign key dari table nasabah (c_agama) ke table agama

alter table nasabah add foreign key (c_agama) references agama (c_agama);

·         Memasukkan data nasabah

insert into nasabah (I_REK, N_NASABAH, A_NASABAH, D_LAHIR, C_JOB, C_AGAMA)

values (01, 'dita', 'jambi', '11-nov-92', 'MGR', 1);

·         Memasukkan data ke table tm_tabungan

insert into tm_tabungan (I_TRANS, C_ TRANS, D_ TRANS, V_DEBET, V_KREDIT, V_SALDO, I_PETUGAS, I_REKENING)

values ('trans01', 1, ‘25-oct-2012’, 10000, 5000, 5000, 1, 1);

insert into tm_tabungan (I_ TRANS, C_ TRANS, D_ TRANS, V_DEBET, V_KREDIT, V_SALDO, I_PETUGAS, I_REKENING)

values ('trans02', 1, '25-oct-2012', 5000, 2000, 3000, 1, 1);

·         Mengeluarkan data nasabah

select i_rek, n_nasabah, v_saldo

from nasabah

inner join tm_tabungan

on nasabah.i_rek=tm_tabungan.i_rekening or

where v_saldo=(select min (v_saldo) from tm_tabungan)

·         Menampilkan c_trans, e_transaksi, SUM(v_debet), SUM(v_kredit)

select tr_kodetransaksi.c_tr, tr_kodetransaksi.e_tr, sum(tm_tabungan.v_debet), sum(tm_tabungan.v_kredit) from tm_tabungan inner join tr_kodetransaksi on tr_kodetransaksi.c_tr=tm_tabungan.c_tr group by tr_kodetransaksi.c_tr, tr_kodetransaksi.e_tr

·         Menampilkan c_trans, e_transaksi, SUM(v_debet), SUM(v_kredit) periode jaminan 2012 s/d Des 2012

.select tr_kodetransaksi.c_ trans, tr_kodetransaksi.e_ trans, sum(tm_tabungan.v_debet), sum(tm_tabungan.v_kredit) from tm_tabungan inner join tr_kodetransaksi on tr_kodetransaksi.c_ trans =tm_tabungan.c_ trans where d_ trans between '01-jan-2012' and '31-dec-2012' group by tr_kodetransaksi.c_ trans,tr_kodetransaksi.e_ trans;

·         Menampilkan jumlah

select count (*) as "jumlah" , c_trans from tm_tabungan group by c_trans

Syntax DML

Syntax DML

SELECT column_name(s) FROM table_name
SELECT * FROM table_name
-----------------------------

SQL SELECT DISTINCT Syntax :

SELECT DISTINCT column_name(s) FROM table_name
SELECT DISTINCT City FROM Persons
-----------------------------

SQL WHERE Syntax :
SELECT column_name(s) FROM table_name WHERE column_name operator value
Contoh :
SELECT * FROM Persons WHERE City='Sandnes'
----------------------------------------

The AND & OR Operators :

SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson'
SELECT * FROM Persons WHERE FirstName='Tove' OR FirstName='Ola'
--------------------------------

INSERT INTO Syntax :

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1,

value2, value3,...)
Contoh :
INSERT INTO Persons VALUES (4,'Nilsen', 'Johan', 'Bakken 2',

'Stavanger')
-----------------

Order by syntax :

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC
Contoh :
SELECT * FROM Persons ORDER BY LastName
--------------------------------

SQL UPDATE Syntax :

UPDATE table_name SET column1=value, column2=value2,... WHERE

some_column=some_value
Contoh :
UPDATE Persons SET Address='Nissestien 67', City='Sandnes' WHERE

LastName='Tjessem' AND FirstName='Jakob'
----------------

SQL DELETE Syntax :

DELETE FROM table_name WHERE some_column=some_value
Contoh :
DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob'
--------------

SQL Server Syntax :

SELECT TOP number|percent column_name(s) FROM table_name
SELECT column_name(s) FROM table_name LIMIT number
SELECT * FROM Persons LIMIT 5
-------------

SQL LIKE Syntax :

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
Contoh :
SELECT * FROM Person WHERE City LIKE 's%'
SELECT * FROM Persons WHERE City LIKE '%s'
SELECT * FROM Persons WHERE City LIKE '%tav%'
SELECT * FROM Persons WHERE City NOT LIKE '%tav%'
-----------------------

SQL IN Syntax :

SELECT column_name(s) FROM table_name WHERE column_name IN

(value1,value2,...)
----------------

SQL BETWEEN Syntax :

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1

AND value2
---------------

SQL Alias Syntax for Tables :

SELECT column_name(s) FROM table_name AS alias_name

SQL Alias Syntax for Columns :

SELECT column_name AS alias_name FROM table_name
Contoh :
SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p,
Product_Orders AS po WHERE p.LastName='Hansen' AND p.FirstName='Ola'
------------

SQL INNER JOIN Syntax :

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON

table_name1.column_name=table_name2.column_name
--------------

SQL LEFT JOIN Syntax :

SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON

table_name1.column_name=table_name2.column_name
-------------

SQL RIGHT JOIN Syntax :

SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON

table_name1.column_name=table_name2.column_name
-----------------

SQL FULL JOIN Syntax :

SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON

table_name1.column_name=table_name2.column_name
----------------

SQL UNION Syntax :

SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM

table_name2

SQL UNION ALL Syntax :

SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s)

FROM table_name2
-----------------

SQL SELECT INTO Syntax :

SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename
SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM

old_tablename
-----------------

SQL CREATE DATABASE Syntax :

CREATE DATABASE database_name
----------------

SQL CREATE TABLE Syntax :
Contoh :
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

contoh :
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Bandung',
UNIQUE (P_Id)
)
----------------

SQL CREATE INDEX Syntax :

CREATE INDEX index_name ON table_name (column_name) "Value duplikasi

diperbolehkan"
CREATE UNIQUE INDEX index_name ON table_name (column_name) "Duplikasi

tidak diperbolehkan"
----------------

DROP INDEX Syntax for MS Access:
DROP INDEX index_name ON table_name

DROP INDEX Syntax for MS SQL Server:
DROP INDEX table_name.index_name

DROP INDEX Syntax for MySQL:
ALTER TABLE table_name DROP INDEX index_name

DROP TABLE table_name
DROP DATABASE database_name
-----------------

SQL ALTER TABLE Syntax :

tambah kolom dalam tabel:
ALTER TABLE table_name ADD column_name datatype

Hapus Kolom dalam tabel :
ALTER TABLE table_name DROP COLUMN column_name
-----------------

SQL CREATE VIEW Syntax :

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE

condition

Contoh :
CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName FROM

Products WHERE Discontinued=No

syntax DDL

Syntax DDL

•  Tampilkan nama mahasiswa untuk nama mahasiswa yang karakter keduanya huruf ”u”

SELECT Nama FROM MHS WHERE Nama LIKE ”_U%”;

•  Tampilkan alamat mahasiswa, dimana alamat yang sama hanya ditampilkan satu kali saja

SELECT DISTINCT Alamat FROM MHS;

•  Tampilkan semua data mahasiswa dengan nama terurut dari ”z” ke ”a”

SELECT * FROM MHS ORDER BY Nama DESC;

•  Tampilkan alamat dan jumlah mahasiswa yang bertempat tinggal dialamat tersebut

SELECT Alamat, COUNT(*) FROM MHS GROUP BY
Alamat;

•  Tampilkan alamat dan jumlah mahasiswa yang bertempat tinggal pada alamat yang jumlahnya lebih dari 1

SELECT Alamat, COUNT (*) FROM MHS GROUP BY
ALAMAT HAVING COUNT (*) > 1;

•  Tampilkan nilai mid tertinggi dan terendah untuk mata kuliah ”KD132”

SELECT MAX(MID), MIN(MID) FROM NILAI WHERE
KDMK=”KD132”;

•  Tampilkan rata-rata dan jumlah nilai final mata kuliah ”KD132”

SELECT AVG(FINAL), SUM(FINAL) FROM NILAI WHERE
KDMK=”KD132”;

Contoh lebih dari 1 tabel
1. Tampilkan nama mahasiswa yang mempunyai nilai mid < 60
SELECT NAMA FROM MHS WHERE NPM IN
(SELECT NPM FROM NILAI WHERE MID < 60);

JOIN : penggabungan kolom
SELECT NAMA FROM MHS, NILAI
WHERE MHS.NPM = NILAI.NPM AND NILAI.MID 75
SELECT NPM FROM MHS WHERE NAMA = ”BUDI”
UNION SELECT NPM FROM NILAI WHER FINAL > 75;

TUGAS CDM DAN PDM KOPERASI

1. CDM Koperasi

2. PDM Koperasi

buat apa ya?????????