SQL Course: Learn SQL from Beginner to Advanced

Learn basic select statements, using literals, expressions, and aliases, with optional as naming literals car and expressions price, as in the Toyota and 3000 plus 130 example.

Define a character as a single element of a string and illustrate converting between letters and numbers using ascii and char functions.

A literal is a fixed value of a data type, numeric or non-numeric, such as 123 or 'bill dot dux at gmail.com', used for calculations in SQL.

Understand that non-numeric data values must be enclosed in single quotes in sql. Switch to pgadmin, run a statement, and observe that all non-numeric values are quoted.

Understand that numeric literals must not be enclosed in single quotes, and see examples like 37,973 and 1234567. Learn to write and execute select statements using numeric values without quotes.

Explore arithmetic expression operators in SQL, using literals, functions, and operators to compute data. Evaluate expressions left to right and view the result sets.

learn how aliases serve as substitute names for literals or expressions, use the as keyword (optional), and name headers with aliases, for example select hello world as introduction.

Learn how escape characters change the interpretation of special symbols in SQL and C style syntax, with examples using apostrophes and backslashes and applying them in statements and aliases.

Explore how the concatenation operator, represented by two bars, combines two or more strings. Use examples like hello, world, space, and 2025 to see left-to-right results.

Explore how to annotate SQL statements using block comments with /* ... */ and single-line comments with --. Learn to span multiple lines and execute the statement in PgAdmin.

Explore SQL data types, including numeric, character, and date, and practice creating columns in pgadmin with aliases and names such as maintenance, cost, and maintenance date.

Learn to convert data types using the cast operator and cast function, applying them to convert Mercedes-Benz to varchar and executing the statement in Pgadmin.

Compare smallint, integer, and big int ranges for use cases. Summarize how numeric types store exact values with precision and scale; real and double precision are inexact floating point numbers.

Explore the character data types, including varchar or character varying(n) and char(n), and how varchar uses variable length with dynamic memory while char uses fixed length and padding.

Explore the date time data types, including date, timestamp, and time, within the SQL bootcamp for learners.

Learn how the boolean data type stores true or false values in sql, and apply it to simple logic in your queries.

Learn to use the select clause to retrieve columns or expressions, specify the from clause with tables or views, and apply aliases, an asterisk for all columns, and distinct to remove duplicates.

Use an asterisk to select all columns from a table in pgadmin, then run the statement to execute and display all columns from maintenance.

Learn how to select specific columns from a table using PgAdmin, choosing description, maintenance date, and maintenance cost from the maintenance table, then run the query.

Learn how SQL identifiers, both unquoted and quoted, name objects like tables, columns, views, schemas, and functions, and use them to write queries and design schemas.

Master unquoted identifiers in SQL: start with a letter A to Z or underscore, may include letters, digits, and underscores, are not case sensitive, and use aliases avoiding keywords.

Learn how delimited identifiers, or quoted identifiers, use double quotes to include special characters, spaces, or digits and to preserve exact casing.

Learn how to use column aliases in SQL, including the optional as keyword, and when to enclose aliases with spaces or special characters in double quotes.

Eliminate duplicate rows in a query by applying the distinct keyword, comparing results with and without distinct, and observing row counts drop from nine to seven.

Explore how to generate a set of rows in PostgreSQL using the values clause to create a literal table of constants, and compare it with union all.

Explore how the where clause filters data by meeting a specified condition and limits the query result set with several applicable conditions.

Master comparison operators in SQL by executing queries with <, >, <=, >=, =, and != on maintenance cost data in pgadmin, illustrating true or false conditions.

Master the between operator to filter data within a range and negate results with not between in pgadmin; practice with statements like maintenance cost between 30,000 and 40,000.

Learn how the in operator matches a column to any value in a list, with optional not in, using pgadmin to build and run the statement.

Explore pattern matching in SQL using the LIKE operator with percent and underscore wildcards, filtering car type and maintenance cost by end, contains, or starts with patterns.

Learn to use IS NULL and IS NOT NULL to filter queries, such as selecting car type, maintenance cost, and car ID from maintenance where car ID is null.

Apply the and operator to SQL queries by selecting all columns from the maintenance table where car type equals Rolls-Royce and maintenance cost greater than 30,000.

Demonstrates using the or operator in sql, showing that at least one condition must be true, and walks through building and running a statement in pgadmin.

Master sql operator precedence by using and and or, with parentheses to control evaluation order, and relate to bodmas and pemdas in pgadmin.

Execute sorting with the order by clause to sort by maintenance cost or other columns in ascending or descending order, handle nulls, use multiple columns, numeric positions, and aliases.

Learn how the offset clause skips rows to produce a subset of results, using row numbers and over window clause on the maintenance table.

Understand how fetch returns a specific number of rows, uses offset to retrieve a subset, and how fetch with ties extends results via the order by clause.

Apply the limit clause to restrict the number of rows returned by a query, enabling glimpses in large data sets, or use the fetch clause to return first three rows.

Combine offset and limit to control result sets, skip the first three rows, and display three rows in Pgadmin after running the statement.

Learn how to use offset and fetch together in SQL to limit results, with a hands-on PgAdmin example that fetches the next three rows after skipping the first three.

Explore string functions and operators, mathematical functions and operators, and date time functions in SQL, gaining practical skills for data manipulation.

Combine strings in SQL using concat function or concatenation operator to create full names. Handle nulls: concat ignores nulls, while the operator yields null if any value is null.

Master SQL case conversion with lower, upper, and init cap to standardize text. See practical examples transforming car type values and rental literals.

Learn to extract parts of a string using left, right, and substring (substr) by specifying a start position and length, retrieving from the left side, right side, or any position.

Apply ltrim, rtrim, and trim to remove hashtags or spaces from the left, right, or both ends of a string, specifying characters in the parameter list and observing the results.

Learn sql string manipulation using lpad, rpad, and replace to pad strings to fixed lengths with specific characters and replace a with the at character.

Apply the length function to count characters in strings such as first name; switch to pgadmin and fetch the first three rows from the maintenance table, then run the statement.

Learn to use the modulus function and operator to find remainders, filter even and odd numbers, and apply these conditions in where clauses of select statements.

Demonstrate ceiling and floor functions in SQL, showing ceiling rounds up and floor rounds down. Illustrate with examples such as ceiling(1.73) = 2 and floor(1.73) = 1.

Explore rounding and truncating numbers using the round and truncate functions. See how values like -3.77, -1, and 3.77 convert under decimal and integer rules.

Master date and time functions in sql by using current date and current timestamp, and extract to obtain seconds from timestamps in pgadmin, then run the statement.

Master Posix regular expressions for literal column or expression matching and manipulation. Run the attached script in pgadmin and refresh the object explorer to see created table.

Learn how POSIX regular expressions work in SQL. Use case sensitive matches, pattern literals, and negation with not or ! to filter values like Google in the X table.

Explore the building blocks of regular expressions, including literal characters, the dot, start and end of string, quantifiers, bracket expressions, escapes with a backslash, grouping with parentheses, and the pipe.

Master literal character matching in regular expressions by learning how to escape special characters with backslashes, enabling exact patterns like dots and other symbols in SQL queries.

Dot matches any single character except a new line, with examples like color and cat, and you use it in a select statement with a where clause.

Explore posix regular expressions by using anchors ^ and $ to match string beginnings and endings. Learn to filter with where clauses in sql, using case sensitive patterns like ^AB and GL$, and verify results in pgadmin and a coding editor.

Bracket expressions define character classes inside square brackets, specifying sets of characters to match. Use ranges like 0-9 and negated classes in where clauses and run the statements.

Escape special characters in SQL patterns with a backslash to treat them as literals, such as escaping the dot so values with dots appear in the result set.

Groups (parentheses) form a single logical item and bind groups within a pattern using quantifiers, showing how groups and quantifiers alter matches and values in a query.

Learn to use the vertical bar for alternative matches in sql, identify color patterns, and switch to pgadmin to add a where clause that applies that pattern.

Explore regexp_count basics: set a start position, apply optional flags such as I for case-insensitive matching, and see how the pattern count changes when the flag is on or off.

RegExp in str searches a string for a regular expression and returns the match position. Use optional occurrence, starting position, subexpression, and flags like C or I to refine results.

Discover regexp_like flags: C for case sensitive, I for case insensitive, overriding operator type. See a dot example matching a single character and observe uppercase Google included.

Explore the regexp_replace function, detailing replacement parameter, start parameter, and occurrence parameter, and flags for case sensitivity, with Coca-Cola and Cola examples comparing positions and occurrences.

Explore using regexp_substr to extract text by start position, occurrence, and flags, with subexpressions and patterns, illustrated through examples returning many, mine, and Molly.

Explore how the case statement acts as a conditional expression using boolean expressions. Learn nullif returns null when values match and coalesce returns the first non-null value from multiple arguments.

Use SQL case statements to evaluate maintenance cost with conditional when clauses, classifying values as low, medium, or high and generating a price range from key performance indicators in Pgadmin.

Learn how the nullif function, which uses two arguments, returns null when values match and otherwise returns the first value, handling nulls and preventing division by zero.

Use the coalesce function to return the first non-null value from a list of parameters, substituting nulls with a value like not applicable, illustrated with text casts and selects.

Explore the least and greatest functions that accept multiple arguments to return the smallest or largest value, using values with literals to compare actual cost and estimated cost.

Learn how aggregate functions produce a single result from a set of values, including count, sum, average, minimum, and maximum across non-null inputs.

Learn how to apply aggregate functions to an entire column data set, using count, average, min, and max on the maintenance table in PgAdmin.

Learn to use aggregate functions with a where clause to filter data in a column. Run the query in pgadmin with car type = Rolls-Royce and observe reduced results.

Master the count aggregate function to count all input rows, count non-null column values, and count distinct non-null values for the car_id column in PgAdmin.

Learn how aggregate functions ignore nulls, use coalesce or nullif to handle them, and cast car ID to varchar in Pgadmin while noting nulls in count.

Learn how the group by clause divides data into groups, applies sum to maintenance cost, and orders results by the grouping key, using car type as an example.

Learn how the group by clause groups rows by multiple columns for aggregates, ensuring non-aggregated columns appear in the group by and the aggregated column is not included.

Explore how the having clause filters groups after grouping, using an aggregated column such as maintenance cost greater than 38,500; contrast with the where clause that filters rows before grouping.

Learn to generate aggregated reports using rollup, cube, and grouping sets, extending the group by clause to identify aggregated and non-aggregated values, with a multi-column grouping script in PgAdmin.

Apply the rollup operation to extend the group by clause, producing subtotals and a grand total within a grouping set, then switch to pgadmin and run the statement.

Explore the cube operation that extends group by to generate all grouping element combinations, yielding subtotals and a grand total in pgadmin.

Learn how the grouping function identifies aggregated rows in SQL queries using rollup and cube, returning 1 for aggregated and 0 for non-aggregated results.

Explore grouping sets to replicate rollup and cube results, using year and car type as examples; follow steps in PgAdmin to build and run the query.