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.

Learn to download Oracle database 12c release 2 for Windows 64-bit, choosing standard or enterprise editions, and note licensing and the free 11g express edition.

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.

Unlocking the HR account in ORCLPDB by connecting as SYS, querying v$pdbs, moving from CDB$ROOT to ORCLPDB, unlocking HR with identified by HR, and configuring tnsnames.ora for HR access.

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.

Explore how to apply and, or, and not in Oracle SQL's where clause, master operator precedence with parentheses, and filter employees by salary, department, and null values.

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.

Explore substitution variables in Oracle SQL, using & and && to prompt for values in where conditions, order by, column expressions, and entire select statements.

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.

Master character functions in Oracle database: upper, lower, and initcap convert case and format names. Use them in select, where, and order by with real examples on first_name.

Master three character functions in Oracle SQL: concat, substr, and length, and learn how concat differs from ||, how substr uses start and length (including negative indices), length counts characters.

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 nesting of single-row functions in Oracle SQL, evaluating from deepest level to outermost, using upper, substr, lpad, and instr to split name into first, middle, and last segments.

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.

Learn how to_date converts a character to a date by supplying a valid format, such as dd-mm-yyyy or dd.mm.yyyy, and understand rr vs yy with a recommendation to use yyyy.

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 group by and having clauses in oracle database sql certified associate 1z0-071, using aggregate functions to summarize salaries by department or job, enforce grouping rules, and filter groups.

Learn how joins combine employees, departments, locations, countries, regions, jobs, and job history using foreign keys and primary keys, as shown in the HR ERD.

Explore how the cartesian product forms when a join condition is omitted, joining all employees with all departments; avoid it by using a valid join condition in a where clause.

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.

Learn self joins by splitting the employees table into worker and manager, then use outer joins across employees, departments, locations, and countries to show complete employee details.

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.