UNIT 4: SQL-99 and Its Key Concepts

Hello Everyone, Welcome to Rajasthan Polytechnic Blogs.

Subject - Introduction to DBMS CS 4002(Same as CI/IT 4002)
Branch - Computer Science Engineering
Semester - 4th Semester

📍⚡ Important 

▶ Video PDF - Update Soon

👉 WhatsApp Group - Join Now

👉 Telegram Channel - Join Now

📄 Notes in Hindi - Click Here

📄 Notes in English - Click Here

🔥 4th Semester All Subjects Notes - Click Here

Please take a moment to share your Review in Comment Section.

UNIT 4: SQL-99 and Its Key Concepts

In this unit, we will explore SQL-99 (Structured Query Language), which is a query language used for retrieving, updating, and managing data in databases. We will go into detail about various aspects like schema definition, constraints, queries and views, along with security and SQL programming techniques.

---

4.1 SQL-99 🔍

SQL-99 is a version of SQL that introduces new features and improvements for the operation and management of databases. It includes several updates to increase the functionality of databases.

4.1.1 Schema Definition 🏗️

Schema is the structural definition of a database, which includes tables, their relationships, and other database objects (such as views, indexes, etc.).

• CREATE SCHEMA: This SQL command is used to create a new schema, which groups the database objects together.

  Syntax:

  CREATE SCHEMA schema_name;
  

• CREATE TABLE: This command is used to create a table, which includes attributes and constraints.

  Syntax:

  CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
      ...
  );
  

This schema defines the structure of the database, which can later be used by users to store and access data.

---

4.1.2 Constraints 🛑

Constraints are used to ensure data integrity and accuracy in the database. SQL-99 includes several types of constraints, such as:

• Primary Key (PK): A column or group of columns that uniquely identifies each record in a table.

  Syntax:

  CREATE TABLE Student (
      Student_ID INT PRIMARY KEY,
      Name VARCHAR(100)
  );
  

• Foreign Key (FK): A column in a table that is linked to the primary key of another table, establishing a relationship between both tables.

  Syntax:

  CREATE TABLE Enrollment (
      Enrollment_ID INT PRIMARY KEY,
      Student_ID INT,
      FOREIGN KEY (Student_ID) REFERENCES Student(Student_ID)
  );
  

• Unique: Ensures that all values in a column are unique.

  Syntax:

  CREATE TABLE Employee (
      Employee_ID INT UNIQUE,
      Name VARCHAR(100)
  );
  

• Check: Ensures that the data meets a specific condition.

  Syntax:

  CREATE TABLE Product (
      Product_ID INT PRIMARY KEY,
      Price DECIMAL CHECK (Price > 0)
  );
  

• Not Null: Ensures that a column cannot have NULL values.

  Syntax:

  CREATE TABLE Customer (
      Customer_ID INT PRIMARY KEY,
      Name VARCHAR(100) NOT NULL
  );
  

Constraints are crucial for maintaining data integrity and quality. They ensure that only valid and accurate data is inserted into the database.

---

4.1.3 Queries and Views 📊

SQL Queries are used to retrieve data from the database, and Views are virtual tables that present data in a specific way, often derived from one or more tables.

• Queries: SQL queries are used to retrieve data from a database. The most common query is the SELECT statement.

  Syntax:

  SELECT column1, column2 FROM table_name WHERE condition;
  

  Example:

  SELECT Name, Age FROM Student WHERE Age > 20;
  

• Views: A view is a virtual table that presents data in a specific way. Views are defined as queries and act like temporary tables.

  Syntax:

  CREATE VIEW view_name AS
  SELECT column1, column2
  FROM table_name
  WHERE condition;
  

  Example:

  CREATE VIEW AgeView AS
  SELECT Name, Age FROM Student WHERE Age > 20;
  

Views are used to present data from different perspectives, and they help simplify complex queries for users.

---

4.2 Security 🔐

Database security is essential to protect data from unauthorized access, theft, or corruption. SQL-99 offers several features related to security, such as:

• User Authentication: Database systems require authentication (username and password) to log in and access data.

• Role-based Access Control: This system assigns different permission levels (such as view, insert, or delete data) to different users.

• Encryption: Sensitive data in the database can be encrypted to protect it from unauthorized access.

• Audit Logs: Audit logs are used to track any suspicious or abnormal activities in the database system.

SQL-99 offers various options to implement security, such as GRANT and REVOKE statements.

Syntax (GRANT):

GRANT SELECT, INSERT ON table_name TO user_name;

Syntax (REVOKE):

REVOKE SELECT, INSERT ON table_name FROM user_name;

---

4.3 SQL Programming Techniques 💻

SQL programming techniques are used to manage data, make queries more efficient, and automate various tasks.

• Stored Procedures: Stored procedures are programs stored under a specific name in the database and can be called repeatedly.

  Syntax:

  CREATE PROCEDURE procedure_name AS
  BEGIN
      SQL_query;
  END;
  

• Triggers: A trigger automatically performs a task when a specific event (like an INSERT, UPDATE, or DELETE) occurs.

  Syntax:

  CREATE TRIGGER trigger_name
  AFTER INSERT ON table_name
  FOR EACH ROW
  BEGIN
      -- trigger actions
  END;
  

• Functions: SQL functions are used to perform complex calculations or operations in a simplified manner. They return a value and can be used in queries.

  Syntax:

  CREATE FUNCTION function_name (parameters) 
  RETURNS return_type
  AS
  BEGIN
      -- function logic
  END;
  

---

Conclusion ✨

• SQL-99 is used for designing and managing databases in a structured manner. It includes important concepts like schema definition, constraints, queries, and views.
• Security measures protect the database from unauthorized access, and SQL programming techniques help automate complex tasks and improve query performance.
• Proper use of SQL-99 ensures better management, security, and functionality of databases.