Get Started With SQL | Part 1

source: https://www.sitepoint.com/understanding-sql-rails/

This is Part 1 of the 2 part series I have written to get you started with SQL

“Data is the new oil!” Yes, we all have heard this phrase, right?!

All the software professionals who closely happen work with databases or anyone who is looking to diving into ‘Data Science’, ‘Data Engineering’, ‘Data XYZ’ knowing SQL is the primary skill required.

“So, What is Database and what is SQL?”

Database is any collection of related information. SQL is the language used to interact with relational database management systems.

There are also a Non-relational type of databases which usually can be encrypted in the form of JSON, XML, or similar document files and NoSQL is used to deal with such database but, let’s focus on Relational Databases and SQL for now :)

In Relational Databases, we organize data into one or more tables. Each table has columns and rows with one column having a unique identifier/key for each row known as the primary key.

When we say to work with data, we basically mean to perform these 4 fundamental operations. C(Create), R(Read), U(Update), D(Delete)

Now for dealing with Relational databases we will need to interact with Relational Database Management System (RDBMS). MySQL is one such popular RDBMS client which helps the user create and maintain a relational database.

Want to read this story later? Save it in Journal.

To perform CRUD operations on data, we need to write Query. Query is a set of instructions given to RDBMS that tell the RDBMS what information you want it to retrieve for you.

Let’s get started with the actual work then!

Download and install MySQL as per your device requirements and set up Workbench which is a nice visual tool for databases from here.

Creating and populating a table

Before we make a table, we should be familiar with the primary datatypes of SQL, so here are they

• INT → whole number
• DECIMAL(M, N) → Decimal number (M=whole numbers, N=digits after decimals)
• VARCHAR(l) → string of text of length l
• BLOB → Binary Large Object, Stores large data
• DATE → YYYY-MM-DD
• TIMESTAMP → YYYY-MM-DD HH:MM:SS

Now, we will create a student table that will contain the columns ‘id’, ‘name’ and ‘major’. ‘id’ column will receive the integers as an input, ‘name’ and ‘major’ columns will receive a string as input with a maximum length of the input being 20 characters.

CREATE TABLE student (  
    id INT PRIMARY KEY,  
    name VARCHAR(20),  
    major VARCHAR(20) 
);

Now, we will populate the table

INSERT INTO student VALUES(1, 'Anna', 'Maths');
INSERT INTO student VALUES(2, 'Smith', 'Physics');
INSERT INTO student VALUES(3, 'Adam', 'Biology');
INSERT INTO student VALUES(4, 'Smith', 'Computer Science');

• To see the table in MySQL,

SELECT * from student;

The created table is the same as the table in image below, red column indicates a primary key, and the others being general attribute columns

student table

Note that, here although there are two students named ‘Smith’ but their ‘id’ which happens to be a primary key is different therefore they identify as different students.

We can also insert information only in certain columns. In this case, the non-populated column will receive value NULL unless you have defined a default value for it. For example…

INSERT INTO student(id, name) VALUES(2, 'Arya');

here, Arya will have NULL as a major

• To get the description of the table,

DESCRIBE student;

• To add another column showing ‘ gpa’ of students having decimal datatype

ALTER TABLE student ADD gpa DECIMAL(3,2);

• To drop the ‘gpa’ column from student table

ALTER TABLE student DROP gpa DECIMAL(3,2);

• To delete a created student table,

DROP TABLE student;

We can also put certain constraints on columns such as UNIQUE (to ensure the unique value in all the rows for that attribute), NOT NULL (to ensure values inserted are not null), DEFAULT (If value inserted is null then the default value to be inserted), AUTO INCREMENT (so that value gets incremented every time a new element is added)

example:

CREATE TABLE student(
    id INT AUTO INCREMENT,   
    name VARCHAR(20) NOT NULL,
    major VARCHAR(20) DEFAULT 'undecided',
    PRIMARY KEY(id)
);

Above code is self-explanatory as discussed earlier with constraints being applied on columns. Note that as it can be seen in the code, PRIMARY KEY can also be defined separately.

Updating and Deleting rows

Tip: Before you move ahead, there is a possibility that your SQL editor is using safe update mode. You will need to disable it with this command

SET SQL_SAFE_UPDATES=0;

Also, note down the comparison operations in SQL

• = : equals
• <> : not equals
• > : greater than
• < : less than
• >= :greater than or equal
• <= :less than or equal

SET is used for updating the values. But before that, we will need to define which table we want to update before using SET by writing UPDATE <table_name>. The below code snippet will update the major of the students from ‘Biology’ to ‘Healthcare’

UPDATE student  
SET major = 'Healthcare'  
Where major = 'Biology';

we got the idea about UPDATE and SET. But what is WHERE?

• WHERE clause in SQL is used for filtering the rows. For the people coming from programming backgrounds, you can relate it with the conditional statements. It will filter out the only rows which match the given condition. Hence the typical template for updating information in SQL can be…

UPDATE <table_name> 
SET <column_name> = <new_value> 
Where <condition>

Note that for the conditional statements you can utilize OR and AND boolean operators.

Now, suppose we are planning to convert the students with majors in ‘Biology’ or ‘Chemistry’ to ‘Biochemistry’, this is how we can do it…

UPDATE student 
SET major = 'Biochemistry' 
where major = 'Bio' OR major = 'Chemistry';

DELETE is used for deleting the row with certain conditions.

For example, the below code snippet will remove all the rows from student table where the major is found to be ‘undecided’.

DELETE FROM student 
WHERE name = 'Tom' AND major = 'undecided';

Simple Queries

A query is a question or inquiry made to filter out and return certain data from a table.

The SELECT statement in SQL is used to select particular information in the table. Let’s see how it’s done…

SELECT name, major 
FROM student;

above query selects and returns ‘name’ and ‘major’ columns for all the rows in student table.

In case we want to limit the outputs to a certain number we can do it this way

SELECT name, major 
FROM student 
LIMIT 1;

We can also get the information according to ascending or descending order of a specific column(s). ORDER BY is used for defining the order.

The below query will return rows from the student table which is according to the alphabetically ascending order of their majors limited to 3 outputs.

SELECT * 
FROM student 
ORDER BY major

For alphabetically descending order of their majors,

SELECT * 
FROM student 
ORDER BY major DESC

The query below will give the name(s) of the students from student table who are majoring in either ‘Computer Science’.

SELECT name 
FROM student 
WHERE major = 'Biology' OR major = 'Computer Science';

So, We have covered the basics of SQL in this article. There is a lot more to cover apart from this. Part 2 of this SQL series will focus on more advanced topics in SQL such as tables with foreign keys, advanced constraints on columns, aggregate queries, Union, Joins, and some advanced functionalities.

Thanks for reading. If you liked this article don’t forget to give a clap and share your views in the comments :)

Enjoyed this post? Subscribe to the Machine Learnings newsletter for an easy understanding of AI advances shaping our world.