Beginner to T-SQL [Full Course]
Pragmatic Works・149 minutes read
Mitchell Pearson from Pragmatic Works is hosting a live event on T-SQL basics, covering SQL queries, functions, and management tools. Participants can explore SQL fundamentals, download scripts and guides, and enhance their data skills in a three-hour session.
Insights
- SQL is essential for data professionals to interact with databases, enabling tasks like data insertion, retrieval, and editing.
- SQL queries can manipulate data efficiently by using functions like concatenation, ordering, and mathematical operations within the query.
- The importance of handling null values in SQL queries is emphasized, showcasing methods like "is null" and "coalesce" to manage unknown values effectively.
- Filtering data in SQL involves utilizing operators like "equals," "greater than," "less than," and functions like "like" and "in" for precise data retrieval.
- Understanding joins in SQL is crucial for merging data from multiple tables, with inner joins being fundamental for combining related data effectively.
Get key ideas from YouTube videos. It’s free
Recent questions
What is SQL and its importance in data management?
SQL, or Structured Query Language, is a programming language used to manage and manipulate relational databases. It is essential for data professionals to interact with databases, allowing them to insert, retrieve, and edit data efficiently. SQL databases store and organize organizational data, replacing outdated paper-based systems. SQL plays a vital role in data extraction, transformation, and loading processes, particularly in cleaning and transforming raw data for reporting purposes. Understanding SQL basics is crucial for professionals working with data to ensure accurate and efficient data management.
How can I connect to Azure SQL databases using SSMS?
Connecting to Azure SQL databases using SQL Server Management Studio (SSMS) involves entering server names, login credentials, and database names. SSMS is essential for interacting with SQL databases and can be downloaded for free from aka.ms/ssms. Once SSMS is installed, users can input the necessary information to establish a connection to Azure SQL databases. This connection allows data professionals to query, manage, and analyze data stored in Azure SQL databases effectively.
What are some common SQL functions for data manipulation?
SQL offers a variety of functions for data manipulation, including mathematical expressions, string functions, and date functions. Basic mathematical expressions like addition and multiplication can be performed within SQL queries to calculate values. String functions like left, right, upper, lower, and replace allow for text manipulation and formatting. Date functions such as DATEADD, DATEDIFF, and end-of-month functions enable users to work with date values effectively. Understanding and utilizing these functions in SQL queries is essential for data professionals to manipulate and analyze data accurately.
How can I handle null values in SQL queries?
Null values in databases represent unknown or missing data, which can impact calculations and query results. Handling null values in SQL queries is crucial to ensure accurate data manipulation and analysis. Functions like "is null" and "coalesce" can be used to address null values in SQL queries. The "is null" function replaces null values with specified expressions, while "coalesce" selects the first non-null value from a list. By accounting for null values in SQL queries, data professionals can avoid errors and ensure the integrity of their data analysis.
What is the significance of joins in SQL queries?
Joins in SQL are used to combine data from different tables based on a common column, such as customer_id, to retrieve related information. Inner joins are commonly used to merge data from multiple tables, providing a comprehensive view of the data. When joining tables with the same column names, aliasing the tables can prevent ambiguity and ensure the correct data is retrieved. Different types of joins, including inner joins, left outer joins, and right outer joins, offer flexibility in combining data from multiple tables. Understanding and utilizing joins in SQL queries is essential for data professionals to gather comprehensive insights from relational databases.
Related videos
Summary
00:00
"Essential T-SQL Event with Mitchell Pearson"
- Mitchell Pearson from Pragmatic Works is hosting a three-hour live event on T-SQL, a crucial tool for data professionals.
- Mitchell Pearson has been with Pragmatic Works for nine and a half years, working in software, consulting, and training roles.
- He has authored books on Power BI, SQL Server 2019, and the Power Platform, and blogs at mitchellpearson.com.
- The event covers SQL basics, including the SELECT, FROM, WHERE, JOIN, and aggregate queries.
- The event runs from 11 am to 2 pm Eastern Time, with a 15-minute break at 12:15 pm.
- Participants can download SQL scripts and a PDF guide to connect to SQL Server Management Studio.
- SQL Server Management Studio (SSMS) is essential for the event and can be downloaded for free from aka.ms/ssms.
- SQL databases store and organize organizational data, replacing outdated paper-based systems.
- SQL is vital for data professionals to interact with databases, insert, retrieve, and edit data.
- Connecting to Azure SQL databases using SSMS involves entering server names, login credentials, and database names.
14:43
Executing SQL Queries in SSMS: Tips & Tricks
- Press F5 or execute to run the script.
- Drag the "0-1 introduction" file into SSMS or double-click to open it.
- Use the asterisk (*) to return all columns from the table.
- Limit the columns returned by specifying them (e.g., first name, last name).
- Alias columns by using "as" followed by a new name.
- Order data using the "order by" clause, specifying ascending or descending.
- Order by multiple columns by listing them in order.
- Avoid using ordinal positions to order columns for clarity.
- Concatenate columns by using the plus sign (+) and add aliases to expressions.
- Highlight and execute specific code portions in SSMS to avoid running all scripts at once.
27:43
Querying Sales Data with Calculations and Functions
- The query specifies the columns to return, starting with "unit price."
- Running the query displays sales information from the sales order detail table.
- To view all data, the query "select star from" can be used.
- Calculations like multiplying order quantity by unit price can be done within the query.
- Basic mathematical expressions like addition and multiplication can be performed in the query.
- Tips for using IntelliSense include toggling on completion mode under the Edit menu.
- Additional calculations like doubling unit price and calculating line total can be included in the query.
- String functions like left and right can extract specific characters from a string.
- Functions like upper and lower can change the case of characters in a string.
- The replace function can replace specific characters in a string with another value.
41:47
SQL Functions for Date Manipulation and Handling Null Values
- The speaker discusses returning the first name in a query, emphasizing the importance of using commas to separate expressions and columns in SQL.
- Demonstrates the use of the "AS" keyword to alias columns explicitly in SQL queries.
- Introduces date functions by exploring the "DATEADD" function to subtract a year from the order date in a sales order header table.
- Shows how to calculate a due date by adding three days to the order date in a SQL query.
- Utilizes the "DATEDIFF" function to determine the number of days between the order date and the ship date in a table.
- Discusses the significance of null values in databases, explaining that null represents an unknown value.
- Illustrates how null values can impact calculations, such as adding null to a number resulting in an unknown value.
- Introduces the concept of handling null values in SQL queries and the importance of accounting for unknown values.
- Provides practical examples of using SQL functions to manipulate dates and calculate differences between dates.
- Emphasizes the role of SQL in data extraction, transformation, and loading processes, particularly in cleaning and transforming raw data for reporting purposes.
55:13
SQL Query Optimization Techniques and Training Offerings
- The process begins with selecting from the person.person table, specifying to return the first name, middle name, and last name concatenated as "full name."
- To enhance code readability, the first name, middle name, and last name are also added as separate columns in the final result set for validation.
- Null values in the middle name column can lead to issues in concatenation, resulting in unknown values in the full name column.
- To address null values, the "is null" function can be used to return a blank space if the middle name is null.
- An alternative method involves utilizing the "coalesce" function, which returns the first non-null value in a list, effectively handling null values in concatenation.
- The "top" function is demonstrated to limit the number of rows returned in a query, such as returning only the top 10 rows from a table.
- Another option is using "top 10 percent" to return a percentage of rows from a table, useful for sampling large datasets.
- The "distinct" function eliminates duplicate values in a query result, providing a cleaner and more concise output.
- Pragmatic Works offers training courses, including an Introduction to SQL and Advanced T-SQL class, available at a discounted rate during the event.
- Participants are encouraged to take a break and return for further exploration of SQL topics, including joins and aggregations.
01:23:50
SQL Functions, Filtering, and Removing Duplicates
- The process involves returning the last date of the month by utilizing the "end-of-month" function, followed by subtracting one month to revert to the prior month.
- To proceed, the end of the prior month must be determined, and then advance by one day, building a sequence of expressions to reach the desired outcome.
- Various methods can be employed to achieve the same result, with one approach utilizing date add and end-of-month functions.
- Addressing the issue of removing duplicates, a method involving temporary tables or common table expressions to assign row numbers to duplicates is suggested.
- The use of "is null" and "coalesce" functions is explained, with "is null" replacing null values with specified expressions, while "coalesce" selects the first non-null value from a list.
- Distinct can be applied to multiple fields, but it operates across all columns in a query, necessitating more complex solutions for distinct values from specific columns.
- The distinct function, when applied to all columns, returns unique rows based on all column values being identical.
- The significance of the "where" clause in SQL is highlighted for filtering data, reducing result sets, and enhancing performance by limiting data retrieval.
- An example demonstrates filtering data for a specific customer ID to display all transactions associated with that customer.
- Further filtering based on specific criteria, such as extracting the year from a date column to filter data for a particular year, is illustrated, with a caution on potential performance implications and alternative approaches provided.
01:38:10
Efficient SQL Filtering Techniques for Developers
- Null values are handled by using "where order date is null" instead of "order date equals null."
- Use "equals," "less than," or "greater than" operators for normal comparisons.
- To filter results based on starting letters of names, use "where first name starts with" or "last name starts with."
- For efficient querying, avoid using inefficient methods like filtering by year.
- To filter out specific customer IDs, use "where customer id is not equal to" or "!=".
- To filter by ranges, use "greater than," "less than," or "between" operators.
- The "like" function allows for wildcard filtering, such as returning names starting with a specific letter.
- The "in" clause is used to filter results based on a list of specified values.
- Use the "in" clause for filtering by specific values, like last names "smith" or "anderson."
- SQL developers commonly use the "in" clause for efficient filtering based on specified lists.
01:52:36
Mastering SQL Query Filtering and Joins
- Using the "where" clause to filter data in SQL queries is crucial.
- Criteria can be set using "and" conditions to match multiple parameters.
- "Or" conditions can be employed to broaden search results based on different criteria.
- The "like" statement allows for pattern matching in SQL queries.
- The importance of parentheses in complex logic to ensure accurate results.
- The "not" predicate can be used to exclude specific values in SQL queries.
- Utilizing "is null" and "is not null" to handle null values in SQL queries.
- Understanding the impact of null values on query results and how to address them.
- Exploring the concept of joins in SQL to combine data from different tables.
- The significance of inner joins in merging data from multiple tables in SQL queries.
02:07:06
SQL Table Joins and Aggregates: A Guide
- To join tables in SQL, you need to specify the common column, like customer_id, to link the tables together.
- To avoid confusion in queries with the same column names in different tables, alias the tables by assigning them new names, like "soh" for sales order header and "cuss" for customer.
- When selecting columns from joined tables, specify the table name to avoid ambiguity, ensuring the correct data is retrieved.
- The purpose of joining tables is to gather additional information about customers, like account numbers, alongside sales order details.
- Different types of joins, such as inner joins, left outer joins, and right outer joins, offer various ways to combine data from multiple tables.
- The union function in SQL merges data from two tables into a single result set, eliminating duplicates by default.
- Union all, on the other hand, retains all records, including duplicates, when combining data sets.
- When using union, ensure that the tables being merged have the same number of columns and the columns are in the same order.
- A cast operation in SQL is necessary to convert data types when combining columns with different data types, ensuring compatibility.
- Aggregate queries in SQL allow for the retrieval of summarized data, such as total purchases, last order dates, and customer names, providing valuable insights into the data.
02:21:05
Understanding SQL Aggregates for Efficient Queries
- Aggregates in SQL involve functions like count, min, max, and sum for basic queries.
- To find the total number of transactions in a table, use "count star" to count all rows.
- Specific columns can be counted using "count" with a particular column, like "sales person id."
- Different functions like sum, min, max, and average can be applied to columns for varied results.
- For instance, "sum" on the "total due" column gives the total sales amount.
- An error may occur if non-aggregated items are present in the select statement with aggregated ones.
- To resolve this, use a "group by" clause for non-aggregated items like customer id.
- Grouping by a specific column, like customer id, can provide insights into individual transactions.
- Filtering results based on aggregated values requires using a "having" clause after the "group by."
- The order of SQL clauses matters, with "where" filtering before "group by" and "having" coming after.
02:35:42
Optimizing SQL Operations for Efficient Data Analysis
- SQL execution plan filters rows before performing aggregate functions like sum, max, min, or average over a large dataset to enhance efficiency.
- The order of operations in SQL involves filtering, then executing select statements, followed by group by, having, and finally order by.
- Understanding the order of operations in SQL is crucial for optimizing performance and structuring queries correctly.
- SQL basics involve retrieving data through select statements and where clauses, with having clauses used for aggregation.
- The having clause is utilized for filtering aggregated data based on specific conditions, distinct from the where clause.
- SQL queries can combine where and having clauses to filter data based on different criteria, enhancing result set precision.
- Monthly subscriptions are available for SQL learning resources, offering in-depth classes on various SQL aspects beyond basic data extraction.
