🗄️ Tutorial SQL Server

Belajar SQL Server dari DDL/DML hingga backup & restore.

🗄️

DDL & DML

Membuat tabel, insert, update, delete data.

Latihan 1: Buat database baru.

CREATE DATABASE db_school;

Latihan 2: Buat tabel dengan primary key dan foreign key.

CREATE TABLE students (
    id INT PRIMARY KEY IDENTITY(1,1),
    nama NVARCHAR(100),
    email NVARCHAR(100)
);

Latihan 3: Insert data ke tabel.

INSERT INTO students (nama, email)
VALUES ('Choirul', 'choirul@gmail.com');

Latihan 4: Update data di tabel.

UPDATE students
SET nama = 'Choirul Haq'
WHERE id = 1;

Latihan 5: Delete data dari tabel.

DELETE FROM students
WHERE id = 1;

🔗

Join

Menggabungkan data dari beberapa tabel.

Latihan 1: Buat dua tabel yang berelasi.

CREATE TABLE orders (
    id INT PRIMARY KEY,
    student_id INT,
    product NVARCHAR(100),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

Latihan 2: INNER JOIN — tampilkan data yang cocok dari kedua tabel.

SELECT s.nama, o.product
FROM students s
INNER JOIN orders o ON s.id = o.student_id;

Latihan 3: LEFT JOIN — tampilkan semua data dari tabel kiri.

SELECT s.nama, o.product
FROM students s
LEFT JOIN orders o ON s.id = o.student_id;

Latihan 4: RIGHT JOIN — tampilkan semua data dari tabel kanan.

SELECT s.nama, o.product
FROM students s
RIGHT JOIN orders o ON s.id = o.student_id;

Latihan 5: Aggregate functions — COUNT, SUM, AVG, MIN, MAX.

SELECT COUNT(*) AS total FROM students;
SELECT AVG(age) AS rata_usia FROM students;
SELECT MAX(age) AS tertua FROM students;

⚙️

Stored Procedure

Membuat stored procedure untuk operasi database.

Latihan 1: Buat stored procedure sederhana.

CREATE PROCEDURE sp_get_students
AS
BEGIN
    SELECT * FROM students;
END;

Latihan 2: Eksekusi stored procedure.

EXEC sp_get_students;

Latihan 3: Stored procedure dengan parameter.

CREATE PROCEDURE sp_get_student
    @id INT
AS
BEGIN
    SELECT * FROM students WHERE id = @id;
END;

Latihan 4: Stored procedure untuk insert data.

CREATE PROCEDURE sp_insert_student
    @nama NVARCHAR(100),
    @email NVARCHAR(100)
AS
BEGIN
    INSERT INTO students (nama, email)
    VALUES (@nama, @email);
END;

Latihan 5: Stored procedure dengan output parameter.

CREATE PROCEDURE sp_count_students
    @total INT OUTPUT
AS
BEGIN
    SELECT @total = COUNT(*) FROM students;
END;

💾

Backup & Restore

Backup dan restore database SQL Server.

Latihan 1: Backup database.

BACKUP DATABASE db_school
TO DISK = 'C:\backup\db_school.bak';

Latihan 2: Backup dengan compression.

BACKUP DATABASE db_school
TO DISK = 'C:\backup\db_school.bak'
WITH COMPRESSION;

Latihan 3: Restore database.

RESTORE DATABASE db_school
FROM DISK = 'C:\backup\db_school.bak'
WITH REPLACE;

Latihan 4: Backup differential.

BACKUP DATABASE db_school
TO DISK = 'C:\backup\db_school_diff.bak'
WITH DIFFERENTIAL;

Latihan 5: Restore differential.

RESTORE DATABASE db_school
FROM DISK = 'C:\backup\db_school.bak'
WITH NORECOVERY;

RESTORE DATABASE db_school
FROM DISK = 'C:\backup\db_school_diff.bak'
WITH RECOVERY;

← Kembali ke Profil