SQL Beginner to Advanced Level - Data Analytics

Explore data, databases, and database management systems, and understand their roles as core elements in this course. Learn to create, use, and delete databases through hands-on code demonstrations.

Define data as unorganized, unprocessed facts from observations or measurements—numbers, images, sounds, text, or videos—and show how processing turns them into usable information, like tracking bicycle-lane usage.

Define a database as an organized collection of data, typically stored electronically in a computer system, acting as a container for numbers, text, video, and images in SQL contexts.

Understand how a database management system manipulates, retrieves, and manages data stored in a database, and see how it retrieves information and presents results to users.

Explore relational database management systems (RDBMS) as the most popular DB engines, where data sits in tables of columns and rows, including MySQL, PostgreSQL, Oracle, SQL Server, and IBM DB2.

Learn how to create databases using the create database syntax, naming databases such as restaurant, basketball, and computers, in SQL Server Management Studio for data analytics.

Learn how to save SQL queries in SQL Server by creating a dedicated folder, using the save option, and interpreting green and yellow indicators to track saved work.

Use the use database syntax to switch between databases in SQL Server, such as computers, basketball, and restaurant, while viewing system databases like master, model, msdb, and tempdb.

Drop a database using the drop database syntax and a concluding semicolon. Understand that deletion is irreversible without a backup, and refresh the server to reflect changes.

Explore how SQL binds databases and database management systems, explaining what SQL is, what it's used for, how it's used, and exploring SQL dialects and naming conventions.

Discover how SQL communicates with a relational database management system to retrieve, manipulate, add, update, or delete data in a database.

Explore how SQL dialects differ across relational database systems, while SQL remains the standard for communication. Learn that the main differences are features, not the language.

Learn SQL naming conventions for tables and columns, using names in snake_case or camelCase, avoiding spaces, keeping names under 30 characters, starting with a letter, and reserving uppercase for keywords.

Explore how SQL recognizes the expected data types in columns and how numeric, string, and date and time types are represented.

Discover SQL data types that define stored data. Explore numeric types like int and bigint, string types like char and varchar, and date types such as date and year.

Explore numeric data types in SQL, including int, bigint, smallint, and tinyint, focusing on range and storage, and learn decimal with precision and scale for currency and use over double.

Explore string data types, focusing on varchar for variable-length strings up to 8000 characters, stored in single quotes; learn varchar syntax and how numbers become strings when quoted.

Define and enforce data types in a dog table, using strings for names and breeds and integers for ages, to enable calculating human years by multiplying dog ages by 15.

Explore how data is stored in tables, create tables, insert records, retrieve data, and delete tables to manage databases.

Understand what tables are in SQL: database objects that hold all data, with columns as fields and rows as records, illustrated by an employee table with names, departments, and IDs.

Create tables with the create table syntax, define columns with varchar and int, and learn naming conventions like snake case and camel case.

Learn how to insert data into tables using insert into with specified columns and values, including single and multiple row inserts for cat and cat2 tables.

Master data retrieval in sql by using select star from table to view all records and columns, with hands-on examples from cat and cat2 tables.

Delete a table using drop table in SQL, see examples with cat and student tables, and understand that deletion is final unless a backup restores the data.

Explore data definition language (ddl) commands used to define table schemas, apply constraints, and include non-intrusive comments in sql queries.

Learn how to use data definition language commands, including create, drop, and alter, to manage database objects, add or remove columns, insert records, and verify tables.

Learn to add comments in SQL queries, using single-line and multiline styles, to describe steps and data logic without affecting execution, with examples of creating tables and inserting records.

Learn how sql constraints enforce data integrity by distinguishing null from unknown values and applying not null rules on table columns to require essential fields during inserts.

Enforce email uniqueness in an employees table by applying a unique constraint on the email column in SQL Server, preventing duplicate records and ensuring clear, distinct staff emails.

Explore how the primary key constraint creates a unique, non-null identifier for each row, using a student table in SQL Server to demonstrate schema, insertion, and constraint enforcement.

Learn how to use identity in SQL to auto-generate student ids with a seed and increment, making student_id the primary key, demonstrated on sample inserts.

Explore data query language by learning the select statement and where clause, including how to select specific columns and filter records to refine results.

Learn how to use the select statement to query a database, choose specific columns, and print results from tables like penguin, demonstrating column selection without changing data.

Use the where clause to filter a penguin table by sex, color, and age. Learn to select only the needed columns and rows to improve server efficiency.

Explore data manipulation language commands such as update and delete. Review aliases, their usage and importance, and study the decimal data type with a schema of decimal records.

Master deleting records with SQL's delete command, using where clauses to target single or multiple rows, and distinguish delete from drop while avoiding unintended deletions by names or colors.

Learn how aliases give temporary names to tables and columns to simplify queries and improve readability, using as to print penguin id as id and penguin name as penguin name.

Explore the decimal data type in sql, understanding precision and scale for price and size, and compare decimal with integer using a shoe store table example.

Explore string functions essential for querying and analyzing records within SQL, and practice with the commonly used string functions. Build foundational skills for SQL data analytics.

Discover how SQL string functions work, including the concat function to join strings, and follow a hands-on setup to create a bookstore database and a books table.

Learn how to use the concat function to join string values in sql, add spaces between words, and concatenate author names and titles into readable sentences with aliases.

Learn how to use the concat_ws function to join multiple strings with a separator, applying the separator once across title, author first name, and last name in a query.

Master the SQL left function to extract the first characters from strings, illustrated with street addresses and book titles, and learn to append with concat for abbreviated results.

Learn to use the right function to extract the rightmost characters from a string, count from the right, and combine results with concat for author name, year released, and title.

Discover how the LEN function returns total characters of a string, counting spaces but not trailing spaces, and use it in queries with the concat function to count last names.

Utilize the lower function to convert strings to lowercase and the upper function to uppercase in SQL; apply them separately and, in the same query, concatenate results if needed.

Discover how ltrim, rtrim, and trim remove leading and trailing spaces from strings, with password examples showing character counts and backend input handling.

Learn to use the sql reverse function to reverse strings, apply it to first and last names, alias the results, and combine string functions to compute name lengths.

Explore the replace function in sql to search and replace substrings within a string, using replace(string, old_substring, new_substring), with examples like happy to yellow and spaces to plus signs.

Learn to use the substring function to extract part of a string by start position and length, with examples from Madagascar and server titles and applying it in SQL queries.

Apply string functions to manipulate data, sort the results in an orderly way, retrieve distinct values, and use obscure wild cards to refine queries.

Learn how to insert names containing apostrophes in SQL by escaping with two single quotes, avoid unclosed quotation marks, and insert records into the books table.

Learn how to sort data with the order by clause, choosing ascending or descending order before analysis. See examples sorting by last name and reviews.

Master the distinct keyword with select to return unique values across columns such as gender, genre, year, and last name. Order results by last name in ascending or descending order.

Apply the top clause to limit records returned from the books table, printing the first or top n records and ordering by copies or reviews.

Master the offset and fetch arguments to skip top records and retrieve a defined number of rows, with practical examples using books and review counts.

Master wild cards and the like operator in SQL, using percentage wildcards to find titles that start with, end with, or contain a specific term.

Use the underscore wildcard in SQL Server to represent a single character, filter the books table by available copies, and combine with the percent wildcard.