SQL ( Structured Query Language)

1. SQL (Structured Query Language) DML

2. Bahasa untuk mengakses basis data • Bahasa untuk mengolah basis data • Bahasa untuk memanggil fungsi-fungsi agregasi • Bahasa untuk melakukan query • Jenis-jenis query: • Sederhana • Join • Bertingkat

3. ContohPenggunaanSkema

4. SQL is based on set and relational operations with certain modifications and enhancements • A typical SQL query has the form: select A1, A2, ..., An from r1, r2, ..., rm where P • Ais represent attributes • ris represent relations • P is a predicate. • This query is equivalent to the relational algebra expression. ÕA1, A2, ..., An(sP (r1 x r2 x ... x rm)) • The result of an SQL query is a relation.

5. Query Sederhana • Bentuk umum SQL: SELECT [ALL|DISTINCT] nama_kolom_kolom_tabel [INTO nama_tabel] [FROM nama_nama_tabel] [WHERE predikat] [GROUP BY ekspresi] [ORDER BY nama+kolom_tabel]

6. The select clause corresponds to the projection operation of the relational algebra. It is used to list the attributes desired in the result of a query. • Find the names of all branches in the loan relation select branch-name from loan • In the “pure” relational algebra syntax, the query would be: Õbranch-name(loan) • An asterisk in the select clause denotes “all attributes” select * from loan • NOTE: SQL does not permit the ‘-’ character in names, so you would use, for example, branch_name instead of branch-name in a real implementation. We use ‘-’ since it looks nicer! • NOTE: SQL names are case insensitive, meaning you can use upper case or lower case. You may wish to use upper case in places where we use bold font

7. SQL allows duplicates in relations as well as in query results. • To force the elimination of duplicates (menghilangkanduplikasipenampilan • output yang sama) insert the keyword distinct after select. Find the names of all branches in the loan relations, and remove duplicates • select distinct branch-name • from loan • The keyword all specifies that duplicates not be removed. • select all branch-name • from loan • Menampilkan isi tabel customer • select * from customer • Menampilkan semua fname dari tabel customer • select customer_name from customer

8. Menampilkan account number dan balance dari kantor cabang (branch_name) “Pondok Kelapa” select account_number, balance from account where branch_name = “Pondok Kelapa”; • Perintah diatas dapat juga dituliskan dengan menggunakan qualifiedcolumn names sebagai berikut: select account.account_number, account.balance from account where branch_name = “Pondok Kelapa”;

9. The select clause can contain arithmetic expressions involving the operation, +, –, *, and /, and operating on constants or attributes of tuples. • The query: select loan-number, branch-name, amount * 100 from loan would return a relation which is the same as the loan • would return a relation which is the same as the loan relations, except • that the attribute amount is multiplied by 100.