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 postgresql on your local machine, set the super user password, and note the default port 5432. Connect to the database and prepare your scripts after finishing installation.

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.

Install SQLectron by launching the downloaded installer, wait for setup to complete, and learn how to connect to the database.

Identify the database user (admin or super user) and its password to connect to the default database POS Chris on localhost at port 5432. Create multiple databases as needed.

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 craft SQL queries to extract employee data by salary: base salary 5000 or more, salary between 1000 and 2000, or specific amounts like 1627, 2700, and 4200.

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.

Master date-based filtering with the higher_date column, converting to full dates and using operators to select employees hired before 2005, between 2008 and 2010, or on 2010-08-22.

Use the between operator to filter employees hired from 2008-01-01 through 2010-12-31, and learn to sort results while avoiding date boundary mistakes.

Use the equals operator to filter by an exact hire date in SQL. The example selects employees hired on August 22, 2010, returning a single matching record.

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.

Apply math operations to calculate each employee's net salary and show a separate column, then filter results using where with conditions like commission over 2000.

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.

Learn why alias names cannot be used in where clauses and how to apply conditions using actual column names, with examples like base salary over 3000.

Learn to limit query results with the limit clause, using where and order by to fetch the top records efficiently while reducing memory use on large tables.

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.

Develop a unique view of employee data by applying distinct to state and job title, showing grouped state and job title combinations in a clean report.

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.

Apply the sum function to calculate the total base salary across all employees. Filter by state with a where clause to compare totals for Washington, New York, and California.

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.