Scribd Logo
Upload

Welcome to Scribd!

  • 169 views

    DBMS Part-1 Lab Assign

    Uploaded by

    vaibhavbdx
    This document discusses assignments related to database management systems and SQL. It includes details about creating tables, inserting and querying data, and using PL/SQL blocks and functions. The assignments cover open source databases like MySQL, designing a database to manage customer orders with tables for customers, orders, order items, and stock, and writing PL/SQL code to calculate student grades using functions and cursors. Examples of triggers are also provided.

    Copyright:

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

    DBMS Part-1 Lab Assign

    Uploaded by

    vaibhavbdx
    169 views7 pages
    This document discusses assignments related to database management systems and SQL. It includes details about creating tables, inserting and querying data, and using PL/SQL blocks and functions. The assignments cover open source databases like MySQL, designing a database to manage customer orders with tables for customers, orders, order items, and stock, and writing PL/SQL code to calculate student grades using functions and cursors. Examples of triggers are also provided.

    Original Description:

    dvbms

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses assignments related to database management systems and SQL. It includes details about creating tables, inserting and querying data, and using PL/SQL blocks and functions. The assignments cover open source databases like MySQL, designing a database to manage customer orders with tables for customers, orders, order items, and stock, and writing PL/SQL code to calculate student grades using functions and cursors. Examples of triggers are also provided.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    169 views7 pages

    DBMS Part-1 Lab Assign

    Uploaded by

    vaibhavbdx
    This document discusses assignments related to database management systems and SQL. It includes details about creating tables, inserting and querying data, and using PL/SQL blocks and functions. The assignments cover open source databases like MySQL, designing a database to manage customer orders with tables for customers, orders, order items, and stock, and writing PL/SQL code to calculate student grades using functions and cursors. Examples of triggers are also provided.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 7

    Dr.EmmanuelM.

    PICTPune

    DATABASE MANAGEMENT SYSTEMS LABORATORY



    PartI: Database Programming Languages

    Assignment1:DetailsofOpenSourceDatabases:MySQL
    All Basic commands of MySql . Like :
    mysql>create database ManageCust;
    Query OK, 1 row affected (0.00 sec) // to user ur own database,
    other than default.
    mysql>use ManageCust;
    Database changed
    mysql>QUIT To exit the MySQL Shell, just type QUIT or EXIT:
    mysql>exit
    mysql>SHOW TABLES;
    mysql>DESCRIBE <Table Name>;
    mysql>SELECT what_to_select
    FROM which_table
    WHERE conditions_to_satisfy;
    For implementation of DDL, DML , DQL statement using MySql, we have considered a real time
    example of Managing customer orders system . Following is the Scenario:
    1. A customer has a unique customer number and contact information ,
    2. a customer can place many orders, but a given purchase order is placed by one
    customer
    3. a purchase order has a many-to-many relationship with a stock item.

    Dr.EmmanuelM.PICTPune

    Assignment 2 :DesignandDevelopDBforManagingcustomerorderswithalltheconstraints.
    ThestatementshoulduseSQLobjectssuchasTable,View,Index,Sequence,Synonym.

    A)Createtablecustomer(
    CustNoINTNOTNULL,CustNameVARCHAR2(200)NOTNULL,
    StreetVARCHAR2(200)NOTNULL,CityVARCHAR2(200)NOTNULL,
    StateCHAR(4)NOTNULLDEFAULTPUNE,ZipVARCHAR2(20),
    PRIMARYKEY(CustNo));

    B)CREATETABLEPurchaseOrder(
    PONoINT,CustnoINT,OrderDateDATE,ShipDateDATE,ToStreetVARCHAR2(200),
    ToCityVARCHAR2(200),ToStateCHAR(2),ToZipVARCHAR2(20),PRIMARYKEY(PONo)
    ,FOREIGNKEYfk_cust(CustNo)REFERENCEScustomer(CustNo));

    C)CREATETABLEContains(
    PONoINT,StockNoINT,QuantityINT,DiscountINT,FOREIGNKEYfk_Pur(PONo)
    REFERENCESPurchaseOrder(PONo),FOREIGNKEYfk_Stock(StockNo)REFERENCESStock(StockNo),
    PRIMARYKEY(PONo,StockNo));

    D)CREATETABLECust_Phones(
    CustNoNUMBER,PhonesVARCHAR2(20),FOREIGNKEYfk_cust(CustNo)REFERENCEScustomer
    (CustNo),PRIMARYKEY(CustNo,Phones));

    E)CREATETABLEStock(
    StockNoINT,PriceINT,TaxRateINT,PRIMARYKEY(StockNo));

    Dr.EmmanuelM.PICTPune

    Assignment 3:ManageDataintotheabovetablesusingInsert,Select,Update,Deletewith
    operators,functions,andsetoperator.AndExecutequerieslike

    1. Display all the Purchase orders of a specific Customer.


    SELECT*
    FROMCustomerC,PurchaseOrderP
    WhereC.CustNo=P.CustNoANDC.CustName=XXXXX;


    2. Get Customer and Data Item Information for a Specific Purchase Order.
    SELECTC.CustNo,C.CustName,C.Street,C.City,C.State,C.Zip,
    P.PONo,P.OrderDate,
    CO.StockNo,CO.Quantity,CO.Discount
    FROMCustomerC,PurchaseOrderP,ContainsCO
    WHEREC.CustNo=P.CustNo
    ANDP.PONo=CO.PONo
    ANDP.PONo=1001;
    3. Get the Total Value of Purchase Orders.
    1. SELECT P.PONo, SUM(S.Price * CO.Quantity)
    SELECTP.PONo,SUM(S.Price*CO.Quantity)
    FROMPurchaseOrderP,ContainsCO,StockS
    WHEREP.PONo=CO.PONo
    ANDCO.StockNo=S.StockNo
    GROUPBYP.PONo;
    4. List the Purchase Orders in descending order as per total.

    CREATEVIEWX(Purchase,Total)AS
    SELECTP.PONo,SUM(S.Price*CO.Quantity)

    Dr.EmmanuelM.PICTPune

    FROMPurchaseOrderP,ContainsCO,StockS
    WHEREP.PONo=CO.PONo
    ANDCO.StockNo=S.StockNo
    GROUPBYP.PONO

    SELECT*
    FROMX
    ORDERBYTotaldesc;

    5. Delete Purchase Order 1001 (without on delete cascade )

    DELETEFROMContains
    WHEREPONo=1001;
    DELETE
    FROMPurchaseOrder
    WHEREPONo=1001;

    Assignment 4:WriteaPL/SQLblocktocalculatethegradeofminimum10students.Writefunction
    forthesame.

    setserveroutputon
    createorreplacefunctionres(noinnumber)returnvarchar2as
    nosnumber(7,2);
    psnostud3.sno%type;
    psnamestud3.sname%type;
    psub1stud3.sub1%type;
    psub2stud3.sub2%type;
    psub3stud3.sub3%type;

    Dr.EmmanuelM.PICTPune

    pgradestud3.grade%type;
    ptotalstud3.total%type;
    tperstud3.per%type;

    begin
    selectsno,sname,sub1,sub2,sub3into
    psno,psname,psub1,psub2,psub3fromstud3wheresno=no;
    ptotal:=psub1+psub2+psub3;
    tper:=ptotal/3;
    iftper>=70then
    pgrade:='Distinction';
    endif;
    iftper>=60then
    pgrade:='First';
    endif;

    iftper>=50then
    pgrade:='Second';
    endif;

    iftper<50then
    pgrade:='Fail';
    endif;

    updatestud3setgrade=pgradewheresno=no;
    dbms_output.put_line(psno);
    dbms_output.put_line(psname);
    dbms_output.put_line(psub1);

    Dr.EmmanuelM.PICTPune

    dbms_output.put_line(psub2);
    dbms_output.put_line(psub3);
    dbms_output.put_line(ptotal);
    dbms_output.put_line(tper);
    dbms_output.put_line(pgrade);
    return0;

    Write a PL/SQL block to implement all types of cursors and triggers.


    (Consideremployeetablewithfieldsfirst_name,last_name,salary.)

    Example1DECLARE
    CURSORemp_curIS
    SELECTfirst_name,last_name,salaryFROMemp_tbl;
    emp_recemp_cur%rowtype;
    BEGIN
    IFNOTsales_cur%ISOPENTHEN
    OPENsales_cur;
    ENDIF;
    LOOP
    FETCHemp_curINTOemp_rec;
    EXITWHENemp_cur%NOTFOUND;
    dbms_output.put_line(emp_cur.first_name||''||emp_cur.last_name
    ||''||emp_cur.salary);
    ENDLOOP;
    END;

    Example2CREATEorREPLACETRIGGERBefore_Update_Stat_product
    BEFORE

    Dr.EmmanuelM.PICTPune

    UPDATEONproduct
    Begin
    INSERTINTOproduct_check
    Values('Beforeupdate,statementlevel',sysdate);
    END;
    /

    You might also like