6 Essential Skills to Make A Great BI Analyst Series 3 | KAIMA L. | Skillshare

Playback Speed


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

6 Essential Skills to Make A Great BI Analyst Series 3

teacher avatar KAIMA L., BI/Data Scientist

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

54 Lessons (3h 24m)
    • 1. BI COURSE SERIES OVERVIEW

      3:15
    • 2. Why SQL

      2:34
    • 3. Why My SQL

      2:16
    • 4. Intro to Databases

      5:14
    • 5. Relational Database Fundamentals

      5:13
    • 6. Comparing Database and Spreadsheets

      8:05
    • 7. Importance of Database Terminology

      4:32
    • 8. The Concept of A Relational Schema

      4:56
    • 9. 8 The Concept of Relational Schema Foreign

      4:53
    • 10. 9 The Concept of Relational Schemas

      3:05
    • 11. The concepts of Relational Schemas

      4:44
    • 12. Installing My SQL Work Bench Server

      8:23
    • 13. LINK GUI WITH MY SOL SERVER

      2:18
    • 14. Familiarise Yourself with MYSQL Interface

      4:29
    • 15. Coding Tips and Best Practice

      5:13
    • 16. Coding Tips and Best Practice 2

      3:36
    • 17. Loading the Employees Database

      2:05
    • 18. Using SELECT FROM

      4:27
    • 19. using WHERE

      2:22
    • 20. USING AND

      2:11
    • 21. USING OR

      3:31
    • 22. USING IN NOT IN

      3:00
    • 23. USING LIKE NOT LIKE

      3:26
    • 24. Using Wild Characters

      1:53
    • 25. USING BETWEEN AND

      2:15
    • 26. using is not null is null

      1:35
    • 27. Using Other Comparison Operators

      4:20
    • 28. USING SELECT DISTINCT

      2:04
    • 29. Getting to know Aggregate Functions

      5:15
    • 30. USING ORDER BY

      3:11
    • 31. USING GROUP BY

      5:24
    • 32. USING ALIASES

      1:57
    • 33. USING HAVING

      3:11
    • 34. WHERE VS HAVING

      3:35
    • 35. USING LIMIT

      4:18
    • 36. APPLYING COUNT

      4:03
    • 37. Applying Sum

      1:29
    • 38. MINI AND MAX

      1:12
    • 39. Applying AVG

      1:55
    • 40. Rounding Numbers with Round

      1:53
    • 41. What are JOINS

      3:33
    • 42. THE FUNCTIONALITIES OF INNER JOINS 1

      5:17
    • 43. The Functionalities of INNER JOINS 2

      5:01
    • 44. Extra Info of USING JOINS

      3:43
    • 45. Duplicate ROWS

      3:56
    • 46. The Functionality LEFT JOIN PART 1

      4:23
    • 47. The Functionality pg LEFT JOIN PART 2

      5:15
    • 48. The Functionality of RIGHT JOIN

      3:57
    • 49. Differences Between the New and Old JOIN SYNTAX

      4:14
    • 50. USING JOINS AND WHERE TOGETHER

      2:38
    • 51. The Functionality of CROSS JOINS

      7:37
    • 52. Combining Aggregate Functions With JOINS

      2:08
    • 53. JOIN MORE THAN TWO TABLES

      3:37
    • 54. TOP TIPS FOR USING JOINS

      5:04
  • --
  • 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.

67

Students

--

Projects

About This Class

About This Class

Hi! Welcome to our Business Intelligence Analyst Course Series. This course entails the six essential skills you need to make a great BI analyst

We are excited to present you a course series that stands out.

This BI program is different than the rest of the materials available online.  

These are the precise technical skills recruiters are looking for when hiring BI Analysts. And today, you have the chance of acquiring an invaluable advantage to get ahead of other candidates. This course will be the secret to your success. And your success is our success, so let’s make it happen!  

  • Introduction to Data and Data Science  
  • Statistics and Excel  
  • Database theory  
  • SQL  
  • Tableau  
  • SQL + Tableau  

Here are some more details of what you get with The Business Intelligence Analyst Course:   

  • Introduction to Data and Data Science – Make sense of terms like business intelligence, traditional and big data, traditional statistical methods, machine learning, predictive analytics, supervised learning, unsupervised learning, reinforcement learning, and many more;  
  • Statistics and Excel – Understand statistical testing and build a solid foundation. Modern software packages and programming languages are automating most of these activities, but this part of the course gives you something more valuable – critical thinking abilities;  
  • Database theory – Before you start using SQL, it is highly beneficial to learn about the underlying database theory and acquire an understanding of why databases are created and how they can help us manage data  
  • SQL - when you can work with SQL, it means you don’t have to rely on others sending you data and executing queries for you. You can do that on your own. This allows you to be independent and dig deeper into the data to obtain the answers to questions that might improve the way your company does its business  
  • Tableau– one of the most powerful and intuitive data visualization tools available out there. Almost all large companies use such tools to enhance their BI capabilities. Tableau is the #1 best-in-class solution that helps you create powerful charts and dashboards  
  • Learning a programming language is meaningless without putting it to use. That’s why we integrate SQL and Tableau, and perform several real-life Business Intelligence tasks  

 Sounds Awesome, right?  Our course comes with

Our courses are unique and is equipped with:

  • Work with real-life examples  
  • Provide easy to understand and complete explanations  
  • Create beautiful and engaging animations  
  • Prepare exercises, course notes, quizzes, and other materials that will enhance your course taking experience  
  • Be there for you and provide support whenever necessary  

We love teaching and we are really excited about this journey. It will get your foot in the door of an exciting and rising profession. Don’t hesitate and subscribe today. The only regret you will have is that you didn’t find this course sooner!

What you’ll learn

  • Become an expert in Statistics, SQL, Tableau, and problem solving
  • Boost your resume with in-demand skills
  • Gather, organize, analyze and visualize data
  • Use data for improved business decision-making
  • Present information in the form of metrics, KPIs, reports, and dashboards
  • Perform quantitative and qualitative business analysis
  • Analyze current and historical data
  • Discover how to find trends, market conditions, and research competitor positioning
  • Understand the fundamentals of database theory
  • Use SQL to create, design, and manipulate SQL databases
  • Extract data from a database writing your own queries
  • Create powerful professional visualizations in Tableau
  • Combine SQL and Tableau to visualize data from the source
  • Solve real-world business analysis tasks in SQL and Tableau

Are there any course requirements or prerequisites?

  • No prior experience is required. We will start from the very basics
  • You’ll need to install MySQL, Tableau Public, and Anaconda. We will show you how to do it step by step
  • Microsoft Excel 2003, 2010, 2013, 2016, or 365

Who this course is for:

  • Beginners to programming and data science
  • Students eager to learn about job opportunities in the field of data science
  • Candidates willing to boost their resume by learning how to combine the knowledge of Statistics, SQL, and Tableau in a real-world working environment
  • SQL Programmers who want to develop business reasoning and apply their knowledge to the solution of various business tasks
  • People interested in a Business Intelligence Analyst career

Meet Your Teacher

Teacher Profile Image

KAIMA L.

BI/Data Scientist

Teacher

 

 Hello!

My name is Karima and I am super-psyched that you are reading this!

Professionally, I am a Business Intelligence Analyst with over five years of experience in e-commerce,, retail, information technology, other industries. Today I leverage Big Data to drive business strategy, revamp customer experience and revolutionise existing operational processes.

I combine my real-life experience and academic background to deliver professional step-by-step coaching in the space of Data Science. I am also passionate about regularly presenting Big Data to individuals and groups with no background knowledge in the field

To sum up, I am absolutely and utterly passionate about Data Science and I am looking forward to sharing my passion and knowledge ... 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. BI COURSE SERIES OVERVIEW: I and welcome to our business intelligence analyst course, Siri's. This course entails the six essential skills you need to make a great BR on a list. My name is Karima, another business intelligence analyst. Was spent a significant amount of time walking on business intelligence dust requiring more skills like statistics, Excel, database story, Bablu, beytin, the combination off SQL and tableau on even the combination of SQL with tableau and biter. I'm really excited to present to you because Siri's that stands out because each other call Siri stitches. You is done alone skill you need a Xabi are on a list, and by the end of critical Siri's, you would know how to apply Eat in the real world walking environment they cost. There is curriculum that has been prepared for you. Consists off diverse sets off topics are serious. Start with getting you to know the world of data and data signs. We explain all that to Science Jagan's on areas of activities before diving into more sophisticated analytical. Thus, once we have built a solid theoretical foundation, we go through statistics applied in excel and teach database management in s key. Well, then you will be ready to land disabilities off data of data visualization are reporting by creating professional visualizations with Bablu public, one of the most popular, be Iittle available all day. What I consider to be remembered if it's off this program, is that you will let out of leverage, ask your skill and combined them with Bablu to visualize the data contained in disobeys files. Once you know how to do that, it is time to start accordion infighter. But pretty soon he will understand that Lenin, a programming language, is meaningless without putting it to use. That's why, in final Siri's off, this course will integrate SQL bite and and taboo, which would allow us to build a model that predicts clients default rates on. Visualize your findings in taboo, and this will take your preparation to the next level. Because Siri's is it truly also adventure. To get the most out of this series journey, please don't skip any of the lessons in every Siri's as well gradually beauty or knowledge . Given that we teach several skills, it is essential he starts at the beginning as this lessons at the foundation, you need to tackle the more advanced topics you finally town in the program. Many of our lesson contained downloadable resources that will help reinforce what you've learned, such as cost notes, exercise files, pdf materials and the notebook files. Everything is included and can be downloaded easily. I strongly suggest you complete all exercises as they are designed, not only for practice but also as an additional source of information that will end the hands. You'll be I problem solving skills. Are you excited? Awesome. Let's begin this journey together. See you they 2. Why SQL: So why I ask you put yourself in the shoes of an employee or a manager are becoming walks with large amounts of data. For example, it could working in business intelligence dates of science, database administration or back in the Web developments. Any of these jobs believes two story large amounts of data. Therefore, before carrying out any analysis of the data, you will have to retrieve it as a consequence the focus of our courses, namely on developing the skills required to solve business. A logical problems on applying solutions in his software that allows you to extract the desired information from the data bees. To achieve all this unit S Q. O. S cure means structured query language. It's a programming language specially designed for working with databases. It allows you to create manipulates and Shia data, especially data from relational database management systems. The way to tell the computer to extract information from database for you is by writing queries in SQL Equivalence Query Query is a piece of code in Greece. In the completed to execute, it set an operation that would deliver the desired output. The latter process. It's called Queer in the Data bees briefly. SQL is the language that allows you to write queries that a computer can execute on them. Provided turbines insights in return. The goofing about SQL is that it's an intuitive and easy to lend language because for as this does not mean, you need to be a simple task, not at all. Sometimes the business problem you will solve will be peculiar. I will involve a procession of large amounts of data. This by being relatively simple in structure. Sq comes very powerful while computing very complex task. This is why, in this course, theories provide the raw skills you will need to be coming. What programmer it deter Analyst. It gets a scientist or a business intelligence analyst. The idea to combine video lessons on advance exercises that assure you SQL is cool and fun to work with. SQL. It highly demand skill on the job market. No matter how you look at it, stay tuned for the next lesson to understand what it is and why it is the most suitable software environments for this course. Thanks for watching 3. Why My SQL: okey. So at this point, you may be telling yourself, Great. Let me install software, continue rescuer and start lending. Unfortunately, it is not so simple. Why? To implement SQL commands? You need it. It's a busy management system often called a database or a platform. The problem is, there are a few popular databases all day, which are all based on SQL. The support various forms off the original long, which on there are only slight differences between different versions. As a developer, you want to lend out to use the one that is most suitable for your situation. Well, bizarre lesson entirely on my ask your boys. You know there are other platforms like Oracle, SQL Lights, Microsoft's SQL Sever Prosecute Sequel, Maria DB, Microsoft says also won. The minor differences between the version of SQL Language means that transformed for Monday Tubby's to the order, which usually happens if our Children jobs will not be an obstacle for you. If your user of another platform you can still take the cause on easily, apply the tools we teach. So why do we choose my escape? Oh well, because it is reliable, mature and open. Source. The latter means it is free. It is used by many institutions and companies, including Facebook, YouTube, drop books, Twitter for interest, Book in a Calm and Lyndon Considering the world renowned DB engines. Rankin My Else Curio comes as the first open source Relational database management system. Yes, Morocco goes on top with only a slight advantage. But don't forget, it is a paid soft way. Therefore, I'm not providing you with the best expiry. It's possible we choose my sq. Oh, now let's dig into some day. Today's on ehskyoo jury. This will provide the sets off tools you will need when we deal with various SQL Trust lessons to come. Thanks for watching. 4. Intro to Databases: all right. One thing you will notice when starting program along, which is that the concept you encounter are interrelated. This means for costume on a single topic can hardly deliver the content the most explained in its entirety. To this to me, SQL mixing exception this by that we will start from some way right. You'll be much faster in learning or writing efficient queries. If you go through a brief introduction to databases, it is the best thing to begin with. So here's what I would like to share with you. The temple you see here contains data about the customer sales off the furniture store, and this is how we should read this information. Purchase Number one was registered on the third of September 2016. Then customer number one bought the item, which called a one. Each of the four elements has a specific meaning. We'll call each one. It's our value. All four deter values may cope one record a record as each entry that exists in a table, it corresponds to the rule off the table. Therefore, this Ford it's our values. Form one record and visit our values regarding porches. Number to form another record, you could use the terms rule on record interchangeably. Besides the rules, you can see the details separate it into four columns or fields. If you It is a column on its able containing specific information about every record in the table. This means or records, and it's able regardless. If there are 10,000 or 10 milion, will have a certain porches. Number it off. Coaches. Customer Heidi on item information. When did you have is organized in rows and columns? This means you're dealing with stored trouble, a data that is important information because you will often see in the literature that database management relates to data stored in tabular form greats. Here comes the more interesting parts in this table. We know nothing about a customer besides a Heidi. The information of our customers is stored in another table called customers. There are several feuds. Such cars the first and last names email address on the number of times customers are far the components in our store. So what would the logic of that structure be? Every time we have a customer with a 90 number one in the sales table, we can refer to the customer with the number one any customer stable and see a name email address on the number off complaints frowned. Sin goes to the item table it contains the item could put out description. It's innit Price I d. On the name of the company that was delivered it on a company's headquarters phone number. Yea, the connection between the sales table on the Items table is not a customer i d. For the item called Well, we could stop this information into one table and to look like this. As you can see, it is huge. I would not want to imagine what it's able would look like if we had registered over 10 rules. My point is there are too many columns, and it is hard to understand what type of information is contained in the larger Tebow. Relational algebra allows us to use mathematical logic and creates in relation between a few Tibbles in your with that allows us to retrieve data efficiently, namely district tables. Sales customers on items are related through the customer Heidi or item cold columns on for a relational databases. And importantly, it won't be as specific. Meaning contains data characterizing it. One of the tables contain information about sales, the other about customers, and they thought about the items to recap. Remember, did it. Our values in your room for my record in its able in, each column represents a field that carries specific information about every record. If you're related tables for a relational databases, and for those of you who are interested in slightly more technical definitions, remember, the smallest units that can contain a meaningful settle data is called an entity. Therefore, the rules represent the results out entertaining table. The column is its vertical entity. The table is a bigger data entity on its own. It can also be referred to us it. Abi's project a single rule off the table being a single occurrence off that entity can also be called an entity instance. Okay, great. So stitching for the next lesson where you'll see SQL Room when working with relational databases. Thanks for watching 5. Relational Database Fundamentals: All right, let's shift gears. What was the first thing that comes to your mind when you hear the world data bees? For many people, this question is more challenging. Done. It's my seem at first announcer, like a big farm where more information is told is not satisfactory. I would not please potential employers. You should remember there are two minutes IVs off databases, relational on a non relational, the formal beautiful coast off. This calls what a lot of regards more complex systems. Although understanding non relational databases requires a serious mathematical and programming background, some of the logic a plight and it's called in is the same as a secure, likewise religion auditor busies of a few advantages on the old. It's more bit off theory will explain why they're still the preferred choice in many companies and institutions. Databases Main goal is to organize huge amounts of data that can be quickly retrieved upon users requests. Therefore, they must be compact, well structured, on efficient in terms of speed on data extraction. Today, people need such extra efficiency because it occupies memory space on the bigger size, the most longest digitize on this lower the retrieval process becomes if we have it, it appears, contain one million road table with many columns. Then every time a request had been received, the several most lewd, all the recalls with all fields. I need to take too much time for its asked to be completed. Don't forget. Every single is a container off Information on requires bites off storage space, hence loading that motivator will not be an easy task for the computer. So what allows us to contain so much data on this ever. But let us efficiently use only the portions we need for analysis. The secret lies behind the use of mathematical logic originating from relational algebra. Please don't worry. We will not bother you with math. Imagine each table would deter as represented by transparent circle. That means all data values off the table, categorized by columns or, as we often call them, films. Now, if I do, it'll be is a consistent off only one table. A John Circle would represent the entire day. Today's something like this huge table for my fiction example, where the sales disobeys on when we need a piece of information from the database. For example, if we wish to see who has brought something on a certain date will have to lift the old circle and in search for what we need. This challenge seems weak on the process of data. Extraction will not be efficient. See what can happen if we split a circle into three smaller circles, just as we did with the sales databases for a second was done for the sales table, the other for customers on the last one for items that are various theoretical combinations between three homosexuals. But in our databases, we have the following model sales, and customers have the same customer already. Column and sales on items out of sin Item called column This week. We can see the circles overlap, and they have common feuds. So if we like to extract this information, the new of the customers who are for chased something on a given date who need only the date off watches column from the sales table on the first or last name from the customer's table. So to satisfy this request, do not need to leave the third circle for marital. These items this week we consider energy or, more technically, increase efficiency less detail represented as early, too. On the three circles will be involved in this operation. The mathematical trick lies in relating the tables to one another. Relationships were formed, namely through these common fields. More explanation about this technique. We before write it in our next lessons. Anyway, I'm sure that now you understand why we use the term relational databases. Some professionals may refer to the tables or the cycles in a plot US relations, because genetically they are the smallest units in the entire system that can curry Integra logical meaning. Likewise, the tree cycles are all parts off ourselves. Data please. Where we combined it obeys on its existing relations. We'll pretend the famous term relational databases management system frequently abbreviated us. Our D B. M s with optics geological illustration makes things clearer. Ehskyoo is designed for managing relational database management systems. I can do that by creating relations between the different tables in it. It Abi's stay tuned for the next lesson. Where will outline the most substantial differences between a database on s precious? Thanks for watching 6. Comparing Database and Spreadsheets: Okay, Now I understand why people use data busies, however electing emergence of of you XO users. As do imagine an Excel spreadsheets where we talk about double A data. Please don't do that. Data tables, databases on Excel spreadsheets are different things. In this lesson, we're focused on the differences between spreadsheets and databases. This exercise will be relevant not only for current, like so users deserve. You would not use Exubera bloody. We still have the chance to understand the advantages and its advantages of using deter busies all spreadsheets. Okay, let's start with the definition. What is a spreadsheet? It is on electronica ledger on electorally conversion off people or accounts in worksheets . It was created to facilitate people wanting there to store the accounting information in tabular form digitally so it is possible to create tables in his spreadsheets. This is one of the reasons some people believe spreadsheets and databases are interchangeable. What in reality, the sirens there are similarities between the two but can contain in large amount of tabular data and can use assistant data to make calculations. Third, need us for sheets. No databases are typically used by a single person, so many uses will walk with the data. The differences between the two forms off data storage locker in the way the street cart or sixties are implemented. Okay, imagine it. Spreadsheets. Every cell is treated as a unique entity. It's conform any type of information. It dates on integer value is tring name. And then not only can we have different settle value in various, but you can also have a specific for months to this cells. This is not inerrant. To deter bees is they only contain raw data. Each cell is a container off a single data value. It is the smallest piece of information varies most percent the type of data contained in a certain field. This feature prevents in Advent. Since mystics, for example, in a feud containing data values should use our try to insert his drink. The software was sure a narrow, and she will have the chance to call it myself. This one's opponent excel if he insults string in the column with the data values you won't obtain on their own message, Excel was toe the string value in his spreadsheet data can be stored in its so while on a date Abi's data is told. Any record of a table, meaning you must count the records in its able to express our long. The detectable is not the number of the cells, and that is it. You cannot speak in front color or size or you care about Is information been stood you don't care about? For Martine are, Mingo is to save the numbers. Another substantial difference is that in the spreadsheets, different cells can consent solutions such as functions and formulas. This means if you want to combine to introduce, the result will be stored in another cell and it'll bees. All calculations on a pressures are based on the existing data on a don't after eight retrieve. Oh, there is a specific future called views, similar to the tables in which you can do a calculation. These objects also contain column that's come in no more columns that the wounds in the tables or could contain it set in type of copulation. There is no way you can make mistake. A record editor with the car pollution. It's obvious features mentioned so far improved data integrity. You can also store different sets of data and the same feud it is unlikely somewhere will make mystic. It is our value for outcome of a calculation, especially in large data sets that my integrity is a strong advantage when working with databases. Naturally, you might think a spreadsheet can contain multiple worksheets so one can create tables in the worksheets and then use the worksheets to create relations between the tables. Why bother using relational databases? Well in his spreadsheets, such relations will be logically limited instead of setting up spreadsheets or worksheets. One consent operations between the tables and this will be about the performance off operations increasing the speed with which you shoot manipulated data states all be. It's powerful for men. It's a constant. Sees. Spreadsheets have their limitations. Excel is incapable of handling over one million rules of data. These immediately induces us to look for a solution. Usually, the fix issues databases where having 5 to 10 million records is not a problem. Referring to motor user property. Spreadsheets are logging essentially. Every person was updated Tijuana spreadsheets with new data, for instance, if there is a new party is to register or last name in a customer's table to correct every user. Most make this changes Montali. You would justify everything good dogs and letters vision off office solve this issue. But they do so only partially. In Google Books, you might have trouble finding out who changed or deleted information incorrectly, which often leads to a cumbersome situation where people have a hard time organizing their test, as opposed to that. You saw any data control language that little bases provide a stable structure, controlling US s commission on the user restrictions. One person can make a change that is viable toe. Everybody instantly did features, increases, efficiency and it's a consistency. Were using data disease cause, he dreamed, deter integrity and data consistency. Using databases eliminate duplicate information, which is another way to save space and increase efficiency. You got a customer stable, you know, a certain forced our last name corresponds to unique email address. So if you know John McKinley has changed his email, are you using a spreadsheet floated with data? You may change the email address words on accidentally miss updating the same address in another record. This may lead to an adventure and mystics. They can be avoided when using a relational databases on accredited user only needs to assess the customer stable and change your McKinney's email address. There just wounds not only with this operation save time, but it also anticipates inconsistencies. So what we discussed in his lesson are highlights. Why did a busies are a better environment for storing and keeping track of data when walking with multiple dimensions on large amount of data? Spreadsheets of the advantages as well? They are an excellent tool that allows us to carry out extensive analysis, but for the easier retrieval on updating of detail, efficiency, deter, consistency, detail, integrity, speed and security. Relational disabilities are definitely the structure to opt for. They can stall lots of raw data on our excellent when separate in the data from the way it is displayed for analysis. As you saw, it will be a good idea to stop trying to visualize data tables in the form off spreadsheets . They are different. Stay tuned for the next lesson where we will offer more did today's terminology. Thanks for watching 7. Importance of Database Terminology: to complete our theoretical preparation who need to learn more disobeys terminology. This is a necessary step that will help falls when we start coding in SQL later it does go through. The entire process is off Creating data bees Azuma editor beads containing customer sales data has not been set up yet. Okay, so imagine you're the shop owner. Are you realize you have been selling goods quite well recently? You have more than a 1,000,000 rules off data. What do you need then? It it Abi's. But you know nothing about it of these do you call and ask? Ask your specialists Knew you needed it. Abi's designer. She will be in charge of deciding out. Organize the data into tables on how to set up the relations between these tables. This step Escutia, if you just have design is not profess from the beginning, the system will be difficult to work with. Our wardens Facilitate your business needs. You will have to start over again. Consider any time on data are money involved in the process. You want to avoid going back to 0.0? What? To deter? These designers actually do the plot. The inside it of these system on the converse, using a visualization to there are too many ways to do that. One is drawing an entity relationship diagram or are diagram for brief in looks like these . Andi, as its name suggests, the different figures represent different deter identities on the specific relationships we have between entities. The connection between the tables are indicated. Two lines this one off represents in detail disease. It's bar Ford unprofessional. What's it is complicated. We will not focus in depth on your diagram in this course, but you should know they exist are referred to the process of disobeys design. I'm not from of representation of editor bees is the relational schema. There's an existing idea off how digital days both be organized. It is useful when you're setting off the structure and organization of the database. You would like to create more precisely a relational schema who look like this. It represents a table in a ship off Rotundo, the name of the table at the top of direct tango. The column names are listed below Relational schema, Immediate Abi's formally disobeys schemer. You can also see lines in the key 18 how tables are related. In a little tease us, you'd probably noticed we have some other features, but we leave the explanation for the next lesson. Was it it appears, designed? Possesses? Don't. The next step will be to create the databases to this moment has been ideas planning abstracting king on design. At this stage, it to be called to see sq, can be used to set up the databases. Fiscally are supposed to control my needs. Obstruct Lee. Then you can enjoy the advantages of data manipulation. It will allow you to use a deficit to extract business insights that aim to improve the performance and efficiency of the business you're working for. This process is interesting. On practically the mid part of the course related to deaths. I remember well thought out Little Easy's that are carefully designed and created a push up record sides for determining pollution. If you have done good work with the steps, we could write effective queries on navigate inevitabilities rather quickly. You'll often edit them database management. It comprises all the steps business on the takes to design, create and manipulate instant abates successfully. Finally, database administration is the most frequently encountered job amongst all its up. His administrator is a person providing daily K on maintenance of Italy's. A scoop of responsibilities is narrow, uh, regarding the world's carried out by a database manager, but she is still indispensable for the databases departments of the company. This lesson allows us to lend set in terms you'll be using often when dealing with data Deasy's and in this lesson will explain how many and four original schema come before you to understand the function in over certain it Abi's see you there. 8. The Concept of A Relational Schema: All right, so in this lesson, who uses database to illustrate the concept of original schemers? A little better. The data will be stored in four Seibu's Seals Customers Items on continents You see how the stables Harvey tabular sheep? Let's see original scheme are can be applied to represent them. Let's do the landscape piece by piece. We care about the sales, the capital off our company. And that's why we have a sales table. It's also the number which which each poaches was registered. The date off the porch is the respect of customer. I d on the item called. So in this table, the dates of a few patches is Mikko inside because it is no more to sell some of the same goods in the same D logically in effect column. The idea of a customer me a pair a few times. Given that some customers make more than one for cheese, people may have bought many units. It's off the same product. Hence, there is a possibility to see the same item code a few times in the last column. This reasoning does not apply to the first few do. Each porches is unique in deter busies. This means all the numbers in this column will be different because which parties will be assigned to a specific number that is unique. There is a term for such type of feud, a column or a set of columns whose value exists and is unique for every recorded notable is called a primary key. Each table can have one on only one primary key in one table. You cannot have three or four primary keys, for instance. In ourselves, stable purchase number can act as a single column primary key, and there'll be no other primary keys. It did have these creators decide. The Primerica over a table will be composed of a set of columns, not just one column in the sales table. Put in above patches on the debt. Off approaches can be talked off. The unique identifier pay off the detail in this table, for instance, purchase number one on porches date, which is the third of September, will form a unique pay. And so we put his number two on the same date, third of September. This means the two rules are different. Please don't overlook the fact that you cannot have a combination of the same party is number one on the same date. Third of September. Mother wounds in your table. I didn't table or the dates for the difference. This is because, as we already said, a primary key will be unique. Good motorways of representation makes sense. Since having one column primary key, it would mean opportunities in our databases would be recorded on the difference number. This means they can go from one. So hopefully a 1,000,000 or two, this logical preserve the uniqueness of the porches. Okay, so we proceeded purchase number at a single column primary key also. Therefore, you can think of a primary key as a field or a group of fields that identifies the content of its able in a unique way. For this reason, the primary keys are also called the unique Identify us off the table. Another crucial future of primary keys is that they cannot contain no values. This means in the example, with a single column primary key, there must always be in value in such a any rules on that. This column You cannot leave it blank. Please be careful. And don't forget this characteristic of the primary key. Technically, to create a relational schema, we draw a table in the form of Iraq tango, our place, its name on top. Then we list the fields vertically are supposed to the results organization in the spreadsheets. The feud that is the primary key off the table is usually quoted on top of the other fields , which is more important. It is always underlined, keeping these rules in mind. When he spots this table, it will immediately know it is called sales. Its primary K is purchase number on. There were three other fields did off coaches. Customer Heidi on item called. This image corresponds to tabular data in the following before, all right, good to conclude the temple for presenting its evil would help off. Such a rectangle on the names of the field is called relational schema. When you combine the original skimmers off the tables, we have any disabilities. This gives those database schema. Informally. It is okay to call the representation of the inside of a breeze in plural relational schemers. Thanks for watching 9. 8 The Concept of Relational Schema Foreign: all right. The customer Heidi doesn't give me much information about how to purchase a does it. A few lessons ago, we said we was told her first and last names email address on a number of complaints submitted in another table called customers. Here's a table containing all these views as well as a customer Heidi Column, which is unique. There is no need to keep the information about the same customer more than words. Rights. Therefore, in this table, the customer Heidi is a primary key. The other columns represents the rest of the information about the customer. First name, last name email address on a number of complaints. Interesting. We already have two tables, sales and customers. We know they're really true. The customer. Heidi Feud on As you can see, a customer, Heidi Column insists in the sales table. The coins and only two column names is chosen clearly for convenience. The same relationship could be based on columns with different names. Column Heidi and just i. D. For example, what is important is that the information in the sales table can be obtained through the cost Ahmadi column and customer stable Great lasted two. Using the same name for Boots columns. All right, here's the big trick custom. Heidi is the primary key for the customer stable on the foreign key in Excels Table and the original skimmers form of representation relation between tables. I expressed any fully away. The column name that designates political march is a foreign key in one table, and it's connected with the corresponding column from another table. Often, relationship goes from a foreign key to a primary key. But in modern circumstances, this will not be the case to catch traditions on which database is viewed. Always look for the foreign keys as a show horse where relations are. I was just showing. Only Converse first issued, indicates the customer, Heidi is a foreign key in the sales table with F K in Pockets Nest to the Colombian. Second, him was drawn Haru that begins from the same level as a field name off the foreign key on hands pointing at a column name from the table it is related to. Don't worry, the arrow would be street. What matters? Our It starts on its hand, a present for you. An example from our sales deter these to avoid confusion between primary and foreign keys in a customer stable customer. Heidi contains different values that is, different numbers are stored on heavy room, and there are no missing values. This compliance with the requirement for primary keys to contain unique values. Only in the sales table do we see under the Katamari column we have repeating on missing values. This is okey. For instance, it is normal to expect custom and on the to to have executive three purchases in our shop. Correct. Also, it is possible to have not ready said I was a kid. Ted pushes number nine the values on a dispute in the sales table who dimension in the customer's table as well. If we have registered information for customers with ideas 123 and four, we cannot have a value off 5 10 or already a right. This also means if you try to insert in the sales table a record with a customer, I d. That difference from the world's. We have already seen any customer stable. My SQL reason Herro on the record wouldn't be stored fantastic by the same talking. We can create the item, stable the field at him could appears in this table as a primary key on any sales as a foreign key. A logically to the previous example with customer Heidi Column from the customer's table item called contains a letter and a number forming a unique combination in items table while in sales, the values could repeat, as in the case with the I 10. Be, for instance, therefore with those f K to indicate an item code as a foreign key in the sales table on by drawing on the Haro, starting from the item Coldfield in this sales table point in the column with the same name in the items table. Great. Remember that primary keys were called unique identifiers of its evil. While foreign keys can be called, identify us to put their in five relationships between tables, not any tables themselves. Amazing so far. We walked it on imaginary database containing three tables. In the next lesson that follow will tell you more about relational skimmers. Thanks for watching 10. 9 The Concept of Relational Schemas: Okay, Great. Now we have a good idea of what did it obeys you look like. However, there is one missing beads. We have to create a connection between the sales customers on items table. Any companies table you created earlier? Why is a company stable so important to us? Well, I hope you can already see that accompany Heidi is a primary key in it. Spotting these must be a piece of cake for you already. On the other hand, you can really have two or more companies for the same name. But it's not impossible, especially India registered in the different states. Or if they operate in different industries. How about the headquarters for number field? Can you have to US numbers that are completely identical. New were you delightful number. There is just one phone that will ring somewhere in the US, right. Therefore, we can conclude that the values in this field have to the unique go to it. This also means that headquarters phone number is a primary key would not be possible. We just said that there is wound on only one primary key in its able year. The company Heidi column. He's a primary key. Don't worry. There is a name for this type of feud. On it is unique keys. They are used. Whenever you would like to specify that you don't want to see duplicates data in a given field exactly has, in our example, we headquarters for number. There is no substantial difference between unique keys on primary key, which is that they can contain non values. 1,000,000. If we don't know the headquarters for number off, complete de because still have this record in our table will be function in a spot off their relation. Altitude. These, however it's home. Give us information about the phone number off completely if instead we remove number two as complete. Heidi lived in the company, be on everyone's phone number. Eskimo would display an error message because company I D is a primary key, and we are obliged to provide data in every room off this column. In addition, it typical have 012 or more you, Nikki's. This means that depending on the requirements of databases it's ever made or may not contain one or more you Nicky columns. This is different, which respect to the characteristics of the primary key constraints. Remember, you can have only one primary key table for the more what a similar between these two types , of course trains is that what can be applied to multiple columns, not just a single column. In other words, the you Nicky constraints can be defined by a single field from the table it is applied to , or it could comprise a few off the columns in that civil boots scenarios are possible. Wonderful. This complete the creation of the entire sales database. Schema in our next lesson, will explore various relationship types. Tables can have see you there. 11. The concepts of Relational Schemas: all right. This is a lesson in which, who try to illustrate relationships between tables can be categorized will not explore all types of relationships in detail, Given that this topic is rather theoretical time consuming in its entirety. Instead, will started the main types of relationships you likely need your walk, please. Relationships tells you how much of the data from the foreign key field can be seen in a primary key column off the table. It's are treated to advise versa. What do we mean by that? As we already discussed the customer, Heidi Column is a primary key of the customer stable. This means that contains only unique values. 123 and four. The same information about customer I D coming found in the sales table as a foreign key, but he will likely have a lot more than four rows here. Hence the value from 1 to 4 come reputation many times because the same customer could execute, nor the one porches. This is an example of one too many type of relationship. One value from the customer I D Cologne on the customer's table can be found many times in a customer. Highly column in the sales table and the relational schema. This a shimba signing the correct symbol at the hand of the room. You should always read the symbol according to the direction of the relationship you are exploring, for instance, think of it this way. Single custom are could have made one parties, but she could have made more than one purchased you. Therefore, the second symbol, which is next to direct Tango, shows the minimum number off instances of the customers entity. The company associate it with the sales entity. When this symbol is a tiny line, it means one. The symbol look at the next direct ago indicates the maximum number of instances that can be officiated with sales entity. The angle like symbol stands for many. Let's check the relationship in your posted direction for a single party is registered in the sales table. A single customer can be indicated as the buyer, so the minimum number of customers you will find any customer stable for women. We have sort information. His name, email, a number of complaints and that corresponds to a single porches in the sales table is one At the same time. We just said that forgiving parties we cannot have Mother one buyer 1,000,000. That's the maximum number of instances from sales, as a shooter with customers is also one. Therefore, for every parties, we can obtain the name email, a number of complaints data for one customer. I'll represents this logic by joining in line for the minimum on the line for the maximal it to symbols in closer proximity to direct tangos formulation. She between the customers on a sell stables are calling to the specified direction. In our case, it is quite to say that the customers to sales relationship is one too many. While the sales to customers is many to one, the symbol show no solution. Ship limitations are called criminality constraints. There are other symbols that can be used to M or N for infinite associations or a circle for optional instances, which would have been the case if it wasn't necessary for registered person to have purchased a night. Um, there are some other types of relationships that well, I want to one many to many on others. This is information that we can share with you for general knowledge, as this is a specialized topic which is off interest, mainly toe advance users. To sum it up. Relational database skimmers do not just represent the concept. Database administrators most implements they depict. Our database is organized. They can be taught off as blueprints or plans for databases because they're usually prepared at the state of databases design. Drawing original schema isn't an easy job, but religion or schema will help you immensely. One right in your queries it needs are completed. Organization of the structure off the entire database will always be useful when retrieving information. Yeah, when it's theoretical section of the course on the next few lessons, you learned how to install and use my SQL workbench. I will be able to dive into the challenge in room off SQL queries. This will do for now. Thanks for watching. 12. Installing My SQL Work Bench Server: this lesson will be able to be technical a little bit complicated. Put very important with Stomach SQL or binge on my SQL server. Even it put in almost every lesson onto the end of the course. Let's look at my school website. Wtlv totes my SQL that come So let the Donald section and click on community as we like to show you how to download the file that is open, source and free. We'll go through the installation process for your machine opportunity Windows, please Remember, this video will be useful if you're installing the software on the machine running on Mark or s or Lennox, as what needs to be done is similar. So I will click on my SQL Oh, on Windows. You can either download my ask your components and sues separately or download my securing , stolen, then choose which to you like to install from day. The second option is much quicker and easier to implement. That's why I use a lets the installer scored on the way. You see two visions of the installer now the for discussing them. Please be attention to this important notes my SQL workbench. It's in stolen and also to a twos are constantly being updated to so different books on what's not. This is why, during solution process, we come in yet with the impression that the components were installing a different missions . This is not an issue. Usually the changes between different versions are marginal. That's why they will not affect execution off the task presented throughout this course. So please, if you are about to download inversion off my obscure that this order or new are than the one you see in this redo. Remember, he continues calls on apply or cord used lectures on exercises without experiencing issues . Not that we say that's less for consultation. Only two options provided the 1st 1 is suitable for those of you off Internet connection throughout the installation process, the 2nd 1 would be appropriate if you just don't know the far and then installed soft way when you're offline. Naturally, the 1st 1 is better so that the relevant download bottom the page offering you to log in or sign up for our cool website will open. This is not an obligatory requirement as you compress know things. Just that my download then in the window we pop up Select directory where you would like to install a file to be downloaded when you've done that, could conceive. We don't need any of the my SQL page anymore so you can open in stolen and wait for each to loot. This is the Mayas killing still ongoing due to proceed. He must steps the Oracle software, those germs. You can do this by taking the little books at the bottom of the window and then clicking on next. What comes up by a few different set up types? We need not to discuss all of them for purposes. Costume will allow us to specify the tools we need. So Lester, let's costume and click on next. OK, at this stage, he consulates the products to be installed in the resource sexual of this lesson. You can find a file explaining why we'll need my Escalera pinch on a my SQL sever. Fine. So first expand application section, then the My SQL Work Change sub section and then click on the name of the two on on the little Green arrow. So movie to the section of products off features to be installed. Good now, most hard a server expanded several section Dane, the My SQL server, subsection and finally choose one of its use visions. 64 bits or fails to beat, which is called exoduses because you're ends with a workbench feature, so I will select the 64 bit version greets we're done Selected features click on next to move onto the next teacher for the installation process. After your specified the products and features you need depending on your purchases, them instill. Um, I wanted to check it is prudent to meet certain requirements in that step. It could be provided a suggestion about how to satisfy these requirements. For instance, there's some I want you to download an additional piece off information. Alternatively, the status of a certain requirements will be marked as Manuel, this woman that you will have to find a way to solve the issue on the old. Please remember that you will really have a problem with the step if you stick to the set up type suggested in this video. Nevertheless, the issue that people might sometimes encounter arises when you have an older visual off Microsoft Visual C plus plus on your computer. Although this is a free, soft way it is not. Part of the solution sets up on innovation might not be the one you will need in order to proceed smoothly. Apple said that let's go back to the installation process Press as acute to install walk range, I'm a SQL server package. Once this is done, the status of both features will be marked as complete. This means we are ready to proceed. Let's click next again. You move to the stage of products configuration, the last step off the installation process. The status on the server should become ready to configure. This means you can move a step for the okey. Now you'll have to adjust the configurations of your server. We need not go into details keeping the default settings in the next two pages. Who let us install aggression that will suit the purpose of this course. So just click next two times. It is important information that, in the new provisions of the My SQL install, a section in the column on the left inside have been organized differently. A section named Authentication Method will appear naturally. The more recent at its options has been recommended, however, although it's ours, it's advantage. He's it will not be new dead for the scope of this course. Therefore, and this Redux who posted by choosing the legacy authentication mattered. Okey Now we suggested Let's in the second option and then clicking on next. Let's that is very crucial on its must not be overlooked. You have been asked to set up a root password, and you mustn't forget your password because it would be hard to change or recover its later otherwise router festered in many connection established between you as a user and my SQL server, you're about to restore. So I will set up and confirm a password I would forget was doing press next once more and keep all the citizens that just appeared as they are, especially do most regarding its start off my SQL sever at System startup. Unless you leave this option sick, you must set hub to several Monroe early when you use sq, and that's not a straightforward operation. Okay, press. Next again, you will be offered to install a plug in that contains improvements on previous occasions off workbench boats. It'll no be really want for our costs, so please skip the step by clicking next once more. I'm finally as acute with consider, installation is complete and click on finish. You won't need to go through The Wizard appears so click on next the lunatic on started. I ask you a workbench after start up and press finish. Now what bench will elude? Are you set up the connection between walked range on the my SQL Server? We learn how to do that in our next lesson. Thanks for watching. 13. LINK GUI WITH MY SOL SERVER: All right. Great. My SQL workbench is the official graphical user interface to or go to from our rescue. Oh, determines its ability to do that. Was shows communication with the my SQL sever Fantastic. First, to assess workbench, we must settle the connection between agree on the my SQL server. Otherwise, the two winnable end on you cannot execute any koot connection has been created for you. Click on the rectangle you see then this static password designated during the installation process press OK on you go. Now you can using my SQL or change That's not content with interphase. Beautiful course of our next lesson. Before doing that, I like to tell you that you can load more than one connection at the time. Your gooey all collections will be on the difference Tabs next to this little house in the upper part of the screen she do press the house icon logically you will ability jump to the men top show you my SQL walked pinch You can see just one. Okay, If you want to go back to the connection already alluded most click on his name and to close it Just press the little close sign for years. You see, I just close the collection are went back to the main top. Nice if you need to set off the connection in most press political sign on the right side of the My SQL collection Labor and the winner the props up, you'll see several empty feuds. The 1st 1 is compulsory, as the test inserted will be the name of the collection you are about to establish. Then you can click on the text connection to see if after type in the password is signed in advance. Once the connection is currently set, hope another Tsonga will appear in the home screen and you'll be able to assess Walk bench from there in this course you want after set up a connection on your own as you need the default connection that's already been set up. Greats stay tuned for the next video. We'll study the main characteristics off the my SQL workbench interface. See you there 14. Familiarise Yourself with MYSQL Interface: Okay. As you can see here, the central parts of your screen is where you can create queries or my SQL objects. In simple terms, it's a bit area where you will be typing cooled, for example. I can type a line. Of course, that's what select or records of its able contained in it. It Abi's okay, Please don't pay too much attention to the court to use in this video. For instance, employees in the movie disobeys you walk on the town in the cause. We'll show you how to download and use it in detail. For the moment, however, concentrates on interface off my SQL workbench, you can see a small set of icons executing various functions By clicking on the First World , you can get a window from which you can select on hope on an existing Ask your script. The second icon allows. It is safety scripts on your computer and so on. The functionality off most of the remaining icons in this state will be explored literally . Course on important cycle to remember is the one depicting enlightening by president can execute around the cord we've routing. Lestrade is excellent in New Block, appeared in the middle of the screen. It is called the result Greed, although more off the New year people referring it to its content as a result set. Obviously, you can see the data obtained after running the cold everything. It is accurate to say that in the middle part of the screen you can see the results obtained after indicating a query finally to close the results. It in most press the cross sign on its app indicated Down here, all right at the bottom of the screen. See the ultimate section. It's keeps track of four successful or unsuccessful executed operations in my rescue. In the compensation, for instance, we obtain an output last time, didn't we? That's why we see a little green circle with a tick mark way on the number and time off the oppression. Is it you did if you consider its extraction undertaking a message for Hope Bench. We got in this operation on December to December to reply to your query within our sports. Lovely under Let's part of the screen is a Navigator section. It is really want for advanced analysis on for more adverse database maintenance stations. This exceptional came about in our cause is the skimmer section. It represents all disabilities, their tables and other related sq twos and features. The operates part of the screen contains 32 squares that will allow you to hide or shooting Arigato, the ultimate section or the SQL Additions section, where we can find more advanced features if necessary. You can use these buttons to adjust the program interface according to your preferences. Greats Finally, Benita sexual with connection tops, you can see a few small icons. They allow us to have videos, types or fouls on objects on little because he will often need defers to when you over Over and icon walked Pinch displays an explanation of what it does as a say easier. It would create a new rescue built up for the kitchen queries. So let's president icon, you can see in new SQL Top opened. This is the police to start a new rescue. Oh, script from scratch. Another. Your brother went up open unity single click over the tabs name to jump from one as well, a script to the other. Easy right, mystical. I context it to your window that allows you to select and then hopefully existing SQL Scripts Tropicals. You will often need to use this icon whenever we ask you to load this setting SQL script. It was Click on that. I could go to the directory where you've stored the respective SQL script, Select eight and then press the open button. Amazing. This was an introduction to the main characteristics off my SQL walk Bench. Please play around with the interviewees. I hope you enjoy watching this. Listen. So you in the next one. Thanks for watching. 15. Coding Tips and Best Practice: in the next two lessons will pay attention to code in Star. There are many ways you can writer court, but they're only a few considered professional. Comply with Golden Star ruse Escutia. When advising programming not only in ehskyoo, put in any language you'll never walk on your own. You would always walk in it seem, even if you're a freelancer working online, there will always be someone or needy or court at some points. Then you find out there is one Lucien that will not be giving you a break. The notion off clean Code three code is accorded is focused on understandable, which means it must be readable. Logical on changeable. Remember good good, Islam warned. Computers understand it is the one human can understand. Often broken can be created in many ways. On court, in general can be organized in several ways. Good practice implies you choose the vision that would be easiest to read and understand. I will be the one that there's no hinder your colleagues from updating its When there's a street, you will likely walk on top of your code. So it is best if you don't lose time figuring out I didn t need complex lines off court. That is why the assumption is that at your walk, please, you will always type called clearly are simple us possible. Perfectly organized, maintaining a steady, logical flu. All right, good. Now let's focus on another aspect off Gordon and good style. When our sunny names two variables or school objects always choose shorter, meaningful names conveyance specific information by many for women names that are pronounceable where one what the concept has been picked. For instance, this is the reason which use purchased number, not customer porches. Unique number in the sales table. On that account, you need not to be in a rush to choose names, since the most reflects as much of the objects functionality as possible. The names, of course, the two to more than 80 tens of your code. So it much as which ones you work with is often this called whether capital or small little must be used when coding Detroit is it depends on your style or the style of the company are walking. For most often professionals, we capitalize the SQL keywords. I will write object names in a different way when it comprises more than one would, such as porches. Number was I usually either separated by owner, score or attached to each order. On each word starts with a capital letter. Both approaches are encountered in professional coding. Wanting it sure do. It can't leave a blank space between words, my sq. Oh, if you're on a roll message, if you try to do that all right, the third phase will focus on in this lesson is the readability of your coat on one hand disregards the resumes all on vertical organization off court on the other piccolo with which words are displayed. Technically, my SQL query can be written on a single line. However many queries got too long. I will be difficult to read if we do that. What needs to be done in such cases is organizing court, not just horizontally but also vertically dependent on the Aridi in iniquity. On on the way, we want them to read what's coming. Greeting in different colors ehskyoo keywords. I written in blue opportunites names in black numbers in orange and so on. Good as we move on any cause, you would develop an eye for impeccable organization off the court you right. For now, remember, there are three main ways to maintain your Coldwell number one professional years at OCS off the way that we organizes cold on Carlos. Different words consistently Any more dynamic coding environment Time will be a fractal. A unification of coding style will be a top priority. It is impossible to have 50 programmers in your company or writing in the same style. It is unprofessional to medical greeting in the same language, but in a different star. So when completed piece of walking cold go through checkoff on your boss will have a pile off court all retained in the same style. Number two use the really violent on biological to provided in workbench Leslie to brush beautifies your court the shot key combination to apply dysfunction to the query where your cursor is looking at it is control and be you See also, if you would prefer to clean your court differently, you should intervene manually and adjust it as you like. Great In the next lesson will implement These are some other techniques in a exercise. See you there 16. Coding Tips and Best Practice 2: Okay, Great year. We will apply what we've learned in the previous lesson in an exercise in this simple line who create a test table with two columns, numbers and words we've written. He called on one line with small letters. Notice are my ehskyoo change Nicola off keywords Data types on numbers now will not be dealing with on hard rock Soft Way as we are focusing on workbench after having pleased because in somewhere in this query, you compress it to brush Icon to be for Mart script to do the same oppression faster, remember that control and be short court keywords in capital Letters on the data for each column of the table start on a new line. Greats imagine you every for that preference regarding the results organization of this court on the alignment off all data types he hasn't. It's important from the keyboard he can set end on March are to begin on the same result off point. According to some users, this streak for the improves code's readability the technical term is called indentation. We see the column names and the data types when dented to the rights. Amazing. Another aspect of maintaining good coding Star is using comments this alliance off text that workbench will not roll as a coot to convey a message to some more, reads our court. Technically, in my sq Oh, you can start a comment by typing before slash on the stock and closing a truce. Typically with a star on afford slush. This approach is used mainly for large comments. For one line comments, you can use either two dashes or one or more harsh science Majeski remarks or comments in Greek. Fantastic. Finally, I would like to elaborate on the lightning symbol, which helps you as acutely cooled. Let's start on the fact that you're Cody separated into blocks as not by semicolon separate . Oh, okay. So if you place your cursor on one block and then press the lighting icon or the control shit answer combination, Ehskyoo were on the selected on all remaining queries. That's why, if I click for money is acute, be called this way out. See the output from the select statements. I would drop detestable if you like to run. Just the statement on that because all you most pressed the adjustment icon were enlightening. Picasso depicted the corresponding people combination is controlled on Enter. I can promise you will not stop using this keyboard combination on to the very end of the course as it allows you to see the Outsports office setting query quickly without having to run the entire as killer could. Well, I hope you like this. Less is they were aimed at making you away off in ocean, off plan coding and coding in good style. We consider this to be essential for good Professional. Cody, enough about this theory. I'm busy X in our next lesson will show you how to load the employees disobeys, which is rather big. I will set goals for the most of the remaining lessons in the sq A part of this course will start are determined appellation lesson right after that. Thanks for watching. 17. Loading the Employees Database: So congratulations. You have completed a large part of the course. You learn about relational databases ehskyoo, teary, my SQL, creating databases on tables, data types, constraints on best coding practices. In addition, he walked on this mortal today's cold sales. This exercise showed you how relational databases walk in practice. It's time to get to the next level. We'll start manipulating data in my s cure for this purpose. You be using a unity to state that can be found on get hope. It is frequently used by programmers about to step it up a notch and dig deeper into my sq . Oh, this means, Deter said, is the best possible choice for laying the foundations off walking with my sq I relation. Now it's a disease. We'll take the advantage off its complicity by solvent. Several challenging. That's this tax would be a wonderful exercise that will turn you into a proficient sq. Oh, user, Okey. So we have reorganized the date of the script in an executable far that you can download on roll. Please send the link available in the results section off this lesson, then there'll only database far named employees. Don't forget a location your story in 18. Once this process is ready, open. Discreet file From what bench? As you would open other files. All right, here's the script as his cool down he was sitting. But that will create your entire day. Today's from the first to the last piece. There is nothing else to see. Please run this far, or which for your long once the educational face is over. The French data, your skimmers section, you city employees. It obeys. Appear on your list. Wonderful. This would be Deter said you be using for many of the following lessons in this course. So let's get ready for coding. 18. Using SELECT FROM: congratulations. You went through the entire two critical part of the course. Are you practice with this Monitor these. You also spent significant time creating objects like database tables on creating sq Oh, constraints. It is time to study the data manipulation to in ehskyoo. We'll go through all states mens on the data manipulation language. I will apply them in practice. So what are you waiting for? Let's begin. This sentiment you'll be using almost all the time is select. It is one of the most important statements in my S key. Well, on ehskyoo selects, as its name suggests, allows you to extract a fraction of the entire deters It. It's allows you to retrieve data from tables. You can also hear people see distant mint is used to query data from the data bees, so let queries can be a short as forwards but in your feet in your 30 inch screen. If esque euro is the principal language you're using at work, there is no chance you spend a day without applying to select statement. The business elects determine syntax is the following to let column one column to at as many columns as you want from a specific table separate and then by a comma from table name on a semi column practically the from keyword is Monday tree. It tells ALS which table will be retrieving data from when extracting information said it goes with from You are always selecting something from this certain table or that sides off SQL objects Good. Now where we expand did several section off Louise Detailees. We can see there are six tables in these little these lovely. Then we can expand the employees table, for instance, on City Column section comprises six columns. Perfect. Assume you're interested in exploring the names of all employees. Are Can you do that with the select statements abiding by the above mentioned sent US select first name last name from employees. They should be the query that we return on output of the entire first name and last name columns from the employees table. Let's ready down on Rony to see if it works. Yes, represents as to school down in the multiple section, we see all the records retrieved. Great. So quick notes forced as mentioned, I will never forget to hard the statements. A brutal at the end of the query. From this point, I will not say every time that something I am doing Second, I might occur tonight. I've shot queries like the one we used the air in a single line. The SQL keywords on the light blue polo will help distinguish between the elements off the statements. As we said a couple of lessons ago, I'll be using the beautifully option off in what range. I think it's an excellent for much until, especially for educational purposes. So please, lunatic advantage off eat. It's got only organize. Decode better. Wonderful. There is a very important thing with most initially, so you're interested in information contained in employees table. There is a substitute for enlisting all the column names between select Andi from UNESCO. The star symbols technically defined as a wildcard character means all on everything. Therefore, Celeste are from employees should be taught off us. So let's all columns from the employees table. Let me beautify on their around. This query to verify the results is courage. Absolutely. We have the entire multi road deter from this table shown here. For each record, we can see UN employees number birthdates first or last name gender and it dates. He started with the company. Amazing. In this lesson, you saw how select walks on a few ways it can be applied. The next lesson will show you how the wear clothes can improve the results of your query. Thanks for watching. 19. using WHERE: you already know how to retrieve information from a SQL Oh table. You can use the Let me start symbol to obtain the data from the table off interest. Oh, you can use. Select with column names to designate which columns from the table you would like to see displayed. The new technique Colon in this lesson is add anywhere close to the select statement. This will allow us to set a condition upon which we will specify what's part of the data. We want to retreat from the disobeys. This is much easier than it sounds. Let's start by checking the relevant sent us anybody of the query after the select from structural avoided, seen almost hod the way keyboard and then specify a condition. This condition can be, for example, a vacation that certain information is available, a check or a mathematical expression. Any lesson to come. We will examine all possible types of conditions to be used after the wear clothes. Let's look at the employees table again. You have get about thousands of employees, right? As you me know that there are people in this huge least who are named Dennis track strategy records from the employees table type Select Star from employees were first name equals Dennis, Please note the following first. Don't forget so hard. Single or double courts to the string Dennis. Otherwise, your query won't walk second and ehskyoo Equality Sign isn't useful. Mathematical expressions only in this line of court, it means the selection of data will be based on the condition that the individual's name is Dennis. Let's run this query and see what happens. Great to honor and 32 rules were affected, which means we retrieved to around 32 instances of the employees table. These are people whose first name is Dennis Cool. What follows is a set of lessons that it consists of various starts off conditions. You can apply to the select statement in my SQL. Thanks for watching. 20. USING AND: in the previous Listen, we use equality. Sign on disclosed out functions. You should know that a technical term for the quality sign is equal operator in S. Cuba. There are many other licking keywords on symbols called operators they can use with the wear clothes. Examples include on and or in not in like not like between. On and exist not exist. It's no, it's not. No comparison operators are more. Some of them are straightforward, while orders not so much on the next lesson will concentrates on the most frequently encountered operators. Let's start with the end. This is on a brutal that allows you to logically combine to statesman's. In the Coalition code block, For instance, you may wish to retrieve data about all employees whose first name is Dennis on who Our meal. The latter would mean the agenda is in setting off such a condition stems from the fact that's not all. People will carry the name Dennis off the same gender, so we might want to obtain a list containing men who are called Dennis. Only the and operator allows us to narrow the output who electric struts from our data. Technically, this capital must be placed right after the first condition. First name. Because Dennis it's mostly succeeded by the second condition. We just talked about deal, so we must type and gender equals. M. Let's run the query. 1 40 Rules affected. Great. This means not all 232 people named Dennis women just 1 40 off them. Therefore, the outwards is a list off all the records off all men named Dennis Stereos as in the next lesson, who discovered out all a brutal functions. 21. USING OR: you should know. Perhaps you think and on all at the most straightforward operators to apply. This isn't exactly true. To be sure off what you're asking the computer to do, you should know our SQL treats these reserved words. Let's quickly revise some of the queries we run in the last few lessons. Well, we executed. Select with the condition that's an individual's forced name. Must be Dennis retrieved to honor 32 rules. Then we ran this inquiry at any conditions to wear clothes were looking for men only. We extracted 140 rules off data. To achieve these, we use the and operator. This means that binds as curio to meet put conditions listed in a wear clothes simultaneously. We selected the data, satisfying both condition. One on, too. There's the least obtained. True, The last query contains people will meet both criteria. Their first name is Dennis on the agenda is now. That's why only 1 40 rules numbers smaller than the initial so three to extracted. The difference is represented but the number of women 92 named Dennis Greeks. Now let's see our or walks, we can execute this inquiry genetic conditions in the were close to the first name Dennis or Frost named Elvis. Perhaps you think it is logical to obtain toe outputs one containing in fullest off individuals named Dennis on another one containing those named Elvis. However, that's not SQL Understand things. You will obtain a single output list off individuals whose name is either Dennis or Elvis. This list will contain a combination of records of the two outputs you're thinking off. Moreover, the first names will be scrambled. They are order differently following the employees number this list contains for 78 rows off information. Joe, Knock it off. All people named Dennis Blows obviously the to 4 to 6 people named Elvis to prove to you that this is the way Ehskyoo understands the Aunt Andi or operators. Let's run the Sim query using and instead of all notice are both conditions are set on the same column, first name with the kids query and walk. Bench returns nothin. Well, SQL was unable to find a person named Boots Dennis on Elvis at the same time. Therefore, we can conclude that we should either use and with a condition set on difference columns or which you use all of the conditions set on the same column. Wonderful. In this lesson. We started out, but we can expect when declaring conditions, which the all operator, I think right now we have an awesome temple in this section. Let's try to keep it up in our next lesson. Thanks for watching. 22. USING IN NOT IN: Okay, let's see another example. Imagine you wanted to extract data for people whose name is Ida. Coffee. Mark or Nathan, in other words, this time will try to retrieve, at least in which not two but three conditions are satisfied. We're looking for your first name. That's Ida coffee mark or needing. Considering what you've learned so far to solve this problem, you could write the typical select statement with a wear Clothes, which includes three conditions, are separated by the all operator. Let's check the outcome of this query. 691 rules fine at a second check because school over the multiple section to see that individuals with all three names appear in our list. We can see coffee mark on Nathan. Fantastic. Well, there's a quicker, a more professional way to approach detail retrieval when we must satisfy more than two conditions. To do this, you will need the in operator and where close will look like this. Where the first name in open princesses in lists, all given names off interest, cutie mark on Nathan close princesses speaking more technically, the in or brittle allows sq oh to return the names retain in parentis is it exists in our table. Let's verify the query walks properly. Absolutely again, 690 more rules were retrieved. Perfect. There is another important reason why someone would prefer the second over. The first cold structure. Look at the amount of time and secure needs to fetch the desired output. The instructor was quicker than all structure, and please don't think this 10th of a second difference is negligible. When a plight a larger data sets, it will grow. Minimizing. It is a primary tax for SQL professionals off our role. When many conditions are to be applied in equerry, the interpreter is preferred. Great. That said, it is straight forward to imagine our notes in operator can be used. In this case, every name over than catty mark on Leighton will be displayed. Let's verify. Yeah, it's true. The ultimate doesn't contain any off these trainings. Because our database contains an extensive list of records. It is normal to expect to see here only the limited by default output off 1000 rules anyway , as its core down. You won't find any of the three names, which proves we've done our job correctly. Amazing. That's it for the moment. So you in the next lesson 23. USING LIKE NOT LIKE: Sometimes you will need to look for a specific pattern in a column. What do I mean by that? Imagine you would like to extract a list off all people whose first name begins with an a R . Such names include Mark Martine Migrates and so on to obtain the desired output after the were close, you must, as usual, indicates the feud off interest. They used the like operator. It is succeeded by parents, and since within which the pattern must be indicated, that's why they implemented techniques is called a pattern marching. Here comes the interesting part. You must use the percentage sign to indicate that pattern. The following examples would best explain this since us so let's start if you set for more percentage, sign or within courts, because we will be looking for string values you'll obtain in least off all name starting with Mark. Is this true? Yes, Captain Mark Margaret. A soul Good. If we remove the letter M on such for you, part time indicated by the letters a R on the percentage sign will retrieve a list of names starting with K are great. Therefore, the percentage sign can be seen as a substitute for your sequence off Carter's. It represents zero or more letters that exceed dues indicated before eat Awesome. What will happen if we move the percentage sign before ER, in that case will get all the names ending with a Are can we use to percentage signs around the designated letters? Yes, and in this case, A and R will be found next to each other somewhere in individuals Must name wonderful. Okay, what's next? There is another symbol you can use for pattern matching on the school, while the percentage sign is necessary for much in its decoys. Off Carter's underscore helps you might. A single character, if you would like to fetch only means starting with Mark on reaching with four letters, you typed Maher on the score within quotes on appearances. So sitting like operator, let's check the outcome of this query excellence. Only four symbol names appeared Mark, Mark, Mark and so on. Finally, besides, like one can use, not like the exact opposite oppression of the carry out. As a result, when you used a sin query with a condition that the first name be not like percentage sign , Maher presented Sign I guarantee you won't see on individual was given them contains the sequence. Ma. Also to conclude this lesson, Lemon mention that's unlike other languages. My SQL is case insensitive. This means that when you search for the sequester mark, you can use any combination off small on capital letters to obtain the same output stitching for the next lesson. It would be brief but nevertheless will enrich your vocabulary. Thanks for watching. 24. Using Wild Characters: This is a brief listen about the percentage on underscore Symbols will also get acquainted with the star sign. What is common among the three is that they are all called wild characters. When I first heard this tale, I did not understand what in in came from. It took me a while to grasp what a fortune ality off these three symbols had in common. It goes down to the fact that you would need a wild card character whenever you wish to put anything on its place, something like taking a wild guess about the detail or the symbols that will appear in the output. After we execute the query, we got any like corporate, so we just saw The percentage sign is more frequently used than the other school sign. The book contained the functionality off a wildcard character. If you don't know the sequence off cartas to use after the mile string off letters, the percentage sign will be used as a substitute. If you are under a single little succeeding Maher, he on the school who provide the potential outcome wildly. While the percentage sign on the on the school I used for string values the status symbol and comm passes a broader meaning. As we discussed earlier in this section, it would deliver a list off all columns in a table. In some of the next lessons, you see that it can be used to count all rose off the table. That's why the star single is called a White Carter to great. This is good to know right combined these wildcard characters within new material from the previous Listen on, try the exercises following this lesson. I'm sure you do. Well, thanks for watching. 25. USING BETWEEN AND: in between. Operator helps also designates the interval to which given value belongs. That's why it is always used in combination with the aunt or brittle. If you want to obtain a list of the people who were hired between the first of January 1990 on the first of January 2000 years, the same select statement structure and indicate these higher dates in the wear clothes using the following sentence where I added between 1991 1 on 2000 or 101 Pay attention. Both values off this interval will be included in the retrieved list. Off records. Let's run the query Great. As we score down, we see nobody who is hired before the first of January 1990 after first of January 2000. It shouldn't be a surprise that not between and referred to on in Savar, composed of two parts on interval below, the first value indicated on his second interval above the second value as a result for which the IOD it is not between the first of January 1990 on the first of January 2000 should be read as a query in which the dates is before the first of January 1990 or after the first of January 2000 on loudly between and structure here. The two values are not included in the intervals. So when you use not between, your list won't contain the first of January 1990 on the first of January 2000. To prove these asses go down, look for higher dates between the designated values. You won't find him as a final note. Remember that between is not used only for date values, it could also be applied to strings on numbers. You see examples off these exercises within for you because resource section Thanks for watching. 26. using is not null is null: Okay, Next on our agenda is the It's not no operator, as its name suggests it to be used to extract values. That a lot. No, this syntax is intuitive. Select column names from a table where it's set in column is not know applied to the employees. That's a table. This opera. It'll could be used to retrieve the records off all employees whose first names are not know this means where the first name is not know s care its own, at least in which devalues off the first name Colon on not missing great at four sites. If you look at the employees number, the names of all employees already start inevitabilities. Is this possible? Not a single first name missing. Well, let's check the outsports off the Sim query. Using your preacher with opposites functionality is no if you deliver results, it containing the records off all employees was forced lame where not provided so select in all columns from employees where the first claim is no leads us to an empty output on the interpretation of the results. Off put queries will run is that digital bays is brilliantly organized and there are no employees for which the first name is missing. Fantastic. Stay tuned. For the next lesson, we'll discover how Andy the comparison operators can be. See you there. 27. Using Other Comparison Operators: Greeks. So far, you've seen a few comparison operators like between on and like not like is not long is no in this lesson who focus on the way we work with other comparison operators, the ones derived from mathematics. Andi Sq A. Will be able to use the same science as the one we usually use for designating. That's a value is equal to Greater Dan, with Madonna equal to less done a less than or equal to another value. One particularly is that not equal. Also called the Different from Sign is not identical To use eight, you must either a touch the symbol for less. Dan and Greta done next to each other, obtain angle brackets or precede the quality sign with an exclamation mark. In SQL terms, boat operators are called not equality operators. Let's see how the comparison operators can be applied. In my skill, you have a secretary type of query. You see well, we implements the condition and to extract individuals whose first name is, Mark said More technically, whose first name equals the string mark. We obtained a list of records which everybody called Mark. If we would like to do the opposite retrieve announce sport with names different than Mark . We should substitute the quality sign with angle brackets. As you can see known off the names in the list as Mark the same outcome compute seen by using the exclamation mark on the equality sign to indicate any quality. Okay, yes, right. We got the same result when using the exclamation mark and equality sign. Perfect. Okay, let's leave people's name aside will allow for the next comparison operators consider the date employees started working with a company. So post your boss told you to provide a list of employees hired after the first of January 2000. Therefore, after they were close after you select query, we should specify you are interested in the higher dates after 2001 1 after camp indicated namely, but he greater than sign to see if the code is correct as a kitty query. Fantastic new record as a higher deeds. Elliott Dunne The first of January 2000 please note is the foot on January 2000 was executed from the retrieve data. If you like to include it, it was used the greater Dan or equal to sign right? Absolutely That's why instead of 12 you obtain 13 records in your outputs on when you check the results. You see employee number 108 to a one was fired on first of January 2000. Precisely awesome at this teach explaining, the less done on the less than or equal to comparison operators can't be easier to avoid obtaining a large amount of data. Let's check if anybody was hired before the first of February 1985. Yes, this sets is big on dates back to the eighties off the previous century. Wow, so we must type way high of it is less than these day to obtain it. 10. Rule output. Cool. Don't forget it dates First off. February 1985 Was executed any previous results to include it. It's only comparison a preto to less than or equal to. When you Jenny compares your pretzel and executives query, you will achieve 25 rules. This means you have executed 15 more rules representing people who started working in this company exactly on the first of February 1985. Great, this will do for now. Thanks for watching 28. USING SELECT DISTINCT: all right. Excellent. So far in this section, you watch a lot of lessons on did many exercises using the select statement. Therefore, you are well acquainted with its functionality. You country rules from a designated column giving some criteria right? I would see if there are duplicates values contained in this field. Well, this next statement will extract them all. Look at this example will obtain the information. We have an agenda column and the employees table through this query. Select gender from employees. The list is long. That's why we obtain the limit of 1000 rows. For the more the outlook is clear. You have many men and women on the list. OK greets our community, released that contains a genders but contains no duplicates. Values in such a situation is determined to use is not simply select, it is select distinct ends. The syntax to abide by is select distant columns from table name. Select Dixon's agenda from employees will show all distance value in war room. Therefore, with on F as genders, we will obtain one rule for em on 14 f two rules in Toto, right? Isn't that intuitive? In the first case, you are asking the several to select all data values from the gender column on agendas were shown in different rules. In the second case, he specified, You want to select our deck stains, different data values from this feud. That's why zeros appeared. Any outsports fantastic. Keep it up in our next lesson where we'll see a particularly useful application off the Dixons key would see you there. 29. Getting to know Aggregate Functions: Okay, Very good. We are making excellent progress here in this lesson. Will cover advocates functions. Angry functions are amazing. They are applied on multiple rules of a single column of the table on return on outsports off a single value. This is just a definition. Don't be scared, please. Things will become much clearer once we start walking with five other great functions. In Ehskyoo, the most notable is count. It comes to the number of non no records in a feud. Some intuitively storms or the nominal values in a column while mean landmarks returned the minimum and the maximum value from the entire list finally average abbreviated. A VG calculates the average off all. No. No values belong into its second column off the table. Little because who provide examples with all these functions. For now, please concentrates uncounted. It is frequently used in combination with the reserved world. Distinct. Let's see how Count walks the syntax to follow ease select count Open Prior instances. Designated column was number of rules you would like to obtain. Close parentis is from table name notes the parents as this after account most that writes after the key would not after a white space. Otherwise, the query once walk. The logic behind the speculator Pretty of syntax stands from clean coding rules when the contents in princesses is tightly related to the Foshan, designated by the preceding reserved would no whitespace must be used. Okay, let's see a few quick examples. Azumi Most answer the following question. How many employees already start in our database? We know employee number is a primary key on the employee table right there. Four. If you count the number of records in this column, we should obtain the correct answer to a question type Select count open for instances which are living distance between them and account. Then proceed with em on the school number. No sprinters is on. Finally, specify, you wish to retrieve this information from the employees table as acute query to see we have deter about wow, 300,000 and 24 people. This proof counts delivered the total number of values in the specify feud employee number . Let me refresh the lesson about it's not a brutal Do you remember when we try to extract data from employees table having a no name you obtained on MT. Output right, they were rightfully concluded. There is no missing data in the first name column throughout the whole table. Now we can use account function to prove the same thing in another week by substituting the column name within these poor entrances with first name, we are supposed to obtain the number of values contained, enforced name field as acute on we have 300,000 and 24 again, The number of names and the number of employees recorded in our database is much great. This is our count walks on. It should be more intuitive to see out this things can be used. It is supposed to deliver in above different names found throughout the day. It's a table. So if we asked ourselves how many different names can be found in the employees table count , Distinct should help us provide decorates AZA. Please be careful who you think that's to this distance. Keyword is not police. After select this time, it's must be retained. Within the parentis is the 40 column names, so the line of court becomes select Count. Open, for instance, is the extends first name. Close parentheses from employees. Let's run this query to see what we will obtain. 1200 to 5 is significantly smaller number, right? The interpretation is the people in our databases share 1000 tourists and five deck stains names. Excellent. This was a precise answer to our second question. Wasn't it? As a conclusion to this lesson piece, remember, unimportant future off aggregate functions? They ignore no values unless told not to. This means if there were any no values in the employees number or the forced me in columns , counts would not have counted them. I will not have hunted them to the hotel. I hope you enjoy watching this lesson. However, stay tuned, as I'm sure the next one touches on an even more interesting topic. See you there. 30. USING ORDER BY: Let's think about how you can refine your output. See what happens if you achieve overcoats from employees stable. You get at least automatic. A Libyan or that based on employee numbers as in your balls, has just asks you to other the people by first name instead off employee number. So how can you do that? You can use the other by close. I didn't other by first name at the end of this query provided desired outcome less as acute it to see what's going to happen. Excellent. The entire least was reorganized in alphabetical order. According to the field containing employee names. One off to specified reserved words can be attached at the end of the other. By close. The 1st 1 is a SC aggravated for ascending, requiring output to be sorted by the values in a designated field in the ascending order. U disk you What is not attached to the statements at the end? SQL implicitly understand you want things or that precisely in ascending order anyway. Therefore, if we had A S C at the end of its on, the query will obtain the same output. Correct? Yes, absolutely. As you can see the employees from the list are ordered by their first name in ascending order. The alternative is to use the sea, which is operated for descendant. Hence, if you let your results plotted in reverse order. DSC is a key word to add at the end of the order by close. Let's check if what I'm saying is true. We see also hope that we have names starting only with ze Great. Now let's demonstrates. Gotta buy does not work for columns containing string values. Only this close can handle numbers as well. Let's sort the list my employee number in the Senate. Other. Our query will look this way. So I left everything from employees over by employee number descending as acute. Andi Perfect. Here we start from a larger employee number that decreases as we scroll down. Fantastic. Let's see another interesting feature off this close you can over the results by more than one field, for instance, we can other employees by forced on last name to make the comparison less return. The query where we started out sports by first name in ascending order boot. Now look how the other off last names we can sort employees sharing the same force name according to their last name, To do this most simply type comma. Last name. Here we designated second column off interest. It's a cute to see if this code would affect our outsports any desired way. Also, indeed. Now all people withstand force Name are ordered by their sunlen. Wonderful. Okay, Thank you for watching. 31. USING GROUP BY: this lesson is important. It'll I'll show you could seem intuitive at the beginning, but I can guarantee understanding it is the most when writing more advanced SQL queries must train This SQL feature will make your life easier. Without it, you can handle up in deep trouble, so please be careful they're walking in ehskyoo results can be grouped according to a specific field off fuels. Technically, this is done by using the group buy clothes, which must be placed immediately after the way conditions, if any, and just before the other by close, we talked about in our previous listen. Remember, Group I is one of the most powerful I useful tools in ehskyoo. The sentence to comply with these. The same old select column names for me giving table where some condition or conditions have been satisfied goodbye column name or Cullinan's and then finish with other by in the same or different column names. So, for the moment, let's remember the group are close, is located just above the other by close. Good. Let's see an example first. What would happen if we select the first name column from the employees table? I'm sure you can tell. We'll obtain on on So tell list off the names registered and Italy's Check. What's going to happen when we group the Outsports by first name. The results will be shorter as only distant values will be selected. Its value within the first name field appears in the table more than words by using grew by it to be split only in a single rule. Okay, yes, absolutely perfect. Practically speaking, this is the same us. If we use the world known Select Dick stint structure. The only difference is this time. His selected rules were so said in a different way. In addition, Group I was slower than select extent. Therefore, why would we need this group by states remains at all the and size because knowing how to use it grew by state. Elaine's, whenever you have adequate functions, is essential in most cases where you need an adequate function, you must had a good buy clothes in your query. Two years What I mean as you immunity. At least composed of two feuds, the force must contain extent, first name of the employees and second, the number of times this name is encountered in identities. Looking for a single total value. Most drinking bill straightaway. If we types, let's count Force name from employees who get the total value off records in this table. Then, if we had a group buying force, name was splits. The result. Return from select statements into groups. Yes, correct. That's true in this column with See the number of times each name is encountered. But we don't see the names these values refer to write years. Every love Tom professionals strictly are there to always include the field you have grouped. To result by any select statement, Let's do that. I would inside the first name just before the count's parts returned to obtain a clay outsports with a straightforward interpretation. Um, our can be seen 220 times. I'm at 2016 times and so on. Amazing. This rule is crucial because in what bench, as you just saw, the query would run properly if you don't include the group by field and select statements . But this would not be valid in some other databases. They it would be impossible to execute the query if written without the group are column and select statements, so please stick to this simple rule. It's also improves organization on readability off your outsports. Make this a habit from now on, and you will avoid 15 messy situation when coding As a conclusion. I would like to remind you that on other by block can be used right after it grew by block . Imagine you wanted to solve the entire outcome by the force turning in descending order. The coterie added his brother by first name D E S C. Let's run this query to see if we get a satisfactory output. We obtained the desired result. The last piece of information was an important addition to the contents of this lesson. Not our blocks off court. A mandatory, but it must get used to the other, in which you status blocks and a query Remember the following logical flu. Let's something from a certain table where certain conditions are met would be result by column and possibly other. Them in a certain direction, greets That's it for the moment. Thanks for watching 32. USING ALIASES: while the right column delivers information about a number of time. Each names appear in our records, so using the second column is called Counts First Name Rachael problems with a name off that's a con column forced. It is not a professional to leave the function. Keyword in the column names off the output. Second, it will be much better to see a name that explains the reasoning behind. Using this. Advocates function boots. Problems come solved using aliases on Alias or Alias Name is used to rename a selection from your query. In example, the first name is more selection on the counter number or the first name in the group is another selection by using the keyword, yes, you could rename a selection. Therefore, if you live, select four slim Counsel's name on that Hard A s Names count. When finally is a kitty cooled you city second Colombini in your results to be renamed as names count. Is this true? Let's check 100%. Also, what if someone does not have a clear about your analysis observed his house would now it can immediately understand The left hand column contains the name off. Some people while the right Sun column delivers information about a number of time. Each names appear in our records, so using aliases doesn't actually change your ultra. Put that this technique is constantly being applied inquiries as it clarifies the analysis on the taken. That's why this was an important lesson. See you in our next lesson. 33. USING HAVING: Okay, So having is it close? Frequently? Implement said it grew by because it refines the out sport from records that do not satisfy is set in condition, probably asking yourself, We already learned out a year's way. Why does have in close exist and to analyzing the correspondence in tax will help us explain the difference between the two key words. So let's begin here, having needs to be inserted between a group I on and other buy clothes. In addition, having is like way what applied to the group I look, the difference can be better understood through an example First. Also, locations on identical results could be obtained by implements in the same condition. Other with the way, or were they having close? For instance, should we select all employees I had after the first of January 2000? The retreat table will be the same. Whatever is in the way for Alvin, you see, Whoa. The mint extension between the two closes is that having can be applied for subsets off advocate groups while in the Web look, this is forbidden in similar worlds after having you can have a condition with an aggregate function while where cannot use aggregate functions within its condition. Let's look at the following example Azumi want to extract, at least with all force names that appear more than 20 times in the employee stable. If you tried to set this condition in the wear clothes walk bench warrant indicates there is a mistake in your code because this is the correct syntax, you'll be shown an error message. When you try to execute the query on it will be very eloquent. One invalid use of good fortune Janicki want to have in and had the line of court in the right place just after the group by statements Now around the query. It works. Shortly I retrieved 193 names that cannot be encountered more than 20 times in the employees table. Perfect. So when is the right time to use away on when she do you use? Having it is not so hard to decide on the problem. We just solved extract all the first names that appear modern Tory times in the employees table. You most four spots the freeze 200 times at least a counting the number of times something appears in the data table on counting numbers. It is acutely through the count's function, isn't it? Is count on aggregate function for years. It is the most used. Having not way it is as simple as that. The same logic was the applied. Any time on our good function is required for the solution of your task. Safer calls on the next subject, as in the next lesson, would have a lot more related features explain. Thanks for watching. 34. WHERE VS HAVING: it is important to decide without to use away or Arvin in set in situations. It's a topic deserving more rotation. That's why, in this lesson, go through an example. Here's a summary of wear and having where allows us to set conditions that refer to subsets off individual rose. These conditions are applied before reorganizing. The OUTSPORTS into groups knows the rules that satisfy the where conditions are chosen. The progress and data retrieval process can be grouped by the extent values recorded in a certain field or fields. It is not until this moment when the outsports can be for the improved off future, with the condition specified in having close. Finally, you call Saudi records off the final list through the other by close to reinforce your understanding of the data retrieval process. Let's see an example containing both away and having condition. Yes, it's asked its product list off all names that I encountered less than 200 times. Let it It's unfair to people, I add, after the first of January 1999 only. Okay, let's create a query step by step. Apparently the most selective force names on a number of times a first name is encountered remain in the second selection as names count. That's okay. Second thing to do is designates. The table will retrieve data from employees. Fantastic. Here comes the interesting beats. Should we use only way only having or both keywords? Why sets in our conditions? Well, there took on the shows to satisfy to solve our problem. One is that the names must be encountered less than 20 times 20 times immediately means we must use counts, which will count the number of times it's certain. First name appears in the employees. Get a table count is a lot of good function on. As we said in the previous listen, it's most go. It's having cool Deana condition to satisfy his general. All the roads instructed must be off. People were hired after the first of January 1999. This condition refers to all individual rose in the employees table. No specific Arabic function must be applied. Therefore, this condition was go with the way close between the way and they have in blocks. We shouldn't forget to insert the group by segment. We must group by first name, not by other feud. Since our task requires close toe aggregate outsports by the number of times it's set in first name is encountered good, actually less over the Outsports by first name in ascending order. All right, great. So just look like a more daunting query, doesn't it? Nyra's off court more elements to consider what creating it. SQL starts being more fun when you agree. Let's press control on answer to execute the operation eight. Or and it is his rules where they cute it also it query, walked and solved our problem. We have, at least with the number off all distance names off people hired after the first of January 1999. Fantastic. Let's keep up the peace for next lesson. Thanks for watching. 35. USING LIMIT: welcome to the last lesson of this part. Let's select all rules from the salary stable. Once we do that, we'll obtain, at least with 1000 rolls. Because, as we said, this is the default maximum off rules to be displayed in the results that in workbench this default limit value can be lifted. Yes, how we do that. Expand the Eddie Top from the main menu off the ends office. Select Preferences left outside of the window that appears you have the SQL Hesitation option in the SQL Editor section. Click on its Then you can unlock the limited or books or just the limit Rules counts as you like. Leslie the limits completely by unlocking the books, press OK and then return the same query. Wow, it's cure took more time to deliver the results for them or we obtained now at 6 to 7 times on three on city records. Not a lot less than a 1,000,000. So many. Do you always need a list So long? Absolutely not. Here's an example of how to avoid scene so many records in your output. Imagine a Post told you, Please show me the employee numbers of the 10 I spit employees in entirety. Today's forced the stand You're looking for top four months, as shown by setting statistic immediately. Think off sorting out sports in descending Other. Such is the case here with other all individuals in the least according to their salary. For instance, to this shot query, we could add the freeze order by salary descending on you obtain, at least with the highest paid individuals on top. Amazing, however, this list was huge, too. I just in the limits count from the professes. We do off the Aidid's top affect any query you run from your SQL 82 often in my professed set in a limit to the outsports off a specific query in this way at a limit statement at the end, just before the semi condom, it is always succeeded by an integer value indicating how many records must be displayed in our results. You're both axed about detained. I have speed employees, right? Therefore limits 10 would do the trick. Correct? Correct. This was much quicker query on its Assad. Our problem straight to the point. Also, please note if you order the statement by another column. The results at least will be different. Let's substitutes this Alaric alone would complain. Number here is a cute. So what happened? The result in 10 records were started by the employee number, and they are not the highest paid individuals. Immediate of these, we can say we must be prudent with a column we are ordering our output by, especially when Hardin the Limits determines, at the end great the syntax to comply with its personally missed. It remains at the end of your query, no matter how long. Daiquiris, for instance, think off the example of one of our Elia lessons when we had to extract the number off distant names off people hired after the first of January 1999. If we most limits that outputs 200 rules, the most ad limits 100 at the end on the result of a satisfactory wonderful. Finally, there's it's all the way to set limits. Just expand this Little East support the SQL query Alito and select a limit counts according to your preference, or like the force to options. This was sets no default limits or limit belonging to a specific query. Only it could be applied to the queries your own at this moment. Unless a limit close designating a different limits rule accounts has been used. So congratulations. This is where we hand the extensive section about the select statement. It is one of the most important toes you must use when walking in Ehskyoo. We hope you enjoyed watching this lessons. See you in in this part of the course series. Thanks for watching. 36. APPLYING COUNT: welcome to another part off this Siri's. It's time to take a step forward. In the next lessons you'll be dealing with aggregate functions. What a day We already introduced them in a select section. Their name derives from the fact that they got a detail for many rules of the table. Then I'll get it into a single value as we explain. Hey, Leah. This means the information contained in multiple rules will be imports off. This functions on a single value that provide is the respective output. There are other integrates functions besides count. Those that I use most often are so main Max on average, aggravated a V G. This is because of the ability to it's only single value from an entire column on that would to refer Algot function. It's ask summarizing functions. Why do dysfunctions exist? Their response to the information requirements off accompanies different organizational levels. A business top management executives are to become interested in summarize figures on really in detail data In so situations. Algot functions are the perfect solution. Let's focus on salary table instead of chicken D d. D. L statement. We can use select extract, a brief general outsports that will show holes that in this table we have information about employee numbers. Salaries on starting on, ending dates off employments. Great. We can use accounts function to counting above salaries, contracts in these detective piece. Remember that the name off a column salary it acts as an argument off the count's function by running the query will take approximately 967,000 records. Okay, remember that counts is applicable to boot in America on non America data. If your boss would like to know how many employees start dates are any databases you can use? Count from dates on, Obtain the results diary. Why do we have the same number? Because the lens off both columns is the same, and we don't have missing data either off them great. Finally, we talked about count instinct. Remember this additional officers to find a number of times unique values I encountered in a giving column. Therefore, counting stains from dates will deliver the outside to the question you're both act contracts have been signed on 6392 different dates. Now is the right time to introduce another interesting feature on the Count's function, Elia. We mentioned that adequate functions typically ignore null values through house field to which they are plight. Actually, this can happen only if you have indicated specific column name within the princess is the alternative option you can choose is type in the style. What cut character not proceeding. It's by the keyword dick, since this is a special feature off the count function that returns the number off all rows of the table, no values included. So by writing a specific column name or e star symbol within different sources, you will define whether no values are taken into account. My final note is that the parent senses in the argument must be attached to the name off the aggregate function. In other words, he shouldn't leave white space before opening The client is is if you do that, your query won't walk good in the next lesson, you learned how to use some. See you there 37. Applying Sum: So now that you know what halibut functions are about and how to use the first off them count, we are ready to introduce the next one the next song. All the Values in Salaries column To see how much money the firm spent on salaries, all we have to do is use some instead of count on Designate the Salary column as an argument. Let's run this query boom. Huge number. Don't spend so much time within the result. Trust me, it's larger. Down 61 billion. Wow. But a sick off this exercise. Let's replace the feud. Name indicated in princesses with star symbol Do you think, What bench will some all numbers from a salary stable? No, it would rather raise its syntax. Aargh! That's proof. The star symbol feature goes well with only the count function. It doesn't walk with other aggregate functions. Overall, remember, the counts can be applied to both in America and non American data differently. Some on the remaining Algot functions average mean landmarks, walks only wheat numeric data. You can't use them with known American data, and the next lesson we'll show you how to use mean and Max. Thanks for watching 38. MINI AND MAX: okay, So much. I mean, return the maximum and the minimum value off the column. If you're asking yourself weight, which is the highest salary we offer, you needed maximum value from the salary column. Wouldn't you listen? It's right after the Celeski. Would they must function can retrieve this value for you, then conclude by from salaries in a semi colon. Let's run the quarry to see the results. Well, the highest salary paid by the company is War Refugee. It's house on July and $20 they handle not bad on which is the lowest contract value any company hopefully off on internal or a young new comma to get the answer substitute. The max function would mean without doing anything with arrest. Off the court inquiry is acute that he it's hours on several $35. As you can see, the returns the lowest from the list off numeric values. Great. In our next lesson, you learn about average functions. Thanks for watching 39. Applying AVG: okay, Perfect. The next function who consider is average admitted as a the G extract the average value off all known all values in, if you'd not. Surprisingly, decrease structure is identical to the one employed for all of it functions up to this moment. Just remember to use the function. Name a VG. If you type the entire world average, for example, the query wants walk. All right, let's calculate the average enterprise of the contract in the employees that Abi's, which is the same house asking ourselves, which is the average under salary that the company's employees received. Okay, all we have to do is as acute Andi years, the number approximately City 3000. Several asked a $1 on 20 cents. This concludes the introduction of our great functions in SQL police. Don't forget that Margaret functions can be applied to any group off the values Woodenness setting column. This is another reason why they are frequently used together with a group. Buy clothes isn't were did not contain where is long enough to include a group I block. We intended to present their good functions without additional complexity. I promise you let Zion because you look at most sophisticated examples involving aggregate functions. By the way, it's a bit annoying to see four digits after the decimal point, isn't it? You probably expect to see all its representing the number off sense in the price rights. Never. You wish you could run this value somehow. Yes, you can. In the next lesson will show you how to do that. And as one of several of our pressures that will help you walk in, sq Oh, thanks for watching. 40. Rounding Numbers with Round: around is in America. Oh, my function you can use in my sq Oh, it is usually applied to the single values that are good functions return. Look at the average value we obtained any previous Listen, As you know, we don't want to show Della amounts and sense on that. You like to present a number as a whole integer. This would be an easy task for the round function. It's syntax is very simple. It's embraces its arguments, which is the number of the most around it round can be looking, said anywhere in the query, As long as in numeric value have been provided within the parents, asses in our Keys rounds should proceed the average function to get our wits eat arguments . Let's run the query to see what we get. Also the same number. Run it to the closest integer. 63,761 Fantastic. In addition, don't forget, you can have a second argument on around function. After average salary, you can put a comma on Hadi number, which will equal the number of digits you see after the decimal points of the value in your outputs. Yeah, given that The aggregate function gives us an average of a monetary value. It makes sense to around the result. Three value With the precision off sense, let's say it's three digits after decimal points will be a satisfactory results. This court complies with the center that's required. Let's as acute seats and see if we obtain what we want. 100%. 63,000 Several asks to $1.20. Excellent. That is it for now. Thanks for watching. 41. What are JOINS: at the beginning of the course, Siri's. We talked about the advantage of using relational databases. Remember, we worked on an example of the sales data bases where we explain how applying additional orgy brought techniques to field with identical type of information into tables Can speed hope. Retrieving data. You didn't forget this, right? Well, this is a turning point in our SQL journey. Any lessons to calm you will learn how religion SQL tables walks in practice joins. I ask you all to that. Allow us to construct a relationship between objects. Think about strategies that would allow us to link some of the tables in our employees. Data our car. We know what information coming retrieve from the different tables are. Wash your baby criteria according to which will create relationship between two tables. Traditional skimmers are the perfect to that will help you find a strategy for linking tables. Let's examine the representation off the employees. Databases in specific will focus on the employees and the department employees tables. Technically, a joy shows a result set containing fields derived from two or more tables so they can use a join to relate the employees on department employee table, then extract information like employee number four star last name from the employee stable . Andi department number are start dates off the labor contract from the department employee table. The idea of using joints is to food forced almost finally related column from two tables that contains the same type of data in our keys. This can be the employee number column. This technique wouldn't allow horse to extract information from two tables under certain condition, based on the employee number column. Second over. Frito had columns from these two tables to our outsports. In example, we could opportunity fields we just mentioned and play number and name, department number and start contract. It's great in the next lesson will show you how the joint centers walks before that. While still at the stage thinking about a strategy for a trillion data from different tables. Please be very careful about the following the column. You used to relate tables. Most represent the same objects such as I D, but the tables you are considering need noticeable. Logically, I just sense in the relational schema. An example off to logically Justin tables are the employees and department managers tables on the employees Andi titles, tables the department manager table on the department employees table are not logically adjustments. This means they can be joined to and you want after Medicaid's relationship that goes through the employees table and the employee number qi or through the departments and departments number key Specifically, what is important is you have found a related column connecting the two tables. Also in the lessons that follow will show you the most notable types of joins in my SQL In our next lesson will start with the inner join. Thanks for watching. 42. THE FUNCTIONALITIES OF INNER JOINS 1: all right now the first ask Your Joe Discourse is in a joint to the streets. Its functionality. We suggest you refer to the following graphics. This is a Venn diagram in mathematical to representing all possible logical relations between a finite collection offsets the department money judge duplicates Able is represented as this set on the left while the department's duplicates able is represented by the search or the second on the right. Okay, good, which will be related? Colombia, We said we can join tables with columns of the same type and having the same meaning. Therefore, the related column off these two tables who built apartments number the area that belongs to both circles, which is field. Which rate represents all records belonging to both. The department manager duplicates on the department's to pick eight tables. This area is called results set. The inner join can oppose extract. This result says, would you step of joint become much records off two or more tables? The same values appearing in a department number column on both tables are the much in values also called a matching records. The values that not much will not appear in our final outsports. Logically, they are called non much in values or not much in records, as usual on example from the employees. It of these can clarify the concept. Let's check the current state's off table on the left side of our graph. The department manager duplicates table destination. We have ordered the OUTSPORTS by a department number, so it is easier to see which records consented values in all fields on which don't Okay, what we are way for Records Continue information about department number on contracts date . That's why the values in that column appear as no. In addition, we collected about departments Number one. Then we have data about the departments from 2 to 9. Okay, lets carry out the same analysis about the other table departments. Duplicate stable populations contain no department number for some reason. Therefore, we seen all in the first column or the first rule. Next, we have details about the number on the name off all departments from wanted level. With these exceptions, there is nothing about the apartment number two Nida in number. No Indian. The pattern stain on 11 do not have correspondent names. Okey nice. Let's remember these creates on in. I join two duties who need to join syntax, which is very intuitive, it says. For showing columns from the different tables are specifying which related column will be. Here we go and it's a let statement, right? All columns you wish to see in the results. It is important to designate the tables the columns belonged to because the data is not a single table this time. That's why, besides type in the keyword from an animal before stable, it should proceed by writing. Join in the name of the second table. OK, great to this moment. The syntax allows us to specify the fields would like to see in the result on the tables were much in, but we haven't specified how we would relate these tables of week. Let's go ahead by typing own. When quoting the columns, Te relates the two tables. This is done in the following way. Use equality Sign surrounded by the related column names from the two tables. You are specifying where s key will extract the matching via leaves from all right. Accordions year is the main body of this infrastructure about right enjoys great. Before prosecutor example, let me share fundamental could in practice that professionals use. Join syntax, aliases? Not precisely. I'm talking about aliases, all the tables names. This means that one can renamed to city one until 22 t two when used for signing table names. The aliases are usually added, writes after the originality name, which are using the keyword us. Then, instead of typing the entire tables names in a select block, you can use T one and T two respectively. Assure you by the hand of dissection of the course, Siri's. You will have internalized this technique and to become written for you, please. If necessary, take a short break to wind. This lesson in the next war will see how in our joint come implemented with an example using the department money. Don't duplicates on the department's duplicate tables. Thanks for watching 43. The Functionalities of INNER JOINS 2: excellent. Let's apply. Joins in tax to the department manager. Duplicates on the department's duplicate tables. Jumped in a list of records containing information about the department number. The employee number on the department name. Later to every manager. I'll use letters. M. Andi as idiocies 42 TiVos, respectively. No India content. We can specify the future I would like to see right after the select ski ward. Let's instruct the department in employee numbers from department manager duplicate civil and the department name from the department's duplicate table. All right, therefore, the next time will most state, but it suits tables to be joined. While doing that, he still forget toe. Add their aliases. So most type department manager duplicates am carefully. A answer. Join, as this is the type of joint we implementing and finally departments duplicate the also to conclude the joint, since else we must specify the column relating to the two tables. Remember, let's do that by typing all on, then equalizing or conceptually much in the department's number column from the first table with Colombia in the same name from the second Table. These are the department number field from department Meijer duplicate civil on department number. Field from departments. Duplicates table. I like to remind you that they're letting columns call. The two tables need not be named in the same way, however, it helps avoid confusion, and it's more convenient you do. That's why, in example, both fields carry the same name before running. This interesting query are the results. According to the department number, this will improve the outsports representation. Since we'll pretend it's column from the apartment manager duplicate, Seibu will sort the information in the owner by close off the department number from the M . Tebow. All right, less is a tedious query and see what we obtain. 20 rules off information. Let's check to retrieve data. We have data about it departments from 3 to 9 only. There is no detail about department of 1 to 10 or 11 about the ones labelled as null values . Why is this happening in our joints? Extracts only records in which values interrelated columns, much null values or values appearing in just one of the two tables and not appear in any other are not displayed. Let's analyze the outcome by examining tables rule by rule, the department. My duplicates as a few. No value department numbers on as new information about the partner. 1 10 and 11 therefore, in our joints do not represent in all values. The 1st 4 rows do no appear in our join We executed. We have to insist records in this table altogether Entrances matters forgives those 22. Hence, there must be two records. Any and I join. We do not see because it displayed only 20 rules. Now we can recheck the content of the department. The briquettes zero. It's out in no value. Department number two the receipt in our joint out boots. No, we don't are. Why didn't ask you? Oh, mighty no values or the first table with this? No value Because it would make no sense. Only no, no much in values. I am play moving for the department. About two dozen participates in any record Three rabbits in the department Manager Duplicate table. Yes, we do. There are two records with its name in there On namely this ready to records we do not see in the final output. There were no values from it. Departments duplicates able to be much with them in a joint. Also Finally, the department's duplicative will ask two more records. They're off departments, 10 on the level. But as we mentioned, they have no values to be much which, from the department's manager, duplicates table. These two rules are dropped from the outscore. Great. I hope this analysis makes this clearer and explain why we say in our joint is represented by the common area of the two circles. An event diagram. This area is namely to set off the marching values off both tables, which are the only walls displayed in the inner join output on what ifs not imagine, values did not exist simply, the result said with the empty there'll be no link between the two tables. Thanks for watching. 44. Extra Info of USING JOINS: in this lesson will provide you with a few notes on using joints, not just in our joints, but any types of joints forced. We saw that any out sport we can select different columns from the tables were referencing in the last example, which shows the department's on employee numbers from the manager's duplicate civil on department name from the department's duplicate civil. We could also have headed statin on ending dates off managers contracts. Let's check for em from dates on em to dates 100%. So me hard, any combination of columns would like in our outsports. The only condition is that it column about the parts off world of the joint tables. The second note our like to share with you is that some people preferred type in the code, any from block forced, so they specify in advance all aliases they will need in the rest of the query. For instance, they would first type from department monetary pickets am in a joint departments duplicate D on the equality of the two departments numbers. Then they will specify selections off interest in certain the feud names with the help off the assigned aliases. This may be up for if you're joining more than two TiVos bought. In my opinion, what can get used to remembering a few letters? Unfollowed. It's up, down flew off the query anyway, the order to be followed is a decoders. This question. What matters is in the query. You free teen is correct, all right. As far as in our joints in task is concerned, the world inner is not obligatory. It's could be admitted in this query, whether you type in a joint or just join its next. No difference as ehskyoo, we always understand you're talking about in a joint. This is what protection as would typically do. That's why I will use join instead off in a joint. On that issue in of this program I could encounter is whether the order in which is pacify the much and columns mutters meaning. Is there any difference between Cyprien M adults? Department number equals deducts department number on deducts department number equals M does department number. No, there is no difference. It is off to you. I use the m table first on and the D table second toe. Avoid confliction with the order which I designated the two tables to be joined on. This might be a very useful trick, as you see in the next lesson. Otherwise, from a technical perspective, there is no difference. Okay, good, let's focus on the order. My clothes. Do you remember we saw that the outs 40 obtained, according to the department number from the manager's duplicates Able. Technically, this query will walk if you remove the table indication or if you change it to the departments to picket table. The point here is that it is not obvious why we use endured department number and not something else, but you'll appreciate this little detail in advance. I walk with much larger datasets. Presidio's indexes are other tools, unless used is. In addition, when any kids in the column off the department manager to pick it stable, SQL would display a narrow. I hope that after going through this notes, you feel you have joined the rescue of community in the next lesson will show you what duplicates records are on how to deal with them. Thanks for watching 45. Duplicate ROWS: also known as typical rooms I'd in the car rose in our SQL table. This means for you. Pay off the picket records in values in each column coincide generally to pick a rose are not always allowed any disabilities or detectable. The regular practice off many occurrences is to clean your data from such occurrences. However, there sometimes encountered, especially in new raw on controlled data in this lesson will show you a way to under them in your queries, as in, not all Fox are decides. Clean does not have any duplicate rules. Therefore, we can insert one rule in a department manager duplicate table that's identical. Which one of the others and play number 1102 to 8. Department number three walked on the 21st of March 1992 with the contract with an indefinite end. Apparently, that's why the last value will be 99990101 All right will also create a duplicate rule in the other table. Let it be the night apartment customer service. Great. Let's check what happened with the two tables forced its everybody data about the managers is the same as resource AIDS in the previous two lessons. With so difference, the employee number 1102 to 8 appears there twice on How about the department's duplicative all We find it the same way we did before. The only difference is that the customer service department is shown twice. Fine institution is clay. Now look at this picture. It isn it photo off the outsports off 20 rules when the initial state or the two tables was used without replicates? How do you expect the new multiple to change we got in the water from the picture? Do you think the number presented rules will be smaller, equal or greater? Let's check the outsports off this inquiry this time for the two tables containing duplicate records. We obtained 25 rules. What do you think this additional five rules came from. First, we don't just have one but two employees with number 1102 to eat, hence two times the department's name. Human resources is appended against them. This gives us a single additional record appeared with what we had earlier. The remaining for extra records at the end of this result sets the department duplicates. Able contains a difficult rule for the apartment number nine customer. Siri's. That's why every time Department number nine from the First Table is much with department number nine from a second table, two records are displayed. That's true. You can see each of these employees twice on 11784877939 on 692 Also, what I have in duplicate values is a mistake in the databases, or you just don't want to see the pick your rules. Query results years out of undue duplicates group by the field that differs most among records. In our case, this will be the Employee Number column. This was stuck all rose that have the same employee number. Would it return the initial passports with no duplicate values? Absolutely 20 rules. Perfect. This is an important tool in your arsenal. You cannot allow yourself to assume there no duplicate rules in your deter, especially if it contains millions of rules. It would be good to get used to group in the joints by the field. That differs most more records. I hope you enjoyed watching Just listen See you in the next one 46. The Functionality LEFT JOIN PART 1: we spent significant time introducing joints in the inner join type the benefits are doing . This extensive introduction was that now you can quickly understand Len other types off ehskyoo joints. The Ben Diagram you see allows you to visualize all the left Jonah walks. It's outsports allows us to see all records from the table on the left side of the joint, including all marching rose off its tables. That's why competitive in our join, the results said. Colored in red, includes the rest of the area they left table UNESCO tens. This translates into treatment or much in values constitute tables. That's all values from left, several that much. No values from the right table. To clarify these concepts, let's see an application forced that remove the duplicates from two tables. This without hours to explain. Its actual joins will introduce in the next few years, since better implicate values on flatter output attributed records on do not contribute to the result, we obtain meaningful E. That is why we decided to completely section about joint without them. Pakistan, with duplicates will be left a spot off the exercise in this section. So don't worry, you would do with them. What? Proceeding with the course. All right. Technically, after debuting the rules in which we see the respective employees and department numbers SQL deal. It's all in a sherry. Codes on all their duplicates. So to proceed, we must hardback the initial records. Please don't forget to do that. Okey. Everything lose good now. So do you know what else we can do? We can load the same court we use Any previous exercise. The joints on text will be subject to its lights. Change. Do instead of religion into tables to the key war joint or in our joint, The most type left. Join. Okay, that's all that needs to be modified in the code. Let's see what will happen when we execute this query. It returns 26 rules. Six rules. More than 20 rules we obtained in the example about in our joints, which are the six rules that make the difference. Let's check this out to start. Analysis is refer to devel diagram. Basis records represent the area field with blue. This part of the left sets where no values march with the right table. Technically speaking. Luckily, all six records off interest and it's all part off the output forced. All four records are appear in the manager stable but are not seeing any department stable . Have been retrieved. Referred to employees with numbers. 99990456 and seven. Why had this for Rose? Retrieved Because they were ready. Start and it's able only left, but not any table on the right. There are no department number values that can match. This records the table on the right. Hence, they are an example of non matching values from the left table. Great, there are two more records. We must discuss the ones off the pattern into from the left table. Department managers duplicates appear in a left. Join out support. If you remember, there are no such records in departments. Duplicates table Right there. We had information for departments 1345 and so on until 11. And nothing about department to the two records appear in the final of journalist, but there were no corresponding values for department infield that can come from the table on the rights. Perfect. Basically, this is proof that differently from what we saw, foreigner joints were working with the left joins the other in which you joined tables, mutters having the maladjusted will aim or the department's table D left can change the results completely in the next lesson will provide an example of what happens when we switch the other off the two tables. Thanks for watching. 47. The Functionality pg LEFT JOIN PART 2: in this lesson who like to emphasize that in certain locations the order in which he joined tables in SQL Motors, Let's invert the other or the tables who plays the department's table in the left and the department money just table on the right return on a different out bolt. This was due to our swapping the direction of the two tables. So now we'll have all much in values from the two tables. Close the department numbers from the department stable that much. No value from department manager or duplicates able. Therefore, we can expect to see records for the parchments. One saying on 11 Right? Okay, but why don't we see any of them? The US allies, In a way, we've chosen the first election in this query after we worked there, the order of its tables and as a pathetic weary The out will change. But that's not so abuse. Organize it properly. Piece retreated Department number data from the department duplicates able not from the department manager duplicate table. The correct way to do that is to retrieve the four selection from the first table you are set on enjoying since that's good. Now let's as acute query and check its output. Great. The picture is the opposite off the one the analysis just conducted, referring to the Venn diagram. Imagine that with this query, we swapped the places off the two tables. That's why in the results section, we have only one or department number value we have did about department, one that has no information about an employee number. The latter is a consequence of the fact that we have no data about the partner number one in the monitor table, all right, And because the department stable contains no information about the second department, there will be no record about AIDS in the results set, either. Finally, at its core down, you see information about all departments from 3 to 9, with corresponding employee numbers on department names. At the end, as you probably expected, departments 10 on 11 are quoted, which no information the remaining two fields. This is all fantastic now. This is I mean, if you since article regularities we started in our joints, any periods, lessons, why they called us, because the values and outsports are common to the two tables and results set is in the inner part of the Venn diagram. It is area where both circles overlap. So outer joints waas exist, too. The left join falls in this category, since any outsports obtained you have data from the outer part of the Venn diagram to my point, Thierry's. In this case, you could also use a keyword left outer. Join our you will extract an identical results. Therefore, if it's on the query, I don't care what out. Any cord out common notes the far from the one we obtained earlier. True, True. Absolutely. We obtained an identical results. Just remember that left join I left outer join our two interchangeable freezes. From a professional perspective, there is no reason for typing key word out of here because if you're using a left join, it will always be on outer type off join. This is like committing the ward inner from the inner join, if you remember, therefore, as long as it is clear, what's up on joint will be applied. Professionals prepare shutter phrases cool. There is one final interest in detail are like the hard left joints can deliver, at least with all records from the left table that don't know much any rules from rights able differently. The idea to obtain or records that appear only left out of blue parts off the diagram in this picture which are these records in our case, looking at the outsports obtained with a forced to live. Join this that it stops its rules off the detail list. You see, they have no values in the department. Infield on. How do we get to this Rose programmatically The trick is to include a were close. His condition will be all department name values to be known. So you must type where department name is No. Three left. Join will retrieve this beats off the table. Whose column information comes from the right stable and is no. Makes sense when you think of it, doesn't it? Let's as acute, equerry and check, if you will obtain Is six roll results Excellent. Yes. Can't be better. Now roll up your sleeves and have it go off exercise attached to this. Listen. See you in the next lesson. 48. The Functionality of RIGHT JOIN: Besides, left joined. We also have right joints in my ehskyoo. Their functionality is identical to the left join, with the only difference being that the direction off the oppression is inverted. So innocent query We run earlier. We could implement a right join or a rights out of joint. These two phrases are interchangeable as well. Okay, let's do that and see what the out sport will get. 24 rules. But they are different, right? How could we interpret this result? Forced The managers table was set to be on the left side while the department's table was placed on the right. So looking at the vendor Graham, imagine we're interested in the entire right circle, comprising the common area in the middle where we carried out here is logically symmetrical to the operation we performed earlier, which left join Well, this time, the altar recalls will be from the table. Look, it said on the right side and ven diagram the department's table. Think of it as the department's table is joining the managers table from the right side, since in our selection, the department number is extracted from the manager's table, we see for null values if we substitute the table off this field with the departments to bake a table instead of having four, no values at its top will have one all value the one on then the 10 and 11. Okey. If we post for a second and think about the techniques we learned in the last few lectures , we could come up with this question with. They were only right, joined or left joint with uninvited tables. Order will obtain the same out sport, right? Yes, we will. Just please remember to keep the sim aliases as the ones used in your eye joint. So amazing it wants a one output. Both it, I guess, considering what we said in this lesser, you could feel there is no sense using right joints instead of left joins. Given we can revert the tables other and you'd be right, because right joints are seldom applied in practice. But it exists on I good exercise for developing a sense off our outer joints. Walk in my SQL greets Please remember the following notes before concluding this lecture. Most if year Tim Lincoln column Remember, it is the same as much in column because summarizing functionality off the right joint. In the following way, we'll apply in Iraq. Join all the records from rights able will be included in the results it values from the left stable will be included only if the Lincoln column contains a value coinciding or much inlay left value for the Lincoln column off the right table. Fantastic. In addition, when talking about relationships, let's are right. Joints are profit examples off one to many relationships in my Askew. Oh, for instance, in our last example, when we said a left join each department's from the department's duplicates. Able as represented by the department number could have been the department off. One. How many managers formed it. But men's manager duplicates in manager, who is also an employee can belong to a single department. Only this an example off our they want to. Many relationship can be exhibited in the left or right join Kiss. Also feel free to apply various elections on joint combinations on these two tables to get used to it. Thanks for watching 49. Differences Between the New and Old JOIN SYNTAX: all right, so we've covered a wide range of topics. There are many lectures off. This section will be more demanding. And so, with all lessons until the end of the course, there will be challenging. And that's why you may want to rewind some of the earlier videos or spend extra time while solving exercises for this lessons. History. Free to do so because we approaching in advance, ask your topics. Were walking with Deter busies would be even more fun. Let's start by examining the join. You see, we ran its any lesson about inner joints. So please assume we're connecting the same duplicates off the department manager on the department's tables. Once this joint has been executed, we obtained 20 rue output information. About 20 employees is available. These people walk in departments from 3 to 9. Okay, good. Please remember, the two tables overlap. Therefore, there are several entries with the same employee numbers. Professional call such entries. Connection points basically by using joints as a shooter. Data with the connection points for one table. The partner manager duplicates is executed with the connection points off the other table departments duplicates. This is a principle we apply to obtain the desired output. Great. Now let me show you The same results can be obtained using the wear clothes. The logic toe add air to is different. Instead of joining the man adjustable with department stable through the departments number field, we could indicate all tables involved in this query. After the from statement will have to separate several names with a comma, then to completely query it. Wear clothes will be followed by the connection point. Let's run this cold and verify the results will be identical to what we had before. Absolutely on employees Walking Department three through nine. Fantastic. So at this stage, you are probably wondering, isn't it the same what I will use way or join to connect these two tables? The answer to your question is both years a new yes, it is the same because apparently the retrieved output is identical. What I you choose to join or the Western terms? It's practically the same, especially when dealing with small tables or queries, but our example not complex enough to show you why. The real difference between its structure is using where is more time consuming because the competition our approach for pretending to require deter is inefficient. Misremember. The Westons acts is perceived as morally old on Israeli employed by professionals. That's why I would avoid using its. In addition, on advantage off the joint syntax is that it allows you to modify the connection between tables is the one could apply a left or right join Isley, so it is intuitive for the reader off the court to understand. Finally, in our example, we applied away on the joint structures to two tables only. But please remember the similar dick applies to situation when God drain deter from modern two tables. Wonderful in this lesson represented two ways of collecting data from tables in your database is although, and advocates of using joints for connecting tables do not think the knowledge of yours in the where clause in this context is redundant in a couple of lessons will show you out of combine it with the exist or brittle and perform more delicate tasks 50. USING JOINS AND WHERE TOGETHER: to continue. The topic was started in our Paris. Listen, let me see. You shouldn't be surprised if you encounter boat away and join in this inquiry. When this happens, the where clause is not used for connecting employees on salary stable. That's done by the joint clothes instead. The where is used to define the condition or conditions that would determine which will be the connecting point between the two tables. Let's clarify it is with an example said. We want to see its able containing the employee number on the first and last name off any withdrawals. Who's unwell? Rent Admiration has been more than Warren of $45,000 to achieve this group who need information from two taboos employees called E for short on salaries named s. Okay, Perfect. Then we can join the two tables by collecting them through the employee number data not in new. So far. Rights. Good. This is where we should ask ourselves an important question based on which conditions do we want to extract data? The answer is I'm lost salary. Greater Dan War around $45,000. Therefore, the court we need is where the salary column from its allies table contains a value greater than 1 45,000 Let's check if this come, bring the desired outcome. Absolutely retrieved for four records off people paid more than the designated amount. But remember that any retrievable we can include any of the column within. Important, however, it is good practice, not omit. The column that acts as a connection point between the tables. Used any joint in question on the field upon which the condition has been set. An example. This means it is good to include both the employee number table from the employee stable in a salary column from the salary stable greats. That's by this. If you're not interested in the employee number, although it's the connection points in this query, you can live it out off the list off selected fields. Remember that a professional would really do that. Do having the connection point quoted can lead to set in programming benefits. Walking with more advised sq, Choose some of which you'll hear about later in the course. Okay, this will do for now. Thanks for watching 51. The Functionality of CROSS JOINS: to continue the topic. We started in our previous Listen, let me see. You shouldn't be surprised if you encounter both away and join in the same query when this happens. But what is this type of joints determines exactly about. Well, it cross join will take values from a set in table and connect them with all the values from the tables we want to join it with. This is in contrast to the inner join that typically connects only too much in values because joint connects toe all the values. Not just does that much. That's why, for a mathematical point of view, it cause joint is the cutesy in product of values. Off two or more sets, All right equals Joint is particularly useful when the tables any data bees are not well connected. The most admit that employees database is not really suitable for applying this kind of joint beautifully, since its tables are indeed were connected, However, because to use the employee state of these two just two on exercise with across joint, can't we? Sure, let's see an example. We want to obtain results it we deter containing all department managers in the department they can be assigned to. This means that we will need all the detail from the department manager table to join all the detail from the department stable. We can achieve these by using it cross joint. Technically, the results can be obtained by the kitchen. The following query It will allow us to connect all the values, form the department's table with all the values off the department manager table. More precisely, the results will be the project, or this set of values for the foster boom would set off values from second civil. Then, to visualize our multiple bitter will order. The values by employee number are specified in the department manager table and then by the bottom in number are specified in the pertinence table. Let's run this query to check. If it was correct Perfect. We can observe that all the department managers have been connected with all departments. In other words, there are nine different department numbers that corresponds to the employee number off each monitor. Nice. Now let's see a more interesting application of the course joint. Well, introducing the old joint sent us a couple of lectures ago. We showed you how commas can be used in the from close to designated tables that must be connected. Remember less civil apples where we apply this technique, for example. Well, the result is the same the size that this is exactly the export of the join Between these two tables, with no way statements with which we can set a condition, three tables ends. The result is across joint between the departments. Manager Andi departments Let's go evil for the in analysis like I write this example like this. See, there is no sign of the World Cross in this query, although the result is the same as before. In addition, we don't not be conditional statements connecting the two tables leader anyway, statements nor after the own key would. Nevertheless, my SQL Williams operatives joint as across joint. I won't raise a since our scare. Oh, you can't even write it as a in a joint on. The results will still be the same because there's no condition that has been assigned. The truth is that writing and in our jury without the keyword on is not considered this practice Writing. Cross join, on the other hand, will help your colleagues have a much clearer idea about the expected results. Worried in your court. That's why my SQL is so powerful. Often there are many ways that could lead you to an identical results. But off course clarity is a substantial parts of writing good court. Hence, in this cause, we stick to using best practice only. All right, cool, it appears. Example. We showed out. Combine two tables with across Joint one could clearly see in the OUTSPORTS for each department manager we had obtained is set of values, including also a column containing the number off the departments that it was managing. However, what should we do if you want to display all the apartments, but the one where the manager is currently off to be frank, there is not in simpler than that. All we have to do is anywhere close containing the condition off Department number in the department's table is different from the department number off the employee in question years. The results that we obtain after executive in this query, we know it is right because we can see that the department in which the manager is working , that's nothing soon. Moreover, if you compare the number of records retrieved the A with who wants retrieved in the last example. The difference will be exactly the number of the monitors in the department's managers. Stable. Fantastic. To sum it up so far, we can say that one can certainly substitute across, join just in a joint or really old join sentence. Moreover, we can include a condition in the unclos off the joint instead of writing one anywhere close. Nevertheless, in August, Ricketts is the result will be the same. But again, being clear on applying good practices is important, so we suggest you always use across join whenever it is possible. Great. Finally, we can cross join more than two tables. However, it should be really careful in doing so because it tables contained in lots of records, there's a chance the results might become too big, and hence my ehskyoo won't be able to execute the query. This problem may arise if you cross joining two tables. Continue lots off records as well. Nevertheless, when it's tables do no contain too many records, cross joints can become the perfect to you need. Okay, so let's make a cross join and combined eight really good gold in our joint. So we want to show more information about our department managers, like their names or add. It's this information is in the employees table, so we'll have to connect it to our previously defined tables. And yes, our after the second table and it from close type joint employees, the alias E. Vicky Ward on and said too much in column to be the employee number off the department manager and employee tables on When we have a cute this query, we obtain the desired results. Fantastic. Our little our debts again, we included the condition not to show the department in which the manager is working on. In the end, the number of records was the same as before, but this was something we actually expected. Right? Okay, so you've learned about another. He suits out off. Ask your joint in this lesson. So you in the next one 52. Combining Aggregate Functions With JOINS: All right, let's learn about something else we can do with joints. Imagine you're axed to find Harry salaries off men and a woman in the company. This is something you can easily do. Using the advocates function using on my great function means that the detail must be goodbye field off interests your boss is interested in separate in the employees by gender . Therefore, we can end this query by typing goodbye gender. All right, After I run the school, we consider the average salary off Men and a woman in the company are almost the same. Nice. Now please be careful because who go through something that is quite important and can potentially cause a mystic as a nation. In the previous lesson, you are free to choose any column for dimension tables at a selection. However, where you're using our git functions, we can simply do that. Let's see what happens if we set the Lincoln Field between the two tables and a select statement. We obtained the same output. But there is one additional column which Mary courts the employee number of the first individual from any off the genders registered in Italy's. However, neither 10001 No. 1000 To a numbers representing agenda. Delivering the fourth values is simply armor ehskyoo bees. When it is asked to extract a single value from a column that groups data other vision off SQL who probably respond with an arrow to the school, I will not display a result. This is another confirmation that it is your job to carefully check the obtained output for a meaningful explanation. In our kiss, we can avoid this trap by selection, gender and the average salary amounts. Aunt skipping the employee number Stay tuned for list lesson in which we will see another practical way to use joints. Thanks for watching. 53. JOIN MORE THAN TWO TABLES: joints are very solid tool. However, it should still under them. With K, for instance, my SQL allows you to join more than two Thibault's. Technically, this is not very hard to do when creating a query that joins multiple tables in most back it up with strong intuition, a crystal clear idea of how you like the tables to be connected. Let's see all that is done in practice. Imagine you knitted the first and last names off all managers. They're tired. It's on dates. When you were promoted to managers, you also need the name of their departments. All of these should be in the same, please. But looking a delusional schema exceed forced last names. You can retrieve the ire dates from the employees table, the statin off from dates from the apartments, my deductible on department name from the department table. We'll have to join these tables. The common elements between the employees and a department manager vegetables is that they contain information about employee number, while the department manager on departments tables both contain the department number column. Therefore, the forced to tables can be joined through employee number, while the second and the third can be joined through department number fun. Fantastic. What's causing on good notice? How half study select on from Statements will first have to join the employees in the apartment manager tables through employee number on, then directly tied. Join on designates the Third Table departments specifying the second Lincoln column at the end department number. Let's execute this query to check out sport. We've obtained 24 unique entries. It walked also. Now we have all the dates. Are we need in a single output? Perfect? No, the more because you're using that in our joint here, although we type just joined not in our joint. The other over tables inquiry can be inverted, so let's comedy tables through the department number on then through the employee number columns. Okey as acute, and here we are again, we'll 10 24 rules which are sorted a little differently because we have not designated specific order. So the outsports is practically the same. Fantastic. If you like to combine more than two teams using not just in our joint but also a right or left join, you be able to use the same Cory structure. In example, if I sought to speak inside of second table with their I join the outsports would be the same. However, if you try various combinations off left and right joints, you can obtain completely different results. For instance, in the third table joins the result table from the rights. The out sport will contain all the rules of information we have in our data. Sets were just 24. Plan B right joints are query Lisa Swan outsports containing all rules of the employees stable. In fact, this was not a very meaningful results. However, there are many other situations in which you will need to join more than two tables on right or left. Joining some of them will be key. Therefore, it was important for you to know about reservation off joint symptoms. Thanks for watching. 54. TOP TIPS FOR USING JOINS: to reinforce what we've learned so far about joints on aggregate functions. We can complete the following exercise. Let's obtain the names off all departments and calculate the average salary paid to the managers on each of them. This exercises with shows how sometimes the house sport we want to obtain. It's quite simple but requires the use off. More sophisticated query. Yes, for cause Coalition asking more, please. Department names are in the department stable while the salaries amounts are recorded in salaries. Table is the relation between the stables. No, there isn't so following the lines and a schema, she refers joined apartments, which but my manager there would employees. And finally, with salaries no necessarily years, one really important feature off joints. We should look for key columns, which are common between the tables involved in the analysis on Unnecessary to solve the task. And this columns do not need to be foreign priorities in the context off our example. This feature translates into joining the department stable with a department my your table using the patron number as a Lincoln column. There we can join the sun are stable. True, the connection created with employee number because lets the department named feud off departments table on. Apply the average folks shown to the salary field off salary civil. There we can join the three tables in the way we described. Let's run this query to see what's the outsports will look like. Just one rule. Yes, years. Why you forget to goodbye Department. Name my escrow. Apply it default order to the parts Friends on deliver only the first World. This is how my s killer walks so less hard. Didn't buy clothes and see what happens. Excellent. That's it. The possible to be obtained is it's able with all departments on the average amount of salaries paid departments. Another important thing to know is that these department names consulted by feud. We have not explicitly mentioned after the select statement, given that it has two parts of the joint tables. In other words, we got other departments by their numbers. Will this walk 100%? Also just for information, Although that's not what professionals usually do, you could it's the table indication in the Group I section. If a column name is encountered, only ones throughout the joint tables put differently. If I delete indication Letter D before department name. The results obtained were identical because there is no other column called departments name that belongs to departments, department manager or salaries. Okay, good speaking off observations. Remember that you are able to rename the salary column in out schools anywhere you like. For instance, I'll call it every salary on here we are. Moreover, it was sort results by Harry salaries calculated using the market function just in your query with other by average salary descending as a cute and perfect. This is even more men in full output. By the way, if he used an alias after the select statement, it should implement eight in the other by close immediately. This is considered a better or more professional way off. Gordon. When it comes to sort in your outsports, aliases can be really handy. You don't have to type the allocate for shown any column name another time. Those you can think off this variable as average salary and then employ it whenever needed . Excellence. Finally, you could assign a condition through. Having remember that Aren is a close associate ID, which grew by. If you're interested in the apartments in which the average salaries are higher than $60,000 a year. You can use having on illness average salary again. Here's the output is what we obtain correct? Absolutely great. You would probably agree with the following it is. Sometimes it's hard to create a joint. However once currently created, it delivers nothing more than its able which data? As an analyst, your job is to extract fast and accurate results. So you must be pretty good at using joints. We look you enjoyed the last few lessons. Please do not keep the exercises attached to these lessons, as they are just as important for the learning process. Thanks for watching.