DP-300 Microsoft Azure Database – Review prerequisites Part 2

3. Installing SSMS, connecting to Azure SQL Database, and quick look around SSMS

In this video we’re going to connect to our SQL database that we installed in the previous video. So we can do this in the Portal, the Azure Portal, by going to the SQL database and going to Query Editor. So it’s asking for my SQL Server authentication. So here it is, click OK. And here is what we’ve got so I can expand. And this, and here are all of the tables that we’ve got.

We’ve got various views and we’ve got stored procedures as well. So I could say for instance, select Star from Sales Lt Address and unlike some products I can’t actually just drag it across. I do have some options like select top 100 rows or 1000 rows or Edit Data. But if I just type in Sales Lt Address and run there, we can see the results. Now we won’t be using the query editor that much in this particular course, but it’s nice to know you can actually get access to your data without needing any installed programs. What I’m going to be using mostly in this course is a program called SQL Server Management Studio SSMS.

So if you just Google it, find the latest version. So in my particular case it’s 18. 9. 2, it might be version 19. Now it doesn’t really matter what the version is. Now let’s just have a look at the system requirements. It is for a 64 bit version of Windows. If you don’t have a 64 bit version of Windows, then we go to previous versions, click on previous SSMS releases and you probably want to install version 17. 9. 1 if you have a 32 bit version of Windows. If you don’t have Windows at all. Do I’ll be showing you a suitable program that we can use for querying though?

It doesn’t have everything that we’ll be using in SSMS, so I’m going to click on download, so 600 megabytes. So I’m just going to pause the video until it downloads, right? So it’s basically downloaded, so let’s launch it. So the first thing I’ve got is, do I want to allow this app to make changes to my device? So basically do I want to run in administrative mode? So yes, and it’s very simple install. All I do is click Install and it downloads and installs everything that it needs.

Now microsoft SQL Server Management studio SSMS is bundled nowadays with Azure Data Studio. And if I just open another Google page and go to Azure Data Studio, you can see that this is another tool, a cross platform database tool for people using Windows, Mac OS and Linux. So you don’t need to have a Windows machine. If you have something else, you can use Azure Data Studio.

Now it is not as feature packed as SSMS and in fact SSMS is referred to in the practice Test exams and Azure Data Studio isn’t. So that’s why in this course we will be using SSMS rather than Azure Data Studio. But it’s there you can download it if you wish, right? And now it has been successfully installed. So if you want to install Azure Data Studio separately, you can do so again, just Google it. So let’s have a look at SSMS. So I’m going to launch it from my Windows Start menu.

So here it is. And the first thing is going to ask you is the server name. Now I’ve got a dot, a full stop period. Well, that connects me to my local SQL Server that I’ve got installed on my computer. But I don’t want that, I want Azure. So what I’m going to do is just cancel that and I’m going to have SSMS on the left hand side and I’m going to have my database on the right hand side. And I’m going to go back to the overview. So there is actually a connect wave, but this connect wave is only useful for Azure Data Studio. But we do have over here the server name. So I’m going to copy it to the clipboard. So I’m going to use this little icon to copy it. And I’m going into Object Explorer and connect to a database engine. And I’ll just paste that in now. It’s currently set up for Windows authentication.

I can’t use Windows authentication on an SQL database. There isn’t a version of Windows behind it that I can use. So I’m going to change this to SQL Server authentication. You’ll also see that there are Azure Active Directory authentications as well. We’ll have a look at that in later videos. So I’m going to put in my login, which is DP 300. It’s the same that you set up as you were setting up the database. And I’m going to copy and paste my password and I’m going to check remember password. So click connect.

And there we are, I’m connected. And you can see if I expand this left hand side, it takes a little while because it’s a basic database as opposed to anything higher like general purpose. We can see tables, we can see views, and in the programmability section we can see the store procedures which are there. So I can click on new query. I have to change what I’m looking at.

At the moment. I’m looking at the Master database. So that is a systems database. I want to change to my user database, DP 300. And by the way, if you’re used to being able to say use Master like that, for instance, you can’t actually do that in SQL Database. Use doesn’t work. So I’m going to say select Star from Sales, Lt, dot address, drag it in and execute. And there we can see very quickly all of our data. Now I’m going to close this down and have a look at this connect with, I’m going to connect with Azure Data Studio.

So you can see that I can download Azure Data studio there. Already got the app. I’ll click on Launch it now. So I’ll open it. So this is exactly the same as if I was in the Start menu, except it comes up with an additional dialog box. Do you want to connect to this particular server? I’ll click Open and you can see here we have got our connection. It’s connecting once I put in my username and password. So basically all that’s done is put in the server name to start with.

So I’ll put in DP 300 as my username and my password and say which particular database I want to connect to. And click connect. And there we are. Now, if I were to click on this particular server, well, that is my local server again, so that’s not going to be very helpful. Instead, I need to click on Azure. This is only if you want a tree of all of your items or tables and other objects. Click sign in. I will add an account.

So I will click on my account. My account was successfully added so I can close this page. And inside this azure I have got my SQL database. If I drag this up there, we have got tables and views and stored procedures just like in SSMS. And if I don’t want to see this servers at the top, I can click on the dot and say no thank you to servers, no thank you to SQL Server big clusters. So again, what I can do here, select Star From and make sure I’m in the right database. So here’s my Sales lt address.

But again, it’s hard for me to actually drag it across. So I’ll just type it Saleslt Address, run this, and there is my data. So this you can use on Mac and Linux as well as Windows. Now, just one more thing to add about connecting to Azure SQL Database. So what I’ve done is I’ve rebooted my computer and I’m going to connect again. Now, what happens is that your IP address might change.

So your IP address, your Internet Protocol address, it’s a series of four or six numbers between zero and 255. And you might have a static address. In other words, you might probably pay extra to your Internet broadband provider and say, okay, this is going to be my address. That’s particularly for businesses. If you’re not a medium size or big business, then you may have a dynamic address. So when you no longer need it, then your Internet Service provider goes, okay, let’s forget about it. And when he logs back in, we’ll give him a new address. So you can see I’ve got a new address now.

So I’m going to sign in to Microsoft Azure. I’m now signed in so I can sign in this one specific address, or I can sign in 256 addresses where this number doesn’t change, but the last number goes all the way from zero to 255. I’m just going to add this one IP address. And now I can connect. So it’s just an additional thing that you need to be aware of. If you’ve already added in your individual IP address, when you reboot, you might have a different IP address. And so you just have to connect and say, yes, this address can be trusted as well. So this is something to bear in mind if when you are connecting to Azure SQL database data.

4. Review 6 principal clauses of the SELECT statement

In this video we will briefly review the six principal clauses of the select statement. Select from where group by having an order buy. Now, it won’t be the purpose of this course to actually teach you these six clauses. If you don’t know them, then I suggest you stop this video right now and go to a place where you can learn these select statements. So I’m going to create a new query. So in this video we’re just going to revise what hopefully you already know. So a select statement is made up of at least one of these clauses. So you could just have one particular clause, select one plus one. Now, I just want to point out what this dialogue box is all about. It’s all about prioritization for something called Always Encrypted, which we’ll be looking at in this course. So it’s just a safer way of passing information through. I’m not going to be using it for this course, but we’ll have a look at Always Encrypted later. So if you get that you can click on do not show me this message again.

So I’m going to click on clause Not Enable to get rid of this dialog box. So here we are with just one select clause one plus one. If you use the from, that tells you where your source data is from and you generally will be using the from. So I’m going to get the Table sales OT address. It could be view, it could be other things. So I’ll go select Star. Star means give me all of the columns, give me all of the fields. Now, I’m not limiting the number of rows. I can limit the number of rows by using the where. So where Address ID is less than ten. For example, equally you could say is greater than, is greater than equals, is less than or equal to and is not equal to, which can also be expressed with an exclamation and an equal sign. So here we show everything which the Address ID is not equal to ten.

You can also have an and so you could say where the Address ID is not equal to ten and the city is equal to both L, for instance, and ensure that any string literals are in single quotation marks. For instance, some variants of SQL require speech marks. Here will require single quotation marks. So you could say and so if I change this to is under 15 and city equals bottle, then here we can see these two or we can say all so and means both of those have to be true. All means only one of them has to be true. And not is the same as not caring about what’s true and what’s caring about what’s false. So give me everything apart from Bobble. So if I put a not this happening, then it gives me everything. But we can also use like so for instance, where the address line is on like and somewhere in the middle I want jump. So these percentage sign says zero to infinite number of characters. You could also have an underscore. So for instance, I could say where it contains this and that is exactly one character.

So Yosemite will come up there. Finally you can put in date. So here we have a modified date. So where the modified date is less than. And I would use this terminology, so put it in as a string and have year, month, day, what I call as Japanese format. It’s also used in Korea and China and a few other places as well. So that’s the where clause. Then we’ve got the group clause. So maybe I want to count the number of countries. So I could say select the country region and I want to count, but at the moment the computer is going, I don’t know exactly what you’re talking about. A particular column is not contained in an aggregate function. So that’s sum count min max or in the group clause. So let’s put it in the group by clause. So group by the country region. In other words, each row has a different country region. So I’m going to name this as number of rows.

So Canada’s 115 roles. United Kingdom 40, united States 295. Now if I wanted to filter down this further, the where filters down the individual roles in the source data. The having clause filters down what’s after the group by so having current star greater than 100. So I don’t care if there’s individual countries that have one row or two rows, I just want those where the count star is greater than 100. So that just gives me two rows. And then finally I’ve got orderby.

So I can order by the country region, for example, and I could order it ascending or descending. So these are the six principal clauses of the select statement. Select from where group by having an order by. So the reason I’m putting this into this course is we will be using some SQL select statements, but we won’t be looking at how they’re built up necessarily. We won’t be saying and so I need this in the from clause or in the group by clause. I’m going to assume for this course, you know, all of that, but I’m hoping that this is going to be a quick review of these six clauses. So it doesn’t get much more complicated than what you see on screen, apart from one thing joins. We’re going to have a look at joins in the next video deal.

5. Review JOINs

In this video we’re going to briefly review joins and joins are used in the from clause of the select statement. Here we have on screen two tables sales Order Detail and Sales Order Header. And we can see that there is a primary key. Primary key means it’s got to be unique in that particular table. So the primary key for Sales Order Header is just this one column and it’s unique. The primary key for Sales Order Detail is the combination of these two columns so 7174 is not unique by itself in this table but when used in conjunction with Sales Order Detail ID, the combination is unique.

So we’ve got this one table at the top. Now I want to put in, say, the due date, the ship date, which doesn’t exist in this particular table. Well, I can do that by joining them together. So there are lots of different kinds of joins left joins, right joins. I don’t think we need to worry too much about the types of joins in this particular course. I’m just going to say join at the moment, so I’m going to join these two together and what’s usually good is putting an alias to these. So this is the sales order detail and this is the sales order header, so we need to say how they join, what is the common linkage? And the common linkage is a sales order ID. Now, this can also be called a foreign key in another table. So it’s unique. It’s a primary key in another key, the foreign key, which is in a different table, it’s not necessarily unique. So we say on so on the detail sales order ID, where it is equal to the header sales order ID, so it joins them together only when this condition is true.

Now you can have different types of conditions left right is less than, it’s greater than, I’m not going to worry about those too much in this particular cost. So if I execute this now we just have the one big table so we have all of the detail from the sales order detail connected with all of the detail in the sales order header so we now have the due date, the order date, the ship date and so forth. Now this will cause duplication of columns so sales order ID is in both of those and therefore we will have two sales order ID. So it’s usual at this stage to say well I want just these particular columns so I want the sales order ID from one of these tables and I want the order date.

I don’t necessarily have to specify which particular table as long as order date is only in one of them and I want the order quantities let’s say. So if I reduce the number of columns then that gets me information from both of them, information from one table and information from another and then for instance I could group by and order and all the other clauses of the select statement. So the joins, this is when you have more than one table and you are joining them together in the front clause and you can use left joins, right joins and many more types of joins. This default is called an join and that’s one we will generally be using in this course.

• Category: Uncategorized