ITEC48 (Sir Noel) Flashcards
is
used to fetch the data from a
database table which returns this
data in the form of a result table.
SQL SELECT statement
specifies the columns
to be displayed
SELECT Clause
specifies the table
containing the columns that are listed in
the SELECT Clause
FROM Clause
used to specify a condition
while fetching the data from a
single table or by joining with
multiple tables.
WHERE clause
can
perform arithmetical operations
on numeric operands involved.
Arithmetic operators
can also
be used in date arithmetic.
+ and - operators
are used to
override the default precedence
or to clarify the statement.
Parenthesis ()
Renames a column heading
Is useful with calculations
Immediately follows the column
name
Column Alias
are
used in conditions that
compare one expression to
another or expression.
Comparison Conditions
selects values within a given
range. The values can be
numbers, text, or dates.
The BETWEEN operator
is also known as the
membership condition.
IN condition
to test values in a specified
set of values
IN condition
is used in a
WHERE clause to search for a
specified pattern in a column.
LIKE operator
two wildcards often used
in conjunction with the LIKE
operator:
% and _
represents
zero, one, or multiple characters
%
represents a
single character
_
combines the
result of two component conditions
to produce a single result based on
those conditions, or it inverts the
result of a single condition.
logical condition
used to sort
the result-set in ascending or
descending order.
ORDER BY
sorts the
records in ascending order by default.
ORDER BY
used to add, delete or modify
columns in an existing table.
ALTER TABLE
used to remove a
table definition and all the data,
indexes, triggers, constraints and
permission specifications for that
table.
DROP TABLE
used to delete
complete data from an existing
TRUNCATE TABLE
used to return only distinct
eliminates
duplicate records from the results.
SELECT DISTINCT/DISTINCT
is a predefined
formula which takes one or more
arguments as input then process
the arguments and returns an
output.
function
are simply
sub-programs, which are
commonly used and re-used
throughout SQL database
applications for processing or
manipulating data.
SQL functions
returns a single value,
calculated from values in a
column.
AVG(), COUNT(), FIRST(),
LAST(), MAX(), MIN(), SUM()
Aggregate Function
returns a single value, based on the
input value.
It also allows you to perform different
calculations on data values.
UCASE(), LCASE(), MID(), LEN(),
ROUND(), NOW(), FORMAT()
SQL Scalar Function
(Non-Aggregate Function)
a function
where the values of multiple rows
are grouped together as input on
certain criteria to form a single
value of more significant meaning.
allows
you to perform a calculation on
a set of values to return a single
scalar value.
aggregate function
function calculates
the average of the values /
returns the average value of a
numeric column
AVG()
returns the number of rows in a
group or in matches a specified
criterion.
COUNT()
returns the
minimum value/smallest value
in a set of values/selected
columns..
MIN()
returns
the maximum value/ largest
value in a set of values/ selected
columns.
MAX() function
is used
to group rows returned by
SELECT statement into a set of
summary rows or groups based on
values of columns or expressions.
GROUP BY
is often used
with the GROUP BY clause in the
SELECT statement to filter group of
rows based on a specified condition.
works like the
WHERE clause if it is not used with the
GROUP BY clause.
HAVING clause
used to filter
rows
WHERE CLAUSE
used to
filter groups of rows.
HAVING CLAUSE
A functions that perform
operations on character values.
MySQL String Function
Converts a string to upper-case
UCASE()
extracts a
substring from a string (starting
at any position).
MID()V
A functions that perform
operations on numeric values.
MySQL Numeric Function
rounds a
number to a specified number of
decimal places.
ROUND()
A functions that perform
operations on date values.
DATE FUNCTION
DATE FUNCTIONS
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
SQL Date Data Types
DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD
HH:MI:SS
YEAR - format YYYY or YY
A functions for handling null
values.
Accepts two arguments and returns the first if its not NULL
IFNULL
combine
the results of multiple sets into a
single set
are
used to combine or subtract the
records from two tables.
RELATIONAL SET OPERATORS
The resulting
records will also have same
number of columns and same
datatypes for the respective
column.
UNION
COMPATIBLITY.
is used
to combine the result-set of two
or more SELECT statements.
(only distinct values)
UNION operator
is
used to combine two SELECT
statements, but returns rows only from
the first SELECT statement that are
identical to a row in the second
SELECT statement.
returns only common rows
returned by the two SELECT
statements.
INTERSECT operator
used to subtract
the result set obtained by first SELECT
query from the result set obtained by
second SELECT query.
will return only
those rows which are unique in only
first SELECT query and not those
rows which are common to both first
and second SELECT queries.
MINUS operator
operator is
used to combine two SELECT
statements and returns rows from
the first SELECT statement that are
not returned by the second
SELECT statement.
returns
only rows, which are not
available in the second SELECT
statement.
EXCEPT operator
specifies how
to relate tables in the query.
is one of the set
operations available in relational
databases.
JOIN operator
is used to
combine rows from two or
more tables, based on a related
column between them.
JOIN clause
returns
records that have matching
values in both tables.
INNER JOIN
returns
all rows from both the
participating tables which satisfy
the join condition along with rows
which do not satisfy the join
condition.
SQL OUTER JOIN
is a query that
contains the same or different
join types, which are used more
than once.
Multiple join
Goes through conditions and return a value when the
first condition is met
CASE
Converts a value (of any type) into a specified
datatype
CAST
Returns the first non-null value in a list
COALESCE
Converts a number from one numeric base system to
another
CONV
Converts a value into the specified datatype or
character set
CONVERT
Return a specified value if the expression is NULL,
otherwise return the expression
IFNULL
Returns 1 or 0 depending on whether an expression is
NULL
ISNULL
Returns the AUTO_INCREMENT id of the last row that has
been inserted or updated in a table
LAST_INSERT_ID
Returns the current date and time
CURRENT_TIMESTAMP
Returns the current time
CURTIME
Extracts the date part from a datetime expression
DATE
Returns the number of days between two date values
DATEDIFF
Returns a date from a numeric datevalue
FROM_DAYS
Returns the current date and time
LOCALTIME
LOCALTIMESTAMP
Returns the difference between two time/datetime
expressions
TIMEDIFF
Returns the number of records returned by a select
query
COUNT
Returns e raised to the power of a specified number
EXP
Returns the largest integer value that is <= to a number
FLOOR
Returns the natural logarithm of a number
LN
