SQL for ABSOLUTE BEGINNERS | MICHAEL DUFFY | Skillshare

Playback Speed


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

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

27 Lessons (6h 19m)
    • 1. INTRODUCTION

      4:42
    • 2. Install SQL Server

      12:56
    • 3. Databases and Tables

      3:56
    • 4. A Basic Query

      19:43
    • 5. WHERE Clause

      15:20
    • 6. Schemas and Aliases

      3:01
    • 7. NOT Operator - its NOT bad!

      4:03
    • 8. Other Text Operators

      9:32
    • 9. NULL - it's important!

      5:16
    • 10. Prime Keys

      7:33
    • 11. Column Types

      17:14
    • 12. Text Operations

      25:49
    • 13. Working With Nulls

      11:36
    • 14. Working with Numbers, and Assignment 1

      10:38
    • 15. Data Type Conversion

      19:02
    • 16. More Text Operations

      12:31
    • 17. Joining Tables

      31:38
    • 18. Reasons for Left Joins

      5:43
    • 19. Cross Joins - Just don't do it !

      7:18
    • 20. CAST conversion, and Assignment 2

      13:46
    • 21. Making a CASE for smart queries

      16:13
    • 22. DATES, and Assignment 3

      30:08
    • 23. Recordsets - They Help a lot

      14:36
    • 24. The UNIONS make us strong!

      24:29
    • 25. Grouping, and Assignment 4

      29:50
    • 26. ROLLUP, ROLLUP! Automatic Totals

      10:25
    • 27. Sub Queries - "Sort Of" Joins !

      11:43
  • --
  • 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.

68

Students

--

Projects

About This Class

From NO CLUE to GURU ! 

Confused by SQL?  This is your one-stop complete guide that assumes no prior knowledge, and draws on 20+ years experience in best practice SQL query design. 

Students can enter this class with no prior SQL experience, and leave with the skills to write advanced queries for any industry application.  Be it application development or report writing.

This courses use the Microsoft SQL server platform, but ALL SKILLS learnt here are transferable to other platforms such as MySQL and Oracle.

If you enjoy this class or would like to leave feedback, please leave a review!

f89c82c1

Meet Your Teacher

Teacher Profile Image

MICHAEL DUFFY

SQL Developer and Troubleshooter

Teacher

Hello, I'm Mike Duffy.  I've had over 20 years experience supporting applications with relational database back-ends.  My roles have varied from training, first level support, SQL code development and business analysis, for large education institutions and government departments.

My approach to training is to "break it down" in logical structures , starting from a common framework we can all understand, and keep it fun along the way!

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
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.

Transcripts

1. INTRODUCTION: Hi, My name's Mike and welcome to my SQL course. This course is designed for anyone to come into who has practically no prior SQL server experience and leave with enough of the core skills to be able to write any query for any real world application. It's ideal for students who may be doing a development course of some sort. I need to know about data back ends in order to write queries to supply data, Teoh front end applications or to anyone who has just taken up a report. Writing brought so crystal reports or something like that. You know, I've been working in for 20 years supporting an application with the next couple server database back in as my mental. To bring that experience to the to the course are not only in the court query coding skills but also coping with some of the more common and quite annoy error messages that you may get along the way. And we're gonna learn to how to cope with us so that any further, please enjoy the course and let's get stuck in and have some fun. So welcome to SG well, for absolute beginners and the topics are going to bay. What is SQL? What is the SQL language? What does this give all stand for? What is a database? What makes up a day device? The tables and columns, The various column types start off with a couple off simple SQL queries and we're going to do operations involving text and numeric values, manipulating the text, manipulating some numeric values, some calculations how to refine down as selected data without the where clause an important part of SQL some basic SQL functions and the basics of how to select from multiple tables using joints. So we're gonna go through sitting up our training environment. We're going to download and install SQL Server Express. That is the database engine that we're gonna be doing our training in. Rossa Grew download and school SQL Management Studio that is a front end management and query soft way in which we're gonna be doing at queries and querying this device. The adventure works. Sample died of eyes which we're gonna download and set up on SQL Server Express. So what does SQL stand for? Well, it stands for structured query language and that's called dumb query language, because it's a language we use for writing queries, but it's structured in that it's constructed almost like a plain English sentence. Like most sentences, it's got clauses, and they conjoined the get together to form basically a question a plain English, almost like a plain English question that we can direct to our the data source. Now quick word about the actual SQL language that we're going to be learning. There are different versions off the Eskimo language out there. For example, Microsoft's version of SQL is called T sequel. Oracle's version is called Pl SQL. Now, between these languages, there are some differences. But these differences are more in advanced programming commands in terms off the fundamentals of just doing queries, selecting data, they're essentially the same, and they're the same because most SQL languages out there, including the two of just mentioned they will comply with the ANSI standard with respect to the basic QUERIAN commands and the fundamentals off the query language. So in terms of the those most common languages are implementations of SQL, Microsoft and Oracle. In terms off the fundamental commences, selecting and querying they are the same throughout, and those that fundamental part of SQL that is common throughout is called NC SQL. So that's what we're going to be training and see actually stands for American National Standards Institute. In case you're wondering, so that's every time you hear the wood ncsu well and little, the commands we're gonna be landing will be ansi compliant. And if you're actually Google women, look up Microsoft's website about SQL. You will see that this command in that commander all handsy compliance, that everything that we're gonna be learning is anti compliant, which means it is a usable, but that skill is transportable across all platforms. 2. Install SQL Server: Okay, let's get stuck into installing SQL Server Express 26 day. So this should be the file that you have downloaded on Microsoft's website. It is the dot xy that's ah program. So to install, it will just right click it. You should see something like open or run at the top there, in which case, select that. But if you see this one here, run as administrator, select that cause that'll make things go a little bit easier with Windows 10 and security and stuff. So if you see runners administrated, just do that and get a bit of a split screen happening, okay? We're just going to select our basic SQL Server. Can have a lot of models toward fancy modules. We don't want that basic is just going to install the database engine, and that's what we want. So we'll just pick that. OK, but what Euler except that select the default in store folder now, that download may take a little bit of a while, So you're just gonna be patient once the actual in store files have downloaded, then it will begin in story. Okay, so the insults finished, and it's given us this little results. Poach are telling us some of the details and defaults that are used for the install. We don't need to know about this stuff, but what I will tell you is what it has done is it's installed the basic SQL Server, that database engine, which is a much of software sitting on your computer's hard drive. And it's ah, well, it's just sitting there and it's Ah, it's able to host databases, but unfortunately we don't have any way to send it commands. We have no way of opening it up and talking to it because it's just the basic engine. So if I just very quickly show you where it is to give you an example, I go into see Dr Program Falls, Microsoft SQL Server and the You Got. You got all this stuff here, and that's what we've just installed. Now the thing about SQL Server is it's a service services basically offensive would for a program that is running on your computer all the time. Now we've got to make sure that that service, that is, as people serving is running now. The way we do that is I'll get you to go to start menu. And if you confined control panel, however it is where it is in your start menu, Find control panel and go Teoh. Uh, I'll just make those large icons any. Go to administrative tools and just make those large icons there. And if you go to services, it's this thing with a couple of Cox services that just open that guy up. I can't now. As you can see, there's lots and lots of services running many all sorts of wonderful things. You don't need to know all this stuff, what I'm just going to show you. And there's a little something that you need to know to make SQL server work. So if we scrolled into where it says SQL and hey, presto this SQL Server and in Brackets SQL Express. So that's the service that is SQL Server. Now, just double check two things, one that its status is running, and secondly, that it's startup type is automatic. That means it will start up every time you start up your PC every time you open your PC. So in other words, it's already running, and you don't have to do anything and you can start doing queries and completing the course . Um, that's said to automatic. There's a couple of other options. There's manual or disabled, but as long as that says automatic, you should be fine there, just in case that does not say automatic. We can fix that. If we right click and go properties anywhere on the road, they just right click on properties and aren't you? Get a dialogue pop up and on the first tab called General, You've got a field called startup type that should be set toe automatic, so just make sure that's automatic Click OK, and if it doesn't already say running, they're just right. Click and guard, right. Click anywhere on the road and Gar start and you'll be up and running. The engine will be running, and then we can start making it host out of Isis and and do queries and stuff. So we'll just close that and I'll just go back to the install with it now, a site that is just the basic engine, but we can't do anything with that. We can't talk to it. We can't send it commands. To do that we need are sort of front end program as we call it to actually make it do things. And it's called SQL Server Management Studio. Or is this and miss for short? And we come back to the results. Going to the installer are there is a button that says in store it says him it. So let's just quick that and get management studio installed. Okay, now that will take you to our by Microsoft's Web page to download medicine studio so you'll see some sort of a lengthy now, of course, webpages changes over time. So a time of doing this yourself this might look a little bit different, but you should see some sort of link to that, says Download Management studio will similar sorrow. What will just click on that link and I'll get Save that file. Of course, that process may be a little bit different, depending on the type of browser you're using. I'm using Firefox, but one way or another, you should end up with some sort of a dot exe file that has the wood S S M s insider. So to install luck Management studio just right. Click if you see open or run to select that. But if you do see run as administrator again. Pick that option as it makes things, makes things go a little bit smoother. Okay? And sit ups complete. Now what? Depending on the circumstances, you may see a restart button there. Which case just restarts like that button and restart. But in this guise, I'll just close and we're ready to start using management studio. So I'll go to the start menu and search for something that has managed or management in it . And there was the icon for Management's Jr. So you know what? I'll just up in that to the task bar, make things easier for me and will run, okay? And it started up. Now. Every time you started up, you'll get this little log on box appeared. Look, just leave all those defaults as they are. That is the name of your your survey, your SQL server. And that's the method by which you're locking in again. You don't need to know any of this stuff so well. Just connect. So by connect, we're connecting management's geo to the SQL Server engine and then we can send commands to the engine. Teoh do stuff like queries so broken it. Okay, now here is what's called the Object Explorer and that tells us about everything that's in our SQL Server database engine. We're only interested in this little folder here that says databases everything below. It is really getting into the guts of SQL Server, and we don't need to know any of that. So let's just expend databases and that will tell us all the databases that are hosted on our little SQL server. Now there aren't any, so we've got to set one up those two folders there. Basically, special types of existing databases don't need to know any of that. So the next step will be to restore the adventure Works training database on two areas cable server so we can start having fun with queries. So to set up the eventual starter vice first off, well, we have to get hold of it. And using the link in the class notes, you should, uh, end up with a daughter. Be a cave file that dot back stands for up back up that is a back up off the adventure works training database. So once you would like back far, the next step is to restore the database into its extended to split it up into two files, and it will set it up such that the daughter vice engine can talk to that database and extract out of from it. So first step is we've got to go back to up management studio, right click on databases and select restore database. Okay, so the first step is again This may look a little bit daunting, but I'm going to talk you through it. Sorry. Go do are the right hand pain here and this little section headed source select that radio button that says device and that will have a little button with three dots on it. Select that and that will actually browse a layered browse to the dock back file that you downloaded. So we got this little pop up here, and we're gonna add a file to it. Click on, add. And we've got this kind of weird looking dialogue. So if I go back to where I downloaded, it will go back to Where are we? Uh, day. So it's Pharrell's SQL Server. Adventure works, and there is where I put my adventure works dot back files. So selecting that by clicking on it and click. OK, wonderful. Okay, A whole bunch of stuff has been filled in, and more importantly, again, you don't need to know in most of this stuff. But I will point out that that little field there are just under destination. You've got a field called database, and that is the name off the database that will be restored onto a server. And once that's done, were able to start a fund queries. So from now on, that will be the name of the dollar based that will be working with adventure works 2016 or one word. Now just a zone aside, I will point out, if I just click purely for your information, this is not super important. But if you click on files here on, I'll just explain that whole window and I'll just expand those columns. They What that will do is that's telling us there are two files to this database. One is a data file and one's a log file that ends with the extension dot mdf and that it ends with the extension dot ldf. And what this is telling us is that is where those two files are going to go to, um, again. You don't really need to know that. I'm just giving you this view information in case, uh, because, I mean, you've got a right to know what's going on with you pay, say, but we got back to the general tab and that will set the restore running by clicking. OK, we got a little progress about the top and database it bench weeks 2016 restored. Successfully click R k. Wonderful. And if we look over here in the back of the object Explorer under databases Hey, presto. There is our adventure works 26 Dane database ready to go, and we can start having fun with queries. Okay, Now, just as a little bit of side information for your benefit A zay said that database is actually represented by two files on your computers are drive, and I'll show you where they are purely for your information. If I go to see Dr Program Falls Microsoft SQL Server, Emmis, Sq, a lot a whole bunch of stuff that ends with the word SQL Express, M s SQL and data. Now, if you click on double click on that data folder, you may get a pop up saying you don't have permissions to this. If you do, just click, OK? And it'll let you through. So I'm gonna double click on data. And there are my two files. Adventure works 2016 data dot mdf and log dot ldf There the two falls. That sort of make that up. So all the data is stored in's inside these two guys, um, again, just a bit of that side information. But with that out the way, now that we've got a database restored, we're ready to have some fun with queries. 3. Databases and Tables: So we've got adventure works. 2016 dot device restored, set up, ready to guard. But before we start getting stuck into doing lots of fun queries, let's talk a little bit about what exactly is that we're working with? What is a dye device? Well, a database is from a storage perspective on a diet of perspective. A database is basically a collection off tables, and if we go over to the left or to the Object Explorer, we can actually see these tables that we expand the tree here under a database like a whole bunch of folders. We don't need to know about most of these, but I will show you the tables and this lists the tables and they're all listed there. There's a few funny folders up the top that you don't need to know, but basically what we're looking there is for the tables. So what is a table? Well, you know what? I'm just gonna open one up and we'll have a look at it. So I'm going to go to this funny thing he called person dot person that naming convention. I'll talk about that in a little while, but for the time being, Let's just right. Click it rightly inside management studio and we'll go select top 1000 rows and I'll talk about the active selecting later on. But let's just do this just to get in and look at a title. Okay, so this is a table, and the table is essentially. It's like an Excel spreadsheet. Okay, so here we've got a typical art spread shake. Now a spreadsheet is arranged. Aziz, we've sure we've all seen many times is arranged with information arranged on Rose and in columns. The rose generally represent things objects that were collecting information about and the columns represent the attributes, all the pieces of information associated with that thing or object. So here we've got a row, that year's pace of events, a piece of inventory, and here we've got the bits of information better arranged in columns. That column, representing that thing initial value date, purchased that sort of thing. A lot of different types of bits of information. Great. Now a table is exactly the same sort of thing. A table is essentially like an Excel spreadsheet. It's a collection of data about something that we want to track that we want Teoh store information about where the rose generally represent an object or an item, or some unit that we want to collect information about eso. The road represents the thing, and the attributes of that thing are arranged in columns. So in this case, this'd a person table. It's about people in the company, and each of those rows represents a person on each of the colon. Represent. I think about that person, such as the 1st 9 middle name last night now, and we're going to get stuck into some super technical terminology. Eat the rose in databases such as these ones here. The road is the technical term, for those things are Rose. How simple is that? Makes perfect sense, right? And the columns in a table in a daughter vice title. The technical term for that is columns. How about that? Two. Simple, easy to remember now, just a sort of cover all bases in terms of terminology. Here through the Rose do have on alternative term, and that is records, so that could be called records for Rose. You may hear the term records used in various places, but for this course just to keep things simple, we'll stick to the shame. Royal columns also can have an alternative terminology. And that is Fields, uh, for this course will just stick to the term columns just to keep things pretty simple. OK, so that's basically tables. 4. A Basic Query: Okay, so let's get stuck into writing some queries. So if we stopped at this little window wipe in here, we'll just get rid of that by hitting the X button. A top they and that's gotten rid of that's called a query window. Eso. We've gotten rid of a query window, and I'm going to start a fresh one, and we can do that by going up to the top tool by he on this button. That says, New query just hit that on. That starts up a you query window, so we'll just type the following select at a spice and pastor risk and a spice and the wood from and the spice and person don't person person dot person is the name off one of those tables that you can see. You can actually see it down here on the bottom left person dot person. Now you may have noticed as you started typing that table name this'll, it'll drop down, appears listing that very table known that we started typing, and it's a bit like Google when you tries to auto complete things as you talked them in the query box. It's called Intel a sense If you find it annoying or distracting or confusing, you can switch that off and you can switch that off by that little button up the top. The that sort of looks like a tall rectangular button that sort of looks like a fridge. And if you're having a mass Point Rover that the tool tips, says Intel, a sense enabled so you can press that and turn it off if you want. Let's just personally, I'll love leave that running, but it's up to you if you want it or not. So with that type, I will now run that query Select start from person dot person, and we can run it by going up here to the toolbar and clicking the execute button. Great. And what have we just done? We have selected all of the data from the table that is person dot person. We've selling it all the rose and all the colors in that title. Now, the key thing I wanna point out, he is that the first would we've written here? Select Is the command telling SQL Server, the database engine. What it is this statement is doing? We are selecting. We are selecting querying data out off a table in the database, so you'll find that in SQL. Is that the first word that you type? He is the primary command telling the system the purpose off what it is we're telling it to . Dough Select is one of four core commands in the SQL language. The other three are actually part of this course, but I'll just touch on them just to give you a overview. The other three commands are insert, which actually inserts rose into a table. It creates new donna. Another one is update, which will actually go to some of these rows, and it will update or change some of the values in in the columns in the cells. On that on that record and the fourth command is dilly, which had chicken invention delayed one or more rose from a table again, a very serious command and not one that you do lightly. But that's all about touch upon some of the other commands. So let's focus for the rest of this course on the select committee, right? So let's look at the components off this select statement and see how it all hangs together so as I said, the first Commander is select. The next thing that we've written is an asterisk or star. What does this star mean? Well, the star represents is a way of saying everything. And in this context, it means give me all off the columns in this table, every single one of them, every single one. Don't leave anything else. So the start is kind of like, you know, when your in file explorer and you got to the search bar at the top ride and you want to search for, say, all of your documents and you anything that ends with the file extension dot C D o c. And you're typing star dot de. I see you're effectively saying Give me everything where the file name is anything at the beginning And Dr you see at the end well again s risk is a kind of away in a lot of programming languages of saying everything. Give me the whole done law. So that's in this context. It means give me all columns now, while actually use that old in the course of my work, if I'm developing and I've come across a table that is new to me and I just want to get an overview of what's inside it. And I want to see all the columns. I will just go select, start, just grab everything. Okay? As we progress, we'll learn how to work instead of going star and get everything. We can be specific and select one or more specific columns by naming them. But we'll come to that in a second. The next K part of the statement is the K word from which is we're telling it, we're selecting all the columns from, and the next bid is what we're selecting it from. Which is that table? No, in this case, person dot person. So it's almost like applying English statement. Select or select all columns from this title night. Okay, so now I mentioned before that Theis Query window, which were typing, is very, very free text. In fact, you can type in carriage returns. Andi put commands on different lines and everything still works. It's it's very, very free text. So, for example, and just watch me as I'm doing this, it's equally is acceptable to type the command like so I can put in a carriage return here and I can get all these parts of the statement on different lines if I wanted to. And I can actually put in extra spaces. And I can go crazy with this if I wanted to. On if I execute that again, the system. Just accept that you can type it with any type of formatting you like. And as long as the words are separated, the system interprets it correctly. And the whole theory just works now that can actually be used to wear advantage because it enables us to format or arrange the query in such a way as to make it very, very readable. So tell you what, I'm just gonna put everything back the web waas now to four minutes. So it looks the most readable, and I'm drawing on. My experience is encoding in this way. He is that this is what I like to do. I like to use our tab stops to make things are line up a lot. So what I'm gonna do is I'm just gonna get rid of that space there between the star on the select and we're gonna hit the tab key. And I'm just gonna put from on its own line and where we've got the table name, I'm just gonna put a tab. So as you can see, that looks a lot more readable. And that will come to our advantage when we start riding longer and longer queries and we're gonna go right down the page. He and especially when we get into more advanced carting that will you'll appreciate that when you could take very, very complex queries, and four minutes in this way will make them a lot more readable and bear in mind. If you're writing a query, someone else might come along later on and look at your query and use your query. And if it's formatted in the most readable way, don't be able to make sense of it, so that's very important. Okay, so here we talk about selecting columns, so let's start customizing their query. And instead of selecting all the columns, let's just a hand pick some of the columns we want. Let's say I'm only interested in this left most column here, Business entity I D. So I'm just gonna pick that column align, So I'm gonna get rid of my asterisk. I'm gonna type is the nurse entity i D. And as I was saying before, good old and Tele Senses helped us out here. And it's given us a drop list with that object name, you know, just for brevity. I'll just, uh, pick that guy there and great. So we've gone select instead of star. Just that one column. Let's just execute our queer and see what happens. And it's done exactly what it told us to do and it selected business entity. I did. Let's say you know what? I'm just going Teoh put that back. So I put the s respect in that so so I can see the other columns and I'm going to pick and choose a couple of other columns. Let's say I'm interested in Let's say I'm writing a report and it's gonna be a mailing label. And I want to get the addressable name of a person title, first name and last name because you know, I'm writing mailing labels or something like that. So let's just get rid of the asterisk and we'll put back in into the I D. And I'll using Tele since they and imported column and typing title and all typing and just a ah bit of, ah, tip he You can actually use the intelligence window by never getting up and down it. Using the up and down arrows in your keyboard. I mean, selected an item that you want. You can hit Tab, and it just copies it right into your little Queary window. They very convenient. So I keep typing. Noticed that as I'm typing Aled, the column names is separated by a comma. You'll see that a lot in inquiries. The column names have to be separated by a comma because the system has to know where the column names begin and end. It's the only way that it can interpret your code, because, remember, we're very free about the way we can write a code, but that within that freedom, you need some constraints of this system can interpret it correctly, and it needs to know where the column names beginning end. And we tell it that by virtue of commerce. So I put a comedy after first name in all types space had Allport last night and a spice now with that. So just to recap select business into the I d comma title comma first name, comma, last name and we will heed Execute And there we go. We've got just the columns that we want Now I'm gonna give you a bit of a teepee. This is a a very common era, one that even I make from time to time Let's say I forget what I'm doing and I put a comma on the end off the the last column that's actually in. I know you can't do that. That's an error in SQL. And if I go and he'd execute with that era in place, that cult lost calmer on the in the I get an era message. Bloody bloody body. But I won't go into what this means, that basically, you need to focus on the next line in an error message. Incorrect Syntex Near the key word from now this is very handy for troubleshooting errors, a sense of free take systems. So when the system is interpreting the card, it's basically saying or came reading, reading, reading, reading, reading and I'm coming to the wood from all Hang on. Something's not right. So when you do get an error message like this, you've got to sort of read what it's telling you when it says near the key would from you gotta find that word, Look at it and go, What have I done wrong here? What's not right away? I've got a comma on the Indian and because we don't have comes on the end after the last column, it's just literally column comma column comma rather to the last column, and nothing only end between that and the wood frog. So if you can get that right, you'll be fine. But just to run that again, execute and there is our list. Okay, so let's say that we're doing a mailing label report for crystal reports or whatever, and we want all the mailing labels to print out in order off soon. I'm alphabetical order or surname. Ah, that old last night. This column over here Now, at the moment it is printing it out in order of business Entity I Day. Now there's a technical reason for that. It's because business entity I D. Is what's called a prime que Now I'll come to that as, ah, lighter topic. But let's say that the default order of Prime K, that's not what we want. We want to order it by last night. So let's just the way we do that is that we can tailor the order in which the rows are returned. And we can do that with another one of these keywords the clauses, the order by clause. So I'm going to start a new, uh, we call them clauses or talk about clauses. In a minute, we're going to start a new line, which represents a new clause in our query, and that is the order by clause. So I'm going to start a new line order by and this part off the query tells the database, When you're gathering and returning up these roads, what's the order that we want to return to us in? And basically, I just simply type the name off the column that I want Thesis TEM to use when ordering this stuff so I will type and I want ordered by last name. It's a bit like ordering in excel, so we'll just go last name and it will hit, execute and hey, presto! Everything has ordered in last name with the guys at the top. Now you can tell the system whether it's ordered an ascending or descending order now by default, it is always in my sending the way to explicitly state that is, with this little our command at the end, they a S C, which is short for ascending now. I didn't type that because I can leave off ascending, descending pod and execute, and it'll still work by default. It's just assumes that if you don't specify ascending or descending, it goes by sending that is regarded as poor practice. Teoh leave defaults in there. It's always considered good practice to explicitly state what it is you're doing by putting in things like the you see ascending keyword. Because remember, other people might come along and look at the queries that you've saved and stored and put into crystal reports or whatever, and it's when they're looking at it. It's always good to give them as much information and at a glance that can see exactly what it is you're doing and that you are sending by virtue of that ascending K would. So let's say that we don't want it ascending. Let's say, for example, we wanted descending. How do we do? Descending de es a short for descending and if we execute, and everything is now in descending order of last time with these ends at the top in the eyes of the bottom. Well, let's put it back to us, ending for the next exercise. So replace to disk the disk keyword with ask ascending. I will execute that. And then we got. Now let's say that we want to get a little bit more elaborate with their sorting. Now notice, for example. Down here, we've got several people called Adams. They're gonna get the if in the results kind. That's the section down here which has got the label results. We have the mouse pointer over the border with that kind, we can click and drag it up with, and if we look down here, you can actually see that we have got several several several people called Adams. Let's say that the requirement is that the mailing labels report has to be sorted with within. If the time is the same, then within that set of Adams's, we want it sorted with by first time ascending. How do we do that? Now? Notice that there's actually no sorting there because Edward is coming after Francis, so we want it. So the the A's are at the top where it starts from a within that that particular set. So how did we do that? Well, we can get a bit elaborate with that order by Clause E. And we can specify more than one column. So let's just do that so we can add another column. And to do that after the first column, we are separated with the comma. You're gonna see a lot of Commons in SQL. We specify more than one object or column. It's usually separated by a comma. So let's just talk being the column name that his first name and specify whether it's ascending or descending. It is good practice by putting in a SC, and if we execute that, what's just happened? Well, if we get back down here to wear Adams's all the Adamses within that sec west, So nine is where last time is Adams. It's all sort of with the A's at the top, followed by the bees and southern and so forth. So again, you can get very, very elaborate with your order buys and is practically no limit to the number of columns that you can specify in your order by clause. Okay, Now I have mentioned earlier about clauses. A select statement is made up of clauses and we've got enough. Cody, here I can demonstrate examples off these clauses. The first clause is the select clause where we specify which columns have got to select. The next clause is the from clause. Maybe specify where was liking it from and another close down. The very bottom is the order by clause where we specify what we're ordering it by now, not all causes a mandatory. You can get away with just the two basic causes selecting from which makes sentiment. You've got to tell a query what to select him. We're from So I've just highlighted my order by and I just cut it. I'll place that back in later. But just to demonstrate, it will work perfectly well with just the two basic clauses. Now, um, if I were to wipe out the from clause, that's not gonna work. In fact, I've got all these bits of red takes indicating an era and when actually is good. I've got all these Harris happening down the bottom here in the results pain. But you know what I'll go control. Plus, is it to undo that and our pace back in my order by clause and we're good to go. We've got enough enough clauses to get us going. So let's just a recap off the SQL clauses that we've covered so far. The select clause. That's the top line, which, because his mandatory we need to tell the system what to select the front claws, the table from which we're selecting the data again mandatory and the order by clause. 5. WHERE Clause: So now we're going Teoh cover the most important? Well, one of the more important clauses in SQL that is the where clause now the where clause. It enables us to search for records anonymously. Powerful features you can imagine in the where clause the where clause applies a condition that we right and the system will only return records that match that condition name I had noticed if we come down here to the results pain and we used to scrawl body go right down to the very bottom. That column at the far right there is actually not part of the data. It's just a sort of a row counter. It's a bit like the wrong numbers in Excel. And that's if we go right up right, right down the bottom. That can show us that we've got 19,972 rows being returned. That's quite a lot. Now let's say we don't want all the Roy's returned. We only want some of them returned, and we want the database to search for the rose that we want and return them. How can we do that? Well, I'm gonna introduce another cause, and that's the way. A clause that way floors It's the problem. Most powerful clause in SQL, which is allows us to search for the data we want. So I'm gonna get stuck right into it. I'm going to start a new line after the from clause. I'm gonna type the following where I hit a Ted K. Just a lot of my my text. Where? Let's say I am interested in our only those rose where the last name is Smith. I'm only interested in the Smiths. Only the people whose last name with us a Smith in this cell. That's the only ones I want. How do I type that? Well, I'm just gonna type it, and then we'll talk about it, right? Whatever. I just talked where last night, the column. 98 balls and the value of interested in Smith surrendered by single quartz. So that is a condition. I'm just gonna highlight that. That is a condition. Ah, that is a condition that or a row must satisfy if it is to be returned in our query. So let's just run that now. And this is a bit of a trick. Um, if I was to run that notice out just highlighted a part of the Queary. That's the thing with management studios that you can highlight individual parts of something and execute them on their own. So far highlight that little bit of text on its own, which is not a complete SQL statement. It's just a tiny part of it. And if I, with that bit of tech selected ahead execute, I'm going to get a big fat there. It's each down the bottom me because I'm telling it, High database. Can you execute the command last night? Michael Smith. Well, that's not a command. That is a command. That's just one little part of it. So a little bit of a trap that you can go on highlight bits of text for the purposes of cutting and pasting and and what have you. But when you're running, I don't have any text selected. Otherwise you're gonna end up with that problem. So do you select that takes but no selected text. And now I will run my entire query. Okay, what has just happened? We have now got nothing but Smith's wonderful. And if we scroll down the very bottom, we've only got 103 rows. There are only 103 Smiths in their entire set. That's pretty cool. Now we've actually get even mawr elaborate without were clauses, and we can specify mawr than one condition. I'm using that word again. Condition. That thing about last time equals Smith. That is a condition, but we can have more than one condition. In fact, we can get quite elaborate with their wares. But as an example off expanding on a where clause let's say we're not only interested in are the Smiths, but we're only interested in the Smiths that also have also have a first name off Denise. So they've got to be a Smith and I gotta be a Denise. How did we do that? Well, I'm just gonna write the code and then we'll talk about it. So on my wear clothes line, Hubble type will add to it. And I'm helping using intelligence to help me out. He and first time equals Denyce, so I'll execute that and then we'll talk about the results. And there we go. We've got to rose in that table that satisfied both conditions. Last name it will Abel Smith and first name equals Dennis. So we've passed two conditions to the query. One is last name has gotta be Smith and first name has got to be Denyce. Now we've also combined those with the key would. And because the system is effectively, it wants to know it needs to know Well, hang on. You giving me to instructions? How do I combine them? Do they have to satisfy both conditions? Only one. What is it? So they've got to satisfy both conditions. They've got to be a Smith and I've got to be in the Nace and we tell it using heart the K would. And so again we're seeing an example of SQL is based on common ordinary language. We're telling it, Give me everything where they were. Smith and I are a Denyce. Now let's just get rid off that second condition about Denyce and put things back to where they are. Just the Smiths. Now let's say we're not only interested in the Smiths were also interested in the Adamses as well. So we want the Smiths and the Adamses combined together. How do we do that? Sorry, we want to expand. The condition were not it. Just dismiss. We're gonna get some. Adams is in there, too. How did we do that? Well, this is how we are code for that. So I'll just coat it and then we'll talk about it right? What have I just done? They where last name Equal Smith or last name equals Adams? That could be a Smith. Or they can be in Adam's. Let's run the clear and see what happens. That's exactly what's happened. We got a whole bunch of Adams's in near it. When we scroll down about Midway Dan, we can see the Smith, said Dan there as well. And if we scroll right down the bottom, we can see we've got 180 on records, more records than 101 103 we had earlier. So it's done exactly what we told it to do. Please give us everything with this Smith or Adams. In other words, it's an or type situation. I A record can satisfy either one, either one off those two conditions that can satisfy that one or that can satisfy that one . If they satisfy that or that Hey, you're in. We sort of widened the net. Now, when you discuss inquiries with with people with a laypeople or or you're in a meeting and you've got someone who's explaining to you the data that they want you to get, they can be a bit off. Is that what they are telling you? What you're describing in common language may not be 100% the way you code for it. So for exempt, your boss may say to you, Give me a list off the Smiths and the Adamses, the Smiths and the Adamses. So what he really means is he wants the Smith or the Adamses. We want a record can be a Smith Warren Adams, in other words, are the net is wider, the condition is more relaxed, and you can have either or so where is in common language. People may ask you for this end that what they really mean is, or so you go to sort of think in terms of database terms when you're interpreting people's requirements or explaining them to people. Now we can get even more elaborate with their conditions, and we can put as many conditions into the wear clothes as possible. So let's say we not only interested in the Adamses in the Smiths, but the Abercrombie's as well. So I could just walk another conditional me. So just go or last pools have the, um, Big and I will execute that. Hey, presto! We've got another through Rose being the Abercrombie's. So that could be one of those or one of those or one of those and they all get included. Great. Now I'm gonna show you. Ah, bit of, Ah, potential pitfall here with this school. And that's when you start mixing up your keyboards in where clauses. So let's say out off along this bunch of people that's missed the Adamses and the Abercrombie's out of that bunch. We only want those whose first name is Eduardo. Sorry, that's gonna narrow things down. A favorite. I think that there's not too many Eduardo's out. So let's just start a new line and I will talk in the K would and and let's see what happens. Execute that. Hang on what's happened. That's not on it. Why, that's not enough water. She's definitely not on Eduardo, What's happened? Well, this is what happens when you start mixing your ends and yours. You get those really weird results, and I could explain to you the way it works. Is that end Take precedence over the ors. And you know what? I could go on and demonstrate to you how that presidents works. It's a bit like, you know, multiplication and arithmetic operators. You know, the pluses and the minuses take precedence over multiplies, that sort of thing. It's a bit like that. Now I could go on, but basically, to be honest, it's It's so complicated. I'm not even gonna bore you with this. Leave aside that it is a really bad idea to mix up hands and Cole's. So how do we fix this? Well, the way to do it just with a sort of partition off Arians and always sort of group them. And the way we do it is with brackets. It's much like brackets when you're doing mixing up Arab medical operators, your dividers and your multiplication and additions and things like that. So we're gonna sort of partition them off in the way we do. It is with brackets, so what we really want is we want people to satisfy that joint condition of last names, and they also have to satisfy that as well. So we sort of group those conditions in tow. One condition, if you like. So the way we do it is with a bracket, so we'll start it at the beginning of the first condition. When a group put an open, I've been, um, curves Breckin and put a closed curve bracket. Let's see what happens now. Great. That's exactly what we want. We've got all the group that is a Smith or in Adam's or inhabit cramming all of them London together. And then we apply that second and condition. And there we go. We've got a couple of Eduardo's and ones that Adams and ones that Smith, which makes sense. So that is a very big role. That's a very big tip that I'm going to impart you with. And that is when you have a mixture of ends or always make sure you always partition off the ends and the oars with brackets so that everything makes sense. No one ever example. We used a series of ors to compare one field last name to three different values, and if it's an Abercrombie or in Adam's Aerosmith, it'll gets returned Well, there was actually a much simpler way to write that. And that's using another rum operator called in. I'm gonna show you how to do it. I'm gonna rot it and then we'll discuss it. Okay, what's just happened? We have written where last name in and the three text values separated by commas in brackets. So it comes down to this cable it in, and the way it works is in supplies a literal list to compare to the column. We want so of last name is in the set off these values. It gets returned. So and this list can be as long as you want. It could be 123 Any one of a number off characters and later on down the track will probably do some more advanced queries where you can actually supply a set of values using a select command within those brackets, and the in will compare to those. And that's quite a powerful method itself. Now I'm going to rejig the where clause here to select a specific record to demonstrate a particular problem when querying takes strings. So have a go. Okay. What do we got here? We've got a gentleman here who's last name is O Brien. That's O apostrophe Brian. Now what would happen if we wanted to get a list of everybody who's got the last name our Brian based on what we've learned? Sorry far. Let's see how we would do it. Well, we'll start typing. Been quite oh, quart Farai in court. What's just happened? We've got a whole bunch of areas. In fact, it's actually identifying on Eric brought there by underlying it in right now. What's just happened? There's a quote that now what are we? What have I been saying so far is when you're passing text parameters in a where clause, you delineate the text that the value of passing by making the beginning and the end a single court. But what if the value itself contains a quote? This confuses SQL because it doesn't know where the value is supposed to end, and it's supposed to begin. If what is delineating the beginning and end is actually stuck in there. In the value is you can say so. How do we handle something like that we delineated by simply putting to quiets. So when you've got two quotes, the actual technical name for that is an escape character. It's sort of ah, secret way of saying, Hey, SQL, when I say to quite are really mean one. So let's see how that happens. And hey, presto, we've got an eye. Brian returned night problem and will come to those lettering the course. You can have more than one condition within aware claws, and those conditions can be combined with an end or on or keyboard. If you've got many conditions and you've got a mixture of ends or in your were close, you have to partition those ends or wars with rounded brackets. We also learned that in addition to the comparison operators and and or there is also in a very powerful command where you can pass a set of values off variable length variable numbers of values in that sit, and lastly, we learned that if the value was seeking actually has a single quote in it, we pass it in by court, court 6. Schemas and Aliases: Now we'll talk a little bit about thes table names here. Now the table that we've been dealing with is called person dot person, and you can actually see it over here in the table listing Object Explorer. Now you may be wondering why is the table got two names? Well, I won't spend too much time on this, but basically in SQL Server, and this is generally true of most database systems. Certainly Oracle as well is that a table name actually comprises two things. It's the table name itself, which, in this case, and I'm just highlighting their the table known person and the first bit there is the schemer. Now the scammer is sort of a grouping system for tables. I won't go into too much detail about that. But suffice to say that in these types of systems when we're talking table, we really need to specify that. Take the scheme and name and the dot and the table name as well. Now there is a bit of a short hand that I can show you toe simplify this rather than have to type out that full blooming great long name, for example, and some of these could be quite lengthy. For example, Um, and if we go down here, there's one here called a person dot phone number type little bit lengthy. So there's a bit of a short hand I can show you that we don't have to talk the whole thing out. Now I will show you there is an exception to that. But the scheme of things this exists for SQL Server. I don't believe it exists for Oracle, but I'm just going to show you I'm going to go to a slightly simpler SQL Server database. It's called North Wind. It's another but much simpler sort of sample database. I'm just gonna show you these are the table names here and noticed that the scheme and I'm the first part of the table is not something meaningful. If this funny thing called DB or DB i dot employees territories dbr dot orders and in these sorts of situations the DB o in SQL Server that represents what's called the default skimmer, which in plain English means it's sort of like a catchall one and the practical implications. You don't have to specify the schema know if I was interrogating that database and to be perfectly legal for me to type, select, stop from orders, lobsters made a type of there select star from orders. I would not have to specify the schema named dot db I. I could have wanted to, so that would be legitimate. But because it's DBR and that's the default one, we can leave Thebes default of skimming off of the front, and that's perfectly legitimate. So depending on the type of system you're working with, whether it's SQL Server, Oracle or or my school or whatever you may, it is clear that out return back to adventure works here. You might see these are perfect says to the time of names that represents Game is and we do have to use them in their queries. 7. NOT Operator - its NOT bad!: so so far, we've just been working with the April's operator, where for a ride to satisfy the condition, That column must absolutely equal the value it's gonna able it exactly. Let's say we want to do it the opposite way. We want all the Reuters where the column does not equal that particular value. It's gonna be any value except the one that we're indicating. Well, that's quite easy. Because we about to do is much like in maths. We substitute equals with not equals. It's a simple Is that so? Looking over a query again that will get us everybody who's last name. He is a Smith or in Amazon Abercrombie. But the first name is anything other than Eduardo. Let's see what happens. I'll check that out. OK, got some Kim's. We got some errands. Charles is lots off the ego. So not equals. Pretty powerful. That very handy piece off value command. So I'm now going to show you another technique of getting stuff that's not equal to what we want. And I'm gonna slowly I'm just gonna simplify Queer. I'm just going to get rid of that second condition there. About the first I get rid of it all together, and we just limited to the bit about the last. Names were interested in the three lots of last night Smith, Adams or Abercrombie. Now let's say we're interested in everybody, except that Smith's the Adamses in the Abercrombie's. And I'll tell you that because I was, ah, training a group of SQL trainees. And one of the training is asked Meteo do this big career and the board because it boss had actually asked her to do it. She said, My boss come up from the other day and wondered this query. Show me how to do it now. I did it on the board and then she said, And then my boss came back five minutes later and said, Can you give me a list of everybody except the ones on those least sort of the opposites? Sar? Um, that's what I'm gonna show you. How to do is to have to get everybody except this condition. Now these two ways to do it is, ah, a long way, and there's a short way, and I'm gonna show you the short way. So we've got our conditions. He, Smith, Adams and Abercrombie and all we've got to do is to get everybody. Except that bunch of three Bunches is using this word One simple word. Not so everybody except those guys. Literally. What desk you was going to do is it's going to go to each rope and say, OK, are they not a Smith and not an Adams and not an Abercrombie? And they will be returned so it literally goes OK, Don't satisfy that condition? Yes, then I'm going to not return them. So if you can sort of mentally flip that But if we look at the results, uh, it should become apparent. So let's executed. I'll check that out. There's an Elvis is an app on bell. Is there an Adam's apple? Let's just slowly scrolled in the last names A B, C D. Add north. There's no Adams's eyes there any Abercrombie's? No, there isn't. If there was, they would be between the able and the a bulb apple. Lloris. I think that is on. May not table. Let's crawled in a bit. You will find that there's no Smith's, either scrolled and scrolled and scrolled down Ah Dio Dio slave and Sloan Smith Bates. But notes Smith's so it's done exactly. We wanted to. Not all those guys are very handy and powerful. Command is not. So. Let's summarize what we've just learned. We've learned two new key woods, the not equals operator and the not operator, not equals is a another comparison. Operators like April's. Except it's the opposite, not equals simple. And you've got the K would not, which gets us everything except the conditions following it. 8. Other Text Operators: Now I'm gonna touch on something that you may be wondering at the moment. And that's case sensitivity. Now, in my condition here, I've said Last name equals Adams in caps, and yet it has returned. Somebody whose last name was Adams in title case SQL is case insensitive. Now there are special commands to make your queries case sensitive. Um, that will very depending on the platform that you were working on. That's really outside the terms of this course. Eso Look for the time being, let's just accept that Theis SQL language is case insensitive, and Adams equals lower case title case or anything else now. So far, we've just been talking about the equals operator where we've told the database to return a row. It must. It's column must equal the value. We're passing it in in this case, Smith or Adams, and it must equal it. Absolutely. It can't be greater than or longer or like your tour or contain bits of it. It's gotta equal it absolutely 100% or doesn't get returned. Now we can get a bit more flexible with this, and we can get the ESC able to return US values that are like the value with different variations on it. And I'll explain what I mean. And that's gonna be using a thing called wild cards and the like operator. Now they're very powerful tools within the Eskimo language. And let's get stuck into bed and I'll show you. So first off, I'm going to wipe out that command. And you know what? I'm gonna wipe out the wear clothes all together. Now I'm gonna show you a trick here. I could wipe out the word toe, wipe out the where clause and return everything. I could just wipe out the word we're just got back space right there, or I can comment it out. Commenting out is a very powerful, very handy tool where if I just want to simply disabled a particular line just to remove it so I can do other testing or coding. So to just disable one particular line, all we do is go to the front of the line and type in the hyphen hyphen and look what's happened. It's gone. Grain. The line is gone. Grain, because it is disabled, it's not going to apply. And if we run us hey presto, we effectively have? No. We're close at this point and it returns everybody all 19,000 rise. Okay, well, look, let's just dump a NCAA meant that. And there's actually a handy button that does this forest in management studio. Andi. This will come in handy when she start missing around and experimenting with your inquiries to comment. Out of line. There's a button that doesn't and it's this one here. It's about the What is it? 123 4/5 1 from the right on the second tool. But And if I just have in my toolbar having my mouth server it it has the tool. T comment out selected lines. And if I press it, whatever line I'm on, hey, presto, it's got to dash is ah comment market and therefore the line is commented out and is disabled. To undo that, the button to its immediate right says a NCAA meant the selected lines press that, and all it does is just go to the far right and just delayed 32 dishes. Okay, so what we're gonna do here is I'll just get you to type exactly what I'm typing here. Let's say we are only interested in those first names that start with the letter B. So could be Brian. It could be what's another person starts would be Brian. So I don't have any people with names that start with big. I tell you what, Let's just scroll down here, Brian, Heavy guy Brandy, Britney Every guy. Bridget, Brianna, Whatever. Okay. We want allies, Guys, they're gonna start with bait. How do we achieve that? Well, I'll show you how. Let's just talk to card and see what happens. First name like single quote B percent. You'd sign close. Quite. What does that mean in English? Well, that's telling it. Get us all the roads where for this name has baby the very front and anything after it. The percentage son is a wild card again, the term wild cards. The percentage sign means any piece of text. After the bay. It's our let's just see what that gets us. Check that out we go. Bailey's We've got barbarous got Barry's been spin Germans, and if we scroll right in the very bottom, it ends with Byron because that is the last first name that has bee and anything after it. After that, it's it starts with sees. I can tell you that because I've actually looked at the data myself, so I'm sort of cheating, but yeah. So Okay, so just to go through that again first name like be centered sign, which means in English, anything starts with bay and anything after anything at all. Let's say we're looking for only the ones that start with B A. So it could be a 1st 2 characters and anything after that. Well, we changed that to be a then it will be a B. I gotta have obey. First character is gonna be a base. Second carriage is gonna be an eye and anything you like offered. We don't care what's after it. So I will execute that they regard with a Baileys Barbara's Barry's and it stops it. But because after that, we've got at the second character is not a night and doesn't satisfy the condition on we can get even more elaborate here. Let's say we're only interested in those with the 1st 3 characters a baby, I and up and anything we like after it. I think you're starting to see how this works be ir Anything like after it. Barbara's Barry's bots and the B Ys don't come into it. So again, that is a very, very powerful technique. Now we can also go even more elaborate. And let's say we're interested in anyone with for his name contains and A and in our I in that sequence, how would we talked that conditions? In other words, it could be an I N and I in any part off the first, Like so we put in percentage I, uh, again a wall card. What does that make? Well, anything at the beginning and I are anywhere in the middle Anyway, it could be the 1st 2nd 3rd character, the third of the fourth character, the 16th and the 17th character doesn't matter and anything after it. So let's just check that out to see what happens. Okay, that's exactly what I Aaron's high. It's gonna a at the front. We scrolled in. Um, what if we got? And I are at the beginning that Arianna we scrolled in right down? Hey, Carlos, there is Well, that's exactly what we wanted, because A is in the middle that Carla Carly Sorry, Carly has satisfied the condition. It was anything at the beginning. I somewhere in the middle and anything we liked after right now. So far, we've had the wild card at the end. We headed at the beginning and the end. Let's see what happens if we have the wild card at the beginning. What happens? And hey, presto, look at that. Sees our I r On the end it go. They are on the end manda air on the end. So a pretty powerful piece off searching ability is the light command and the percentage sign. Now there are more advanced techniques where you can involve involving a thing called regular expressions, for example, that you can do really fancy things where, like you can specify and is the second or the fourth or the last character and the role returned. We'll do that as, uh, that's a more advanced topic towards the end of the course, but again, that's very, very, very, very powerful on day. Now, I'm gonna take this opportunity to revisit the fancy K would not that the killer would not can also be used in other contexts in conjunction with other commands and special ways, and one of those is the light command where we can simply say, Let's say we want everybody except the ones that are like three. The condition we've spelled out here ends where they are, so we want all the ones that don't end with are all we've got to do. He has put the word not before the word like first name. Not like safety. It's on IR. Let's see what happens. Check it out. Well, I'm not seeing any ours. I'm singing er, singing me on I in. But if you look closely, you will see that there are no a ours and it's done exactly up will be told it to. So let's just recap what we've learned in this lesson. We learned that to comment out of line. We just put two hyphens at the far left of the line. We learned about the like operator to compare parts off a string to character or characters , and we learned that the like operator is used in conjunction with the percentage sign wild card 9. NULL - it's important!: Now what I'm about to tell you is one of the trickiest things trickiest concepts that exist in SQL. So if you get a handle on this, you are ahead of the game completely. So I'm gonna make this is easy for you as possible. Now, what I'm gonna do is I'm going to get rid of my were closed altogether, and we'll just get the whole set off person records, everything sort of my first name. And let's say we are interested in all off the person records that don't have a title that say your boss is sitting. I've got a list of all the people that don't have the title filled in so that I can weaken , go fix it up and put titles in where appropriate. So we want a love. The records where there is no title. How do we do that? Well, if we look down title, we noticed that some of them have valued like Mr Scrawled in here. There's another Mr and there's a couple of Mrs they're great, but there's also a whole bunch of them that have thing here called No, no. What is known may no means blank. It means Theo. Absence of a value it actually doesn't mean. In fact, let's say I should maybe thinking here, right, Well, let's get alot of them were title equals. This is where you might think that this would get you ones with that don't have a title of what you're probably thinking. Let's just type total eight balls. Port Court, nothing in there. No, it doesn't. We were expecting all the knows that that didn't work. Let's try another technique that's got word actually exactly equals no. Let's see if that works. No, it doesn't. What's happened? Well, it's because of the nature off. No. Now I'm just going toe comment out this line as we saw before. Let's get everything back, Okay? Lets to do with the concept off. No. Now I know is the Raisin. None of that work is because no is a very special value in in SQL, and it is actually the absence of a value. It's It's like a blank, but it's actually worse than a blanket sexual. There is no value. It's kind of like, Ah, black hole. Now our for all the SciFi fans out there, you may have seen episodes of TV where you know, some starship comes along in space and it comes up against a black hole and you know the captain's is scan that anomaly in front of a scan, that black hole and they find nothing. There's nothing inside it. There's nothing coming out. It is a vacuum. There is absolutely nothing inside it. Well, there is nothing inside it. There is no value that you can compare to. You cannot say we're title equals No quite know quote. You can't even say I just re enable that you can't even say we're title April's and all because we don't get anything because no is not capable off equaling anything. It cannot be greater than or like anything. So if I just disable it again, it's because it has no value. We can't compare it to anything, so it's almost like black holes you have heard with The normal laws of physics don't apply well with nodes. That's almost like the normal laws of sq old. Don't apply. Sorry. How do we do that? How do we get those notes and we're gonna get a list? How do we identify the notes? Well, this is how we do it. We're gonna re enable that lawn again and is using a special wood and SQL called Is Title is no. So let's get that ago. Hey, presto. There you go. We've got and Nell's So we're basically telling it, Get us ones where the column is in the state That is no sorry. That's how we do it is no. Now I'm gonna take this opportunity to revisit our good old friend the not word, which is pretty powerful command we saw earlier with the light commanders that not can work in with other commands, like like, and it can work in with the is command as well. We're testing for not so let's say we want a ones that are not know that actually have some sort of a value in the Let's just check it out. So title is title is no, no, and check that at we scrolled in. We can see there absolutely no nos inside, not annul among not a now, not any nose among the set. So again, another example of how SQL is based on a plain English that is makes perfect sense. They where title is not know. So let's recap. What we've learned about Nell's annul is the absence of a value because it's the absence of the value. There's nothing there to compare to, so we cannot use the equals operator on Earth. What we can do is we can compare to nose using is no what we can compare to something or the lack of another words a proper value. Find proper values by using is not know. 10. Prime Keys : Now I'm going to talk about something very, very important in SQL and in databases in general. And that's the concept off a prime que. Now every table has a prime key and I'll show you the prime key in this table and they take McQueary and I'm going to convert it back to the very basic form was a little while ago where it was just all columns and all rows. So we want all rise. So I'm going to get rid of my were cause and gonna get rid of the captain's selection off columns and just put in has to risk and execute that. And there's the in talk column. No, every table on every row on every table has a prime. Que what is a prime key? A prime key is some value that uniquely identifies that record in the table. Say that again. A column of values where that value uniquely identifies that road on that table operative will is unique now to demonstrate. Um I'm going Teoh let you in on a little secret. The prime key off this table is business entity idee. It is common practice that the left most column the first divine calling the one you first say on elect inside when you select a local practices, the prime key has put there. Now, in this case, it's called Business Entity Day. Now, if I it'll become better parent. If I start this this sub table by business into the idea instead of first name is it is here. So I'm just gonna change that to business into the idea your date. We'll just use the intelligence, they and I'll execute that. And there we go. If we scroll down, we can see that they completely unique there, Uh, the numeric six values and their, uh, ascending as we go down. And if we would scroll right down to the very bottom, we would see that they all insects, they're all unit to 41 to 40. There's no repeats. They are all unique. I'm not gonna make you scroll 10 whole 19,000 rows, but take my word for it. They are all unique, and that is absolutely fundamental. It is basically like an address or any sort of a code that you have when you're dealing with an interview like, for example, in this country Australia, we have tax file numbers where you are dealing with the Australian Tax Office to go larger tax return, You have a unique idea being a text ball number that's got to be unique to you. You can't have two people that there that have a the same tax file number. Otherwise, the tax office doesn't know who to send through. The tax refund to that could end up sending it to the wrong person, and that wouldn't be good. You miss out on your recent, so that's got to be very, very unique. Otherwise, the system doesn't know which are record to pick when you're tell it to go find that Rome. And when we start doing queries involving multiple tables, prime keys, they're gonna working with that. And it's very, very critical. Now, for example, let's say we want to find a person whose name is Brandon. I'm gonna pick this guy here, Brandon hi to Prime brother. Unusual Night. Let's say I know there's only there's a specific guy in the company that I want to find his telephone number, and I know his name is Brandon Haider Prime. If I look for do that query and I program it to look for where first name is Brandon. Last name is Haider Prime. There may be to Brandon Haider primes in the company, for that matter. That might be too Jose Lou guys, for example. But I know there's only this specific one that I want. So if I would, until I know I want one returned. And if I search for first name is Brandon, Last name is high prime. I make it two or maybe even three return, and that's not helping me. I only want the one that I'm interested in. And the way to do that is that each record has its own unique identifying. So if I go, hey, never mind the last 7 30 I know his unique code in the company is 35 so I will write a query that says, Give me wear business. Identity equals 35 I'll get the Brandon that I want. So you will always find that in every table in every database they will have one right with one of the far left that is the prime key, and the value in it is absolutely unique within that title. So let's assume we've established what the prime keys off the record that we're looking for . So let's go write a query to look for that prime. Que so I'm gonna put it created were close Michael caps for consistency and I will type in the condition where business slips is nurse into the odd day. Used intelligence, they eight balls. 35. No notice that this time I have not enclosed the value in single quotes and I'll explain why in a minute. But let's just run that. And, hey, presto, we've got our record and will only ever be one. If you specify a prime key value for comparison to the Prime key column, you're only going to ever get one record because the prime key is unique. So how can you go about finding out which off the columns? The name of the column in a particular table is the prime cake. Now what you can do is an option is to go to the front end application off the system that you were querying and go to a display screen. For that, you know, relates to the table. We are clearing and find the selectable field, and that's electoral field is most likely the prime key in this case. We've got a display which shows that the staff in an organization on the top left field there is actually says K staff key. So if we hit that drop list, we will find that enables us toe pick a list off unique keys page person amongst the staff , like so So that tells us that that staff K field is actually the key prime key off the staff table. And quite often, if you have be a mouse pointer over a field, you'll get some sort of value tool tip that will tell you the name off the field. So how can we find the prime que? Well, as we've already seen, there's the of browsing the front end application, but you can also find it by inspecting the darted dictionary for the system within which you're working. Now, the data dictionary is a blueprint or floor plan off the daughter base within you, which you were working. You may be able to obtain that from the vendors off the system that you're querying or the administrators, and within that document, you should be able to find enough about the tables and the columns to identify the prime. Que. The reason I have not enclosed that value in quotes is because that column is not a text type off column. It is a numeric type of color, so we're gonna have a bit of a discussion about column times. 11. Column Types: so I can't column types. Let's explore some column types now. Oddest, um, closed the query window. They save it. If you want to feel free to what going Save it, save it under your management studio folder. But look, I'm just gonna close it. Sorry. Column types will talk about column types will talk about the need for column types. A little exploration of column types is not going to get Super Super, Super technical You. I'm just going to show you all You really need to know about the three basic column types. There's only three you need to know, and you just need to know the few rules about those three basic column types in order to write working queries. So let's just go and explore explore things inside this database, and I'll show you the kind of column types I'm talking about just to form a mental framework. So over on the left hand side, which is the Object Explorer, I'll just expand the tree there under the database, expand tables, and I'll go down to get old person dot person, which we've been dealing with, and I'm gonna hit the plus button next to person dot person. It's a whole bunch of folders. He look, we're just interested in columns. That's dollar columns that we've been querying Sipho. Now when it lists the columns, um object exportable list the column name and there's the column knows we've been dealing with and in brackets next to it is a whole bunch of technical stuff. Now the first bit of technical stuff. Next word is the column type. For example, let's look at a T first name. That column name is involved. Char 50. I'll explain what that is. The critical bid is in Varsha in Varsha is the column type down here on the last name Thekla type again is in vodka. And if we go back up to business entity, which we were querying just a second ago, um, that a column type is int, which is actually short for inter job on. I'll show with respect to the enviro Charles, we were looking at a second ago. I'll tell you what that means, what it stands for, But again, you don't really need to know it. So I'm just showing you where to find out what the data type of that column is. And I'm gonna show you an easy sort of way to remember what group they belong to. What type of types, if you like, what family of types these things belong to. And then you'll know how to treat different columns when you were querying them. So let's so let's go. What do a bit of a discussion about the various data types within SQL Server? Because this is SQL Server, remember? But I'm also going to explore those types as they relate to the other platforms that you may be working in Oracle and my ask you are okay, so column types. Why do we have different column types? Well, one of the purposes of column times is that it can control the type and the range of data that is entered into a particular column. For example, we need some sort of control to stop, you know, letters A to Zed. ABC should not go into a number field that just simply wouldn't make sense to put someone's address into a field that's supposed to contain someone's salary. Because someone comes along, looks at that field and goes, Why have I got a salary of maybe say it doesn't make sense. So it also controls from a technical point of view over the operations that we know we can perform on that field. So let's say we had a salary field and we may want to do a calculation on that, like the average salary or the totals. And you're gonna be able to do these things because I'm gonna I'm gonna show you it's really cool average totals or Earth medical calculations on a salary column. Now that would not make sense. And it's not gonna work if you got letters ABC in a salary field. Which is why we've got these rules governing what data can go into a field on. That is what the column type does. So let's go through some of those fancy names about column types. Okay, now I've talked about column tops, and we've seen some pretty fancy names of column types. It's a little bit intimidating bought. The truth is, there is only three real types off column types or families of column tight, and there's only three when you're designing queries to suit them. So let's go through those three types of column times text, text columns We've been dealing with those so far. The names The first name, the last name. New Merrick's A J celery or rum or ah totals like a number of orders, that sort of thing. Now within numeric. Six, you've got some subtypes inter jizz. They are whole numbers. For example, order numbers orders on a particular stock item are whole numbers for the most part. Decimals, for example, a celery field. My salary for a week is $700.23. And the other family of contacts these dates. There's other fancy types, like buying everyone's. Which of these special fields that are contained out of that you actually can't queer. You can't go select on it and see, like letters or dates or numbers coming out of it because there are all these fancy ones or zeros on example. That is a picture daughter. We're not gonna be covering that. I'm just throwing it out there. In case you see when browsing column types, he may still would binary. I'm just informing you of its existence, so to speak. So we've got these three main family types text you Merrick and diets, so let's explore some of the data types that SQL Server has to offer, and we're gonna try and map those actual data types to these broad families of data types. There's three broad families that we discussed a second ago, so let's explore the text data times within SQL Server. And in addition, in addition, we're going to explore the equivalence in a couple of other systems Oracle and my ski well on. The idea behind this is that when you're exploring these columns in Object Explorer and you see the data type and you're going Teoh, know that data type from the list I'm about to show you. You'll be able Teoh mentally. Map that, so to speak to the broad family, off text numbers or dates, and you'll know how to treat it when you're writing a query. So let's look at what s cable service actual data types? Shaw. Now no one's actually sure no one's actually sure of its brunettes, char or car. I've heard it pronounced both ways, but for this course, let's just call it Char because that's how I do it. So chart is short for character and a character. What's special about the Chart data type is that it is fixed. If you've got a Char 50 and the whites pronounced is I'm just gonna go back to object. Explore here. When did sexually specified in Public Explorer, it's It's actually specified as a chart and a number in brackets after it, and that is the number of characters that are allowed in that column, for example. So it's never actually just char on its own. It's always chart 30 or something like that, and we'll see some examples in a second so sharp and we'll just go down to the next one in . Shot in charge is like char, except it enables international characters. That's the short explosion in char enables different types of non English alphabets. It allows the Cyrillic alphabet, the Japanese alphabet, the Chinese characters, that sort of thing. Vodka. Now you're probably wondering, that's probably is that not the same as chart will. It actually is. It only allows English talk characters like Shaw, and the reason it's got the wood var in front of it is because it is variable in size. Now when it gets to find an SQL server, I'll just go over here, gets to fund like that Vardy H R and the number of characters allowed in that column. Now you're probably wondering, what is the difference between that and a char 30 column chopped Well, the difference is a child. 30 column. Every Roble always take up 30 characters, even if there's nothing in it. Even if we've got a column that say a child 30 and there's only one character in a particular record you know, on A or B, he will actually take up 30 characters worth off space. Ah vah CHOCK column. Davar actually stands for the var stands for variable. So in terms of taking up space, the voucher. 30 If there's only one character in a bar chart. 30 column on the record that one character will only take up one characters with of space. If there's 25 characters, it'll take up 25 characters with of space. And if there's 30 the maximum of 30 it will take up 30 characters with off space, and you're probably seeing a bit of a patently. There was also the enV archer and in Varsha is like a voucher, but it allows international characters, not just English characters. Now, with respect, Oracle, Oracle has equivalents, data types much like those. And they are called surprise surprise chart in Java, Ciara and involved They're exactly the same. So everything I've just shown you and everything that you're going to see. You will see the same sort of thing if you've got an Oracle system, so that makes life a little bit easier. And if you've got on my SQL system that you're going to go away and start querying in the course of your work, you will find Charles and botches. So there's none of this end stuff and designed to standard. I think my skills Charles and barges, they allow for international characters, I believe. But you're only ever gonna find those two so pretty much everything that you're going to learn here in SQL Server, you will be able to utilize that if you're system is actually Oracle or my school. Now let's explore the second family off column types and that's the number times, or at least the ones in SQL Server. And again, I will also explore their equivalents in Oracle on my SQL INTs into short for Inter Gia. That makes sense because it only stores interject in other words, hole number values. There are variations on interject called Begin, to which the only difference in begins and end is that begin to stores. Numbers over. I believe it's 2.1 billion. So really big numbers, I think indigenous from about zero upto about yeah, up to just before a 2.1 billion. So yeah, small and is zero to again. It's a variation on the range off whole numbers, that Econ store and that 0 to 32 32,000. I believe it's It's very small in practice I've only ever seen in, um, I've only ever seen begins in very limited circumstances. Small inns never even used them. So there you go, quite after another and gonna see inside and begins again in terms of writing queries, it makes no difference, really, whether it's an into begin to a small and you will treat them the same. And we saw an example of that when we were writing that Cleary for the prime key. When we put in the value, we didn't quote it. We didn't put quite surround it. So that sort of roll the same set of rules applied on all three so you really don't need to know. The difference is in big, intense small. And if it's an end, you treated accordingly. Ever go to cover that as we go on? There is also our money data types, and an example of that is I'll go back here, Teoh Object Explorer. And if we browse to the product production dot product table and we go down here, we should see. Here we go list Price. This is the price of our products, and that is of type money. And you know what? I'm just gonna query, but it's running. Query Queary. Older records don't and execute, And I will just look at l this price I'll go down here and we got some values there. Now, For your information, the money is a little bit special because all that enables is four decimal places. That's all you've got other. You've got other data types that allow decimals going way, way, way off to the right there. But money ones only allow four in case you know, you want to go 0.1 of a cent or something like that. Yes, so that's the only thing that's really special about money data types is that is the range off decimals that you can put it. And then you got float, which is basically a float. The reason it's called Flowed an example of Afloat is basically it's a decimal, but it's called a float because it can store something like 19.1 for 2.637 or 100 0.12 And the reasons called float is because the decimal place can be it can be one decimal. It could be after one decimal or after five decimals or after two decimals. In other words, it can sort of float as to its position in the number. So floats are pretty cool because you can have flexibility is to the a range of decimals that you can store. That's about as technicals we have to get. I'm not expecting you to know this stuff. Basically, you all you need to know is how these air treated in terms of writing queries, whether it's an into begin small in money flowed decimals are whole numbers. We're gonna be treating them the same in terms of writing queries on the tops of operations we can perform on the now I'm just gonna point out there is another type of float column type called a decimal. I'm not even going to go into why Afloat is different to a decimal leavers say Decimal is just It's like a float that's decimals involved. But it is a numeric types, like all the other ones, and we'll be treating it accordingly. But let's move on to the equivalent in Oracle and Moscow. Well, Oracle, his dead simple. It's only got one number type, and it's called number, which makes perfect sense. My SQL has exactly the same data type names equivalents has. Does SQL Server So basically again, everything I'm showing you in SQL Server you will be able to apply to these other two platforms, if that is what you were working with. Lastly, let's look at the date types. There aren't many of those, but let's just go through them. The SQL Server data types one is called date, which makes perfect sense, and there's not another, more common one called date time. The difference between date and date time is date. Time is capable of stirring time as well as the date. I tend to find eight times more common than dates, but they're essentially treated the same. That said, there are a few tricks when dealing with date time that you've got to be aware off and we will cover those so Dayton date time, that's that's pretty much it. But again, in terms of writing queries for them, the mechanics of it on with syntax is essentially the same and will go through that. So, looking at the requirements in Oracle Argel, they're exactly the same day and date time, although my SQL only has one date related type and that is called date. 12. Text Operations: So we're gonna get into our some slightly more advanced things in SQL. So far, we've just been selecting data, but let's do some operations on data to put it together. Shopping. Change it a bit to meet our needs. So we're going to start off with exploring the type of operations. Weaken due on text columns in SQL. We're gonna look at Turk and Kept Nation, which is a big fancy word for taking two pieces of text and putting them together like a text from column A and column Bay and put them together. Sounds like a Chinese menu doesn't. Also, it will be looking at getting parts of text, like getting some letters from the beginning or the middle or the end off a piece of text and doing all sorts of crazy stuff with it. Searching a piece of text, searching within a piece of text for a letter or letters, and replacing some of the text within a text value in a column. Teoh, change it up a bit to media and Neitz. Now, in the course of this work, you may will hear the term strings strings, especially in some of the functions and commands that were going to be using. You may hear me refer to the word strings or the function names. The code referred to the word strings. What the heck of strings? Well, strings are just Bunches of text. That's all they are. The term string actually means it's a string of letters stringing a bunch of letters together to make up a word or words or a sentence. That's all it made. So let's get into operations on text. Now, if you close down the management studio and gone away and come back, we'll take you through reopening it again as a reminder. So I'm gonna fire up a management studio off my task bar. We can fire it off the start menu, and we just click connect at this little connection log on dialogue box. Let's be day now in the left hand Object Explorer Pain will just expand databases and just expand adventure works there and expand the tables. And we'll just start scroll down Teoh person dot person, which we've been working with and will apply what we've learned about column times. So we're going to expand the table on the columns and we'll look at some of these some of these data types. What if we got here? Well, business entity. It's got the word intend there that tells us it isn't and it is a numeric. So that's a numeric, um, person type again, that's inch are We learned that there was a text time name style that's a bit title that's an inch are that's, Ah, text em middle name, Last name, all of them text. So they're the ones of the kind of ones we're gonna be working with in these operations experimenting with operations on text data types. So I'll open up a new career window. It was set up with the basics We're going to select select all of them to start off with, and we'll make a decision making caps for consistency and we'll go select. Stop start means pull the columns from some person. Now I'll just put on Alias on the and they will end about aliases in an earlier lesson, which is just a short hand name for the table. And it will make life easier for us later on when we do some more advanced commands involving the table. But let's just continue on will we won't worry better wear clause for the moment. But we will put an order by clause, even though a new order by clause is not mandatory. It would have worked with just the select in the From Clause. But it's considered good practice to put in an order by clause. So will select Thebe Prime Key, which was business entity I. D. And we'll specify whether it's ascending or descending by. Putting ask. Now is a bit of a tip here for you if we go over to the object Explorer Weaken, tell which of the fields is theme. The key. I know, I said, It's pretty much always the 1st 1 mentioned in the list. But there's another way as well is that the prime key has a little icon off a key, right? Next. What? So that always tells us it's a k just a bit of a tip day for you, but with that query, they will execute it. Okay, there's their data. So let's say we want to do a mailing label report in crystal reports and we want to put together we want to print out right next to each other. The first name and the last name has one column. And if you have ever done reporting report riding, if you've got the first name and last time in its own column, that's actually gonna show up in crystal reports or any other report writer has one unit of text that you can put together as one piece together and not have two of them the first time in the last day here. We're going to do that. Well, tell you what, Let's portrait at the very front of our columns here. That's perfectly legitimate. It is perfectly legitimate toe. Have our columns appear in their query more than once, So let's just purely for right for ease of finding it. Let's put the first name as the first calm on the far left there and all the other columns on the left just leaving in ages so we can check them out. So what First name? Hey, presto, I Until a sense is filled in for us. And you know what? Let's put last night as well. I won't use the intelligence. I'll just, uh, they don't like it is caps lock and well executed, and they would go first name and last name and all of them by appear more than once in the set. That is perfectly legitimate. As we get more events, there are certain very unique circumstances were not allowed to have columns out more than once in a result set in the query results. But we'll come to those much, much later in the course. For now, it's okay to have the same column more than once. Okay, so what we want to do is we want to combined first and last name into one big, long run together. Notes. So we want Ken Sanchez to appear as Ken Sanchez in one column, and Terry Duffy is one. And you know what? I'm gonna jump straight in and show you how to do it so and this is a little bit funny. We're going to go first name, plus, last night. Now that may look a bit funny. You're probably thinking, Well, hang on the text columns. How can you add a column to one column to another one piece of text to another? How is that even possible? I mean, they're not numbers. Well, take my word for it, and we'll just look at the result Okay, what's just happened? Instead of having those two columns there, we've now got one column. It doesn't have a name, and we've got a lot. Those words run together. There's another. There's one problem, though. They actually run together without a space in between. Them will fix that later. But let's just talk about what we've got. What we've got is what's called an expression column. No, what an expression column is. It's not a riel color. It is. It doesn't physically exist in the database. We haven't physically created another column. There isn't a another column. They would still get the same number of physical columns that we had before, but it is basically just an extra sort of. It's almost like a mathematical product. It's one that we've made up, and we can make up any sort of expression column using the various operations and commands available to us in SQL. But if we were to take this query and plug it into crystal reports, that column would be available to us, and we could drop in Dragon onto a report just like any of the other ones right there. If we do one last thing and that is to give it a name. At the moment it's got nor column NYT. If we were to take this, plug it into crystal reports or some report writers them actually throwing error and not working with it. So it's important on always good practice that expression column names have a name. So you know what? Let's just call it full name, and Tony had actually called something. It's using the word as, and I'm gonna cool that. Nine. There's generally a bunch of rules about the name that you can given expression column. Generally, they have to start with a letter. They can't contain certain characters such, for example, mathematical operators like back slash yl plus or something like that. Fathers to execute that we've got an error so anything involving, you know, special commands. There was some special commands, and SQL if I was, for example, to call that, um, order, I think that wouldn't work, actually, or rock table. I think that actually wouldn't work. No, because table is a special wood and SQL. So if there's a special command and SQL or a mathematical operate, you can't use that in a column name because it confuses SQL that things will hang on. He's telling me, plus divided by. But does he actually want me to use? That is Nariman Arithmetic will operate or as an actual column name, so it's confusing. SQL. So, basically, if you come up with an expression column and it's surrounded with its underlined and right , you know you've done something wrong. Apart from using special SQL commands, the only other real requirement is that is that it stopped with a letter. So let's just call it full neck. It is possible to have numbers on the end, but the first character in the expression no, must be a letter. So let's just set Call it full name. I'll just run that. And I price that we've got our expression column and there really is full night. So what we operated? Just a recap is an expression column. It's a column that we make up we created on the fly, using any one of a number of SQL commands to create, shape and chop and change the daughter within A. It only exists for the duration off the weary. We're not creating a new physical column. It's only for the direction of the query. And after we finished with the query, it doesn't exist anymore. The name off the expression column can't contain any SQL commands, such as arithmetic operators, and it has to start with a letter. They have probably noticed. There's a little bit of an issue with their expression column there in that with these names, there was actually no space between the first name on the last night, so let's insert one in there. Let's modify our expression toe. Have a space between the first and last night. Now this is how we're going to do it. Quote space quote and a plus. So what is their expression? Read now. First name. Plus Can Captain Nation plus Maine's can cap knighting when we're dealing with the text columns and a space surrounded by single points, we've seen the use of single quotes in there were causes. When we're comparing values to text fields, this is the same thing. Only this time it's what we call a constant eso it. Basically it's not like a column the whole expression is made up of can be made up of columns. That's what saying here, the first time in the last night, but it can also be made up of Constance can be a spice. Andi can have letters in there as well, so we might mess around with that in a minute. But for the time being, its first name plus space, plus the last name on the name of the Colonies for nine. Let's see how that looks great. That's looking a little bit better. Okay, now I can sit. We can put some, uh, text in here, for example. The requirement might be that we've gotta have a hyphen between the first and last night. Not quite sure why, but it may happen so we can put a hyphen in there and the hyphens day. It's a text constantly, completely anything we want in there. Okay, so let's just leave. That is space for the time. Bang. Great looking. Pretty good. No, let's say the requirement is toe have not the full first name, but just the first letter. The initial off the first name. So it reads. In the case of Safe Ken centuries down here, it's going to read K. Sanchez. How do we do that? Well, that's when we start getting into functions so What is a function? Well, a function in the SQL language is a lot like a function in Excel. For example, if you've ever used those, it is a command that is actually stored inside a database. That command accepts inputs more, possibly one or more than one. It performs operations on those inputs all sorts of chops and changes them or adds them, subtracts them whatever, and it returns a result. It only returns one result or and this is a bit advanced a table. It can return a table results sit, but that's that's an advanced topic. We'll get to that in a more advanced course. The SQL language comes with a whole bunch of built in functions, which very, very useful, but it actually is possible to mic up Byron. Custom functions surprise rise, and it is stored in the database. That is a more advanced topic, which we may get to later on. In a related course, if you're still with me and I hope you are, so let's try are using a function to get us what we want, which is the very first character off the first night going over the king. We want the capital K in Ken instead of first night. So we're gonna use a function to do that. And this is how we gonna do it. So just write it in the mall, talk about it in a second. Okay? What did I just write? Left First name comma, one in brackets. And you know what? I'll just execute it and then talk a bit about what I've done. Check that out. There's their first initial K Sanchez T. Duffy are timber roller Fantastic. That's looking pretty good. Okay, so I'm gonna break down exactly what it is that I have written here in writing my function . Well, first off, I wrote the word left. That is the name off the function. It's one of the built in functions in SQL. And just to give you a bit of background, what it does is it takes a bit of text, and it returns. Thea a certain number of characters starting from the left hand side, off the piece of text. He can be the first character or the 1st 2 characters or the 1st 3 characters or what have you But how did I write it? Well, I wrote started off writing the name of the function left and then a bracket. And that's what functions dough. They that they basically form the Follow the form and I'm gonna write it down here. Okay, so the basic for me is the function. Name a bracket. Some inputs that may or may not be optional. There were some functions that don't accept any imports. They just spit, spit out a result. So inputs and then possibly some more inputs. And then that's what That's three dots they're meant to represent possibly mawr inputs. You can have 234 Some functions can accept many imports, but they always in with a close Brecher, So its function name open bracket one or more imports and a close bracket. But largest get rid of that, and we'll just talk about the function. We just right. So it's left, and the first parameter is three bit of text that we want to analyze. In this case, it is the column and the next bit of perimeter and the perimeters airy, separated by coma. On the second perimeter is the number off characters in the left off the piece of text that we want to extract. And of course, we close it with a bracket. So that's it. So looking at the result and there's actually a very, very quick way to find what the parameters are without having to look them up on Google or anywhere else. So I'll talk about that in a second. But let's look at the result now you may have noticed and this is the important part is that that function is different for every Roy some again, kind of like you put functions in excel. Uh, that is the result is contextual depending on the road that you're on. So it executes that individually for each row. So you get the idea now I'm just gonna rot that again. Gonna want that. I'm gonna write it again. I'm gonna show you a cool way to work out what? The parameters up. Now we've got toe have our intel A since switched on. That's this little, a little bit of a reminder there that this little button that looks like sort of an upright fridge refrigerator. So with that switch on will start writing a function. Okay, left open bracket. Now, because of said open bracket until a sense knows, always writing and functional. Give him ahead, and it will actually spell out a kind of an instruction manual for the parameters on the output. We actually call that metadata sometimes called the function definition. So it's telling us that the first parameter is an expression of some sort. And when it says expression, it actually really means any piece of text that evaluates to any object that evaluates toe a piece of text. And it actually tells you the data type never come back to her two data types and column topsy And we saw before that involve char is a text data type. So basically that can, except any pace of text, it actually doesn't matter up. It actually doesn't matter that the column that we're taking that from our first name that we're about to put in there, um, it Actually it doesn't actually have to be an in vodka. It actually doesn't have to actually be any sort off text data type. Basically, if it's a text data type, it'll work because that's cable service. Smart enough to sort of do a bit of on the fly conversion in the background, so take it as read that if the data type off the perimeter, he is one of these many text data types that we saw in that PowerPoint slide earlier. A zone is what we passing his takes. It'll work. Okay, so it's asking for an expression. That expression can be constant text or it can be a column. And then the definition goes on. It's a comma and number of characters, and the data talk for that second pregnant parameter is an eat or interject and actually tells us what the output is gonna Bay returns in vodka. So let's just give that a go. So we're gonna put an expression. This goes first. No, I'm and comma and we want the one we saw before. So and there we go. We could have some fun with this weekend, Mike, that the 1st 2 characters and as you 1st 2 characters. Now I'll talk about some other really common cool. Put that back on like that one. Now let's say the requirement is that to be a proper title or mailing title of some sort in addressing title, it's expected to go first, initial and a period or a dot or a full stop where? If you want to call it followed by the surname. So how do we change that? Well, we've got a first initial. We've got the last night and we got a space. So how do we insert that dot? Well, I bet you can guess how. Just put a period in there. So let's just leave their query for the time being. Will save it first will go file, Save as. And I'll get you to say that in the folder of your choosing. And we call that my person Query. I've already got one. They great Emel close that ready for the next query which will doing exercise involving getting sections of a string and replacing parts of a strength. So we'll start a new Cleary window and we'll start by. What we're gonna do is we're going to select all the columns from the table production dot product. No, I can tell by browsing. Thedc is columns. That product I do with that little key symbol next to it is the prime key off the table. So I've ordered three results it by that key. Let's just have a look. So I've got a name column he Now, if we scroll down the list of products, we can see that there's some of them have the second would as not in the product night. Now let's say there's a requirement that we need to find a way the products that follow that particular text pattern. I not just that the second word in the name is not, but that not is after a three character word. In other words, not is the fourth character in that strength. So we're looking where the characters at Positions four through two and three after it followed that pattern off nuts. So let's say How can we do that? We'll do it with us with a function called sub string, so we'll do it and then discuss it. Okay, what's just happened? There's a little expression column, and if we get into the nuts they regard, we've extracted just the would not just that very part of it. So what's just happened with this sub string? He is sub string takes a certain slice off a piece of text. It accepts these parameters theme, the expression or the column that we want to apply it to the starting position at which we want to start getting a piece of text. In this case, it's number five, so it starts a position five. Now the thing about sub string is it's one of these functions were when it's counting characters or counting something in a Siri's. It starts at one, so the first character is treated as one as one. So hexes. One site hatches one e is one right hatches. One is to X's three. The space is four, and the first character we want to extract nut is five. The third and last parameter is the number off a characters in that string starting from the start, one that we identify here that we want to extract. So that's three. So that's how we got our air word. Not so. If you want to identify just those, we just take that expression and we'll put it into where we're close. Bearing in mind has upset before. That s cable servers by default case insensitive so that would capitals not equals title case. Not eso. Yes, that will get us what we want. And there we go. There's about 23 of them. So let's say the requirement is that now that we've identified, the records were interested in the ones with not in that particular spot. We want to fix the name we want to replace the would. Not with something else. Let's state bolt because let's say there was a mistake and they're not supposed to be not disposed to be bolt. So how do we do that? Well, first off, you know, let's get in, not name and just get it out of the select list because that served its purpose. Its in the where clause there. So we want to take name and we want to replace part of it, and that is using a function called, strangely enough, replace. So that's type it and then we'll discuss it. So what are we talk to you? We've got replace. We're putting into it. The name column we're going to, and what it does is the second parameter. This is the piece of text that that is going to be searched for, and that is what's going to replace it. So the metadata of the function is what we're going to search, what we're going to search for and what we're going to replace that with. So Let's just look at the results. Okay? Where it says hex, not five. NASA's hex bolt, five volts, seven someone and so forth are fairly powerful function. I'm sure you look great. So let's recap what we've learned. We talked about expression columns, which might up columns that we can create using SQL Code. We talked about in Katyn, aiding pieces of strings by using the plus operator. And we can keep Captain eight both existing physical columns and constant values that we make up like the court dot court. We talked about the use of functions and SQL I would so some examples of functions such as lift, sub string and replace, and we're gonna cover more and more powerful text functions later in the course. 13. Working With Nulls: Now let's say the requirement is to build this full addressing full name is that we want to proceeded with the title. There's the title day. Now what? I'm gonna show you how to do that. And in doing so, I'm going to introduce you to the concept off Knowles and operations involving Knowles and why they generally don't work and you've got to code around it. And this is an important lesson that we've really got to take notice off because you could be the best SQL developer in the world. And if you don't take account of Nell's, you can end up having major problems. Okay, so the job is we've got to take title and working onto the front day under the front or their first. Initially, I will just put title. I just select title in front that so we can see that a separate column to the left of full name just walking demonstrate what's going on here. So I'm gonna go so cut again. It is perfectly legitimate to have the same column listed twice in a query. There's some advanced exceptions to that, Will you actually can't. But again, that's a very advanced topic will come to that much lighter on so with Totally Okay, So the idea is we want to take that title and build it into my full name expression here. So I am going to put title in front offices. My expression gonna build on my expression Title spice plus her space literal piece of constant text. So it's a space and in that with a single quote and can catch tonight that onto the front, off the rest of their expression S o It's going to end up paying Mr Spice be Sanchez, someone and so forth. Now high close attention to these guys here. Case inches. D. Duffy and G. Erickson just focus on these guys here. These guys all have a null. In other words, no title. There is an absence of a value. That's that black hole was talking about where his g Erickson and Goldberg below him. They actually have a title. So let's just look at the results for these guys when I execute now. The 1st 4 actually had a full name, and now they don't and we had text there, and it suddenly turned to know. But Ericsson and Goldberg, I didn't turn to know it's actually work. There's their title. But for the other one, not only did it not work it actually what it appears to have wiped out at work all together , what happened to the value that was there? Well, that comes down to the rules, Governing knows. So let's revisit the discussion about knows. No, when you see it in the database is not a value. No is the absence of a value. It's like a little black hole in space. There's nothing in there. No, it's basically a vacuum. There's nothing in there, there's no light. There's nothing. And because there's nothing, there is no value to compare it with. And because of that, when I was studying databases university, Electra matters, stand up and recite the following, and I'm going to do the same for you know, equals nothing and nothing equals no. So I'm gonna repeat that no equals nothing, and nothing equals no, no is not capable off equaling anything, because that implies that not has some sort of a value that you can compare it to something . But it doesn't. It's it has no value. It's it's, There's nothing in there it can't be compared to anything and you can't use it in any sort of equals operations. So I'll talk a little bit about searching phenols because if no labels nothing, well, how do we select for it? How do we detect it in their daughter? Will is a bit of a trick to that. And when I've done that will come back and visit what's happened with that little operation where we selected a no. Now we saw it in an earlier example. What happens when we try to search for adults by simply using an equals operator like we doing well, because in most were clauses so bothers to type where title equals no nothing. Why not? Because same thing again, no. Doesn't equal anything. No, equals nothing. And nothing is capable of Equalling. No, What we can to is we've got the special K word is no. Hey, presto, Yes, they're all the ones that are no see knows knows now the roads that actually have a value entitled they're not being returned because they're not know they actually have a value. So give middle, get rid of my way clause. Come back. Teoh All the records so by putting a null value for these guys, like in candidate, It was something The result ended up being no. So what happens when we actually use a no value that happens to be on a record in a row? We have to use that in an operation. What happens? Well, I'll tell you what happens. You take no, and you can cabinet it with a piece of text, Any text? The result? He's no. That piece of text could be a 1,000,000 characters Long doesn't matter. You put a known next to what the result is no, literally. It's s cable service way of saying, Hey, this is a black hole. It's almost like the text get sucked into the black hole and the result is just one big black hole. It says Cable service way of saying, Hey, I do not know I had a handle. I know there's no value if I take something that's not a value when I applied to another value, I have no idea what the hell to do with it. So the result is enough. If you would take a bit of text and can cabinet with a null on the right hand side of the result is still no, you're that bit of text and can capture that The visit takes and I know and more techs and there's any nose in there. You end up with one big fat no, in the end. So when you're doing these kinds of operations, you have to allow for knows, you have to take account of nose in such a way that is not going to screw up your value. And if ever you get to the level of writing SQL for to supply data to applications, you've got to be very careful because failures to take account of nose and returning and nobody when you don't mean to. That could result in your application being a Web application or a dot net destroy application. Whatever it could result in that application crashing at run time, and you have lots of really annoying users. So we're gonna have a bit of a primate here and had a handle nos. Now the top line there's looking a bit long, so you know I'm gonna put it on its second line. They hit Tab Day to shift driver in line with the rest of card. Now we're gonna use another function, and it is called Is No. It's actually colored purple because it is kind of a special system function and, as with all functions, that I begin with the name followed by an open bracket. Now, when Tele senses very kindly given us the metadata for the function sizes, looking it up on Google and what is the metadata? Well, it accepts two parameters 12 and then the close brackets separated by comma. It accepts an expression, some sort off a value, some sort of customarily some sort of field in the end, the record set and then a replacement value. In other words, what the function is doing is if amongst that function, it encounters and know it will substituted in substitute in some proper value, that is not know. So we're basically stripping out all the notes. If it does not encounter and no, then it will just leave that expression where it is. So what we're doing is we're gonna put in air expression that is polluted with dolls title . I gotta put in the replacement that will be used if and only if there was a note in that record. So we'll put in, um, let's say like just to tell us that there's a blank. The and all expressions should in good practice, have a name for the expression column. So we'll call that fix title and let's see what the result is, right? What's just happened? Now there's a no title day, and we've substituted in a blank. There's a null title day. We've substituted in a blank. There's an auto them that there is not another day. It's left the original proper good value there. Great so that he's basically all that monster knows that's gonna be a footstep to fixing a little expression color. Now a far better way. And I'm gonna show you a trick a far better way than the Wood Blank is. Instead, let's just put in a blank string. I'll show you how to do that. This is an interesting concept, a blank string. So in that string, but of text, I'm going to take out the wood blank and just leave those two quarts by themselves. Looks a bit funny. I'll explain and show you the result. Okay, what do we have here? What we've got is is kind of a bit of white space it shown is white space. But it's not know, and, of course, the values that actually had a proper value. Entitled Les, it's left untouched, but the ones that were no, it's got an indeed strength. So what is an empty string? While an empty string is an actual string of text, it's not the same thing as a no and inescapable. We represented by single court, single court and nothing in between. It's like any other string. It's just got zero length, and because it's not annulled, it's an actual red blooded object. It can be used in any other operation like any other strengths, such as contaminating or passing into a function that requires strings. That sort of thing never have proved a point about the white Knoll is not works. Let's just remove that has its own column on that sign and, uh, well, just inserted into a full nine expression here, instead of titles instead of title, which is polluted with Nell's will put in this expression, which substitute Nell's with a nice string and say with the result is so just get rid of it in a tern colony and see what the result is. What's happened to a full night. Okay, while off where is before? The ones that had no entitle full name that was destroyed with a big fat No, it's now God what we want. There's none of this wiping out of everything. Basically, where there was a title, it's just put in an empty strength. So there's an empty string just before the K there. You just can't say it because, well, it's empty. So if you can get your head around that you're doing well. So let's recap. What we've learned about knows knows cannot be compared to values and in the same way that cannot be combined with values, either, because if they are, the result is always another. No. This, however, can be avoided with the use off. The is no function 14. Working with Numbers, and Assignment 1: So now we're gonna come to working with numbers. So far, we've been working with text columns. Never gonna work with number columns and seethe sort of operations that we conduce with number columns such as addition, subtraction, multiplication will look at the module. Its function will. We won't do division in this lesson because that requires that we examine something to do with data time. So we'll cover that in a later Listen now, if we haven't done so already, let's save our query far with their a nice, big, long full name will save the work we've done so far because we're going Teoh, close this. Start a new query and come back to this one later on. So with that query window, there will just go file save as no. I've already saved this myself, but I'll get you to save this as under documents SQL Management Studio or wherever you choose to, but will say that is my person query. No, I've already got my file there, so I'll just override the existing version. Save that and we'll just close the query window ready for the next. The next lesson we're going to scroll down, get you scrolled in in the object Explorer on the left. If you haven't got the tables folder already expanded, please do. So I'm going to scroll down to sales order detail. Now, we're gonna do some operations involving numbers and that particular table. So let's just start a new query file. And you know what? Let's just save it before we go any further, put it on the documents, this commission studio wherever you like to call it my number. We're right. Okay, so we're interested in things table. So let's just do a basic query to select all the columns. Select Star as we go, uh, go to specify the from the table were coming from and that is sales start sales order, detail, sales. Where sales. The first beat is that this came up and you know what it's tell a sense is helping us out of here, So let's just select it now. It's always good to put a short name what's called an alias to the table. So let's just call it s O day sales order detail. Okay, so let's just select it and see what's in there. Okay, It's a pretty lengthy table and What have we got here? We've got to sales order, right day that I bet you any money like that is the prime key. Another call them there. Another column there. We've got an order. Quantity, a few I d. Things. We've got a price. We've got a discount and we've got a line total. So we're gonna do some funky things involving some of these columns. Now, if we're interested in the actual types of some of these numeric columns, we could always use the object Explorer to go down and expand the table. Expand columns and hey, presto. We've got the actual data types off the columns listed there. If we know how to look for them, let's look at some of these numbers Order quantity that is a small into We saw that earlier . It is a variation on the inter jadeite atop its numerical. What else? Unit price. That is money. That's another numeric type. It's got actual decimals involved. As we can see. They the line total. That's actually a numeric That's another numeric data type in SQL server. Now, if you don't have access to the darted dictionary for the system for which you were designing queries. The river of there are a couple of tricks you can do to work out if a particular field is a numeric field or a text field and then card accordingly. Now, in this example, this is, ah, student management system. There were two fields, their registration and class off. That's if we can find out which of them on the American which of them are text? We can do that simply by just typing in them. Most systems will. If it is a numeric field, it will not allow letters to be entered into it. So let's see if we can type letters, end or numbers into registration. Let's see what happens. I'm typing 123 semis. So obviously that is a text type field because it accepts both numbers and letters. So it's takes because it allows letters. Now, if I go to class off and I start typing in, I'm typing in letters. They nothing's happening at Wendell that letters, but it will allow numbers. So obviously that is a numeric type field. Okay, so we're going to do a simple operation involving a numeric field, and we're gonna do it involving the order quantity. Our column right here. So, as I've said, it's perfectly legitimate to repeat columns in the query. So I'm just gonna put order quantity at the front there and execute. There it is. Now let's say, for example, we're going to give everybody one free item in the equality. So we're just going to increase their order quantity by one because we're going to give him a free T. So how do we increase that by one? Well, I mean, as in all records, altogether in that call. Now, this is not gonna do it. So we're going to. It's a simple is thes, so just do it and we'll talk about it. Now notice that that starts off being an actual physical column that is a representation off the physical column that is border quantity. But after I run this expression, I can't What's just happened? That column has gone from a proper physical column to an expression called and what's exactly happened? Well, it's done just that. It's taken that value of order quantity, and it's increased it by one. That's two that's gone from 3 to 4 that's got from 1 to 2. You can see what's happening. Let's just give it a name. Cool it increased quantity. It was a good idea because if you have sometimes under certain conditions, with some advanced queries, if you leave a column that does not have a proper name, it will cause errors. That's a more advanced topic. Will come to that in a much lighter class. Eso, just like that is red that we need names for a columns, air expression columns. So let's have a look at the actual expression itself. Wages simply waned and took in the American column, and we went plus and we added a constant literal value. One and it Dutch. Is that so? Literally The operator Plus had a different outcome, depending on the context in which we invoked it. So plus, is one of those kind of special operators in the case off text columns. The plus operator can caffeinated a piece of text onto that column or other expression value. Okay, so let's do something else. Let's use a different operator. Let's go minus. Oh, look at that. It's gone from 1 to 0 from 3 to 3 to two. It's not exactly what we told it to So, as you can probably see with numeric columns, you can perform any kind of arithmetic operation on it. Let's go Times Times is actually as to risk, which is actually like Excel. So let's go to right. What's happened. One times two is 23 times to a six. You see the idea. So let's also cover the modular operator. The module ISS function in maths is, if you didn't know, is basically the remainder left over after you divide by a certain amount. So let's say if we were interested in identifying a with the order quantities that are non even and a simple way to do that is to go modules to. And if any of them have, Ah, a remainder After dividing by two, then we know that there are untold number, so I'll start a new line. Oh, good Q T. Why on the marginalise operator is represented by the scented son and I'll call that column or, uh, more Jew plus. So let's just, uh, execute that and see how we go. There we go. You know what? I'm just gonna put order quantity at the very lifting just so we can see the import and the effect on our okay. So if the order quantity is one and we divide by two, the leftover is one. If it's three left arm is one. If it's to that, of course, there is no left over. So that's basically how a modulates works. So we've covered the fundamentals about working with number, columns and number values. In the next lesson, we're going to extend that to working with numbers and the division operator. But in order to do that effectively, we need to cover a discussion about data conversion so the next lesson will focus on data conversion, but as an example will lead into division operations. Now, before we get into the next lesson about data, times and division will get us to You know what? We'll just get rid of the little line there about border modulates and the second order quantity, and we'll just put it back to back to the way it was before this last exercise and will save that as my number query and we'll put on under rescuing management studio. Save that if you've already saved it, just override it, and we will use that file in the next lesson. Well, now it's assignment time. Don't worry, I'm not gonna make it too hard. But if you could download the file assignment one dot zip and inside it, you're gonna find the following files. So I'll get you to attempt the business report mints in assignment one dot pdf and I've given you a suggested solution. It's not necessarily the only solution. You can come up with your own if it gets the same results. But up offered a suggested solution in the file assignment one suggested solution dot SQL and the results of my solution or an assignment one results dot XLs X. 15. Data Type Conversion: Now we're going to take this opportunity to revisit the American data Times on. We're going have to go a little bit further into depth with different types of numerical data types because I will need to be aware of their behaviors when we perform operations on them. The differences in data types will affect the outcome the result that we get. So we're gonna be aware of that in order to get an accurate result. So let's look up the whole number date of times most commonly is int in the interview. Big end same isn't it's just got a wider range small in, and there's even a tiny and in practice of only ever seen or worked with the EMTs and begins mostly ends. But if we work work towards the number times that have decimals, money, as you can probably imagine, is used to contain currencies. It's accurate only upto a certain number of decimals, and it has a core feature in that it can be formatted. The output of our money column can be formatted with a particular currency in that format, so that's pretty handy. Decimals and you, Merrick's decimal and the America actually effectively the same data time, but I've only ever worked with decibels, but we need to be aware of numeric send. That'll become clear in as we go through the course and floats. A different type of decimal will explain the difference between these three in the next slide. So let's work through this table off the decimal data times right up the top. There, we've got toe money that is accurate to four decimals, and there's an example. And if this was meant to represent Australian dollars or U. S. Dollars, that would be accurate to 100 off a scent. And when you're browsing Thea Object Explorer, you will see the column defined simply as money decimals get a little bit more elaborate. The definition off a decimal. If you're looking at an object explorer, it is the word decimal and then two numbers in brackets afterwards. The 1st 1 is the total digits that are stored in the decimal, So this is variable. The point I'm getting at is that decimal columns ah, nor decimal column is exactly the same as the other liken will be different. They could have a different number off these two factors here the 1st 1 is the total digits stored in the entire number, and the second digit there, in the definition, is the digits to the right of the decimal. So let's say, for example, we wanted to define the decimal column that we have to store. So up to 100 you know, three decimal, three numbers there at the front and six decimals up to six decimal places. We would define that as decimal 96 the Americans that works exactly the same as decimals. They will appear in the course of their carding, and that'll become clear later on. Floats are a little bit different. The definition is you really define one number, and that is the total number off digits stored to the right off the decimal. So if we were to what if we wanted to represent this number here, we would have to define it as float five in terms of the numbers, the number of digits to the left that's actually determined by the total size of the float , which is eight bytes. So basically you can have his number as many to the left of the decimal point. Aziz will be stored up Teoh. Eight bites. So that's quite a lot. They're getting a little bit technical. But if ever you say the word flirt and a number next to it in Object Explorer, you know what that represents. So let's return to our little query involving order, quantity and the sales order detail table where we took order. Quantity were multiplied by two and got an expression for increased quantity. Now I've been saying all all the while that every physical column in database such as Order Quantity has a date a time, and we go into Object Explorer. We've seen the order. Quantity is defined as a small into its one of the whole number data times, and we've seen that in the column. It's all 123 their whole numbers. There's no decimals now that's a physical native column in the database. What about a made up expression collar? What data time is that? Well, there's a clever way to find out. It's with a special function called SQL Variant property, and I'm gonna show you how to use it. So let's just put it on a new line here before a type it. I'm just going to go to the Microsoft Documentation webpage where SQL variant property is to find, and basically it accepts. That's its definition. There it accepts that's its name SQL Variant property. It accepts two parameters. One is an expression, and another is the property about that expression that we want to find out. Now there's a whole bunch of the world ab There's a select number off them. One is the base type, and that's what we're going to use. We're going, Teoh put in based type is their property and see what is returned. Animal return. It will return one of these many data types within SQL Server. So let's just find out which one. No gonna copy and paste this same expression here into the first parameter. And they're gonna find out the base type property, cool it our life and just say what gets returned. Check it out. The type off at little expression is int. That's still a little bit more investigation. He So they actually see the, uh, elements that make up this increased quantity expression. I'm just import order quantity in its own column. And you know what I'm gonna put to the other element to this'll expression that's order quantity. In times that I'm just I'm just gonna put two as a constant in its own call as the factor. Let's just call it the factor. So order, quantity, times the factor resulted in that expression column. Increased quantity. Now we know that the order quantity is ah and into job by looking at Object Explorer. The factor. What data talk was that? Let's find out, I'd be curious to find out. Well, we can find that at using our SQL variant property function in the same way. So we'll do that. And the expression on trying to find out of it is to, and I'll call that column is to type for one of the better way. So what does that actually tell us? Okay, the two type is an int. So what we're finding is order quantity, which is in into the two factor, which is an end that times that resulted in that and that data type was in it. Someone end multiply bond and resulted in an it Well, let's get let's delve a little bit deeper into he. And instead of using inst, let's work with one of the decimal types of data types on we'll work with. If we look in a table, there's a couple of money columns. There's unit price and there's unit price discount Both monies and they are decimal types off data types. So let's ah, investigate Does. So I'm gonna put those two at the front of the query. There we go. Now let's multiply the two of those and then we'll investigate all the data Times involved . Unit price times the discount as call it discounted now because there's a lot of zero unit prices. The result is 00 times anything. Zero OK, so what are the data types involved? Well, we know that you know, prices, money. We know that you crossed discount is money. What is discounted. So let's just use how variant function again. We'll put in that expression. Call it this can't type for want of something else. Okay, so that the money times that the money resulted in that which waas a money. I think we're starting to see a bit of a pattern here, So let's see if we can compile some rules that we've found about how data conversion works . Well, First off, if you've got the same data times involved in an operation. The result will be that same died a type. So let's save the father we've been working on so far. So we'll go file Saive as And I'm just going to call that my number Queary with with SQL variant Now I've already got a t so we'll just drive around it. We'll save that away. We might come back to it later. By all means refer to it if you want to get back to the S capable variant property and say some examples of that. But let's just close that and we'll come back to the my number query file that we saved at the previous lesson. Soldiers got file, open my number, Queary. And there we go. It's coming to the basic query, and we'll make sure that you select adventure works database. Otherwise this isn't gonna work at all. So we come back to the basic query about order, quantity times to as increased quantity. And you know what? I will just put order quantity on its own royally. And I'll tell you what, I'll just save that file Now. Let's say our requirement is that we have Teoh device a column that is the order quantity. But instead of multiplying it by two, we have to divide it by two. We want to know what it is. If we were toe Harvard. Let's say that the number that were required to come up with is if it's Ah one, we need Teoh come up with a figure of 0.5. That's assuming the type of good we're working with is divisible by two. If it's true that it's got to be divided toe one, that sort of thing now that sounds fairly straightforward. But let's just do it and see what happens. So let's just call this expression. Would, uh Q T Y devote about two as split. Underscore peut why? And see what happens. Okay, there's are split quantity. They now what's going on here? Because there's some funny results. He if we look at the order quantity that divided by two is well, if it's six that ends up being three, that sounds about right. If it's too, it ends up being one. If it's for it ends up being too. That's great. But if it's one, the order quantity is one. The split amount is zero. Now that's not right. One divided by two is not 0.5. Same with 383 Divided by two is not one. It's 1.5. What is happening? Well, what's happening is exactly what we saw in the previous exercise, when we were working with a multiple of two. What we're saying is, this is an inter Gia, the factor that we're dividing by number two. The constant to that is research in the previous exercise is an inter job. So if we do an operation involving an integer and an interject, what is? The result, as we saw before, is an integer, and an interject is ah, whole number. It is not capable of expressing a decimal amount. That's what the other decimal data times are. Four. Now when SQL Server arrives at that zero value, it's actually truncating it. It's not even rounding it. It's truncating it in. Truncation is rounding down, so it's always going to be zero. So even in terms of an approximation, it's not accurate. If, for example, that truly evaluated to say 0.9 David, always if it still pretend zero. So it's not even accurate as an approximation. So what's the solution? Well, I'm gonna show you one solution, and then we'll discuss it. So I'm gonna take my split factor here. Gotta change that from 2 to 2.0 executed and see what happens, and then we'll discuss it. Okay, what do we got? Split quantity has gone from a whole number. Result to a six decimal result. In the case of order quantity, dividing up by two gave us 0.5 and a whole bunch of zeroes. That's pretty much it 0.5. That's what we wanted. Three divided by to give us 1.5000 and so on. And it's accurate to six decimal places. So what's happened? Well, this will become clear if we find out what is exactly is the data type of this 2.0, constant that we've brought into the mix. Well, I've just talked in the S cable variant property function up the and I'm asking it what is the base type of a little split factor? 2.0 constant. And let's see what it tells us. It is day to talk America. So going back to West slide about the data types that can express decimal amounts. We came across the numeric now the numeric and dismal data times and constructed by passing in two values to the name of the data. Type one is the precision, and one is the scale. Now, with that in mind, what is the actual data type of our split quantity? That is the result of all this? Well, let's just try cool that now time So that little spoke quantity that what is it's based heart? We'll check that out. It's a numeric. So in this case, an operation involving an integer and your numeric resulted in a numeric Well, let's go a little bit deeper and find out the precision and scale off the set split quantity that we've produced. So I'm going to use very property again. Only this time we're gonna find asking what is the precision and Colette precision out? Pay for short and one is the scale and ah, scale for short and see what SQL Server tells us. Precision and style is the position is 12 and the style is six. So what are we saying? He Well, what we're seeing is an example of data type precedence. Now it's probably easier if I take you to the Marcus off documentation page about data time presidents, so I will just go there. There it is, and it begins. Bought, discussing. When an operator combines expressions of different data types, the data type with the lower precedence is first converted to the data type with the higher presidents. Now it shows this prisons listed in that list down there. And if we look at the two data times we were working with, one is interject there at number 16. The other one was an American. I remember the Americas. The same is decibel, and that's at number 12. So what's happened here? Use when it did that operation involving an inch and a decimal? What it did was it converted? Because that's number six Dane, and that's ranked higher. It converted the into the decimal. And when you've got the decimal on a decimal in an operation, the result waas a decimal. Okay, so now that we've got a list, so you get information about variant property, I get you to, If you would be like, save this in its own file for later reference oldest site. That is my number query with SQL Variant and but for the time being will go back to our normal my number query. So I'll just get rid of all this SQL variant stuff and I'll put it back to order. Quantity, the split quantity expression, increased quantity or the country and star. And I just said, That is my number query, and I'll run that. So what have we learned? We've got this operation involving a ninja and decimal expression is the result. So how did we achieve that? Well, we had to. In order to get a decimal result, we had to either make sure that that was a decimal or that was a decimal. Now that we achieved by simply expressing it as a decimal, we just put a two point on the end. So that's that's a face solution. Now, let's say, for example, that without denominated there, if it wasn't a fixed value of 2.0, but it was instead some sort of a column. You know what? I'm just gonna make up some sort of a column name. He Ah, I'll cool. It means, let's I mean, there obviously isn't one But let's say, for example, that there was an internship column somewhere on their table, and we wanted to use that in our division operation. Now, when that was a constant value of two point, or we could just port expressing his point. Oh, that's a decimal, and that'll make sure that that ends up being calculators a decimal. But when that's a need and that's Indian, but we want to decimal, What do we do? How do we achieve that decimal? Well, that's when casting comes in, using the cast function to convert one orm or both of these values to a decimal or the data top we want in order to get the result we want. So we'll cover cast in a lighter Listen. So let's summarize what we've learned to that David conversion so far. If you've got an operation and the members of that operation are the same data time, the result is that data time, if you've got different data types, SQL Server will convert one to the other based on precedents into just get converted to decimals, and I would refer you to that big list of the president's that chain if you will to for further details, but another example. If you've got an integer on a decimal, then the result will be a decimal. 16. More Text Operations: So let's go back to learning about text operations and some more advanced forms of text operations, manipulating text and strength. So we'll go back to the my person Queary at Fire. Well, just got file open and go back to my person Query and my person query should read our title on the expression involving title and first name and last name as the expression column. Full name from person dot person. So I think having a little title. Our column extra column there on the file. I've deserved its purpose. Let's get Ruth that now A So you know, many people might have conjoined last time. That's actually two nights put together to form a last time. Let's actually search for those using wild cards. Last name like single quote Walcott Spice Wildcard quart. In other words, any nine, which is something a spice in or something. And there they are. Dermot Junior van, 8 10 You see what I made now just to demonstrate this next operation? Let's just put last bang on the far left. You know what? I was getting a little bit long, so let's just point last night on that sideline there and you can't say more point. OK, so let's just say there is a business requirement to have the last chuck or component of a conjoined last name on its own for I don't know, filing purposes or something like that. Okay, how do we do that? Well, we want some sort of a chunk off last name, and the way to do that is using a function called right and right is like left. Except it's the right side. It's the opposite side, so I'll demonstrate. All right, let's say, Well, OK, we'll get the first right first character on the right parts as part last night. Call up what you And as you can see, it's taken the e on the end develop today and put it on. It's own O on the end of a Leo. Put it there. Now that's not quite what we want. We want the whole thing over the case of LD That's 12345 characters. So we want the case of LD you want the right five. Great, but that's not work for this one here because the last chunky is actually wanted it six characters long. So how do we card in a sort of a variable style to get a big chunk. We don't way that chuck the size of that chunk Berries. According to the data on that particular record. Well, here's how we do it. Okay, there is another function called Khar Index, so let's get stuck in the car index now to find out about a piece of SQL, there's two ways to do what you can. Look up the online help or the local help for SQL Server. Or alternatively, we can just google it. So you know what? Let's just go to Google. Looked down thing and share what to search for. Well, searchable sq World server car in Dix. Okay, And that has taken us to you know what? I will take you to the official Microsoft Documentation website and the page about car index. Now this looks a bit technical, but I really would recommend that you were tempt searching for these commands in online, especially the official documentation for Microsoft, because you do learn a lot and it is the primary authoritative source of information of that SQL My look a little bit scary bought, the more you get into it, the more comfortable you are with it in, the more you're going to look. So let's just stop look a little bit at Car Index. Now what does car index to Syntex? Okay, syntax. Syntax is basically the way you write a piece of code that the way you're supposed to write it, so I can. What is it telling us? Car index. This is spicing the white marks off to telling us to ride it. That's the function. Name starts and ends with a bracket as they do it. And these partly private is that it, Except first, is the expression to find what we're looking for. The second is expression to search. What, what we're actually surging. We're looking for that and was surging in that and optionally we can have a start location . That's what these square records may means. It's saying that this particular parameter is optional. You don't have to put it in Now. What do all these things main? Well, that actually explains it down here. Expression to find expression, to find a character expression containing the sequence to find. And it goes on about the fact it's got a night 1000 limit But that's Ah, that's what I explained. The second perimeter expression to search that's explained down here, Expressionists it a character expression to search. So it spells it all out for you. And just on this last one here, start location, an interview or big into expression. In other words, a number at which the search starts. If sac location is not specified, has a negative value or otherwise, a zero value. The search starts at the beginning, off the expression to suit. So in other words, if you don't put in that last number, it just starts searching the piece of text right from the beginning. And if you put in like a three or something, then it starts searching from character. Three. So enough learning about it. Let's actually do it. Okay, so let's say we are interested in the first instance off a space. The first instance of space in the last night. So that's what we want to find because we want to sort of delineate. We're gonna find the second chance. So let's just find it. Okay, Index. Hi. Pin brackets. Now, what was thief Fist? Okay. The expression to find what are we looking for what? We're looking for. A spice so single court spies single point. It's a piece of text. Pieces of Texas have to be surrounded with single quotes. Comma. What is the 2nd 1? The expression to search? Well, what are we searching with searching last night and the third parameter? Well, that's actually optional. We know it's optional because it's friend of by, uh, square brackets. And help actually says, If you leave it off, it's just going to start at the beginning. So we'll just laid it off and given expression. Where is the space kidding? But that's giving us a six. So out of that last name their You know what? I'll get rid of that part last name there because that's kind of confusing us. So where is the spice that is returning a six, which is telling us that the first space is the six character in Van de Velde E. So let's just count it off. One V is the 12345 and six high pressed of its the right So nine. If you actually count those characters there, you can find that the space again is the not okay. Wonderful so basically want to do is we want to We want to use that piece of information to find out how we can get the second chunk. This is how we do it now. This is a little bit tricky, but basically it involves getting the entire length off that last night. The strength of with surging. Now the way to get the length is another functional show. You cold Lynn and that Texan expression in this guy's last name and we'll call that. Okay, that is the length of actually Count Van de Velde E 123456 P e is the 11th character and there you go. It's work. So how does that help us? What? What we want is we want the right chunk. We don't little of it. We only want that up to the first space. So I think you can see where I'm going here. We can calculate the number of letters up until the first space there by going length, minus the position off the first space. So what we want is the in the case of Van de Velde e. We want the left most five characters. It's right Let me see Now it's 11 living one a six is 12345 11 months. Six is five. Presto! And this guy here, we want the actual 12345 Again, um, 14 minus nine is five. So you can probably guess where I'm going with this. Where, after the number of characters being the length minus the that length, which is the link from there to the first character. So total length minus that is the number of characters that the last chunk of text occupies . So to actually get that number, this is how we do it. We're gonna do an Eric medical operation. I'll call that expression as the difference. Now, as we saw in an earlier listen, we can take our numeric values and perform operations with, um, using your basic arithmetic lock writers plus minus aster X meaning, multiply it and someone and so forth. Now, so far, we've been doing with our columns. Um, but we can do it with expressions, and that's exactly what we've done here. So we've taken that expression, which in this case will be 11 and that current explain the first spice, which is six, and that minus that will result in a dynamically calculated amount. So 11 months six is five. In this case, 11 minus nine is 5 17 minus eight is nine. So let's use this calculated expression in their right function and you consider not trying to do. We're trying to get right that value there, so let's just do it, Okay, right last name and that sort of difference amount there and let's see what the result is. Okay, what do we got? Veld E. That's the right most chunk of the last name. They are Leo Di Matteo's Chander cut. I can't pronounce that, but you get the point. So what we've demonstrated here is is a really powerful technique in getting chunks of text breaking up Tex passing text in SQL Server using Eric medical operations on the right and left functions. So let's re kept the functions that text functions that we've covered in this lesson The right function, which takes a piece of text a number and gets the that number of characters on the right side off the String Car index, which will find for us the first instance in terms of the number of characters along the string off a particular string within another strength. So this is what we tell that define. This is what we tell it to search, optionally weaken, start not necessarily at the beginning, but anywhere along the string. And that will return the first point in the string in terms of a number where this is found in this. And then, of course, there's Lane, which returns simply the length of the string. 17. Joining Tables: never gonna come to perhaps one of the trickier concepts in SQL and in databases, and that is querying data from across multiple tables. We're going to examine. Why do we have multiple tables? Why not just take a series of information and just shovel in one table and just query that one table? Weld is actually very good reasons for it, and we'll examine those. We'll talk about how we take those multiple tables and how we bring them together in the concept off for in Case. And we'll talk about the ways in which you can join tables, the right ways and the not so right ways. Well, let's look at a practical example off two tables that have related data data that has spread across two tables, and we'll look at the structure of it and get an idea of what's actually going on so forget . And here we see a table called production dot product model and let's just career with the whole table and say, What's in the okay, what do we got? We got product model that appears to be a prime key, and we've got a name looking down here, for example, we've got Mountain 300. Obviously, this eventually started base is depicting a bicycle manufacturing company, so that's some kind of a mountain bike model number 300. Now, if we go up to good old table production dot product, let's have a look in that if we will. If we scroll down the and we open up the objects that we open up the columns, if we look down to we have got a product model, I D. Now that column is a little bit special. You may have noticed that it has a little K symbol next word that looks a bit different to the prime case symbol we've seen before. That's because it is a foreign cape that probably sounds a little bit scary, but we're going to explain it quite easily. We also know it's a foreign K because in its little definition, there it has that little initials. If K, which stands for Foreign K now, what is foreign K stand for? Now? Let's just query the entire table and and look at any a bit closer. You can have multiple weary windows open, so to do that, let's just hit new query and we'll have a second window, wiping the McQueary product again. Okay, there's good old product, i d. There's the name off the product, and basically the way it works is the product is a specific implementation or version off the model. So each product has a model. The product is linked to the model by virtue off that foreign K feel That horrible word I mentioned before called Product Model I D. I'll just go down, he to ones that actually do have a model. Now what? That represents those little numbers. They represent prime keys from the model table. So every value that you see here present a product model Indian product can actually be found here. That's how the two are linked. So, for example, if I would do scrolled in to find a product with product Middle I. D. 21 in fact, let's just scroll down and see if we can find one. Hope. There we go, right there's one so we'll just go over to the far left and look at it. Product name. Mountain 300 Black. 38. So it's a It's a mountain 300 bike, but this particular product is black and its size 38. But thanks to that foreign key there of 21 in product model I day, we know that it's model is off Name Mountain 300. Now you're probably wondering, why do we have this data spread across two tables? Well, let's just look at the data that is within product model. Now. We need to retain the product model information. We need to know the name. Obviously. Ah, customer is gonna buy this product. They need to know what model it is so they could get some basic information about the product. So we need to know, with respect to that product, what is its model? So you're probably wondering that mountain 300 name for the model. Why don't we simply just store it on the product table instead of having this funny product model I. D. Number here and linked to a separate table? Why don't we just have a model name he and just put there the actual model name Mountain 300. In fact, there's a few products there that are linked to the same model. Why don't we just simply have that product model name they, they and their instead of having a separate table. Why make life hard for ourselves with multiple tables? Well, part of the answer is when we look at the product model table, it not only has a name, but it's got to other critical pieces of information as well the catalogue description and the instructions. We'll talk about two of the main reasons why we sequester. If you like a data like model off onto its own table and have this key linkage thing going on, let's say Let's see what would happen if we actually took the model daughter and we just simply put it on the product table. So our mountain 300 bike product records would look like this, you know, if the product key, the name and the model name and the model catalogue and the model instructions would all be stored right there now what if the company put a mandate that the catalog name for Model Amount 300 was to be changed from mountain bikes to a water? No mountain bicycles. Now, when you've got that data in its own table, you would only have to do that once. But with this data scattered across all these product records you've executed updated many , many, many, many times and you could be talking thousands of times. So it's It's a slightly inefficient way of doing that from, ah update point of view. Now the other reason is for a sort of data consistency. Reason, Let's say, for example, we wanted to do a report off all bicycle products off Model nine Mountain 300. Now let's say that when they were entering this particular product, someone made a spelling error in the model name and they haven't put a space they or let's say, on this one down here that happened to put a space there after the hyphen you're seeing some inconsistency were dependent on that model known being the same for all those product records. If we're going to get all those models off products together up in the one report, So by having this just plain free text, there's a danger that there is gonna be data inconsistency and the data linkage between model and product will be lost. So now we're looking at the arrangement where the model information is stored away on its own little table. It's got a key, and that key is the what is stored against the products. Not all this bit of information here. So, as you can see if we wanted to update our some information about the models, say, change the name from bike to bicycle, we only have to do it once it's done once on one record, he in the moral table instead off hundreds, perhaps thousands, of times on the product table, and there is less chance of daughter entry era. The other benefit is a thing called referential integrity. Now, when we set up a foreign key and this is what this is this model idea, it's a foreign k. There's that word again. It comes with a thing called referential integrity, and that means that any value here must already exist here in the model table. If, for example, we were to change that model idea on that product 7 85 to 32 there was No 32 in the model table, that would not work. That would throw an era. Now that's to do with the database design on which, of course, is outside this course Winchester showing you how and why we have this arrangement of Table of data across different tables. With that arrangement off the model daughter normalized is the word normalized into its own table and joined by a key there and a foreign key. They we can say with absolute certainty that if we want to query the product table for all Mountain 300 models, all we've got to do is go select star from Kerak Table, where Model I d. A. Equals 31 we know absolutely that these guys we select will have a product name of that on a model kelp catalogue of that. But coming back to where? Product table. Let's say we have an objective that we've got a product and we want to find out the name of the model off this particular product. Now, if we're browsing, let's say we know about the product title and, uh, we've gone and selected all the columns and we're looking across here and looking at the Model Life Day. But that doesn't tell us the model night. We've got two issues here. How do we find out where that product model information is stored, and how do we link this product record to it? Well, as discussed, all this sort of answers can be obtained by looking at the data dictionary for the system that we're reporting on now. In this case, we've got the data dictionary for the products schemer within the database, and this is what a database dictionary diagram looks like. There is more text details later on in this document, but this is the diagram for the adventure work started by specifically the products scammer . And if we look down here, we've got our product table that lists the key and a couple of columns. And over here we've got another table square representation off the product model and without a line drawing the product model table with product, the line goes down here and we've got this funny sort of three pronged crow's feet symbol here. And that signifies it is a one to many relationship, a one to many relationship. What that means is for every one key here that maybe many off them over here in the actual product table. Now we need to know about this stuff because that will affect how we design a query. So let's look at the table and at an example of physical example of this one to many relationship. So this is what one too many looks like. Here is Thea Query for product model. There's our product model record 21. That's the prime. Que. There's only one of them. There's only one problem key, and there's only one model, which is the whole purpose of having this table. So take note of that K there 21. And if we go to the product product table and we will get a mountain 300 products, there's three of them. They if we go across to the product model, there is the 21 code, and that represents that model we were looking at. But you can see there's many off them, so that describes the relationship. You've got one record for model, but there will be many instances off it in the product table and that relationship of one they and many links to it there. That is what this diagram represents. Now that's important. Sounds technical again. This is database design, and the more going further into that is outside the scope of this course. But we need to know about this when we go to designer query that will become apparent. Now we scrolled in further into the data dictionary. We've got a knack chewable, full on proper definition off the product table. Unfortunately, it's sort of split across two pages here, but not to work. If we scroll down. There is a list of all the columns and descriptions, such as the data types and a bit of a description of each column, the first of which is the product I day the Crime K. But if we go down to product model, I D tells us the data type and it tells us Product is a member off this product model foreign key to product model dot product model I. D. So that is telling us Product model is the title nine, and that is the prime key are in the model table, so that links to that. So now we have enough information to start writing a query to join the two tables. Well, I've always thought that the best way to learn something is just to jump in and do it. So what I'm gonna do is I'm going to write the query to join the two tables and then we'll discuss it. Okay with that Now I'm gonna execute that, and we will just very quickly examine the results. Okay, What's happened? Well, there's some funny things here. The prop. The order is suddenly changed. That was the product I d. Number one was at the top, but but But this weird looking order here, but let's look at the columns product I d name. They're the they're the names of their product columns. But if we scroll over to the right, scroll over to the right, keeps girl and keep care and gay all look at that. We've got a whole bunch of columns from the product model table is idea. There's name catalogue instructions, someone and so forth. So what we've done is we have joined the two tables, product and product model and by virtue of having the asterisks there that select not just all the columns from product, but all the columns from product model as well. Now that's looking pretty convoluted, and it's gonna be hard for me to demonstrate what's physically happening to the results. They So I'm just going to select a couple of them, so I'm going to select. I'm going to select to start off. I'm gonna select product a product I d and we're gonna select name, so let's just see what happens. He idea what's happened. We've got an era ambiguous column name nine. Now, what does that make while I'll explain what that means? And it's to do with something that I mentioned very early in the course about having aliases for the tables. Now I'll put the inspect the way they were gonna controls ID my wife out of this. Okay, so we put everything back we got there, he has to risk up the top. The so before I execute that, let's just make a mental note about this era ambiguous column name, name, and then we'll talk about what that means and why it's ambiguous. But what I'm going to show you is this is there is a reason that I've been telling you to put aliases on your table names because we're going to use these aliases in the select wars and imminent, and you're about to find out why. So I'm just gonna go back and select with Star in the select clause. What do we got? Okay, now this is the product table product. I D. Name from the product table. But if we go over here, you know the results. We've also got a second column called Name that came from the product model title. Now that's what the air remains. Ambiguous Column name nine. So when I went name up the toppy SQL Server doesn't know. Do you mean the product name or do you mean the model name? I don't know it. So how do we fix this? Well, we've got to actually tell when we select name. We've got to tell SQL Server Which of the two tables product or product model is the name column that we want now We want product. How do we do that? Well, so far up until now, throughout the course, we've just been typing in column names as such. But there's another way to type them is that you can actually explicitly tell SQL Server what is the table name that it comes from like so? So I've taken my table, alias PP and have put it up the top. Now let's see what happens. Great, that's a bit better. That's actually worked. And you know what? I'm gonna put another column up their product. I d again, I'm gonna perfect sit with the PP table alias and then we go. So what did I do? I perfect my column names with table knives. Now, up until now, we've been able to get away without table prefix is because we were only querying one table and within one table, you can only have each column has to have its own individual special name. You can't have duplicate names. So this ambiguity of column names was never an issue. But when you're joining two tables on those tables may have columns the same name you have to explicitly tell SQL Server where the table known comes from. Even if you are only querying one table, it is considered good practice to prefix your columns with the table nine. Now, up until now, I've kind of been letting you get away with not doing it because it was legal when you're only doing one table. But as a matter of course, it's generally always good practice to perfect your column names with the table nine. So let's continue where query and then we'll discuss it. Okay, what do we got? We've got some columns from something from column A and something from column B again sends a bit like a Chinese menu. But what we've got is some columns from the product table and some columns from the product model table product I. D name. That is the model I d from the product table. We can see it up there, and that is the model I d. From the product model type they can see. What's happening is that the two are the same. So what's happening is that SQL Server has gone to the product record. It's looked at Model I D. And then it goes over to the product model table, takes that value, tries to match it with the product model i. D. Key Prime Key column in that title gets to that record and then obtains this column value from it. So let's analyze the statement that be right to query those two tables. Well, it began with a select Laws Soeda from cause we selected from Table one and then we had another bit of text that was the joint command. We had the joint type. Now I'll come to what joint types are possible. So was table one, a type of joint. In our case, it was in a joint and then the second table that you were joining, too. And then the word on and then the condition to Jewett joining on in our cat. Now that condition has to be one of those wear top conditions. Something equals something, something that's got to be evaluated to true or false. So it's usually something equals something, and it's usually one key equals another key. Usually a prahnk equals of foreign cake. Usually. So let's go through a brief summary off the joint types that can go into that from clause that we saw in the previous slide. And then we'll go into in depth discussion about each of the types so the types are inner. Join an inner join means both tables must match the condition. If a row from that table is to be returned, Ah, left join all off the left hand table gets returned, all of them, regardless, if you've got 100 rise in the left table, physically, all 100 get returned, but only daughter from the right hand table gets returned. If it matches and a rod hand, join Maine's all the records from the right hand table. Get returned. An only daughter from the left hand table get returned if it matches, that's all gonna make more sense if we look at some examples. So here we've got a diagram attic representation off the two tables. We've been working with the product table on the model table in the product table. He we've got three records. Two of them are linked to Model 20 one's, Linked to Model 21 1 has no model at all. And in the model type, we've got a 2021 we've got a 23 here, and that 23 is a model that actually does not exist in the product table at all. So when we do an inner join, head of things match up and what is the result? Well, the matching happens like this. This product has a 20 that matches with that model that has a 20 that matches with that model that has a 21 that matches with that model that does not have a model and matches with nothing, so that's not going to get returns in the diner. This model does not have a value in the product model is not linked to the product table. It'll so that's not gonna get returned at all. So what is the actual result off the joint in the final data? Well, we got three Roy's, so it's kind of ah, product off the matching. Its these three products that have a value in model table, the one that did not have a value, didn't get returned it all and the model wrote that didn't have a model in the product table that didn't get returned at all either. So it's kind of a narrowest combination off the two tables. Now the key concept to get writing is you may be wondering if we go back up to the raw data . You may have wondered Bullhead, is this get represented within a grid? If this is a match, this is a match, and this is a match. But this isn't how does this get represented in a grid off both this and this? Well, as you can see the 20 record because that matches twice that gets repeated. That is on to Roy's. And if you go down to the results, that's what we see. The 20 Roy, the 20 model Roy is repeated, and it has to be repeated in order to match with these two products so that it forms agreed in the results. So let's see in in a joint in action by querying our actual data vice. So I've got three query windows open, the 1st 1 that select everything from product at SQL. And I've taken the liberty of putting thief or in key product model I D. In the left, most column so we can see it. And if we scrolled in, we can see that right down the bottom. We can see there's based on the road tag there in the window. There's 504 Rosell together, but if we just sort of scroll mead way, we can see that the 1st 209 don't have a product model I day, and only about the last the last 300 or so actually have a product model idea site off the products on the 300 or so have a product model. I day. Let's look a product model, so we've got 129 now, I will point out in your copy the standard copy of Adventure works, there are actually only 128 product models. I've actually created one myself number 129 purely to demonstrate what happens when you've got a value in Perak model that's not in product. So you might say this idea. And I'm just doing this purely to demonstrate what happens when they is doddering one table and not in the other. Okay, so product model that's not in the product table. So let's come back to it in a joint query, and we'll just run that again. Now what do we sing in terms off the total number off Rose. We're only getting 295 returns so awful. The products We're only getting 295 rise because that was what we saw before. Only about 300 or so actually had ah model I D. So because the ones that had no model idea that had known it doesn't get returned because it's an inner joint now similarly, with the product models and bear in mind, we are ordering this set by the product model table and its product model, I d prime que. We're only returning them up to 128 the 129th the one that I created myself, which does not exist in the product table. That's not getting returned because it doesn't satisfy the inner joint with an inner join for road to be returned, though joining Dad has got to be in both sides, in the right hand table and in the left hand table. If you're not in here, we anointing here, you don't get returned and a road will not be created for you. So now let's look at how a left join works Now the left join. We return everything in the left hand table, whether it matches or not. So in this case, we've got the same records matching with product with the model table that one matches that one matches that one matches. That one does not match because it's gonna know. But that's going to get returned anyway. So let's see how it works it in the results. Well, the left joint results are a little bit wider. We've now got four rows and we've got four rows because the product record that didn't have a model and did not match the joint that gets returned anyway, so was a minimum. You're going to get all the rows in the left hand table, every single one of them to hold literally the entire table. And you will get the matches on the other side. And you probably wondering. Well, hang on. This is gonna be returned as a grid as uneven grid, like a spread shape in the results. So if that one doesn't match what's on the right hand site, well, what's on the right hand side is a whole bunch of knows. It's just a whole row of nothing but knows everything, including the prime Que So let's apply What we've learned about left joins to the little join query here. So I'm gonna change the inner to a lift and executed and see what happens, right? What if we got We have got 504 rows as opposed to the to 95 that were returned before. So basically we're returning. The entire 504 rose out of the product table, there's a product or do the name all 504 of them, including the ones that had a no product model I D. In the product table. And if we look in the rose selected from the model table we've got These are the ones that failed to join these air the rose that failed to join and in the columns for the model table, the right hand side of the job. We just get a flat. No, but in the product model Roy's that did set us father join. Of course, we do get the product model data. Now we come to write joined Magic, and I think you can guess what's gonna happen here in the right join. We return absolutely everything that's in the right hand table. Everything gets returned there. Nobody misses out. And we only return the rose from the left hand table if they match. So this matches this matches this matches this 23 model doesn't match, but it's gonna get returned anyway. And this seven I date product that doesn't match because it doesn't have a model that does not get returned. So what is that gonna look like in the results? So, as you can probably guess, the rot joint is kind of a mirror flip with respect to the left joint. So we get everything in the model table, the right hand table, including the one record that didn't match the 23. We don't get any product records that failed the match. So when this is represented in agreed form on this road because this has nothing to match with, we just get a whole row of Nell's. So let's see this in action in Al Query. I'm gonna change that to a raw join and just to make things display a little bit of help, it make a bit more. Since I'm gonna order it, buy the product models Prime case. I'm gonna change that 2 p.m. The alias for product model table and we'll execute that and we'll see what happens right. What we got that they ease the columns homes from the product model table is the product models prime cake and if we scrolled in was saying that repeated three times because it's matching three times with the product model. Title will scroll down, strolled in, scold and scroll down, scold and scroll down. Um, now know it. This is these particular models are returned, even though they are not in the product table because it's a right joint whatever is on the right hand side that gets returned at least once and because it's got nothing to match with on the left hand table in the left hand. Title columns would just get a write off knows, but interestingly, if we go down the very bottom and we got in he to the little one that I created. So Mike's dummy model, which we absolutely know for a fact, does not exist in the product model table that gets returned and because it does not match with anything in the product table in the columns for product that just returns a whole bunch of dolls. Now I'm gonna let you in on a little sacred, and I'm gonna give you the benefit off my 20 years experience in working with dot devices and working with SQL. In my 20 years, I have never used a right joint in my life, never used to write joint ever. I'm really only showing it is to make you aware of its existence and to make the course complete. But it's really redundant when you think about it. I mean, all of right joint is is a mirror version of a left join. So if I ever wanted to hear, for example, do a query where I've wanted all the models and only the products that match all I would do is just put the model on the left hand side and doing left joint something like this and a presto. This is our right join that's been turned into a left simply by when we write it, put the model table on the left hand side, the product on the right hand side and then turn it into a left. The benefit is you only have to mentally remember two types of joints in up and left on day . That's all you need to get what you want. So let's try and turn our little fancy right just to prove my point Will grab it to management studio and turn the right Join into a left and see what happens to the results that Okay, so here's our convoluted right joint. So tell you what I'm gonna do. Wanna take product model, gonna put it up here, attack the product, gonna put it down here, and I'm gonna turn that into I lived now in the old query. We had 35 wrote. 305 rows returned. Let's see what happens now, right? We got a whole bunch of rose 305 returned. And if you took were to examine that, your record said you would find them identical. No. My point is, if you do it this way, you only have to remember about inner joins, left joints, and you don't have to remember anything or every use our right joint, which is a bit. You don't have to do that mental gymnastics of flipping things. So in a joint lift and in practice that will get you what you want. So let's recap everything we've learned, and it has been a very long listen. We learned about joins and the ability to draw daughter from multiple tables we learned today. Inner joins and left joins. We also learned about right joins, but in practice, I have never actually really used these because you can just a left join and just swap the tables around 18. Reasons for Left Joins: you may well be asking that this point. Why in practice in the real world, why would you ever really want to do a left join? Why would you be interested in matching in this case? We've got the models. They they're not matching with the product. And you got these? No, substitution is for the columns in the product table. Why would you want all this stuff? What would you be interested in? March Rose, Where the There is no matching. Well, I'll demonstrate why all that shift the columns around so that the model columns are on the left hand side. Which intellectual kind of makes sense? Because we're dealing with a left join and we can sort of see what's happening. Okay, so we've got the model columns on the left hand side and put them on the road. There are NRO they I've ordered by the model prime que We scrolled in If we got rock Rock Broad Broad Dan Weaken. Say the one that I created, the one that we know for effect definitely has no models. It gets returned and nose in the product columns. Now there is a very handy reason for doing this. Let's say we wanted to get a list of all the models that had no products, sort of orphan models. Let's say that was a business requirement to find this out. How do we do that? Did simple. And the left John's gonna help us. I'll show you how. Okay, what about just written where the product product i d The prime key is no. In other words, identify all these Roy's with the reason our product, in other words, it substitute with all these weird looking no values. Now, the reason I've said to get me all the ones where product I D is no is because let's say we had a product I d. That the name was no or one of the other columns was no. So we don't want to go get mailed. Ones were Product name is no, because they could be a match, and it just happens to be a then on that product record. It had a known name for some weird reason, however, because every row in a table always has a prime key. It is physically impossible to have a prime key. That is no, because the product records always have a product our day. If they exist, then if we identify the ones with a product idea, the prime key is no. We know for effect. It's gonna be one of these funny, sort of vacant space rose here. So that's how it's done. That's, Ah, let's execute it and see what happens. And, hey, presto! We've got 10 product models that have no related products because the product prime key is no. If there was a product, it would have a prime key and it would be listed here. But it doesn't so it's not so what we're saying here, a left join and where the right hand tables K is no is the way to find out where there's something here. But there's nothing here now. There's another very legitimate reason for using left joints, and it's to do with when we use queries in reports or maybe display screens in some sort of a user interface. When we displaying the daughter and I'll give you a demonstration, let's say we have designed a crystal report that lists the product model and below it on the page lists the product, and it would look something like this Now I'm going to to demonstrate the data for this report. I'm gonna change this week calls. Let's say I am going to select where just two products. I'm going to select one product model. We, the model does have a product attached to it, and one model which does not have a product attached to it. So, like with the product model, is either one or 1 29 And here we go. This is part of number one. It matches with three products and this is number 1 29 the funding one that I created, which of course, is not linked to any product. And we got the nose there. So what would that look like in a report? Well, it would probably look something like this. You would have a head up being the model, and then you would have agreed being the products that it's linked to. And when it came to Mike's dummy model that has no products link to it, the name they would be the header and under the head out there would be agreed off Nothing , because there's not products. So what's the difference if we use a left John or in a joint. Well, let's turn it to an inner join and say What happens? Marx dummy model has disappeared because it doesn't satisfy the joint on the right hand table having that effect a report. Well, it would look like this. The Mike's dummy rope age would completely disappear because it wasn't returned in the query. We never saw the row with Mike's dummy model on it. So therefore, because it's nowhere in the query, it will not show up in the report. You would get a blank page or no pages or so that serious because that's losing us important information. The report probably needs to show that there is no product linked to Marx dummy model, but because Marx dummy model is not there a door with losing critical information. So let's just recap the two points we covered about what lift joins are a good thing. Left joins, maybe preferred, because when they replied to reports, they may make headers appear where they otherwise would not, and they enable us to highlight missing daughter in the right hand side table 19. Cross Joins - Just don't do it !: Now there's another type of joint which I did not explain to you in the main Listen about joins, it's called across join. And there's a reason I didn't covered indep because in my experience, it really should not be used almost never because it can create serious problems. So I'm just gonna demonstrate you what across joining is and why you probably should never use it. Now, what I've done is I've artificially created two tables purely to demonstrate this exercise table one and table to you will not find these in your adventure works. So if I just select those now, this is something you probably have an arrangement you probably have not seen so far in management studio. You can have more than one issue. Well, stipend in a window. So I've got one. They at a standalone select statement there and select them and they'll just be executed one after the other. And the results sets will be shown one on top of the other down here in the grid. So I'll just execute those to select start from my artificially created table one and table two. Okay, what do we got? So table one, it's got an i. D. Prime key on 23 It's got a text column with ABC and a Table two is almost the same thing. Except it's got ex wives did in it. No, if we were to do a normal in a joint like of shown you he had This is what it would look like are the ideas are meant to catch up and this is what we would get. Great. Okay, three rows, one matches to the other. Wonderful, Pretty straightforward. Now this is how you would achieve the same thing in across joint. So let's just look at the Syntex table. Cross join other table and then it goes into the were close. Seven. The joint There's not on. There's no condition. So to demonstrate what's actually going on, I'm going to what? Comment out. My were close and we're just going to do the basic join in itself. Let's see what it gets us right. We've got nine rise. Know what's exactly happening? What we see here is a table one wrote one matched up with table to row one and then two and then three. Road to in Table one is matched up with table two wrote 12 and three so you can see what sort of is happening. He Each record is matched up with every other record three is messing up with 12 and three . The total is non so literally it's table one multiplied by table 23 times three gives you nine Royce. So the idea is to get the result you want, you then apply a wear clothes. So I'm gonna uncommon my way close there. That is the condition we would normally put in a left or in a joint. So instead of up here, it's down here. So let's just execute that and it's got us the same result. Now you're probably wondering, OK, what's wrong with that? It got us the same result. Well, potentially there is something very wrong with that, because now I'm going to comment. The way equals the and what I'm about to describe to you is to do with ah performance of SQL Server and how it actually handles queries. Now, this is a bit under the hood stuff here, so I'm just giving him a broad overview purely so you're aware of it. So when SQL Server does any query. What it does is it Does this part select from And then in fact, I'll just do that. And then it applies the where clause to cherry pick the records that we want. So it does. This gets these sort of held behind the scenes in memory, and then it looks at the way close and says, OK, I want that one because that equals that that one because that equals that one because that equals that. And then it cherry picks the Rickles we want and we end up with, and I'll just comment that. And ultimately we end up with that and you're probably thinking, What's wrong with that? Well, something could be very wrong, and I'll just come in that out to do it again. Something could be very role if there is a lot off records in the two tables, and when I say a lot, I mean something like potentially 100,000. Do you think about that? If I've got 100,000 records times 100,000 that's 10 billion. That's 10 billion Roy's 10 billion rise, and then it's going to sort through 10 billion rows and apply the were close Now if we done an inner join and it did, and it did this first, we would have just gotten the three rows and that it would have gone through and applied would ever wear condition. We wanted to just those straight on. That's a lot more efficient, A lot more efficient getting, say, ah, joint of site 100,000 instead of 10 billion. Now I'll tell you an example of this was I was actually in the course of my work, I was called out to a site where the it was a school management system and the school was reporting that the attendance marking screens in the morning. We're slowing down the whole system. Every time somebody marked the attendance, the entire system almost brand will halt. I went out there to analyze what was going on and sort of used tools to see what queries were happening behind the scenes. And what I found was one of the developers had put in a cross join into the query that gathered up the records for making attendance. Now, most other sites didn't experience this. This particular one did because they had a lot of records. They had an enormous number in one table, an enormous number and the other. And when I worked it out, they actually did have 900,000 there and and ended up with a record set of 10 billion, and the soffit was forced to troll through 10 billion rise. To get the ones that want it is very, very inefficient, and it can cause an entire system to literally to to shut them and bear in mind than rob rows in a table can grow over time as more dollar resented so literally. If you're writing a report so crystal report, you don't want to be that guy who is responsible for causing the whole system to grind to a halt because you put across joint in your query, so cross joins are strongly recommend against them. The only time you would ever use them is if the conditions in an inner in a joint was so hard to express that you sort of had to put them down here in the where clause. That's very advanced and you absolutely have to know what you're doing and use certain techniques to reduce the number of rows there and they very advanced. I wouldn't recommend you do it unless you're very, very, very experienced. Sorry, my recommendation is cross joins. Avoid them altogether and stick to in and left joints. And it's assignment time again. So I'll get you to download the file assignment to dot zip and inside you will find Thebe business requirements in assignment to DOT pdf. So read those and design Aquarian gets results. And I've offered his suggested solution in the file assignment to suggested solution dot SQL and my results from that solution in assignment to results dot x L S X. 20. CAST conversion, and Assignment 2: So let's return to the function that we touched on during data conversion, which was the cast function. I mentioned that it's a particularly powerful function and we're going to use that in conjunction with the type of operation we saw earlier way. We were dividing by Inter GIs. So if we go to object Explorer, I'll show you the table we're going to work with. And it's right down. He in production dot work order, and we're going to query some of these columns. Several. Start a new query window and we'll talk this query. Okay, so we've got a couple of columns here of some interest, these order quantity. And then there's scrapped quantity, like, for example, this particular order here, which the order was for up 240. But in the course of production, four units had to be scrapped. So let's say we're interested in a report off the proportion oven orders quantity that ends up getting scrapped. In other words, it's the scrap quantity divided by order quantity. They okay, well, we're interested on the in the ones that actually have a script quantity, so let's just never things down. By pulling away condition, way scrap quantity equals is does not equal zero. I've taken the liberty of putting in another condition way. Scrap quantity is not know because, you know, if it was now that we compared it to zero, that actually wouldn't hold true and it wouldn't return the ones we want. So generally in these situations, it's always good to eliminate the possibility of a no. So let's just see what that gets us okay? Nothing that's crap. Quantities there now to make life easy and going to take the two columns are interested in . That's going to be part of your expression. Ward accordingly and scrap quantity and put him on the far left. Okay, now let's say we're interested in theme the ratio, off scrap quantity to order quantity, and we just simply get that by dividing that by that and we wanted expressed is some sort of a percentage. Ultimately, that's what we want. So let's give that a go and we'll put it on its on line here, okay, and we'll execute that and say, What happens? What does that get us right now? We've got the same problem that we saw when we were dividing error quantities on the production table where we were dividing those interject columns by an integral factor and the result waas an integer. So this touches upon data the data conversion rules. If we have one import and this is the other important the same day to talk, the result is going to be the same data time. The only way to prevent that is if one off those operations in the equation is of a higher rating data type in the scheme of data conversion, for example, if that was a decimal, then that would end up being a decimal. But it's not. So what do we do? Well, we can actually convert these to a decimal or any other data type we like within certain restrictions, and so that can manipulate the results in data talk that we want. We can do that with a command called cost. So let's say we want a result here to be have four decimals, so it will convert that to a decimal value with four decimal points on the end. Well, first, I'm gonna demonstrate casting are just by putting the cast result on its own line in its own column just using order, Quantity as an example. So we'll start a new line. I'll put order quantity there and this is how we would cast it. Que what? The command cost Thea value that we're casting and the Syntex is as and a new data time. So I'm going to specify decimal 10 comma, four close brackets as or uh cute. See why dick Meaning decimal. So let's just see how that works. Check that out. We have got the order quantity, but this time it's got decimals on the end because it is a decimal data time. So let's just apply what we've learned to a little ratio expression up the top here and convert that factor in the equation. Order quantity. Convert that to a decimal Great. And this has given us exactly what we were after the ratio of scrapped to the total order expressed as a decimal, which we can interpret as a percentage of we want. So, for example, this one is telling us that the 0.25 off the entire order was scrapped, which equates to 2.5%. You can see what's happening here. That was a decimal decimals are higher up the hierarchy in terms of data conversion over inter GIs, which is what that is. And that means decimal indigent decimal wins out. The result is a decimal. Now we could have for completeness cost that as a decimal as well. But we really don't have to as long as one element is of the higher ranking data type. Then we get that high ranking type as the result, which is what we want. Now I am going to very quickly controls Ed my white backwards to But the query back the wet waas before we introduced Cast here just to demonstrate a particular trip. So I'll just go. They were guy. Now I'm gonna show you the wrong way to use cast in this situation. Okay, Now you would. That might look like exactly the same thing. But is it? Let's just say the result. No, it's not. Where's Ole decimals gone. Well, what's happened is the system has done exactly what we told her to do. It did this operation in the brackets first, which was interject divided by an indigent. And what's that gonna bay and interject up big fat zero. So if you cast a zero as a decimal. What's it gonna bay? It's gonna bay a zero. We just a bunch of zeros tacked on the end. So that's not the way to deal. We've got to be a little bit granular with their casts to get what we want. So I've put that expression back the way it should be in order to a get a decimals. But if you want that expressed as a percentage, as you can probably work out, it's a simple is just multiplying that by a by 100 to get it expressed as such. So I will just do that surrounds the entire expression with brackets and simply times about 100. And you know what? I'll just put that on the lawn. They just was a little bit easier to raid, and there we go. That's a bit more meaningful. 2.5% there, 1.79%. There you get the idea. Feel free to do what saved this if you like, perhaps for later reference, and we'll put it under management Studio Onder perhaps a folder called Casting Wherever You Want, and we'll call that casting daughter ski Well, now the cost function can also be very handy when dealing with text strings. Now I'm gonna use that involving a text string here. Let's say the business requirement is to come up with a column that is a piece of text that is going to be a label dropped on a crystal report. And it is gonna be a plain English description about the script quantity. And it's going to read a script, quantity. Whatever the quantity years units were scrapped, three units were scrapped, that sort of thing. Now let's start off by just putting scrub quantity right over the left hand side here just to make things easy when we start comparing things and then on the next row, I'm going to put this expression now before a stock Kincannon ating. Let's just go over to the columns in the object, explore of columns for our production. Don't work order and let's just double check scrapped quantity scrapped Quantity is small int. Okay, let's just remember that. So we're coming every scrap quantity and we're going to contaminate it to a piece of text. But before we do that, instead of putting the proper label units were scrapped I'm gonna put a number. You might think I'm crazy. There is a reason I'm doing this to prove a point. So let's just give the expression name. Okay? What is that going to get us? Okay, What's happening now? Because we're intending to King Cat Night and that is a string. As you can say, you would kind of think that that would raid the quantity. Scrap, corny, whatever it is. One and a two technology. The end. You would think that would just be one, too, you know, wanted to run together. Well, it's not. It's a three. And neither is that That's not 32 That's actually a fight. So what's actually happened? It's done and arithmetic calculation, adding the numeral that is to to that quantity. Now, what's happened? It's to do with the data type presidents that we discussed earlier in the course. Now that is Ah, Varsha, Take it is red. That's a vodka that is a small into. We saw that a second ago. So how did they rank in the data type Residents stakes, so to speak? Well, let's go back to the Web page where it's discussed. Okay, so this is the data type residents page. Where do we stand? World is there Bartsch our way down the bottom. And if we go to a small end or look, that's further up the chain number 17. So literally a small end beats of Archer sort of like like poker really, isn't it? But if you come back here because a small into bits of archer, in other words, that gets converted to that. So it converts that to to a small end and then adds that Smalling, that is to to the cornering. And that's exactly what we saw it in here. One had to added to it to arrive it threat. So what happens if we actually put in the proper text value that we wanted in the first place? What is going to happen? Idea? We've got an ERA, and the Arab rates conversion failed when converting the var child value units were scrapped. That's this guy up here. Two data types, small int. So what's tried to do the same thing again? It's tried to convert that to a number a small end, and that's completely filing. It worked. It worked when it was, too, where that's not gonna cut it. I mean, that's obviously not a number. So to get us out of this mess, we've got to use cast. What we really want is we don't want that converted to a number. We want the other way around. We want that converted to a piece of text so cast can help us out. So let's do it. Let's just given a nice big number, say, or 100 just to be safe. Sorry to follow the nomenclature off cost that is value the as and what we're converting it to enclose brackets. So let's see what happens. And you know what? I'm just gonna put that only gets online. They and let's see what happens. Bring it that has worked. The scrap quantity is now in a pace of text that says one units were scrapped, three units were scrapped. Great cost has helped us out again. So cast has proved that it can kind of be a Swiss Army knife kind of function. It can help us out in a lot of situations and convert almost any value to any other kind of value within reason. So let's just recap what we've learned about the cast function, the sin taxes cast the column or expression that we intend to be converted as and the destination data type, defined as you normally would. And the closing bracket. Now that's subject to some constraints. We can't, for example, convert a letter to a number. But apart from that cast is a very powerful tool in day, and it's assignment time again. So I'll get you to download the file assignment to dot zip and inside you will find Thebe business requirements in assignment to DOT pdf. So read those and design Aquarian gets results. And I've offered a suggested solution in the file assignment to suggested solution dot SQL and my results from that solution in assignment to results dot x L S X. 21. Making a CASE for smart queries: So now we're gonna look at a very powerful tool in SQL. Ah, very powerful. Commanded is the case statement on the case statement allows us to were make intelligent decisions about their results. The case statement basically follows. And this this is not the execs in Texas is just the former follows. It basically says, if some condition that would give to it if something able something else or something exists, then returns some value or expression, otherwise do something else. So it's kind of like the if function in excel. So let's just get stuck into it and do some examples. So the case statement actually has to say in Texas will examine the first and more common one. It follows this form case the would win and then some sort of ah data condition the same sort of condition that you enter when you're constructing a were close something that evaluates to true or false are quite often that involves a numerical or logical operators such as equals greater than equals something like that, then the way then and the result that we want When this condition is true, this result can be of any data type you wish. The statement is then forward with else and a different result that we want when this condition is not true and it ends with the word end. So let's get stuck into using Thief First Syntex of Case statement In good old production dot product, I'll start a new query window. Okay, now there is a column, he Mike flag. And if we look that up inthe e daughter dictionary for adventure works, that is a bit now. Ah, bit is basically ah, it's effectively a numeric data type. It's one or zero. So the description off the field tells us that zero represents that a product is purchased and won is that it's manufactured in house. That's what these values main. So let's say we want a report that makes use of this make flag value. But instead of that America zero or one, we really want a text description that makes sense of that value and actually tells us whether it is purchased or produced in house a piece of text that weaken drop on, say, a crystal report. And that makes plain English, since so we'll produce that using the case statement and looking at that make flight value . I'll just select some of the columns that we need to actually make it report. Now we're going to create a text expression column on the far right here that actually returns purchased or manufactured, depending on that value. So using that first Syntex of case, this is how we would do it. Okay, so let's discuss this. We followed this in Tex case when P. P dot made flag is equals zero because there's an equals there that evaluates to true or false the sort of thing we would put in a were statement. This is the result we want. If it is true, and if it's not, we just use that and we finish it off with an end, and we give it a meaningful expression description. Now, I noticed that I organized my Syntex of my case statement on separate lines. They personally because that's my preference. I think that makes sense. You conform it that, as you see fit now, this case statement that we have here is fine and well and serves us well when there is only two choices involved. Where there's only two choices in our decision tree in this case where make flag is one or zero. What if there is more than two possible choices? Hadas Case Handle that. Well, let's just construct a query way. We do have more than two possible choices and see how we can make case help us out so we'll just wipe this out and then we'll save. It is a different version off file, so we'll go file savers and we'll put them under. I've chosen to put under medicine studio case and we'll put that under. We'll call that product list. Cross is out. You'll just make that caps is an you fall and we will select all of the product. Now if I go over to the right here, there is a column called a list price of records there with zero in them. So just for this exercise, let's just pick the ones that are non zero now for brevity. Let's just pick the being minimum columns to demonstrate in this exercise. So let's select product I. D name and our list price way over here. And you know what will order it by a list price just to demonstrate the point that I'm gonna make now if we browse the list processing, they vary from $2 to $1000 to well over $3000. So let's say there's a requirement Teoh create an expression column that categorizes, Are these products based on the list price? But based on where they fall in a range of prices? On Let's Say, these three categories of list price that we're interested in, uh, one is $100,000 1 is between 1000 and $2000.1 is over $2000. So we categorize them as big as small, medium and large, shall we say? So? Let's just jump start off with their case statement and see how far we go. So what does that get us? Well, all these guys here that are under $1000 They I categorize this small that's wonderful. The ones that are $1000 or over. They categorized his medium wonderful, but hang on. The requirement was that anything over $2000 is supposed to be lodge, but this is flagged his medium. Why? Because we've only built two choices into our our case statement. A case statement by itself is dichotomous it on the allows one branch or two options. So how do we cater for more than two options? Well, that's where nesting comes into it, and I'm gonna show you how I'll write the code and then we'll discuss it. So what have we got here? Well, if we scroll, then look at the results and then we'll look at the code. So the ones that are the ones that are under $1000 this small, the ones that are over $1000 are medium. And if we go further down and hey, presto! Business requirement is fulfilled. If it's greater than or equal to 1000 but less than 2000 it's a medium. If it's over $2000 however, then it's large, so that has met the requirement. Now just to come up here, let's see what's happening. Okay, we start off with a case on. This is how the code runs. It goes, right? So when the list price is list in $1000 I'm going to categorize that as small otherwise And what we've done here is we have sort of we've replaced the expression that we had the medium. We've replaced it with another expression on that expression isn't just a piece of liberal text. It is a case statement in itself. So it's a case statement within a case statement. And we can nest even deeper because let's say there is another requirement where there's another category where if it's between 5000 it's large. But if it's over 3000 it's extra large or something like that. So we could nest again by putting another dropping another case statement into this else branch. So let's give that a go. So, as you can see, we've got another case statement dropped within a case statement. So the whole logic goes when it's less than 1000 small. If it's not less than 1000 that we come down there to these else bridge and the else branch begins with another case statement. So it goes when it's between 1000 and 2000 then that otherwise and then another one where it's between two and three, then large. And if it's greater than three effectively, it's very large. So let's just look at the results Danny and Habitual finds. I'll look at that if it's under 3000 it's large and If it's true, it 3000 door over it has that very large value, which is what we wanted. Well, that's pretty powerful. But there is a far slightly simpler Syntex the weekend constructed case statements that may suit better in some conditions, so let's have a look at that. So for this next exercise, let's just save that query fall, and we'll just save to say, product list price amble. Close that, and we'll start a fresh one and we'll save the says address talk, because we're going to query the address type table. So let's just select the entire address type table. Okay, so that's pretty straightforward. This is some sort of address type tigers read that that type is assigned to address is proper in another table, but we just look at the values. It's ah, column of an idea value, and we've got a name. Let's say there's a business requirement that we must express in an expression column over here somewhere that indicates whether certain types of addresses receive a catalogue in the mail. Now let's say the business rule itself is actually a little bit convoluted because, say, supposing this type gets a catalogue and this type does. But this type doesn't things type does and dozens or it. Basically, it's so granular that it's gonna be really hard to sort of express it in that nested style that we saw earlier, because it's gonna have a lot of lot of nesting and it could be quite hard to read. Well, there is a slightly simplest syntax that the case statement has, and we'll examine that in detail. So the case statements second style of Syntex. I worked a little bit differently in that it doesn't compare. Truthful is conditions. It compares literal values, so it's actually a little bit easier to understand. Maybe, but let's just look at it. So it begins with Case and the column name that we are evaluating. The next heart reads when our value, in other words, win the column name equals a certain value, then the result we want attached to that value, and then we can have another line when different value, then a different result, according to that, and we can have as many of these when lines as we like. Ultimately, we need an else clause that is the result for all other values other than those caught by the previous win lines and ends in end. So I'm just gonna right the case statement and I'm going to select the prime key. The name and I'm going Teoh, make up the case statement and with the rules as to what values get what results. I'm pretty much just going to make it up as I go, but I'll just write it and then we'll discuss it. So what do we have here? Well, we got a fairly complex a bunch of logic here. But if the actual required logic is complex, then things style, actually is, is fairly easy to understand. When name is this value than do this when it's something else, we do something else. And, you know, we could have had that nested style and said, uh, when p. I name April's billing or April's harm and and combine it with other conditions, like or and and But you know what? This is actually a lot easier and simpler rate, so it's up to you, but it gets the job done. Now. I've disclosed that window and I'm going to start a new one, and I'm going to demonstrate a potential pitfall when we're dividing, dividing by numbers and have the case statement can help us out with that. So I'm going to select. So I just selected the entire purchase order detail a table, and there's a couple off columns that I'm gonna use for an operation here. There's line total and there's stocked quantity. Now let's say three requirement is that we want on expression that is the average line total over the quantities that we've got in stock so that divided by that I'm not quite sure why he would do that. But let's say, for argument's sake, that is the requirement. Now I'll just select the Bay Minimum columns to get this query readable. So the requirement is we're going to have line quantity divided by stocked quantity. Now that may produce a bit of a problem because noticed that some of these columns have a zero in stock quantity. Now, for those of you that remember your school maths, something divided by zero returns an era. Now let's say for argument's sake that we know we could eliminate this just by putting in a where clause and saying that don't return anything where stock quantity is zero. But let's say that, say, to make a crystal report work. We actually do need to have everything returned. And just leaving some out is just not gonna work for us. Sorry, we've got to work around this somehow. So let's just start writing the expression. Okay, let's execute it and see what happens. Idea exactly what I was afraid off Divide by zero era encountered So went down that that table and started calculating things, came to a zero and encountered the zero. So how are we going to get around that will? Hopefully the case statement can help us because it can have its conditional logic. It evaluates each row on its merits, so let's see if we can apply it and then we'll discuss it. Okay, so let's see what we've got here. Well, you know what? I'll just put the expression name on its on line there, but basically we've replaced the actual operation with a case statement on the cases where the stock quantity does happen to be zero. You know what? Just put a zero there. You know, we could have put a no or something, but, you know, afraid purposes. Let's just let's just put zero. That'll serve for the explanation. If it's not zero else, if it's not zero that just go ahead and do the divide by operation. And let's see what that gets us right. We don't get a divide by zero anymore. And if we scroll down the There we go. We had a stock quantity of zero, but it didn't stop. The operation just returned a zero, so case statement helped us out a great deal. 22. DATES, and Assignment 3: So now we're gonna come to a discussion about di ites. We're going to cover the to date data times. There's actually two of them, and that's important because they the two types behave slightly differently, and we've got to be aware of them. We're going to get into querying date type columns. I'm gonna learn how to manipulate the date components within those data types how to extract the month or how to compare theme date or even the day of the week. And we're gonna cover potential traps when we're working with time components within dates the data types involved. There's actually two of them. These date, which you would, uh, that makes perfect sense. If you were to look at the values inside a date column, you would see something like 12 31 2019 which equates to 31st of December 2019 when it's stored in SQL Server. The dates are stored as a month, date and year because SQL Server being an American product, it follows the American Convention for expressing dates. When we go to query them, there are ways to our format, the dates to express them in any style that we like. Then there's date time, and the difference between date and date time is the date Time consort Not only the date, but the time off that dates African store an instance of time right down to the thousands of a second. So, for example, you could look at a daytime value. It could well be just the date, in which case that 12 of the 31st 2019 and a bunch of zeros representing no time component at all. Or it could store the date and have the time tacked on the end of the in this example, down the one minute past 10 36 seconds, 360.827 seconds. So let's get stuck into querying tights. So let's check out some of these date time data times a ZAY said the most of the time in SQL Server. It is daytime data types that are used and not the date. So we're gonna focus most of our work on the date time data time. So we'll start browsing the database in Object Explorer and we'll get into get old Production Doc product table. And if we look down here, we've got three date time data types columns when his cell start date. The other one is cell in date and there is modified date. We're gonna focus for the time being on cell start, date and sell. Indict. So let's start a new query window, and we'll start off by selecting all the columns in production dot product. Okay, so I've selected production dot product of Given an alias. And as we saw in the previous lesson, it is good practice, and I'll get you to kick doing this for the rest of the course to perfect your columns in your select laws with the table name or table alias in this case, paper dot star. And, as his good practice demands are ordered by the prime que And if we scroll over to the right here, we've got sell stock tight and sell indict, and we've got a lot of values and sell, start date and some values and sell in date. So I tell you what. Let's just focus on self start date. So to make life easy for us, let's put cells. Start died at the very far left off select clothes. Now, as you can see in the values in cells start date. The data is formatted in management studios results, greed out like so year, month, date and the time component. Now that is really just the way it's displayed behind the scenes. It's stored quite independent off locale, specific date styles, eso When we get into some advanced querying, it's perfectly possible to display that in any style. We wish American style British star whatever. So if we could just take that as read that it's this is just a style of displaying for the purposes of management studio and we can manipulate Manipulate that as we want. Sorry. Let's say we interested in all the rows where the cell start date is 30th of April 2000 night. So this is how we do it. Okay, what have I just done now? We've got air results there, but if we look to the style in which I passed it, I have stopped up past it in American style being month, date, and the year. Essentially, because Esquivel survey is an American product and in terms of passing values, it follows the American style off of expressing date. But if we just look at the form in which I have passed that value for comparison. It is surrounded by single quotes like a piece of text, but it's gonna be formatted in a certain way. Month, date year, separated by forward slashes. So let's say we're not just interested in the cell start dates that are dated 30 the papal , but the ones that are dated on or after 30th of April. And that's quite easy to do, which has changed the operator to from equals two greater than a equals. Now that's a little bit different to the stars we've been dealing with. Where is even though it's treated almost like a text value surrounded by single quotes were using Eric medical operators. So let's just run that And just Teoh make it sort of a little bit easier to interpret will change the order by that it will order it first by sold it Salt's not date and then by productive I D. Within that section of self start date and little kind of make more sense. So we'll just execute that. And if we scrawled in, we've got 30 the papal there and then we got other dates. 31st of May 2011 2012 and someone and so forth. Let's say we're not just interested in the ones honor after 30th of April 2008 but those ones up to and including 31st off May 2011. So just those up to 31st May 2011 and nothing beyond that, Well, that's is enough to do. We use an end condition, and as you can see, that probably makes perfect sense. The start date has to be on a rafter, in other words, after or equal greater than or equal to. And to get the to date and to include that to date, it's less than or equal to. Let's just execute that and see what happens. But before we do, we'll just check out record numbers. We've got the full set of 504 product records there, and if we execute that, we've got swelled in 293. And if we browse that data there, we go up to 31st of May 2011 and nothing after that. Now there's another clever way to express up those kinds of conditions where we're looking for a value to up to another value and including those two values we're expressing. We were trying to get a range between two values, and it's using the K word between. It's very simple to express, and I'll just demonstrate it. Okay, what have I just talked where Cell start date between that and that, and it means exactly what we had before. It gets us everything where it's equal to or greater than that less than or equal to that same thing. But it's expressed sort of more in plain English language, and it sort of speaks to how SQL is engineered around plain English language, which makes perfect sense when you're rated, Let's executed and see what the results are. Captain, if we scrawled in again, look at that. We've got 293. That was the same value we had before and every scroll up and down. We've got the same sort of range of values 30th of April 2008 and 30 fist in my 2011. Now that between operator can be applied to actually any sort off data type, really anything that can any sort of data type that involves sorting, so that includes numbers, text and as we've seen dates Now let's say that we're interested in the cell start dates that fall within a particularly year. Let's say 2013 now I'm gonna show you a a slightly long way to do it. And then a shorter and more powerful way to do it. The first way will simply go between way. The date falls between first of January 2013 and 31st of December 2013 and then we go. All those dates that we can see there are in 2013. Well, there is a more powerful way to do it, and that's using a function that extracts a component from the dates. Now I'm going to get rid of my way, close he and get back to the full set of data, and that will help me demonstrate things here. So let's say we want to come up with a column that shows the year component off that debt. Let's just do that is an exercise. Well, there's a function that does it for us, quite simply. So I'm going to start on a new line here, and the function we're gonna call up. He's date part, you know, if we look at the metadata there. It's asking for a couple of things, an interval and an actual date or date time value. And you know what? I'm just going to type it and then we'll discuss it. I got what about just written date part, and we passed in a natural text literal called year. And in the date so date part can extract any part of a day. That's the second parameter, and that part that it can extract is specified there. It could be the year, the month, the date the day of the week. And if there's a time component, will come to that later on. If there's a time component in their it, it can extract any part of those the minutes seconds, you name it. So let's just run that query and say what it gets us. Check that up. We've got the year. The 2000 and eight Part of that died is expressed in its own column. Now let's just take this opportunity, Teoh. Look at the documentation surrounding the date part function, so I'm just going to go to the official Microsoft Developers website about date part and let's just look at some of the instruction manual surrounding it. It takes two parameters. As I said before a date part and an actual date, it can actually take a date time. But if we look to the date part arguments that it can accept these the sort of values the year, the months day wake, a note that it can accept other abbreviations as well. For example, it can take why, why, why, why, instead of the literal word year? And as you can see, it's quite powerful that can extract any component off a date time, a date or a date time right down to the millisecond or the nanosecond. Even the return type is int, so because it's in the end, every one of these things is expressed as a number. For example, the day off the day is expressed as 1 to 31. The week is expressed his 12 52. You get the idea now, this date, part functions gonna have to come with a bit of ah, a caveat here. Up until now, all the functions I've shown you are ansi compliant, and they work the same across all platforms. My school Oracle SQL Server. That's actually not quite true off the date part function. But I'll show you anyway. In SQL Server and Oracle. You have the date part function. It works identically. In my school, there is no date part function. The nearest equivalent is the NC compliant extract function, which physically works identically. The Syntex is slightly different. It works, extract the date part from and pass in the date. And there's an example there year from and the Duchess de. So if my schedule is your platform, you should be using the extract function. They're coming back to a query. Bet you can guess what we're gonna have to do next. To actually get through the records with the year is 2013. And that is, take this expression here and build it into a wear cost. So let's do that. Okay, let's execute that. And then we go and we're just limited to the 2013 Now. The die part function is incredibly powerful in that lets it can solve other problems for us. Let's say we're interested in getting all the dates that have a month of may not nothing to do with the year, just the month of May, so whether it's May in 2013 or May in 2012. That's the one we want. So let's just start using a month by putting its own column and putting month into the way claws and say how it works for us and that you see it. We've got the month extracted from that 2013 year, but let's just get rid of this wig laws so we can see the entire range off dates and years . So let's just take this month expression. Build it into the way close so we can get the months we want. And there we go there Roll demise Month equals five in the year 2011 2012 2013 You get the idea. So it actually gets is a better result than what we were using before that between where we could go from this day to that date to get a range of dates, which isn't gonna work here because the selections that we want to actually scattered across a range of many years, so that just demonstrates her powerful date parties. Now this extracting number parts of a year is fine. But what if we want to create a nicely formatted string off the entire date. Say first off March 2013. There's a business requirement for it, but we actually want to use the word march, not the number. How can we achieve that? Well, there's a similar function that we can use called date name. So it the date nine function is pretty much identical to die part, but it returns text instead of a number. It can also return proper names. Sercan return. If we pass it a month of request a month out of a date, it will return the word December instead of the number 12. And the Syntex is identical in that it is the Diagne. I'm the part that we're asking for and the date. So let's go right ahead and use date name to extract the month name from the day. And there you go. That's the month expressed as the actual name My You know what? Let's Ah, it'll probably make more sense if we get rid of this. Wait, what was here? So we can see all the date that we go. We've got April's my eyes and someone and so forth. Now let's apply that to the rest of the date and get strings for the other components are day and year. Okay? And let's see what that gets us. How about that? Now? Have also added, I've got the day here twice off those once expressed using the Component Day, which gets you the number and the weekday. Actual name is in Monday, Tuesday, Wednesday, here, so we can use all these components to construct a string that is Wednesday 30 April 2000 date and someone and so forth. So let's give that a go. And there we go. Check that out. That is a fully formatted died Wednesday, 30 the viable 2000 night and changes accordingly. That is a pretty powerful usage off date night. Feel free to save this file for later reference. Will just go file save as Andi. Save it anywhere you like. Let's just call it a date. Storiesq. You will Now for the next Listen, let's just clear all these expressions and we're going to selected the far left here. The column modified date that his way over here in the right. So we're going to do some stuff involving that, And while we're here, we'll use modified date in our order by clause. And let's just see what's in the We'll modify date is actually the same data type as the sell date that we've been working with so far. The only difference is with the data, because remember, they are both date time. They're capable of storing not just the day but the time as well. And that's exactly what I've done with modified date. He okay, so let's just say we're interested in getting all off the records where the modified date is just that eighth of the 2nd 2014 Let's just give it a go right now. You would think that would work because we've got eighth of February. That's like the food they ate the February 2014. You would think that would get us pretty much all these rosy. But let's see what happens. No, it doesn't. It doesn't get us anything. Why? Because And you know what? I'm just gonna comment out that way clothes and get us air records back because that's not eighth of February 2014 its eighth of February and 10 o'clock in one minute and 36 1 or 27 seconds. Now that's a bit of an issue. Supposing I mean, do we have to get all these times? And what have you what we don't want to Because I can see what's happened to some of the application is obviously updated is inserting the date and time in there and you could have some wildly varying times. You may have eight with 2nd 2014 and 10 Taiwan and 11 or six. Do we have to query literally query for all of them will. No, we don't. There's a bit of a trick to get around this, and it involves the function that we learned earlier in the course called Cast. So this is how we get around something like this. Now, bear in mind that a zai explain, there are to date data times the state time which was seeing here. And then there's date and date is only capable of containing a date night time. So what we're gonna do is we're gonna take modified day. I'm gonna cast from a date time into a plane diet and this is how we do it. Well, first, let's just comment out. Our weight was there and gets alive values back. But this is how we're going to create this costed column. Okay, It is a date as opposed to a daytime, but it is just the date component. So I think you consider I'm going here. We can build that expression into where we're close and get what we want. And there you go. Now I'm going to introduce you to another date out related function, which is extremely powerful, which can get you today's date, and I'm gonna show you how to do it. And it's called Get date. I've called it today and then we go its third of during 2020. That's today and the time here in Australia in Melbourne 14 2041 seconds. And yet now there's a couple of special things about the get diet function. It doesn't accept any parameters. So it spit something out even when we don't put anything in. And the other special thing about it is that it is non deterministic. Now a deterministic function is one that always returns the same value if you put in the same inputs. A non deterministic function is one that doesn't always return the same value. If you put in the same imports in the case of get date. Well, it doesn't accept any imports, but basically what that means is, if we were to run it at one time, we were get one value. And if we run it at another time, we get a completely different value all by itself. So for example, photo run this again. How about that? I get a different value because it's four minutes later. So just a bit of trivia. They just to make you aware of it. Now, just get you to watch this exercise And don't necessarily shadow me because this involves some a data that I've specifically modified in this table which will not exist in your adventure works. So I'm just going to get rid of my wear clothes here. Okay. Now, with the modified date of this one single record, I've changed it to today's date. Well, just the date component had left the time by itself. Now, let's say that I want to write a report to get me all the dates that have been modified today. Now, to get two days, Davis, we've said it's get diet. So you might think that we should do it like this. We live on a five day equals today, and you might think that would work and we would get product idea. Number one returned. But let's say no, we don't get anything. Why? And I'll just deactivate my wear clothes they because it's this time factor. There's a time factor in there and there is a time factor in today's day and they're not going to match up. So how do we do that? Well, we can do the casting again. So let's do that and they we got now I could actually get you know what? I'll just cast that to die expression column as a date, Teoh said. That sort of makes more sense and you can see what's happening that has been matched with the effectively with that that casted version and the two match up. So that is a very, very important affected that you've got to consider when we using today's date. Today's date is not just the date, it's the time. And if we do want at the date just the date for comparison or perhaps to what to drop on report, then you should constant like soy. Now I'm gonna have to just get you to watch this. You won't be able to participate because this involves using a record with a cell start date value that I've deliberately manipulated specifically for this lesson. I've changed that to fourth off June 2020 which is tomorrow's date. And let's say that the exercises I want to develop a query that will return records where cell start date is dated tomorrow. That's tomorrow. Azzan the relative. Tomorrow is in the day after today, Whatever today, maybe so. Let's say we're developing this query that maybe run tomorrow or the day after or next week . And we want to always return records that I dated that date Today's died plus one day. So how did we do that? Well, let's just put get date into the solid close every earth instead. Ized ight. Now, you would think you might think that we should just simply build it into the Wake Wars. Well, habitual. It's not gonna work. And it's pretty obvious why? Because if you compare that to that, you're gonna have the time component. Correct? It doesn't. So what we don't What we need to do is to eliminate the time component by casting this as date instead of daytime like we did before. And you know what? I'll just comment out my weak walls. Great. So that is now a date only on let's compare it to sell stock diet. And of course, that's still not going to work because that's stated today. That's dated tomorrow and the two don't compare. So how can we make this compare? Well, basically, we have to manipulate it using a function called date ad and date ed can allow us to take a date and move it to a certain number of units. Be it months, days, years. It's very flexible. Move it. A certain number of units of, ah component into the future or the past, and I'll just jump straight in and do it. So, you know, I'm gonna comment that out so we can get the full table and I'm gonna go over here and on its on line. I'm going to use the date add function now, but admit metadata is come up there, and the 1st 1 that is prompting this for is what is the interval. In other words, what is the component of the date they were going to manipulate? Well, that is the day a little lower case. The 2nd 1 that were being prompted for is what is the amount that were incriminating or deck preventing? And in this case is one. And lastly, what is the actual diet that you want us to mess around with? Well, it's cellstar tights. Let's just put that in and we'll call that expression as future. You know, I'm like that caps and let's just see what happens. Okay, what's happened? Well, it's self start date, and it's increased by one day. It was the fourth of June. It's now the fifth of June. You know what? Let's just have some fun with is. And let's just make that a year instead. Look at that. There was Fourth of June 2 20. Now it's 21 basically all of the components that we saw in date, part and date name in one of those day, year month you can plug then into date ad and manipulate that particular part. So let's just put that back today and we're going to turn. Then it one minus one, which is perfectly legitimate, and I'll change the expression name to past date. Okay, check that out. It's taken the fourth adjourn, and it's brought it back to third of Gen. Now I reckon we can put that into a where clause and we'll get returned back. The daughter we want and hi! Presto. There you go. Now I'd also recommend investigating are the date def function, which is are kind of very similar to date Ed, because while date ed returns a date that is manipulated, this just takes two dates and gets the difference between the two. So one reports of difference and the other one applies a difference. So date def asks for. We got in here the date parts, such as months, days, years, the date that you're starting the account from in the end date and you could get compared to dates and find out what is the number off days between those two dates on the number of months between those two dates, it's very, very powerful. So what recommend having a look at that? So let's just recap everything we've covered here in diets, which is fairly substantial. We talked about comparing date ranges using greater than a less then and using the between operator. We talked about the date part function, which can extract any part of the date. And the output is in a number, and we can use date name for extracting the text part off a date such as the day of the week or the month we talked about casting date times into what date for purposes of comparison or for returning and printing just a date on its own. We discussed the get diet function to get to Dies Day, and lastly, we examined the Date ed function, which can take a date manipulated by forwarding it in the future or past any component and date. Def. Very similar accepted calculates a difference, and it's time for an next to Simon. So download assignment three dot zip and inside you will find the business requirements in a Simon three dot pdf. Enough supplied a suggested solution in the file assignment. Three Suggested solution dot SQL and the results from that are in assignment. Three results dot x l s exp 23. Recordsets - They Help a lot: now what in this lesson I'm going to show you is a very powerful technique called record sets or otherwise known as a liest record sets. They're quite powerful. They can save you a lot of time and make your code easier to read and better organized and easier to troubleshoot. So I'm gonna get us, get you to open my person query. And if it's not in that state, I'll get you to just put it into that state title, that full name expression through the asterisk on the end, and I will just start by tightening things up a bit. I put the full name on its own line, and I'll put the star on its own line. I will also get you to implement what I've told you is good practice, which is too perfect. All the column names with the table name or table alias in this case PP. So let's just do that and that still works. Now let's say there is a business requirement to come up with two versions of full 91 is as we see there, and the other one is the full name is you see there but with the Suffolk's upended on the end. Now, to make this easier to understand, I'll take saw fix. And I'll just put another selection of that after full 90. And just for the purposes of this exercise are eliminated all the Knowles so that we're just working with ones that actually have a suffix. So we'll put that into the were close Now, just to make it a little bit easier to read. Also, put the expression name on its on line There. Now I'm gonna show you the traditional way to do it, which I've shown you so far, which is slightly longer. We would do it like so and then we go. So the requirement might be that this'll full name is supposed to be on the header of a letter and this is supposed to be the signature or something like that. So what we've done is we've literally taken a copy off their first expression. The foot, the basic four line. I copied it down here and put Suffolk's on the end. Now that's a little bit duplicative now. Yes, we copied and pasted it, but supposing that the requirement for working out for name changed because we wanted. So, for example, changed like we didn't want the first indestructible. We wanted the full name, the full first name, and because that cards there on that cuts, they would have to change it twice, so that's a little bit inefficient. I'm going to show you a more efficient way to do it involving record sets, and I'll just do it and then we'll discuss it afterwards. Now I'm going to do a little bit of cleanup here. Noticed I've got the title and the Suffolk's are twice in the record set once there and once in the rest of the rose after the right, where I did the pp dot star thing. Well, I'm going to get rid of those, and there's a reason I'm doing it and I'll explain towards the end of the lesson. So I'll just get rid off that extra title and they'll get rid of that extra instance of Suffolk's. And I'll just execute that and great. So I will just save that is more person query. Now I'm gonna make another saving of that as file Sybers, my person, Queary Rick could sit. So now I'm going to use record sets to make this a little bit more efficient. Okay, so what's the result? We now have practically the same result as before, but it's looking a little bit convoluted. Let's break it down. So I've got my select clause. Let's just ignore that for the moment. But we've got the from cause. Then we've got how much is open bracket, a close bracket and then as Rs one and then in the order by we've actually replaced the PP table name with Rs one. What's inside the brackets is a query on its own. It's a standalone query on what we've done is we have taken that record said, which works finding itself. And I can prove it because, you know, you can highlight a piece off a section of SQL and executed on its own. And if I do that, that actually works. That sits practically the same query we had before in my person query. But what we've done is we've taken that query and we've kind of turned it into a virtual table in its own right. We've put in brackets and we've a liest, the entire record said so, like we can alias tables as we've seen here we can. Also alias record sets actual queries themselves, so the bunch off columns and rows that is returned by the query is now kind of like it's given a name. It's called Rs one. The convention with record sets is that they be called Rs four record set and a one or two or three depending men. That's not a hard and fast rule. If you give it a name and it's meaningful and someone else comes along and can look at that and know what it means, that's perfectly legitimate. We could call that, you know, extended person or something like that. But for this exercise, let's just call it our wrist one. So we've taken that record said that we had before, and we've called it Rs one. So how do we actually get data from it? Well, because that's effectively a ah table. We can use that in their select clothes. So we've gone select iris dot star, and that is the same as saying. Give me all the columns from this record set side by virtue of doing that, it selected full name and it selected everything from Baby dot Star, and that's what we're saying now the really cool thing about this is that any made up expression within that record said that can be referred to by its name, just like any columns. So effectively, we we've made up a virtual table and we've made up a virtual column now to give you an example. If I go over here and I select rs one dot full name on its own, there we go. Now. Of course, it's actually there twice because we got the iris one dot stark. But you can see my point. I can select that and as on its own as any other columns. So it's basically a virtualized of made up column, and I can select it now, thanks to the fact that I can select it. I'll just get rid of that now. I can take that and build it into another expression. In this case of taking Iris one dot full name, which is the same thing is that whatever the result that is inside that record set, just take that and a pin a space and upend Suffolk's, and we've got a new expression column so you can see the benefit is the big long, convoluted expression for full name. I only had to write that once, put it in a record set and then outside of that record said, I can refer that to a column and do whatever I like to it, and you can see the benefit. It's more efficient. I can, for example, change full name and get rid of the title. And you know what? Let's just do that. Let's say the title. There's a business requirement. The title is not to be part of full name or the Suffolk's expression, so I can just get rid of that. Execute that, do it once and it's built into full name and it's built into full name suffix. Job done. Now I'm going to explain why a little bit earlier, you saw me get rid off the second instances off title and Suffolk's and from within this record set so before, just to kind of put it back to the wet Waas. It had peopIe dark title in it. It also had Suffolk's. But look, let me just put paper dot title in there and let's just say what happens, Bank, We've got an era. What does it say? The column title was specified multiple times for Rs one. And they're absolutely right now. If I do that funny, select text and execute, We do indeed have title twice. We've got it once there, courtesy of their paper. Don't start and we've got it here a second time, courtesy of my explicit selection of it. Now, why is that a bad thing? Well, basically, when you turn this into its own table and we select from it like I respond dot star that confuses SQL because you've got two columns. The same name here and here. When you go rs one dot start a new select title. Full nine. Business injury. Bloody bloody Block s people doesn't know it. Well, hang on. If you've got titling here twice, it doesn't know if you mean that one or that one. Let's say I just got Iris. Want top dot title by virtual explicitly selecting that s cable. Doesn't know. Well, hang on. Within that set, you got title here. You get title. He If you're selecting title, which one do you mean? I don't know it. So too have the same column listed twice in a record set that is actually illegal in SQL. It's perfectly fine in a select statement on its Aren't. But if you put it into a record set virtual as table, that is a no no because it confuses the heck out of SQL. So let's just fix that slightly. Put things back the way they were. I'll get rid of that second instance off title, and I'll put that back to start so that selecting all the columns in pe pe plus our expression column bang and a call suddenly fire life. We've got the one that we created outside of the record set. Now we'll also draw your attention to the fact that any references to any of these columns outside of the record said you have to update the table name accordingly. It's it's no good to have safe PP here because outside, literally outside of this record, said S Cable doesn't know about three person dot person pp table it effectively doesn't exist. Outside of this, we've turned the whole thing into Rs one. It's literally like changing your name by deed poll. So if we were to put pp dot business entity ideas we had before, that would be an error because SQL, outside of this record, said it doesn't know about people would be going home. What that started my friend Klaus Iris. One is but not pp, so it's a potential trap they would go to be aware off. Now. It's also perfectly legitimate to treat this table like any other, and we can actually use it to join toe other tables, never gonna practice joining between a record set and a table by using the table person dot email address. Now, if I just show you this other query window where I've selected everything from person dot email address, take it as read that you've got the email address there and it's got a business into the idea, and that is a foreign key. And it is acting as a foreign key between that and the person table and the person tables Business Entity I D. So take that is red and will join to it accordingly. So let's do it and let's just execute that and hey, presto, we have joined from a little virtual table, a record, said John. Ditto email address, and now we're able to select from that email address table and there you go, So that's all quite powerful. In fact, you can even it is even possible to say, Take this table here and virtualized that as a record set in its own right and then joined the to say we could call that Rs two and we could join Rs 12 Rs two. In fact. What we just give that a go. And let's say we're interested in only joining two email addresses that contain the woods. Stanley, let's say Stanley's part of the aim l address you never know. It might be a requirement. Let's just give it a go. Now, I'm gonna take the liberty off changing that, too, and, you know, join. So we actually get the nervous combination of that to that. Let's see what happens. That's gonna be pretty limited. Um, yep. There's 24 records there, and if we enter joined, we may get a possibly a narrower combination depends on the data. So if we do that, let's just see what happens. Check that out night that we can select at the very top here. I have selected the email address from my IRAs to so weaken again, we can refer to other a list record sets within query just like we did the 1st 1 Very powerful indeed. I'm sure you look great. Now, as one final our tip, you may have noticed that. Well, hang on with earliest tables here. We just had the table name and the alias and we didn't have the wood as in between. Where is when we a list The record set. We had the record set on the name and we did heaven as in between having the as is optional whenever you're a leasing either a record set or a table. So you know, Alias ing the table is perfectly legitimate. Open as there. It works. Fine. But you know what? That kind of the convention these days is not to have the eyes because that's just a little bit easier and quicker to rate. Sorry, because you don't really need the as when your alias thing. It's perfectly legitimate to not use as in a leasing out records. It's so I'll just get rid of that and I'll get rid of that as and it is perfectly legitimate works fine. It is up to you if you want to use the wood as or not. Whatever you feel, make sure code more readable or easy to remember, it's perfectly up to your. So let's have a recap about what we've learned about record sets. Record sets are a liest, virtual style tables. Those virtual tables can allow us to reuse expressions within those records set. They behave the same as tables they can be selected from that could be joined to, and the columns and saw them can be used to order by. There is a heavy and that within a record said we cannot have duplicate column names inside them. 24. The UNIONS make us strong!: So now we're going to come to the topic off union queries. You're probably wanting what union queries are about. Nothing to do with industrial relations Union queries. Ah, will you take record sets and you effectively stack them on top of one another? So whereas it's a different way off, connecting records were connecting them sort of vertically and not horizontally as we do with joints. So what we mean by stacking, as opposed to joining while joining, is the act of taking two tables, aligning them side by side. And the records or columns returned in a result, said literally side by side that column from Table one and that come from table to and you would read them across from left to right, that sort of thing. Now, by stacking them, we basically mean doing this using the union command. We would take this record, set or table, and this record said, and literally stack them one on top of the other. So to represent that visually on bear in mind, that's that's table one, and that's table toe and notice the difference in the values there in the description. So if we were to union, joined those, the result would be this, one record said, with Table one and table two aligned vertically so literally at the two columns are almost merge its two columns, and they emerged into one and their records stacked on top one on top of the other. Now, to demonstrate, I've created two tables manually created two tables specially for this exercise. Again, you will not find them in your copy of eventual works. So what I've called them table one and table to, and they're very simple. They have two columns, a prime key and a details column, which is just a voucher. So let's look at the data in those two tables now. As you remember, it is perfectly possible toe have to select statements in the one window and then just execute. The whole lot will see the results on top of each other. So as you can see, Table One simply has those three rows with the details, ABC and table to as a and Export said. And now to create a union, it is very, very simple. We just have to select, and we simply put them together with one command union, and as a result, we have both sets of data stacked one on top of the other. Now there are actually two union commands that are similar but different in a critical way . One is union. One is Union Coal. Well, let's do union all and see what happens. Well, that looks pretty identical. So what is the actual difference between union and union? All well, the difference between union and union all is union. Eliminates duplicates. So amongst that combined record, sit. If between all the values returned on all the columns on a particular Roy, if when comparing it to other rise there any duplicates those duplicate rose get eliminated . If it's union all, it just returns everything. No matter what, including Jew Pickard's. It's just everything stacked on top of everything. No eliminations. So where does this elimination of duplicates count me in? Well, I noticed that when I got union all and there's no elimination, there is seven rise. So let's get back to union and execute that. Hang on this still seven rise. What's this about? Elimination of duplicates? Well, the sex and never gonna be any duplicates because we've got the idea in there, and the idea is actually making them are unique. When you compare them to details, there's only ever 11 I. There's only ever 11 X orbit from different tables there, so there's no duplicates. Nowhere the elimination does come in is where we've only got one column and there actually are duplicates amongst that sits. So let's go back to union Or and But this time, let's just select the details column with union all there. No elimination. So I will just go details and day tells. So let's just check this out. Okay, we've got seven, writes, no eliminations. But if I go union, what's just happened? We've only got six because remember, there was two A's, the one from table one on one from table to Now there's only one. So that's the elimination. So, in my opinion, I have only ever used in practice. I've only ever used union or I'm really not comfortable with the union and eliminating duplicates. If it eliminates duplicates, there's a chance you're not going to know what's being eliminated, and you may actually want those duplicates so personally I would return everything. So I'm gonna go back to everything here and if you need to eliminate duplicates. There's another technique you can use using. Grouping Grouping will do it in a later lesson. So grouping you get to have that bit more control over what duplicates you eliminate. So for the rest of this course, I am going to use union or and in the course of your work, I would recommend using union. Or because that's what I've always used to write Montag Career. Now, when using union sits, there's a few rules. So we'll go through the rules governing unions and give examples. Rule number one is that within each union sit, each set has to have the same number of columns. So I'm going to put it back to other way. I was selecting all columns from my two tables here, and remember that all record sets have to be returned in a grid. It kind of wouldn't make sense if we had two columns returned up here and one color return down here. The two just don't match up and you don't form and even grid. So for that reason, it's illegal so far. But to select both columns up here and only one column down here, I get an error. All queries combined with union into sector except operator must have an equal number of expressions in the target lists. So we better put things packed away that way. So and they would go to columns two columns, All good. So let's come to rule number two. The order by clause has to be at the very end. Now, the thing about a union queries that were basically taking this big chunk of select statements and which raining it is one big combined sit. So as with order by clause, is the order by a supplied to the entire said it takes all the roads and orders them, so I could It wouldn't make sense for us to have, like, one order here and one order there. It just doesn't It just doesn't work. So if we go and try and execute that, we get an error incorrect. Syntex need the keyword union because the queries gone will hang on. There's a union And what's this? That audible eyes don't work into the scheme of things. I don't expect that. So we're getting an era, so we'll just get rid of that so we'll just go or, uh by and they we got. So the order by clause, just to point out, is as clauses go, that is the only one that is subject that to that stipulation that it must be down the bottom in a union sick. All the other causes aren't subject to that rule, and they can stay within their set. For example, if I want to apply A were close to that set on, perfectly free to do so. And I put it in the usual place I can put away close down here in this set as well. The from clause. I can join that to other tables. In that set, I conjoined that to five tables if I want to, I don't have to join down here. I can have functions up here and functions up and no functions down here as long as its subject are the rules about the number of columns, and we'll come to that in a minute. I'm perfectly free to do so. So what? That order by thing about being down the bottom. That's the only clothes subject to that. Other than that, you can arrange your individual slick closet as you see fit so Let's come to wear next rule . The top road determines the column names. Sir, we're actually sore An example of that with a robocalls. So let's go back to that now. Notice in their order by clause. Here I said, Order by T. One idea That's order by the primacy of table one. I didn't say table to what happens if I use Table two. We get an era the multi part identify T to I d. Cannot be bound the reason being, and I'll come up fix my querian explain. The reason being is that it doesn't know anything about t two dot i d is because they're in this set. There is no t to i d. There's on the 81 i d. That is called t one i d. That is called T one dot details. It's not t two, because as far as the combines it is concerned there is no t to the name or the table. Perfect r t one because T one is the union sit that is at the top. The union said that is at the top literally sits the rules as to column names. Now I'm gonna give you another example. Let's say I won't have my star. I will select the actual column names again. Perfectly legitimate. We can actually give columns, native column, names, other expression names, for example. I contact my data files, and I can give that the alias My details. There may be this giving native columns on Alias. There may be a reason to that. You may want a nice plain English name when you a supply this record sent to a crystal report. And when you drop that column as a field onto crystal reports, you may want a plain English name for the object and for the prompt next to it. Let's have an example of what's happened here. How does this affect our union query? Check that out my details again, the top record said determines the column names. No one think you may have noticed is that thanks to ordering by I D and remember that the two tables have the same idea numbers. We've actually got a bit of a mixture here. We got toe some of the record from table one and table two mixed up. I mean, that's obviously from table one, and that's from table two or maybe the reverse to actually can't tell. So it Z give us a bit more information about what records coming from, where we can put a column in the that can identify which title it's coming from. And it's just using a constant expression. So what? And then we can order by that. So let's give that a go. Okay, so what have I done? I've got a constant expression here that just says table one in another. Medani That's his table to cause that's table two. Now we're gonna order by, not by i d, but by the table name column. It gets that column from up there and we'll just execute that. They're the ones from table one. They're the ones from table to and visually, that condom makes a little bit more since so so far we've been querying tables that have identical structures. All the columns are off the same data type. What happens if we query Ah union query of two tables that have slightly different data types? Well, that brings us to rule number four. The column types should be the same. They should be the same. I don't have to be the same. Let's take an example of one with a column. Names actually are not the same and say What happens if we working with unions now? Ever hear an object Explorer? I've actually reconfigured the tables are table one now has 1/3 column code that is an integer table two now has 1/3 column again card. But this is a botch off 50. So let's have a look at the data. So I'm gonna take my previous query and I'm gonna reconfigure that Teoh, Just query the data in these two nearly reconfigured tables here. So what I'm gonna do is I'm just going to remove the union cores and query them independently. And I'm just gonna get rid of my fancy new column titles there. And I'm gonna add the new code column and let's just see what's going on. Okay, so we've got a code column there Now The Kurd column in table one day is an indigent column , and we've got energy values there. As you'd expect, the card to the code column in Table two is actually a text vodka column, and we've got some numbers there, but they're actually stored as text and then he got some text. Now, with that said, What happens if we actually try to combine those records said into a union? Let's just say what happens, Oh, dear, we've got an era. What is the era? Conversion failed when converting the bar chart. Value some text to data type into. So what's going on now? What I've done here is I've got the query. We just ran here in one window and I've got the old theory which showed us the raw data in another window. Now, if we look to the error message, what did it actually say? Conversion failed when converting some text to data type int. So let's have a look. He will these Arendse and these a text. So what it's doing is it's trying to convert all these values to an int, and it's failing because while 100 converts to one in because it's 100 SOEs, that and that's ah number that justifiably convert. This, however, obviously cannot possibly be converted to a number because it's alphabetical characters. But the question is, why is it trying to convert these things to interject in the first place? I mean, wouldn't it make sense just to convert the whole lot to text because these can be converted to text. And these cannot be converted to interject thanks to this guy. So why is it going from bar chart to interject? Well, the reason is is because of the president's off data conversion. Now, if we come back to the Web page that depicts data type presidents in SQL Server and we go down to this list, we saw this in previous lessons. If we look down here to watch up, that is at 27 on the data conversion charts, so despite and it would come up here to end that's number 16. So an int beats of archa. So when you've got a union to query and you got a mixture of data types, this president's scheme will kick in. INTs beat var Chaz. So it has a mixture of our child's Indians. It will convert everything to an end, and that's exactly what it's attempting to do here. And it's failing on this guy because that contained non integer characters. So coming back to a list of union rules column types should be the same. And if they are not the president's rules apply as we've just seen now in practice, you don't wanna risk data conversion problems like this. So it's always a very good idea to make sure that when you are combining columns in a union query that the columns are all off the same data type. And if they are not, then you can use the cast command to cast the columns in the way that you want to avoid problems. So coming back to wear raw data now as we touched on earlier, If we're going to combine these two columns of different data types, it would make more sense to convert this column in table one to a text data type rather than let SQL server try and convert this column to an integer because that is going to completely file when it comes to this pace of data here. So why don't we do that? And we'll convert this column in the top record set toe of Archa and see where that gets us . I can't that old works. We now got a text column up here and a text column down here. Let's integrate that into a unionized query and see where that gets us so I've gone back to my unionized query, have now copied and pasted are that expression up here. So I reckon that's gonna be perfectly legitimate. Let's run the query and see what happens and high priced Oh, that's completely worked. Uh, that's text from Table one. That's text in table one. That's all takes there they are, effectively the same data type, and that all works now. I've taken the liberty of creating 1/3 table that has the same data structure is table to codas of Archa. It's perfectly possible to have 1/3 selection clause within their union to query, and you can have as many as you like. In fact, it's practically infinite. So let me take my existing union query and add Table three into the mix, and then you go. We've got another section down here, which is all of table three, exactly the same, and it is just another query, but it separated with the union, or so really you can have a Zeman E selection clause within a union taquerias. You like the sin Texas just simply selection, said Union or selection said Union or selection said as many as you like, as long as they are separated by a union or and a subject of the other rules we've discussed now. It earlier in the course we learned about a leasing record sets working with record sets. And that's gonna help us out with a couple of points with this query that we see in front of us. Well, for one thing, Heart of the order by clause is using three i. D column on the table. Prefix is taken from the first selection, said it's using T one. Visually, that's had a little bit confusing. I mean, t one we're ordering. Not actually not ordering three. The top team one records it. We're ordering the whole darn thing. So having that there is kind of a little bit counterintuitive, and it's also counterintuitive when you know we actually don't have a table perfect for this expression. Call them up. That's all. P. So what? We can do it, too. What sort of plain things up of it is to put it all in a record set, and that also gives us a bit more flexibility in terms of selecting what records we want, a bit more power So let's put all this in on a liest record, said, cleaning up a bit and then have a bit of a bit of a play with it. Now, bear in mind that these select statements behind, like any other select statement, we could refined down the selection of data within that select statement by applying aware claws and applying a wear clothes there and there if we want. And that's perfectly legitimate. If we want to apply one single wear clause to the entire city, record sets actually help us do that. So let's just apply record set and I see Here we go. So we've done exactly what we said. We took that entire set that his union surrounded it with brackets, uh, and an alias name of Iris one selected from it. And any clause that applies at the Senate level like the order by we can perfect everything with Iris one. And that kind of makes sense. Now let's I, for example, we want only the ones where I d. One of these values here equals three. Now we could apply away close to each individual set way calls there were close there, which says, where R D equals three or we can do it once at the actual alias Tset level. Like so. And there we go. That's Ah, a lot cleaner. Ah, lot easier to rate and fairly efficient. Unfairly powerful. Well, this is all very fine and well, but let's do a practical example with really data. So if we look over here to the Object Explorer, we've got the transaction history table, which is actually a big, massive table off a lot. Work orders and sales and purchases or lumped into the one table. We've got toe sort of date column there. Quantity column, that sort of thing. Now that table actually represents, and I'll save me the trouble of going to the data dictionary to fund this up. That transaction history is the current history for the current year, and if we look down, we've got a similar table called Transaction History Archive, which actually has exactly the same columns and I can to save you. Looking at the data dictionary that is the history off or previous years. That's what's called an archive. So let's say we've got a business appointment to come up with a report off all transaction history records from not only this year but previous years. In one query, I'm we can do that with a union, so let's do that. So what exactly have we done here? Well, we've queried the transaction history. A table here selected some columns, the idea that day, the quantity, that sort of thing. And we've done the same query down here, but from the archive table and selected the same columns combined them. And I've made up a special expression column here with a text Constant up here is current and archived in order to were distinguished the to. And that's actually really important when you're working with that unions record sits because if we didn't have this source column looking at the records, we probably wouldn't know what source table they were from. And if we wanted to track down the actual physical record itself to examine it in detail. Looking at that, we wouldn't know where that was from. Unless we had that we probably could have. We're looking at the date, but that's assuming we don't know. I dated that well and having that source call them, they absolutely helps and will help us if you're using this for a report, Ah, crystal report or something like that. Knowing where it comes from can actually help you make an intelligent decision about how to treat it on the report. For example, we might a color that read or something if it came from the archive table, and that lets us order it buying your choice of columns within the union set. And that gives this one clear combined view of records from two tables suitable for a report. So let's recap the major points of what we've learned. Try to use the union all clause wherever possible, rather than just union Union will eliminate duplicates. That may not be what we want, and my personal preference is I'd rather have full control over what I eliminate by using grouping, which will do in a later lesson. Watch out for data type issues. Columns should be the same data type and where they're not watch out for data type conversion issues and use cast to get around them tryto alias, the union said. Because it makes the code look cleaner and gives us a bit more ability to do things with the combined union said as one unit 25. Grouping, and Assignment 4: we'll never come to grouping. Grouping is where you take a bunch off records, and visually, you combine them into one line or into a combination of lines. But it's almost like squashing a bunch off records into into a smaller package, visually, at one line on the screen on its away of basically combining the data to given overrule summation sort of you, Well, it's best to try and represent this visually. So in this example, we've got to a simple table with three records and a column description, and it's got three values. They're all the same. Hey, now, if we want a group that by description, we would effectively collapse or squash if you like those three rows in tow one Roy where that description value was common. And as you can see, he this visually expresses what's happened. With one common value of A in description and a group by description, we basically take each unique value and displayed on its own single Roy. Now, if we had more than one unique value in description like so and we would again group on description, then we would have to rise because there were two unique values off description. And to represent that visually, you can see that one collapsed row behind the scenes is represented by two physical rose there. And that grouped row there, which is represented behind the scenes by one physical road they now with a real pair of grouping comes in is within those groups rose. We can perform calculations on the physical Royals that are represented within that Roy. You can perform calculations on a range off the values within that road, for example, totals or averages or minimums and maximums. So coming back to this example, supposing our table has three rows. Description are just one description value of a one or three rows, but it has an amount column, and that amount column has some interview values. Uh, what could we do if we wanted to grip them by description? But what could we do with that group? Amount column? Well, what we could do, for example, is we could total it. So on group representation we can come up with an expression where we apply a group calculation against the amount grouping. Within that calculation, based on a so in this case we've got are the total amount and that is the total amount off amounts pertaining to description A. And that's their 57 which is the sum of 10 15 and 32. Well, let's just get straight into it. So I'm going to start a new query, and I'm going to select everything from sales older data. Okay, so we've got a whole bunch of columns here. There's the prime key sales order detail I d. There's the quantity of the sale, the product that we're selling at the price, the line total. So just Teoh before I go on my next step where we start proving I'm just gonna put aware clause in here. So look, I just headed away floors where I've selected. Only the records were modified. Date was in the year 2011. Now I've done that for a reason, But let's get straight into grouping now. Let's say I want a snapshot off all the product ideas in this table. I mean, obviously I can see we've got a 777 there in a 745 There may be many 77 sevens. There may be many 745 You know what? Let's just order it first and see what? Get a rough idea of what values of the Okay, we've got some seven sevens. We've got some 71 fives. I think there was a 714 up there briefly. Well, that's correct. But if I want a a sort of a snapshot overview off, what are the actual distinct actual values that are available within this sit head? Oh, I do it well, that's where Grouping comes in. So I'll just get straight into grouping by product idea and have a discussion on it. What have I done? I have introduced a new clause. This is the group by Klaus. Now, in the scheme of things, the group by Klaus comes after the where and before the order by So what does it actually do? Well, it's telling us by what criteria do be collapsed. These rise. What is the distinct value by which we arrange our rise? And we're saying its product idea now that effectively, in plain English, that is saying, give me one right for every distinct value in product our day. So let's just do it, okay? And then we go. No, you probably noticed we had lots of 71 fours and lots of 71 fives. But we've only got one here. We've only got one Roy for each product. I d because each one of those rows represents, however many product ideas were within that grouping, that may represent five physical Reuters that may represent six physical Roy's. It just depends now to demonstrate the effective grouping and the sort of things we can do with it. I'm going to start a second query window over here, and I'm gonna tweak the query in it to be the Samos this But I'm gonna ungroomed pit and we're gonna limit it to just this product. I d he Okay, so these are the seven or seven within this set we put to the same other condition here about the were so or what? This is within this set of seven or seven is where the years 2011. These are the records that make it up. And if we scrolled in here, there are 119 of them. Now let's say we want to in their group said we want to find out what is the number of records within that grouping. How many physical records make it up. Well, there's a very easy function to do that, and we'll start a discussion about the functions you can use within grouping. So let's start the discussion about grouping functions. The 1st 1 I'm going to show you is the count function. Now what the count function does is it returns the number of rows within our group Roy. So if a group the line is represented by 10 physical rows count can show you 10. But it has to, ah, usages. One is you can write, count and pass it in a column name, and what that will do is it will pass in the number of rows within, the group said, where there are non null values in that color where there is an actual value in that collar . If within that Roy that column name has a no that doesn't get counted, it doesn't increase account, so to speak. Now. Ah, farm or rum sort of easier representation that I like to use is the other form, which is Count Star, and that just counts all the rows within a particular group, Roy. So let's get stuck into one. Some demonstrations about these forms So here we saw that in our set off where we got product, I d seven or seven. There are 119 writes 119 7 or seven product ideas subject to the condition about the diet. So if we gotta go back to our group, said he how did we find that out? Well, this is that we do it using count I got What have I written? He I've said Count Star. This is the second form of count and that will tell me all the rows, all the rows, regardless off columns or regardless of values of columns. Just give me everything we think that group right? And I've given it on expression column as we dough. So let's see what happens. And then we go now coming to the 707 That's telling us 119 and that's exactly right, because as we saw in the UN correct version, there are 119 Royce now I'm going to show you are the other form of count where we actually pass in a column known now to demonstrate this effectively, I have manipulated the data. You won't see this in your copy Off adventure works, but I've gone to one off these product or D 707 rows and have gone to track carry a tracking number. And I just said that to know. So how does that affect our usage of count? Well, if we go back to our group said here, I'm going to count by Stone Carrier Tracking I D. And let's just see what happens. Okay, so I've gone count. Carry. A tracking number has carry account. Now, looking down here, you might be forgiven for thinking that that is going to return. 119. Same as this. But will it? Let's have a look. No, it doesn't. What does it return? 118. Why? Because what this is counting is all the instances of non null values in carry a tracking number. It's nothing to do with the values itself. You could have values in here of side 13 x a 1,000,000 doesn't meta the values, I mean, but what it is counting is where there is a value. So it counts these. It does not count doors, so it can be a handy tool if you're interested in finding how many of these have no values if you're seeking out null values within your city. Personally, I have never had a use for it myself. If I'm interested in no values, I will just simply doing a non group set, seeking out where Kari account is. No eso In all my 20 years, I really have never had to use this. Personally. I think it's a little bit confusing. I mean, you're counting that, but it's not coming. Words? No, I mean, but in terms of counting non null values, I just simply have never had a practical use for it. So you know what? I definitely have a practical use for counting the sheer physical number of Roy's within that, that's, uh, I've always used that. I've never had a use for that. Personally, I would recommend to stick to using Count Stark. So you know, to claim this up a bit, let's get rid of a second count function. They now if we come back to the UN group to sit, I'll just get rid of that condition about limiting it to seven or seven. I just laid the condition about 2011 Lee Now we scrolled in to the seven on nines. Notice that there's actually several different instances. Our special offer I d in that sit amongst the 70 nines. We've got several ones. We've got a tube, but got a three there. So let's say that we're interested in our group, said he. We're interested in getting at some of those special offer ideas inside that. We want to know what the some of the kind of values of special of her I D that we've been giving out to people who are order products. 799 Well, what's to stop? It's just simply selecting special off Friday up here and just see what happened. Okay, so I've selected it. I'm still grouping bar product. Our day off, sped up, selected it and let's see what happens. Our dear, we've got an era. What is the ERA? Column special Friday is invalid in the select list, happy because it is not contained in either an aggregate function or the group by Klaus. So what does that mean in English? Well, let's just put things back the way they were. Just cut that and I'll read you the query. So what it's saying is that within way are grouping by one factor here. We have got one row for the seven of nine's. One rope with seven awaits the 709 to 7 tens. Now, within that role of seven or nines, we've got maybe four different values for special for I D. But if we selected, how do we represent it? Well, we really can't because we've got one right there for all the seven of nine's. But the error is sort of Esquivel's way of saying, Well, hang on, You want me to put in special offer I d. But within that set this, like three different values of special Friday 1 to 4. Which one do you want me to show? I can't do it. So the area is sort of Esquivel's way of saying, Well, hang on, I've only got one cell per row. I can't show you many values within that cell. Well, what can we do? What we can do is we can expand their grouping so that we're grouping not just by the product idea, one right for each product, i d. But we can expand that and have multiple rows and they can show it. The multiple rows can show the individual values for our special offer ideas. So let's just give that a girl show you what I mean. Okay, what's just happened now? Instead, off one right for 709 we've now got four and was showing each of the unique values off the special Friday. So that's telling us that within the 70 nines, there were some force there. Someone's, There were some threes, and there is some twos that is perfectly legitimate. So let's start compiling some grouping rules. And we've seen the first rule already. And that rule is we can only select a column in a group. Said, If we are grouping by that color now, there actually is an or on the end of that, there's another way that we can select a column and will come to that Now. Now I'll just tidy up my query a little bit, and I'll order by not just productor de, but I'll order by the special offer ideas. Well, sort of make it look a little bit neater, like I looking pretty good day now if we go to the UN group said not that within our various sets of product, i d. We've got an order, quantity, various values of order, quantity, bear in mind, order quantities and interject field. So let's coming back to our group sets. Let's say that we're interested in the order quantity. Now, if we drop it into, um, the select walls, I bet you can guess what's going to happen. We've got that air again. Column order. Quantity is invalid because it's not contained in either an aggregate function or the group by Klaus. Now, I'm just going to, uh, do that. And it's the same thing again because within, for example, this this broke it off seven or nines and special a predictable took We've got eight rice. So this group rose is represented by eight physical rose. So if you want to know that order quantity idea, that era is again saying, Well, hang on. Within that order Quantity. Our day, this various values 1236 How do you want me to show it? How do you want me to represent what is many values within a group set that is one representing the intel on many? How do we do that? Well, that comes to the second exception that we can get around that era, and that's using an aggregate function. So that brings us back to a discussion about grouping functions, because another word for groping functions is aggregate functions, which is what the error message talks about. You can use a the term So one grouping function that we discussed was the account function . It's two forms, which is Count column name and Count Star. Let's look at another grouping function, some which accept a column name and as to what it does well, let's see it in action. So let's give it a go. So I'm gonna use order, quantity, but within a some function. Okay, lets see how we go already. So that's a little bit more informative. So we've still we're still grouping by product idea and special Friday. We've only got one right for each of those unique combinations, and that's told us what is the total off quantity? We think that said so we would simply go down literally if we were to go to Iran roots and we were, too, you know, export that to excel somehow, and we were to get look at all the seven are nuns and threes, and we would add up all the order quantities. We would get that title, so that's that's pretty powerful. So coming back to a grouping rules, we can only select a column in a group. Said, if we are grouping by that column is physically listed in the group by clause, or that column is within a grouping function otherwise known as an aggregate function. Now there are a lot off grouping functions at your disposal. I'm going to show you the ones that I've used most commonly. In the course of my work, there's average, which gets you the average and Min and Max. So let's have a quick go at doors functions. So I'm just gonna use those functions in my select clothes. He okay, so what? We've got some examples of those three and there you go. So within that set, you've got a total quantity off 3 41 That's all of the matter, not added up the average within that bunch of Roy's and we can see there's 120 Rosie would store their count function. So within that 120 rows, the average was to the least was one and the most was nine old, pretty informative stuff. Now that's over, Refiner Walter selecting from one table. But of course, we can select from multiple tables, thanks to the joining ability within SQL. So let's just get hold of Thebes product name related to this product I d and affected that in here and also integrated into the group said. And we'll see how we can use some grouping functions with some of the text columns within the product table. So let's join the product table here. Okay, so there's the name off that product, So let's integrate that into our grips it as well. Okay, now that hasn't really affected the group's it very much. I mean, if you were to break that up, you would see more columns. It basically means there's more columns available to us. It hasn't affected the results because we're still grouping by these columns here, and we still with same selections in terms, off columns and aggregate functions. So what we can do, however, is that we can now use. We now have access to some of the product columns and we can use those in the same way we can either group by them or we can use them in aggregate functions. So you know what? Let's say we're interested in some of the product names in here, so let's give that a go about that in terms off lowest. That is the lowest value within that said, because it's ah min imply sorting. So it's sorting by that text. It's sorting it alphabetically, as you do with text now. What would happen if we tried to apply? What is that? What is obviously a numeric function like average to what is a text column? Let's see what happens. Well, that doesn't work. And there is opera undated type involved. Char is invalid for a V G operator. That makes common sense. You can't use a text data type with what is essentially a numeric kind of function. Now I won't say any more about that, But I would recommend that Familiarize yourself with these functions so that we know how to use them and what sort of data times are expected by them? Well, you know what? Let me just start fix that probably, and I'll put that back Teoh mean now let's say the business requirement. Is that where we're only interested in those order lines where we have offered special offer? I d. Number one. Well, how do we do that? We do that the same way we do with any other refining down of your data. We add that condition to the were closed. Correct? There we go. So what it's done is literally this is their SQL server. Does it is. It will take this part here, people, for it will form the from cause. It will then apply. These were conditions to it. And then that said, it applies to the grouping to it, using that group by clause and the various aggregate functions up here. Now, what if we're also only interested in those the data where the minimum quantity is not one . It's greater than one. Well, how do we do that? Well, we could try put again into the wear clothes, like so. Okay, let's see how that goes. Okay, we've got an era. What is it? And aggregate, that's this guy here might not appear in the where clause unless it is in a sub query contained in the having wear clothes over a select list and and the column being aggregated is on the Attari. That's a pretty long and convoluted era. What it bicycling mains is thes. That is a group function now that is applied to a group. Sit now. Remember what I said about the way grouped sets are arrived at? We do a from clause, not group. We just doing ordinary front claws. We apply the where clause, and then we group it so this can only be applied to a group Sit. It can only be applied to. And you know what? I'm just gonna get rid of that and put things back the way they were. So that function all the minimum quantity that can only be applied once we've reached that state. We've taken our ungroomed rose, and we've grouped them because only in a group set can we talk about minimums. We can. We can only talk about maximums, that sort of thing. They only apply. And that question can only be answered within a group set. It doesn't. It doesn't make sense to try and apply. Ask a group question off a non group set. So what does that mean? Implant? In practical terms, how do we apply our condition involving a group function well, that's because we've got a very special cause that could be applied to group sets. And it's called the having clause. So let's revisit the SQL clauses that we've encountered so far. The select obviously the from clause, the Where clause, the group by that we've just seen now and the order by clause, and that's the order that they appear in select from were grouped by with order by at the bottom. Now I'm about to introduce you to another clause, and that is the having clause having is literally like aware Clause, but it applies to groups sets. So it applies to the city after it's been grouped after the select has been done that from the where, all that sort of thing, after the way, after the grouping and then it refines down the daughter after it's been group, and the benefit of that is in a having clause, we can refer to grouped expressions, so that's an example of that. Now, as you can probably guess, we can have lots of fun with their having clauses we can refer to average instead. Basically, any group expression is available to us in the having clause because that's the purpose of the having clause. So you know what? I'm just gonna put average down here. Let's see what happens. Okay. Great averages off two on four, and someone and so forth Askew would expect. Now, let's say that in addition to that, we've got another condition where the were only interested in those with a minimum quantity is greater than what now? Because having is literally like, as I said, aware clause for group sets, the same syntax applies where were combining conditions and or is that sort of thing? So let's just put that in, and then we go, You get the audio. Well, this is all fine and well, but is there an easier way to get at Grouped daughter? Well, yes, there is. And I'm gonna show you, too wise. First off, it is possible to select an aggregate function just by itself without a group buy clothes. So, Escamilla, smart enough to work out that if there's no other on grouped columns selected on what's up here is just groupings, then it's smart enough to work out. Well, obviously he wants to group the entire table together. So it has this sort of shorthand way of doing that for you. I could put a another aggregate function up there, and that works to it. It is possible to put other parts of the querian like away close, but as long as what's up, he is just aggregate functions that we can just have aggregate functions and no group by Klaus. We just can't put any ordinary selections. They, for example. In other words, we can't have a mixture of non aggregate and aggregate. It's gonna be one or the other. And if it's just aggregate, then no need for a group buy clothes. Well, I'm just gonna do a very simple weary from sales of the Daito. Pretty straightforward. Now what if I want to just have a very quick snapshot off? What are the possible values off product I D in that table? Well, this is how we do it. It's this simple. We put a distinct in front. What's the effect? And we have basically just it's done exactly that. It's gotten us the distinct values off that column within the table. It's effectively grouped the entire table by byproduct idea and just displayed those values absolutely did. Simple. This is quite quite, ah, handy if I'm examining a new table that I'm not familiar with all just and I want to know what What are the possible values in some sort of ah, foreign key field, for example, I will just simply go select, distinct that column, and there's my values. Now we're perfectly free to add more columns to wear select laws and thanks to distinct it'll group by those as well, automatically. So let's take an example that you go so simply by using distinct on the two columns I've grouped by those two cons, and I can see the distinct combinations of those values. Well, let's recap everything we've London it's being fairly considerable. We learned about grouping the principles of grouping the group by clause and the aggregate functions where we can collapse, rose and then get it summed up aggregated data. Within those rise, we learned about the rules about selecting columns. In group sits we learned about the having clause, which is aware close, but it's applied toe group sets, and in this next lesson, we'll look at on efficient way of getting subtitles and grand totals within a group set that's using the roll up command 26. ROLLUP, ROLLUP! Automatic Totals: Now I'm gonna show you a very powerful technique for creating totals. In group sits, you can create totals at the grand total or a sub total for every grouped value within a group set. It's enormously powerful, and it is a command called Roll Up, and it basically at automatically adds total Reuters. Let's see some examples. Okay, so I've started a new query window, and I'm just going to do a, uh, more simplified version of, ah, grouping query that we had earlier. So let's do it now. I will point out that I've put in a where clause here that's limited the product I d to those less than 712 and I've done that just so. I get just a handful of rose That'll fit in that space is so I can Visually, it'll be easy to easy to demonstrate what I'm gonna do with inserting a total rose. Now let's say I would like a grand total of total quantity grand total down the bottom. He's some way automatically with a minimal amount of effort. How would I do that? I got what have I just done simply by adding the woods with roll up that has created another Roy note that that row under product I d. Has a no and that value there is theglobe and total off. Everything in this coat is the grand total of all these guys, and you can add those other you want. But take it as read that those values add up to 20,738. So what it does is it looks at what we have grouping by, and it creates a grand total based on that grouping. In other words, it creates a roadway product idea. What we're grouping by is no, that's how it identifies it, and it will add up. Or it will look to any of the expressions in the select wars. And if there's any sums there, then in that column it will put a it'll take the sum to the next level and a well, some not just by product i d, but by the entire set of all product our days. So that's what roll up means is it basically says, Okay, I'm gonna look at the group's off this down here, and I'm gonna roll them all up, roll diesel up into one figure. So how does that work with other types of aggregate functions? Well, let's put another couple in there and see what happens. So what have I done? I've got the minimum aggregate function. They and the max is well with that roll up in place. So what does that get us? So it's got the usual values there. That's the men quantity in the next quantity within that group. And if we look to air, roll up, created grand totally. What is actually showing? Well, that's a one. And that's a 32. And if you may have noticed that 32 is the Max from is the highest value in in or out of all those groups. So basically it's taking that those grip are expressions, and it's applying it to the entire sick, off all product. Our days now I'm gonna clean things up a bit again, and I'm just gonna get rid of those two min and Max expressions and go back to just having a title. So this is fine and, well, where we're grouping by just one color. What if we grouping by more than one column? How does that work into the mix Well, let's group by your that special offer I d again and see how that works. Okay, What's happened? Well, we've still got era grand. Total figure up here, albeit now we've got a special offer idea column the and that's gotta know in it. Um, no. What else have we got? Well, we've actually got some more rose inserted. And for example, that's something. One of those roads that have been created. We've got a product idea this 707 The special offer I devalue is no. And we've got some sort of Ah, figure there. And you can go check this with the calculator if you want. But I can tell you that that figure is the summation off all those total quantities right there. So it's almost like a mini roll up. It's a sub total. It is a subtitle rolling up just that subgroup of special offer I D. Within product, I d. And we can tell that that is a, uh, a rolled up sub total because product idea is not no and special offer I d is no. So things value represents a total by a certain column, if you will, based on the values of notes. So if the particular column on that road is no, then that means that is the subtitle for that particular value of that particular column. Well, I'm sure you'd agree. That's a pretty powerful technique. Now, looking at the ordering, that's kind of not exactly intuitive, because, I mean, that's supposed to be a grand title and grand totals. A usually at the bottom on. Certainly if we would supply that set to a crystal report, and we wanted to use that as a grand total of the bottom of the page, that wouldn't quite work for us if we just wanted to go by the order off the city, which is what you usually do when you supply Querida crystal reports. So how can we fix that? Well, we can do that using, ah, some expression columns and case statements so I'll do that for you right now, and then we'll discuss it. So what about done here? Well, I've got a couple of I've got a case statement here. Well, it's a nested case statement noticed the away I've arranged. It's a little bit different to the way I've shown you earlier. But to the principles that same soldiers take you through it. So we've come up with a you know, we'll just put it on. That's on, Roy. They we've come up with a text expression column are for right type, and it basically says when productor de is no, then call that grand Total. Otherwise, when product idea is not no and special offer, I d is no. In other words, it's one of these. It's one of sorry one of these guys, then sub total else will just call it data and then we basically put on enough ends. This is a bit of a thing you've got to remember with case statements is when you contested case statements. Remember to have enough ends on the ND to match the number of cases to begin with, and then you'll know that you're nesting is complete. So that's enough information that if we were to supply that to a crystal report, the report writer would know how to treat these totals and subtitles. Where to put them on the page, so to speak. Now that's fine and well, but what about getting the order right? Well, let's try and do the same thing and we'll discuss it. What about just done? I've taken the entire group set there, and I put it in its own record set. Yeah, we're returning to record sets that that topic again. And, of course, about to change the table reference for the order by clause. Now, the reason I'm doing that is that what I'm gonna do is I'm going to do another expression that refers to row type. And now that I've got it in its own record, said, I can go refer to rs one dot ro type on Do stop to it, which you'll see in a minute. Had I not done that to do this other expression, referring right up, I would have to repeat that big, convoluted case statement again. So as we saw from the record sets lesson, that's, ah, more efficient way of doing things. So I'll just continue with my new little expression here involving Roy type and you'll see what I'm trying to do it. Well, let's have a look at what I've done here. Now, This is just one possible technique involving a fancy sort factor that you could use. You might come up with a different one. This is by no means the only solution. But what I've done is I've looked within the record set to the right type, and if its data then are just simply order by, are the product I d like. So So the 70 Sevens get put at the top there, and then the seven our rights then have basically said, Well, if it's the grand title, then I give it a sort order of this mess of great 99999 number, which effectively puts it right at the very, very bottom and then else. In other words, if it's not data and it's not grand total, if it's a subtitle, then I just basically take product I d, and put on just a tiny incremental factor of 0.1. So, such as the group title here that becomes seven or 7.1. So the sort order becomes normal, right? Normal, really normal road normal right Normal row subtitle, because it's that value plus one that makes sure that makes sure that it's the end of the +707 the end of the data's and that's basically it. And then we have got to do is order by that sort factor expression, column and job done. And you could take that set and hand it off to a crystal report. Designer person. And that's job done again. That's, Ah, one possible solution. You could come up with a sort fact that involves maybe a text value. Instead, get a bit creative, but the ago that's the sort of thing you can do it, and we come to the final assignment in this course, I'll get you to download and open assignment for dot zip and ah, the requirements air in the file assignment for Doc Pdf I've supplied a solution in assignment for suggested solution. Daughter ski Well, the results from which are in that excel far This is a fairly comprehensive assignment that uses ah, lot of skills that you will have acquired in this course. So I won't blind me if you take a bit of a PE kit the suggested solution. But give it a go, and I hope you've enjoyed this course, and I hope you have acquired some vital skills out of it. 27. Sub Queries - "Sort Of" Joins !: So we're gonna come to a feature in SQL, which I don't use a great deal. It has its place, and they called sub weary. Now, some Queary is basically a select statement that is embedded within a cause. Now they probably sends little confusing, but we'll look at some practical examples, so I'm gonna start off doing a strike query from a good old friend of production dot product. Now let's say, for example, that I want to have listed against each product i d. The total count off all products, which we scrawled into the bottom here is 504 based on that little row count on the side of the results. Great 504. So let's say, for just for some, for argument's sake, for some reason we're doing a crystal report, and against each one, we have to have the total eso. How did we do that? Well, um, this is one way. It's using some query so we'll do it and we'll discuss it. Okay, what have I done? I have got a bit of code here, self contained in its own bracket. And what does it say? So that counts start from production product. Now we saw from grouping that will get us the total count off that entire table. Now if I would've just copy and paste it into Bertone Window and just execute that and that returns one value as count, does we? We know that from a grouping Listen, so if that returns only one value, it's perfectly legitimate. Tohave it sort of in line are against each record that has returned from the rest of this query. So just to demonstrate, I'm going to execute it. And there we go 504 and someone insightful. So it's It's a very quick and very quick and shorthand way of doing getting a grouped expression integrated into a select clause without having to, for example, joined. We could have a joint Teoh a record set that would have that count within it down here. That's an alternative way of doing it. But this almost is, is equally effective, you know, I'm just going Teoh, get rid of my little sub query. They and what I will draw your attention to is there is a column he called, but I just find it products. Subcategory I d. Some of them have nose, but without making you go to the doctor dictionary, I'll just I'll tell you about the situation that is linked. That is, a foreign kid linked to a table called product subcategory. And I'll just select products up category just to give you a look. And there you have it, this sort of broad classifications of products like mountain bikes and ride bikes. So if we are coming back to our the production table, we could, for example, using that foreign K. And you know what? I will just I will just refined this sit down toe. Only those that actually have a product, subcategory. Okay, and you know what? I'll just for argument cycle put products up category the in the front of my selection list . Now let's say for arguments like that, we wanted to get the product subcategory name listed against product records up here. We want to call him here for the name Now. Could we do that? We could join two Thebe products of a category table from product using the foreign K field using a joint in a joint relief or a left join. Alternatively, we could use a sump weary. So you know what? I'll stop talking to some query and we'll discuss it now. This is gonna have a bit of a problem here because remember, I said that the rule in this situation to rule with some queries is that this can only return one value. Now that's some kind of return. One value, in fact, contrary. Right network. It's obvious that that's going to return the entire list of names. In fact, if we were to execute this query, we would get an error. What is the air sub weary? Returned more than one value. This is not permitted when the sub query and a whole bunch of stuff that I won't go into. But basically you can see the problem that is more than one value, and you can't possibly represent more than one value in one cell in a record set. So how do we make it only return one value? Well, I'll do it and we'll discuss it. Well, what's happened? Well, what's happened is we've forced this sub query to return only one name by virtue of this little wear condition, so there's no joints. We're just doing a were condition of What the where condition is is where pierce dot sub products, subcategory i D. And if we return to our products up category table here, that's the That's the prime que. There are off the table A equals and this is the kicker. April's pay P products, subcategory I date. So what it's done is while it's a self contained select statement, it's sort of gone outside itself, and it can refer toa other objects within the rest of the query so literally and I'll run the query again literally. When a school is running up, it comes here and it basically says, Okay, you want to get the name from this table. Whatever he where its value equals this guy. I hear it literally looks to this. Value looks outside the sub query to the main query and goes, OK, paper products are in your neck category I day is that amongst the rest of the query? Oh, look, there it is there. There's pe p. I will get the Bronx up category I day por que vale value integrated in here and I come up with one value returned, and that is all perfectly legitimate Now. You're probably wondering, Well, that's all fine and well, but why wouldn't you just do it the traditional way by having enjoin from product to product category? Dan hit. Why would you not do that? Well, there may be some situations where if this existing query was a lot more convoluted than it is now, if it had lots of tables joined with joins, joins joins, lots of joints going all the way down here. It may be that introducing another joint may affect the query. Him actually return more rows, then you otherwise would. Because of the way the data is laid out, it has happened to me in the past are not very often so having a sub. Queary is sort of an alternative to that. If introducing another joined table into ah lot of joins messes with their joins in such a way. Uh, this is a way around that now that is not very efficient, because, in fact, it is reading the product subcategory table every time they're in fact, remember, we had something like what, 500 on how many rest we have 295 because we were fun, too. Damned by the subcategory is No, no to hunt. So that means that is 295 times that we are reading the subcategory table. Rating it here, reading in here, reading. And he where? Once for every time there. In other words, we're reading the entire table there, the entire table in the entire table there. Whereas if we had done a joint, it would only be raining at once and then joining each of the products, subcategory records to the product records based on the joint condition we specify. So had we gonna join park subcategory category would have been great once doing it this way . We're actually reading at 295 times. That will potentially slow things down. So some queries. Yes, There are a quick way, but they do come at a cost. Now I'm gonna show you what a new alternative way of using sub queries. Remember that the rule is a submarine can be used in a clause. So far, we've been using it in the select laws. It is possible to use it in the where clause. So we'll show you how I'm going to put this back gun. I'm going to remove my sub query from the top. Now let's say, for example, we're only interested in those products that are of subcategory helmets or bikes. Now again, we could have done it with a joint, and there is a way to do it with a some query on. I'll just do it and we'll discuss it. Okay, let's see whether what that gets us. No, it's pretty much worked. I mean, we can see that these are helmets there and these are rode bikes down here, so that's pretty much done it. Now again, that's a slightly little convoluted way of doing it. We it's not efficient because we're basically running this query against races running this 295 times once on that production. Roy once on that production. Roy. We're reading that table 295 times where he said we didn't enjoy. It would only be read once, so it's not efficient. Note also that this basically this query, the sub query. If I just select that text and executing on its own, that returns more than one value. So this is different to using it when you integrate a sub theory into the select wars because when you've got it in a way close, you can have more than one value return that is perfectly legitimate. So it's evaluating that set against that field in product in product, using the in operator, which is perfectly legitimate now. Why would you actually do that? Well again, if you wanted to join, you could join from products subcategory to product. But again, it may be that in some situations your existing joins are quite lengthy, quite convoluted, the conditions of quite complex. And it could be that if you introduce another joint, it could affect the number of Roy's returned in a way that you don't want what don't expect . So this sort of removes that possibility, and it's so it can be an easier way to do things, albeit not a very efficient one. So let's just break out what we've learned about some queries. You can have a sub queary within a select statement, but it must only return one value. You can have a some query elsewhere in other clauses in your query on that situation, multiples are permissible. It is possible sub queries to join to the mine query by referring to columns in that mind, Query