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