SQL (pronounced as “sequel”)stands for Structured Query Language. It’s a standard programming language used to manipulate and work with relational databases. SQL a language developed in the 1970s at IBM has continuously evolved to meet the demanding needs of modern database times and it is a programming language still in very high demand due to extreme data volumes generated every second by organisations around the world. It is used by data analysts, app developers, web developers, data scientists, among categories. If you’re interested in appreciating the power of this language, I found these links so relevant to you. You can consider visiting them.
- Want a Job in Data? Learn SQL. (dataquest.io)
- 5 Reasons SQL Is The Need-to-Know Skill For Data Analysts (northeastern.edu)
SQL has got different flavours depending on the database software you choose to use. For example, you may find some few statements used in Oracle database that don’t exist in PostgreSQL. Regardless of the database platform you choose, the syntax is the same with very minor differences. Therefore, you don’t have to worry about the database platform you consider while learning SQL. For our case, we shall use Oracle Database software as our basis for learning SQL. The knowledge you acquire here, can be used to work with any database you land your hands on.
Categories of SQL Statements
SQL statements are the commands we issue to the database to perform various tasks we want. For example SELECT * FROM customer; is a command (SQL statement) that requests for information from the customers table in the database. These commands or statements are categorised into the following:
Data Definition Language (DDL) . SQL statements under this category are used to create or define different database objects as well as modifying them e.g. tables, users, views, tablespaces, etc. (You don’t have to worry about the database objects now). Examples of such statements include CREATE TABLE, CREATE USER, CREATE VIEW, ALTER, DROP, etc. The statement below creates a faculty table with a column called name.
CREATE TABLE faculty (name VARCHAR(12));Data Manipulation Language (DML). This category consists of SQL statements used to manipulate data in the database. For instance, we need to make a change to the password or to post anything on instagram, such statements are used. Examples of such statements include UPDATE, INSERT, DELETE. The statement below updates the name of a faculty whose id is 1.
UPDATE faculty SET name = 'Computing & Informatics' WHERE faculty_id = 1;Transaction Control Language (TCL). In case you want to save or discard changes that have been made to the data in the database, you use statements under this category ie. ROLLBACK (undoing the changes), COMMIT (saving the changes), SAVEPOINT (undoing the changes to a certain point). Statements under this category work with DML statements (INSERT, UPDATE and DELETE only). The statement below saves the changes that have been. made to the faculty table.
DELETE FROM faculty WHERE faculty_id = 4;
COMMIT;Data Query Language (DQL). There is only one statement (command) under this category ie. SELECT. It is used to fetch data from the database and it is the most used statement. The statement below fetches all records in the faculty table.
SELECT * FROM faculty;Session Control Language. Statements in this category are used to manage user sessions. A user session is a current user login to the database. Simply put, when you login to any app; your session with all your history is started. For instance on facebook, after logging in, you can see all your posts, notifications, feed, etc. e.g ALTER SESSION.
ALTER SESSION SET SQL_TRACE = TRUE;System Control Language. Under this category, you find statements that help in managing the database properties. e.g ALTER DATABASE, ALTER SYSTEM. In case you want to change anything about database properties for example changing where audit trails should be kept, you work with system control statements.
ALTER SYSTEM KILL SESSION '39, 23';Session Control Language and System Control Language statements are so much administrative in nature and it is rare to use them unless with administrative privileges. Much of our time we shall be focusing on other categories.
Data Query Language is brought as an independent category in so many SQL flavours but in Oracle, it is considered part of the Data Manipulation Language category.
Facts about SQL
- Unlike other programming languages, SQL is not a case sensitive language. For example, all these statements will work and will return the same thing.
SELECT * FROM tab;
Select * from TAB;
SeLEct * FrOM TAb;- All SQL statements MUST end with a semi-colon (termination).
SELECT * FROM employees;If we omit the semi-colon, the statement will not run unless we add it there. Therefore, never leave it behind.
- Data in the database tables is case sensitive. Let me give an example. We have the following data in the database.
| student_id | student_name | programme |
| 1 | Karitus | Medicine |
| 2 | Angellah | Data Science |
If we’re going to search data about students in the students table, data has to match the case. For example, If we’re looking for a student called Karitus, we have to search for this student using the exact case. If we write angellah, nothing will be returned. Just like ANGELLAH won’t work. We have to search for ‘Angellah’.
