LPI 102-400 – 105.3 SQL data management

1. Objectives

Okay, so in this section we are starting a new subject, and it is SQL data management. And on the objectives table, it is marked as 105.3. So we’re still in the 105th section. We are in the third subsection of it, and this will be the last one in it. And then we’ll start with 106 after this. So data management They don’t expect you to be a database administrator when we discuss epic-related topics. That’s something you might want to pursue, but that’s different. But as far as the exam is concerned, there are some very basic commands that begin to run, and they just want you to be familiar with those.

Weightage wise, you’re probably going to get about two questions as per the objectives right now; the description says that candidates should be able to query databases and manipulate data using basic SQL commands. This objective includes performing queries involving the joining of two tables or sub selecting. Key knowledge areas that are going to be covered include the use of basic SQL commands, basic data manipulation, and a partial list of file terms and utilities. Insert, update, select, and deletefrom where group by order by and join will be used.

2. Creating our first database

We’re going to start creating our first database, MySQL. And then we’ll say “Create a database” and then the database name. So I want to call my database events. Semicolon. One row has been created, and I’m going to use it, so let’s do a show database. And as you can see, I have six databases altogether, with Events as one of them. So then I’m going to type in “use events.” And the database has been changed. So we’ve been put into the events database.

Okay, now run a command called Show Tables. Just as we did with “Show databases first,” Although this is not going to show anything because we have not created any tables yet, So in our new database called Events, we’re going to create a new table called Potluck. Okay, so create table Pock luck and then, in brackets, type in ID, which is an integer not null, primary key auto increment, then name the variable character, and we’ll give it a field of 20. Then food is also a variable in characters. We’ll give it a field of 30, then confirm the guest’s character. We’ll give it a Y or an N to indicate yes or no, and then sign up for the date.

And then we’re going to end the book. Okay, so whatever the table has done so far, it has created a table called Potluck. We have set up five columns in the table within the directory Events: ID, name, food, confirmed, and signup date. The ID column has a command int not null, primary key auto increment, which automatically numbers each row. The name column has been limited by the VA character command to be under 20 characters long. The food column designates the food each person will bring. The VAR character limit requires that text be no longer than 30 characters. The confirmed column records whether the person has RSVP’d VP with the letter yes or no. The date column will show when they sign up for the event. MySQL requires the date to be formatted as year, month, and day.

3. database manipulation

Okay, now we’re going to run the show tables command to make sure that our tables are saved. And we do see potluck in there. There’s one row, and another command is describe. And we’re going to use Potluck in there, which will show you how it looks so far. Okay, now let’s try to add something to our MySQL table. So the command is to insert into parentheses ID, name, food, confirmed, signup, date, and then values that are going to be null. Comma. John, it’s one of our guests. And what he’s bringing in is a casserole. The comma is confirmed. Comma. And then 2018-12-15 is the date in parentheses, and we’re going to put our semicolon there. Okay. And then after Y, it’s supposed to be a comma, but I put a period instead. So it’s 2018 12:15, code, parentheses, and a semicolon.

And now our row has been created, and I’m going to go ahead and add three more rows after that to make our table look a little bit more interesting. Okay, so I have added three more entries, three more guests, and the dishes they’re bringing, if they have confirmed or not, and the date that they have confirmed on. So if I were to do a show table at this point, it would be a show potluck. And if I say, “Okay, at this point, if we were to use a select statement,” “select star from star” means everything from the public and shows the current status of each of the things they are bringing and what they have signed up for. So one of our guests, Sandy, has just confirmed, so we can update her. So we’re going to say “update set confirmed equals Y” for yes, where potluck name equals Andy. Our single row has now been modified. So, if you runelect again, this time choose asterisk from poplook. As you can see, everybody has confirmed now, including Sandy.

4. Install Mariadb

Okay, so before we begin any kind of database manipulation, we have to instal MariaDB on our system. And since this is a Santos or Red Hat-based system, I’m going to be using Yum to instal MariaDB, and I’m also going to instal a test database using MariaDB Test. Okay, it shouldn’t take that long. Okay, we are done installing it. And as you can see, there are several references to Perl. Pearl is an important part of MariaDB. so that gets installed in the process if it wasn’t already installed on your system. And so we are done with the installation. The next thing to do is run systemctl start. You have to start the service because the installation doesn’t automatically start it. So Maria DB Okay. Started. Now a couple more steps, and that will be system CTL status for MariaDB.

And that shows that yes, it is running and enabled. One more thing I want to do is make sure that it does get started at boot as well. So I’ll enable it so that it can survive a reboot and you don’t have to restart it every time. So that’s done. Okay, now we’re going to instal MySql or MySQL in the secure installation. MySQL underscore secure underscore installation is the command. And it’s going to ask for the root password. But if you note here that you just installed MariaDB and haven’t set the root password, the password will be blank. So I’m going to hit enter, and the current password for root is enter. And you want to change the root password? I want to say yes. And then I’ll put in the new password. Remove anonymous users. Yes, I do want to remove anonymous users because I’ll be doing it as the root user. Whatever I need to do disallow remote login. Yes, I want to disallow it and remove the test database. No, I don’t want to remove the test database. I want to use it to reload the privileges database now. Yes. And now MariaDB is successfully installed.

5. Creating videos db

Now that our MariaDB database is installed, we can type MySQL. Remember, MariaDB is a fork of MySQL, so they are both sort of the same. So you start MariaDB with MySQL, and I want to start it as root. And I wanted to prompt you for a password. So dash and the password, and currently we are logged in to MariaDB, and the first thing I can do to get started is type in “Help.” And this is our help menu. We can get assistance with the things we can write right away from the prompt.

And then one of the very important commands that you can use or that I would like to use is “Show databases,” and make sure to always use a semicolon at the end of any SQL commands. And these are, by default, the current databases. Remember, Test Database is one of the databases that we install by choice when you are doing the installation. So that’s our test, DB. And how do you exit out of here? You type “exit” and make sure to type “semicolon,” otherwise you won’t be able to exit. Okay, now we are exited out and back to our Linux prompt. From here, you want to get back in, so you type MySQL U root p, you put in your root password, and you’re back in business. Now let’s create a new database, and we’re going to start with the Create Databases database, and let’s call it Videos.

That’s what our database is going to be called, and it says “Query.” Okay, one row was affected. That means our database has been created. And how do we check it? We do “Show database databases” and then a colon or semicolon. And as you can see at the end, we have our video database that has just been created. Now we’re going to create our first database. We’re going to use our first database, I should say. So in order to use it, you have to first mention it to MariaDB, which is the database you’re going to use. So in my case, I want to use the video database that I just created. So that way, MariaDB knows that this is the database I’m going to currently work on, not the test database or any other database. The database changed, and now I’m going to create my first database. Maria DB, by the way, is not case sensitive.

So if you want to write “create all lowercase” instead, you can also write “create all uppercase.” But it’s just habit. I would like to do it in lowercase, but it’s totally your choice. Create table. I’m going to call it Videos and then the title of my video, and I’m going to call it Variable Character, and I’m going to sign it a field off, let’s say 25, then a comma, then Actor, and then again Variable Character. And this time I’ll sign it with “25” as the field year the video or movie was created. And I’m going to sign it as an integer, or int. Then registration. And this is also going to be an integer. And then I’m going to end it with a semicolon. You have an error in your skills. Check the manual that corresponds to the user near you. Call it at line one. Okay, I see what the problem is. And you may have noticed as well that I didn’t close the parentheses. Okay. And now my table has been created.

6. Inserting data

Okay, now that our table has been created, we’re going to insert data into that table. So the command for that is obviously “insert into videos,” which is the name of our database. And then we’re going to put in registration, title, actor, and year values. R one.

Call it Black Panther and put an end to the code comma. Now we’re going to insert them into our videos. I have already done it, but I just wanted to show you again. Enter the following information into the video’s registration: title, actor, and year. And then values are number one. The Black Panther movie actor is Chadwick Bozeman, and the year is 2018. So I already have this created. So that’s why it’s giving me the error that it’s already there. But this is how you actually insert a value into your database. Also, I have noticed that I misspelt something here. They should be inserted. Okay.

Now it’s corrected itself. So just like the change I made from time to time, I changed the name to the Black Panther instead of just the Black Panther or just Black Panther. So now we have inserted Ali into our video database. Okay. Now we’ll insert one more row, which will be inserting into videos and registration, title actor, and year. Then values. This time is going to be number two. The movie is Titanic. The actor is Leonardo DiCaprio. I hope I miss I spelled him correctly. And the years were 1990-1997. Okay. And semicolon. Okay. Titanic. What did I misspell here? doesn’t look like anything. Unknown column. Titanic. So the only thing that was missing were the codes, the single codes. As soon as I put that in, then it went through. Okay, you must include the single coatin when entering the values.