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;