1 / 37

SISTEM BASISDATA

SISTEM BASISDATA. Lasmedi Afuan, ST.,M.Cs. JOIN. Join digunakan untuk menampilkan/query data dari 2 tabel atau lebih JOIN JOIN LEFT JOIN RIGHT JOIN FULL JOIN . SQL INNER JOIN Keyword. Menamilkan data dari tabel minimal ada 1 record yang sm

alaula
Download Presentation

SISTEM BASISDATA

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. SISTEM BASISDATA Lasmedi Afuan, ST.,M.Cs

  2. JOIN • Join digunakan untuk menampilkan/query data dari 2 tabel atau lebih • JOIN • JOIN • LEFT JOIN • RIGHT JOIN • FULL JOIN

  3. SQL INNER JOIN Keyword • Menamilkan data dari tabel minimal ada 1 record yang sm • SELECT column_name(s)FROM table_name1INNER JOIN table_name2ON table_name1.column_name=table_name2.column_name

  4. INNER JOIN SELECT country_name,region_nameFROM regionsINNER JOIN countriesON regions.region_id=countries.region_id

  5. SQL LEFT JOIN Keyword • The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2) • SELECT column_name(s)FROM table_name1LEFT JOIN table_name2ON table_name1.column_name=table_name2.column_name

  6. LEFT JOIN • SELECT country_name,region_nameFROM regionsLEFT JOIN countriesON regions.region_id=countries.region_id

  7. SQL RIGHT JOIN Keyword • The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1). • SELECT column_name(s)FROM table_name1RIGHT JOIN table_name2ON table_name1.column_name=table_name2.column_name

  8. SQL RIGHT JOIN Keyword SELECT country_name,region_nameFROM regionsRIGHT JOIN countriesON regions.region_id=countries.region_id

  9. SQL FULL JOIN Keyword • The FULL JOIN keyword return rows when there is a match in one of the tables. • SELECT column_name(s)FROM table_name1FULL JOIN table_name2ON table_name1.column_name=table_name2.column_name

  10. FULL JOIN • SELECT country_name,region_nameFROM regionsFULL JOIN countriesON regions.region_id=countries.region_id

  11. SQL UNION Operator • The UNION operator is used to combine the result-set of two or more SELECT statements. • SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2

  12. The SQL SELECT INTO Statement • SELECT *INTO new_table_name [IN externaldatabase]FROM old_tablename

  13. SQL CREATE DATABASE Statement • CREATE DATABASE database_name

  14. The CREATE TABLE Statement CREATE TABLE table_name(column_name1 data_type,column_name2 data_type,column_name3 data_type,....)

  15. Create Table CREATE TABLE Persons(P_Idint,LastNamevarchar(255),FirstNamevarchar(255),Address varchar(255),City varchar(255))

  16. SQL Constraints • Constraints are used to limit the type of data that can go into a table. Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement). • We will focus on the following constraints: • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK • DEFAULT

  17. SQL NOT NULL Constraint • CREATE TABLE Persons(P_Idint NOT NULL,LastNamevarchar(255) NOT NULL,FirstNamevarchar(255),Address varchar(255),City varchar(255))

  18. SQL UNIQUE Constraint • The UNIQUE constraint uniquely identifies each record in a database table. • The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. CREATE TABLE Persons(P_Idint NOT NULL,LastNamevarchar(255) NOT NULL,FirstNamevarchar(255),Address varchar(255),City varchar(255),UNIQUE (P_Id))

  19. SQL Server / Oracle / MS Access CREATE TABLE Persons(P_Idint NOT NULL UNIQUE,LastNamevarchar(255) NOT NULL,FirstNamevarchar(255),Address varchar(255),City varchar(255))

  20. SQL Server / Oracle / MS Access CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName))

  21. SQL UNIQUE Constraint on ALTER TABLE MySQL / SQL Server / Oracle / MS Access: • ALTER TABLE PersonsADD UNIQUE (P_Id)

  22. To DROP a UNIQUE Constraint • ALTER TABLE PersonsDROP INDEX uc_PersonID • ALTER TABLE PersonsDROP CONSTRAINT uc_PersonID

  23. SQL PRIMARY KEY Constraint • The PRIMARY KEY constraint uniquely identifies each record in a database table. • Primary keys must contain unique values. • A primary key column cannot contain NULL values. • Each table should have a primary key, and each table can have only ONE primary key.

  24. SQL PRIMARY KEY Constraint on CREATE TABLE MySQL: CREATE TABLE Persons(P_Idint NOT NULL,LastNamevarchar(255) NOT NULL,FirstNamevarchar(255),Address varchar(255),City varchar(255),PRIMARY KEY (P_Id))

  25. SQL Server / Oracle / MS Access: CREATE TABLE Persons(P_Idint NOT NULL PRIMARY KEY,LastNamevarchar(255) NOT NULL,FirstNamevarchar(255),Address varchar(255),City varchar(255))

  26. MySQL / SQL Server / Oracle / MS Access: MySQL / SQL Server / Oracle / MS Access: • CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName))

  27. MySQL / SQL Server / Oracle / MS Access: • ALTER TABLE PersonsADD PRIMARY KEY (P_Id) • ALTER TABLE PersonsADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

  28. SQL FOREIGN KEY Constraint • A FOREIGN KEY in one table points to a PRIMARY KEY in another table CREATE TABLE Orders(O_Idint NOT NULL,OrderNoint NOT NULL,P_Idint,PRIMARY KEY (O_Id),FOREIGN KEY (P_Id) REFERENCES Persons(P_Id))

  29. MySQL / SQL Server / Oracle / MS Access: • To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: • CREATE TABLE Orders(O_Idint NOT NULL,OrderNoint NOT NULL,P_Idint,PRIMARY KEY (O_Id),CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)REFERENCES Persons(P_Id))

  30. SQL CHECK Constraint • The CHECK constraint is used to limit the value range that can be placed in a column. • If you define a CHECK constraint on a single column it allows only certain values for this column. • If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

  31. SQL CHECK Constraint on CREATE TABLE • The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0. • CREATE TABLE Persons(P_Idint NOT NULL CHECK (P_Id>0),LastNamevarchar(255) NOT NULL,FirstNamevarchar(255),Address varchar(255),City varchar(255))

  32. SQL DEFAULT Constraint • The DEFAULT constraint is used to insert a default value into a column. • The default value will be added to all new records, if no other value is specified. CREATE TABLE Persons(P_Idint NOT NULL,LastNamevarchar(255) NOT NULL,FirstNamevarchar(255),Address varchar(255),City varchar(255) DEFAULT 'Sandnes')

  33. SQL DEFAULT Constraint on ALTER TABLE • MySql ALTER TABLE PersonsALTER City SET DEFAULT 'SANDNES‘ • Oracle ALTER TABLE PersonsMODIFY City DEFAULT 'SANDNES‘ • SQL Server ALTER TABLE PersonsALTER COLUMN City DROP DEFAULT

  34. Kun ‘aliman, au muta’alliman, au mustami’an, au muhibban. Walam takun khomisan, fatahlik Jadilah Engkau Orang Berilmu, atau orang yang menuntut ilmu,atau orang yang mau mendengarkan ilmu, atau orang yang menyukai ilmu. Dan janganlah kamu menjadi orang yang kelima maka kamu akan celaka (HR.Baihaqi)

  35. Sekian.....

More Related