10 SQL Concepts That Every Developer Should Know

SQL, that is Structured Query Language is used to perform database operations such as creating, deleting, fetching, and more. SQL Databases are the heart of most of the applications. Below are some SQL concepts that you should know,

1. SQL is Relational Database

There are various database systems such as Relational, Hierarchical, Document, etc. Out of which SQL is a Relational Database. RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. In simple terms, RDMS is a database where data is stored in the form of tables viz. In rows and columns. Thus, the table is basically a collection of related data entries and it consists of numerous columns and rows.

2. Keys in SQL

A key is a single or combination of multiple fields in a table. It is used to fetch or retrieve records/data-rows from the data table according to the condition/requirement. Keys are also used to create a relationship among different database tables or views. Some of the SQL keys are,

• Primary Key - A primary key is a field in a table that uniquely identifies each row/record in a database table. Primary keys must contain unique values. A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.
• Foreign Key - A foreign key is a key used to link two tables together. This is sometimes also called a referencing key. The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.
• Unique Key - A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table. You can say that it is little like a primary key but it can accept only one null value and it cannot have duplicate values.

3. Views in SQL

VIEWS are virtual tables that do not store any data of their own but display data stored in other tables. In other words, VIEWS are nothing but SQL Queries. A view can contain all or a few rows from a table. A SQL view can show data from one table or many tables. Views improve the security of the database by showing only the intended data to authorized users. They hide sensitive data and Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.

4. SQL Joins

The SQL Joins is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. It helps to retrieve data from two or more database tables. The tables are mutually related using primary and foreign keys. There are various types of Joins such as Cross Joins, Inner Joins, Outer Joins, Left/Right Joins, etc.

5. Normalization of Database

Normalization is a database design technique that organizes tables in a manner that reduces redundancy and dependency of data. Normalization divides larger tables into smaller tables and links them using relationships. The purpose of Normalization is to eliminate redundant (useless) data and ensure data is stored logically. There are various Normalization rules such as 1NF, 2NF, 3NF, and BCNF.

6. Transactions in SQL

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program. A transaction is the propagation of one or more changes to the database. Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction. When we talk about transactions, the first thing that comes to our mind is the ACID (Atomicity, Consistency, Isolation, Durability) properties.

7. Subqueries in SQL

A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. It is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <,>, >=, <=, IN, BETWEEN, etc.

8. Cloning Tables in SQL

There may be a situation when you just want to create an exact copy or clone of an existing table to test or perform something without affecting the original table. If you are using MySQL, you can handle this situation by using to steps given below −

• Use SHOW CREATE TABLE command to get a CREATE TABLE statement that specifies the source table's structure, indexes, and all.
• Modify the statement to change the table name to that of the clone table and execute the statement. This way you will have an exact clone table.
• Optionally, if you need the table contents copied as well, issue an INSERT INTO or a SELECT statement too.

9. SQL Sequences

The sequence is a set of integers 1, 2, 3, … etc that is generated and supported by some database systems to produce unique values on demand. A sequence is a user-defined schema bound object that generates a sequence of numeric values. They are frequently used in many databases because many applications require each row in a table to contain a unique value and sequences provide an easy way to generate them.

10. Temporary Tables in SQL

There are RDBMS, which supports temporary tables. Temporary Tables are a great feature that lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables. The most important thing that should be known for temporary tables is that they will be deleted when the current client session terminates.

What are your views on SQL? Which is better? SQL or NoSQL? Let me know in the comments!