Part 1: Using OES4 database, redo the following two queries (you did them in assignment 4) with PL/SQL: 1. List number of orders for each branch with their total purchase (SUBTOTAL), total shipping charge, total tax, and total purchases (there are 15 branches) BRANCH#, #_ORDERS, TOTAL PURCHAES, TOTAL SHIPPING CHARGES, TOTAL TAXES, TOTAL OVERALL PURCHASE (3) Note: Use RPAD or LPAD to develop your report 2. List number of employees with their total sales (SUBTOTAL), total shipping charge, total tax, and overall total SALESS. EMPLOYEE_NO, TOTAL SALES, TOTAL SHIPPING CHARGES, TOTAL TAXES, TOTAL OVERALL SALES (3) 3. List all product categories and number of products sold for each category PRODUCT_CATEGORY QTY_SOLD

Q: Write the Object-Identity and Reference Types in SQL

A:

Q: Create a subprogram called Birthday_sub that accepts today's date as default parameter and writes…

A: PROCEDURE BIRTHDAY_SUB (date); BEGIN INSERT INTO B_C_File ( first_name, last_name,…

Q: The PL/SQL block that is always executed, regardless of whether an exception is raised or not, is…

A: PL/SQL subprograms are named blocks that may be called repeatedly. Subprogram parameters may change…

Q: Difference between procedure and function in SQL

A: SQL functions and procedures are database objects designed to streamline and improve the…

Q: Explain how to work pl/sql block.

A: Procedural Language/Structured Query Language, or PL/SQL, is a potent SQL extension meant for…

Q: What are the advantages of using table-valued parameters in SQL functions

A: Table-valued parameters (TVPs) in SQL are a powerful feature that allows developers to send multiple…

Q: True or FalseString comparisons in SQL are case sensitive.

A: Explanation: The data in DBMS is stored and retrieved by using a specific language called SQL. DBMS…

Q: A variety of numeric information needs to be entered. In SQL

A: Introduction: A booking system lets one process utilise the CPU while another waits for I/O. Since…

Q: What is the purpose of cursors in PL/SQL?

A: The 'Cursor' is a PL/SQL construct that enables users to name and access the work area's stored…

Q: Example 2.1) (Answer according to Figure 1) Convert the data model in Figure 1 to Database Design…

A: CREATE TABLE CUSTOMER( Customer ID integer primarykey, Name char(20) notnull, Surname char(15)…

Q: The only required sections of a PL/SQL block are DECLARE and END. Answers: Yes No

A: Given:

Q: Task 1: Create Schema for Student Registration System. Task 2: Display all the different job titles,…

A: Creating student registration system with some attributes like student name, student id, class,…

Q: Character data types in SQL Server may be differentiated as follows: NVARCHAR, NVARCHAR, and…

A: Introduction: When a SQL developer creates a table, he or she must determine what data will be…

Q: 9. Display the product with the most total sales amount (sale amount = price*quantity, total sales…

A: We have to get the highest price for the items. Here is the script for creating the table and adding…

Q: What is mean by cursor in sql

A: A database item known as a cursor in SQL enables the sequential processing of a result set, which is…

Q: O RETURN DEFINE BEGIN IS

A: Here , in PL/SQL  we have functions that provide us with sophisticated language constructs to any…

Q: Instruction: Include FULL Screenshot that display your code and output on Apex

A: change session set nls_date_format = `YYYY-MM-DD'; Exercise 1declarev_test_date date:=…

Q: What is the purpose of keywords in SQL (Structured Query Language) when writing database queries?

A: Keywords play a vital role in the world of SQL (Structured Query Language), a programming language…

Q: examples of aggregate functions in SQL

A: Examples of aggregate functions in SQL

Q: Write any two command of DML in SQL

A: The majority of SQL statements are part of DML, or Data Manipulation Language, which is used to…

Q: What is the need for transactions in SQL?

A: GIVEN: What is the need for transactions in SQL?

Q: 8. Display all orders with their calculated total amount. For example, an order purchased 1 iPhone…

A: Basic select from where class is used in the sql query written below. The output is sorted based on…

Q: What is a PL/SQL function? Write the general syntax of it.

A: PL/SQL is a completely portable, high-performance transaction processing language that offers the…

Q: 1. Create a data base (company) 3.Retrieve all the 2. Create two tables in the database: attribute…

A: 1. create database company_lab4;use company_lab4; 2. create table employee(employee_no int primary…

Q: In what situations might the SOUNDEX function be useful in SQL when querying text data?

A: The SOUNDEX function in SQL is useful in situations where you need to perform approximate matching…

Q: Q1. A. Consider the table and answer the following queries: Id Seasons_played 21 16 Name 1 Jim John…

A: “Since you have posted a question with multiple sub-parts, we will solve first three subparts for…

Q: Is it possible to use PL/SQL or T-SQL procedures on MySQL?

A: Our task:Is it possible to use PL/SQL or T-SQL procedures on MySQL?

Q: Can we use DDL and DML statements in Function SQL?

A: In SQL, functions are often used to encapsulate logic and carry out unique responsibilities.…

Q: Explain replace and translate functions in Oracle sql

A: The TRANSLATE function in Oracle SQL is used to swap out one set of characters for another when it…

Q: What are the  Advantages of SQL-invoked routines.

A: To be determine: What are the  Advantages of SQL-invoked routines.

Q: How to update in sql

A: The UPDATE statement in sql  is used to change the contents of the table's existing records.