CIS 515 Discussion Student Response

CIS 515 Discussion post responses.

Respond to the colleagues posts regarding:

Advanced SQL and PL/SQL

· Extracting and interpreting data can be very valuable to an organization. Describe the importance of using sub queries in a database system. Provide at least two business case scenarios to support your response.

· A good IT professional is able to be versatile in SQL programming styles. Identify at least two possible advantages of utilizing PL/SQL instead of standard SQL Syntax. Explain whether either syntax provides enhanced code processing capabilities with examples.

JM’s post states the following:Top of Form

A sub-query refers to a query that incurs defined within another SQL statement. It entails used to restrict the amount of data in processed sub-queries. It allows breaking down complex questions into a series of logical steps for easy understanding and maintenance of codes. Sub-query is also essential in filtering data from one table based on the values in another table. Moreover, they can replace complex joints and unions and are easier to understand. They as well allow writing of queries that are more dynamic and data-driven. 

Sub-queries have to engage used to return products whose ListPrice is higher than the average ListPrice for all merchandise. Another scenario where sub-queries have incurred used to identify top-performing employees by comparing the performance with the rate on the first query.

Advantages of Utilizing PL/SQL Instead of Shared SQL Syntax

PL/SQL is more advanced and more convenient to use than Shared SQL Syntax. It supports both static and dynamic SQL, which is not possible with shared SQL Syntax. It is also associated with higher performance than shared SQL syntax. For instance, it allows the sending of block statements to the database. It entails reducing traffic between the application and the database. All SQL data manipulation, cursor control, transactional control statement as well as all SQL functions are possible with PL/SQL. Moreover, PL/SQL allows the storage of subprograms in an executable form which can engage repeatedly invoked. Subprograms stored in PLL/SQL increases manageability because one can maintain only a single copy of a subprogram on the database saver instead of having one copy on each client system (Heller, 2019).

PL/ SQL provides enhanced code processing capabilities as it offers ranking functions, including cumulative distributions, percentage rank, and N-tiles. The partitioned outer join also improves the processing ability. It allows users to densify specific dimensions while keeping others sparse selectively. 

References

Heller, J. (2019). Use SQL More Often with Advanced Dynamic SQL. In Pro Oracle SQL

Development (pp. 523-535). Apress, Berkeley, CA