All things you need to know about RDBMS & SQL

This blog is for anyone who wants to learn databases , SQL from scratch , to revise concepts for tech interviews . In this blog we will cover almost all topics of RDBMS and SQL .

What is RDBMS, What is tables , columns , rows , multi valued -composite-derived attributes, primary key , foreign key , candidate key , super key How to insert , update , retrieve , and delete data from tables SQL joins

What is RDBMS ?

RDBMS is a program to handle relational databases . MYSQL , Oracle are based on RDBMS

Consider the case of designing a database for an ecommerce system .

Database :

A structured set of data . DB is used to store data for a particular system in a structured way using tables and designing schema .

We will use programiz site and their default database for practice purposes .

CREATE DATABASE databasename ; // To create database
CREATE database university ;

SHOW databases ; //To list down all available databases on your server 

USE databasename ;

Tables:

is a set of related data , it consists of rows and columns just like an excel sheet. One table contains information about one entity .E-commerce system will have customer information , orders information and products information . Product , order and customer are different entities .

Every table has predefined columns in it , for Customers table this are the attributes

1. customer_id
2. first_name
3. last_name
4. age
5. country

Attributes :

Multi-Valued attributes : Which contains multiple values eg : mobile number can have multiple values

Composite attribute : Attributes which can be divided into multiple components . address can be divided into country , state , city etc

Derived attributes :Attributes whose value can be derived from other ones . eg : If you know the date of birth and current date you can find age .

Key:

Attribute or set of attributes which can uniquely identify a row in a table , it is also used to establish and identify relationships between tables .

Primary key :

A key which can uniquely identify the tuple or row . In the Customers table customers_id is a primary key . Primary key can not be null . So while creating table make sure you are applying primary key constraint on a key which will always have some value

Superkey :

An attribute or a set of attributes which can uniquely identify a row . Any attribute among a table along with the primary key is a superkey .

Candidate key :

A minimal superkey is a candidate key . Meaning : a minimum set of attributes that can uniquely identify rows . In a Customers table is first_name + last_name +age minimal ? Can it identify row uniquely ? Yes ! It can . But is all three attributes necessary ? No ! Set of any 2 attributes among this 3 can uniquely identify a row . Hence it is not not candidate key . eg : first_name + last_name & first_name +age can uniquely identify rows . There can be multiple candidate keys in a single table but a single primary key primary key.

Primary is also a candidate key & superkey

Candidate key is a superkey but vise versa is not necessarily true

Primary key is a subset of candidate keys & candidate keys are subset of superkeys

Foreign Key :

A key which is a present in both the tables and is the primary key for one table is a foreign key for another one. Foreign keys help to define relations between two tables . In our database, Orders table have customers_id as a foreign key , this key maps every order tuple with the customer with which order is associated.

How to insert , update , retrieve , and delete data from tables

Insert :

Every table has constraints , data types associated with attributes . We can’t insert data with the wrong data type . Datatypes are integer(n) integer of size n bits , varchar(n) string with variable size max n characters, char(n) string of fixed size n . Orders table have primary_key constraint on order_id hence we can't insert same order_id again . Try hitting same query two time . it will give this error

Error: UNIQUE constraint failed: Orders.order_id

Open programiz online compiler and start practicing queries .

Insert into Orders values(6, "Mouse" , 500 , 3 );

Inserting Multiple Values :

Insert into Orders values(7, "Mouse" , 500 , 4 ),(8,"Monitor", 3000, 2)

Retrieve data (Select Statement):

select * from Orders; // To select all columns  from table we use * 

select order_id , amount  from Orders; // To select specific columns there names should be mentioned

select order_id , amount as amt from Orders; // here "as" is used for aliasing

There are different clauses to further process the data we get from SELECT query

1. Where Clause : To apply conditions on rows .

   select order_id from Orders where amount > 500; // "where" clause is used to apply filters on data with a condition
   

2. Order by : To order the output in ascending or descending order with respect to a column

   select customer_id , age from Customers order by age;
   

3. Group by : To group rows and find data following the same attributes . eg : To find the number of customers from each country we can use group by as -

   select country , count(customer_id) as customer_count from Customers group by country;
   

Aggregate Function

MySQL's aggregate function is used to perform calculations on multiple values and return the result in a single value like the average of all values, the sum of all values, and maximum & minimum value among certain groups of values. We mostly use the aggregate functions with SELECT statements in the data query languages.

select item , sum(amount) as total_sales from Orders group by item; //To find sales of each item sum() is applied on amount and grouped by  item 

select avg(amount) as average_order_value from Orders;  // To find average value of orders 

select item , max(amount) as max_price from Orders; // to find item with max selling price

UPDATE data 👍

UPDATE is a Data Manipulation Language query . It is used to modify records in tables .

Let's say in our ecommerce system customers address changes so we should modify it in the table as well . Let's assume customer “John Doe” moves from USA to UK

UPDATE table_name SET column_name1 = new-value1, column_name2=new-value2, ... [WHERE Clause]

UPDATE Customers set country = 'UK' where first_name = 'John' AND last_name = 'Doe' ;

DELETE RECORDS 👍

A DELETE query is used to delete records .

DELETE  from  Customers  where  country = 'UK';  // To delete all records from country ‘UK’

DELETE  from Customers ; // This will delete all the records but not a table 

Drop table Customers ; // This will delete entire table we will have create it again

NOTE : DROP deletes the complete table but DELETE only deletes some entries/records which satisfies the condition (if condition not given it will delete all entries)

SQL JOINS 😀

Why do we need JOINS ? In RDBMS we have relations between two tables . Remember why we use foreign keys ? to establish relation between two tables . In our case Orders table have relationship with Customers table with customer_id as foreign key . You have given a task to find customer details for each order . How will you do it manually ? You will first go to Orders table , pick one order will check customer_id associated with it and now you will find which row in table Customers have that customer_id , this process will be repeated for every row in orders table. This same operation is done by JOINs in SQL .

There are 4 types of joins

• INNER JOIN / NATURAL JOIN

We have to find customers and orders associated with them . In this neither customer details nor orders can be empty .

select Customers.customer_id, Customers.first_name , Orders.order_id , Orders.item from Customers inner join Orders on Customers.customer_id = Orders.customer_id; // will get 5 rows

• LEFT JOIN

Left join takes all entries in left table irrespective of whether it's associativity is present in right table or not . In our case customer_id 5 don't have any order associated with it . Still left join will take this row .

select Customers.customer_id, Customers.first_name , Orders.order_id , Orders.item from Customers left join Orders on Customers.customer_id = Orders.customer_id ; // One row for customer_id = 5 will have null values for orders associated with it

• RIGHT JOIN

Similar to left join right join takes all entries from right table irrespective of it's associativity with left table.

• FULL OUTER JOIN Full outer join will take all entries from left and right table .