logo

SQL for Beginners: Understanding Basic Syntax

blog-image

STRUTURED QUERY LANGUAGE (SQL):

Is a language that has been designed to manage, control, and manipulate relational database. As a Techie, be it a data analyst, scientist, engineer, programmer, etc., SQL is a must-know as its importance cannot be overemphasized.

SQL has lots of statements that can be used to navigate, query, and manipulate data in its working environment, but I will explain the commonly used in the beginner’s query language process. Always remember that SQL is an English Language tool and your understanding of basic English Language makes it even easier to understand and master.

A few of them include:

CREATE TABLE: This syntax is used whenever a table wants to be created.

The process is to write a query e;g

  1. CREATE TABLE, and the right after it should be the name of the table that you want to create e.g Customer
  2. CREATE TABLE customer, Once the name of the table has been identified, the next thing is to open a bracket and include the headers of the tables as well as the data types.
  3. CREATE TABLE customer (customer_name VARCHAR, age INTEGER);
    This will create a table with 2 columns. Table name = CUSTOMER. Column 1 = customer_name, Column 2 = age.


INSERT INTO: After table has been successfully created, the insert into syntax is used to input values into the already created tables. After the insert into statement has been made, I then list the name of the table that I want to insert the values into, I proceed to indicate what I want to insert (values), before I can start listing them out.
Example: INSERT INTO customer VALUES (“Mike Oliver”, 34);
Notice that for values that are numbers only the quotation mark is not used but for values that are alphabets and or alphabets and numbers, quotation marks are used on them.


DATA TYPES IN SQL:

This explains the kind of data that is stored in table column. This is to ensure that every data is restricted and validated by the data inputs.

SOME COMMON DATA TYPES INCLUDE:
  1. Data type for NUMBERS: For numbers we use INTEGER or INT for small whole number values while BIGINT for larger whole number values. We use FLOAT and DECIMAL for numbers with high and low denominations like money or decimals.
  2. Data type for CHARACTERS: We use VARCHAR for small characters and TEXT for larger characters. By CHARACTERS I mean values that include only alphabets, and or alphabets and numbers combined.
  3. Data type for DATE and TIME: We use DATE for values in date format “YYYY-MM-DD”, YEAR for values with 4 digit represented as year, and TIME for values in the time format “HH:SS:MM”.
  4. Data type in TRUE/FALSE or YES/NO: We use BOOL or BOOLEAN to represent values as such. Sometimes this value comes in form of 1/0 where 1 is TRUE/YES and 0 is FALSE/NO
MORE ON COMMON SQL STATEMENTS
  • UPDATE: This is used when there is a need for an edit a column in a table.
  • SELECT: This is used to pick out the column that one wants to work on or to retrieve information from a table. This is known as the most used statement.
  • FROM: The FROM statement is used to call out tables
  • WHERE: The where statement is used to filter out certain information or details from the tables.
  • GROUP BY: This is used when aggregate functions are included in the select statement. Aggregate functions are functions such as sum, average, count, etc. It is also used when there is no need of repetition.
  • ORDER BY: The order by statement is used to sort data from ascending to descending or descending to ascending.
  • LIMIT: In POSTGRESQL, Limit is the function used to reduce the size or number of data/ information in a query. Example: if I have 1000 rolls but I only want to work with the best 15, I can do thus: ORDER BY (name of the column I want to sort) DESC LIMIT 15; Once this is done and executed, the query automatically reduces the rolls to 15.
  • TO_CHAR: A function used to change the data type from just numbers (907890) to numbers with separators (907,890). This is done for readability.
  • CASE: The case statement was used to create a new column or categorize a column by grouping them in sets. Example is creating a new column of age_group by grouping peoples AGE by 10 . i.e from 10 – 19, 20 – 29, 30 – 39, etc., to one category. Case can also be used to pivot by separating different data in a table and grouping them to get the count.
  • AS: The AS is known as Alias that is used to change or rename the name of a column. Example: SELECT name AS first_name (this means I want the name column to appear as first_name).


Author: David Chinyeaka Obiwulu

LinkedIn: https://www.linkedin.com/in/chinyeaka-obiwulu-7b204a1a4/

Twitter: https://x.com/Oluwayeaka?t=gvo1yusDVkYzDPgqBO9Zog&s=09

Back