Mastering SQL Fundamentals: Databases, Tables, Creating a Database, Data Types, and SQL Commands
Welcome back if you have come from Excel Series. If not, hi my name is Abdurrahman(Ab-do-ra-men) and we will be starting a new series on SQL. Another tool that will be added to your Excel skills that will help you analyze datasets that simply cannot be analyzed in Excel. SQL will allow us to communicate to large and I mean very large tables. We learn what a database is and how to create one and finally what commands we should know to interact with it. Lets get started!
What is SQL?
Introduction to SQL
Historical Context: We used to store data in file cabinets back in the good old days. If you wanted to search for something it could easily get lost and it was very time consuming.
Luckily in the past couple of decades programmers have developed new technology called databases that help us manage our data using SQL. SQL stands for Structured Query Language, a language that communicates with databases.
Purpose of SQL: This Language allows us to pull, add, edit, and delete data in the database. Imagine a database is like a warehouse, and the data tables are like filing cabinets.
Basic SQL Commands: For example if we wanted to find names of people taller than a certain height that would take us ages searching through the file cabinet and looking at each file one by one. With SQL all you have to do is write this `SELECT name FROM table WHERE height > 10;`.
Also if data was stored somewhere else in our database like on another table we simply can use JOIN commands to get data from multiple tables using foreign and primary keys. We will touch more on that in the upcoming articles.
Importance of learning SQL: Learning SQL is essential regardless of the domain. Most companies use databases to communicate with data, which can be pulled for analysis.
Relational Databases: SQL works on relational databases that have tabular data (rows and columns) with keys linking tables.
Creating a Database
Using MySQL Workbench
Creating a Database: To create one in your query file type “CREATE DATABASE name;”. Then hit the lightning bolt button to execute on the top. Finally refresh the schema tab to see the new database.
Setting the Default Schema: There are two ways first right-click on the database and select “Set as Default Schema” or use this command “USE databasename;”. Now were in the database we created.
Dropping a Database: To Drop a database simply type “DROP DATABASE name;”.
Setting Database to Read-Only: If you don’t want someone messing around and only want them to view then type this “ALTER DATABASE name READ ONLY = 1;” by setting it to 1 its basically setting it to True so type 0 if you want to set it back to false and reverse the change. If you don’t do that then you can’t drop the database etc.
Adding Encryption: Simply type “ALTER DATABASE name ENCRYPTION = ‘Y’;”. Just like before if you want to reverse it replace the ‘Y’ with an ‘N’.
Data Types
Common Data Types in SQL
CHAR: This is a fixed length datatype. Example: CHAR(10) stores 10 characters. This is when we know our data’s length before hand , like zip codes.
VARCHAR: Variable length datatype. Example: VARCHAR(255) allows up to 255 characters. This is used when we don’t know how long the text will be but we have a rough idea of how long it should be like the title of a book or the address.
INT: Used to only allow whole numbers and just whole numbers. Decimals and fractions are not allowed. Using this for example the ID column would be ideal.
DECIMAL: For decimals, like money. Example: DECIMAL(10,2).
DOUBLE: For decimal numbers without perfect precision. Like 14.123813218 etc.
Signed vs Unsigned: Numeric data types can be signed or unsigned. Unsigned can store more positive values. So if your data is like ID’s its better to use Unsigned since they can never be negative.
Date and Time: Some commands are DATE, TIME, DATETIME, and TIMESTAMP. Will go in more detail in the future but these are pretty self explanatory.
Type of SQL Commands
DDL(Data Definition Language): This is mainly used to create and modify the structure of the database. Some examples are,
CREATE: Create new tables or objects.ALTER: Make changes to existing tables.DROP: Remove tables or objects.TRUNCATE: Remove all rows from a table.
DML (Data Manipulation Language): Here this is where you will spend more of your time in which is manipulating data in the database. Here are some examples,
SELECT: Retrieve data from a database.UPDATE: Modify existing data.INSERT: Add new data.DELETE: Remove data.
DCL (Data Control Language): This is to help us control access to the database. Here are some example commands,
GRANT: Give user access privileges.REVOKE: Take back user access privileges.
TCL (Transaction Control Language): Finally we can manage transactions in the database. This helps us record data commands which allows us to undo unintended changes or fix bugs/errors that may have occurred. Its a good practice to commit our data so we can rollback to it if we make a mistake. Savepoint allows us to go back to our data to a certain point. Here are some examples,
COMMIT: Save changes.ROLLBACK: Undo changes.SAVEPOINT: Set a point to which you can roll back later.
Overall, in this article we learned what exactly is database and what is SQL. We learned that SQL can be used to communicate with these databases that are tabular in format. These tabular databases store tons of information that relate to one another in their own individual tables. We learned how some of these columns in the table can be restricted to certain types which allow for cleaner data and less errors.
We then learned the different types of SQL commands that are not just limited to manipulating data but allowing us to control it and redo changes. I encourage you guys to practice some of these commands on your own to build confidence in your skills. As always have a Good morning, Good afternoon and Good night.
