Pass Microsoft Azure Database DP-300 Exam in First Attempt Easily

Review prerequisites

1. Getting a free trial of Microsoft Azure

So, let's get our free account, which you can see in the free add at the top of your Azure.com account, though you could also just google it as your free credit and see what you get. So let's click on that, and you can see we have start for free" button that is quite enticing at the top. So what do you get? You get twelve months of certain services, $200 to be used in the first 30 days, and 25 or more services, which are always free. And you can see what services are free. For instance, a virtual machine can be free for 750 hours. So what's that? That's about a month. So it's free every month for a year. However, bear in mind that that's not necessarily the same as storage. So you'll find that there is, I think, storage down here, but it's very minimal if there is. But you also notice SQL databases, three 4250gb. Now there's storage, 5GB of file storage, and 5GB of something that's called Blob storage. So we'll have a look at what those are as part of this course. so it's worth getting. Now I should point out that if you go over any limits, you will have to pay. And unfortunately, I can't be held responsible for any expenses that you have. But I will be showing how to pause certain resources. So when you pause it, it doesn’t necessarily cost you any money. So I'm going to click "Start for Free," and you can see we're now at the sign-up page. So first of all, I need to enter some details. So there's a bit of waiting, and you'll find that this is quite often a thing that happens. Unfortunately, with Max Officer, there will be a fair bit of waiting. Now you don't actually have to do for this certification anything practical in Microsoft Azure, you just need to learn about it. However, I do find that it's actually quite good to actually do it, and then you've got a better visual memory of what each of these particular services are. As you can see, it's inviting me to take a card-based identity verification. So yes, there is agreement. Yes, I do want to agree to the subscription agreement. The offer details and privacy statement are worth reading. I don't want Microsoft to share my information with select partners, though it is interesting to get information about Zero. So I'll sign up. So confirming your information, I'm just going to say not sure why tenders use to click submit their feedback sent. So we're now setting up the account and as I say, it does take a bit of awhile to do some things like setting up virtual machines, setting up Cosmos DB databases and so forth. But eventually you will get into the Microsoft Azure account. And this is the Quick Start Center, so you could have a look at each of these if you want to find out how to create a web account, deploy a virtual machine, set up a database, and so forth. However, I'm going to actually know all of this. And indeed, there is another section: take an online course. So this is good, but it's probably not focused on what we're doing in this course. Instead, I'm going to click on this home button, and this is where I will generally start each of these practical videos. So as I say, you don't actually have to do any of these practical things to get the certification, but it probably helps if you know what's happening. And I'm also generally going to expand this menu as well. So you can see we can quickly get access to things like SQL databases, Azure Cosmos databases, and other things. So let's just take a quick look around Microsoft Azure. So as I said, this is a quick way of getting to common places like, for instance, cost management and billing. So if you avoid how much things are going to cost, don't worry, you can information there. You can click on all services, so you're not just restricted to the little bits that you see. So if I wanted to go to storage or databases, they're all clickable there. Alternatively, I could just click at the top and search for a particular resource. So maybe I'm looking for an Azure SQL database, and you can see it's now suggesting SQL databases as a suggestion. Over here, we have got a command-line interface. We'll use that later. But very briefly, the shell is not much used in the certification, but you will have to know things about it. We have your directory and subscription over here, so all we need is information about what you want. Currently we're on the free trial, then we've got notifications. So what is currently happening? So anything that happens—if you set up anything, you'll get a notification that it's being set up, and then it has been set up. We have several options, the most important of which is where you want to be. So do you want to start at home, which I generally do, or do you want to start on your own dashboard? So these are little elements that you pin to a dashboard. What theme do you want that could be quite useful? If you need a dark or high contrast theme, for example, use the question mark. This provides you with various assistance and then informs us of your progress. And then right at the end, we have your sign-out button. So this is a quick look around Azure. And in the next video we're going to start using Azure. We're going to install a new database with some sample data.

2. Creating Azure SQL Database with sample data

In this video, we're going to create our very first Microsoft Azure database. So here, I'm back at the home. So if you've gone elsewhere and you see home at the top left, then please click on it. Now there are two places that I could go. Or I could go to SQL databases. Or I could go to Azure. SQL. And if you don't see any of these, then you can just type them at the top. So Azure SQL So if I click on Create at the top left, this will then say, okay, you're looking at SQL, but there's actually multiple Azure SQL you might be using. And we'll be using all three of these in this course. We'll be focusing on the standalone SQL database at the start of this course, and for the majority of it. So this is a single database controlled by a server, but you don't actually control the server. Microsoft does that for you. Then you have SQL-managed instances. So this is closer to your on-premises database. So you have a server that you can control an SQL server on, but you don't have an operating system. Again, this is all done in the cloud. And then we've got an SQL Virtual Machine. And here you can control the operating system as well as the SQL server. However, it's a lot more expensive. So I'm just going to create a single database. I could be creating an elastic pool as well, but I will just create a single SQL database. so I'll click Create. What we have to do well, first of all, is create a subscription. You probably already have a subscription, so if you have one, just select it. Then you need to create a resource group. So, if you don't already have one, click "Create New." So I'm going to call this the DP 300 SQL database. So that's my resource group. So Resource group contains all the items that will be used for this particular resource. So unsurprisingly, it will contain the database, but it may also contain other things. Then we need a database name. So I'm going to call it DP 300. Now have a look at the requirements. Database names should not match any special patterns, so it shouldn't be an obvious thing. There are up to 128 characters, no reserved words, and no database with the same name exists on the server. Now, I haven't actually created a server, so we need to create a server. And if you already have an existing server, you can select it. But I'll click create new. So here we have the server name. Now it's this thing that needs to be unique across all of Azure. So for instance, let's put in DP 300and you can see fine for reserved words. But it's not fine because it only needs to have lowercase letters, numbers and a hyphen. So I've put in capital letters. So let's change that to lowercase so now it's checking. And here, you can see the specified server name is already in use. Somebody is beating me to it. So I'm going to put the word database after it. And so you can see it's available. Right so now we need an administrator login. So I'm going to have my login as DP 300. So you can see, you can't use typical names like admin administrator, S A, which is short for systems administrator, route, DB manager, login manager, that sort of thing. So again can't include non alphoneric characters. So you could have capital DP this time, perfectly fine. Now we need a password. So what I'm going to do is write a password in my notepad and then I'm going to copy and paste it in. So that way I've got a copy of this password. So you can see it needs to be at least eight characters, no more than 128 characters. I hate to even think what password with 128 characters might look like. And it can contain upper- and lower-case numbers, non-alphanumeric characters, and symbols and must contain at least three of those categories. And I can't use, for instance, DP 300 and then something else. So I'll just confirm the password, and then I'll put in a location. So the location should be roughly near you for the greatest access. So I could put it in Australia. East. But then every time that I access it, then my computer would have to go through Azure. Azure would then send a signal all the way to Australia. Australia would send a signal back. It just adds additional latency. So what I'm going to do is I’m going to put it into UK South. Obviously you might have a different location. So this is your server. So make sure you have a copy of your server name, your server admin login and your password. So I've just copied them alto a notepad, which is off-screen. So let's click. Okay. So you can see that here we have this new server. It's not yet been created, it's just been marked. All the details have been saved. It's going to create all this in a string, in a JSON string, JSON string. So I can save this password as well. Whether you choose to do so, down to you. Do you want to create an SQL elastic pool? We'll have a look at elastic pools later. What about the compute and storage requirements? Well, I'm going to click on "Configure Database." So you can see we've got some dots at the top. It just means that it's working. So once the computer is finished thinking, then you can see we have got a service tier. In fact, there are six different service tiers. We'll be going through them all in later videos and you can see an estimated cost about $500 a month. And we can also change the configuration. But what I'm going to do is decide it's a bit too expensive for what I want at the moment. I'm just going to take it all the way back to a basic service tier. So now the estimated cost is about $7 a month. Much better. You can also change a few options, but that won't actually change the cost. So what I'm going to do is put it all the way up to the top. So 2GB for basic service tier. So click Apply. How do you want your data to be backed up? Well, default, which I'm going to leave it as is geo redundant backup storage. So that's fine. So let's go through the other types. We've got networking. Now as I said before, security isn't going through in a lot of requirements for this particular certification in the sense of networking. So what I'm going to do, I'm going to have a public endpoint so I can access it from outside suppose to a private endpoint, which allows it to be a bit more secure, but a lot more complex, at least at the very beginning. And I'm going to say, shall I allow Azure Services and resources access? Yes. And shall I add me my current IP internet Protocol address? I'm going to say yes as well to that. So we've also got other things, connection policies, how clients connect to your database, and also your TLS versions as transport layer security. So I'm just going to leave that as insecurity you could enable as your defender for SQL. So that is a package including vulnerability assessment. But this is not going to contain sensitive data, infect it's just going to contain the standard data. So I'm going to say not now. Additional settings for my data source. I'm going to start with existing data, I’m going to start with some sample data. So there we go. Tags. I don't need to worry about that's. just having name-value pairs that allow you to search for a particular service and click Review and Create. So you can see an estimated cost of about $7 a month. And here's everything else all looks fine. Click Create down at the bottom. You'll also notice that download a template for automation. We'll have a look at templates later on. So click create validating, initializing deploying. It will take a couple of minutes or so, so just pause this video and you can see while it’s doing this, we've got these notifications up here. So if I click on notifications, it says okay; it is deploying in progress. So I can dismiss this. But you see this little line continues. So it's going to go through various stages here. So now you can see the list of requirements has expanded. We've got things like databases and firewall rules as well as the server database. And here you can see that itis successful, your deployment is complete. So I can click on go to results, but instead what I’ll do is I'll go home and I will go to SQL databases and here we can see our results, our database. So if I click inside of it here, we can see our database. It's part of a resource group. So if I was to open resource groups, you can see what's inside the resource group. We have an SQL Server. So this is again something that you can't actually control. Much of you have a few settings in here, but you can't say, for instance, "I want SQL Server 2016." This is a particular server and you've got a few things that you can access. Within that we have got a database. And again, you've got a few things that you can do with this database. Now if at some point you decide, okay, I'd no longer want this database, then you can click on Delete. When you click on Delete, it will say Are you sure? And it will ask you to type the database name. Now the database name is up here, so you could just copy and paste it. So until you do that, you can't actually click delete. However, a better way will be to go back to home, go to the resource group in which it is contained, and delete there source group so you may see a delete later on. That's not the one we want. It's Delete resource group. And again it asks me, are you sure you have to type in the resource group name? You can't accidentally delete it. In this video we have installed our very first SQL database. It has some sample data in and in the next video we're going to connect to that data.

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 okay. And here is what we've got so I can expand this. And here are all of the tables that we've got. We've got various views and we've got still procedures as well. So I could say for instance, select Starfrom Saleslt Address and unlike some products I can't actually just drag it across. I do have some options like select Top100 rows, 1000 rows, or Edit Data. But if I just type in Saleslt 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. If you just Google it, find that the latest version. So in my particular case it's18.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, click on previous SSMS releases and you probably want to install version 17, nine one, 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. All 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 de? So yes, and All I do is click Install and it downloads and installs everything that it needs. Now Microsoft SQL Server management studio S SMS 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. 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 Escrow 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 with, but this connect with is only useful for Azure Data Studio. 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 Explorerand 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 authentication 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 upas you were setting up the database. And I'm going to copy and paste mypassword 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 handside, 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 stored 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 Starfrom Salesltress, 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 mypassword 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 particularserver, 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 soI can close this page. And inside this azure I have got my SQL database. If I drag this up there, we have got tablesand views and stored procedures just like in SSMS. And if I don't want to see this service at the top, I can click on the dose and say no thank you, to servers, no thank you to SQL Server big clusters. So again, what I can do here, select StarFrom and make sure I'm in the right database. So here's my saleslt 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 Macand 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 logged 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 oneIP address, and now I can connect. So it's just an additional thing that you need to be aware of. If you've only added in your individual IP address when you reboot, you're 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're 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 by 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 up 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 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 dialog 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 saferway of passing information through. I'm not going to be using it for this cost, but we'll have a look at Always Encrypted later. So if you get that you can click on it. Do not show me this message again. So I'm going to click on Close NotEnable to get rid of this dialog box. So here we are with just one select close 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 salesaut address. It could be view, it could be other things. So I'll go select 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, 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 will 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 bothell. For instance, and ensure that any stringliterals 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 if I change this to is under 15 and city equals bottle, then here we can see these two or we can say or 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 bottle. 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 this 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 counter. 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 a Japanese format. It's also used in Korea and China and a few other places as well. So that's the way 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 close. 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 has 115 rows, UnitedKingdom 40, united States 295. Now if I wanted to filter down this further, the where filters down the individual rods 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 role or two roles. I just want those where the count star is greater than 100. So that just gives me two roles. And then finally I've got order by. 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'll be using some SQL select statements, but we won't be looking at how they're built up necessarily. We won't be saying 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 closers, so it doesn't get much more complicated than what you see onscreen, apart from one thing joins. I'm going to have a look at joins in the next video. Bye.

5. Review JOINs

In this video, we'll go over joins and how they're used in the select statement's from clause. Here, we have two tables, Sales Order Detail and Sales Order Header, and we can see that there is a primary key. A primary key means it has to be unique in that 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 714 i The combination is unique, so we've got this one table at the top. Now I want to put in the due date. The ship date, which doesn't exist in this particular table well, doesn't exist either, but I can do that by joining them together, so there are lots of different kinds of joints left to join. Right joins I don't think we need to worry too much about the types of joints 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? 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 phone key, which is in a different table; it's not always unique, so we say on to the detail Sales Order ID, where it's equal to the header Sales Order ID, so it only joins them together when this condition is true. Right. It's less than It's greater than I'm not going to worry about those too much in this particular course, 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 on. Because the Sales Order ID is in both of those, we will have two Sales Order IDs. It's common at this point to say, "Well, I just want 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 I want as long as the order date is only in one of them and I want the order quantities. If I reduce the number of columns, that gets me information from both of them--information from one table and information from another and then, for instance, I could group by order and all the other clauses of the select statement. So the join 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 what we will generally be using in this course.

Hide