STRUCTURE QUERY LANGUAGE


Di bawah ini dituliskan syntax dan sedikit contoh penggunaan Structure Query Language (SQL)

  1. Statement SELECT
    1. Menampilkan data:
    2. SELECT namaKolom1,namaKolom2 FROM namaTabel
      Contoh:
      SELECT * FROM barang1.

    3. Menyaring data dengan WHERE
    4. SELECT namaKolom FROM namaTabel WHERE kondisi
      Contoh:
      SELECT namaBarang,jumlahBarang FROM barang WHERE kode_barang=’1235’

    5. Mengurutkan data dengan ORDERED BY
    6. SELECT namaKolom FROM namaTabel [WHERE kondisi] ORDERED BY namaKolom [DESC]
      Contoh:
      SELECT * FROM barang ORDERED BY nama_barang
      SELECT * FROM barang ORDERED BY nama_barang DESC

    7. Melakukan perhitungan matematika
      1. Operator matematika
      2. SELECT [namaKolom], (namaKolom/angka namaKolom/angka) AS kolomAlias FROM namaTabel [WHERE kondisi]
        Contoh:
        SELECT nama_barang,(jumlah_barang + 10) AS tambah FROM barang
        SELECT (jumlah_barang + 10) AS tambah FROM barang
        SELECT nama_barang,(jumlah_barang + 100) AS tambah,(jumlah_barang - 10) AS pengurangan,(jumlah_barang*harga_barang) AS perkalian, (harga_barang/10) As pembagian FROM barang

      3. Fungsi penjumlahan berkelompok (SUM)
      4. SELECT SUM(namaKolom) AS kolomAlias FROM namaTabel [WHERE kondisi]
        Contoh:
        SELECT SUM (jumlah_barang) AS total_barang, SUM (harga_barang) AS total_harga FROM barang

      5. Fungsi nilai rata-rata
      6. SELECT AVG(namaKolom) AS kolomAlias FROM namaTabel [WHERE kondisi]
        Contoh:
        SELECT AVG(jumalh_barang) AS rata2barang, AVG(harga_barang) AS rata2harga FROM barang

      7. Fungsi nilai maksimal (MAX)
      8. SELECT MAX(namaKolom) AS kolomAlias FROM namaTabel [WHERE kondisi]
        Contoh:
        SELECT MAX(jumlah_barang) AS maks_barang, MAX(harga_barang) AS maks_harga FROM barang

      9. Fungsi nilai minimal (MIN)
      10. SELECT MIN(namaKolom) AS kolomAlias FROM namaTabel [WHERE kondisi]
        Contoh:
        SELECT MIN(jumlah_barang) AS min_barang, MIN(harga_barang) AS min_harga FROM barang

    8. Statement INSERT
    9. INSERT INTO namaTabel (namaKolom1, namaKolom2, … namaKolomN) VALUES (nilaiKolom1, nilaiKolom2, … nilaiKolomN)
      Contoh:
      INSERT INTO barang (kode_barang, jumlah_barang, nama_barang) VALUES (‘1235’, ‘10’, ‘Printer’)
      INSERT INTO table VALUES (‘data_kolom1’, ‘data_kolom2’, ‘data_kolom3’)

    10. Statement UPDATE
    11. UPDATE namaTabel SET namaKolom = nilaiBaru[WHERE kondisi]
      Contoh:
      UPDATE barang SET jumlah_barang = ‘Monitor’ WHERE kode_barang=’1235’

    12. Statement DELETE
    13. DELETE FROM namaTabel[WHERE kondisi]
      Contoh:
      DELETE FROM barang
      DELETE FROM barang WHERE kode_barang = ‘1235’

    14. Statement CREATE
    15. CREATE TABLE namaTabel (namaKolom1 typeData(panjangData)[konstrain], namaKolom2 typeData(panjangData)[konstrain],…, namaKolomN typeData(panjangData)[konstrain])
      Contoh:
      CREATE TABLE barang (jumlah_barang NUMBER(10)PRIMARY KEY, kode_barang VARCHAR(35)

    16. Statement ALTER:
    17. untuk memodifikasi struktur database yang telah ada sebelumnya, seperti menambahkan kolom baru, menghapus kolom, atau menambahkan konstrain pada kolom
      ALTER TABLE namaTable ATRIBUT
      Contoh:
      ALTER TABLE barang ADD harga_barang NUMBER (15)
      ALTER TABLE barang MODIFY harga_barang VARCHAR(15)

    18. Statement DROP:
    19. untuk menghapus database secara keseluruhan
      DROP namaTabel
      Contoh:
      DROP barang

Sumber: Membuat Aplikasi Database dengan JAVA2”.ANDI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: