1 / 28

SQL (Structured Query Language)

SQL (Structured Query Language). SQL. Concepts of Query Basic Structure  Set Operations  Aggregate Functions  Null Values  Nested Subqueries  Derived Relations  Views  Modification of the Database  Joined Relations. Tujuan.

aricin
Download Presentation

SQL (Structured Query Language)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL(StructuredQueryLanguage)

  2. SQL • Concepts of Query • Basic Structure •  Set Operations •  Aggregate Functions •  Null Values •  Nested Subqueries •  Derived Relations •  Views •  Modification of the Database •  Joined Relations

  3. Tujuan • Menjelaskan bahasa formal yang digunakan pada basis data relasional • Menjelaskan SQL sebagai bahasa yang standar • Memberikan beberapa contoh teknik pembuatan query dengan menggunakan SQL

  4. Pengertian • Query adalah perintah-perintah untuk mengakses data pada sistem basis data • SQL adalah bahasa query baku untuk DBMS •  SQL diambil sebagai bakuan sejak tahun 1992 •  Awalnya diterapkan pada DBMS besar seperti Oracle dan Informix, sekarang juga pada DBMS berbasis PC seperti dBASE dan FoxPro. •  SQL bersifat sebagai bahasa tingkat tinggi (high level). Pemakai hanya menyebutkan hasil yang diinginkan dan optimasi pelaksanaan query dilakukan oleh DBMS.

  5. Pengertian (1) • SQL dapat disisipkan ke bahasa pemrograman yang lain seperti C, Pascal, Cobol, dll. • Bahasa SQL terbagi dalam dua bagian besar, yaitu: DDL (Data Definition Language) dan DML (Data Manipulation Language) •  DDL mendefinisikan struktur basis data, seperti pembuatan basis data, pembuatan tabel dsbnya. Contoh: CREATE DATABASE dan CREATE TABLE. •  DML merupakan bagian untuk memanipulasi basis data seperti: pengaksesan data, penghapusan, penambahan dan pengubahan data. DML juga dapat digunakan untuk melakukan komputasi data. Contoh: INSERT, DELETE, dan UPDATE.

  6. DDL • Perintah SQL untuk definisi data: • CREATE untuk membentuk basis data, taable atau index • ALTER untuk mengubah struktur table • DROP untuk menghapus basis data, table atau index •  CREATE DATABASE • Untuk membentuk basis data • Sintaks: CREATE DATABASE nama_database • Contoh: CREATE DATABASE COMPANY •  CREATE TABLE • Untuk membentuk table dari basis data • Untuk menyebutkan spesifikasi dan batasan atribut

  7. DDL (1) Contoh CREATE TABLE:  CREATE TABLE EMPLOYEE  ( PNAME CHAR(15) NOT NULL  LNAME CHAR(15) NOT NULL  SSN CHAR(9) NOT NULL  BDATE DATE  ADDRESS CHAR(30)  SEX CHAR  SALARY DECIMAL(10.2)  DNO CHAR(10) );

  8. DDL (2) • ALTER TABLE • Digunakan untuk mengubah struktur table • Contoh kasus: misalkan ingin menambahkan kolom JOB pada table EMPLOYEE dengan tipe karakter selebar 12. • Perintah: ALTER TABLE EMPLOYEE ADD JOB CHAR(12);

  9. DDL (3) • CREATE INDEX • Membentuk berkas index dari table • Index digunakan untuk mempercepat proses pencarian • Sintaks: CREATE [UNIQUE] INDEX nama_index ON nama_table(kolom1, kolom2, …. ) • Contoh: CREATE INDEX EMPLOYEENDX ON EMPLOYEE(SSN)

  10. DDL (4) • Menghapus Basis Data • DROP DATABASE • Sintaks: DROP DATABASE nama_database • Contoh: DROP DATABASE COMPANY • Menghapus Table • DROP TABLE • Sintaks: DROP TABLE nama_table • Contoh: DROP TABLE EMPLOYEE •  Menghapus Berkas Index • DROP INDEX • Sintaks: DROP INDEX nama_index • Contoh: DROP INDEX EMPLOYEENDX

  11. DML • 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

  12. Contoh Skema

  13. Basic Structure • 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 • A is represent attributes • r is represent relations • P is a predicate. • This query is equivalent to the relational algebra expression. A1, A2, ..., An(P (r1 x r2 x ... x rm)) •  The result of an SQL query is a relation.

  14. 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] 

  15. Select Clause • 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

  16. Select Clause (1) • SQL allows duplicates in relations as well as in query results. • To force the elimination of duplicates (menghilangkan duplikasi penampilan 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

  17. Select Clause (2) • 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 qualified column names sebagai berikut: • select account.account_number, account.balance from account where branch_name = “Pondok Kelapa”;

  18. Select Clause (3) • 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 relations, except that the attribute amount is multiplied by 100.

  19. Where Clause • The where clause corresponds to the selection predicate of the relational algebra. If consists of a predicate involving attributes of the relations that appear in the from clause. • The find all loan number for loans made a the Perryridge branch with loan amounts greater than $1200. • select loan-number from loan where branch-name = ‘Perryridge’ and amount > 1200 • Comparison results can be combined using the logical connectives and, or, and not. • Comparisons can be applied to results of arithmetic expressions.

  20. Where Clause (1) • SQL Includes a between comparison operator in order to simplify where clauses that specify that a value be less than or equal to some value and greater than or equal to some other value. • Find the loan number of those loans with loan amounts between $90,000 and $100,000 (that is, $90,000 and $100,000) • select loan-number from loan where amount between 90000 and 100000

  21. Rename Operation • The SQL allows renaming relations and attributes using the as clause: • old-name as new-name • Find the name, loan number and loan amount of all customers; rename the column name loan-number as loan-id. • select customer-name, borrower.loan-number as loan-id, amount from borrower, loan where borrower.loan-number = loan.loan-number

  22. String Operation • SQL includes a string-matching operator for comparisons on character strings. • Find the names of all customers whose street includes the substring “Main”. • select customer-name • from customer • where customer-street like ‘%Main%’ • SQL supports a variety of string operations such as • concatenation (using “||”) • converting from upper to lower case (and vice versa) • finding string length, extracting substrings, etc.

  23. Ordering • List in alphabetic order the names of all customers having a loan in Perryridge branch • select distinct customer-name from borrower, loan where borrower.loan-number = loan.loan-number and branch-name = ‘Perryridge’ order by customer-name • We may specify desc for descending order or asc for ascending order, for each attribute; ascending order is the default. • E.g. order by customer-name desc

  24. Aggregate Function • These functions operate on the multiset of values of a column of a relation, and return a value • avg: average value • min: minimum value • max: maximum value • sum: sum of values • count: number of values

  25. Aggregate Function (1) • Find the average account balance at the Perryridge branch. • select avg (balance) from account where branch-name = ‘Perryridge’ • Find the number of tuples in the customer relation. • select count (*) from customer • Find the number of depositors in the bank. • select count (distinct customer-name) from depositor

  26. Aggregate Function - Group By • Find the number of depositors for each branch. • select branch-name, count (distinct customer-name) from depositor, account where depositor.account-number = account.account-number group by branch-name •  Note: Attributes in select clause outside of aggregate functions must appear in group by list

  27. Aggregate Function – having clause • Find the names of all branches where the average account balance is more than $1,200. • select branch-name, avg (balance) from account group by branch-name having avg (balance) > 1200 • Note: predicates in the having clause are applied after the formation of groups whereas predicates in the where clause are applied before forming groups

  28. SELAMAT BELAJAR ...

More Related