MAX - Gets the latest record. This function is used on date type of column of a table
Syntax:
SELECT MAX (dateColumn) AS "Max Date", colunm02, ...
FROM table;
FROM table;
MySQL Syntax:
SELECT CustomerName, CONCAT(Address,', ',City,', ',PostalCode,', ',Country) AS Address
FROM Customers;
or
SQL Syntax:
SELECT CustomerName, Address+', '+City+', '+PostalCode+', '+Country AS Address
FROM Customers;
UNION - is used to combine the results of two tables, and it eliminates duplicate rows from the tables. 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.
Syntax:
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
UNION ALL - The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.
Syntax:
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
MINUS - is used to return rows from the first query but not from the second query. Matching records of first and second query and other rows from the first query will be displayed as a result set.
Syntax:
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
MINUS
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
INTERSECT - returns only common rows returned by the two SELECT statements
[MySQL does not support INTERSECT operator]
Syntax:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
INTERSECT
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
SELECT INTO - selects data from one table and inserts it into a new table
Syntax:
SELECT *
INTO newtable [IN externaldb]
FROM table1;
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. 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. A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.
Syntax:
CREATE VIEW view_name AS
SELECT column_name(s) FROM table_name
WHERE condition
CREATE DATABASE - creates a new database
Syntax:
CREATE DATABASE dbname;
CREATE TABLE - creates a new table
Syntax:
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
ALTER TABLE - modifies a table
Syntax:
ALTER TABLE table_name
ADD column_name datatype
DROP TABLE - deletes a table
Syntax:
DROP TABLE table_name
CREATE INDEX - creates an index (search key). Indexes allow the database application to find data fast; without reading the whole table.
Syntax:
CREATE INDEX index_name
ON table_name (column_name)
UNIQUE INDEX - creates a unique index on a table. Duplicate values are not allowed
Syntax:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
DROP INDEX - deletes an index
Syntax:
No comments:
Post a Comment