DML
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
DDL
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
SQL Commands:
Select:
·
SELECT
* FROM tablename;
DISTINCT
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.
·
SELECT
DISTINCT column_name(s) FROM table_name
WHERE
The
WHERE clause is used to filter records.
·
SELECT
column_name(s) FROM table_name WHERE
column_name operator value
AND & OR Operator:
The
AND operator displays a record if both the first condition and the second
condition are true.
The
OR operator displays a record if either the first condition or the second
condition is true.
·
SELECT
* FROM Persons WHERE
FirstName='Tove' AND LastName='Svendson'
·
SELECT
* FROM Persons WHERE FirstName='Tove' OR
FirstName='Ola'
·
SELECT
* FROM Persons WHERE LastName='Svendson' AND (FirstName='Tove' OR
FirstName='Ola')
ORDER BY
The
ORDER BY keyword is used to sort the result-set.
The
ORDER BY keyword sorts 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(s) ASC|DESC
·
SELECT
* FROM Persons ORDER BY LastName
·
SELECT
* FROM Persons ORDER BY LastName DESC
INSERT
The
INSERT INTO statement is used to insert new records in a table.
·
INSERT
INTO table_name VALUES (value1, value2, value3,...)
·
INSERT
INTO Persons VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')
Insert
Data Only in Specified Columns
·
INSERT
INTO Persons (P_Id, LastName, FirstName) VALUES (5, 'Tjessem', 'Jakob')
UPDATE
The
UPDATE statement is used to update records in a table.
·
UPDATE
table_name SET column1=value, column2=value2,... WHERE some_column=some_value
DELETE
The
DELETE statement is used to delete records in a table.
·
DELETE
FROM table_name WHERE some_column=some_value
Delete
All Rows
·
DELETE
FROM table_name
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
·
SELECT
column_name(s) FROM table_name LIMIT number
·
SELECT
* FROM Persons LIMIT 5
We
want to select only the two first records in the table
·
SELECT
TOP 2 * FROM Persons
We
want to select only 50% of the records in the table
·
SELECT
TOP 50 PERCENT * FROM Persons
LIKE (wildcard)
The
LIKE operator is used in a WHERE clause to search for a specified pattern in a
column.
·
SELECT
column_name(s) FROM table_name WHERE column_name LIKE pattern
We
want to select the persons living in a city that starts with "s" from
the table
·
SELECT
* FROM Persons WHERE City LIKE 's%'
We
want to select the persons living in a city that ends with an "s"
from the table
·
SELECT
* FROM Persons WHERE City LIKE '%s'
We
want to select the persons living in a city that contains the pattern
"tav" from the table
·
SELECT
* FROM Persons WHERE City LIKE '%tav%'
It
is also possible to select the persons living in a city that does NOT contain
the pattern "tav" from the table, by using the NOT keyword.
·
SELECT
* FROM Persons WHERE City NOT LIKE '%tav%'
We
want to select the persons with a first name that starts with any character,
followed by "la" from the table.
·
SELECT
* FROM Persons WHERE FirstName LIKE '_la'
We
want to select the persons with a last name that starts with "S",
followed by any character, followed by "end", followed by any
character, followed by "on" from the "Persons" table.
·
SELECT
* FROM Persons WHERE LastName LIKE 'S_end_on'
We
want to select the persons with a last name that starts with "b" or
"s" or "p" from the table.
·
SELECT
* FROM Persons WHERE LastName LIKE '[bsp]%'
We
want to select the persons with a last name that do not start with
"b" or "s" or "p" from the table.
·
SELECT
* FROM Persons WHERE LastName LIKE '[!bsp]%'
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,...)
We
want to select the persons with a last name equal to "Hansen" or
"Pettersen" from the table
·
SELECT
* FROM Persons WHERE LastName IN ('Hansen','Pettersen')
BETWEEN
The
BETWEEN operator is used in a WHERE clause to select a range of data between
two values.
·
SELECT
column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
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.
An
alias name could be anything, but usually it is short.
·
SELECT
column_name(s) FROM table_name AS alias_name
·
SELECT
column_name AS alias_name FROM table_name
·
SELECT
po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Hansen' AND p.FirstName='Ola'
·
SELECT
Product_Orders.OrderID, Persons.LastName, Persons.FirstName FROM Persons,
Product_Orders WHERE Persons.LastName='Hansen' AND Persons.FirstName='Ola'
JOIN
SQL
joins are used to query data from two or more tables, based on a relationship
between certain columns in these tables.
Tables
in a database are often related to each other with keys.
A
primary key is a column (or a combination of columns) with a unique value for
each row. Each primary key value must be unique within the table. The purpose
is to bind data together, across tables, without repeating all of the data in
every table.
·
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
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 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
·
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
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
column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM
table_name2
The
UNION operator selects only distinct values by default. To allow duplicate values,
use UNION ALL.
·
SELECT
E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA
·
SELECT
E_Name FROM Employees_Norway UNION ALL SELECT E_Name FROM Employees_USA
SELECT INTO
The
SELECT INTO statement selects data from one table and inserts it into a
different table.
The
SELECT INTO statement is most often used to create backup copies of tables.
·
SELECT
* INTO new_table_name [IN externaldatabase] FROM old_tablename
·
SELECT
column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename
·
SELECT
* INTO Persons_Backup FROM Persons
·
SELECT
* INTO Persons_Backup IN 'Backup.mdb' FROM Persons
·
SELECT
LastName,FirstName INTO Persons_Backup FROM Persons
·
SELECT
LastName,Firstname INTO Persons_Backup FROM Persons WHERE City='Sandnes'
·
SELECT
Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER
JOIN Orders ON Persons.P_Id=Orders.P_Id
CREATE DATABASE
The
CREATE DATABASE statement is used to create a database.
·
CREATE
DATABASE database_name
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_name3
data_type, .... )
Constraints
Constraints
are used to limit the type of data that can go into a table.
Constraints
can be specified when a table is created (with the CREATE TABLE statement) or
after the table is created (with the ALTER TABLE statement).
We
will focus on the following constraints:
·
NOT NULL
·
UNIQUE
·
PRIMARY KEY
·
FOREIGN KEY
·
CHECK
·
DEFAULT
NOT NULL
The
NOT NULL constraint enforces a column to NOT accept NULL values.
The
NOT NULL constraint enforces a field to always contain a value. This means that
you cannot insert a new record, or update a record without adding a value to
this field.
·
CREATE
TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName
archar(255), Address varchar(255), City varchar(255) )
UNIQUE
The
UNIQUE constraint uniquely identifies each record in a database table.
The
UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for
a column or set of columns.
A
PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note
that you can have many UNIQUE constraints per table, but only one PRIMARY KEY
constraint per table.
·
CREATE
TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName
archar(255), Address varchar(255), City varchar(255), UNIQUE (P_Id) )
UNIQUE
Constraint on ALTER TABLE
·
ALTER
TABLE Persons ADD UNIQUE (P_Id)
·
ALTER
TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
DROP
a UNIQUE
·
ALTER
TABLE Persons DROP INDEX uc_PersonID
PRIMARY KEY
The
PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary
keys must contain unique values.
A
primary key column cannot contain NULL values.
Each
table should have a primary key, and each table can have only ONE primary key.
·
CREATE
TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName
varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) )
PRIMARY
KEY Constraint on ALTER TABLE
·
ALTER
TABLE Persons ADD PRIMARY KEY (P_Id)
DROP
a PRIMARY KEY
·
ALTER
TABLE Persons DROP PRIMARY KEY
FOREIGN KEY
A
FOREIGN KEY in one table points to a PRIMARY KEY in another table.
·
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) )
FOREIGN KEY Constraint on ALTER
TABLE
·
ALTER
TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
DROP a FOREIGN KEY
·
ALTER
TABLE Orders DROP FOREIGN KEY fk_PerOrders
CHECK
The
CHECK constraint is used to limit the value range that can be placed in a
column.
If
you define a CHECK constraint on a single column it allows only certain values
for this column.
If
you define a CHECK constraint on a table it can limit the values in certain
columns based on values in other columns in the row.
·
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) )
CHECK
Constraint on ALTER TABLE
·
ALTER
TABLE Persons ADD CHECK (P_Id>0)
DROP
a CHECK
·
ALTER
TABLE Persons DROP CHECK chk_Person
DEFAULT
The
DEFAULT constraint is used to insert a default value into a column.
The
default value will be added to all new records, if no other value is specified.
·
CREATE
TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName
varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' )
DEFAULT
Constraint on ALTER TABLE
·
ALTER
TABLE Persons ALTER City SET DEFAULT 'SANDNES'
DROP
a DEFAULT
·
ALTER
TABLE Persons ALTER City DROP DEFAULT
CREATE INDEX
The
CREATE INDEX statement is used to create indexes in tables.
Indexes
allow the database application to find data fast; without reading the whole
table.
·
CREATE
INDEX index_name ON table_name (column_name)
·
CREATE
UNIQUE INDEX index_name ON table_name (column_name)
In
second command duplicate not allowed.
·
CREATE
INDEX PIndex ON Persons (LastName)
·
CREATE
INDEX PIndex ON Persons (LastName, FirstName)
DROP
Indexes,
tables, and databases can easily be deleted/removed with the DROP statement.
DROP INDEX
·
ALTER
TABLE table_name DROP INDEX index_name
DROP TABLE
·
DROP
TABLE table_name
DROP DATABASE
·
DROP
DATABASE database_name
TRUNCATE
What
if we only want to delete the data inside the table, and not the table itself?
·
TRUNCATE
TABLE table_name
ALTER
The
ALTER TABLE statement is used to add, delete, or modify columns in an existing
table.
·
ALTER
TABLE table_name ADD column_name datatype
·
ALTER
TABLE table_name DROP COLUMN column_name
AUTO INCREMENT
Auto-increment
allows a unique number to be generated when a new record is inserted into a
table.
è
CREATE
TABLE Persons ( P_Id int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT
NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY
KEY (P_Id) )
è
ALTER
TABLE Persons AUTO_INCREMENT=100
VIEW
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.
·
CREATE
VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
·
CREATE
VIEW [Current Product List] AS SELECT ProductID,ProductName FROM Products WHERE
Discontinued=No
·
SELECT
* FROM [Current Product List]
·
CREATE
VIEW [Products Above Average Price] AS SELECT ProductName,UnitPrice FROM
Products WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
·
SELECT
* FROM [Products Above Average Price]
·
CREATE
VIEW [Category Sales For 1997] AS SELECT DISTINCT
CategoryName,Sum(ProductSales) AS CategorySales FROM [Product Sales for 1997]
GROUP BY CategoryName
·
SELECT
* FROM [Category Sales For 1997]
·
SELECT
* FROM [Category Sales For 1997] WHERE CategoryName='Beverages'
·
CREATE
OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE
condition
·
CREATE
VIEW [Current Product List] AS SELECT ProductID,ProductName,Category FROM
Products WHERE Discontinued=No
·
DROP
VIEW view_name
Dates
The
most difficult part when working with dates is to be sure that the format of
the date you are trying to insert, matches the format of the date column in the
database.
As
long as your data contains only the date portion, your queries will work as
expected. However, if a time portion is involved, it gets complicated.
NOW() Returns
the current date and time
CURDATE() Returns the current date
CURTIME() Returns the current time
DATE() Extracts
the date part of a date or date/time expression
EXTRACT() Returns a single part of a date/time
DATE_ADD() Adds a
specified time interval to a date
DATE_SUB() Subtracts a specified time interval
from a date
DATEDIFF() Returns the number of days between two
dates
DATE_FORMAT() Displays
date/time data in different formats
Date Data Types
DATE - format
YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MM:SS
TIMESTAMP - format:
YYYY-MM-DD HH:MM:SS
YEAR - format
YYYY or YY
·
SELECT
* FROM Orders WHERE OrderDate='2008-11-11'
NULL Values
NULL
values represent missing unknown data.
By
default, a table column can hold NULL values.
SELECT
LastName,FirstName,Address FROM Persons WHERE Address IS NULL
SELECT
LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL
SELECT
ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products
MySQL Data Types
CHAR(size) Holds
a fixed length string (can contain letters, numbers, and special characters).
The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size) Holds
a variable length string (can contain letters, numbers, and special
characters). The maximum size is specified in parenthesis. Can store up to 255
characters. Note: If you put a greater value than 255 it will be converted to a
TEXT type
TINYTEXT Holds a string with a maximum length
of 255 characters
TEXT Holds a string with a maximum length
of 65,535 characters
BLOB For
BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT Holds
a string with a maximum length of 16,777,215 characters
MEDIUMBLOB For
BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT Holds
a string with a maximum length of 4,294,967,295 characters
LONGBLOB For
BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
SET Similar
to ENUM except that SET may contain up to 64 list items and can store more than
one choice
ENUM(x,y,z,etc.) Let
you enter a list of possible values. You can list up to 65535 values in an ENUM
list. If a value is inserted that is not in the list, a blank value will be
inserted.
Note: The values are
sorted in the order you enter them.
You enter the possible
values in this format: ENUM('X','Y','Z')
Number
types:
TINYINT(size) -128
to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be
specified in parenthesis
SMALLINT(size) -32768
to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be
specified in parenthesis
MEDIUMINT(size) -8388608
to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be
specified in parenthesis
INT(size) -2147483648
to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits
may be specified in parenthesis
BIGINT(size) -9223372036854775808
to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum
number of digits may be specified in parenthesis
FLOAT(size,d) A
small number with a floating decimal point. The maximum number of digits may be
specified in the size parameter. The maximum number of digits to the right of
the decimal point is specified in the d parameter
DOUBLE(size,d) A
large number with a floating decimal point. The maximum number of digits may be
specified in the size parameter. The maximum number of digits to the right of
the decimal point is specified in the d parameter
DECIMAL(size,d) A
DOUBLE stored as a string , allowing for a fixed decimal point. The maximum
number of digits may be specified in the size parameter. The maximum number of
digits to the right of the decimal point is specified in the d parameter
Date
types:
DATE() A
date. Format: YYYY-MM-DD
Note: The supported range is from
'1000-01-01' to '9999-12-31'
DATETIME() *A
date and time combination. Format: YYYY-MM-DD HH:MM:SS
Note: The supported range is from
'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP() *A
timestamp. TIMESTAMP values are stored as the number of seconds since the Unix
epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MM:SS
Note: The supported range is from
'1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC
TIME() A
time. Format: HH:MM:SS
Note: The supported range is from
'-838:59:59' to '838:59:59'
YEAR() A
year in two-digit or four-digit format.
Note: Values allowed in four-digit
format: 1901 to 2155. Values allowed in two-digit format: 70 to 69,
representing years from 1970 to 2069
Aggregate Functions
SQL
aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
AVG() - Returns
the average value
COUNT() - Returns
the number of rows
FIRST() - Returns
the first value
LAST() - Returns
the last value
MAX() - Returns
the largest value
MIN() - Returns
the smallest value
SUM() - Returns
the sum
Scalar functions
SQL
scalar functions return a single value, based on the input value.Useful scalar
functions:
UCASE() - Converts
a field to upper case
LCASE() - Converts
a field to lower case
MID() - Extract
characters from a text field
LEN() - Returns
the length of a text field
ROUND() - Rounds
a numeric field to the number of decimals specified
NOW() - Returns
the current system date and time
FORMAT() - Formats
how a field is to be displayed
AVG()
·
SELECT
AVG(column_name) FROM table_name
·
SELECT
AVG(OrderPrice) AS OrderAverage FROM Orders
·
SELECT
Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
COUNT()
The
COUNT() function returns the number of rows that matches a specified criteria.
·
SELECT
COUNT(column_name) FROM table_name
COUNT(*)
The
COUNT(*) function returns the number of records in a table
·
SELECT
COUNT(*) FROM table_name
·
COUNT(DISTINCT
column_name)
·
SELECT
COUNT(DISTINCT column_name) FROM table_name
FIRST()
The
FIRST() function returns the first value of the selected column.
·
SELECT
FIRST(column_name) FROM table_name
·
SELECT
FIRST(OrderPrice) AS FirstOrderPrice FROM Orders
·
SELECT
OrderPrice FROM Orders ORDER BY O_Id LIMIT 1
LAST()
The
LAST() function returns the last value of the selected column.
·
SELECT
LAST(column_name) FROM table_name
·
SELECT
LAST(OrderPrice) AS LastOrderPrice FROM Orders
·
SELECT
OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1
MAX()
The
MAX() function returns the largest value of the selected column.
·
SELECT
MAX(column_name) FROM table_name
·
SELECT
MAX(OrderPrice) AS LargestOrderPrice FROM Orders
MIN()
The
MIN() function returns the smallest value of the selected column.
·
SELECT
MIN(column_name) FROM table_name
·
SELECT
MIN(OrderPrice) AS SmallestOrderPrice FROM Orders
SUM()
The
SUM() function returns the total sum of a numeric column.
·
SELECT
SUM(column_name) FROM table_name
·
SELECT
SUM(OrderPrice) AS OrderTotal FROM Orders
GROUP BY
The
GROUP BY statement is used in conjunction with the aggregate functions to group
the result-set by one or more columns.
· SELECT
column_name, aggregate_function(column_name) FROM table_name WHERE column_name
operator value GROUP BY column_name
·
SELECT
Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer
·
SELECT
Customer,OrderDate,SUM(OrderPrice) FROM Orders GROUP BY Customer,OrderDate
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
·
SELECT
Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING
UM(OrderPrice)<2000
·
SELECT
Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Hansen' OR
Customer='Jensen' GROUP BY Customer HAVING SUM(OrderPrice)>1500
UCASE()
The
UCASE() function converts the value of a field to uppercase.
·
SELECT
UCASE(column_name) FROM table_name
·
SELECT
UCASE(LastName) as LastName,FirstName FROM Persons
LCASE()
The
LCASE() function converts the value of a field to lowercase.
·
SELECT
LCASE(column_name) FROM table_name
·
SELECT
LCASE(LastName) as LastName,FirstName FROM Persons
MID()
The
MID() function is used to extract characters from a text field.
·
SELECT
MID(column_name,start[,length]) FROM table_name
column_name Required.
The field to extract characters from
start Required. Specifies the starting
position (starts at 1)
length Optional.
The number of characters to return. If omitted, the MID() function returns the
rest of the text
·
SELECT
MID(City,1,4) as SmallCity FROM Persons
LEN()
The LEN() function
returns the length of the value in a text field.
·
SELECT
LEN(column_name) FROM table_name
·
SELECT
LEN(Address) as LengthOfAddress FROM Persons
ROUND()
The ROUND() function
is used to round a numeric field to the number of decimals
specified.
·
SELECT
ROUND(column_name,decimals) FROM table_name
·
SELECT
ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products
NOW()
The NOW() function
returns the current system date and time.
·
SELECT
NOW() FROM table_name
·
SELECT
ProductName, UnitPrice, Now() as PerDate FROM Products
FORMAT()
The FORMAT() function
is used to format how a field is to be displayed.
·
SELECT
FORMAT(column_name,format) FROM table_name
·
SELECT
ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate FROM Products
