ITEC48 (Sir Noel)

Question 1

is
used to fetch the data from a
database table which returns this
data in the form of a result table.

Answer 1

SQL SELECT statement

Question 2

specifies the columns
to be displayed

Answer 2

SELECT Clause

Question 3

specifies the table
containing the columns that are listed in
the SELECT Clause

Answer 3

FROM Clause

Question 4

used to specify a condition
while fetching the data from a
single table or by joining with
multiple tables.

Answer 4

WHERE clause

Question 5

can
perform arithmetical operations
on numeric operands involved.

Answer 5

Arithmetic operators

Question 6

can also
be used in date arithmetic.

Answer 6

+ and - operators

Question 7

are used to
override the default precedence
or to clarify the statement.

Answer 7

Parenthesis ()

Question 8

Renames a column heading

Is useful with calculations

Immediately follows the column
name

Answer 8

Column Alias

Question 9

are
used in conditions that
compare one expression to
another or expression.

Answer 9

Comparison Conditions

Question 10

selects values within a given
range. The values can be
numbers, text, or dates.

Answer 10

The BETWEEN operator

Question 11

is also known as the
membership condition.

Answer 11

IN condition

Question 12

to test values in a specified
set of values

Answer 12

IN condition

Question 13

is used in a
WHERE clause to search for a
specified pattern in a column.

Answer 13

LIKE operator

Question 14

two wildcards often used
in conjunction with the LIKE
operator:

Answer 14

% and _

Question 15

represents
zero, one, or multiple characters

Answer 15

%

Question 16

represents a
single character

Answer 16

_

Question 17

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.

Answer 17

logical condition

Question 18

used to sort
the result-set in ascending or
descending order.

Answer 18

ORDER BY

Question 19

sorts the
records in ascending order by default.

Answer 19

ORDER BY

Question 20

used to add, delete or modify
columns in an existing table.

Answer 20

ALTER TABLE

Question 21

used to remove a
table definition and all the data,
indexes, triggers, constraints and
permission specifications for that
table.

Answer 21

DROP TABLE

Question 22

used to delete
complete data from an existing

Answer 22

TRUNCATE TABLE

Question 23

used to return only distinct

eliminates
duplicate records from the results.

Answer 23

SELECT DISTINCT/DISTINCT

Question 24

is a predefined
formula which takes one or more
arguments as input then process
the arguments and returns an
output.

Answer 24

function

Question 25

are simply sub-programs, which are commonly used and re-used throughout SQL database applications for processing or manipulating data.

Answer 25

SQL functions

Question 26

returns a single value, calculated from values in a column. AVG(), COUNT(), FIRST(), LAST(), MAX(), MIN(), SUM()

Answer 26

Aggregate Function

Question 27

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()

Answer 27

SQL Scalar Function (Non-Aggregate Function)

Question 28

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.

Answer 28

aggregate function

Question 29

function calculates the average of the values / returns the average value of a numeric column

Answer 29

AVG()

Question 30

returns the number of rows in a group or in matches a specified criterion.

Answer 30

COUNT()

Question 31

returns the minimum value/smallest value in a set of values/selected columns..

Answer 31

MIN()

Question 32

returns the maximum value/ largest value in a set of values/ selected columns.

Answer 32

MAX() function

Question 33

is used to group rows returned by SELECT statement into a set of summary rows or groups based on values of columns or expressions.

Answer 33

GROUP BY

Question 34

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.

Answer 34

HAVING clause

Question 35

used to filter rows

Answer 35

WHERE CLAUSE

Question 36

used to filter groups of rows.

Answer 36

HAVING CLAUSE

Question 37

A functions that perform operations on character values.

Answer 37

MySQL String Function

Question 38

Converts a string to upper-case

Answer 38

UCASE()

Question 39

extracts a substring from a string (starting at any position).

Answer 39

MID()V

Question 40

A functions that perform operations on numeric values.

Answer 40

MySQL Numeric Function

Question 41

rounds a number to a specified number of decimal places.

Answer 41

ROUND()

Question 42

A functions that perform operations on date values.

Answer 42

DATE FUNCTION

Question 43

DATE FUNCTIONS

Answer 43

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

Question 44

SQL Date Data Types

Answer 44

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

Question 45

A functions for handling null values. Accepts two arguments and returns the first if its not NULL

Answer 45

IFNULL

Question 46

combine the results of multiple sets into a single set are used to combine or subtract the records from two tables.

Answer 46

RELATIONAL SET OPERATORS

Question 47

The resulting records will also have same number of columns and same datatypes for the respective column.

Answer 47

UNION COMPATIBLITY.

Question 48

is used to combine the result-set of two or more SELECT statements. (only distinct values)

Answer 48

UNION operator

Question 49

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.

Answer 49

INTERSECT operator

Question 50

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.

Answer 50

MINUS operator

Question 51

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.

Answer 51

EXCEPT operator

Question 52

specifies how to relate tables in the query. is one of the set operations available in relational databases.

Answer 52

JOIN operator

Question 53

is used to combine rows from two or more tables, based on a related column between them.

Answer 53

JOIN clause

Question 54

returns records that have matching values in both tables.

Answer 54

INNER JOIN

Question 55

returns all rows from both the participating tables which satisfy the join condition along with rows which do not satisfy the join condition.

Answer 55

SQL OUTER JOIN

Question 56

is a query that contains the same or different join types, which are used more than once.

Answer 56

Multiple join

Question 57

Goes through conditions and return a value when the first condition is met

Answer 57

CASE

Question 58

Converts a value (of any type) into a specified datatype

Answer 58

CAST

Question 59

Returns the first non-null value in a list

Answer 59

COALESCE

Question 60

Converts a number from one numeric base system to another

Answer 60

CONV

Question 61

Converts a value into the specified datatype or character set

Answer 61

CONVERT

Question 62

Return a specified value if the expression is NULL, otherwise return the expression

Answer 62

IFNULL

Question 63

Returns 1 or 0 depending on whether an expression is NULL

Answer 63

ISNULL

Question 64

Returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table

Answer 64

LAST_INSERT_ID

Question 65

Returns the current date and time

Answer 65

CURRENT_TIMESTAMP

Question 66

Returns the current time

Answer 66

CURTIME

Question 67

Extracts the date part from a datetime expression

Answer 67

DATE

Question 68

Returns the number of days between two date values

Answer 68

DATEDIFF

Question 69

Returns a date from a numeric datevalue

Answer 69

FROM_DAYS

Question 70

Returns the current date and time

Answer 70

LOCALTIME LOCALTIMESTAMP

Question 71

Returns the difference between two time/datetime expressions

Answer 71

TIMEDIFF

Question 72

Returns the number of records returned by a select query

Answer 72

COUNT

Question 73

Returns e raised to the power of a specified number

Answer 73

EXP

Question 74

Returns the largest integer value that is <= to a number

Answer 74

FLOOR

Question 75

Returns the natural logarithm of a number

Answer 75

LN