Scribd Logo
Upload

Welcome to Scribd!

  • 160 views

    Online Education Laboratory Exercise

    Uploaded by

    Jolex Acid
    This document provides instructions and response fields for laboratory exercises involving SQL queries on a PARTS database table. Students are asked to write PL/SQL code to generate various reports from the PARTS table based on different filtering criteria on column values. The exercises cover basic SQL clauses like SELECT, WHERE, ORDER BY, DISTINCT and JOINs.

    Copyright:

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

    Online Education Laboratory Exercise

    Uploaded by

    Jolex Acid
    160 views7 pages
    This document provides instructions and response fields for laboratory exercises involving SQL queries on a PARTS database table. Students are asked to write PL/SQL code to generate various reports from the PARTS table based on different filtering criteria on column values. The exercises cover basic SQL clauses like SELECT, WHERE, ORDER BY, DISTINCT and JOINs.

    Original Title

    Week006-Where-LabExer003a

    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 provides instructions and response fields for laboratory exercises involving SQL queries on a PARTS database table. Students are asked to write PL/SQL code to generate various reports from the PARTS table based on different filtering criteria on column values. The exercises cover basic SQL clauses like SELECT, WHERE, ORDER BY, DISTINCT and JOINs.

    Copyright:

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

    Online Education Laboratory Exercise

    Uploaded by

    Jolex Acid
    This document provides instructions and response fields for laboratory exercises involving SQL queries on a PARTS database table. Students are asked to write PL/SQL code to generate various reports from the PARTS table based on different filtering criteria on column values. The exercises cover basic SQL clauses like SELECT, WHERE, ORDER BY, DISTINCT and JOINs.

    Copyright:

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

    CS111-Fundamentals of

    Course Code Programming and Database


    Theory and Apps
    Database Management System
    Description
    1
    College / Department:
    LabExer No. 003
    Online Education
    Laboratory Exercise Page 1 of 1

    Direction:
     Use the Parts table in LabExer003 (previous week)
     Copy and paste the PL/SQL code on the space provided after each questions.
    Table Name: PARTS
    PARTNUM DESCRIPTION ONHAND CLASS WAREHOUSE PRICE
    AT94 IRON 50 HW 3 2495
    BVO6 HOME GYM 45 SG 2 79495
    CD52 MICROWAVE OVEN 32 AP 1 165
    DL71 CORDLESS DRILL 21 HW 3 12995
    DR93 GAS RANGE 21 AP 2 495
    DW11 WASHER 12 AP 3 399
    FD21 STAND MIXER 22 HW 3 159
    KL62 DRYER 12 AP 1 349
    KT03 DISHWASHER 8 AP 3 595
    KV29 TREADMILL 9 SG 2 1390

    PARTS structure
    COLUMN NAME DATA TYPE/SIZE KEY NULL
    PARTNUM CHAR – 4 PRIMARY NOT NULL
    DESCRIPTION VARCHAR – 20 NOT NULL
    ONHAND NUMBER – 6
    CLASS CHAR – 5
    WAREHOUSE NUMBER – 6
    PRICE NUMBER – 6
    1. Create a report listing only the column DESCRIPTION, PARTNUM, CLASS, and PRICE of all PART
    whose CLASS is equal to HW.
    RESPONSE:

    2. Create a report listing only the column PARTNUM, DESCRIPTION and PRICE of all PARTS where
    price is less than 500. Sort the PRICE in ascending order.
    RESPONSE:

    3. Create a report listing only the column DESCRIPTION, ONHAND and WAREHOUSE of all PARTS
    where ONHAND is greater than or equal to 21.
    RESPONSE:

    4. Create a report listing only the column DESCRIPTION, CLASS and PRICE of all PARTS where class
    is not equal to AP.
    RESPONSE:
    5. Create a report listing only the column CLASS, DESCRITPION and PRICE of all PARTS where price
    range is between 200 to 500. Sort the Price in descending order.
    RESPONSE:

    6. Create a report listing only the column PARTNUM, CLASS and ONHAND of all parts where partnum
    is equal to AT94, DR93 and KV29. (Note 1 query only and do not use logical condition)
    RESPONSE:

    7. Create a report listing only the column DESCRIPTION, ONHAND, CLASS and PRICE of all price
    where the description ends with letter „N‟.
    RESPONSE:

    8. Create a report listing only the column DESCRIPTION, WAREHOUSE, CLASS and PRICE of all parts
    where the description contains keyword „SHE‟.
    RESPONSE:
    9. Create a report listing only the column DESCIPTION, PARTNUM, CLASS and PRICE of all parts
    where the description fourth letter starting from the first is equal to „D‟.
    RESPONSE:

    10. Create a report showing all rows and columns sort the description in ascending order.
    RESPONSE:

    11. Create a report that will merge the column DESCRIPTION and PRICE put a literal character string of =
    “ with a price of ” in between the two columns. Limit the rows returned by getting only the partnum that
    starts with letter „K‟.
    RESPONSE:

    12. Create a report that will display the distinct value for CLASS and WAREHOUSE limit the rows by
    getting only the parts under WAREHOUSE 3.
    RESPONSE:

    13. Create a report by listing the column DESCRIPTION, WAREHOUSE and ONHAND. Get only the
    warehouse value equal to 3 and the onhand value is equal to 21.
    RESPONSE:

    14. Create a report by listing the column PARTNO, DESCRIPTION and PRICE. Get only those Partnum
    that either starts with letter „K‟ or price that is less than 500. Sort your report by price in ascending
    order.
    RESPONSE:

    15. Create a report by listing the column PARTNO, DESCRIPTION and WAREHOUSE. Get only that
    description that does not ends with „ER‟. Note that you have to merge the said three columns, rename
    the merge column as “Parts Record”. Below is the sample output for column.
    Parts Record
    AT94 is the part number of IRON which belong to warehouse 3
    RESPONSE:
    16. Create a report by listing the DESCRIPTION and Price (Note that in column PRICE add ADDITIONAL
    10000). Get only the prices with no digit that is equal to „5‟. Note that you have to concatenate the said
    column and rename the merge column as “New Price Lists”. Sort the data in DESC order by Price.
    RESPONSE:

    17. Write the different select clause following the order of precedence.
    RESPONSE:
    Here is the correct order of execution of SQL query:

    1. FROM clause – This is the initial execution done by the SQL query statement, retrieving all of
    the information needed.
    2. JOIN … ON clause – Based on the original data set of the FROM clause, the JOIN … ON clause
    is used to unify data based on the condition set, and retrieves the exact query columns that will
    be included in the outcome.
    3. WHERE clause – After obtaining the working set, every constraint included in the WHERE clause
    is executed, discarding rows that do not accomplish the constraint.
    4. GROUP BY clause – If this clause is used, then the working data set will group the data
    depending on the columns added in it, showing exclusive row values, including the Aggregate
    function that was stated in the query. We can also include the ROLLUP / CUBE extension in this
    step.
    5. HAVING clause – Just like how the WHERE clause works if the query statement has a GROUP BY
    clause, the HAVING clause will apply the constraints directly to the grouped rows, discarding
    rows that do not accomplish the constraint.
    6. SELECT – All expressions included in the Select query part are considered.
    7. DISTINCT – If used, this expression will discard any duplication from the remaining rows.
    8. ORDER BY – This clause will not discard any row from the working set, it will only order them, in
    a descending or ascending order.
    9. TOP / LIMIT – These clauses will limit the number of rows retrieved and discard the ones that
    are out of range.

    (Source retrieved from: https://www.got-it.ai/solutions/sqlquerychat/sql-help/data-manipulation/what-is-the-


    correct-sql-order-of-precedence/)
    18. What is the default order of sorting data in oracle? Write one example.
    RESPONSE:
    By default, the ORDER BY clause sorts rows in ascending order whether you specify ASC or not.

    19. When is LIKE condition properly used?


    RESPONSE:
    LIKE condition is being used in filtering values under a column that have the same character pattern.
    For example, if you want to filter a column with values that starts or ends with a particular letter, LIKE
    condition is very helpful.
    20. When is IN condition properly used?
    RESPONSE:
    IN condition is used to test for values in a specified set of values. “Membership condition” is a term
    used to indicate the used use of this condition. It is helpful in testing if an expression matches any value
    in a list of values. For example, if you‟re filtering the Class column above with those values that has
    either HW or AP, this condition will help.

    You might also like