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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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 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.

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.

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.

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.

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.

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.

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.