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

1. Introduction to SQL

SQL structured query language in Access or MySQL MariaDB in any database because you will learn how to use this structured query language that has very minor device differences and the basic logic is identical. So SQL is a database computer language that is designed for the retrieval and management of data in a relational database. So SQL is actually a database computer language that stands for “structured query language.” But the most important thing is that with these lectures, you will understand the basics as well as the advanced concepts related to all the SQL languages. So it is very important because there are a lot of SQL databases.

MySQL, also known as MariaDB, is the new name for MySQL. We have SQL Server. This is actually from Microsoft. I’m sorry for my old letters, Microsoft. Okay, I think it is very bad. Okay, don’t leave me a one-star review for this, please. Okay, we have Oracle. We have less popular languages like Case, Postgres Informatics, and MS Access. That is actually something like Visual Basic for the databases. But it is still very popular because you can easily create and manipulate its data with Microsoft Access. And this is the reason that Microsoft continues to improve this product. So these are the most popular relational database management systems. So RDBMS stands for relational database management. I think my two-year-old girl can do much better than me. Let’s start with database management systems, and RDBMS is the foundation for SQL. So all modern database systems like Microsoft SQL Server, IBM MDB 2, Oracle, or MySQL use this SQL.

2. Why SQL is so important?

So the question is: why SQL? Okay, it is very simple. Because SQL is a structured query language, which is a computer language for storing, manipulating, and retrieving data stored in their national database, SQL is the standard language for all these systems. But SQL is really popular because it offers specific advantages and allows users to access data in their relational database management systems. It also allows users to define the data in the database, manipulate the data, and create and drop database tables. We will see all these things, not theoretically, but the most important is that the most popular web and mobile apps are based on a database. So even Facebook, Twitter, or other huge websites store the data from all these users in databases. And these are the specific databases that we will look at together. And all these databases, as I have already told you a lot of times, are based on these SQLs.

3. Setting up XAMPP, Apache, MySQL Database Server and SQLYOG as a Database Client

Hey guys. So in order to instal your database, I recommend you start with Samp. Samp is actually a free and open source cross-platform web server solution packets developed by Apache Friends, consisting mainly of the Apache Httpserver so that you can have a web server, a MariaDB or MySQL database, and interpreters for scripts written in PHP and Python programming. So the most important thing is that we can use MariaDB or MySQL.

So you can go to the resources of this lecture to download Samp, or you can use Google and, of course, download the latest table release for Windows, Linux, or Mac. So after downloading and installing Apache, that is actually very simple. To do it, you will have to download an SQL client. So there is a default SQL client, which is the MySQL client, which is named PHP Madman, but actually I would like to use a standalone client that you can also instal on your computer, and its name is SQL Yog Community Edition. So you can use Google again to download SQLYog Community Edition, or you can go to the resources of this lecture and download Slog Community Edition. This is all that you will need—nothing more, nothing less. So, after downloading, you can go to the Examp Setup Wizard and select all of this to have an Apache Web Server and a MySQL Server. This is our database system, FileZilla FTP Server, so if you want to create a server so that your computer actually becomes a server, you can also have a FileZilla FTP Server so that someone can transfer files from your computer to your server using a FileZilla FTP client.

Also, you can use the Mercury Email Server if you would like to send emails. So imagine web pages where you can have a sign-up process, and if someone forgets his password, he can click “forgot my password,” and with Mercury Mail Server, you can send him an email. So you will need an Apache Web Server for your website and a MySQL database to store the data. FileZilla FTP Server: If you would like to remotely update some files from your server from your website, Mercury Mail Server shows that you will be able to send emails. Tomcat is actually available if you would like to use Java, but we will not use TomKat. We will not use Java to access our MySQL database. PHP is the default programming language because it is associated with Apache or Pearl and the client is PHP. My administrator accessed your MyScale Webalizer and sent a bogus email; you’re up next. This is the default exam folder; I have already installed exams, so you can continue if you want to learn more, or deselect it and set up is now ready to begin next and finish, nothing more, and you will also be ready for the SQL Yog. You can download the 64-bit version. It is actually pretty small. only six megabytes. so you can select a language. English, of course. And then this is the setup wizard. I ou can select his is the default install. and you are ready. Nothing special. Right here.

4. Starting Database Services

Hey guys. So after installing Samp, you will have this Samp control panel. So actually, here is where you can start and stop the different modules of your server. For example, you can start the Apache webserver, which means that you can serve webpages from your computer through your server. We will need to start the MySQL server so we can use the database system on our computer. So in order to use MySQL with Maria DB, it’s the same.

You have to start this MySQL server. And then an instance will begin. This is actually the process ID. You will see a number right here and the default port of MySQL. That is 3306. so you should start it. And you don’t need to start FileZilla, Mercury, Tomcat, or something else. so I stop it right now. Here is the log of the server status change status change and you should start it. As a result, I’m attempting to launch the MySQL application chain. And here is a random process. ID. But this means that this process is actually running. And here is the default port for MySQL: 3306. So I’m looking forward to seeing you in the next lesson.

5. Creating our 1st Database

So after installing SQL Yoga, you will have an instance right here. I have a lot of demo databases. Actually, we will create a new one. So this is a connection to mysqlhostusername, which is usually root. MySQL host Andres is localhost, and you do not have a password. If you didn’t set a password during the installation process, I didn’t set something. So the password is empty. because this is just a test version. If you would like to have something more professional, of course you have to consider the safety of your web application. So you should have a password while you set up your MySQL database. This is the default port of your database server, 330 six.

If you do not want to access a specific database, leave it blank and test the connection. The MySQL version is 10.1.31. This is Amari DB, the new name of MySQL. So everything is okay to start right now. You should remember before starting that SQL is governed by a unique set of rules and guidelines. And this set of rules and guidelines is identical to that found in traditional programming languages such as C, C++, Python, C, Plus, Angular, PHP, and Java. Of course, in every language, there is something called syntax. So all the SQL statements start with these specific keywords that we will see together.

And the first, and most important, is that here you have a GUI. You can create a new database by right clicking on the local host and clicking “Create database.” Or you can use a query right here. You can write “Create Database” for my new test database. This is the name of my new database. And by clicking on this button, the query will be executed. So one row was affected, one query was executed, one success, zero errors, and zero warnings were issued. Execution time is really fast. So if you click right here to refresh the object browser, this is the object browser. You will find this in my new test database. And if you click right here to the plus side, you will have the tables, the views, stored procedures, functions, triggers, and events. So everything is empty. We don’t have tables yet, we don’t have views, and we don’t have store procedures. But we can start right now.

6. Dropping your 1st Database and Recreating it

If you prefer, you can also use queries to display information. For example, to show databases, you can click Enter. You can write multiple queries. So I can write here. So databases and I decide which query should be executed. And you can either use the “fifth” button or click right here. So here are all my databases right here. This is a query to display databases. So this is the first one to create the database. You can use another query to drop the database or delete your database.

So I would like to use Drop Database and the name of my database. If you want to delete an existing database, then the Drop database statement is exactly this one. So one query was executed, one was successful, and zero rows were affected by a refresh, okay? And if I click to show databases, as you can see, there is no new test database. So the question is: why? I can see it right here. Okay? because you refreshed Object Browser, and now it is gone. So right-click right here. Reload the Object Browser. I will create it again in my new test database. I will right-click right here. Refresh the object browser. Here it is. So be careful, because with this operation, deleting an existing database would result in the loss of all the information stored in the database. So make sure that you want to drop the database. Because if you didn’t make something like a backup or a backup plan, then it could not be restored.

7. Creating out First Tables in our Database

So let’s go on and create some tables. So, by right-clicking, going, and clicking to table, we can create some tables. So here it is again, an easy way to create a table. So we can name the table student here. I’m sorry, student, and then we will use an ID that will be an integer. So in the data type, you have to select int. So I would like to have a column named ID. So this will be something like 123456, and here the data type is integer. So then I would like to have the name of the student, and this one will be an varchar because it is the same as the string. So I would like to use the varchar variable characters, and I should use the length so that I can state that the length will be less than 30 characters. Furthermore, we can say that this should not be null.

So I cannot add something here without using the ID number and the name of the student. I would like to say to use the AIDS, and here the AIDS is also of type integer, not null, and then I would like to use the grade. The grade will again be an integer. Or you can use something like the degree right here and say that this will be a decimal with a specific length, or for now I will not use it. So, click here, and then click to delete the columns you want. So I will save it right now. table was successfully created successfully. So go to the table, right-click Openable, and here you will see the data. We do not have any data yet; we just created the table. Instead of creating the table this way, we can use an actual SQL statement so we can say “create table.” I’ll make a table for student two, then another, and then open parentheses and say that I’ll use an ID. It will be an integer that is not null. I’d like to have a name that will be vargar 30 and will not be null. If it is not null, it will be an integer; if it is an integer but not null, it may be null.

And also the degree if you would like to have a degree and use, for example, a decimal with, say, 18 with two digits, or you can say only this and you can say that this will be a primary key ID. This means that I could only have one ID per student, each with a unique ID. So ID number one will be assigned to a specific student, and I will not be able to use ID number one for another student. So this means that I could have a primary key. The primary key of this table is the ID, which is unique for each student. So this is a query, and you could execute the query. I have an error, and the error is right here. I didn’t use a comma. Right now, it’s okay. With a right-click refresh, I created table student number two. And students. And students. As you can see, this was the first query created. I would like to say that all the queries will be available in the resources of each lecture.

8. Dropping Tables in a Database

So this is the create table, and very, very easily, as we did with the database, we can use the drop table. So the Drop table is really easy because we just type “Drop table” and the name of the table. So, students two, drop table, table. And you have to select this statement and execute this query. As a result, I dropped Student Two. If we refresh the objects right now, we have only students. So if you try to do a select here, you will have only the student tables. This was the drop query and statement. And then we will use the insert query so that with the SQL insert statement, we can add new rows of data to a table in our database.

9. Insert Statements

So the basic syntax of insert is actually, “I will create a new query.” Right here is the general query. The general statement is “insert into the table name.” You should use the columns (column one, column two, and column three), and then you should use values and type the values of each column. So value one, value two, value three, et cetera. So this is the general SQL insert statement. So column one, column two, and column three are the names of the columns in the table into which we will insert the data. And you should not specify the column names in the SQL query if we’re going to add volume values for all the columns of the tables. So, if you want to use this to add values to all the columns, leave out the column names and just type the values. So we will go on with an example. We have it here on the table, students, and it doesn’t have any values.

I’ll try to write insert into students right now, and you could either use the ID name “AIDS grade,” or the four column names, which have a value of 1. Don’t forget, ID is an integer. The name is Chris Mull. The AIDS is 35, and the grade is okay. The AIDS score is 15 or 10, with a grade of 4. It’s the fourth grade in the primary school. So I will select this statement and execute the query for the rows affected. So, if we open the table again, right click on the table, we don’t see anything, but you should refresh the data right here and you will see that with the ID number one, the student’s name is Crystal, she is ten years old, and she is in grade four. so you can add another one. And as I said, if you’re going to add data to all columns, you can omit this one and type “insert” into the student values for name. For instance, 9th grade, nine years old, fourth grade. So I’m going to run the statement successfully again. So I will open the table and I will refresh the data right here, and you will see ID number one is Chris Mall, age ten, grade four. ID number two is the team’s second grade 3 student, age nine. So with these insert statements, you can insert data, fill in this table, and actually populate the data.

10. Create Table based on Another Table

And if you would like to create another table using the data from another table, you can actually create another table by saying “create table.” Students have two as Choose an asterisk from the students so that you can select the statement and create it. This statement tells your database that I would like to create another table of students too. It will have all the data. The asterisk denotes all of the student’s data. So we would like to create another table. I will refresh right-click. And where is the refresh? Right click. The update has arrived. Student one. Student two. Right click. When you open the table, you will notice that all of the data from the students table is populating and being copied to the students. So you can populate the data into Atable with this select statement over another table. So this was a good example to continue with the SQL select statement. That is used to fetch the data from a database table, which will return the data in the form of a result table. And these result tables are called result sets.

11. Select Statement

So the select statement’s basic syntax is as follows: select column 1, column 2, and column 3 from table name. So we’re going to say, “Select asterisks from students,” for example, from Montrose. And here are all the results. So if you would like to have only the names of the students, then you should write here the column name that you would like the results for and select the statement. Click Execute. And here are only the names of the students. You will like the names and the AIDS. Then write “AIDS” right here. Choose the name “AIDS” from among the students. You will also have the student’s names and AIDS status. So this is the fundamental select statement.

12. Where Clause

So this is the basic syntax of the select statement, but we can also use the where clause. So you can use a condition to select all of this one, column one, column two, column three, or whatever column you want from table name. So, for example, you could choose the name and AIDS from students where AIDS equals ten. And I’ll only have one student with AIDS here. So I select this query and I run this query, and you will see here in the result set that it will fetch the name and the AIDS fields from the students table for a student with AIDS 10. So it is important to know that when I would like to do a condition with an integer, it’s okay not to use brackets, but if you would like to create a condition that you will check if, for example, the name of the student is Chris.

If you only want to use the select name assistants from students, choose Levies. If you would like to use “select a name from students where name is equal to team,” then you should use brackets, either a single quote or a double quote. It’s the same, but you must use quotation marks. This is the most basic example of a workload. The workload is used to specify a very specific condition while fetching the data from a single table. If the given condition is satisfied, then and only then does it return a specific value from this table. So you should use this work to filter the records and fetch only the necessary ones. So the work close is used not only with this select statement, but also in the subsequent lectures when we use it in the update in the delete statement. Actually, the workload is the heart of the SQL statements because we can filter either inserting, updating, or deleting queries. So I’m looking forward to seeing you in the next lesson.

13. Operators in SQL Statements

So something that is really useful is that we cannot use only the equal sign. We cannot say, for example, that we will select the name AIDS from the student stable where AIDS is equal to ten, but we can say that it is greater than nine, so the result will be the same. But if I’m going to say greater than eight, give me the names of all the students and tell me that the AIDS rate among them is greater than eight, and I’ll have these two records back. So I can use greater or equal. I can also use less than eight, less than equal, or equal. I can also use the end and or operators to combine multiple conditions to narrow the data in an SQL statement. So, for example, I’d like to choose name and AIDS from students whose AIDS is greater than eight and whose name is Team.

So here is my result. So the AIDS should be greater than eight and the name should be “Team.” If I need to use fewer than eleven, I only have Team. But if I change the operator from AndTour and say, “Give me the name and age of students who have AIDS and whose AIDS score is less than 11, or whose name is Tim,” I’ll have all of these two records again because the AIDS score for Chrismal and Tim is less than eleven. So the end operator allows the existence of multiple conditions in an Escal statement, where “close” means we can combine a lot of conditions using the end operator, not only one. I can say, for example, that the team’s name is Team A and Grade B, and so on. I can have multiple end conditions. So, for the Escal statement to take action, all conditions separated by the end must be true. So the or operator is used again to combine multiple conditions in an SQL statement. But the main difference is that for an action to be taken by the SQL statement, only any one of the conditions separated by the or must be true. So if I’m going to use the or separator, either this condition or this condition should be true. As a result, this is a good example for the checker or operator.

14. Update Data using Update Queries

So we can go on with the update query, the SQL update query. I will create a new query editor right here. And the SQL update query is used to modify existing records in a table so that the workloads can be used again with the update query to update the selected rows. So for example, I will create an updated student set named “name equal to Chris,” where name is equal to Chris Mull. So I will change the name of the student who has the name Chris Mall, and I will set the new name to be Chris. So I selected this statement and clicked Execute query. One row is affected, and if I’m going to use the select asterisk from students to see all the records, I will see that the name is now Chris. This row is updated because this row is where the work condition is true.

The name was Chris Mal, and the new name is Chris. So the general update syntax is update table name set column name one to equal value one. Column name two must be equal to value two; column name three must be equal to value three; and finally, the conditions. So this is the general SQL statement. So if we would like to modify many column values in the students table, you do not need to use, for example, the word statement. For example, I can say to update the students and set the grade to five for all the students. So I do not need a “where” condition. So for all students, set the grade equal to 5. So I’ll select the statement and the two affected rows, and then execute it again; with a double click, you can select all the sentences, and the grade for all the records in this table is now five. So this was a good example to see the update.