DEV Community

Cover image for SQL for dummies
Yel Martinez - Consultor Seo
Yel Martinez - Consultor Seo

Posted on • Updated on

SQL for dummies

SQL is a structured query language through which it is possible to access, manage and retrieve data contained in a relational database, including the creation of databases, the deletion and recovery of rows or the modification of these, etc. SQL is an ANSI (American National Standards Institute) standard language, although there are multiple versions of this language.

SQL is the standard computer language used in relational database management systems (RDMS) such as MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server.

SQL Functionalities

SQL is one of the most widely used query languages in relational database management because of the utilities it offers users:

  • access to data contained in relational databases, 
  • the description of the data,
  • defining the data within a database,
  • the manipulation of these,
  • embedding in other languages through the use of SQL modules, libraries and pre-compilers
  • the creation of databases and tables,
  • the **[views, procedures and functions](https://www.coursera.org/account/accomplishments/verify/SF9MPC4NSUQL?utm_campaign=sharing_cta&utm_content=cert_image&utm_medium=certificate&utm_product=course&utm_source=link)**,
  • establish permissions on tables, procedures and views.
  • Among other functionalities.

Components involved in the SQL process

When a SQL command is executed for an RDBMS, the system determines the best way to process that request, while the SQL engine determines how to interpret that command.

Among the components involved in this process are the query dispatcher, optimization engines, the classic query engine, the SQL query engine, etc.

Note that the classic query engine handles non- SQL queries, just as the SQL query engine does not handle logical files.

Main SQL commands

The main standard SQL commands with which to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands are classified in groups according to their typology.

RDBMS - relational database management system

DBMS - database management system

RDBMS - Relational Database Management System

The Relational Database Management System, known as RDBMS, is a database management system (DBMS) based on Edgar Frank Codd's relational model. On this relational model the ANSI and ISO standards of the management language, definition and manipulation of the SQL relational databases have been defined. It is the basis of SQL and other database systems such as MS SQL Server, Oracle, MySQL or Microsoft Access.

Parts of a table in SQL

Explaining what a table is in SQL

In a relational database management system, data is stored in database objects called tables, which collect multiple data entries and consist of numerous rows and columns.

The table is the simplest and most widely used form of data storage within a relational database.

Explaining what a field is in SQL

Each of the SQL tables are divided into fields or columns within a table, designed to contain information specific to each record in the corresponding table.

Explaining what a record or row is in SQL

A record is a horizontal entity in a table. Each individual entry in a table is a data row.

Explaining what a column is in SQL

A column is a vertical entity in a table, which contains information related to a specific field within the table.

Explaining what a NULL value is in SQL

A field with a NULL value is a field that is left blank during the creation of a record. In a table, a NULL value represents a field with no value, other than a zero value or a field with spaces.

Constraints in SQL

When we talk about constraints in SQL, we refer to the rules that have been defined for the data columns in a table. These constraints are used to limit the type of data that a table can contain, guaranteeing the reliability of the data.

These restrictions can be defined at column or table level, applying only to one column in the first case and to the whole table if it is the second case.

Some of the most common restrictions you can find in SQL are

  • NOT NULL constraint (makes a column unable to contain a NULL value)
  • DEFAULT restriction (assigns a default value to a column when one is not specified)
  • Single restriction (makes the values of one column different)
  • PRIMARY KEY (makes the identification of each row or record unique within a database table)
  • Foreign key (makes the identification of each row or record unique in any other database table)
  • Checking restriction (ensures that the values in a column meet specific conditions)

Categories of Data Integrity in SQL

The data integrity categories of each RDBMS are:

- Entity integrity (no duplicate rows in a table)

- Domain integrity (restricts the type, format, and value range that applies to valid entries for a column within a table)

- **[Referential integrity](https://www.linkedin.com/in/yel-martinez-informatica-seo-desarrollo-web-posicionamiento-buscadores/details/recommendations/)** (makes rows in a table that are being used by other records impossible to delete)

- User-defined integrity (other specific rules not included above apply)

Standardization of a SQL database

Standardization of a SQL database consists of a series of guidelines established as a guide for the optimal creation of a database structure.

SQL database normalization is the process of organizing data in a database efficiently, eliminating duplicate data and ensuring that data dependencies make sense. Normalization seeks to reduce the amount of space used in a database and ensure that data is stored in a logical manner.

Basic SQL Syntax

A syntax is a unique set of rules and guidelines. In [SQL, the syntax](https://www.credly.com/badges/b0809aba-1bde-43fb-aa29-7815fdef90a2) states that all declarations start with one of the keywords SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and end with a semicolon.

It is important to note that SQL is not case sensitive in SQL declarations, while MySQL is case sensitive in table names, so you will need to handle the table names as defined in the database.

SQL data types

In SQL, the attributes that specify the type of data an object will contain are known as the SQL Data Type.

Each column, variable, and expression has a related data type. These can be used when creating tables by choosing the data type to be used for a table column.

These data types are divided into six categories in SQL Server

data types categories

- Exact numerical data types

- Types of approximate numerical data

- Types of date and time data

- Character string data types

- Unicode string data types

- Binary data types

- Various types of data

The SQL operators

In SQL, when we speak of operators, we refer to reserved words or characters, used mainly in the WHERE clause of a SQL statement, in order to carry out arithmetic operations, comparisons, etc.

These operators are used to specify conditions within a SQL statement.

Types of SQL operators include arithmetic operators, comparison operators, and logical operators.

Top comments (0)