let me tell you before you start with this, this gonna be a long ride so sit back and prepare yourself with MYSQL ready and commit the required time for this exercise.
this is going to be short concise and point to point because I don't want to waste your time as well as it will be enough SQL for you to feel confident.
if you have the habit of reading and experimenting with music I would suggest that put your headphones in the ear and let's start with SQL
let's start with very basic,
SQL is a Structured Query Language which is basically a language used by databases. This language allows you to handle the information using tables and allows you to query these tables
A Brief History of SQL (it's good to know)
1970 − Dr. Edgar Frank Codd worked for IBM invented the relational database in the 70s. It was initially called SEQUEL, but due to a trademark problem, it was changed to SQL. However, many people still say SEQUEL.
1974 − Structured Query Language appeared.
1978 − IBM worked to develop Codd's ideas and released a product named System/R.
1986 − IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software which later came to be known as Oracle.
ANSI/ISO standard (SQL is a standard most popular database follow)
The American National Standards Institute (ANSI) make SQL a standard in 1986 and the International Organization for Standardization (ISO) makes SQL the database standard
it means if you learn SQL you can almost be ready to use all the databases because they all follow ANSI Standard there are simple differences in some functions but apart from that, you are good to go.
What is Database?
A database is an organized collection of structured information, or data, typically stored electronically in a computer system.
A database is usually controlled by a database management system (DBMS).
#What is DBMS(Database Management System)?
Database Management Systems (DBMS) are software systems used to store, retrieve, and run queries on data. A DBMS serves as an interface between an end-user and a database, allowing users to create, read, update, and delete data in the database.
Types of databases
- Hierarchical databases
- Network databases
- Object-oriented databases
- Relational databases
- NoSQL databases
Hierarchical Databases :
Just as in any hierarchy, this database follows the progression of data being categorized in ranks or levels, wherein data is categorized based on a common point of linkage. As a result, two entities of data will be lower in rank and the commonality would assume a higher rank.
Network Databases :
In Layman’s terms, a network database is a hierarchical database, but with a major tweak. The child records are given the freedom to associate with multiple parent records. As a result, a network or net of database files linked with multiple threads is observed. Notice how the Student, Faculty, and Resources elements each have two-parent records, which are Departments and Clubs.
Object-Oriented Databases :
Those familiar with the Object-Oriented Programming Paradigm would be able to relate to this model of databases easily. Information stored in a database is capable of being represented as an object which responds as an instance of the database model. Therefore, the object can be referenced and called without any difficulty. As a result, the workload on the database is substantially reduced.
Relational Databases :
Considered the most mature of all databases, these databases lead in the production line along with their management systems. In this database, every piece of information has a relationship with every other piece of information. This is on account of every data value in the database having a unique identity in the form of a record. Note that all data is tabulated in this model. Therefore, every row of data in the database is linked with another row using a primary key. Similarly, every table is linked with another table using a foreign key.
NoSQL Databases :
A NoSQL originally referring to non SQL or non-relational is a database that provides a mechanism for storage and retrieval of data. This data is modeled in means other than the tabular relations used in relational databases.
Top SQL DBMS you should know about:
(Not ordered chronologically)
- MySQL
- Oracle
- PostgreSQL
- Microsoft SQL Server
- MongoDB
Before moving ahead I would like to create a sample table, please note for every SQL query written over here works on this table.
if some code doesn't work please check this table has those values because queries may overlap each other or you might just delete the table but no worries keep the below script handy in the second query tab and execute it again.
DROP DATABASE IF EXISTS `hashnode`;
create database hashnode;
use hashnode;
create table person
(
id int not null,
first_name varchar(50) not null,
location varchar(50) not null,
salary bigint not null
);
insert into person values(100,'Raj','Chennai',50000);
insert into person values(101,'john','Delhi',40000);
insert into person values(102,'Hari','Mumbai',20000);
insert into person values(103,'Suresh','Banglore',25000);
insert into person values(104,'Kaif','Pune',30000);
Types of SQL Commands
There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
1. Data Definition Language (DDL)
DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
All the command of DDL are auto-committed that means it permanently save all the changes in the database.
Here are commands that come under DDL:
# create command is used to create a new table or database
create table person
(
id int not null,
first_name varchar(50) not null,
location varchar(50) not null
);
# alter is used to alter the structure of the database.
alter table person ADD last_name varchar(10);
# drop is used to delete both the structure and record stored in the table.
drop table person
# truncate is used to delete all the rows from the table and free the space containing the table.
truncate table person;
Data Manipulation Language
DML commands are used to modify the database. It is responsible for all forms of changes in the database.
The command of DML is not auto-committed which means it can't permanently save all the changes in the database. They can be rollback.
Here are commands that come under DML:
INSERT
UPDATE
DELETE
# The insert statement is a SQL query. It is used to insert data into the row of a table
insert into person values(100,'Raj','Chennai');
# update command is used to update or modify the value of a column in the table.
update person
set first_name = 'Rohini'
where id = 102;
# delete is used to remove one or more rows from a table.
delete from person
where id = 102;
Data Control Language
DCL commands are used to grant and take back authority from any database user.
Here are commands that come under DCL:
Grant: It is used to give user access privileges to a database.
Revoke: It is used to take back permissions from the user.
Transaction Control Language
TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.
These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them.
Here are some commands that come under TCL:
Commit: Commit command is used to save all the transactions to the database.
start transaction;
set sql_safe_updates = false;
update person set first_name = 'Pratik' where id = 101;
commit;
select * from person;
Rollback: Rollback command is used to undo transactions that have not already been saved to the database.
start transaction;
set sql_safe_updates = false;
update person set first_name = 'Pratik' where id = 101;
rollback;
select * from person;
Savepoint: It is used to roll the transaction back to a certain point without rolling back the entire transaction
SAVEPOINT SAVEPOINT_NAME;
Data Query Language
DQL is used to fetch the data from the database.
It uses only one command:
select which is the most basic command in SQL
select * from persons
SQL Data Types
Each column in a database table is required to have a name and a data type.
An SQL developer must decide what type of data will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.
MySQL uses many different data types broken into three categories:
Numeric
Date and Time
String Types.
Numeric Data Types (popular ones are listed here, not all of them)
INT − A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits.
BIGINT − A large integer that can be signed or unsigned. If signed, the allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. You can specify a width of up to 20 digits.
Date and Time Types: (popular ones are listed here, not all of them)
DATE − A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example, December 30th, 1973 would be stored as 1973-12-30.
DATETIME − A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, 3:30 in the afternoon on December 30th, 1973 would be stored as 1973-12-30 15:30:00.
String Types (popular ones are listed here, not all of them)
VARCHAR(M) − A variable-length string between 1 and 255 characters in length. For example, VARCHAR(25). You must define a length when creating a VARCHAR field.
CHAR(M) − A fixed-length string between 1 and 255 characters in length (for example CHAR(5)), right-padded with spaces to the specified length when stored. Defining a length is not required, but the default is 1
What are SQL operators?
An SQL operator is a special word or character used to perform tasks. These tasks can be anything from complex comparisons, to basic arithmetic operations. Think of an SQL operator as similar to how the different buttons on a calculator function.
Arithmetic operators
- (Addition)
The + symbol adds two numbers together.
SELECT 10 + 10;
- (Subtraction)
The - symbol subtracts one number from another.
SELECT 10 - 10;
- (Multiplication)
The * symbol multiples two numbers together.
SELECT 10 * 10;
/ (Division)
The / symbol divides one number by another
SELECT 10 / 10;
% (Remainder/Modulus)
The % symbol (sometimes referred to as Modulus) returns the remainder of one number divided by another.
SELECT 10 % 10;
Bitwise operators
& (Bitwise AND)
| (Bitwise OR)
^ (Bitwise exclusive OR)
Comparison operators
= (Equal to)
select first_name from person where salary = 25000;
!= (Not equal to)
-- != (Not equal to)
select first_name from person where salary != 25000;
(Greater than)
select first_name from person where salary > 20000;
< (Less than)
select first_name from person where salary < 40000;
= (Greater than or equal to)
-- >= (Greater than or equal to)
select first_name from person where salary >= 25000;
<= (Less than or equal to)
-- <= (Less than or equal to)
select first_name from person where salary <= 40000;
<> (Not equal to)
-- <> (Not equal to)
select first_name from person where salary <> 50000;
Logical operators
The ALL operator returns TRUE if all of the subquery values meet the specified condition.
select first_name from person
where salary > all (select salary from person where id > 101)
The ANY operator returns TRUE if any of the subquery values meet the specified condition.
select first_name from person
where salary > any (select salary from person where id > 101)
The AND operator returns TRUE if all of the conditions separated by AND are true.
select first_name from person
where salary > 10000 and location = 'Delhi';
The BETWEEN operator filters your query to only return results that fit a specified range.
select first_name from person
where salary between 10000 AND 35000;
The EXISTS operator is used to filter data by looking for the presence of any record in a subquery.
select first_name, id from person
where exists (select salary from person where id = 104);
The IN operator includes multiple values set into the WHERE clause.
select * from person
where first_name in ('Raj','Hari');
LIKE operator searches for a specified pattern in a column.
select * from person
WHERE first_name like '%i%';
The NOT operator returns results if the condition or conditions are not true.
select * from person
WHERE first_name not in ('Raj','Hari');
The OR operator returns TRUE if any of the conditions separated by OR are true.
select first_name from person
where salary > 10000 or location = 'Delhi';
IS NULL
select first_name from person
where location is null;
Something more on the writing SQL queries in a more effective way:
here I am listing out query writing sequence, please note this is not an execution sequence it is simple query writing sequence.
- SELECT
- FROM
- JOIN
- ON
- WHERE
- GROUP BY
- HAVING
- ORDER BY
- LIMIT
I know we did not cover some of the above queries here, but here we have covered essentials which are backbone concepts of SQL and those who are at the beginning of getting their hands dirty with SQL this SQL essential guide going to help them to get started with the most demanding yet somewhat easy skill.
on the ending note, I want to list simple tips that can eventually deepen your understanding in some sense.
SQL is not case-sensitive however some organizations do follow some code patterns so according to them, you will need to use the case.
you can write your queries in a single line as well as multiple lines its recommended practice to write queries in multiple lines because it is easy to read and makes more sense.
it is best practice to use comments wherever necessary because it helps to understand scripts better and if you are looking at code after some time interval it's going to help to recall.
Now I feel those who are at this end of the page are ready to take beginner level challenges and explore some statements and combinations
I would like to stop here, it was a pretty lengthy article but it's worth it!
Thanks for reading...
