SELECT THE CLASS AND STAR LEARNING C Sharp
We'll start today by installing the SQL Server Management Studio (SSMS) Express, the preferred way to work with the Structured Query Language. Usually a simple installation wouldn't require a video, however in this case there are a couple of gotcha's when installing SSMS side-by-side with Visual Studio 2010 Express Edition.
This video demonstrates how to open and execute a SQL script containing potentially dozens or hundreds of lines of code. In our particular situation, Bob wants you, the viewer, to have the same database tables and a thousand rows of data that he's working with, thus this crucial step in staying synced.
Curious how (1) Bob created 3000 rows of fake data, and (2) how he scripted the tables and data into a simple script file … in less than 6 minutes? This video shows two third-party tools that make simple work of both tasks. Note: this is not part of the curriculum … Bob is just demonstrating this in case you ever have a similar need.
In this video we look at how to write a simple SELECT statement in T-SQL, explaining the syntax and variations like selecting only certain columns, aliasing column names using the 'as' keyword, formatting columns and more.
Continuing our work with T-SQL, we explore the INSERT statement syntax to add rows of data to a given database table. Additionally, Bob shows a second form of the syntax "INSERT INTO" which combines a SELECT and an INSERT.
This video comes with a warning label: be careful! Using the UPDATE statement allows you to change one more more rows of data with new values, but can be very destructive. Bob outlines the proper way to execute UPDATE using a copy / backup of the database and a script that is executed on a live production server only after it's been thoroughly tested.
The DELETE statement is simple, but lethal. This video shows you the syntax, but moves past that to explain a different technique for removing data that has a built-in safeguard against accidental deletes.
In the case of the SELECT, UPDATE and DELETE statements, the WHERE clause is crucial to working with only those rows of data the meet certain criteria. In this video Bob demonstrates a dozen WHERE operators such as LIKE, BETWEEN, greater than, greater-than-or-equal-to, not-equal-to, IS, IN and more.
When SELECTing data, the order in which the data is sorted can often be important, and this video demonstrates the ORDER BY statement and it's associated operators.
Till now, we've only worked with T-SQL in the context of a single table. What if you need to perform a join (based on the primary / foreign key relationship between the tables) to select some columns from multiple tables? Bob demonstrates the basic syntax of an INNER JOIN, and shows how to add table aliases, and how to add more than two INNER JOINs in a single statement.
Day 7's homework assignment requires you to write five T-SQL statements based on the Customers and Orders table we used throughout today's videos. You'll exercise virtually everything you learned today, including TOP, LIKE, BETWEEN, INNER JOIN and more.
Got stuck on a query or two? Bob demonstrates step-by-step how to work through and solve the T-SQL queries.
0 comments:
Post a Comment