SQL, or Structured Query Language, offers a structured set of commands to manage databases effectively. These commands encompass keywords and clauses that instruct databases on how to interact with stored data.
Keywords: These hold special meanings within SQL, serving as pivotal instructions guiding database operations.
Commands: These directives, also termed statements, are user-provided instructions that the database executes to perform various operations.
Clauses: They initiate with specific keywords and comprise both the keyword and its corresponding argument, enabling more nuanced and refined operations within SQL queries.
DDL Commands
The CREATE TABLE command in SQL is used to generate a new table in a database. When creating a table, specific columns are defined with their respective data types and, optionally, their sizes. Each table must contain at least one column.
The syntax for the CREATE TABLE command typically follows this structure:
CREATE TABLE Customers (
id int,
Name varchar (50),
age int,
city varchar (50));
This SQL command creates a table named "Customers" with columns representing Customer ID (id) as an integer, Customer Name (Name) as a variable character string of maximum length 50, Age as an integer, and City as a variable character string of maximum length 50. This structure defines the table schema to store customer information within a database.
Types of SQL Constraints:
In SQL, constraints play a crucial role in defining rules or limitations for the data stored within tables, ensuring data integrity and accuracy.
Column constraints are specific rules applied to individual columns. These constraints restrict the type of data that can be inserted into a particular column. They ensure that the data inserted meets certain criteria set for that column.
On the other hand, table constraints are rules that apply to a group of columns within a table. These constraints involve more than one column and are used to define conditions across multiple columns in the table.
For instance, let's consider some common constraints:
NOT NULL Constraint: This constraint ensures that a column cannot contain NULL values, meaning it must always hold some data.
CREATE TABLE Customers (
id int NOT NULL UNIQUE → Unique constraint
Name varchar (50),
age int,
city varchar (50));PRIMARY KEY Constraint: It uniquely identifies each record in the table. It enforces uniqueness and does not allow NULL values.
CREATE TABLE Customers (
id int NOT NULL PRIMARY KEY → Primary Key constraint
Name varchar (50),
age int,
city varchar (50));UNIQUE Constraint: This ensures that all values in a column are unique, except for NULL values. It prevents duplicate entries.
CHECK Constraint: It defines a condition that must be met for data to be entered into a column. For example, ensuring that values fall within a specific range or match a certain pattern.
CREATE TABLE Customers (
id int NOT NULL CHECK(id > 0), → Check Constraint
Name varchar (50),
age int,
city varchar (50));FOREIGN KEY Constraint: It establishes a link between data in two tables, ensuring referential integrity by enforcing a relationship between the tables based on a key.
CREATE TABLE Customers (
id int PRIMARY KEY,
Name varchar (50),
age int,
city varchar (50)
Order_id int FOREIGN KEY REFERENCES Orders (Order_id) → FOREIGN KEY Constraint);
DEFAULT constraint: The DEFAULT constraint within SQL serves the purpose of assigning a predetermined value to a field when no explicit value is provided during data entry. This constraint defines a default value that will automatically populate the field if no other value is specified.CREATE TABLE Customers (
id int PRIMARY KEY,
Name varchar (50),
age int,
city varchar (50) DEFAULT 'Unknown'
Order_id int FOREIGN KEY REFERENCES Orders (Order_id) → FOREIGN KEY Constraint);
- Complete SQL CODE
- CREATE TABLE Customers (
- id int NOT NULL UNIQUE, -- Unique constraint for ID
- Name varchar (50) NOT NULL, -- Name cannot be NULL
- Age int CHECK (Age >= 18), -- Age should be 18 or above
- City varchar (50) DEFAULT 'Unknown', -- Default value for City column
- PRIMARY KEY (id) -- Define primary key constraint for 'id'
- );
Data Manipulation Language (DML)
- DML (Data Manipulation Language) commands in SQL allow users to interact with the data stored within tables. These commands include INSERT, DELETE, and UPDATE.
INSERT Command:
- The INSERT command is utilized to add new records or data into a table. It's structured as follows:
- INSERT INTO <table-name> [column-list]
- VALUES (data1, data2, ...);
- OR
DELETE Command:
The DELETE command removes one or more records from a table based on specified conditions. Its syntax is:
UPDATE Command:
Transaction Control Language (TCL )
TCL (Transaction Control Language) commands are used to manage transactions within a database. Here are some common TCL commands:
(i) COMMIT Command: The COMMIT command is used to permanently save changes made during a transaction to the database. Once committed, the changes become permanent and cannot be rolled back.
Syntax
The syntax for committing a transaction in SQL is:
COMMIT;
SQL Queries
-- Deleting rows from the Customers table where the age is 25
DELETE FROM Customers WHERE AGE = 25;
-- Committing the changes to the database
COMMIT;
Output
As a result, two rows that had an age of 25 have been deleted from the customer's table. Subsequently, if a SELECT statement is executed, the output would reflect the changes made after the deletion.
(ii) ROLLBACK Command: The ROLLBACK command is used to undo transactions that have not been committed. It reverts the database to the last committed state.
Syntax: The syntax for executing a ROLLBACK TRANSACTION in SQL is:
ROLLBACK;
Example: An illustration using table named "Customers":
DELETE FROM Customers WHERE AGE = 25;
-- Deleting records from the Customers table where the age is 25 DELETE FROM Customers WHERE AGE = 25; -- Rolling back the changes made (undoing the deletion) ROLLBACK;
(iii) SAVEPOINT Command: The SAVEPOINT command is used to create a point within a transaction to which you can later roll back.
Syntax: SAVEPOINT savepoint_name;
Example: SAVEPOINT cust1;
SAVEPOINT cust1 SQL command used in database management to set a named transactional point for future rollback or commit actions. This command creates a savepoint named "cust1" within the transaction.
Data Control Language (DCL): Managing Access Control
Data Control Language (DCL) commands play a crucial role within a database management system (DBMS) by governing access privileges and maintaining data security. It enables database administrators to grant or revoke permissions, ensuring data integrity and security. Here are some common DCL commands:
(i) GRANT Command
One fundamental command in DCL is GRANT
. This command authorizes specific users or user roles to perform certain actions within the database.
Example Tables:
Consider two sample tables in a database: employees
and sensitive_data
.
Table: employees
emp_id | emp_name | emp_role |
---|---|---|
1 | Dr. Abebe | Manager |
2 | Mr. Yordanos | Developer |
3 | Mrs. Tsion | Analyst |
Table: sensitive_data
data_id | data_type | data_content |
---|---|---|
101 | Confidential | Access Codes |
102 | Secret | Financial Data |
103 | Classified | Client Records |
GRANT Command Example:
Granting permissions on the employees
table:
GRANT SELECT, INSERT ON employees TO marketing_role;
marketing_role
to perform SELECT
and INSERT
operations on the employees
table.(ii) REVOKE Command
Conversely, the REVOKE
command is used to withdraw previously granted permissions.
Revoking permissions on the sensitive_data
table:
REVOKE DELETE ON sensitive_data FROM temporary_user;
REVOKE
command revokes the DELETE
permission from the temporary_user
for the sensitive_data
table.Combining DCL Commands with Table Data:
Granting Specific Permissions:
Let's assume we want to grant UPDATE
and DELETE
permissions on the sensitive_data
table to the finance_role
.
GRANT UPDATE, DELETE ON sensitive_data TO finance_role;
finance_role
to perform UPDATE
and DELETE
actions on the sensitive_data
table.Revoking Permissions:
If later, the finance_role
no longer requires DELETE
permissions on the sensitive_data
table, we can revoke it:
REVOKE DELETE ON sensitive_data FROM finance_role;
This command revokes the DELETE
permission from the finance_role
for the sensitive_data
table while retaining other granted permissions.
These SQL queries demonstrate how DCL commands such as GRANT
and REVOKE
can be applied to manage access control within database tables, allowing or restricting specific operations for different user roles or entities.
0 Comments