SQL Tutorial or SQL Journey for Intermediate / Level 2

Learn SQL data types, including character types, date types, and xml storage, and see how to design an employee table with columns like employee number, first name, and last name.

Explore the character data type in databases, comparing fixed-length char to variable-length varchar. Understand how storage space is allocated and what happens when data exceeds length, including multi-byte languages.

Explore how the number data type handles digits and decimals, and how precision and scale define total digits and decimal places, using salary examples.

Learn about date, time, and timestamp data types, including microseconds, and how precise time storage enables tracking events such as weather data and flight paths.

Explore large object data types like binary files and character data, and introduce xml data types, with examples of storing long text and files in sql columns.

Explore DDL concepts by comparing table design to building a house: plan columns and data types, and use create, alter, drop, and truncate.

Learn to use ddl create syntax to define tables, columns, and data types, and describe the table structure to verify design.

Explore ddl alter operations in sql, including adding, modifying, renaming, and dropping columns and tables, with practical examples of alter table statements.

Learn ddl operations with drop and truncate, including dropping tables or columns, renaming them, and understanding that truncate removes data but preserves table structure.

Explore the substr single-row function to extract a portion of a string by specifying the column, start position, and length, with practical examples using first and last names.

Explore the single row function INSTR to locate a character's position in a string, starting at a given index, counting occurrences, with examples using first and last names.

Learn to use the length function in SQL to count characters in a column, with examples on employee first names, last names, emails, and phone numbers.

Explore single row functions in SQL by using the replace function to substitute characters in a column, with the syntax replace(column, old, new) and examples like replacing dollars and ampersands.

Learn how the translate function replaces characters in a string with designated replacements in a single expression. See examples of symbol replacement, case sensitivity, and sequential mappings.

Learn how the SQL concat function combines two columns into a single result and how to handle three or more values by chaining or nesting.

Explore the abs single row function, which converts negative values to positive, with syntax abs(value) and practical sql examples using fixed values and simple selects.

Learn how the round function in SQL rounds numbers to a chosen number of decimal places, including handling dollars and cents and applying rounding for negative values.

Explore how the truncate function works on numbers and dates, dropping decimal digits and enabling date bucket. See examples like converting 456.897 to 456 and applying truncation to dates.

Master the SQL MOD function by computing remainders, using examples like 10 mod 3 to illustrate a remainder of 1, and 18 mod 4 to practice applying the syntax.

Learn the SQL single row function ceil to round numbers up to the next integer, with examples like ceil(2.5) = 3 and ceil(11.2) = 12.

Master the floor function in SQL, a single-row function that returns the greatest integer less than or equal to a value, with examples like floor(25.75) = 25.

Master how to retrieve data from multiple tables by joining on common columns, covering inner, left outer, right outer, and full outer joins with employee and department examples.

Learn to write SQL join queries using left, right, and full joins with on conditions, aliasing tables to avoid column ambiguity, and selecting columns from multiple tables.