1Z0-171 exam prep: Oracle AI Database SQL Associate

Learn to download and install Oracle Database 23 AI Free for Windows, extract the ZIP, and run setup, noting the two CPUs, 2 GB RAM, and 12 GB disk limits.

Download and extract Oracle SQL developer, run the program, and connect to the database using a manual connection with sis dba; verify the connection and start querying.

Learn practical fixes for Oracle SQL Developer connection errors, including checking and starting the listener with lsnrctl, restarting the listener service, and reattempting connections after reboot.

Explore composing and executing select statements with the select, from, where, group by, having, and order by clauses to retrieve and view data in Oracle SQL.

Learn how to rename fields in Oracle SQL by creating select statements and applying column aliases with or without the as keyword, including handling spaces with quoted identifiers.

Learn why Oracle SQL requires a semicolon to end select statements, avoid errors when running multiple statements, and add comments using -- for single lines and /* ... */ for multi-line.

Learn how the where clause filters results in Oracle SQL, using equals, greater than, less than, and not equal operators, with examples on col_hash and column_number.

Explore using group by and distinct to produce one row per name (and per name and type), count rows with count(*), and apply the where clause between select and from.

Learn how the having clause filters grouped results after a group by using count. Understand why where cannot apply to aggregates and how to diagnose errors like Ora 00934.

Master the order by clause, the sixth clause of the select statement, to sort by one or multiple columns in ascending or descending order with best practices for column names.

Learn the six clause order in SQL: select, from, where, group by, having, and order by, using a keyboard mnemonic and practice activities in Oracle SQL Developer.

Practice activity 1 teaches you to build basic select statements, retrieve all columns from view dollar, filter with where cols = 12, and order by obj desc.

Create tables in Oracle SQL using GUI menus or DDL, then drop tables and compare repeatability, while learning to define columns and data types with create table statements.

Insert data into a table with insert into my first table values and verify using a select query. Learn how data types distinguish numbers from strings.

Learn how to delete data with delete from (with a where clause), use truncate table for removal, and drop table to remove the table entirely, noting DML and DDL distinctions.

Create a table with a decimal column, insert two rows, verify with select, then delete one row, truncate, and drop the table; prepare for future decimal calculations.

Explore simple arithmetic operations in Oracle SQL, using the select statement and the dual table (optional in Oracle 23i), and master the order of precedence with Bodmas, Bidmas, and Pemdas.

Practice Oracle SQL arithmetic by evaluating four plus nine and 24 times four plus three, with aliases like my answer and balance, noting precedence and parentheses, from dual by version.

Create a sample table with a decimal field, insert and query data, and compare running statements versus a script to ensure proper execution order.

Explore how numeric, decimal, and number types map in Oracle SQL, define precision and scale, and see how rounding and default values affect storage and accuracy.

Learn how a comma as a decimal separator affects numeric literals, causing errors like too many values, and how to alter the session and quote numbers with single quotes.

Explore Oracle SQL float data types, including binary float and binary double, and why they store decimal numbers imprecisely, versus number, numeric, or decimal for accurate storage.

Practice activity demonstrates creating a table, inserting and viewing data, and removing data and the table using create table, insert into, select, delete or truncate, and drop table.

Explore Oracle numeric and trigonometric functions, including power, square root, exp, ln, log, abs, sign, and trig and hyperbolic functions, with practical examples and radians considerations.

Master the rounding functions in Oracle: floor, ceil, round, and trunc, including behavior with negative numbers and rounding to various decimal places. Compare mod and remainder for division results.

Explore practice activity 5 by applying sql rounding functions: floor and trunk to round down, ceil to round up, and round for one-decimal-place results.

Explore char and varchar2 string types, their fixed vs variable lengths, and concatenation using the || operator; learn about literal strings, the alternative quote operator, and length versus lengthb.

Explore char and nchar versus varchar2 and nvarchar2, comparing fixed and variable length, ascii versus unicode encoding, and when to use the n prefix for unicode literals.

Learn Oracle SQL string functions, including substr, lower, initcap, NLS language support, trim variants, ltrim, rtrim, lpad, rpad, replace, translate, soundex, and chr, with examples on from dual and concatenation.

Explore nulls in SQL, observing how missing data affects numeric operations and string concatenation, with practical examples of inserts, selects, and concatenating first and last names.

Learn how null functions in Oracle SQL, including nvl, nvl2, coalesce, and nullif, to manage nulls and build clean concatenations.

Master string manipulation in Oracle SQL with concatenation, substr extraction, and replace, then trim spaces using ltrim, rtrim, or trim, as preparation for numbers-to-strings conversions.

Explore implicit conversions that join a string to a number using addition or concatenation pipes. Know when to use explicit conversions for formatting and how locale separators affect results.

Explore explicit number-to-string conversion with the two_char function in Oracle, including customizing formatting, currency display, sign handling, and using the fm modifier to trim spaces.

Explore converting numbers to strings in Oracle using the two_char function with customizable number formats, including currency symbols, signs, separators, locale rules, and roman numeral options.

explore converting strings to numbers in Oracle SQL using implicit and explicit conversions, two_number and cast, with format strings and default null on conversion error.

Explore how to convert text to numbers and numbers to strings in non-american formats using to_number, to_char, and the NLS parameters for currency and numeric characters.

Practice conversion to char and formatting with a dollar format to display numbers rounded to two decimals, using fm to trim spaces, and convert back with to_number using dollar 99999.99.

Explore date and timestamp data types in Oracle, including creating tables, inserting values, ISO 8601, locale considerations, and how date stores time to the second while timestamp supports fractional seconds.

Explore converting dates to strings by formatting with to_char, combining with strings, and controlling year, month, day displays using fill mode, case, and iso options.

Learn to convert times to strings in Oracle SQL using format elements and literals. Explore 12/24 hour formats, minutes and seconds tokens, am/pm, fractions, and seconds since midnight.

Master converting strings to dates with Oracle's to_date, craft custom format models, handle locales, and use RR and FF options to interpret two-digit years and ensure exact matches.

Practice activity demonstrates converting strings to date and timestamp, adding months, using extract for month and year, and truncating and to_char for formatting.

Create and populate the tbl_employee table in SQL, defining columns for employee number not null, personal name, family name, government ID, and date of birth with appropriate data types.

Add and modify columns with alter table, adjust varchar2 sizes, and handle nullable versus not null; compare drop table with recreating non-system tables, and use select to align insert columns.

Reinforces using the select statement with a where clause, covering case sensitivity, comparison operators, and like patterns with % and _ to filter employee data.

Explore the where clause with numbers, including greater than, less than, less than or equal to, not, between, and in, and learn to combine conditions with and, or, and not.

Learn how to query dates in Oracle SQL using where clauses, between, date literals, and index-friendly methods to accurately include times and avoid midnight pitfalls.