Page 5 Of 7 Delete This Text And Type Your Name Here

Page5of7delete This Text And Type Your Name Herethis File Will Become

This assignment involves SQL programming tasks using Oracle Application Express (Apex). It covers creating, modifying, describing, dropping, and recovering tables, as well as querying data with complex SQL statements, subqueries, and set operators. Additionally, students will practice updating and deleting rows, and understand the implications of foreign key and primary key constraints. The tasks include executing specific SQL statements, describing table structures, capturing screenshots of results, and analyzing errors related to data integrity constraints.

Paper For Above instruction

The laboratory assignment centers on practical SQL command execution within Oracle Apex, designed to reinforce understanding of database table management, data querying, and data manipulation. Over a series of tasks, students will engage with SQL commands like CREATE TABLE, DESCRIBE, ALTER TABLE, DROP, RENAME, and RECOVER. These commands facilitate essential database operations such as defining new tables, modifying existing schemas, and restoring dropped objects, essential skills in database administration and development.

For example, students will first create a table called grad_candidates and then describe its structure to understand its schema. They will clone the table using a subquery into another table named after their last name. Subsequently, they will insert personal data into the newly created table, modify its schema by adding and dropping columns, and finally, rename or delete the table as needed. These operations demonstrate fundamental SQL Data Definition Language (DDL) commands and the importance of managing database objects effectively.

Furthermore, students will explore the recycling functionality of Oracle databases by examining the user_recyclebin view, which stores dropped tables. They will recover a table from the recycle bin, emphasizing the importance of data recovery options in DBMS environments. Besides schema modifications, the assignment emphasizes querying data with aggregate functions, GROUP BY, HAVING, and set operators like UNION, INTERSECT, and MINUS, thereby cultivating skills necessary for consolidating and analyzing large datasets.

SQL query formulation is a core focus, with tasks requiring complex selections such as calculating average maximum salaries grouped by departments, combining multiple queries with UNION, and filtering data with subqueries. For example, students will find employees sharing the same department as a specific department, or those earning the minimum salary within a department. These exercises promote a deep understanding of data relationships and the use of nested queries, essential for managing real-world relational databases.

Finally, the assignment involves data updates and deletes, illustrating how foreign key and primary key constraints influence data integrity during modifications. Tasks include updating employee salaries, deleting records, and understanding error messages caused by referential integrity constraints. Students will analyze why certain delete operations fail when related records exist, emphasizing the importance of database design and the management of dependencies between tables.

Throughout, students are instructed to document their commands with screenshots, ensuring they can verify their steps and results. They are also required to answer conceptual questions, such as the differences between TRUNCATE and DROP, and the implications of updating schema objects or data, fostering both practical skills and theoretical understanding.

References

• Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.