Monday, December 25, 2017

Kumpulan beberapa syntac dasar SQL

SELECT Syntax

Example

SELECT column_name,column_name
FROM table_name;
and
SELECT * FROM table_name;

SELECT Column Example

Example

SELECT CustomerName,City FROM Customers;

SELECT * Example

Example

SELECT * FROM Customers;

SELECT DISTINCT Syntax

Example

SELECT DISTINCT column_name,column_name
FROM table_name;

SELECT DISTINCT Example

Example

SELECT DISTINCT City FROM Customers;

SQL WHERE Syntax

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

WHERE Clause Example

Example

SELECT * FROM Customers
WHERE Country='Mexico';

Text Fields vs. Numeric Fields

Example

SELECT * FROM Customers
WHERE CustomerID=1;

SELECT DISTINCT Example

Example

SELECT DISTINCT City FROM Customers;

AND Operator Exampl

Example

SELECT * FROM Customers
WHERE Country='Germany'
AND City='Berlin';

OR Operator Example

Example

SELECT * FROM Customers
WHERE City='Berlin'
OR City='München';

Combining AND & OR

Example

SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');

ORDER BY Example

Example

SELECT * FROM Customers
ORDER BY Country;

ORDER BY DESC Example

Example

SELECT * FROM Customers
ORDER BY Country DESC;

ORDER BY Several Columns Example

Example

SELECT * FROM Customers
ORDER BY Country, CustomerName;

ORDER BY Several Columns Example 2

Example

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

INSERT INTO Example

Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

Insert Data Only in Specified Columns

Example

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

SQL UPDATE Statement

Example

Change the value of the "City" column of a record in the "Customers" table:
UPDATE Customers
SET City='Hamburg'
WHERE CustomerID=1;

UPDATE Multiple Columns

Example

UPDATE Customers
SET ContactName='Alfred Schmidt', City='Frankfurt'
WHERE CustomerID=1;

UPDATE Multiple Records

Example

UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';

Update Warning!

Example

UPDATE Customers
SET ContactName='Juan';

SQL DELETE Example

Example

DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';

Delete All Data

Example

DELETE FROM table_name;
or
DELETE * FROM table_name;

SQL Injection Based on 1=1 is Always True

Server Result

Example

SELECT * FROM Users WHERE UserId = 105 or 1=1;

Example

The SQL statement above is much the same as this:
SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1;

SQL Injection Based on ""="" is Always True

Here is a common construction, used to verify user login to a web site:
User Name:John Doe
Password:myPass

server Code

uName = getRequestString("UserName");
uPass = getRequestString("UserPass");
Example
'SELECT * FROM Users WHERE Name ="' + uName + '" AND Pass ="' + uPass + '"'

Result

SELECT * FROM Users WHERE Name ="John Doe" AND Pass ="myPass"
User Name: " or ""="
Password: " or ""="
The code at the server will create a valid SQL statement like this:

Result

SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""="";

SQL Injection Based on Batched SQL Statements

Most databases support batched SQL statement, separated by semicolon.

Example

SELECT * FROM Users; DROP TABLE Suppliers
And the following input:
User id:
105; DROP TABLE Suppliers
The code at the server would create a valid SQL statement like this:

Result

SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers;

Parameters for Protection

ASP.NET Razor Example

txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = @0";
db.Execute(txtSQL,txtUserId);
Note that parameters are represented in the SQL statement by a @ marker.
The SQL engine checks each parameter to ensure that it is correct for its column and are treated literally, and not as part of the SQL to be executed.

Another Example

txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
db.Execute(txtSQL,txtNam,txtAdd,txtCit);

Examples

The following examples shows how to build parameterized queries in some common web languages.
SELECT STATEMENT IN ASP.NET:
txtUserId = getRequestString("UserId");
sql = "SELECT * FROM Customers WHERE CustomerId = @0";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@0",txtUserID);
command.ExecuteReader();
or
INSERT INTO STATEMENT IN ASP.NET:
txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
command = new SqlCommand(txtSQL);
command.Parameters.AddWithValue("@0",txtNam);
command.Parameters.AddWithValue("@1",txtAdd);
command.Parameters.AddWithValue("@2",txtCit);
command.ExecuteNonQuery();
or
INSERT INTO STATEMENT IN PHP:
$stmt = $dbh->prepare("INSERT INTO Customers (CustomerName,Address,City)
VALUES (:nam, :add, :cit)");
$stmt->bindParam(':nam', $txtNam);
$stmt->bindParam(':add', $txtAdd);
$stmt->bindParam(':cit', $txtCit);
$stmt->execute();

The SQL SELECT TOP Clause

SQL Server / MS Access Syntax

Example

SELECT TOP number|percent column_name(s)
FROM table_name;

SQL SELECT TOP Equivalent in MySQL and Oracle

MySQL Syntax

Example

SELECT column_name(s)
FROM table_name
LIMIT number;

Example

SELECT *
FROM Persons
LIMIT 5;

Oracle Syntax

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

Example

SELECT *
FROM Persons
WHERE ROWNUM <=5;

SQL SELECT TOP Example

Example

SELECT TOP 2 * FROM Customers;

SQL SELECT TOP PERCENT Example

Example

SELECT TOP 50 PERCENT * FROM Customers;

SQL LIKE Syntax

Example

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

SQL LIKE Operator Examples

The following SQL statement selects all customers with a City starting with the letter "s":

Example

SELECT * FROM Customers
WHERE City LIKE 's%';

Example

SELECT * FROM Customers
WHERE City LIKE '%s';

Example

SELECT * FROM Customers
WHERE Country LIKE '%land%';

Example

SELECT * FROM Customers
WHERE Country NOT LIKE '%land%';

Using the SQL % Wildcard

Example

SELECT * FROM Customers
WHERE City LIKE 'ber%';

Example

SELECT * FROM Customers
WHERE City LIKE '%es%';

Using the SQL _ Wildcard

Example

SELECT * FROM Customers
WHERE City LIKE '_erlin';

Example

SELECT * FROM Customers
WHERE City LIKE 'L_n_on';

Using the SQL [charlist] Wildcard

The following SQL statement selects all customers with a City starting with "b", "s", or "p":

Example

SELECT * FROM Customers
WHERE City LIKE '[bsp]%';

Example

SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

Example

SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';

Example

SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';

IN Operator Example

Example

SELECT * FROM Customers
WHERE City IN ('Paris','London');

BETWEEN Operator Example

The following SQL statement selects all products with a price BETWEEN 10 and 20:

Example

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

NOT BETWEEN Operator Example

To display the products outside the range of the previous example, use NOT BETWEEN:

Example

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

BETWEEN Operator with IN Example

Example

SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);

BETWEEN Operator with Text Value Example

Example

SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';

NOT BETWEEN Operator with Text Value Example

Example

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'C' AND 'M';

BETWEEN Operator with Date Value Example

Example

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

Alias Example for Table Columns

Example

SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

Example

SELECT CustomerName, Address+', '+City+', '+PostalCode+', '+Country AS Address
FROM Customers;

Alias Example for Tables

Example

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;

Example

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName="Around the Horn" AND Customers.CustomerID=Orders.CustomerID;

SQL JOIN

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

SQL INNER JOIN Example

The following SQL statement will return all customers with orders:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

SQL LEFT JOIN Example

The following SQL statement will return all customers, and any orders they might have:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

SQL RIGHT JOIN Keyword

SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

SQL RIGHT JOIN Example

The following SQL statement will return all employees, and any orders they have placed:

Example

SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Orders.OrderID;

SQL FULL OUTER JOIN Keyword

SQL FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

SQL FULL OUTER JOIN Example

The following SQL statement selects all customers, and all orders:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

The SQL UNION Operator

SQL UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

SQL UNION ALL Syntax

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

SQL UNION Example

Example

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

SQL UNION ALL Example

Example

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

SQL UNION ALL With WHERE

Example

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

SQL SELECT INTO Syntax

Example

SELECT *
INTO newtable [IN externaldb]
FROM table1;

Example

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

SQL SELECT INTO Examples

Example

SELECT *
INTO CustomersBackup2013
FROM Customers;

Example

SELECT *
INTO CustomersBackup2013 IN 'Backup.mdb'
FROM Customers;

Example

SELECT CustomerName, ContactName
INTO CustomersBackup2013
FROM Customers;

Example

SELECT *
INTO CustomersBackup2013
FROM Customers
WHERE Country='Germany';

Example

SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2013
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID;

Example

SELECT *
INTO newtable
FROM table1
WHERE 1=0;

SQL INSERT INTO SELECT Syntax

Example

INSERT INTO table2
SELECT * FROM table1;

Example

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

SQL INSERT INTO SELECT Examples

Example

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;

Example

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';

SQL CREATE DATABASE Syntax

CREATE DATABASE dbname;

SQL CREATE DATABASE Example

The following SQL statement creates a database called "my_db":
CREATE DATABASE my_db;

SQL CREATE TABLE Syntax

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

SQL CREATE TABLE Example

Example

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

SQL CREATE TABLE + CONSTRAINT Syntax

Example

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

SQL NOT NULL Constraint

Example

CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

SQL UNIQUE Constraint on CREATE TABLE

SQL Server / Oracle / MS Access:

Example

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
MySQL:Example
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
);
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 uc_PersonID UNIQUE (P_Id,LastName)
);

SQL UNIQUE Constraint on ALTER TABLE
MySQL / SQL Server / Oracle / MS Access:

Example

ALTER TABLE Persons
ADD UNIQUE (P_Id);

MySQL / SQL Server / Oracle / MS Access:

Example

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);

To DROP a UNIQUE Constraint

MySQL:
ALTER TABLE Persons
DROP INDEX uc_PersonID
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID;

SQL PRIMARY KEY Constraint on CREATE TABLE
MySQL:Example
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
);
SQL Server / Oracle / MS Access:Example
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
MySQL / SQL Server / Oracle / MS Access:Example
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)
);

SQL PRIMARY KEY Constraint on ALTER TABLE
MySQL / SQL Server / Oracle / MS Access:Example
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
MySQL / SQL Server / Oracle / MS Access:Example
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName);

To DROP a PRIMARY KEY Constraint

MySQL:Example
ALTER TABLE Persons
DROP PRIMARY KEY
SQL Server / Oracle / MS Access:Example
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

SQL FOREIGN KEY Constraint on CREATE TABLE

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

SQL FOREIGN KEY Constraint on ALTER TABLE

To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:Example
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:Example
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

To DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:Example
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access:Example
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders;

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.
MySQL:Example
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)
SQL Server / Oracle / MS Access:Example
CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:Example
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

SQL CHECK Constraint on ALTER TABLE

To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:Example
ALTER TABLE Persons
ADD CHECK (P_Id>0)
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:Example
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

To DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:Example
ALTER TABLE Persons
DROP CONSTRAINT chk_Person
MySQL:
ALTER TABLE Persons
DROP CHECK chk_Person;

SQL DEFAULT Constraint on CREATE TABLE

The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:
My SQL / SQL Server / Oracle / MS Access:Example
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
);

SQL DEFAULT Constraint on ALTER TABLE

To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:
MySQL:Example
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
SQL Server / MS Access:Example
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'
Oracle:Example
ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'

To DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:
MySQL:Example
ALTER TABLE Persons
ALTER City DROP DEFAULT;
SQL Server / Oracle / MS Access:Example
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column_name);

SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.

CREATE INDEX Example

The SQL statement below creates an index named "PIndex" on the "LastName" column in the "Persons" table:
CREATE INDEX PIndex
ON Persons (LastName)
If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:
CREATE INDEX PIndex
ON Persons (LastName, FirstName);

DROP INDEX Syntax for MS Access:Example

DROP INDEX index_name ON table_name;

DROP INDEX Syntax for MS SQL Server:Example

DROP INDEX table_name.index_name;

DROP INDEX Syntax for DB2/Oracle:Example

DROP INDEX index_name;

DROP INDEX Syntax for MySQL:

ALTER TABLE table_name DROP INDEX index_name

The DROP TABLE Statement

Example

DROP TABLE table_name

The DROP DATABASE Statement

Example

DROP DATABASE database_name

The TRUNCATE TABLE Statement

Example

TRUNCATE TABLE table_name;

SQL ALTER TABLE Syntax

Example

ALTER TABLE table_name
ADD column_name datatype
To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):
ALTER TABLE table_name
DROP COLUMN column_name
To change the data type of a column in a table, use the following syntax:
SQL Server / MS Access:

Example

ALTER TABLE table_name
ALTER COLUMN column_name datatype;
My SQL / Oracle (prior version 10G):

Example

ALTER TABLE table_name
MODIFY COLUMN column_name datatype
Oracle 10G and later:

Example

ALTER TABLE table_name
MODIFY column_name datatype

Change Data Type Example

Example

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year

Example

DROP COLUMN Example

Example

ALTER TABLE Persons
DROP COLUMN DateOfBirth;

Syntax for MySQL

Example

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

Example

ALTER TABLE Persons AUTO_INCREMENT=100
To insert a new record into the "Persons" table, we will NOT have to specify a value for the "ID" column (a unique value will be added automatically):
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')
The SQL statement above would insert a new record into the "Persons" table. The "ID" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

Syntax for SQL Server

Example

CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Syntax for Access

Example

CREATE TABLE Persons
(
ID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Syntax for Oracle

Example

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

SQL CREATE VIEW Syntax

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

SQL CREATE VIEW Examples

Example

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

Example

SELECT * FROM [Current Product List]

Example

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

Example

SELECT * FROM [Products Above Average Price]

Example

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

Example

SELECT * FROM [Category Sales For 1997]

Example

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'

SQL CREATE OR REPLACE VIEW Syntax

Example

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

Example

CREATE OR REPLACE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

SQL DROP VIEW Syntax

DROP VIEW view_name;

SQL Working with Dates

SELECT * FROM Orders WHERE OrderDate='2008-11-11';

Example

SELECT * FROM Orders WHERE OrderDate='2008-11-11';

SQL IS NULL

Example

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL;

SQL IS NOT NULL

Example

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL;

SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions

Example

SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products;
MS Access
SELECT ProductName,UnitPrice*(UnitsInStock+IIF(ISNULL(UnitsOnOrder),0,UnitsOnOrder))
FROM Products;
SQL Server
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products;
Oracle

Example

SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products;
MySQL

Example

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
or we can use the COALESCE() function, like this:
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products

Single Line Comments

Example

A single-line comment as an explanation:
--Select all:
SELECT * FROM Customers;

Example

A single-line comment to ignore the end of a line:
SELECT * FROM Customers -- WHERE City='Berlin';

Example

A single-line comment to ignore a statement:
--SELECT * FROM Customers;
SELECT * FROM Products;

Multi-line Comments

Example

A multi-line comment as an explanation:
/*Select all the columns
of all the records
in the Customers table:*/
SELECT * FROM Customers;

Example

A multi-line comment to ignore many statements:
/*SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;
SELECT * FROM Categories;*/
SELECT * FROM Suppliers;

Comments in Statements

Example

Ignore part of a line:
SELECT CustomerName, /*City,*/ Country FROM Customers;

Example

Ignore parts of a statment:
SELECT * FROM Customers WHERE (CustomerName LIKE 'L%'
OR CustomerName LIKE 'R%' /*OR CustomerName LIKE 'S%'
OR CustomerName LIKE 'T%'*/ OR CustomerName LIKE 'W%')
AND Country='USA'
ORDER BY CustomerName;

The AVG() Function

Example

SELECT AVG(column_name) FROM table_name;

SQL AVG() Example

Example

SELECT AVG(Price) AS PriceAverage FROM Products;

Example

SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);

SQL COUNT(column_name) Syntax

Example

SELECT COUNT(column_name) FROM table_name;

SQL COUNT(*) Syntax

Example

SELECT COUNT(*) FROM table_name;

SQL COUNT(DISTINCT column_name) Syntax

Example

SELECT COUNT(DISTINCT column_name) FROM table_name;

SQL COUNT(column_name) Example

Example

SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders
WHERE CustomerID=7;

SQL COUNT(*) Example

Example

SELECT COUNT(*) AS NumberOfOrders FROM Orders;

SQL COUNT(DISTINCT column_name) Example

Example

SELECT COUNT(DISTINCT CustomerID) AS NumberOfCustomers FROM Orders;

SQL FIRST() Syntax

SELECT FIRST(column_name) FROM table_name;
Note: The FIRST() function is only supported in MS Access.

SQL Server Syntax

Example

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name ASC;

Example

SELECT TOP 1 CustomerName FROM Customers
ORDER BY CustomerID ASC;

MySQL Syntax

Example

SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;

Example

SELECT CustomerName FROM Customers
ORDER BY CustomerID ASC
LIMIT 1;

Oracle Syntax

Example

SELECT column_name FROM table_name
WHERE ROWNUM <=1
ORDER BY column_name ASC;

Example

SELECT CustomerName FROM Customers
WHERE ROWNUM <=1
ORDER BY CustomerID ASC;

SQL FIRST() Example

Example

SELECT FIRST(CustomerName) AS FirstCustomer FROM Customers;

SQL LAST() Syntax

Example

SELECT LAST(column_name) FROM table_name;

SQL Server Syntax

Example

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;

Example

SELECT TOP 1 CustomerName FROM Customers
ORDER BY CustomerID DESC;

MySQL Syntax

Example

SELECT column_name FROM table_name
ORDER BY column_name DESC
LIMIT 1;

Example

SELECT CustomerName FROM Customers
ORDER BY CustomerID DESC
LIMIT 1;

Oracle Syntax

Example

SELECT column_name FROM table_name
ORDER BY column_name DESC
WHERE ROWNUM <=1;

Example

SELECT CustomerName FROM Customers
ORDER BY CustomerID DESC
WHERE ROWNUM <=1;

SQL LAST() Example

Example

SELECT LAST(CustomerName) AS LastCustomer FROM Customers;

SQL MAX() Syntax

Example

SELECT MAX(column_name) FROM table_name;

SQL MIN() Syntax

Example

SELECT MIN(column_name) FROM table_name;

SQL MIN() Example

Example

SELECT MIN(Price) AS SmallestOrderPrice FROM Products;

SQL SUM() Syntax

Example

SELECT SUM(column_name) FROM table_name;

Example

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

SQL GROUP BY Syntax

Example

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

Example

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;

SQL HAVING Syntax

Example

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

Example

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

Example

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
WHERE LastName='Davolio' OR LastName='Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

SQL UCASE() Syntax

Example

SELECT UCASE(column_name) FROM table_name;

Syntax for SQL Server

Example

SELECT UPPER(column_name) FROM table_name;

Example

SELECT UCASE(CustomerName) AS Customer, City
FROM Customers;

SQL LCASE() Syntax

Example

SELECT LCASE(column_name) FROM table_name;

Syntax for SQL Server

Example

SELECT LOWER(column_name) FROM table_name;

Example

SELECT LCASE(CustomerName) AS Customer, City
FROM Customers;

SQL MID() Syntax

Example

SELECT MID(column_name,start,length) AS some_name FROM table_name;

Example

SELECT SUBSTRING(column_name,start,length) AS some_name FROM table_name;

Example

SELECT SUBSTR(column_name,start,length) AS some_name FROM table_name;

SQL MID() Example

Example

SELECT MID(City,1,4) AS ShortCity
FROM Customers;

SQL LEN() Syntax

Example

SELECT LEN(column_name) FROM table_name;

Syntax for Oracle

Example

SELECT LENGTH(column_name) FROM table_name;

Example

SELECT CustomerName,LEN(Address) as LengthOfAddress
FROM Customers;

SQL ROUND() Syntax

Example

SELECT ROUND(column_name,decimals) FROM table_name;

Example

SELECT ProductName, ROUND(Price,0) AS RoundedPrice
FROM Products;

Example

SELECT ProductName, Price, Now() AS PerDate
FROM Products;

SQL FORMAT() Syntax

Example

SELECT FORMAT(column_name,format) FROM table_name;

Example

SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate
FROM Products;
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)