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.

Discover the hr schema by exploring tables like employees, understanding data types (number, varchar2, date), and using dictionary views to map foreign keys to departments and locations.

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 column aliasing, including as, without as, and quoted aliases, learn concatenation to build full name, retrieve distinct values, and use describe (desc) to inspect table structures in Oracle SQL.

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.

Learn how to use is null and not operators, handle null values, and apply not equal logic in SQL queries to filter commission data and other records.

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.

Learn to use the order by clause to sort query results in Oracle databases, including ascending defaults, descending orders, null handling, and sorting by aliases and expressions.

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.

Explore how define creates a session-long variable, how the single ampersand prompts for input, and how undefine removes the variable, with examples.

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.

Use the double ampersand to define and reuse variables in sqlplus, toggle substitution with set verify and set define, and undefine variables when finished, or set define off for migrations.

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 lpad and rpad to pad values to a fixed width, and use replace and trim for string manipulation. Explore the dual table for testing functions and trim variations.

Explore number functions in Oracle SQL, including round, trunc, and mod, to manage decimals. See how rounding, truncation, and mod compute remainders and determine odd or even.

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.

Learn to use Oracle date functions such as months_between, add_months, next_day, and last_day to compute tenure and six-month review dates, adjust dates, and identify key dates like the next Friday.

Learn how round and trunc date functions round dates to the nearest month or year, and truncate dates to the month or year with hire_date examples.

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 implicit and explicit data type conversions in Oracle, and learn how to use conversion functions to_date, to_char, and to_number, plus why explicit conversion improves reliability.

Learn how to convert dates to text using to_char, apply format models (YYYY, MON, DD, HH24, AM/PM), and use fm to suppress blanks for clean date strings.

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.

Explore conditional expressions using the case function and decode in Oracle SQL, aligning with ANSI standards, and compare two case statement methods with when, then, else, end.

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.

Explore how group functions compute one result per group on a table, covering min, max, sum, average, and count, including count distinct and count star, while ignoring null values.

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 to display data from multiple tables by mastering joins, understanding primary and foreign keys, ERD structures, and the choice between old and 1999 Oracle join syntax.

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.

Master outer joins in oracle sql with emp and dept tables, showing how null values are preserved and when to place the join on dept side or employee side.

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.

Master Oracle 1999 syntax for joins by replacing comma and where with join and on, using table prefixes, and applying multi-table, non-equal, and self joins.

Master left, right, and full outer joins using employees and departments. Understand main table roles, join conditions, and converting to modern syntax.