Exam 1Z0-051

Cheatsheet for Exam 1Z0-051 Oracle Database 11g: SQL Fundamentals

  1.  Data  must be enclosed in single quotes and is case sensitive
  2. Columns name, table names  are not case sensitive unless enclosed in double quotes
  3. Null means unknown and  is not equal to zeros or blanks
  4. Null values are compared using  IS NULL operator or IS NOT NULL operator
  5. DISTINCT must be the 1st clause after SELECT (SELECT DISTINCT)
  6. The ORDER  BY clause  must be the  last clause
  7. By default Nulls are sorted last
  8. You can  change the sort order of nulls  by specifying NULLS FIRS T or NULLS LAST
  9. The default sort order is in ascending sequence
  10. Sorts are case sensitive in character sort.
  11. RIGHT OUTER JOIN returns both matching and non-matching data from the right table in the join.
  12. LEFT OUTER JOIN returns both matching and non-matching data from the left table in the join.
  13. FULL OUTER JOIN returns both matching and non-matching data from both tables in join.
  14. The ON clause for joins can be used for joining table columns with different name but compatible data types
  15. Additional  join conditions can be specified with WHERE clause  when tables are joined with ON or USING clause or Natural joins
  16. NATURAL JOINS cannot have qualifier (table alias or table name). Example:  SELECT table1.col1, col2, col3 is wrong.
  17. Number of data values in the VALUES clause of INSERT command should match the number of columns.  Example INSERT INTO table1(col1, col2) values (col1,col2);
  18. VALUES clause cannot be specified for INSERT command if insert is based on output of SELECT. Example: INSERT INTO  table1 SELECT * FROM table2
  19. You cannot mix single row results and group functions in SELECT statement unless single rows  are grouped with group function
  20. WHERE Clause is use to eliminate rows; HAVING clause is used eliminate groups. Both WHERE and HAVING can be part of same select.
  21. Transaction ends with   COMMIT, ROLLBACK or DDL or DCL or normal exit
  22. INTERSECT : Returns common values from 2  or  more s SELECTS
  23.  UNION: Returns all Distinct values from 2 or more SELECTS
  24. UNION ALL: Returns all values including duplicates from 2 or more SELECTS
  25. MINUS: Returns rows from  1st SELECT that are not in 2nd SELECT
  26. The number of columns and data types must be identical for INTERSECT, UNION , UNION ALL and MINUS
  27. By default, All set operators expect for UNION ALL (for INTERSECT, UNION and MINUS) return data in sorted order of 1st column of the 1st SELECT statement.
  28. Oracle internally stores date in numeric format.
  29. Date1-Date2 returns  numeric value in days
  30. Date1+ n returns date (where n is numeric  value)
  31. Only one LONG or LONG RAW data type supported per table
  32. LONG or LONG RAW cannot be used in ORDER BY clause.
  33. Starting value of sequence cannot be altered. The only option is to drop and recreate the sequence if you want to change the starting value of sequence.
  34. All savepoints defined are lost when transaction ends. Using rollback to savepoint after transaction results in error.
  35. <ANY is less than maximum ;  >ANY is more than minimum; =ANY is same as IN
  36. <ALL is less than  minimum; >ALL  is more than maximum;
  37. COALESCE returns 1st non-null value in expression and take multiple values as argument. All arguments must be of same data type
  38. Indexes are automatically created on unique and primary keys only
  39. Primary key cannot contain null values whereas unique key can contain null values
  40. A table can have only one primary key.
  41. A table can have more than one unique key
  42. Any constraint can be added to table with existing data provided the existing data satisfies the constraint to be added. Example: Primary key can be added to table with existing data if there are no duplicates and NULL values.
  43. Group function COUNT(*) returns the count without ignoring duplicates and null values.
  44. Group function COUNT(DISTINCT col1) returns the count  ignoring duplicates and null values.
  45. DELETE statement can be rolled back whereas TRUNCATE cannot be rolled back.  TRUNCATE is DDL command and therefore implicitly committed
  46. DELETE statement deletes all the rows in the table ONLY if the WHERE condition is NOT specified. Otherwise rows are deleted based on WHERE clause.
  47. TRUNCATE always deletes all the rows in the table keeping the structure of the table intact.
  48. Multicolumn or expressions can be compared between main query and subquery. Example:  SELECT col1, col2, col3, col4  FROM tab1 WHERE (col3,col4)  IN  (SELECT col3, col4  FROM tab2)
  49. Sub queries can contain GROUP BY clause , GROUP functions and ORDER BY clause
  50. SELECT  NVL2(NULLIF(sal, comm), 0, 10000)  as TOTAL_SALARY  from emp will return total salary of 10000 if  sal =comm or if sal is null.
  51. INTERVAL YEAR TO MONTH stores period of time in years and months.  Example : 5-1 is  5 years and 1 month
  52. INTERVAL DAY TO SECONDS stores period of time in days, hours, minutes and seconds.    Example: 6:02:30:00 is 6 days 2 hours  30 minutes and 00 seconds
  53. Data stored in BFILE data type is external (OS file) to database whereas data stored in BLOB is internal or stored within the database.
  54. Null values can be retrieved with IS NULL or IS NOT NULL operator. You cannot use =NULL in WHERE clause. Example: SELECT * FROM tab1 WHERE col1=NULL is wrong.
  55. Col1=NULL can be used as target. Example: update employees set commission_pct=NULL
  56. NULL values are ignored when check is made for duplicates.
  57. Arithmetic expressions containing NULL value evaluate to NULL. This can be mitigated with appropriate functions like NVL, NVL2 etc.
  58. CHECK constraint  cannot refer to  SYSDATE, UID, USERENV & USER functions
  59. CHECK constraint  cannot refer to  CURRVAL, NEXTVAL, LEVEL &ROWNUM pseudo columns
  60. You cannot delete a row from view if it contains DISTINCT clause, GROUP BY clause, GROUP functions and ROWNUM.
  61. View cannot be modified if it is defined with  DISTINCT clause, GROUP BY clause, GROUP functions ,  ROWNUM & columns defined by expressions
  62.  Data cannot be inserted into  view if it is defined with  DISTINCT clause, GROUP BY clause, GROUP functions ,  ROWNUM ,  columns defined by expressions & NOT NULL columns are not part of view
  63. CREATE TABLE table1 AS SELECT * from table2 will create table1 which will have same table structure and data as table2. None of constraints other than NOT NULL are copied to new table1. Data will be copied to be new table based on condition specified in WHERE clause.
  64. Public and private synonyms can have same name for same table.
  65. Sub queries can be used in WHERE clause , HAVING clause, FROM clause,  SELECT list,
  66. When a table is dropped, all data, indexes, constraints on the table are also dropped. Views and synonyms dependent on the table are not dropped, instead they become invalid.
  67. When a table is dropped with pending transaction, all data is committed before dropping the table.
  68. Sort is done with ORDER BY clause and must be the last clause of given SELECT statement.
  69. For SELECTs with Sub queries, each subquery can have its own ORDER BY clause.
  70. Only one ORDER BY clause is supported for SELECT statements with UNION, UNION ALL MINUS &INTERSECT clause.
  71. ORDER BY supports ordering of column by column name, column position, column expression & coumn alias. When used with set operators (UNION, UNION ALL MINUS &INTERSECT), Oracle only recognizes column name or column alias or column expression of the 1st SELECT statement. Sorting using column names from 2nd or later  SELECTS will return in error.
  72. NVL2 evaluates   expression1. Expression2 is returned if expression1 is NOT NULL. Expression3 is returned expression1 is NULL.SYNTAX: NVL2(expression1 ,expression2, expression3)
  73. NULLIF compares expression1 and expression2.  NULL is returned if expression1 and expression2 are equal. Expression1 is returned if expression1 and expression 2 are not equal. SYNTAX: NULLIF(expression1, expression2)
  74. COALESCE returns 1st NOT NULL expression in the list.SYNTAX:  COALESCE (expression1, expression2, expression3, …..)
  75. All group functions expect COUNT(*)  ignore NULL values. Examples of group functions are AVG, MAX , MIN, STDDEV, SUM, VARIANCE .COUNT(expr) ignores NULL values whereas COUNT(*) does not. Use NVL to force group functions to include NULL values. Example: SUM(NVL(sal,0))
  76. By default all group functions does not ignore duplicate values. You can ignore duplicate values with DISTINCT clause. Example: AVG(SALARY)  vs. AVG(DISTINCT sal)
  77. With Natural Join , tables are joined by matching values of  one or more columns with same column names. An error is returned if columns names are same but data types are different
  78. Qualifiers or table alias cannot be used with Natural Joins. Example: table1.col1 is not valid
  79. Additional restrictions for natural joins are implemented with WHERE clause.
  80. USING clause cannot be used with Natural Join
  81. Qualifiers or table alias cannot be used in the SELECT list with USING clause for joins. Example: SELECT   table1.col1 is not valid. This restriction is also applicable for condition used in USING clause. Example: USING (table1.col1) is incorrect
  82. ON clause for joins is used to specify join condition. Additional condition can be specified as part of ON clause or with WHERE clause.
  83. RR  date format  calculate the century format of the date
  84. Single row functions return a single result for every row in the table that satisfies the given condition.
  85. AVG(date1) will fail where as AVG(date1-date2) will succeed because  2nd  date expression returns numeric value unlike 1st  date expression.
  86. DBA permissions are required to create PUBLIC synonym.
  87. NULL values can be inserted into table by omitting the column from column list or specify NULL for column value in VALUES clause.
  88. NOT NULL constraints must be defined at column level. All other constraints can be defined at table level or column level.
  89. A foreign key must match existing value in  parent table or can be NULL
  90. ON DELETE CASCADE deletes dependent child data when parent is deleted.
  91. ON DELETE SET NULL sets dependent child data to N ULL when parent is deleted.
  92. Views create with “WITH CHECK OPTION” specifies that INSERTS or UPDATES performed via the view cannot create rows that view cannot select.DELETES via view are not impacted as DELETES do not add rows.
  93. A sequence can be shared by multiple tables
  94. Sequence should not  be created with CYCLE option if they  are used for primary key values
  95. Sequence number is lost if the transaction is rolled back.
  96. All sequences stored in the cache are lost if the database /system crashes.
  97. A  public and private synonym with same name  can exist for the same table
  98. Use format specifier  spth to display date as  spelled-out ordinal numbers.
  99. A parent table having children dependent on it cannot be truncated w/o disabling referential integrity constraints.In other words , TRUNCATE command will fail if referential integrity constraints are not disabled
  100.  A transaction is logical collection of DML statements. If one the DML statements fails, only that statement is rolled back and not the entire transaction.
  101. Oracle DB issues implicit commit before and after any DDL even if the DDL statement fails.
  102. “FOR UPDATE” clause is used to hold lock on rows returned by SELECT statement for future update. This lock is released only when the transaction is committed or rolled back.
  103. No constraints can be defined on LONG or LONG RAW column
  104. A Cartesian join is formed because of missing join condition or invalid join condition.
  105. Minimum of n-1 join valid join conditions are required to avoid Cartesian join where n is number of tables to be joined.
  106. When <, > or = operator is used for comparing the results of subquery, the query will succeed as long as the subquery returns one and only row.Generally subquery with  group functions returns single row but this is not always the case
    • Example: SELECT MAX(sal) FROM EMP returns one row but SELECT MAX(sal) FROM EMP GROUP BY  deptno returns multiple rows.
  107. If multiple rows are returned by subquery, then use ANY, ALL, SOME or IN operator

 

%d bloggers like this: