A lot of times people tend to wonder how all the stuff they see online is stored, it's not like whatever that is online is being retrieved from a paper secured in a vault somewhere underground. Let's start from the basics, a database is a collection of related data, so a database can be your cardboard, your suitcase, or anywhere you put files alike.
This means there are many database types depending on a lot of topics, which could be ease of access, security purposes, or any other. The most popular one of the ages is the traditional file system. Just as the name suggests it's writing records in files.
The current database trend is relational database systems, which represent data in a tabular form, just as you have rows and columns in a table. If you have a database, it serves as a container, inside it is what is called tables, these tables are used to cluster relative records, this table comprises Rows and columns.
A row is a full record and a column is an attribute of the record or a piece of the record. Just as you Would have a person's full name as a whole record, you would have to divide it into First Name and LastName, right?
Now that we have an Idea of how A database is, head to the basics of SQL, one thing to take note of is that SQL is just like the English you speak with just a slight difference. The first step you need to choose the Database Management System you wish to, for this piece I will use Microsoft SQL Server with Microsoft SQL Management Studio and the syntax will be in Microsoft SQL format.
The management system you choose to use affects how you'll write certain statements, don't worry much its just some slight difference. The first thing to note is that we mostly write the reserved words of SQL in BLOCK, not that it's compulsory to do, just that it makes it easy for documentation and helps a lot.
So let's get down to business, if you need a database you definitely have to create one right? We use the CREATE DATABASE command followed by the name of the database we wish to have.
Example:
CREATE DATABASE SQLclasss
My management studio looks like this:
Take a look at my databases present
A success after the execution
Added to my databases
Since it is MS SQL, mostly when you open the management studio, you'll find that the master Database is selected by default, so after using to create the database, you then select the database you wish to use by using the USE command, followed by the name of the database.
Example:
USE SQLclasss
After execution:
Note
A success after the executionThe next step is the commands for creating, and manipulating our tables. This is called CRUD a short form for Create, Read, Update, Delete. This means commands for creating tables, retrieving them, updating records, and finally deleting them.
Creating a table has a key factor, which is what it will entail, I'm talking about the attributes. The attributes are things that describe the data to be kept there, meaning, for instance if your table is supposed to be a table that will take the full name and age of people, then you could have a table like this:
First Name
Last Name
Age
So that would mean the table has three attributes, First Name, Last Name, and Age. One thing to note is that attributes should have a data type and size, meaning, If you need something that will take Words or lists or anything that has alphabets, then you'd need a data type that will allow it.
VARCHAR : Used for characters or strings
CHAR : Used for a character with a fixed length
INT : Used for Integer numbers
FLOAT : Used for floating point number or decimal numbers
DATE : Used to store date
TIME : Used to store time
DATETIME : Used to store both time and date
There are a whole lot of data types out there for different purposes which will always come in handy, for now, I'll just be using these few. Now, let's get back on track, to create a table, we need a name for it, the columns to be used and their data types, this is for the start, we can add what's called constraints to it too but let's leave that for now.
Let's create a table in our SQLclass database with these columns or attributes: First Name, Last Name, and telephone number of our friends. One thing to note is that the first three columns are going to be made of alphabets right? so why not use VARCHAR for it? and us INT for the telephone number? this is how we execute it:
CREATE TABLE friends(
FirstName VARCHAR(50),
LastName VARCHAR(50),
Telephone INT);
Note: The syntax from above, and the Columns are kept in brackets and each column is followed by a data type and the size in parenthesis, the size means how many characters it can hold. VARCHAR(50) means 50 characters Also, note how I highlight the command I wish to execute and then tap execute, this is because there are various commands there, without highlighting and executing it will execute all commands sequentially.
And the comment too, will not be executed no matter what, unless, of course, you take away the "#", this is used for documentation.
The tables in the Database before execution
After executionThe syntax: CREATE TABLE your_tabel_name_here(
column_name_here DATA_TYPE_HERE,
column_name_here DATA_TYPE_HERE,
column_name_here DATA_TYPE_HERE);
"CREATE TABLE" is the first command, followed by your table name then put in brackets the Names and Data types of your columns. Use the example I've implemented as a reference point.
Since we have a table, why don't insert some data into it? To insert we need to know the columns we're inserting into, we could also decide to insert into all columns.
To insert into our table, we do this:
INSERT INTO friends(FirstName,LastName,Telephone) values(
'Abdul Kahar',
'Abdul Rahman',
0123456789);
Note that INSERT and INTO are keywords, meaning they possess their own purpose in the language hence you can not use it for any other purpose than what it's meant for.
The end results:
Make sure to take note of how you arrange the columns in the INSERT command, also, whatever you are writing that has to do with characters should be quoted just like the "Abdul Kahar".
So let's say the data we took and inserted has an issue that we need to update a column with a different value or whatever. The syntax is very simple:
UPDATE frinds set FirstName='Abdul Kahar Gunu' where Telephone=0123456789;
Firstly let's see what in our 'friends' table this command:
SELECT * FROM friends
Retrived data
The above command is what's used to read data in a table, the * there means all, meaning the whole command says"SELECT all FROM friends" makes sense right? This also means we could retrieve only one record or two or however many we wish for. To retrieve specific records, we would have to add a condition so it'll help in the search for the record(s).
Check out this command
SELECT * FROM friends where = Telephone=0123456789;
Yeah, it means we retrieving all columns of the record which has its Telephon= 0123456789 . We could retrieve a column of more and even specify how we wanted them to be presented:
SELECT LastName FROM friends where = 0123456789;
Retrived data
See the column name above? we could also do something like:
SELECT Telephone,FirstName FROM friends WHERETelephone = 0123456789;
Retrived data
Let's get back on track to Updating a record, our command was
UPDATE friends set FirstName= 'Abdul Kahar Gunu' where Telephone=0123456789;
It means we are updating the column "FirstName" and changing it to "Abdul Kahar Gunu" and the condition is a record(s) that possess a "Telephone" number = 0123456789
Sucess
After executionLet's delete something from our table, right? Firstly, let me add another record... Done lets gooooooooo. Just as we updated our record, it is basically the same just a slight change:
DELETE FROM friends WHERE Telephone = 1234567890
See the keywords? the condition? yeah, simply ain't it?
The recoreds
After executionSQL is quite simple once you put your mind to it, I have tried to dumb it down somehow, If you do not understand any part you can reach out to me. If you want I make a recording and explain it properly. This is just the beginning of it, this is just some bit of concepts and with time we'll to moving to more concepts.