SQL: The Love Language of our Databases

What is SQL?

SQL Stands for Structured Query Language. The primary use of SQL is to talk to Databases – to Create things, Relate things, Update things and Delete things (CRUD).

Pre-requisites

A Database Management System
Ubuntu Desktop
MySQL

What is a Database?

A database can be thought of as one giant excel spreadsheet with Rows (Records) and Columns (Fields) that contain our data. While excel is great for small tables of data, Databases are used to store large amounts of data.

Let's make our own Database

First open your terminal in Ubuntu

Then update Ubuntu with: sudo apt update

Then install SQL Server with: sudo apt install mysql-server -y

Check the instance is running with: sudo systemctl status mysql

It should say the service is Active and running

Then run the SQL command: show databases;

Next create a database with the command: create database datadistillery_coffee;

run the show databases command again to confirm the database has been created

To use the database type the command: use datadistllery_coffee;

A database needs tables, currently we have none which you can verify with the command: show tables;

Creating Tables in Database

Type in the command:

create table coffee_table (id int, name varchar(255), region varchar(255), roast(varchar(255));

Then type the command: show tables;

To get the database Schema, type the command: describe coffee_table;

Inserting data into table

Type the command: insert into coffee_table values(1, “default route”, “ethiopia”, “light”);

To see that data in the table type the command: select * from coffe_table;

So far we have:

Created a database
Created a table inside the database
Added data to the database
Returned that data using a SELECT statement

Let’s update some data in our table with the following command:

update coffee_table

set name=’Mocha’

where ID = 1;

select * from coffee_table;

A big list of commands to manipulate our database can be found on https://www.w3schools.com/sql/

Using AI to generate SQL Code

If you’re just starting out in SQL and know what you’re trying to select from a database, but don’t quite know how to write the Syntax you can use many free AI resources to help you write your SQL Query.

Some websites I like to use are:

https://beta.openai.com/playground

https://www.askedith.ai/

https://www.useblackbox.io/