Hey experts, please help me out on this :( xx Sample database:For all the implemented tasks, your report or output must include a listing of all PL/SQL statements processed. To achieve that put the following SQL*Plus commands in all your scripts:SPOOL file-nameSET ECHO ONSET FEEDBACK ONSET LINESIZE 100SET PAGESIZE 200SET SERVEROUTPUT ONat the beginning of SQL script andSPOOL OFFat the end of SQL script.PARTSUPP table : i. SELECT p_brand, p_type, p_retailpriceFROM part;ii. SELECT count(*)FROM partWHERE p_brand = 'Brand#18';iii. SELECT l_orderkey, count(*)FROM lineitemWHERE l_orderkey = 1184000GROUP BY l_orderkey;iv. SELECT max(ps_suppkey)FROM partsupp;v. SELECT ps_suppkey, ps_partkey, ps_supplycostFROM partsuppWHERE ps_suppkey = 3699AND ps_partkey = 700; b) Consider the relational table PARTSUPP of the TPCHR sample database.Find SELECT statements that will use the index in the ways specified in thequestions (i) to (iv) below. The values used to create the query such thatthe queries can meet the specified criteria is up to you, but the values mustbe sensible.i.ii. Execution of the second SELECT statement must traverse the indexvertically then horizontally and it must access the relational tablePARTSUPP.iii.Execution of the first SELECT statement must traverse the indexvertically then horizontally and it must not access the relationaltable PARTSUPP.iv.Execution of the third SELECT statement must traverse the leaf levelof the index horizontally and it must not access the relational tablePARTSUPP.Execution of the fourth SELECT statement must traverse the leaflevel of the index horizontally and it must access the relationaltable PARTSUPP.DeliverablesSubmit your spooled file solution1b.lst (or solution1b.pdf) that contains your SQLscript and the output from the execution of the script. The report must have noerrors related to the implementation of your task and it must list all PL/SQLand SQL statements processed.Remember to set ECHO option of SQL*Plus to ON!

iv. CREATE UNIQUE INDEX In1 ON OrderLine(OrderNum); Explanation: This query is used to create the…

Answered: Hey experts, please help me out on this…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

Hey experts, please help me out on this :( xx

Sample database:

For all the implemented tasks, your report or output must include a listing of all PL/SQL
statements processed. To achieve that put the following SQL*Plus commands in all
your scripts:
SPOOL file-name
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 200
SET SERVEROUTPUT ON
at the beginning of SQL script and
SPOOL OFF
at the end of SQL script.

PARTSUPP table :

i. SELECT p_brand, p_type, p_retailprice
FROM part;
ii. SELECT count(*)
FROM part
WHERE p_brand = 'Brand#18';
iii. SELECT l_orderkey, count(*)
FROM lineitem
WHERE l_orderkey = 1184000
GROUP BY l_orderkey;
iv. SELECT max(ps_suppkey)
FROM partsupp;
v. SELECT ps_suppkey, ps_partkey, ps_supplycost
FROM partsupp
WHERE ps_suppkey = 3699
AND ps_partkey = 700;

b) Consider the relational table PARTSUPP of the TPCHR sample database.
Find SELECT statements that will use the index in the ways specified in the
questions (i) to (iv) below. The values used to create the query such that
the queries can meet the specified criteria is up to you, but the values must
be sensible.
i.
ii. Execution of the second SELECT statement must traverse the index
vertically then horizontally and it must access the relational table
PARTSUPP.
iii.
Execution of the first SELECT statement must traverse the index
vertically then horizontally and it must not access the relational
table PARTSUPP.
iv.
Execution of the third SELECT statement must traverse the leaf level
of the index horizontally and it must not access the relational table
PARTSUPP.
Execution of the fourth SELECT statement must traverse the leaf
level of the index horizontally and it must access the relational
table PARTSUPP.
Deliverables
Submit your spooled file solution1b.lst (or solution1b.pdf) that contains your SQL
script and the output from the execution of the script. The report must have no
errors related to the implementation of your task and it must list all PL/SQL
and SQL statements processed.
Remember to set ECHO option of SQL*Plus to ON!

Expert Solution

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions