Catching on to the concepts of SQL with fundamentals

Introduction:

In my previous blog, I discussed the applications of SQL by solving the case study- Danny's Dinner. Now in this blog, we will learn the concepts of SQL, which will boost your understanding and help you in clearing the interviews. If you are a fresher in Data Analysis, want to expand your knowledge in SQL, or preparing for interviews as a Junior Data analyst, this blog can help you to cut the interviews.

In this blog, I'll delve into the concepts related to SQL, Oracle, MS SQL Server, and MySQL databases which will assist you in grinding the basic concepts in databases. So without wasting any time, let's get into this...

Understanding DBMS and its different types:

Let's begin with a simple question- What is a database, a database is a structured collection of data. A Database Management System is a software application that interacts with the user & database itself to store and analyze the data.

The DBMS allows a user to interact with the database such as modifying, retrieving, and deleting data of any type strings, integers, images, etc- stored in the database.

There are two types of DBMS:

• Relational Database Management System
• Non-Relational Database System

Relational Database Management System(RDBMS):

A relational database system is a set of applications and features that allow users- IT professionals to develop, edit, administer, and interact with relational databases. Most commercial relational database systems where the data are stored in the form of tables- Structured Query Language(SQL).

Now, here is the small question- are DBMS & RDBMS the same? Well the answer is no,they aren't same. Here are the key points about the distinction between DBMS and RDBMS:

• A DBMS can only handle one user at a time, whereas an RDBMS can handle multiple users.
• A DBMS requires less software and hardware than concerning RDBMS.
• Data is stored in a hierarchical format in a DBMS, whereas RDBMS uses a table with headers that serve as column names and rows holding the associated values.

In-built functions in SQL

Let's proceed with the use of in-built functions in the Database Systems......

1. Self-Join: A self-join is a type of join that can be used to connect two tables. It mostly combines and compares rows from the same database table.

2. SELECT: A SELECT command gets more rows from more database tables and it's the most frequent Data Manipulation Language command (DML).

3. WHERE clause: filters records that are required depending on the conditions.

4. ORDER BY clause: is used to sort the data in ascending or descending order.

5. GROUP BY clause: group the entries with identical data.

6. CONCAT: is used to join two or more values together.

7. RANK(): defines the rank of each row within the ordered partition. If both rows have the same rank, the following number in the ranking will be the previous rank plus several ranks.

8. DENSE_RANK(): assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. It always indicates a ranking in order of precedence.

9. NOW(): will fetch the current date and time both in the format ‘YYYY-MM_DD HH:MM: SS’

10. CURRENT_DATE: will fetch the date of the current day ‘YYYY-MM_DD’.

11. JOINS clause: Combine rows from two or more tables, based on a related column between them, and merge two tables, or retrieve data from there.

• Inner join: most common type of join. It is used to return all the rows from multiple tables where the join condition is satisfied. Left Join: Left Join in SQL is used to return all the rows from the left table but only the matching rows from the right table where the join condition is fulfilled.

• Right Join: used to return all the rows from the right table but only the matching rows from the left table where the join condition is fulfilled.

• Full Join: returns all the records when a match is in any of the tables. Therefore, it returns all the rows from the left-hand side table and all the rows from the right-hand side table.

Keys in Relational Models

1. Primary Key: a set of columns in a table that uniquely identifies each record in the table, ensuring that every row in the table has a unique. For example, consider a table called "Customers" with a primary key column "CustomerID". Each row in the "Customers" table will have a unique value in the "CustomerID" column, allowing you to uniquely identify each customer.

2. Unique Key: a constraint that ensures the value in a column or a set of columns is unique within a table, just like a primary key. However, unlike a primary key, a unique key can allow null values.

3. Foreign Key: a column or a set of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables, known as a parent-child relationship. For example, let's say you have another table called "Orders" with a foreign key column "CustomerID." This column references the "CustomerID" column in the "Customers" table. By using a foreign key, you can link each order in the "Orders" table to the corresponding customer in the "Customers" table. This relationship ensures referential integrity, as you cannot have an order with a non-existent customer.

Conclusion

As above I explained the basic concepts of SQL, generally asked in the interview perspectives. I haven't covered the entire SQL query and I've already provided you the SQL case study of Daniel's Dinner, so you can refer to it as an example, and might help in exams and interviews. In the next blog, I'll start with Machine Learning and python with some brilliant case studies. Thank you enthusiast readers to go through this blog!!!

Happy Learning!