Sistem basisdata
Download
1 / 37

SISTEM BASISDATA - PowerPoint PPT Presentation


  • 229 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SISTEM BASISDATA' - alaula


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Sistem basisdata

SISTEM BASISDATA

Lasmedi Afuan, ST.,M.Cs


Sistem basisdata
JOIN

  • Join digunakan untuk menampilkan/query data dari 2 tabel atau lebih

  • JOIN

    • JOIN

    • LEFT JOIN

    • RIGHT JOIN

    • FULL JOIN


Sql inner join keyword
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


Inner join
INNER JOIN

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


Sql left join keyword
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


Left join
LEFT JOIN

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


Sql right join keyword
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


Sql right join keyword1
SQL RIGHT JOIN Keyword

SELECT country_name,region_nameFROM regionsRIGHT JOIN countriesON regions.region_id=countries.region_id


Sql full join keyword
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


Full join
FULL JOIN

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


Sql union operator
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


The sql select into statement
The SQL SELECT INTO Statement

  • SELECT *INTO new_table_name [IN externaldatabase]FROM old_tablename


Sql create database statement
SQL CREATE DATABASE Statement

  • CREATE DATABASE database_name


The create table statement
The CREATE TABLE Statement

CREATE TABLE table_name(column_name1 data_type,column_name2 data_type,column_name3 data_type,....)


Create table
Create Table

CREATE TABLE Persons(P_Idint,LastNamevarchar(255),FirstNamevarchar(255),Address varchar(255),City varchar(255))


Sql constraints
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


Sql not null constraint
SQL NOT NULL Constraint

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


Sql unique constraint
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))


Sql server oracle ms access
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))


Sql server oracle ms access1
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))


Sql unique constraint on alter table
SQL UNIQUE Constraint on ALTER TABLE

MySQL / SQL Server / Oracle / MS Access:

  • ALTER TABLE PersonsADD UNIQUE (P_Id)


To drop a unique constraint
To DROP a UNIQUE Constraint

  • ALTER TABLE PersonsDROP INDEX uc_PersonID

  • ALTER TABLE PersonsDROP CONSTRAINT uc_PersonID


Sql primary key constraint
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.


Sql primary key constraint on create table
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))


Sql server oracle ms access2
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))


Mysql sql server oracle ms access
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))


Mysql sql server oracle ms access1
MySQL / SQL Server / Oracle / MS Access:

  • ALTER TABLE PersonsADD PRIMARY KEY (P_Id)

  • ALTER TABLE PersonsADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)


Sql foreign key constraint
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))


Mysql sql server oracle ms access2
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))


Sql check constraint
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.


Sql check constraint on create table
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))


Sql default constraint
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')


Sql default constraint on alter table
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


Sistem basisdata

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)


Sistem basisdata

Sekian..... muhibban. Walam takun khomisan, fatahlik