70-461 Session 1: Querying Microsoft SQL Server 2012/2014 | Phillip Burton | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

70-461 Session 1: Querying Microsoft SQL Server 2012/2014

teacher avatar Phillip Burton

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

31 Lessons (3h 38m)
    • 1. Introduction

    • 2. Curriculum

    • 3. Downloading SQL Server

    • 4. Installing SQL Server

    • 5. Opening SQL Server

    • 6. Looking at SQL Server Management Studio

    • 7. Create a database

    • 8. Creating our first queries

    • 9. Creating a table - first pass using GUI

    • 10. Creating a table - first pass using T-SQL

    • 11. Entering data using the GUI

    • 12. Entering data using T-SQL

    • 13. Retrieving data

    • 14. Deleting the data, then the table

    • 15. Creating an Employee table

    • 16. Creating temporary variables

    • 17. Integer numbers

    • 18. Non-integer numbers

    • 19. Mathematical functions

    • 20. Converting between number types

    • 21. Strings

    • 22. String Functions - extraction

    • 23. NULL - an introduction

    • 24. Joining two strings or a string and number together

    • 25. Joining a string to a number

    • 26. Date data types

    • 27. Setting dates and Date extraction

    • 28. Today's date, and more date functions

    • 29. Date offset

    • 30. Converting from dates to strings

    • 31. End of Session 1

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

This course is the foundation for the Microsoft Certificate 70-461: "Querying Microsoft SQL Server 2012/2014".

Rather than present one huge course, this course takes some of the basics and then goes in depth. The basics presented are: how to install SQL Server, and how to create and drop tables.

We then try to create a more advanced table, but find that we need to know more about data types - so we go into some detail about data types and data functions, the foundation of T-SQL.

No prior knowledge is required - I'll even show you how to install SQL Server on your computer for free!

There are regular quizzes to help you remember the information.

Once finished, you will know what how to manipulate numbers, strings and dates, and create database and tables, and have an appreciation of how they can all be used in T-SQL.

Meet Your Teacher

Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.

He enjoys investigating data, which allows me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.

He has also developed expertise and programmes to catalogue and process and con... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Introduction: and thank you for joining me for this course on Rocket Soft SQL Server. I'm fit person. A far cat stuck called it UK and I'll be taking you through this session One off 70-461 Clearing Microsoft SQL Server 2012 Today I'll be introducing you to the world off. Tsk. We'll be downloading and installing SQL Server for three so that you can use it on your own machine would then be creating a database on our first table. We'll enter some data and there will delete the data on the table. Well, then try and expand this table and run into problems. We're going to find out that we need to know a lot more about date string on number times before we continue. So we look at these data types and also days of functions in some detail. Now there's a functions allow you to manipulate dates, strings and numbers. So overall that this first session will allows start objectives 168 and 12 off the 70-461 exam, this course is ideal. If you want to learn SQL from scratch, you don't even need to be taking the exam to learn it. You don't need any pride database tools, just a willingness to learn we'll be taking this day nice and slowly on future sessions will build on what we learned from this very first session. So why have I created this cost? That there are already several video courses available regarding Microsoft SQL Server. Indeed, one of the most popular ones is just over two hours long. SQL cannot be learned social time, and even a 12 hour course is not sufficient. Residential courses teach SQL over five days, and this is a more realistic amount of time, which is needed. And don't be teaching this in bite size chunks of morning session and then an afternoon session. I'm afraid you'll have to provide the food, though, but overall over all of these sessions, I intend to take you through all of the objectives which are listed for the 70-461 example . By the end, you should be confident about SQL and looking forward to taking on passing your exam. So without any further ado, let's have a look at the objectives for this exam 2. Curriculum: just before we start the course. A word about video clarity. Now this video cost was recorded in high definition, but on some computers it might look like it isn't now if you can't see it clearly in high definition, if it looks a little bit fuzzy, then please click the high definition button near the bottom of your video it to the right off the time bar and it's hate D might be very difficult to see. It might be black on black. Almost kick that on. The quality will improve enormously. I thank you and let's start the course now. This course is going to follow the objectives in Exam 70-461 Quitting Microsoft SQL Server 2012 And you can get those objectives yourself by going to Google and typing 70 Dutch 461 Now the objectives are creating database objects, working with data modified data on troubleshooting optimize. But when you expand that, you'll see that there are lots of sub specifications. Andi is not really the best fall I find for this. So what I've done is I've expended it and created a Microsoft Power Point presentation. So these are the sub objectives for creating database objects. So we're talking tables, views, design views, constraints and triggers. And they're working with data, modifying data and troubleshooting and optimizing. And for each of these sub items, I've created the subsidy items that were going to be following. Now this is day one. It's a introductory day. And so I'm assuming you have absolutely no experience with SQL Server whatsoever. So we're going to find a way to instill SQL Server on your computer for three Onda. We are going to very slowly work through the beginning process of it. We're going to be concentrating on objective number one, creating and altering tables using TC course syntax Andi Objective number eight implementing data types. So I'm going to be typing at the beginning. All of what I'm going to be teaching with you saw to give you a lot of time just to absorb hitting. So I'm not going at 100 miles an hour. Later, on towards the end of day one, I'll be presenting some called our previously tight and we'll work through it. But this first day is going to be Falih gentle now. I mentioned in the previous lecture that in the introduction that there are courses you can take on who are example of a five day course on 70 Dutch 461 and you can see it's about £2000. This residential course that's around $3200. It's around 604,800 euros. So what I wanted to do was to bring this quantity off instruction to an affordable level. And so we are going through similar information. Except you don't have to go anywhere. Have breakfast out. Didn't there have accommodation there? Andi, you get to revisit it later. Now there is one very important thing about what I won't be doing on this card on. If you have look at the exam policies and these are the same exam policies that you will have to abide by if you do this exam, Andi instantly. You can see down here that I have done this example. Yeah, acquiring Microsoft SQL Server 2012. This is what I want to be doing. If a candidate violates the following testing rules policies, then you may be de certified, and I don't want to be decertified from being a um, Microsoft Certified Solutions associate. So what toward her be doing? I want to be showing you how to use on authorized material attempting to satisfy certification requirements. Now, this includes brain dump material. Andi, I put this really high up here because if you Google 74 61 you will see that it offers you exams. Exam questions. Do not do that. Don't even look at them. They are against Microsoft policy and any attempt to read them in a hope off passing the exams is misconduct equally. I won't be copying or publishing the exam in whole or in part, so I won't be giving you questions that I went through. Sorry, I'm not allowed to do that. I'm not allowed to disseminate actual exam content. What I can do His tail that measures up is the official Microsoft certification. For this exam, you can see they are certification track partners. If we go back to this pit webpage on 70 Dutch 461 you can see that there is a Microsoft official practice test from measure up dot com. So if you do want any of the exam questions, that won't be coming up, but would be of a similar standard. They go to measure up dot com, type in 70 Dash 461 and you can get the practice test. I don't really recommend the certificate tool kits. I don't really recommend the practice labs. I feed the practice test 139 questions more than sufficient for what you need, as long as you are able to learn the stuff. Fruits, this video. So what I will be doing in these videos is going through these requirements on teaching them. Hopefully, you know, easy to understand way we've quizzes so that what I've just told you are able to say, Yes, I understand it because I've now just answered related questions on it. Now I should mention that Microsoft also has an official guide on this book, and it's a pretty good book. It, too, has some quizzes, though they are varying standards it teaches, the examine tells a teacher's suddenly relevant stuff for by relevant I mean, it's not on the objectives, the official objectives that I've just pointed out to you. So if you go to your favorite bookseller, type in 70-461 you'll see that this Blue Microsoft book. That's the official training manual. I do recommend it. However. There's a lot of Virata lots of mistakes in it as well. So I would suggest you also have a look at the Erato as well to get a complete list of the errors. It's several pages long, unfortunately, so let's start day number one. And by the end of the day, you be able to talk about data types, and you be able to create Andi altar tables using T SQL. So. But let's begin by finding and downloading sequel server so that you can follow on on your computer. It's very important that you actually do some typing. You do some learning by making mistakes or going. Yes, I got it right. That's the only way you will be able to retain this information afterwards. So let's find SQL Server and downloaded 3. Downloading SQL Server: now the first thing we have to do is to download the software for SQL Service on as we are Doing Max Off Certification 70-461 Quoting Microsoft SQL Server 2012 It makes sense down Lord the 2012 version. So while I love to give you a specific, you are, well, it keeps changing. So the best thing I've been to suggest you do is to open on for Web browser, go to Google and typing SQL Server Express 2012 S P. Two. This gives you the latest version. Service pack two off SQL Server 2012. You can see that the first hit here is from Microsoft dot com. It's important that you downloaded from a Microsoft website. So when I click on that, you see that even though this is described as a server, you don't actually need a server to download it on. Look at the number off operating systems that you can download. That's in all the way down to Windows Vista Service back to and all the way up to Windows 8.1. There are rumors it won't work on Windows 10 but as the full version has not been released yet. As off the time of recording, I don't know. So let's click, download and excuses a Dalek box with about 8 to 10 different items 100. Only the move. You can signal most of them. The only ones that you need are the ones saying SQL Express W T. That means with tools on. If you've got a 64 bit version off Windows, you need the X 64. If you got a 32 bit version of Windows, you need the X 86. Not sure which version of Windows you got. Then open Windows Explorer writing. Click on computer or my computer score two properties. And here you see 64 bit operating system. If you've got a modern computer, it's likely to be 64 bit. If you've got more than four gigabytes of RAM, it's almost certain to be 64 bit. So find the father you need. You can see here it's one. Put one gigabytes and click. Next. It, then say is it's going to download it. If it doesn't have tried this on multiple computers and it doesn't download it, there's a click next, which takes us back to the previous dialog box. We've in addition, a direct download link, so let's find a W t vision 64 bit on. We go click here and it starts downloading. So while it's downloading letters, just discuss the other versions that are available. First of all, the 2012 version is not the first version off escrow services being without going too far back. We have an escort service 2000 and then five years later, 2000 and five on the 2008 on, then every even number JIA. So there was a release in 2012 to 2010 in 2012 2014 and there's a beater coming out in 2016 . Andi name off this product, SQL Server. It's girl Survive SQL Server followed by the year each time except for one. The version that was released in 2012 No doubt for marketing purposes, they called it SQL Server 2008. Release to both 2000 and eight are too. No idea why they did. That still confuses people today, so we've got a few minutes while it downloads. So let me talk to you about the over versions off SQL Server that there are this work page shows that there are four enterprise, standard business intelligence and express enterprise being the very top, very expensive business. Intelligence is then the next version. Don't know why it's listed as fourth here. It's actually the second version it has more through to that enterprise. Halves Standard is a lower version. It excludes some of the more high end stuff that you need from multiple calls, multiple machines and then express is the free version. This, however, is missing one version, and it's a very important version. Is cold the developer version. This gives you most the tools that the enterprise version gives you. We've just won't have yet. It cannot be used for real life situations. It cannot be installed on a production server. It's license for demonstration use or development, learning, testing, that sort of thing. Basically, it's ideal for you, and if you find a good source, it's not that expensive. You see, it gives you the business intelligence tools. So if you are going down the SQL server route, if you want to do exams for 62 4 63 on for 66 4 67 then my advice. Choose to get the developer edition. It's fairly cheap. It gives you everything you need. But for now, for 4 61 all we need to do is look a t SQL on for that. The express version. It's fine. Now make sure you install service Pack two if you just type in SQL Server 2012 Express new major sketch. The standard version without service back to on that might not install on your machine. Obviously, back subsidence tweaking now, as you can see what a few minutes to go before it actually downloads. So why don't you download it for yourself? Remember, go to Google and type in SQL Server Express 2012 sp two Download it and I'll see you in the next video. 4. Installing SQL Server: running 12 SQL Express with tools has now downloaded. So however you click it, if you can see it, or if not, you might have to go to your download section in the Windows Explorer. So that's quick run. You can now minimize or even close the webpage just to get empty desktop. And now it's a bit of waiting. This will take about 10 minutes in total this process and there are unexpected weights now . First thing is that it is downloading on unzipping the files. So it's like we downloaded a huge sip and at the moment is just unzipping everything. You can now see the administration requesting administrative rights user account control. Do I want to allow SQL Server by Microsoft to make changes to this computer? Yes, I do. And now we're waiting for $2. Box is the 1st 1 is just a inter spatial is just saying Please wait, and then the 2nd 1 is a very complicated box. That wreath can ignore most of the complications. If you are going to do, call 70-462 We can explore everywhere about this, but for now we just need the menu installation that we can get away with. So we're in the installation top, and we want a new SQL Server Standalone installation. So having click that and now I can't click anymore won't allow me to. I can still click over here for one, but not this. I can now minimize this dialog box because this will bring up yes, another dialog box. There's going to be a lot of dialogue boxes. The first thing is going to do is check to make sure that your computer fulfills the minimum requirements like you got dot net. If you've got Windows Vista Service Pack two, it's possible you might not have the version of DOT Net that it needs. So if it does need it, it will ask you to download it. Here we have the license terms, a fairly standard life's standard license terms. Nothing unusual, so click to accept whether you want to click this box for feature usage data down to you. It would then see if there's any updates on this computer. It won't check, but you still allow me to click next, and now it is installing the installation files, so it's not actually installing the program yet we're still a bit of a distance from that. So now it's installing the fouls that it needs to install the server. Now I haven't chosen to install the 32 bit versions themselves having problems installing the 64 bit version at this stage. If you find you're getting messages saying that I'm unable to install a particular component, it's this one. Then try and install the 32 bit version instead for what we're going to do. There's no major difference. So what features to install? You can see we've already got dot net versions 3.5 and four. There are some to be installed. Do we need all of this? No, but we might still have all of it. This is expressed version for SQL Server developer. Then there'll be a lot more here, including tools for S s eyes, Which is example 63. Andi S s s s s R s, which is 4 66 4 67 So we are. It's going to say it's going to just tell me which Dr it's going to go on. So that's just select all click. Next on. Basically, it's a lot of clicking next. So database engine services is the 1st 1 first of all and named instance or defaults instance. It doesn't really matter which. So I'm caught leaving it as the named instance SQL Express. Then it's going to check that we got enough dis space got more than enough on that is going to talk to us about the configuration so we don't have to do anything here. So click next. Then it's going to talk to us about the database engine configuration, so we have to authentication molds. This is how you can actually get into the database, either using your Windows authentication or a combination off passwords and windows authentication for what we're going to do. Windows authentication. It's fine. You will add Jews Administrator. That's fine. Everyone else. I'm sure it's fine. Data directories who? It's on the drive so all of this is fine. Don't need to change anything. Let's go next. This a reporting? Do you want to send our messages to Microsoft or your corporate report server? Probably not next, and then finally we get to the installation. Progress on this is why it is going to install every single program, every single component that you don't have. There may be additional dialogue boxes, but this is when finally, all of the major dollar boxes have finished and he's just going to install. So if you see any more dialogue boxes, just click next, next, next. So I'm going to pause this video while it does this. See, you've never site well. It's taken a few minutes, but I didn't need any more dialogue box. Okay, so anything like that it's just succeeded, Succeeded, succeeded. So it's close. I understand. So in the next video, we'll have a look at how to open SQL Server. 5. Opening SQL Server: right. So now got SQL server installed. And if you got any off these dialog box is still open. Feel free to call him. So in today's lesson, we're going to open SQL Server and see what's there. So we've got to stop menu all programs on. We have Microsoft SQL Server 2012. Probably highlighted. Now, this is the important one S S. M s SQL Server management studio and in the acronym is the M. That's important because in SQL Server, you've got S S S S s eyes and ss ours. Your work will be for this in some in S S M s. You don't need any of the other things at the moment, so let's click to open. If you're using Windows eight, then you'll have to go through the start menu in the modern interface. If using 8.1, then you might be able to search for the modern excess M s Here It's easy, even just typing. Assess M s bring to the program. If using Windows 10 again, you can search for it if you can't immediately see it so lording user settings of these settings that were created by default on Dhere. We have the connect to server Dialog box. Now the servers actually installed on your computer. So you're connecting to your own computer. So we have here the name of my computer Onda. We have the instance of it, which we installed by default. We're using Windows authentication. That just means is very standard authentication. It's the user that you are in. So if you are different user trying to get into a database that you set up that might not work, let's click connect. Now we are in Microsoft's S S M s. So to close it, we just closed the puttin on the program and we can offer these hope again. SMS. It doesn't need to install user settings for the first time because that we've already done that. Just click, connect and join. It's that simple. The next video, we'll have a look around SMS and see some of the important things 6. Looking at SQL Server Management Studio: So in this video, we're gonna have a quick look around s SMS, and I do mean a quick look. We'll get into more details later. Andi, for much more details, you have to have a look at to course 70-462 full database administration For now, all we are interested in is T SQL Queer ing SQL Server. First of all, we have an object Explorer here. This contains everything about not just the database, but about the installation. And there are several databases already installed in this tree view. If you kick a plus sign, we get the next level down and who we have four standard system databases that you probably won't be using during this course master model mst be and attempt to be. But it's useful to explore what sort of things we've got so it can keep going down and down and down. So let's contract that for now. No, you can close object. Explore here. If you want it open again, go to the views Object Explorer. You can also press f eight. You could detach it by dragging it away and notice all of these icons you can put it on the left hand side top. Or have it filled the entire screen in a tapped in defense. So for now will keep it to the letter inside. There's also a refresh button here, which were going toe use later. The rest of this, you don't need for this cost. We've also got various menus up here on the bits and pieces that will be using later, including the Templars Explorer. This is a very important bottom. New query brings us a query window out here, and it also gives us the properties window. And again, if you can't see the properties window, then God, have you got two properties right at the bottom or press that four. Now notice. We've got a few more options here we can, but it next to this object explored for 12 false pass a screen full screen. But for this instance, we're going to keep it all the way to the right and keep the object exploring the left, and we'll be doing most of our work in here. There's something else I want to do. First is well before we continue on. Got it. Tombs options. We have a look at this text editor in general, so you can see all of these things. The text editor, old languages. We have display lying numbers. I'm going to take that and you're not. It's on the left hand side. Over here. We will have numbers appear. So when I say if you look at line one, you know instantly what that means. Now this quay window is open. We have additional two bars, including this important button execute. So we'll be using those in two lectures. Time for now. Let's close this query. I'll see you on the next video. We'll be creating a database. 7. Create a database: right. Let's create our first database now. Databases. Nothing more than a container off tables, queries, functions, everything that we're going to do in this calls. It's also ah, hold of security to ensure that people who don't have access to a database can't get him. So to create a new database, right and click on the word databases in Object Explorer and Click New Database. A database needs a name. I'm going to call this 70-461 Everything else here is fine. We could ignore all of these additional pages because you can't ignore all of these paths. Okay, okay. And it's done for. Expand databases. We now see 70 Dutch 461 if you can't the fresh and that will update the list. Sourcing this very little though no tables. There's hundreds of views, but very little else. There sufficient security that we need, but most of security and service broker and storage. They are part of Exam 70-462 So well done. You just created your first database 8. Creating our first queries: right. We're ready to correct our first query. Every Cleary is run in the context of a database. We've now got a database here. 70-461 So it's writing. Click on that in the Object Explorer and click on New Query. We could also click the new query off here as well. This shows the database that were 70 Dutch 461 And as you can see, we have a choice of five. So let's go to the quay window. Andi, retyped the word select Select is needed on absolutely every single query that we're going to write is the equivalent of print in over languages. It says. I'm going to put something after select. I wanted to be out, put it to the bottom. So we have a squiggly red line, which means that we're not complete yet. There's a syntax error, so it's completely sentence. Select space one plus one. The right squiggle disappears. We're ready to press execute, and here we have the answer. Now notice. It's on roll number one, and it's in common Name. No column name now because this is a database. Every single column needs a column name or you will pay like this. So let's have a column name. We'll go back up to here, press space again and put the word result. That's a nice thing for our column. Impressed. Execute. So we now have select one plus one result and show enough have the word result appear as the column header. Now this is sent tactically valid, but I don't like it. Select one plus one result. It just is a bit too unformed if you put the would, as in the middle, notice that select and as are in blue because they are official keywords on one plus one result or in black, they're not. So press execute, and we have the same result. If you're coming from a language such as C Sharp, you might be used to having semi colons at the end. And that's perfectly fine, too. Silicones are optional, though apart from this food few specific situations, I will get into much, much letter. No, if you press enter on type, select one times one as result and execute, we now have two outputs. We can make life even clearer and at the word go in between. And why is this important notice now have put a carriage return as a line to we have this minor sign joining lines one through three. That's saying this is all a batch will get into what a batch means later. But basically about is everything. Something guage is to be executed all at once. If I type the word go, this interrupts while the batches it says this is the end of the match. Do it. That's execute that we have exactly the same thing that's had another goal on select one. Divided by zero. I notice the's can be in lower case uppercase a combination. It doesn't matter. So let's see what happens. As you might have expected, we have a division by zero error encountered. Now notice. This comes in the second tub messages. If you go to the first time, it still has done the 1st 2 batches, and it's done the third as far as it can go. So does it actually stop things happening? Well, let's put in another command, Teoh. Select one divided by one as result. Press execute, Go back to results. It doesn't. What if I put this inside? This bunch will ask to get this one at the bottom. I still will. But you can see these are joined together in a batch. Batches aren't that important necessarily for select statements. They're important for over ones which will look at later. So just know at the moment Select is the equipment to print on go ends a batch you can use as to say the following is our header Our column header. So that's the end of this lesson, right? How much can you remember what we just talked about? Let's have a brief quiz. 9. Creating a table - first pass using GUI: right. So now we know how to create queries. Let's create a table. And to do that regard to the Object Explorer and we expand the database that we want to create a table in another two ways of creating a table 1st 1 is by using the graphical user interface or gui. We do this by writing, clicking on the word tables and clicking on new table. Now let's go to ask, is what column name do we need? On what data type? So let's just call this my column Cand use a type int, which means interview. And that's it. That's just close down this save. We need a Ted name for the table now, So this is a field name. So we have a database inside a database is a table inside a table is a field. Now I'm going to use something called Hungarian Naming System on the table here is going to start with T B l in lower case for Table Onda. What's the table name? First it's a table first and click OK on to expand the table view. Ondo. It is debatable. Don't table First Debbio is a sort of standard name it stands for database owner. If you don't see this, then click the refresh button. Now that's an easy way off creating a table. But it's not one of the objectives in the course. In the next lesson, we'll find out how to do it in the quo window, using T SQL. 10. Creating a table - first pass using T-SQL: right in this lesson. We're going to create a table without using the boat in tools, which is objective one. A. So to do this, we go to a queer window so you can write and click on the database and go nuclear if you wish. But I'll just delete everything that's in this quote. I will quote a table and it's done like this. Create notice crazy now in blue table space and then the table name. So previously, when we were using the gooey it has for the table named last. Now it's asking for us to provide it first, so I'm calling it the table Second. Next, we need to create parameters. The promises In this case, they're going to be the field name in the field. Type on. Do we include them? We've in soft brackets, so let's type herself bracket to start with an open bracket. There on, we create a field name. I'm going to call this my nobles space hint, and then we close the brackets once we've added all of our fields. So we're just going to create the one field in this case, close bracket red squiggle goes away. So we know that's fine. No sin tax hours. Press execute. Command completed successfully. We haven't used a select, so it's not. We're not asking you to output anything. So he's just are standard command completed successfully. And if you go over here, you can see table second and no, we count on. That's one of the things about using T SQL to correct tables. It doesn't refresh it, so let's refresh it manually by going to this refresh. And now we can see Table second. So if we expand this table, we can see that there are columns, keys, constraints, triggers indices and statistics. In reality, there's none of those apart from columns. So it's explained the column, and we can see we've got a mind numbers column, which is an integer Now. If you had wanted to, you could put the command go at the end of it. We could put semi Colon here is entirely down to you in the next video. We've got this blank table. We're going to be addicts, values into it and then getting them out 11. Entering data using the GUI: right. We now have two tables to empty tables, table first and table second. And this lesson. We're going to be adding values into table first using the gooey. What we do is writing, click on table first and select Edit Top 200 rolls. Now don't click on select Top 1000 Rolls is that will show them all down here, but we won't be able to edit anyway. I was edit. Top 200 rolls will be able to add new roles as well. So let's kick on it on and you can see we have zero off. Zero. We have absolutely no rules whatsoever. If you used to make soft access than this interface will be quite straightforward. So let's have some values. Because these are images. We're going to add whole numbers. So 567 you can see cell is being modified, but it won't be finished modifying until I go to a new rock. So I pressed the down key 678 a precedent icky free throw five. I could go back opened. It is a previous 123 all that say, I don't want to edit that I wanted to go back? Well, because it is still being written with that pencil mark, I can just press escape. I can also go through the rose by thes bottom bits. So I got to previous orca goal to the last one, go to the 1st 1 or can make a new record here. Now can put whatever introduce I like I can't even repeat a previous into job that I've put in. Oh, can I says that there is a problem here, So not sure what the problem is that's closed it. See if it really is a problem by writing, clicking and editing the top 200 runs again. No, no problem. I could also select the top 1000 rolls student problem right in the next lesson, we won't use the gooey. We will use T SQL to do roughly the same figures we've done here. 12. Entering data using T-SQL: So in this video we're going to add new records into Table Second. But instead of using the gooey, we're going to use T SQL. We do that with the insert command, so insert into table Second if I use open bracket 234 close bracket press execute one role affected. We don't know exactly how it is affected, but we come writing, click and select the top 1000 rolls and we see that it's been added. Now you may notice that table second is underlined in red, and when you hover over it, it says invalid object name. It's just like when no table second didn't come up over here in the Object Explorer, The Intel essence, which is what SQL server relies on for known got objects are there hasn't been updated. And to do that, we need the equivalent of refresh. So to update the intelligence we got to edit intel is sense. Refresh local cash, not This is a short cut their control shift. Our This is something I use quite often. So you might want to memorize control shift are are full of fresh. Once I press it, notice what happens. Toothy squiggle underneath table. Second. It goes away in a few seconds. Let's center that commander gate insert into Onda. Soon as I start typing now, I can select Table Second saves me some typing values open bracket because the values are parameters going to enter. 678 Close bracket. So inserting into table second the values 678 one roll effected to get. And as you can see, we now have two rolls in there. That's it into this commander. Gain in 32. Now. This time I'm going to drag table second from the Object Explorer to the quay window, so that might save you typing. If you like using the mouse personally, I like using the intelligence myself. Values Andi 456 close bracket. But this time I'm going to insert more than just one roll. I'm going to insert two roles. So a comma all the bracket 156 told Bracket. Let's insert a federal comma or bracket full 78 kills rockets. Now you might be wondering about carriage returns because previously we had the values down here in the second line on a you can do whatever you like. We've carriage returns it doesn't matter. As you can see, this is making it all one bunch. The only thing you must do is ported character turd in the middle of a word like that. Then it has got no idea what's going on. It's treats a coach turn like a space. That's let's execute three rules affected. So now we can see you got free roles plus the two that we ritually hod making five in total . So that's all we're going to do. For now, we've inserted values into tables, using both the gooey anti SQL. The next will be trying to get them out using the sled command. 13. Retrieving data: all right, so now we have two tables with some roles in them. How come you see what the rules are? Well, we've previously used the gooey to select the top 1000 rolls or edits top 200 rolls less used t SQL Instead, Now we're going to create a query. So what does every query have to have? The select command flak statement there are select now. Previously we've been doing select one plus one, but this time we want field from a table. So I want to select. I want to output told to select my numbers from the second table. So let's execute and see what we get. We get five rows, right? Let's do that again for the first table. I can't remember what the field is called. Is it my numbers? Is it my number? Let's get every single field, and we can do that by pressing the star. The story short on for everything. Every single field going across. In this case, we'll need the one from on going to drag this across. No, it puts hard brackets around them that's optional. You don't need them unless you had a table or a field or something with a space in or a nonstandard character, in which case you would need the heart rockets on this Debbio is optional as well. That's the default. So select star from table. First, I called it my column. I wouldn't to remember that now can put a semi colon after. What if I like on. I could put a never select statements from TVL. And now I'm going to use the arrow keys to select table second and press tab to selected. Here we go. We have to select statements. We now have two bits of output Now. Suppose I didn't want every single field now that have run it. I just want my numbers. Well, let's delete the star and start typing, and there you are. My numbers notices a small highlight around it. A compressed tab and it's also completes. On that to me, is one of the advantages off starting your queries select. Start from because when you put in your table, you can then go back and put in your field using intelligence. Whereas if you start off with select and try and do my column, it doesn't know what table you going from, So it can't hope you. So I love doing the table. First, make sure there's a style day of wise would be sent tactically inaccurate on then my numbers in this case so you can put as many as we like down there. Okay, Even put goal in between. But notice that the semi colon works just as well in this instance because here's one batch . Here's another all the way down here. If I go here, that's ends the batch ty for quick quiz. 14. Deleting the data, then the table: Now that we know how to add data in, we need to know how to deal eaters as well. On there are two ways of doing this in T SQL. The first is using the delete command delete from name of table and that's all we need. So, first of all, I'm going to select everything from this table. I'm going to delete everything in the stable and then I'm going to slight everything again . So we had six items. Six rolls. We've now got zero. No, since it doesn't actually delete table first. If I refresh, you can see it's do there. It just deletes the contents we're deleting from that table. Enough away is a troll bait truncate table. So again, this doesn't delete the table itself. It just delete the contents. So we had five rows of data now called zero to actually delete the table itself. We don't use delete from we don't use truncate table. We use drop table, we drop it out of the database. So let's see what happens when we use drop table. So I'm going to highlight this text and click execute, and that will only execute the bidders highlighted command completed successfully. If you go over here, we'll see that table first. Is Disa again? No, it hasn't. We have to refresh. He really has disappeared. We'll have to refresh to update the object explora so we can drop the second tape and to prove that it's gone. Even though we conceive Chanel dress explorer, let's select everything from it. Nothing that not it's still there in the intelligence. So to get rid of that from the attorney sense, edit entirely sense. Refresh local car. So now we're not trying nothing that. 15. Creating an Employee table: So now we know the rudiments about how to create our data. Drop a table. Let's start to create a more complex table. Now, the table I've got in mind is a list of employees. So festival, We're going to make sure that we're using the right database. To do that. We start with use 70-461 Is this the right database and then go, Which makes that happened? So if I'm in the master database and execute, you can see that the context has now changed to the 70 Dutch 461 Because if I just create a table and I happened to be in the wrong database, then the table will be corrected in the wrong database. But with this context, with the use command, we've go. I'm confident now that he will be created in the right context. So that's quite a table with employees number on an employee's name. So, first of all, how do you create a table? What do we start with? Great Not is when I get it right, it goes into blue. If I do a title, it's in black. So let's get the keyboards right. Great table on a table. Employees. Then what do I need? Unopened rockets, Right? So we have employees, you know, but that a comma separate this from the next field play name, and then we have a close bracket. So we're in the master context. If I execute, has that table being corrected? We'll only know if you press the refresh button there. We are terrible employees. You can see it's got two columns. Employee number. Employing name. So now let's go on and on our first employee. So employing about one John Smith. Now. Hey, we come up with the first problem. There's a loss of words in here, but the key thing is this one and play input String was not in a correct format. The reason for that Is that what this is expecting? He's and Inter gia I a whole number. What we've given it is the name strength. The reason that it's coming up of this is that when I declared the table, I said Employee number int, which is fine employee numbers, can be an interview, employees name that can't be it into job because an integer is a whole number we're trying to put in a text a word so you can see that before we go much further down the creating tables route. We need to learn something about this bit. The data type. We need to know more about data types. What over? Data types. Are there data types for whole numbers? Data types for floating point numbers strings. Andi dates more quickly. Nona's date times. So we have to put this to one side for a moment. I'll see you in the next video. 16. Creating temporary variables: No, I think one of the easiest ways of looking at data types is to take it out of the context. Off tables and databases. Let's just get rid of them. So just like this simple, select statement when we were looking at Mathematical Properties Onda, we got a table. As a result, we did. We didn't have to have from table do anything like that. We just concentrated just a select statement. So let's get rid of this object browser now and concentrated just on the quay window on the results. It's at this stage that I should introduce you to one of the best help systems that there is for SQL Server. It's called Books Online. We can get to it quite easily by going to Google or your favorite search engine and just typing in its girl server books. Online notice. I'm talking 2016 and it's given 2012 and it's give me the 2016 version on. To be honest, not too much has changed in the T SQL World. Between 2012 and 2016 there's a lot that changed between 2000 and eight on 2000 and eight are two compared to 2000 and 12. But if you want the books online strictly for 2012 but you kick on the other versions on, go to your Version. Now there is a lot off confusing stuff that's also available on books online. So if I have a look at something that are going to be using, you could see that there is a huge amount of Gulbuddin Geico here, which is probably unintelligible. If you studied to see Sharp. So Books online starts off with an introduction for what it's going to be doing, then the very complicated syntax explanations about everything about this Intacs on, then maybe the best bit so examples. You might find it easier working from the examples, finding out what some things about, then putting it into the context off the syntax. No, what it also say's for each command applies to SQL Server, and it gives the number when it started with 2000 and 18 this case fruit to the current version. Some things. If you're using 2005 you wouldn't be able to use this, at least not in this phone, right, So I want to talk about for instance, interested in numbers. Andi, it's obvious to you and to me that one on one are interred in numbers. But is it obviously t SQL? And the answer is, I don't know. Not from the not just looking at this. So what's want to do first is I want to declare a variable Now. A variable is something that contains a value. If you've used vb dot net O V B A. Indeed, any version of basic you will be quite comfortable. We've dim terrible as into job. If you have used see shop, you've been quite comfortable. Wave dim my var equals note. Andi. Indeed, in the Phoebe dot net world, you might also have equals not to the end as feel Server takes a spin on the VB version, but it's not so far from the C sharp version. This is the SQL Server version. Instead of dim, we are declaring, Do you declare a variable? All variables must start with the at sign, so declare space at my vote as the word as is optional here, just like it was optional in there. That will work on that will work, but it's a lot easier for me say with the Momesso word as included 12 then removed. So declare my bar with the at sound at sign, making it available as on Inter job, and we can give it a value at this stage. So I'm going to give it the value, too, so you can see it's fairly close to the visual basic version. Andi. If you just move the after that, another will declare it's fairly close to see sharpish. It's much closer to basic now. We've defined what my various. It's an integer, and we've given it an initial value and that, by the way, is optional. You could just give it that I didn't say my fault genitals to set my via calls to much easier to do it all at once. We can now use a select command notice right here that we've got my bar as it dropped down in the Telus sense. So it's intelligent enough to know that my var is something that you're going to be using in this query. That's all we need, though I would like to actually have a heading as well, so my variable it's called that as the heading of my available So let's execute. So we have declared available cold My vote we have, sir, given it a foul type, the data type the integer and we've given it an initial value off to If you want to give it another value, then this is how you would do it in visual Basic birdie SQL You can see that doesn't work. What we need is the set command. So we're declaring a variable as an integer. We're making it too. Were then saying that the variable is now equal to three. So this select command will give the number three We can also manipulate it. So who were saying that that we're now setting my var to be whatever it was previously in this case too. And we're adding one, so it will interpret that as being cassette. My VAY equals to two parts water. So we have three games multiply by four and one do be cool tonight. But the important thing about all of this is this bit we have declared available and given it a data type. So now we know that whatever is in this valuable is going to be an internship. What happens if we give it value. That's not an interview. So set my far as 2.5. No, since it doesn't give it an hour. If you look in the messages tab, it just gives the standard one rolls affected it instead. True, Cates. It gets rid off anything that that is after the decimal point. Does it Gopal down? It always goes down 2.99 gets rounded down to two. It was about negative numbers who that become minus two o minus free. What? I've just said it rounds down, but it rounds down towards zero. So really, it trend toward zero trunk eights rather than round it just gets rid off anything that's after the decimal point. So now we know what these A declaration of a variable. Now we know that we can use this to this. Let command. What I'm now going to do is I'm going to just add some comments like this. Now you can see that comments in SQL Server, huh? Have two lines of the front two hyphens and you can also use thes two buttons to comment on toe uncommon. So if I click on this, it suddenly becomes not a comment I was. If I click on it again, he becomes a comment again. And I never comment, and they never come into. So you can comment your t SQL as much as you want to make sure that you understand it later . What I'm going to do now is put this in a word document. You could see that the coloring remains. Andi, I'm going to say this word document so that you could retrieve it later so that if you want to just look at what I've done rather than having to listen to the entire lecture, you can literally look at what we've done. So now we've got a way to assign. Available as a interject or a data, Any data type on. Retrieve it. We can now talk about the various different types off data types of the road and start doing that in the next video 17. Integer numbers: Now, in case you're wondering where we are with regard to the objects for exam objectives for Exam 70-461 We're country. Here we are implementing data types were using appropriate data, more understanding the use and limitations of each data type on the data types want to talk about today in this Elektra are the interview ones. Andi, I will often start these type of lectures. We've looking at books online simply to show you that it's a really good results. As you will see, there are four different types off images we've got tiny hint small int int and begin. Now this storage, the number of bytes it takes two store A particular integer is important. He may well come off in your exam. You'll need to know that a tiny int which stores from note to to to the power of eight minus one, which is 255 just takes one bite to store in the SQL Server database. A small and int and data and our whole what's called signed, which means that they couldn't hold negative numbers as well as positive numbers this tiny and it's unsigned. Pickaninny stole positive numbers. So don't try storing minus one and detainee. And even though it's a small value, it was, won't hold it so small and consoled to around 30,000 plus or minus 30,000. Int can go all the way up to about two billion and begin What small? I can't see reason why you'd want to exceed begins. But every time we go up a step, we double the number of bytes. We start with one bite, two bites for bites. Eight. So let's go to SS service. Now, if you want to say that what we're going to be storing here is going to be no more than 255 then int is far too big. I mean, you'll work as you can see. It's what, but we can get away with a tiny and and use only one bite instead of fall, you might be asking me. Okay, White Sai Baba Four Bites isn't going to be the end of the world. Well, maybe not immediately, but when you're talking about, for instance, one billion rolls of a database, an additional free bites is an additional three billion bytes. That's free gigabytes that we didn't have to waste If we're just going to store not to 255 So will this welcome tiny int cause he will. What happens if we then subtract two from this initial value of two doesn't go all the way down to zero. It does. What happens if we go down and never won? Making it minus one? Why do you think will happen? Would it give us an era? Or will it wraparound All whipped? 255. If you're worried about this wraparound, Got no idea what it is. Don't why it happens sometimes in some programming languages, it doesn't happen in SQL Server. It will given era arithmetic over floor. Never. Once it's gone out, it's flowing. How flawed. Out off the holding that it's got a tiny int nor to 255 and it's gone. Outside of that, you can see the results all of the old results. Even though it says one Roy factored, he's still giving us the old result. Interesting. Now what happens if I subtract half? So we start with two. We're in a tiny and Andi west, attracting 1/2 what you think will happen while it calculates to minus no 0.5 is 1.5. We've got to still that back again and Italian. So we truncate as we saw in the last lecture, we truncate the 0.5 anything after the decimal point on we may cute therefore just one. Now how can you make this work? We have starting off too and we are subtracting free. How can we make that work? We change this tiny and can't remember the next one to a small. And now the small int allow for negative numbers. Yes, it's only tiny into which is unsigned. All the others are signed, as you can see. Two minus free. Minus one. Now what if we were to give us a value off 2000 on we multiply that by 10. Is that too big? First Mollet as Moreland goes up to 30,000 Technically 32,767 68. So my round That's fine. Now how can you remember all of these different interview verbals? Well, let's just write them down. We've got Tunney int which is know to 255. We've got small and which is about 32,000 level. We've got int, which is up to about two billion, and then we got big end, which is even bigger. So if you take the first letter of each of these, then we have to zip. Okay, That public isn't as easy to members. And I think Okay, what's part if we do it the over way, So we have big into the top. Um, there best. Okay, that could be a way to remember it best. But if we swap But the last two around, we have beats. So can you remember one lot of bits about ins? We have bits begin, and Italian and small int. So if you're coming from a language like C sharp, you know, you've got signed and unsigned byte type information and signed an insight into into just we've only got fort away about. And this is how you can remember it if you just remember the acronym bits. And just remember, it's in descending gold over the last two the wrong way around 18. Non-integer numbers: right. How did you get over the quiz? Did you get that last four, right? The number of Bynes they might be saying Flip, or why do I need to know the number of bytes? Yes, I understand that if there's a 1,000,000,000 rules, it could make a massive difference. But the biggest one of these only goes up to eight bites. Exactly Know why can I just get a simple variable that will just start a number on bond? It will start at number precisely, and I don't care about how big it's going to get. Well, as you'll see, it's going to take a awful lot of bikes to do what that simple question would allow. Now you can see that there are four different types, according to books online off numeric types. We've gone through the int begin small in detainee and not sure why that's not in the right order because enters the basic unit. But why not have it in the correct old have no idea. But looks like this. Four different types decibel new brick float and riel money and small money off those three . In reality, designing to we'll have look a decimal knew Rick first decibel a new Rick are exactly the same thing. Numeric is functionally equivalent to decimal. It's just that one is used in off operating systems over the next actual servant, so they decided to bring both in. When you still a decimal or numeric, you've got to give it a bit more information because we're talking about floating point numbers. We're talking about numbers that potentially have something after they fall. Stop after the period 4.5 three and 1/4. We need to sell it. How precise you want it to be. We need to tell it how many numbers you are going to. Still, how many digits on do you have to tell it? How many of those digits are after the decimal point? So let's say we're going to stall seven digits, of which two are going to be after the decimal point. So 1234567 is valid for the book, but 123456.7 that has seven digits. But we've said that Tooth Amar after the decimal point, which means that only five are allowed before the decimal points. That's not it right, So we're going to sect. Um, my fall as wanted people. $5.67 So let's see what we get Exactly what you expect. 12345.67 is fine, but if you were to change that 2123456.7 arithmetic overflow converting a numeric because city still numeric got it's a lot like number two numeric, which doesn't make what sense until you remember that there are different salts of new brick or decimal. And, um, just not to belabor the point too much. But if I change the word decimal two numeric, it will look now. How many bytes is this store? Only bites are needed to store 12345.67 Go back to books online and you can see the answer is five. Is this first figure. That's important. The second figure is unimportant as to how many bites it needs, so the minimum decimal old numeric needs its fights. That's greater than it already. If you need a 10 to 19 you need nine notice. We're going up by four this time. If we want 20 to 28 weeds, and never for about a 29 to 30 we want another fall. 17. Bynes is a lot of bite, but how often will you actually be storing with exact precision? 29 digits? I don't even know how big that is. A quadrillion is only 15 now. Technically, this comma to the second argument is not actually mandatory. If I omit it, all this work just got this. No, it will work, but this time he's has not truncated it. You notice it's not going down toe. 12345 If this was an end, if he had said in here, and that would have gone down toe wanted free for folly. Instead, it's rounded it hope all down to the neighbor's number. You can see that the scale default to zero. Now. I don't need the precision, either. The precision defaults toe 18 for historic reasons. 18 is a ridiculously big number. You'd be going up to a 1,000,000,000. Let's just try with this out. So one billion that's 10 digits and then I never ate. That gives us the precision off 18. So generally don't use the default off just new brick. It's just not going. Teoh. Give you what you need in terms off a requirement for your data. It's going to be far too much, all farty little and we have 18 precision, but we have zero decimal places in the default. That's what we've just done is that if you wanted face, then we still have a decision of 18 because that's a number off zeros and the wallet beginning that we have the number of digits, but we need to define the scale. 12345678 would need to define that. That will give us that. Allow us to store. That is the figure. So let's just put that in here one of no no no. 12345678 And there you go. So if he needs still them was like that, then you can just work out your requirements first, because this takes harmony bites. It's between 10 and 19. It takes five plus four bites nine by so it's a minimum of five bites just to get out of the door. We decimal and numeric on their Wii golf and falls. Now that is precise. I can put in that figure on being sure that when I put this let command we get that figure precisely. The second data type I would like to talk about is money and small money. Now, you remember that decimal and numeric starts off with five bites needed or small money when he starts off with four. Now, small money and money are accurate to four decimal places that's fixed. So you don't need to worry about declaring harmony decimals. You need the only thing you need to worry about his. Are you going to go up to around 210 1000 while you're going to go beyond that? So if you are going up to 210,000 then you're fine with small money. I suspect for most transactions where he was actually talking with money itself, you'd want to go into the millions. At least have the possibility of doing so, so you would need the money. Now, if you do go into the money data type than you then have to think while that's eight bites , would I be better off using a decimal that only requires five bites? But then I can only got nine precision places. Can I do that while if I have to after the full stop, that means I can go to 9,000,900 national files of 999. Don't 99 If that's efficient, then maybe decimal is better. But if not, then maybe money is better. So let's just see it in action. So declared this terrible for use more money. Get us out. You can see that it works. If I try and add any more decimal places, it will round it to the nearest fourth decimal place. Now the last one I have to talk about because you have to know about it is flossing. Riel. Andi. I would advise you not to use it. Here's the problem. They are approximate number data types. Not all values can be represented exactly. It's 1000 poles and caveat. If you don't mind that it maybe a fraction out then okay, but helped into this happen in real life. Now they're all really to different floor types. Float 24 and float 53. The number represents the number of bytes that aside Bates that it's going to store Andi given that float one requires four bites and floor 24 requires four bites. There's no advantage. You just using floor. One such issues. Floor 24 or floor 53 if you need to and look down. Here s girl server treats. It is one of two possible values. Even if he used flawed one, it will still treat. It has flown 24 so don't bother about anything else Now there's a number of term had cold riel. Real is flow 24. So let's see it in action. So if I'm using a float, I can just call it is floats on floor 24. Uh, so why has it curtailed it? There is Girl told it there because float 24 is person is to seven digits. I'm sure there's a reasonable explanation for it being seven digits. It's because off its two to the power of 24 you just don't need to go that. Just remember Floor 24 which is the same. Israel is precise to seven digits, so we don't need to say how many digits were going toe after the full stop. We could go like this, and it's still going to be precise to seven digits. 123456 And then something approximate and we could have said to declare as really, it's exactly the same thing. But as I say I would avoid in real life, unless you've got a very specific used for it. Perhaps you're talking about trillions quadrillions, and it is a matter if you're wrong to the neighbors to one. So we've had to look at two decimal, which requires, and so which is the same as numeric, which requires five bides adding Opened falls 59 Once you get past 10 13 what you get past 19 and 17 we've had to look at money and small money, which Christ four white bites. We've had a look at floss, a real which cries for or eight bites. So they are. This is decimal numeric Christ. 59 13 17 The go pinfall. Basically every time on The key thing to have about decimal is the first thing that that you declare is the total number of bytes, not the number of bytes. To the left of the decimal point is a total number bites, and the 2nd 1 is a Tottenham of by off death digits after the decimal point. So I'll just say that again. The important finger decimal and numeric is that the first thing you declares the number off digits total. And the second thing you then declared a number of digits that right of the decimal point. Okay, it's a quiz. 19. Mathematical functions: my congratulations. You basically know Know everything there is about data types. So how come you use him? Well, we have a numeric or decimal initialized on You can do basic things such as addition, subtraction, multiplication on division. We can make it to the power off to. For example, here is the carry, a sign which is shift six on my keyboard. So what this is saying is free to the power to three squared and this is when we come to our first problem. You can't use this in t SQL for what I was trying to do. Instead, we have to use a power command so free to the power off to would be expressed like that. So first of all we do. The commander called it a command. It's a function. We do the power function. Then we put in what we are going to use. So in this case, the three and then what? We're going to return it to the power off on The great thing about these functions is that they will try to be helpful. You don't have to go on to books online to work out what they are just typing the word Andi . If it's a pink denoting in this case of function, you'll get an idea of this. Intacs. Let's execute that there will. Nine. There is another west of doing this and that would be selected square on here. We don't need to say to the power of to, because squared raises it out to. In any case, they are free to power Tuesday, nine with power power is more flexible because we could say three to the power off free. Once we can't do that with square as one of squares, we can also do square roots. No, If you know you're after, you know that you could do a square root like this free to the power off half 1.7 free. But you can also do it using sq are tee square root and this time you don't have to say to power half just like the word square square raises it to the power of to square root raises it to the power of 1/2. Right. The next free commands functions. I'd like to talk to you about oh, floor ceiling around. You can tell the day are functions because they think Now these are all to do with rounding , rounding to the nearest. So what do you think? Flawed ceiling round to the nearest floor rounds down to the nearest whole number. Ceiling rounds up to the nearest whole number. So let's just execute those. So the number 3.7 goes down to the floor to number three and up to the ceiling to number four. So here's the question. What if it was minus 3.7? What does floor go down to what the ceiling go up to? Floor always goes down from minus free 0.7 girls down to minus four minus Freeport seven. In sealing goes pop tu minus free round. What is around you rounders? A lot of things round goes up all down to the nearest and you tell it what the nearest thing you wanted. So here I'm texts telling it that I wanted to go to zero decimal places. So let's see what happens. It goes down from minus 3.7 tu minus four. The nearest whole number has changed this 212345 This is a positive number. What is the floor off? 12.345 What's the ceiling walked round? Do have a think about that floor always goes down. Seeding always goes up. Round goes to the nearest. So if you said 12 13 and 12 your right. But round doesn't need to go to the nearest hole number. It could go to one. That's more place. So what's one decimal place rounded off? 12.345? Well, one day small place we get it to talk about Frito Alive would be talked about free or top. Part four is going to round to nearest, so it's going to round down in this case. As you can see, it doesn't on. If you have looking books online, you will see a really annoying thing. It demonstrates that round by the last digit is always an estimate. So what I think it does is it has a look. A 12.345 rounds that to tour 0.35 then grounds that up to 2.4. So don't I think this will always work exactly as you would expect it if I put it down to 12.344 Then it goes to open free for four rounds to 12.34 round to 12.3 that he, uh you could see you've got a problem. It should round down to 2.3, but it doesn't. So just my mind round is not always going to do exactly what you expect. What about if we go to two decimal places? Well, this time it could go either way. 12.34 down or 2.35 up. And it chooses to gab. Here's a question for you. What if a rounding to minus one decimal places? Well, if one does more places 1/10 and zero decimal places are whole numbers, then minus one decimal places is tens. So you around 12.345 to the nearest 10. So we get 10. So floor ceiling around. Very important. Just bear in mind around my do slightly hot the next ones I'd like to introduce you rpai. Now, you think you could just go like that and see if you can? No, I do. We get a little squiggle on. We have to have often bracket close bracket. Now you know that. PiS Freeport 14159 etcetera. Never related thing is E. On. This is e ease, a figure which is about 2.7 or so. You got to put one in brackets after it to get e itself. If you don't know what years, don't why you won't need to. Next. Two things I'll tell to you are the absolute value. Andi, The sign by you. What absolute does is take away any minus sign on. We turn you the positive figure. So what's the absolute off? 456 It's 456 authorities. A positive figure wasa sign of 456 While if it's a positive figure, the sign of 456 is one. It shows her one. If you've got a positive figure, it was about minus 456 Well, absolute will return the positive form of minus 456 Making it plus 4 +56 sign will return minus one so it returns, one multiplied by whatever the sign is, and this one's full. Any figure absolute gives you the positive version. If it's already positive, it doesn't make a change. Sign gives you I have a minus one or plus one. What happens if it zero while absolute gives you the positive version. Zero, which is zero and signed, gives you a zero. So sign will only give you minus one plus one. Well, zero absolute will give you the positive version off any particular function. This next one's a bit strange. Round Rand gives you a random value. Now it's based on the value that you give it. So if you give it never value gives you a different random by you. There is no major connection between 5678 and that free for five This. So generally what you need to use in the brackets around is a random number, so you need a random number to get another random number. As you can see, this runner number is between zero and one. We look at how you can get a random number to start your randomness a bit later. The final set of functions. I don't think you need to be honest right at the moment, but let's just have a quick look at him. They are whole friggin knew. Metric economic trickle metric We have sign we have in verse sign arc sine we have tangent , Arc Tangent. We have radiance we have caught calls. If you don't know what any of these are, don't why you won't need to. Just remember that, just like in other languages. You need to have this in radiance. So I'm not going to go through them just like you know that they exist. OK, that's if all these mathematical functions off the you remember power square and square root floor ceiling around Andi, I know that you have absolute and sign. Okay, Time for Chris. 20. Converting between number types: Now you know that there are introduced types and non Internet data types. The question is, how do you convert from want whenever and why would you want to? Well, let's take an example. Select, really divided by two. The others that comes up is one. The reason why it's not 1.5. It's because three is an integer two is an integer and what it gets to introduce together on divides one by the ever he's going to produce an integer. How do you make this not to be an interview? You could put 0.0 after one of the numbers. Now 3.0 is a floating point number, probably decimal. Is that sufficient? What about a decimal divided by nature? Well, you can see that with turns a decimal. What about if you do the other way around an inter Joe divided by decimal that also returned two decimal. Now, you think if you didn't know about this integers and floating point numbers that these two figures would be exactly that gives exactly the same result and they do. They just do so indifferent data types. So how can convert from one to another? Are there two different ways. The first way is implicit. The second way is explicit, so let's look at implicit first, going to declare a variable as a decimal. I'm going to give it a starting value off three. Now, as we've just seen down here, three is an integer, not a decimal. So will it work? Or will it give us an arrow and saying You're trying to get a decimal and interviewing for a decimal field that's not allowed? That's a look. You run this and it does work. What happens is that it knows SQL Servant knows that free is an integer. You're trying to get into the decimal, and so it tries to convert it, and in this case it succeeds. That is called implicit. It is doing it without you actually doing anything. It's ask your servitude. Now let's look at explicit. We're going to have a look at this free and see if we could convert that into and in a decimal. Free is currently an interview under two main ways of doing it. Festival have already said, Do you want to convert it? And convert is a function. What we do is we give it the target type. We want to convert it in for decibel Onda. We want the initial value. This case, the integer three. Let's see what happens now. So that is converted three an integer into a decimal. Now we can divide that by two Onda We are converting decimal binding to Joe which gives us a decibel Now technically this form of conversion is called casting and cast is another way of writing this. But this time cast goals the over way around be cast free I was a decimal So the first time we are converting into a decimal the number free Now we're casting free as a decimal. Both of these works fine What happens if you try Andi convert or cast something that can't be cast Since what is the biggest value that decimal five comma two can take Have a think while the five means that there are five digits in total that two means that there were two after decile place which makes free before the decimal place So the highest is 999.99 So what happens if you try and convert 10,000 into decimal? It won't allow it No, not is that this is over. Over Kitale Dough message is an arithmetic overflow era converting an integer the 1000 into numeric. It doesn't say that it would work if you changed the numeric fractionally is not saying it wouldn't work in all new bricks. It's just saying right at that time, converting 1000 into something that's too big just won't work, and we get an error message now. No messages, Not necessarily the best solution. The best thing we want to get back on. We'll have a look in a very short space of time at another way off trying to convert it into a field type on giving is a value back, as opposed to an error message. If it doesn't work. No, you don't just necessarily have to convert from interviews into floating point numbers. You can do the way around. So if we were to convert into an integer, the number 12.345 got the number 12 which is an integer. So if we're to at that to the conversion off full point six, what did you make it? 12.7 now 12.345 12.7 together add up to 25 but when you convert 12.345 into an interject over 12.7 into an interview, you get 12 plus 12 making 24. So even though you could then comfort 25 into it into a job to get 25 it will not give you the same thing as converting 12 and a bit into an interview. And I didn't get into 12 and a bit. So the major thing is to remember which way around it. Girls, you convert a file type and then give the number, or you cast the number as a data type, so you have to remember which way around it girls, because excuse me, because while you're in there ASUs mess. As soon as you type convert off a bracket, it tells you you do the target tight first and then the expression. But if you're doing the exam, you don't get to use SMS. You have to know these things. You don't get the tool tip. Unfortunately, in the example, right, let's see if you remember which way randy girls 21. Strings: right. We've now gone through images and floating numbers, but that doesn't actually answer our wish. Nor query. If you remember, we were trying to create a table. Onda. We had you play number using an end which might be okay. You could argue. Perhaps we could use a small into perhaps and then employ name on reusing in for us as well . So the problem is how come you reference employee name to not be a number. So in this lesson will be looking at all of the string times that there are Andi. Frankly, they used anymore, but they're now only ball. They are char vulture on the two above, ones with an en it beginning in charge in a vault chop. So what are each of these ones? And why should you be interested in the difference if you go back 2030 years? He used to be that computers were generally credited for the English alphabet. So let's is from a tea set. Andi, Law case eight. Is it Andi numbers from not nine and the creators off the computers decided to get together and make specific numbers for each of them. Each letter. Each number was going to be represented by a number between North and 255. So the capital A was going to be represented by the number 65. Lo que se by the number 97 on the number zero by 48 in the space was 32. So every single character had to have a number of these numbers were from no to 255. Now the really early days didn't actually agree on what the numbers should be. You do find some computers in the 19 eighties which use different numbers for these letters . But making them numbers is great because computers working numbers andan those days we had four kilobytes, eight kilobytes of memory. It was very little you had to compress as much as you could. So this unified numbering system became nuns. Asking and asking is great for English language speakers. But hang on. What about French language speakers? What about German? What about Spanish? So they started to expound on this on dso you had accented letters appearing in the space above 128. So he had a foreign accent A with a different accent you have a certain flex accent and that's finest far zip gun went. But then what about the Greek letters? How for beat a gamma delta? What about various Indian language? What about Chinese language? What about Japanese language? You can't put all of those into 256 characters from not to 255. And so these boffins then came together and decided to make a never unified numbering system called unique old. Now, if you look at a program that week, you may not know, but it's installed on your computer told character map. This is the US ski alphabet, starting off with an exclamation which is number 33 a, which is number 97. No. Okay, so op guests a 65 lo que se is 97. The numbers that you're seeing here are Hexi Decimal, which means that you have f has being a number A, B, C, D e and F is number still. Why about that? What you should worry about is as soon as this digit becomes is not zero. You got further forever down like here we are out off the ask e range under Now in the U Nicole Rain Unicord range. So this why with the two dots above for normal out that is part of the s key range. Everything goes that you see further down. It is part of the U Nicole unique old range. So why should you care about this? You should care about this because these two characters, these two data times char and Varsha work on the asking range on these two with an end at the beginning. Welcome the new record range. So what does that mean? It means that the amount of bites need to store each character for the asking range. It's just one bite bite is a number between north and 235. One bite. Why was for the U tickled you nickeled you need two bites her character. So if you are able to stick with string expressions which are predominantly based on English or western European type alphabets, then you're fine to stick with asking if you've got even just one letter that goes outside of this range, then you need to go to uni called. So how do we define one of thes we could declare available and we need to tell the computer how many characters are going to be in this char in this character's drink. So let's say 10. Now we define this. Set this as equal to now. If you're used to using C sharp vb dot net, you would then say, or from quotation marks Hello, close quotation marks. This is not the case in SQL Server, just like the c sharp char use single quotation marks on. Now you see anything that I put into single quotation marks, including the quotation marks themselves. It is shown in right. So now I can select what I've got and there we see. Hello. I can also see the length off my expression. So hello is five characters. So how long do you think the length off this favorable is the answers? Five. But the variable is stalled, has 10 characters because that's what we said is going to be the maximum. We could find that out by not asking for the lead for the string, but for the data length notice this time length spoked out long way. Why was Len is just about short off the terrible. So now if you run this, we'll see that the string is five letters long but is taking 10 bites to still. So if I now put hello there without a space, so yes, without space, we see that the string is now 10 characters long and is taking 10 bites a store. If I add an extra character, guess what happens. Absolutely nothing. It details the length off the available to 10 characters, so hello there stops now. If I wanted zero characters in now, I just have to separate quotation marks That's different from a double quotation marks that's different from this. That's two separate quotation marks, and so we have an empty string, which still takes 10 bytes to store. That's a bit inefficient because you might have a 1,000,000 world, you matter of 100 million roles and a lot of empty strings. So what happens is that you can then use a variable character, available character string, far char. So now an empty string has zero characters and zero back to store. It says it's lying to you. The disadvantage with available characters is that it takes on additional two bites, so an empty string would take two bites to stop. If you go to books online, you can see here vodka. The story size is the actual left, the data plus two bites. So it's a bit lying to you, but two bites Sisto it much better than 10 bytes of star it if you were just using a char, so use Charl as opposed to watch our. If you know roughly how long your string is going to be, it could be that it's a national insurance number, car registration, something which is going to be off roughly a fixed length. If it's not, definitely consider using vodka now n char and in voucher work exactly the same way. So this is my blank string. Get 10 Sha the Dental and 20 because every single Unicord character takes two bites. Sisto. So if I say hello, that's five in length, but it's going to take 20 to store. Likewise, if a putting far, it's only taking it say's tender store because it's five times too. But it's actually five times two plus two, so it's taking 12 bytes to store this five character phrase. So the major advantage of using the end is because you can talk symbols that are Unicord symbols like this particular one, which comes from Arabic into an env archa on N char. But what happens when you try and put it into a voucher without the end? Well, you can see that we have a question mark. The unidentified character has been transformed into a question mark in the vulture, but it's also been transformed to the question mark in the enV archon that unique old. Now, why would that be? It's because these quotation marks did not a string, specifically a char string. It's not denoting an n char string, and to do that, you need to prefix it with the capital. That's the end. You can see here that the capital letter end is in red. If I put a small case in, it isn't it needs to be a capital. This is one of the few times where que sensitive data is important. So it's execute that again. On there, we can see the Arabic symbol. So what he was doing previously here it was doing an implicit conversion from a char to an n child that's is a child without the end. This revolted, defined as an inch are on N Varsha. And so he was doing Getty Implicit Conversion. Two reasons why you don't want that to happen. Firstly, it does take the computer a bit of time. You could be fractions of a second. Could be milliseconds could microseconds into the matter. It takes time, and so your cold will be slower if you have to do an implicit conversion. Secondly, it's an implicit conversion off at Bacha or Charles, and that doesn't include these symbols. So this symbol will already be lost as a question mark because it is already Bean shown as a asking that supposed to uni card before it could be transformed into a unique card. So the moral of the story. If you're walking in n vulture, make sure you have this letter capital letter n right to the beginning. It would just make your life a bit easier if you think this is going to be a nuisance. Sorry, it's a nuisance. You have to get used to 22. String Functions - extraction: now something. Get that you might be curious. Felt what? How big? All smoking this number get. We can go all the way down toe one away up to 8000. After that you have something called Varta Max and in Max on that can go to two gigabytes. So that's what two billion? So it's a very might seem of Oh, weird thing. One 2 8000 on, then almost infinity. However, it's the way that it stars it. Its SQL stools. Ross, basically in Bunches off 8000 and 96 bites so saying that it could go to 8000 is for SQL Server. Reasonable if you need to go beyond that voucher and n varta Now, if you ever have ever heard off something else. Cold and text, text and image in text and text are the old way off saying Virtual Max. Andi Texmaco looks but not is what you say. They will be removed in a future version of SQL Server. No idea when that's going to be so. If you see in techs, just sink in virtual max. If you see text to see Varta Max basically do not use right now we've got these phrases here. We can extract them now. Previously, we've seen that we could just go like this. I get the results out in a query in the corporation. Your table output it from a slack statement. If you've used over languages, you will know that you are able to get smaller baits from the strings. You just get the 1st 2 characters, the last two characters the middle for, for instance. That's do the same here, and we use commands that visual basic people will be quite familiar with were used, left and right. And it doesn't matter whether they are asking characters for n virtual characters. We'll get the 1st 2 of the last two regardless. What if you want to the middle? Well, if you are visual basic, you want to use that, but it doesn't work. This is when C sharp people can go. Yes, all of our commands is in sub string. Two things to remember about this. The 1st 1 is that use soft brackets, not hard brackets. So it's not a substrate is if you're using C Sharp, you'd be doing so like that. So it's soft brackets, and the first thing you talk about is what you are extracting from. The second thing to remember is that C Sharp is a zero based language, so the first letter is a letter number zero. That's not the case in Phoebe dot Net or Phoebe A. That's not the case in SQL Server. The first letter is letter number one. So have a look at this. What letters with this extract from this string. It doesn't extract the apostrophes. The apostrophes aren't actually part of the string is just there to say this is a string, so it's going to call to the third letter and it's gonna get to letters, So it's going to get double L. Now what if you got spaces in the beginning and the end you can get rid of death is, well, Onda again. You might be thinking you can use trip. You can't. It doesn't work. Instead, you got l train to trim from the left and are trying to trip for the right, and now you know that it does exactly what you might expect. Don't gots them from the left if you can't see the ones on the right because they just spaces. But for doing our trim. You can see that there's a couple of spaces there. So to get both the left on the right, you have to do in l trim Andan Ultra the final command. I want to show you his replace and replace actual variable. Andi, you can replace any letter or combination of letters, wave another one. So I'm replacing the loco cell with another case. So who has gone through the entirety off the hello string? Not just taken the first l and replace that with a capital l but taken all the owls replaced him with a capital L not that. Of course you have to do that to get it to capitals or lower case. You used the upper Onda Law Command some languages that might be UK. So in l case, we use open lower here so that you can see here we have the allow in upper hereafter. Hello. In law, there are over string functions very quickly reversed. Texas backwards space gives you lots of spaces asking you, Nicole to give you the number here in off the character. We've already taken life, Len. Former will look at later and contact will look at later as well. But the ones that I've shown you are the important ones. I think so. Let's see how much remember. 23. NULL - an introduction: I welcome back. Are you train these calls? Well, you might have said yes. You might said no. You might have said Maybe. And maybe I want to talk to you about Not about what? You enjoying this course, but about the answer to my question? My question could have bean a yes or no question. But you could give me 1/3 option, Maybe. I don't know. It depends in Bates. You never something that isn't? Yes. Oh, no. Why is this important? We have a simple declaration here declaring. Got my far as a teacher and we're equaling it to four. We're setting it to four. At the beginning, we could just as easily do this as well. Means exactly same thing. So this select statement I was selecting my var. He's going to give us the answer for you. Declared available. We sent it to fall. We've output it it. But what if we delete that second line? What is the value off my vote now? Some programming languages would make any integer that's declared zero by default. No SQL server as girl server gives it a I don't know. It has no idea zero would be a figure thief. SQL Server was to say this figure is zero. Well, it doesn't actually know that. The answer is it doesn't know what the figure is now going to tell you this in the form of the word No, we will be running into the word no at various points during this costs. But let's see what annoyances it might cause you. What is this unknown figure? Plus two. Now, if it had set it to zero, would be getting answer to back. But we don't know what the initial figure is. So how can we know what a figure plus that we don't know? Plus two is that's with we don't What is this? If we multiply by two no divide by two or anything, I might have a very, very, very long list base. There's just one millet it. That means I haven't a clue what the actual answer is. This isn't limited to numbers you've had. Declare a strike and ask what that string value is. Don't know. So what's the 1st 2 couches of? That's drink? I don't know. What's length of gastric? I don't know. Was the date alone for that streak? This is when it's lying to you again. It doesn't know what the data like that string is. It's just not going to tell you What is that data like for that string? While the number of characters is zero because it's an n vulture, we have to add enough to on. So that makes two. That's not actually the end of it when it goes into table, but at the end of it for now. Now it can't be a pain in the backside if you I don't remember that the answer could be yes , no, maybe for anything but the world fantasy. Choose to knows, let me show you one of them. Now let's go back to one of our earlier lessons. Onda we hade a decibel. Remember what five to means means There are five numbers in total five digits, two of which are to the right, and we're trying to convert that decimal or a number, which was the number 1000. If you remember, that doesn't work into I got let's form around. Go to convert decimal five to you got to convert into decimal five to the number 1000 police have to work out which way around it is because cast goals the oval I won't be found earlier. It's gotten arithmetic era. It doesn't work in that. In SQL Server 2012 they realized that this was a bit of a problem. And so Microsoft added two additional functions. Try, convert and try Cast. As you can see, it's in there, but it doesn't know about it is Fate Bazaar. But if you're looking books online is definitely a valid function. I notice it says applies to SQL Server 2012 fruit to the current version. So if you're using 2008 using 2000 and eight hard to this doesn't exist. And so, while they've introduced it there, no introduced it very well. So what happens now when we try and convert or cast something that doesn't work? We get to know we don't get no message. We actually get a table bike. The table has one by you. In this particular role in this particular field, we get no, no means, I don't know. So penny convert 1000 into a decimal with five digits? No. So I given our message. No, I'm going to say I don't know what the answer is, and that's a much better answer than giving in our message and having to try and find that our message. Just imagine what this means. You do a select statement on a table with a field, so it's like good decimal. It feels man from this table and you got a 1,000,000 roles and it worked for 999,999 of them, and it doesn't for one. And so it gives you an error message instead of showing you the table by adding, Try underscored the beginning. It gives you something he gives you all 999,999 lost the one that doesn't work. Obey that. It says the answer is no. And that, for me, is a very good use off. No, there are others that were coming on to you later, but this for me no can be rally irritating. But this for me, makes told worthwhile 24. Joining two strings or a string and number together: right in this lesson. If you have a look on the screen, we have declared a first name middle name in the last name and then set them to John Walker Smith. What we want to do is to amalgamate them together in the one field in the one column. So if you know of the programming languages, you know that we may have two choices we have over the come percent all the plus and see if the other soundworks no incompatible with the ampersand operator. No words doesn't work. Let's change the surpluses that works. So when joining get two strings together, you cannot use an ampersand. You have to use a plus in T SQL. That's that's, um, Spaces and see what we get to now. John Walker Smith. Okay, let's change the name this person's name is. It's, uh, Milica. Can you see what the problem is? Yes, there's no middle name. What does that mean? We still got a middle name declared, and we're still using it in our slick statement. So what's the value off middle name? If we haven't set it, it's no. So what happens when we combine Sarah No and Millikan together, the computer doesn't know what the answer is going to be. The answer is Donna, and that's exactly what he will give us. No, if you just add anything to annul or if it's a number string, anything the answer's gotta be No, no, it just doesn't know. So how can we get our way around this? Here's one possible solution. We're going to assume that middle name is the only one that could be left out. So first name is never going to be No on last name is never going to be No. What we're going to use is something from Microsoft Access, which has Bean added to t SQL to eight compatibility between Microsoft Access and T SQL. Some people don't like it for that reason. We are saying that if notice that if has two eyes eighth middle name is no notice. I'm not saying equals no, because anything equaling No. Well, I don't know if four equals no or five equals no or Teddy cause no. And I don't know that Noel equals no mean. Just remember what no means. I don't know. So does something that I don't know equal something that I don't know. I tell it now. I don't know. The first thing is that none of the second fingers I don't know if the recall, but I can ask. The question is something is actually No. So I'm not trying to compare you something. I'm just trying to ask it of its state, is it? No. If so, then comma, give me a string, which is empty, if not, give me a string which has a space and then had that to the middle name. So now we have If middle name is no, then we have first name plus nothing plus a space plus last name. Which is what we got here. Sarah Millican. However, we could have a middle name not being no middle name being equal to something. And they would have first name on then a space and then middle name in the space in the last name. So if we did set the middle name Jane, then the middle name would no longer be No. So I'll ask the question if the middle name is no, while it's not. So we don't go after the next bit. We got to the bit after that. This bit. That's one possibility. And as I say, some people don't like using. If with a capital I two out sex with to lower case eyes, that way they could be up a case. It's just the quantity that's important to know whether they are up a law. Here's another way of doing exactly the same thing. So we slept in the first name on. We're adding it to something and then hardening into a space and adding it to the last name . So let's look at this thing instead of using a faith. We're now saying case. So in this case, do this in another case. Do that now Case can have more than one or more than two outcomes. It could be in the case of a do this. In the case of B, do that in the case of sea, do the ever in all of the cases do something else. It will do the 1st 1 which is correct. So if it's this, then do that and then ignore everything else. So case when middle name is no, then anti quotation marks. So if middle name wasn't old, then what we would do is give me dente quotation mugs. You look for the word end and then go past that. So in that case, you will get first name. Add it all too. Case when middle name is No, that's true. Then give me nothing. Find an end. Give me a space. Give me a last name. So if I run this, we have Sarah Millican. If we add Jane back in, then select first name on. Okay, case, right when the middle name is No, but the middle name is not know. So let's go on to the next condition. We haven't else so else's everything else. So give me the space. Give me the middle name at the end of the case statement. So let's continue then give me a space to give it the last name. It's for me a bit more confusing than the if statement. The if statement is firstly more compact. Andi, secondly, is a bit more familiar to me, to be honest, but there are significant uses off the case command, especially as we get into later. But this is certainly one another way of doing it. Well, this is another way of doing it. Not as good, though Normally it is good, but in this particular case it isn't. What's this core less statement does? It takes a Zeman E arguments as you like. It will even take one, though, why you'd want to use one of no idea. What it does is it looks at each argument in turn, starting from the left hand side and gold is this? No, If it's not know, let's take it. If it is no, let's go to the next one. So let's see. Middle name is no. We have a look. Go middle name. While that's no so I'm not going to use that, Let's go to the next run. An empty string is an empty string. No, no. An empty string is definite. An empty string is saying I have the information. It's nothing as opposed to. I don't know what the information is. So we had that your first name to space to space to last name. The reason why it's not as good in this particular case is because it gives us two spaces in the middle of a space here in a space there. We can't put go for that. They actually become, we can do that? Because if you do that, we have a space on the middle name. Nice theory. If this is no than a space plus snow equals no. So we have I know he s so it goes. Let's go into next one. You have an empty string, so that will work just as well. So first name Andi, give me the first non null off this Finally, this is microphone way. Using the calm cat, we avoid the problems of all of this. Plus, is the problem being that if one single entries no then the entire losses? No, What can cut does is it takes is many arguments that you like And if any single argument is no, let disregards it So let's assume middle name is no So we have a first name. We have a string plus the middle name Well, that's equal to know. So a space plus none is no So we have like that first name Nothing Here we have a space we have last day. Well, if middle name is not equal to know that we have all of these socom cat That is really how you get potentially no strings to join to each other coalesces enough. A good one. If has so many uses outside of this has does case. And by the way, when we get to case later on that there are actually two versions of case. This one is case when we'll see that there's a number less flexible while now, but still very useful version of case. Just will just, uh, revisit this again when we get to that area. So can you remember how to join two strings together? Let's see. 25. Joining a string to a number: in the previous slide show. We drying two strings together in this one, not going to join a string and a number together. So here we see my number is on 4567 Now is that 4567? A string or number? It's a string. It's enclosed in single quotation marks on his right. So if I execute it, you can see my number is 4567 How do you like that? 4567 as an integer as a number, I just write it. No quotation marks around it. It's not in red isn't black. So let's see what happens when we execute this. What we're hoping for is that is going to convert this into a string and then added to this well, joining to that. But it doesn't. Let's have a read conversion failed when converting the voucher value. My number is to the data type int, so it's not. Converting. The ent to a string is converting the string to a tent, and it fails because you can't convert. My number is to a natural number. Why does it fail? Gossiping cold precedence? We have a list here off all of the various data times right at the bottom. We have all of the strings further up. We have all of the numbers, so let's rejoice it say's the rules for Data Type President specified that the dead type with the lower precedence is converted to warm of a higher. So and if it's not supported and now is returned well, it's not supported. You cannot convert. My number is to a number. So therefore, we can't rely on implicit conversions. We have to explicitly convert it. Now. We've seen how to convert all cast numbers into different types of numbers. So will this work here? So what do we do first, convert right the data type. What data type we're going to be converting into a strength. So let's call it a vodka. It doesn't really matter what the length is. As long as it's long enough to contain this drink, what will be converting 4567 a closer brackets? So would this work? Yes, it does, and similarly, we can also called use cast as well. So let's take this stuff over. My salary is so let's say your salary is 2345 so you got to convert it number. So here we go. My salaries. 2345 And it works. But does it work well? I mean, 202,345. What Pounds dollars? Euros, Rupees yen Now, of course, we could add dollar sign here, but let's just had a bit more. 2345.6. We really wanted to say top 60 most currencies outside off a few light. The Japanese yen used to decimal places so works for no one. What we can do instead is use a function cold, full marked now. First of all, we give it the value that we want to convert to. 3456 notices is opposite to convert but similar to cast. Now format knows that we're formatting into a string, so we don't need that. But now it needs to know how we want to format it. Now we want to convert it into a currency looking string. So to do that open quotation mark, see close quotation, not close bracket. Let's see what happens. My salary's on its nicely converted. $2345.60. Now let's just expand this a bit more. Suppose we didn't want it in dollars. Also wanted it in pounds sterling, where there is 1/3 optional at attribute. That this function can give can use furred optional practice and it is called the Culture. So it wants to know into what culture into what society we want to put it in. Now the British culture is a string off e n law case minus be off the case. So now what? We do it. We get pound sign 2345.60 But why is this important? Because simply put the pound sign here. Well, let's change it to French fr fr. In France, they have the currency symbol. After the number, I notice how it's for much of the number. They don't use commerce in France to separate thousands to use air space, and they don't use a full stop off period or decimal point. The's a comma, so it really converted into three culture of where you're going now for full list off all of the various things that you can do. You should look up standard numeric format strings so Let's see what you got. First of all, we've got C. See his currency and you can add a number afterwards. The number of decimal places. So the standard Japanese currency, it doesn't have any decimal places. They don't have fractions of yen, but you can add it if you so wish. De is decibel. He's exponentially probably won't want to use that much. F is fixed point. Jeez, General used that one new. I don't really want to say specifically what you want it to go in. But notice, for instance, what would get to the US version? It's got a full stop when we get to a Swedish version. It uses a comma. So these are the major ones. See for currency dif a decimal F for fixed point G for general. And if you've used C sharp if you've used Phoebe dot net, you know these pretty well because these are exactly the same as in don't necked. So you have to convert numbers strings over wise. What you're doing will fail on what you're doing that you might store format it well, let's see what you 26. Date data types: Hello, Right now, We've gone through numbers and strings. The next thing, we're going to go through our dates. Now. If we have a look at Excel on putting the date today, let's put in the first of January 2000 and 18. You can see it is for mattered in this British version one Jan. 18. However, if you change it to a general format, we see that what's it House leaders is store. It has a number, So this is a number from a date early in 1900 or 19 or four, depending on how you got your computer set up. So if I put in a date a time first of January at 12 o'clock, just make it six oclock and former that as a general, you can see that the 6 a.m. is being converted to 1/4 of the day. So it's saying it's 43,000 and solving days plus 1/4 from the first general, the 29 for generally 1900 or 1904 That's fine for Excel. SQL Server doesn't work like that. It actually has native date fields on the There are major advantages of that in that it knows that it's a date field. It doesn't have to still a number. It knows the precision is going to have to start with. You might need it down to the second you might need to down to the millisecond. You might just need it down to the day. And you might also need to know that it is a time here in Britain or time here in New York so that you can convert it to British time or two American time or two Japanese time later on. For this reason, SQL Server gives you six different types off date and time data fields. Here, the types we start off wave, probably the most used one date time. As you can see, it goes through from the adoption of the Julian calendar in Great Britain. 17 52. That was in September, so it start from 17 53 on goes through to the end off the 10th millennium. It is accurate to 1 3/100 of a second, so a huge date range very good accuracy. The downside eight bites, and that is a bit of a downside. And so later versions Microsoft decided to introduce some more date fields to get around this problem to store with sufficient accuracy on Jews, less data, princes. You might not care about the time. Which case you can use the date field day field goals for as many days that you might want I could to a day, but it only requires free bites so instantly you're saving 62% off the size of date time, which again, that's a shame. Got a database with 1,000,000,000 rolls. That's five gigabytes of data, say just then, or it could be that you're not interested in the data, just interested in the time. Then you can use the time data type so you can get it down accurate to 100 nanoseconds that is 1/10 off a microsecond. It's certainly is very small. Under Rick Wise have free to five bites, certainly a lot smaller. You can define how accurate you want it. Is the state time too big and to accurate? Well, how about if you could get another data type that is adequate to just one minute so you don't need to stall the seconds now accurate to one minute. That doesn't necessarily mean you just started in minutes, so just be careful. I'm not saying that it is going to be necessarily approximate, but you should always tested just in case. Now, this doesn't have a huge date range. It starts from the not the beginning of the 19 of the 20th century because that was 1901 But it starts in 1900 it goes through to 2079. Now, why could that be a problem? It could be that you're looking at retirement dates for people. So let's say I'm 40. Let's say retirement age is 65. So if I'm recording this in 2015 that that means that the retirement age would be 2040. But what about a new bone? His retirement ages. Let's say 65 65 adults 2015 is 20 eight. We're already outside this date range, so it's accurate to a minute. So this is good if you need the date. Andi, roughly your fairly accurately, but not completely accurately the time, and it still is. It was just four bites half of what date time needs, but its usefulness is going to be dependent on whether you can only get 64 years into the future. So if you're taking out a mortgage, you might have a 30 year mortgage just fine. You could use it for that. But in Japan that had Bean in one case, one time 100 year mortgage go out, you wouldn't be able to use small date time for that date. Time to is also one of the newer ones. You can define how accurate you need it, and yet it does it within 6 to 8 bites. So it's still cheaper in terms of bites are potentially saw the date time, but she could still two more decimal places. You still to eight decimal places, 87 on a wider time. My job here from year one to year known and the line was this one just called from 17 53. We'll have a look at the last one that later. So how can you remember this date? Time is the standard date. Time is the standard. Now, if all you care about a date or time, then you got those. You can have a small date time, which our needs to the minute. Or you could have the new daytime too, which allows you to go down to microseconds. So I start off with date time and build up from there. Date time, and then you can have a small dick time. Or you could just have the time. You can date all. You could have daytime, too. 27. Setting dates and Date extraction: now, you might have noticed something interesting in this format. Bit on this Web page from books Online. The format is Chyna's year month date. I sat. Today's date is June 24th 2015. If I was to Wrap said that in Britain would be 24 slash six slash 2015 in America, it would be six slash 24 slash 2015 in Japan would be 2015. It's hyphen zero six hyphen 24. The reason why the Japanese format is used is because when you add a time as well, everything is in descending order from the biggest units. First the year, then month, then the date than the hours into minutes and seconds. And this is exactly the way that you will set it. So here we have a date, declared his date time and refuse exactly that. Former 2015 or 6 24 12 34 56 duct 12 full. Run it. We have the correct time except to say, Is doctor want to free? Why is that is because we declared it is a date time and if you remember, date times are accurate. Toe 1 3/100 of a second. This means that the third decimal place in the seconds of a date time will only be a free a seven on a zero. Now, you might be asking yourself why not a six? Well, 1/3 is point free, free food for free. So that rounds down four point free. But 4.0.66666 rounds up, 2.7 point 990 rounds up to zero. So be free a seven hole zero. Next, we have exactly the same thing. But without the hyphens. Will this work? Yes, it will. But if I change this from a date time to a date. Time to now we've got theocracy. And that's how Chrissy something you might need if you're holding it hundreds of second thousands of a second. How many digits is this? Seven. For hover over this, you can see that the full thing is date Time to bracket seven. You can choose how accurate you want. So here we just say we want three decimal places here. We're saying we want five now. The reason for choosing different amounts is because if daytime, too, if this figure is up to three that he would take six bites. I was. If it's more, you take seven bites or eight bites. You can see this on the books online pays, So if it's too, it takes. Exp ites. If it's a three, it will be seven bites, and if you more, it will be eight bites, so that will take eight bytes that will take six bites that will take seven bites. Sometimes you just want to make sure you have the right number of bytes. Now you can declare dates in other ways. Date from parts. Is the equipment off the date commanded? Excel? We specify in the parameters of the arguments. Three arguments. Year, month date. So run this and that's what we get. You can go all the way up to date time to from parts. Now there is a date time from parts and the over things like time from parts, but would choose this one because it's the most complicated. Here we have month, year, month, day We have our we have minutes and we have seconds. Then we have division of seconds. Aunt, How many decimal places that is so 56 adult 1 to 4? Because we said three decimal places. That's for a nut, and that's what we get. If we had said five decimal places, then it wouldn't say. Don't want to. Four. You decide. Don't 00 1 to 4 because we're just giving the number 1 to 4. But then we say it's five decimal places, so that one takes eight arguments. Year, month, date our minutes, seconds, sub seconds. What those subsections are. So if we go back to this, we can see that we can now extract the year, the month, the day from it, so we can see that the year is 2015 months. Six. The day is 24 and so we'll add to that by getting the hour on a find that you can't actually do it that way. So we got year, month day. If you want to get the time, then we have to use another function and we look at that in the next lecture 28. Today's date, and more date functions: What's today's date? Well, you can see from the Bottoms Green its 24th off June. But how do we get SQL Server to tell us what the day's date is? Well, there are three different ways. Current underscored time stamp, get date with brackets and six state time with brackets. Do we really need three different ways to get today's date? Turns out, no, we don't. But there is a reason Gate time Stump is the standard version off getting today's date and time, which is in more than just SQL Server. It's used in over SQL languages. Microsoft presumably didn't like that format of current underscored timestamp. It's nothing that there used it, Don't generally used underscores in their functions. And so they created their own version called Get Date. So for Rondo's, you see those givers exactly the same answer. Andi. What date type is this? If I wear this again, what data type do you think that is? Well, it's got accurate to three decimal places on. The last one is always a 03 or seven, so that makes it a date time. This one, which was introduced more recently, gives it to the daytime too, which is more accurate than a date time. So here you can see it being accurate to seven decimal places, and you can see that the answers are not identical because it's taken 3 1/100 of a second freeborn thousands of a second to get the answer. Or it may have bean that this isn't 185 rounded down to 18 free, so it may have taken just two or 1 1/100 of a second. We don't know this is not precise enough to be able to give us the exact difference. So if you want to get him or exact answer of ways right now, here you go, sis Date time. Let's have a look. It's a few of the date functions. Date add enables you to add a particular unit of time, such as a year to it. Eight. So here, amounting one year. Two. Generally, the first 2015 gives is generally the so generate. The second 2016. Not is how you can do this. You can give it a month minute. Microsecond. Yeah, there's so many different things you can do, and you can actually do it in plain English. Here we can extract the hour. Remember last lecture. We were not able to extract the our from a date, but date part allows us to extract the part of the date or time in this case, the time. So it's the third hour. Now here's an oddity. Not the function date name, but how it's. Actually you can actually use Intel a sense so open brackets and I have to have the interval. So I want the interval. I want the week day So Friday, Tuesday, Wednesday. What's having days? So if I start typing in week day, you'll notice that intelligence is not actually working. But if I delete what I've just done and try Game intelligence works very out. You've also got I saw a week. That's a week. Number week means week of a year. Isil Week also means week of the year, except if a year starts on a particular day, like 1/3 like a Friday, Saturday or Sunday Week. One of that year is counted as the next week, whereas if it starts on a Monday Tuesday Wednesday, Thursday, it's counted as the previous week. Did you have Thursdays The next week so I So I so weak. It's basically it's half the week in this year's are half the week in the previous year, so you get slightly different answers from weaken ice a week. So if you find that one of them is not working, you might want to try the four. So now I put the day and see the answer. What is today's weekday? It's a Wednesday finally date death. This gives the number off Miss Kay seconds from this particular date to know 15 million 162 of her be nearly round number. And obviously, this world change every time I click on it, because now changes what would happen if I was in the world. We're going from January. It's now June that that's five months on 22 days. What does it do to the 22 days? Nothing. It just gives us the exact number on what will it do if I ask for microseconds? The number seconds is 15 million microseconds would be about 15 billion on The problem with this is that we are getting an int back, not a big and and it's go up to what figure do it's got to the highest value can have. It's about two billion. We're trying to get 15 billion. That's far too much. So the limitations. But they're not that bad. Limitations. So you've got current underscore Timestamp, get date, Andi Cyst A time which will get to today's time in daytime. Daytime. Daytime, too. We've got data. Add date, part date, name, date sacked, date deaf. They all start with the time unit day, then continue with the date except for date at which wants to know how many you going to add digged if once the time unit and then the early date and then the later date so they add the part date, name, date, death on get date. Those are the ones I suggest you memorize. 29. Date offset: In the previous lectures, we locked out various data types, which allows the saving of date and time with varying degrees of precision. However, we omitted one, which was that none of them talk about the time zone in which those dates and times were taken. For instance, right now the time is seven minutes past four in the evening. In here in Britain. However, in India the time is 2036 because India is 5.5 hours ahead of GDP. I was in Houston, Texas. The same time is 10 or six in the morning because it is under central daylight time, which is GMT minus five hours. So what time it is is partly dependent on why you are. And there is one data date one day, one deter field type that will actually still that's information. It is the date time offset the offset being the number of hours ahead or behind GMT. Attar's so it's declared as any ordinary day time. But there is a bit of a Miss Norma in the statement that it is the date time offset. It seems like it's related to date time, not date time. If you remember is precise to 1 3/100 of a second, which means that the third digits after the decimal point can only be zero of three or seven, while data more said it's actually a variant of date. Time to daytime, too, can be precise up to seven decimal places. So if you have a look at this first statement, we're declaring available as daytime offset. We're giving the date, and afterwards we got a space on that, a plus or minus sign, and then the number of hours and minutes. That's different. So if we execute that, we can see that the date and time is I shall but notice that it's seven decimal places. On the third digit is a six, not a seven. So dig time offset is related to date. Time to now. Daytime to takes 6 to 8 bites. Date Time offset Needs a bank for the number of hours, which can be up to 14 hours, plus or minus hand. A bite for the number of minutes, which can be up to 59. So you could if you wanted to have plus 14 Colin 59 or minus 14 color. 59 so daytime offset requires 8 to 10 banks, and that is dependent on your precision. Just saying daytime offset means that that it is precise to seven decimal places, and you can see that in the results of the bottom. You can change that to, say, date time offset to which only requires eight bytes, not 10. As you can see, it rounds it to the nearest two decimal places. In this case. Now, this isn't the only way that you can declare daytime offset. You can take a date, time or date, time to or date or time and use the function to win. Votes were transferring it to a date time offset. And that says two arguments to parameters, the first of which is a date time to or something that can be implicitly converted to daytime, too. So it could be a string. It could be this string. Here we just turn, have that string in here or could be available that can be converted to daytime, too, and then we have the resulting time zone. Now, the important thing to remember about it, too daytime offset is it doesn't actually change the date or time. It just adds the time zone. So this is one. What am. I think a time zone will still make it 1 a.m. It just adds a time zone. It is not to be used to converting now. Another way is using a function that's based on the date time to from parts. If you remember from that, I think two letters ago, we have lots of promises, eight of them being the year, the month, the date, the hours, the minutes, this whole seconds, the part of seconds and harming decibel places. It is now the equivalent command is daytime, offset from parts. So again we lose the two. But it comes back as a daytime, too offset. So I really wish you would call it a day. Time to offset. But it's called the daytime Offset. So dig time offset from pants starts the same year, the month, the day, the hours, the minutes, the seconds the point, the pilot of seconds. And then we get the extension. They get the time zone here in the middle, 5 30 in this case, and it's on the precision. The number of decimal places off the seconds on the girls at the end. So the offset is putting things and middle off this function. And that's easy to remember. If you remember that the function name, date time offset from parts. The word offset is in the middle. Why did it that way and not put the duh? The times are at the end. I have no idea. In the time zone is at the end up here. Whites in the middle here. No idea. It's just something to be aware of. Now you remember we had current on the skull. Timestamp had get date to get the current date on. They gave you date. Time answers. We had s wise date time function, which gave us the current time in daytime too. So more accurate is to seven decimal places. Well, we have that's here as well. We have date time offset, sis. Daytime offset. And that gives the current time accurate, too. Seven decimal places. I'm not sure why. It's only giving me free decimal places, but if you have a look at books online does say returns the date time offset seven value. So it could be the accuracy of my computer. It tells me that I'm in Britain in the summertime and so that is G O T plus one hour. Now, if you want the time in GMT. Another way nowadays of saying GMT is UTC which is coordinated universal time. So we have s I s UTC date time. The system GMT daytime except GMT is translated to UTC and so here we see that the UTC time is course past three. So if I run these two together, the time here in Britain is 4 15 The GMT time on the UTC time is 3 15 now said previously that to date time offset is not to be used for converting time zones. It's only to be used for adding a time zone to an existing date and time information. If you want to change the time zone, use swift use switch offset. Here it is, which takes the date time offset so devotes a day time that already has a time zone that his times on aware Andi your new times on. So I'm going to take this time which is in India, and convert it to warn which is in Texas, which is GMT minus five hours. They're in some of time as well. So if I execute you see that two minutes past one in the morning off the 25th is half past two in the afternoon, off the 24th in Texas. So there's a lot off functionality here which can be used just as easily as your date. Times. You just need to remember that you need a space and you need the plus all you need. The minus is not optional. Andi offset girls in the middle of this one day time, offset immediately after the point number of seconds. 30. Converting from dates to strings: In previous lectures, we looked at date time data types. We also looked at combining strings and numbers together. Let's have a look now had combining strings and date time types. We've got the sort of implicit conversion in row one. We are declaring a variable as a date time and using a string to set it. That is known as an implicit conversion. Does it work? That's highlight Reel One. Yes, it WAAS command completed successfully, so we got down to roll too. We're combining a string with a date time. All this work? No, The reason it won't work is because of the order of precedence. A string type has a very low order of precedence. Above them come numbers and above them at the top or never talk. Ah, date Time Fields. What is trying to do is convert a string ink type, which is law down into ah, higher version, the date time. And it's failing because it cannot convert the string. The date and time is into a date. So one way of doing this is by using tools that we've previously got convert and cast. Here, you can see we're converting a date time into an enV arch are Andi. As long as the involved char is long enough, they should work so you can see it's converted it into June 25 2015 1 or 2 a.m. We can also use cast for the same work thing, So now it is converted into a string. We can I just ring to it like that. So the date and time is June 25 2015 World to him. But what if you don't have it in that handy format? Has a string? What if you have it like this Thursday, 25 June 2015. Can we converted like that? No, it needs it in a relatively straightforward format. What doesn't, though, is the function of pass past looks of the string and tries to make sense of it. So here it sees Thursday on Git knows that's going to be a day on day 2015 2015. So what's intelligently at this string? So if I execute this, you can see that it does convert Thursday 25 June 2015 as a date. Now what if I accidentally said Friday the 25th of June does it actually just ignore it? As you can see, he doesn't work. It knows that there's a problem. So it's got to be correct. How good is past? We've got here a dating Spanish. Why vis went you think of the Cuneo, the 2015? That's meal king today. Can it extract the dates out of that? The answer is no. But let's look Theo message to see why Eric averting this string value into a data type date using culture, culture, meaning language blank. So what we have to do is we got to give it a culture. We've got to tell it. This is in the Spanish language. We can do that here in the past. We can end with using, and we can give it the culture in this case, punish Now. What that last bit means is that it's Spanish off Spain. You could have Spanish of Mexico, Spanish of Argentina. So let's execute that, see if it works. And there yeah, Corvis. But do you think of the who kneeled just meal Keith A. Translated into 2015 or 6 25 apologies. By the way, if you do speak Spanish, no, my accent must be dreadful. So that's converting strings into dates. What about perverting dates back into strings? We've had this particular example, but we weren't able to do much with it. While the format function gives you a lot of flexibility, so it takes two things. Festival. It takes your date, time or date tank to date. Whatever, Andi. Then you tell it how you want it to be converted. Now there's two standard conversions. The 1st 1 is a Capital D, and the 2nd 1 is a lower case T. Let's see what they both do. So as you can see, the 1st 1 converts it into a long form. Art. Thursday, June 25 2015 On the second Diversity Just short format. 6 25 2015 Using, in this case, the American format. Now we can override it because they want you the British format by just entering a cousin format. And if you're used to Max Optics Cell, you're probably used to custom formats already. So here we have Data's two characters. The month is two characters and the years four characters. Let's have a look at that, so we have 25 which is the date 06 which is a month 2015 misses a year. You might notice that I've got the month in capitals. That's because if you have a no case, eight things you're talking about, the number off minutes. Just see here that the number of minutes is too. If I change the number of maids too 59 for example. You see that we got the date. The number off minutes on the year. So just what the lies months need to be in capitals. Now, how these dates are formatted is dependent on your culture. Here. The culture is United States. But what if we wanted it to be in a different culture? What if we wanted it to be in Spanish? Well, the full mart function allows 1/3 parameter, which is culture in exactly the same format that we had before. So let's see what it is in Spanish. Whoever has spent a thing called a Cuneo to dust me looking What about if we put it into German Don Stark, for instance, I see who? Something like that. You could even put it into Chinese. There you can see 2015 year, six months, 25 day. So this format command in Line 14 will be different. If I'm using an American based computer or a database that has such a culture, then if I was using a Chinese one, so maybe best not to override it unless you're developing application for specific language market Now, finally, there is a little bit that you could do. We've converting this state using a convert function. There is 1/3 argument is a numeric one on. You can see that changing it gives a little bit of a difference, formats it according to different cultures in the books online that you can see that there is a page and a cast and convert, which say's what each of these numbers mean. Basically, I would only learn the ones that's actually apply to you, Andi. Possibly not Even then you can always let them up. Now this sort of thing can't be done using cast. If you have a look at this in tax, convert allows this stale well, as there is no such style argument hearing cast. So to convert from strings, my advice is, choose the past. If you're converting from a date, type to a string my vices use the format. But bear in mind even though things like this fail, what you can do is use. Try converting. Try cast that. If it does fail, you've got to know rather than in our message. 31. End of Session 1: well done. Made it to the end of session one. What's a session is being Just think what we've done. We have downloaded and installed SQL Server on your machine. We have created a database created table, drop the table and then we have learned all about date number and string data types. We've also learned a lot about functions. How to Munich later, How to get the first part. That last part of strings. How to get the year or month of dates on how to divide inter, jizz multiply Andi Anything else we need to do? Raise it to the power square root all those things now, You may not have got all of the information just yet, but that's OK. What you can do is you can really run some of the videos. If you want, you could take more of the quizzes, get us information solidified in your head. Alternatively, you can continue to session to because session to bodes in session one. So we'll be using information that you've just learned in future sessions. So what is seven to session two will be creating tables which used these data types. Andi, I'll be asking you which data type is best for particular situations. Then we'll be writing queries which retrieved and summarize this information. And then we'll create additional tables and joining them all together. We will then update on delete data from the tables. So it's enough a big session. I'm sure you want to try this. So as it's the end of this session, why don't you go and have lunch? I'll see you in section two, but I