SQL for beginners
Hello, I am Tane J. Tangu, a forever beginner (learning never ends) and software developer (freelancer as of now). I love writing beginner articles, and this article is the start of a tutorial series on SQL for Beginners. If you are an SQL beginner or have little or no knowledge of SQL I believe this series is for you.
This article will be pretty straight to the point, with the basics you need to get started with SQL and it will be a great advantage for you if you get your hands dirty by running the commands in this article. We have a lot of SQL Graphical tools like PHPMyAdmin, MySQL Workbench just to name a few that permit you to interact with an SQL database management system like MySQL without requiring you to have SQL skills not forgetting ORM(Object Relational Mappers) which can be used in code to abstract SQL operations. There are times you might need to do complex queries and you might not be able to achieve such flexibility with these tools. Also, it is good to have an understanding of SQL as this can be plus to you as a developer and as a database administrator, it is a requirement. That said let's get started. Oh if you are wondering in the first place what is SQL, I got you. SQL stands for Structured Query Language, it is a relational database management system query language. We can say it is a programming language based on a declarative set of commands to create and manage databases in an RDBM (Relational Database Management System).
We take a practical approach in this tutorial, we start by installing MySQL which is an RDBM. There are different procedures depending on your operating system.
Installing MySQL on Windows
To install the community edition follow the instructions on the link https://dev.mysql.com/downloads/installer/
Installing MySQL on Linux
sudo apt-get update
sudo apt-get install mysql-serverAfter installation, you will be prompted to configure the root account for your database. The root account is like the superuser or super admin account for your database, the account with the boss access level and permission. You can later create accounts to manage your databases as the need be and we are going to demonstrate that in this post. Once your MySQL database is installed, it comes with some couple of databases we are going to explore them but will not talk about them at this stage.
Tighten your seat belt we are getting this thing practical, I am going to be playing around databases on ubuntu terminal, if you are on windows chill is the same thing as long as you get your PowerShell open. If you do not have a mastery of command line, you can follow my earlier article on the command line for beginners.
Login to MySQL server
Mysql Commands
So that is what we get once we are logged in as root user. Say we want to look at the existing databases.
SHOW DATABASES;
Apart from the database "adonis" the rest of the databases are the defaults when MySQL was installed. Note however that MySQL is case insensitive, meaning "show databases" or "SHow dataBases" will produce the same output shown below. Now that we know how to see the databases on our MySQL server, let us create our own database. It is pretty simple, with more practice it will be possible for you to be intuitive about certain commands in MySQL. SQL commands end with a semi-colon.
mysql> CREATE DATABASE sql_beginner;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sql_beginner |
| store |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql>
Notice the database we created "sql_beginner" in the list of databases. Let's consider that sql_beginner is a database for students who want to learn SQL.
We want to collect some information about these students and save in our database so we can access them subsequently. A database is made up of tables, where the table name is the type of information we want to collect for example basic information about the students taking SQL for a beginner course, address of students taking SQL for beginners. Now each table column is specific information we want to collect, for example for basic information, we will need the first name, last name, email and gender. Each row represents a record or data collected about some student. Now that we have some idea of what a table is, let create a table.
First, you need to select which database you want to use to create your table.
mysql> USE sql_beginner;
Database changed
Create your table now. We will create a table called student_basic_info.
mysql> CREATE TABLE student_basic_info(first_name VARCHAR(45), last_name VARCHAR(45), email VARCHAR(45), id INT AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.69 sec)
So what we did was creating a table called student_basic_info, to collect basic information about sql_beginners supposedly taking our just imagined online course SQL for beginners. The collected information includes first_name, which has a type of VARCHAR of the max length of 45 characters, so each collected information has a data type in which it will be stored and retrieved from the database. You can refer to this link for more SQL data types https://www.journaldev.com/16774/sql-data-types
Also, we need to make sure the information we add about each student should be uniquely identified as there can be students with the same first name and last name. We make use of an id field, just to note table column names are called fields in SQL terminology. So for each new student added this id automatically increments by 1, because this should be unique for every user we set it as the Primary key.
We can explore our table by typing the command as shown below.
mysql> describe student_basic_info;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| first_name | varchar(45) | YES | | NULL | |
| last_name | varchar(45) | YES | | NULL | |
| email | varchar(45) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
From the above result table, I will love to add that the column Null, tells us if that field can be left empty when adding a student, say for example a student who has no email, this student will be added to the database because by default it can be null, but we can change that such that the first name, last name and email cannot be null or left out when adding a student to the table.
We are now going to add a student record to our database.
mysql> INSERT INTO student_basic_info(first_name, last_name, email) VALUES("Ngwa", "Maggie", "ngwamaggy@test.com");
Query OK, 1 row affected (0.08 sec)
As always let's check out our created record
mysql> SELECT * FROM student_basic_info;
+------------+-----------+--------------------+----+
| first_name | last_name | email | id |
+------------+-----------+--------------------+----+
| Ngwa | Maggie | ngwamaggy@test.com | 1 |
+------------+-----------+--------------------+----+
1 row in set (0.00 sec)
We can add multiple students with one command.
mysql> INSERT INTO student_basic_info(first_name, last_name, email) VALUES ("nji", "Fidelis", "njifidelis@test.com"), ("Patrick", "Njassap", "patrick@test.com"), ("Fred", "Zurich", "fred@test.com"), ("Chris", "Ateba", "chris@test.com");
Query OK, 4 rows affected (0.12 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM student_basic_info;
+------------+-----------+---------------------+----+
| first_name | last_name | email | id |
+------------+-----------+---------------------+----+
| Ngwa | Maggie | ngwamaggy@test.com | 1 |
| nji | Fidelis | njifidelis@test.com | 2 |
| Patrick | Njassap | patrick@test.com | 3 |
| Fred | Zurich | fred@test.com | 4 |
| Chris | Ateba | chris@test.com | 5 |
+------------+-----------+---------------------+----+
5 rows in set (0.00 sec)
Modifying SQL table
We pointed out earlier that some tables fields can be left blank, for example, we are going to add a new record and leave the email field blank.
mysql> INSERT INTO student_basic_info(first_name, last_name) VALUES ("Atangana", "Christian");
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM student_basic_info;
+------------+-----------+---------------------+----+
| first_name | last_name | email | id |
+------------+-----------+---------------------+----+
| Ngwa | Maggie | ngwamaggy@test.com | 1 |
| nji | Fidelis | njifidelis@test.com | 2 |
| Patrick | Njassap | patrick@test.com | 3 |
| Fred | Zurich | fred@test.com | 4 |
| Chris | Ateba | chris@test.com | 5 |
| Atangana | Christian | NULL | 6 |
+------------+-----------+---------------------+----+
6 rows in set (0.00 sec)
Let us look once more at the fields that can be NULL and decide which ones cannot be NULL.
mysql> DESCRIBE student_basic_info;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| first_name | varchar(45) | YES | | NULL | |
| last_name | varchar(45) | YES | | NULL | |
| email | varchar(45) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)
The first_name and email field cannot be left blank, but we can, however, decide that the last_name can be NULL. Let us update our sql_basic_info.
mysql> ALTER TABLE student_basic_info MODIFY first_name VARCHAR(45) NOT NULL;
Query OK, 0 rows affected (0.83 sec)
Records: 0 Duplicates: 0 Warnings: 0
When we try to do the same for email, it fails. This is because a record already existed with a NULL entry for email.
mysql> ALTER TABLE student_basic_info MODIFY email VARCHAR(45) NOT NULL DEFAULT "";
ERROR 1138 (22004): Invalid use of NULL value
So what we can do is change the value of that particular record to some default value. We are going to set the default to "email@test.com"
mysql> UPDATE student_basic_info SET email="email@test.com" WHERE email IS NULL;
Query OK, 1 row affected (0.26 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM student_basic_info; +------------+-----------+---------------------+----+
| first_name | last_name | email | id |
+------------+-----------+---------------------+----+
| Ngwa | Maggie | ngwamaggy@test.com | 1 |
| nji | Fidelis | njifidelis@test.com | 2 |
| Patrick | Njassap | patrick@test.com | 3 |
| Fred | Zurich | fred@test.com | 4 |
| Chris | Ateba | chris@test.com | 5 |
| Atangana | Christian | email@test.com | 6 |
+------------+-----------+---------------------+----+
6 rows in set (0.00 sec)
We then update the email field to have NON NULL values.
mysql> ALTER TABLE student_basic_info MODIFY email VARCHAR(45) NOT NULL;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE student_basic_info;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | YES | | NULL | |
| email | varchar(45) | NO | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.08 sec)
Something we will want to do is add some fields to the table student_basic_info, let us add fields "password" with default value 'testpassword', gender, about, created_at, banned, deleted, last_updated, now we are diving into developing a database for an application for students taking an SQL beginner course.
mysql> ALTER TABLE student_basic_info ADD COLUMN password VARCHAR(32) NOT NULL DEFAULT 'testpassword'; Query OK, 0 rows affected (3.93 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE student_basic_info ADD COLUMN gender CHAR(1);
Query OK, 0 rows affected (1.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE student_basic_info ADD COLUMN about TEXT(300);
Query OK, 0 rows affected (0.86 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE student_basic_info ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP();
Query OK, 0 rows affected (0.79 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE student_basic_info ADD COLUMN banned BOOLEAN DEFAULT false;
Query OK, 0 rows affected (0.75 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE student_basic_info ADD COLUMN deleted BOOLEAN DEFAULT false; Query OK, 0 rows affected (0.93 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE student_basic_info ADD COLUMN last_updated DATETIME DEFAULT CURRENT_TIMESTAMP();
Query OK, 0 rows affected (0.71 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE student_basic_info ADD COLUMN just_record DATETIME DEFAULT CURRE
Query OK, 0 rows affected (0.73 sec)
Records: 0 Duplicates: 0 Warnings: 0
Let's take a look at the structure of our database.
mysql> DESCRIBE student_basic_info;
+--------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+-------------------+----------------+
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | YES | | NULL | |
| email | varchar(45) | NO | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| password | varchar(45) | NO | | testpassword | |
| gender | char(1) | YES | | NULL | |
| about | text | YES | | NULL | |
| created_at | datetime | YES | | CURRENT_TIMESTAMP | |
| banned | tinyint(1) | YES | | 0 | |
| deleted | tinyint(1) | YES | | 0 | |
| last_updated | datetime | YES | | CURRENT_TIMESTAMP | |
| just_record | datetime | YES | | CURRENT_TIMESTAMP | |
+--------------+-------------+------+-----+-------------------+----------------+
12 rows in set (0.70 sec)
Let display the table of our student_basic_info
mysql> SELECT * FROM student_basic_info \G;
*************************** 1. row ***************************
first_name: Ngwa
last_name: Maggie
email: ngwamaggy@test.com
id: 1
password: testpassword
gender: NULL
about: NULL
address: NULL
created_at: 2019-06-01 00:39:34
banned: 0
deleted: 0
last_updated: 2019-06-01 00:40:48
just_record: 2019-06-01 00:41:07
*************************** 2. row ***************************
first_name: nji
last_name: Fidelis
email: njifidelis@test.com
id: 2
password: testpassword
gender: NULL
about: NULL
address: NULL
created_at: 2019-06-01 00:39:34
banned: 0
deleted: 0
last_updated: 2019-06-01 00:40:48
just_record: 2019-06-01 00:41:07
*************************** 3. row ***************************
...Deleting from SQL table
We have a record "just_record" which is not needed, so we are going to delete this record from the table.
mysql> ALTER TABLE student_basic_info DROP just_record;
Query OK, 0 rows affected (1.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE student_basic_info;
+--------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+-------------------+----------------+
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | YES | | NULL | |
| email | varchar(45) | NO | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| password | varchar(45) | NO | | testpassword | |
| gender | char(1) | YES | | NULL | |
| about | text | YES | | NULL | |
| created_at | datetime | YES | | CURRENT_TIMESTAMP | |
| banned | tinyint(1) | YES | | 0 | |
| deleted | tinyint(1) | YES | | 0 | |
| last_updated | datetime | YES | | CURRENT_TIMESTAMP | |
+--------------+-------------+------+-----+-------------------+----------------+
11 rows in set (0.00 sec)
Setting keys on SQL table
We have already seen primary keys that uniquely identify records, we might want to have additional fields that should be unique for records in the database table, for example, constraints like no two records should have the same email. These keys are called unique keys.
mysql> ALTER TABLE student_basic_info ADD CONSTRAINT UNIQUE(email);
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
To test this we will add a record with an email that is already existing in the table student_basic_info.
mysql> INSERT INTO student_basic_info(first_name, last_name, email, password) VALUES ("Ndikwa", "Gilbert", "ngwamaggy@test.com", "mypassword");
ERROR 1062 (23000): Duplicate entry 'ngwamaggy@test.com' for key 'email'
An error occurs telling us the email we want to add to the table already exist in the field "email".
Foreign key constraints.
A foreign key constraint links two tables together, with this constraint we can define what happens when one table gets updated or deleted.
We will create a student_address_info table to keep track of the student's address, this table will have a field that will reference the student_basic_info table id field. So this field will serve as a foreign key in the student_address_info. The table containing the foreign key is called the child table and the table being referenced is called the parent or candidate table.
CREATE TABLE student_address_info (id INT AUTO_INCREMENT, stud_id INT, address1 VARCHAR(45) NOT NULL, address2 VARCHAR(45), city VARCHAR(20), state_province VARCHAR(20), country VARCHAR(30), PRIMARY KEY (id), FOREIGN KEY (stud_id) REFERENCES student_basic_info(id) ON DELETE CASCADE ON UPDATE CASCADE);
Query OK, 0 rows affected (0.39 sec)
mysql> describe student_address_info;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| stud_id | int(11) | YES | MUL | NULL | |
| address1 | varchar(45) | NO | | NULL | |
| address2 | varchar(45) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state_province | varchar(20) | YES | | NULL | |
| country | varchar(30) | YES | | NULL | |
+----------------+-------------+------+-----+---------+----------------+
7 rows in set (0.05 sec)
We add a foreign key constraint on the student_address_info stud_id field which references the id field of the student_basic_info field. We should note that in setting the foreign reference we specified ON DELETE CASCADE, this means that if the parent is deleted the child also will be deleted from the database. Equally, we specified ON UPDATE CASCADE so if the student_basic_info id changed, the stud_id field of the student_address_info should equally be updated.
You can have many foreign keys in a table as well was combine ON DELETE x and ON UPDATE X, or use one of them, there defaults for them set by the database in case you don't explicitly specify them.
ON DELETE/UPDATE X
SET NULL:
This action specifies that the column will be set to NULL when the referenced column is UPDATED/DELETED.
CASCADE:
CASCADE specifies that the column will be updated when the referenced column is updated, and rows will be deleted when the referenced rows are deleted.
SET DEFAULT:
The column will be set to DEFAULT value when UPDATE/DELETE is performed on referenced rows.
NO ACTION:
This is the default behaviour. If a DELETE/UPDATE is executed on referenced rows, the operation is denied. An error is raised.
As an example , let's add a record to the student_address_info table with a stud_id that does not exist in the table student_basic_info, we will notice that it fails. This happens because there is a foreign key violation.
mysql> INSERT INTO student_address_info (stud_id, address1, city) VALUES (34, "Malingo", "Buea");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sql_beginner`.`student_address_info`, CONSTRAINT `student_address_info_ibfk_1` FOREIGN KEY (`stud_id`) REFERENCES `student_basic_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
Let's create an address record for the student of id 1.
mysql> INSERT INTO student_address_info (stud_id, address1, city) VALUES (1, "Malingo", "Buea");
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM student_address_info;
+----+---------+----------+----------+------+----------------+---------+
| id | stud_id | address1 | address2 | city | state_province | country |
+----+---------+----------+----------+------+----------------+---------+
| 2 | 1 | Malingo | NULL | Buea | NULL | NULL |
+----+---------+----------+----------+------+----------------+---------+
1 row in set (0.00 sec)
Let's create another address record for the student of id 2.
mysql> INSERT INTO student_address_info (stud_id, address1, city) VALUES (2, "Bolifamba", "Douala");
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM student_address_info;
+----+---------+-----------+----------+--------+----------------+---------+
| id | stud_id | address1 | address2 | city | state_province | country |
+----+---------+-----------+----------+--------+----------------+---------+
| 2 | 1 | Malingo | NULL | Buea | NULL | NULL |
| 3 | 2 | Bolifamba | NULL | Douala | NULL | NULL |
+----+---------+-----------+----------+--------+----------------+---------+
2 rows in set (0.00 sec)
Let's delete the student of id 1 from the student_basic_info database.
mysql> DELETE FROM student_basic_info WHERE id=1;
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM student_address_info;
+----+---------+-----------+----------+--------+----------------+---------+
| id | stud_id | address1 | address2 | city | state_province | country |
+----+---------+-----------+----------+--------+----------------+---------+
| 3 | 2 | Bolifamba | NULL | Douala | NULL | NULL |
+----+---------+-----------+----------+--------+----------------+---------+
1 row in set (0.00 sec)
Deleting a database table
This section marks the end of the first of series on SQL for beginners. We will create a student_location_info table and later delete this table from the database sql_beginner. We begin by showing existing tables.
mysql> SHOW TABLES;
+------------------------+
| Tables_in_sql_beginner |
+------------------------+
| student_address_info |
| student_basic_info |
+------------------------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE student_location_info (id INT AUTO_INCREMENT, lat FLOAT NOT NULL, lon FLOAT NOT NULL, PRIMARY KEY(id));
Query OK, 0 rows affected (0.54 sec)
mysql> SHOW TABLES;
+------------------------+
| Tables_in_sql_beginner |
+------------------------+
| student_address_info |
| student_basic_info |
| student_location_info |
+------------------------+
3 rows in set (0.00 sec)
Let's delete the just created table from the database.
mysql> DROP TABLE student_location_info;
Query OK, 0 rows affected (0.23 sec)
mysql> SHOW TABLES;
+------------------------+
| Tables_in_sql_beginner |
+------------------------+
| student_address_info |
| student_basic_info |
+------------------------+
2 rows in set (0.00 sec)
Thanks for reading through and most importantly, if you were getting your hands dirty by putting it to practice. The next article will be on "Querying and SQL Database for Beginners". The link will be posted in here once available.
It will be interesting to know what you think about the article and any updates.
tanerochris
I trust in knowledge sharing and love sharing my knowledge with others.
- 5 years ago
learn how to setup angular 7 and firebase
- 2 years ago
comment passer une command sur maxipanier
Save Your Money Today!
By receiving free stock articles and smart tutorials to advance your career.
- 5 years ago
sql for beginners - sql queries
Save Your Money Today!
By receiving free stock articles and smart tutorials to advance your career...