Short Notes: SQL Tips
List the capabilities of SQL SELECT statements
·
The numeric data type is represented as NUMBER
·
The WHERE clause in the SELECT
statement does not support a group function.
·
Single ampersand (&) and double ampersand
(&&) are the types of substitution variables.
·
Projection is a capability used to choose the columns
that a user wants to retrieve from a table by using a SELECT statement.
·
The concatenation operator (||) is used to
combine the column values.
·
The ALL operator is used in the WHERE clause and it
compares a value with any of the values in the list provided.
·
The column list can be omitted from the INSERT INTO
command, but the values must match the order of the columns as listed in the
output from the DESC(RIBE) command.
Apply conditional expressions in a SELECT statement
·
The CASE expression is preferred over DECODE().
·
IS NULL is a comparison operator that is used to
compare a value with a NULL value. It evaluates to TRUE if the value is NULL,
otherwise it evaluates to FALSE.
·
The AS keyword is used to define a column alias, and
double quotes are used when an alias consists of multiple words or its case is
important.
·
The result of any arithmetic operation between two
date values represents a certain number of days.
Limit and sort the rows that are retrieved by a query
·
In positional sorting, only those columns that are
specified in the SELECT list can be appended to the ORDER BY clause.
·
The NOT operator negates conditional operators.
·
Oracle performs an implicit data type conversion as
and when required.
·
Character literal data is always case sensitive and
must be enclosed within single quotes.
·
The syntax of using the DISTINCT clause is SELECT
DISTINCT columnname1, columnname2,.. FROM tablename;.
·
The BETWEEN operator is used to retrieve a range of
data between two values.
·
The ORDER BY clause is used to sort the data in either
ascending or descending order.
·
In Oracle, the character searches are case sensitive.
·
The ROWNUM pseudocolumn is used to avoid a massive
sort of large result sets.
·
The <> and != operators are the not equal
operators in Oracle.
Use ampersand substitution to restrict and sort output
at runtime
·
The ampersand substitution variable must be enclosed
in single quotes when dealing with the character and date values.
·
The SET DEFINE command is used to specify a variable
character other than an ampersand (&).
·
Substitution variables such as & and &&
are used to temporarily store values.
·
Notepad is used to edit an earlier issued SQL
statement in SQL*Plus.
Describe various types of functions available in SQL
·
The ROUND function is used to round numeric values in
a column or numbers to n decimal places right of the decimal point.
·
A forward slash, a semicolon, and RUN command are used
to execute a SQL statement or a PL/SQL block.
·
DUAL is a dummy table that returns exactly one row,
used especially in pseudo columns.
·
The soundex function is used to get a string
containing the phonetic representation of x.
·
The Translate() function is used to convert
occurrences of characters present in x from i sequence to j sequence.
·
The MOD and REMAINDER functions are the same, but both
use different functions in their formula.
·
The NVL function is used when a non-NULL
value needs to be returned in place of a NULL value.
Use character, number, and date functions in SELECT
statements
·
The CONCAT function must be nested to join three
string literals.
·
The concatenation operator (||) is capable of
performing a complex task in a simpler manner.
·
The SUBSTR function extracts a substring of
a specified length from the source string. The start position after the source
string specifies from where a substring is extracted.
·
If the end date occurs before the start date, a
negative number is returned in the MONTHS_BETWEEN function.
·
A negative value specifies that the date value to be
returned by the ADD_MONTHS function must be prior to the specified date.
·
Using the TRUNC function to truncate a date to the
year level will return the date to the beginning of the current year.
·
The TRUNC function is used to truncate a number to n
digits of precision.
·
The NLS_TERRITORY parameter is used to specify the
name of the territory. The date format is according to the name of the
specified territory.
·
The ROUND (number) function is used for rounding off
values in a column or numbers to n decimal places.
·
YEAR is a fmt that rounds up the date value on 1st
July.
Describe various types of conversion functions that
are available in SQL
·
Oracle returns an “ORA-1722: invalid number” error
while performing an implicit conversion for invalid character data representing
an invalid number.
·
Single arguments are not allowed in the NVL function.
Therefore, Oracle will generate an ORA error.
·
Oracle does not implicitly convert character literals
to DATE data.
·
The Translate() is used to convert numbers and
strings. It can also accept a column value as an argument.
·
The TO_CHAR function is used to convert data
types explicitly.
·
Oracle treats the number value given in single quotes
as a string value.
·
When the two date values are subtracted, it yields the
number of days between the two dates.
Describe group functions
·
Group functions can only be nested two levels deep.
·
Rows with null values occurring in group columns or
expressions are ignored.
·
No aggregate function can be used with the WHERE
clause.
Group data by using the GROUP BY clause
·
Group data by using the GROUP BY clause.
·
Some of the rules that decide the usage of group
functions are as follows:
1.
If an item that is not a group function appears in the
SELECT list and there is no GROUP BY clause, an “ORA-00937: not a single-group
group function” error is raised.
2.
If a GROUP BY clause is present, but the item present
in the GROUP BY clause is not a grouping attribute, an “ORA-00979: not a GROUP
BY expression” error is raised.
3.
If a group function is placed in a WHERE clause, an
“ORA-00934: group function is not allowed here” error is raised.
·
A single-row function can be nested within group
functions.
Include or exclude grouped rows by using the HAVING
clause
·
Any column or expression that is in the SELECT list is
not an aggregate function.
·
When any group function is used in a query, the WHERE
clause is not used to restrict the rows, rather the HAVING clause is
used.
·
MAX is an aggregate function that returns maximum
value in a group.
Write SELECT statements to access data from more than
one table using equijoins and nonequijoins
·
Equi join is also known as inner join or simple
join.
·
Inner join produces only the matching rows from
the joining tables.
Join a table to itself by using a self-join
·
The full outer join can also retrieve all
unmatched data from two tables.
View data that generally does not meet a join
condition by using outer joins
·
To join n tables, n-1 join conditions are required.
·
Some of the limitations of an outer join are given
below:
1.
The outer join operator can be placed only on one of
the joins.
2.
The outer join operator cannot be used with the IN
operator.
3.
The outer join operator cannot be used with any other
join operator that uses the OR operator.
·
Different table aliases for the same table is used to
perform a self join.
Generate a Cartesian product of all rows
from two or more tables
·
A Cartesian product is a set of all possible
combination of rows drawn from each table involved in the join.
Write single-row and multiple-row subqueries
·
Two classes of comparison conditions are used in
subqueries: single-row operators and multiple-row operators.
·
If an inner query returns a NULL, the subquery returns
no rows.
Describe set operators
·
(unary), – (unary), *, /, ||, =, !=, <, >,
<=, >=, IS NULL, LIKE, BETWEEN, IN, NOT, AND, OR is the correct order of
operators precedence.
Control transactions
·
The ROLLBACK command is used to end the
current transaction, and to discard any changes made since the beginning of the
transaction.
·
If a query returns one row, an implicit cursor is
automatically executed.
·
An existing column cannot be renamed.
·
ACID stands for Atomic, Consistent, Isolated, Durable.
·
The COMMIT and ROLLBACK commands are used to
terminate a transaction.
·
Two savepoints with a common name cannot exist.
·
Oracle does not allow to insert a row with a primary
key value that already exists in a table.
Categorize the main database objects
·
VALUE() can be used to retrieve rows from an object
table.
·
Primary key is used to enforce row integrity in a
table.
·
Data files are system files.
·
A sequence is used to generate the Primary key value
for a database.
·
The last_number column displays the next available
sequence number if NOCACHE is specified.
·
The USER_SEQUENCES data dictionary view is
used to verify the values of a sequence.
Review the table structure
·
Once a table alias is defined for a table, the columns
of the table can be qualified with the table alias only.
·
The DESC command is used to describe the schema of a
table.
·
The DESC(RIBE) command is used to describe an Oracle
table.
·
The DESC(RIBE) command is used to describe a table.
List the data types that are available for columns
·
User defined exception messages are written in the
declarative section of a Pl/SQL block.
·
String values and date values must be enclosed in
single quotes.
·
The VARCHAR2 data type trims the insignificant spaces
of a string value.
Explain how constraints are created at the time of
table creation
·
Columns can be defined either at the column or the
table level.
·
Oracle server generates the name of a constraint with
SYS_Cn format when the name has not been defined by a database user. The
constraints and all the related information are stored in the USER_CONSTRAINTS data
dictionaryview.
·
The USER_CONSTRAINTS data dictionary is used to store
all constraint-related information of any current user.
Create simple and complex views, private and public
synonyms, indexes, sequences
·
The CREATE TABLE does not immediately end with a
semicolon, and a column name cannot start with a numeric value. However, a
numeric value can be used in between a column name.
·
A tablename cannot start with the @ special character.
·
A view is a virtual table that helps to manage
permissions and other administrative tasks on a table.
·
Oracle Server retrieves the view definition from the
USER_VIEWS data dictionary view, checks all access privileges for the base
table, and also runs the view query on the base table.
·
The OR REPLACE clause is used to modify a view without
dropping, recreating, and regranting the object privileges on a view.
Retrieve data from views
·
The GRANT CREATE VIEW TO user_name; statement is used
to grant the CREATE VIEW privilege to a database user.
·
The CREATE VIEW and CREATE ANY VIEW system privileges
must be granted to create a view in user’s own schema.
No comments:
Post a Comment