3- Assume that the nymber.ef.stens attribute of the Cruise table contains the total number of stops for each cruise. Write a trigger that will be fired whenever the Stops table is modified to increase or decrease the emkeretsters value accordingly.
Q: Create LessonSchedule table with FK constraints Two tables are created: Horse with columns:
A: Answer has been explained below:-
Q: What is the syntax for the drop table command?
A: Justification: In SQL, the DROP statement is used to deactivate a component in a relational database…
Q: Which DDL command is used to display all the contents of the table? UPDATE DISPLAY INSERT SELECT
A: Question 15:
Q: In Kali, login to DVWA, set security level to low , then click SQL Injection on left pane .Write…
A: 1. find all database names %' or 0=0 union select null, user() # 2. find the current database name…
Q: B5. Write a SQL command to perform the following operations by using this schema: BOOKS(BooklID.…
A: SQL command to ⦁ create the table BOOKS⦁ insert a new row in the BOOKS table⦁ Update the value of…
Q: PayRate for an irregularities in terms of updates. The ChangeID is to be autogenerated and should…
A: Lets see the solution.
Q: The Driver Relationship team realized that there is no need for DRIVER_DRIVING_LICENSE_CHECKED field…
A: - The question is to delete the column DRIVER_DRIVING_LICENSE_CHECKED as there is no need of it. -…
Q: ay only tables within the system catalog that are of the type BASE TABLE. Display only the following…
A: MySQL It is a relational database management system based on the Structured Query Language, which…
Q: Create a trigger named trg_cust_balance that automatically updates the CUSTOMER table’s CUS_BALANCE…
A: Syntax for creating a trigger: CREATE TRIGGER name{AFTER|BEFORE|INSTEAD OF} {DELETE|INSERT|UPDATE}…
Q: Create a trigger named trg_pic_hours that automatically updates the PILOT table when a new CREW row…
A: Triggers are stored programs which are automatically fired when some event occur. Syntax for…
Q: triggers. Triggers should be meaningful.
A: According to the question we need to write the 5 Triggers:
Q: Create a trigger (separately not in any package) called CHECK_SALARY_TRG on the EMPLOYEES table that…
A: Defines a new trigger. User-defined triggers are not supported in Greenplum Database. Synopsis…
Q: Create a row level trigger which insert a tuple into the customner_audit (customerNO, customerName,…
A: Customer Order tables: Customer (customerNO, customerName, address, city, category, custBalance)…
Q: The SQL data manipulation command HAVING: modifies an attribute's values in one or more table's…
A: Question The SQL data manipulation command HAVING:
Q: 1. Cruise(cid, name, number_of_days, price, number_of_stops) Port(pid, city, country,…
A: As the database schema is given here. The Oracle pl/sql query for the trigger will be like this :…
Q: Create a trigger named trg_line_prod that will automatically update the product quantity on hand…
A: Given : Create a trigger named trg_line_prod that will automatically update the product quantityon…
Q: Write a trigger that will be activated when a new customer is added to the Customers table and that…
A: Solution :This is a trigger that will be activated when a new customer is added to the Customers…
Q: Create a table Customer ; ID | NAME |AGE|ADDRESS | SALARY Select * from customers; I IR | NAME | AGE…
A: Triggers are stored programs, which are automatically executed or fired when some events occur.…
Q: The DD_PLEDGE table contains the FIRSTPLEDGE column that indicates whether a pledge is the donor's…
A: Solution: Given: The DD_PLEDGE table contains the FIRSTPLEDGE column that indicates whether a pledge…
Q: create tablespace having size of 0.5 MB.
A: I have given an answer in step 2.
Q: 1.Cruise(cid, name, number_of_days, price, number_of_stops) Port(pid, city, country,…
A: Trigger named Number_of_Stops is created for table cruise It will be executed when modification…
Q: Changing the. EmployeeID = 1 to 10 in the table Employee. If it is rejected, explain EMPLOYEE(…
A: We need to changing the EmployeeID = 1 to 10 in the table Employee and if it is rejected, explain.
Q: 2- selects the order line items (orderNumber, orderlinenumber) from the “orderdetails” table. Also,…
A: According to our guidelines, we are allowed to solve only the first three subparts of the question.…
Q: Problem 31 Create a trigger named trg_line_total to write the LINE_TOTAL value in the LINE table…
A: Explanation: The below query is used to create a trigger named “TRG_LINE_TOTAL” to add the product…
Q: Task 4: ' The Car Maintenance team wants to add new maintenance tasks into the system. The team has…
A: SQL Query:
Q: 3.8 LAB - Create LessonSchedule table with FK constraints Two tables are created: Horse with…
A: The SQL Query for LessonDateTime table is given below The 2 constraints are kept in mind while…
Q: EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in…
A: MPDET is an external table containing the columns EMPNO and ENAME. Which command would work in…
Q: Create a INSERT trigger called checkPubCity to ensure there are no more than two publishers in the…
A: Triggers are consist of procedures that fired or executed whenever any manipulation occurred on the…
Q: Write a trigger to update the CUST_BALANCE when an invoice is deleted. Name the trigger…
A:
Q: 14 LAB - Create LessonSchedule table with FK constraints Two tables are created:
A: Solution: SQL Code: CREATE TABLE LessonSchedule ( HorseID INT NOT NULL, StudentID INT,…
Q: Create a trigger for the Invoices table that automatically inserts the vendor name and address for a…
A: Trigger When an event occurs, the trigger runs automatically which is a stored procedure. The…
Q: Write the command to display only tables within the system catalog that are of the type BASE TABLE.…
A: I have provided solution in step2.
Q: Create a lessonSchedule table with FK constraints
A: -- create table LessonSchedule with below attributes, respective data types, not null constraints,…
Q: The Movie table has the following columns: ID - integer, primary key Title - variable-length string…
A: The LEFT JOIN returns all records from the left table and the matching records from the right table.…
Q: The given commad ALTER TABLE test MODIFY id NOT NULL; The command will always be executed…
A: ALTER TABLE command is used to add, delete or modify columns in an existing table. The basic syntax…
Q: Create or replace table logline (Mno number (7), Mtext1 varchar2 (150), Mtext2 varchar2 (50), Mtext3…
A: answer is given below
Q: _____, also known as RESTRICT, yields values for all rows found in a table that satisfy a given…
A: See, UNION: Combines all the rows from two tables, excluding duplicate one. INTERSECT: Choose only…
Q: an Student endpoint that will connect to database and pull in the back all the data from table (The…
A: using System; using System.Data; using System.Data.SqlClient; namespace DatabaseConnectivity {…
Q: Create a trigger named trg_line_total to write the LINE_TOTAL value in the LINE table every time you…
A: Triggers are stored programs which are automatically fired when some event occur. Syntax : CREATE…
Q: Create a trigger named trg_line_prod that automatically updates the quantity on hand for each…
A: Lets see the solution.
Q: Create a Custom Toast View as given below. (Provide only Java Code)
A: Solution:-- 1)As per given in the question is to do the programming in the Java language.…
Q: Task 4: The Driver Relationship team wants to have quick search options for the active drivers.…
A: Here we write a simple mysql code for find the active driver id,name: we write two method to get…
Q: FIGURE P8.31 CH08_SALECO2 DATABASE TABLES
A: Trigger A trigger is defined as a special form of stored procedure that executes on its own when a…
Q: Create a trigger named trg_line_total to write the LINE_TOTAL value in the LINE table every time…
A: Given : Create a trigger named trg_line_total to write the LINE_TOTAL value in the LINE tableevery…
Q: The InstantRide Management team considers setting up a Lost & Found inventory. In order to start the…
A: Solution: Given: The InstantRide Management team considers setting up a Lost & Found inventory.…
Q: Which are the types of Triggers?
A: Trigger is a special type of storage in the database that automatically executes / runs / fires when…
Q: Produce a script file with 15 queries: At least two using join At least three using where clause At…
A: The JOIN clause is used to combine more than one different table based on the condition which is…
Q: 4.9 LAB - Select employees and managers with inner join The Employee table has the following…
A: Answer is given below. SELECT A.FirstName AS 'Employee', B.FirstName AS…
Oracle pl/sql
Step by step
Solved in 2 steps
- Q. Write a trigger that checks the length of a contract when a new contract is inserted into longtermcontract table. If this is the first contract between the pharmacy and pharmaceutical company, the length of the contract should not be more than 12 months. If the contract is more than 12 months, the trigger should alter the end date and reduce it to 12 months after the start date. * You have to use BEFORE INSERT ON* THE INSERT STATEMENT IN THE END OF THE GIVEN CODE MUST WORK. GIVEN CODE:-- Do not change the name of the triggerdelimiter //create trigger first_contracts_length_limitation -- do not modify or delete the following lines. Do not write anything after this line.insert into longtermcontract values('parapharmaceutical', '24 South St., Fabreville, QC H7P-E2H', '2020-02-01', '2021-04-01', '***'); SCRIPT:The Stock of the book needs to be updated correspondingly after a customer has made an order, or a customer has cancelled an order. It can be accomplished by using trigger. You are asked to implement a trigger which needs to take following into consideration: a. The trigger needs to be fired after a new row has been inserted into table Orders, or after a row has been deleted from table Orders which indicates an order has been cancelledb. Each order may contain multiple books in ORDERTITEMS table You also need to write SQL statement or/and PL/SQL code to demonstrate that the implemented trigger accomplishes the business logic9. Write a trigger that prohibits duplicate values except for nulls in the NoDupName column of the following table: CREATE TABLE TestUniqueNulls (RowID NoDupName varchar(20) int IDENTITY NOT NULL, NULL); (Note that you can’t do this by using a unique constraint because the constraint wouldn’t allow duplicate null values.) If an INSERT or UPDATE statement creates a duplicate value, roll back the statement and return an error message. Write a series of INSERT statements that tests that duplicate null values are allowed but duplicates of other values are not.
- EXAMINE THE TRIGGER BELOW IS IT CORRECT? IF NOT MODIFY Create test to show that the triggers are correctly implemented, do the following: Truncate the Transaction table Reset the Tx_Nbr sequence back to 1 Update the Account table, setting the Balance back to zero Re-run the INSERT statements for the transactions Use simple queries to demonstrate that the results in the Transaction and Account tables are as expected GIVEN THE TRIGGER BELOW: Trigger to enforce the referential integrity for the Transaction Ref_Nbr: Deposit or Withdrawal transaction to Bank Branch Bill Payment, Debit Purchase, or Return transaction to Merchant CREATE TRIGGER Transaction_RefNbr_Check BEFORE INSERT OR UPDATE ON Transactions FOR EACH ROW DECLARE TxRefNbr_count INTEGER; BEGIN IF:NEW.TxTypeCode = ‘D’ OR :NEW.TxTypeCode = ‘W’ THEN SELECT COUNT(*)INTO TxRefNbr_count FROM BRANCH WHERE BranchNbr=:NEW.RefNbr; IF TxRefNbr_count = 0 THEN…- Create a trigger that will insert new emp_no, salary, from_date and to_date into salaries table after a new employee record is inserted. Assume the new employee salary is 10000 and from_date is current date and to_date is “9999-01-01” - List the employees’ first names and salaries where their salary is above the average salary among the employeeswrite step - by - step command CREATE BELOW TABLE CREATE TABLE empl (month INT, emp VARCHAR (15), dept VARCHAR (15), salary INT); INSERT INTO empl VALUES (1, "Oliver", "HR", 9000), (1, "George", "IT", 8000), (3, "Harry", "HR", 20000), (6, "Jack", "IT", 110123), (6, "Jacob", "SALES", 3000), (12, "Noah", "SALES", 101000), (12, "Charlie", "IT", 123456); 1.Count total no of Employee 2.Find the lowest salaries in each dept 3.Find month-wise maximum salaries 4.Find the sum all employee salaries 5.Find the AVG of salaries in each dept
- Create a table in your own database using the following statement. CREATE TABLE DateRange (DateID INT IDENTITY, DateValue DATE, DayOfWeek SMALLINT, Week SMALLINT, Month SMALLINT, Quarter SMALLINT, Year SMALLINT ); Write a stored procedure that accepts two parameters: A starting date The number of the consecutive dates beginning with the starting date The stored procedure then inserts data into all columns of the DateRange table according to the two provided parameters.MYASQL 7. Create Avgoffreight() stored procedure to retrieve the average of freight from Orders table. Then , show the result.8. Write a database trigger TO insert the UnitPrice field.IF UnitPrice is less than 10 then make it equal to 10.(BEFORE INSERT). Test the trigger and show its result.procedure and trigger9. Select the first 10 characters of each customer's address. (Hint: You can use one of the built-in functions that are explained in functions file). (bonus, if you want to get higher mark, you can answer it, if not ,you can upload the solution file without it )Database: CUSTOMER (CustomerID, LastName, FIrstName, Phone, EmailAddress) PURCHASE (InvoiceNumber, InvoiceDate, PreTaxAmount, CustomerID) PURCHASE_ITEM (InvoiceNumber, InvoiceLineNumber, Item Number, RetailPrice) ITEM (ItemNumber, ItemDesciption, Cost, ArtistLastName, ArtistFirstName) Write an SQL statement to show which cutomers bought which items, and include any items that have not been sold. Include CUSTOMER.LastName, CUSTOMER.FirstName, IvoiceNumber, InvoiceDate, ItemNumber, ItemDescription, ArtistLastName, and ArtistFirstName,. Use a join using JOIN ON syntax, and sort the results by ArtistLastName and ArtistFirstName in ascending order. Note that in Microsoft Access this require multiple queries. From Database Concepts 9th Ed. (Kroenke)
- Create a trigger called Trig_Enroll_BI on the ENROLLMENT table that fires before an INSERT statement. The trigger must generate a proper error message if the student id or section id or both invalid. Test your code for all possible options. Make sure that all columns that have NOT NULL values (Use system date for all date fields, user id for all created and modified by fields). Display the inserted record from the ENROLLMENT table after a successful enrollment transaction.please use store procedures to answer the following question Create a procedure to update Job Title for a given JOB_ID This procedure is called Update_Job_Title will have two INPUT parameters. p_job_id as VARCHAR2 as INPUT parameter and also p_new_job_title as VARCHAR2 as INPUT parameter. In BEGIN and END executable section Execute UPDATE command on JOBS table to change title of a JOB_ID Here is the command that you can use in BEGIN and END UPDATE JOBS SET job_title= p_new_job_title WHERE job_id= p_job_id; Test your code by executing procedure for below job_id. Currently in Jobs table you have Job_id = ‘MK_MAN’ and job_title=’ Marketing Manager’ Change title from Marketing Manager to Marketing Sr. Manager execute Update_Job_Title(‘MK_MAN’, ‘Marketing Sr. Manager’ )MongoDB 1) The customers collection includes documents that have accounts as a field that contains an array of values. Write a statement to retrieve a cursor containing all documents where there is only one account. 2) Write a statement that returns only the email addresses of all customer documents. 3) Write a statement to retrieve a cursor containing all customer documents where the active field within the main document (i.e., do not worry about nested documents) is set to true. 4) Write a statement to retrieve a cursor containing all customer documents that do not have the active field within the main document (i.e., do not worry about nested documents). (Hint: Take a look at the Query and Projection operators in the MongoDB documentation to find an operator that helps determine whether a field exists within a document.)