Oracle SQL Developer Mastery: Practical Training & Projects

Explore database creation and table setup in Oracle SQL Developer, insert dummy data, and execute diverse queries, including where, joins, and functions like substring and upper.

Develop practical Oracle SQL skills by creating worker, bonus, and title tables with primary and foreign keys, inserting data, and executing aliasing, upper, distinct, and concat queries.

Master Oracle SQL queries by printing and sorting worker data, filtering with in and like, using between for salaries and joining dates, performing inner joins, aggregations, duplicates, and top-n results.

Master advanced SQL queries to find the fifth highest salary, first and last records, and department totals, using union, intersect, and regex-based numeric and character data separation.

Demonstrate creating and querying data in Oracle SQL using decode, mod, rownum, and dual; pivoting, case expressions, regex replace, and building binary tree like root, inner, and leaf hierarchies.

Explore practical Oracle SQL joins—from inner and left/right/full outer joins to cross joins and self joins; plus group by and roll up techniques for real-world data analysis.

Learn to use the having clause with group by to filter groups, compare it with the where clause, and apply unions, subqueries, and correlated subqueries for advanced data analysis.

Cover Oracle SQL developer project 2 with case studies on cube, roll up, pivot, unpivot, and insert, update, delete, merge statements, plus views, indexes, synonyms, sequences, triggers, and functions.

Explore cube and rollup operations in Oracle SQL to generate subtotals and grand totals. Create views and pivot queries to transform and summarize sales data across categories, customers, and status.

Learn to unpivot in Oracle by transforming product columns in sale_stats, using include nulls, and insert techniques such as insert into, insert into select, and insert all.

Learn to create and use views in Oracle, including updatable and inline views, with check options to restrict changes and enable inserts, updates, and deletes through views.

Explore creating and managing indexes to speed queries, work with sequences, triggers, synonyms, and views, and apply explain plan techniques to optimize Oracle SQL.

Explore how Oracle 9i supports relational and object-relational data models, enabling ER modeling and scalable internet applications across databases and platforms.

Explore data storage across media, from hard copies to databases, and learn relational databases, DBMS concepts, and ER modeling with entities, attributes, relationships, and cardinality.

Relate data across multiple tables using primary and foreign keys, understand relational database concepts, and learn sql statements, including dml, ddl, and dcl, within Oracle 9i.

Master SQL basics with the select statement, including projection, selection, and joining; learn aliases and arithmetic operators like plus, minus, asterisk, and hash for display-only calculated columns.

Learn operator precedence, null handling, and column aliases in Oracle SQL. Explore the concatenation operator and literals to format results, and apply distinct to eliminate duplicate rows.

Learn how SQL and iSQL*Plus interact with Oracle SQL Server, submit statements from a browser, and display table structure using the describe command.

Demonstrates practical use of select statements in Oracle SQL, covering selecting all or specific columns, aliases, arithmetic expressions, null handling, concatenation, distinct values, and describing employees.

Explore writing basic SQL select statements in Oracle. Learn select syntax, from employees, use aliases, distinct values, and concatenation, and describe tables such as departments and employees.

Learn how to restrict and sort data in Oracle SQL by using the where clause and comparison conditions, handling character strings, dates, and numbers correctly.

Explore comparison conditions in Oracle SQL, mastering between, in, like, and null checks to filter data with inclusive ranges, membership tests, wildcard patterns, and null handling.

Craft and apply logical conditions using and, or, and not within where clauses, master operator precedence with parentheses, and sort results with order by in ascending or descending order.

Explore practical Oracle SQL data restriction with where clause examples, including equals, like, between, in, null checks, and comparisons on the employees table, highlighting case sensitivity and operator usage.

Master oracle sql data restriction and sorting using and, or, not operators, not in, not between, and is not null. Apply precedence with parentheses and sort by multiple columns with aliases.

Master Oracle SQL select statements by practicing queries that fetch last names, salaries, and department IDs, applying between and not between, using in, aliasing columns, and ordering by dates.

Display the last name and job title of all employees without a manager. Filter by commission_pct is not null, then sort by salary and commission_pct in descending order.

Explore how Oracle sql single row functions manipulate data items, perform calculations, format dates and numbers, and convert data types, with a focus on character, number, date, and general functions.

Explore case and character manipulation in Oracle SQL, mastering lower, upper, initcap, and functions like concat, substr, len, instr, lpad, rpad, and trim for formatting and querying employee data.

Explore Oracle's number functions, including round and truncate, and the mod operator, then apply date functions such as sysdate, date arithmetic, and understanding date storage and formats via dual.

Master arithmetic with dates in oracle sql by computing tenure, using months_between and add_months, and applying round, truncate, next_day, and last_day to derive meaningful date results.

Explore Oracle SQL data type conversions, including implicit assignment conversions and explicit conversions with to_char, to_number, and to_date, using format models and NLS parameters to format dates and numbers.

Learn to_char, to_number, and to_date in Oracle SQL, formatting dates with models like dd month and rr/yy, converting numbers for concatenation, and applying the pH exact-match modifier.

Explore nesting and general functions in Oracle SQL, evaluating inner to outer expressions and using nvl, nvl2, null if, and coalesce to handle null values with practical examples.

Explore null handling with coalesce and other null-aware functions while applying case expressions and decode for conditional salary adjustments and tax calculations in Oracle SQL.

Explore Oracle SQL practicals on single row functions, covering case manipulation (lower, upper, init cap), concat, substr, length, instr, lpad, rpad, trim, and round, truncate, mode.

Explore practical Oracle SQL exercises on rounding, truncating, mode, and date arithmetic, including system date, months between, add months, next Friday, and last day calculations.

Explore conversion functions such as to_char and nvl, demonstrating date and number formatting, currency display, and handling nulls in salary and commission from employees.

Explore practical demonstrations of Oracle SQL functions—nvl, nvl2, null if, coalesce, case, and decode—to compute employee salaries and income from salary plus commission.

Master Oracle SQL through session three's additional examples, including current date and salary calculations. Explore calculations like 15% salary increases, new salary, months between hire date, capitalization, and dream salaries.

Explore sql with practical salary queries: format salaries with lpad, compute next monday review dates, display days of week, and grade job ids using decode and case.

Learn how to retrieve data from multiple tables using equijoins and related joins, including cartesian products, self joins, and outer joins, with join conditions in the where clause.

Learn how to qualify ambiguous column names with table prefixes or aliases, and join multiple tables using non-equi, outer, and self joins, including performance considerations.

Master SQL 1999 joins by using cross, natural, and outer joins, plus using and on clauses, including self joins, to retrieve data from employees, departments, and locations.

Master three-way joins using on clauses, equijoins, and using, while understanding inner and outer joins, including left, right, and full outer joins, with practical demonstrations and aliases.

Master practical Oracle SQL joins from Cartesian products to equijoins and table aliases, then extend to multi-table, outer, non-equity, and self-joins.

Master how to display data from multiple tables using SQL 1999 joins, including cross join, natural/equi joins, using and on clauses, and multi-table queries.

Explore displaying data from multiple tables using inner joins with the using clause and all outer joins, left, right, and full, plus filtering such as manager id 149.

Practice Oracle SQL group functions across rows, handle nulls with NVL, and apply where before grouping; compute max, min, sum, and average salaries by job and department.

Craft Oracle SQL queries using decode to count total employees and hires by year 1995 through 1998, then build a pivot-like matrix summing salaries by job and department.

Master Oracle group functions to aggregate data with group by and having. Explore avg, sum, max, min, count, stddev, and variance, including distinct, all, and ignore null values.

Explore min and max functions across all data types with date and string examples. Master count, distinct, and group by, and use nvl to include nulls in averages.

Explore grouping data with the group by clause, apply group functions such as sum and average, and use where and order by to shape department and job title groups.

Explore group functions with group by and having to compute department salaries. Avoid where restrictions and filter by max or average salary using the having clause.

Learn to aggregate data in Oracle SQL using group functions such as avg, sum, min, max, and count, with group by, having, and order by, while handling nulls via NVL.

Explore how group functions aggregate across rows, handle nulls with nvl, and compute max, min, sum, and average salaries by job and department, with counts and two-decimal rounding.

Demonstrate aggregating data with group functions by counting employees and hires in 1995–1998 using decode for year columns and a pivot of salaries by departments 20, 50, 80, and 90.

Learn to write single row and multiple row subqueries and see how inner queries supply values to outer queries, with usage in where, having, and from clauses.

Master how subqueries work in the having clause of Oracle SQL, handling single and multiple row results with in, any, all, and not in, including null considerations.

Explore practical subqueries in Oracle SQL, including single-row and multiple-subquery examples, comparisons with outer queries, and using aggregate functions, having, and group by to analyze salaries and departments.

Explore Oracle SQL subqueries with practical examples that distinguish single-row versus multiple-row results, and demonstrate using any, all, in, not in, and null handling to avoid common errors.

Explore Oracle SQL subqueries through practical session six examples, including listing employees in the same department while excluding a specific last name and sorting those earning above average salary.

Master subqueries in Oracle SQL with practical examples across employees, departments, managers, location id 1700, executives, and salary comparisons using the average salary.

Learn to use sqlplus substitution variables to create dynamic, readable queries and reports by using ampersand and double ampersand variables, define commands, and script files that modify where clauses.

Learn to use sqlplus verify to preview changes, manage session with set and show commands, format reports with column and break, and substitution variables for t title and b title.

Explore Oracle SQL substitution variables, define and undefine values, and use double ampersand to reuse inputs while formatting reports and tables.

Learn to produce readable Oracle SQL output with iSQL+ by using define variables, date conversions, string concatenation, and labeled result sets across employees, departments, and locations.

Mastering Oracle SQL session covers inserting, updating, deleting, and merging rows, plus transaction control with commit, savepoint, and rollback, and data manipulation language (DML) syntax.

Master update, delete, and merge techniques in Oracle SQL, including updating with where clauses, using subqueries, copying rows with insert into table select, and enforcing integrity constraints.

Learn how Oracle transactions ensure data consistency through dml-backed units, with commit, rollback, savepoints, and handling user process failures and system failures.

Learn to manage Oracle SQL transactions using commit and rollback, ensure read consistency, understand implicit and explicit locking, and the effects of DDL and DML.

Learn Oracle SQL data manipulation by inserting into departments and employees, using implicit versus explicit column lists, handling nulls, and applying proper date formats and quotes.

Practice manipulating data with Oracle SQL: use substitution variables for inserts, fix quotes, view results, copy rows from employees, and create and populate a sales_representatives table with appropriate data types.

Manipulate data with update, subquery-based multi-column updates, and inserts, including with check option, while enforcing foreign key constraints and exploring transactions, commit, and rollback in Oracle SQL.

Create and describe the my_employee table, insert multiple rows, and generate a lowercase user_id by concatenating the first name initial with the first seven of the last name using substr.

Practice manipulating data in Oracle SQL by updating records, committing transactions, deleting rows, inserting new data, creating save points, and rolling back to points before persistent changes.

Create, alter, drop, and rename tables; describe data types and column definitions; explore database objects like views, sequences, and synonyms; understand naming rules and the create table syntax in Oracle.

Examine tables in the Oracle database and the data dictionary, including user_, all_, dba_, and v$ views, and review time span and time zone data types.

Explore how to modify and manage Oracle tables, including adding, altering, and dropping columns, creating tables from subqueries, and using describe commands to verify structure.

Learn to use the set unused option to mark columns as unused for faster access, and review Oracle ddl commands—drop, truncate, rename, delete, and adding comments to the data dictionary.

Create and describe Oracle tables with numeric and varchar2 columns, then explore data dictionary views such as user_tables and user_objects. Practice time span with local time zone and interval types.

Create and manage Oracle SQL tables with practical examples: subquery-based table creation, describe, alter table operations (add, modify, drop), rename, truncate, and comments.

Create and verify dept and emp tables in Oracle SQL, insert 13 rows from departments, extend last_name to 50, verify with user tables, create employees two from employees, drop emp.

Rename employees to emp, describe the emp table, add and verify comments, then drop first_underscore_name and set department_underscore_id unused before dropping all unused columns.

Learn to enforce data integrity in Oracle SQL by using constraints like not null, unique, primary key, foreign key, and check, defined at column or table level.

Explore the Oracle unique constraint, its behavior with nulls and composite keys, and how it contrasts with primary, foreign, and check constraints to enforce data integrity.

Learn to use alter table to add, drop, disable, enable, or cascade constraints, including not null, and to view constraints via user_constraints and user_cons_columns.

See how Oracle enforces data integrity with not null, unique, primary key, foreign key, and check constraints, and implement them via DDL; query user_constraints to view definitions and names.

Explore Oracle constraints by creating tables with not null, primary key, unique, and foreign key constraints. View constraint details with user_constraints and user_cons_columns, and apply a salary > 0 check.

Add primary key constraints on emp(id) and dept(id) named at creation, create a dept_id foreign key in emp referencing dept(id), and enforce commission with a check constraint greater than zero.

Create, alter, and drop views; insert, update, and delete data through a view. Use inline views and top-n analysis, and query the data dictionary for view definitions.

Create complex views to summarize department salaries and enforce data integrity with check option and read only, then use inline views and top n analysis to rank employees.

Explore how a view acts as a window to table data, stored in the data dictionary and visible via user_views, with simple or complex options, read-only capabilities, and creation demonstrations.

Learn to create and manage Oracle views with practical demonstrations: simple and inline views, aliasing, create or replace, check option and read only constraints, and top-n analysis.

Practice creating and querying views in Oracle SQL with hands-on examples, including employee_view, dept 50, and salary_view built from employees, departments, and job_grades.

Learn to create and maintain sequences, indexes, and synonyms, use sequences for unique primary keys, and verify settings via the data dictionary.

Explore how nextval and currval pseudocolumns generate and reference sequence numbers, including qualifying with sequence names, in inserts and updates, with caching and alter sequence options.

Learn how Oracle indexes speed data retrieval through unique, non unique, and function based indexes, when to create or drop them, and how synonyms simplify object access.

Explore practical demonstrations of Oracle SQL: create sequences and indexes, view data dictionary, use nextval and currentval, alter and drop sequences, and create synonyms to access views.

Create a dept_id_sequence starting at 200 with max 1000 and increment 10. Insert two depts using nextval and add a non-unique index on emp.dept_id, and finally review emp indexes.

Learn how to control database access in Oracle by creating users and roles, granting and revoking system and object privileges, and managing synonyms and database links.

Grant object privileges with grant option to users or roles, then revoke and view privileges via data dictionary views; use database links and synonyms to access remote objects.

Learn to create users with default passwords, create roles, grant and revoke privileges, grant a role to users, and use grant option in Oracle SQL.

In this practice, two teams grant and revoke select on departments, insert rows with commits, create synonyms, and query the tables while exploring user_tables and all_tables for ownership and access.

Create Oracle SQL tables with proper data types and integrity constraints, defining member, title, and copy tables with primary keys, not null, unique constraints, foreign keys, checks, and default sysdate.

Create title_copy, rental, and reservation tables with composite primary keys, foreign keys to member and title, and a status check for available, destroyed, rented, or reserved.

Verify tables and constraints via the data dictionary for member, title, title_copy, rental, and reservation. Create sequences starting at 101 and 92, then insert titles using next values with dates.

Practice Oracle SQL by scripting inserts into member table using a sequence for ids and a join date, then commit; also populate title_copy table with copy IDs and availability statuses.

Instructs on inserting titles and rentals with explicit statements, then creates a view title_avail to show titles, copies, status, and expected return dates via joins.

Practice Oracle SQL data manipulation with inserts for titles, copies, reservations, and rentals; update statuses; query title_avail; and add and populate a price column via a scripted update.

Execute eight sql workshop exercises, including creating and altering tables with a not null price constraint, and generating a customer history report of rental dates and durations.

Master set operators in Oracle sql to form compound queries with union, union all, intersect, and minus, and learn how to control row ordering and handle duplicates.

Explore Oracle SQL set operators, including union, union all, intersect, and minus; ensure select lists match in number and data type, and apply order by at the end.

Learn how union, union all, intersect, and minus operators combine queries, manage duplicates and sorting, require order by at the end, and ensure matching select expressions with session 15 examples.

Explore practical Oracle SQL set operators, including union, union all, intersect, and minus, with where clause examples and matching select statements.

Master Oracle SQL set operators minus, intersect, union. List departments not containing street clerk, countries with no departments, jobs 10, 50, 20, employees with history, and null handling.

Explore Oracle 9i date time functions, including tz_offset, current_date, current_timestamp, and localtimestamp, and learn to work with time zones, UTC, and fractional seconds.

Manage database and session time zones, and use extract and time zone conversion functions such as from_tz, two_time_span, and two_time_interval to work with date time values.

Explore timestamp data types, time zone concepts, and extract functions, and see practical demonstrations of using these date time functions with Oracle nine tables.

Learn tz_offset and time zone queries, list v$time_zone_names, alter session time zone, compare db time zone with current dates, and extract year and month from dates.

Explore Oracle9i datetime functions with practical examples of from_tz conversions, two_time_span and two_time_span_ts formatting, and two_time_interval arithmetic on sample tables.

Master Oracle9i datetime functions through additional practice, altering sessions to set date formats and time zones, querying tz_offset for multiple zones, and extracting years from employee dates in department 80.

Master Oracle SQL grouping techniques with rollup, cube, and grouping sets to generate subtotals, cross-tabulations, and single-result sets using group by, having, and aggregate functions.

Explore oracle sql grouping functions, cube and rollup, grouping sets, and composite and concatenated groupings to generate multi-dimensional subtotals and totals.

Explore roll up and cube as extensions to group by, generating subtotals, grand totals, and cross-tabulations, while using grouping functions and grouping sets for composite and concatenated groupings.

Master advanced group functions in Oracle SQL, including avg, std dev, count, and max, with group by department and job IDs; learn having, roll up, and cube for aggregates.

Explore advanced grouping techniques in Oracle SQL, including rollup, grouping sets, cube, and composite groups, with practical demonstrations of department_id, job_id, and manager_id.

Master advanced aggregation in oracle sql using rollup, cube, and grouping sets to compute total salaries by manager and job, including cross-tab and overall totals.

Master advanced oracle sql subqueries, including multiple-column subqueries in the from clause and correlated subqueries with the with clause. Use exist and not exist, and perform updates with correlated subqueries.

Learn how to perform column comparisons in multi-column subqueries, including pairwise and non pairwise cases, and apply scalar, correlated, and inline-view subqueries and case expressions.

Explore correlated subqueries in Oracle, where the inner query depends on each outer row and is evaluated per candidate, contrasted with nested subqueries, using exists, not exists, and not in.

Explore correlated update and correlated delete to modify or remove data across tables using correlated subqueries. Learn how the with clause enables query block reuse and performance optimization.

Master session 18 by applying multiple column queries, pairwise and non-pairwise subquery comparisons, and scalar subqueries in conditions, plus decode and case and practical demonstrations.

Master advanced subqueries in Oracle SQL with practical examples, covering normal and multi-column subqueries, pairwise and non-pairwise cases, subqueries in from clauses, and scalar subqueries in case and order by.

Explore advanced correlated subqueries with practical examples, including using exist, not exist, and in operators, along with correlated updates and deletes, and a width clause to compute department totals.

Practice advanced subqueries in Oracle SQL by querying employees across departments and locations, handling null commissions with nvl, and comparing salaries to department and job-level benchmarks, including average salaries.

Compare not exists and not in for non supervisors, note null handling, then use exists and scalar subqueries to test salaries against average in each department using a with clause.

Discover how to use Oracle hierarchical queries to create tree-structured reports by tree walking from root to leaves, using start with and connect by prior.

Master hierarchical queries in Oracle SQL by walking the tree bottom up or top down, using start with, connect by prior, and the level pseudo column.

Master hierarchical queries in Oracle SQL to retrieve data from natural hierarchies using the level pseudo column, connect by prior, and start with, with practical demonstrations and examples.

Mastering Oracle sql hierarchical queries demonstrates bottom-up and top-down traversal, using start with, connect by prior, level and lpad to format org chart hierarchies and burning branches to filter results.

Mastering Oracle SQL demonstrates hierarchical retrieval by building organization charts and manager hierarchies using start with and connect by prior, displaying last name, salary, and department id.

Review the update statement in Oracle SQL and how it modifies rows. Learn multi-table insert types: unconditional, conditional all, conditional first, and pivoting; external tables for ETL.

Explore unconditional insert all and conditional insert all in Oracle SQL, inserting employee data into sal_history and mgr_history from employees where employee_id > 200.

Learn pivoting inserts to convert non-relational sales source data into the relational sales info table, and use Oracle external tables to query external data.

Create an external table from a flat file by first creating a directory object, then defining the table with Oracle loader, using newline-delimited records and comma-delimited fields.

Explore multi-table insert variants, external tables, and naming the primary key index in create table to query external data without loading it.