Structured query language (SQL) is a database computer language used to store, manipulate, and query relational data. Since it became a standard for international organization for standardization (ISO) in 1987, SQL has become widely used in data banking and utilization. It operates with these techniques-
Data definition language (DDL)
It is used to create, modify, and drop the structure of database objects. It uses these commands- create, Alter, Drop, Rename, and Truncate.
Data manipulation language (DML)
stores, retrieves, modifies, and deletes data- using the commands, Select, Insert, Update, and Delete.
Transaction control language (TCL)
It uses the commands, Commit, Rollback, and Savepoint to manage data changes.
Data control language (DCL)
DCL uses Grant and Revoke commands to provide security to database objects.
SQL uses a group processing method to store, modify, retrieve and delete data. This Database activity is enabled by some other syntax techniques such as
SQL uses the select statement more than any other tool, to query or retrieve data from database. The select statement has a syntax consisting of-
Column list from table name
Group by clause
Order by clause
But first of all you need a table to get other SQL tools to work. SQL Database tables are the foundation of every Relational Data Management System (RDMS). You need to-
- Create a table using the key word create table
- Then add the word defining the purpose of the table
- Make bracket of names defining each column
- Define the Data according to their types
Verify the success of your table by looking at the server display message. (Server varies) There are MS, SQL server MYSQL, DB2, oracle etc.
To insert data, you need to specify info according to columns, using column 1, column 2, column 3 etc. You may use Aliases to define a data input, e.g you may use (com) to represent company, or (st) to represent staff.
Select could be used to enable other tools- that's if you want to. This is because in select statement, only select and from are mandatory. Below is a table showing how an organization uses its SQL Table.
Here, you only need select to choose company, and where to choose date, then you will get the number of staff as at the time of your query. E.g Select Palness + where 20/06/11 =100 the number of staff appears as 100.
But you can add any member of the SQL syntax to retrieve data, it all depends on the specifics. That means that in another setting, you may need to add order by clause or order by group thereby adding (a group of staff) to get your result. That is select Palness + where 20/06/14 + order by group staff = 300.
The SQL group by works with other group members in retrieving data from columns, e.g when you need something more than the number of staff in a year. Maybe the number of male or female staff. Looking at the table below, you have to select BizHam + where 22-06-12 order by group F = 100
Order by clause
This can be used in a very simple way. You have to select from column 1 column 2 and maybe column 3, it depends on what you want to sort your data by e.g If you want to sort your data by the number of male staffs at constative in 23-06-14. Select constative order by clause M work 23-06-14 = 180 male staffs.
To update data information you need to go the table, choose columns to be updated, add the new values. Note that you will need where clause to make the changes, because your updates will need to be identified in the update statement by where clause.
To delete from the table you will use where clause because it identifies the rows in the table that got deleted. When your intention is to delete only a row, and you do not use the where clause, so many other rows get deleted as well.
In our next article we will be discussing other elements of SQL.