 Welcome to this course and this course is called get them started in the sequel server and it's for people who have never used a sequel server before for them to get started in the sequel server so it will show the basics of downloading and installing sequel server and we will go through some basic sequel server commands for you to get started in. After this we will show you a training path on what you can do next after you have finished this course. So this course is for people who have never used a sequel server before or want to get started in the sequel server. I hope you enjoy the course and let's continue. Hello and for this session of the course what I will do I will download sequel server and install it onto this operating system if you just follow what I do you should be fine so what you need to do is first open up your favourite web browser and search engine so I will do that here and I will just go to google and next I will type in sequel server download and press enter and I will go to sequel server download and click on this and this should take me to Microsoft website for sequel server any second now and on here it will show you all the different types of offerings from macrosoft about sequel server and we just go down here you have got developer and express. Developer is the full scale version of sequel server and sequel server comes in many different editions once express developer standard enterprise like the higher end like standard and enterprise you have to pay for when it costs like thousands or hundreds and thousands of pounds to use these in a production environment but with developer you can download this for free and you can use this to develop your skills and sequel server or to develop something or test things out in a test environment and express is also free as well but it's a cut down version of sequel that you can use for development and you can also use this in production you can't use developer in a production environment so I can always use this for testing things out or just develop your skills so what we will do is download sequel server developer edition it doesn't really matter which year you download just to make sure you get the latest one as this is just an introductory type of course for sequel server it doesn't really matter which version of developer edition you download so at the time of me doing this course sequel server 2017 is the latest version so what you might be using a different developer version depending when you get to watch this so I'll download it now like I can the download button so on here now at the bottom of screen on this particular version of my web browser I can just save it so I'll just save it and take that long for more seconds to go it's pretty quick usually and once that is done the next thing to do is well after it's done the security scan of the file just press the run button and this is part it's just it's it's just like a pre installation of sequel server so you got three options basic custom and download media basic is just a very basic installation of the sequel server and custom you can actually pick and choose what you want to install and download media is for when you want to download the media and install later on a different system or different server or PC so you can put it like download until you like a USB drive take out a USB drive and install it somewhere else and what we'll do we will do a custom install so I'll click on custom and that's this is a server where you want the media to download so it will create this folder and then put install make sure you've got enough space install and this will just take a while to download install package and this looks like it's going to take a while so what I will do I'll just pause the video here and start it again once the download is complete so now sequel server has been downloaded you're now ready to install sequel server and the way you do this is to click on the installation tab and click new service standalone installation or add features to exist installation you just click on this and it will open up another window and with this window you can select more options on how to install sequel server so with this screen you just click on developer and click next and accept the license terms and conditions you want to read all that not many people do but if you want to really can read it and after this it's up to you to if you want to click this Microsoft update check updates for this example I'm to only place off and now it's going to do some more tests it just has to see if it's possible to install sequel server if you if you get any fails on here just look on one of these there's come on these and it'll tell you what you need to do to get past a fail and that's just a warning it's fine so for this one you can press next after you've got all the past at least all passes and then you have a warning and here what we're going to do is click on the database engine service and what else do we need and for this example for this test for this course you can click client tools connectivity and that's it we should be fine with that and click next you can you can change your direction but I'm to leave my defaults where we're to install the software here and on the next screen so for this you've got two choices default and names and later on in more advanced courses we can go through what they've been default and named instance but for this course just stick to the defaults so just click next waiting then on this screen press next again on this screen press add current user which is you'll show be yourself yeah it's going there took its time but we've got there in the end just leave on Windows authentication mode and just make sure you click this add current user and it'll see screen looks a bit like this and press next press install so this is going to install SQL server and what I'll do I'll pause this video and come back to you once it's done so now that it's installed and we can press close here and then close this window and the next thing that we need to do is download a thing called SQL server management studio so really this is like a front-end for you to connect and do lots of development and administration on SQL server using this front-end tool so what you need to do is click on install SQL server management tools and also open up on your browser and what you need to do is just click on this whatever downloads available to you at this time click on the latest one so I'm going to download this download SQL server management tools 17.91 it might be a different version what you what you have but this is the latest version for me because I just click on this I'll save this it's going to take three minutes just see yep it's going to take four minutes now so what I'll do again I'll pause this video and once it's downloaded I will come back and we'll go through installing this as well so now it's downloaded you now have the ability to install this front-end so that you can connect to SQL server so on my browser I can click the run button so if you've got a run button just click on that then this screen should appear and just click install and this shouldn't take this that long just going through some basic installation stuff that Microsoft like to do so I can tell this not going to take that long after this installed I will give you a quick tour around SQL server management studio now that it's installed I will just take you on a quick tour around SQL server management studio so you just press this close button close this as well and if you go to your start button and you click on this SQL server server whatever edition you have got and you'll see Microsoft SQL server tools and in there you will see Microsoft SQL server management studio so if you click on this and you'll see the splash screen any second no she'll actually take this long I think she'll take about 10 more seconds 10 9 8 7 quick on that and server name is the name of your PC my PC is called win and this number so this is the name of my server and Windows authentication is you your login name so on this screen just press connect and this is SQL server management studio with this tool you are able to look at different databases set security on your database create tables insert information to tables do some diagnostics it could do you can do practically most everything my most stuff from this tool and we will go through all the different well a lot of the different sections of how to navigate around SQL server management studio during this course but for this point of the course it's just good enough for you to download SQL server and install SQL server and get to this point and the next section will start to delve into doing something with SQL server that is like inserting data finding data creating databases and things like that so this is the end of this section and I look forward to you starting the next section for this lecture I will talk about what is a database and why do we need them many years ago the only way to store information in a secure place was by using a filing cabinet this was because you could put the filing cabinet in a secure room with limited access to people who were allowed to read the information then you would put the information in a filing cabinet that also had a lock this way if someone gained access to the secure room they would need another key to get inside the filing cabinet fast forward to today the same principles apply to database systems where you need special access to the server then you would need access to the database to view the information a database is just a modern version of a filing cabinet that is located within the computer and the advantages are that you can find delete and update your information quicker and compared to a paper based filing cabinet most companies in the world will use a database to store information and the devices that you already use will also contain a database you probably already use a database on your phone tv and other electronic devices and you don't even realize you're using them so now you understand what a database is I'll talk about tables in the next lecture so what is a table a table is where you store information that you gather from a source tables are very similar to an excel spreadsheet where you would have columns and rows you would insert information into the table like a spreadsheet and save the data but instead of serving the data to a file you would save it within the database to make a row in the table uniquely identifiable you would add a primary key with the primary key you will be able to search the table to find the correct entry by knowing the primary key value that you want columns are defined by its data type so you can have numeric text based and date type columns to store your data using the correct data type helps with the validation of the data to be stored so that is a basic explanation of what a table is for this lecture I will show you how to create the test database with test data inside so the other thing you need to do is go to the left hand side and right click databases then click new database then type in test database then press okay then the next thing you need to do is download the setup underscore create a database file which is contained in this lecture as a resource and once it's downloaded is just file open and go to file here and find a file that you just downloaded from the resource and open it up into management studio once it's open it will look something like this then the other thing you need to do after that is press the execute button and it will create all the test data for you and your screen should look like this once you've done that you can proceed on to the next lecture for this lecture I will talk about the select statement and we will be talking about two clauses of the select statement and that is the work clause and the order by clause so the work clause is used to filter out things that you do need or you don't need so if you wanted to know for example from this table you want to know all the employees where the employee ID is to you can do that by using the work clause so if I wanted to do this and find out where all the employees IDs are to I'll type in from this select statement where employee ID equals 2 and press execute and as you can see I've got one employee where the ID is to if I wanted to do by the first name so what I'll do now I'll bring all the table back by highlighting the first section of this select statement I did that by holding down the right sorry the left mouse button and highlighting it and just dragging it across and pressing execute and that will bring everybody back if I was to do all of it I'll just get the two after some of it it's I'll get everybody so if I wanted all the employees were the first name is Imran I'll just change this to first name and change the variable to Imran and press execute and it will bring back the employee so again I'll bring back everybody back again and another thing that you can do is filter by a numeric value so it's greater than or less than value so for example if I wanted to find all the employees who are older than 30 I can just type in age where age is greater than 30 and press execute and it'll bring back these two employees because the first employee if you if I if I bring everybody back is only 28 so that is the work clause and you you can experiment by doing things by changing that to a less than then it will bring back does that one employee um do this clause that we're going to look at is the order by clause so if you wanted to order this list by the salary so if I put order by salary in in this order 40,000, 60,000, 70,000 if I wanted to do it by descending order I'd put desk and it'd do the way around 70 60 70,000 60,000 40,000 and the default is asynchronous but you don't really have to put ASC as you can see it's the default option anyway so you can just type in order by and column name and that is the order by statement for this lecture I will show you how to use the union command the union command is used to join two identical structured results sets together when I mean structured results sets I mean the exact amount of columns in both results sets so for example I've got this result set here and that brings back three records with these five columns and I've got another result set what brings back one row with the exact same five columns so if I wanted to join these two results sets together the other thing I need to do is type in the word union between them both and this will bring back both results sets and it will also remove any duplications if you want the result set to bring back any duplicate rows the only thing you need to do is type in the word all after the word union and press the execute button and as you can see it has brought back four results sets now with the duplicates showing so in conclusion the union brings back two identical structured results sets into one result set and you have a choice of bringing back with or without duplication for this lecture I will show you how to use the date functions in tcql I'll go through some of the most popular date functions that you will use and for this I'll start by getting today's date and as you can see today's date is the 28th of April 2019 if I wanted to add one day onto this date I can use the date add function and the syntax for the date add function is date add open bracket the interval that you want to add how many days you want to add and the actual variable what you're going to pass into this function if I was to run this it has added one day onto today's date so you can see it now says the 29th of April and if I want to take one day off and run it as you can see it is now the 27th of April so in the date add function the d is telling you what the interval is going to be so for example d means day and m means month so if I wanted to take one month off as you can see it's now the third of March and two months down now it's in February and if I want to change this to years if I type in years like that and take off one year and run it as you can see it's 2018 if I was to take off 10 years it's 2009 so that is how you add and subtract years from today's date or a date that you'd pass into this date add function if you wanted to only show a part of the date so today's date is the 28th of April and you only wanted to know from that result set what is the month you can by running this command or this function called date part function and from this result set you can see it is showing four because it's April and it's a fourth month of the year if I wanted to only show the day I'd run or change the m to a d and now it's showing 28 if I only wanted to see the year and execute that it's showing the year there is also a different way I could have done this by using the month function so as you can see I've got a month open bracket here and if I execute that it is showing the month if I execute the year function it's showing the year so these five are five different methods of splitting apart a date to give you the only information that you desire so if you only wanted the month today the year you can use one of these functions another way of splitting up the date is by using date name so for example if I run this this is showing that it's a Sunday because it's showing the name of the date and if I was to put in a month like for example in this query it is showing April it's showing the name of the date so if you want to find out the name of the date you use the function date name and one more example of how you can manipulate the date is using date diff date diff calculates the difference between two dates so here I have got an interval of d which stands for day I've got one date here the first of January 2019 and I've got another date value the 10th of January 2019 so this command will calculate the day's difference between the first of January and the 10th of January so these commands are the most popular type of date functions that you will use in sql for this lecture I will talk about what it means to be a database administrator so to become a database administrator you have to make sure that you look after the security of the database that the database is performing efficiently and making sure that the backups and restore process of the databases are working so there are many tools what can help to do this so in this demo I'll just go through them really really quickly so with the databases you have to make sure that it sets up correctly and that's by looking in the properties of each database and selecting the right configurations and with the server you have to right click on the properties again and click the right settings like the right amount of memory to set for the server any advanced features you have to enable or disable you also have to make sure that the databases are running correctly so you do look at the database backup and the restore process in this submenu and you can also make sure that there are not unforeseen errors in the sql server instance so you check the error logs by clicking on one of these error log listings here also you have to make sure that it's performing correctly so you'd go into each table and you would put indexes on the table and rebuild the indexes and so you can also with being a sql server database administrator make sure that if you've got any jobs running and a job is a process that you can run or schedule at a convenient time so it's just to make sure that any if any jobs are running that they are working correctly and it sends out the proper error messages out to the relevant people that if it works or it fails so there were many tasks being a database administrator so I would say that you have to make sure that you rebuild all the indexes and the server and the databases are performing correctly making sure that the backup restore process is working and looking after the security of the server in my sql server boot camp course I will go through how to do this in more detail but for this demo I've just gone through the different types of options that there are and what you need to learn and to become a sql server database administrator a sql server database administrator is a lot different than being a sql server developer because a sql server developer will know you would have to know more tcql how to use ssis and power bi a database administrator is more to do the back end on how the the server is performing and how how secure the database and server is so that's around the about it this is a quick overview of what it means to be a sql server database administrator for this lecture I will show you what the power bi is and how it can be linked onto sql server so power bi is a reporting tool that you can link onto sql server so in this example I will link onto sql server to retrieve basic information and put it into a graphical report so I'll go through this quickly so what I will do I'll just get some data from sql server and get some test data I'll just pick the employees table and press load so that's imported data from out of sql server into power bi and on the right hand side you can see some m columns from the imported data first name or last name salary and other things there so if I was to click on the first name and the last name it appears onto this canvas so I can drag this canvas around put it into the middle and add other things as well and I can also resize this and over here are some visualizations where I can change this into show different graphical visualizations in the sql server bootcamp course I'll go through more advanced features of power bi and how you can link sql server onto power bi and create better visualizations how to filter data how to do custom formatting and how to produce useful reports for people so again this is a sql server bootcamp course if you go straight off to there it'll give you a better overview on how to use power bi but for basics I've just shown you a really easy way of importing data from sql server and creating your own reports for this lecture I'm going to show you how to create a sql server database within AWS so I'm going to create a basic msql database by using rds so I will click on this rds link here then once the screen appears I will click on the create database tab here then on the screen I have a choice of different types of engine types I will click on sql server then I'll click on the express and I'll just make it a free one then this is just like the database instance name so I'm going to keep our default I will give it a password then I will just keep with all the defaults and press create database then at this moment in time sql server is being created that is how easy it is to create a sql server express edition database so if you want to learn a bit more on different types of settings that you can do you can join my sql server bootcamp course and we'll go through another different types of ways to create sql server and linking it from your desktop onto sql server and go through sql server security settings on AWS and how to backup database as well so while this is creating you can just pop along and join the course now but that is how easy it is to create a sql server express edition database