The Ultimate Beginner’s Guide to SQL

⛔This article is very important to be able to follow the SQL training articles/videos. We recommend reading the full article in order to understand the up-coming practices.

What is SQL?

SQL stands for Structured Query Language. It’s a domain-specific programming language designed for managing and querying relational databases. SQL allows users to retrieve, manipulate, and manage data stored in a relational database management system (RDBMS).

• Data Manipulation: SQL enables users to manipulate data in various ways, including retrieving, inserting, updating, and deleting records in a database.
• Data Definition: SQL also allows users to define and modify the structure of databases, including creating and altering tables, views, indexes, and other database objects.
• Data Control: SQL provides mechanisms for controlling access to data, such as granting and revoking privileges to users and roles.
• Data Querying: SQL’s primary purpose is to query databases to extract specific information based on defined criteria. It offers powerful querying capabilities for filtering, sorting, aggregating, and joining data.

SQL has become a fundamental skill for database administrators, data analysts, software developers, and anyone working with data-driven applications. Its importance lies in its universality, as SQL is widely supported across different database systems, making it a valuable skill in various industries and domains.

To learn more about relational databases, we invite you to read our article(insert link)

Data types in SQL

In SQL, data types are used to specify the type of data that can be stored in a column. Different SQL database systems may have slight variations in their data types, but generally, the following categories of data types are common across most SQL databases:

Numeric Data Types

1. INTEGER (INT):

• Used to store whole numbers without decimal points.
• Typically, it can store values from -2,147,483,648 to 2,147,483,647.
• Example: 123, -456.

2. SMALLINT:

• Stores smaller range whole numbers.
• Range is typically from -32,768 to 32,767.
• Example: 32767, -123.

3. BIGINT:

• Used for storing very large whole numbers.
• Range is typically from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
• Example: 123456789012345.

4. DECIMAL(p, s) or NUMERIC(p, s):

• Fixed-point numbers with precision p (total number of digits) and scale s (digits after the decimal point).
• Example: DECIMAL(5,2) can store numbers like 123.45 or -12.34.

5. FLOAT:

• Used for approximate floating-point numbers.
• Precision can vary, leading to possible rounding errors.
• Example: 1.23, 4.56E7.

6. REAL:

• Another type of approximate floating-point number but with less precision than DOUBLE PRECISION.
• Example: 123.456.

7.DOUBLE PRECISION:

• Double-precision floating-point numbers with more precision than FLOAT or REAL.
• Example: 1.23456789012345.

Character Data Types

1. CHAR(n) or CHARACTER(n):

• Fixed-length character string.
• If the string is shorter than n, it is padded with spaces.
• Example: CHAR(5) storing ‘abc’ results in ‘abc ‘.

2. VARCHAR(n) or CHARACTER VARYING(n):

• Variable-length character string.
• Can store up to n characters without padding.
• Example: VARCHAR(10) storing ‘hello’ results in ‘hello’.

3. TEXT:

• Variable-length character string with no specific length limit.
• Not available in all SQL databases (e.g., supported in PostgreSQL and MySQL).
• Example: ‘This is a long text string’.

Binary Data Types

1. BINARY(n):

• Fixed-length binary data.
• Similar to CHAR but for binary data.
• Example: BINARY(5) storing \x00\x01\x02 results in \x00\x01\x02\x00\x00.

2. VARBINARY(n):

• Variable-length binary data.
• Similar to VARCHAR but for binary data.
• Example: VARBINARY(5) storing \x00\x01\x02 results in \x00\x01\x02.

3. BLOB (Binary Large Object):

• Used for storing large binary data, such as images or multimedia files.
• Example: Binary data representing an image file.

Date and Time Data Types

1. DATE:

• Stores date values (year, month, day).
• Format: YYYY-MM-DD.
• Example: ‘2023–05–27’.

2. TIME:

• Stores time values (hour, minute, second).
• Format: HH:MM:SS.
• Example: ‘14:30:00’.

3. TIMESTAMP:

• Stores both date and time values with an optional time zone.
• Format: YYYY-MM-DD HH:MM:SS[.fraction].
• Example: ‘2023–05–27 14:30:00’.

4. DATETIME:

• Similar to TIMESTAMP but may vary slightly in behavior across SQL databases.
• Format: YYYY-MM-DD HH:MM:SS.
• Example: ‘2023–05–27 14:30:00’.

5. YEAR:

• Stores year values.
• Format: YYYY or YY.
• Example: 2023 or 23.

Boolean Data Type

1. BOOLEAN:

• Stores TRUE or FALSE values.
• In some databases, implemented as TINYINT with values 0 (false) and 1 (true).
• Example: TRUE, FALSE.

Miscellaneous Data Types

1. SERIAL or AUTO_INCREMENT:

• Auto-incrementing integer values, typically used for primary keys.
• Automatically generates a unique number for each row.
• Example: 1, 2, 3, ….

2. ENUM:

• A string object that can have one of several specified values.
• Example: ENUM(‘small’, ‘medium’, ‘large’).

3. SET:

• A string object that can have zero or more values, each chosen from a list of permitted values.
• Example: SET(‘A’, ‘B’, ‘C’).

Constraints in SQL

Constraints in SQL are rules applied to columns in a table to enforce data integrity and ensure the accuracy and reliability of the data within the database. Constraints can be applied at the column level or the table level. Here are the most common types of constraints in SQL, explained in detail:

NOT NULL Constraint

• Purpose: Ensures that a column cannot have a NULL value.
• Usage: Applied to columns where a value is always required.

UNIQUE Constraint

• Purpose: Ensures that all values in a column or a set of columns are unique across the table.
• Usage: Applied to columns where duplicate values are not allowed.

PRIMARY KEY Constraint

• Purpose: Uniquely identifies each record in a table. A table can have only one primary key, which may consist of one or multiple columns (composite key).
• Usage: Combines the properties of NOT NULL and UNIQUE which means that the field that is specified a primary key could not be unique and should be unique.

FOREIGN KEY Constraint

• Purpose: Ensures referential integrity by linking a column or a set of columns in one table to the primary key of another table.
• Usage: Applied to columns where a relationship with another table is defined.

CHECK Constraint

• Purpose: Ensures that all values in a column satisfy a specific condition.
• Usage: Applied to columns where values must meet a predefined criterion.

DEFAULT Constraint

• Purpose: Provides a default value for a column when no value is specified.
• Usage: Applied to columns where a default value is desirable.

Conclusion:

Mastering SQL is essential for effective database management, with a focus on data types and constraints being particularly crucial. Data types, including numeric, character, binary, and temporal, ensure that data is stored accurately and efficiently. Constraints like NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT maintain data integrity and enforce business rules. Understanding and utilizing these features allow you to create robust, reliable databases, laying a solid foundation for any data-driven application.

We will talk in detail and explain the commands in the following articles.