SQL : The Hitch Hikers Guide To Writing SQL Queries

SQL : The Hitch Hikers Guide To Writing SQL Queries

What is SQL?

SQL:

Hi Guys. Welcome to this interesting article on SQL So in this we'll mainly understand the different concepts related to SQL. 

So we'll start today's article by understanding the data definitions language commands in which we'll understand what is relational database management system.

How to normalize your database management systems, how to create, delete and alter database objects. And also get an understanding about the  different types of constraints present in database such as create, alter and delete.

Once you understand the data definition language commands, the next topic would be the data manipulation commands. So in the data manipulation language commands would basically understand how to enter and update the data in existing tables using SQL commands.

Apart from that, you will also understand how to delete data from single tables,

and also how to fetch and show the data from databases using various kinds of commands. And finally we'll end this topic with the SQL operators such as comparison, logical, and so on to get an understanding of how you can play around with your databases.

After you get an understanding about the data manipulation commands. We'll the next get into how to retrieve data from multiple tables.

So in this part of the session, you'll basically understand the different types of joints, present in SQLs, that is the inner left, right and cross. And after you understand the joints, the next topic would be the inbuilt functions in SQL.

So in this topic, we'll basically look into how to use the built in functions and SQL and also understand what is group by clause and having clause.

And finally, we'll end the session by understanding how to create advanced database objects in which we'll mainly focus on the store procedures, functions, and triggers. So I hope the agenda was clear to you guys.

So let's start with what is RDBMS. RDBMS basically stands for, relation database management system. So basically in RDBMS we'll define the data, or we store the data in to collection of tables, which is a two dimensional database.

So as I mentioned, it's stored the data in tables and the tables have rows and columns. So some of the properties of relational database, as you can see in the screens, it's Values are Atomic, Column Values are of the same thing. Columns are undistinguished.

Sequence of row, how the data is stored is of insignificance. They can store the data in any sequence and all the columns have a unique name.

Most of the database which we know as of the like Oracle, MySQL, all are relation database management system, and SQL is the common query language for all database management systems.

As we discussed that RDBMS is basically we store data into collection of tables. So then we store the data and collection of tables, it is very important that data is organized properly. So we applied the concept of normalization for the better management of DBMS. 

Normalization is a process of organizing data to avoid duplications and redundancy. So we apply normalization to database systems to minimize duplicate data, data modification issues to simplify queries because we don't want to run into complex queries when we fetch the information from database.

If our database is not properly normalized, then we may face issues when we try to update any records in the database. There are various rules for normalizations, but generally we follow third normal form in order to organize our data efficiently.

So we have first NF that is first normal form, second normal form, third normal form and the end we have Boyce and Codd Normal Forms, But it is enough and it's sufficient if we satisfied up to third normal form. 

Okay, so what is first normal form? So as for the first normal form, as you can see on the screen that each set of column must have a unique value. It means every column should have a single value in it.

On the screen as you can see in the left hand side we have our students table and this table has three columns, student, age and course. 

But in the first row as you are seeing on screen that we have two values for a single column: CR001 and CR005.CR001 and CR005. Because of the multiple values in a single column, it is violating first normal form. So in order to satisfy first normal form, we have to split the row into multiple rows.

So after I splitting in the right hand side you can see that we have split the row first into two rows, Adam 15 and the course is CR001. Adam 15 and the course is CR001, and another ways, Adam 15 CR005.

So we have split the multiple values into single values and now our table is satisfying the first normal form. Let's move to the second normal form, as per the second normal form, there must not be any partial dependency of any column on primary key.

So first of all, what is primary key? So primary key is a key which helps us in identifying a unique row in a table. Most of the time, the primary key is a single column, but sometimes more than one column can be combined to create a single primary key.

Now as you can see on the screen, our table was not satisfying the first normal form. So we have split into two rows.

So in this table we cannot consider student as a primary key because as I mentioned that the very first prerequisite of a primary key is that no two row can have the same value for that key.

Here in the first column strength, we have Adam as two entries of Adam. So a student is not a primary key. So in order to make a primary key, sometimes big continent, or add one more column to our primary key. 

So in our case we have student and goes as a primary key. So our primary key generally contains a single column, but sometimes aspect of business requirement. We may add multiple columns in the primary key. 

So in this table, student and course together have a primary key. Now, second normal form states hat in order for a table to be in satisfy the second normal form, it first satisfy the first normal form. 

And after that all the columns should depends only on the primary key and they should not partially dependent on the primary key.

So as of now, and you can see in this table that column age, only depend on the student and not on the course. So this is the basic introduction on SQL hope you enjoyed it!

Validity 1 to 10 days

With Certificate