Pembuatan Tabel, Database, dan Operasi DML

1. Pembuatan Database :

Create Database 0872071_BDL_F

Use 0872071_BDL_F

2. Pembuatan Tabel :

create table dbo.tblKaryawan

(

      NIK int Primary Key Not NULL,

      Nama varchar (50) NULL,

      Alamat varchar (50) NULL,

      Kota varchar (50) NULL

)

create table dbo.tblGaji

(

      ID_Gaji int identity primary key not null,

      NIK int foreign key references tblKaryawan (NIK) on update cascade on delete cascade,

      Periode     varchar (20) not null,

      Jumlah_Gaji int not null

)

3a. Operasi Insert Tabel Karyawan :

INSERT INTO [0872071_BDL_F].[dbo].[tblKaryawan]

           ([NIK]

           ,[Nama]

           ,[Alamat]

           ,[Kota])

     VALUES

           (10001, 'Alex', 'Jl. Banda No.10', 'Bandung')

GO

INSERT INTO [0872071_BDL_F].[dbo].[tblKaryawan]

           ([NIK]

           ,[Nama]

           ,[Alamat]

           ,[Kota])

     VALUES   

           (10002, 'Budi', 'Jl. Merdeka No.92', 'Jakarta')        

GO

INSERT INTO [0872071_BDL_F].[dbo].[tblKaryawan]

           ([NIK]

           ,[Nama]

           ,[Alamat]

           ,[Kota])

     VALUES

           (10003, 'Rudi', 'Jl. Surya Sumantri No.61', 'Bandung')         

GO

INSERT INTO [0872071_BDL_F].[dbo].[tblKaryawan]

           ([NIK]

           ,[Nama]

           ,[Alamat]

           ,[Kota])

     VALUES

           (10004, 'Rini', 'Jl. Jl.Aceh No.1', 'Bandung')         

GO

Create Database 0872071_BDL_F

INSERT INTO [0872071_BDL_F].[dbo].[tblKaryawan]

           ([NIK]

           ,[Nama]

           ,[Alamat]

           ,[Kota])

     VALUES

           (10005, 'Nina', 'Jl. Sudirman No.76', 'Jakarta')

GO

 3b. Operasi Insert Tabel Gaji :

INSERT INTO [0872071_BDL_F].[dbo].[tblGaji]

           ([NIK]

           ,[Periode]

           ,[Jumlah_Gaji])

     VALUES

           (10001, 'Maret',5000000)

GO

INSERT INTO [0872071_BDL_F].[dbo].[tblGaji]

           ([NIK]

           ,[Periode]

           ,[Jumlah_Gaji])

     VALUES

           (10003, 'April',3000000)

GO

INSERT INTO [0872071_BDL_F].[dbo].[tblGaji]

           ([NIK]

           ,[Periode]

           ,[Jumlah_Gaji])

     VALUES

           (10004, 'Maret',4500000)

GO

INSERT INTO [0872071_BDL_F].[dbo].[tblGaji]

           ([NIK]

           ,[Periode]

           ,[Jumlah_Gaji])

     VALUES

           (10003, 'Januari',4000000)

GO

INSERT INTO [0872071_BDL_F].[dbo].[tblGaji]

           ([NIK]

           ,[Periode]

           ,[Jumlah_Gaji])

     VALUES

           (10001, 'April',3250000)

GO

INSERT INTO [0872071_BDL_F].[dbo].[tblGaji]

           ([NIK]

           ,[Periode]

           ,[Jumlah_Gaji])

     VALUES

           (10002, 'Mei',2750000)

GO

 4. Operasi Update Tabel Karyawan :

UPDATE [0872071_BDL_F].[dbo].[tblKaryawan]

   SET [Nama] = 'Robert'

 WHERE NIK = 10002

GO

5a. Delete Kolom

DELETE FROM [0872071_BDL_F].[dbo].[tblKaryawan]

      WHERE NIK = 10005

GO

 5b. Delete Tabel

DELETE FROM tblKaryawan

6. Delete Tabel

SELECT     dbo.tblGaji.*, dbo.tblKaryawan.*

FROM       dbo.tblGaji INNER JOIN

           dbo.tblKaryawan ON dbo.tblGaji.NIK = dbo.tblKaryawan.NIK

WHERE     (dbo.tblGaji.Periode = 'Maret')

7. Pembuatan Tabel (Advanced version)

create table dbo.TPegawai
(
    [NIK] [nchar](7) Primary Key NOT NULL,
    [Nama] [nvarchar](50) NULL,
    [Alamat] [nvarchar](80) NULL,
    [Telepon] [nvarchar] (20) NULL,
    [HP] [nvarchar] (20) NULL,
)

create table dbo.TPendidikan
(
    Pendidikan nvarchar (10) PRIMARY KEY NOT NULL,
    Nama nvarchar (20) NULL,
    Lulus nvarchar (15) NULL,
    TahunMasuk datetime NULL,
    TahunKeluar datetime NULL
)

create table dbo.TSekolah
(
    IDSekolah nvarchar (10) Primary Key NOT NULL,
    NamaSekolah nvarchar (20) NULL
)

create table dbo.TBidang
(
    IDBidang nvarchar (10) Primary Key NOT NULL,
    NamaBidang nvarchar (20) NULL
)

create table dbo.TRiwayatPendidikan
(
    [NIK] [nchar](7) NOT NULL foreign key references TPegawai (NIK),
    [Pendidikan] [nvarchar](10) NOT NULL foreign key references TPendidikan (Pendidikan),
    [IDSekolah] [nvarchar](10) NOT NULL foreign key references TSekolah (IDSekolah),
    [IDBidang] [nvarchar] (10) NOT NULL foreign key references TBidang (IDBidang),
   
     CONSTRAINT [PK_TRiwayatPendidikan] PRIMARY KEY CLUSTERED
(
    [NIK] ASC,
    [Pendidikan] ASC,   
    [IDSekolah] ASC,
    [IDBidang] ASC
)
)