Advanced Features in
SQL
Outline
1. Views
2. Authorizations
3. Indexes
Views
A view is a virtual table that is based on the result of a SELECT query.
A view doesn't store data itself; instead, it's a saved query that can be used to retrieve and
display data from one or more underlying tables.
Views are a valuable tool in SQL for enhancing data organization, security, and query
simplification.
They are particularly useful when dealing with large, complex databases and when multiple
users or applications need controlled access to the data.
Manipulating Views
1. To create views
CREATE VIEW view_name AS
SELECT column_list
FROM table_name(s)
[WHERE condition];
2. To drop views
DROP VIEW view_name;
Example
CREATE VIEW EmployeeDetails AS
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
WHERE Department = 'Sales';
Materialized views
Materialized views are a type of view in a database system that stores pre-calculated results of
a query.
Instead of executing the query every time it's needed, the database system can simply access
the materialized view, which can significantly improve performance, especially for complex or
frequently executed queries.
The materialized view is periodically refreshed to ensure that its data is up-to-date with the
underlying base tables. This can be done manually, automatically, or based on triggers.
CREATE MATERIALIZED VIEW view_name AS
SELECT column_list
FROM table_name(s)
[WHERE condition];
Refreshing a materialized view
REFRESH MATERIALIZED VIEW view_name;
OR
Use the ON COMMIT REFRESH clause when creating the materialized view to refresh it after changes
to the underlying tables.
Drop a materialized view
DROP MATERIALIZED VIEW view_name;
Authorization
. Users in SQL
Authorized entities that can access and manipulate data within a database system. Users are
granted specific privileges to control their actions.
Syntax
To create users:
CREATE USER user_name
WITH PASSWORD 'password’;
To grant privileges
GRANT privilege_list TO user_name;
Example of privileges : SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP
To revoke privileges
REVOKE privilege_list FROM user_name;
User Roles
✓User roles are a mechanism for managing access control in database systems.
✓Assigning users to specific roles enables the controlled allocation of privileges to perform
different tasks within the database.
✓ This helps to ensure data security and prevent unauthorized access.
Syntax
To create role
CREATE ROLE role_name;
Grant privileges to the role:
GRANT privilege_list TO role_name;
Assign the role to a user
GRANT role_name TO user_name;
Example
Create a "manager" role with SELECT, INSERT, UPDATE, and DELETE privileges on the "orders"
table.
CREATE ROLE manager;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO manager;
Create a "salesperson" role with SELECT and INSERT privileges on the "orders" table.
CREATE ROLE salesperson;
GRANT SELECT, INSERT ON orders TO salesperson;
Example
o Assign the "manager" role to a user named "john_doe".
GRANT manager TO john_doe;
o Assign the "salesperson" role to a user named "jane_smith".
GRANT salesperson TO jane_smith;
Indexes in SQL
An index in a database is a data structure that helps to speed up data retrieval operations.
It acts as a lookup table, allowing the database system to quickly locate specific rows based on
the values of one or more columns.
When an index is created on a column, the database system stores a sorted list of values for that
column, along with pointers to the corresponding rows in the table.
When a query is executed, the database system can use the index to efficiently locate the
relevant rows without having to scan the entire table.
When to Use Indexes
Frequently queried columns: If a column is frequently used in WHERE clauses or JOIN
conditions, creating an index on it can significantly improve query performance.
Unique values: If a column contains unique values (e.g., a primary key), creating an index on it
can be very beneficial.
Joined columns: If two tables are frequently joined on a column, creating indexes on both
columns can improve join performance.
Syntax
Creating Single-Column Indexes
CREATE INDEX index_name ON table_name (column_name);
For example, to create an index on the customer_id column of the orders table:
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
Creating a composite indexes
According to Index in SQL, an index on two or more columns of a table is known as a composite
index. It may create the same index with a different number of columns.
CREATE INDEX Name_index ON db_table(column1,Column2…….);
Example
Consider a database with a customers table and an orders table. To improve the performance of
queries that join these two tables on the customer_id column, you could create indexes on the
customer_id column in both tables:
CREATE INDEX idx_customers_customer_id ON customers (customer_id);
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
By using these indexes, the database system can efficiently locate matching rows in both tables
and join them together.