98-364 MTA Microsoft Database Fundamentals – Creating Database Objects.

1. Tables

Welcome back. In this video, we’re going to start with the creation of tables. But first, we’re going to create a new database. There are two ways we can create a database in SQL Server Management Studio. We can use the graphical user interface or we can write the Transact SQL script ourselves. So, in the Object Explorer, right-click on Databases and select New Database. The following window opens. The first thing we need to specify is a name for the database. We’re going to name this database fundamental.

When creating a database, two files will be created by default. The first file is the primary data file and has a MDF file extension. This is the file group or data file on which the database objects are stored by default. Then we have the transaction log file with a LDF file extension. Aside from these two files that are created by default, there are also secondary data files we can create that have a NDF extension.

These files can be created on a separate disc if the space on the disc containing the primary data file runs out or to improve performance. So we’ll leave everything at its default settings and press OK. Our database is now created using the graphical user interface. Let’s go ahead and delete the database from the Object Explorer and recreate it using Transact SQL. To do this, we’re going to open a new query window to create database objects, which include the database itself. We need to use the Create command.

We then specify the type of object we want to create. In this case, we would like to create a database. So we simply write “database,” and finally we specify the name of the database, which is “fundamentals.” This is the general syntax for creating a database. If we execute it, we get a message stating that the command was completed successfully. If we refresh the Databases node, our newly created database should now appear. We can now create our first table in the fundamentals database. If we expand it, right-click on Tables and select New. You’ll see we have a number of different types of tables we can create. For now, just click on Tableland, and the following window should appear: We have two panels in this window. We have the top panel, where we specify the columns of the table as well as define some of the basic attributes of the columns, such as data type and whether the columns should allow nulls or not. Then we have the bottom panel, where we can configure more advanced properties of the columns.

We’re going to create the following tables to manage our music collection: As you can see, they’re very simplified, but that’s enough to cover most of the important stuff. We’ll do the artist table using the graphical user interface and the other tables by manually writing the Transact SQL script. We first have the artist ID column. For this column, we’re going to use the End data type. Then we have the first name column. In this column, we’re going to store the first names of singers from a number of different countries. which means that the names might be language-specific or contain special characters. So in this case, it’s best to use the Varchar data type. We’re going to allow a maximum size of 25. Then we have the last name column here. We’re also going to use the Nvarchar data type with a maximum size of 25. For the stage name column, we’re going to do the same as well. Then we have the birthdate column. In this column, we’re only going to store the date component, so it’s best if we simply use the date data type.

Last but not least, we have the Birth Country column. This column will be used to store the birth countries of the artists. I’m also going to use the NBAR Char datatype, this time with a maximum size of 45. Now we’re going to configure the columns. The Artist ID column will be the primary key column. To define the primary key on a column, we use the Table Designer toolbar. Depending on the version of SQL Server Management Studio you’re using, it might be somewhere else. If you can’t find it, leave a comment so I can assist you. On the Table Designer toolbar, you should see a key icon. We’re going to click on it to set the primary key. You’ll see a key appear on the left of the Artist ID column. This column is now the primary key. Next, we’re going to let the database automatically generate the values for this column by enabling the identity property. If we look at the column properties, there should be a property named Identity Specification. If we expand it, we should see the following properties: We’ll set the asidentityproperty to true by default. The identity increment and the identity seed attribute are both set to one.

The identity seed attribute defines the value from which the numbering will start. So in this case, the first row we insert into the table will have a value of one for the Artist ID column. The identity increment attribute specifies the value that will be used to increment the identity value when inserting a new row. So if we insert a second row into the artist table, that row will have an artist ID of two. Basically, one plus the previous value. Let’s look at the nullability properties of the columns. The first name and last name are definitely mandatory. So we’re going to uncheck the Allow Nulls checkboxes. Then we have the stage name column. We’re going to let this field allow nulls because not every artist has a stage name. So this is going to be an optional field. The following columns are the birth date and the birth country. It’s nice to have information about an artist, but I don’t find it to be of that importance to be a requirement when creating a new artist. So these two columns are also going to be optional fields.

So this is what our table is going to look like, but we’re still not done yet. To save or create the table, you can press the CTRL and S keys together. We will then be prompted to enter a name for the table. This will be our artist table. So we’re going to write “Artist” and press OK. Our table has now been created. To verify this, we can expand the tables node, and here you’ll see our newly created table. What we’re going to do now is create the rest of the tables using Transact-SQL. So let’s open a new query window. The next table we’re going to create is the genre table. Just like we’ve created the database, we first write the create command followed by the type of object we want to create, which is a table. Then we specify the name of the object. Here I’m using the two-part name, which includes the name of the schema and the table. Since I haven’t created any particular database schema, I’m using the default schema.

We are now going to define the column definitions, which will be enclosed within parentheses. To define a column, we need to first specify the name of the column followed by the data type, after which we can define numerous properties and constraints if necessary. The first column is the Genre ID column. Given that there aren’t many genres, we’re going to use the tiny int data type. This will allow us to store up to 255 genres, which is more than enough. We’ll now enable this column’s identity property by simply writing Identity. If you can recall, when we were using the graphical user interface, we had two additional properties. When we enabled the identity property of the column, we had the identity seed and the identity increment property. If we leave the identity declaration like this, both properties will be assigned the default value, which is one. We can change the default values by specifying the custom values within parentheses. First, we write the value for the identity seed property and then the value for the identity increment property. So if we were to insert a new row into this table, the first value of the Genre ID column would be ten. The second value would be 15, then 20. And so it goes on and on until it reaches the maximum value supported by the data type. Next, we’re going to specify the not null constraint, because this will be the primary key column. Actually, this isn’t required because, even if we don’t specify it, defining the primary key constraint on this column will enforce that nonulls can be inserted into this column. Then we have the name column for storing the names.

Name is a reserved keyword, so we’ve used square brackets to declare it. For the data type, we’re going to use N varchar with a maximum size of 25. Since it’s pretty useless to insert a new row into the genre table without specifying a genre name, I’m going to use the notnull constraint to make this a required field. There’s still something we need to do, and that’s define the primary key constraint on the genre ID column. There are a number of ways we can do this. We could define it in line with the column definition. We could perhaps declare it at the end of the Create statement, or we could create a separate script that will alter the table once it’s created and then add the primary key constraint. We’re going to focus only on the first two methods, both of which are pretty simple. First, we can simply write “primary key” inline with the column definition, and our column is now a primary key column. In the second method, we write the primary key at the end of the column definitions and use parentheses to specify the name of the columns that will form the primary key. But this method is mostly used if we have a composite primary key. So we’re just going to use the first method. Let’s execute this query. If we refresh the Tables node, you should now see the genre table. We’re now going to create the album table. So we have four columns in the album table. We have the Album ID column, which is the primary key column. We have the artist ID column, which is the foreign key column that references the artistID column of the artist table. We have the title column, and last but not least, we have the release date column.

So let’s go ahead and create the album table. Since we’ve already covered some of the basics, I took the liberty to partially write the DDL script to create the Album table. What we haven’t done yet is enter the foreign key and the release date column. We’re going to use the inline method to create a foreign key constraint on the artist ID column. First, we simply write “foreign key.” Then we write references because a foreign key references a primary key, and then we specify the table and column we’re referencing. So in this case, we’re referencing the artist table using the artistic column, which is the primary key of the artist table. Now let’s execute the query.

As you can see, the commands were successfully completed. If we head over to the Object Explorer and refresh the Tables node, you should be able to see the Album table. The issue is that we forgot to include the Release date column. Luckily, we have the Alter command. As we’ve seen in one of the previous videos, we can use the Alter command to change the definition of database objects. So we’re going to alter the album table and add the “Release date” column. We first begin with the alter command. Then we specify what type of object we want to alter. In this case, we want to alter a table. Then we specify the name of the table we want to alter.

There are a number of things we can do when altering a table. We can, for example, add columns, modify columns, delete columns, add constraints, remove constraints, and a bunch of other stuff. What’s important to us now is adding the release date column. We do that by writing the add command, and then we write the definition of the column, starting with the column name, which is release date.

Since we’re only storing the date component, we can use the date data type, and this will be an optional column. So I’m going to write “null.” When we run this, the commands are successfully completed. If we refresh the columns node of the artist table, we should see our newly created column. So there is only one table left in the track table. We have five columns. What we’re going to do here differently is add two constraints to the track number column. So the thing is, we don’t want nulls in the track number column, but we also don’t want to make this field a required field when inserting a new track. So we’re going to specify a default value by using the default constraint. The second thing is, if someone does decide to insert a track number, we want to make sure it’s valid. So here we’re going to use the check constraint to make sure that when the user specifies a tracking number, it is in fact valid.

So if we go back to the IDE, you’ll see that I already have the DDL script to create the track table partially written. We have the track ID column, which is the identity column, and also the primary key column. We then have the album ID column, which is a foreign key that references the album ID column of the album table. Then we have the genre ID column, which is also a foreign key that references the genre ID column of the genre table. Then we have the title column.

There’s really nothing special here. And finally, the track number column So first, we’re going to add the default constraint. If a user inserts a new row and does not specify a value for this column, we want a default value of zero to be assigned to the track number column. To do this, we simply write the default key word and then specify the default value. As you can see, it’s pretty simple. The next thing we’re going to do is add the check constraint. So we’ve assigned the “tinyant” data type to this column. With this data type, we can store integers ranging from zero to 255.

The reason why we’ve assigned the “tiny” in data type is because an album generally has a maximum of 24 tracks on it. While this data type is storage-efficient for this column, it doesn’t validate if someone specifies a track number greater than 24. So the check constraint we’re going to create will validate that the track number falls within the range of zero to 240, which is the default value. To create a check constraint, we simply write “check,” and then we use parentheses to specify the condition or criterion. In this case, the track number has to be between zero and 24. Let’s execute this DDL statement to see if the result commands were completed successfully.

The track table has now been created. If we head over to the Object Explorer and refresh the tables node, you should see the track table. If we double-click on it and expand the columns and the Keys Node, you should see three keys: the primary key and two foreign keys. When creating a table, you can actually specify a name for these keys, but we’re not going to cover that in this video. Another thing I’d like to show you is the clustered index that is created by default when we declare a primary Key, and that is the Clustered index. Later on in this course, we’re going to take a deeper dive into these types of database base Objects. I have attached a document with a number of insert statements you can use to insert data into the tables. Some of the statements will contain an error. For example, a check constraint violation or a foreign key violation But that’s just so you can observe how the constraints behave. That’s it for tables. In the next video, we’re going to look at database views. see you then.

2. Views

A database view is a name select statement that’s stored in the database and acts like a table, sometimes also referred to as a “virtual table.” This means we can select data from a view just like we would select data from a table. The biggest difference is that a view does not store data, while a table does. So there are generally a number of reasons why we would want to use a view.

The first one is security. Imagine you have a table that stores data from multiple departments. You want the members of the finance department to only see the subset of data that relates to their department. So in this case, you could create a view, and instead of giving them direct access to the table, you only give them access to a view that filters the data accordingly. This way, you’re effectively restricting the data they can see as well as the data they can manipulate. In SQL Server, we can run DML operations such as insert, update, and delete operations against a view. Since a view does not store data, this will only be possible if SQL Server can determine a single underlying or base table.

If SQL Server can determine the base table, then these DML operations will be executed against the base table. If someone, for example, inserts data into a view, we can enforce that. The constraints or conditions used in the view also have to be satisfied by the data inserted into the view. So to go back to the example of the finance department, if any member of the finance department wants to insert data into the view, then that data has to be about the finance department only. Otherwise, a DML operation against the view would fail. So aside from security, there’s also an extra layer of data integrity added by using views. Then there’s query simplicity and reusability. When we create a view, the view either selects data from one table or it selects data from multiple tables. These select statements can get very complex. So, essentially, a view encapsulates complexity, which means that when we query the view, we are not interacting with a complexity. Instead, we interact with the view as if it were a single table. Reusability speaks for itself. If we create a view, the view is permanently stored in the database, and we can use it whenever the need arises. Views are also commonly used for reporting purposes, just like plain queries, aggregations, and computed columns, which are relatively easy to define. Another thing about views is that they always display the current data. And since the expressions for the computed columns are defined once, it provides a high level of consistency.

So let’s head over to SQL Server Management Studio and create our first view. Just like the other database objects we’ve covered, views can also be created by using either the graphical user interface or by writing the transact SQL script ourselves. So we have a basic select statement here. This select statement retrieves all the track titles we have in the track table. We also have an alias defined for the title column, which gives a little bit more insight into the data returned. If we execute the query, we get the following result. What we’re going to do now is convert this select statement into a database view, which is relatively easy to achieve. To convert this query into a view, we need to use the Create command, since we’re basically creating a database object. Then we specify the type of object we want to create, which is a view. And this is the part where we specify a name for this object. We’re going to call everything Tracks. As a convention for naming views, we always start with the prefix VW, after which we specify the name of the view. So if someone else is looking at the database, it will be easier for them to differentiate views from tables.

The last thing we need to add is the as clause. The definition for our database view is now complete. Let’s run this to see if the commands were successfully executed. If we head over to the Object Explorer and double-click on Views to expand it, you’ll see our newly created database view. If we double-click on the view and expand the Columns node, you’ll see the columns that are returned by this view. There are also other types of objects that can be created on a view, such as triggers, indexes, and statistics. We haven’t covered triggers yet, but if you can remember from the slides, we said that we can perform DML operations against a view and that that’s only possible if SQL Server can determine a single underlying table. If a single underlying table cannot be determined, which is most likely the case, if we have a lot of joins or set operators in the select statement, such an operation would fail, and that’s where triggers come in. We can create triggers on the view to support DML operations. Here we’re going to define our own logic for what should happen when one of these operations is performed against the view.

In this video, we have indexes that can be created on a view. Once we’ve created an index on a view, we no longer refer to it as just a view but rather as an “indexed view” or “materialized view.” These types of views also behave differently than the normal views we’re working with. Instead of just displaying data, they also store it to keep the indexes up to date. And finally, we have statistics, which are basically created for every database object. These statistics help to create high-quality query execution plans. To create a view, there are a number of things we need to pay attention to. First things first. When creating a view, we cannot have unnamed columns. So if I, for example, add a new column by simply writing an expression, you’ll notice a red line appearing under the name of the object. We can simply fix this by providing a name for the column and using the as clause. As you can see, the red line is now gone. Another important thing is that we cannot have an order by clause in the select statement of a view.

Say we, for example, were to order the results set by the tracks column. If we now execute this query, we get the following error: This error states that the “order by” clause is invalid in Views. In line functions, you derive tables, subqueries, and common table expressions. For now, just know that using the order by clause on its own in a view will raise an error. So always pay attention to this when creating views. What we’re going to do now is query the view.

We’re going to select all the data from the view. If we execute this query, we get the following result, which should be the same as if we were to execute this statement. We are now going to look at a different example with a more practical approach. Here we’re selecting the stage name from the artist table, the title of the album, and the total number of songs we have for each album. We have two joins in this table, which means we’re joining three tables. Since all artists are associated with an album and all albums are associated with an artist, the inner join will return all the data from both tables. You might be wondering why I used the right outer join. Well, I have used the right outer join because not all tracks have an album associated with them. If we select the top 1000 rows from the track table, you’ll notice we have a number of nulls in the Album ID column.

So in order to display these tracks, we need to perform an outer join. Since the track table is on the right side of the join clause, we use the right outer join. So if we execute this query, we get the following result set, which includes the stage name of each artist, the name of their album, the number of songs we have for each album, and most importantly, the total number of tracks that do not have any album associated with them. Had we used the inner join, this row would not be in the result set.

We’re now going to turn this select statement into a view. The same principles apply here. We first write the Create command, then specify the type of object we’re creating, followed by the name of the object. We’re going to name it Total Songs per Album. And to make this complete, we use the as clause. If we execute this DDL statement, our view will be created. If we head over to the Object Explorer and refresh the Views node, you’ll be able to see our newly created view.

Let’s query this view by selecting the top 10 rows, and here you can see the same resultset returned as when we executed the select statement. What we’re going to do now is modify the database view. So instead of retrieving only the stage name for each artist, we also want to retrieve the last name and the first name. To do that, we use the Alter command. In the previous video, we had, among other things, covered how to alter a table. If we have an existing table, we can just add, drop, or modify an object that is part of the table’s definition, for example, by adding a new column or removing an existing one.

Altering a view, however, is done quite differently. When we alter a view, we cannot change part of the view’s definition. Since a view is basically a select statement, altering a view means we would need to modify the select statement. Therefore, we would need the entire view definition so we could modify it. This is also one of the major differences between tables and views. So to alter this view, we simply replace the Create command with the Alter command. Then we modify the definition of the view by adding the last name and first name to the select list. Since we have a “Group By” clause, all the columns in the select list have to be contained in the Group By clause.

This is our modified definition for the view. If we execute this DDL statement, our view is now altered. If we head over to the Object Explorer, double-click on the view, and expand the columns, you’ll see that the Last Name and First Name columns are now part of the Views definition. If we now select the top 10 rows from the view, we get the following result set, which includes the last name, first name, stage name, album, and total songs: column Altering this view was easy because we already had the definition right here.

But what if we no longer have the definition and we are now asked to remove a column or adjust one of the calculated columns in the view? This will be very problematic if we don’t have a definition. Fortunately, there are several ways to obtain the definition. I have attached a document to this lecture that covers a few methods you can use to get the definition of a view. We’ve now covered the basics of database views. Thank you for watching.