SQL with Microsoft SQL Server for Beginners | Anil Batra | Skillshare

Playback Speed


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

SQL with Microsoft SQL Server for Beginners

teacher avatar Anil Batra, Marketing and Analytics

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

26 Lessons (2h 18m)
    • 1. Introduction

      1:47
    • 2. Installing MS SQL Server

      3:01
    • 3. Understanding sql server structure

      4:25
    • 4. Database normalization

      4:29
    • 5. Restoring adventureworks sample database from bak file

      3:39
    • 6. Selecting records from a table

      12:39
    • 7. Selecting specified number of records

      5:04
    • 8. Where clause

      7:20
    • 9. Pattern matching in where clause using like

      4:46
    • 10. Where in clause

      5:05
    • 11. Selecting unique records

      3:08
    • 12. Sorting the result set

      4:48
    • 13. Joining multiple tables

      10:25
    • 14. Using alias

      7:38
    • 15. Aggregating data in sql count min max

      13:04
    • 16. Aggregating data sum average

      4:05
    • 17. Null values in aggregation

      3:52
    • 18. Using aggregated values in where

      4:04
    • 19. Combining columns and string values

      4:39
    • 20. Extracting string values righ left substring

      5:19
    • 21. Find length of a string/column and position of a character in a column

      3:57
    • 22. Inserting records in a table

      5:03
    • 23. Update records in a table

      4:45
    • 24. Deleting records from a table

      3:26
    • 25. Outer join left right

      7:09
    • 26. Closing comments

      0:26
  • --
  • 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.

41

Students

--

Projects

About This Class

Learn how to use SQL using Microsoft SQL server. This course will teach you everything you need to get started and pull the required data for your analysis and reporting.

You will learnĀ 

  • Database fundamentals
  • SQL Server with sample database
  • Select statements
  • Sort records
  • Restrict statement based on your requirements
  • Aggregate the data

Meet Your Teacher

Teacher Profile Image

Anil Batra

Marketing and Analytics

Teacher

Anil is a seasoned Digital Marketing and Analytics professional with 15 years of experience. He has worked with companies ranging from Fortune 50 to startups and has helped them improve marketing results. His customers include Microsoft, SmartMoney, ESPN, T-Mobile, Hoovers, RealNetworks, Starbucks, and TheStreet. He holds a B. Tech in Electronics and Communication Engineering from India and an MBA from University of Washington, Seattle.

Anil has taught and trained several Marketing and Analytics folks in past nine years of teaching at UBC, University of Washington and Bellevue College, and speaking engagements at various conferences as well as his blog. He teaches Digital Marketing, Digital Analytics, Google Analytics, Tableau and Social Media courses. He also served on the Board... 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: Welcome to sequel with Microsoft Sequel Server Course sequel is one off the most sought after skills. The simple reason is because data eyes everywhere because organizations are collecting tons of data about their customers. Customers. Interaction with various channels online offline in store sales for chase data, employee data employees, interaction data, financial data. Many third party it all. This data then ends up in a database or databases. Then companies have toe analyze this data to make sense of that data and make business decisions. Organizations that are not using data are being left behind. That's why organizations across the globe know the value off data and the analysis. For analysis, you need sequel skills. You must have seen plenty of data science job positions available. All these data science positions require sequel skills. Whether you are trying to become a financial analyst, marketing analyst, digital analyst, data scientist, sequel skills, bottom most, having an understanding of data Impalas you and that's why I developed the scores. This course is based on my in person workshop. This course is going to take you from no understanding of sequel and databases to really having a good command, where you can write a sequel queries with confidence. So let's get started 2. Installing MS SQL Server: for this course, we will be using Microsoft's secrets over 2017 Express edition. This version is free. Whatever you will learn in this version will also apply to other versions as well. In order to download it, go on Google or being and such for sequel Server Express 2017 and you will find the link to download this addition to Go ahead and click on sequel Server 2017 Express tradition on this page. Click on download now and sequel server is getting downloaded. Once the download is complete, open it and you will see a window to install this version. Go ahead and click on Basic. This will install everything that you need. Accept the terms. Make sure you have the right directory. I'm gonna leave it as default and click on install. Once the installation is complete, you will get a bunch of information that shows you where the files are. Next you need to do is install the S s m s, which ISS the sequel Server Management studio. This is where you will be writing your sequel qualities and all the manipulation. So far, what you have done is installed the database, which is in the back end, and SMS is the front end that you will use too many plates your data and interact with the database. So go ahead and click on install S S M s. It'll open up another window, go ahead and scroll down and click on the latest version off as S. M. S click on Brand. Once the installation is done, you will be required to restart your computer. So go ahead and click on restart before you click on restart. Make sure everything else that you have going is saved. And once that's done, go ahead and click on Restart. Once the computer restarts, you will be able to see Microsoft sequel server in your start menu. Here is your Microsoft Sequence of management studio. Go ahead and click on it to launch Management Studio one prompted to enter the server name and authentication. Leave it as it is. This is your default connectivity if you're connecting to the server that we just installed . If you have credentials to connect to another server, let's say your I T team has provided you the user name password and the server name. Then you will enter it here. Once done, click on Connect and you will be taken to the interface that we will use throughout the scores. 3. Understanding sql server structure: before proceeding to write Sequels or equities. Let's spend some time to look at how sequel server is structured in Sequel Server Up on the top you have a server. Think of this as a physical machine that you're connecting, so you're connecting to a computer that is serving as a server. Under each server, you can have multiple databases. Databases are entities that contains all the data structured in various tables. Under each database, you have schemers think off schemers as the ownership structure. So within each database, you can have different owners. Say you have sales team that owns their own schema and objects underneath it, such as tables. And then you have schema that belongs to customer service. Under eat schema, you have what we call objects. Objects are entities, such a stables, store procedures, views, etcetera. Our main focus is going to be tables. I'm gonna go to Secret Server and show you how this looks. Wouldn't sequel server Here I am in secret separate management studio. I am connected to a server that's on my machine. And here is the name off that server. You can connect to multiple server within this interface and all of them will appear underneath it. So when you click on this minus sign, it collapses and you will be able to see next server at set to connect to a server, go up to file menu and connect Object Explorer. And this is where you can put second Settlers information, and it will add it right here. We don't have any second sequel server. We will just work with one that we implemented earlier. So go ahead and cancel it. Now click on this plus sign to expand the server underneath the server, you will see the data basis. If you expand it likely you're not going to see any databases because we haven't created any databases yet. I will walk you through the process of creating a database. However, for now, you can just fall along to see how the structure is. So here you have the data basis. Adventure works until test marketing DB. When you click on any of these databases, it'll show you it will show you another set off folders. That's the way for database to organize objects underneath. We will be looking at tables. This is where all the data gets store so go ahead and expand on tables, and this is where you can see all the list of tables. The tables are prefixed with the semen name. This table is owned by Debbio. This stable department is owned by Human Resources. Address is owned by the person schema. You have the production schemo sales schema, etcetera. If it's not here at this point, don't worry. As we go through the scores, things will start to become more clear. Another thing I want to touch is the menu options up here in the Sequel Server Management studio. Clicking on New Pretty will open a window where you will write all your secret queries. When you first click on it, it's going to connect to master database. That's a system database that's available under system databases. However, we won't be working with the system databases because those are used by sequel server internally. In order for this window to execute the quickie, you have to be in the right database. The way to do that is our over to this option. And click on the arrow and pick the database that you want to work. Now all your queries will be executed on the state of these. If you are not in the right database, then your equities One word. Any time you get an editor that can't find the table, it is very likely that you are working in the wrong database. So you have to go up here and change it. I will use the rest of the menu options as needed as we go through this course. 4. Database normalization: most of the data that's organized in Secret Server is organized in a normalized way. So what does the normalization means? That's what I will talk about in this lecture. Normalization is the process off organizing data in a database. It's by creating multiple tables and establishing relationships between those tables. The idea behind those relationship is to make database more flexible, and the way you do it is by eliminating redundant records are data points, which means you store data in a way so that if you have to change the data point, then you do not have to change it in multiple tables, you change it in one table, and the changes have reflected everywhere and also to remove inconsistent dependency. That basically means two story die in a way that makes logical sense. So let's say you haven't table about employees. Let's say you have a table about employees in that table. You will only store information about the employees and not every single detail off the department that employees belongs to the department Information oversight in a department table. I will show you an example that will make this normalization process much easier to understand. It is an example. I have an extra sheet that contains data. If I have to put this in a database, one way is to store all this information, as is in the databases, so I can create a table called Customer, which will contain customer I. D. First name, last name, company, email, state main store and then. But these data points each row will represent one customer. However, if you notice here, I have the company named multiple times. So Microsoft Microsoft here and as you can see, more Microsoft or Google and then you have Google here, and also I have information about the store were the shop and then there State. As you can see, same data point is repeated in multiple places. Let's say tomorrow Microsoft changes its name to big. I'm just making it up. In that case, you will have to change the name of the company from Microsoft to being in all of these places. Similarly, if the mall changes to a strip mall or a strip mall changes tomorrow you will have to change all this information. Bless storing all this name. Multiple times also means more space is required in the database. The way to handle these situations is to create a normalized database. That means creating multiple tables instead of one giant table. So let me go to another tab in here is how the table structure will look like you have one customer table. Then you have separate tables that have store state in company. And then instead of storing the name of the company, for example, I am starting the idea of that company. So I d. Number one basically means Microsoft I D. Number two means Google etcetera. Similarly, I've used ideas for stores and for state. Rather than storing the complete name, I have stored two letters representing that state, which are the ideas and escapes. So you got state code right here and the statement this way, if Microsoft changes its name rather than changing it everywhere, you just goto this table and change the name right here. So you have multiple tables, which makes it easy for you to change the records if you do need to, as the less there is less storage, because all year storing is an I d. Rather than repeating Microsoft in all these places, so that's the process of normalisation. When you look at your data bases, generally you will find this structure, so remember there could be multiple tables for you to look into to get you the data that you need. 5. Restoring adventureworks sample database from bak file: in this lecture, I will show you how to install or restore our database from a backup file. In doing so, we will also create a simple database that we will use throughout this course sequel server can back up the entire database along with all objects and data in a dark B A k file this be a K file or back file again, then be transferred over to another server and the whole database can be restored on that server. And that's exactly what we will do here. There is a database called Adventure Works. It's a widely used sample database for learning sequel server. So that's what we're going to use. In order to get the backup file of the database, go to the U. R L listed below. Once on this page, scroll down and find the appropriate back up. We will be using Adventure Works 2017 dot back. Remember, you have to take oil TP worship, which is online transaction processing. Do not pick the ones that are listed below, such as lightweight or data warehouse. So this is the version that you want to use. Go ahead and click on it and it'll download the back file in your download folder, so click on it. And here's your download files. Once the download file is complete, make sure to copy this file into somewhere on your C drive. I usually move it toe. C Program files Microsoft Sequel Server So this is my adventure works 2017 dot bak file. Once that's done, go over to sequel server and we will restore this back up in the sequel server. So right, click on your databases and go to restore database. Now it's going to prompt you to pick the files that you want to restore. So click on devices and this field gets highlighted. And so does this selector click on the selector. We are restoring from a file so you'll see file here, click on add and then go to your sequel server Back of file location like on the back of file and click. OK, this is how you can restore any backup file. You just click on add to pick it up and click. OK, here is the name off the database that it will use. You can change the name here if you would like. I'm going to rename it to adventure works. So I'm going to remove 2017 and then click. OK. Once the databases restored successfully, you'll get this message. Go ahead and click on. OK, Now you have your adventure works database, expand the database and again expand the tables in. Here are all your tables. We will be using this database in the future lectures. Now you're ready to write the sequel queries. 6. Selecting records from a table: Selecting the records from a table is the most common function that you will be performing in a database. In this lecture, I will show you how to select records from a table. The Syntex for selecting or pulling these records from a table is select and then put the column name. This is the column name that you want to pull the data from and then from, and put the table name next to it. This will pull the value from specified column for all the rules in that table. When you have to select the data from multiple columns, you use the syntax where the column names are separated by a comma. You can put as many columns as you want and then but from the day building. That is also a shortcut available when you have to pull the data from all the columns in the table. In that case, rather than putting each columns name you put a star star means all the columns from the stable. So let's go to the database and see how this works. Here I am in secret several management studio. I am going to write a new quickly, so click on new query When you click on new Query, it will generally take you to the monster database. This is where you need to specify the database where you are going to run the query against , so click on this little button and picked the database. We are going to be working mostly in adventure works, so click on adventure works. This is where we will write equity, and when we execute, it will be executed in Adventure Works database. In order for you to see what tables are available, click on databases, click on adventure works and then tables. This is the list off all the tables that are available for you in the state of base. As you'll notice, each table is prefixed with the schema name so you can see sales dot shopping cart items. That means shopping cart Item is the name off the table, and it's owned by sales schema. When you write your queries, you will have to refer to the full name along with the schema, not just the table name, but the schemer as well. So when refering to this table, you will have to write purchasing dot render when you are the owner off a table, as is the case with anything that starts with D B O. Then you do not have to refer with the ski my name. But if you refer it with schema, that works as well. So keep that in mind as we write these queries, so the question we are going to write is to pick the values from person. Dark person table. This contains information about every person. By that, it's an employee or a customer in this stable. So go ahead and click on plus to see which columns are available and then click on plus to see the names of the columns. Here's all the information that's available in this table, so you have first name, middle name, last name, tidal, etcetera. So let's say we want to write a statement to pull the first name for every single person in this stable. In order to do that, let's go ahead and construct our select statement. So click here. This is where we will write our query. So go ahead and write. Select all these statements in sequel server are case insensitive? That means it doesn't matter whether you write them an upper case, lower case or mixed case, they will all be considered the same. So you can write select in all uppercase or in a mix case it'll still work. I'm going to write it in lower case here, so select and then you write the name of the column. In this case, we're picking a first name. So that's the name of the column. So first name again. It's case insensitive so it doesn't matter, and then you're right from you will notice a squiggly line. I don't need the column name. That's okay, because it doesn't know which table we're refering to, so it doesn't know, but that this first name is valid column or not. So go ahead and write from and now right the name off the table. Remember, select column name from and then the table. Name the right person. As you're typing, you'll notice sequel server management studio starts to give you some options to pick from it'll looking its own structure and find the matching thing based on what you're writing. In this case, it's giving you the schema person. Possess your typing it. You can choose to select this or continue writing it doesn't matter. So I'm gonna continue writing. Then start once the ski my name is done, I will give you all the tables within that schema. Remember this person schema? And then these are the names off the tables. So person dark person is person schema and the person table. So let's go ahead and write person. And this time I'm just gonna pick it up from this list. Now squiggly line is gone. Statement is ready to be executed. Go ahead and click on execute, and here you have the list off all the first names. Next, we are going to look at how to select multiple columns. So now I'm going toe. Also select middle name as well as last name. In order to do that, we separate the list of columns that we want to select by a comma. So go ahead and put a comma and now start typing the next column. Lee. As you're typing now, sequel Server Management studio knows that you are trying to select it from person. That person table. It's going to find matching column names from that table as a less any functions that are available in secret server for you to use so you can go ahead and pick the column name from here. Or you can continue typing. I'm going to pick up the column name this time, so go ahead and double click on this and it'll select it. And then next comma. And then the next column name spacing between these commas and column name does not matter . I can put multiple spaces here if I will, but for the readability off the sequel statement, keep it consistent. I'm just gonna go back and type in last name again. As you can see, there is a function called Last Value in the column name. So I'm going to go ahead in Never click on this collar, and now I have my sequel statement ready. So go ahead and click on Execute. And here is the results of Now let's write a query that'll select all the columns from this table. I'm also going to show you another feature off sequitur management studio. In previous example, I simply added two more columns to the previous quickie. If you want to keep this quickly as it is, you have two options. You can either right the query here or create a new query window to create a new query. Render click on new query. This will open up a new window. This is where you can write your new query. Your previous one is still here. Let's write the sequel Quickie in this window. The query we're going to write is to select the data from all the columns in person table. So go ahead and write. Select to select all the columns you use star from. And then the table name. That's it. Go ahead and execute it and you can see that results that it's picking up all the columns in that order. So you got business entity idea right here, person type. Then you have named style title and so on. Now I'm going to copy this quickie so that I don't have to write it again, and I'm gonna go to the previous window in this window. I can use any of the space here to write this quickie on any equity, so I'm gonna go down a bit. It doesn't matter how many lines you put in there. It's just sort off a note bad. Were you writing equity? So I'm gonna faced it here. So now you can see there are two equities to run a specific weight in this window. I can just select that portion and executed. If I go ahead and select this portion and executed, then it will give me the result for that query. So whatever query you have selected is the one that's going to be executed so you can write multiple queries here. If you would like to do that, you can even run both of these queries in the same window, and it will give you two results sets to do that un select everything, or you can select both of them. Either way, it's gonna work and click on execute. Now, As you can see, there are two windows here. This is the result from Foursquare E, and this is the result from second quarter. You can have as many questions as you want in this window. However, keep in mind that result window is going to get smaller and will be hard for you to take a look at the results. You also have an option to save any of the queries that you're writing in order to save the questions click on save button. Click on it, and it's going to give you an option to save this query by providing it a name. So let's say I'm going to call it Select, not sequel and save it. No, this active Indo is saved. It's not going to save this one. It only saved this one. As you can see now, the name is Select Out sequel. I can close this window and go ahead and open that file that we just saved. File Open Goto file menu and pick the query that we just wrote. Double click on it and here's your equity. It saves the query, but not the results that so as you're going through these exercises and you want to save your queries, make sure to click on save and save them so that you can refer back to them if needed. So that's all in this lecture. You learn how to write a select statement as well as some of the menu options. Within. Sequels are management studio 7. Selecting specified number of records: When you have a large database and there are millions of records in your tables, then it's not feasible to select all the records from the table. If all you want to do is just have assembled set in this lecture, I'm going to show you the queries that you can use to select only specified number off records from a table. There are two ways to specify the number off records that you want to pull from a table. One is by actual number, so you know whether it's 10 20 100 etcetera and the other is by specifying a percentage. So if you have 1,000,000 records, you can say I want 5% of the records and it'll select those 5% of the records. In this lecture, I will show you how you can limit the number off records by specifying both types of criteria to select specified number off records from a table where you know exactly the conch, say 1 1000 etcetera. You can use select Top and Syntex, where N is the number of records that you wanna pull and then specify the columns that you want to select or star for all the columns and then wrestled us in tax is similar to the other select statement you just put from and Timberlake. You can also select certain percentage off records from the table. In that case, you use select top en ways and is the number say, 5% 10% but the number and then person followed by the column Names or star to select everything from table. So let's go in Secret Server and see how it works. Here I am in Microsoft Secret Server. I'm going to write a new quickie again against the person table, so click on you Quit E and make sure you are in Adventure Works Database in Let's Right the query that we wrote in the previous lecture. So Select star from Purse and Dark person. You'll notice that this time I rolled equity in one line. It does not matter whether I write the quickie in one line or I split it in multiple life. Either way, it will work. So go ahead and execute disk ready. Here are all the records. As you can see, there are 19,972 records in the stable. This is ah, relatively smaller table in real scenarios, you might have millions off records, and it might take a lot of time for it to return those records. And if you're just trying to see a sample of records, so you can I've all the records or just pull them out for further analysis, then you will have to limit how many records you're getting. And I'm going to show you both ways off limiting the results so far us. Let's make sure we only select top five records. So select Stop five star, which means all the columns and then from the table. That's all we need to do. Go ahead and execute it. And here you have only five records. Alternatively, you could have also used Top X percent. Let's go ahead and copy this quickie, so go ahead and highlighted right click and copy. Now I'm going to pace the squidgy right here, and instead of five, I'm going to use five percent. That's it. Go ahead and execute this quickie, and now it's pulling up 9999 records. That's 5% off the total records. So that's it. This is the way you can limit how many records you get from the database to make the queries go faster. A lot of time for your analysis, that's all you'll require. One. You want to see what data is in the table. So in that case, you might just to top one to something like that. Or if you're doing some kind of simple analysis, you might use percentage in select 5% 10% of the records, pull them out and conduct your analysis. Go ahead and practice these queries so you become comfortable with them. You can try it on other tables as well. 8. Where clause: majority of the time. When selecting records from the database, you wanna net around the results said, based on certain criteria that you choose. This is where a where clause can have in this lecture I will show you the syntax for were close and how to use it effectively. We're clause specifies the criteria used to narrow down the results The sin Texas Select column names or a star for all the columns. And then you put where give a column, name an operator in the criteria. This is where you can specify the criteria to narrow down your results that you can combine multiple criteria using and our or conditions. This will all start to make sense when we go in secret server and start to type in all these secret quitters, the operators that you can use R equals greater than less than greater than equal toe less than equal. You can also use between for numbers and dates. Now I'm gonna go in secret server and right sequel queries using wear clothes as well as these operators in sequence over click on nuke Ready. We're going to create a new query. I'm going to write another select statement again using the person table. So select star from person, dark person. Go ahead and execute it. And here's that his own set. Now I'm going to add a where clause toe only limit where the first name is. Ken, you can pick any name where then you put the column name first name. I'm going to select the value here equals two. Now, Any time you have to right a string value, you have to enclose it in single goats. So in this case, I am going to put a single court and then type in can, that's all. So go ahead and execute this. And here we can see all the kids that are indeed a base in this case that already few records so you can clearly see them. However, if you had lots off records with Ken and you wanted to narrow it down toe, only look at people whose last name was Meyer. In that case, you can write another laws with where and use and criteria so you can say where first name is equal to Ken. And again, it's case insensitive. So it doesn't matter whether you right and this way our end this way. Both are going to work and last name equals two. Meyer and execute. We got to can Mars in our database Now what if I want to look at the first name or the last name is Meyer, then I can simply go and change and toe, or that's all you need to do. Click on Execute Now we got people whose first name is either can or the last name is Meyer . You can use as many criteria here as you want. Now let's take a look at other operators. We'll start with greater than less, then etcetera. These operators are generally used with numbers and dates. However, you can also use them with string values such as first name, last name, etcetera. Now let's write equity where you're still selecting from person tables. I'm gonna copy this portion like a new quickie and based it here and then add a wear clothes where business entity I D. Is greater than a certain number. Let's say I want to find out. All the records were Business Entity I D is greater than 2000. I go ahead and click on execute. So here are all the records where the idea is greater than hotels. If I have to include I D 2000 as well, then I can put an equal sign, which means greater than or equal to 2000. They can execute, and here we have it. On the flip side, If I want to look at business entity, I d less than 2000 then I'll put less than symbol and execute. Here you have all the records where business entity I D is less than 2000 similar to greater than you can also do less than equal to and execute. Here you have the results said, which also includes 2000. Now let's look at between between operator uses the Big Ning value and end value and returns. The results said that is between those two values, including those two values. So if you want to select all the records that had business entity I D between 2000 in 2010 then you can use the between operator between 2000 and 2010. Go ahead and click on execute, and here you have the results. You could have done similar thing by using in and cranky So let me show you how that could be done with and criteria. So we're business entity, I d remember. We want to include this value as well. So the head and place this with greater than equal to then moved n and type and business entity I d. I like to copy the values less than equal. It's going to match both those criteria and give you the record set. I'm going to go ahead and execute it in here. Is that results? So between just removes the need to use and and simply fights the statement a bit. So this is it. This is how you narrow down your results that using a where clause and the operators that meets your needs. 9. Pattern matching in where clause using like: so far we looked at the where clause using a specified value. What if you want to select records based on the pattern? In that case, you can use like operator in where clause. In this lecture, I will show you how to use the like operate. So, like operator allows you to match a pattern. The Syntex is similar to other select statement. The only difference is in the where clause you use alike. So you have where column name like and then the criteria. You can add other statements as well, using an or or so, let's go to sequence over and see how this works. Here I am in sequence of management studio. I'm connected to Adventure Works Database. Let's go ahead and click on new quickly and make sure your databases adventure works. This time we are going to use a different table. We will use product in production schema, so let's go ahead and write a select statement to see what kind of records are in this table. Select star from production Start product and go ahead and execute it. So here we have a list of 504 products in this table So let's say I want to see all the products that start with Alphabet E. In that case, I can use a like statement. So where and then you give the name of the column, which is name in this case, and then you use your operator like it's case sensitive. So it doesn't matter which case you're writing the like in and then single court and the value that you want to search. So we want to search all the products that start with alphabet A so I can do a and then it should be able to match anything after it. In that case, you use a wild card, which is percent, and then close the characteristic. So what we're saying here is match anything where the first character is A and we don't care what's after that, because all we want to do is look at all the products that start with. So go ahead and execute it. And here you have it. A Hey, get what if we want to find all the products that end with A In that case, you would move the percent sign here and remove the last percent sign all we're saying here is we don't care what it starts with as long as the last character is a Go ahead and execute this and you don't have any product. So I'm going to make it D because I know that our products that anybody and execute here you have all the products that end with D. Now, what if I want to find anything that has a paint anywhere in the name, the word paint, the whole word. So in that case, you can create a pattern like this. Where did say is no matter what it starts with, and no matter what it ends with, it should have the word being in the middle. Remember, this is also case insensitive, so it'll match everything that has lower case painting it or makes case. So go ahead and execute the statement. And here you have everything that has been done. It you can go ahead and try it with different patterns. So let's say I am going to change it and make sure that is a hyphen in the product name. No, I'm saying anything. Ah, hi Fund and then anything. So as long as it is a hyphen in the middle. I want those names executed. And here you have all the products that have hyphen in the middle. So this is how you use, like, go ahead and play with the like statement. Use different tables. If you would like, just practice it. The more you practice, the more comfortable you will get. 10. Where in clause: So far, we have looked at how to select values from a table using a where clause and a specified value from a column. For example, if we have to select all the values from a table, were ideas equal to 123 We don't just select statement as select star from table name where ideas good. 123 Now, if we want to select, were ideas equal toe either 123 or 234 Then we add an or close if we have to use another I d. Than we use. Another are close so we can keep adding or closest. This works fine when you have limited set off values for ID's, say, 234 maybe five when it gets to hundreds off values, adding the or closest becomes too cumbersome and sequel becomes very hard to read. This is where another sequel, Clause called In, comes into play. The syntax for In is simply select star from table name Were I D? Rather than using equal you use in Start up Orontes is put all your values separated by comma and then close the branch offices. So let's go to sequel and see how this works here. I'm in secret server, so let's first right. A secret query to select all the values from person table just to see what's in that table . Person dot Person. Go ahead and execute it. And here you have all the values. Now, let's assume you have bunch of values that were given to you by another department. And they want to know the first name and last name of thes people. All you have with you is I DS. So in that case, you can write select first name, last name, right? That's what we're looking for. And I'm also gonna add the business. Enter D I d. And then you right where I'm gonna show you an example off 23 values. But the same concept applies even if you have 100 values. So where business entered the I d. Equal to one or business entity Ideas. Eagle 200 Our business entity I d is equal to 1001. Our business entity ideas see photo 2034 but says you mean these are the values you have, You go ahead and execute. And here you have the list of all those people. Now, as you add more ID's, you have to keep writing or other way, As I mentioned earlier. Here's to use an enclosed. So let's go ahead and write this sequel using in close. So I'm going to copy this and based it here and rather than equal what's put in, Parenti says. And then you separate your values by comma. So one 101,001 2034 and close it. You don't need this sequel and here does. As you can see it. This is much cleaner, too, right then writing bunch of sequel statements here. Let's go ahead and execute these two statements to see if the values match click on execute and scroll up. And, as you can see, those two values match. So when you have multiple values from one particular column you can use in, please note that if you have to use string values, then you will enclose them in single courts. Let's say we have to now write a statement where first names can or Aaron. Let's go ahead and write that statement, so copy this registered again. Now, instead of business entity, I'm going to use first name, and then your values and values are Can I'm, uh, Paris? You have to put them in single coach, and you can keep adding more values if you would like. Let's go ahead and add another one as well. Now execute this statement and here you have all the fuss names Aaron can and Lola. If you want to use date values here, then you will also use single courts. 11. Selecting unique records: So far, we have looked at the select statements that return all the rose that match your criteria. In some cases, you might have duplicate records, and you only want to return unique records from the database. In this lecture, I will show you what sequel commands can you use to return unique records. Only your secret command is exactly the same as before. You've got your select where and order by. The only difference is that you add distinct before the column names in your select statement. This instructs the sequel toe only return the records that are distinct, which means they're unique. Let's go to Secret Server and see how this works, but let's write our secret equity. This time we are going to write it again on the person table so select and rather than star , I'm going to pick first name plus name from person Start person. Go ahead and execute it. Here you have all the first names, as you can see in this results that there are some names that are repeated. So if in this case you only wanted to get a unique list off names, then you were poor, distinct in front this thing First name and executed again. Now you have a list of distinct first names in order to see if there are duplicates or not . Let's go ahead and ordered them by the first name. So order Hi. First name. Go ahead and executed again. Here you can see all the names. Let's go down to find Kim gonna expand this column. There is only one Kim also you can see now our records are 1018 instead off 19,000 records . That won't return initially. If I go ahead and add last name to it as well, it's going to find unique combinations off first name and last name. It looks at all these columns and then finds the unique combinations of those to return in the results set. Now, if you scroll down, you will see multiple. Kim's right here and you can expand these columns. Here you have all three Kims return. So this is how you get a list of distinct records or unique records from the database 12. Sorting the result set: by default. The results said that you get for your secret equities is sorted in a random fashion, meaning there is no default sort. Order in Secret Server, which is not very useful for the results, had to be meaningful. You might want to sort it in some way. There is a functionality within sequel server that allows you to do that, so let's take a look on how it works. The way to do is you write your secret quickie with all your where clauses and then add an order. White clothes order by takes a list of columns and then, if so, they're sending alphabetically extra it on an assembly. How you want the scholar SC What if you want to change heart despite this column? In ascending order, DSC means sort in descending order on this collar, you can use as many columns as you want in this order by clause. When the results that comes out first, it'll execute the order by based on this column, everything else will be sorted, and within that results set, it'll sort by second column and then within that results that sort by third column, etcetera. Let's go take a look at how this works here. I'm in Sequel server. Let's go ahead and write a new quickly. This time we're going to use purchase order details table, So go ahead and write Select star from the table name. So far, I've shown you that you can write the table name by using your keyboard. Alternatively, you can also click on the stable that you're writing the query for and drag it over. And that works, too. Make sure toe Munsil this portion. Otherwise you're going to get better because sequel server is going to try toe. Just run disporting shin off quickie. So go ahead and click outside. No, it's un selected and click on execute to see what's in this table. Here you have that data about videos, purchase orders, order quantity, product ideas, unit price line total at Centra. So let's go ahead and right in order my clothes. Remember if you had a where clause where claws will come before you can put order by clause . Since we're not going anywhere by Klaus, let's go ahead and remove it and just leave order by order by and then you have to give the name of the columns, So let's first order by order. Quantity. As you can see, the sword is by purchase order i D. Not by order quantity, but let's say we want to sort it by order. Quantity. So put in order quantity O R D. I want to be next. You have to put the sword order, whether it's ascending or descending. So if we want to do ascending order, then we can put a S C four completely. Ignore it. If you do not write in order, it's assume to be ascending, so you do not need to write ascending order or A S C. However, if you want the clarity in your sequel, then you might decide to put Air Sea here. Your choice. Let's go ahead and leave it there and click on execute. Now you can see your purchase order. Ideas are not in any order. However, your order quantity is we just sorted the result based on order quantity. Now let's go ahead and do descending and executed. Now you can see order. Quantity is in descending order. Now let's go ahead and do another sort order on unit price so we can see that in this order Quantity How the unit price are stacking up. So go ahead and buy comma unit price. And do not put any order, which means it will be ascending order. Click on execute. You can see now. Order Quantity is still in descending order. And within that descending order you have the unit price that are in ascending order. So smallest will be first and then larger ones. So now you know how to order the results. That in the way you want go ahead and try a few other combinations. The more you practice, the better you become. 13. Joining multiple tables: So far, we have looked at how to pull the data from one single table. However, in most of your cases, you will need to connect multiple tables to get the data that you need to form a bigger picture for your analysis or for your reporting. In this lecture, I will show you how to connect multiple tables to get the data that you need for your analysis or report in Secret Server. I have the new quit even though open and I am an adventure works database before you conjoined tables in a sequel quickie. You need to be familiar with your database structure. What tables are there, what kind of columns are there and how the tables connect to each other in adventure works . A person schemer contains a lot of information about person, the people who are in the database. You have a person table person table contains information about a person. There is a primary key called Business Entity I D that identifies each record, so this is a unique value that's assigned to each person. Let's go ahead and do a select. Not this time. I've written the quickie in one line, which is fine. You can write in multiple lines on one line, execute it, and here you have the results. That is a sequential number that gets assigned to each person as they are added in this table that identifies that person. So it's a person I D. Sort of. They're calling it business Entity I D. That is also Table called person email address. Here is information on each person's even address, so you've got the person I D. And then each email address has an idea as well. This allows for a person to have multiple email addresses. That's why you email i d is also a key that identifies the email address. It's a combination of these two is unique. A person I D email, i D. And then you got email address. So let's go ahead and take a look at what's in this table. Gonna go to new line like so are from person heart evil address. Select this and execute. You can see all the business entered the I D and the email address i D. And then the email addresses. So in order for us to get a person's first name, last name and email address, we will have to join these two tables. We can get the first name and last name from this table, an email address from this table, and the way to join them is going to be taking a key that's common between both of those, which is business entity i. D. The column does not have to be called business entity I d in both of the stables. It could have bean something else, However. What you need to know from your database structure is which column is used to connect and the way adventure works has named their columns. It's pretty easy to figure out how they connect, so they are all connecting on business entity I D. That means once you know a person, you can go ahead and look at that person in this stable find their I D. And you will get that email address. But we're gonna go through this. Why a sequel joint rather than men really doing it. So let's go ahead and write our secret. Quit for now. I'm going to leave it at all columns rather than picking up individual columns at this time , So go ahead and write your secret quickie select star from person Dark person. Now you have to join this table to another table where you want to pull the data from. In this case, it's email address table from the person schema. So let's go ahead and write or join, which is simply joined. It doesn't have to be upper case. It can be lower case as well. Person Dark email address. Now you have to tell it which column you want to join it on. So then you right on person. Since you have already I didn t 5% table. You don't have to write full person dot person. You can simply do person and it'll know you're refering to this stable person dot business and a D I. D equals two email address dog business entity idea. So what we're saying here is select everything from person dark person table enjoying person dot email address. This star will now apply to the columns from both these tables. So what you will see here is columns from this table as the less columns from this table, the way sequel is going to construct the joint is by looking at I d from person table and matching it to email. Address that business entity I. D. So these two business entered the I. D s have to match, and that's how the joint will be creator. So let's go ahead and execute and see how it looks. So here are all the columns from person table, the first table if you keep scrolling, and this is where you see columns from email address T. And here are the email addresses. Now let's go ahead and limit this toe only the columns that we want. So let's go ahead and change the star to first name, last name and then email address. As you're typing, you'll notice that it knows what options are available based on the tables that you have selected. Let's go ahead and exit your desk. And here is the results of that's what you're looking for. A first name, last name and the email address. Using the same format, you can join multiple tables. Let's say now you also have to join another table, and that table is a person's phone number. So here is a person person phone click on it, click on columns and here you have business entered the i D, which is the person I d and a phone number associated with that business entity I D. So let's join this stable as well. So I'm gonna do another join. And for that you simply write join and then the table name, person, dark person, phone, and then on and the column that you want to join. You can join the stable toe either of these tables by business entity I D. So let's go ahead and do that and person dot business entity I d. Equals two person phone Dark business entity idea. That's it. Now you joined these two columns and you're saying joined the stable to the person table with Business Entity I D. As well as email Address is also joined. Another thing we need to do is we need to add a full number column here. Now go ahead and execute it. And now you have the results that not only got the first name last name, email address, but also the phone number. Let's say now you have to write A were close to limit only where the first name is Kim. You can do that as well here, so let's go ahead and do that aware clause. So where worsen dot first name is equal to Kim, that's all you need. Go ahead and execute it. And here are all the Kims, their email addresses and the phone numbers. Before I end this lecture, I want to call up one more thing. As you were writing the sequel, you could refer to columns by Justin Names because these columns were unique in all these tables, that means first name only existed in one table. That was person table email address only existed in one table called email address table, and similarly for phone, it existed in person phone table. Now, if you have a column that you want to refer here that exist in multiple tables, then you have to provide the exact table name before you can use that collar. So, for example, we know that business entity I d exist in all these tables. So I'm going to use business entity I D in that results set. So let's go ahead and add that as well. Comma business entity I D. Now, as soon as you write the sequel, you'll notice that there is a squiggly line which tells us that there is an adder and sequel. It won't work. If you try to execute it, you'll get an error. It says Ambiguous column name, which basically means is you have to tell it. Which table do you want to pull this value from? I'm gonna use person table in this example, and now that's quickly. Line is gone and you can execute. So that's it. That's how you join multiple tables to get the desired results. 14. Using alias: In this lecture, I will talk about how to use Alias in sequel Port. When you use aliases in your sequel, the amount of court you write will become less. Your sicko becomes much more readable and easy to understand. And you also have better column names. So let me show you in sequel what I mean by aliases and how it helps you in thes three fronts. Here I am in sequence over I'm an Adventure Works database, and I have equity that's selecting first name, last name, email, address and phone number from multiple tables. First name and last name is coming from person table. Then I have joined the email address table to get the email address and then join is on business Entity I D. Next, I'm pulling the phone number from person phone table, and the joint is again on Business Entity I D. Let's add a wear clothes and only pick the records, which have business entity I d. Greater than 2000. So let's go ahead and write. That were clause where business entity idee is greater than 2000. No, you'll see a squiggly line underneath business entity I d that reason is because business entity I D is available in multiple tables, so you have to specify which table you're refering to. In this scenario, it's not going to matter which one you choose, because it's going to be the same value across the stables, but you still have to specify it. Which table your reference to? So let's go ahead and pick it up from person table. So let's start with person dot Business entered the I D. Now it knows that it has to check person business Entity I D. Column. Go ahead and execute it. And here's your results. Now let's go ahead. And also add Business Entity I D. In a select statements. So go ahead and click Business Entity I d. You'll run into the same situation. You have to specify which table this time. Let's go ahead and use email address table. Okay, you had an executed here is that it is outside. So now, as you can see, it's quite cumbersome to write the column names because you have to give the full table name and then you have to write the column name from there. This is where aliases can help. Also, if you want to give more meaningful names to any of these columns, then you can use aliases. So let's see how the aliases work so you can give aliases to your columns and table. I'm going to give an alias all of the tables that we have used so instead off every time refering to person that person, I could simply use my alias and sequel. The way you do it is you write as and then give a name. I could simply say P so person dark person will be Effort SP email address as e. You can give the name that makes sense for you so that it becomes easier to write, and this one has phone. Now you'll notice as you give aliases here and wherever the actual tables were referenced, you are seeing squiggly lines, which means that isn't adder in your sequel. What you have to do here is also changed these to refer to your alias now. So in this case, it's e person. Dark person is P email. Address is e person. Dark person is p person dot phone is just phone and person. His people, that's it. Now you're sequel has less coat because you're not using the full table name. You're only referencing it by the alias. Go ahead and execute it and you get the same results. You can also use aliases to rename your columns. So let's say I want to call this first name as F name got an executed and now this column shows you F name. You can also give it a more meaningful name. So let's say I want to call it first Space name. So go ahead and write first space name. Now you'll notice that it's telling you that there is an error. The reason is that sequel doesn't like the space in the name that's given. So what you have to do is you have to put them in square brackets, right? There's and this Now. Wherever there is a space, you use this format to name them. Go ahead and executed, and here you can see first name. There is another alternative to using square brackets. You could use double courts instead, so let's go ahead and change this to double coats and executed. That still works. So it's your choice how you want to use it. I'm going to show you using square brackets here. Similarly, you can do that here as last name has email address. I'm going to move the rest of the sequel and next line. It doesn't have to be all in the same line, has phone and leave everything else as it is. Go ahead and executed, and here you can see first name, last name, email, address, phone. This is much easier to read, then what we originally got based on the column names. Also, another thing to notice is, I have written all caps here for as lower case here sequel, This case insensitive So it doesn't matter whether you right lower case are uppercase, but that's how you use aliases to make your sequel much better to write, give your columns and name and also results in less gold when you have to write bunch of where clauses and refer to the column names that exist in multiple tables 15. Aggregating data in sql count min max: Okay. In this lecture, I will show you several functions that you can use toe aggregate data from your database. 1st 1 is come. Count allows you count the number of records that will be returned based on your sequel. So let's go do it. Sequel server and see how it works. Let's first right. A simple, select statement. Select star from person Start Person. I'm getting the satyr that tells me I'm not in the right database because it can't find a table name. And look hit it. They said Master. So I'm gonna go change it to adventure work. Go ahead and execute it. And here is your results. If you look down below here, you can see number off records for this quickie 19,972. You can achieve the same in your secret equity by using account function. So go ahead and type count and then But Auntie says and start, that's all had an executed. And here's your results. You conjoined multiple tables and right, we're clauses to do this count. If that's what you need to do, you can also use a column name instead of star to find out how many non null values are there in the database, so that means that call him that you use should have a value. So, for example, if I write middle name here, which I know a lot of records are missing a middle name, then this number should be lower than 19,970 to go ahead and executed. And here you have 11,473 records. Have middle name in them. Now let's take a look at Men and Max Functions in sequel Main function allows you to return the minimum value for the specified column in that table. And on the flip side, Max allows you to get the maximum value from that specified column. So let's take a look at how it works. Both men and Max can be used in any type of column, but generally they are used for number. Column are for the date columns. I am going to use sales order header table to demonstrate the min and max, so let's go ahead and expand this table, and here you have the sub total field, so that's what we're going to use. Let's first find out the minimum order value in the sealed stable. So select star from gonna drag this so I can get the table name and executed. So here you can see all the data. Here is your sub total. That's the column we're gonna use. So let's first right The minimum Well, you so men. And then you have to specify the column names or remove star and right sub total and executed. This is the Lewis sales that they have had to find the maximum value of the sales order. Go ahead and change meant to Max executed. And here you have it. You can even use a bear claws in this if you want to restrict it by a particular date, particular i d. Or to a particular customer, anything that you want to use. So let's go ahead and write ofher clause where customer I D. Is equal to 20,000 who had an executed. And here you have the maximum value for that customer. Similarly, I can go ahead and do minimum for that customer executed. It's the same. That means the customer only had one sale, and that was 589 96 To verify it, you can change this two star and click on execute here is only one line item for that customer and that value waas 589 96. So far in this lecture, we looked at How to Do Count and Min and Max at the table level. What if you have to look at the count off records by a particular column? Say you want to look at how many orders have each customer placed or the minimum or maximum value off orders placed by each customer? This is where group by can help. Let's see how this works in sequel, Let's write a news equals Let's write a new sequel statement. This time I am going to use that count on the sales order header table Your hadn't type account for this table. Select star from sales Order Header and then go ahead and execute it. This is what we're getting for coming that tells us that there are 31,465 orders in this table. Now I want to find out order count by each customer. In that scenario, I can go ahead and put customer I D either before or after I'm gonna put it before Come customer, I d go ahead and click on execute and you'll get an error. The reason you're getting an error is because this sequel statement needs to know at what level are you counting the records at? Since you are picking customer idea in your statement, you're trying to do account at customer i d level. But you have to tell the statement to do so. So go enter and the statement is group by. This is case insensitive, so you don't have to write in all uppercase. And then the column. Lee. Now this tells the statement to pick us to an I. D. In the count and group them at the customer i d level. So go ahead and click on execute. And here it is, customer righty, and then you have number of orders. Aggregated columns would show no column names in all your sequel statements. You can give it an alias so that it shows up properly here, so let's go ahead and do as order cone. You need double coats or square brackets and click on execute again. As you can see Now you got customer I D in order co You can also sort this list if you would like. I'm gonna do order by and let's say we want to do by order. Come see now. Order Count is showing up in this list. Go ahead and click on it and that's it. Go ahead and execute. Bendis sorts it in ascending order. You can similarly do are descending here, Execute in here. Now you can see which customers have, how many orders. You can do the same with men and Max. So let's try a min and Max here. Gonna change cone Two men we want minimum off sub total. Let's go ahead and change this also to minimum order value, cause that's what we're calculating here. And then you have to change the order by clause as well. So if you want to order them by minimum order valued, then go ahead and put that here. Minimum order value executed. And here you have for each customer. What's the minimum order value? I can also change this to Max or even add Max to the same statement. So let's go ahead and add Max to the same statement Comma Max, and then you do sub daughter as max Order value. It's a max order value. Here. You can decide which column do you want to sort them by? Whether by minimum or maximum, let's keep it at minimum and execute. Now you got customer I D. Minimum order value maximum order value. Similarly, you can even add other aggregated value, such as count. So if you want to see how many orders a person has placed, so go ahead and do a comma count and star are you can use customer i d. Either will work, click on execute and here you have customer i D. Minimum order value max Order value and then the count. You can give an alias to come as well, if you would like. So far, we looked at aggregating by one column. In many real case scenarios, you might need more than one column toe aggregate the data. Let's say we want to find out the total order count by eat sales person in each territory so you're aggregating and at the sales person in territory level. Let's go ahead and create a new query For that click on new query, make sure you're in the right database and then right you're ready here. So go ahead and type. Select Sales Person I D. Terry Story I d from a cone star as goto orders from sales order Hagar and then corrupt by this is where you'll need thes two columns. And how do I know these columns? I ds, Because I am familiar with the database. If you're not familiar with the database, take a look at the columns that are available. Scroll down, click on the stable and columns And here you can see all the columns I am using sales for sanity and Tedder Tree I d. And here's your sequel. I'm also gonna do order by Sales Person I d. So we can see breakdown off each salespersons sales for a particular territory. Click on execute And here you have it. That are some sales person. Those are missing. You scroll down. Here is a sales person I d to 74 and their sales in each territory. So Territory four, this sells person had 14 sales territory one had 10 sales. So that's it. This is how you right? You're aggregated functions. For in this lecture we covered the Count Min and Max. In the next lecture, we will look at two more functions for aggregating the data 16. Aggregating data sum average: in this lecture, I will show you two more functions. Toe aggregate your data some an average. As the name suggests, some allows you to get there some off. The value in a given column average, on the other hand, gives you the average off values in the specified column. So let's go to Secret Server and see how these work here in Secret Server. I have a new quit a window open and I'm in Adventure Works database. We will be using the sales order 100 table. So let's go ahead and calculate does some off sub total, which is the total order values select. And the function you use is some again. It's a case insensitive so it doesn't matter whether you use lower case or upper case and then sub total. Let's grab this column because, the Parenti says, and then from you provide the table name unattractive table name here, and that's all we need. Go ahead and executed here you have. There's some off all the orders in sales order 100 table. If you have to calculate the sum off totals by, say, customer I D or sales person, i d. Then you can add them to secret quickie. Let's do it by the customer. I d Go ahead and type comma customer I D. And then you have to provide the group I grew by tells the sequel statement. What is the column that you need to calculate this total by so group by customer I D and execute. So for each customer you have total order. Let's go ahead and also provide an alias to this column as Sorto order value executed again , you have the total order value similar to some. You can also do an average. So let's go ahead and write another query. I'm just gonna write it underneath it and copy the same quality basted here and changed the sum to every G. Every G is the function used to average. And let's also change the alias to average order value. Select this statement because that's all we want to run and execute. This is your average order value for each customer. Now let's bring these two together in one statement. So all you do is copy this and based it anywhere in your select statement separated by comma. I'm also going to move customer I D in front so it's much more readable, had removed its comma and now select the statement and executed. So here you have customer I d. Total order value and average order value. In case you want to verify these numbers, you can also write account statement that will allow us to count the total number off sales . For that customers come star, select a statement and execute. So there are three orders. If you multiply three by average order value, you'll get this value. There was only one order here, two orders here and so on. So that's it. That's how you calculate some an average. 17. Null values in aggregation: in this lecture, I'm going to talk about the impact off null values in aggregation. No values means that column does not contain any value. It's not zero. It's not empty string. That means the value is not there. Null values are excluded from calculations. So when you are doing thes aggregations, these values are excluded from men. Max. Some count and average. It's not an issue for Min Max. Awesome, because when they're ignored minutes just finding the minimum value for that particular collar Mexes finding the maximum value in some is taking some off all the values. And if the value is no, it has encountered, however, it does impact come to an average. I'm going to show you this with an example in the database. So let's go to Secret Server and see how Nell values impact these aggregations. To show you how null values impact your aggregate statements, I have created a sales table in my test database. You're not gonna be able to recreate this and write these statements, but what I want to show you is how it impacts. So when you're writing your own sequel, you're aware off the consequences off having a null value in database columns. I have created this devious sale stable with only two columns. So I'm gonna show you what's in this stable and had our full records. I d in order Value. As you can see, I d one is 10 to his 23 study and then four. There's no let's write sequel statements to see how null values impact different aggregations. First, I'm going to start with Count Select Count, count Start basically saying, Look at all the records in the table from sales. When I do that, it's gonna look at all these records and say Yes, I phone four records Here. You have four records. I'm gonna run this statement along with other statements for the next ones so you can see the values alongside Now. If I go ahead and just do the count on order value, then it should only return count of three. So it's only returning three because it's looking at this order value caller and only picking up the non null values so it only find three. That's why you're seeing three men is going to just find the minimum value. Max is going to find the maximum value, which is 30 right here. Some is going to just do us some off these columns because there's nothing in this column, so it's going to calculate to 60. It's right here. Average is also going toe only. Take these three columns, so when it does the some, rather than dividing by four rows, it's completely going to ignore this row and use only three does, or 60 divided by three. Your answer should be 20 years kind of execute, and as you can see, it's 20. So keep that in mind when you are writing aggregations and sequel. How Your Aggregations Get Impacted by null values. 18. Using aggregated values in where: many times you might have to use aggregated values in your were cross. For example, you might want to select all those customers whose order, Countess less than two or more than 23 You pick a number. That count is an aggregated value, however, where close does not take aggregated value. So in this lecture, I'm going to show you how be used aggregated values to select your records. Let's go to Secret Server and take a look for this example. I'm using Sales Order 100 table. This table stores information about every order the customer has placed, so there is one record for each sales order, and then you have a customer i e. That contains the customer who placed disorder. So what we're going to do is find the customer I ds where sales count. So let's go ahead and write that sequel. First, I'm going to write a sequel that selects the customer I D. And the sales order I d. Select customer I. D. Com a sales order i d from sales dot sales order header. Go ahead and execute this. And here you have all the customer ID's and their sales. Alrighty, as you can see This customer has three records, three records, three records, etcetera. So what we need now is the count off sales orders. So let's go ahead and put account on sales order, cause that's what we're counting and we're grouping at customer level. That means for each customer, Give us the count off sales order I DS and then grouped by customer Raidi. That's all you need. Go ahead and execute it. And here you have all the order counts for each custom. Let's go ahead and also given Alias to our total count. Let's call it as sales comb. Execute again. Now you have sales. Go now if we only want to select all the customers who's count is less than two in that scenario, Normally you would do is where sales count is less than two. However, that is not going to work. As you can see, where close is already showing a squiggly line, that means that is an adder in your syntax. What you need to do here is you need to change where to Having. Another caveat here is that you can't use an alias in the situation. You have to use discount formula here, So let's go copy it. And but the count here and that's it. Now squiggly lines are gone. Click on execute. And here you can see all the customers whose order count is less than two, which is what you can switch it to less than equal to. And then it will also show you those customers that have two orders or less. Here you have it. Similarly, you can switch it and change it to greater, then are greater than equal to, and you can see all the records here. Keep in mind for any off the aggregated functions such as men max average foursome you will have to use having, if you want to restrict your results set by those aggregated values. 19. Combining columns and string values: Sometimes you have to join the data from multiple columns are string values. To get the desired result, I will show you how you can do that in sequel Here I am in secret server. We are going to use person table again. This time we will join first name and last name to make a name column. Let's first right A select statement to select first name and last name Select first name. Call my last name from person Dark person Click on Execute Here you have first name and last name off all the people in person table If we have to join force name and last name to come up with the full name of the person you can do that, Wyatt to Syntax Plus Index is using a plus sign. So let me show you how that's done. Let's go ahead and click on Comma and then first name, then plus last name This is going to join first name and last name together like on execute . Here you have first name and last name As you'll notice there is no space between first name and last name. In order to add a space, you will have to join on empty string in the middle off first name and last name. In order to do that, you create an empty string. This is an empty string and then another plus sign. So first name bless basically say's add another string to it and then plus another string click on execute and here you have to call him. If you want, you can also given Alias has and give your alias a name. Let's call it full name. Keep in mind. Whenever you have a space in your alias, you have to enclose it in double coats our square brackets. I'm using double courts here. Go ahead and execute it. Now you can see full name caller. You do not have to use these two columns here if you do not want to, so I'm going to remove them and click on execute. Now all you have is a full name caller. You can accomplish the same thing. Using another syntax, you have to use the plus signs in tax. If you are using older versions of secret server for newer versions after 2008 you can use the seconds in text that I'm going to show, you know, plus, syntax works in all the versions so far. I am going to copy this statement and based it here. And rather than plus, we will use corn cat function corn cat. And this is where you have to give columns that you want to conquer. You can give as many columns are string values as you want. All of them have to be separated by a comma, so remove this plus sign changed to a comma and similarly removed this plus sign and change it to comma and then close your conquered function. That's it. Go on, get first name. The next one is a spent E string and then last name. You can give as many columns as you want as many strings as you want. Now let's go ahead and execute these two statements so we can compare to that. And here you have the results. You can see both of them return the same values. So there you have two ways off combining data from columns and string values 20. Extracting string values righ left substring: in this lecture, I will show you how you can extract certain characters from a string value. So let's go to sequel server and see how it works. So first I am going to write a select statement to select data from Person Table, Select Star from person, Dark Person. Go ahead and execute it. And here you have all the records. Let's say you want to hide a person's last name and only used the first character. When you pull the data out first, I'm going to change this, too. Fussed name, last name and let's go ahead and execute this. Now I'm going to add another column that contains the first character off the last name. There is an in build function called Left, so go ahead and write left and then open Prentice's. This is how you use in build functions, and as you right, you will see that it provides you the options. That means it takes one, and to values first is your string value that you want to extract the characters from. So in this case it's last name, so go ahead and type last name and then you have to provide number of characters that you want to return starting from left, so I'm going to use one. Go ahead and close the brand pieces. Given Alias as last in this show and closest in double Coach. And that's it for clarity of sequel. I'm going to move from to the next line so that it's easy to read. Go ahead and execute it. Here. You can see the last initial their skates it's be and keep going down. You can see it if you change the number of characters 23 and executed. Now you can see the three characters A, B, B, J, B E and so on E any. So that's how you use left. Another way to extract those characters is by using a sub string, so I am going to add another column. But go ahead and type a comma and let's go to the next line so that it's easy to read and then type Sub string sub string sub string is another built in function that takes three values. First value is the column that you want to use to extract value from In this case again, it's last name. Next, you have to provide where do you want to start extracting the value from will start from Position one and then number of characters that you want to extract, so we'll call it one. All it seeing is from this column. Start from Position number one and extract one character. Let's call this one as last in Michelle Sub String so that we know we're using sub string here. Go ahead and execute it. And here you have the one character. Now let's go ahead and change. There's 23 characters again and execute. Here. You can see these values. If I change the starting position to number two, you will get a different set of values. So that means start for number two and extract three characters and fuss. Case. It will be be be a go ahead and execute it and you can see BB start from second position and extract three characters. That's what sub string does similar to left. There is another function called right, so let's go ahead and write. That right can give it the column that you want to use last name and then number of characters that you want to extract starting from right. If I give three. Then for the first row, it's going to bring B A S. It'll start from here and go back three and give you that string. So let's go ahead and execute it and you can see it's B. A s these other three characters starting from the right hand side. So this is how you extract values from a string column or just any string value. 21. Find length of a string/column and position of a character in a column: many times you have to look at how many characters are there in a strict and then also find position off a character or a set of characters in a string in a column. In this lecture, I will show you how toe exactly do that. For this example, I will use person dot email address table. So let's first go ahead and write a sequel statement to select everything from the stable reason I write to select statement to pull everything from the table is to get an understanding off what columns are available in what data is there. So let's write the statement. Select Star from Execute. We will be using email address column. Put our seats. First thing we're going to do is count the number of characters that are in email address column for each off the road. So for that, I'm going to change start to email address just so that we can see which email address is there, and then use the function card. Len Len measures the length off this column, so let's go ahead and type email address, and that's all you need. Go ahead and execute it, and here you can see total number of characters in each off the email address. This comes in handy when you have to look at if there are any empty columns or to extract certain number of characters from a column. Now what I'm going to do is like another statement to get the position off at Symbol. In order to do that, let's put a comma and do and use a function called char Index. Job Index allows you to find the index that is the position off a given character or set of characters. First thing you need to provide is the character or set of characters that you want to look for. So in our case, we're looking for at symbol. Remember, you have to enclose your string in a single coat because that's how you enclose a string value, then your string value we're looking for at Symbol, and then close this and then the column name where you want to look for this value. In our case, it's evil. Address e mail address and then close the branch offices and let's give it an alias and call. As at position, give us space here executed. Now you can see email address in the second column, we could have given another alias here, like Mingo, and provide Alias as daughter Len and executed again. So Total enters 22 and the position off at Symbol History. If you look in this first row is, you know and add is that position three? Similarly, you can look at rule number three. The position off at Symbol is at seventh position, so you got 123456 and seven is at symbol. So this comes in handy as you're writing your sequel. You have your business use cases to find a circuit character in a string, find the total number of characters empty strings, etcetera. 22. Inserting records in a table: in this lecture, I will show you how you can insert new records in a table. Let's go to sequel server here. I'm in sequel server For this example, we are going to add a new record into Human Resources Department table. Click on this plus sign to expand. So there we can see which columns are available in this stable click on the plus sign and then click on columns. So here are the columns that we need to insert the values into. You have a department I D. A name, group name and modified date. So we will be adding these values. Why are a sequel statement? In most of the cases, the primary he column does not require an explicit value to be entered. This value is automatically generated from the system in order to see if you need to insert a value or not, go ahead and right click on the table and click on design. When you click on design, you will see all the column names here. The data types and whether the allow null values are not null means no value in them. There had an expand this and go down to where it says identity specifications. So these properties here are for this color. When you scroll and go to the next column, the properties change. For now, we need to look at the department I D column and scroll down, and you can see identity specifications set to yes, click on this arrow. What these values are saying is this is an identity column, and the identity column increases by one every time a new value is entered in the starting value was one. So any time you enter a new record, you will automatically generate an I D value, which will be one higher than the previous highest value. I don't expect any other columns to be identity because they're not number values, so they can't increment by itself to go ahead and click on this X to close. Now let's write a sequel statement. So the sequel statement to insert value is insert into. Then you have to provide the table name. We're gonna provide this table name and then put on T's is and then the list of the column names that you want to provide values into. So the columns are name, group name, and then Marty fight date. Then you specify values. Parent teachers start, and this is where you passed the values. The values you pass have to correspond with these columns. Remember, we skipped department I D column because that's auto generated from the system. So go ahead and provide the values for these columns. I'm gonna call the department name as training and group name as training and education and then the modified date. Let's use the current date to get Karen Date. You can use a pre defined function in sequel server called Get Date. When you use a pre defined function, you do not want it in single coats. All you do is type the function name, get date and then opened Prentice's Close Prentice's and then closed The values Prentice is that apprentices here matches this one, and this is a function. Go ahead and click on execute, and you'll notice one drel affected, and this is the time it was inserted. Now let's do a select statement on that table to see if our record got inserted correctly. So select star from and select the statement. Click on execute Scroll down And here is our new record. I D was assigned are dramatically 17. We enter training, training and education and the date time. So this is how you enter records into a table using sequel. 23. Update records in a table: in this lecture, I will show you how you can update the data. The records in a table using sequel This comes in handy when the data is not right are you have to make certain changes based on the new information that you go. So let's go to sequel and see how this works. Let's do a select statement to see what's in Human Resources Department table So select. Start from human resources. Depart, click on execute. And here you have all the records. This is the record that we inserted in last lecture. Imagine you made a mistake, and rather than in person, you need to put and in there just like other places. So you will need to change this record in order to do so, you have to write an update statement. The syntax is update. Can you provide the table name in the next? You have to use the keyword called Set and then the column name. So group name is the column that we want to change is equal to provide your value that you want to set in this column. So instead of training and education, training and education is what we want next, you have to specify, where do you want this record to be updated? If you just go ahead and execute the statement, it's going to change in every single roll off the stable. So that's where Aware Kloss comes into play. So where Department I D and you can use any of these values as long as it's unique and identifies this row, we know department ideas unique, so that's why I'm using it. And Key column is always unique. You can be sure that whenever you're updating, you will update the right record is equal to 70. So go ahead and execute this wonder affected. Select this and execute to see if that update happen here. Now you have training and education. So that's how you update your records in a table. Be careful with your wear statement. If you're wear statement is wrong, you might end up updating the wrong record. What I always suggest is conduct a select statement to make sure you're getting the right role before updating it. So how do you write a select statement? Select that table that you're trying to update select star from the table and then you're wear clothes this were clause is same as this were close that you're going to use to update . So let's go ahead and execute this. Yep, this is one road. That's the one we want to update. That means your update is going to work. You can even update multiple columns in one sequel statement. So let's say if we want to also change the name to training education, you can simply type the columns here, separated by comma. Comma name is equal to training education and similarly, if you want to, also said the modify date, you put another comma modified date equal to and remember we can use a system date, so get date, and that's all. You can update as many columns as you want in that table. Go ahead and select this. Execute wonder Affected. Let's see what's in this row training, education, training and education and the current. That's all. That's how you update records in a table. However, I want to caution you again. Be careful. Make sure you have the right wear statement to update. Otherwise, you are going to mess up your table 24. Deleting records from a table: in this lecture, I will show you how you can delete records from a table. So let's go to secret server here. I have all the records from Human Resources Department Table. I am going to delete this loss record. In order to delete that record, I will write a delete statement, Delete statement, IHS, delete from and then you provide a table name. Now, if you go ahead and execute this statement, it's going to wipe out everything from the stable. However you need toe only delete this record, so you write a rare statement where and then pick our column that'll identify this row uniquely. Generally, you can use the key column. In this case, it's department I d is equal to 17. That's all you need to do. You can use any of the columns listed here. I could have used name group name etcetera. So be careful when you're writing the statement to pick the right column to delete the records. So if I wanted to delete everything that contained Executive General, then I could have used a group name with a like statement, just as you did in select statements. But for now, execute this statement and one droids affected. Go back to your select statement executed, and you can see that role is gone. That's how you write a delete statement you can use anywhere close here. Since delete completely wipes out the record, I highly suggest you right a select statement to make sure you're getting the right set of data before you execute the delete. So how you would do that is, let's say I wanted to delete records from Human Resources Department Table. So I'm going to write a select star from Human Resources Department table and then construct my were clause that I will use in the delete statement. So let's say it's where group name like exact curative percent who hadn't select that. If I used this wear statement in delete statement, then that'll wipe out all these records. So that's why you have to be very careful. Make sure you're select returns the right rose or right kind of data before you execute the lead and never, ever delete without a wear clothes, because that will remove all the records from that table 25. Outer join left right: so far, the joints that we have looked at, match the data from one table to another table and pull the joining records. However, sometimes that might not be enough, however, Sometimes that might not be enough. In some cases, you might want to pull the records from one table and then joining records from the other table with empty values. Whenever there is no match in record, let me show you an example of her results. That and then we'll go to sequel to see how that works. Here are two tables. One is an employee table that contains the idea for employees. The name and department I D. Some employees don't have department I d assigned yet. Then here is another table department table that contains the idea of the department and the department name. When you join these two tables to pull the employee information along with the department they belong to, then you will join them on department I. D. Column. In that scenario, your results that will look like this you got a nail department ideas 100 marketing, and similarly a me 200 and department is that you are. Since Mark and Jim don't have any department idea signed to them, they won't appear in this result. Set, however, you might want a result set that looks like this. You still want all the employees, and if they have matching department and show the department name else, leave them blank. Or alternatively, you might want to get a list off all the departments and matching employees. So in this case, sales doesn't have an employee yet you'll still see her record. Both of these can be achieved with outer joints. So let me go to sequel and show you how that works. Let's frustrate a secret quickie dental. Pull all the customers who have placed an order, any number of order. It doesn't matter for that. We will be using sales order, header and sales customer table. This is the list of customers, and this is a list off all the orders. So first select star from sales dark customer join sales, art sales order Header on and I'm going to give alias toe these two tables A and be so that I don't have to write a full names. A dark customer. I d. Is equal. Toby Dark customer I D. So what I'm saying here is give me everything from these two tables and match them on customer. I d go ahead and execute ER and here you have all the records, all the customers and dead orders. As you can see, there are 31,465 rows. So I am going to write this number here. The way to write a comment in your sequel statement is to do two dashes, so I'm going to write. Total records were 31 46465 Now, if we want to get list off all the customers, whether they have placed orders or not, in this result set and if they haven't placed the orders, then all the sales records will be no or empty. So let's go ahead and write that statement. I'm gonna copy this statement and paste it. Let's give a space here. What you need to do is you need to write an outer join. The way to do an outer join is to decide which table do you want to get all the data from. So in this case, we're saying, give me all the data from customer table table that is in front is considered to be on the left side. So what you need to do is left joint. What we're seeing here is from the left table, which is the first table. Give me all the data. And if there is any matching data from the other table, then give me those records. Otherwise, leave those records as no. So let's go ahead and execute this. And here you can see now you have 32 166 Earlier you had 31 465 That means you got more records here. I'm gonna stroll here to see which customers are missing. Records keep going down. And here you have all the records that don't have any sales information. So here is a list off all the customers that have no sales record. These are the extra ones that showed up that we're not showing up in this first quickie. So this is how you write out a joint. You join the tables as you normally would, and then add left. If you do around, if you do are right, outer join, that means it's going to give you all the records from the stable. Whether they have a matching customer or not. Now let me show you how you can write the same quality but using our right joint. So I'm gonna copy this and based it here. Now I'm going to switch my tables. So I am going to move sales order header as my table A and then they're stable as my table B. And then, since my table B is where we want, all the data from that table is now considered a right table. So rather than left, you will have to write right here. So go ahead and executed. When you execute, the number should still be 32 166 Click on execute and you can see 32 166 Same as this previous query. So keep in mind, left basically means the first table in the joint right means the second table in the joy. That's how it's constructed. So that's how you write. An ultra outer joint allows you to join two tables and get the data from one table, even if it doesn't have a matching record in the other table. 26. Closing comments: I hope you have enjoyed the score so far. There is a lot more that I have planned for this course. I'm working on several new lectures. Those lectures will be added as they become available, so stay tuned and watch out for messages from me.