SQL for Developers and Analysts with MS SQL Server

Learn database basics by installing Microsoft SQL Server developer edition, using SSMS shortcuts, creating a sample CIA database, writing SQL, and applying comments, drag-and-drop, and context switching.

Understand that a database is an organized collection of data used to model an organization, with entities and relationships, managed by a DBMS using SQL Server.

Compare SQL Server editions by features and cost, noting Express Edition is free but limited, Enterprise is production ready but costly, and Developer Edition is free for demo.

Explore the SQL Server Management Studio environment, create a secure user, connect via localhost, navigate databases and server objects, and use shortcuts to run and debug queries.

Learn to create a sample database in SQL Server, configure data files, switch context with use, and load a CIA Factbook dataset to explore tables and top 1000 rows.

Learn the basic structure of an sql query, covering select, from, where, group by, having, and order by, including set operations such as union, intersect, and except, plus with expressions.

Explore the basic select clause in SQL, showing how to select numbers, strings, and date literals, alias columns with AS, and run queries in SQL Server Management Studio.

Learn to project only specific columns, such as country name and population, and display the top n rows using the TOP function in SQL Server.

Explore conditional evaluation in a select statement using case when and the iif function to categorize GDP as developed or underdeveloped, based on thresholds like GDP greater than 300 billion.

Learn to use the order by clause to sort results in ascending or descending order (ASC or DESC) and, when needed, order by multiple columns such as name and elevation.

Explore SQL Server exercises: identify top ten lakes by area, ensure unique river names, compute (4+5)*6, and display all airport table columns ordered by state.

Explore using comparison operators in a where clause, including greater than and not equal to, with literals and an example filtering elevations to illustrate the concept.

Learn how to use the in operator in the where clause to filter records from a list of values or a subselect, with examples using countries and their capitals.

Use the between operator in a where clause to filter records by inclusive lower and upper bounds, illustrated with dates and elevations, and compare to using two conditional statements.

Use is null and is not null to filter missing or present values in where clauses; avoid = or <> with null, and apply to elevation and city data.

Practice solving sql queries with exercises on listing new york cities, islands elevation 3000-4000, countries by continents, us airports by elevation, and top lakes by area using where conditions.

Learn to write subqueries with group by to summarize data and use a where clause to filter with a subselect, revealing the religion with the maximum followers per country.

Learn to use aggregate functions in the order by clause for group by queries, and the execution order of the from clause, with max and column headings in descending results.

Solve SQL exercise solutions by identifying the country with the smallest GDP, calculating city population growth using CTEs and window functions, and ranking top five countries by lake area.

Learn how to use a basic from clause, alias tables, and join tables to build queries. Select columns from a country table and understand execution order in sql.

Learn how to use the as operator or a table alias in the from clause, such as aliasing country as c, to reference its columns more succinctly in SQL.

Explore execution order in SQL Server with joins across multiple tables, using aliases to avoid ambiguity, and ordering results, including finding the religion with the most followers per country.

Create and manage SQL tables with constraints, data types, and identity columns in MS SQL Server. Learn data security, primary keys, and practical table design using SSMS.

Explore how to create and work with temporary tables, including local # and global ## types, and learn their session scope, plus inserting, querying, and indexing.

Create a many-to-many relationship between students and courses with a third table, define primary and foreign keys, apply optional cascade, and generate an entity-relationship diagram.

Explore SQL Server constraints to enforce data integrity and normalization, including primary keys, unique constraints, foreign keys, and check, default, and domain integrity rules.

Master granting, revoking, and denying permissions in SQL Server across server and database levels. Create logins and users, assign roles, and use views to protect sensitive data while controlling access.

Explore inner joins and other join types like right and cross join, and learn to implement them in queries and where clauses. Cover union, intersect, and except operations.

Learn how full outer join combines left and right joins and how cross join produces a Cartesian product, with practical notes on when to use or avoid them for performance.

Learn to use subqueries in the from clause and join tables with a where clause, including left and inner joins, to combine country and mountain data in MS SQL Server.

Learn how to use cross apply in the from clause to join queries and project population data by country, illustrating multiple ways to write the same query.

Learn to create databases and tables, and use insert into, insert select, and select into to add or copy data and create new tables.

Learn to import external data into a table with bulk insert, using delimited files (comma) terminated by carriage returns, and create or populate a new table from the data.

Learn to use the update clause and update with a where clause in MS SQL Server to modify specific records safely, avoiding overwriting rows, with first and last name examples.

Use the output operator in MS SQL Server to log insert and delete dml operations to a separate table, including who performs them.

Discover how to declare and use table variables in SQL Server, assign values with set or select, and when table variables suit single-batch operations over temporary tables.

Learn how to use explicit transactions in SQL Server to safely delete and update data, undo changes with rollback, and experiment without risking production data.

Create user defined functions in SQL Server, including scalar and table-valued types, defining parameters and returns, and applying a function to convert Celsius to Fahrenheit in queries.

Learn how to create stored procedures in SQL Server, including defining input and output parameters, returning data, and handling errors with try-catch, while using temporary tables to encapsulate operations.

Learn to back up and restore databases in SQL Server using both scripts and GUI, including selecting backup locations, options like compression, and restoring from a backup device.

Use Excel as a reporting tool to fetch data from SQL Server, run a SQL script, load sample CIA factbook data, and refresh the report to reflect changes.

Learn to create SSRS reports from SQL Server data by configuring a Visual Studio BI add-on, creating data sources and queries, and deploying to a report server for web access.