1 / 21

SQL ALIAS

SQL ALIAS. Alias dipergunakan jika ingin memberikan nama lain pada tabel atau kolom/field Sintaks untuk Alias Tabel SELECT column_name (s) FROM table_name AS alias_name Sintaks untuk Alias Kolom/Field SELECT column_name AS alias_name FROM table_name. Contoh.

nadine
Download Presentation

SQL ALIAS

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 ALIAS • Alias dipergunakan jika ingin memberikan nama lain pada tabel atau kolom/field • Sintaks untuk Alias Tabel SELECT column_name(s)FROM table_nameAS alias_name • Sintaks untuk Alias Kolom/Field SELECT column_name AS alias_nameFROM table_name

  2. Contoh • Mengganti nama tabel persons menjadi p SELECT p.LastName, p.FirstNameFROM Persons AS pWHERE p.LastName='Hansen' AND p.FirstName='Ola‘ • Membuat alias untuk sebuah kolom dihasilkan oleh query SELECT LEN(Address) as LengthOfAddress FROM Persons

  3. FUNCTION BASIS DATA I

  4. FUNCTION • Function atau fungsi dalam perintah SQL digunakan untuk menghasilkansebuahnilaiatauoutput. • Adabeberapafungsi yang digunakanuntukmemanipulasi data yaitu • Fungsi string ataukarakter • Fungsiwaktu • Fungsimatematika • Fungsisystem ataufungsi scalar • FungsiAritmatika • Fungsitambahan yang dapatdigunakanuntukmengubah (konversi) dari data yang satuke data yang lain.

  5. Fungsi String • Fungsistring, yaitufungsi yang digunakanuntukmemanipulasitipe data string ataukarakter. • LOWER DAN UPPER UPPER  mengubah string menjadihurufbesar Sintaks : SELECT UPPER(column_name) FROM table_name

  6. UPPER  mengubah string menjadihurufkecil Sintaks : SELECT LOWER(column_name) FROM table_name

  7. Contoh • SELECT UPPER(LastName) as LastName, FirstName FROM Person

  8. LEN  menghitungjumlahkarakterdalam string. Sintaks : SELECT LEN(column_name) FROM table_name Contoh : SELECT LEN(Address) as LengthOfAddress FROM Persons

  9. MID  digunakanuntukmengekstrakkarakterdarikolomteks. Sintaks : SELECT MID(column_name,start[,length]) FROM table_name • Contoh SELECT MID(City,1,4) as SmallCity FROM Persons

  10. SUBSTRING  Mengambilbeberapakarakterdarisebuahkarakter string. Sintaks : SELECT SUBSTRING(column_name,start,length) FROM table_name • LEFT / RIGHT  Mengambilbeberapakarakterdarikiri/kanan Sintaks : SELECT LEFT/RIGHT(column_name, length) FROM table_name

  11. FungsiMatematika • SUM  berfungsiuntukmenjumlahkannilaikolomtertentu. Sintaks : SELECT SUM(column_name) FROM table_name • Contoh : tabel Orders

  12. Menjumlahkan total OrderPrice SELECT SUM(OrderPrice) AS OrderTotal FROM Orders • Tampilkan total order price milik customer Hansen

  13. FungsiMatematika • COUNT  menghitungjumlahbarisdalamsebuahtabel Sintaks : • SELECT COUNT(column_name) FROM table_name • SELECT COUNT(*) FROM table_name • SELECT COUNT(DISTINCT column_name) FROM table_name

  14. Contoh : • SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer='Nilsen‘ • SELECT COUNT(*) AS NumberOfOrders FROM Orders • SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Order

  15. 1. 2. 3.

  16. FungsiMatematika • AVG  untukmenghitung rata-rata darisuatukolomtertentu Sintaks : SELECT AVG(column_name) FROM table_name • Contoh : Tabel Orders

  17. Tentukan rata-rata OrderPricedaritabel Orders SELECT AVG(OrderPrice) AS OrderAverage FROM Orders • Tampilkannama customer yang order pricenyalebihbesardari order rata-rata.

  18. FungsiMatematika • MIN  untukmengetahuinilaiterkecildarisebuahkolomtertentu Sintaks : SELECT MIN(column_name) FROM table_name • Contoh : menggunakantabel Orders, tampilkanorderpriceterkecil.

  19. SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

  20. FungsiMatematika • MAX untukmengetahuinilaiterbesardarisebuahkolomtertentu Sintaks : SELECT MAX(column_name) FROM table_name • Contoh : menggunakantabel Orders, tampilkanorderpriceterbesar. SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

  21. FungsiWaktu

More Related