Oracle Database SQL and PLSQL Basics

Explore core Oracle database objects and their SQL commands, including create, alter, and drop, plus grant and revoke privileges, with practical examples for tables, views, synonyms, and sequences.

Explore flashback drop and the recycle bin in Oracle database, including drop and purge operations, restoring dropped tables with flashback table to before drop.

Explore data manipulation with insert, update, and delete in Oracle, and transaction control with commit, rollback, and save points.

learn how to declare variables in pl/sql, understand the four blocks (declare, begin, exception, end), differentiate anonymous vs named blocks, and print outputs with dbms_output.

Explore if-else, case expressions, and loop constructs in Oracle SQL and PL/SQL, including for, while, and nested loops, with emphasis on exit conditions and practical examples.

Explore Oracle database cursors, comparing implicit and explicit cursors, declaring and using cursor variables in PL/SQL, opening, fetching, and closing, with parameterized queries and loop-based data retrieval.

Explore Oracle database procedural concepts by designing and invoking named procedures, defining parameters (in, out, in out), handling exceptions, and implementing modular, secure, reusable code.

Explore how to create, invoke, and drop Oracle database functions, understand return values and parameters, compare functions with procedures, and use functions in select statements with examples.

Explore how Oracle SQL and PL/SQL basics handle errors through exception handling, covering types, structures, and mechanisms to raise and manage exceptions.

Explore Oracle database packages, their specification and body, public versus private components, and how to create, compile, invoke, overloading, and persist state.

Explore Oracle triggers: create and manage before, after, and instead-of dml and non-dml triggers on tables, views, schemas, and databases, with auditing and event-driven logic.

Explore PL/SQL types by defining records and collections, declare and use type blocks, and fetch single or multiple records through anonymous blocks and packages.

Explore plsql record types and collections, including associative arrays, nested tables, and varrays. Learn how records differ from collections, declare and instantiate, and manage elements with extend and index by.

Explore bulk processing in Oracle PL/SQL, using bulk collect and for all to perform insert, update, and delete on large data sets, with limit and returning clauses for efficient operations.

Learn how the DBMS_CLOUD API enables Oracle Database to read and write files to object storage (OCI, AWS S3). Manage credentials securely and perform put/get/list object operations for ETL workflows.

Explore pivot and unpivot operations in Oracle SQL, turning rows into columns and vice versa, with practical examples of aggregate functions, group by, and cross tab results.

Explore how the with clause in Oracle SQL enables subquery factoring and refactoring to reuse results, reduce code, and improve readability and performance.

Explore how to use extract and connect by level to work with dates in Oracle. Learn to generate previous and future years via the level pseudo column in hierarchical queries.

Learn how row_number over partition by and order by creates top n queries in Oracle. Apply partition by to get top three countries in each continent based on population.

Master listagg, the aggregate function that concatenates rows into a single column with a delimiter. Use group by and within group order by, and explore distinct and top five samples.

Explore Oracle SQL Loader concepts by designing control files for fixed-length and variable-length data files, loading data into tables, and generating log, reject, and discard files.

Learn to load multiple data files into one table and a single file into several tables using control files and a when clause in Oracle loader.

Invoke SQLLoader from Unix using single-file and pattern-based data loading, manage control and data files, validate credentials, run shell scripts, and generate per-file logs with archiving.

Learn to install Microsoft SQL Server Developer Edition or Express Edition, and use SQL Server Management Studio to work with sample databases, create tables, and run demo queries.

Explore stored programs in SQL Server, including procedures, functions, triggers, and views, with and without parameters; learn their syntax, execution, and differences from Oracle packages, plus scalar and table-valued functions.

Learn to load data with the bulk insert command in MySQL, using text or CSV files to populate an existing table with appropriate delimiter and row terminator settings.

Master importing and exporting CSV data in MSSQL Server Management Studio using the export and import wizards, configuring data sources and destinations, file formats, and bulk insert for large datasets.

Explore vector databases and embedding to store and query data as numeric vectors, with Oracle 23 database supporting a vector data type and cosine similarity for vector searches.

Explore ddl and dml operations on vector columns in Oracle database 23c, including creating tables with vector data type, managing embeddings via json arrays, and dense versus sparse vectors.

Learn to connect Python to Oracle database, generate embeddings with the sentence transformer, insert vectors into Oracle Vector Search 23AI, and perform cosine similarity searches.