Database Foundations: Querying Databases

Name: Database Foundations: Querying Databases
Author: Rodney Harris

Learn the fundamentals of T-SQL and database querying to effectively manipulate and retrieve data

Preview the first lesson free — get full access to all 4 lessons.

Course: On-Demand

Hello, my name is Rodney Harris, and in these lessons, you will learn about manipulating data by querying a database, as well as learning the basics of T-SQL. We will be discussing the two main categories of SQL statement, which are DDL and DML. During this lesson, we will be discussing their differences and walk through some examples of how they are used. A structured query language, or SQL for short, is a programming language used to manage and manipulate data within a relational database. We can insert new records into a table, update existing records, or delete records that are no longer needed. This makes it easy to manage larger amounts of data and keep our database organized. We can write queries to extract data from one or more tables, thus allowing us to analyze our data and generate reports. This can be very essential for making informed decisions. Let's take a look at each of these statements in more detail using the AdventureWorks 2019 database in SQL Server. In our first example, we will start with the Create statement. The Create statement is used to create a new database object, such as a table, a view, a store procedure, or a trigger. Here's an example of how to create a new table called Employee. As we can see, this statement creates a new table called Employees, with five columns, Employee ID, first name, last name, email address, and phone number. The Employee ID column is set as our primary key. If we expand our database and look under the tables, once we refresh, we will now see our new table employees here. In our second example, we will take a look at the Alter statement. The Alter statement is used to modify the structure of an existing database object. Here's an example of how to add a new column to the Employees table. Looking at our Alter statement, Alter table indicates we are modifying a table object. Employees indicates the name of our table. The Add clause is used to add a new column to the table, followed by the name of the column HigherDate, and the data type of the column, which is Date. If we take a look at our table now, we will see this statement as a new column called HigherDate to the Employees table. In our third example, we will be truncating a database table. The best illustrate this point, we must first add sample data to our new Employees table. Next, let's execute our Select command. As we can see, we have two new records. Now, let's execute our truncate command. A truncate is used to remove all the rows from a table in a database. It is a faster, more efficient way of deleting data from a table as compared to the Delete command. As evidence of this, let's run our Select command again to see the changes. After the truncate statement is executed, we can see when we go back to our Employees table, the statement removed all of our data from the Employees table. Equally important, the truncate did not delete our table structure as the columns in our Employees table still exist. In our fourth and final example of the DDL category is the Drop statement. The Drop statement is used to delete an existing database object. Here's an example of how to delete the Employees table. Now, let's try to run our query on the Employees table again. Turning our attention to the bottom error, we can see our Employees table no longer exists in our database. Thank you for watching.

Beginner Provider : Rodney Harris 4 Lessons · 22m in English

Course Description

In this course on Database Foundations: Querying Databases, you will dive into the world of T-SQL and learn how to effectively query databases. The course begins with an introduction to T-SQL and the two main categories of SQL statements: DDL and DML. You will learn about the differences between DDL and DML and explore examples of how they are used. Next, you will learn about SQL commands for filtering and sorting data. The WHERE clause in the SELECT statement allows you to filter data based on specific conditions, while the ORDER BY clause helps you sort the data in a desired order. You will explore various examples of using the WHERE and ORDER BY clauses to retrieve and arrange data according to your requirements. The course also covers the GROUP BY clause, which is used to group rows based on one or more columns. You will learn how to use aggregate functions like COUNT, SUM, and AVG to calculate values within each group. Additionally, the HAVING clause will be introduced, which allows you to filter the grouped results based on specific conditions. By the end of this course, you will have a strong foundation in querying databases using T-SQL. You will be able to write effective SELECT statements with filtering and sorting, understand the importance of DDL and DML statements, and apply your knowledge to manipulate data in a database.

What You'll Learn

Query databases effectively using T-SQL
Distinguish between DDL and DML statements and how each is used
Apply the WHERE clause to filter data based on specific conditions
Sort query results using the ORDER BY clause
Group rows with the GROUP BY clause and apply aggregate functions like COUNT, SUM, and AVG
Filter grouped results using the HAVING clause

Key Takeaways

T-SQL statements fall into two main categories: DDL and DML.
The WHERE clause in a SELECT statement filters data based on specific conditions, while the ORDER BY clause sorts data in a desired order.
The GROUP BY clause groups rows based on one or more columns, and aggregate functions such as COUNT, SUM, and AVG calculate values within each group.
The HAVING clause filters grouped results based on specific conditions.
By the end of the course, learners can write effective SELECT statements with filtering and sorting and apply their knowledge to manipulate data in a database.

Frequently Asked Questions

What will I learn in this course?

You will dive into T-SQL and learn how to effectively query databases, including the differences between DDL and DML statements, filtering and sorting data with the WHERE and ORDER BY clauses, grouping rows with GROUP BY and aggregate functions like COUNT, SUM, and AVG, and filtering grouped results with the HAVING clause.

What skills will I gain from this course?

The course covers skills in Data Classification, Data Feed, and Transact-SQL.

What topics do the lessons cover?

Lessons cover the basics of T-SQL across two parts, getting started with SELECT statements, and filtering and sorting data.

What will I be able to do by the end of the course?

You will have a strong foundation in querying databases using T-SQL, be able to write effective SELECT statements with filtering and sorting, understand the importance of DDL and DML statements, and apply your knowledge to manipulate data in a database.