Learning Beginner level SQL Syntax can seem challenging at first but its done step by step, command by command. We learn one command, master it, learn a new command and build up on top of the previous command. Eventually you are writing advanced SQL queries like the best of them.
What is CTF?
CTF stands for Capture The Flag”. It’s a CyberSecurity Competition that challenges contestants to solve a variety of tasks ranging from using Open Source Intelligence for tracking down hidden information across the internet, to programming exercises, cracking encryption and to finding your way into a server to capture or find a Hidden Flag.
There is nearly always a Hidden Flag, in the code, on the internet or buried deep inside a unsecured Database or Web Application server. For IT security experts and IT experts in general it is a deeply rewarding and educational experience.
However the knowledge required to become proficient in all the topics and be successful in the challenges can be very daunting and the Knowledge difficult to acquire with out years of experience. So my blog should help with some of the knowledge gathering.
What is SQL
SQL stands for Structured Query Language and is a programming language used by nearly all relational databases to query, manipulate, and define data, and to provide access control. It was first developed at IBM in the 1970s with Oracle as a major contributor, which led to implementation of the SQL ANSI standard. SQL has spurred many extensions from companies such as IBM, Oracle, and Microsoft.
Relational Database ?
RDBMS (Relational Database Management System) is the basis for SQL. Items in a relational database are organized as a set of tables with columns and rows. Relational database technology provides the most efficient and flexible way to access structured information.
Set-Up
Database Host
In a previous post I installed MariaDB and set up a database. So I will use this database for all the commands in this post. My database is running with the following config.
| IP Address | 192.168.1.163 |
| Port | 3309 |
| User | root |
| Password | mypassword |
| Database Name | employees |
Beginner SQL Syntax and Commands
select
The “select” command selects data from a database. In the example below it selects all rows in the columns called “first_name” and “last_name” from the table called “employees“.
select first_name,last_name from employees;where
The “where” clause is used in conjunction with “select” and adds a filter. In this example the “select” statement is extended to add a filter. The example will only display columns from data where the row are equal to the string ‘227474’.
select first_name,last_name from employees where emp_no = '227474';
limit
Sometimes the “select” clause has too many answers. We can use “limit” to reduce that to a fixed number. The example limits the output from the “select” query to a result of 5.
select data from table limit 5;count
The count statement just counts the number of rows returned.
select count(first_name) from employees;
distinct
The select query can return many rows of data that is the same. When we only need one of each result we can use “distinct”
In the previous example “count” returned 300024 results, but how many were distinct?
select count(distinct first_name) from employees;
union
The UNION operator is used to combine the results of two or more “select” statements. Note, every “select” statement within “union” must have the same number of columns and must be of the same data type.
The below example only selects two column from each table
select first_name,last_name from employees where emp_no = '227474' union select first_name,last_name from employees where emp_no = '242779';
The above example is selecting the same columns (first_name, last_name) from the same table (employees). However this does not have to always be the case. With the “union” statement you can “select” any column from any table you have access too as long as you follow the rules.
In the below example the first “select” is grabbing the first_name and last_name from employees but the second “select” is grabbing the first_name and, hire_date.
select first_name,last_name from employees where emp_no = '227474' union select first_name,hire_date from employees where emp_no = '242779';
group_contact
Using the “group_concat” statement allows the concatenation of multiple rows into a column. From the union example above we are limited to only two columns in the union select statement but using “group_concat” we can embed multiple columns into a single column.
select first_name,last_name from employees where emp_no = '227474' union select first_name,group_concat(first_name,hire_date) from employees where emp_no = '242779';
It looks a bit messy so we can clean it up a bit with delimiters ” || “. You can read more about delimiters in the next section on String Manipulation.
select first_name,last_name from employees where emp_no = '227474' union select first_name,group_concat(first_name, " || ", hire_date) from employees where emp_no = '242779';
Wrap-Up
As a Beginner, SQL Syntax can seem intimidating but as you use it more and more, you will become familiar and confident with using it. Stick with it, its worth it.
Support
I really enjoy making this content and if you would like to support the cost of keeping this site up and running, please make a purchase through one of my affiliate links.
Be First to Comment