Scribd Logo
Upload

Welcome to Scribd!

  • 21 views

    Lab 13 (SOLUTION)

    Uploaded by

    asmasaeed18as
    This document outlines the tasks for a lab on stored procedures in database systems. The tasks include: 1. Creating a stored procedure to add a new book to a library database table. The procedure accepts book details as parameters and inserts a new row. 2. Creating, altering, reading and deleting stored procedures for a user accounts database table from a project. This involves creating a procedure to get all user accounts, altering the add account procedure, and dropping the procedure. Screenshots of the results must be submitted in a Word file for grading. Late submissions will not be accepted.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Lab 13 (SOLUTION)

    Uploaded by

    asmasaeed18as
    21 views9 pages
    This document outlines the tasks for a lab on stored procedures in database systems. The tasks include: 1. Creating a stored procedure to add a new book to a library database table. The procedure accepts book details as parameters and inserts a new row. 2. Creating, altering, reading and deleting stored procedures for a user accounts database table from a project. This involves creating a procedure to get all user accounts, altering the add account procedure, and dropping the procedure. Screenshots of the results must be submitted in a Word file for grading. Late submissions will not be accepted.

    Original Title

    lab 13 (SOLUTION)

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document outlines the tasks for a lab on stored procedures in database systems. The tasks include: 1. Creating a stored procedure to add a new book to a library database table. The procedure accepts book details as parameters and inserts a new row. 2. Creating, altering, reading and deleting stored procedures for a user accounts database table from a project. This involves creating a procedure to get all user accounts, altering the add account procedure, and dropping the procedure. Screenshots of the results must be submitted in a Word file for grading. Late submissions will not be accepted.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    21 views9 pages

    Lab 13 (SOLUTION)

    Uploaded by

    asmasaeed18as
    This document outlines the tasks for a lab on stored procedures in database systems. The tasks include: 1. Creating a stored procedure to add a new book to a library database table. The procedure accepts book details as parameters and inserts a new row. 2. Creating, altering, reading and deleting stored procedures for a user accounts database table from a project. This involves creating a procedure to get all user accounts, altering the add account procedure, and dropping the procedure. Screenshots of the results must be submitted in a Word file for grading. Late submissions will not be accepted.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 9

    Faculty of Computing

    CS220: Database Systems


    Class: BESE-13AB
    Lab 13: Stored Procedures
    Date: December 18, 2023
    Time: 10:00-1:00
    Instructor: Dr. Bilal Ali

    Lab Engineer: Sundas Dawood

    Task:
    A. Create a Stored Procedure to Add a New Book to the Library
    1. Create a table for books
    create database books;
    -- Step 1: Create a table for books
    CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    publication_year INT,shelfno int
    );
    -- Inserting sample data into the books table
    INSERT INTO books (title, author, publication_year,shelfno) VALUES
    ('The Catcher in the Rye', 'J.D. Salinger', 1951,45),
    ('To Kill a Mockingbird', 'Harper Lee', 1960,67),
    ('1984', 'George Orwell', 1949,89),
    ('Pride and Prejudice', 'Jane Austen', 1813,12),
    ('The Hobbit', 'J.R.R. Tolkien', 1937,32);
    2. Create a stored procedure to add a new book

    DELIMITER //

    CREATE PROCEDURE AddNewBook (


    IN p_title VARCHAR(255),
    IN p_author VARCHAR(255),
    IN p_publication_year INT,
    IN p_shelfno INT
    )
    BEGIN
    INSERT INTO books (title, author, publication_year, shelfno)
    VALUES (p_title, p_author, p_publication_year, p_shelfno);

    SELECT LAST_INSERT_ID() AS new_book_id;


    END //

    DELIMITER ;
    3. Call the stored procedure to add a new book
    B. Create, alter, read and delete Stored procedure of a table from your project

    CREATE TABLE useraccounts (


    id INT(11) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(111) NOT NULL,
    balance VARCHAR(111) NOT NULL,
    cnic VARCHAR(111) NOT NULL,
    number VARCHAR(111) NOT NULL,
    city VARCHAR(111) NOT NULL,
    address VARCHAR(111) NOT NULL,
    source VARCHAR(111) NOT NULL,
    accountNo VARCHAR(111) NOT NULL,
    branch VARCHAR(111) NOT NULL,
    accountType VARCHAR(111) NOT NULL,
    date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
    );

    -- Insert data into the useraccounts table


    INSERT INTO useraccounts (id, email, password, name, balance, cnic, number,
    city, address, source, accountNo, branch, accountType, date)
    VALUES
    (1, '[email protected]', 'some', 'Fayyaz Khan', '9800', '3210375555426',
    '03356910260', 'Islamabad', 'Some where in isb', 'Programmer', '1005469', '1',
    'Current', '2017-12-14 05:50:06'),
    (2, '[email protected]', 'some2', 'Ali khan', '16000', '3210375555343',
    '03356910260', 'Islamabad', 'Some where in isb', 'Programmer', '10054777', '1',
    'Saving', '2017-12-14 04:50:06'),
    (6, '[email protected]', 'afsdfasd', 'Fayyaz Khan', '234234', '3240338834902',
    '03356910260', 'Taunsa', 'Dera Ghazi Khan', 'Govt. job', '1513410739', '1', 'saving',
    '2017-12-16 07:52:40'),
    (7, '[email protected]', 'safsadf', 'Fayyaz Khan', '12121', '3240338834902',
    '03356910260', 'Taunsa', 'Dera Ghazi Khan', 'Govt. job', '1513410837', '1',
    'current', '2017-12-16 07:54:18');

    a. CREATE/READ PROCEDURE;

    DELIMITER //

    CREATE PROCEDURE GetUserAccounts()


    BEGIN
    SELECT * FROM useraccounts;
    END //

    DELIMITER ;
    CALL GetUserAccounts()

    ALTER PROCEDURE:
    DROP PROCEDURE IF EXISTS AddUserAccount;

    DELIMITER //

    -- Create the modified stored procedure


    CREATE PROCEDURE AddUserAccount (
    IN p_email VARCHAR(255),
    IN p_password VARCHAR(255),
    IN p_name VARCHAR(111),
    IN p_balance VARCHAR(111),
    IN p_cnic VARCHAR(111),
    IN p_number VARCHAR(111),
    IN p_city VARCHAR(111),
    IN p_address VARCHAR(111),
    IN p_source VARCHAR(111),
    IN p_accountNo VARCHAR(111),
    IN p_branch VARCHAR(111),
    IN p_accountType VARCHAR(111),
    IN p_date TIMESTAMP
    )
    BEGIN
    INSERT INTO useraccounts (
    email, password, name, balance, cnic, number, city, address,
    source, accountNo, branch, accountType, date
    )
    VALUES (
    p_email, p_password, p_name, p_balance, p_cnic, p_number,
    p_city, p_address, p_source, p_accountNo, p_branch,
    p_accountType, p_date
    );
    END //

    DELIMITER ;
    CALL GetUserAccounts()
    DROP PROCEDURE:
    Deliverables:
    Complete your lab tasks in SQL workbench and submit a word file with queries and the
    screenshots of the results to all the questions attempted. Upload it on LMS. Late submissions
    will not be accepted.

    You might also like