sintax SQL tugas

TEKNIK INFORMATIKA / S -1 4003080002

TUGAS SQL

1. CREATE TABLE

Fungsi : membuat tabel

Sintaks : CREATE TABLE tbname

(col 1 data type data spec,

col 2 data type data spec,

2. CREATE VIEW

Sintaks : CREATE VIEW viewname (column1, column2, ……..)

AS SELECT statement FROM tbname

3. CREATE INDEX

Fungsi : membuat index

Sintaks : CREATE [UNIQUE] INDEX indexname ON nama_table (nama_kolom)

4. DROP TABLE

Fungsi : menghapus Tabel

Sintaks : DROP TABLE tbname

5. DROP VIEW

Fungsi : menghapus view

Sintaks : DROP VIEW viewname

6. DROP INDEX

Fungsi : menghapus index Sintaks : DROP INDEX indexname

7. ALTER

Fungsi : merubah atribut pada suatu tabel

SQL 5 Pengantar Basis Data

Sintaks : ALTER TABLE tbname

MODIFY (nama_kolom tipe_kolom) ADD (nama_kolom tipe_kolom [[before, nama_kolom]]) DROP (nama_kolom tipe_kolom)

DATA MANIPULATION LANGUAGE

1. INSERT

Fungsi : menambah baris (record) baru

Sintaks : INSERT INTO tbname (col1, ...) VALUES (value1, ...)

2. UPDATE

Fungsi : merubah record

Sintaks : UPDATE tbname SET field = ekspresi WHERE kondisi

3. DELETE

Fungsi : menghapus record

Sintaks : DELETE FROM tbname WHERE kondisi

4. SELECT

Fungsi : menampilkan record

Sintaks : SELECT [DISTINCT] colname FROM tbname [WHERE kondisi] [GROUP BY kondisi] [HAVING kondisi] [ORDER BY kondisi]

___________________________________________________________________________________

LATIHAN

create database aghy gp

use aghy gp

create table t_barang(kd_barang varchar(10),

nm_barang varchar (35),

harga decimal)

select*from t_barang

insert into t_barang

values ('A01','Buku',1000)

insert into t_barang

values ('A02','Pencil',500)

insert into t_barang

values ('K01','Hardisk',400000)

insert into t_barang

values ('K02','Mouse',50000)

select*from t_barang

where kd_barang like 'K%'

select*from t_barang

where nm_barang like 'pencil'

update t_barang

set nm_barang='Keyboard'

where kd_barang='K01'

delete t_barang

where kd_barang='A02'

select case

select kd_barang,nm_barang,case left(kd_barang,1)

when 'A' then 'ATK'

when 'K' then 'Komputer'

End katagori

from t_barang