1_Fundamental SQL Concepts

Question 1

Relational Database

A relational database is one based on the relational data model. This means the database is a collection of relations. Those relations are shown as tables, which consist of columns, rows, and values. The relational database aims to minimize or completely avoid data redundancy, leading to data integrity and speeding up its retrieval.

Question 2

Relationships in the database

The relationship defines the type of connection between the tables in the database. There are three main types of relationships:

• one-to-one relationship (1:1)
• one-to-many relationship (1:N) or many-to-one relationship (N:1)
• many-to-many relationship (M:N)

Question 3

Database normalization

Database normalization is a process of organizing data in the database to achieve its purpose: data integrity, its non-redundancy, and speed of retrieval.

Question 4

Constraints

The constraints are the rules that define what type of data can and can’t be entered as a value in the database.This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

The most common attributes are:

• NOT NULL: Ensures that a column cannot have a NULL value
• CHECK: Ensures that the values in a column satisfies a specific condition
• UNIQUE: Ensures that all values in a column are different
• PRIMARY KEY: A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY: Prevents actions that would destroy links between tables. Refers to the PRIMARY KEY in another table.

Question 5

Indexes

The indexes are structures in the databases created to tune the database performance. They are used to speed up data retrieval. The types of indexes are:

• clustered index
• non-clustered index
• unique index
• filtered index
• columnstore index
• hash index

Question 6

Data Definition Language (DDL)

• CREATE
• ALTER
• DROP
• RENAME
• TRUNCATE
• COMMENT

Question 7

Data Manipulation Language (DML)

• INSERT
• UPDATE
• DELETE
• MERGE
• CALL
• EXPLAIN PLAN
• LOCK TABLE

Question 8

Data Query Language (DQL)

• SELECT

Question 9

Data Control Language (DCL)

• GRANT
• REVOKE

Question 10

Cursor

A cursor is a temporary memory or workstation. They store database tables, and there are two main types:

1. Implicit cursors: allocated by the SQL server when users perform DML operations
2. Explicit cursors: based on user inputs.

Question 11

Trigger

A trigger is a stored procedure that automatically runs when a certain event occurs in the database server.

For example, DML triggers run when a user tries to modify data. Triggers can be used to:

• Audit database activity
• Implement rules
• To enforce referential integrity