- This topic has 1 reply, 2 voices, and was last updated 1 year, 6 months ago by .
- February 14, 2020 at 2:43 pm #85819Spectator@oghenemarho
Databases are organized collections of data typically stored and accessed electronically via computer systems. They are used to store all forms of simple and complex digital data and are an essential part of modern software systems. There are several models for how databases store information in them but today, the most popular type of databases are relational databases which make use of the relational data model. This particular model is designed to make databases independent of any particular model and it relies on tables to store information about entities as well as define the relationships between these entities.
Database Management Systems (DBMS) are the software tools that serve as the interface between end users, applications and the database itself. They capture, analyze, retrieve and store data in the databases. Sometimes DBMS is used interchangeably to describe both the database management systems and the databases themselves. For relational database management systems (RDBMS), the language of choice for managing the interactions between the database and the RDBMS is called Structured Query Language or SQL. SQL allows you, through the database management system, to access and manipulate data stored in databases. There are several different types of relational databases being used in software systems but almost all of them make use of SQL for their interactions with data. A few popular ones include Microsoft SQL Server, MySQL, PostgreSQL, Oracle Database, IBM DB2 among others
Today we will be taking a closer look at SQL as a tool for manipulating databases in RDBMS. Assuming we already have PostgreSQL as our RDBMS set up and running, we will be looking at the command and queries that we can issue to our system in SQL to perform tasks on our database or its contents. Relational databases make use of tables for storing data in them so before you can even add information to our database, we need to create a table and specify the types of data that the table can store. Specifying datatypes improves the quality of data stored in your database and enforces the integrity of your data which in turn strengthens the reliability of your applications. Some common datatypes include the following:
- Integer: a numerical data type used for numbers between -2147483648 and 2147483647, or Unsigned numbers between 0 and 4294967295.
- Char: used to represent fixed length alphanumeric strings which can contain letters, numbers, and/or special characters. The size parameter specifies the column length in characters – can be from 0 to 255. Default is 1
- Varchar: used to represent fixed length alphanumeric strings containing information that is usually longer than what the char datatype can manage. The size parameter specifies the column length in characters – can be from 0 to 65535.
- Bool/Boolean: This datatype is used to represent True or False values, where zero is considered as false while all nonzero values are considered as true.
When creating a table for a database using SQL, you need to specify the table name, the titles of each column of the table, the datatypes of the contents of that column as well as which of the columns will serve as the primary key. You can also include some parameters that will add constraints to the table contents for each column. For example, the SQL command below will create a table called Registration, which will have 4 columns labelled id, firstname, lastname, and email:123456CREATE TABLE Registration (id SERIAL PRIMARY KEY,firstname VARCHAR(30) NOT NULL,lastname VARCHAR(30) NOT NULL,email VARCHAR(30),);
The column labelled id will serve as the primary key and has a datatype called SERIAL which stores a sequential integer that increases automatically with each entry into the table. Setting the id column as the primary key means that the contents of this column will be used to uniquely identify each row of the table. The other columns have the varchar datatype with a limit of 30 characters for each of them, meaning that these columns will not accept any entry that has more than the specified number of characters. The firstname and lastname columns also have the NOT NULL constraint which means that for each entry into the table, these columns must have a value and cannot be left blank. If you try to add an entry where these columns are blank, the database will reject that entry based on this constraint.
Running the above command will give you a table like this:
id firstname lastname
Having successfully created a table in your database, how do you now add entries or records to that table? This would be done using the INSERT command. Using this SQL command, you can specify which columns of an existing table that you want to insert data into and then the data you want to enter into each of the columns, in the order you listed the columns initially. Remember that the information must match the datatypes you specified for the columns, otherwise the database will not accept the data. Another thing you must note is that string values must be encased in quotes while numerical values should not have quotes. An example of an SQL command to insert data into the table you created earlier is:12345INSERT INTO registration (firstname, lastname, email) VALUES (‘Ben’, ‘Walker’, firstname.lastname@example.org);INSERT INTO registration (firstname, lastname, email)VALUES (‘Tom’, ‘Fisher’, email@example.com);INSERT INTO registration (firstname, lastname, email)VALUES (‘Isaac’, ‘Boris’, firstname.lastname@example.org);
Running these commands will result in the table being populated with the information like so:
id firstname lastname 0 Ben Walker email@example.com 1 Tom Fisher firstname.lastname@example.org 2 Isaac Boris email@example.com
You can add more entries using the same SQL queries and what you will note is that even when there was no value for the id column, it added incremental values from 0. This is because of the SERIAL datatype which increments the contents of a particular column for each entry added to the table. You can also insert NULL values into a table, as long as there are no constraints against it defined during the creation of the table.
Another commonly used SQL query is UPDATE which allows us to take data already stored in a database table and modify it. To update the contents of a table you need to specify the table name, specify the column you want to change and the new value you want to add to that particular column and then identify which of the rows you want to modify. Still working with our earlier sample table, we can run this UPDATE query:123UPDATE registrationSET firstname = ‘Francis’, lastname = ‘Paul’, email = ‘firstname.lastname@example.org’WHERE id=2
What this will do is go to the table named registration and look for the entry that has a value of 2 in the id column and when it finds that entry, it will change the values it has in the firstname, lastname and email columns to Francis, Paul and email@example.com respectively. Running this query will leave your table looking like this:
id firstname lastname 0 Ben Walker firstname.lastname@example.org 1 Tom Fisher email@example.com 2 Francis Paul firstname.lastname@example.org
Databases are not just for adding and updating data. You can also read information from your database. This can be done by running an SQL query like SELECT which will read the contents from a table based on the parameters you specify. In the table we have above, running this SQL query will select and display all the contents of the table.1SELECT * FROM registration
You can add some level of specificity to your query. For instance,1SELECT firstname, lastname FROM registration
will select and display only the contents of the firstname and lastname columns.
One last basic SQL query which you can use to manipulate data in a database is the DELETE query. The format is similar to the SELECT query.12DELETE FROM registrationWHERE id = 2
The above query will delete the row that has a value of 2 in the id column of our sample table resulting in our table looking like this:
id firstname lastname 0 Ben Walker email@example.com 1 Tom Fisher firstname.lastname@example.org
What we have listed above are just the beginner queries in SQL that can be used in RDBMS to interact with data in relational databases. As the data you store becomes larger and the programs you build become more complicated, the SQL queries you will need to run to manipulate your data become more complex. There are a few resources online you use to learn more about SQL and databases. W3 Schools is just one of such resources which can teach you how to use SQL in conjunction with other programming languages to build applications.February 23, 2020 at 11:13 am #86534Spectator@ebere
Thank you very much. Clear and concise 🤝
- You must be logged in to reply to this topic.