Understanding SQL Basics, DBMS, Concepts, and Derived Columns

Conquer SQL: From Zero to Resume — Part 1

SQL = Structured Query Language

Contents

• What is SQL
• What is DBMS
• How to build a concept of SQL
• How to make derived calculated columns in SQL

Photo by Mika Baumeister on Unsplash

What is SQL

SQL is the language for performing data manipulation on a database.

• Data Manipulation — CRUD

1. Create
2. Read
3. Update
4. Delete

👆Back to the contents

What is DBMS

DBMS = DataBase Management System

Through a DataBase Management System, SQL can “automate” and “scale” data operations for CRUD (Create, Read, Update, Delete) on the database.

😸😸😸😸😸😸😸😸😸😸😸😸😸😸😸😸😸😸😸😸😸😸😸😸

• Categories of DBMS:

1. RDBMS = Relational DataBase Management System
2. NoSQL DBMS = Not only SQL DataBase Management System

• Common RDMS

1. Oracle Database
2. Microsoft SQL Server
3. IBM DB2
4. Open source — SQLite
5. Open source — MySQL
6. Open source — PostgreSQL

The following record of SQL learning progress uses the open-source SQLite.

Get Mini Cactus’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

The data tables stored in a database have a two-dimensional data structure:

• The first dimension is referred to as observations (Rows).
• The second dimension is referred to as variables (Columns).

👆Back to the contents

How to build a concept of SQL

There are four key concepts of SQL:

1. SELECT … FROM … Select from a table using SELECT and FROM
2. — — or /*……*/Adding comments in SQL
3. AS Assigning aliases to columns in query results
4. DISTINCTDisplaying unique values in query results

Two ways to Add comments in SQL

• Single-line Comment:

-- comments

• Multi-line Comment:

/*
...comments
...comments
...comments
*/

👆Back to the contents

How to make derived calculated columns in SQL

In addition to specifying column positions of data tables after SELECT, commonly used constant types include:

• Integer (INTEGER)
• Floating-point numbers with decimals (REAL)
• Text enclosed in single or double quotes (TEXT)
• Null or missing values (NULL)

Basic Numerical Operators

Numerical Operator: Description

+ : Addition
- : Subtraction
* : Multiplication
/ : Division
% : Modulus(Remainder)
(): Parentheses(Precedence)

• Note: When using / to divide two integers, note that the resulting derived field will still be of integer type. If you want the derived field to be floating-point, add a decimal point to either the numerator or the denominator.

Basic Text Operators

||: Two vertical bars (can be entered using Shift + \) are used to concatenate text.

👆Back to the contents

SQL: Mastering Functions — Universal and Aggregate Unveiled