Oracle Database SQL Certified Associate 1Z0-071

Learn sql without installation using Oracle Live SQL cloud; create an account, sign in, and practice with the hr schema in the sql worksheet.

Install Oracle database 12c on 64-bit windows, selecting desktop class and enterprise edition. Configure ORCL container with ORCLPDB and non-C drive paths.

Connect to an oracle database with sqlplus or sql developer using sys as sysdba, then use tnsnames.ora to access the ORCL service and a pluggable database.

Unlock the HR account in the pluggable database orclpdb by switching from the container database ORCL and update tnsnames.ora for ORCLPDB.

Learn to troubleshoot ora-01033 by opening pluggable databases after restart using sqlplus and sysdba and manage hr and sys connections for essential admin tasks in Oracle 12c.

Explore Oracle SQL select statements in the HR schema, using * to fetch all columns, selecting specific columns, applying column aliases, and adding comments with -- and /* */.

Explore arithmetic expressions in Oracle SQL, including plus, minus, multiply, and divide. Understand how null values affect calculations and how aliases display computed results.

Master the select statement for projection and selection, including from and select clauses, and apply proper formatting, indents, and semicolon rules in SQL Developer and SQLPlus.

Master how the where clause limits rows and apply comparison operators such as =, >, <, between, in, like, and is null, with rules for character and date values.

Explore the between, in, and like operators in Oracle SQL, using the where clause to filter salaries and names, with escape characters for underscores.

Master the fetch clause to limit query results, using fetch first n rows, fetch first percent, and offset with fetch, with ties for top salaries in the employees table.

Learn how to define variables with the accept command in Oracle SQL, customize prompts, and undefine variables, then run scripts to filter employees by department id or employee range.

Learn about Oracle single row functions, how they act on each row to transform data with built-in character, number, date, and conversion functions, with examples such as upper, lower, to_date.

Learn how the Oracle instr function returns the numeric position of a substring, with optional start position m and occurrence n, demonstrated using FIRST_NAME and the name Nanette.

Master oracle date functions and arithmetic, including sysdate, adding or subtracting days and hours, and computing days or weeks between dates, while understanding the dd-MON-rr default format.

Explore how to_char formats numbers into characters using format models to insert thousand separators, currency signs, and decimals, with examples of rounding and negative representations.

Explore NVL, NVL2, NULLIF, and COALESCE in Oracle SQL. Learn to handle null values, apply default values and datatype rules, use to_char for results, and compare nested NVL with COALESCE.

Master the decode function in Oracle SQL, a simple alternative to case for static conditions, with a clear syntax, default values, and salary by job id examples.

Learn how to use Oracle group functions - sum, average, max, min, and count - with real examples on salaries, names, and dates, including handling nulls and distinct values.

Master equijoin as a core old join, exploring inner, outer, non-equal, and self joins, and learn to join employees and departments via department_id with aliases.

Explore non-equal joins, using salary ranges to map employees to job grades via between. Note its infrequent use and potential duplicates, and learn subqueries as an alternative.

Explore the 1999 join syntax, including cross join, natural join, join using, join on, and outer joins. Compare it to the old syntax and practice Cartesian products.

Explore the natural join, which matches columns with the same name and data type across tables like departments and locations, and contrast it with equijoin and post-join where clauses.

Explore the 1999 syntax using clause to perform joins on shared columns, compare it with natural joins, and rewrite queries using department_id between employees and departments.