SQL Querying - Basic

In this course you will compose basic SQL queries to retrieve desired information from a database. You will explore database concepts, write simple SELECT queries, use built-in functions and expressions, join tables, build subqueries and unions, and finally perform some advanced where clauses.

This course is taught using Microsoft SQL Server, but students using versions of SQL such as Oracle or MySQL will benefit by attending. The courseware and lab environment provided will be Microsoft SQL Server.
We have additional SQL Courses if this course doesn't fit your needs:

• MySQL Workbench: Data Modeling & Development
• SQL Querying - Advanced
• SQL Querying - Basic

 Length: 2 day(s)
 Daily Start Time: 10:00 Eastern Time
 Daily End Time: 17:00 Eastern Time
 Registration Cutoff: 15 Days Prior to Class Start
 Price: $990.00 (U.S. Dollars)
 Location: 1900 West Park Drive, Suite 280, Westborough, MA 01581
 Training Format:

Hands-on, Live Online Training with a Remote Instructor

Certstaffix® Training classes are instructor-led live online training you attend in a local computer lab or from your home/office. Our live online instructors teach you from a remote location while being able to interact with students as in a traditional classroom setting. Instructors can view student progress and take control of their PC to provide direct assistance. Students can see the instructor's presentation as well as voice questions directly to the instructor and participate in class discussions.

Our Remote Instructor-led classes offer:

• A real, live instructor teaching you from another location

• Hands-on learning with the software you are being taught

• Interaction with all students in the class at other locations

• Easy assistance from the instructor:

• To ask a question, you simply speak and all students and the instructor can hear you

• They can see your computer screen with permission

• They take control of your computer if you get stuck with permission

Two Ways to Attend:

1. Attend From Your Home or Work

You can attend any of our public classes from your home or work - all you need is a high-speed internet connection. We provide access to any needed software and lab environments during your class. We’ll ship you any course material about 1 week prior to your class start date.

2. Attend From One of Our Computer Labs

You can also attend any of our public classes at our computer lab location at 1900 West Park Drive, Suite 280, Westborough, MA 01581. You’ll get access to a fully set up learning environment for you to walk into and start class. We provide a computer and access to any needed software and lab environments. We’ll ship you any course material about 1 week prior to your class start date.

Questions?

Feel free to call or email us if you have questions about course content or how our training method works:

Call Us: 888-467-4387
Email Us

Course Outline:

Exploring Database Concepts
What Is SQL Server?
Databases in the Cloud
Service vs. Application
Database as Container
Data Is Stored in Tables
Data Types
Normalization
Understanding Indexes
Database Schemas

Writing Simple SELECT Queries
Using the SELECT Statement
Selecting a Literal Value
Retrieving from a Table
Generating a Select-List
Mixing Literals and Column Names
Filtering Data
Adding a WHERE Clause
Using WHERE Clauses with Alternate Operators
Using BETWEEN
Using BETWEEN with NOT
Filtering on Date and Time
Using WHERE Clauses with Two Predicates
Using the IN Operator
Working with NULL
Sorting Data
Thinking About Performance
Taking Advantage of Indexes
Viewing Execution Plans

Using Built-In Functions and Expressions
Expressions Using Operators
Concatenating Strings
Concatenating Strings and NULL
CONCAT
ISNULL and COALESCE
Concatenating Other Data Types to Strings
Using Mathematical Operators
Using String Functions
RTRIM and LTRIM
LEFT and RIGHT
LEN and DATALENGTH
CHARINDEX
SUBSTRING
CHOOSE
REVERSE
UPPER and LOWER
REPLACE
Nesting Functions
Using Date and Time Functions
GETDATE and SYSDATETIME
DATEADD
DATEDIFF
DATENAME and DATEPART
DAY, MONTH, and YEAR
CONVERT
FORMAT
DATEFROMPARTS
EOMONTH
Using Mathematical Functions
ABS
POWER
SQUARE and SQRT
ROUND
RAND
Logical Functions and Expressions
The CASE Expression
IIF
COALESCE
Administrative Functions
Using Functions in the WHERE and ORDER BY Clauses
The TOP Keyword
Thinking About Performance

Joining Tables
Using INNER JOIN
Joining Two Tables
Avoiding an Incorrect Join Condition
Joining on a Different Column Name
Joining on More Than One Column
Joining Three or More Tables
Using OUTER JOIN
Using LEFT OUTER JOIN
Using RIGHT OUTER JOIN
Using OUTER JOIN to Find Rows with No Match
Adding a Table to the Right Side of a LEFT JOIN
Adding a Table to the Main Table of a LEFT JOIN
FULL OUTER JOIN
CROSS JOIN
Self-Joins
Thinking About Performance

Grouping and Summarizing Data
Aggregate Functions
The GROUP BY Clause
Grouping on Columns
Grouping on Expressions
The ORDER BY Clause
The WHERE Clause
The HAVING Clause
DISTINCT Keyword
Using DISTINCT vs. GROUP BY
DISTINCT Within an Aggregate Expression
Aggregate Queries with More Than One Table
Aggregate Functions and NULL
Thinking About Performance

Building on Subqueries and Unions
Writing Subqueries
Using a Subquery in an IN List
Using a Subquery and NOT IN
Using a Subquery Containing NULL with NOT IN
Using EXISTS
Using CROSS APPLY and OUTER APPLY
Writing UNION Queries
Using EXCEPT and INTERSECT

Advanced WHERE Clauses
Using CONTAINS
Using Multiple Terms with CONTAINS
Searching Multiple Columns
Using FREETEXT
Thinking About Performance

Public Class Policies and Conditions

When you enroll into our public classes, on the registration form, you have acknowledged and agreed to our public class policies and conditions. This includes cancellation/reschedule/no show policies that are time sensitive.

I understand:

• Certstaffix® Training classes are live, hands-on training with your instructor teaching from a remote location. If not familiar with this format see our Remote Instructor-led page.

• Certstaffix® Training is primarily a Business-to-Business training provider. In certain locations, we cannot accept enrollments from students who's tuition fees are being paid by themselves, not their employer. Read full details here

• Payment: If paying by check - Payment is due 15 days from this registration request. To request alternative payment terms, you must call 888-467-4387. If you have submitted a late registration, less than 15 days before your class start date, we require immediate payment. If we have not received your payment by the due date, we may reschedule/cancel your enrollment. We will inform you prior to making any changes to your registration.

• I can cancel or reschedule my registration without penalty or charge provided I give Certstaffix® Training notice of 14 days or more before the first day of my class.

• If I request to cancel or reschedule my registration less than 14 days before the first day of my class, I will be charged 100% the course fee and am not entitled to a refund. I have one (1) opportunity to use Certstaffix Training Make-Up policy to have those funds applied to a later class date.

• If I no show to my class, I will be charged 100% the course fee and am not entitled to a refund. I have one (1) opportunity to use Certstaffix® Training Make-Up policy to have those funds applied to a later class date. If attending in a computer lab: an additional seat fee of $100 per day will be charged to attend my makeup session.

• I cannot change my class location 14 days or less before the first day of class because Certstaffix® Training has shipped training materials and provisioned resources. Location changes requested prior to that timeframe are subject to availability and may incur an additional charge.

• Should Certstaffix® Training need to cancel my class due to insufficient enrollment, or postpone it due to events beyond their control, Certstaffix® Training will notify registered students as soon as possible. In such cases, the student may reschedule to a future class date at no additional charge or receive a refund for any money on account relating to that registration.

• Travel arrangements and costs are the sole responsibility of the student. Certstaffix® Training suggests obtaining refundable reservations. Certstaffix® Training classes are confirmed approximately 14 days before the start of the class. We cannot guarantee class commitments before that window of time. Certstaffix® Training will not be responsible for any cancellation costs incurred, including but not limited to, airline/mass transit tickets, hotel reservations and so on.