Data Control Language

Advanced Techniques in SQL

1. ACID

Now we explore the basic principles of ACID (Atomicity, Consistency, Isolation, Durability) in database management. Learn how these principles ensure reliable transactions by guaranteeing complete operations, keeping data consistent, isolating concurrent transactions, and ensuring permanent data storage.

Course Overview

What is ACID?

Atomicity

Challenge: Transaction Creation

Consistency

Isolation. Read Committed and Uncommitted Levels

Isolation. Repeatable Read and Serializable Levels

Challenge: Specifying Isolation Level for Transaction

2. Query optimization.Indexes

Explore how to enhance the efficiency of your SQL queries through index optimization. Delve into the significance of indexes in database performance, learning how to design, implement, and utilize them effectively. Discover strategies for choosing the appropriate indexes, understanding their impact on query execution plans, and optimizing query performance through index selection and utilization.

What are Indexes

B-tree Indexing

Challenge: Creating B-tree Index

Hash Indexing

Data Caching

General Optimization Strategies

Challenge: Query Optimization

3. Some Additional Topics

This section explores pivotal topics such as Triggers, Window Functions, and DCL (Data Control Language), offering comprehensive insights into their implementation and optimization strategies. Furthermore, it navigates the landscape of NoSQL DBMS, providing a thorough understanding of its architecture, advantages, and practical applications.

Triggers

Challenge: Creating After Trigger

Window Functions

Types of Window Functions

Challenge: Using Window Function

Data Control Language

Views

NoSQL

Data Control Language (DCL) is a subset of SQL used to control access to data stored in a relational database management system.
DCL commands are primarily concerned with granting or revoking privileges on database objects such as tables, views, and schemas.

DCL commands

The two main DCL commands are:

GRANT: This command is used to give specific privileges to users or roles;
REVOKE: This command is used to remove specific privileges from users or roles that have been previously granted.

Privileges

Privilege	Description
`SELECT`	Read data from a table or view.
`INSERT`	Insert data into a table.
`UPDATE`	Update data in a table.
`DELETE`	Delete data from a table.
`EXECUTE`	Execute stored procedures or functions.
`ALL PRIVILEGES`	Grant all available privileges on an object.
`CREATE`	Create new objects within a database or schema.
`CONNECT`	Connect to a database.

Objects

Object	Description
Tables	Control data manipulation operations.
Views	Control access to underlying data.
Schemas	Control access to all objects within a schema.
Databases	Control access to entire databases.

Implementation

To grant some privileges for an object in SQL for a particular user we can use the following statement:

There are 3 types of roles in DB to which you can grant some privileges:

user: An individual database user;
role: A database role, a named group of privileges that can be assigned to users (e.g. admin, developer, analyst);
PUBLIC: A special keyword that grants the specified privileges to all users.

We can grant a role to a user using the following statement:

Finally, we can revoke previously granted privileges using the following statement:

Example

Here are some examples of how to use the GRANT command to assign different privileges on the bankaccounts and userlogs tables to different roles and users.

Now we can revoke some of the granted privileges:

Все було зрозуміло?

Секція 3. Розділ 6

Зміст курсу

Advanced Techniques in SQL