SQL Subjective Assessment Test

1. What does SQL stand for?

Structured query language

Software query language

Structured questionnaire language

Standard query language

SQL stands for Structured Query Language. It is a standardized programming language that is used for managing and manipulating relational databases. Through SQL, users can create, modify, and extract data from databases, and also control access to that data. The language organizes commands, clauses, and syntax to effectively retrieve and manage data. This standardization makes SQL essential for database management across various platforms.

Explanation

SQL stands for Structured Query Language. It is a standardized programming language that is used for managing and manipulating relational databases. Through SQL, users can create, modify, and extract data from databases, and also control access to that data. The language organizes commands, clauses, and syntax to effectively retrieve and manage data. This standardization makes SQL essential for database management across various platforms.

2. What happens to data updated in a view within the underlying table?

Cleaned

Deleted

Removed

Updated

When data are updated in a view, the corresponding data in the underlying table are also updated. A view is like a window or a specific presentation of data from one or more tables. Changes made through a view directly affect the base table since the view is just a query on the table. This ensures that the database remains consistent, whether modifications are made directly to the table or through the view.

Explanation

When data are updated in a view, the corresponding data in the underlying table are also updated. A view is like a window or a specific presentation of data from one or more tables. Changes made through a view directly affect the base table since the view is just a query on the table. This ensures that the database remains consistent, whether modifications are made directly to the table or through the view.

3. What are the two parts of SQL?

DML and DMBS

DMBS and DDL

DDL and DML

DML and JAVA

SQL, or Structured Query Language, is primarily divided into two parts: DDL (Data Definition Language) and DML (Data Manipulation Language). DDL includes commands that define the database structure, such as CREATE, ALTER, and DROP, which help in creating and modifying tables and database schemas. DML comprises commands that are used for managing data within those tables, including INSERT, UPDATE, DELETE, and SELECT. Understanding these two parts is essential for effectively managing and utilizing databases.

Explanation

SQL, or Structured Query Language, is primarily divided into two parts: DDL (Data Definition Language) and DML (Data Manipulation Language). DDL includes commands that define the database structure, such as CREATE, ALTER, and DROP, which help in creating and modifying tables and database schemas. DML comprises commands that are used for managing data within those tables, including INSERT, UPDATE, DELETE, and SELECT. Understanding these two parts is essential for effectively managing and utilizing databases.

4. In what objects are the data stored in an RDBMS?

Columns

Tables

Views

Doc

In a Relational Database Management System (RDBMS), data are primarily stored in tables. Tables are structured in rows and columns, with each row representing a record and each column representing a field. This structure allows for the efficient organization, retrieval, and manipulation of data. Tables are the fundamental building blocks of an RDBMS, enabling it to manage data in a way that ensures data integrity and facilitates complex querying and analysis.

Explanation

In a Relational Database Management System (RDBMS), data are primarily stored in tables. Tables are structured in rows and columns, with each row representing a record and each column representing a field. This structure allows for the efficient organization, retrieval, and manipulation of data. Tables are the fundamental building blocks of an RDBMS, enabling it to manage data in a way that ensures data integrity and facilitates complex querying and analysis.

5. Which of these is not a query or command in the DML (Data Manipulation Language) part of SQL?

Select

Update

Drop table

Delete

"Drop table" is not a part of Data Manipulation Language (DML); it belongs to Data Definition Language (DDL). DML commands are used to manage data within table objects. These commands include "Select," "Update," and "Delete," which are used to retrieve, modify, and remove data, respectively. On the other hand, DDL commands such as "Drop table" are used to define and modify the structure of database objects like tables. "Drop table" specifically is used to permanently remove a table and all of its data from the database.

Explanation

"Drop table" is not a part of Data Manipulation Language (DML); it belongs to Data Definition Language (DDL). DML commands are used to manage data within table objects. These commands include "Select," "Update," and "Delete," which are used to retrieve, modify, and remove data, respectively. On the other hand, DDL commands such as "Drop table" are used to define and modify the structure of database objects like tables. "Drop table" specifically is used to permanently remove a table and all of its data from the database.

6. Which of these is a physical structure containing pointers to data?

Insert

Views

Index

Table

An index is a physical structure containing pointers to data. It helps speed up the retrieval of rows from a database table by providing quick access to the rows based on the index keys. This structure is particularly useful in improving the performance of queries, as it allows the database system to find data without scanning the entire table. An index is analogous to an index in a book, which directs you to the exact pages where the information you are looking for can be found, thereby saving time.

Explanation

An index is a physical structure containing pointers to data. It helps speed up the retrieval of rows from a database table by providing quick access to the rows based on the index keys. This structure is particularly useful in improving the performance of queries, as it allows the database system to find data without scanning the entire table. An index is analogous to an index in a book, which directs you to the exact pages where the information you are looking for can be found, thereby saving time.

7. Which of these stores and manages SQL triggers?

Tables

Scala

Groovy

DMBS

DBMS, or Database Management System, is responsible for storing and managing SQL triggers. Triggers are special procedures that are automatically executed in response to certain events on a particular table or view in a database. These events can include insertions, updates, or deletions. A DBMS manages these triggers by activating them when their specific conditions are met, ensuring that automatic actions defined in the triggers are performed, which can help maintain data integrity and enforce business rules.

Explanation

DBMS, or Database Management System, is responsible for storing and managing SQL triggers. Triggers are special procedures that are automatically executed in response to certain events on a particular table or view in a database. These events can include insertions, updates, or deletions. A DBMS manages these triggers by activating them when their specific conditions are met, ensuring that automatic actions defined in the triggers are performed, which can help maintain data integrity and enforce business rules.

8. Why is truncate faster than delete in SQL?

It deletes only from the database.

It changes data instead of deleting them.

It writes the deleted records into rollback.

It moves data out of the database instead of deleting them.

Truncate is faster than delete in SQL because it does not generate a log for each deleted row and does not write the deleted records into the rollback segment. This is unlike the delete command, which logs each row it deletes, allowing the operation to be rolled back if necessary. Truncate effectively removes all rows in a table without logging the individual row deletions, making it a quicker operation when you don’t need to keep a record of the deletions for recovery or auditing purposes.

Explanation

Truncate is faster than delete in SQL because it does not generate a log for each deleted row and does not write the deleted records into the rollback segment. This is unlike the delete command, which logs each row it deletes, allowing the operation to be rolled back if necessary. Truncate effectively removes all rows in a table without logging the individual row deletions, making it a quicker operation when you don’t need to keep a record of the deletions for recovery or auditing purposes.

9. Which of the following statements about views in SQL is not true?

The result of using a view are permanently stored in the database

Views are created by standard T-SQL select command

The result of using a view are not permanently stored in the database

Views can be used for deleting rows

The statement that the results of using a view are permanently stored in the database is not true. Views in SQL are virtual tables that are not stored permanently; they are defined by a query and only display results when they are accessed. A view dynamically generates data from its underlying tables each time it is queried, without storing the data itself. This makes views useful for simplifying complex queries and providing a specific look at the data without altering the underlying structure or content of the database.

Explanation

The statement that the results of using a view are permanently stored in the database is not true. Views in SQL are virtual tables that are not stored permanently; they are defined by a query and only display results when they are accessed. A view dynamically generates data from its underlying tables each time it is queried, without storing the data itself. This makes views useful for simplifying complex queries and providing a specific look at the data without altering the underlying structure or content of the database.

10. What process is used to optimize databases by removing the potential for redundancy?

Nomination

Normalization

Nomination

Notifystack

Normalization is the process used to optimize databases by removing redundancy and potential for data anomalies. It involves organizing the fields and tables of a database to minimize duplication of information and to segregate data in a way that one fact is stored only once. Normalization typically occurs in several stages, each termed a "normal form," which systematically reduce data redundancy and improve data integrity. This process ensures that the database is efficient and that updates to the data do not lead to inconsistencies.

Explanation

Normalization is the process used to optimize databases by removing redundancy and potential for data anomalies. It involves organizing the fields and tables of a database to minimize duplication of information and to segregate data in a way that one fact is stored only once. Normalization typically occurs in several stages, each termed a "normal form," which systematically reduce data redundancy and improve data integrity. This process ensures that the database is efficient and that updates to the data do not lead to inconsistencies.