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…

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,

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,

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter6: Additional Database Objects

Section: Chapter Questions

Problem 1HOA

See similar textbooks

Similar questions

Let's assume that the following indices are now set on the schema from the previous task: - Level 2 B+ index on the attribute Vehicle.year - Hash index on the attribute Rental.date Our DBMS has the following algorithms to perform the join: - Block nested loops join - Index nested loops join Translate the following SQL query into relational algebra and find the plan that reads the fewest pages or blocks: SELECT Vehicle.brand, Vehicle.model FROM Vehicle JOIN Rental ON (Vehicle.vid = Rental.vid) WHERE Vehicle.year = 2015 AND Rental.date > ’31.12.2022’
1) Assume the EMPLOYEE table has the following rowsID DEPT SALARY NAME100 SALES 40000 Smith101 RD 38000 Terry102 HR 60000 David103 SALES 58000 Ellie104 RD 70000 Judya) Provide the complete PL/SQL code to implement a Virtual Private Database suchthat an employee could only view the records for employees in the samedepartment while masking coworkers’ salary with NULL.b) Based on the Virtual Private Database you implemented in step a), would anemployee be able to insert, update or delete a record for another employee whoworks in another department? If yes, what might be the potential security risk andhow would you fix the problem by setting up the VPD appropriately?
Which of the following database operations should be handled with extra care?
This is my attempt at DBDL statements trying to plan a database in MySQL. Employers: CREATE TABLE Employers ( EmployerID INT PRIMARY KEY, CompanyName VARCHAR(255), Location VARCHAR(255), ContactInfo VARCHAR(255), ); Customers: CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, Name VARCHAR(255), ContactInfo VARCHAR(255), Education VARCHAR(255), Experience INT ); Reference: CREATE TABLE References ( ReferenceID INT PRIMARY KEY, Name VARCHAR(255), ContactInfo VARCHAR(255), RelationshipToCustomer VARCHAR(255) ); Skills: CREATE TABLE Skills ( SkillID INT PRIMARY KEY, SkillName VARCHAR(255), ProficiencyLevel VARCHAR(255) ); EmploymentPositions: CREATE TABLE EmploymentPositions ( PositionID INT PRIMARY KEY, PostionTitle VARCHAR(255), EmployerID INT, Duration VARCHAR(255), FOREIGN KEY (EmployerID) REFERENCES Employers(EmployerID) ); Interviews: CREATE TABLE Interviews ( InterviewID INT PRIMARY KEY, InterviewDate DATE, Interviewer VARCHAR(255), Feedback VARCHAR(255) ); HiringStatus:…
In this example, we will pretend that the student table in the university database is indexed by StudentID (the primary key), Major, Age, MaritalStatus, and HomeZipCode (all secondary keys). Let's pretend a university needed a list of students from the 45462 zip code who are either above the age of 25, married, and majoring in MIS or computer science OR who are under the age of 25 and majoring in computer engineering. Is there a way to use indexes to restrict results to to those entries that fulfill this condition?
Let's pretend the Student table in a university database is indexed by StudentID (the primary key), Major, Age, MaritalStatus, and HomeZipCode (all secondary keys). Assume the school needed a list of students from the 45462 zip code who are either over the age of 25, married, and majoring in MIS or computer science OR who are above the age of 25 and majoring in computer engineering and are single. How can indexes be used to restrict access to records that exactly match these parameters?
Write a SQL trigger to carryout the following action: If an account is deleted, then write a trigger to delete thedependent tuple(s) from the depositor table for every owner of the deleted account. Notethat there may be jointly-owned bank accounts. In other words, you are to write a triggerthat performs the exact action of an ON DELETE CASCADE clause of a FOREIGNKEY CONSTRAINT. You must submit both your trigger function definition, and yourtrigger definition
I am using MySQL and I am creating a college event management database. In the database, I want to add a check constraint that prevents overlapping events when inserted into the events table. The problem I am having is that it seems like it will not allow an alias for the Events table so that I can compare the inserted row to all the other rows in the table. Is it not possible to use aliases in check constraints? ALTER TABLE Events As EADD CONSTRAINT eventOverlap CHECK ((E.LocID=LocID) AND (E.Date=Date) AND ((End-E.Start) > 0) AND ((E.End-Start) > 0)) I have also tried using a select statement I get an error saying "Error Code: 3815. An expression of a check constraint 'eventOverlap' contains disallowed function." ALTER TABLE EventsADD Constraint eventOverlap CHECK (NOT EXISTS (SELECT * FROM Events E WHERE E.LocID = LocID AND E.Date = Date AND ((E.Start <= Start AND E.End > Start) OR (E.Start < End AND E.End >= End)))) OR (E.Start < End AND E.End >= End))…
write MySQL functions to accomplish the following tasks: A:Delete the order whose number is stored in I_ORDER_NUM. B: Change the date of the order whose number is stored in I_ORDER_NUM to the date currently found in I_ORDER_DATE. C: Retrieve and output the item number, description, storehouse number, and unit price of every item in the category stored in I_CATEGORY.
a) from university database , Write the following queries in SQL Find the titles of courses in the Comp. Sci. department that have 3 credits Find all instructors earning the highest salary (there may be more than one with the same salary) Find all courses that have never been offered (i.e., do not occur in the section relation). b) given the following tables( instructor, teaches) what is the output of the following query select i.name from instructor as i, teaches as t where i.ID=t.ID and semester=’ Fall’ and year=2017;
One implementation technique of a view is as a temporary table. That creates the challenge for the system to keep the temporary table up-to-date as modifications are done in the base table(s). Consider this example: create table R (a int, b int, primary key(a,b)); create view V1 as select A+B as S, A-B as D from R where A between 1 and 10; a) Create a temporary table for V1 b) Create the appropriate trigger(s) on R that will keep V1 current on inserts and deletes c) Provide sufficient test cases to demonstrate the correctness of your solution.
1. Use appropriate clauses and filters to run the query with minimum time complexity. 2. Try to avoid running the queries on unnecessary rows and columns. 3. Share the queries along with a brief description of the code in a python file, so that the results are reproducible. Q1: Write a query to find the students working in a company having more ratings than their seniors EMPLOYEES RATING TABLE student id student name rating senior id designation 1 george 9 3 student 2 micheal 8 4 student 3 mary 6 null senior 4 steve 9 null senior For the above table GEORGE has more rating than his sENIOR