1. SELECT * FROM
Customers;
2.The following SQL
statement selects the "CustomerName" and "City" columns
from the "Customers" table:
SELECT CustomerName,City
FROM Customers;
3.The DISTINCT keyword
can be used to return only distinct (different) values.
SQL SELECT DISTINCT
Syntax
SELECT DISTINCT
column_name,column_name FROM table_name;
4.The SQL WHERE Clause
The WHERE clause is used
to extract only those records that fulfill a specified criterion.
SQL WHERE Syntax
SELECT
column_name,column_name FROM table_name WHERE column_name operator value;
Example
SELECT * FROM Customers WHERE Country='Mexico';
Operators in The WHERE
Clause
The following operators
can be used in the WHERE clause:
Operator Description
= Equal
<> Not equal. Note: In some
versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple
possible values for a column
5.AND Operator Example
The following SQL
statement selects all customers from the country "Germany" AND the
city "Berlin", in the "Customers" table:
Example
SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin';
6.SQL ORDER BY Syntax
SELECT column_name,
column_name FROM table_name ORDER BY column_name ASC|DESC, column_name
ASC|DESC;
Example
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
7.SQL INSERT INTO Syntax
It is possible to write
the INSERT INTO statement in two forms.
The first form does not
specify the column names where the data will be inserted, only their values:
INSERT INTO table_name
VALUES (value1,value2,value3,...);
The second form
specifies both the column names and the values to be inserted:
INSERT INTO table_name
(column1,column2,column3,...) VALUES (value1,value2,value3,...)
INSERT INTO Customers
(CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');
8.SQL UPDATE Syntax
UPDATE table_name SET
column1=value1,column2=value2,... WHERE some_column=some_value;
9.SQL UPDATE Example
Assume we wish to update
the customer "Alfreds Futterkiste" with a new contact person and
city.
We use the following SQL
statement:
Example
UPDATE Customers SET
ContactName='Alfred Schmidt', City='Hamburg' WHERE CustomerName='Alfreds
Futterkiste';
10.The SQL DELETE
Statement
The DELETE statement is
used to delete rows in a table.
SQL DELETE Syntax DELETE
FROM table_name WHERE some_column=some_value;
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';
Delete All Data
It is possible to delete
all rows in a table without deleting the table. This means that the table
structure, attributes, and indexes will be intact:
DELETE FROM table_name;
or DELETE * FROM table_name;
11.SQL LIKE Syntax
SELECT column_name(s)
FROM table_name WHERE column_name LIKE pattern;
SELECT * FROM Customers
WHERE City LIKE 's%';
SELECT * FROM Customers
WHERE Country NOT LIKE '%land%';
12.SQL IN Syntax
SELECT column_name(s)
FROM table_name WHERE column_name IN (value1,value2,...);
Example
SELECT * FROM Customers
WHERE City IN ('Paris','London');
13.SQL BETWEEN Syntax
SELECT column_name(s)
FROM table_name WHERE column_name BETWEEN value1 AND value2;
Example
SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);
14.SQL Alias Syntax for
Columns
SELECT column_name AS
alias_name FROM table_name;
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;
The same SQL statement
without aliases:
SELECT Orders.OrderID,
Orders.OrderDate, Customers.CustomerName FROM Customers, Orders
WHERE
Customers.CustomerName="Around the Horn" AND
customers.CustomerID=Orders.CustomerID;
15.SQL INNER JOIN
(simple join).
An SQL INNER JOIN return
all rows from multiple tables where the join condition is met.
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;
16.SQL LEFT JOIN Keyword
The LEFT JOIN keyword
returns all rows from the left table (table1), with the matching rows in the
right table (table2). The result is NULL in the right side when there is no
match.
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;
17.SQL RIGHT JOIN
Keyword
The RIGHT JOIN keyword
returns all rows from the right table (table2), with the matching rows in the
left table (table1). The result is NULL in the left side when there is no
match.
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;
18.SQL FULL OUTER JOIN
Keyword
The FULL OUTER JOIN
keyword returns all rows from the left table (table1) and from the right table
(table2).
The FULL OUTER JOIN
keyword combines the result of both LEFT and RIGHT joins.
SQL FULL OUTER JOIN
Syntax
SELECT column_name(s)
FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;
19.The SQL UNION
Operator
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.
SQL UNION Syntax
SELECT column_name(s)
FROM table1 UNION SELECT column_name(s) FROM table2;
Note: The UNION operator
selects only distinct values by default. To allow duplicate values, use the ALL
keyword with UNION.
SQL UNION ALL Syntax
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
20.The SQL CREATE TABLE
Statement
The CREATE TABLE
statement is used to create a table in a database.
Tables are organized
into rows and columns; and each table must have a name.
SQL CREATE TABLE Syntax
CREATE TABLE
table_name(column_name1 data_type(size),column_name2 data_type(size),
column_name3
data_type(size),....);
21.SQL Constraints
SQL constraints are used
to specify rules for the data in a table.
If there is any
violation between the constraint and the data action, the action is aborted by
the constraint.
Constraints can be
specified when the table is created (inside the CREATE TABLE statement) or
after the table is created (inside the ALTER TABLE statement).
SQL CREATE TABLE +
CONSTRAINT Syntax
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,....);
22.SQL NOT NULL
Constraint
The NOT NULL constraint
enforces a column to NOT accept NULL values.
23.SQL UNIQUE Constraint
The UNIQUE constraint
uniquely identifies each record in a database table.
24.SQL PRIMARY KEY
Constraint
The PRIMARY KEY
constraint uniquely identifies each record in a database table.
25.SQL FOREIGN KEY
Constraint
A FOREIGN KEY in one
table points to a PRIMARY KEY in another table.
MySQL:
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))
26.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:
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))
27.DROP INDEX Syntax for
MySQL:
ALTER TABLE table_name
DROP INDEX index_name
28.SQL ALTER TABLE
Syntax
To add a column in a
table, use the following syntax:
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
29.SQL AVG() Syntax
SELECT AVG(column_name)
FROM table_name
30.SQL
COUNT(column_name) Syntax
The COUNT(column_name)
function returns the number of values (NULL values will not be counted) of the
specified column:
SELECT
COUNT(column_name) FROM table_name;
31.The FIRST() Function
The FIRST() function
returns the first value of the selected column.
SQL FIRST() Syntax
SELECT
FIRST/LAST(column_name) FROM table_name;
32.The MAX() Function
The MAX() function
returns the largest value of the selected column.
SQL MAX() Syntax
SELECT
MAX/MIN(column_name) FROM table_name;
33.The SUM() Function
The SUM() function
returns the total sum of a numeric column.
SQL SUM() Syntax
SELECT SUM(column_name)
FROM table_name;
34.The GROUP BY
Statement
The GROUP BY statement
is used in conjunction with the aggregate functions to group the result-set by
one or more columns.
SQL GROUP BY Syntax
SELECT column_name,
aggregate_function(column_name) FROM table_name
WHERE column_name
operator value GROUP BY column_name;
35.The HAVING Clause
The HAVING clause was
added to SQL because the WHERE keyword could not be used with aggregate
functions.
SQL HAVING Syntax
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;
36.The UCASE() Function
The UCASE() function
converts the value of a field to uppercase.
SQL UCASE() Syntax
SELECT
UCASE/LCASE(column_name) FROM table_name;
37.The MID() Function
The MID() function is
used to extract characters from a text field.
SQL MID() Syntax
SELECT
MID(column_name,start[,length]) AS some_name FROM table_name;
38.The LEN() Function
The LEN() function
returns the length of the value in a text field.
SQL LEN() Syntax
SELECT LEN(column_name)
FROM table_name;
No comments:
Post a Comment