SQL Pt 1: Syntax Languages & Statements

Intro:

If you’ve worked on the backend of a project, or intend on building your own API, knowing how to access and manipulate the database(s) that you are working with is crucial for your application to run correctly. This is an introduction on SQL’s syntax languages and the theory operating behind the scenes. Lets get into it.

Why Databases?

Databases are intended to organize huge amounts of data that can be quickly retrieved upon a user’s request. In order to accomplish this, databases need to be compact, well-structured, and efficient in terms of speed.

The thing is, when you have huge amounts of data, the more sluggish a database becomes, and the retrieval process gets slower.

So how do we go about resolving this conflict?

Side-Bar:

There are two main types of databases: relational and non-relational.

The long and short is that relational databases (like SQL) are structured, as in, the data it stores is in the form of organized tables (tabular data: columns and rows). Relational databases create relationships between different objects within the database in order to efficiently access data that the user asks for. Non-relational databases forego these relationships and is document-oriented, meaning the data gets stored in a list-like format. Non-relational databases tend to rely on more complex systems and place a higher emphasis on using a mathematical and programmatic approach.

Welcome SQL

SQL is a programming language that lets you execute commands that allows you to create and manipulate relational databases. It is mainly regarded as a declarative language (emphasis on “what” not the “how”), and contains 4 distinct syntax languages that are used to execute the user’s commands.

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Control Language (DCL)
  • Transaction Control Language (TCL)

Each of these languages are comprised of statements that follow a particular syntax, allowing you to perform various commands and operations within the database.

The CREATE statement:

  • used for creating entire databases and database objects as tables
  • Syntax:

CREATE TABLE object_name (column_name data_type);

  • ex. CREATE TABLE sales (purchase_number INT);
  • the above command creates a table called ‘sales’ with a single column ‘purchase_number’ with a data type of integer

The ALTER statement:

  • uses the same syntax structure as CREATE, and used to alter columns (aka ‘fields’) within a table you select
  • once the table is selected, you have the option of using the keywords ADD, REMOVE, or RENAME to alter the table
  • Syntax:

ALTER TABLE table_name

ADD COLUMN column_name data_type;

or

REMOVE COLUMN column_name data_type;

or

RENAME COLUMN column_name data_type TO new_column_name new_data_type;

  • ex.

ALTER TABLE sales

ADD COLUMN date_of_purchase DATE;

  • this statement will add the column ‘date_of_purchase’ to the table ‘sales’ with a data type of date

The DROP statement:

  • will ‘drop’ or delete the entire table from the database
  • syntax:

DROP TABLE table_name;

  • ex.

DROP TABLE customers;

  • this statement will delete the ‘customers’ table from the database

The RENAME statement:

  • will rename a table within the database
  • syntax:

RENAME object_type object_name TO new_object_name;

  • ex.

RENAME TABLE customers TO customer_data;

  • this statement will rename the table ‘customers’ to ‘customer_data’

The TRUNCATE statement

  • will remove all the data within a table, but will not delete the table
  • syntax:

TRUNCATE TABLE object_name;

  • ex.

TRUNCATE TABLE customers;

  • this statement will remove all the data within the ‘customers’ table, but keep the table so it can be used in the future

This comprises the SQL statements found within DDL. With DDL you can:

  • CREATE
  • ALTER
  • DROP
  • RENAME
  • TRUNCATE

*If you haven’t noticed, the syntax so far is very readable and straightforward. Once you get familiar with the commands, making queries becomes almost 2nd nature.

The SELECT statement

  • Allows you to extract only a portion of the table that satisfies given criteria
  • syntax:

SELECT col_name1, col_name2, etc. FROM table_name

  • ex.

SELECT first_name, last_name, phone_number FROM users

  • this will select only the data from the listed columns from the ‘users’ table

The INSERT statement

  • Allows you to insert data into tables i.e. enables you to add more records/rows
  • syntax:

INSERT INTO table_name (column_name1, column_name2, etc.) VALUES (val1, val2, etc);

  • Ex.

INSERT INTO sales (purchase_number, date_of_purchse) VALUES (1, ‘2017–10–11’);

  • the above statement will insert the two new columns with their respective values
  • You have to specify which fields you want to add data to unless you want to add data to all columns (create a new record altogether)
  • So if our sales table only had 2 fields, you could write the above statement like so:

INSERT INTO sales VALUES (1, ‘2017–10–11’);

  • this statement would do the same as the statement above it, just shorter code

The UPDATE statement

  • syntax:

UPDATE table_name

SET column_name = new_data_value

WHERE column_name = data_value

  • ex.

UPDATE sales

SET date_of_purchase = ‘2017–12–11’

WHERE purchase_number = 1

  • This will update the record on the ‘sales’ table with purchase_number equal to 1, thereby changing the ‘date_of_purchase’ to the 12th instead of its previous data value

The DELETE statement

  • Similar to TRUNCATE statement, but allows you to delete precisely what you want to remove by utilizing the WHERE clause
  • syntax.

DELETE FROM table_name

WHERE

column_name = data_value;

  • ex. without the WHERE clause and acts like TRUNCATE

DELETE FROM sales = TRUNCATE TABLE sales

  • ex. With the WHERE clause

DELETE FROM sales

WHERE

Purchase_number = 1;

  • This statement will delete only the record with the purchase_number = to 1

This comprises the SQL statements found within DML. It includes the following keywords/statements

  • SELECT …. FROM
  • INSERT INTO …. VALUES
  • UPDATE …. SET …. WHERE
  • DELETE …. FROM …. WHERE

The DCL contains only two statements: GRANT and REVOKE.

These two statements allows the database to determine who has permissions to certain resources within the database.

The GRANT statement

  • will grant permissions to a specific user
  • syntax:

GRANT type_of_permission ON database_name.table_name TO ‘username’@’localhost’;

  • ex 1.

GRANT SELECT ON sales.customers TO ‘george’@’localhost’;

  • this statement will allow ‘george’ nothing but the SELECT statement to the ‘customers’ table within the ‘sales’ database
  • ex 2.

GRANT * ON sales.customers TO ‘george’@’localhost’;

  • this statement will allow ‘george’ access to all the query statements listed above

The REVOKE statement

  • is used to revoke the permissions and privileges of users working within the database
  • syntax:

REVOKE type_of_permission ON database_name.table_name FROM ‘username’@’localhost’;

  • ex.

REVOKE INSERT UPDATE DELETE ON sales.customers FROM ‘george’@’localhost’

  • the above statement removes the INSERT, UPDATE & DELETE actions from ‘george’

Not every change made to a database is saved automatically, TCL statements are used to allow for persisting changes, and undoing changes with their two statements: COMMIT and ROLLBACK

The COMMIT statement

  • will save the changes made to the database and thus, allow other users to access the modified version of the database
  • used in relation to INSERT, DELETE and UPDATE: after using these commands you will need to use COMMIT to save the changes
  • To save the changes simply type:

COMMIT

The ROLLBACK statement

  • will undo any changes made since the last commit; it will revert to the last committed state and all the changes made will be removed
  • to take a step back simply type:

ROLLBACK

Summary

SQL databases are relational databases that use mathematical logic to create relationships between the data stored within tables. The 4 language types allows users to create, access, and manipulate the data stored within these databases like so:

DDL — Data Definition Lang

  • Creation of data

DML — Data Manipulation Lang

  • Manipulation of data

DCL — Data Control Lang

  • Assignment and removal of permissions to use the data

TCL — Transaction Control Lang

  • Saving and restoring changes to the database

--

--

--

Just another one of those dreamers with a sparkle in his eyes.

Love podcasts or audiobooks? Learn on the go with our new app.

Installing of Hadoop and Using Hive to Get Data

Scrum Is Not Always A Good Idea And Here’s Why.

Scraping Hansard with Python and BeautifulSoup

Trading the Black Indicator

FF Multi-User Multi-Display Solution

When EntityFramework(Core) gets tricky

Mutable and Immutable Data Type

Eat that Frog with a Pomodoro.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Janu Sung

Janu Sung

Just another one of those dreamers with a sparkle in his eyes.

More from Medium

Git Introduction 101

Version Controlling, GIT, NO SQL

Difference Between OOP And POP (Blog # 1)

How to deal with a cyclic foreign key constraint using PostgreSQL?