Querying SQL Server: Beyond the basics

MP4 | Video: h264, 1280×720 | Audio: AAC, 44.1 KHz, 2 Ch
Genre: eLearning | Language: English + srt | Duration: 99 lectures (21h 6m) | Size: 8 GB
Advanced SQL Server database development and querying

What you’ll learn:
This course will teach you everything you need to know to master advanced SQL Server database development and querying features of this popular DBMS:
You will master the concepts and terms you need for working with relational databases and SQL
You learn how to create a database using SQL statements and its tables using SQL statements or the Management Studio…valuable skill whether you are on the DBA track or not
You learn how to use the Management Studio to work with databases and database objects
How to work with views, scripts, stored procedures, functions, table-value parameters, triggers, transactions, locking, and security
You learn to write SQL statements that retrieve and update the data in a database
Master the details of inner and outer joins, summary queries, and subqueries…it’s all here

Requirements
You need at least 3 months working with SQL Server 2012 or above
Be able to download and install the Express Edition of SQL Server 2012 or above
Be able to install Management Studio
Some programming skills in other procedural languages. Basic understanding of programming concepts such as looping (WHILE), conditional branching (IF…THEN) will be beneficial but not required
You must be willing and prepared to complete all the quizzes and all the intensive hands-on exercises
Good analytical abilities

Description
NOTE: SQL Server is a hands-on product. No matter how much videos you watch, to master SQL Server, you need to get your hands dirty. Our course is not for the casual SQL Server database user who just wants to know how to write simple SELECT queries to extract data for ad-hoc reports. This course is for someone who is keen to master SQL Server databases for a career, or for whom SQL Server is an integral part of day to day activities. This course will take you longer to complete than most SQL courses. We provide a lot of quizzes and non-trivial hands-on exercises to help you master SQL Server databases design and querying. If you can have the time to go through the course and complete all the quizzes and the exercises, you have come to the right place.

YOU WILL NOT BENEFIT MUCH FROM THIS COURSE IF YOU DO NOT DO THE QUIZZES AND THE EXTENSIVE EXERCISES WE HAVE PROVIDED FOR EACH SECTION OF THE COURSE

======================================================================================================================

This course will teach you everything you need to know to master advanced SQL Server database development and querying features of this popular DBMS:

Master the concepts and terms you need for working with relational databases and SQL

Learn how to create a database using SQL statements and its tables using SQL statements or the Management Studio…valuable skill whether you are on the DBA track or not

How to use the Management Studio to work with databases

How to work with views, scripts, stored procedures, functions, table-value parameters, triggers, transactions, locking, and security

How to write SQL statements that retrieve and update the data in a database

How to work with inner and outer joins, summary queries, and subqueries…it’s all here

COURSE STRUCTURE:

We introduce each lesson with a Word document that briefly summarizes the topic of the lesson. This information includes syntaxes if applicable, terms as well as bulleted points of the concepts to be discussed such as the following:

· A role is a collection of permissions you can assign to a user or group of users. By assigning a user to a role, you grant the user all the permissions of the role. You can use roles to simplify user and security administrations.

· SQL Server has built-in, or fixed, roles defined at the server level and at the database level. In addition, you can create user-defined roles for your server or database

· Each role is assigned a set of permissions. For example, the dbcreator role can execute CREATE DATABASE ALTER DATABASE, DROP DATABASE statements. This role can also add new members to the role.

· You use the ALTER SERVER ROLE statement to add a user to or remove a user from a server role. You can also use this statement to rename a user-defined server role.

· The fixed server roles are intended for users who are involved in the administration of the server.

2. VIDEO:

Next, we provide a video demonstration of the topic in a SQL Server Management Studio Query Editor. If applicable, we walk you through the code to achieve a desired result. We suggest that you follow along with us and do the coding if possible

2. QUIZZES:

Each lesson comes with about 25 multiple choice quizzes. We suggest that you immediately repeat the lesson if you do not score at least 22 points before you move to the next lesson. Total of 150 quizzes for the course

2. HANDS-ON EXERCISES:

We provide extensive hands-on exercises for each section of the course, an average of 5 exercises per section. These exercises will take you much longer to complete than the quizzes. They are designed to ensure that you fully understand the topic. Solutions are provided within the course. The database for these exercises are provided for you to install on your computer.

A typical exercise is as follows:

===========================================================

1. The following script uses a derived table to return the date and invoice total of the earliest invoice issued by each vendor. Write a script that generates the same result set but uses a temporary table in place of the derived table. Make sure your script tests for the existence of any object it creates

USE AP;

SELECT VendorName, FirstInvoiceDate, InvoiceTotal

FROM Invoices JOIN

(SELECT VendorID, MIN(InvoiceDate) AS FirstInvoiceDate

FROM Invoices

GROUP BY VendorID) AS FirstInvoice

ON (Invoices.VendorID=FirstInvoice.VendorID AND

InInvoices.InvoiceDate= FirstInvoice.FirstInvoiceDate)

JOIN Vendors

ON Invoices.VendorID=Vendors.VendorID

ORDER BY VendorName, FirstInvoiceDate;

==============================================================

3. TWO EXTRA DATABASES:

You need the AP database for the course and for the first set of exercises. We provide this database for you to install on your instance of SQL Server. In addition, we provide two extra databases (My Guitar Shop and Acotex Training databases) for more exercises. These two databases have the same structure as the main course database (the AP database). The idea is to expose your database and querying skills to different domain of real life databases. A typical exercise in one of the databases is as follows:

====================================================================

1. Create a view named CustomerAddresses that shows the shipping and billing addresses for each customer in the MyGuitarShop database.

This view should return these columns from the Customers table: CustomerID, EmailAddress, LastName and FirstName.

This view should return these columns from the Addresses table: BillLine1, BillLine2, BillCity, BillState, BillZip, ShipLine1, ShipLine2, ShipCity, ShipState, and ShipZip.

Use the BillingAddressID and ShippingAddressID columns in the Customers table to determine which addresses are billing addresses and which are shipping addresses.

Hint: You can use two JOIN clauses to join the Addresses table to the Customers table twice (once for each type of address).

======================================================================================

4. SOLUTIONS TO EXERCISES

We do not provide the solutions to the hands-on exercises based on the 2 additional databases that come with the course. If you need the solutions to these exercises, we expect you to send us your own solution file. Then, when we are satisfied that you have put in the required effort to do the exercises, we will allow you to download the solutions from our website.

GOOD LUCK

Who this course is for
Our course is not for the casual SQL Server database user, who just wants to know how to write simple SELECT queries to extract data for ad-hoc reports
This course is for someone who is keen to master SQL Server databases for a career
You use (or intend to use) SQL Server as an integral part of your day to day activities
If you are coming back to SQL Server after a long lapse, this course is just for you
For those preparing for the SQL Server querying examinations 70-461 & 70-761

Password/解压密码0daydown

Download rapidgator

Download nitroflare

转载请注明：0daytown » Querying SQL Server: Beyond the basics