SQL is monumental when dealing with relational databases. Let’s see how.
Photo by Markus Winkler on Unsplash
Curious about SQL? Check out the entire series here:
[Introduction to SQL for Data Analysis 📈
The what why and how of SQL when you are just getting started.medium.com](https://medium.com/@aakriti.sharma18/introduction-to-sql-for-data-analysis-1c4177b36eba "medium.com/@aakriti.sharma18/introduction-t..")
Databases in SQL
But first what is a Database?
A database is made up of a collection of tables containing related information linked together in a structured manner.
Take the example of a school database.
It consists of a number of tables like teachers, students and classes. Each table consisting of data which can be linked to another related table like each class in the classes table has a teacher which is represented by the column teacher ID linking it to the teachers table.
So dealing with databases huh? Let’s start with looking up the existing ones.
SHOW DATABASES
This returns all the database names in the system. In most cases you can spot the one you need and start working on it like this :
USE db_name
Otherwise, if you need to work on a new database you can create so with the command -
CREATE DATABASE db_name
Tables
Next up we need tables to work with in our database. Similar to the databases we can look at the existing columns as -
SHOW TABLES
We can spot our table and start working on it using commands like SELECT, UPDATE, ALTER or we can create a copy of the table, if not we can create a new table as -
CREATE TABLE table_name ( col_name datatype, col_name datatype, col_name datatype .....)
To look at the description of the table like the number, name, datatype of columns we use the DESCRIBE clause as -
DESCRIBE table_name
It will return a result as :
While creating a copy of a table we have two options -
- copying just the structure of the table
- copying the structure and data as desired from the table
For copying the structure of the table we use the good old LIKE clause.
CREATE TABLE new_table LIKE old_table
This will copy the column names along with the datatype for each column WITHOUT data. It’ll be a blank table.
When we need to copy the structure WITH the data we can do so as -
CREATE TABLE table_name
SELECT < desired data >
FROM old_table
For changing the name of the table we use the RENAME clause.
RENAME TABLE old_name to new_name
Next we can perform operations on our desired table using the ALTER clause.
ALTER TABLE table_name
< changes >
This can be used to change the structure of the table by adding new columns, changing the name and datatype of the columns.
A new column can be added as :
ALTER TABLE table_name
ADD COLUMN column_name datatype
A new column can be added after a specified column or at first.
ALTER TABLE table_name
ADD COLUMN column_name1 datatype AFTER column_name2
The keyword FIRST is used to add a column at the beginning.
ALTER TABLE table_name
ADD COLUMN column_name datatype FIRST
To modify a column’s name we use -
ALTER TABLE table_name
CHANGE COLUMN old_name new_name datatype
A thing to note here is that specifying the datatype is a must regardless of whether it gets modified or not.
To change just the datatype and retain the name we use the command -
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype
To delete a column from the table DROP is used as -
ALTER TABLE table_name
DROP COLUMN column_name
That’s it for this time. Next up in the series we discuss about views and keys in SQL. Stay tuned ✨
Interested in daily data analysis content? Follow me on Twitter and Medium.
