Syntax DML

Syntax DML

SELECT column_name(s) FROM table_name
SELECT * FROM table_name
-----------------------------

SQL SELECT DISTINCT Syntax :

SELECT DISTINCT column_name(s) FROM table_name
SELECT DISTINCT City FROM Persons
-----------------------------

SQL WHERE Syntax :
SELECT column_name(s) FROM table_name WHERE column_name operator value
Contoh :
SELECT * FROM Persons WHERE City='Sandnes'
----------------------------------------

The AND & OR Operators :

SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson'
SELECT * FROM Persons WHERE FirstName='Tove' OR FirstName='Ola'
--------------------------------

INSERT INTO Syntax :

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1,

value2, value3,...)
Contoh :
INSERT INTO Persons VALUES (4,'Nilsen', 'Johan', 'Bakken 2',

'Stavanger')
-----------------

Order by syntax :

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC
Contoh :
SELECT * FROM Persons ORDER BY LastName
--------------------------------

SQL UPDATE Syntax :

UPDATE table_name SET column1=value, column2=value2,... WHERE

some_column=some_value
Contoh :
UPDATE Persons SET Address='Nissestien 67', City='Sandnes' WHERE

LastName='Tjessem' AND FirstName='Jakob'
----------------

SQL DELETE Syntax :

DELETE FROM table_name WHERE some_column=some_value
Contoh :
DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob'
--------------

SQL Server Syntax :

SELECT TOP number|percent column_name(s) FROM table_name
SELECT column_name(s) FROM table_name LIMIT number
SELECT * FROM Persons LIMIT 5
-------------

SQL LIKE Syntax :

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
Contoh :
SELECT * FROM Person WHERE City LIKE 's%'
SELECT * FROM Persons WHERE City LIKE '%s'
SELECT * FROM Persons WHERE City LIKE '%tav%'
SELECT * FROM Persons WHERE City NOT LIKE '%tav%'
-----------------------

SQL IN Syntax :

SELECT column_name(s) FROM table_name WHERE column_name IN

(value1,value2,...)
----------------

SQL BETWEEN Syntax :

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1

AND value2
---------------

SQL Alias Syntax for Tables :

SELECT column_name(s) FROM table_name AS alias_name

SQL Alias Syntax for Columns :

SELECT column_name AS alias_name FROM table_name
Contoh :
SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p,
Product_Orders AS po WHERE p.LastName='Hansen' AND p.FirstName='Ola'
------------

SQL INNER JOIN Syntax :

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON

table_name1.column_name=table_name2.column_name
--------------

SQL LEFT JOIN Syntax :

SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON

table_name1.column_name=table_name2.column_name
-------------

SQL RIGHT JOIN Syntax :

SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON

table_name1.column_name=table_name2.column_name
-----------------

SQL FULL JOIN Syntax :

SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON

table_name1.column_name=table_name2.column_name
----------------

SQL UNION Syntax :

SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM

table_name2

SQL UNION ALL Syntax :

SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s)

FROM table_name2
-----------------

SQL SELECT INTO Syntax :

SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename
SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM

old_tablename
-----------------

SQL CREATE DATABASE Syntax :

CREATE DATABASE database_name
----------------

SQL CREATE TABLE Syntax :
Contoh :
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

contoh :
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Bandung',
UNIQUE (P_Id)
)
----------------

SQL CREATE INDEX Syntax :

CREATE INDEX index_name ON table_name (column_name) "Value duplikasi

diperbolehkan"
CREATE UNIQUE INDEX index_name ON table_name (column_name) "Duplikasi

tidak diperbolehkan"
----------------

DROP INDEX Syntax for MS Access:
DROP INDEX index_name ON table_name

DROP INDEX Syntax for MS SQL Server:
DROP INDEX table_name.index_name

DROP INDEX Syntax for MySQL:
ALTER TABLE table_name DROP INDEX index_name

DROP TABLE table_name
DROP DATABASE database_name
-----------------

SQL ALTER TABLE Syntax :

tambah kolom dalam tabel:
ALTER TABLE table_name ADD column_name datatype

Hapus Kolom dalam tabel :
ALTER TABLE table_name DROP COLUMN column_name
-----------------

SQL CREATE VIEW Syntax :

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE

condition

Contoh :
CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName FROM

Products WHERE Discontinued=No