Write a script that produces the following report: For each instructor, display one line with InstructorID, last name, first name, how many courses the instructor teaches, and a note that is defined as follows: “On leave”, when instructor teaches no courses, “Available for another course”, when instructor teaches only one course, and Nothing otherwise

Write a script that produces the following report: For each instructor, display one line with InstructorID, last name, first name, how many courses the instructor teaches, and a note that is defined as follows: “On leave”, when instructor teaches no courses, “Available for another course”, when instructor teaches only one course, and Nothing otherwise

Programming Logic & Design Comprehensive

9th Edition

ISBN:9781337669405

Author:FARRELL

Publisher:FARRELL

Chapter7: File Handling And Applications

Section: Chapter Questions

Problem 12RQ

See similar textbooks

Similar questions

The SERVICE_REQUEST table uses the CHAR data type for the DESCRIPTION and STATUS fields. Is there an alternate data type that could be used to store the values in these fields? Justify your reason for choosing an alternate data type or for leaving the data type as CHAR.
Using the INSERT command do the following 1- Make at least 5 problems, with one called “LOW” and one called “HIGH.” Make some descriptions for them. Jake Albert is the author of HIGH. Although the difficulty level of problems is a derived attribute, we will assume that some human has provided a (non-derived) difficulty rating which is stored. LOW is rated at 1.1 and HIGH is rated at 8.9. You can choose the rest to be between these values.2. Make at least 6 universities, with one representing UA, one representing NBCC, and at least one other Canadian university. Of the remaining universities, at least two should belong to the same non-Canadian country. Georgia Institute of Technology should be one of the universities.
Use the HOL Small Company ERD and HOL Small Company Details xlsx to completethe steps listed below.It is important to name the tables and columns exactly as they are shown in theERD/xlsx.Failure to do so will cause future assignment to fail.*//*()Comments1. At the beginning of your script, make single-line comments for yourname, the name of the assignment, and the date.2. At the beginning of your script, make a multi-line comment thatdescribes the issues you had with this assignment.*//* ()Creating TablesAfter the 5 DROP TABLE statements, complete the 5 CREATE TABLE statementsbelow to create the tables listed below.Make sure you choose the appropriate data types.Include NOT NULL and UNIQUE constraints.DO NOT include key constraints (i.e., primary keys, foreign keys) in yourstatements.The table names are:() HOL_CUSTOMERS(6) HOL_BOOKS(6) HOL_BOOK_CATEGORY(6) HOL_ORDERS(6) HOL_ORDER_ITEMS */DROP TABLE HOL_ORDER_ITEMS;DROP TABLE HOL_ORDERS;DROP TABLE HOL_CUSTOMERS;DROP TABLE…
Task 7: The development team wants to add new residents and new service requests to StayWell without checking the latest IDs and manually incrementing it. Therefore, you need to alter the RESIDENTS table and change the RESIDENT_ID field to an auto-incremented field of type SMALLINT. Task Alter the RESIDENTS table and change the ID field to an auto-incremented field. Task 8: The Colombia City office of StayWell indicated that there is a new tenant by the name of Yigit Yilmaz staying at the property with an ID of 13. You need to include this new resident in the RESIDENTS table. Task Add Yigit Yilmaz to the RESIDENTS table.
write 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
2. Create a trigger that will maintain the correct value in the membership balance in theMEMBERSHIP table when videos are returned late. The trigger should execute as anFOR/AFTER trigger when the due date or return date attributes are updated in theDETAILRENTAL table. The trigger should satisfy the following conditions. a) Calculate the value of the late fee prior to the update that triggered this execution ofthe trigger. The value of the late fee is the days late times the daily late fee. If theprevious value of the late fee was null, then treat it as zero (0).b) Calculate the value of the late fee after the update that triggered this execution of thetrigger. If the value of the late fee is now null, then treat it as zero (0).c) Subtract the prior value of the late fee from the current value of the late fee todetermine the change in late fee for this video rental.d) If the amount calculated in part c is not zero (0), then update the membership balanceby the amount calculated for the…
Task 7: The development team wants to add new residents and new service requests to StayWell without checking the latest IDs and manually incrementing it. Therefore, you need to alter the RESIDENTS table and change the RESIDENT_ID field to an auto-incremented field of type SMALLINT. Task Alter the RESIDENTS table and change the ID field to an auto-incremented field. 1 Task 8: The Colombia City office of StayWell indicated that there is a new tenant by the name of Yigit Yilmaz staying at the property with an ID of 13. You need to include this new resident in the RESIDENTS table. Task Add Yigit Yilmaz to the RESIDENTS table. 1 Task 9: The StayWell property management team wants to add two additional properties and run some simulation tests relating to market coverage. Add the following properties to the PROPERTY table: PROPERTY_ID OFFICE_NUM ADDRESS SQR_FT BDRMS FLOORS OWNER_NUM 14 1 9 Houston Drive 1,100 2 1 MO100 15 1 11 Village Drive 1,300 3 1…
Which of the following statements would be a valid DROP VIEW statement for an invoice_verification table that would prevent the removal of a view if there are any objects depending on it? DROP VIEW invoice_verification RESTRICT; DROP VIEW IF EXISTS invoice_verification; DROP VIEW invoice_verification; DROP VIEW CASCADE invoice_verification;
Create a view of the Join of Deposit and Withdraw transactions to Bank Branch UNION with the join of Bill Payment and Debit Purchase, or Return transactions to Merchant (i.e., all transactions with appropriate reference name.). BranchNbr stores RefNbr of either 'D', OR 'W' transactions, and MerchantNbr stores RefNbr of either 'B', 'P' or 'R' transactions. List of only the Accounts that have multiple Clients associated Provide a count and total amount of Transactions for each Type Description PLEASE DONT NOT COPY THE EXISTING SOLUTION ON BARTLEBY IT IS WRONG AND DOES NOT WORK AND INCOMPLETE. GIVEN THE BELOW: --THIS CREATES THE BANK BRANCH TABLE: CREATE TABLE BankBranch ( BranchNbr INT PRIMARY KEY, BranchName VARCHAR(250)); --THIS CREATES THE MERCHANT TABLE: CREATE TABLE Merchant ( MerchantNbr INT PRIMARY KEY, MerchantName VARCHAR(250)); --THIS CREATES THE TRANSACTION TABLE: CREATE TABLE Transaction ( TxNbr INT PRIMARY KEY, AccountNbr INT,…
Create a view of the Join of Deposit and Withdraw transactions to Bank Branch UNION with the join of Bill Payment and Debit Purchase, or Return transactions to Merchant (i.e., all transactions with appropriate reference name.). BranchNbr stores RefNbr of either 'D', OR 'W' transactions, and MerchantNbr stores RefNbr of either 'B', 'P' or 'R' transactions. List of only the Accounts that have multiple Clients associated Provide a count and total amount of Transactions for each Type Description PLEASE DONT NOT COPY THE EXISTING SOLUTION ON BARTLEBY IT IS WRONG AND DOES NOT WORK AND IS INCOMPLETE. GIVEN THE BELOW: --THIS CREATES THE BANK BRANCH TABLE: CREATE TABLE BankBranch ( BranchNbr INT PRIMARY KEY, BranchName VARCHAR(250)); --THIS CREATES THE MERCHANT TABLE: CREATE TABLE Merchant ( MerchantNbr INT PRIMARY KEY, MerchantName VARCHAR(250)); --THIS CREATES THE TRANSACTION TABLE: CREATE TABLE Transaction ( TxNbr INT PRIMARY KEY, AccountNbr INT,…
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.
Fragment the Client table so that clients of Consultant 19 form a fragment named ClientConslt19, clients of Consultant 22 form a fragment named ClientConslt22, clients of Consultant 35 form a fragment named ClientConslt35, and clients of Consultant 51 form a fragment named ClientConslt51. (Include all fields from the Client table in each fragment.) In addition, you need to fragment the WorkOrders table so that orders are distributed and stored with the clients that placed the orders. For example, fragment OrdersConslt19 consists of those orders placed by clients of Consultant 19. Write the SQL-type statements to create these fragments.