Mastering SQL Essentials: Unleash the Potential of Databases

WHAT IS DATA?

A set or collection of values presented in different forms, including text, words, pictures, audio, video, and other types is called DATA.

HOW TO STORE DATA?

• Traditional way i.e. on paper

• File system

• Databases

  • Updating and deleting data are far simpler in databases compared to file systems and paper.

DATABASES

A database is an organized collection or repository of data. It deals with interrelated data, enabling efficient retrieval, insertion, and deletion. Different queries are used for various actions based on their requirements.

DBMS

A Database Management System (DBMS) is an application that enables users to interact with databases, allowing them to create, update, and manage data efficiently. Oracle, Sybase, and Microsoft SQL are examples of popular DBMS software used in the industry.

TYPES OF DBMS

• Hierarchical DBMS

• Network DBMS

• Relational DBMS

• Object-oriented DBMS

Note - In this blog mainly we are going to study Relational DBMS

RELATIONAL DATABASE

It is a type of database where everything is stored in relations or tables

Tables have different columns and rows where rows are termed as tuples and columns are attributes

The relation or table also consists of some terms such as the primary key and foreign key. Let's view them one by one.

Primary-key

The minimal set of columns or attributes that uniquely identifies a row in a table is called a primary key.

Features

• must contain a unique value for each row of data

• containing null values is not acceptable

Foreign-key

Foreign keys are columns that match the primary key in other tables, creating relationships between them.

Features

• can be used to cross-referring table

• do not need to have unique values in the referencing relation

S.Q.L.

Structured Query Language (SQL) is used to interact with relational databases, enabling various tasks such as deleting, adding, modifying, and querying data. With SQL, users can manipulate and retrieve data from database tables efficiently.

There are various SQL software like

• SQLite (open source)

• MySQL (open source)

• PostgreSQL (open source)

• Oracle DB (owned by Oracle)

• SQL server (owned by Microsoft)

HOW TO INSTALL AND EXECUTE MySQL

1. Download MySQL Installer: Visit the official MySQL website (https://dev.mysql.com/downloads/) and download the MySQL Installer.

2. Run the Installer: Run the executable file to start the installation process.

3. Choose Setup Type: During the installation, you'll be presented with setup types. The most common options are:

   • Full Installation: Includes the MySQL Server, client tools, and connectors.

   • Custom Installation: Allows you to select specific components to install.

4. Configure Installation: Depending on your setup type choice, you might need to configure the MySQL Server, port, and other settings. Set a password for the MySQL root user.

5. Install and Configure MySQL Server: Follow the on-screen instructions to complete the installation.

6. Start MySQL Server: Once the installation is complete, you can start the MySQL Server.

7. Test MySQL Installation: Open a command prompt or terminal and enter the MySQL command-line client by typing mysql -u root -p and enter the root password you set during installation. This confirms that MySQL is installed and running properly

   

BASIC COMMANDS

• SHOW DATABASES: To show all the databases that are present or previously created. show databases;

  

• CREATE DATABASE: To create a new database. create database database_name;

  

• USE DATABASE: To use any database that is previously created.

  use database_name;

• SHOW TABLES: To use any tables present in the created or pre-existed database.
  show tables;

CREATING A TABLE

create table table_name(column_name1 datatype, column_name2 datatype);

To describe a table therefore there is a second command which is describe table_name

INSERT DATA IN A TABLE

insert into table_name (order of collumn_names) values ( 'something', 88, 88);

Now to view the table:-

ADDING COLUMN TO AN TABLE

• ADD COLUMN TO AN EXISTING TABLE

  alter table table_name add(column_name datatype);

  

  similarly, we can add multiple columns to the table

  alter table table_name add(column_name1 datatype,column_name2 datatype);

• ADD COLUMN WITH DEFAULT VALUE TO THE TABLE

  alter table table_name add(column_name datatype default data);

  

MODIFYING THE COLUMN OF THE TABLE

• CHANGE THE DATATYPE OF AN EXISTING COLUMN

  alter table table_name modify column_name new_datatype;

• RENAME A COLUMN

  alter table table_name change old_column_name new_column_name new_datatype;

  

• DELETE A COLUMN

  alter table table_name drop column_name1, drop column_name2;

SELECT

Select allows us to retrieve the specific information as per our requirement from our relational database. There are various types of select commands.

• select* - to select all the data contained in the specified location

• SELECT ONE COLUMN - select column_name from table_name;

• SELECT MULTIPLE VARIATIONS -

  select column_name1, column_name2, column_name3 from table_name;

  

LOAD, LIMIT AND DISTINCT

• LOAD: used to summon data or the file on which data manipulation is to be performed. source 'file_name.sql';

• LIMIT: limit the table to a certain no. of rows

  select column1, column2, from table_name limit number_of_rows;

• DISTINCT: gives the distinct values from the table.

  select distinct from column_name;

  

WHERE

Using the WHERE clause, we can select rows from the table that match specific criteria, similar to how an "if" condition works.

We can use relational(<,>,=,<=,>=) / logical( AND / OR ) / ( is Null / is not Null ) with the WHERE clause

FEW EXAMPLES

select* from table_name where collumn_name = 'something';

select* from table_name where collumn_name < something;

select* from table_name where collumn_name1 = 'something' AND collumn_name2 > something;

NOTE: WHERE clause is the most widely used clause or command which makes many complex tasks doable and without it data cannot be filtered or manipulated at will.

AGGREGATE FUNCTIONS

It performs calculations on a set of values and returns the single-value answer.

• COUNT -

  • count(*) -----> [ count no. of rows in a table]

  • count(column_name) ------> [count of non-null values in the table]

  • count distinct( column_name) -----> [count non-null unique values]

    

• UPDATE

  update table_name set column_name = 'column_value';(same value for whole column)

  update table_name set column_name = column_value where condition; (select rows to which the updation should apply)

  

• DELETE

  delete from table_name where condition;

  (will delete the particular row where the condition matches)

• TRUNCATE / DROP

  truncate table_name;

  drop table table_name;

  (delete all the rows of the table)

GROUP BY

• It is used to group rows that have the same value.

• It helps in summarizing data.

• GROUP BY clause returns one row for each value.

NOTE: Also In MySQL, when using the GROUP BY clause, all non-aggregated columns in the SELECT clause should either appear in the GROUP BY clause or be part of an aggregate function.

If not an aggregate function then should be converted to one, it will be clear with the help of an example.

SELECT MIN(Id) AS Id, MAX(FirstName) AS FirstName, MAX(LastName) AS LastName, MAX(City) AS City, Country, MIN(Phone) AS Phone FROM customer GROUP BY Country;

To explain how does the group by clause works let us see a part of a sample table.

Now, as you can see, there are multiple entries for some countries. For example, Mexico appears in both the 2nd and 3rd rows, and Germany appears in the 1st and 6th rows. When we apply the GROUP BY clause on the Country,only one entry for each particular country is displayed, as shown below:

The selection, by the way, works on a first-come, first-served basis. In other words, whichever row of a specific country appears first is selected for the GROUP BY operation.

Now to count the no. of appearence of countries,

We can use group by for two columns to get more complex answers like,

Here, the counting is done based on the combination of country and city. In other words, it shows how many entries exist for a specific pairing of country and city.

We can also use the WHERE clause to filter out more results in it.

HAVING

• WHERE can not be always used with GROUP BY in some cases it will pose to error.

• We have HAVING clause in the SELECT statement to specify or filter conditions for grouped results.

• In short when GROUP BY key is used HAVING acts as WHERE clause.

select city, count(*) from customer group by city having count(*) > 5;

ORDER BY

• It is used to sort the result set in ascending or descending order.

  • ascending by default

  • descending

    order by column_name desc;

IN

• The in operator is shorthand for 'OR'

• It is used to determine if a specified value matches any value from a list or from the subquery.

• select column1, column2, from table_name where column3 in (value1, value2);

  will return with the rows where column3 contains value1 or value2.

BETWEEN

• It is used to replace "<=" AND ">=" conditions.

• The values return record between values inclusive.

• Value can be no., text, or dates.

LIKE

The like operator is used in a WHERE to search specified patterns.

% ---> represents 0-x any no. of characters.

_ ---> represents only a single character.

_SS%': This pattern will match strings that have three characters, where the first character is anything, the second and third characters are both 'S', and the remaining characters can be anything.

_S%P': This pattern will match strings that have at least four characters, where the first character is anything, the second character is 'S', the third character can be anything, and the fourth character is 'P'.

S_%_%: This pattern will match strings that have at least three characters, where the first character is 'S', the second character can be anything, and the third character can be anything.

JOINS

• We can query data from multiple tables based on a related column which is in both tables.

• We should provide column and condition.

• Use JOINS in SELECT, DELETE & UPDATE statements to join tables.

• It is of 4 types:

  • Inner / simple

  • Left over

  • Right over

  • Full outer / full

INNER / SIMPLE JOIN

• Returns rows that are at least one row in both tables that matches the join condition.The image below shows and help us understand how the inner join works.

• 

• SELECT column1, column2, ... FROM table1, INNER JOIN table2, ON table1.column = table2.column;

LEFT OUTER JOIN

• Returns all tables from table left and rows of table right which matches the join condition.The image below shows and help us understand how the inner join works.

• 

  SELECT column1, column2, ... FROM table1, LEFT JOIN table2, ON table1.column = table2.column;

RIGHT OUTER JOIN

• Returns all tables from table right and rows of table left which matches the join condition.The image below shows and help us understand how the inner join works.

  

• SELECT column1, column2, ... FROM table1, RIGHT JOIN table2, ON table1.column = table2.column;

UNDERSTANDING HOW THE JOINING WORKS

Suppose there are two tables T1 and T2 as shown in the image below,

Now, applying the inner joining command we get.

Applying left now,

Applying right now,

INDEXING

• It is a way to optimize our search queries, with its help we can avoid scanning the whole table.

• It is a type of data structure that improves the speed of operation in a table.

• We should apply the indexing to the column where searching is easier like I.D. or Name rather than country/location.

DEFAULT INDEXING

• If some column in some table is stated as the primary key then it is by default selected for indexing.

• show indexes from table_name;

• If there is no primary key the above command returns an empty set.

• If any column is marked Unique key then it is selected for indexing.

• It is clustered in the primary key i.e. whenever some row is added or deleted it will rearrange accordingly.

• It is on the other hand non-clustered in an unique key i.e. whenever some row is added or deleted it will not rearrange and it will add the new row at the bottom of the row, unlike the clustered or primary key indexing.

WORKING ON INDEXING

The first view their any indexing already in the table or not, in other words, is there any primary key / unique key or another type of indexing added to the table or not?

Now let's compare the searching process in indexed and non-indexed columns.

here, only one row is scanned as it is the primary key;

Now when we search through a column that is not indexed then the rows search comes out to be 50. This concludes that when indexed searching through something is more effective.

INSERTING INDEXING

create index index_name on table_name (column_name);

creating an index while creating a table is done as
create table table_name ( col_name1 datatype1, col_name2 datatype2, index index_name (col_name1);

Note: When an index is created that column when not the primary key or the unique is marked with MUL which is a short form of multiple as there can be multiple values in the column, also when the above commands are executed they will result in MUL.

Also do not apply all the columns with the indexing as this will increase complexity while adding, deleting and updating as the rows get shuffled or rearranged.