SQL for Newcomers - The Full Mastery Course

Download PostgreSQL for Windows 64 bit, choose version 10.1, and download the installer from the project page.

Install the desk Eskil client to connect to busker scale and run scripts, then download sequel electron across Windows, Mac, or Linux, selecting the correct installer.

Learn to connect to PostgreSQL with SQLectron by creating a named connection, using localhost, entering user and password, selecting initial database, testing the connection, then saving to start the course.

Import course data using sqlectron by opening a downloaded data file, executing scripts to build the database, and refreshing the public schema to reveal departments and employees tables.

Learn to download and install the db beaver community edition for windows 32-bit, configure the connection, import the sample dataset, and run queries against employees and departments.

learn how to use the select statement to retrieve data from the employees table, choosing all columns with * or specific columns like first_name, last_name, and phone.

Explore what a database is, how tables organize data, and how relational databases use a management system and structured query language to access, update, and manage millions of rows.

Explore the three core data types in SQL—numbers, dates, and strings—through examples like salaries, hire dates, and emails, and learn how proper data type handling sharpens efficient, accurate data filtering.

Learn to filter employees by base salary using the where clause in sql, applying conditions such as greater than, greater than or equal to, and not equal to five thousand.

Use the between keyword to filter base salary between 1000 and 2000. Select first_name, last_name, phone, base_salary from employees and show how semicolons separate multiple queries.

Learn how to filter employees by a set of base salaries using the in operator. See how to select all employees whose base salary is 1600, 2700, or 4200.

Filter data by strings in SQL using where with single quotes to match last names, state as 'FL', and phone numbers from the employees table.

Master sql string filtering with like patterns and underscores to query states whose second letter is a and four-letter names starting with a and ending e.

Identify records using like with wildcards to filter strings: start with data, end with 566, or include Washington, and apply exact word and position constraints using % and _.

This lecture demonstrates filtering employees by multiple states using the sql in operator, selecting records where state equals 'CA' or 'NJ'.

learn to perform case insensitive string filters in sql using ilike, compare with like, and explore pattern matching for names and addresses for newcomers.

Learn how the and operator combines multiple filters in SQL queries. Filter data by state, base salary, hire date, and job title to retrieve targeted employee records.

Explore using the or operator to filter employees by hire dates such as before May 2011 or after August 2013, and use like or ilike for case-insensitive matching in queries.

Master the not operator in SQL to filter employees outside specified states using not in, and to find job titles and addresses using not like for spaces and patterns.

Explore how and, or, and not operators determine query results, with and precedence and parentheses clarifying Florida salary conditions of 1800 or 2200.

Explore basic math operations in SQL: addition, subtraction, multiplication, and division, including how to force real results with floating numbers and handling integer division.

Explore the modulo operator and its role in returning the remainder of integer division. See examples like 5 mod 2 equals 1 and 19 mod 5 equals 4.

Calculate each employee's net salary by computing base salary plus commission using SQL, showing first name, last name, base salary, and the resulting net pay.

Demonstrate string concatenation in SQL using the pipe pipe operator to join first and last names, inserting spaces with a separate string to create full names.

Learn how to concatenate strings with numbers in SQL by converting numbers to strings, choosing order, adding spaces, forming addresses, and creating new columns in select statements.

Learn to create a full name by concatenating first and last names, and alias the new column in a select statement without changing the underlying table.

Create a payroll report by selecting employee id, building a full name, constructing full email addresses, listing job titles, and calculating the next payroll amount from base salary and commission.

Learn how to order records by a column using order by, choose ascending or descending, and manage aliases with quotes.

Sort by columns with order by, first by job title then by name, using ascending or descending order, and filter titles with ilike and % wildcards, using aliases.

Use the distinct keyword to retrieve unique values from a column, such as states or zip codes, reducing 393 rows to 45 unique states in the employees table.

Discover how null values differ from empty strings and query them with is null or is not null in SQL, and identify missing phone numbers for data cleanup.

Use case statements to map state abbreviations to full names in sql. Enhance readability with when, else, and aliases in select queries.

Calculate net salary by combining base salary and commission and subtracting income tax using SQL expressions and case statements, showcasing practical payroll queries.

Explore how SQL functions perform calculations, data cleaning, formatting, from counting employees to trimming spaces and capitalizing names, distinguishing single-row and multiple-row functions, with examples like upper and sum.

Use the avg function to calculate the average base salary from the employees table, filter by states such as New Jersey, and alias results for multi-state comparisons.

Learn to use the count function to tally values and records, counting rows in a table or non-null values in a field, with attention to empty strings and nulls.

Explore how the min and max functions extract salary ranges from the employees table, compare min and max values, compute their difference, and analyze salaries by state with grouping.

Learn to count unique values in a column with count and distinct, using the employees table to tally states, job titles, California zip codes, and overall totals.

Learn how to count employees per state by using a group by clause, turning a total count into per-state counts and revealing state-level metrics.

Learn to count employees by state and job title using group by multiple dimensions, add job title as a dimension, and sort results by count, job title, and state.

Explore building a multi-dimensional payroll report by grouping by job title and state, calculating count, average and minimum base salaries, total net salary, and maximum commission rate.

Learn to filter results by aggregate functions using the having clause, showing counts greater than one per state and job title, and understand the select statement structure.

Explore character functions in sql, including upper, lower, and initcap, and learn how case manipulation and single-function queries transform strings on dummy values and the employees table.

Learn how the concat function joins strings and the length function counts characters, including spaces. Use nested calls to count characters in concatenated names and reveal full names with alias.

Master SQL string padding with LPAD and RPAD to left or right pad strings to a target length, using characters like stars and dashes, with job-title examples.

Explore the trim function to remove leading and trailing spaces, clean dirty data, and ensure accurate string length and sorting in SQL.

Learn how to use the SQL replace function to swap substrings in strings, applying nested replaces to fix addresses such as streets and avenues, and understand its case sensitivity.

Learn how to use the substring function to extract specific chunks from long strings, using start index and length, with examples and cross-database notes.

Learn sql number functions: mod (modulo) returns remainders; round rounds numbers to the nearest value with optional decimals and negative second argument; trunk truncates digits.

The truncate function cuts off decimals to a defined precision, unlike rounding, and can keep a set number of decimals or zero by default, even for negative values.