KTU DBMS LAB CSL 333 - 2019 SCHEME BTECH S5 - Dr Binu V P

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s

In PL/SQL (Procedural Language/Structured Query Language), functions are subprograms that can be stored and executed on the database server. They allow you to perform specific tasks, typically returning a single value. Functions are similar to procedures, but they differ primarily in that a function returns a value while a procedure does not. Key Features of Functions in PL/SQL: Return Type:  Functions must have a return type. This can be any valid PL/SQL data type. Syntax: The general syntax for creating a function is: CREATE [OR REPLACE] FUNCTION function_name     [ (parameter_list) ]    RETURN return_datatype IS | AS    [declaration_section] BEGIN    executable_section    RETURN return_value; EXCEPTION    exception_section END [function_name]; Parameter Modes: IN: The parameter can be read, but not modified. OUT: The parameter can be written to, but not read. IN OUT: The parameter can be read and written to. Example of a Simple PL/SQL Function CREATE OR REPLACE FUNCTION hello_user 

In PL/SQL, exceptions are used to handle errors and other exceptional events that occur during the execution of a program. PL/SQL provides predefined exceptions and allows the creation of user-defined exceptions. Predefined Exceptions Some common predefined exceptions include: NO_DATA_FOUND: Raised when a SELECT INTO statement returns no rows. TOO_MANY_ROWS: Raised when a SELECT INTO statement returns more than one row. ZERO_DIVIDE: Raised when an attempt is made to divide a number by zero. DUP_VAL_ON_INDEX: Raised when a duplicate value is tried to be inserted into a unique column. User-Defined Exceptions You can define your own exceptions using the EXCEPTION keyword and handle them using RAISE. Syntax Here's the basic syntax for exception handling in PL/SQL: BEGIN    -- Executable statements EXCEPTION    WHEN exception1 THEN       -- Exception handling statements for exception1    WHEN exception2 THEN       -- Exception handling statements for exception2    WHEN OTHERS THEN      

Introduction to MongoDB Introduction to CURD operations Introduction to MongoDB MongoDB is a popular open-source, document-oriented NoSQL database designed for storing and managing large volumes of data. It uses a flexible, schema-less design, allowing for efficient storage of JSON-like documents called BSON (Binary JSON). MongoDB is known for its scalability, performance, and ease of use, making it a preferred choice for modern web applications. Key features of MongoDB: Document-oriented storage : Stores data in flexible, JSON-like documents. Schema-less : No predefined schema required, allowing for dynamic changes. Scalability : Supports horizontal scaling through sharding. Replication : Ensures high availability and data redundancy. Indexing : Supports various types of indexes for fast query performance. Aggregation : Powerful aggregation framework for data processing and analysis. CRUD Operations in MongoDB CRUD operations are the four basic operations performed on databases: Crea

A query plan (or execution plan) is a detailed, step-by-step map that a database management system (DBMS) like MySQL follows to execute a SQL query. It describes the operations the DBMS will perform to retrieve the required data. Understanding query plans is essential for optimizing query performance. Query Plan A query plan outlines how the DBMS will access data and which methods it will use to perform joins, filters, sorts, and other operations. The plan includes information about:The order in which tables are accessed. The indexes that are used. The type of joins performed. The filtering and sorting mechanisms. The estimated cost of various operations. Understanding the Execution Plan with EXPLAIN Basic Usage of EXPLAIN To analyze a query, prepend EXPLAIN to your SQL statement. This will show you how MySQL plans to execute the query.sql EXPLAIN SELECT * FROM employees WHERE employee_id = 123; Optimizing with Query Plans By analyzing the query plan, you can identify potential bottlen

In PL/SQL (Procedural Language/Structured Query Language), a package is a schema object that groups logically related PL/SQL types, variables, and subprograms (procedures and functions). Packages have two parts: the specification (spec) and the body. The specification is the interface to the package; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body defines these elements and implements the subprograms. Benefits of Using Packages: Modularity: Packages help organize related PL/SQL types, items, and subprograms. Easier Application Design: You can design an application as a set of related packages. Information Hiding: You can specify which types, items, and subprograms are public or private. Improved Performance: The entire package is loaded into memory when a package subprogram is called for the first time, thus reducing the disk I/O for subsequent calls. Structure of a Package: Package Specification: Declares the public items (

Backing up and restoring databases is a crucial aspect of database management, ensuring data availability and integrity in case of failures, data corruption, or accidental deletions. The process and tools used for backup and restoration depend on the database management system (DBMS) in use. MySQL Backup Using mysqldump: mysqldump -u [username] -p [database_name] > backup.sql -u [username] : Specifies the MySQL username. -p : Prompts for the password. [database_name] : The name of the database to back up. > backup.sql : Redirects the output to a file named backup.sql . Example: mysqldump -u root -p mydatabase > backup.sql Restore Using mysql: mysql -u [username] -p [database_name] < backup.sql Example: mysql -u root -p mydatabase < backup.sql PostgreSQL Backup Using pg_dump: pg_dump -U [username] -d [database_name] -F c -b -v -f backup.dump -U [username] : Specifies the PostgreSQL username. -d [database_name] : The name of the database to back up. -F c : Specifies the fo

 NoSQL Databases: An Overview NoSQL databases (short for "Not Only SQL" or "Non-relational SQL") are designed to handle large volumes of structured, semi-structured, and unstructured data. Unlike traditional relational databases (SQL databases) that use tables and predefined schemas, NoSQL databases offer a more flexible, scalable, and efficient way to store and retrieve data. Key Features of NoSQL Databases Schema-less Design : No predefined schema is required. The data model can be easily adjusted to accommodate changing requirements. Horizontal Scalability : NoSQL databases are designed to scale out by adding more servers, making them ideal for handling large amounts of data. High Availability : Built-in mechanisms for data replication and distribution ensure high availability and fault tolerance. Flexible Data Models : Supports various data models, including document, key-value, column-family, and graph databases. Types of NoSQL Databases and Examples 1.Documen