Data Analytics: Intro to SQL using Healthcare Data

Prepare for section two by downloading Microsoft SQL Server Management Studio or updating your database management system on Windows 10 to ensure you work with an up-to-date SSMS.

Learn to download and install Microsoft SQL Server Management Studio on Windows 10, and explore Azure Data Studio as an alternative while practicing SQL in a database management tool.

Learn to set up a SQL environment using SQL Server Management Studio, connect to a server, and create and drop a database. Follow along with slides and code.

Connect to the database engine in Microsoft SQL Server Management Studio, select a server (SQL Express), and open a new query window with Ctrl+N to begin writing syntax.

Learn how to locate your hostname and server name for local SQL Server connections using SQL Server Configuration Manager and SQL Server Express, then connect with the host and instance.

Connect to a server, create a new database named sql_course_db using create database syntax, avoid spaces by using underscores, then execute and refresh to confirm creation.

Master the drop database command to remove databases, specify a target, and execute the drop to keep the server clean, using examples like SQL course DB and test.

Learn to use a created database by selecting the intended database on a server, managing multiple databases, and executing commands (with comments and F5), then prepare to create multiple tables.

Practice self evaluations in Microsoft SQL Server Management Studio after each section to reinforce learning; attempt them on your own before the instructor reviews them in a follow-up video.

Practice self evaluation steps in SQL Server Management Studio, show commenting methods, and create and use a database named SQL Course for interactive SQL exercises.

Section 4 introduces tables, table structures, and data types, guiding you through creating tables and understanding variables to design SQL schemas that support healthcare data, with downloadable code.

Learn how data types in SQL tables define columns such as int, varchar, datetime, and decimal. See how correct types prevent errors in the average charge per visit.

Connect to the SQL course DB and switch to the desired database. Create a test_table with patient_id varchar(255), patient_name varchar(255), patient_state varchar(255), visits int, charges int, then execute to verify.

Learn to view a newly created table in SQL Server using select 1000 rows, then drop the table with drop table syntax and verify it no longer exists.

Learn how to insert data into a SQL table by specifying the table, columns, and values. Practice with a test table and verify results with selects, including a single-row example.

Download the provided SQL code file, then run a multi-row insert into the test table in Microsoft SQL Server to populate data efficiently, after learning table creation and data types.

Explore how null values behave in SQL when inserting partial data into a test table and applying not null constraints and defaults, and why null is not equivalent to zero.

Learn how to create tables with a primary key to enforce unique, non-null values like patient IDs, prevent duplicates, and enable table joins across related data.

Learn to manage SQL scripting by using if object_id to safely drop and recreate tables in Microsoft SQL Server Management Studio, enabling smooth testing and reruns with minimal errors.

Complete the section four self-evaluation by seven steps, including creating five rows of your own data to practice insertion and deepen learning, then pause before watching the next video.

Create a SQL table with id as primary key, name and address as varchar, and visits as int; set default zero for nulls, then insert data and verify with select.

Master retrieving and manipulating existing data using where, order by, group by, having, and case statements, preparing clean, consumable data for analysis.

Explore selecting all columns with select star from, pull top rows with select top, and set up a test table in SQL Server to practice on healthcare data.

Explore SQL's distinct function to return only unique values, applying it to rows and to subsets of columns like gender or state in a 25-row table with a primary key.

Discover how the where clause filters data by specific conditions, such as patient state equals Georgia, and apply it in select, update, and delete statements.

Learn sql where clause operators: and, or, not, between, in, through examples with Georgia and female patients, and practice writing the syntax.

Master the like operator and wildcard symbols in SQL, including brackets, underscore, percent, and dash, to build pattern-based where clauses and handle null and not null values.

Sort data in SQL using order by to arrange results ascending or descending, with default ascending, by column name or number, illustrated by sorting male patients by state.

Learn how to order query results by column number in SQL Server, using column two to sort by patient state instead of the name, a time-saving shortcut.

Explore how aggregate functions summarize rows with count, including count(*) and distinct count, and how where filters and group by affect results and errors.

Master how group by works with aggregate functions in SSMS and Azure data studio. Practice grouping by patient state and using order by for summaries.

Explore aggregate functions min and max in SQL, compute minimum and maximum charges, group results by gender, filter by states, and apply order by to reveal sorted summaries.

Learn how the sum function adds numeric charges across rows, and how changing granularity with group by or roll up shifts sums from patient-level to state-level, avoiding non-numeric data.

Discover how to use the avg aggregate function to compute average charges, compare with sum divided by count, and analyze charges by state with group by and order by.

Explore alias names to rename columns for clear results like average charges, counts of patient IDs, and sums of charges, and rename tables for future multi-table queries.

Learn how to use the concat function in SQL Server to join two columns, apply an alias, and format results with a separator, using patient, state, and gender data.

Explore where clause operators, including greater than, less than, greater than or equal to, and not equal, and apply them in where and having statements with healthcare charges data.

Explore how having changes granularity with aggregate functions in SQL, compare having with where, and apply group by and order of operations to sum charges by state.

Learn how case statements in SQL evaluate conditions inside a single column, returning mapped values or the original data, and chain tests for data cleaning and reformatting.

Analyze a SQL query to master select, from, where, group by, having, and order by. Calculate the average charge per visit for female patients in Georgia and Florida using aliasing.

In this self-evaluation review, practice SQL basics from section five, including selecting columns, grouping, case statements for gender, averaging visits, filtering by charges, and ordering results.

Learn updating, deleting subsets, truncating and altering tables, and using select into with the into function. Build temporary tables in Microsoft SQL Server Management Studio to practice section six concepts.

Update the test table with a where clause after confirming the target row with a select. Learn to set one or more columns and update Nate from Wyoming to California.

Learn to delete data efficiently with a delete from statement and a condition, and avoid risky drop table and reinsert methods when handling large healthcare data.

Learn how to use the truncate table function to remove all data from a table while preserving its structure, including column names and data types.

Alter a table's structure in sql server by changing column data types, adding or dropping columns, and adjusting constraints, including default values and the money data type.

Explore the select into operation in sql, creating new tables and backup copies from filtered data, and compare it with create table, highlighting its use and limitations.

Execute select into with conditions to roll data up by gender, applying case statements, aggregating visits, charges, and patient counts, then create a new table for repeated analysis.

Explore temporary tables in SQL Server, using the hashtag to create test tables for quick validation, stored in tempdb and not in the actual course database.

Guide learners through self-evaluation steps: update gender values, rename patient IDs to Bobby, move names and IDs into a patient table, remove Florida patients, and add weight, height, and age.

Update and query a 25-row healthcare dataset using update, delete, and insert into to build a patient table, refine gender values, and add weight, height, and age.

Learn to join multiple tables, establish primary keys and foreign keys, and run queries across related data in SQL Server using healthcare data as a practical example.

Apply primary keys to ensure unique patient IDs and identify each record. Link data across tables, such as test table and test location, using constraints with primary and foreign keys.

Explore relational databases with star and snowflake schemas, linking a fact table to dimension tables, and use SQL Server Management Studio to run joins on health care data.

Explore the core data relationships in healthcare data, including one-to-one and one-to-many connections, analyze star and snowflake schemas, and note many-to-many as a rarely used, complex case.

Set up practice data in SQL Server, create hospital and test tables with primary and foreign keys, and practice joining them to explore join types.

Explore inner joins, left joins, and right joins using simple visual diagrams and hands-on SQL with test and hospital tables, showing how inner joins match data and drop nonmatching rows.

Master left joins in SQL by returning all records from the left table and matching rows from the right, with nulls for nonmatches.

Learn how right joins pull all data from the right table and match to the left table, using hospital and test tables to compare with left joins.

Discover how foreign keys connect a fact table to dimension tables in a star schema, introducing primary keys and joins for health care data.

Create a foreign key constraint on test.location_id referencing hospital.location_id to enforce referential integrity. Visualize the relationship with a database diagram and verify with an inner join that all rows align.

Section eight introduces a reusable SQL data set and reviews functions from creating tables and joins to rounding, is null, nested queries, stored procedures, and constraints.

Practice using the SQL round function to round hospital charges with decimal precision, learning syntax, decimals, and aliasing to clean data and display nicer results.

Learn how to use cast and convert to change data types in SQL queries, understand precision and decimals, and note they alter only the query output.

Explore the format function in Microsoft SQL Server 2017 and Azure Data Studio to format numbers, currencies, and cultures, with caveats for ordering by varchar.

Apply the upper and lower functions to transform text in a table, practice with patient name, alias results as upper patient name and lower patient name for business intelligence tools.

Review the is null function and demonstrate replacing null values with a default like zero, using visits as the example and applying aliasing for aggregation.

Explore the nullif function in SQL by converting zeros to nulls to avoid divide-by-zero errors, applying it while computing charges per visit across hospital data without altering the base table.

Explore inner queries (nested queries) to adjust data granularity, count patients by state, and use a having clause with a named subquery for SQL Server health data analysis.

Create and execute stored procedures to encapsulate complex queries, enabling daily or weekly automation and incremental data loads from electronic medical records, billing, and accounting systems.

Add a new location name column to the test table, then update it by inner joining the test and hospital tables on location ID to set the location name.

Explore common SQL constraints, including not null, primary keys, foreign keys, defaults, unique, and check, with practical examples for creating, dropping, and using constraints.

Explore left and right functions in SQL to shorten data and extract initials, update gender from m/f to full words, and display a four-character location ID while preserving unique keys.

Learn how the trim function removes spaces at the start or end of a substring in a hospital data table, and understand its limitation with spaces in the middle.

Wrap up section eight by reviewing the diverse functions used and inviting student feedback. Prepare to dive into section nine and ten with health care data.

Section nine guides you to set up a health care database, create eight tables, import data from Excel into SQL Server Management Studio, and build ERD, preparing for section ten.

Explore how health care data flows from electronic medical records to billing and insurance payments, using a hospital scenario to illustrate CPT codes, billing, SQL-driven analysis, and accounts receivable.

Explore the healthcare ERD and star schema, linking the fact table to primary keys and foreign keys to dim patient, dim physician, dim date, dim CPT code, and data dictionary.

Explore the data dictionary, mapping tables and columns with keys and definitions. Learn how fact and dimension tables: dim patient, dim physician, dim date post, link gross charges and adjustments.

Review the healthcare data model in Excel, covering fact and dim tables, keys, CPT and diagnosis codes, transactions, and preparing imports into SQL Server.

Learn to create a health care database and its tables, define primary keys and null constraints, and prepare data import from Excel for a healthcare analytics workflow using SQL.

Create and enforce foreign key relationships from the health care fact table to dimension tables using primary keys, add constraints, and generate an erd in sql server management studio.

Avoid dropping the healthcare_db or its tables to preserve data, foreign keys, and prior imports created with the import export wizard in SQL Server Management Studio.

Learn to join healthcare database tables in SQL to analyze patient visits, diagnoses, CPT codes, charges, and insurance payments, using dim and fact tables in a hospital billing scenario.

Explore health care finance through an Evergreen Clinic example that tracks gross charges, payments, adjustments, and accounts receivable to compute gross and net collection rates.

Practice extensive SQL with the healthcare database, using section ten questions to master SQL syntax, fact tables, and gross charge filters, after reviewing section nine resources.

Count rows in the fact table for healthcare data where gross charge exceeds 100 to answer question one, with 5513 results. Explain why a where clause preserves granularity over having.

Learn how to count unique patients in a healthcare database using distinct counts from fact tables or patient numbers across tables, yielding 4962 patients.

Count distinct CPT codes by CPT grouping, order results by descending counts, and identify distributions across surgery, radiology, and medicine codes.

Link dim payer and dim physician to the fact table, filter Medicare claims, and count distinct providers (NPI) to find 682 providers submitted Medicare claims.

Compute the gross collection rate by location from the fact and dim location tables, summing payments (negating for credits) and dividing by gross charges, revealing Twins Mountain Hospital at 63.4%.

Analyze CPT codes by summing units in fact table, group by CPT code, filter with having units greater than 100, and use an inner query to count codes, yielding 29.

Identify the physician specialty with the highest payments and show those payments by month using the payments fact table joined to the dim date, grouped by month period and year.

Learn how to count CPT units by diagnosis code group, filter for J codes with a wildcard search, and interpret ten J-code groups linked to respiratory infections.

Build a patient demographic view from the dim patient table by concatenating first and last names, using case statements to bucket ages, and formatting city and state for dashboards.

Analyze credentialing write-offs across locations, show the highest adjustments at Angel Stone Community Hospital, where 30 physicians are impacted and $2,000 is tied to credentialing issues.

learn to compute the average patient age by gender for patients with type two diabetes at Bigheart Community Hospital, using inner joins and a granularity shift to patient ID.

Compare established and new office visits, join CPT data from fact table, and compute charge per unit as gross charges divided by CPT units, formatted in dollars at two decimals.

Analyze payment per unit by payer for initial hospital care CPT codes, aggregating CPT units and payments while handling zero values with null and compare across commercial, Medicaid, and Medicare.

Compute net charge from gross charges minus contractual adjustments and assess net collection rate (payments over net charge) by physician specialty using healthcare data from a fact table.

Join the dim location with physician and patient tables via the fact table to produce location-level counts, gross charges, and average charge per patient for visualization.

Create your own sql question using the healthcare erd, practice joining tables, write the syntax to answer it with patient, physician, transaction, and diagnosis code data, then submit for review.

Master SQL using Microsoft SQL Server Management Studio while learning healthcare data for real-world applicability. Invite feedback and express appreciation for your participation with a five-star review.