PostgreSQL, PgAdmin, SQL and JDBC [Combo!] | Karthikeya T | Skillshare

Playback Speed

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

PostgreSQL, PgAdmin, SQL and JDBC [Combo!]

teacher avatar Karthikeya T, For Your Learning Needs

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

31 Lessons (5h 6m)
    • 1. Must Watch!

    • 2. Chapter 1 : Introduction to PostgreSQL

    • 3. Chapter 1 : Installation and Setup

    • 4. Chapter 1 : Servlet JDBC and Database

    • 5. Chapter 2 : Introduction to DBMS

    • 6. Chapter 2 : DBMS Components and Architecture

    • 7. Chapter 2 : Relational Database Terminologies

    • 8. Chapter 3 : SQL Basic Operations

    • 9. Chapter 3 : SQL Constraints

    • 10. Chapter 3 : Working With Relations

    • 11. Chapter 3 : Fetching Data

    • 12. Chapter 3 : Fetching Specific Data

    • 13. Chapter 3 : Joins

    • 14. Chapter 3 : Combining Queries And Result Sets

    • 15. Chapter 3 : Dealing With Sub Queries

    • 16. Chapter 3 : Conditional Expressions

    • 17. Chapter 4 : Users And Roles

    • 18. Chapter 4 : Views

    • 19. Chapter 4 : Transactions

    • 20. Chapter 4 : Functions Or Stored Procedures

    • 21. Chapter 4 : Triggers

    • 22. Chapter 4 : Schema Objects

    • 23. Chapter 4 : Database Objects

    • 24. Chapter 4 : DDL DML DCL TCL and Postgresql Commands

    • 25. Chapter 4 : Other Comp And Help

    • 26. Chapter 4 : Database Design

    • 27. Chapter 5 : JDBC Intro

    • 28. Chapter 5 : MVC Architecture

    • 29. Chapter 5 : Mini Project Walk Through Part 1

    • 30. Chapter 5 : Mini Project Walk Through Part 2

    • 31. 12

  • --
  • 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.





About This Class

In this course you will learn PostgreSQL which is the most popular Database used in the industry. You will learn about 'PGadmin' which is administrative tool for Database administrators. You will learn 'SQL' which is a language to communicate with Database. You will learn about 'JDBC' with is essentially a glue code, that will enable your application to interact with the Database.

We are also going to create a CRUD Application by combining all the components.

If you...

- don't know much about relational database (or)

- don't know much about PostgreSQL (or)

- don't know much about PgAdmin (or)

- don't know much about SQL (or)

- don't know much about JDBC (or)

- don't know much about MVC Design Pattern (or)

- don't know how the real world MVC architecture works

Then this course is for you. You will learn all the above!

Meet Your Teacher

Teacher Profile Image

Karthikeya T

For Your Learning Needs


Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
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.


1. Must Watch!: full stack developer sort in great demand and are paid very high. So it's not enough that you'll on one technology but rather full stack off technologies. We have all the courses on sculpture that would help you become a full staff Java developer so that you will gain skills to develop, end, to end applications from scratch all by yourself and be eligible for top profile jobs or to work on freelance projects. So was it this link? Its corporate dot com slash skill share. It has all the links to our courses, and they're all organized in a proper sequence. You can start from Java programming, and here's a link to that course. Once you complete this course, you can get into advance Java. And here's the course link for the same. Like was, you keep going and complete the entire stack off technologies. The courses were also include creating into and applications from scratch. And if you're struck anywhere, you can always post your questions. Even if you're inexperienced. Dollop er I would highly recommend you to start from scratch because many people who followed this approach and gone through all these courses from scratch have seen very good results. Many individuals have clear interviews. Some of them also cleared certification exams and some of them also got into good jobs with better income and all. So I would highly recommend that you followed this approach. Start from Java programming all the way to all the advanced courses. Hope it makes sense. 2. Chapter 1 : Introduction to PostgreSQL: all right, let's get started with understanding the data based concepts and structured query language . So in next, few medios will try to get a sense off war databases, and it's uses etcetera. In fact, we're going to take a look at a couple off practical examples as well. And then we'll jump into the actual concepts of understanding the database management system, as well as the structured query language and many more. So in this video we'll try to get a sense of what it is. Just imagine the situation where you were given the task off, creating this website Wikipedia dot org's. And as you notice that this website or Web application to be more precise, has lot of data, it has more data than the source code. So if you were to create an application like this without using a database, then he would end up having a lot off problems. One of the problems you may have is you're going to dilute this source code with lot off content like this. This is raw text, and it makes it harder for a programmer, which is you to find the source code and make some changes to it. Another problem with this is two more. If you want to add new data, you'd have toe add not only the data, but also the source code that helped that data populate. In order to illustrate this behavior have created a very simple application, and this application is created without using a database. Let's say I was given with the task off creating a school management application. So my client asked me that he needed this application for his school, and one of the features that I would offer him is to be able to see all the list off students available in the school. So this is how it's going to work is going to click on this button show student list. It's going to show list off students, and when he clicks on hide student list, it's going to hide the students. And the way this application is working is when I click this link, I'm actually passing this para meter show students, and I'm saying it to be true. I have a conditional statement if condition to be specific in my soul it code, and if it is true, I'm trying to display all the student information. And when I click on this link hide student list, I'm not sending any perimeter, which means, you know, the condition becomes false and I'm not showing anything. Let's see how the score looks in ourselves. It So this is how it's going to look like we have our two links along with the perimeter, and I'm displaying all the student details. Now, take a look at this. Take a look at this big mess. If you write source code like this, then soon you would find yourself searching for a new job. Nobody would like to keep a programmer who writes code like this. I mean, take a look at this. I'm mixing board the data, the raw text as a last the source code. The problem with this is all that. This is very simple application. Just imagine writing an application like Wikipedia, which has lot off text in it. And if you were to populate all the text as part of your source code like we did here, then definitely it becomes harder for me to find the source code in my job. A file and Makesem edits, and it is very much error prone as well. And let's say my client, who owns a school academy or whatever, is going to ask me to add a few more students. He can't do it by himself. He need to request me to add the students, and I have to make it. It's in my source scored to be able to do that, and again it becomes a big headache to make changes to my source code. I would never, ever would like to take a look at a source code like this. So how can we solve these problems? We can solve these problems by separating the data from the actual source score or the business logic. So I came up with this idea off storing data separately into an Excel sheet. For example, I have this associate where I have populated only the data. This does not have my source code, and I'm going to write a soul. It whose source code is going to look something like this. Now, this is just an imaginary code. This is not actually working good. But essentially, this is what I would be doing in my source code. I would create an object off the excel. She it when it's an object off next social, you're going to make use off some external libraries. In fact, we have taken a look at one such example where we're able to read the data from the associate have expended in my Cordova tutorial. So I'm going to create an object. And then I'll try to get list off Rose, which will be list off students like you see here. And then I'm going to have another for loop where I would loop through all the individual cells in each row. Like so. So in each and every rule we have multiple cells. I'm just going to look through all of them, and I'm just simply going to display them in a certain format. Well, this has actually sold our problems, so I'll have my source code in my soul. It and data in an ex associate. So tomorrow, if I want Makesem edits, I don't have to look at the data in my source code. Also, my client does not need to ask me to add new students. He can do it by himself. He can just add an entry in the associate, and he's quite familiar with using extra sheets anyway, and then my court will take her off populating the student information. Added to that, this has also reduced the number of fines off court involved. I don't have to add this set off lines for each and every student that will all be taken care by these looping constructs. So our problems are sold, right? Not really. We got additional problems. In fact, although this has reduced the number of lines off court, it has increased the complexity. And again imagine that you want to write an application like Wikipedia. How can you put so much off data in an ex associate? It's going to cost a lot off performance. I mean, if you're used to using X OSI, it's You must have observed that if you have too much off data in an ex associate, you can't even scroll up and down and let alone managing data like Wikipedia etcetera. And moreover, it's going to take a lot of space. Even an empty document without any data is going to consume some space because it needs space for itself. Another problem, associate ID, by using an associate is security. Somebody else can actually take a look at your content inside this Excel sheet. And if it is some sense to information like passwords, that's going to be a real problem. We can address the problem off efficiency by using the maximal sheets, which are little lightweight compared to exercise it, but still rest off. The problems are going to remain. So what is the solution to this problem? Obviously, a database management system. So a database management system is a software that is specifically tuned to help you manage your data for your applications. So some of the soft rest that are available in the industry and these are some of the popular ones There are many, actually. But these are some of the database management system software's, which will help you manage data for your applications. We have a pretty popular one, which is or it'll database. We have one from Microsoft Gassville, Microsoft SQL Server, and this is the one that we're going to be using pose Greer skill. And finally, my SQL is one of the popular ones as well. So all these Softwares will actually help you address the problems that we had talked about , and in addition to that, they're actually going to add value to application by providing all this set of features, they are for more security. There are for better performance. They help us maintain data consistently. We're going to talk about data consistency. Then we get into the concepts off data, best management systems and it would L. A. Was to have these off access to the data by using so called a structured query language. We'll talk about it, and basically it's going to offer better transaction management. Now it is everything is transactions being flight booking, application or banking system or whatever. Everything is transaction based. So we'll talk about all of them in detail pretty soon. So basically doesn't matter what software that you use, they're all going to sell the same purpose off managing your data. But mice person suggestion is to follow along with me and make use off pose Greece cure. So now why don't we just go to our white board and take a look at what we're trying to accomplish using this data basis? So we'll have our business logic, which in our case, we're using a soul it and will install so called a database management system hard simply DBM s. This could be Oracle database or my a skill. Or in our case, it's going to be posed. Agree. Ask your software. Once you install your database software, there is one additional step to be able to let your application communicate with the database. And that step is installing so called a driver typical. It would be J. D. B C drivers, and you need to install and driver that is specific to the software that install. If instruct was great, ask your than you would need to have its corresponding drivers according Leaf Install. Say my SQL server. You need install my SQL database drivers so the driver is actually kind off. Act like a glue court between your business logic and the data best management system so that the both can talk to each other. This is synonymous. Do installing it driver for your printer, for example. You have your computer and you would buy a printer. If you connect your printer to computer, would they talk to each other? The sprinter work right away. The answer is no. It doesn't. You'd have to install so called a driver software that's going to make these two entities compatible to each other. And that's exactly what we have here in order to make this two entities compatible to each other when it installed so called a driver software. Now, when I say install, we're going to install it in program away, not any exit file, that you can just double click and click next, next, next. It's not going to work that way. We're out of that comfort zone. We're now programmers. But that being said, it's not a big deal at all. I'm going to show you I'm going to walk you through anyway. So once you have that rapid buildup between these two entities, you can let your business logic communicate with database management system. But how are you going to communicate with the dead of best? You had to come to get with the language that the database software will understand, and that language is nothing but a structured query language. We have multiple videos dedicated to learn ask. You'll, in fact owe the most part. We're going to focus on a school because no matter what database software the to install one thing is going to remain consistent. That structured query language so tomorrow. If you want to change your vendor, maybe from post Greece to maybe wanted to switch to my school, you don't have to change your SQL queries. It's going to remain same. The only thing that you need to change is the driver software. So anyway, more on it later. So once you have everything set up using a skill queries, you can perform crowd operations see for create, which means you can create some entities inside your database. In our example, it's going to be students table. Or you can do read operation, which means we'll try to read the data from the database in your application and do something with it. Or you could do update. You may want to change an entry in your table or deal it. You may want to delete some entry from your table. Now, for the sake of this example, I'm just saying that we have single table, but in reality there are hundreds off tables, and that's where, and that's why we have database management system to handle all that complexity. So for next few, we'd Eos we're actually going to try to get a sense off how we can make use off database software's and the problem it Saul's. And then we'll jump into the concepts of understanding DB Emma's and structured query language, and that would suffice, so stay tuned. 3. Chapter 1 : Installation and Setup: All right, let us try to make use off a database software to store our applications data and make our application more robust. So hero, the sequence of steps that we're going to follow. The first thing is to install the DBM A software which in our case, is going to be post gray. Ask your software. It's a database management system, and then we'll try to use these jar files, which is Java database connectivity or simply jdb see jar files, which will help us connect to the database in order to exchange data between the business logic as well as the database. And then we're actually going to use the structured query language to populate some data into the database so that we can then fetch those details from our application logic. So the data that we're going to insert is the student list, and then we'll read the same from our So let so let's see how we can go about doing this. So the first thing that going to do is to install the software search for this term in Google, download post grad school for whichever operating system you're using, and click the link whose domain name is post grayscale dot or GTA. Click on the download installer and here you get to choose abortion that you wanted to download. Obviously need to choose the latest wash in. And once you do that, you also get to choose the operating system for which you want this software to be installed. In my case, it's Window 64 bit, So I've chosen this. Once you do that click download, you would have it downloaded on your local computer. I've already done that, and this is how it's going to look like. All you have to do is to double click it, wait for the screen to pop up. Once it does, we can start the set up preserved by clicking next. This is going to be the installation directory if you wish, you can change it, but leave it as it is. Nothing wrong in doing so and hero various components which are going to get installed So we have our pose grass, your server, which is the main component. This is the actual software that would be needing, but apart from that deserve additional components, which will be using as well. The PGA admin is like hair user interferes to interact with the database. I'm going to walk you through that as well. The stag builder is a tool that will help us install additional modules on top of this components. If the post great skill is like a plain cake, then the stag builder will allow was to put some topping on the cake. So this is ideally not required. But if you would wish it can install it, let us install it may be in future. We might want to use it. The command line tools will actually help you interact with the data Best software from a command line. Now, this is equal into PG Edmon, except that this is a lighter version and you would typically run set off commands to perform your respective tasks. So this is a little lightweight combat to PG admin, and I personally recommend you to use the command line tool in straw. PG admin. Since you're in the learning process. Also, another reason why I'm recommending you to use the command line is it is light with it. It's not going to consume additional RAM and CP resources. As much as speedy admin does. So Let's see what we can do about that. This is the place where the Post Grace silver is actually going to store your data. Now, when I say it's going to store the data, the data will be stored in the form that you cannot read it. For security reasons, of course, I'm going to live it to the default Click. Next, and this is very important by default. This is a user post. Greece is a user name, and you can do the passport off your preference that you can remember. What are your type in here? You're going to be using it to be able to connect to the database. This is similar to the user name and password that you do when installing the operating system so that you can use it to log in for the first time. I'm going to give the password the same pose. Grace and I would recommend you to you the same because my examples would work with you as well without making any changes. Click Next. This is the port where the server will be running. If you have a good reason to change it, please go ahead and change but otherwise leave it to default. You can also choose the default low car. I'd like to leave it to default as well. Shouldn't be a problem. You can change it later on. If you wish and then our installation will start. It's going to take a while. Let me click next and then again next. While this installation is in progress, we can simultaneously take her off. Jerry. Busy drivers. So first, let us download the drivers. Pacific Jar files again. Go to Google and search for this term. Bulls Grass killer Jeb is a driver. Click on this link whose domain name is Post Greece Kildare Arc, click on download and choose the latest one. If you're still using G r E seven, then you might want to go with this. Have downloaded this and this is what I had. So in order to use this jail visit driver libraries in our project, we either have to copy this into the projects lip holder or on the service Lib folder to use it across. All the applications have copied it inside silver's lip folder. So this is my party bomb. Got home territory have now get it inside the live directory, and I placed my jar file over here. Hair it is. So go ahead and do the same while our software is being installed. All right, we reached the end of the installation. So this is also starting the database server, creating the menu shortcuts, etcetera. Just take a look at all these messages and try to get a sense of what it is doing were done . Now, we could actually keep this enabled and click finish so that we can install additional modules. But I don't think they're required at the moment, so I can un select it and then click finish. But let me just show you what you would see. If I select it, he would get into this additional screen, very have to choose the silver on what you want to install the marginal click next. So these are additional modules and Adams that you can install, and one of the module is actually the JD visit driver that we've already downloaded. So I'm not going to be needing any off this, So just click cancel. We don't want toe instantly know that edition stuff. So that's it. We've installed our database management system pose great. Ask your and we've also set up the Jetta busy drivers to use them in our code. What comes next is too popular the student information in our database. Now there are three different ways you can do that. But no matter which way you choose, you have to use the language that you database software can understand. And that language is structured query language. We have multiple videos dedicated just to understand and learn about structured query language. But just for the sake off this example, we're going to be using following query language to perform or task. This is actually data definition language. This will help us define the data that we're going to put inside our database. For example, the piece off text that I've selected is actually going to help us define the table. In our case, it's going to be a student stable. Now Table is the Concept Off Relation database, which will talk about when we explore on database management system concepts. But for no, this is simply going to be a student table, which has the following columns. We have I D column, the student name column, age and description the description is going to be off form text are in dollar terms. It's like a string, and we said it to be not now. That means we cannot have a null value specified. A student must and should have a description. Similarly, the name shouldn't be now, and age is going to be off type indigent, so we're going to be storing bunch off numbers. And then we have the I. D column, which is said to be a primary key. We're going to talk about primary key later on. But for the completeness off this video primary key will help us uniquely identify each and every individual row in the table. For example, if you go back to wreck associate assuming that this is how the data gets stored in the database and if a make this particular column as primary key, then all the values that you specify here must be unique. That means you can't have double get values. For example, if you say this to be to then that's going to cause problem. The entry wouldn't be entertained because this column is said to be primary key and all the values, and it has to be unique, why it has to be unique. That's because each and every road data will be identified with a unique identifier and in the scares, it's going to be the serial number. Also, you can't have no values when you make a column as primary key. All right, let's go back again. We'll talk about primary key at later point of time. Just try to understand what we're doing. I'm actually going to walk you through all these as part off SQL videos, so nothing to worry. And once you create this table using data definition language, you need to use so called a data manipulation language that help you add entries into that table and the way you do it by using this insert, really and later on from our application, all we're going to do is to read all the student information and display them on the browser in some meaningful way. So in order to fish the student details, this is the SQL query that we're going to pass from our application and, of course, by using data with drivers. So first things first, let's create our student table. And as I mentioned, there are multiple ways you can do so. One is from our business logic, but for that way to use database drivers. I'm going to show it when we demonstrate this particular query. But the other two approaches is from so called an SQL council. So it for Shell and then you would come across with this SQL show. PS Girl Stands for Post Grass Cure This is a command line tool that will help us connect to the database and use SQL queries to do a variety of things. Let's see how we can create our table from here. First, you need to specify the server location. In my case, since it's installed in my local computer, I would leave it to local host, which is the default one. And by default pose. Grace is a database which was chosen. This is a default database, which gets created when you install the software for the first time. If you created a different database, then you would type it down here. In my case, it's default, so I would leave it like that and hit Enter the port. Number various over is running and the user name is going to be posed. Greece as well. If you remember when we were setting up our software, this is what we have seen. And the password is same in my case. Hit, Enter. So so far, we're able to connect to the database now. Way to take it off. Creating the stable. I also know that I made this idea karma cereal, which means I want to auto generate the numbers in incremental in order and passed it all here. This would create our table student table. No, What we need to do next is to insert each and every individual draw into the stable, basically the student information. And as you can see here, we're using the inside query and we specified the columns that we wanted to populate. We're populating student name, age and description, but we're leaving the I d part because we said it to be a serial, which will. Which means this column will now have auto generated numbers again. Will this cause about them? And we talk about structured query language. Now, this time, let me execute the statement using another approach. This time I'm going to be using the PG admin. So again you go to stop menu and the search for PG admin, right click on it and the neutron knitters administrator. It's going to take a while now. Do taken note that this is an entirely different application running on different port altogether. So this is not going to be the port, which was specified while installing the software. This is a module that will help us play with the database in user friendly passion. So this is sort of going to provide us the graphical user interface for our database software. Or, in other words, you can do pretty much what you can do in the common line, but in a user friendly way. So let's try to execute, are clearly and in order takes getting to use one of these tools, which is a Quito but currently is greater because we haven't yet choose in the database on which we want to run that. So let's click that this is the silver. We could have multiple servers installed on multiple ports in our kids just once over, so I'm going to click this, and then I went to choose the database where I want to run the query. So I expand this and choose the database. This is a default database. Let's start expanded and see what's going inside it the lot off things that are that you're currently not aware off. We're going to uncover some of these, definitely. And in fact, I'm going to in fact, how really are two dedicated just to understand the PG admin. But definitely we're not going to go very deep because it's not worth it. Just as an eclipse, you're not using each and every feature in here the lot off things. We didn't explore it. In fact, they're certain features in eclipse that I myself haven't explored till date. They're just They're just for the completeness now, since this is an application that lets us do exclusive things, they're also going to cover some corner cases, which are not regularly used. But still, I'm going to try to cover in a majority off these features incoming videos. But that being said, it's always better to use the command line tool because it's lightweight and is going to take wary, lest amount of resources, or at least once you're done using PG admin, shut it down. You can see that application in here, and you can just simply right. Click it and then stop it. One. Sudan with activity. So anyway, let's run our query. I went to choose the database and click on these tools. Now this gets enabled. Quit tool. I'm going to paste decree, which have selected to insert an entry into the students table. So let's run this query by clicking this icon, or you can just simply hit F five. So likewise, Let us executor start the queries as well. To add all the entries off all the students. I already have them ready, so I'm just going to copy and paste them copy and business over here, and they all get executed in one goal. Something like this. Now, in order to take a look at what's inside the table, you can go to the command line and say, Select star from students, and that would fetch all the student details. And this is the exact same statement that we're going to use in our program to get all the student details. Alternatively, you can also check the same in here as part off tables. You can see the student table list off creates or the columns called it. Now since we're done with PG admin. Let us close it. It's a good practice. Right? Click and then shut down Server. Yes. All right. Now, I could actually walk you through the program, but just for your psychological satisfaction, I'm going to move it to next radio. But last night, I just want to mention how we can start and stop the database server plus Windows key and the letter R that would prompt you this and then type in this command services dot MSC This is going to show you a list of services that are currently running and you would be able to find the Post Grace over who were here. And here it is. So you can either stop restart, I'll stop the service. Alternatively, you can use the same from Windows Command line as will heal. The windows are type in CMD and you would say net stop and then it would provide this name . It has to be exact. Same name was Greece cure hyphen. Heck, 64. I haven't done access denied. That's because Angela's this command Prosser as an administrator. So you right. Look on it. More run as administrator and tried to run the same command net. Stop both agree. Ask your hyphen X 64 hyphen. 10. That this is going to stop the service. You would see the same getting reflected over here as well. You can stop this office from here or from the command line. But this time it only going to use Net Start Command to be able to do so. In the next week, I'm going to show you how we can access the student details from the Soviet using database A driver. But do try to do everything what I've done so far so that you can follow along in my next video as well. All right, see you. 4. Chapter 1 : Servlet JDBC and Database: All right, let us try to implore code by reading the data from the database. So we're going to pretty much have the same court. Except instead of hard coding all the data off the student details like this, we're going to first the details from the database, and we're going to use a vile loop to look to each and every student and then display each student details in some presentable manner by wrapping the data with these HTML tags. So let's see how it's done. So the way we go about it, basically, we're going to organizer Cordon. In this manner, we'll introduce a piece off code that will connect to the database. Isn't the jerry besiege our that we had included in our live directory? And then we'll fetch the store indeed, us from the database, and then we'll present them to the user in some meaningful way. So for this purpose have created another Servet, which does not have any code in it. Now. I could have Britain the court beforehand and could have explained it to you, but I don't want to do this this time, because the set off instructions that in malls to connect. A data base is error prone, so I won't go type along with you, So let's see how it's done. So full section is too connect to database and fit student students detail. The first thing that point to take care off is to include, or rather load the driver class onto the jbm the driver class recite in the jar file that we have included as part off the lib directory. So the way we load the class file onto JBM System is by saying class 0.4 name. It's going to accept arguments that's going to the name of the class file, including its package. In our case, it's going to be ork dot post. Greece was great. Ask your dot driver Do take a note that the letter casing is very important here. The letter D has to be a per case, and since we're using pose grease as a database software, this has to be posed serious cure and this is actually going to throw class, not phone exception would need to handle gracefully. So I'm going to wrap this section off. Corbett, Try catch block. Let's get it out. The auto generated comments What comes next is we're going to try to connect to the database by using a class called driver manager. So we'll have a driver manager Don't get connection and it except three different arguments . The 1st 1 is the Ural that helps you locate the database in our kids, the database that were created. In fact, it's the default database his post Greece, if you remember. But before that, we also need to tell the server location where dispose grease databases reciting. So the way we specify it is by saying, Jerry, BC colon pause Greece cure again Colon. And then you would specify the I P address off this silver. In our case, it's local host, so I'm going to say 1 27.0 dot one, along with the port number, which will be 5432 In our case, that's the port number, which we had said while installing the software. If you remember, and the next two arguments is simply the using them and pass forward. So it's going to be pause Greece and pause Greece. That's it. Now this instruction is actually going to return the connection object. Now we have another here let's see what it is. It says even this instruction is going to throw an exception, which is a skill exception. So let us handle it by adding a catch close to the existing try catch block. All right. Also, if you notice the get connection method is actually returning an object called connection that implements the connection interface so we can define our connection interface here. And then let's make it now, Here, I'm going to do control shift. Oh, to import the required classes or interfaces. Make sure what are the class feels that your classes or interfaces that you're importing are part off this package java dot at school. Don't import anything else. So I'm going to say connection equals. So until now, using this connection object, we can create a statement and execute and SQL query. The query that we wanted to execute is going to be this and will use it in our code. So I'm going to say connection, don't create statement. This will return the statement, object So again for that, I don't introduce a new reference type statement s equals. No Alcantara shift. Oh, to import that interface. And we've been death sane this to this. We're pretty much reached the end off the formalities required to connect a data base. Now we can actually Exubera quickly by saying statement dot exit get creating, and it's gonna tax it on string. In our case, it's going to be this. This statement will execute this query, but we wanted to get that result back here so that we can display them so this method would return so called the result set. So let's declare it over here. That's important as well. And I'm going to say Horace equals eggs. Get quickie Now. This RS object will no have all the student details. We just have to display them to save your time. I already have a piece off code that's going to help us display the student details. Let me just best it down here. Here it is. It's pretty simple and straightforward. This instruction will actually display the heading tag with some hard coded text, but the important section is this. We're looping through that result said and trying toe fetch each and every individual student details by saying results that dot get string is the method that is part of the results. That and you would specify the name off the column we had set in a student table. So it's going to be this. We specified the exact same name. Still the name to be able to fetch the current students name Same goes with age and description as well. So likewise, were able to display all the student details also has a good practice. We can also include the finally block to close all the open resources. Everything in here is a resource went to take her off, closing them as well. So in order to save your time, let me have the court ready and then I'll get back. So here we have it on disclosing all the resources and then again, we to have pride catch block for them as well. Or alternatively, if your affair off the John Lines feature private resource, we could make yourself that as well. Let me just quickly show you how we can accomplish the same. It's pretty simple. Hardly going to do is to take this out, but all the resources inside the try section, you know, just like so we're going to have ah statement as well as part of the try. Well, this is going to look messy. You just have to bear with me for a minute. And I sure are called the look cleaner and get it off these two, and then we can have we no longer need the finally block now, and we need try catch block for this, though. We can't have tried with resource for this instruction and similarly can now get it off. This these two instructions from here went to have private resource for the results. That as well. So we're going to move it to here. So that's how it goes. Get it off this from here. All right. Will wrap everything inside this Kali braces, and we'll do some formatting. This works. We don't We don't need this because, you know, try catch block. We don't have this instruction. No possibility for class, not phone exception. So but our code looks little cleaner. So if you're if you're comfortable with private resource, you can use it as you wish. But since industry hasn't adopted all the javelin features yet, I would assume that even you are not too pretty well used to it. So let me just undo all this changers and go back with Try, catch block. Here we are. We're back. So now everything is set. Why don't we describe the courage to launch our application? Let's do that when direct, Click on this and run as run on so over. All right, we got another. Let's see what it means. It says it's no wonder exception. And then if you go to the council, you can actually traced through what's happening. It's a no suitable driver found for in the spot. How could God this I p incorrect as well as, Ah, you know this one supposed to be jdb seen O J S D B. Let's go get them and see if things will work. I'm sure it will work this time. I don't think we messed up with anything else or let let's hear the file clean the project how, as usual on drawn it again, and this time it worked pretty well. Harris also note that I haven't included this, too. You are girls. If you wish, you can add them. Also, I need to mention that after we talk about structured query language, I've dedicated one or two videos talking about Jerry BC. So if you're not sure of what's happening in here, then you will better understand the concept then. So wait until that point. Moreover, we have written or code without following the industry's best practices. For example, it's not register that you include the estimate code as part of a soul. It we would want to let it go inside a jsp file. Similarly, we don't want all these instructions that help us connect to the database to be part off A . So let we would warn them to be part off another class. We're actually going to make changes to our code and improve our code to make it fit for industry's best practices. Basically, we're going to follow a tree tire architecture, moral, real controller, architectural, which will talk about that later. Point of time. In fact, we were going to actually create a mini project with some basic operations, were going to create a very basic school management application where that men can actually add a student, remove a student or update a student, or delete a student etcetera, all such kind of basic operations. So stay tuned. There's a lot coming our way. Hope that makes sense. See you in my next video 5. Chapter 2 : Introduction to DBMS: all right. This is how arrest of the course on database is going to look like roughly. We're going to dedicate couple off videos, or maybe three, in understanding some of the concepts off database management system. Especially, we're going to focus on relational database because Relational database is the most popular form off database used another days, and then we'll jump into understanding structured query language, which in turn will have all these sections. And this is where most off our energy is going to go into. Because this is the place where Java developers will come into picture. They should know how to make use off SQL to come and get with the database from their applications. And then we'll talk about some of the things that you can do in PG admin. I mean the graphical user interface we have looked at. In fact, these two sections may go hand in hand. When I talk about structured query language, we're going to take a look at how we can accomplish the same task from post gree admin, and as a bonus, I'm also going to walk it through how you can go about designing a database this may help you in future, so why not discuss it? We'll jump into understanding some of the classes and interfaces involved in jail. ABC and in the end will try to wrap up by discussing some good practices. So let's start with some database concepts for this purpose. I've created this document. I'm going to make this available for you to download. DBM is introduced docks whatever. So let's start with the basic definition. What is a database and what is database management system? A database is simply a collection off interrelated data typically stored in the form off tables, just as we have seen in gives off US students table. And the most popular form of database is the relational database. This is where tables will come into picture. In fact, this tables are termed as relations will get into that next video, maybe NDB and misses a software that's going to assist you in managing your database in more efficient and secure manner and is also going to love you to perform credit operations , create, read, update and delete. Along with that d be amiss also offers host of other features which will discuss a spot off these advantages off db Emmis and is also going to offer better transaction management, which is also something that will discuss in a moment. So these are some of the examples off DBM a software, but the one which we have installed is obviously was great. Ask your Celestica. Look at some of the advantages off DB amiss. Now, whatever the advantages that have listed here are the advantageous compared to traditional way of storing data, I mean in files maybe in an associate are in an XML document. So the first head want is that we're going to talk about his data consistency, meaning that the data will be stored in consistent manner. Let us start understand an example off and data which is inconsistent. Let's say that you somehow decided to not use D. B. M s and instead you want to use an X erxi it and let us assume that you're storing the student information on multiple excel sheets. But now, today you wanted to make an update to their student information, for example, maybe you wanted to change his fee structure. You would change it in one location and you forget to change another location. That's going to result in data being inconsistent. So that's one of the example off data being inconsistent. But D VMS allows us to maintain data and consistent manner. You understand the concept of consistency when we taken example off transaction, which is coming next, DBM muscles offers better security and access restrictions. Let me just show you what I mean. If you go to PG Edmund, for instance, expand the server and then you go inside the section that says logging or group rolls expanded. And then you will be able to see the post gray user that we had used to log into there the best directly can it and go to properties and go to privileges. This user has all these privileges. He could, for instance, can log in. Yes, and then he is a super user, which means he'll have almost all the privileges on administering the was great skill. If we turn it off, then it's going to restrict certain privileges for this user. Similarly, got other privileges as well. But the point that I want to make here is we can create such restrictions. We can have multiple users with certain privileges. DBM Israel also ill. I was to have these off access to the database by using the structured query language, and the data gets retrieved in efficient manner. Internally, Devia misuses lot off algorithms in most efficient way possible to get their desired data. That's going to contribute in improving the user experience off your website D B. A missile seller was to take backups or for database. Let me just show you what I mean. For instance, if you choose one of the data basis available and if you go to tools, we have an option to take the backup. You take the backup and, in case often a disaster. Or if you lose your data in future, you can then restore the database and put things back on track. So recovery is being able to record the data and gets off data loss. You can also have automatic recovery. In fact, that's one off. The major features off DBM s is to be able to replicate the database on multiple locations . For example, let's say that you have multiple database servers were sitting on two different geographical locations and at the same time both the service would have exact same data without any mismatch. And in an event off loss of data on one of the servers, the others are is going to help recover the data. So it's one of the prominent features used in the industry as well, and d b a missile of us to switch to alternate. It's as well to more. If you decide to use another provider off DBM s. Maybe one of the other options we have here then it's not a very huge task to do that. You can easily switch because ultimately all this DBM a softness follow standard language, the structure, clear language, it can gender its SQL from the existing database. Run those instructions on another database and you're good to go. D bm us offers better transaction management, everything number. This is transaction. So let's talk about the transaction. Transaction is simply set off logically related sequence of actions. Let me just give you a quick example. Consider the following case where the person A has 1000 in his bank account and similarly be is also having 1000 in his account. Now let's say that a wants to send 200 to be now here is the sequence off steps which are logically related to perform this transaction or, in other words, in our transaction logic. This is what we're going to do. We're going to read the balance off A, which will be 1000 then we'll read the balance off B, which will also be 1000. We didn't 200 from a and then we credit that amount to be second, so a is will become 800 bees balance will become 1200 now. This is a simple example. Off transaction. Now let's see how dbm this is going to ensure reliability and consistency. Basically, it's going to follow this principle called acid properties A stands for a Thomas City See for consistency. Hi, for isolation D for durability. Thomas City insurers that the transaction takes place either completely are nothing or no transaction will take place. But not in between. Let us say that our dbm mrs not atomic in nature, Then that's going to cause problem. Let us consider the same example. What if my logic has deducted 200 from years account? But when I'm trying to execute this, maybe I lost my connection. Or maybe there is a park it, then this is going to result in debating the amount from years ago, but not but not crediting the amount to the bee's account. So at Thomas City insurers that either all these actions will take place or if the transaction failed somewhere in the middle, then nothing will be taking effect. You will still have the same old balance. Off 1000 consistency ensures that the database remaining consistent state even after a transaction has an example. The total amount of this, too and B is 2000 and after the transaction, it's still 2000. So that means our databases and consistent state. This is just to give an example off what it means to maintain a consistent data. And another important feature off transaction is isolation. Each and every individual transition needs to be executed in isolation. Let's see what problem with my face. If there is no concept off isolation, let's consider a similar example. Let's say that he has decided to send two under. Toby has alas, too, Mr C. So we're going tohave. Same set off transactional instructions for C as well. So this going to be see, for instance, No, let's say that we're performing those two transactions in one goal, or let me just quickly copy this and do seems it off Transaction for se. See? So this is how it's going to look like. So what this transaction are going to go simultaneously? Letter resume. Now, when you try to execute step number three simultaneously, then both these transactions are going to deduct 200 from that count. But do they can note by the time these transactions are going to deduct 200 the current balance off A. In both this transaction is 1000. So all the way directed 200 from both this transaction. Ultimately, it's going to result in only direction off 200. And the balance is going to be 800 instead off 600. So you flee exhibited these transactions one at a time in isolation. Then this transaction will take place first and the balance will become 800. And then comes this transaction to send the funds to person C and then the resultant while they will become 600 ultimately so But son do those changes. So, ultimately, isolation play a great role in performing transactions. The durability insures that the transaction doesnt lead to inconsistent database in case off power outage or network outage, or in case off system or load etcetera. So all these features off database ultimately is going to help us perform transactions in most reliable way. So clearly combat Teoh storing data in traditional way Devi Mrs Clearly the option. Unless you have a very simple application which has only say a page off data, then then it's OK to deal with XML Czar are in associate, But most likely your options should be a database. All right, see you in my next video. 6. Chapter 2 : DBMS Components and Architecture: in this video, we're going to take a look at various application architectures and see where DB a mess will come into picture. But before that, let us try to understand where he is competent involved, to form a database management system. The basic component is the hardware that's great. That's where the software will get installed, your D BMS software. And this is a place where the actual raw data will get stored as well. So this would basically be your computer or the Silver Harbour very install your D BMS software. Next comes is a software which is basically the software that you installed on this hardware so that you can now start interacting with the database data is simply the raw data that is stored in the database. In fact, it's because of data DBM Mrs. Existing procedures are just simply set off procedures that you follow to interact with the database. I mean, if you're falling, the instructions on installing this software are to access the data. That means you're following that in procedure to interact with the database. As simple as that query language is, the language is in which you would communicate with the database and use the underlying data, and we would typically be using Ask your structured query language to be able to do so. The query processor is the actual process that will take care off, understanding the SQL and do something with it. In fact, the query process will actually work with the database engine to perform their quite operations. Basically, all the crowd operations that I've talked about and the DB manager is another component which will actually help you manage the data. It said that you wanted to take the back up off your database. That's when the DB manager will come into picture. It will help you take backup or record the data etcetera. All such kind of additional stuff will be taken care. But the DB manager also when I say data that gets stored. It also involves meta data, which is data aboard the data. For example, a meta data could be details on when the data is stored, or how much size is a store data taking etcetera. What comes next is the architecture. First, let's talk about the one tire architecture er, in one time architecture, you would interact with the database without having to install a database server, which basically means you're just make use off some libraries, typically a dark jar file in the context of Java, and you would use those libraries to be able to access the underlying database so your application logic would directly communicate with the database that is stored in the same computer. An example off a wonder architecture would be a standalone application. All the applications that doesn't have to deal with the outside world, for example, and anti where software are a browser, for instance, Although the browser helps us in tracked with rest of the world and access Web applications , the brother in itself is a standalone application, and it typically relies on. These embittered databases are, in other words, without having to install additional data base silver to store the data for the application to run. Coming to do tire architecture ER, you would install a separate database over, and your presentation tire would interact with it. A good example for this is your using JSP pages for presentation time and from JSP file. You're going to have some instructions, typically by using and driver like Jerry, Busy driver to be able to connect to the server and communicate with it. Now, in this to their architecture, there is no soul that involved. JSP, which typically falls on the presentation tire, would directly interact with the data. Best Tire and Tudor architecture. A is not a popular one. What is popular is treat our architecture or a four tire architecture. Let's talk about the treat architecture. It simply means we get additional layer in between the presentation tire in the database star, which is the application tire where you'll have the business logic. The presentation tire would come and kill with the application tire and if necessary, application Tire logic, which is the business logic, would connect to the database using the driver Softwares. And then it would interact with the database. And it would present the data back to the presentation there so that it can render the data for the user. A typical Web application follows the street architecture, but it could be more, especially if you want to include additional components like, for example, enterprise java beans in the context of Java. Then we would have a controller layer, which will which will be the sole, that which will control the flow of execution and then in the application tire will have the business logic. Arrest all remain same. You'll understand more about that architecture model view, controller, architectural. But this is basically it. Also, I could have talked about where he is. Different data. Best models available. But let me tell you, the most popular more 11 days is relational database. Moral period. There is no substitute for that as off now. So there's no point in discussing all those old models which were used in 19 seventies and 19 eighties. They're off no significance at this point. So in my next video, we're going to talk about the relational data best model and see some of the terminology is involved in it. See you soon. 7. Chapter 2 : Relational Database Terminologies: All right, let us try to understand some of the relational data. Best concepts. First of all, let us try to get the definition off. What's a relational model? A relational model is a technique used to organize the data using so called relations or, in other words, tables. We have in fact, taken one such example in case off our students table, restoring all the student information in the form off a table. Well, that's the concept off relational database. So here we have a picture that Alistair is the same. We have a table which in this case is a student table and we call the stable. Relation are a table, and each of the record are the row in the table. We sometimes refer them as people, as you can see it here, and all the columns are actually called attributes. The world attribute would make more sense when you try to look at the definition off the table. So we use this instruction to create the table. Whatever is highlighted in green are actually attributes. Actually, we can map it with object oriented programming. Israel, for instance, if student is a class than these, are all the attributes in it. In fact, using the modern airframe books like Hibernate, We can actually map the Java object with the table in the database and the kind of data that these attributes can support is termed as at domain, like I've highlighted here. So these are some of the terms typically used in the context off relational database keys will actually help you identify each and every individual people or row or sometimes also called as record keys are also used to establish relationship between tables. You'll understand this when we talk about Foreign Key, which is coming in a minute or two. Take a look at this example table. When you choose a key, it has to help us in uniquely identifying a record. If you choose the name as a key, that won't suffice because name can be duplicate. Two different students can have the same name, just as you see in here, so we can't choose name as a unique identify. Where for the rose in the stable or in other words, name alone cannot be chosen as a key. Let's take a look at some of the categories off keys available in the context off relational database so that you understand it better. His super key is a set of attributes within the table that uniquely identify heat row recorder tuber. Here are some of the examples Heidi is a valid super key because there is no possibility off having duplicate values in this field or attribute. Same is the case with combination off name and phone number. You can have duplicate values and name, but when you combine these two, it will remain unique. So these two attributes combine together will form a super key as well, and name as have described cannot be a key name, and age together cannot be termed as a key because two students can have same name as, well a same age, so we can't choose this to as a key phone will remain unique. Forage in every student so it can be a possible super key. Same goes here as well. Coming to candidate key. A candidate key is a minimal set off attributes, which can uniquely island for each record in a table. So out of this Super keys, whichever is the minimal set off attributes that will help us uniquely identify a record in the table his termed as a candidate key. So I D and phone are two examples. All the name and phone together conform a key. It can't be a candidate key because we need to find the minimal set so one would suffice. Here. We don't need to use two attributes, so that's not a candidate key. And when you choose one of these, Keys has a unique identify. Where for your table rose that KIIS toned as a primary key? It's as simple as that. So, like a primary key can be a single attribute. Our combination off two attributes. But typically, let me tell you, we always choose the i. D column as the primary key. In fact, we introduced Heidi column in each and every table just for that purpose to make it the primary key. It's a good practice, and all the rest of the keys, which we haven't using are called. Secondary keys are alternate keys. Hope that makes sense. And Foreign Key is actually one of the key concepts in relation database because foreign key actually helped establish the relationship between two relations or tables. So let us see how it's done well, if you go through the definition, you probably don't understand. So let us take an example. Consider these two tables. We have a course table as well as the students table in here. I have listed all the courses, and each of the course is identified with a unique identify Where, which is course I D. So this will be the primary key off this table and C e mails with the students table. We have student I D. As a primary key. Now let us assume a scenario where we can have multiple students and rolling for a course, and a single student cannot enroll for more than one course. Just try to understand what I said if you want. If you wish to pause the video, can Paulus try to get what I'm saying? So for that purpose, we're going to define their foreign key, a foreign keys, a field or a column like we're seeing here in one table. That refers to the key, typically the primary key in another table to create the link between two tables. So if you take a look at this instruction, we're seeing course form Key, which defines this particular field and then it refrains us to the core stable course I D Field. This instruction will actually make this field a foreign key, which is actually different to the primary key off another table. So by that, the only values that can go inside here are the values that are part off the referring table Primary key, which means that well, is that you can possible enter here or the values that are listed as part of the primary key that the stables referring toe. In this case, we said its course table. So by this table, we can say that Sam and Sunder have enrolled for course Number one, which will be Java, and Nick has enrolled for Jsp, etcetera, and ask about this design. One course can be enrolled by many students, but it's not the other way. We can't have a single student and rolling for multiple courses. If you want both these things to happen, went to design our database to support many to many relationship, and the way we do it is will have course table in the student stable will introduce another table with two feels both or foreign keys, and do they ignore that as a good naming convention. We always say F k for foreign key, and this foreign key refers to course table, and this refers to the students table. So course one is andl best to, and one course to is enrolled by student, one so on and so forth. So in here, not only multiple students and all for a single course, for example, store in one and to enroll for course one but also a single student and rolled for multiple courses. Student One has enrolled for both one and two. A composite key is a key off two or more attributes that uniquely identifies a row. But each of the individual attributes doesn't form a key. Consider the falling example in here we have student I D. In course I d. The country's this field as a primary key because there is a possibility off having duplicate values. Same goes here. We can choose this field, but we can choose the combination off these two attributes or fields to form a key primary key for this table. So essentially we have the same table with the additional field off date off enrollment. So this is what is that composite key. If you're all around with number off keys, then I would just suggest you to have a good understanding off primary key and foreign key that put suffice. I believe others are just there to create confusion, but it's always better to know. So let's talk about schemer Schema is like a design that describes how the database is structured and organized. These designs are typically done, but the database designers to illustrate how the data is organized to rest of the team or with customers. So we have a couple off scheme. Us. One is the relation schema, which will basically talk about the tables in the database. And we have the database schema, which is like the big picture of the entire database that we're dealing with an example of what you can see here. By the way this images picked up from this location, I put the mate source Justin, because if you want to take a look at so here's a major or the scheme of that to present the students and teachers database. All these individual blocks represent tables, so we term each block as a relation schema and combination off all these is together called as a database schema. If you take a look at this, each and every student well, actually deal with multiple subjects. And so when the good exam they get multiple marks. So single student multiple marks, which is one to many relationship. If you observe this line carefully, we have multiple points here, which represents many. So we have one to many relationship between these two tables. Same goes here as well. Multiple students can belong to a single group, and we have one to many relationship. Now this diagram is based off entity relationship diagrams which will talk about that later point of time. But this is a very quick in major on how we can design a database. Now, with all this in mind, we're definitely good to go head to start exploring the structured query language. See you soon. 8. Chapter 3 : SQL Basic Operations: How could this very is kind of like a starter video to get started with structured query language? So we're basically going to take a look at some of the basic operations that you can perform on the database using SQL language. So we'll take a look at how we can create a table, read the content from the table, abrade the table and delete some content from the table. And for these purposes, I've chosen four different types off instructions that you can use. So let us take a look at them. If you want to take a look at the same documented can defer to the stockman that's is 01 basic. Ask your and then you would be able to see the same. So here's a syntax for creating a table you'd use this key would create space table, and then you would provide the name off your choice. Basically, this is going to be the name off the table, and then you would have the name of the column. This could be any name that you would restrict you, and we may have constraints. Has an example of this line in here. You can see it here, for example, for the column I. D were play the constant, which is the primary key. And we're saying Serial, which means we want toe auto. Generate its value by implementing the number by one, and you can also have a column with some type and then optionally can specify a constraint . We've done the same thing here, has it student name and the type of the content that we can store in this attribute is text . The domain off student name is text, and then we put a constant saying that the values shouldn't be no for this column, so similarly have mentioned rest of the fields that would describe or construct my students . Stable Optionally can also use this word inherits and then you specify the name of the older, existent table. That way, the students table will not only have all these columns or attributes, but also had two birds that are part off the table it inherits. This is optionally can get it on this if you wish, but do make sure that you end the statement with a semi colons. It's under those changes, so let's see how we can execute this statement. There are two ways you can do so. One is from the PG admin or P SQL Shell provided by deposed grass Cure. So let's first launch or Post Grad school shell. Just search for Shell, and you'd be able to find P SQL or SQL Shell. Just click it. You would specify the location off your silver. In my case, it's local host, so that's the default. And that's where you see this being highlighted in the square. Brackets hit, written or enter. Specify the name of the database. In my case, I'm still using the default database. Post grace out head enter port number better over your silver is running. In my case, I've left it to default while installing the software the user name is going to be. They use the name with which we can log in to this database and perform certain operations if he's entitled to. In my case, this is the default is a name. If you had followed my instructions on installing the software, then it should do exact same steps that's it enter and would be asked to enter the password . Give the passport that you have given while installing the software are the passport off the user name that you've entered. In my case, it's same post Greece hit. Enter. Now you have logged in the post, Chris. Alternatively, gone also just to log in from PG Admin Council. But make sure that when you're launching this application, right click on it, goto more and run as administrator. And once you do that, he would have a browser console that looks something like this. All right. Now let's see how weaken execute our instructions and went to copied this from here to create a table. And let's see how we can execute from the PS cure. You can just try to the best it'll here. But the problem is, we have already created the table students. You could change the name and run. It alternatively, can also do the same from PG admin as well. So you expand the list off so was available. If you're seeing this cross saying, that means we haven't yet connected to the server from this PD admin console. So the very connect to that's ovaries. You right. Click on it. Click Connect us over. You may be asked with user name and password. If so, go ahead and enter your user name and password. Expand the data basis. Here we have list of databases and my case is just one would expand that goto scheme us public and you would be able to see list off tables available, expand them, and we are able to see the students table. So let's create another. I would choose the database where I would like to create my table and I go to tools. Excuse me. I need to choose a database and then I go to tools, Click on Query Tool and past your Quindio here. But of course you need to change the name to something else. I would say, Let's name it some name. Whatever you would like to give and try not to do space here. Just use underscored instant office space in case if you would like to use more than one word for your table name, Um, whatever. And then click this icon that looks like a flash icon or thunder or whatever dislike it, or you could alternately hit F five that would do the same job. So guess what we have now created at table. He won't see it here because went to refresh right click and hit refresh, and you're able to see the table over here. What comes next is the read operation. But in order for us to perform the read operation with the first update, the table with some content. So we're going to use this one of the upgrade instructions, which is insert it will allow us to insert at pupil or a role indoor table. We also have another key word that says update and using that keep what he would be able to make edits to the existing row in the table. But in this case that has use insert, the entered statement would have the pouring syntax. You would say Insert into you would provide the table name where you would like to perform the insert operation your specialist off columns for what you want, update the values and then you use this key would values and again in parentis is your provide list off values now do taken note that the first value that you provide corresponds to the first column that you provide here, same as with other columns. The second value maps to the second column so on and so forth. Now here's instruction that helps is insert some data into a table. I went to copy it, but this time let us run from P s school. Just paste it here. But before that, let me open the North bad and change the table name too. Some name, because that's what we have created out. Copy this and then tested over here. The problem is, I've given the name incorrectly. The right name is you can use the command show tables for this purpose. Show tables. Okay, Basically, I've used the command we used in my skill. But for pose great, it's going to be different. We have to say slash DT and that would list all the list off tables available in the current schemer or the crime database. We're going to take a look at a list of commands that you can run from P a skill that's part off the course as well. So here's the name. So let us use the same name while trained. Insert the data. We got the name wrong there. But let's use it anyway. It's, um, some a n m P. Now that has inserted the values in the table. Now we can actually go ahead and read the data from the table. By using this instruction, select the list off column that you would like to take a look at from the table name example of which is demonstrated here. This instructional letters Take a look at these two attributes off the table. Students are If you'd like to take a look at the entire table, all the columns are that tributes involved in the table. Then you're just simply use this star. It's a wildcard. So let's go back to PS School and say Select star from some name and with the semicolon and hit enter and you're able to see here. Now let's see how we can accomplish the same from the PG admin. So we're here. Some name you'd right click on it and then choose View Added data all rose Here. We're able to see that data. Let us populate new data. Just click on the field that you would like to populate and do take note that I'm not going to populate the I d feel because we want auto generate the values so would leave it alone. And with public the name let's say the name to be some, um, apple or whatever. Let's give him meaningful human name soon There, for example, and you hit save. But when you try to save this table, we're going to get a never. Because if you remember, we said our description not supposed to be No. So we're forced to enter some value here in the description. So let's do that. Let's click this errors that it will go away and then populate this some description and it saved. We're going to hit Save here as well. No errors. And you can see that some I d got generated already. So this is the way you would insert data from the PG admin. Both are same at the you do it from P s killer. From here, the only difference is from PD admin. You'd have this use interface without having to write the SQL instruction. And that's the very reason why I would like to suggest you to use PS kill for learning purposes. You would get to write the complete SQL statement which will help you learn things better than you do when you use a graphical user interface from PG admin. this gives you a little comfort. And I don't like you to be in comfort zone, especially when you're learning. Correct. So let's see how we can delete the record from the table which is coming next. We're taking a look at create read update and now we're going to take a look at the delete operation. The village instruction will have the fallings in tax, Deal it from table name and you provide the condition, for example. The condition in this example is I d to I want to delete a row from the students stable whose I d is set to its competence instruction and go back to a the PS kyul r two RPG edin . So I would open the query tool. We have based instruction here Possible Exchange the table name too some. It's I guess it's a enemy. Sorry about that. I got the name wrong and we're struggling here and there. So let me make this three and run the query. It worked. Let us do refresh And let's take a look at the data that gets populated in here and you would see that particular role missing since we've deleted it. So this is about the basic operations. This week is just to give you a sense off how we can make yourself structured query, language, etcetera. But from this point on, I'm not going to be running all the skill statements from the PG admin or from the PS school because we have instructions. Where would be requiring to make use off multiple tables, and it becomes very complex at times. I cannot explain in better fashion if I won't execute all those statements from PG admin or PS Cure. I would rather give an example. Table in here, right here in this world document and our Babel to explain you in better fashion have experimented it, and I thought it wouldn't work. I can explaining better fashion from this document, but sure enough, we're going to talk about some of the other features, as in when they come. For example, I'm going to show you how we can go about creating users. Heardem creating a making use off functions, for instance, like was, we're going to explore some of these features for sure are. In fact, we're going to explore all the major, popularly used features from PG admin, Asil, SPS girl and added to that not to mention that s girl is, ah, language. Which means it has lot off key words that we can use equal in number of words in the dictionary. Well, not really, but I mean to say that there are too many words that we just can't discuss each and every one off them. That being said, as usual, have planned to discuss everything that are used regularly in the industry. So we're going to continue to talk about structured query language to the most part, will be spending our time with the word document. But if I find that I can explain things in better fashion using PG admin, then I would definitely do so without hesitation. All right, we have many things to cover. Let us make some smart moves. See you soon. 9. Chapter 3 : SQL Constraints: okay in this, really, we're going to talk about constraints will also take a look at how we can create this constraint from PG admin, as well as using structured query language, Primary key and the Foreign Keys, something that were already aware off We've taken Look at one such example. But what's new are these three constraints, most of which is not. Know what simply means when you specify this, not null key word. Along with the attribute. When you create the table, then you can't have an empty value or another value in this field. You must and should provide some value off the data time that respectful here. In this case, it's text the unique constraint. You can specify it. This does you did here When you do so, you can't have the same value entered twice for this column. And it's logical. For example, in this case, you can't have two similar email addresses, so we can put this constant saying that it should be unique. Then, when you're trying to insert an email ID, which is all that existing, either accidentally or intentionally, it won't allow you to do so, and then we have the check constraint very can specify your own condition. Or, in other words, what are the value that you put it must satisfy the following condition. So in here I'm saying, What are the value that is being entered in this field fee? I'm going to check to see if it is less than this number. Only then will I allow the insert statement to work. All right, let's take a look at an example in our example. This is essentially what we're going to do. We're going to have a student stable, and the idea column is going to act like a primary key. Will also have the course table, which will have the list of courses. And I do feel is going to be its primary key, which will have unique numbers that would, uniquely, I didn't for each and every Joe. And in order to map these two tables or to create the relation between these two tables were having the student course table, which essentially contained two fields, each of which corresponds to the primary key off these two tables in here, which means we're using the foreign key. This is a foreign Guelph students table and this is a foreign key off the course table, and then we're able to map its many to many relationship because if you take a look at this , we have the same student registering for multiple courses. Similarly, the same course can be registered the multiple students as well women to just take a look at it. So let's go to P J admin and see how we can create this. Tables with constraints have already created this tables, but let me create them again just to show you how they work. So you right, click on the tables, create and then click table. You would specify their name off the table. First, let's create the students table students, and I'm going to say temp because this is temporary table I'm creating just for demonstration purpose. And then you can go ahead and add the columns that you wanted to be part of the stable. I want to give it some name. Let's call it I D or identify data type is going to be in Teacher. You can select the one from this list. I'm also going to introduce another column which will be student name, and this is going to be off from text. Another column would say E mail I d. And this is going to be It's going to be a text as ville or whatever you would specify. And if you go to the SQL section, you would see whatever we're doing here, its corresponding a skill is getting generated. So if you want to do whatever we're doing, the creating columns, etcetera, then this is how the SQL statement would look like. Now. This is pretty formal. The presentation of SQL. Typically, you don't have to specify the public, which is Ah, the schema. You also don't need to specify explicitly that the onerous post grease it would automatically be done. If you're just run this part off this query, that would suffice. But it can always run. Center quickly yourself if you wish to. Let's go ahead and add some constants to the stable. We want one primary key, which would be the idea. Field someone to say. Well, click this plus sign. I went to view some name. It doesn't matter what name you just to identify this primary key for this table, um, some name or whatever, but what matters is When you click this button, you would get to choose the column What you want to choose as primary key in the scarce you can choose. Any field that makes sense, obviously, is going to be I. D. You can also choose to fields or three feels whatever is your preference. And, ah, that's all there is to it. You click save and we have this table we have. Emailer does field. Why shouldn't we just put unique constant in that field that's click on properties and go to constraints? Look on unique against, like on this plus sign and the click Whatever name that you want to give you. Click this and choose the column. Humility. We wanted to be unique and click Save all well and good. Now let's go to quit it, too, and insert some data indoor newly created table. I'm going to say select star from students temp, empty table and insert into students temp. What's best for the list? Off columns do take note that we didn't choose i D to be auto generated. If you want auto generated values in your I D field, then it can just go ahead and change the data type of the column and the scales. You choose cereal, but you can't choose No, because you have to do that during the time off creation off the table. But let's see if we can see that by adding a new column such for cereal, and you would be able to see it. So while creating the table, make sure that you choose cereal and gets. If you want to auto, generate the I D field there isn't it, won't l. A. Once after creating the table is that maybe you might have already inserted some values in your I. D field so it won't allow us to disturb that. Cancel. Let's do it ourselves. I'm winding through it. I d Field student name and email. I D. And values are going to be save. One student name is going to be whatever the name, obviously some name and the email. I d. Some me milady. Some email at the rate some company dot com. Let's end with a semi colon on the query, and things worked pretty well. Now let's do select star from students temp. I have five pretty well done. Good where the start insert same humility once again, of course, by changing the Heidi to something else, and Mets from the query would expect a never because they put a unique constraint in the email field and it won't allow us hope. That's clear Now. Let's see how we can create a foreign key. So right click on the stables, create new table and, uh, you would give some name. Let's a student underscore course temp. We wanted a pill. Get this table click on columns. Have the columns off your choice. Let's say our column name is student F. K stands for foreign key type is going to be off type integer, and let's go to constraints and add the foreign key again. You can click this plus sign you water the name that you want to give. Click on this edit row. I can go to columns. Choose the column that you wanted to make foreign kindest cause we only have one column, so that's only option. Available references is where you get to choose the table that you want to point to and our kids. It's going to be students and, ah, we'll get to choose one off its column. And as a good practice. You always choose the primary key off the table, so I d and you need to click. This had button, so the details will be added. Now, now we have the foreign key. If you want to take a look at their skill, licked the stamp. And, uh, when we talked about the foreign kid, this is a sin tax we were talking about. We're talking about the differences Keyboard, Richard, first ODI, foreign table. And it's primary key. Pretty straightforward. It clicks save, and then we have the stable. So that's essentially how you go about creating the relations between the tables. Using the foreign key constraint so similarly can add the other constraints as well should be self explanatory. Or maybe let me just quickly show you how we can create the check constraint, right? Look on the table, go to properties and then to constraints, click on the check constraint had give it some name. And this is where you would provide your condition. Maybe, let's say age should be greater than 18. You would see the same getting reflected over here as well. But off course, we don't have the column age. You can indigenes that if you wish, but just trying to show you how we can create the check constraint. And there's another constraint, basically, which is the exclude constraint. Now, this is not a standard. Ask your constraint, so would better not talk about it at the moment. So that's it. See you on the next radio. 10. Chapter 3 : Working With Relations: All right, let us take a look at some of the SQL statement that helps us do something on the table creatively, something that we had already talked about. But what's new is create table. As so this is the create tables and that's ready. Straight forward, coming toe, create table as it will letters perform or execute a query whose result would then be populated in the new table. An example of what? You can see it over here as it create table. I give some random name as and I would have some kind of a query here. In this case, I said, sell its star, which is Ah, wildcard symbol from Students Table, where students age is greater than 28. Whatever is the result would then be stored onto this new table along with its fields or attribute. So let's see how we can execute this statement. Let's go toe r p g admin that's copied this statement and bested in the query processor who and run it. So this has created a table, went to refresh the database and you would see the new table here. This, And if you look inside the continent off the table, you would see all the students whose age is greater than 28. But the reason why you're not seeing any data is because if you go to the students table, you would notice that there are no students whose age is greater than 28. They're all 28 So let's try to change the query. But before that, let us drop the step or delete the stable from here and then read on the query again. So I go toe created tool again. I would face the same query, but this time I'm going to say it's a greater than 27 and this time you should see the result taking effect. So here is a table all rose, and you would see all the list off students. So all those students are kind of old students. In our case, let's go back the altar table Key world would actually help you after the table as simple as that. For example, using altar table, you can actually had a column to the existing table or dropping column are really in the table. Or maybe change the constraint off the table. Attribute etcetera. You can do a variety of things with it. Let's try to understand one by one in here, this instruction would actually add a new column toe the students table with the name E mail, and its data type is text. I'm not going to run each one of these instructions because I believe they're pretty straight forward. And then we have all the table students drop column, which means we want to delete a particular column from the Students table in the Scarce. It's the column email. We want to get it off this attribute off the table, and we can also rename the column off the table and the skills. We want to change the name of the column email to student email, and this is how you do it. You can alter the table name itself like so. Like I said, we can also add constraints on existing field, like I did here. In this case, I said in the students table, I want this to combined together as a primary key. If you just want only one attribute to be acting as a primary key, it can specify only one in here. You can also change the data type off a particular field like we did here. In this case, we're trying to change the data type off email field, Tovar Car versus text, which was the case earlier. You can even alter the database name and change it to something else, just as I did here. So these are basically the operations that you can perform to change the definition off the table. There are many more keyboard, so chicken news. If you want to take a look at the complete reference off all the table, then you can go to this link. This is official was great skill dot org's website, and you can pretty much see everything that you can run on. Pause Grass Cure. If you search for older table and click this, you would say the complete reference. I thought these were set off key words that you can use in the statement to do a variety off things. Just take a look at it, get us, and so, for it's what they're doing. Nothing fancy. Pretty straightforward. Let's go back. Let's take a look at how you can go about leading a table or it's row or database conducive . By using this word drop drop table and optionally ca NSI. If exists, students, this instruction would dilate the stable or better it for the sake of performance, especially if you have a huge amount of data and your table. Then insert off saying drop table. You're better use trunk it table that would delete the table in most efficient manner just to save some performance. So I guess that's it on this video. See you in my next video. 11. Chapter 3 : Fetching Data: all that. Let us take a look at how we can fetch the data from the database by using multiple clauses . Were all that ever off the select applause that helps us view all the list of Rose available in the table. The select clause by default is going to fish the details in the same order they were inserted. But if you accompanied by using this keyword order by and with A C stands for ascending than the data that gets fetched would be in ascending order. They're sending al Guard, um, would get applied on the column name that dispense for here, so this particular instruction would display all the columns in the table. Students and his order by the age and in ascending order similarly can also say it to be in descending order. If you don't specify either ascending or descending than the order by clause defaults to ascending. Similarly, you can also sort a string attributes. For example, we're trying to sort the student name in ascending order, which means in alphabetical order. If you want in diverse alphabetical order, then you would say descending as simple as that. The select distinct clause would help us eliminate the duplicate rose from the results set . The best fit understanding is to take a look at this example. If you take a look at this, I'm having list off students, and I'm saying Select distinct age from students, which would actually eliminate the rose that has the same age. And this statement would actually display the age column, but distinct values. So even though we have age 28 repeated multiple times, it would be displayed only once, just as you see here on the similar lines. We also have the group by Klaus. The group by is actually going to work the same way the select distant works. For example, we have Krish repeated twice. Same is the case with Maria, but those two entries are not seen over here. These are all distinct names, but things will change with group by when you use the aggregate function. So in here, the only addition I made, here's the segregate function, I said student name, and then this keyword some. We're going to take a look at the list off aggregate functions available in a moment, and then I specify the column. So this has displayed the student name and their aggregated some off their fee. For example, for Krish, we got to students with the same name, Krish. And when we try to find the sum, that's going to be 3000 and that's exactly what we're seeing here. Same is the case with Maria. If you take a look at this, there are two students with the same name. Maria. When we try to find a go, get some off their fee, that's going to be 7000 and same thing gets reflected here as well. And along with that, we can also use the having clause. This is just too for the filter out, the result set. So in here we're seeing some offi, which is the exact same thing. What we specified here greater than 5000. So we'd like to display only the entries. Who's some off fee is going to be greater than 5000. In our case, it's Maria and Flint O and those two God displayed here. So these are list off aggregate functions available, and they're pretty self explanatory as well. So can just quickly take a look at them and practice see, assume 12. Chapter 3 : Fetching Specific Data: all right, This is going to be a very easy going video as well. We're going to take a look at some of the SQL statements with which we can fetch specific information from the table. And for this purpose, we're going to use the where clause, which would actually help us stretch details from the table based on the specified condition. And when you combine the where clause within and between, we can do already off things, and those are all demonstrated in these examples. First, let's talk about the bear claws we're going to say select and the list off column that you'd like to display from the students table where this condition is met in this case or condition is the age field must have the value greater than 25. And the student name has to be Krish. Whichever the records that match this condition would be returned by the statement. It's as simple as that. And if you would like to take a look at where he has different operators that we can use like we've used here, then I've created this document for you operators Doc txt even download and take a look at all the list off operators. If you're ever off any programming language, this should be pretty straightforward, and the next instruction would actually pretty much the same instruction. Except we're using the are operator to fetch the list off students whose ages 8 to 25 or 28 as simple as that. Alternatively, we can accomplish the same task by using the key word in, and you'd specify the list off values this does you see here so in is kind of like an alternative to our. The only difference is if you're using in, then that's going to make all statement look more readable. Little come backed nothing different, and similarly, we can also use between to be able to fetch the students whose ages between 25 35. You can accomplish the same by using the end. For example, I'm going to say which all the student details from students Table, whose age is greater than 25 and age is less than 35. That's as good as saying between 25 to 35 years of age. You can also use this key would not between that is just simply opposite off between So we get all the student details whose age is not in between 25 to 35 and we have this fetch. This is like the sq limit key would. But the limit keyword is not a standard. Ask your key would, so you had better. Always use fetch instead. So what this does is it would help us limit the number off roars return from the quickie, an example of which you can see here. Select column names from students where age is greater than 25 we're saying Fetch Castro Only that means, as the statement suggests, it would only fetch one single row that gets found based on the condition we provide. And similarly, you can also specify the number off Rose that you would like to see a retreat. In this case, it's three pretty easy, right. SQL is also indeed one of the easiest language off the HTML. Of course, Next comes the keyword like and it would basically help us return The rose from a query based on the matching Patton, for example, in here were saying select star from students were student name like the pattern. This means any student name who has the letter A are would be fetched. And in here it's the opposite. You can also say not like so we would get less off. Students whose name does not contain a are the person date symbol is kind of like a placeholder for any other list of characters. Alternatively, you can also use underscore. Which means, for example, if you take a look at this Patton, then this means we wanted to fetch list. Or students whose name has three characters and the center character must be s. That's how it goes. Hope that makes sense. See you in my next video. 13. Chapter 3 : Joins: already in this, really, we're going to talk about joints. There could be cases where you might want to extract information from more than one table, for example, the data that you want to retrieve, maybe reciting in multiple tables. In that case, he would use joints to join those two tables and present some data. First, let's talk about the inner join, and if you understand they not join perfectly, then it's easier for you to understand. Any other joint instructions will talk about after this. So let's not go through the definition. That's just jump right in to the example, and then I'm sure you'll be able to understand the definition. So we have the street tables, student table, course table and student course table, which will actually map these two tables. So this table essentially tells which student has registered for which course. And these two are the foreign keys mapped to the primary keys off, student table and the course table. Pretty straightforward. Now let's say that I wanted to display all the list off students who have enrolled for this course is now. If you observe the only students who are listed in here or 12 and three, which means the rest of the students are not registered Well, if we have a column here that says to the name and if we have this tour name that corresponds to the course that he registered, then our job is easy. You can just say select student name from student course stable, and that would give us list off. Students who registered for this course is, but it's not so straightforward because we don't have the student name column here. Instead, we're having the student name column in the students table, so we need to somehow joined these two tables to be ableto retrieve the information. So essentially my task is to find the common rose in these two tables. But how do you determine mine? The commonality of the Rose is based on the i D. If I find an idea that is here mapping with the I D, which is specified in the students table, then I would display the student named that corresponds to it. In other words, ignore their school statement will talk about it. This is a resultant output. I'm expecting so list off students and their free structure and do taken note that it won't be able to see the other students because they haven't handled for any courses. So let's see how we go about doing that. Let's go to PG admin r P ehskyoo, whichever is your preference. So we have those three tables populated here. Let me just get rid off other tables, which are which we have created. We don't need that some name table as well as the old students stable could just delayed it or weaken delivered with drop table SQL statement as well. So let's open our query tool. No. First, let's try to display the student name, select student name from students and with the semi colon and run it. I could just hit their five to run it, and that's what I'm going to do from no one. So this has listed all the students, but this is not what we're looking for. We don't want to display the students who who haven't enrolled for any courses. Well, before we talk about joining the tables, let me talk about so called and Elias we condemn present the students table with a letter. In our case, let's say the letter is s you can give any letter off your choice. And once you define that, you can defer to its column by saying s dots to the name or say as dot I d coma, etcetera. And if you run this, you're able to see those two columns. The significance off this is it's a also want to show that details off course stable and even it has the field i d. If there was no concept off Elia's, and if I say I become I D, there is an ambiguity here, which I really belongs to which table they both have the same field with the same name. So it's always better that you use Elias, for example, for course. I want to say, see, and then I want us display it cited by saying, si dot i d. As simple as that, by the way, we are seeing so many columns because what has just happened is cross joined. We'll talk about it in the end of this video, but the point is, this is about the Elia's. This will help us avoid ambiguity nor statements as allows, this would say, was some typing as well. You don't have to enter the entire table name in stricken. Just type the alias on the subsequent statement instructions. All right, so let me just undo the court spot. But we're going to keep using the students Elia's. So I want to display fee as well. This works pretty well. No. Let us join this table with the student course table. And so we can get the common rose within them based on a certain condition eventually. What? I mean, I'm going to say inner join. And then I would specify the table that I want to join student Underscore Course I'll call it S C. This is going to be an Elias for student course stable on. The reason why you need to specify this keyword on is because it would allow us to provide the condition on which we can say that the person both the tables are common. In my case, I want to say has dot i d is equal int to s c dot student underscore. I d underscore foreign key because that's the feel that we have in here. If you take a look at it, we got student I D and Foreign key. So let's go back, give the same here. But when you create a Colum using the PG admin you'd have to provide or put it inside this double quotations. Only then will it work. So let's run our query and see how are our put looks so pretty well and good. We have in fact, seen all the people off the student names who haven't rolled along with their fee structure . For that course. Now we're seeing the double gets here. If you want to get it off it and showed distinct student names, then you can always use the key word. Guess what distinct and that would display the desired result. It's let me undo it. So if you go back to our document, were able to see the result and this is the skill which we have just run. If I want to represent the scenario, dia grammatically, then this is how it would look. This circle represents all the rules in the student stable, and this circle represents all the rose off the student course stable. But what we're displaying here are the roads that are common to these two tables, and that's why we have highlighted this common section. So inner join helps us rose that are common in both the tables, and the commonality can be deter mined based on the condition that you provide. In this case, this is the condition. I hope that's clear. This is just another example. Let's say that you wanted to display the name column as well the name of the course. But if you notice the course name is actually reciting in another table the course table now we have to use in a joint in the same SQL statement to expect the result from this table as well, and ultimately were able to club all the results by joining all these three tables. And this is instruction to do so. I said I wanted to do in a join between the table that is mentioned previously, which is student course table as well as the course table. Based on the falling condition, I said the course table I d equals I mean, idea off. This equals the idea off this, and since there is no mention off, if I d four here, we shouldn't be able to see the course as cure. And here, in this result, he won't see it and die grammatical. This is how it's going to look like. We've joined students with the student course table and then we joined Stone Course table with the course table so we can populate the rules that are common within these three tables. Just as the idea that have violated here, let's run this instruction an RPG admin. And here's result, of course, we need to say si dot name pretty straightforward. Now, if you understood inner join, our rest of the journey is going to be very easy. The left joined simply means that not only do we want to populate the common rose, but also the rose off the table that is reciting on the left hand side off the left joint key would in this case we have the exact same instruction of just copied it and best it here. The only difference is instead off saying, inner join, I said left join. So left joined, meaning that we wanted toe also populate all the rows and students table, regardless off if they have registered with any course or not. And that's why if you take a look at this result, we've also populated are the students who haven't general for any course but that they seem to have paid fee even though they haven't enrolled for any course but destroyed. Understand the concept. This is just a example, and right join is the opposite. Off left join. Not only will it display the common rose between two tables, but also all the rose off the table that recites on the right hand side off the right joint keyword in the scarce. We have the exact same instruction, just as we have here. But the only difference is, I said, the course table needs to write join with the student course table, so diagram magical. This is how it's going to look like we're going to display all the roads in the course table. So this has in fact, displayed all the course names. If this sounds confusing, just walk through this document and try to understand. I'll go ahead and run these instructions and pretty easy to understand. The full joinus simply combination off left and right joins nothing fancy, and this is how it would look now. I provided a sample Eskil statement. This is not an SQL statement that that we were typically use in real world. This is just, for example, purpose, and that's why I give you this warning. We would never want to join two tables based on their i D, as I did here. But here's the result. So we've joined students as well as the core stable, and this literally displays all this. All the rose in both the tables coming to the cross joined. This is a default join when you don't specify the where clause or if you don't specify any other joints and basically it's going to join each row off one table with each other. Roy, in another table, I'm going to show you with an example. In fact, I've actually demonstrated this already, if you remember. I mean, just keep on doing say that we also have course table Coursey and wanted to display. See name, of course name. So I want to see that name and let us run it. If you notice the number of rows in the students table is six, and the number of rows in course table is four, and when we multiply this too, we get 24 rows in total, and that's exactly what we see here. So all the roads in student table are joined with each and every roll in the course table. We're seeing all the student names mapped with Java. Similarly, we've seen all the store names matter jsp, similarly with the rest of the courses as well. I couldn't think off any real time scenario where this could be useful, but it is existing. So just letting you know. Well, there could be cases where you might want to join a table with another instance off the same table, and that's called self join. But it's not something that is popularly used, and so I'm not putting it in this document because these are all the joints which you should be aware off as a Java programmer. Hope that makes ends. See you in my next radio 14. Chapter 3 : Combining Queries And Result Sets: All right, let us take a look at how we can go about dealing with multiple queries or combining the results sets off multiple queries. The first thing that we're going to talk about is union. The union keyword would actually help us combine. The result sets off two or more queries into one result set. Let us take a look at this example. We have students table, and with this quit, I'm just displaying worse What's inside the students table and using this good. I'm displaying the course table with this instruction, which I just selected weaken despite the students table. And with this instruction, Aiken display the course table. But when I use this keyword union in between these two queries, then I'm actually combining these two result sets. And so we have this. This is as good as me dragging this particular image right below this like this and combining this too so the union would actually sort off. Combines arisen, sets off to queries. But there are certain rules to be followed, whichever the crease that decide on the left hand side, as well as the right hand side of the Union key. Would the number of columns specified onboard. This queries should be same. In this case, there are two columns which are specified. Same should go in here as well. Also, the corresponding columns must be compatible data types, for example. The idea column is off type integer. Same is the case here. Similarly, si dot name is off type text, and student name is also off type text in the boat or compatible data types. So there's a couple of things that you need to keep in mind when you're using the union operator. Also, the union operator would actually get it off. The Duke will get Rose. If you don't want to get it off the duplicate Rose, then you have to use union all. Let me show you what I mean. Having this competence exact query and let's go to our query tool, pestered or here run it. Now let's try to remove in the skins. We don't have any duplicate rose, and so you don't find any difference. But let me just get it off this to the name column as alas, here and run the query. What do you expect in 10 rows? It won't be because the Union operator actually eliminated all the duplicate rose from the result set. If you want to display everything, then you use union all. And that would display all the rose. Let's go back. So that's about union. Now let's talk about the Intersect. Intersect, as the name suggests, is only going to display the common dross found in both the result sets. For example, let us make use off the exact same query, and instead of union, all I'm going to say intersect. So these are the rolls off both the results of soft this queries that are common. I mean, there's going to display 1234 has, alas, this. So the intercept has just displayed the common rose. And if I were to represent the same scenario di grammatically using the Wen diagram, then this is how it would look. The circuit on the left represents the results that off a table which is basically the table that decides on the left hand side of this intercept keyword and in the right circle represents the result set off the B table, which is on the right hand side of the Intersect keyword and the highlighted area in here which is grain color is set off roads that were displaying pretty straightforward. The except is kind off opposite to intersect, and it basically helps us display distinct grows from the left query, which is this that are not in the output off the right query. For example, our core stable comes on the right hand side of the except keyboard and if you notice we have the ideas 1234 And these ideas are also common in the students table, and so the except would display the remaining Rose six and five. So let's say, except and it shows just as expected, let's go back. And this is a diagram that represents the same scenario, but it's self explanatory. Grouping set is kind of like a shorthand for writing multiple group by queries. Let me show you what I mean for this purpose have greater this particular table. We have missed off students and the course that they're registered and then went off we that they had paid for their courses. For example, John has enrolled for this two courses javelin database, and he had to pay 1000 each, which, when combined together, would become 2000. Similarly, we have the same course Java bean, the district by multiple students, John as well asunder. So keep the scenario in mind and let's go to our query tool. Let me copy the table name and goto our query tool. I'm going to say select s name and some coffee from the student called stable and I wanna group by s name. We've already talked about the group by keyword. If you want to take a look at it once again, feel free to do so. All right, so this is the expected result. Now, let's say that I wanted to group by the course name, so I would say see name and grew by sea name. That's right. Now query, credible and good. Let's copy the statement and let her know I wanted to display about the results in one goal . And for that, I'm going to make use off the union. Oh, because I also wanted to display the duplicate draws if they exist. That's run the query. Now, if you notice. Although we have displayed the union off these two queries, the column name is little misleading. For example, the column name says that's name which represents the student name. But in here we're also displaying the course name. So let us write it more meaningful for that in this crude, I'm going to say no in place off the course name field, because in this query we can't display the course name when we're trying to group by the student name. And similarly, we can't display the student name when we're trying to group by the course name. So in place off the student named Field, we're gonna say now as well. And no, it's done the query so you would see not getting populated now, Although this doesn't display the course as the column name, we in fact separated this column from the student name, so we're fine with it. Now let's say that I also wanted to displace some grouping by fee, and I wanted to combine the results with this table. For that, I went into just another query and of course I'm going to be using union all for this purpose this time. See, Name blow to remain has now, and I want to grow by fee. And let's run the query incredible and good. Now, instead of writing so many group by queries, we can accomplish the same task with just one clearly and that by using the grouping sets, it's very simple. Let's get it off. All the queries will keep only one grew by grouping sets, you would have an open and the closing parentis is just like so you would end with the semi colon. And in here we'll have our list off groups we have group by s name. We've also group by seen ing and by fee, that's all there is to it. It's on the query ordered. The reason why you're seeing all the null values here is because we said second column to be now. Instead, we want this to be see name. Where does run the query, and you would see the exact same result what we have seen before. And just as you could use group buy clothes on multiple columns. You can also specify multiple columns in this parentis is separated by coma, for example, see name, for instance, etcetera. Let's go back. So this query as well as this gritty are both same, except this is more readable and would get the same result. So if you understood the grouping set, Cuba is pretty easy. The key War Cube would put their simple for your job. For example, this query in here is equal into this. Observe this section off query was that Cube? And then we give three column names and those three column names will be represented like so. So instead of writing this statement, you can just simply right this. So we have sort off, have all the combinations off group by close on all these columns. And as you would expect, the result off this going to be pretty big. This is actually single result. With the 22 rows, the rule up is essentially does the job off a cube, which will help us reduce the number of lines off. Clearly instruction. But the task off rollup is to form the group by clauses like So I said, Roll up. And then I gave three columns. But internally, the statement is equal into performing group operations on all these sets off columns. It's like for ABC column. We have group by ABC, and then we have group by a bee, grew by a and then grew by nothing as simple as that are adopted on this video. See you soon 15. Chapter 3 : Dealing With Sub Queries: okay in this, really, we're going to talk about sub very a sub. Queary is simply clearly nested inside another query. Let me just show you with an example. Let's say that I want to fetch all the list of students whose age is greater than the average age off. All the students combined together, so I can't have a query that say's. So let's start from students where age is good or than the function average off the age column. Now this is not going to work because we can't use functions like this in in the Where clause. And that's what it is complaining. So let me just don't do that. So what we're going to do is use this up very instead. So foster fall were to have a sub very that Britain's is the average age off all the students. And the way we go about writing the same is well, it says select average off age from students. As simple as that. This will return the average age, and now we can actually use this query in our wear Clothes has a sub query to retrieve this student list whose age is greater than the average age. So I'm going to say where age is greater than I put opening and closing Parentis is inside , which will have our sub query just as you see here. But we're not going to have the semi Colin here because this together is a single statement become. Have a statement inside another statement. So it's on this very and this list, all the students and age is definitely greater than 27 which we, which is the average age now let's take a look at a couple of key words, which are any and all. First, let's talk about the any key word letter that I wanted to retrieve all the list of students who enrolled for various courses. If you observe, if you take a look at all the list off students and if you compare with the foreign key reciting in the student course table dessert, all the list of students who haven't rolled There's a 12 and three, and we don't have the students 456 register for any course. So let's go back here. I'm going to say sell its star from students where the I d off the students table will match with any off the ideas that we're seeing here. So I'm just simply going to copy this statement, which is going to retrieve the list of ideas and passed it as a sub pretty, and that's all there is to it. But wait to use the keyword any because we wanted to see if I d matches with any off the ideas returned by this query again, you shouldn't be having the semi column here. And let's run the query credible and good. Similarly, we have the key word all instead of any would say all so that they were checking to see if my day is equal to all the values returned by the statement. But instead of saying equals, let's say greater than and we're retreating all the list of student whose idea is greater than all the ideas mentioned in this table and let's on the query. And obviously this is not a real time use case. This is just for demonstration purpose, all right, that's it on sub queries, and I've also created a document to demonstrate the same for your reference. It essentially has everything would have just explained its title dealing with sub Berries . All right, see, assume 16. Chapter 3 : Conditional Expressions: okay. We're pretty much reaching the end off the structured query language section off this course. And in this way, we were going to take a look at some of the conditional expressions you can use in your structured query language. So here are the three that we're going to talk about the first off, which is a case statement. This is something similar to the Felts clause in any programming language. So it goes like this. You will start the statement with the keyword case, and then you will provide your condition or an expression with the ven keyword. So when the expression is evaluated to true, then we would execute whatever recites in the den section, or else we would evaluate whatever is there in the L section and finally would end the construct with the key would end. So here is the example that the monster is the same. I said, select student name and age from students and in between those two, we're having this case construct, and we're displaying it as a new column. So we have the keyword case and then I'm thing. When the age is greater than or equal to 28 that I'm displaying the following message or else we're displaying this This text and we're ending with the end keyword. And here is how the result would look. People are the students whose age is greater than 28 are equal to 28. Then we're displaying. Feel young. Don't give up. It is their ages. Lesson 20 It were displaying your young as simple as that. Let's go to next one. We have cold dysfunction which would take multiple arguments and it would display the first argument. That is not now, for example, with to this matter, if a person and no value and one and two in the first value that is not no, in this case is one. So it would just simply to spread up. If I have the following expression, then it would result in two. If we have all the arguments has now, then it will just simply display now. So let's take a look at one example off the same. I already have it ready. So here it is. So in this case, I'm saying select collies and I'm passing two arguments. One is the fee, which is an attribute that is part of the students table and then zero. If he happens to be a null, then we would just simply display zero instruct displaying now for this purpose. I have actually made changes to the stable and made one of the fee as now. And when I run this query, as you would see, we're having one of the fee as zero, which was actually an l value. So this is one of the use case where you would use the police. And lastly we have no. If the only function would actually take two arguments. If those two arguments are same, the result would be now, if they're not same, then it's just simply going to display whatever is the first argument. And same thing is reflected over here as well. In the result, if the feed is equal into 1000 then we're saying it to be now. Otherwise, we're displaying the first argument. The actual fee. Hold it. That's it. On conditional expressions 17. Chapter 4 : Users And Roles: in this year, we're going to take a look at how we can create a user or a group with certain privileges so that we can have a set off users who will have restricted access to the database. For example, we may restrict the user. Teoh only view the content in the database, but he may not be able to create New Table or be able to create a new database, etcetera. On the other hand, we can also have users with exclusive access. For example, the super user pose Greece, which we've been using so far. He has access to all the elements in the database and has all the privileges to perform any operation on the database. He can even go ahead and create a new users like we're about to do now, basically the concept of similar to creating users in an operating system. Certain users will have certain privileges, while other users will have exclusive access. Consider the example off windows, for instance, an administrator can actually do anything on the open system. For example, installing a software etcetera and a standard user, on the other hand, will have certain restrictions. For example, he cannot install and new software on the operating system. So let's see how we can create a user. We're actually going to see how we can create a role. Well, impose grass cure. Creating a role means creating a user or a group. First. Let's take a look at how we can create a user. So you click this and then you get this reserved. He would provide some name to the user, for example, on when to call it new user. The definition is where the passport goes. You can also said the account expiry date, if you would wish so. Basically, after the expiry date, the user cannot log in. You can also said the limit on number of simultaneous connections that this user can log in minus one, meaning he can have unlimited simultaneous sessions. Privileges is a section where you would provide all the privileges to this user. And one thing that you need to make sure is to turn this on here to make sure that can Logan is set to Yes, only then whatever we're creating will be considered as a user. If you turn this off, then this is actually like creating a group which will talk about in a minute or two. So make sure that you turn this on and you can set some privileges. For example, you can let the user create. Other users are create rolls just as we're doing right now, but I want to leave it to know. And I also don't want this user to be a super user who would have the exclusive access and membership of the section, which will talk about once we talk about creating groups. You'll understand better. The panel middle section is actually little advanced, and we're definitely no need to worry about it too much. Basically, this has something to do when you're trying to make changes to an existing role or user. Similarly, security is something we're least bothered about. We don't have to set the security labels. So here is this cure that gets executed. This is essentially what we're doing with this resort. So you would say, create user and followed by all these keywords you can refer to the documentation in the official pose, agrees website this type and create user, and you're able to find the entire documentation there where you get to know list of other terms of chicken use. So no super user means that we didn't said this, too. Yes, so he's not a super user. If you said this, yes, you will see it getting reflected here as well. So that's undo it and click Save This has created the user new user. Now let's see what he's able to do now. In order to log in with this user, he could either do it from PG admin. Oh, are the skill show, So let's tryto do it from here first. For the right of us, disconnect from the silver directly. Call it click on Properties in Connection. You would change. They use the name to the new user, click Save and then try to connect back. It's going down the password into the password. In my case, it's a SDF. I'll click. OK, let's see if this user has the privilege to access the data inside tables. For instance, how dry? Click on it and go to view at the data. All rose and you would notice that permission is denied. Let's try to do the same thing from the SQL show. I went to say they use the name is new user password is going to be as DF select star from students. It says relations students does not exist. Even there is existing. This user is not about access it. So let's see what we can do here. Let's say that I wanted to give this user access to students table only and not other tables. Let's see how we go about doing that. Let me just disconnect from the server again. And that's log in with our super user pose greaser. But the connection I went to suppose, agree user, and I'll try to reconnect. So what I'm going to do is I'm going to give grand taxes. So this user on the particular table But before that went to give him access to this schema . Now the scheme of that I talked about in one of the videos explaining the structure off a table or the database Well, this game is little different. What you're seeing here is called the physical schema, the Eskimo that were talked about going through the word document is a logical scheme, and that describes the entire database structure, but they're off tables. So here's a scheme with the various objects in it like we have tables, functions, etcetera will try to explore some of these incoming videos anyway. So let's right Click on this public is Ah, scheme and and click properties go to security. It is where you would grant the privileges to the user if you notice the post great user is already having privilege on the schema. So let's add however new user as well and you to click on this privileges and you can either you create usage or all access. I'm going to give him all access for the time being. Are you could just a stick to usage anything that you think is appropriate. Click save. No. There are two ways where you can grant access to the students table for this user. You could aid the do it from here directly from students table. Let me show it this way. First, who into click properties and you go to security in here was Greece is already existing. So similar. Go, Dad, How user New user privileges. And here you would select what are all things that he is privileged to do on the stable. I would like to let him do only the select operation and nothing else. He may not be able to do restaurant the operations that's click save and it's done. We can no actually access. I mean, the user can actually now access this student stable, whose in the select query and the other way that I was talking about that you can do is by taking on this parent object of tables. And then you go to Grant reserved here. He would get to choose all the objects that are reciting as part of this table's section, and you can choose the table click next, and everything is same. Grand is going to be the new user privileges you can select, whatever the privileges that you wish to view. I went to close it because we've already done that. Alternatively, can also right click on the data basis again. I'm sorry is supposed to click on the database in which you would like to hello the privileges again. You would click the Grand Wizard here. You get to choose the one of these objects. When you access the grand wizard off a database, you would have objects at a broader level. So not only do we see the tables, but also other objects like functions, etcetera and rest. Always going to be seen. All right, I'm going to close it. So the state taxes the table? No. And sure enough, we're able to access it. No state taxes, the other table to which this user is not having right. Let's try to access course table and the permission is denied. So that's how it's going to work. We can give. Restrict our access to the users. Let's quickly talk about the groups, so creating group is essentially similar to creating a user, which is by using this role wizard. But the only difference is as have all the dimension. We just have to make sure that you not enable this this time because we want to create a role. I'm going to give some name, new role, etcetera, with some privileges and again religious are just not restricted to these. We can also set privileges at object level, just as they demonstrated, and the membership part would essentially let you add all the existing groups. For example, if I add this couple of groups, what are all the privileges that are defined in this? Groups will be applicable under group that I'm creating or the rule that I'm creating, whatever you call it, And in addition to that, it can also have your own privileges. So that's where this membership would come into picture. So once you create the membership and this is how the SQL is going to look like and if you know what is this? Diamonds Ralph, create user. This is create role and look at the state and dance. Will we grant these two roles to new role? So we are sort off allocating all these privileges off. This, too. Groups are roles to this new role who that's not confusing and you click Save that would create a new role. And, no, you can actually create new users. By enabling this, you would give some name. This time, you can just simply add that mural has a membership so that this year's will now have the privileges set for this particular membership that's get canceled. So that's how it goes. It's wonderful, isn't it? Hope that makes sense. See you in my next radio 18. Chapter 4 : Views: in this. Who they were going to talk about views? Well, rather than taking a look at this definition and bang, Go ahead, Let us try to understand view with the real world example letter that I own a school and the school management software and I happen to use pose great skill as my database software and as a super user. I've logged into the software, and I execute this query to see a list of all the students in my school. No, let's say one off. My subordinate are another lecture. Wants to get the names off people or students whose ages 28 maybe he need to talk to the students. No, I could actually give my credentials the Super user credentials and let him access this database. But obviously it's not secure, so certainly that's not an option. The another option is we could create a new user with restricted access. Maybe we would allow him only to view the students but not make any changes to the stable. But that will introduce another problem. For example, he also can see all the sense to information like fee or a description about the student etcetera. I only want him to see the names off students whose ages 28. So how can I hello, that to happen? The answer is views. If you would let me represent data from one or more tables with some limitations, let me show you what I mean. First, let's create a view and the very do It is pretty simple. I'll say Create view and I'll give the view name. I'll call it my view as And what comes next is a query. This could be a very simple query. Our could get very complex. I already have an example off query where we're displaying the names of students whose age is equals 28. So let's go back and pissed it all here and run our query. This has essentially created a view. No, let me just simply try to display this view by saying Select Star, basically, just as you would display table, it would display of you by using the Selic statement Select star from your view name that's run it, and you would notice that the result doesn't have the information about their fee or the description etcetera. And in fact you can see this view getting populated in here in the view section. Now guess what we can create a user who will have restricted access. I mean, we can only let him view this particular view, but not the content is at the table. So let's make use off the user that were created in our previous video. So I'm going to click this table good properties security and delete this user from here and out. Click Save and I go to views. Click properties go to security. New privileges to that user. For this view, new user privileges select and save. Well, that's all there is to it. So I would open this new shell new user they use. The name Password is going to be as DF think. I've entered the wrong password. Let's tryto reopen New user. Hey is DF Select Star from students. He tries to view the students table, but he can't. Permission is denied, but he says select star from my view and he can see the information that he needs. So that's the advantage off views. And if you go to my document, that's what is written here. Is there a couple of advantages that are associated with the view. This is something that I've just demonstrated, and it can have very complex query here. It doesn't necessarily have to deal with only single table he could use in a joint out of joints, etcetera, and make it as complex as you wish. You can also create a view from PG admin as ville, for example. You just go here, create view, you would get some name and the definition is very your actual court will go. Let me just copy this code from here this statement and passed it over here and that's it. You can add the privileges if you wish. Click save. I'm going to say sell its star from his dear What's from the Cleary and it works. Views are actually used in real world as well. For example, if you have a sales team, maybe you wanted to expose some sales details to them while hiding some of descends to information like user personal information like email I D or a phone number. So it's what, while talking about views. All right, see you in my next video 19. Chapter 4 : Transactions: in this very We're going to talk about transaction management in database. If you remember, we had already talked about what is a transaction? What is transaction management in database? We've talked about it in one or for intra videos on D BMS. If you'd like to take a look at it again, you can go through this document db Emma's intro along with my video on it. So basically a transaction is has set off logically related sequence of actions that perform a certain task so that either all of them get executed flawlessly or none of them will get executed. So either data will be saved completely or no data will be saved at all. But it's not a rocket signs to deal with this transactions imposed. Re ask your I'm a show. You what? I mean, so all you have to take care off artistry, keywords. The begin transaction will help us initiate a transaction. So followed by this key, would you would have set off a skill statements that together perform a certain transaction and then, later on, you rather say, commit or roll back commitment. You'd like to comet or save changes in the database based on whatever you execute has part of the SQL statements, the rollback will simply undo everything and will not save any data in the database. So let's take a look at this in action. I already have this set off instructions which will cancer them to be a single transaction . Here. We're just simply trying to add three entries in the student stable. But in real world, these instructions could actually be a product purchase or a bank transaction, like transferring funds. Still one account to the other, etcetera. So we use this key would begin transaction, followed by all the set off transactional statements. And then I said, Roll back, meaning that I don't want to save any off the details in the database, meaning that I don't want to save any of the data into database. So let's run this and make sure that nothing is in fact saved in the student stable. And sure enough, nothing got saved because everything got undone with the rollback. Now let's a comet, and sure enough, this time everything will be saved. Just as you see here Now, we could actually use mix up, both commit and roll back. Let's say that I wanted to move, Come it until here and after which we're executing this instruction. And then I say, Roll back now. This time these two instructions will be executed than its data will be safer because you said comet afterward, you're trying to execute this statement and then we said, Roll back not. This rollback will not undo water has been done prior to come It it will only undo until previous commit or roll back instruction. So let's run this. And before that, make sure let's delayed these entries from here. It could still keep them, but to stay. What confusion? Let's run the query. And sure enough, we only have two entries that are saved. And just for your information in software development, we would never typically managed transactions directly from database like you're seeing here. We would typically used frameworks, which will handle the task for us. Also, there's something called locking mechanism that you use as part off a transaction and that lets you actually get exclusive access to a table, meaning that when your transaction is working on that table, no other transaction can actually make updates on the table, so that they'll be less scope for creating conflicts. And the way you go about acquiring a lock on a table is by saying, Look table and then the name of the table students in our case. And then you say the more than what you would like to act for the lock. And here we have radius options. But the option that I'm going to use is to have exclusive axis. I think I supposed to say it's ah, access exclusive mode. So this transaction will get an exclusive access to the students table. No, the transition can actually interact with the table during the time frame. This transaction is happening and let's run it. Things worked fine, and you would certainly see some new later getting populated. And there are actually where yes moored available. You also got to row exclusive more etcetera. You can just refer to the documentation An official pose, great skill dot Argh website. There, the search will just search for the lock table keyboard and then you will find its documentation. All right, See you in my next video 20. Chapter 4 : Functions Or Stored Procedures: okay, And this, really, we're going to talk about functions in database. A function in database is similar to a function in any other programming language or is similar to methods in Java. All they do is they. Villela was to write set off instructions, and these instructions are associated with a name, which is a function name, so that later on we can actually call or execute those set off instructions by using that name. It's as simple as that. So here are some of the advantages which I've listed, first of which is there won't be a need for sending multiple database request from the application logic, which means, let's say that you have multiple SQL statements to be executed, then you don't have to send request for each and every escrow. Clearly, it can club all the SQL statements into one function, and then you can just simply call that function with just one request from your application logic, so that will actually improve. The performance functions would also make testing a lot easier, because we can now run those function directly from your database software instead of having to run the entire application, so that would make all life a little easier. Also, functions will improve the performance because the first ember eggs good the function it's compiled. Abortion would be stored in the cache memory and later on, for for the request to the same function. The compiled abortion will get executed instead, off re translating everything so that will improve the performance. And this is the very reason functions are also called as stored procedures. They're stored in compiled form, which are ready to run. But there are also some disadvantageous and using functions. The first off, which is switching to another database software would need a lot of technical expertise, because sometimes these functions are written in other programming languages like C Pull Python etcetera. So in order to switch to a different database, the developer may need to learn a new technology or eat to hire a person who is super good at those technologies to be able to make them berg in another database software, which is quite a task and time consuming. Also, typically, the majority of the database operations that we perform or that we ever need are crowd operations, and in order to perform these simple crowd operations, writing functions is definitely an overkill. So let's take a look at how we go about creating a function. Here is the syntax for that he would use the create key would to create the function and optional. You can also provide this keyword or replace, which means, if this function was all the existing, then we want to replace that function with newly introduced logic, and then you provide the name of the function. This could be whatever you wish. We'll also have arguments associated with the function just as functions in any other programming language, and similarly will also have a written type. And he would specify that using this keyword returns and this section as variable is specific to see language. When you're writing this function using C language, that's where this will come into picture. Currently, since we're not concerned with C language, you can just simply ignore that. But the language that we're going to use to write or function is peel PDS school. Now you don't have to worry if this is a new language that we need to learn, because this is very easy. All it has is bunch off key words just as you can see here, we'll talk about this in a minute. So similarly also got other programming languages like pull python and a few other. And then your pride, the body where you would have declarations. Action very would initialize your variables and declare them etcetera and written this Begin and end. You would have your actual logic. You also have the written statement and you would use the return key word. You would add a return, a variable which is declaring the regulation section maybe are a simple literal And then in the end, it would provide the language that you have used by using this keyword language. So just try to map this particular syntax with the example in here. In this function, all I'm trying to do here is on accepting one. Indeed, your argument under stinker menting it by one and returning the final value. Which in this case, is that the name final some has simple as that. Just take a look at this and see how things are going here that's copied this and go to our PG admin and select the database where I want to run this Cleary and let me past it down here and let's run it. So this has created the stored procedure and you can actually see it getting reflected or here as well. That says, Add 10. Now, in order to call this function again, you could just simply say select and then the function name Add 10. Let's pass some integer argument and run it by hitting a five. Did you have it? You can also actually do the same using the user interface that PG admin provides. All you have to do is to right click on this functions create function. You'd good some name, Let's say add one. We're just simply going to increment the value by one click on definition. The written type is going to be in digital, in my case. And here is where our logical girl. Let me just simply copy this section off code and pasted over there. You also include the end, the key word as simple as that. Now you can actually see everything getting reflected in this SQL as well and thereby you'll be sure that things are in good shape on it now. Actually, there is no point in using a variable here can just strike Ever used this written keyboard here just as so and everything will be taken. Care looks OK. I don't want to set any options, but we do have an argument which will come in here. I'm at it. It's going to be off type in de jure Azriel. The name of the argument is going to be madam one or whatever the name that you want to do . But make sure you would have the same thing here as well. Let's call it argument one instead off Badham. One thing that's all there is to it Just walked through all the fields in different tabs. And once you're satisfied with that, you can just click save. OK, Looks like we have a syntax error. Okay. Looks like the default language is a skill that has tried to change it to P. O. P. G s school and try to save it. And this time it worked. So instead, off at 10 I would say, and one let's run it and it worked. So this is the way you go about creating a function from the PG admin again. Try not to use the comfort off user interface. Try to type down the function, thereby you will learn better. Let's go back. So similarly, we can also include and SQL Statement as part of the function. Just as you see here, we're trying to get the average age of all the students combined together, and we're starting the result into a variable that were declared here. So we use this keyboard into to be able to store the result into a variable. Let me copy dysfunction and granite an RPG admin and it looked, Let's try to use this function by saying Select and then we'll call that method. Let's run it So this is the average age. Let's go back. We have another example that demonstrates that a function can also return the table continent. This time the written type is going to be off type table and then you would want to provide a couple of fields and these will should map but the fields not just specifying your query . So student name and this name off type text they both must be compatible. Similarly age and message off top indigent And in here I'm just simply trying toe get those two fields from the students table But this time I need to use the ski would return query to be able to return the result and then back to where dysfunction is called. So let's run this in RPG admin. It worked. That's copy, and he was that function. So this region's all the student details. So all the different types off key words that I'm talking about are part off this programming language. Be OPD SQL again. It's very simple. You don't have toe brainstorm too much. We can also have constants declared. In that case, you would need to use this keyboard constant, and then, instead of simply called Sign, you would have a colon and unequal sign an example of what you can see here, representing by as a constant with 3.14 as its value, which nobody can change. We can also have control statements in this PG pl SQL language, and it's pretty straightforward. Only have to do is to use this key words if condition then exited this else if condition and then he would execute this etcetera and finally would say and if similarly, can have a care statement. This is similar to switch case in Java you would say case and then some variable are a constant. If the variable is banana, then we go ahead and execute the statement tested Sweet beach sore, etcetera and finally would end with end case as simple as that. We can also have a looping construct like this while the count is less than whatever is the number, he would say loop And you would keep looping until this condition fails and then finally would say and loop as simple as that. And finally, if you would like to get it off the function, you would use a statement drop function. You're toward the function name. Shall we try this? Let's copy this and go to PG admin. That said drop one and he would no longer see it who are here. Okay, that's it. On functions and typically as a Java developer, we would interact less with dysfunctions because most probably will be working with frameworks. But nevertheless, it's always good to know these concepts. This might come in handy at later point off time in your life. So see you in my next radio 21. Chapter 4 : Triggers: in this video, we're going to talk about triggers. A trigger simply is an automatic call to a function when a certain database, even Dockers an example often even could be an insert, for example, inserting a ruin to a table or delete or update or trunk it. We haven't talked about trunk it, but it essentially does the job off what a drop key would would do. But the differences, for example, drop table would delete the entire table. But if we say truncate table, it would just delete the rose in it, not the complete table. That's the difference. But this does set off events, which you can specify as part off a trigger. So when one of these events occur, we wanted to trigger or call a function, which we right along with that, we can also specify when that function should be called. Should it be before a certain even Dockers or after a certain even doctors are instead off meaning instead of performing the normal operation what inside dilator a bit would perform ? We would instead want to execute the logic in our function. In that case, he would specify instead off so in order to create a trigger, you have to follow the fallings in tax, you would say create trigger and then you do the trigger name followed by when that function should be called and on what? Even on which table you'd provide. The table name here and you were also specify. Should it be, should they even be associated with the row or a statement? If it is with the road, for example, every road that is inserted in the table, you would call a function or if it is statement every time you perform an insert statement , he would call that function. So you get to choose header off them and finally would say execute procedure. He would specify the function that you want to execute. So let's go ahead and take a look at an example and for the sake off this example, what I'm going to do is I'm going to set a trigger along with the function which that trigger would be associated with. And every time a new student detail is an turn, stood in stable. I would like to create an entry in another table which will store the student joining debt . You'll understand better when we take a look at this example. So first of all, let us create that table which will have to feels, which is to indictee. And then the date of joining. Let us run this and he would see that they were getting created. We have that over here, so we have it here. And what comes next is willing to write off function. And if you observe what it is doing, it is just simply inserting some data into the table that we have just created. So these are the two fields which we have in the table student I D. End Dodge did of joining and whose values are new dot i d. Basically, when you call this function using a trigger, you would actually have access toe the road that is being inserted into students table and you would be able to access the data that is inserted These in this reference new and then you would specify the column name off the students stable. The current time stream would just simply put the trend date and time, so this would be the joining date. The moment a data is entered in student stable. We're also going to add entering to this table along with the current times tramp, which essentially means that would be the joining date of that student that has a zoom. And then you would say, return new all this keyboards a mandatory. And in fact, if you observe the written type of this function is going to be a trigger, which means we're going to use this function as part of a trigger which is coming next. So this is how we would go about defining the trigger. But before the letters run this in RPG admin letters and dysfunction is now created. But if you take a note now, this function is reciting in a different section that is, trigger functions smart part off a normal function, so you would have to call it associating this function with the trigger. And here is how you would define the trigger. Basically, this follows us in tax that we've talked about, and in my case, every time there is an insert event on the students table, and I would like to call my function after that insert even happens. So I said, create trigger a good name, perform a certain action for each row instead of a statement. Execute procedure and I provide the name of dysfunction hasn't blast that. So let's run this and we're going to create a trigger all well and good. Now let's start insert some data into a student stable and see what will happen before that . Let me make sure that there no entries in student joining the table and you see nothing. Now let's try to insert something in here in the students table I'm going to say is, I'm going to say some name and I'm going to provide some age. Let's keep 24 and some description. He is going to be whatever and went public Save Now, since we've created a trigger for the inside event, we should see some data getting populated here. And sure enough, we have the student i D, which is the idea of the student which you have just inserted, and then the current date, which would be the date of joining hope that makes sense. See you in my next video 22. Chapter 4 : Schema Objects: in this video, we're going to take a look at some of the objects present as part off a schema. Now, when I say schema, I don't mean to say the logical schema which designers will use. I'm talking about the physical schema which is here have already mentioned about physical and logical schema in my earlier videos. So essentially a schema, I mean, the physical scheme is essential, like a folder that holds various types off files. In here we have this default schema, which is public and is holding all these different types off objects. And we're all the river or some of these objects. For instance, we got table objects are we have functions. In fact, you can actually create a new schema, and that would be like creating a new folder. Let's give it some name, you save it and now you have to scheme us again. You would be able to see all the objects that are responding to the schema and also note that whenever you're tryingto create an object using the Wizard, the auto generated quote actually, for example, I'm trying to introduce a new column New table here and the SQL is actually is very formal . It is saying the schema name dot the table name, and this is how you would actually create at table or run any other SQL queries. You would use this scheme on name just as you see here, so that's about the schema. But no, let's talk about various different objects. Present threat. Let me just delete this quickly. We don't need it. First. Let's talk about domains. Creating a domain is more or less like creating a new data type with some constraints. Where Michelle What? I mean, let's try to create a domain using this wizard. Let's call it new domain. The definition is where we provide the data type. It's going to be off type indigent, for instance, and constant is what makes domains unique from a normal data type. Here. We can actually specify Let's give it, Ah, any name, Let's say cones or whatever, and you have to say values. This is a key word or value. This is a key with that in to use here to provide your condition, the value should be, say, less than 30 file. Let's I want to put a restriction on my student stable that there shouldn't be a student whose age is greater than 35. So I can certainly use this domain on the field. And here is the Inspector SQL Statement. Create domain with some additional data. You can always click this question mark in each and every desert that you come across with so that that will take you to the documentation off the desert. Or, if you'd like to take a look at the Heskey awash in off doing the same thing and you can click this I icon and that would take you to that place just as you see here. Looks like a distinct while anyway can check it if you wish. So I'd like to save this and let's try to use this domain that were created. Let's close closed. I've been trying to create a new table. Let's call it, uh, some name when Dad Two columns. One is I D. And I'm going to make this cereal to auto generate it and I'm going to add a mother field age, for instance. And this time I'm going to choose the domain which have just created you would see it getting populated over here and Let's click. Save now Let's try to add an entry into this table. I would be fresh and you would get this. Currently, there's no data as you expect. Let's open the quit it, too, and entered some data in it. I would say Insert into some name age values. Hey is going to be, say, 25. And let's end the statement on the Cleary and it works. Well, let me just try to insert a rally that is greater them 35. This time it fails. Value for domain Nudelman violates check constraint. Cons. So that essentially means we're able to put some restrictions on that column. And let's take a look at the columns and you don't see that. So that's where the main will come into picture. Let's do it. The stable. Now let's talk about materialized views. Remember, we had talked about extracting the data from another table and creating a new table. But using create table table name as and then the query, for example, you would say create table some name as and you would pour the query select star from students That's run it, and this has created this table with the content which is currently present in the students table. All well and good, but this is going to remain permanent. We can't refresh this table and get the latest updated values from the store in stable, for example, for add an entry in students Table. Let's say when died Uh, 45 and some name and some fee and click Save the same thing will not get reflected over here. Pretty obvious, but we can certainly make use off of you. We already have our view created in one off for videos. So let us try to use this. Let me do sell it star. From my view, that's run it now. The data that you're seeing here is fresh. The moment I click this button to execute the query, the view is going to actually run the internal query that we had set in the view to extract the Quran data. So if it make changes to the students table, it would get reflected here as well. But coming to materialized views it's going to be different. It is similar to create table as but the difference is we can all this update the data with the current data on demand remission. What? I mean, so I took on this. I'm going to say M view for metalized view. In the definition, I went to provide my query. I would say select student name and age from students as simple as that. And then here is a resultant has skill. Again, this is something that we can use to create a materialized view. You would say, create materialize view, and then you would provide all the details. And do they not off distinct here with no data? Meaning we're creating a metalized view without any data in it. If there is no data in it, we cannot view it. Let me show you what I mean. It's our public sale installed my view, I would say m view he can't view its data. It shows than ever has not been populated. So we need to populate the data. And the way you go about doing it is you would rather use this command refresh materialized view, command, and then you would provide the view name when we just quickly do it. So you would do this now This would petrol information from the students table and it gets populated alternatively, can know to do the same from here by saying Refresh view with data. So no, let's try do first that data and you would be able to see. Of course, it's supposed to be M view. So here is the data, and if I make updates through the actual Stewart unstable, it won't get reflected here on July. Demand this view to be refreshed with the updated data. So that's about the materialized views. And then we know about tables. Let's talk about sequences. We already got couple of sequences created. Basically, these are the sequences Rive used in students and course table. These are kind of part of generated, if you notice in both these tables. We had actually used the key word cereal on her i. D column. The serial is going to generate auto generator values incriminated by Valley one, and this is where it is defined. If you go to the properties off the sequences to go definition for courses table the crane values for because they're just for interest in that and the valley is going to get increment it by one for the attic. Calm off course, and the minimum value is one. That's where that's a starting number for the sequence. The maximum is going to be this. The Cash Valley is amount off ideas that will get auto generated and get stored in the local cache memory, so that will basically contribute in performance. Not very noticeable, though, but you can increase the value if you wish. But let's try to create a new sequence. Let's say that I want to generate a sequence where the number will get incremental did by three. In the definition, I'm going to provide the increment value. It's going to be three. The starting value is going to be, say, 10 minimum values. 10 Maxwell use, say 1000 account exceed more than 1000. Let's say I would say Kasher to be one, and here is a skill to create a sequence. Again. You can click this two icons at the question mark that expense about this reserve or this I icon. The talks about ah, the create sequence command that you can run either in PG admin or BS Cure. Let's see of it and try to use it. So let me pause the video and create a query quickly to save your time. and then I'll get back. So here is the query that I'm going to use to create a table which uses that sequence. I'm going to copy this and pasted in or query processor. So as that create table new table, we're going to have a couple of feels idea and the name the ideas off type indigent in this primary key and the defaults to the cereal that you have just created and do taken note of the ski would next. Well, you have to use this keyboard with, ah, the name of the sequence that you have created and the name is off type text that's run the query, and we seem to have some problem. Let's get it on this semi colons here and it booked. Now let's try to insert some data in it and when to use. Insert key word for that, and I'm going to just insert the name because I deserted generated. Based on this video, I'm going to insert multiple values. And if you take a look at the data that gets populated here and the sequence looks like I opened the wrong table, let me refresh, and here it is, okay, looks like, for some reason or sequins didn't take effect. Let's see, for configurations are intact and unfortunately, our conflagration seemed to have not saved. So let's make changes to this. An increment by three. So let's save it. Minimum value is going to be, Let's keep it to one. Maybe I have given something wrong in the previous prompt, and so sequence defaulted to the default values. So let's save this this time and try to insert the data again, going back to our insert query. Before that, let me just get it off the existing data. Thank God populated and two had delete. And then let's from this query, And this time it worked. So that's about the sequence. You can define your own sequence if you wish, and that's pretty much shaped. But we haven't talked about some of the other objects present in here, because whether they are to add once at the moment and is majorly meant for database administrators, people who dedicate the carrier working on databases, for example, if you're talking about after years or full text search, it is sort of way of configuring the text search mechanism, and that is useful for search engines to efficiently find the date and is specifically more useful for Web applications. Whose date eyes were you up to date? For instance, news channels. They would need to enable all these features in order for search engines to efficiently find the updated daytime their website. So this would definitely is not going to help us and coming to types. These are some advance data types, for example, and numerous in type etcetera, composite type again desire off. Not very relevant for I developer like us and coming to foreign tables, it would best Korea love you to populate tables from a different database, for example, to go to definition Here is very appropriate departments ever. Now this several could be an Oracle server Oracle database over my skill or whatever it is , you could populate its tables in read only mode so that we can work with it. So that's it on all these objects. I think we've talked about the primary ones. In fact, we're talk more than what is necessary to be honest. So that's it on this video. See you soon 23. Chapter 4 : Database Objects: in this video, we're going to take a look at various objects present at database level. We've talked about how we can create a new schema and its objects. Similarly, we can create a new database and months to do so. You would see all these objects getting populated in that new database, and the way we go about creating a database is either by using this wizard. Let's give it some name or you could use this SQL statement created a base to do the same and want to click. Cancel. Let me walk you through all these various list off objects present in here. First, let's talk about casts. Gas is similar to typecasting in any other programming language. Let me just open up the query tool by selecting the database. Let's do that. I wanted to typecast this literal, which is off type. Text to you are car. This would obviously work. By the way. This is how you would typecast in Post Grad school. You would use this couple off colon's, the source type and the destination type, So basically this will work because these two types are compatible or in the terms off post Greece cure. These two types are binary compatible. But how about trying to convert text to Belene? Now? This is obviously not possible because these two types are not compatible. This would try an error. This is where cast will come into picture. We can create a new cast that will actually convert text to Belene. But it is our responsibility to provide the logic that does that task. So first of all, before we create a new cast went to create a function that's going to take the source data type as a perimeter, do something with it, and finally return the destination data type. So I'm going to go inside our default schema and create a new function. I'm actually going to copy the existing court from one of the existing functions just to save a bit off time and create a function. Let's call it as DF and I'm going to return, um, a bull in value. True, for instance, the written type is going to be Belene now, just to keep things simple, I'm just simply returning a bull in value in real time. You would have something meaningful here. Maybe you would like to do something with the string that comes as an argument. Let's add that argument here. Argument is going to be off type text that's name it as arc or whatever. And here is the final Lescure. We have the source type as an argument, and we're returning the destination type. What's like? Save that? We seem to have some error. Sustain the programming language to P O. P. G. A skill and, like save now we can go ahead and create a cast. And what that does it is. It's going to convert the source type text to decision, Taibu lean, and it will take help of the function that we have just created is DF, and if you look at their skill, this is how you go about creating a cast. Create cast, followed by various options can defer to the documentation by clicking this high icon click save. Now let's try to run this query, and sure enough, we're able to convert this text to Belene, so that's where casting will come into picture coming to catalog. This is similar to a schema. In fact, these two words are used interchangeability. If you expand the catalogue and get inside it you would notice that you would see all the objects, what you would see in a schema, but you can't really work with them. For example, I cannot create a new function. You can think off catalogue as a kind of a broader view office kema where not only will this have the functions that we have defined, but also the functions that are external. For instance, if expand this section, he would see quite a big list off functions which will help us perform various tasks in our database. So that's about it. You don't have to worry too much about catalogs. He can work on it much. The even triggers is sort of similar to the triggers we had talked about in schema. But these triggers will come into picture not only when we're dealing with tables, for example, adding a row into a table or inserting daring to the table, etcetera, but also at the database level. Let me right click on this and take a look at the desert and see what it offers. And if you notice it is allowing us to associate at trigger function when any off these events happen. So maybe we wanted to perform something when we try to create a database or when they're trying to create a schema so you can put that query in here and a particular function will be triggered. It's as simple as that coming to extensions. These are set off extensions that you can add to enhance the capabilities off post. Great. Ask your We already have this couple off extensions, which we've been using so far. Pl PDS Cure, for instance, will help us work with this language. Similarly can add See other languages, like C language extension to be able to work on the same. We also got admin pack that would help us with some administrator tasks. Similarly, we got host off other extensions provided by Pose Greer's. You can actually afford to the documentation in and see what each one of these extensions does. The foreign data rappers is sort of similar to being able to see external data from external data sources, maybe from other databases or from external file system. And not only do we read the data, but also we can perform right operations with current configuration. But this is a little advanced, and I personally never get to work on this section. The languages has just simply set off languages, which were supporting, if you'd wish, you can add more. And that's about the stop objects President Despot off a database. And let us also talk about the stable spaces. It's pretty simple. So these are the places where the actual data is getting stored. In fact, we had talked about this in one afar introduction videos. Wherever these two reciting said that the disk is full, then we want to switch to alternators so you would get some name. In the definition you report the location where you would like to continue storing your data, and once you do so, you can actually point the database to store its data in that location. In the definition off the database, for example, you get an option to choose the table space. All right, that thing that will summarize on on objects present in database see you in my next video 24. Chapter 4 : DDL DML DCL TCL and Postgresql Commands: in one of my videos have mentioned that we'll talk about some of the terms like DDO Deimel , Ortiz CEO. Well, in fact, we did talk about all of them in our previous videos. The only thing is that I didn't tell you that we were talking about them. My statement would make sense after you listen to this video video stands for data definition language and is mentally meant for defining the database, like the database itself or its relations. For example, if I use the create statement to create a database or a relation like create database or create table, then essentially, I'm defining the structure of the database. Similarly, from trying to make changes to the structure by using the keyword alter. Maybe I wanted to alter a table to add a new column. That means I'm altering the structure off the database. So all these kind of commands, like create, alter, rename, say, rename it table or drop it table or a database. They all fall under data definition language. They help us define the database altogether, coming to data manipulation language or D m. L. They will help us. Manu Plate that data in the relations, for example, lets her like to insert a data into a table. What I would want to update existing data in the table or delete some data from the table? Are you select to be able to view the data etcetera. Now this role set of commands, which have taken as an example off data manipulation language, which would help us manipulate existing data inside the tables. But they won't let us actually alter the structure of the database. I'm not adding here any column or anything of that sort and coming to Decio or data control language these will deal with allocating the privileges to the users are roles. For example, let's I would like to grant certain privileges to this particular user. Or maybe I want to revoke certain privileges from another user after I find him misusing his rights. So all this sort off commands that we had talked about that deals with the the user creation or are a group creation or a procreation? Then we're actually deferring to data control language, and finally we have transaction control language, and we have already taken a look at an example. I come it roll back. They all fall under this transaction control language, so just be aware of that. And I also want to point out some of the commands that you can run in PS skill now in here , I haven't listed some of the commands that will help us create a table, create a database alternate table, etcetera. They're all they all come under structured query language, and we have all that it discussed. But these are set of commands which are popularly used. For example, if you say slash l, that would list all the data basis. Let me just show you what I mean. Out opened this connect to a database and how do slash l and that lists all the available data basis. Similar. We got other comment, For example. He could use this command slash D to look at the definition off. Whatever you provide here, maybe I would like to take a look at the definition off students table. Then it displays just that, including its triggers. If its existing just go through all these, it's pretty self explanatory. I'm also going to make this file available for you to don't know so that we can take a look at it. All right. See you in my next video. 25. Chapter 4 : Other Comp And Help: in this video, I'll try to uncover some of the other competence president as part off PG admin and try to understand their purpose. Why their existing the value that they're going to add etcetera. Honestly, this is something that I didn't plan to cover, but we all dairy when beyond what is planned when we talked about all these objects. So I thought, Why not talk about the remaining components President BG admin so that our course will be a complete course. So let's try to understand. First, let's talk about this dashboard in here, all the status sticks that looked like a stock market. Graphs, etcetera. They're just showing all the rial time information, and these graphs would actually make sense when you have a huge amount off transactions happening in your application. At the moment, we don't have an application that is using this database, and so the one many transactions. So you're not seeing any kind of a fluctuation in these graphs, but in real world you'd be able to keep track on number off silver sessions. That application has initiated a number of transactions that took place over a period off a second etcetera number off table rows that are being inserted and number off rose are being read or two pills that are being read. Also, it's sort of information, and below is the table that shows all the past sessions. You can sort of take a look at it and get a sense off who has done what the locks is, something that we had talked about acquiring a lock for a relation etcetera can keep track off them as well Again. This would make sense, especially when you have a huge amount of transactions that are taking place in your application. And still the sections ready much. Are there for the very purpose to get it sort of like a past history or the summary to get your sense of what's going on in your application. The properties part is similar to what you would configure when you're trying to create a database. For example, if I choose and depending on the type of object that you select, the properties panel will change for choose the database, it's going to show its properties. So, basically, whatever you're going to see here, for example, the table space, etcetera, same thing is going to get reflected here, and you can actually do the same stuff that you would do in the properties panel when you right click on an object. So that's about it. The Eskil parties shows a SQL that has actually helped in creation off this database again , this is specific to the object that you select. The statistics section will show a summary off all the information. For example, you'd be able to see number off people's inserted so far, the number off triples off, fetched, updated or deleted, and all that sort of information dependencies. You may not be able to see any dependence is when you choose database. But when you choose a schema, prince stands the public skimming. Then you would be seeing something getting populated and same thing goes for tables as ville. It's I would like to select the students stable. You would notice that this is sort of like a summary where this table is dependent on well , don't think of dependency as the dictionary, meaning off dependency. In this context, it's going to very different. This just means that this is their off objects that are kind off using this particular table, for example, If you're talking about function, then we did use the students table in it. So basically all such kind of information you would find here. So that's about this section or this particular panel in this PG admin. Let's talk about the menu items here. The file menu item is similar to a file menu item in any other application. For example, if you just click that, you get to configure some preferences, and this is somewhat synonymous to preferences in Eclipse, say that you would like to change some display settings or change the user language to something else like French, Russian or whatever. So all that sort off things you confined here. Maybe you'd like to increase the fund or change the color, etcetera. You can also result the layout in case if you have missed with it. And next comes the objects, and depending on the object that just select the many would change captains of selected a table. You're seeing this menu but finesse. Select, say, a schema or a database. You're going to see a different menu item. Basically, whatever you see, when you right click on a particular object, you would pretty much be the same. So this is kind of like an alternative way off dealing with objects and its properties. Nothing really fancy. The tools are set off tools, which would actually that you perform a verity off things, and all these tools again would make sense. When you have an application running behind the scenes, manipulating all the data in your database, then you might want to make use off. Some of these, especially backup, is something that is pretty important. For example, we can actually take a back up off a particular database or ah, table and then in Justin gives if something goes wrong with the database weaken, sort off restore the database back to its original state. For example, I can just take a quick backup if you wish. Let's call it is dear, for instance, and click backup. By the way, go ahead and try to explore other options present as part of the back of panel. For example, if you take a look at this, you can quick cava quick. Look at all these different options we have. In my experience. I just used to take a backup Occasionally. Basically, this task would be taken care by data. Best administrators out my job as a developer, to be honest with you, So let me cancel. So here is Ah, our completed activity off backup. You can click this click here for details, and then you would be able to see the location off the file. It's in users. Admin document is DF, so let's close this or let's navigate to that directory It's in. Users have mean documents, and here is the back of fire. You can also choose the kind of file that you'd like to create that you find in that back a panel or wizard or whatever you call it. So that's about it. And let's try to do something. Let's take a little risk and try to delete one off these tables. And let's if we'd be able to restore what we have deleted accidentally. Someone to choose that file I went to provide. I want to choose this file and select restore. This might take a while. I doubt if this is going to work because I believe we have to choose the database in which I would like to run. Oh, it's successful. Let's refresh and looks like things. Okay, well, it didn't restore really looks like. So let's use the database and click on Restore. And then again choose the file and click on this store. Let's see if this is going to work and it failed. Let's take a look at the error. You can see all the sequence off activities that took place to perform the restore operation, and he would notice that somewhere down, the landlords find this error saying that function add joining that already exists with same argument type. Okay, this means that we're trying to restore a function, which is all that existing. Let's see if we can do something with the restore options we have. I like to do restore again, which is the same file and then in destroyer options. Let's see if we have an option to sort off all right, existing data. So we have this option clean before it's store. I believe this would just wipe out everything from database and tried to recreate everything from scratch. So strived that and click restore. And, uh, we do have another problem. Okay, Looks like this is just simply complaining that the table that we had dilated is not existing already and so it had to replace it. Something of that sort. Let's try to refresh and see if things will take effect and expanded tables. And sure enough, we have that new table populated along with its data. Usually back up and destroy. Operation is predator boast? I don't know why it is, Ah, throwing so many errors and it looks intimidating for beginners. But trust meters post grazes pretty robust application. So, like so you got other tools with which you can do a lot of things. You can initiate the maintenance activity again. This would make sense when you have a huge amount of transactions taking place with huge amount of data like maybe you'd like to share index everything for efficiency is sick and also sort of things coming to help. There are actually multiple locations where you can explore. For example, if you have any country in with any of the key words used in, Was Greece killed and you could just simply go to this website and there you would find a search field. You can just go ahead and put your keyword, for example, select. Then you can choose its documentation and see a lot of things that you can do with the select query. Similarly, something can be seen from the Post Grace Shell. Let's open it up and it's log in. Type in help. You'd get various options here, so if you type in slash hedge, you would get help for SQL commands. So let's do that. So this is a list off a skill key was that you can use to use your database. Similarly, can type in slash question mark to get you help for various commands. Japan Drum in the spear scale shell, for example, slash Q Till quit. Let's do that. Terminated the job? Yes. Also one of these years. Where to get help is by clicking one of these objects. Prince stands a table. You can use this to icons. You can pretty much see these two icons in every problem that you get in. PG admin. The I icon will take your the SQL Help their skill command that you can run to sort of work with that object. Hurrican. See what all can go inside this panel or disprove romped by clicking this question Mark I can and Justin, because if any off these help options doesn't help you adjust your problem. Then you can certainly make use off. Google are. Stack off with another website where you're pretty likely to find a solution for your problem. May be the problem that you're facing now. Might have already been asked by somebody in stack overflow. So it's a pretty good website, and often if you search your query in Google, it would take you to stack overflow, lastly or like to mention that SQL is pretty last. It has almost a dictionary off key words, so it's hard to fit everything under single course. But that being said, what a make sure is that we have covered all the frequently used keywords and escrow statements. It is somewhat similar to learning a new language. Say that you're trying to learn English, then you don't tend to learn each and every word in the dictionary. You're just simply learn all the common words, which are frequently used for your day to day activities. Similarly, we have code the Eskil keywords that would help you as a Java developer, but just in case, if you're very curious about some of the other keywords that we might have missed. Then you can go toe this website w three schools search for a secure and you would be landed in this page. This website is not tuned. Proposal be Eskil, meaning that some of these queries are not meant for post rescue. If you're straight of a copy this query and use it in your post grease, then it may or may not work. But the interesting thing that this website offices you can experiment with structured query language In general, you can click this button, drive it, try try to yourself, and you can experiment with their database. They have a sample database with some sample content so that you can experiment with it with your SQL queries. You can just type it down here, run SQL, and you would see the result. You can experiment with pretty much all the keywords president in SQL. I believe they might have covered all of them and in fact, we might have already discussed, say, 80% off these already. But Justin guests, if you're if you're curious about the rest 20% you can experiment them over here. So don't take this website for learning purposes. In fact, I find the information is too simple. Firing often doesn't describe exactly what each one of these commands does, but, as I mentioned, use this website as a tool to experiment with structured query language, but not to learn. SQL our post grease in general. All right. I think that will suffice. See you soon. 26. Chapter 4 : Database Design: in this little driver house on quick understanding on how we go about designing a database . We're also going to go through some of the good practices we can follow to make sure that the data in the data base is maintained inconsistent manner and that there no do will get interest scattered across multiple tables in the database. So the first thing that into consider while designing a database is to have a thorough understanding on all the requirements for your application and then try to visualize all the real time transactions that are going to take place in your application. The role of the database in it, etcetera. You would basically try to understand where those kinds off crowd operations you would perform on your database for your application needs. And this is a phase where you could make or break things. Because if you're not careful enough in understanding their comments thoroughly, then that's going to cost you a lot at later point off time in your project, in terms off time spent as well as a number off resources required to fix day shows. So you need to be very careful in understanding the requirements and then you'll try to visualize the database fiscal in your brain. So once you're done with that, once you have a clear picture on how the application works, you would come up with a list off tables for your application. As an example for a school management application, you may be having all these list off tables. We have students, table teachers, stable courses, etcetera, and once you're satisfied with that, you can move on with the next phase where you would try toe, create a relation scheme off a region, every individual relation or tables in your database. For example, we have this picture here which have picked from one awful previous documents. We've already talked about this diagram, So each and every individual block you're seeing here is the relation schema. So you were thought off. Create all these relations, scheme us along with their constraints, for example, to take a look at this relations. Chema marks. We see that the mark Heidi is marked as the primary key, and then we got a couple off foreign keys as well. Similarly would try to figure out a list of constraints that can be part off each and every individual table, for example, for students table, the student idea is going to be the primary key email and phone number has to have a unique constraint like wells for quarters stable, you would have course ideas the primary key, and you may have student ideas form key etcetera. And once you're done with that face, then you would ultimately create the big picture, the entire database. But connecting all these relations key mass with these connectors and that the relations between them could be anyone off these oneto one relations one too many relation or many to many relation. We've talked about all this street we've actually taken. Look at some of the examples for all these three nor previous videos and bears down that you would design a database schema that looks something like this and notice that one too many has, Ah, the symbol that looks like three spikes. We talked about it as well, and then once you have this entire database schema, you would then conduct a meeting with your customers are with the developers, etcetera, and then you would discuss out what's happening in here, take their input and possibly make some changes in here, etcetera. So you would basically go through all this for a second and again until we have a very mature database schema that we can finally finalize to bring it to implementation. So that's about designing the database. And then we have some of the good practices, which are sometimes termed as normalization is a set off techniques that you can follow. These are actually the techniques that are tried and experimented on the relation database by many people to make sure that the data has maintained in a consistent manner across the database. So the principal off first normal farm, or sometimes referred as one enough say, is that a relation must have a primary key, which uniquely identifies each row. And as a good convention, we always choose the i D field as the primary key. And the reason by winter do society is for the very reason to make it a primary key for each and every individual table are. Alternatively, as we discussed, we can also have multiple attributes are multiple columns together, be a primary key as well as you wish, and the rule of first normal form states that every collonville store atomic value, and there are no repeated groups. For example, if you take a look at the stable in here in the column, courses were trying to store multiple values like, for example, the students under has registered for Java and ESPN were separating those two courses with this coma character. This is not, I mean, this table is not following the first normal form in the scales because it says a column bill store atomic value, which means we should have only one value, not the way you're seeing here. This is going to cause some problems because at later point of time, if Sunder decides to choose another course, or if he was done with one off the course, then we need to make updates to the sentry in the stable, which is quite tedious. You would get to know it once you start using the database. If you use database before then, you would certainly know the importance off maintaining atomic values and columns. If you didn't use the database before then, just believe in what others have tried and experimented. He never supposed to have values like this is supposed to have only one atomic value, but If there is a case, how are we going to represent that? Sunder is actually registered with multiple courses. I'm going to talk about the solution in a second, but here is another example off a table that is not following the first normal form. You're not supposed to have repeated groups like this, for example, for each and every individual course have introduced a new column, and I'm setting the flag in the scare. Soon there has registered for Java and GSB, and he didn't register for SQL and DB. Emma's John has registered for DBM misson sq, but not for Java and GSP. Now this is definitely bad practice because at little one of time, if you wanted to introduce another course, then you would end up adding a new column in here. And that's going to bring a lot of complexity. And in fact, it may result in actually changing the logic Gassville the application logic to make it work with new changes. Basically, as a rule of thumb, it should always minimize the need to change this actual structure of the database. You don't want to change the definition off the database are the tables, so to solve these two problems. We have a solution which is basically what we have used so far. In our examples. You introduce another table that looks like this and you try to map the tables. And here we can say that Student one has a district for courses one and two, and similarly, a single course one has been registered by these two students one and two. This is many to many relationship. Similarly, we have second normal form as well, and it stays that all the non key columns should be fully dependent on the primary key. Even if the primary key is made up off several columns or, in other words, every non key Colin shall depend entirely on the primary keys and not part off it. I'll try to give an example from this from the stables itself. If you take a look at the stable, we're trying to associate the fee with a student, while should we really do that? It actually depends on your application needs, but we can actually move this fee column from here to this table student course stable, and then we can say student one register for, Of course, one and then he paid a certainty again, stood in to register for course one, and he paid a certainty. And if we choose these two columns as the primary key, then we can say that the fee column is actually dependent on this too student as well as the course I D. But not part off it. That means the fee column is actually fully dependent on the primary key. It's not dependent solely on the student I D or the core society, but it is dependent on combination off this to the full primary key, provided we choose these two columns as the primary key. And it actually makes sense because we have to have somebody who pays the fee to endure for a course. Only then will be able to have some fee. Well, I'm just trying to give an example, but I think you got the idea. Similarly, we got your normal form would just simply means that all the non key columns are dependent only on the primary key and not anything else. Imagine that just for the sake off this example. We're trying to introduce another column that say's eligibility. If the students ages girdle and 35. Then we're going to set the eligible to stables with the flag, basically, yes or no, in our case, although students are eligible. But just imagine that we have another column here, and we're trying to set this eligibility status. In that case, our table is not following the tor normal form because our eligibility column is dependent on the age column, which is not a primary key and asked for a definition off third normal form. It says non key columns are dependent only on the Primerica and not anything else. So that would be a bad practice if you introduce such column, and I believe this is sufficient. And definitely I think this video is definitely going to help you at later point of time in your carrier. So I think that's it. I think we've talked about pretty much everything. See you soon 27. Chapter 5 : JDBC Intro: hold it. We already had some understanding on Jerry BC We've taken a look at how to set up the enrollment, to use the Jerry busy drivers etcetera and in this very well thought off, try to talk on the same lines. So we have our application logic and say that we have all these three data basis from different providers like posed area school, my SQL or SQL Server. For instance, no letter that I wanted to talk to one of these databases From my application logic here, Java can actually provide us set off libraries or classes that can let us talk to your specific database. For example, we could have set off classes that will help us talk to oppose greedy the base. Or we could have another set off classes that will help us talk to the Maya skill database . But the problem is to more. If there is a new database that gets introduced in the market, then Joshua has to support that as well. There has to be another library return just to support that. In addition to that, even from programmer respect to once I write the code that talks to a specific database using the class libraries that are relevant to that database. Two more. If I would like to switch to a different database, then that's going to cost me quite a lot off a fort because I have to change the names off the classes and methods everywhere in my court, which is quite impossible. So to address this problem, we have the Jerry B. C A P I Java Database connectivity application program interface, which is nothing but a bunch off interfaces whose implementation is provided by these database providers. So the a p I that I'm talking about is basically set off classes and interfaces that are part of the package java dot SQL wants to import this package. You get access to all interfaces in it, and their implementation were actually provided by the driver jar files provided by these individual database providers. For example, if you'd like to work with post great skill, then you would download its corresponding driver jar. You would place it in the Silver Lib directory so that your program can actually make use off those driver libraries to communicate with special database. And that's the very reason why we had to download pose. Great. Ask your specific drivers and copy that jar file into our service Live directory. And in our application logic. We used this package and we wrote our code using interfaces and classes that are part of this Jerry B. C. A. P I. So two more. If I'd like to switch to a different database, then I don't have to make too many changes in application logic. Instead, I'll just delete this jar file from my library and put this jar to talk to my school database. So let me quickly walking through set off classes and interfaces that are part of this J D B C a P I. So if you go to the official Oracle Side Dark, start oracle dot com and search for this package, you would be able to see these list off interfaces and classes that are part off them. In fact, we have used some of these interfaces and classes already in our example that have demonstrated how to connect to the database from the application logic. So here we have. Ah, the interface is like we have a connection to face which we have used to maintain connection with a specific database. And prior to that, we have used this class while driver manager to actually help us connect to the database by using the jdb. See, you are Let me show you what I mean. So here's our code, which I have written. Henman offer examples in the beginning. Now we know that understand all the rest of the court in here. You must have gone through my course on GSP and so let's. But here's a section that is off relevance. So first of all, you to copy the driver jar file into the applications Live directory once you do. So you need to execute this following statement that this class dot for name and you would provide this saying that you wanted to load this driver onto the JV m so that we can now connect to the database and then you would use this class driver manager and you say, get connection. You provide the you are oh, the euro has to follow certain format into, say, Jerry BC hyphen was great skill for post visit the base for my skill. This could be different. Maybe my ask your and then you would provide the I p address and port number wherever your databases running the name of the database and his credentials with which you can actually access the database and perform crowd operations. So once you do that, the statement will actually return an object off the class that implements the connection interface and later on using that object you can actually call this method creates statement that will get you this statement object, and then you're good to go ahead and execute any query off your choice. The execute query is an interface method that is part of the statement interface, and its implementation is present in the driver jar file that you have downloaded. It will have the largest that will actually process this instruction our escort statement and get you some results. Whatever the result that gets sent back will be assigned to this result set, which you can then sort off, travels through all the list off rose and then display them. As you wish, just as we're doing here. Once you do that, run your application and here is the output how it's going to look like now again in order to know how to run this application then you need to go through my course on Jsp. And so let's otherwise distant gives. If you haven't gone through it, then just get a sense of what we're trying to do here. Pretty simple and straightforward. Similarly, just try to walk through all these list off classes and interfaces and exceptional classes that are part off this package. Jabara Rescue. Try to get a sense of what they're doing. In fact, most of these are something that we don't use frequently. But nevertheless, it's always good to understand. And this will definitely lay a solid foundation for learning frameworks. All right, see you in my next video. 28. Chapter 5 : MVC Architecture: in this video, we're going to talk about model, view, controller architecture or simply embassy architecture. In fact, we're going to go ahead and make changes to our code to make it fit for NBC architecture. So what is embassy architecture? It is one of the design patents. So now what is a design? Patton at Design? Patton is actually I tried and experimented way off, logically dividing the code so that it will address a certain problem. We have many different design patterns, but we're not. The popular one is the MBC designed Patton, or sometimes referred as embassy architecture. In fact, we will mix multiple design patterns to address multiple problems. But in this video, we're just concerned about one specific design pattern, and that's M. V C. So before we talk about it, let us try to understand the problem we have with the current code in this code were actually mixing multiple things. We have a piece off court that actually helps us connect to the database and to to some information. We also have a piss off court that is majorly meant for presentation purposes. I mean, the client side technologies like esteem, etcetera, so they're mixing. These two technologies here along with that, were also including the court that deals with the database. Well, this is OK in our simple example, but in real world, when you have thousands off files in your project, it becomes very tedious or, I should say, impossible to maintain the code because we want multiple developers working on their specific technology that they're good at. We don't want to mix all these three different sections together and create problems. This doesn't let the dollar purse work seamlessly, so the solution is obviously the embassy architecture. So what we're basically going to do is we're going to take out the code that is specific to database into another section or another file, and we're also going to take out the view part or the HTML code has part off a JSP file and the soul. It is actually going to act like a mediator between the JSP as well as the data best code. So let me go toe white board and try to explain what I mean. So here's how our current example is written. We have the data best code history. McCord has allows the business logic written under a Soviet. So what we're going to do now is we're going to take out this data best code and put it in another file. We call it model because we're sort of dealing with the data best model. And this, while will now have the cord that help us connect to the database as a less perform the crowd operations on the data base. Similar. We're going to actually take out this view court or the presentation code or the code that helps us with the presentation aspect off for application. We're going to take it out and put it under a JSP file, and we call it view because this is what the user would actually view on the browser and the business logic would remain in Seoul. It So now we know what is a model and what is the view. But what is controller ourselves? It is actually going to control the flow off our application. So here's how it is going to work. Like once the Euro request is placed, the request would first land on the sole it the logic inside the soul. It will then decide whether the request has any need with the database. If it is a case, it's actually going to fetch the required data from the database. By using the moral logic, it would connect to the database as well as do necessary operations with the database and finally get some response. Once it does that, the soul that will also take care off transferring that data that needs to present it to the user by making it part off a para meter to the view section, which will be the JSP. So now eat one off these modules will have their individual responsibilities. The model is taken. Care off the database. The JSP are the view part is taken. Care off the presentation aspect off your application and the soul. It is actually going to act like a mediator or the controller, which will control the flow off your application. This would make sense once we go through the example. So let's go back to our eclipse. I'm actually going to create a brand new project and do everything from scratch because it's not easy to digest for first timers someone to create a new dynamic where project that search for it. I went to call it First NBC application and click finish. The first thing that I'm going to do is to create the model. Let me create a new class file. I'll call it. It was Grease. Dow. Now what does this Dow stand for? The house stands for data access. Object. Once you create an object off this class, he can use that object to access the database. So that's what it meant. Data Access. Object. It's a good naming convention with softer dollar purse use for these kind of class files, so let's click finish now. The first thing that I'm going to do is to introduce a constructor that's going to accept the connection para meters that will help deposed with driver connect to the post grease database to perform more tests. So let me pause the video right here and introduce a few fields. So I have these four feels defined, which will be Excalibur, the connection para meters. Now let us go ahead and use the eclipse feature to auto gender. The constructor for us went to go to source generate constructor using fields. I'm going to choose all of them. Click OK, let me give my court proper for mating beheading control shift f I mean poured this class file which is missing. Now the next thing that I'm going to do is to introducing method that will fetch all the student details from the posibles database that is currently running. So our matter would take no para meters but would return list off students. So now, in order to create list off students, we now need to create a student class. Let's do that when directed here, new class. I'm going to say student, this class will actually help us hold student information temporarily for our activities. You will see how it is used in a while. Click finish. So we have these two files here. So now this student class for will have the fields that corresponds to the fields in the students table we have in database again. Let me just quickly pause the video while I write all the fields. So I've created this four fields. Let me make use off eclipse building feature to create the constructive for us which will accept all those fields so that now, when you're creating this object, we can also pass in some details like i d name, age etcetera to create that object also would like to introduce some ghettos and settles for all these fields again, it's always better that you use the art of generate feature so that you don't make silly mystics. So we got everything set our student classes ready. Now we can go back end, right that mattered. So it's public. It's going to return list off students. I went named the method as get student list, hoping in closing curly braces you to list. Okay, there is quite a bit off court that's going to go in here, So let me just save your time pausing this video and I'll walk you through the code. Okay, here's our code. Were just simply doing whatever we have done in our previous example, trying to fetch all the student details by this query and before we're trying to execute this query were actually connecting to the database by calling this method, which is also written by me. And in the end, once we're done with the operation, we're disconnecting as well. So here are the two methods to connect a data base, which is is simply lowering the driver as allows creating the connection object by using the driver manager and in this kind were just simply closing the connection. Pretty simple and straightforward. And in order to see if this is working, you can just simply create a class well with public sadiq void main, create an instance off this class and try to call this method. If that results in extracting information from the database, which in our case is going to believe star students, then it's working. So that's how you test. I didn't do that because I think this will pretty much will work. We've already used the same court in our previous example, so I'm quite confident about it. But if something goes wrong, then I walk it through what we can do about it as well. So the next step that we're going to do is our controller logic, which will be a soul. It So let's create another class file and let's call it so lift controller hit finish. Well, actually, I should have just created a solar that would bring those methods by default, immigrated off this and create a Soviet look finish. So this is what I was talking about in the new project. We don't have our soul that jar imported, So let's do that quickly. Bill Part Had external jars search for Soledad FBI Helped by and close Let me get it off all this unnecessary junk. It's unnecessary for now, in real world, Cummings do have importance. Well, again, let me this pause. The video doesn't clean up. Also, I would like to introduce the method that will actually help us connect to the database. Isn't the Dow plans that were created? So here's a cord. So during the initialization face off the soul it we're trying to connect to the database, but there's little modification that I would like to make is basically we're trying to accept the connection as an argument. We don't need it, so I'm just simply getting rid off it. And same thing goes here as well. Let's have the file now. What comes next is I'm going to introduce piece off code that will call this method to fetch all the student details, and those student details will be sent to the JSP page where we present those details to the user on the browser. So let us introduce the JSP file into a project. So I'm going to say new JSP file. I'm going to call this as JSP View and like, finish again in order to save your time. Let me write the code and get back. All right, so everything that you're seeing in here is very straightforward, but this is the court off significance. We're trying to look through the student list now. What is the student list is just simply the request attribute that will have all the student information. And we're going to populate this attribute in our soul it by fetching the data using the data access object. So let's do that in here. List off students. Student list equals on when to make use off this object. Get student list. I mean, port. The list interface is going to throw an exception. So it's try captured. Now I'm going to say request not said attribute. Let's give it some name, basically to give the same name what you're trying to read here. So let's copy invested here, and this will be the value. So now we're going to use the request dispatcher to forward this request to this jsp along with this attribute so that we can then read all the student details and render them one by one, because dispatcher equals request dot get request dispatcher and then you'll specify the name of the JSP file view dot Js be and then simply dispatcher dot forward request and response. All or nothing, this would suffice. Let's clean our project hard to simply run, since we're doing it for the first time going to run this application random. Several. It's going to It's going to take a while, Okay, as usual, eclipses and knowing at times. But so let's that's tried to clean the project and tried to run again and hopefully to work this time. And as for amounts over all right, sure enough were able to list all the students. So although the first lands this looks like we have actually increased the number of files and number of lines of code, this approach will actually be a lot beneficial in large scale projects. I hope that makes sense. See you soon 29. Chapter 5 : Mini Project Walk Through Part 1: in next few videos, I'm actually going to walk you through this many project. I'm calling this application a school management application, and this is kind of like a enhanced Washington. For previous example. In our previous example, we have seen how toe populate all the student details but in this application were actually going to perform all the crowd operations. So this application would basically list all the students of Elburn the school. You can add a new student by clicking this button. Insert that particular the space very well filled. The digitals off the new student and you click this button once you do so the same will get reflected in the stable, similarly, can also make changes or make edits to an existing student. Maybe wanted to change his description or age or his name by clicking this button edit, and then you will make your updates for that student. And likewise, we can also perform delete operation. Once you click this button, he would no longer see that student getting listed in the stable. This is fairly simple application, and I didn't pay attention to cascading style sheets. I didn't pay attention to the styling aspect off this application if you wish, you can do that. The reason why I didn't do that is because it's going to increase the number of lines of court off our GSP page. Well, we could actually create a dot CSS violent link it to this page. But I don't do that. I see no purpose in doing that, so that's not pay attention to the presentation aspects. For now, as it's a very simple job, we're going to pay a lot of attention to what's happening behind the scenes, what kindof actions that are being taken place in this application, etcetera. And as I mentioned, this is ah is going to follow the model re control architecture. So we have the dead access object and now I have introduced a lot off other methods to so our purpose to perform the crowd operations. Similarly, we got a controller the Soviet, and this time we got quite a bit off a court. Here, I'll walk you through each and every bit off it. We got two pages to JSP view pages. One is the one which you have seen the home page, and once you click, either edit or add you would see this page. So let me just first talk about dill it as it is, one of the easiest one in here. So this is a paid that gets rendered and using this competent for each were looking through all the list off students and populating them. And obviously this list is populated from the Soviet and the Soviet would, in turn, will take help from the day Iraq's object to access the data from the database, retrieve it and present it back to the view. This is something that we're we've already seen. But what's new here is this deal it operation. The beauty about Jsp is that these components, these custom components, are actually aware off the country that is populated in it. What I mean by that is, for example, if you click delete here, then the competent is a wear off this particular record. So the student object that we're referring here will have information off the student on whom were pressing the delete button. So this to an object will have the current student details. And then we're trying to access the students. I d the same. We're sending it as a para meter were actually calling the soul it with couple off para meters when his task, which is which no cases delete, will see what's going to happen so that in a second and then the i d, the current store indictee for whom I clicked, delete. So now let's go to the solar it and, you know, logic. We have a switch case, and if you go to this case delayed, it's going to actually call this method deal. It's student forwarding, both request and response objects. So let's see what's going to happen there. So I pressed on control button and click this method that would take us to that mattered location. So all I'm doing here is I'm trying to get the idea of the student that will send as a perimeter in here and isn't that idea? Um, actually trying to delete that student from the database and I'm going to use the post Greece dow object to perform that operation. So I'm calling this method get student of the dark object to get the student details, so this will actually return this student object. So if you go to that village student method again, I press control. And then I click this method that would take us to that location. It's going to accept this student object, and we have a piece off code that will actually delete that user from the database using the standard SQL query and what's new. Here is this prepared statement. The prepared statement will actually love you to place some placeholders, which will be a question mark. And then you can later populate or replace this question mark with something else. It can be better demonstrated in here. I'm playing to update a student information here, and I have a question mark for student name, age description and the i d. So I'm able to fill all those placeholders in here in the same sequence. So when I say set string won a match, you're referring to the first place holder. And I said to I'm referring to the second place holder. So in here were populating the age and so third and fought. So that's about the prepared statement. So using that, we're able to delete that particular user, and once we do that again called, comes back to the soul, it and we're just simply redirecting to this so let so that would basically render the same page, and we would see that student record getting deleted. So let's perform that operation in here. I want to delete this guy, John. Let's say we look like Dill It and sure enough is no more. His are off our school. He is now well settled. He got a new job. So that's about the deal. It maybe we'll continue from next radio. 30. Chapter 5 : Mini Project Walk Through Part 2: All right, let's continue. Let's talk about the headed behavior. So once you click this button, look what's being passed from here, observed the euro that is getting displayed on the bottom left corner. But Jules, this said it. It's going to say I D 68 which corresponds to this student. Similarly, if I hope for my mouth on some of the student, the I D is changing. So basically, this competent is actually a bear off the data that gets displayed in it. So let's see what's going to happen when I click edit for one of these students. Let's I want to edit this first guy bell. I went to click him. Do take a note on the perimeters that are being sent the euro para meters when his task, which is it's edit. In 1964 I click this and I'm taking to this page and behind the scenes. Those two para meters will help us navigate to that page. So let's go to our soul let and here in our switch gears you would see edit. And that would just simply get this student from the database, storing it in this new object and Then we're setting a request, a tribute with the name student, and later we're forwarding the request to a new page at its student dot jsp page using the request dispatcher. So once we go here, this is suggested page. Where will be navigated? We're checking to see if the student para meter is now or not. If it is not know, that means we are indebted page. So we have said that Param Eter in here before editing that particular user. And the interesting thing about this page is that we're trying to use the same JSP page for both add or insert as well lasts for a day. But we're having these conditions. The if conditions. If the student details are populated, that means we have pressed edit because that's when we're trying to provided this. Attribute their cost, attribute verses in ad. We're not providing anything of that sort. So we have all those fields here to fill in the name, age and description, and we're going to perform. Submit it. I'm going to change his name to is DF and age to 34 description to something else. And when I click this button, add or update. What actually going to happen is not only are we submitting all these details, but also we have a few hidden fields. When his i d which we don't want to change, we want Daddy to remain same for the same student. So we made that hidden. It won't be visible to the user, but this will be submitted as part off form request and will be sent to the Soviet. Similarly, we have another input field, but very simple value update in gets off head it and in case off insert, this is going to be insert. We're going to use this to in our soul it and do something with it. So let's good or so let So what made Just click this button and make sure see, it got reflected with the same I d that I didn't change. But all these details are changed for that student. So you know are so let here we have it update. So I'm going to go to this method. All I'm doing here is unjust trying to read all the form details that are submitted, including the idea which was a hidden field, and then I'm trying to create a new student object and I'm feeling it to this method of great student. The update student, as we have looked at, will have this ah SQL statement that would update that particular record in the database. So once you do that, we're just simply navigating to that soul it that would render a fresh list off students. It's as simple as that and insert would pretty much work the same way even Dad, some details and sage to be 32 description. And when you click add, you would see that new student getting populated with a new identify air and to see how it works. We've already talked about it. So instead of update in here, the hidden feel is going to populate insert as a task with all the new store in details and then from this section, Inter student were trained to again create a new object student object using the information that were submitted and then we just simply saying in such student and this method will again have that Eskil statement along with this placeholders which were using ah depopulate the relevant data later point of time in and we're sort of inserting that student information in the table and same gets reflected, in our view, very simple. So that's about the simple application. The idea off walking you through this many project is to get your sense off how the real World Model view controller application works and have also demonstrated all the database operations that you would have a perform the crowd operations on the student table. Actually, when you start writing the code, he would face challenges. So I would recommend that you not just listen to me, but rather go ahead and try to do the same on your own. And then you will have someone. The flight challenges, which you can then debug and learn from it, holds it sort off things. Also one thing that I would mention that you better not special this para meters in the cell vit. Let these perimeters go inside the deployment descriptor and read these para meters from the deployment descriptor. That's a good practice because two more if you'd like to switch to another database, let's say, then you have to change this euro inside the soul it But whereas if you put it outside in the Web dot xml. Then you can just simply update the XML without having to edit the actual job decor and then compile it, build it and then deploy the application. So maybe a list pause the video, make those that it's and show you how it works and that we will have a complete, full fledged satisfying application. So I've created this file web dot xml under the ribbon of directory and then I populated with the falling content. These are set off context para meters. Best qualities are digital busy pal emitters. What we had in our So let I moved them to hear and then from So let I'm actually going to read the same from the web dot xml and rest all remains same. So now at later point off time, if you want to switch to a different database, you can just simply upgrade this para meters and point to a different jar specific toe. That new database you don't have to mess with the source cord. Hope that makes sense. See you soon 31. 12: So the $1,000,000 question. What is Jonah? The simple answer would be. Java is a programming language and the computing platform. What exactly does that mean? Let us first understand what platform ease. Let's say you bought a new computer and as a friend off yours, I give you a few Softwares and games. I'll give you one photo shop, Chrome rosa and need for Speed Risen game. Would you be able to install these Softwares or play games? No, because your software's does not know your computer and your computer does not know your software's They both are strangers to each other. Now we need to some 100 years both the strangers to each other and develop friendship between them so that we can play games. So we need to hire a guy who does a job and you guesses his none other then the old rating system like Windows or Lennox. So if you see operating system is acting as a platform on which we can run applications or play games now, I hope that you got some idea on what black for me is. So just as operating system is there to run applications like photo Shop benefits and Crume Java platform is there to run Java programs or job applications Except Java Platform is installed, a new operating system just as any other software. And this is the very reason why. Job, I said Toby Portable will talk about portability later. Remember, I said, Java is also a programming language. These job applications must have been developed using Java programming language. So, depending on the context, you need to deter mined. What Joe is Java is a platform on which you can run Java programs or applications developed using Java programming language. Now let us take a look at some of the categories off programming language. We have high level programming language. These are somewhat closer to human readable language and our hands is it to learn? And is it to court? Most of the high level languages are portable or platform independent, which means you can write your program once and run it anywhere without having to worry about the operating system on what you're running a court. One setback, though, is that high level languages are relatively slower compared to low level language is one major reason is due to the conversion process which converts the court that you write the court. That mission can understand. This process abstracts lot off low level details for the convenience of the programmer. This would be minimal in case off low level language so more the languages grows up to human natural language, the slower it becomes C plus plus Java PHP or some of the examples off high level language coming to low level language. These are somewhat closer to mission language. I'm in zeros, and once and hence humans find it difficult to understand and to code. These languages are often dependent on platform, so the developer have to keep in mind on which offering system is really willing to run his program. So if you write a piece off court for Windows, there is no guarantee that it runs on Lennox. Developers have them and the court to get it working for all operating systems. And since these languages or close to mission readable language and my liver from humans, they run faster compared to high level languages. Assembly language is one good example, and middle low language lies in between high level language and low level language, and their nadir close to human now computer. This stain between the two. A good example would be C language. Although you can write core, which is somewhat closer to human datable language, it can also access member using pointers. Or you can even write us available cordis and see. You must be glad that job is considered as high level language, though.