Step by Step Practical Oracle SQL with real life exercises

Download and install Oracle; select edition (express, standard, enterprise) and version (12c or 11g); use Windows or Linux with admin privileges; configure SYS and SYSTEM passwords and complete installation.

Install Oracle SQL Developer to access the Oracle database; download the 64 bit version, extract the files, and launch the program to begin using.

Learn how Oracle uses schemas and users to manage access, grant permissions, and connect to a host, port, and SID using SQLPlus and SQL Developer.

Build a practical data model for a library using two tables, book_inventory and customer_book_list, linked by book_id, including price_per_day, and learn to create, alter, drop, and load sample data.

Create table statements define book_inventory and customer_book_list with their columns and data types, using varchar2, number, and date. Insert rows to load data into these skeleton tables.

Learn how Oracle data types guide storage and validation by choosing between character, number, and date fields using examples like price_per_day and borrow_date.

Learn how to insert data into the book_inventory table using two methods, including handling price_per_day as null, and understand commit versus rollback across concurrent sessions.

Learn how to read data from an Oracle table using the select statement, with examples from the BOOK_INVENTORY table, demonstrating distinct results and selecting all columns with *.

Order results by price or by price_per_day, use book_name for tie-breakers, and apply ascending or descending order.

Learn to use like and not like for partial name matches, including handling spaces, and apply in and not in with wildcards like % and _ in Oracle SQL.

Learn relational and arithmetic operators in Oracle SQL with real-life examples, using >, <, between, not equal, concatenation with ||, and aliasing in select and where clauses.

Understand null values as missing or unknown in Oracle, use is null or is not null, and beware not in with nulls; NVL and empty strings are treated as null.

Learn how to update table content using where and without where clauses, compare delete versus truncate, and understand rollback limitations and performance implications.

Explore the dual table in Oracle, learn how rownum behaves as a pseudo column, and use sysdate to fetch the current date with practical examples.

Apply lower() and upper() to compare strings in Oracle queries, and use length(), substr/substring, instr, and trim to extract, search, and clean text.

Handle nulls with nvl(), and round or floor numbers. Convert strings to dates with to_date() and format them with to_char(), and convert numbers with to_number(); understand date versus character types.

Explore aggregate functions in Oracle SQL, including min, max, sum, avg, and count, and learn how count(*) differs from count(column) across multiple rows, including count(distinct column), while nulls are ignored.

Learn how to use group by on single and multiple columns. Master having vs where clauses with practical library-style examples counting per customer and filtering aggregates.

Master inner, left, right, and full outer joins to link tables by book_id and fetch borrowed books with their names and customers, including unmatched rows.

Master joins across multiple tables by linking the book inventory, customer list, publisher list, and author list to build real-life Oracle SQL queries.