SQL Querying - Basic Course



Course Details:

Length: 2 days

Price: $990/person (USD)

Bundle & Save: View Bundle

Group Price: Request Quote

Training Reviews

Course Features:

Live Instructor Teaching

Certificate of Completion

Courseware: Print

Free 6 Month Online Retake

Hands-On Learning?: Yes

Software Lab Included?: Yes

Delivery Methods:

Live Online

Individuals and Groups
@ Your Location

Onsite for Teams

Group Teams
@ Your Organization

This is an instructor-led course. It is taught by an instructor live online or at organizations for groups.
For team training, we can teach onsite at your office or private live online.

 

Course Overview

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.


Register Early: Registration Deadline is 2 Weeks Prior to Class Start.


Course Notes

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.

Suggested Follow-Ons

SQL Querying - Advanced
MySQL Workbench: Data Modeling & Development

 

Course Topics

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


Course FAQs

What Are the Available Class Formats?

This course is taught by a live instructor and is available in two class formats:

  1. Live Online for Individuals
  2. Onsite/Online for Private Groups

What Are Database Concepts?

Database concepts are an essential part of understanding how to use databases and data processing tools effectively. A database is a collection of information that can be organized, accessed, manipulated, and updated systematically. The database concept is used to define the overall structure, characteristics, and organization of the database. Database concepts also include the design principles that guide the development of the database.

Database concepts include data modeling and normalization, relational databases, object-oriented databases, queries, and query optimization. Data modeling is the process of creating a model or prototype of the data stored in a database system. Normalization is a technique used to minimize redundancy in data structure while maintaining its integrity. Relational databases are structured in a way that data is stored and retrieved based on relationships between elements. Object-oriented databases are designed to store objects instead of traditional data structures. Queries are commands that allow users to extract information from the database, and query optimization is used to improve the speed and efficiency with which queries are processed.

Database concepts form the foundation of all successful database systems, and understanding them is key to developing efficient and reliable databases. Learning these concepts is a must for anyone looking to work with databases.

What Are SQL Select Queries?

SQL select queries are a powerful tool used to retrieve data from databases. The query language allows you to specify the type of data you would like to extract, as well as any conditions that must be met before the data is returned. Select queries can also be used to perform calculations on retrieved data, such as sorting or averaging values. By taking advantage of various operators and functions available in SQL, you can create powerful select queries that provide the exact data you need for your application. With careful use of select queries, developers can optimize their databases to minimize redundancy and maximize performance. This makes them an invaluable tool for both database administrators and application developers alike.

Additionally, SQL select queries are often used to join data from multiple tables, allowing you to build complex reports and analytics. This allows users to extract data that would otherwise be difficult or impossible to access by hand. By combining various query elements, developers can create powerful queries that retrieve only the exact information they need.

Overall, SQL select queries are a versatile tool that allow developers to quickly and accurately retrieve data from databases. They are an essential part of any database application, allowing developers to optimize performance and create powerful reports and analytics. With the right knowledge, select queries can be used to great effect in a wide range of applications.

What Are SQL Built-In Functions and Expressions?

SQL built-in functions and expressions are used to manipulate data in the database. These functions can be used in SELECT statements, UPDATE statements, DELETE statements, or even in WHERE clauses. The functions allow you to perform arithmetic calculations, as well as string manipulation and validation tests. Common operations such as aggregate counting, finding the maximum value, or finding the average value can also be accomplished through these functions. SQL built-in functions and expressions are an essential part of querying the database for data manipulation and analysis purposes. They offer a variety of operations which make it easier to perform complex operations efficiently and accurately. With the help of these features, developers can construct powerful queries that yield the desired output in a fraction of the time. Furthermore, they help to ensure query reliability and reduce potential errors that can lead to data loss.

SQL built-in functions and expressions are an invaluable addition to any database querying system. By taking advantage of these features, developers can build powerful and efficient queries which yield quality results quickly. They allow for the manipulation of data in ways that would otherwise be impossible or very difficult. Ultimately, they provide a great deal of flexibility and power when it comes to working with database queries.

What Are SQL Table Joins?

SQL table joins are a way to combine data from two or more tables into one result set. Joins allow you to query related information from multiple tables in a single statement, which can be incredibly powerful when pursuing complex queries. By using various types of join operations, you can easily retrieve data that would otherwise require several individual statements and operations. Depending on the type of join you use, you can also modify or delete data in multiple tables at once without having to execute separate statements for each.

Inner Joins are the most commonly used type of SQL join and are used to combine rows from two or more tables based on a common field. An inner join returns only the rows that match the criteria specified in the ON clause and can be further restricted using a WHERE clause, allowing for greater control over what data is returned.

Outer Joins are used to return all the data from one table combined with data from the other table that meets the criteria specified in the ON clause. Outer joins are different from inner joins as they will return all rows from one or both of the tables, regardless of whether a match exists or not.

Cross Joins create a Cartesian product between two or more tables, resulting in all possible combinations of the data. Cross joins are the least common type of join used and should be employed with caution as they can produce very large result sets.

Self-Joins allow you to join a table to itself, allowing data from within the same table to be compared and combined. Self-joins are often used for queries that involve hierarchical relationships such as parent-child or manager-employee.

SQL joins are an extremely powerful and versatile tool for use with relational databases, allowing users to create intricate queries without having to execute multiple statements. With the ability to combine data from multiple tables into one result set, SQL table joins can help make your database operations more efficient and effective.

What Is SQL Grouping and Summarizing?

Grouping and summarizing data in Structured Query Language (SQL) allows users to quickly view large amounts of data, identify patterns, and gain insights.

Grouping is a process where data is arranged into subsets based on specific criteria. For example, grouping customer records by country can help an organization identify geographic trends or preferences.

Summarizing is a process where the data is summarized into single values, such as averages and totals. Summarizing can be used to identify trends in sales or customer usage.

SQL grouping and summarizing provide users with a powerful way to quickly analyze data and make decisions based on the insights they uncover. With the help of these features, organizations can gain valuable insights into their data and make informed decisions based on the results.

What Are SQL Subqueries and Unions?

SQL subqueries and unions are powerful ways to query data from a relational database. A subquery is a SELECT statement that is nested within another query, while a UNION clause combines the results of two or more queries into one result set. Subqueries can be used in various parts of an SQL query such as the WHERE clause, the FROM clause, and the SELECT clause. UNION statements are used to combine the results of two or more queries into a single result set.

Subqueries and UNIONs can be used to create complex SQL statements, allowing users to retrieve data from multiple tables, perform calculations on data values, join multiple tables in one query, and apply filters to the result set. The use of subqueries and UNIONs can also improve query performance by reducing the number of SQL statements that need to be executed to retrieve the desired data.

To properly utilize SQL Subqueries and Unions, knowledge of both Structured Query Language (SQL) and relational databases is essential. Knowing how to write properly structured SQL queries, understanding the differences between various types of joins, and being familiar with database design best practices are all useful skills for a person wishing to use advanced SQL features such as Subqueries and Unions.

By leveraging the power of SQL Subqueries and Unions, data analysts can create complex queries that greatly improve the speed and accuracy of their work. With these powerful tools, questions that would otherwise require multiple queries can now be answered with one statement. As such, SQL Subqueries and Unions are invaluable for anyone dealing with large amounts of data or needing to analyze complex datasets.

What Are SQL Where Clauses?

SQL WHERE clauses are used to filter the results of a SELECT statement. They provide criteria for which rows should be included in the result set of a query. This can be used to select specific rows with certain conditions or even exclude certain rows from the result set. The WHERE clause is an important part of SQL and it can help make queries efficient, reliable, and secure.

It's important to understand how the WHERE clause works to create effective queries. The syntax for a WHERE clause is very simple: it consists of an expression that is evaluated for each row in the table being queried. If the expression evaluates to true for a given row, then that row is included in the result set; otherwise, it is excluded. For example, you might use a WHERE clause to select all rows with a certain value in a given column or to select all rows within a certain range of values.

Additionally, the WHERE clause can be used in conjunction with other SQL clauses such as ORDER BY and GROUP BY. This allows you to further refine your queries to get the most out of your data.

By understanding how the WHERE clause works, you can create powerful and efficient SQL queries.



Start your training today!