Getting Started with SQL
SQL Basics
For us to manage data in a database we need to understand the language in which the database operates, and this is where SQL comes in.
In this article, we will learn what SQL is, its applications, its syntax, a brief introduction to databases and tables, then we’ll write simple SQL queries.
SQL is an acronym for Structured Query Language which is a language designed for the storing, manipulating, and retrieving data in a relational database. A relational database contains multiple tables that relate to each other.
SQL is a declarative (non–procedural) programming language which means that we will focus on writing queries to solve problems rather than worrying about their flow. SQL is not case sensitive and is easy to learn & understand because it uses simple English sentences.
Applications of SQL
SQL is used for database administration, retrieving information for analytical purposes, etc. It is also used in real-life applications like; ATMs, POS systems, Payroll systems, Websites, etc.
SQL Syntax
Syntax refers to the rules that define the composition of a language. The main elements of SQL’s syntax are:
1. Data Definition Language (DDL): This is used to define the database, create and modify the structure of objects in the database.
- CREATE — Create a database and its objects like; table, index, etc.
- ALTER — Alters the structure of the existing database
- DROP — Deletes elements from the database
- TRUNCATE — Removes all records from a table
- COMMENT — Adds comments to the data dictionary
- RENAME — Renames an object
2. Data Manipulation Language (DML): is used to modify and manipulate the database.
- SELECT — Retrieves data from a database
- INSERT — Inserts data into a table
- UPDATE — Updates data within a table
- DELETE — Delete all records from a database table
3. Data Control Language (DCL): This language deals with the rights and authorizations of the database system.
- GRANT — Permit users to access the database
- REVOKE — Removes users’ access privilege
4. Transaction Control Language (TCL): This is used to manage transactions in the database and to manage the changes made to the data in a table by DML statements.
- COMMIT — Commits a Transaction
- ROLLBACK — Rollback a transaction in the event a problem
- SAVEPOINT — Reverses the transaction points for a group
- SET TRANSACTION — Define the transaction’s characteristics
Brief Introduction to Database and Table
A database is an organized collection of data that is stored and retrieved electronically from a system.
A table is a collection of related data organized in rows and columns. A database can contain more than one table. To illustrate the properties of a table, I’ll use this example:
Considering the Titanic dataset above, the table contains data about the passengers in the Titanic ship. Here’s how we can read this information: the first passenger did not survive, his ticket class was 3, his name was Mr. Owen Harris, he was of the male gender, he was 22 years and so on…
Each of these details is a data value and all these details per passenger makes up a record or row while the field is the column.
An entity is a real-world object that can be easily identified. A row is a horizontal entity while a column is a vertical entity.
Basic SQL Query
From the data in the table above, let’s assume that we want to get the following information;
- Total number of passengers.
- Details of the female passengers.
To get the total number of passengers, we use the query;
SELECT COUNT(*) FROM Titanic;
This query means retrieve all the data from the Titanic table and count it. (Titanic is the name of the table, you can decide to rename yours).
From the output above, there were 891 passengers.
2. SELECT * FROM Titanic WHERE sex = ‘female’;
This query means retrieve all the data from the Titanic table whose gender is female and display it.
I hope this article was helpful, If you need clarification, please feel free to send a Direct Message to me on Twitter or drop a comment in the response box below.
