SQL syntax

http://www.w3schools.com/sql/sql_intro.asp

RDBMS stands for Relational Database Management System.

SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The query and update commands form the DML part of SQL:

• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are:

• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

SELECT column_name(s)

FROM table_name

ORDER BY column_name(s) ASC|DESC

INSERT INTO Persons

VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')

INSERT INTO Persons (P_Id, LastName, FirstName)

VALUES (5, 'Tjessem', 'Jakob')

UPDATE Persons

SET Address='Nissestien 67', City='Sandnes'

WHERE LastName='Tjessem' AND FirstName='Jakob'

DELETE FROM Persons

WHERE LastName='Tjessem' AND FirstName='Jakob'

Delete All Rows

DELETE FROM table_name

or

DELETE * FROM table_name

----------------------------------

SQL TOP Clause

Not all database systems support the TOP clause.

sybase support top, oracle use rownum

SQL Server

SELECT TOP number|percent column_name(s)

FROM table_name

MySQL Syntax

SELECT column_name(s)

FROM table_name

LIMIT number

Oracle Syntax

SELECT column_name(s)

FROM table_name

WHERE ROWNUM <= number

----------------------------

SELECT * FROM Persons
WHERE City LIKE '%tav%'

SELECT * FROM Persons
WHERE City NOT LIKE '%tav%'

---------

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

------

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.

• JOIN: Return rows when there is at least one match in both tables
• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
• FULL JOIN: Return rows when there is a match in one of the tables

----------

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

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_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

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_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

The FULL JOIN keyword return rows when there is a match in one of the tables.

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

-------------

The SQL UNION operator combines two or more SELECT statements.

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

The UNION command selects only distinct values.

UNION ALL will include everything

SELECT E_Name FROM Employees_Norway
UNION ALL

---

create DB

CREATE DATABASE database_name

create table

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

SELECT E_Name FROM Employees_USA