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 %';