Learnsqlwithsqlserver2014 Course

Create a Table and Write a Simple Query and Display the Results Preview 13:58

Learning Results

1) Understand how to create a table

2) Understand how to select records from a table

3) Understand that a database is a complex construct consisting of multiple objects

Simple Queries

Check Your Understanding of This Lecture

Write a Query with a Where Restriction Preview 04:25

Learning Results

1) Understand how to write a query

2) Understand how to restrict the columns chosen

3) Understand how to restrict the rows chosen

Adding a Simple Where Restriction

Write a Query with a Where and an Order By Preview 09:08

Learning Results

1) Understand how to use the order by clause

2) Understand how to select a row in which two cells match a given condition

Order By Clauses

Filtering with Where and Between Preview 09:05

Learning Results

1) Understand how to use the between clause

Using Between

Aliasing Columns Preview 11:03

Learning Results

1) Understand how to write a query with column aliasing

2) Understand how to order by multiple columns

If there are issues with ordering by multiple columns, the link below gives additional detail.

http://stackoverflow.com/questions/2051162/sql-multiple-column-ordering

Aliasing Columns

Aliasing Computed Columns Preview 10:38

Learning Results

1) Understand how to add computed columns

2) Understand how to alias computed columns

3) Understand how to use computed columns in various statement types

Computed Values

Use The Query Designer Preview 06:40

Learning Results

1) Understand how to use the query designer to build queries fast

2) Understand how to modify queries using the designer

3) Understand that the designer has its own way of formatting queries

Query Designer

Make an ASP.NET Web Page with SQL Server Preview 19:24

Learning Results

1) Understand how to incorporate basic SQL queries into dynamic web pages

ASP.NET Pages

Summarize Blocks of Records with Group By Preview 11:30

Learning Results

1) Understand how to group records

2) Understand how to combine grouping, filtering with where, and ordering

Group By

Having Clause Preview 07:10

Learning Results

1) Understand how to filter grouped results

Having Clause

Select Into Preview 08:03

Learning Results

1) Understand how to copy one table into another

2) Understand how to copy existing columns

3) Understand how to copy computed columns into another table

Select Into

Add Multiple Parameters to an ASP.NET Page Preview 19:26

Learning Results

1) Understand how to add and use multiple parameters

2) Understand how to make, design and modify databases from within Visual Studio

Multiple Parameters

Table Creation and Data Insertion Preview 08:18

Learning Results

1) Understand how to create a table manually

2) Understand how to insert data into a table

3) Understand how the system formats some types of data automatically

Table creation and Data Insertion

Updating Records Preview 06:19

Learning Results

1) Understand how to use the update statement

2) Understand how to use the delete statement

Update Records

ASP.NET Page with Updating and Deleting Preview 16:49

Learning Results

1) Understand how to create a realistic application of updates, inserts, deletes,

Updating, Deleting and Inserting Through Web Pages

1st Normal Form Preview 12:12

Learning Results

1) Understand how to bring a table into 1st normal form

2) Understand how to remove duplicates

3) Understand how to make a composite key

4) Understand how to make an index and how the index helps in speeding up access to data

5) Understand what happens when you try to make a key using information that repeats across records

First Normal Form

Second Normal Form Preview 09:57

Learning Results

1) Understand how to bring a table from 1st normal form to second normal form

2) Understand that in 2nd normal form, the columns must be such that each nonkey field depends on all the components of the key

Second Normal Form

Third Normal Form Preview 08:25

Learning Results

1) Understand how to produce the third normal form

2) Understand that in this form, each nonkey field must depend on the key, the whole, and nothing but the key

Inner Join Preview 11:49

Learning Result

1) Understand inner joins as the intersections of tables

2) Understand how to visualize the concept of an inner join

3) Understand how to implement an inner join in code

Inner Joins with Editor Window and Where Statements Preview 08:12

Learning Results

1) Understand how to form an inner join using the editor window

2) Understand how to subset the data

Inner Joins

Left Join Preview 10:45

Learning Results

1) Understand the concept of a left join

2) Understand how to implement a left join in code

3) Understand the execution plan and actual behavior of a left join

5) Understand how the nonmatching records from the table on the right are dropped.

6) Understand how records from the left are null extended

Left Joins with Editor and Where Statement Preview 15:24

Learning Results

1) Understand how to write a left join with the editor window

2) Understand how to filter the results of a left join with a where

Left Joins

Right Join Preview 10:14

Learning Results

1) Understand the concept of a right join

2) Understand how to implement a right join in code

3) Understand how nulls are added during this process

4) Understand how nonmatching records from the table on the left are dropped

Right Joins with Query Editor and Filtering Conditions Preview 10:19

Learning Results

1) Understand how to code a right join with the query editor

2) Understand how to filter data using a where statement

Right Joins

Full Joins with The Query Editor Preview 06:42

Learning Result

1) Understand how to construct a full outer join

2) Understand how to add where statements

3) Understand the full join graphically

Full Joins

Connect Gridview to Tables with C# Preview 19:38

Learning Result

1) Generate four new tables using the query designer

2) Save the tables inside the database

Create a Query Editor with C# Preview 25:05

Learning Results

1) Create a website

2) Connect the databases to the grid views

3) Connect a database to a gridview programmatically

4) Display all the results

Question: After watching this video, can you draw a schematic of the process illustrated?

Create a Table Suited For XML Output Preview 12:41

Learning Results

1) Understand how to build a table for XML usage

2) Understand how the columns from a table designed for XML map to the names of elements

2) Understand how the columns from a table designed for XML map to the attributes of an element

Create a Page With a Control That Connects to the XML Table Preview 11:00

Learning Results

1) Understand how to save the XML output

2) Understand how to configure a web page to use an XML file as a source for controls

3) Understand that ASP.NET controls are sent to a browser as HTML

XML Tables

In Logical Operator Preview 08:40

Learning Result

1) Understand how to use the 'IN' logical operator

2) Understand that this operator returns either true or false

Where with Like Preview 10:45

Learning Result

1) Understand how to use the Like operator

2) Understand how to use the and operator in more detail

3) Understand how to use the getdate() function

Like, And and In

Scalar Functions Preview 12:20

Learning Results

1) Understand the concept of a function

2) Understand how functions operate

3) Understand how to nest functions

4) Understand how to apply functions to do something practical

Scalar Functions

Aggregate Functions Preview 12:04

Learning Results

1) Understand how to use aggregate functions

2) Understand that these work with grouped data

3) Understand how to next function calls

Aggregating Functions

Summarzing Records with Over Preview 16:58

Learning Result

1) Understand how to call functions on partitioned data using the over statement

2) Understand how to embed over statements in function calls

Question: Can you draw a picture to illustrate the functionoity of an over statement?

Student Observation: Paul Staley observed that you can also just write "order by 'Total'" at end

Over Statement

Subqueries in Select Clauses Preview 06:30

Learning Result

1) Understand how to write a subquery as part of a select statement

2) Understand how to apply a function inside a subquery

Subqueries in Selects

Subqueries in From Preview 13:04

Learning Results

1) Understand how to place a subquery inside a from

2) Under the process of the query visually

3) Understand how to use aggregate functions inside subqueries

Write and Execute a Simple Stored Procedure Preview 08:22

Learning Results

1) Understand how to create a stored procedure

2) Understand how to call a stored procedure

3) Understand how to alter a stored procedure

Simple Stored Procedures

Part 1: Write a Parametrized Procedure Preview 17:37

Learning Results

1) Understand how to add parameters to a stored procedure header

2) Understand how use parameters inside the body of a query

3) Understand how to call a stored procedure with parameters

Part 2: Asp.NET Page Powered by Parametrized Store Procedure Preview 18:05

Run a web page powered by a parametrized stored procedure.

Procedures with Parameters

Add IF/Else Blocks Preview 13:11

Learning Result

1) Understand how to construct an if/else block

2) Understand the logic of an if/else blocks

3) Understand how to express logically equivalent ideas in different ways

4) Understand how to use the debugger to trace the execution of a query

Question: Can you draw a schematic to represent the logic of an if/else block?

If/Else Blocks

Case and When Statements with Functions Preview 16:50

Learning Result

1) Understand how to incorporate case/when into a stored procedure

2) Understand how to incorporate function calls into case/when

3) Understand how to use the floor function

Case and When

While Blocks, Variables Preview 13:32

Learning Result

1) Understand how to declare and use variables

2) Understand how to cast from one data type to another

3) Understand how to incorporate if/else blocks into while blocks

Note: Please put the variable increment line after the if/else block

While, Variables

Stored Procedures with Output Parameters Preview 09:24

Learning Results

1) Understand how to fill a table

2) Understand how to return values from a stored procedures

3) Understand how to use values returned from a stored procedure

Try/Catch Error Handling Preview 10:24

Learning Results

1) Understand how to construct a try/catch construct

2) understand how the try/catch behaves

3) Understand how to decrement variables

4) Understand that try/catch is a general construct that exists in most modern programming languages

5) Understand how to create and delete temporary tables

Try/Catch

Comparing Values Between Records with the Lag Function Preview 15:39

Learning Results

1) Understand how to use the lag function to compare values between records

2) This function works in SQL Server 2012 and up.

Common Table Expressions Preview 09:11

Learning Results

1) Understand how to create and use a temporary named resultset

2) Understand how to increment a date by adding a counter variable to getdate()

3) Understand how to extract the month from a date

4) Understand how to formata name field by using substring and upper and lower functions

Using the Lead Function with Grouped Data and CTE's Preview 11:35

Learning Results

1) Understand how to use the Lead function with data from a CTE

2) This function works in SQL Server 2012 and up

Lead and Lag Functions

CTE with Inner Join and Aggregating Functions and CSV Files Preview 16:00

Learning Results

1) Understand how to combine CTE's, inner joins, and aggregating functions with while loops and table creation

Nesting Functions and Pivoting Tables Preview 11:04

Learning Results

1) understand how to nest functions correctly and understand the results

2) Understand how to pivot tables to get a different view of the same data

Nesting Functions and Pivoting Tables

Scalar Valued Functions Preview 13:00

Learning Results

1) Understand how to create a user defined function

2) Understand how to call a user defined function

3) Understand how to format the results of a user defined function

User Defined Scalar Functions

Table valued Functions Preview 15:06

Learning Results

1) Understand how to create a function that returns a whole table

2) Understand how to insert into a table that is going to be returned

3) Understand how to call a tabled valued function

Table valued functions

Table Variables Preview 12:26

Learning Results

1) Understand how to declare a table variable

2) Understand how to set the values of a table variable

3) Understand how to use the char function to convert numbers to characters

4) Understand how to copy the results of a table variable

Table Variables

ASP.NET/SQL Server/LINQ Preview 22:57

Understand how to use SQL Server, ASP.NET, C# and Linq together. 

Write a View Preview 12:38

Learning Results

1) Understand how to create a view

2) Understand how to embed complex logic into column defs.

Views

Write a Trigger Preview 13:19

Learning Results

1) Understand how to write a trigger

2) Understand how to execute a trigger

3) Understand how to connect two controls in a page with a trigger

Triggers

Begin Transaction and Committ Transcation Preview 14:45

Learning Results

1) Understand how to begin a transaction

2) Understand how to commit a transaction

3) Understand how to use the global transaction count variable

Begin and Commit Transcation

Save and Rollback Transactions Preview 08:30

Learning Results

1) Understand how to reverse a transaction

2) Understand how to get the number of rows affected by a transaction

3) Understand how to generate random values

Rolling Back and Saving Transactions

Make the Table, and Write the Stored Procedure Preview 19:41

Learning Results

1) Make a table with identity and autoincrement features

2) Write a stored procedure with a transcation to ensure data consistency

Make a Simple Interface Preview 19:57

Learning Results

1) Understand how to add validation to asp.net controls

2) Understand the purpose of regular expressions

Write the C# Preview 30:15

Add C# Code

Improve Interface with CSS Preview 14:24

Learning Results

1) Improve interface

Add JQuery, Basic Example Preview 14:24

Refine the JQuery and Understand IIS Express Preview 20:57

Refine the jQuery and understand how to use IIS Express better

Add AJAX and a Check Box Preview 23:56

Add Ajax and a Check box

Port To IIS Preview 15:48

Port site to IIS

Use jQuery To Make Interface Draggable, Droppable and Saveable Preview 19:14

Understand how to make the interface draggable and how to save its position between page loads.

Adding Logins and Users Preview 08:18

Learning Results

1) Understand how to add a login

2) Understand how to add a user

3) Understand how to map users and logins

Database Roles with Users Preview 09:58

Learning Results

1) Understand how to add multiple logins

2) Understand how to add multiple users

3) Understand how to add multiple users to a single database roles

4) Understand how to add securables to a role

Backups, Restores, Excel Preview 10:11

Learning Results

1) Understand how to backup and restore databases

2) Understand how to connect Excel directly to SQL Server

Backup Device and SQL Server Log Preview 10:43

Backup devices and logs

Create a site on IIS and an HTTP page Preview 06:41

Create a table in SQL Server and connect the page with a Detailsview Preview 13:33

Add CSS and jQuery to make elements draggable, droppable and saveable Preview 15:52

Make element expandable and add aJAX Preview 18:35

Add sales table with nested while loops Preview 21:06

Add trigger for adding sales records Preview 09:53

Add foreign key constraint for deleting records Preview 07:53

Synchronize Gridview and Details View Preview 16:34

Add a summary table Preview 18:01

heloo

Add theme switching with jQuery Preview 10:32

Add a sticky Preview 16:03

Add a jQuery stick to the site.

Printing GridViews to printers, Google Drive, and the cloud Preview 23:24

Learning Results

1) Understand how to add a div for printing

2) Understand how to write JQuery code for printing

Final Project Review Preview 21:06

What to do next Preview 00:08