SQL Queries and Database Design

16 Questions

What SQL query would you use to delete a product with the Product Code P1004?

DELETE FROM PRODUCT WHERE P_No = 'P1004'

What action is the database designer taking when analyzing the environment and mission of the company?

Analysing the company situation

At which stage of the database design cycle do you identify main processes and validate reports?

Database Analysis and Requirements

What SQL command would you use to grant access rights to a table LECTURER to a new professor with the code GRET?

GRANT USE OF TABLE LECTURE TO GRET;

What category of maintenance is involved in ensuring an incremental backup of the Organisation's Data every week and a full backup every month?

Preventative Maintenance

How would you examine the current balance for a customer number 104 located in the CUSTOMER table?

Using the SELECT command

What SQL command is used to delete a product with the Product Code P1004 from the table PRODUCT?

DELETE FROM PRODUCT WHERE P_No = 'P1004'

What stage of the database design cycle involves defining problems and constraints?

Database Analysis and Requirements

What is the correct query to retrieve the customer number and balance for a specific customer?

SELECT CUST_NUMBER, CUST_BALANCE FROM CUSTOMER WHERE CUST_NUMBER = 104

What phenomenon occurs when two transactions update a value at the same time, and the transaction for T1 is not committed before T2 transaction is committed?

Lost Updates

Which type of lock is mostly used in multiuser database management systems (DBMS)?

Page-level lock

What is an inaccurate change affecting database development and design?

Business objectives

What is the problem brought about by the growing number of remote locations over greater distances for a centralized database management system?

Performance degradation

What problem is associated with a company that depends on a central database?

Scalability problems

What is an advantage of a distributed database management system?

Improved communication

What is the main issue with a centralized database management system?

Scalability problems

Study Notes

Database Administration and Design

To delete a product from the Product table, the correct query is DELETE FROM PRODUCT WHERE P_No = 'P1004';
A database designer analyzing the company's situation and mission is performing the action of Analyzing the company situation

Database Design Cycle

Identifying main processes and validating reports is performed during the Database Analysis and Requirements stage

Database Security

To grant access rights to a table LECTURER to a new professor with code GRET, the correct command is GRANT USE OF TABLE LECTURER TO GRET;

Database Maintenance

Ensuring incremental backups every week and full backups every month is classified under Preventative Maintenance

SQL Queries

To examine the current balance for a customer with number 104 in the CUSTOMER table, the best query is SELECT CUST_NUMBER, CUST_BALANCE FROM CUSTOMER WHERE CUST_NUMBER = 104;

Database Transactions

When two transactions update a value at the same time, and the transaction for T1 is not committed before T2 transaction is committed, it is known as Lost Updates

Database Locking

Page-level lock locks the entire disk page and is mostly used in multi-user database management systems (DBMS)

Database Development and Design

Advanced network technologies is a social and technological change that has affected the development of database development and design

Centralized Database Management Systems

One problem associated with a centralized database is Scalability problems
One problem brought about by the growing number of remote locations over greater distances for a centralized database management system is Performance degradation

Distributed Database Management Systems

One advantage of a distributed database management system is the Improved communication

Test your knowledge of SQL queries and database design concepts with these questions. Identify the correct query to delete a product from a table and define the role of a database designer.

Make Your Own Quizzes and Flashcards

Convert your notes into interactive study material.