Learn
Basic SQL with Example...
SQL Statement
|
Syntax
|
Example
|
|
AND / OR
|
The AND operator displays
a record if both the first condition and the second condition is true
The OR operator displays a record if
either the first condition or the second condition is true.
|
SELECT column_name(s)
FROM table_name WHERE condition AND|OR condition |
SELECT * FROM Persons
WHERE FirstName=‘Anand’ AND LastName=‘Somani’
SELECT * FROM Persons
WHERE FirstName=‘Anand’ OR LastName=‘Somani’
SELECT * FROM Persons WHERE
LastName=‘Somani’ AND (FirstName=‘Anand’ OR FirstName='Ola') |
ALTER TABLE
|
The ALTER TABLE statement
is used to add, delete, or modify columns in an existing table.
|
ALTER TABLE table_name
ADD column_name datatype
or
ALTER TABLE table_name
DROP COLUMN column_name |
MySQL / SQL Server / Oracle / MS
Access:
ALTER TABLE Persons
ADD UNIQUE (P_Id)
MySQL:
ALTER TABLE Persons
DROP INDEX uc_PersonID
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID |
AS (alias)
|
You can give a table or a column
another name by using an alias. This can be a good thing to do if you have
very long or complex table names or column names.
|
SELECT column_name AS column_alias
FROM table_name
or
SELECT column_name
FROM table_name AS table_alias |
SELECT po.OrderID, p.LastName,
p.FirstName
FROM Persons AS p, Product_Orders AS po WHERE p.LastName='Hansen' AND p.FirstName='Ola' |
BETWEEN
|
The BETWEEN operator selects a range
of data between two values. The values can be numbers, text, or dates.
|
SELECT column_name(s)
FROM table_name WHERE column_name BETWEEN value1 AND value2 |
SELECT * FROM Persons
WHERE LastName BETWEEN 'Hansen' AND 'Pettersen' |
CREATE DATABASE
|
The CREATE DATABASE statement is used
to create a database
|
CREATE DATABASE database_name
|
CREATE DATABASE my_db
|
CREATE TABLE
|
The CREATE TABLE statement is used to
create a table in a database.
|
CREATE TABLE table_name
( column_name1 data_type, column_name2 data_type, column_name2 data_type, ... ) |
CREATE TABLE Persons
( P_Id int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ) |
CREATE INDEX
|
CREATE INDEX index_name
ON table_name (column_name)
CREATE UNIQUE INDEX index_name
ON table_name (column_name) |
CREATE INDEX PIndex
ON Persons (LastName, FirstName) |
|
CREATE VIEW
|
In SQL, a view is a virtual table
based on the result-set of an SQL statement.
A view contains rows and columns,
just like a real table. The fields in a view are fields from one or more real
tables in the database.
|
CREATE VIEW view_name AS
SELECT column_name(s) FROM table_name WHERE condition
You can add SQL functions, WHERE, and
JOIN statements to a view and present the data as if the data were coming
from one single table.
|
|
DELETE
|
The DELETE statement is used to
delete rows in a table.
|
DELETE FROM table_name
WHERE some_column=some_value
DELETE FROM table_name
(Note: Deletes the entire table!!)
DELETE * FROM table_name
(Note: Deletes the entire table!!) |
|
DROP DATABASE
|
The DROP DATABASE statement is used
to delete a database.
|
DROP DATABASE database_name
|
|
DROP INDEX
|
Indexes, tables, and databases can
easily be deleted/removed with the DROP statement.
|
DROP INDEX table_name.index_name (SQL
Server)
DROP INDEX index_name ON table_name (MS Access) DROP INDEX index_name (DB2/Oracle) ALTER TABLE table_name DROP INDEX index_name (MySQL) |
|
DROP TABLE
|
The DROP TABLE statement is used to
delete a table.
|
DROP TABLE table_name
|
|
GROUP BY
|
SELECT column_name,
aggregate_function(column_name)
FROM table_name WHERE column_name operator value GROUP BY column_name |
||
HAVING
|
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 |
||
IN
|
The IN operator allows you to specify
multiple values in a WHERE clause.
|
SELECT column_name(s)
FROM table_name WHERE column_name IN (value1,value2,..) |
|
INSERT INTO
|
The INSERT INTO statement is used to
insert new records in a table.
|
INSERT INTO table_name
VALUES (value1, value2, value3,....)
INSERT INTO table_name
(column1, column2, column3,...) VALUES (value1, value2, value3,....) |
INSERT INTO Persons
VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')
INSERT INTO Persons
VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger') |
INNER JOIN
|
The INNER JOIN keyword return rows
when there is at least one match in both tables
|
SELECT column_name(s)
FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
SELECT Persons.LastName,
Persons.FirstName, Orders.OrderNo
FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName |
LEFT JOIN
|
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 |
SELECT Persons.LastName,
Persons.FirstName, Orders.OrderNo
FROM Persons LEFT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName |
RIGHT JOIN
|
The RIGHT JOIN keyword Return all
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 |
SELECT Persons.LastName,
Persons.FirstName, Orders.OrderNo
FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName |
FULL JOIN
|
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 |
SELECT Persons.LastName,
Persons.FirstName, Orders.OrderNo
FROM Persons FULL JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName |
LIKE
|
The LIKE operator is used in a WHERE
clause to search for a specified pattern in a column.
The LIKE operator is used to search
for a specified pattern in a column.
|
SELECT column_name(s)
FROM table_name WHERE column_name LIKE pattern |
|
ORDER BY
|
The ORDER BY keyword is used to sort
the result-set by a specified column.
The ORDER BY keyword sort the records
in ascending order by default.
If you want to sort the records in a
descending order, you can use the DESC keyword.
|
SELECT column_name(s)
FROM table_name ORDER BY column_name [ASC|DESC] |
SELECT * FROM Persons
ORDER BY LastName
SELECT * FROM Persons
ORDER BY LastName DESC |
SELECT
|
The SELECT statement is used to
select data from a database.
The result is stored in a result
table, called the result-set.
|
SELECT column_name(s)
FROM table_name |
|
SELECT *
|
The SELECT * statement is used to all
columns/data from a database.
|
SELECT *
FROM table_name |
|
SELECT DISTINCT
|
In a table, some of the columns may
contain duplicate values. This is not a problem, however, sometimes you will
want to list only the different (distinct) values in a table.
The DISTINCT keyword can be used to
return only distinct (different) values.
|
SELECT DISTINCT column_name(s)
FROM table_name |
SELECT DISTINCT City FROM Persons
|
SELECT INTO
|
SELECT *
INTO new_table_name [IN externaldatabase] FROM old_table_name
or
SELECT column_name(s)
INTO new_table_name [IN externaldatabase] FROM old_table_name |
SELECT *
INTO Persons_Backup FROM Persons
SELECT LastName,Firstname
INTO Persons_Backup FROM Persons WHERE City='Sandnes' |
|
SELECT TOP
|
The TOP clause is used to specify the
number of records to return.
The TOP clause can be very useful on
large tables with thousands of records. Returning a large number
of records can impact on performance.
|
SELECT TOP number|percent
column_name(s)
FROM table_name |
|
TRUNCATE TABLE
|
What if we only want to delete the
data inside the table, and not the table itself?
|
TRUNCATE TABLE table_name
|
|
UNION
|
The UNION operator is used to combine
the result-set of two or more SELECT statements.
Notice that each SELECT statement
within the UNION must have the same number of columns. The columns
must also have similar data types. Also, the columns in each
SELECT statement must be in the same order
|
SELECT column_name(s) FROM
table_name1
UNION SELECT column_name(s) FROM table_name2 |
SELECT E_Name FROM Employees_Norway
UNION SELECT E_Name FROM Employees_USA |
UNION ALL
|
SELECT column_name(s) FROM
table_name1
UNION ALL SELECT column_name(s) FROM table_name2 |
SELECT E_Name FROM Employees_Norway
UNION ALL SELECT E_Name FROM Employees_USA |
|
UPDATE
|
The UPDATE statement is used to
update existing records in a table.
Note: Notice the WHERE clause in
the UPDATE syntax. The WHERE clause specifies which record or records that
should be updated. If you omit the WHERE clause, all records will be updated
|
UPDATE table_name
SET column1=value, column2=value,... WHERE some_column=some_value |
UPDATE Persons
SET Address='Nissestien 67', City='Sandnes' WHERE LastName='Tjessem' AND FirstName='Jakob'
UPDATE Persons
SET Address='Nissestien 67', City='Sandnes' |
WHERE
|
The WHERE clause is used to extract
only those records that fulfill a specified criterion.
|
SELECT column_name(s)
FROM table_name WHERE column_name operator value |
SELECT * FROM Persons
WHERE City='Sandnes' |
