98-381 Microsoft Introduction to Python – Beyond MTA 98-381 Exam: From Python to SQL – Using Databases to Store Data Part 2

15. Delete Data with Delete Statements

And we will go on with the delete query. So I’ll make a new query editor and delete from students where name is equal to Chris. So again, I right-click “open table.” Here are the records. I will delete the record where the name is equal to Chris. So I will execute this query. And if I’m going to open it again, the table here is there, but it is cast. So, please, refresh.

And here it is. This is the only team present. So if you want to delete all the records from the students table, you just do not need to use the workloads, and the Delete query will delete all the records. So simply delete from students. Delete from students is executed. And as you can see, this is a cast refresh. Nothing is here. This was my previous condition for two students, and I’ll use student two for the next example. So the delete is really very easy to use. If something happens, remove the conditions from the table name.

16. Like Operators in Where Clause

So let’s see how the SQL looks up close. I will create a new query right here. The SQL-like operator “close” is used to compare a value to other values that are similar using Wildcard operators. So there are two wildcards used in conjunction with the like operator. The first is that I’d like to choose students from I’m sorry; I should always say what to select from students to now select (asterisk). So, if I’m going to say that name is synonymous with something, it should be either Chris Mall or team. If I would like to fetch some records from this table, I can say that I would like to use the percentage.

And the percentage means that I would like to tell all the students that there are three letters (Ma) and some other letters before and some other letters after this M in the name. So Chrismal has mal in the name, some other characters before it, and some other characters after this one. So I don’t have to use the exact value, but I’d like to find any values that have mal in any position. So I will use this one. And there is an error. And the error is that I used like instead of equal. So I’ll use that the name should be something like mal. And here it is the same. You can do it if I use, for example, Chris. But if I would like to search for any values that start with Chris, I have to delete this percentage. So it will start with Chris, and any other characters will follow him. So it is okay. The result will be the same. So this is one great example.

17. Limit Data using LIMIT/ TOP/ ROWNUM in MYSQL/ MSSQL/ Oracle Databases

So I will go back to the insert statements. I will copy these two insert statements. I will create a new query editor, I will paste it, I will change students to “students too,” and I will use different names. I will add to numbers three and four. I will change Chris Mall from George’s team to Michael’s. I will run it. So, if I’m going to open two students, I’ll add two more now that I have four records. I’ll add Maria to five and six, and Jorophy to twelve and 610 and four. I will also run these two statements. So if I would like now to use the top-level selector to fetch a top-level n number or x percent of records from a table, So it is important to know that all the databases do not support the top-down closure. For example, MySQL supports the limit close to fetch a limited number of records, while Oracle uses the ronum command to fetch a limited number of records.

But it follows exactly the same logic. Exactly the same logic So I could, for example, select the top three asterisks from students if you’re going to use a Microsoft SQL Server, but here on a MySQL server, I could say, “Select an asterisk from students, limiting it to three.” And if you select this one, then in students, I do not have records, but in students too, I will get only the top three results. So if you’re going to use midscale, this is the syntax. If you’re going to use Oracle, it is almost the same. Choose an asterisk from students whose rowNUM is less than or equal to three. So this is for Oracle, and the result will be exactly the same. You will get only the top three records from the table. Students two. So, if you need a specific number of records from a table, use Limit from SQL, Top for Microsoft SQL Server, or Ronan for Oracle Server. Oracle.

18. Using Order-By Clause to Order Data

So we will go on and see the order by clause that is used to sort the data in ascending or descending order based on one or more columns. As a result, some databases cut the query results in the sending order. By default. But if you would like to use it, I would create a new query editor. I could say, “Select all students from students.” I will execute this query, but if I would like to sort the results by AIDS, Then I could say “order by AIDS.” And then you will have all the students in an ascending order according to their HIV status. So you can use more than one column in the order of byte loss.

But make sure that whatever column you are using to shorten that column should be in the column list. So order by AIDS. Sort by AIDS and name. So both Team and Michael have the same AIDS, but Michael should be first because his name starts with M. Well, the team starts with T. So if I’m going to run this query, then the result changed. So this was a good example to show you how you can use the order by selecting a column list from a table. Multiple conditions should be ordered in ascending order by column name (one column name, two column names).

19. Using Group-By Clause to Group Data Based on Columns

And finally, we will see the group up close. So the SQL group by is used in collaboration with the select statement to arrange identical data into groups. As a result, this group generally follows. When you use the word close in a select statement, it comes before the order by close. As an example, we could ask students to name some AIDS. I apologies for the parentheses.

From students to group by group by name. So if I’m going to select the statement, I’ll make an appointment with the AIDS organization. So this is similar to the select asterisk that students use. But imagine that I could also have I will go back and add these two values again. I will change only the seven and the eight. So, if I group by name, that means I’ll have two students named Maria who are twelve years old. So, if I add up the ages, Then I will have Maria and 24. when I’m going to use this group by name. This is because I have two students with the name Maria and will have two students with the name Dorothy.

So if I’m going back, I have only six records because they are grouped by name. And Maria has 24 right now because I have two Marias with 12 AIDS and I have two Dorothy’s with 10 AIDS ten. So you can use it right here. If we would like to count the names, I will say that I have two Dorothys and two Marias right here. So it was grouped again by name, and I can see that I have two Dorothys, two Marias, one George, one Michael, one Tim, and, of course, one Chris Mall. So this was a good example to understand how you can group your results. As a result, the group must closely follow. By a certain date, the work conditions must have preceded the order. So you can say right here, group by name, order by name. Sorry. by name. And here it is. Okay, I used some of your font’s beginning. If you would like to change the font actually in Escalade, go to Tools Preferences and just change here if you would like the size to be bigger.

20. Using Distinct Clause To Return only Unique Values

This was the group by a long shot, and I’ll continue now and show you the distinct keyword. I’ll continue, create a new query editor, and write right here that I could have selected asterisks from students so that I could have all of student two’s names.

But here I have, as I said before, two Marias and two Dorothys. So, if I wanted a list of names, I could also choose names from the students. And if I would like to have only distinct records to eliminate the duplicate records, then I will use the keyword distinct. So I would like to have only the records that are not duplicated. So the SQL distinct keyword is used in conjunction with the select statement to eliminate all duplicate records and fetch only unique records. So, for example, you could have multiple duplicate records in a table, as shown here. And while feting such records, it makes more sense to fetch only the unique records instead of feting duplicate records. Selectdistinct column name one or column name two, columnname n from table name where conditions are true is the basic syntax of the distinct keyword. So this was a good example of how we can use distinct to ensure that we don’t have any duplicate entries.

21. TRUNCATE TABLE Command to Delete the Data Inside a Table and Conclusions

And finally, we will see how we can use some commands to delete complete data from an existing table. I will create a new query editor, and either you can say delete from student two, and this will delete all the records from student two as we did before, or you can say that you would like to use transform Please transcribe table students two and then delete all the records. You delete complete data from an existing table. So you can also use drop table to delete a complete table. However, if you only want to remove the data from the database and not the entire table structure, and you want to recreate this table again if you want to store some data, So actually, you should truncate the table by students two, and I will select the statement; click right here. One success. And two things are clear now, students. I do not have data for students 2 and 3. The student’s two tables are truncated, and the output from the select statement is totally null.

I have an empty set, so if I’m going to use select asterisks from students too, I do not have any data. So I think this was a good example for the traced table command to use to delete complete data from an existing table. And with this statement and this command, I think you have an overview of the basic commands of SQL. So there are some more advanced commands that we will add in the following lectures in the future.

But for now, I think that you have a good overview of the basic logic and the basic concepts of SQL. So I am really happy that we saw together all these commands, all the statements, and the logic of SQL. And these lessons are actually to help you understand the basic concepts related to all SQL languages for Microsoft, SQL for MySQL for Oracle, and how You can use SQL to connect it with your application so that you can store data, manipulate data, and do some queries to fetch some results. So I would be really happy to ask if something is not clear to you, and I would be really happy to help you clarify these complex things. So I’m looking forward to seeing you in the next lesson, and I’m waiting for your messages. Bye-bye for now.