98-364 MTA Microsoft Database Fundamentals – Querying and Manipulating Data

1. Introduction to T-SQL

DQL is used for querying or selecting data from a database. This involves the famous select command; with which we can get data or a subset of data from a database. Secondly, we have the data manipulation language, or DML. This is a subset of transact-SQL commands that are used for manipulating data in a database. The most common DML commands are Insert, which is used for inserting new rows into a table or view. Update, which is used for updating columns in a table or view.

Finally, the delete command, which is used to remove rows from a table or view, these are the DML commands we’re going to use later on in this course. In some classifications, the select command is also seen as a DML command. It’s just something you need to be aware of. Then we have the Data Definition Language, or DDL. This is a subset of transact-sol commands that are used for defining and maintaining the structures of database objects. Let’s look at a few commonly used DDL commands. First, we have the use command. This command is used for switching the database context, which basically means changing the selected database we are working in.

Secondly, we have the Create command. This command is used for defining the structure of database objects. Then we have the Alter command, used for altering or changing the structure of database objects. Next, we have the Drop command, which is used for removing objects from the database. And finally, we have the Truncate command, specifically used with tables to instantly remove all rows from an entire table or selected partitions of a table.

These are the DDL commands we’re going to work with when creating database objects later on in this course. Then we have the transaction control language used for managing transactions and a database. The exam does not cover this, so I’m just going to give you a high-level overview of TCL. But first, we need to understand what a transaction is. A transaction is one or more transact SQL statements performed against a database that behave as a single unit of work and follow the Acid principles, which stand for atomicity, consistency, isolation, and durability.

This basically means that if one of the statements within the transaction fails, the whole transaction fails. So either all statements succeed as a whole or fail as a whole, therefore preventing data inconsistency. In SQL Server, we generally have three types of transactions: implicit transactions, auto-committed transactions, and explicit transactions. Transactions that don’t auto commit have to be explicitly ended using the commit command, which permanently saves the changes made by a transaction to the database, or have to be explicitly ended using the rollback command, which basically undoes all the changes made by a transaction. Then we have the Save Point command, which is used for temporarily saving a transaction. This is most commonly used when we have nested transactions. When a rollback is initiated in a transaction, all of the transactions will be rolled back, but if we temporarily save a transaction using the Save point command, we don’t necessarily have to roll back all of the transactions.

We can simply roll back to a specific save point. These are the TCL commands that are commonly used. Remember, this is just a high-level overview of transactions. It gets a lot more fun and complex than this. As the final classification, we have the data control language used for managing authorization. The term “authorization” refers to a security mechanism used to determine which access privileges or permissions a user has. In transact SQL, we manage authorization using the following commands: We first have the grant command, which is used to grant certain access privileges or permissions to a user. Then we have the revoke command, which is used to withdraw privileges that were initially granted using the grant command from a user. And finally, we have the deny command, which is used for denying permissions. Later on in this course, when we cover database administration fundamentals, we’re going to take a deeper dive into these DCL commands. Thank you for watching. In the next video, we’re going to look at the different types of data we can use in SQL Server. see you then.

2. SQL Server Data Types

What is the data type? A data type is an attribute that specifies the type of data an object can hold, as well as how many bytes that type of data takes up. When creating a table, it’s mandatory to specify a name and data type for the columns. The data type we specify will be a determinant for the type of data we can store, so it’s imperative that you choose the best suitable data type.

SQL Server includes a wide range of predefined data types, which are generally classified into the following categories: character string data types, Unicode character string data types, numerical data types, temporal data types, and the rest, which includes large character and binary data types, XML geometric data types, geographic data types, and hierarchical data types. Let’s take a look at the character string data types. These data types are used for storing character-based or string data. For this, we have two data types. We have char and varchar. The Char data type is used for storing fixed-size string data, while the Varchar data type is used for storing variable-size string data.

What this essentially means is that if we define a column with a char data type and specify the size using the identifier or parameter n, the data will always take up exactly the same amount of storage space as n. So even if we have two entries where one entry has fewer characters than the other, the storage space taken up would still be equal for both entries. Then we have the varchar data type, which does things a little bit differently. Instead of maintaining a fixed size, the variables have a variable size depending on the size of the data entered into the column. So if we have a scenario where we have two entries where one entry has fewer characters than the other, the storage space used for that entry would be less than the storage space used to store the other entry. So it basically just depends on the size of the character data entered, which will still be limited by the value specified by the size parameter.

Something very important about the size parameter is that it indicates the maximum size of the data, not necessarily the maximum length of the characters. Generally, we have two types of character encoding. We have single-byte encoding, for which the size Ni is equivalent to the length of the characters, and that’s because one character takes up exactly one bite. This is why people usually say that the size parameter specifies the maximum character length of the column, and we also have multibit encoding. In this case, the length of the characters is less than their size because one character could end up using two or more bytes. So it is important that you are aware of this parameter and what it actually stands for. Then we have the Unicode character string data types used for storing Unicode characters.

These are pretty similar to the character string data types with one major difference, and that’s their ability to store Unicode characters. If we look at the range, you’ll see that we can have a maximum of 4000 Unicode character bytes, while for the non-Unicode character data types, we have a maximum of 8000. That’s because Unicode characters require more storage and therefore take up twice as much space. Unicode character-string data types are best suited if our application uses localization or special characters. Then we have the temporal data types used for storing date and time data. Here we have a number of data types we can use. I’m just going to touch the surface because these are not really required for the exam, but it’s good to know which options are available. We have the Date Time data type, which stores date and time values with an accuracy of 333 milliseconds and takes up eight bytes of storage. Then we have the small date time, which is a smaller version of the date time data type in terms of range and storage requirement. Then we have the date data type, which is used for storing only date values and takes up three bytes of storage. Then we have the time data type, which stores only time values. It has a parameter, N, which specifies the number of digits from zero to seven that are used for fractional-second precision and takes up three to five bytes of storage depending on the value of N.

Then we have the date and time data type, which stores date and time values and also has the similar parameter we’ve seen with the time data type. Depending on the value of N, the date time two takes up six to eight bytes of storage. And finally, we have the date time offset data type. This data type is an extension of the DateTime2 data type with additional information about the time zone offset. Depending on the value of N, the date-time offset can take up between eight and ten bytes of storage. We are now going to look at the numerical data types. These data types are used for storing numerical data. There are several types of numerical data we can have. This is why numerical data types are further classified into integer data types, decimal data types, and real data types. Integer data types are used for storing whole numbers, which are numbers without any digits to the right of the decimal point. First, we have the big int data type, which takes up eight bytes of storage and can store a rather large range of integers. Then we have the end data type, which is the most commonly used integer data type.

The int data type takes up four bytes of storage and can store integer values ranging from -2 billion to +2 billion. The small int data type uses two bytes of storage and can store integer values ranging from -32,000 to plus 32,000. Then there’s the tiny int, which takes up one byte of storage and stores integer values ranging from zero to 255. And finally, we have the bit data type, which takes up one bit of storage and can have only one of two values: either a one or a zero. kind of like a boolean where it can be either true or false. Then we have the decimal data types.

These data types are used for storing decimal values, which can include digits to the right of the decimal point. Here we have four data types. First, we have the decimal data type. This data type has two parameters. We have the P, which specifies the precision, and we have the S, which specifies the scale. The precision of a decimal value indicates the total number of digits that can be stored, while the scale indicates the number of digits that can be stored to the right of the decimal point. Then we have the numeric data type, which is functionally the same as the decimal data type. Then there is the Money Data type used for storing monetary values with four decimal places. Basically, it’s the decimal data type with specified parameters. And finally, the small money data type, which is a smaller version of the money data type.

The last category of numeric data types are the real data types, also known as the approximate numeric data types. These are used for storing floating-point numbers, which have a limited number of significant digits. We have two data types in this category. The first one is the Float data type, which has a parameter P that specifies the precision. The precision of floating-point numbers represents the number of bits used to store the decimal portion of the number. By default, this is 53. Then we have the real data type, which is basically the float data type with a precision of 24. The data types we’ve covered are some of the most commonly used data types you’d come across in SQL Server. Depending on the version of SQL Server you’re using, some of these data types may not yet have been implemented. It’s very important that you familiarize yourself with these data types because data types are the building blocks for database development. In the video where we’ll create database objects, we’re going to play around with some of these data types. Thank you for watching. In the next video, we’re going to start with the practical part of this course, which is an introduction to SQL Server Management Studio. see you then.

3. Introduction to SQL Server Management Studio

Welcome back. In this video, we’re going to start with an introduction to SQL Server Management Studio. Hopefully, you’ve already successfully installed SQL Server. If you haven’t, you can always read the installation guide on how to do so. If you encounter any issues during the installation, feel free to let me know using the comment section so I can assist you. If you start SQL Server Management Studio, this should be the first form you see where it prompts you to connect to a server. First, we have the server type, which we’re going to leave at its default. Then we have the server name, or the name of this SQL Server instance. By default, this name is equal to the name of the computer. On a single computer, we can run multiple instances of SQL Server, which are essentially multiple copies of the SQL Server executable programme running in the background.

This is where we come across the concept of named instances. A named instance is an instance of SQL Server that has the computer name and a separate name for the instance, which you specified during the installation. In this demo, I have left it at the default, which is my computer name. Then we have authentication. SQL Server supports two authentication modes: Windows authentication and mixed mode authentication, which is a combination of Windows and SQL Server authentication. Windows authentication is often referred to as “integrated security” because it’s integrated into Windows. Since we’re already logged into Windows, we don’t have to supply credentials to separately log into SQL Server. It’s already been taken care of. Then we have mixed-mode authentication. The idea here is that we can log in using either Windows authentication or SQL Server authentication. SQL Server authentication requires us to authenticate ourselves by specifying the name of the login and a password.

So we’re now going to connect using the Windows authentication mode. Then I’m going to configure the SQL Server instance and change the authentication mode to mixed-mode authentication. So we can also try out SQL Server authentication. So let’s connect. We are now connected to the SQL Server instance. On the left, we have the Object Explorer. At the top of the tree list, you have the name and version of the SQL Server instance. We also have the login name, in this case, the domain user we used to connect to this SQL Server instance. Then we have the classifications of things that are on this server. For now, we’re briefly going to focus on databases and security.

If we expand the databases node, you’ll see all the databases that are on the server. Here we have system databases that are created by SQL Server itself. We haven’t created any databases yet, but if we did, they would also appear in this section. If we right-click on the SQL Server instance and select Properties, you’ll see some very important information about the server as well as a number of options we can use to configure this SQL Server instance. If we head over to the security tab, you’ll see the authentication modes I was telling you about earlier in the video.

By default, we have Windows authentication mode selected. Let’s change this to mixed-mode authentication. As a general rule, we need to restart the SQL Server instance after configuring the server. But we’re not going to do that just yet. In order to authenticate ourselves, we need to create a login. A login is an identity used to connect to a SQL Server instance. If not changed during the installation, a login based on the Windows domain group will be created for us. This is the reason we were able to log in using Windows authentication.

So if we now want to authenticate ourselves using SQL Server authentication, we would need to create a SQL Server login. If we expand the Security node and the Logins node as well, you’ll see a number of logins here. What’s important to us right now are the logins that start with the SQL Server instance name, which for me is this one. If we right-click and select Properties, you’ll see the properties of this login. The first thing we see is that this login is based on Windows authentication. This is the login we used when we first connected to this instance. As you can see, the password fields are both disabled.

That’s because this is integrated into Windows. The same credentials we use to log into Windows are the same credentials this login uses. If we head over to User Mapping, you’ll see a column named “User.” This is where things get a little bit interesting. In SQL Server, we have logins and users. A user uses a login to authenticate with the SQL Server instance. Basically, we create a login and map it to a number of users so that they can log in. In the videos where we’ll cover database administration fundamentals, we’ll take a deeper dive into SQL Server security. For now, let’s just click Cancel and create a new login. Right-click on logins and select “New Login.” The new login we create will be for SQL Server authentication, which means that we’ll have to specify the credentials.

Let’s click OK and restart the SQL Server instance. The instance has now been restarted. We’re going to disconnect this SQL Server instance by right-clicking on it and selecting Disconnect, so we can connect to it again, this time using the SQL Server authentication mode. If we click on Connect, you’ll see the following dialog, which prompts us to change the password for this login. This is just a security mechanism that SQL Server uses. Let’s specify a new password for this login. We are now logged in using SQL Server authentication. I’m going to pause the video, set the authentication mode back to its default, and connect again using the Windows authentication mode. Once you’ve also done that, we’re going to restore a database so we can finally start querying databases.

To restore a database, we need a database backup file. I’ve attached a database backup file to this lecture. I hope you’ve already downloaded it. Let’s head over to the databases. Right-click and select Restore. We’re going to restore a database that is on this device. So let’s open the browse dialog. Here we can add a backup medium, which is basically the backup file you’ve just downloaded. Here we have a tree list where we can navigate to the directory where the backup media is stored. Then we’re going to select the North wind database backup file. Let’s wait for it to load. Now we’re going to restore the database. As you can see, the database has been successfully restored. You’ll now be able to see the North Wind database and the Object Explorer. I hope you had fun getting somewhat familiar with SQL Server Management Studio. In the next video, we’re going to start with querying databases. Thank you for watching. see you then.