SQL Essentials: The Beginner's Guide to SQL Language | Vlad Burmistrov | Skillshare

Playback Speed


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

SQL Essentials: The Beginner's Guide to SQL Language

teacher avatar Vlad Burmistrov, 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

39 Lessons (2h 37m)
    • 1. Welcome! Introduction to the course.

      1:55
    • 2. SQL at a glance

      6:21
    • 3. Installing PostgreSQL - Overview

      0:45
    • 4. Installing PostgreSQL for Windows

      4:07
    • 5. Installing PostgreSQL for Mac

      5:19
    • 6. Creating Tables. Basic Data Types.

      5:00
    • 7. Relationships between tables. Primary Key, Foreign Key.

      10:50
    • 8. Basic queries: Filtering columns. Expressions.

      3:30
    • 9. Basic queries: Filtering rows. Logical Operators.

      4:59
    • 10. Basic queries: More logical operators.

      5:08
    • 11. Basic queries: Nested SELECTs / Subqueries.

      1:15
    • 12. Basic queries: Comments

      1:11
    • 13. NULL values: What they are.

      9:08
    • 14. NULL values: NOT IN and subqueries

      6:58
    • 15. Aggregation: GROUP BY. Counting rows.

      5:32
    • 16. Aggregation: Filtering - HAVING

      1:08
    • 17. Aggregation: Functions SUM, COUNT

      3:05
    • 18. Aggregation: Other aggregation functions

      2:00
    • 19. Aggregation: Counting DISTINCT values

      2:05
    • 20. Sorting the query result - ORDER BY

      2:37
    • 21. Table Joins: INNER JOINs. How the joins work.

      10:36
    • 22. Table Joins: Duplicate Rows, Missing Rows.

      8:17
    • 23. Table Joins: LEFT OUTER JOINs.

      6:00
    • 24. Table Joins: Syntax Variations

      1:10
    • 25. Table Joins: RIGHT OUTER JOINs

      1:50
    • 26. Table Joins: FULL OUTER JOINs

      2:46
    • 27. Table Joins: SELF JOINs - joining a table to itself

      14:37
    • 28. Table Joins: CROSS JOIN - cartesian join

      2:23
    • 29. Set Operators: UNION ALL and UNION

      2:27
    • 30. Set Operators: EXCEPT ALL and EXCEPT

      2:17
    • 31. Set Operators: INTERSECT ALL and INTERSECT

      1:30
    • 32. Functions and Operators: Overview

      1:11
    • 33. Functions and Operators: Useful Numeric Functions and Operators

      2:54
    • 34. Functions and Operators: Useful Character Functions and Operators

      3:54
    • 35. Functions and Operators: Useful date/time Functions and Operators

      3:06
    • 36. INSERT - adding new rows

      4:03
    • 37. UPDATE - modifying existing rows

      3:17
    • 38. DELETE - deleting rows

      1:05
    • 39. Thank you! Course Summary. Congratulations!

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

30

Students

--

Projects

About This Class

In this course you will learn the basics of SQL language for relational databases.

You will learn standard SQL syntax applicable to most relational databases.  In the practice exercises you will use PostgreSQL, but the knowledge is easily transferable to other databases.  The standard SQL is the same in all ANSI-compliant SQL databases.

The SQL language seems simple - and that's true. However, some topics can cause mistakes - things like NULL values, duplicate rows and missing rows, etc. This course covers the frequent mistakes, and how to avoid them.

The primary focus is on SELECT queries, for reading data. Often the tables already exist, the data is already loaded, and the only thing you do is writing SELECT queries to read the data. 

Other SQL commands - how to create tables and change data - are also briefly described in this course.

I'll be happy to see you inside the course!

Meet Your Teacher

Teacher Profile Image

Vlad Burmistrov

Data Scientist

Teacher

Vlad is a passionate instructor who teaches various courses on IT technologies, both in class and online.  He truly knows that every student's opinion matters. He is very happy to share his knowledge and experience.

Vlad has a Masters degree in Mathematics / Computer Science, and a Masters degree in Economics. He develops programming skills and applies them the real-world business tasks and challenges. He has many years of experience,  especially in large scale data analytics. 

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. Welcome! Introduction to the course.: Hello and welcome to the square essential scores. In this course, you will learn SQL the language for relational databases. We will start with simple queries to get data from one table and more complex queries with stable joins, aggregation and so on. We will pay attention, toe common pitfalls and how to avoid them. By the end off this course, you will be able to write queries. Similar. Do what you see on this slide a couple of words about me. My name is blood, and I have been working with sequel and databases for more than 15 years, teaching classes both in person and online. I'll be happy to deliver this course for you. So what's inside After doing Conover you? We will set up some tables with data Fulop exercises. Then we'll talk about creating tables and then select queries for one table. Next, a separate section about now will use aggregation and then a large section about table joints, then set operators, functions and operators and how to change data. This course contains basics equal elements for everyday use. If you don't have any experience with a squeal, then this course is for you. This course is also for you. If you know some phrases like select from where, but are not quite comfortable with using them. All right, so the road is ahead. I really hope that you will enroll in tow this course. Thank you. And let's meet inside the course. 2. SQL at a glance: First of all, let's talk about relational databases and what the square language is all about. In the relational databases, the data is stored in tables. Each table has columns and rows. Let's see an example. This is stable bond. It contains data about music buns. The table has four columns. Each corner has a name. Columbine Tidy. Contains bonds Number column name contains the band name called on the year is when the Bunt was formed and comment. Folk Women's. As for the rules, the table contains three rows of data. Etro is a particular music bond. The Second Table album contains music albums. It also has four columns, album number, album, name, music, bands number and the year when the album was released. In Relational databases, one table can reference another table. In our example, The Table album has a column Bunt I D, which contains the bond number. All other information about the bond he stored in the bonds table went to tables are connected this way. We can say that table album references the table bond. All right, so let's sum up in relational databases. The data is stored in tables and tables can reference other tables, then the question is, where is the database is located? Well, if the data size is small, you can install a database on your local PC or laptop. However, usually the data basis are installed on servers like age it in server rooms or even in special data centers. The powerful servers can come work with terabytes and petabytes of data. So we have a client, several architecture from your laptop. You connect to the database like ate it on the server, sent some sequel commands to the database and to get the results back. So what does Abbreviation esque really stand for? And what are they? Squeal commands. First of all, let's say that these free letters can be pronounced differently. One way is to say SQL another way is to say sequel. In this course, we will mostly use sequel. They're Variation sequel stands for structured choir language, and it has three categories of commands. The first category data definition language. Kris Commons for creating tables, changing table structure, creating in dishes and so on. The second category, data manipulation language, has a select common for eating later and insert update delete for changing data are dinking euros, changing existing cross and deleting crows. The third category, data control language, has commands to change prodigious manage disk space and other commands for database administrators so that they store it in tables with columns and rows in it. You can also use other words to describe the tables. Let's cover those alternatives. The word table is mostly used as it is. Sometimes it is called entity during early stages of database design, and another word is relation. Please don't confuse relation and relationship listed below. These are two different terms when the table has a column that points to another table. It's called a relationship between tables or reference between tables. Ghoulam's are also called fields or attributes. We here we can say that Table Bond has four columns. We can also say Table Bond has four attributes. Rose are also called records. The table bend has free arose. We can also say the Table Bend has three records. Another word for O is double, and this course we will mostly use their worst columns and rows and before diving into details. Let's have a look at a simple, select Queary. Here's of the bond table. Imagine that We want to get information about the bond. Metallica. They expected. The result is on the right side of the slide. It is also a table. And this table has only three columns and only one row. A simple, select Queary is written like this. Select and then at least of columns we want in the result. In our example, we want three Colmes. Then the word from end the table name where we get the data from table bond. Then the work where and a condition to filter the rose name equals Metallica. When we sent this query toe the database, it will find only Rose that contain Metallica in the name column. And it turns out there is out back. Okay, so this is a simple Queary will dive into more details in later sections. 3. Installing PostgreSQL - Overview: In this section, we will install, pose great squeal and create tables with data full up exercises first. Awful. The next two lectures will contain instructions. How to install was great squeal. One lecture will be for Windows and another lecture will be for Mark. Please choose only one off the do lectures according to your computer in the browser, we will put the following Klink listed here and the files with scripts allocated on the resources tub. 4. Installing PostgreSQL for Windows: in this lecture we will install was great squeal for windows. If you have Mark, please keep this lecture and go to the next lecture. The next lecture is for Mark. This one for Windows opens their browser and click was great school dot Work, download. Win those now loads the installer. Choose the latest version and choose your operating system. Either Windows 32 or 64 bits click download now. So the file is downloaded. And once it is downloaded, click it and executive. All right. Click run. Yeah, And give it some time. Yeah, Okay. Next. Next. They stole everything legation. And here give a passwords. Just any possibility you like for the server? Next for the lucky eel. She was English. United States. Yeah, and start the installation. All right, it's finished. Now take the zip file from the resources stub and place it on the desk. See in the temp folder, extract the files. And here we have the data files and the file script X there. Open that script file, select everything and click corpora. No, in the start menu, start again at mean for it will open up on the left. Click on servers was good sequel here. It may ask for the password and click on Post Girl Now their tools. Not just that. The tools quiet, too, might degrade out. It will be available on Lee if you click on the progress that a base on the left now here placed the text, which you copied from the script file. The script contains great table commands and core picker months here. If you have another folder for the files, what's the appropriate folder here in the screen to execute everything? Click here, the button on the top, right? And now click on positively Squeal scammers, public tables, and here you should have the tables created. 5. Installing PostgreSQL for Mac: in this lecture we will install was great square for Mark. If you have Windows, just skip this lecture. So opened the Web browser was grayscale dot Work and click download here. Chose Mark or speaks and down laws the installer. Choose the latest version and mark off eggs for the operating system. Trick download now. So the file is downloaded. Executes the file. It might ask for their bus work, They execute the file. All right, so here, click next, next and still every think next and here, give a passport. Any parts worth Do you like for this server for the lay Gail chose English. US utf eight Next x and do the installation. It may take some time. - All right, so it is finished on chick the button and click Finish now on the resources stop. Take the zip file and now unload that file. It should appear in the downloads folder. Now, Corporal, this file, click Korpi and go toe in the finder Goto folder. They're slash day, Empire folder. Go here and click placed. Then click on the archive to extract the files and on the folder click. They get in for menu here on the bottom click the plus sign, and here we will add pre religious Choose the Post Gary Squeal user and here we give it pretty religious on this folder. It is necessary for the Post Greece quail to access the files inside the folder. Now click the script mark TXT file. Open it. And here this has come ons to create the tables and look to the fails. Select everything and Click Corp Then in the launchpad, find the Forgot Me and four and click the bag admin for If it is not open up, it means it is already running. So find on the upper right corner and click new a godman for window on the left Open servers Open post gay squeal. It might ask for a passport here, then opened it a basis and click on the tools. Quito is great out because we have to choose a particular database just progress, and now it is available. So here waste the text that you have. Copy it from the script. 60 file. It has create table commands and core pick a months. Load the date, click the execute button to execute the come ons, and now let's check the result. Click on the PAS grace scam, US public and tables. The tables should appear here 6. Creating Tables. Basic Data Types.: in this section, we will talk about creating tables. This will include the data types, primary keys, foreign keys and the different relationships between tables. A table is created with a create table. Command in this example would create a table bond with four columns. Well, right, the cures create table and then a list of columns separated by commas. Every corner has a name and a data type, whether it is a number or a text or a date time, we will talk about the data types on the next slide. There are more than a dozen different data types, and they are different in different databases. In this lecture, we will cover the most frequently used to data types. They are the same in most that the basis the types small inthe integer and bigon store whole numbers. They have different size and different maximum values small and has two bytes per number. Integer has four bytes per number, with values for minus two plus two bilion. If you need larger numbers, then a bigon is the way to go. It can store large numbers shown here. In most cases, the type integer is a good choice for whole numbers. Fractional numbers can be stored in a floating point for month. It's a double precision data type, which has eight bytes per failure and aerial data type with four bites purveyor on the right. We have some examples. One E six means one and six zeros, so it is one milion. Fractional numbers can also be stored. Is exact values with a decimal point between whole part and fractional parts. These are data types, debts, Immel and numeric. For them, you can explicitly specify the maximum number off significant digits. The first parameter is the maximum number of digits both before and after the decimal point . And second parameter is the maximum number of digits after the decimal point. Okay, so we discussed the data types four numbers. Next, let's discuss data types for character data. It is char or character, the store text off, fixed length and watcher or character wearing toe story text off variable length in the parenthesis. You specify the maximum number off characters. The text data type can store character data off annual Young's, however, this that the type is not a sequel standard and it may or may not be present in different databases on the right side. Off the slight. Notice how the character literal are written in single quotes to include a single quote within the literal right toe adjustment. Single quotes, as in the phrase It's a pencil one would think are You can combine toe character values into a single value, and the operator, Tokcan Kogenate values as written as two vertical bars. And finally, there are data types for storing date time and day plus time. It's date time and times them by default. The time and time stamp do not store the time zone. If you need to store a time zone, you can add the worst with time zone on the right. We have literally for the date. The literal is written like this. A date key work full of it by the date in a fix it format year month and they same for time and time stems all right, so we discussed the basic data types. This is not a complete least. There are also other data types, but they're used less frequently 7. Relationships between tables. Primary Key, Foreign Key.: As we discussed in earlier lectures, a table can reference another table in our example. The album table references the bond table. Let's see exactly how it works. Let's take the first row off the table album. It's the album master of Puppets. This guy's a bond number 93. Not just that. When we're looking couple this value in the bond table, we expect to find only one row there. It would be strange to find two rows. Then we would not understand which off the two bands recorded the album Master of Puppets. So in the bonds table, we expect only one row for the Bond 93. This means that the column Bond i D. Contains unique values. Every rose has a different value, and for each value there is only one role. This concept of having one row for Rich Ray earlier. It's called Primary Key. The Colon Bond I D. Is a primary key off the bond table. If two conditions are met first, the calm has unique values, and second, the column does not contain now values. The no Whaley's are unknown Wellies. We will discuss them separately later on. Okay, so if the Bunt I. D column is unique in the bonds table. Then, for a given value of 93 we will find only one role or, to be more exact, no more than one role. And does the row exist for the value of 93? The answer should be, yes, the row must exist because we have an album pointing to that value in the bond table. Basically, everybody devalue is the album table should also be present in the bond table so that we don't have any albums that have about number, which is missing in the bond table. To describe this condition, databases have a foreign key concept. The foreign key means that this column references a primary key off a second table, and all the wiliest must exist in that second table. All right, here's a summary when the table references another table. In most cases, this reference can be described as a foreign key column in one table, which references a unique primary key column in another table. Then the question comes. When should we declare primary key and foreign key? And is this mandatory to declare them? Let's start with a primary key. The quick answer is no. It is not mandatory to declare the primary key. We can simply write, Create Table has shown on the top off the slide here. We don't say anything about primary keys or second scenario. If you want to define the primary key, you can do it when the table is created in the create stable command after the common bond , I. D. Were right integer and then the keywords. Not now. Primary key. If we define the primary key, then the database will ensure that the colon is unique. It will verify uniqueness when you rose are added to the table. The database will not allow any attempt to insert will use that already exists in the bond i d. Calm. Same for the foreign key. You may only not define it if you want to define it. Then in the create table, you can write, bunt, idea integer and then references the table name bunt and in the parenthesis is the coal name in the bonds table Bunt i G. If the foreign kiey is defined, then the database will check the reference when you rose are added to the table. If any new row contains will use that do not exist in the bond table. The database will not allow that operation. As a result, we can be sure that all values in the album's bond idea column are present in the bond table. Okay, so let's do a summary the primary keys and foreign keys way only not be defined. Both options have advantages and disadvantages. If we define the case, then we can be sure that the data is correct. In the tables, however, there is a cost. The operations toe adn euros modify rose and deal it rose will be slower because the database will do the checks to ensure that the keys conditions are met. If we do not define the kiss, then the data manipulation operations will perform faster. However, in this case, it is us who should take care that the tables correctly reference each other for already existing tables. You can check whether the keys I different or not. Different data bases have different ways to do it. In post Gary Squeal, you can open per get admission tool, find the tables on the left side and opened the Constraints menu. If the primary key and foreign key are defined, they will be shown in the Constraints menu. Now let's discuss how tables are related to each other. There are different types of relationships when tables are designed. In most cases, their relationship is one tomainia. Let's take our example tables, bonds and album. The relationship Oneto Mahaney means that each row in the bond table can have multiple correspondent gross in the album table. Indeed, one Music Month can release multiple albums. However, each oh in the album table can have only one corresponding grow in the bonds table. That's why the relationship is called 12 mania. A similar relationship is minuto one. It's really the same relationship on Lee. The tables are written in reverse order. In our example, let's write album first, followed by Bond. Now everybody boom. Rome can have only one corresponding ban Trow, and one band row can have multiple corresponding album arose, so the relationship is my knee toe one and as a type is a 1 to 1 relationship. When one row from the first stable can have only one correspondent grow in the second table and vice versa. In our example, we have table bond and another table bunt starts with additional data, forage, bent number of albums and number of songs. These two tables are related is 1 to 1 one row in bonds. Table can have only one corresponding grow in Bonn start stable and vice versa. This type of relationship is quite rare in our example. It could really be just one people not to tables as shown at the bottom of the slide. Creating two separate tables can be useful, for example, when some columns I used only rarely. If that's the case, then they can put those columns in a separate table, making the main table smaller in size. And finally, two tables can be related as many tomainia when when you rose from one table can correspond to many rose from another table. Let's take a bond table and a person table with people each. Music bond can have multiple members, and each person can participate in multiple bonds, especially if we don't take time into account. So the tables, bond and person have a mania tomainia relationship between them. In most cases, the many to many relationship is implemented by adding 1/3 table, and the third table is related to other tables with one domain ian relationships. In our example, that sort stable is the bond person table. It is related to bond. Table is one too many, and it is related to person. Table is many to one. Every row in table bunt person means that the bunt with number but I D because a member with number person I d. And through these stable the tables, bunt and person I related to each other is many To many, However, these two tables are not directly related to each other on Lee through the third table. All right, let's do a quick summary. There are three types of relationships between tables. First oneto one 2nd 1 too many or many to one insert many tomainia. 8. Basic queries: Filtering columns. Expressions.: is this section. You will learn to write simple, select queries to read the data from one table. You will learn car tow, filter columns, filter rose, use expressions and use logical operators. Let's start with the filtering off columns on the left side. We have bond table with music bonds. Ah, one quick note. Before we begin on the slight, we only have a small number off rose, as many as can feet on the slide. So the bonds table here has only three rows. On the other hand, in the lap exercises, you will work with the same tables, but they will have many more owes. You may even be able to find your favorite bond there. If you try, let's write some queries that retrieve all data from the bond table on the right. The reliquary examples. The first query is written us select and then us. The risk. That's the risk means that we want to get all columns from the bond table. The second query hasn't explicitly stuff Combs. They are listed after the select keyword separated by commas. The third query has the same combs, but the combs are written on different lines. This can be useful when you have a lot of columns. Each query is finished with a cynical, um, the Simic on separates different queries from each other. If you want, we have one query. You can write it without a cynical. You can write column names that's first. Second. You can also write expressions. Some formulas, functions and so one. In this example, we have again the bonds table and on the right. The query result. Two columns from the table and also the third Komen, which has the name converted to the upper case. How can we get this result to get this result in the query after the select keyword We right column names and also expressions. So we were right Bunt I D name and then an expression. The function upper is used to convert a character column to upper case. We will discuss functions and operators later in a separate section. One more thing in the third column, not just the words is you name. That's the way to specify a new column name, which is especially useful for expressions. These names are also called colon aliases. We will talk more about analysis later in this course, both aliases for columns and analysis for tables. All right, so you learned how to specify column names in expressions to get the needed combs in the query result. 9. Basic queries: Filtering rows. Logical Operators.: the next topic is about filtering gross. In this example, we select only Rose, which have bunt I d equal to 93. The filtering condition is specific. It in the wear clothes. In our example, the query returns only one row. If the table had two or more rows with the bunt, I d. Equal to 93 all those rows who would be return by this Queary? The way condition is optional if the wear condition is spits. If it then the query returns only rows that satisfy the wear condition. If the way condition is not specific it the query returns all rows from the table. There is also 1/3 option. If you want to have a glance on the table rose to understand what they look like, you can ask the database to return on. Lee several rows. You can use the limit close. In our example. Limit 10 means return only 10 rows. This option is useful when the table is large and the query without where condition would return. A lot. Afros. The way conditions can be more complex. One of the slight with heaven or operator, but I d equals 93 or bunt I d equals 1 92 For every table role, we check the first condition and then the second condition if one off the conditions is met . The query retorts that role in our example we get to host in the query result. If you want only rows that satisfy both conditions, you can come by and those conditions using the and operator. In this example, let's select only those bonds that were formed before year 1980 and which have a known empty text in the comment column. The condition year less than 1980 is met for the first and the third row. However, only the third row satisfies both conditions. Onley that row is returned by the query. If I were condition contains several end or operators, the result may depend on the order in which the operators are applied first or and then end vice versa. In our example, the condition bunt I d equals 303 is satisfied for the first row after Queary, and the condition in the parenthesis bunt idea equals 93 and the year equals 1981 is such as fight only for the second throw since these conditions are come by and using the or operator. Both rose. I returned by the query. Now let's take the same query. But let's put the parenthesis differently. Then the result will be different. The thirst condition in the parenthesis is true for the first and second row with the table . The second condition is true only for the second role. And because these two conditions are come by and using the end operator on Lee, the second row is returned by the query. Now, if the parenthesis are not specify it at all, like on this slide, then the sequel has an agreement about order in which the operators are applied. The inter operator is applied first, the or operator is applied second. However, even if you know this and remember this, it is usually a good idea to write the parenthesis anyway, like here at the bottom of the slide. This will make things easier for those who will read the query. All right, let's do a quick summary. We have learned how to filter certain rose from the table, using the wear conditions and logical operators and and or 10. Basic queries: More logical operators.: Let's look at some syntax, which can make it easier to write queries. Imagine that you have a list off radios and you want to compare a column with that least off values. You can write a lot of conditions like column equals Wailua and combined them using course . Here's an example. There is another way to do the same thing. Use the in condition like at the bottom of the slide. You're right where, but I D in and then provide the list off. Will use this condition. Compares the bunt idea column with the list off values and returns. True if you want, I d equals toe any off these Raiders and opposite operation not in is when you want to get all of a lease except the provided least off will use again. You can write several not equal conditions combined using the and operator, by the way, not just that. The non equal operator is written in sequel like this, the symbol less and then the symbol more. Another way to do the same thing is to use. They're not in condition you right where but idea not in. And then the list of values this query returns all rows, for which Bunte I. D. Is not found in the list of values. In our example, the Queer returns the rose with the bunt I. D. Three or three and 93 But the role bunt idea 1 92 is not returned. It was filtered by the not in condition. Now, sometimes the list of Phyllis is not at least off, literally like three or 3 93 Imagine that the will use to compare with our store it in another table. For this case, the sequel has a in condition with a sub cu area. It looks very similar where, but I D in and then in the brand officious instead of writing a comma separated Lee stuff literally like we did before. Now we're right. Another select from another table, least one. The logic of the square is the following. We select only those music bonds from the bun table whose numbers are present in the least one table. In our example, the bunt Quinn, with number 1 92 is not returned by the query because there is no corresponding number for it in the least one table. Okay, so this is an in condition with a sub Queary Um next operator is between the separator were , if ice that the comb is within a given range between lower and upper boundaries, the boundaries are included. So in our example, this condition means year is more than or equal to 1970 and less than or equal toe 1981. So the query is similar to the following query shown at the bottom of the slide. If you don't want the boundaries to be included, then they between is not the right fit for the job. Then you my right, the queer similar to the queria at the bottom and specify that strength comparisons more than or less then, is necessary. Now they're not operator. Sometimes it is useful to reverse the whole condition. Like here, we can write Bunt I d equals 25 Take this important ethicists and right the not operator is the same as writing, but I D is not equal toe. Five. One more example. Not want i d. Between two in six means that we take all the other will use less than two or more than six . You can also write this as a bunch idea. Not between two and six. The result will be the same. All right, so let's do a quick summary we cover. It's the operators in not in between and not. 11. Basic queries: Nested SELECTs / Subqueries.: Let's talk a little bit more about sub queries. Here's the query we have seen before in the filter in condition we're right where but I G E n and get the really was from another table. Least one. This query from table least one is a sub queary. It is located in the wear clothes off the upper level query. Um, so that's first. Second. You can also write enlisted query in the from clothes, not in the wear clothes. In this case, the nested select returns a result which is a table, and this result is given is an input toe. The upper level queria. Both queries can have their own were conditions and other options. These nested queries are useful when you write more complex queries selecting data from several tables. We were right, some off the squarest. Later, when we talk about table joints 12. Basic queries: Comments: and finally, one more useful thing is comments. They are dext that you write to describe the code for those people who will read it later, or another reason you can comment out certain parts off the Queary. In sequel language, the comments are written like this one line Clement's Begin with judicious everything after those day GIs to the end of the line is equipment and multiple lines begin with slash asterisk. Finish with asterisks slash, for example, the query at the bottom is commented out and will not be executed like in any other programming languages. It is a good idea to you score mints. All right, let's do a summary. We learned how to write the select query for one table, how to fill their columns, how to fill the Rose, how to use expressions, logical operators and Corman's 13. NULL values: What they are.: the next section is about null values. This topic is so important that we have a separate section about it. We will take a look at situations Weldon al values can cause mistakes and how toe with those mistakes. So, first of all, what is the knowledge value? The value now is an unknown failure. A mission quail you. It is used when a value is not known, and we simply don't know some information. When we don't know what to write in a certain colon off the table, we can write an olive alia. In our example, the table music instrument contains information about, as the name suggests, the music instruments. The 1st 2 rows have now values in the parent I D. Comb for the numeric data types than Australia is different from a zero failure. It is an unknown now value for character that the types then all value, is different from an empty value. The empty failure is written as two single quotes with nothing between them. The difference between al value and empty failure is really thin, and in some that the basis they are really the same value. However, in most databases, they are two different rail users now will you is one thing Empty Ray earlier is another thing. If you want to check whether a certain column contains an alv alia, you can use a special Syntex in our example Parent I d is no this queer will return only first and second throws off the table. No, just that if you write the condition like this, but untidy equal snow, this queer will not return any rose. The reason Because this behavior is the following, then Alitalia is an unknown failure. When we compare parent idea with our unknown failure, we don't know whether parent idea is equal toe or not to that failure Because the trailer is unknown in this case, the sequel language has the following agreement. The equality comparison with null values always returns force. Same applies to other comparisons, like more than less than and so one. Whenever you compare within Australia, the result is false. All right, so let's remember this. If you need to select Rose, let have on a value in the parent I d comb you shall not, right parent. I d equals Now you should write parent I D is now same for the opposite condition. When you want the roads where parent I d is not equal toe now, you should write Parent I D is not now. When working with now will use a useful function is a Carless function Carly's replaces now will use with familiar. You provide any other way earlier. Do you, from from now stays the same? In our example, Lets out 1/4 column to the Queary using a Carless function. Let's give this column and name parent idea to in this column we replace now will use with a minus one, will you? This approach can be used when we know that all parent idea values are positive, so we know that minus one will never appear there. If that's the case, we can make an agreement that minus one is used for unknown values, and we can use callous function to replace Now will use with minus one in our table. The 1st 2 rows have now values in Parent idea column the Carly's returns, minus one for them all other rose have values different than now. So those Wellies I returned as they are by the Carless function, let's discuss one case about null values where you should be careful. It's about adding to values to each other. Let's look at the table, my table in with two combs A and B if we want to add them together, we write acquiree, Select A B and the third, Cornel April's Be What will the query return in the first row? One plus five. We get six in the second row attention here when we add two, and now the result is now because, adding to an unknown will, you also results in an unknown value. This behavior off now, values can be a source off mistakes. Sometimes there are queries like this, and only a small number of rows have nose in the big home, and you might not notice that somewhere use from the i column are lost because they are added to the NAL avail you If we don't want to lose number two. In our example, we can use Carly's function to replace now values with zeros. Let's add free more combs toe this Queary. The fourth column applies Kollias function, toe calm, a replacing now's with zeros and keeping other way lose as they are. Same for Colon be. And finally, when we add two calls together, issue off them with a Carless function. Then we get number two in the second throw. Now, for the second troll, it would be sufficient to apply callous on Lee to call on B. We did colleagues for both Combs because some other rose can have now in the A column in our example is the fourth row on the slight. We add two values together we same behavior about knows is true for any other operations minus multiplication division. And so every time and now value is encounter it. The result is also now same applies to the character data types for the concatenation off values. The concatenation is combining two values together. For example, could imagination off A and B one is a B one here work of another table. My table s there and character columns s there A and s there be concatenation within Australia returns now value this way. For the second row, we get a nav alia for the concatenation, as we did before for the numeric data. Let's add more columns to this square with a Cordless function here We replaced knows with empty rail use two single quotes with nothing between them. So now when we do a concatenation with an empty ray earlier, that operation does not turn any values into now values. In our example, the second row has a knowledge failure. So when we can kitchen eight with the now in the third column, we lose the value that came from column A in the same role. When we use Carless, the failure is not lost because now we can Kogenate with an empty ray earlier, not on our failure. All right, let's do a quick summary. We learnt what the normal values are and how function sent operators behave when they encounter the knowledge values. 14. NULL values: NOT IN and subqueries: another case when you should be careful with the knowledge values it's about in and not in operators. Let's start with the first. Here's a query that selects Rose from the bond table, taking only those bunt I D. Values that are present in the least one table. The contents off the list. One table is here on the left. It has valleys three or three and 93. In essence, this query is similar to the Queer is here at the bottom. We can write Bunt I D. In three or free 93 and that we can write is But I d equals three or three or bond. I d equals 93 here. We don't have any now. Will you see it now? Let's imagine that in euro was added to the least one table, and that row does contain an AL value in the I. D Co. The task is the same. Let's find music Bonds, whose numbers are present in the least one table. But now the question is, how will the condition where? But I d in respond to the Nala values against wants of this question. Let's real right. The query at the bottom of the slide. Here we have an alv alia. Now the condition is where but I d in three or 3 93 now and on the right, an additional condition is or but I d equal snow. And here we really have a equality check. So this condition always returns force for all the rose. But we're like this condition is combined with other conditions using an or operator, so other conditions are still working. This query re tours Onley rose, but idea three or three and 93. So basically the now Alia did not affect the Queary the query returns. The same result is if there were no in in al value on the slight Witcover now value Onley in the least one table. Now let's imagine that we also have an AL value in the bond table. How will the condition where But I d in response to situation when we have now valueless in both tables, Will it returns the last roll off the bonds table. When we look at the query, the answer is not so obvious because now we have an AL value in the sub query, as we did before less real right this squarely at the bottom of the slide, since the condition or but I d equals now is not satisfied for the last roll with the Bonds table. That rule is not returned by the query, so the query returns. Only Rose Bunt I D. Three or three and 93. All right, so we discussed the in condition. Now let's talk about not in again. First, let's start without any an AL values. This query returns rose from the bunt table except those bunt I D values, which are present in the least one table. The least one table is here on the right. If Israel use three or three and 93 we can all right, this queer like this but idea not in three or 3 93 and on the right Bunt I D. Not equals three or three. And Bonta, you not equals 93. So in our example, the Queary returns only one role with anti G 1 92 So far, so good. But let's look at the next slide. Let's imagine that we added one more row to the least one table with an AL failure in the I . D. calm. The query is the same. Let's see what it returns when we rewrite this, not in condition. At the bottom, we see an additional Nalle value. Where bond idea not in three or 3 93 now and on the right. The new condition bond idea not equals now returns false for all table rows. And since this condition is combined with other conditions using and operator the whole condition where becomes force for all table rows. So this queer returns zero Rose Notice that this Queary worked fine Before the valve Alia appeared, and now it returns zeros. So let's remember this case when they're not in condition, has a sub queary and that's up. Queary returns an AL value. This can cause the whole Queria to return zeros. How can we avoid this behavior off? Not in one way is to are aware condition inside the sub Queary like this. Where? But I d is not now. This way. The now value disappears. When were you write this query at the bottom? We returned back to the situation when the now Alia was not there. And now the query returns. Sam arose. So the recommendation every time you write and not in who. The sub query. It makes sense. Toe. Add a condition where Coloman is not now for the selected column. Even if the now value is not present today, it may appear tomorrow, and the query with this wear condition will continue toe work properly. In any case, all right, this is a land of section about now values. We have a new $1000 alias work, and let's remember that you should be careful with now values. 15. Aggregation: GROUP BY. Counting rows.: The next section is about data aggregation summing the data, averaging, finding maximum and minimum and counting the number of rows. Let's start with an example. Here is an album table with the albums released by the music bonds. Imagine that the Table has five Rose has shown on the slide. Each row in the Stable is a separate music album. Now What if we want to count the number off albums? To do this, we should count the number of rows in the table. This Queary is written like these as before, we'll write, select and later from and table name album. However, this time after the select, we don't write the column names, we write aggregation functions to count. The number of rows in a table were right count asterisk. The asterisk here is the same asterisk we have seen before. When we selected all columns from the table, this queer will return on Lee one row with the number of rows in a table. In our example, the number five. The next thing is a group by. In the album table, there is a bunch idea. Calm the bonds number. Let's count the number of albums released by every music bond, so we want the query result toe look like this. The 1st 2 rows of album table belonged to Bunt Number 93. So for these bond, we want to get one row in the query result with two albums in the count, then The Bunt 1/19 2 It has three albums. This is the second row in the query with free albums, so the query. So do the grouping calf rose by the bunt numbers we write group by Bond, I. D. And in the select were right, but I D and the aggregation function count asterisk. Remember that before using Grew by The query returned only one row with the total number off albums. This time when we use a group by The Queer Returns as many rose as the number of distant values in the Bond i d. Call. Um, let's group the Rose by another column the year Call him. It's the year when the album was released, so we want to count the number of albums released in any given year. The Queen looks like this again. Toto. The grouping Well right, the go by and the column name this time it's a year and in the select well right year and count us the risk. Why did the query return this result? Sometimes, in order to understand how this result was obtained, imagine that you first sort the data on the year. Coloman. This is done on the bottom of the slide. So now you can visually see that you have three distinct values in the year Coloma. And it is easier to see why. The Count column returns the values two and one When we use a group by close, there is a special rule about what we can specify in the least of columns. After the select keyword, we can specify either the columns from the group by or the aggregation functions. Nothing else. For example, we cannot write Select Bond I D Year and Count because the calm year is not present in the group buy clothes. It would not be clear what the query result would look like in this case. If we want to make the grouping by two combs both bond idea and the year, then both those columns should be spits. Fight in the growth by close. Let's take a closer look at the case when we're grouping the Rose by two columns, but I D and the year. So we want to count the number of albums released by certain bond in a certain year. In our example, we have one album in most cases, with one exception. The Bunt One Night. You, too, in the year 1974 released two albums. The query is written like this in select, We write Bunt I D Year and Count Asterisk and in Group, by also two columns But I D in the year, All right, so we learned how to count the number of rows and the use grow by toe. Do the calculation for the group's Afros. 16. Aggregation: Filtering - HAVING: one more thing about filtering data. When we do a go by, we first apply the way condition the filter only necessary rose and then Onley those rose participate in the group by aggregation. But what if we want to feel that the query result by the count asterisk a woman which appears only after the group by That's exactly what the having close does. At the bottom, we have the same query, and we added one more condition, having count us the risks more than or equal to three. The upper query returns to rose, and the bottom query returns only one role because the having condition filtered the first roll off the upper query. Okay, so the summary the wear clothes can filter rose before the girl by aggregation. And the haven close can feel the rose after the aggregation. 17. Aggregation: Functions SUM, COUNT: the count is only one off several aggregation functions. The next fashion is some. So some of the values off a numeric comb here we have a table bunt extended. This table has one row for every bond, giving us the number of albums and number of songs for every bond. Let's count the number of months and the number off albums they released. To do this, we should count the number of rows, count us the risks and some of the values. In the end album Comb by using the some function. In our example, we have three bands and 42 albums. So this is the sum function. Let's talk a little bit more about count function, as we discussed before when we were right. Count us the risk. We count the number of rows. That's first. Second, we can also write count and a particular column. In this case, we count the number of rows, which have a not now familiar in that particular column. To illustrate this, let's imagine that the bunt extended table has one more roll with an olive alia. In the end, Albom's column Let's write a query that counts the total number of rows and then the number of rows, which have a not now will use in the an album school and also the some off the An album school. The search stable has four rolls, but only three off them have an album, Israeli er. Other than now, this behavior off count function may be useful for calculating averages. Imagine that we want in a virtual earlier, off the end, Albom's column later, we will see an a veggie function for this, But you can also take the some off the column and divided by either Count us. The risk or count and albums depends on whether you want or don't want the null values toe . Participate in every raging the values Now one more Think about now values. Imagine that the group by comb contains an olive Alieu. In this case, the now alias at Rigid as a separate failure, all rose with the novelas are grouped together and are added as an additional role toe. The query result. To illustrate this, let's imagine that we add one more row to the table with an AL value in the bond I d calm. In the query result, we get an additional roof for the knowledge failure with the Wailua one for the count asterisks 18. Aggregation: Other aggregation functions: all right, the aggregation functions. We have already discussed the count and some functions. Let's briefly repeat count. Asterisks is the number of rows account for the particular column councils. Number of rows. Where that cone is not know the sun function sums up with the values off a column. There are also other aggregation functions mean is a minimal Wailua. Marks is a maximum wailua. A VG is the every ritual failure, and finally, there is account distinct function for counting the number of distinct values in the column . All these functions can be used in conjunction with the group by Close Toe group. The Rose in our case, go by the bunt idea. Call um, as we discussed before the group by contain silly stuff columns as shown on the left. Another option is to provide a list of numbers shown on the right. These numbers are Coloman positions in the select least of columns grew by one and two means that we're grouping by the first and second columns in the select. Please, not just that. This is first and cycle combs in this select, not in the source table. This approach is especially useful when we have a lot of columns. So when you modify the query, you don't have to copy paste. The changes from select toe grow by you just provide the column positions. All right, a quick summary. We have learned how the aggregation deals with now failures, what the other aggregation functions are and that a group by can work with colon positions . 19. Aggregation: Counting DISTINCT values: next topic is about distinct values. As we discussed before the count distinct counts the number of distinct values in a column . In our example. The column. The year has three different values, so when we were right count distinct year, the results will be three. You can also write the word distant separately without the count work. In this case, instead of counting the distinct values, the query will just return those distinct values. In our example, we have three different years to get those values you can write silly as distant year from album. Another way to get the same result is to write go by year and provide only Call me year in the select without any aggregation functions. The go by will return one roll for each year we failure, and since we don't have any more columns, the result will be a list of distinct will use in the year calm. You can also use select distinct for several columns. In our example, let's find all distance combinations of a bond idea and the year columns. We will get four rows in the query result. There is one special thing here. However, if you want to count the number off those distant values. You cannot use the count distant for two combs to get the number of distinct values for more than welcome home. You can do this first, use a sub query to get the list of distinctive failures and then use an upper level select toe count that number of rows using Count us the risk. 20. Sorting the query result - ORDER BY: and finally the sorting Afros when were right, a select from and so on. The database can return the rose in any order. If you're railing it to sort the query result, you should provide another buy clothes specifying the least of columns to use for sorting the Rose in the upper example. We're sorting by column year first and then by the bond i d. Call. Um, not just that the year calm has a desk word after it. It means that the sorting should be in descending order. If desk is not specific, the default order is the ascending order at the bottom. We have the same other by, but now we use column positions that refer toa the select least. This is the same Colin positions we have seen before for the group by. And what about null values? When we do a sort, we effectively are comparing values which one is greater than the other so that we place them in order. However, for the Nalle Valya, it is unknown whether an AL value is greater or less than any other way earlier. So here we have a special syntax. We can use now's first to say that the novel values should be sorted before any other way, layers or the second option. Knaus lust that the nose assorted after any other way lose. If these words are not specific it, then the nurse assorted after the other whalers. So the now it's last option is the default option. Let's have a look at a select query that has all the syntax elements. The elements are select to filter the columns from to specify the table, whether those come from where the field of the roles go. Buy for aggregation paving to feel the rose after the aggregation and over there by for sorting the queer result. All right, so we learned how to write a select query for one table. 21. Table Joins: INNER JOINs. How the joins work.: the next section is about table joins. This is a way to get data from more than one table in a single select query. We will talk about inner joins, outer joints, cross join and self joins. We will discuss how the database built the queer result and will discuss duplicate rose and missing grows. Let's begin with an in condition. You have seen this syntax before. We're right. Where Bon Tiggy team and then the sub Queary. So this Queary, it turns rose from the album table, which have a corresponding grow in the bond table. How many rows does the square a return? The answer is all rose from the album table. That's because for every album the bunt was found in the bond table, there are no albums that they filter it out by this wear condition. Much is that this query Presto tables. And in these stables we find those that correspond to each other with a matching will use in the bunt idea calm. This is very similar to joining tables together, which we will discuss shortly, but here we have one limitation. In this query. We can only select Rose from the album table The second table bump is used only for filtering, and we cannot get its columns in the upper level select To get the ability to select columns from both stables, we can join two tables together like this from album as a where a is a table alias. Then in the joint Bond has be, and then a condition for much ink. The corresponding crows on a dot bond i d equals b dot bond i d. It says that the column bond i d. From the first stable with Alias A must be equal to the column Bond i d. From the second table with Aaliyah's Be in the Square here we have a daughter us the risk after a select. So we get all combs from the album table. But we did not yet. I would put any calls from the bond table. Let's do this on the next slide. Let's select columns from both stables album and bond. So now, for every album, we don't only provide about number but also a bond name. Not just come when euros the query produces. It's the same number of rows as we have in the album table. We just added more combs from the bond table. So how does the database build the query result? One way to think about this is the following. Let's walk through the first table. Oh, by roll, Let's take the first row. It's the album, The Black Album. We take this role. Take the number 93 from the Bond I D. Column and search for the corresponding gross in the bond table for 93. It's one role, 93 Metallica. So we take these album row that bans Row and put those two roles as one combined row in the query result. Then we continue to walk through the first table. Take the second album, Master of Pipe. It's again Find the corresponding Crows in the Bond table and so on. Remember in earlier lectures, we talked about many toe one a relationship between album and the Bond tables. One bunch can have many albums, but one album can only have one bond. When we do a table join. That's the moment when this relationship comes into play. When we take each particular album, we find only one bunt in the bond table. Let's talk a little bit more about table aliases. When we specify aliases in the from Clause, then we can use those delicious to qualify the columns they link that that always where to get these or that colon. You can also live without a laissus. This is shown on the right. In that case, you can use stable name to qualify the column. Names album dot bond i D and bond dot bond A. Now here's the thing. Let's say we have a comment column. This column is present in only one off the two tables. In this case, you might simply write the column name without any Ellis or table name. The death of Boys will know anyway. Which table has that column? However, there is one caveat here. Imagine that we have a query. It works fine. And let's say the square is executed every day. After some time, the album table may change. Someone may say that the album's should also have comments, so Equipment column is added to the album table. If that happens, then the query will fail with an error because now it is not clear from which table the common column should come from. On the other hand, if we did use the table alias in the first place, as written here at the bottom of the slide. Then the query would continue toe work fine in any case. So the recommendation here when the query contains more than one table, it is a good idea to specify either Ellis or a table name for the columns in the Queary. Let's discuss most scenarios on the previous slides with Joint Table Album, Toe Table Bunt. For every album, we found the corresponding bond to get the bunt name. The tables were and may need to want a relationship. Now on this light, let's switch you the table order. Now we take the music bonds, and for each music bunt, we find the corresponding albums so in the form close well right, the table bond first and then the album table. Let's see how the database built the query result. The algorithm is the same as we discussed earlier. We walk through the first table here. It is stable bond. Take the first room. It's the Row three or three. The Beatles search for the corresponding gross in the album table. In our example, we don't find any rose if no correspondent Gross are found. They're all three off. Really? Bitos is not put into the query result. We continue to walk through the bonds table, take the second throw 93 Metallica Search for the Corresponding Crows in the album table. Here we have not one but two rows. In this case, the table joints work like this with technology three Metallica and we take the first corresponding album place those tools as one combined thrown in the query results. Then we take their own 93 Metallica Again and take the second corresponding album. Place them as a combined throw in. The query result. So much is what happens there all 93. Metallica is present twice in the query result as many times as we found albums for it. Then we continue Toe Walk through the Bonds table takes the role 1 92 Queen Find the three curse, pointing to albums and write the three rows in the query result. So from the bonds perspective, we have the following. They're all for the Beatles does not make it to the query result because it has no albums. In our example, they're all Smith, Aleka and Queen are present multiple times. It is an expected behaviour because we know that one bond can release many albums. Let's compare to query results when the albums are joined to the buns and vice versa. When the buns are joined toe the album's as you have probably guessed the results are the same. We only might a different order of columns in each case, so you can follow the joint mechanics more easily. If we specify this same column list in both queries, they will produce exactly the same result. So for the Endeavour join the order of tables in the from clause is not important. All right, a quick summary. We learn how the tables are joined together with an inner join both for many to one and one tomainia relationships. And we learned how the database produces the query result. Searching for responding rose in tables 22. Table Joins: Duplicate Rows, Missing Rows.: Let's talk in more detail about duplicate Rose. First, let's take a look at the table join we discussed before. For the one to many relationship. They're all 93. Metallica from the Bunt table is present two times in the query result. Because it has two albums. This is a normal situation because we know that one Bond can have many albums now. The reverse order first album table, then bond table Here. We know that one album can only have one bond. As we discussed in earlier lectures, the table bond can have a primary key on the Bunt i D column so that the Bond I D column is unique. If that's the case, then we can be sure that only one role will be found in the bunt table. But that ive the primary key, is defined. Now let's see what happens if the primary key is not defined and the table bunt has some duplicate rose. In this example, imagine that we have to host for the Bunt I, D 93 Mitt Aleka and the Dog Bond. The table June will do the same algorithm as we discussed before. It will walk through the first table, the album table, take the first roll, The Black Album, then search for the corresponding growth in the bond table. Now, this time we found not one Row but Turow's So We Were Right to Rose in the query result for this album, one for Metallica and one for our band. Next we take second throw from the album table, the mustard off pipe. It's It also has to corresponding cross in the bond table, so we write to Rose in the result. And so, um, now this is a duplication off roast that we did not actually expect on this slide. We have five albums in the album table, but the query result contains seven rows. Because two albums are listed twice. This happened because the tables are not in many toe one relationship. They are effectively in a mania tomainia relationship. Now you may ask, Why do we even discuss the incorrect data shootings? The data always be correct. The answer is yes. It should be correct. But sometimes the data may have some quality issues, and we studied. This case is to understand how the table joints work in those situations. No, just that initially the duplicate rose were present in the bond table two rows for the bunt Idea 93 however, because off this the table join duplicated Rose from another table, the album table. So the summary, if the table sigh related, is one too many. But in reality, the data does not follow the rules. Then the table joins can produce duplicate Rose. Now duplicate Rose is one thing. Another thing is missing Crows. It's when an album table contains some Want I D values that are not present in the bond table. In this example, imagine that the bonds table does not have a row for Bundt idea 1 92 This would not be the case if we had a foreign key defiant on the album table, but let's see what happens if the foreign key is not defined. Let's do the table. Join the organism is the same. Take the first row from the first table search for the corresponding close in the second table. On this slight, the albums a kind of Magic Quinto and sheer car heart attack do not have corresponding growth in the bond table, so these rows are not returned. In the query results, so we have lost some off the albums. The album table has five albums, but only to off them. I left in the query result. After joining Table Bond, the three rows were lost. Not is that, initially, some rose were missing in the bond table. But because of these, after the table join, some rows are missing from another table. The album table. So the summary. When we do a table join, we can have duplicate toes and missing crows. This might happen when a primary key and foreign cares are no defined. And if the data does not fall over the one tomainia relationship, so for the existing tables, how can we check whether or not the tables correctly reference each other? In other words, does the data full of the requirements of primary key and Foreign Key let's write test queries to verify the data that that's queer is at least it here on the slide? The first query is about primary key. It comes the number of rows and also the number of distant will use in the Bond Idea column . If those two numbers are the same, it means that, but I D column is unique. All the rallies are different. This query also checks that the column does not have now. Values is. If there was now, then the count distant would return a smaller number because it ignores now values. In our example, we have three and three, so the primary key requirements are met for the bond table. The second Queer is about foreign Kia for the album table that references the bond table. We count the number of rows in the album table that's don't have a much in the bond table. In the sub query, um, the condition where But I d is not now is added because off the note in behavior we discussed earlier in this case, we know that the bond table does not contain knows. We checked this in the first Queary. Nevertheless, we write this condition because those now Bailey's my appear in the future and the last condition in the square war Bond ideas now checks whether the column contains Indian alway layers in the album table now, formerly speaking the foreign key. Thus, alot now values, however, during a table, join those rows with now Willie's will be lost. This why we my want to include this condition in the test. Queary. In this example, this Queary returns number zero. It means that all albums have correct references to the bond table. All right, so the summary we have learned cases when the rose may be duplicated and the rose may be lost. And we discussed how to check the data if their primary key and foreign key are not defined . 23. Table Joins: LEFT OUTER JOINs.: the next topic is out of joint. The outer join is used when we have rose in one table that don't have corresponding close in another table. But now, instead of losing those hose, we want to keep those rose in the query results. Let's see an example. Same tables album bunt and imagine that the Bond table does not have a row for Bunt I. D. 1 92 However, we want all albums in the Query result. Even for the Bunt I g 1 92 we can use an outer join toe. Do this. The query is written like this in the from Close, instead, off enough join now We're right left, outer join. So how does the outer joint work is? Before we woke through the album table, Row by Row and Search for the Corresponding Crows, take the first album with the Black Album. It has a matching bond. We plays that role in the query result, the same way we did earlier for the inner join. Same for the second album, Master of Puppets. Now let's take the third album, a kind off magic. It doesn't have a corresponding crow in the bond table so the outer join does this. We place this album in the query result, and we put now values in the columns that should come from the bunt table. Same for the album's Quinn to and sheer heart attack. So this query returns five rose, the same as the number off albums. We did not have any duplicate rose, and we did not lose any Rose because we used an outer join in this example. We used outer join toe Compensate for the Mission grows in the bunt table. These missing grows are incorrect data. If all the data were correct, every album should have a matching bun throw. Incorrect data is not the only case when out of joint can be useful. Let's have a look at the now the case. Imagine that we want a least off music bonds and a list of albums for every bond. To get this information, we get the bonds and their albums. Now it's quite possible that some buns do not have any albums yet. Perhaps they released a couple of singles, but no albums. And if we want those bonds Toby included in the query result, we can use outer join for this purpose. We joined the Bonds table to the album table. Let's do the join. Let's walk for the first table bond in our example On this slight, the Beatles do not have any albums. We write this roto, the Query result and Putin al values into the album columns, then continue toe work for the bonds table for Metallica and Queen Buns. The albums are found. So for those rose, the Outer Join does the same thing as the inner journey. Just put the corresponding gross toe the query result. So in this example, we have all the music months in the query result, even those that do not have any albums. The Outer joins have a special behavior when it comes to using where conditions. Let's see an example. Imagine that in the previous example, we want only the album Master of Pipe. It's earlier We said that when we do and left outer, join all rose from the bonds table should they present in the query result. But let's look at this query result. The bonds of the Beatles and Quinn are not present in the result. Why did it happen? The accessories, because the wear condition is applied after the left out of a jury. In our example, they were condition effectively filtered out those two buns, the Beatles and the Queen. If you really want all the rose from the first table, then the filter in conditions should not be put in the wear clothes. It should be put in the own clothes and combined with other conditions using and operator. So now we do get all the buns. The Metallica with the album Master of Puppets because Onley that album is spits if it in the filter in condition and we have bonds of the Beatles and queen because it is an outer join. So let's remember this behavior off outer joints. Sometimes you may hear someone say, Oh, my outer join is not working now. This might happen because off the wear condition, which applies after the outer join and filters, some arose. All right, so we learned the left outer. Join 24. Table Joins: Syntax Variations: let's say a few words about different syntax on the left. We have a syntax that we used before in the from We have album in a joint bond, and then the German condition is in their own clothes. On the right, there is another syntax in the from we write only the table names separated by commas, and the joint condition is written in the wear clothes, combined with other filtering conditions using and operator, these two queries are effectively the same. They both returned the same results. Another thing about different syntax. Instead of inner Children, you can write only the word join. The queries on the left and on the right are the same. Instead of left out of June, you can simply right left join. The career is on the left and on the right are the same 25. Table Joins: RIGHT OUTER JOINs: When we write left outer join the first table is the primary table. All rose from the table should get in the query result. But why the first stable? Not the second table. This because off the left keyword left means that the primary table is written on the left after the out of joint keywords or before those keywords. You can also write another option, right outer join. In this case, the primary table would be the table toe, the right off the phrase outer join Or after that phrase. Remember when we talked about inner joins? We said that the order of tables in from close is not important. That's for the in their insurance Now for the out of joints there are there, have tables, is important. One off the tables is the primary table and the worst left. All right, are telling us which table is the primary table. Actually, it's possible to use only left outer join in all cases, in your right out of June can be really Britain is a left outer join simply by switching the tables. This way, the left outer join is used more often than right out of the jury on the slight. We have took wearies that produce the same result. In both cases, the primary table is the album table. All right, so we learned to the right outer join. 26. Table Joins: FULL OUTER JOINs: so we have left out of adjourn and right outer join. Finally, there is 1/3 option. A full outer. Join this when both stables are primary tables for the full out of join. The query result will contain rose from both tables, even those rows that do not have corresponding crows in another table. Let's see an example with urine tables, album and band toe find albums and the bonds that released those albums. Imagine that on this slide, the bond table does not have been tidy 1 92 And as you remember, in our example, the Beatles do not have any albums in the form. Close off the query. Well, right, full outer join. Let's build the query result as we did before we walked through the First Table, the album. For every album row, we search for the corresponding bond rose. If any corresponding gross are found, we put them in the result, the same as an inner joint would go in. Our example is the first and second trolls in the results. If the corresponding rose are not found, we put the album in the Result Place Inc nose in the bond columns as the left out of joint would do in our example is the 3rd 4th and fifth rose in the query result, and we do so for every roll off the first table. But we're not done yet. After we finished the work for the album table, we make one more step. We take the right table bunt and take those buns that do not have any albums. In our example, it's the Beatles. We placed that throw in the result, placing mouse in the album columns as the right out of joint. Who would do so? The query result contains rose from both tables. Even those rows that do not have corresponding Croce in another table for the full outer join. The order of tables in the firm close is not important. All right, so we learned to the full outer join. 27. Table Joins: SELF JOINs - joining a table to itself: one more topic about table joins is a self join. It's when a table is joined to itself. We will discuss when this type of June is used and how it works. Let's start with an example. Here is a music instrument stable, and, as the name suggests, it stores the music instruments. Now the instruments are not independent. They form a hierarchy off instruments as shown on the right side. Off the slide. The top level of Erica has two elements. Wind instruments and strings. It's the first and second row in the table music instrument for those two holes. The column parent idea is now because they do not have any parent elements. The wind instruments have to child elements. Woodwind and brass. It's the second level of hierarchy. These rows in the table have worry. One in parent I D Comb, which points toe the I D off the parent element wind instruments and finally would win, has three child elements flute, aids and game. Sure, in the parent idea column. They have value, too, which is the I D off woodwind. It's the third level of Eric you. As you can see, we have a hierarchy off instruments, the table music instrument references itself. The column parent idea contains various that exists in the I. D column off the same table. This approach can be used to store hierarchical information, for example, information about employees and their managers. Every employee has a manager and the manager of themselves also report toe higher level managers, all except one or several top level managers that do not have any higher manager. On the next slides, we will do some table joins, so let's remember this higher again. We have three levels. The upper level hostile elements, wind instruments and strings. The second level has three elements woodwind, brass and I only on harp and finally the third level also have three elements. Flute reads and the game short. Let's see the following example on the left. We have music instruments for every instrument. Let's find its parent instrument not only parent idea but also the parent name. The desired result is shown on the right side of the slide. How can we get a result like this? To get this result, we can do a self join during the table music instrument to itself. The query is very similar to joining two tables together. Imagine that we have two tables, one on the left, another on the right, in the select query in the from clause with specify music instrument with one Ellis a and then the same table, but with another alias Be so how does the table during work? It works exactly the same as if the tables were two different tables. We walk through the first table, take the first role and search for the corresponding gross. In the second table, he found right, the much in gross toe. The queer result in our example. Let's work for the left table. The first row one Wind Instruments has an AL value in parent I. D. Because it's on the top level of the hurricane without any parent instrument since parent ideas. Now for this. So there is no corresponding crow in the second table, but luckily we have a left outer join because off the out of joint well, right this roto. The query result with a now failure in the parent's name called Same for the second row, 46 strings. It also doesn't have a parent row. We write the proto the query result with a nav alia in the parent's name column. We continue working for the left table. Next throw is 24 brass. It has parent I. D. One. Let's find the corresponding grow in the right table with I D. One is the road one Wind instruments. We found too much in gross. So we write them is one come by and throw in the query result and so one for every row in the music instrument table. We search for the parent row in the same table now, not just the following, when we find a pair intro in the right table. Logically, we expect to find only one role, not many rows. That's because the column idea is unique in the music instrument table. It's a primary key off the table. For every given idea value, there is only one hero, and the parent I D is the foreign key that references the I. D column in the same table. So when we joined the music instrument table to itself, it works the same as if they were two different tables and those tables are in the main it one relationship. Because of that, we did not have any duplicate rose in the query result. The result contains the same number off rose as we have in the music instrument table. So no duplicates. And also we did not lose any rose. There's because after left outer join, if we used an energon, we would lose the 1st 2 rows off the table that do not have corresponding parent rose. In the previous example for every instrument were found its parent, Thoreau. Since the hierarchy has several levels, we can make one most step along the hierarchy. Find a grand parent row as well. To do this, we can do an additional table Jurin in our example. For every music instrument, let's find its prayer and throw columns. Parent idea in parent name and also the Grand Parent Row comes Gap, A, I, G and Gapen. Name the world Grand Parent is too long to fit the slight. So we use Gap, where for grandparent's let's right the query. Now it has two left out of the joints. The first outer join finds the parent row, as we did before. The second daughter June finds the gun parent role, not just the during condition on c dot I Dy equals b dot parent idea references that they will be, which is the parent table. So we have three. Ellis is here a for the table itself. B for the parent table and see for the grand parent table. Let's work for the table. The 1st 2 rows do not have parent rose, so they also don't have grandparents. Rose dessert. The fourth and fifth rose have parent row, but they don't have a grandparent row. This is because of those rose allocated on the second level of hierarchy and finally the Rose Flute Needs and Game shorn, which allocated on the third level of Hurricane. Those rules have both parents Rose and Grand Parent Rose. One more thing to notice. When we did table joins, we moved from the bottom of the hurricane toe, the top off the hurricane. This because the jury in condition was written is be dot i dy equals a dot parent idea. So for every role from the a table, we take the parent idea calm and then we search for the corresponding where anthro. So we search for the parent rose. Another option is to move down the hurricane from the top to the bottom. So for every role, instead of searching for the present rose now we will search for the child grows. Will I need this when we want to get the following result for a given music instrument, get all its child instruments so again the table music instrument. And let's take only upper level elements wind instruments and strings. We can do this by specifying a filter in condition where anti D is no. Let's find the child elements They went instruments, role crests to child rose, woodwind and brass. The strings row because once altro I only on harp. So when we stake one step down the hurricane, we have three rows in the query results. How can we write a queria to get this result again? Imagine that we join two tables, one table music instrument and another table same table, but with a different aliens. Now the query. It's very similar to the query we rode before. The main difference is how we write the joint condition. Now the condition is be dot where and I d equals a dot idea. It means that for each row in Table A, we find the rose that reference it by the parent i d. Call. So those other child throws. We search for the child holes. When we search for the child grows any given row in the A table can have several child throws. So now the jury is not for remaining toe. One relationship is we did before. Now it is one to many relationship. One row from the left table can have multiple rows in the right table, and some elements may not have any child elements at all. So we still need left out of join to ensure we put all the rose from the left table toe. The query result. So when our example we start with upper level elements were right, select something from music instruments as a and the way condition. A parent idea is no to get only upper level roles. Actually, we could start from any level. We just chose an upper level for simplicity. On the upper level, we have to rose. So we take those throws and we do a join toe, find their child hose. All right. When we take one step down the hurricane, we get three rows in the query results. We can do a second step down the hierarchy. Let's take the result off the previous step on which we found to the child holes. This result is on the top left of the slide and for every row off these data said, Let's find their child Rose. These roads will be the grandchild rose for the regional table for the first room. For us we don't have child hose, so we're right. This row with now will use in the grandchild idea and grandchild Name Colmes for the second throw Woodwind. We found three child hose. So we write three rows in the query result for the Sandro I only on harp we did not find Child Rose Well, right, this role and place. Now in the Grand Chel columns, the query is written like this. Now we have two left out of joins. So with urine, three music instrument tables with different aliases and we give column different names so that child combs are named as child idea and child name. And the next level down the curriculum is gun, child idea and gone shelled name. All right, so we learned how to join a table to itself. It is very similar to joining two different tables, and when we talk about hierarchy, we can move in two different directions, from the top to the bottom and from the bottom to the top. 28. Table Joins: CROSS JOIN - cartesian join: and finally another type of table joins is across join. It is also called a cactus. Asian join is when we do specify the two tables, but we don't specify adjoin condition. In this case, every row from the first table is joined toe every row from the second table. In our example, we have five rose in the first table and three rose in the second table, so the cross jin will give us 15 rose five most deployed by three In the query result, The origin Rectangular shows how the first album, a Row, is joined to all the bank rose. Then the second album, Rome, is joined to all the bonds rose and so one These multiplication of rose is relatively rare . It may be useful, for example, to generate some test data. Another case when the Cartesian join my eye Pierre. I remember when we talked about two types of syntax. Four table joins. The second syntax is when the tables are coma, separated in the from close, and the joint condition is specific in the wear clothes. Now for the syntax. If we don't specify the joint condition for any reason, then we get across Jurin. Every oh from the first table is joined toe every row from the second table. This case is often a mistake in the query, when we simply forgot to write a jury in condition it may happen. When we joined several tables together and for one off the tables, we forgot to write the jury in condition. So if you see that a queer A returns an unexpectedly huge number of rows, you might want to check of the joint conditions. All right, so this is an end of section about table joins we learned in the inner Join an outer, join a self join and the cars Jane. 29. Set Operators: UNION ALL and UNION: The next section is about set operators. We will talk about union except and Intersect operators and how they deal with Duplicate Rose. Let's start with union all. It's a union Afro sets. Imagine that we have two tables album and album archive. Earlier we did table joins toe find corresponding crows. This time it's something different here. We select rose from both tables and simply put all those rows into the query result. That's what union old does. We write one select, then union all and then another. Select the query result. Contains throws from both select. The first and second select may have different column names. They only think that matters is that the second select should have the same number of columns and the data types of which call, um, shoot much with the first select. Otherwise, the database will return an error apart, found that both selects may contain anything any columns, expressions were closes and so on. Like any other select one more thing. If the first and the second select contained the same rose, all those rows will be returned by the query. In other words, the union no does not eliminate duplicate rose in our example the album Quinto is present in both select When we do a union, all this road is present twice in the query results. If we want to eliminate duplicate Rose, we can do this by specifying key union instead of union, all for the Union. In our example the album Quinto will be returned on Lee once in the result. All right, so we learned the union? No, in the union operators, they do a union off rose. 30. Set Operators: EXCEPT ALL and EXCEPT: the next separator his union. No, it's when one set off Rose is subtracted from another set of rose in some that the basis the separator is also called minus except all takes. The first queer knows, and from them it takes away the second query hose. In our example, the album Quinto is present in both selects, so they accept all will return All Rose from the first Queer except the Quinto album, which was removed. Let's carefully say how except Operator deals with Duplicate Rose to illustrate this. Imagine that the table album 1st 3 rows for Quinto as we discussed before, except all takes all the first select rose and takes away from them. The second select Rose in this example. We have three rows, and we take away one role. The result is Turow's So for Quinto that to rose stay in the result. Now let's look at the except without all keywords. Remember that for union they like afterward, all means that we eliminate duplicate is se think Here we eliminate duplicate rose in the first select and then take away the second select a rolls in our example, three rows for Queen to will not be present in the result. There's because first, the three rows will become one row after elimination of duplicate rose and then that one row will be removed because of the same row exists in the second select. All right, so we learned the operator except all and accept, which subtracts one set off, rose from another set off rose. 31. Set Operators: INTERSECT ALL and INTERSECT: and finally the third operator is intersect. The intersection of For All Sets, The separator returns rose that are present in both select. In our example, it's only that row Quinto Onley The throw is returned by Intersect. All as we did before. Let's talk about duplicates to illustrate this. Let's imagine that the table album has three rows for queen, too. And the Second Table Album archive has two rows for Quinto. Operator Intersect. All takes only rose that are present in both selects in our case, three rows in first select and two rows in second select intersection off those two sets is store owes. So only those to us are returned by intersect All now, let's look at the Intersect without all cured here. As you can guess, the duplicates are eliminated. So the row quinto is returned Onley ones. All right, so we learned the operators intersect all and intersect. Would you do an intersection off? Rose 32. Functions and Operators: Overview: The next section is about functions and operators. We will see the most frequently used ones. Let's do it over every you. When we discussed the wear clothes in earlier lectures, we already discussed logical operators and or not and comparison operators listed here equal less than greater than and so on. On the next slice, we will also discuss the like operator mathematical operators plus minus multiplication division. No surprises here for the character data types. Is we discussed before? The concatenation operator is written as two vertical bars. Concatenation come by and still values into one failure and then some functions. Sequel language has many functions here. We will talk about the most frequently used ones. Details on the next slides. 33. Functions and Operators: Useful Numeric Functions and Operators: first the functions for the new American data types. The baby s function returns absolute values for the positive numbers. It returns to them as they are for negative numbers. It removes the minus sign so they become positive. On this slide, we have a select without any firm close. This select does not get rose from a table. It only returns one row with the literal you provide. Next faction is round. It returns the closest whole number. The number 2.8 is rounded up to three. The number 2.3 is rounded down to number two. The number 2.5 is in the middle. By agreement, it rounds up to number three some more functions to get whole numbers. The drunk function removes the fractional part. So drunk for so 0.8 is toe not three as the round would do and two more functions floor and ceiling. The floor It was the close. This whole number, which is less than a given value for posited numbers, is the same s trunk. However, for the negative numbers, the results are different. In this example, removing fractional part from minor stood out. Eight results in minor stool, but the closest whole number, which is less than minus, stood out. Eight is minus three, the same thing for the ceiling function. Either it does a whole number larger. Then they given value photo. Do it ate its three and four minus does not eight. It's minus two, and finally, the trunk can do one more thing. If you give it a circuit argument, it can remove only part off the fractional part up to the given number of digits. In our example, the value to in the second argument means these keep two digits after the decimal point, remove anything else so we get to 0.84. All right, so we learn some functions for the numeric data types. 34. Functions and Operators: Useful Character Functions and Operators: the next topic. ISS functions for character data type. First, let's talk about like operator. The separator takes a character of Alieu and compares it with the template in the template . There are two special symbols, percentage and underscore. The 1st 1% means any number of characters, including zero characters, and the 2nd 1 on the score means any character but exactly one character in our example. The condition is named like percentage off percentage. It means that we search for the rules with a name which contains the world off, surrounded by the blanks. And then they can have any number of characters before and after that off. In our example, we get to host who's name Much is the potter mustard off puppets and a kind of magic. The next function is sub string. It returns a sub string off a string. In the first example, we take the phrase this is a book and give its upstream cough it starting at Position 11 with four characters in length. So we get the word book. In the second example, we take a Substrate Inc starting at Position 22. She is. Our strength is shorter. We do not find a sub string, so the result is an empty string. Noticed that we did not get an AL value in this case, we get an empty will you the second function disposition. It searches for a sub string inside a string and if it finds one, then returns to the starting point. The position off a sub string. In our case, the World Book is found in the phrase This is a book and it is found starting at 11th position. If the sub strength is not found is in our second example. Here they're the function returns zero again. Notice that it is not. Now it's a zero, and finally, the character length. This function returns the number of characters in a given string a few more useful functions. The faction Treem removes some characters on the left and right off a string by default. It removes blanks, is in the first example, but you can also provide which characters to remove is in the second example. By default, we remove characters both from the beginning and from the end off the string. You can also remove characters only from the beginning were only from the end to do this, you can write either leading what rating, as shown in the third and fourth example or this slide. And finally the function slower and upper. They changed the given values either to the lower case by the lower function or toe the upper case by the upper function. All right, so we're learning some functions for the character data types. 35. Functions and Operators: Useful date/time Functions and Operators: Now let's talk about some functions for date, time and time stems. First of all, let's convert a character data type toe the date data type. In our example, 2018 12 31 is a character of Alieu. If we want to create a date out off it, we need to provide a four month where the year month and they are specific in the given string. We do this by specifying the for a month, year than month. Then they. In the second example, the date is provided in another layout we specify and other formal here so that the to date function will work correctly. Same for the time stem data type. Here we give a format for the date and for the time. For the time, we have abbreviations for hours, minutes and seconds. There are also some other formatting options here recover only the most frequently used ones. The next function is to char. This is the other way around, get a date failure and converted to a character data type Here. The first argument is the date, and the second argument say's how we want this drink, though, look like the first example year than month, and then the day. The second example is they month Year much is that in the second example, the first argument is already in the date data type and the standard literal for the data data type is provided, like this year, then month. Then they here. We take this literal, um, and converted to a character data type with a given former. Sometimes you way wants to get current date, current time and current times them. These functions are provided here, and finally, one more function extract returns different bars off the date or time extract the year from a given date. This function returns a year off that date. Same for a month and a day. The same function is applicable toe time and time stem There you can also extract our minutes and seconds. All right, so we learned some useful functions and operators for different data types 36. INSERT - adding new rows: The next section is about modifying data. The comments are. Insert for a Dink Euros update for changing existing gross and delete for deleting existing crows. Let's start with insert. Here we have a create table. Command toe creates the bond table after the table has created. It is empty. It does not have any rose in it. To add new rose, we can use the insert command if the table already has some rose. The insert comment does not change existing crows. It only adds new rose insert can come in different forms. The first form is inserted values. It is adding one role we're right insert into then the target table bunt. Then we give the list off Colmes, then values and then the values for the neuro we want to insert. In our example, we have three insert valleys come ons. Each of them is, I think, one role. Socially Commons ad free roast to the table. There are different options for the syntax. The first option is when the inserts specific is all the columns off a table. Our table has four columns, and we have all those combs in the insert and the other option is to populate only some off the columns. In this example, we don't have a equipment column in the list. In this case, the command will insert an Al Rilya in tow. That column. And finally the third option is to go without any column. At least arrow. It means that the values close should contain all the values for all the columns. Okay, so this was an insert Values, I think One row toe the table, Another flavor off a dink neuros is insert select. Here. The data is provided by the select and the result rose are added to the tagine table. This way we can take data from one of several tables, maybe make some transformations and then inserts the data into another table. The third option is loading data from files. Ah, when we talked before about inserts, the syntax is the same in most that I braises. However, here when we talk about lording data from files, the syntax is different in different databases in post Gary Squeal the command tous Korpi from we specify the table name and the file with data. You might remember this command. We used it to insert data in our tables, Fulop exercises and yet another way. Toe a data to the table. The common create table s select this command craze stable and inserts the data provided by the select queer here the select defiance, the number of columns, their names and data types. All right, so we learned the insert. Come on. And several ways to add Rose toe the table. 37. UPDATE - modifying existing rows: the next common is update. This command changes the existing grows. It does not add any in euros. This index is the following up date. Then the table name then set and what the changes are. Coolum is given in you failure, then aware. And we say which rose should be changed. In our example, there is only one Roe that satisfies the wear condition Onley that Roe will be modified by the update. The equipment cone will begin a new value rock band. Now let's careful. Let's say the following. Even though the update come on does not delete any rose, it can effectively delete information in the existing crows. Imagine that we were right there following couple date, we put an empty will you in the name column and since we don't have aware condition here, this update will be applied toe all rose off the bunt table. So after executing this command, we will lose all the information in the name column. So let's remember that we should be careful with updates now. In the previous example, we provided new Willers explicitly by using collaterals. Another option is to get new rallies from another table. Imagine that we have a table bunt old which has music buns with a populated name column, and we want to copy of those values into the bond table. This comment is written like this update, then the tagine table, and here we give it an alias. This alias will be used later when we joined toe tables together. Then set name equals Tito dot name where Tito is an alias for the second table bunt old. The stable is specific in the next line in the from close and finally in the way condition . We specify the condition tojoin tables that it works like this. It works for the tragic table bond and for every rule we search for the correspondent grow in the bond old table using the joint condition specified in the wear clothes. If the corresponding crow is found for a given row, then we use it to update the given role in the tagine table. If the corresponding CO is not found for a given row, then that given row is not updated. All right, so we learned the update Command 38. DELETE - deleting rows: and finally this served command in this section is the delete Command. This command deletes existing crows from a table I the all rose or only particular rose. That's just why aware condition in this example, we delete a row with the name Metallica. Now, before deleting any rose, you may want to make sure that the wear condition filters only the rose that you plan to delete. So it's a good idea to do the following before doing deal it do a select count us the risk with the same where condition. If this queer gives you the number you expect, then change. Select, delete and execute the command. Alright, so we finish the section about insert, update and delete. 39. Thank you! Course Summary. Congratulations!: This is a final section here we will do a course summary. So congratulations. You have finished the square Essential scores. Please accept my sincere congratulations. Here is the list of sections that you have learned. This course contains way six equal elements for everyday use. I also plan to do an advanced course with topics like views in dishes, partitions and some other topics. All right, so the course is finished again. Please accept my sincere congratulations. Thank you so much for your time and effort. I wish you all the best and hope to meet you again in other courses.