ORACLE Database Management System

Explore aliasing, arithmetic expressions, and concatenation to shape query results; apply distinct, where, between, in, and like filters to retrieve precise data from employees and departments in an Oracle database.

Explore oracle character functions such as concat, superstring, length, replace, and trim; learn numeric functions like round and truncate, and how to handle nulls and aliases in statements.

Explore numeric and group functions in Oracle database management, including average, max, min, sum, and count, with null handling, distinct values, and group by clauses for data aggregation.

Learn to apply the group by clause with aggregate functions, handle null values using nvl, and use having to filter grouped results such as total salaries by department and job.

Explore the types of joins in relational databases, including natural, inner, outer (left, right, full), and Cartesian product, using primary and foreign keys to combine department and employee data.

Learn how a natural join in Oracle database management system combines two base tables into a single query, fetching city, department id, and department name from locations and departments.

Explore implementing a natural join in Oracle database management by combining locations and departments on the shared location_id, retrieving department IDs, department names, and city.

Explore performing inner joins with the using clause in Oracle dbms, including joining on department_id, the impact of aliases, and why aliases fail with using.

Combine employee and department data using the on clause with aliases and equal attributes on department id, illustrating inner join behavior and joined results.

Apply additional conditions to joined employee and department data using where and and/or clauses with a department alias, filtering for a specific manager or Jennifer.

Learn how a self join links a table to itself to reveal employee–manager relationships; alias the table as worker and manager to display each employee with their manager's name.

Explore outer joins in database management, including left, right, and full joins, plus natural and cross joins, and understand how unmatched data and metadata influence result sets.

Learn to join employees and departments with inner and outer joins, display department names for employees like Jennifer, and include unmatched rows using left, right, and outer join techniques.

Describe outer joins (right, left, full) and the cross join as a Cartesian product, using Oracle's HR schema to show matched and unmatched data between employees and departments.

Explore single row sub-queries in Oracle, using inner and outer queries to fetch job and salary, compare with peers, and translate business questions into secure, dynamic SQL.

Use multiple rows subqueries in Oracle to filter employees by salary conditions, compare salaries with a subquery, and return those whose salaries match a list or department.

Explore implementing subqueries that return multiple salary values and compare employees' salaries against those results. Use any and all semantics, including IT department and department 10 examples, to filter data.

Use subqueries that return a single value to compare salaries, replace ambiguous names with unique identifiers, and resolve errors when a subquery returns more than one row in Oracle SQL.

Explore PL/SQL concepts in Oracle: embed finance logic with anonymous blocks, procedures, and functions, covering declaration, begin, exception, and end, executed on the database server.

Declare a variable x, assign it 200, and display it using dbms_output.put_line within an anonymous block. Turn on server output to view the sequential, procedural execution.

Learn how to create or replace a procedure in Oracle, declare variables in a code block, output a variable, and call the procedure to verify successful execution.

learn to create or replace a procedure with input parameters X and Y and an output Z, declare variables, call from an anonymous block, and display results via server output.

Explore variable declaration in Oracle database management system by defining scalar and composite data types, naming rules, and using %TYPE and host variables to handle large objects.

Learn how to create or replace function in Oracle to calculate tax from a salary, returning tax as a numeric value and testing it on employees data.

Learn about Oracle's procedural language, PL/SQL, and how it enables developers to embed business logic in the database, using anonymous blocks, procedures, and functions.

Explore Oracle database concepts through hands-on demos of anonymous blocks, declare-begin-end blocks, and printing with dbms_output. Create and use procedures, functions, and bind variables to perform calculations like taxes.

Explore using a cursor in Oracle to process data row by row: declare, open, fetch first row, loop through records until end, and close the cursor.

Learn to work with cursors in Oracle database by opening, fetching into variables, and closing; iterate with for and while loops and apply if statements.

Develop a tax function that takes salary and rate, applies tiered criteria with if/else if statements, multiplies salary by the rate, and returns the tax for Oracle database management system.

Declare and use a cursor to fetch last names from employees where department id equals 30, loop through results, and apply an if statement to filter and display specific names.

Participate in a lab to practice connecting to an Oracle server within the Oracle database management system.

Practice basic use of the select statement in a hands-on lab within the Oracle Database Management System, mastering simple retrieval of data.

Practice querying an Oracle database by using the select statement with a where clause in this hands-on lab for Oracle database management system.

Filter data in Oracle database management by using the where between clause in a practical lab, enabling precise range queries and hands-on sql skills.

Explore wild card comparison in the Oracle database management system through this lab, focusing on querying techniques to filter data efficiently.