If you are just starting your journey into Backend web development or Software Engineering, you might have come across the term "Database" an electronic storage that holds data systematically. Most social media platforms, banks, hospitals and e-commerce sites use databases to store data, form relationships between data, filter data, search data and perform the CRUD (Create, Read, Update and Delete) operation on data. A database can be a physical or conceptual representation of data/entities in rows and columns. A database is stored in a dedicated machine within the organization or most popularly stored in the Cloud (cloud hosting) which is easy to manage, access and retrieve data from the internet as well as a low-cost option.
Types of database:
Relational or Structural Database: it stores data in a tabular form. eg are graph and document databases.
Graph database: stores data in the form of a node(pointer) with relations between them as edge.
Document database: stores data as a JSON Object where data is represented as tables with JSON text format.
NoSql Database: An unstructured and flexible way of storing data thereby making the data scalable.
- Object-Oriented Database: it stores data as an object. i.e. an online bookstore will have a (Person class) that will store information like author, book, customer and others.
There is also a Big Database which is a combination of structured, semi-structured and no-structured databases used by big organizations with large processing data like e-commerce with a lot of visitors in a mili-seconds and data grows over time. There are many languages we can use to interact with the database but we will focus on SQL.
Introduction to SQL:
The structural query language is a standard language that can interact with structured data in all databases. examples are MySQL, Oracle and Postgres. For SQL to interact with the database, A DBMS (Data-Base Management System) is required to translate the SQL into a form understood by the database. SQL uses the DDL, DQL, DML and DCL to perform its CRUD operation.
To create a database in SQL, run this command:
CREATE DATABASE <nameofdatabase>;
If you have multiple databases created, you can specify the database to use by running this command:
USE DATABASE <nameofdatabase>;
A table comprises columns and rows where data can be stored in a Relational database (RDBMS).To create a table in the database, run this command.
CREATE TABLE <NameofTable>;
As well a table can be created with its fields and input types concurrently, run the command below. A field is data input columns and rows or labels for the table like Firstname, Lastname, Age and others. A field has specific data types. These are the types of data in SQL:
STRINGS: for alphabet, numerals and symbols.
CHAR: for characters. char 50 means a minimum of a fixed 50 characters.
VARCHAR: for a variable length of characters. it can extend to the number specified, if any.
TINY TEXT about 250 texts, DECIMAL, INT, TEXT about 65, MEDIUMTEXT about 16.7 million texts, LONGTEXT and others.
INSERT INTO <NameofTable> (NameoftableID int, NameoftableFirstname vachar(50),Nameoftableprice decimal)
VALUES(value1, value2 value3);
A Table cannot be isolated i.e. it must have a relationship with other tables.
Types of Table Relationships are:
ONE to ONE: A person must have one BVN or NIN number.
ONE to MANY: A student can be offering many online courses at once, A teacher can be teaching many subjects and A Businessman can have many stores or many bank accounts to his name.
MANY to MANY: in an organization, they have many committees representing all the members. At the event, many musical Artists performed for the audience.
A table must have a key. There are many types of keys and how to use it in a Database:
A primary key is a unique identifier of a table. like In a table of students, two students might have the same name and surname but not the same Email or ID number, therefore the ID or Email becomes the primary key because it is unique to each student.
A foreign key is a primary key from a different table to a table that has its primary key. example: A teacher table with a primary key of teacherId, when the teachersID is put in the student table to link or create a relationship between them, the teachersID becomes a foreign key in the student's table with studentID as its primary key.
If a value is mistyped or misplaced, you can use this command to update or change the value.
UPDATE <nameofTable> SET Fieldname = 'value';
You can also select the Middle or last column or row by using the keyword 'WHERE'.
UPDATE <nameofTable> SET Fieldname = 'value' WHERE ID = <idNumber>;
Deleting a table row or column uses the keyword 'DELETE FROM'.
DELETE FROM <nameofTable> WHERE ID = <idNumber>;
To search or query your table, use the keyword 'SELECT'.
SELECT Fieldname, Fieldname, Fieldname from "nameofTable";
SELECT Firstname, Lastname from "Student"
SELECT from "nameofTable" WHERE ID = <idnumber>
conclusion:
SQL is flexible to use with distinct and easy-to-master syntax as well as with a large community. using SQL helps to improve productivity by handling complex data in a short period once the database schema is determined. it can run on any computer or hardware. it is also very compatible with all relational databases, user-friendly and efficient in large data processing.