Oracle Cloud: Basic Oracle SQL with hands on Experience

Learn the basics of SQL, the structured query language used to retrieve and modify data in relational databases, run queries, filter results, and create or alter tables, with hands-on demos.

Create and name database tables with columns and rows to store data, such as employee, department, and job tables, then use SQL to select and filter data from these tables.

Explore essential Oracle SQL data types and how to assign them to each column during table creation, using examples of number, varchar2, and date to ensure data integrity.

Explore the structure of Oracle SQL queries, including the from clause to specify the table, the select clause to return columns or all columns, and where clause to filter results.

Download and boot the Oracle database development virtual machine, install Oracle toolbox, and adjust username, password, file location, and database names in the exercise files for hands-on SQL lab activities.

Explore Oracle SQL Developer, an integrated development environment and SQL client for building and running queries against an Oracle database, with download and installation and connection setup.

Create and configure a new database connection using the connections panel, supplying a connection name, user credentials, and host, port, and service details to access the HR schema.

Manage existing connections by right-clicking the collection name, selecting properties, and updating details such as name, password, and port, then connect or send; you may delete the collection by name.

Explore database objects by navigating the schema’s tables, inspect table structures and data types, and view sample data in employees and departments to build hands-on sql understanding.

Explore how to quickly filter and locate specific database objects in Oracle, using name filters, case sensitivity, and object type like tables to view structure and columns.

Learn to navigate SQL Developer tabs, open multiple worksheet tabs, and run simple queries against employees and departments tables, with each tab linked to a database connection.

Learn to view query output in Oracle SQL, including column headers, retrieved rows, and execution time, with options to scroll, refresh, and inspect the exact SQL used.

Learn that SQL keywords and Oracle table names are case-insensitive by default, allowing commands in any case, while string value filtering can be case-sensitive; explore editor features to switch case.

View the log of recently executed SQL commands and use the on-screen grid to filter or search by employee or department table, then close the history with the x icon.

Learn the difference between grid output and plaintext script output in SQL queries, and how the same results appear in the grid tab versus the transcript button's plaintext output.

Learn to write your first select query to retrieve data from a table, using select for columns and from for the table, then run to view all rows.

Master how to select specific columns in Oracle SQL, using the select clause to retrieve only first name and last name from a wide employees table.

Use the where clause to filter rows and display a subset of data, showing first name, last name, and salary for employees with salary greater than 10000.

Learn to filter data in Oracle SQL using multiple where conditions, including department ID, and combining criteria with and and or. Use parentheses to group filters and refine results.

Use the where clause with like and the % wildcard to filter partial strings, such as first_name starting with E. Strings are case sensitive.

Learn to filter ranges in SQL using between in the where clause, such as salaries between 5000 and 10000, and combine with and for department id between 50 and 80.

Filter employee data with where in to match multiple string job IDs, such as the ADP or IT program IDs, and select first name, last name, and job id.

Learn to use where in and not in with numeric values in Oracle SQL to filter by department and school id, returning names, departments, and schools.

Filter rows by the hired date using where with to_date and a date format mask to retrieve employees hired after January 1, 2000.

Learn how to use the where clause to filter exact string literals in Oracle sql, preserving case sensitivity and matching last names like King with capital K through hands-on examples.

Use distinct in a SQL select to filter duplicates and display unique department ids from the employees table; apply distinct to single or multiple columns to refine results.

Perform basic numeric calculations within Oracle SQL queries, such as applying a 15 percent raise to employee salaries and displaying the new salary and bonus values as on-the-fly calculated columns.

Apply column aliases in Oracle SQL to label calculated and existing columns, using as and quotes for spaces to produce legible output such as salary after bonus.

Learn to view a table's structure in Oracle SQL by checking column names and data types, including sample data, using describe commands or UI browse.

Use the order by clause after the from clause to sort query results by one or more columns, such as salary and department ID, in ascending (default) or descending order.

Explore Oracle SQL functions, including scalar functions like uppercase that transform single values and aggregate functions that compute per-group results, such as department-wise maximum salaries.

Use the substring function to truncate long job title values by specifying a start position and length. Return the first five or fifteen characters to format query output.

Learn to use the length function in Oracle SQL to measure string characters and include the resulting lengths of employee first and last name columns in your query outputs.

Learn how to concatenate strings in Oracle SQL using the concat function and the double pipe to combine first and last names with literal text from an employee table.

Explore how to control data case in Oracle SQL using upper and lower functions, format employee names, and perform case-insensitive filters on string values.

Learn how to use the INSTR function in Oracle SQL to locate a substring's position within a string, and apply it to display or analyze employees' names and phone numbers.

Explore Oracle sql padding with lpad and rpad to fix first name lengths. See how left and right padding with characters like stars creates consistent results.

Learn to use to_char with a format string as the second argument to format numbers as readable strings, showing local currency as configured in the database.

Apply the to_char function to format date values in Oracle SQL, using a formatting mask to display month names or numbers, day, and year.

Learn to use to_char to filter date values by month in Oracle SQL. Extract the month from hire_date to select employees hired in January, and compare month name or number.

Discover how the Oracle dual table lets you return literal values from a single row, perform simple selects, and apply functions such as uppercase to hello world.

Learn to round numbers in Oracle SQL using the ROUND function to control decimal places, applying it to constants or retrieved values, with 1 or 2 decimal options.

Learn to use trunc to trim trailing decimals in Oracle SQL, preserving digits after the decimal point without rounding; apply to constants or table values by specifying decimal places.

Use sysdate to retrieve the server’s current date and time, override default values, and format the output to show day, month, year, hour, and minute in 12- or 24-hour formats.

Learn how to use SYSTIMESTAMP to retrieve the current timestamp with high granularity in Oracle SQL, from dual, compare it with date functions, and format output using a formatting string.

Learn to use the months_between function in Oracle SQL to calculate months between the current date and hire date, round results, and derive years by dividing by 12 with aliases.

Learn to add months to a date with the add_months function in Oracle SQL. Use the date as the first argument and the number of months as the second.

Learn to combine data from two tables using sql joins, linking employee and department data to display employee id, name, and department name, and understand left, right, and full joins.

Learn how to join the employees and department tables on department_id to display employees' first and last names with their actual department names, clarifying normalization.

Explore inner join concepts by joining employees and departments on department_id to return records with matching values in both tables, the default behavior when no join type is specified.

Explains how the left join returns all rows from the left table and matching rows from the right, using employees and department data with nullable department IDs.

Learn how to use the right join in Oracle SQL to return all department records and any matching employees, using the from clause and the department_id join condition.

Explore full outer join, a combination of left and right joins, to return all rows from both employees and departments, including non-matching rows; watch for very large result sets.

Use table aliases in joins to reference columns such as first_name and department_name from employers and departments, and join on department_id to get the expected result.

Explore Oracle join syntax versus ANSI join syntax, and learn to join employees and departments using both on and where clauses for cross-database compatibility.

Explore data manipulation language (DML) and data definition language (DDL) in Oracle SQL, and practice inserting, updating, deleting data and creating tables while retrieving data.

Learn to create a simple table in Oracle SQL and insert new records by defining column names and data types, then verify results by querying the empty table.

Use insert into to add new rows with string and numeric values, verify results with select, and learn inserting with specified column lists and handling nulls for omitted columns.

Learn how commit and rollback control database transactions, making changes persistent or undoing them. Each action is an autonomous unit of work you complete with commit or rollback.

Explore how a transaction, an autonomous unit of work, controls the end state of data modifications in Oracle SQL, using commit to make changes persistent and rollback to undo.

Learn to delete rows using the DML delete command in Oracle SQL, with where clauses, examples for single or multiple rows, and rollback options to undo changes.

Use the DML update command to modify specific column values in table 1, targeting rows with a where clause, and verify changes with a select.

Explore how multiple dml statements delete, insert, and update execute as a single transaction and how commit and rollback restore data to its prior state.

Explore ddl commands to modify tables by adding and dropping columns, with alter table examples, and learn how auto-commit affects ongoing dml transactions.

Truncating a table is a data definition command that deletes all rows quickly, cannot be rolled back, reduces storage on tables, and is efficient for large tables when purging data.

Discover how aggregate functions in Oracle SQL work with group by to partition data and compute max, min, avg, and count within each department.

Learn to use aggregate functions in Oracle SQL with hands-on examples, computing max salary, min salary, average salary, sum of salaries, and employee counts, plus rounding results.

Learn to use group by in Oracle SQL to count employees by department_id, add readable aliases, and order results by the count to show departments with most staff.

Explore how to use group by with max, min, and avg to analyze department salaries, round results, alias columns, and order output for clear, readable insights.

Discover how the having clause filters aggregated results after grouping by department, using average salary and maximum salary to limit output.

Create a demo table with a primary key on column 1 to enforce uniqueness and inserts. Duplicates are allowed in column 2, and primary keys can be single or composite.

learn how not null constraints enforce data in a column by preventing null values during inserts in Oracle SQL, illustrated by inserting partial rows and recreating the table.

Enforce data validity with check constraints to accept only values meeting a rule, such as uppercase names, during insert or update operations.

Learn how foreign key constraints enforce referential integrity between tables by ensuring every supply_id in the product table exists in the supplier table, with practical inserts and errors when missing.

Learn how subqueries in Oracle SQL fetch data by using inner queries, inline view, and placing them in the from or where clause.