SQL Basics in MS SQL Server

Table of contents

1. 1. A primer on databases

   0/6

   Find out what relational databases are and get to know T-SQL – a language to talk to SQL Server databases

   List of exercises Start now
   1. Introduction
      1. Welcome
      2. Tables
      3. Columns and rows
      4. Notation
      5. SQL
      6. Queries

2. 2. Selecting data from one table

   0/23

   Discover how to select data from a single table

   List of exercises Start now
   1. Returning all data from a table
      1. Get all data from a table
      2. Read the result
   2. Select some columns
      1. Select one column
      2. Select multiple columns
   3. Filtering rows
      1. Select only a few rows
      2. Conditional operators
      3. The inequality operator (!=)
      4. Conditional operators and selecting columns
   4. Logic
      1. Logical operators – OR
      2. Logical operators – AND
      3. The BETWEEN operator
      4. Logical operators – NOT
      5. Join even more conditions
   5. Text patterns
      1. Use text
      2. Unicode text
      3. The percentage wildcard (%)
      4. The percentage wildcard (%) continued
      5. The underscore wildcard (_)
   6. To be or NULL to be
      1. Looking for NOT NULL values
      2. Looking for NULL values
      3. Comparisons with NULL
   7. A bit of mathematics
      1. Basic mathematical operators
   8. Let's practice
      1. Put your skills into practice

3. 3. Querying more than one table

   0/13

   Learn how to select data from multiple tables

   List of exercises Buy course
   1. How to query more than one table
      1. Data from multiple tables
      2. Get to know the tables
      3. Multiple tables in the clause FROM
      4. Join tables on a condition
   2. Creating JOINs
      1. The JOIN keyword
      2. Join tables using JOIN
   3. Referencing columns
      1. Display specific columns
      2. Refer to columns without table names
      3. Rename columns with AS
      4. Filter the joined tables
      5. Filter the joined tables (continued)
   4. Let's practice
      1. Put your skills into practice
      2. Further practice

4. 4. Aggregation and grouping

   0/25

   Computations in T-SQL? Why not? You'll learn how to group rows and compute statistics with aggregate functions

   List of exercises Buy course
   1. Ordering
      1. The scope of this part of the course
      2. Get to know the tables
      3. Sort the rows – ORDER BY
      4. ORDER BY with conditions
      5. Ascending and descending orders
      6. Sort by a few columns
   2. Limiting the output
      1. TOP n rows
      2. TOP ordered values
   3. Eliminating duplicate results
      1. Duplicate results
      2. Select distinct values
      3. Select distinct values in certain columns
   4. Aggregation
      1. Count the rows
      2. Count the rows, ignore the NULLS
      3. Count distinct values in a column
      4. Find the minimum and maximum values
      5. Find the average value
      6. Find the sum
   5. Grouping
      1. Group the rows and count them
      2. Find min and max values in groups
      3. Find the average value in groups
      4. Group by a few columns
   6. HAVING: filtering and ordering groups
      1. Filter groups
      2. Filter groups continued
      3. Order groups
   7. Let's practice
      1. Put your skills into practice

5. 5. More on JOINs

   0/20

   Learn about various kinds of JOINs to best suit your needs

   List of exercises Buy course
   1. Get to know the data
      1. The scope of this part of the course
      2. Get to know the Room table
      3. Get to know the Student table
      4. Get to know the Student table – continued
      5. Get to know the Equipment table
      6. Get to know the Equipment table – continued
   2. JOIN revisited
      1. JOIN revisited
      2. JOIN columns revised
      3. INNER JOIN
      4. How INNER JOIN works
   3. LEFT JOIN
      1. LEFT JOIN explained
      2. LEFT JOIN again
   4. RIGHT JOIN
      1. RIGHT JOIN explained
      2. RIGHT JOIN again
   5. FULL JOIN
      1. FULL JOIN explained
   6. OUTER JOINS
      1. The keyword OUTER
   7. Aliases for tables
      1. Table aliases
      2. Aliases in self-joins
      3. Joining more tables
      4. Summary

6. 6. Subqueries

   0/23

   Queries within queries? That's right! Discover how to build and use subqueries

   List of exercises Buy course
   1. Introduction
      1. The scope of this part of the course
      2. Get to know the data – the Country table
      3. Get to know the data – the City and Mountain tables
      4. Get to know the data – the Trip and HikingTrip tables
   2. Very simple subqueries
      1. Learn about subqueries
      2. Subqueries with various logical operators
      3. Subqueries with functions
   3. Subqueries with multiple results
      1. The IN operator
      2. The IN operator with subqueries
      3. The ALL operator
      4. The ANY operator
      5. The SOME operator
   4. Correlated subqueries
      1. Learn about correlated subqueries
      2. Aliases for tables
      3. The IN operator with correlated subqueries
      4. The EXISTS operator
      5. The EXISTS operator with NOT
      6. The ALL operator in correlated subqueries
      7. The ANY operator in correlated subqueries
   5. Other subqueries
      1. Subqueries in the FROM clause
      2. Subqueries in the FROM clause
      3. Subqueries in the SELECT clause
      4. Subqueries – summary

7. 7. Set operations

   0/11

   Combine query results using set operations like UNION, INTERSECT, and EXCEPT

   List of exercises Buy course
   1. Introduction
      1. The scope of this part of the course
      2. Get to know the tables
      3. Get to know the tables – cycling medalists
      4. Get to know the tables (continued)
      5. Get to know the tables – speed skating medalists
   2. UNION
      1. How UNION works
      2. Conditions for UNION
      3. The keyword UNION ALL
   3. INTERSECT
      1. How INTERSECT works
   4. EXCEPT
      1. How EXCEPT works
      2. Summary

8. 8. Quiz

   0/9

   Check your SQL Server skills

   List of exercises Buy course
   1. Quiz
      1. Introduction
      2. Task 1: Selecting rows from one table
      3. Task 2: Selecting rows from multiple tables
      4. Task 3: Aggregation and grouping
      5. Task 4: Sophisticated JOINs
      6. Task 5: Subqueries
      7. Task 6: Set operations
      8. Task 7: Challenge
      9. Congratulations

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Obtain the Certificate Learn more