Getting Started with SQL Server 2017 and .NET Core

Create a university database using SQL Server 2017 to design a data model with student, instructor, course, and enrollment entities, including primary and foreign keys.

Use SQL Server Management Studio to turn the university data model into a database diagram, then create tables—student, instructor, course, enrollment—and define primary keys, identity columns, and relationships.

Build and visualize relationships among student, course, enrollment, and instructor tables by creating primary and foreign keys in an ER diagram, then save, refresh, and generate scripts with ddl.

Generate random seed data for the student and instructor tables using a mock data site, export insert statements, and execute them in SQL Server Management Studio to seed test data.

Generate seed data for sql server 2017 and net core by creating random instructors, courses, and enrollments, using insert statements and foreign keys in a university database.

Generate and save scripts for all tables and data, including insert statements for enrollment, course, instructor, and students, using drop and create scripts and database deployment options.

Master basic SQL queries on the university data model using SELECT, FROM, and ORDER BY. Limit results with TOP, select specific columns, and order by ID ascending or descending.

Learn to use table and column aliases in SQL Server queries, rename results with as, reference tables with aliases, and format queries for readability while selecting all or specific columns.

Filter data with the where clause using like and wildcards to match course names starting with c, and use equals and in to retrieve exact or multiple records.

Master selecting from the course table using operators for id comparisons, and combining conditions with and or, while using like with wildcards to filter names starting with c.

Learn to use SQL Server 2017 aggregate functions, count, max, min, and avg, on enrollment and course tables, with aliases and casting for precise averages.

Join the course and instructor tables through a primary key-foreign key relationship using aliases. Select course id, course name, units, and instructor first name, last name, and email.

Explore using the group by keyword to summarize joined enrollment, student, and course data, counting courses and summing units to reveal each student's totals.

Explore common table expressions (CTEs) in sql server 2017, using a course subquery to identify students with the lowest or highest units and join enrollment and students tables.

Create a user defined view by combining columns from multiple tables, such as instructor and course, using an inner join to produce a concise summary report.

Create a scalar valued function to format a student name by combining first and last names. Use it inline in a select statement.

Create a stored procedure that accepts a student id, uses joins across student, enrollment, and courses, and returns multiple result sets with student and course information.

Explore current data access layer strategies for SQL Server with .NET Core, contrasting classic ADO.NET approaches with ORM using Entity Framework Core, covering code-first and database-first workflows.

Install the latest .NET Core framework and the .NET Core SDK, then install Visual Studio Community Edition to build .NET Core apps and connect to SQL Server.

Explore using .NET Core with ADO.NET to read and write data from SQL Server in a console app, configure the connection string from a JSON file, and display results.

Create an appsettings.json file, add a connection string, and paste the connection string from SQL Server object explorer to connect to the university db with integrated security.

Use dotnet core ado.net configurationbuilder to read appsettings.json by building a configuration, setting the base path with directory.getcurrentdirectory, copying file to output directory, and retrieving the connection string section.

Demonstrate a .NET Core ADO.NET workflow: open a connection, execute a sql command, fill a data table with a sql adapter, and display student records in the console.

Add SQL Server core, EF Core tools, and EF Core design packages to the console project, then use scaffold DbContext to generate models and a DbContext from the database.

Scaffold an EF Core database context from a SQL Server database using scaffold-dbcontext, generating the university DV context and related course and student models with proper relationships and foreign keys.

Use EF Core in a database-first workflow with the university database context to query the students table and print id, first name, last name, and email.

Explore dapper, a high performance micro orm that maps query results to objects, like entity framework core. Install dapper, open a sql connection, and fetch students with a simple query.