LEARNING DATABASE (SQLSERVER QUERY) PART.5

SUMMARY [Part.2]

B. DATA MANIPULATION LANGUAGE

1. Insert

Format:

use [DATABASE_NAME];

insert into [TABLE_NAME] ([COLUMN_NAME_1], [COLUMN_NAME_2],...) values

([VALUE_OF_COLUMN_NAME_1], [VALUE_OF_COLUMN_2],...);

    Example:

    USE nilaiSiswa;

    INSERT INTO matkul (kodeMatkul, namaMatkul, sks) VALUES ('MK001', 'Networking', 4);

2. Update

Format:

use [DATABASE_NAME];

update [TABLE_NAME] set [COLUMN_NAME]=[VALUE] where [CONDITION];

    Example:

    USE nilaiSiswa;

    UPDATE matkul SET namaMatkul='Web Programming' WHERE kodeMatkul='MK001';

3. Delete

Format:

use [DATABASE_NAME];

delete from [TABLE_NAME] where [CONDITION](s);

    Example:

    USE nilaiSiswa;

    DELETE FROM matkul WHERE sks < 4 OR sks > 2;

4. Select

Format:

use [DATABASE_NAME];

select [distinc/*] [COLUMN_NAME] from [TABLE_NAME]

where [CONDITION]

group by [COLUMN_LIST]

having [CONDITION]

order by [COLUMN_LIST] [ASC/DESC]

    Example:

    USE nilaiSiswa;

    SELECT DISTINC username FROM username WHERE userStatusId=1

    AND userClassId <> 'ADM' ORDER BY username ASC;

Read More

LEARNING DATABASE (SQLSERVER QUERY) PART.4

SUMMARY [Part.1]

A. DATA DEFINITION LANGUAGE (DDL)

1. Create Database

Format:

use master;

create database [DATABASE_NAME];

     Example:

     USE master;

     CREATE DATABASE nilaiSiswa;

2. Create Table

     Format:

use [DATABASE_NAME];

create table [TABLE_NAME]

(COLUMN_NAME DATATYPE([RANGE]) PRIMARY KEY NOT NULL,

COLUMN_NAME DATATYPE([RANGE]) FOREIGN KEY REFERENCES [RELATION_TABLE_NAME]([COLUMN_NAME_FROM_OTHER_TABLE]) NOT NULL,

COLUMN_NAME DATATYPE[(RANGE)] [NOT NULL/NULL]);

     Example:

     USE nilaiSiswa;

     CREATE TABLE username

     (username varchar(10) PRIMARY KEY NOT NULL,

     userStatusId int FOREIGN KEY REFERENCES userStatus(userStatusId) NOT NULL,

     userClassId varchar(3) FOREIGN KEY REFERENCES userClass(userClassId) NOT NULL);

3. Create Index

Format:

use [DATABASE_NAME];

create index [INDEX_NAME_WE_NEED] on [TABLE_NAME] ([COLUMN_NAME]);

     Example:

     USE nilaiSiswa;

     CREATE INDEX useridx ON username(username);

4. Create View

Format:

use [DATABASE_NAME];

create view [VIEW_NAME] as select [COLUMN_NAME](s) from [TABLE_NAME] where [CONDITION]

     Example:

     USE nilaiSiswa;

     CREATE VIEW userview AS SELECT username, userClassId FROM username

     WHERE userStatusId=1;

5. Alter Table

Add a Column in a Table:

use [DATABASE_NAME];

alter table [TABLE_NAME]

add [COLUMN_NAME]([DATA_TYPE]);

    Example:

    USE nilaiSiswa; 

    ALTER TABLE username

    ADD sex char(1); 

Delete a Column in a Table:

use [DATABASE_NAME];

alter table [TABLE_NAME]

drop column [COLUMN_NAME];

    Example:

    USE nilaiSiswa;    

    ALTER TABLE username

    DROP COLUMN sex;

Change a Datatype of a Column in a Table:

use [DATABASE_NAME];

alter table [TABLE_NAME]

alter column [COLUMN_NAME] DATATYPE;

    Example:

    USE nilaiSiswa;

    ALTER TABLE username

    ALTER COLUMN username varchar(20);

6. Drop Database

Format:

use master;

drop database [DATABASE_NAME];

    Example:

    USE MASTER;

    DROP DATABASE nilaiSiswa;

7. Drop Table

Format:

use [DATABASE_NAME];

drop table [TABLE_NAME];

    Example:

    USE nilaiSiswa;

    DROP TABLE username;

8. Drop Index

Format:

use [DATABASE_NAME];

drop index [INDEX_NAME];

    Example:

    USE nilaiSiswa;

    DROP INDEX useridx;

9. Drop View

Format:

use [DATABASE_NAME];

drop view [VIEW_NAME];

    Example:

    USE nilaiSiswa;

    DROP VIEW userview;

Read More

LEARNING DATABASE (SQLSERVER QUERY) PART.3

Cases:

1. Show all from table "siswa".

2. Show all from table "matkul".

3. Show all from table "nilai".

4. Show only selected files we want.

5. Show "namaMatkul" that "sks" is two .

6. Show all from "nilai" that "uts" more than 90 or "uas" more than 85.
7. Show all from "siswa" where "namaDepan" have "s" letter

SELECT

Sintaks: SELECT [DISTINC column_name /ALL] FROM table_name

[WHERE condition(s)]

[GROUP BY column_list(s)]

[HAVING condition(s)]

[ORDER BY column_list [ASC/DESC]]

1. Show all from table "siswa"

    USE nilaiSiswa;

    SELECT * FROM siswa;

 

2. Show all from table "matkul"

    USE nilaiSiswa;

    SELECT * FROM matkul; 

 

3. Show all from table "nilai"

    USE nilaiSiswa;

    SELECT * FROM nilai;

 

4. Show only selected files we want [use inner join]

    SELECT nilai.idNilai, nilai.nim, siswa.namaDepan, siswa.namaBelakang,
    nilai.kodeMatkul, matkul.namaMatkul, matkul.sks, nilai.uts, nilai.uas
    FROM nilai
    INNER JOIN siswa ON nilai.nim=siswa.nim
    INNER JOIN matkul ON nilai.kodeMatkul=matkul.kodeMatkul;

Format inner join:

Select column_name(s)

from table1

inner join table2

on table1.column_name=table2.column_name; 

5. Show "namaMatkul" that "sks" is two

    SELECT DISTINCT namaMatkul FROM matkul WHERE sks=2;

6. Show all from "nilai" that "uts" more than 90 or "uas" more than 85

    SELECT * FROM nilai WHERE uts > 90 OR uas > 85. 

7. Show all from "siswa" where "namaDepan" have "s" letter
    SELECT * FROM siswa WHERE namaDepan LIKE '% s %';

Read More

LEARNING DATABASE (SQLSERVER QUERY) PART.2

Cases:
1. Fill table "siswa".
2. Fill table "matkul"
3. Fill table "nilai"

1. Fill Table "siswa"
    USE nilaiSiswa;
    INSERT INTO siswa (nim, namaDepan, alamatSiswa, tanggalLahir) VALUES
    ('20123302', 'Siswanto', 'Gresik', '1961-08-18');
    INSERT INTO siswa (nim, namaDepan, namaBelakang, alamatSiswa, tanggalLahir) VALUES
    ('20123303', 'Sri', 'Redjeki', 'Jakarta', '1965-10-25');
    INSERT INTO siswa (nim, namaDepan, namaBelakang, alamatSiswa, tanggalLahir) VALUES
    ('20123304', 'Fredi', 'Ferdiyanto', 'Jakarta', '1987-05-02');
    INSERT INTO siswa (nim, namaDepan, namaBelakang, alamatSiswa, tanggalLahir) VALUES
    ('20123300', 'Fandi', 'A Riyanto', 'Jakarta', '1988-08-06');
     INSERT INTO siswa (nim, namaDepan, namaBelakang, alamatSiswa, tanggalLahir) VALUES
    ('20123301', 'Dian', 'Anggraini', 'Jakarta', '1992-10-01');

The result is:


2. Fill Table "matkul"
    USE nilaiSiswa;
    INSERT INTO matkul (kodeMatkul, namaMatkul, sks) VALUES
    ('mk001', 'Algorythm Logic', 2);
    INSERT INTO matkul (kodeMatkul, namaMatkul, sks) VALUES
    ('mk002', 'Database Administrator', 4);
    INSERT INTO matkul (kodeMatkul, namaMatkul, sks) VALUES
    ('mk003', 'Web Programming', 4);
    INSERT INTO matkul (kodeMatkul, namaMatkul, sks) VALUES
    ('mk004', 'Networking', 4);
     INSERT INTO matkul (kodeMatkul, namaMatkul, sks) VALUES
    ('mk005', 'Graphic Design', 2);

The result is:

 
3. Fill Table "nilai"
    USE nilaiSiswa;
    INSERT INTO nilai (nim, kodeMatkul, uts, uas) VALUES
    ('20123300', 'mk005', 100, 100);
    INSERT INTO nilai (nim, kodeMatkul, uts, uas) VALUES
    ('20123301', 'mk004', 100, 100);
    INSERT INTO nilai (nim, kodeMatkul, uts, uas) VALUES
    ('20123302', 'mk003', 100, 100);
    INSERT INTO nilai (nim, kodeMatkul, uts, uas) VALUES
    ('20123303', 'mk002', 100, 100);

The Result is:

Read More