CompTIA DataSys+ (DS0-001)Database FundamentalsMedium
A data scientist frequently runs complex analytical queries involving multiple joins and aggregations on a `Sales` database. To simplify these queries and improve reusability, the scientist wants to create a persistent stored block of SQL code that can be invoked by name. Which database object should the scientist create?
- AMacro
- BStored procedure
- CFunction
- DView
Show answer & explanationAnswer & explanation
Correct answer: B. Stored procedure
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. It can accept parameters, perform complex logic, and execute DML statements, making it ideal for encapsulating complex queries and business logic.
Why the other options are wrong
- A. Macro is not a standard database object for persistent SQL code; it's more common in spreadsheets or programming languages.
- C. A function is similar to a stored procedure but typically returns a single value and cannot perform DML in many SQL dialects.
- D. A view is a virtual table based on a SQL query, used for simplifying access or restricting data, but doesn't encapsulate procedural logic.
Stored Procedure
A subroutine of SQL statements stored in the database catalog, which can be executed by applications or users.
- Encapsulates complex business logic and SQL statements.
- Can accept input parameters and return output parameters.
- Improves performance by reducing network traffic and pre-compiling queries.
- Enhances security by granting users access to procedures, not directly to tables.
Memory trick: Stored procedures are your database's toolbox.