OCA 11g SQL 1z0-051 Short Notes: Exam passing 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.

Pass OCA Certification DBA 11g Certification Exam in a first attempt.

• Download free Practice test and Study Notes for 1Zo-051- OCA: Oracle Database 11g-SQL Fundamentals-1
• Download free Practice test and Study Notes for 1Z0-052- OCA: Oracle Database 11g:Administration-1

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.

Pass OCA Certification DBA 11g Certification Exam in a first attempt.

• Download free Practice test and Study Notes for 1Zo-051- OCA: Oracle Database 11g-SQL Fundamentals-1
• Download free Practice test and Study Notes for 1Z0-052- OCA: Oracle Database 11g:Administration-1

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 dictionary view.
• 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.

Pass OCA Certification DBA 11g Certification Exam in a first attempt.

• Download free Practice test and Study Notes for 1Zo-051- OCA: Oracle Database 11g-SQL Fundamentals-1
• Download free Practice test and Study Notes for 1Z0-052- OCA: Oracle Database 11g:Administration-1