The Ultimate SQL Syntax And Functionality Knowledge Test

1. Nomalization reduces data redundancy.

True

False

Normalization is a process in database design that eliminates data redundancy by organizing data into separate tables and establishing relationships between them. By doing so, each piece of data is stored only once, reducing the chances of inconsistencies and improving data integrity. Therefore, the statement that normalization reduces data redundancy is true.

Explanation

Normalization is a process in database design that eliminates data redundancy by organizing data into separate tables and establishing relationships between them. By doing so, each piece of data is stored only once, reducing the chances of inconsistencies and improving data integrity. Therefore, the statement that normalization reduces data redundancy is true.

2. You want to select the employees that do not belong to any department

SELECT * FROM employee WHERE depatment_id is null;

SELECT * FROM employee WHERE depatment_id is not null;

SELECT * FROM employee WHERE depatment_id in (null);

SELECT * FROM employee IF EXISTS depatment_id;

The correct answer is "SELECT * FROM employee WHERE depatment_id is null;". This query selects all the employees from the "employee" table where the "department_id" column is null. This means that it will retrieve the employees who do not belong to any department, as null in the "department_id" column indicates the absence of a department assignment.

Explanation

The correct answer is "SELECT * FROM employee WHERE depatment_id is null;". This query selects all the employees from the "employee" table where the "department_id" column is null. This means that it will retrieve the employees who do not belong to any department, as null in the "department_id" column indicates the absence of a department assignment.

3. You want to get the unique first names of the employees that are from Portugal

SELECT UNIQUE first_name FROM employees WHERE county = 'Potugal'

SELECT DISTINCT first_name FROM employees WHERE country = 'Portugal'

SELECT first_name FROM employees WHERE county = 'Portugal'

None of the above

The correct answer is "SELECT DISTINCT first_name FROM employees WHERE country = 'Portugal'". This query selects the distinct (unique) first names from the employees table where the country is Portugal. The keyword "DISTINCT" ensures that only unique values are returned. The column name "country" is spelled correctly and matches the condition "Portugal".

Explanation

The correct answer is "SELECT DISTINCT first_name FROM employees WHERE country = 'Portugal'". This query selects the distinct (unique) first names from the employees table where the country is Portugal. The keyword "DISTINCT" ensures that only unique values are returned. The column name "country" is spelled correctly and matches the condition "Portugal".

4. You want to find the products whose pices are between 200 and 400

SELECT productName FROM products WHERE Pice > 200 AND < 400;

SELECT productName FROM products WHERE Pice BIGGER 200 AND SMALLER 400;

SELECT productName FROM products WHERE Pice BETWEEN 200 AND 400;

SELECT productName FROM products WHERE Pice IN 200 AND 400;

The correct answer is "SELECT productName FROM products WHERE Pice BETWEEN 200 AND 400;". This is because the BETWEEN operator is used to specify a range of values, in this case, the prices between 200 and 400. The other options do not use the correct syntax or operators to specify a range of values.

Explanation

The correct answer is "SELECT productName FROM products WHERE Pice BETWEEN 200 AND 400;". This is because the BETWEEN operator is used to specify a range of values, in this case, the prices between 200 and 400. The other options do not use the correct syntax or operators to specify a range of values.

5. A many-to-many relationship can be resolved by using a linking table

True

False

A many-to-many relationship refers to a situation where multiple records in one table are associated with multiple records in another table. In such cases, a linking table can be used to resolve this relationship. The linking table acts as an intermediary between the two tables, storing the related records and their associations. This allows for the efficient and organized management of the relationship, ensuring that each record is correctly linked to its associated records in both tables. Therefore, the statement "A many-to-many relationship can be resolved by using a linking table" is true.

Explanation

A many-to-many relationship refers to a situation where multiple records in one table are associated with multiple records in another table. In such cases, a linking table can be used to resolve this relationship. The linking table acts as an intermediary between the two tables, storing the related records and their associations. This allows for the efficient and organized management of the relationship, ensuring that each record is correctly linked to its associated records in both tables. Therefore, the statement "A many-to-many relationship can be resolved by using a linking table" is true.

6. You want to list all the databases existing in the curent MySql sever, which command would you use?

SELECT DATABASE NAMES FROM MYSQL

CREATE DATABASE

SHOW DATABASES

USE DATABASE NAMES

The correct answer is "SHOW DATABASES". This command is used in MySql to list all the databases that exist in the current server. It provides a result set that includes the names of all the databases available. The other options given in the question are not appropriate for this purpose.

Explanation

The correct answer is "SHOW DATABASES". This command is used in MySql to list all the databases that exist in the current server. It provides a result set that includes the names of all the databases available. The other options given in the question are not appropriate for this purpose.

7. Regarding the secuity manager

Sets-up a database connection

It veirfies the user granted pemissions to execute queries

Manages the buffer memory to optimize queies

None of the above

The security manager is responsible for verifying the user's granted permissions to execute queries. This means that before allowing a query to be executed, the security manager checks if the user has the necessary permissions to perform the operation. This ensures that only authorized users can execute queries and helps maintain the security of the database system.

Explanation

The security manager is responsible for verifying the user's granted permissions to execute queries. This means that before allowing a query to be executed, the security manager checks if the user has the necessary permissions to perform the operation. This ensures that only authorized users can execute queries and helps maintain the security of the database system.

8. Nomalization increases the isk of loosing data integity

True

False

Normalization actually reduces the risk of losing data integrity. It is a process in database design that eliminates redundancy and ensures that data is stored efficiently and accurately. By organizing data into separate tables and establishing relationships between them, normalization helps to maintain data integrity by avoiding data duplication and inconsistencies. Therefore, the given answer is incorrect.

Explanation

Normalization actually reduces the risk of losing data integrity. It is a process in database design that eliminates redundancy and ensures that data is stored efficiently and accurately. By organizing data into separate tables and establishing relationships between them, normalization helps to maintain data integrity by avoiding data duplication and inconsistencies. Therefore, the given answer is incorrect.

9. What is tue about the following query: SELECT o.orderNumber, p.productName, p.msrp, o.piceEach FROM products p JOIN orderdetails o ON p.productcode = o.productcode AND p.msrp > o.piceEach WHERE p.productcode = 'S10_1678';

The query only retieves data from table products

The query uses an inner join

The query uses a ight join

The query joins tables based on the msrp as key column

The given query uses an inner join because it combines the data from two tables, "products" and "orderdetails", based on the condition that the product code in both tables matches. The SELECT statement retrieves specific columns from both tables, and the WHERE clause filters the results based on a specific product code.

Explanation

The given query uses an inner join because it combines the data from two tables, "products" and "orderdetails", based on the condition that the product code in both tables matches. The SELECT statement retrieves specific columns from both tables, and the WHERE clause filters the results based on a specific product code.

10. We're interested in wine stores. Therefore, we want to retieve the SUPNR and SUPNAME of each store which contains "wine" in its store name. Which of the following queies can we use?

SELECT SUPNR, SUPNAME FROM SUPPLIER WHERE SUPNAME = "WINE"

SELECT SUPNR, SUPNAME FROM SUPPLIER WHERE SUPNAME IS "%WINE%"

SELECT SUPNR, SUPNAME FROM SUPPLIER WHERE SUPNAME IS "WINE"

SELECT SUPNR, SUPNAME FROM SUPPLIER WHERE SUPNAME LIKE "%WINE%"

The correct answer is "SELECT SUPNR, SUPNAME FROM SUPPLIER WHERE SUPNAME LIKE "%WINE%"". This query uses the LIKE operator with the wildcard "%" to search for any store name that contains the word "wine". The "%" wildcard represents any number of characters, so this query will retrieve the SUPNR and SUPNAME of all stores that have "wine" anywhere in their name.

Explanation

The correct answer is "SELECT SUPNR, SUPNAME FROM SUPPLIER WHERE SUPNAME LIKE "%WINE%"". This query uses the LIKE operator with the wildcard "%" to search for any store name that contains the word "wine". The "%" wildcard represents any number of characters, so this query will retrieve the SUPNR and SUPNAME of all stores that have "wine" anywhere in their name.

11. CRUD refers to

Create, Read, Update, Drop

Create, Read, Update, Delete

Create, Retieve, Update, Drop

Collect, Read, Update, Delete

None of the above

The correct answer is "Create, Read, Update, Delete." CRUD is an acronym that stands for the basic operations performed on data in a database or computer system. Create refers to the action of creating new data, Read is for retrieving existing data, Update is for modifying existing data, and Delete is for removing data. This set of operations represents the fundamental actions needed to manage data effectively.

Explanation

The correct answer is "Create, Read, Update, Delete." CRUD is an acronym that stands for the basic operations performed on data in a database or computer system. Create refers to the action of creating new data, Read is for retrieving existing data, Update is for modifying existing data, and Delete is for removing data. This set of operations represents the fundamental actions needed to manage data effectively.

12. About the 1NF (one or more can be selected):

Each cell in the table can have only one value

Each cell in the table can have only one value, except for numerical values

Each cell in the table can have only one value, except for character values

It is possible to have more than one value, as long as the values are separated by a semicolon

None of the above

In 1NF (First Normal Form), each cell in the table can have only one value. This means that each attribute in a table should have a single value, and there should be no repeating groups or arrays of values within a single attribute. This ensures that the table is atomic and avoids data redundancy and anomalies.

Explanation

In 1NF (First Normal Form), each cell in the table can have only one value. This means that each attribute in a table should have a single value, and there should be no repeating groups or arrays of values within a single attribute. This ensures that the table is atomic and avoids data redundancy and anomalies.

13. Assuming that emp_no is the pimay key, and that the job_title is unique. What the following query does? SELECT e.first_name, t.title, e.emp_no FROM employees AS e, titles AS t WHERE e.emp_no = t.emp_no AND t.title = 'Senior Engineer

Retrieve the first name, title, and employee identifier of the senior engineers

Retrieve the first name, title, and employee identifier of the employees that are not senior engineers

Retrieve the first name, title, and employee identifier of employees where the employee identifies is equal to the title identifier

None of the above

The given query retrieves the first name, title, and employee identifier of the senior engineers. This is because the query joins the "employees" and "titles" tables using the employee identifier (emp_no) as the common attribute. It then filters the results to only include rows where the title is "Senior Engineer". Therefore, the query will return the desired information for senior engineers only.

Explanation

The given query retrieves the first name, title, and employee identifier of the senior engineers. This is because the query joins the "employees" and "titles" tables using the employee identifier (emp_no) as the common attribute. It then filters the results to only include rows where the title is "Senior Engineer". Therefore, the query will return the desired information for senior engineers only.

14. The tigger can be activated in the following events:

INSERT, UPDATE, DELETE

SELECT, INSERT, UPDATE, DELETE

INSERT and UPDATE

INSERT and DELETE

The correct answer is INSERT, UPDATE, DELETE because these are the events that can activate the tigger. A trigger is a database object that is associated with a table and is automatically executed or fired when a specific event occurs, such as inserting, updating, or deleting data in the table. Therefore, the trigger can be activated in these events to perform certain actions or tasks defined in the trigger code.

Explanation

The correct answer is INSERT, UPDATE, DELETE because these are the events that can activate the tigger. A trigger is a database object that is associated with a table and is automatically executed or fired when a specific event occurs, such as inserting, updating, or deleting data in the table. Therefore, the trigger can be activated in these events to perform certain actions or tasks defined in the trigger code.

15. An entity type is 2NF when all of its non-key attibutes are fully dependent on its primary key.

True

False

A 2NF (Second Normal Form) entity type is one where all of its non-key attributes are fully dependent on its primary key. This means that every non-key attribute in the entity type must be functionally dependent on the entire primary key, rather than just a part of it. In other words, the values of the non-key attributes can be determined solely by the primary key attributes. Therefore, the given statement is true.

Explanation

A 2NF (Second Normal Form) entity type is one where all of its non-key attributes are fully dependent on its primary key. This means that every non-key attribute in the entity type must be functionally dependent on the entire primary key, rather than just a part of it. In other words, the values of the non-key attributes can be determined solely by the primary key attributes. Therefore, the given statement is true.

16. In crow's foot notation:

An entity type is represented by a rectangle

The name of entity type should be singular

The name of the entity type goes in the upper pat of the rectangle

All of the above

The given answer is "All of the above" because all three statements are correct. In crow's foot notation, an entity type is represented by a rectangle, the name of the entity type should be singular, and the name of the entity type goes in the upper part of the rectangle.

Explanation

The given answer is "All of the above" because all three statements are correct. In crow's foot notation, an entity type is represented by a rectangle, the name of the entity type should be singular, and the name of the entity type goes in the upper part of the rectangle.

17. About the left join, being table A the left table and B the ight table:

Return all of the records in table A that do not match any records in the table table B

Returns only the records in table A that match the records in table B.

Returns all of the records in table A regardless if the records have a match in table B.

Returns all of the records in table B regardless if the records have a match in table A

None of the above

The correct answer is "Returns all of the records in table A regardless if the records have a match in table B." This means that even if there is no matching record in table B for a record in table A, it will still be included in the result of the left join.

Explanation

The correct answer is "Returns all of the records in table A regardless if the records have a match in table B." This means that even if there is no matching record in table B for a record in table A, it will still be included in the result of the left join.

18. You want to retieve the average salay by year

SELECT YEAR, AVG(s.salay) FROM employees AS e, salaies AS s WHERE e.emp_no = s.emp_no GROUP BY e.hire_date

SELECT e.hire_date, AVG(s.salay) FROM employees AS e, salaies AS s WHERE e.emp_no = s.emp_no GROUP BY e.hire_date

SELECT YEAR(e.hire_date) AS year, SUM(s.salay) FROM employees AS e, salaies AS s WHERE e.emp_no = s.emp_no GROUP BY YEAR(e.hire_date)

SELECT YEAR(e.hire_date) AS year, AVG(s.salay) FROM employees AS e, salaies AS s WHERE e.emp_no = s.emp_no GROUP BY YEAR(e.hire_date)

The correct answer is "SELECT YEAR(e.hire_date) AS year, AVG(s.salay) FROM employees AS e, salaies AS s WHERE e.emp_no = s.emp_no GROUP BY YEAR(e.hire_date)". This query selects the year from the hire_date column and calculates the average salary from the salay column in the employees and salaies tables. It then groups the results by the year. This allows for the retrieval of the average salary by year.

Explanation

The correct answer is "SELECT YEAR(e.hire_date) AS year, AVG(s.salay) FROM employees AS e, salaies AS s WHERE e.emp_no = s.emp_no GROUP BY YEAR(e.hire_date)". This query selects the year from the hire_date column and calculates the average salary from the salay column in the employees and salaies tables. It then groups the results by the year. This allows for the retrieval of the average salary by year.

19. Assume table REGION has 3 rows with region_name= 'Ameicas', 2 rows with region_name = 'Europe', and 4 rows with other world regions. How many rows updates the following SQL code: UPDATE region SET egion_name = 'America' WHERE region_name = 'Americas' AND region_name = 'Europe';

0

5

3

2

4

not-available-via-ai

Explanation

not-available-via-ai

20. Between DDL and DML

DML compiler helps to creates tables

DDL compiler helps to inset data

MySQL only manages DML, not DDL

None of the above

The given answer "None of the above" is correct because the statements provided in the question are incorrect. In reality, DDL (Data Definition Language) is responsible for creating tables and modifying the structure of the database, while DML (Data Manipulation Language) is used to insert, update, and delete data within the tables. Additionally, MySQL is capable of managing both DDL and DML operations. Therefore, the correct answer is "None of the above" as the statements provided do not accurately describe the roles of DDL and DML or the capabilities of MySQL.

Explanation

The given answer "None of the above" is correct because the statements provided in the question are incorrect. In reality, DDL (Data Definition Language) is responsible for creating tables and modifying the structure of the database, while DML (Data Manipulation Language) is used to insert, update, and delete data within the tables. Additionally, MySQL is capable of managing both DDL and DML operations. Therefore, the correct answer is "None of the above" as the statements provided do not accurately describe the roles of DDL and DML or the capabilities of MySQL.

21. What is true for this code SELECT concat(p.product_code, ' ', p.product_name) FROM product AS p;

Retrieves two columns: product code and product name

Retrieves only one column

May retrieve more than one column if product code is not unique

All the options are wrong

The given code is using the CONCAT function to concatenate the product code and product name columns into a single column. Therefore, the code retrieves only one column, which contains the concatenated values.

Explanation

The given code is using the CONCAT function to concatenate the product code and product name columns into a single column. Therefore, the code retrieves only one column, which contains the concatenated values.

22. About the following SQL code: DELETE FROM region LIMIT 2 WHERE region_name = 'Europe';

Deletes 2 rows coresponding to Europe region

Completes execution but deletes zero regions

Deletes the two first rows regardless of which region they refer to

Raises an error

The SQL code is attempting to delete 2 rows from the "region" table where the "region_name" is 'Europe'. However, the syntax of the code is incorrect. The "LIMIT" clause should be placed after the "WHERE" clause. Therefore, when the code is executed, it will raise an error.

Explanation

The SQL code is attempting to delete 2 rows from the "region" table where the "region_name" is 'Europe'. However, the syntax of the code is incorrect. The "LIMIT" clause should be placed after the "WHERE" clause. Therefore, when the code is executed, it will raise an error.

23. What is true about ETL tools

Highly recommended for Data Warehouses

Extract data only from stuctured sources

They must be from the same provider of the database sever

All options are wrong

ETL tools are highly recommended for Data Warehouses because they are specifically designed to Extract, Transform, and Load data from various sources into a centralized data warehouse. These tools facilitate the process of extracting data from different structured sources, transforming it into a consistent format, and loading it into the data warehouse for analysis and reporting purposes. ETL tools help in automating and streamlining the data integration process, ensuring the data quality and consistency in the data warehouse. Therefore, they are considered essential for building and maintaining a robust and efficient data warehouse solution.

Explanation

ETL tools are highly recommended for Data Warehouses because they are specifically designed to Extract, Transform, and Load data from various sources into a centralized data warehouse. These tools facilitate the process of extracting data from different structured sources, transforming it into a consistent format, and loading it into the data warehouse for analysis and reporting purposes. ETL tools help in automating and streamlining the data integration process, ensuring the data quality and consistency in the data warehouse. Therefore, they are considered essential for building and maintaining a robust and efficient data warehouse solution.

24. About primary key:

Must be composed by one column only

Can be composed by multiple columns

Suppots not unique values if defined as varchar type

It is a bad practice to leave tables without a primary key

A primary key can be composed by multiple columns, meaning that it can consist of more than one column in a table. This is useful when a single column cannot uniquely identify each row in a table, and a combination of columns is required to ensure uniqueness. It is considered a bad practice to leave tables without a primary key because it can lead to data integrity issues and difficulties in managing and querying the table effectively.

Explanation

A primary key can be composed by multiple columns, meaning that it can consist of more than one column in a table. This is useful when a single column cannot uniquely identify each row in a table, and a combination of columns is required to ensure uniqueness. It is considered a bad practice to leave tables without a primary key because it can lead to data integrity issues and difficulties in managing and querying the table effectively.

Submit

25. SQL vs NoSQL databases. Which statement is tue?

MongoDB is a well-known relational database

NoSQL databases are easier to scale horizontally than relational databases

Relational databases are easier to scale horizontally than NoSQL

NoSQL databases are the best choice if we want to ensure data consistency

NoSQL databases are easier to scale horizontally than relational databases. This is because NoSQL databases are designed to handle large amounts of data and are built to scale horizontally by adding more servers to distribute the workload. In contrast, relational databases are typically scaled vertically by adding more resources to a single server, which can be more expensive and limited in terms of scalability. Horizontal scaling in NoSQL databases allows for better performance and availability as the workload is distributed across multiple servers.

Explanation

NoSQL databases are easier to scale horizontally than relational databases. This is because NoSQL databases are designed to handle large amounts of data and are built to scale horizontally by adding more servers to distribute the workload. In contrast, relational databases are typically scaled vertically by adding more resources to a single server, which can be more expensive and limited in terms of scalability. Horizontal scaling in NoSQL databases allows for better performance and availability as the workload is distributed across multiple servers.

26. What is true about the SQL dialect

Refers to the oiginal SQL86 version

The way SQL coders communicate to each other

The SQL implementation provided by each vendor

The SQL propety of being embedded in JAVA applications

The correct answer is the SQL implementation provided by each vendor. This refers to the fact that different database vendors may have their own variations and extensions of the SQL language. Each vendor provides their own implementation of SQL, which may include additional features or syntax that are specific to their database system. This means that SQL code written for one vendor's database may not necessarily work on another vendor's database without modification.

Explanation

The correct answer is the SQL implementation provided by each vendor. This refers to the fact that different database vendors may have their own variations and extensions of the SQL language. Each vendor provides their own implementation of SQL, which may include additional features or syntax that are specific to their database system. This means that SQL code written for one vendor's database may not necessarily work on another vendor's database without modification.

27. What is true about a view

It is a vitual table without physical rows

The name of the view cannot be the same as the table

The view does not physically store the data

All the previous are tue

None of the above

A view is a virtual table that does not have physical rows, meaning it does not store the data itself. Additionally, the name of the view cannot be the same as the table it is based on. Therefore, all of the previous statements are true.

Explanation

A view is a virtual table that does not have physical rows, meaning it does not store the data itself. Additionally, the name of the view cannot be the same as the table it is based on. Therefore, all of the previous statements are true.

28. Transfomation activities do not include:

Aggregation and merging

Data source selection

Fomatting

Cleansing

Transformation activities refer to the process of manipulating and changing data in order to make it suitable for analysis or storage. This involves tasks such as aggregation, merging, formatting, and cleansing. However, data source selection is not considered a transformation activity. It refers to the initial step of choosing the appropriate data sources for analysis, rather than altering or modifying the data itself.

Explanation

Transformation activities refer to the process of manipulating and changing data in order to make it suitable for analysis or storage. This involves tasks such as aggregation, merging, formatting, and cleansing. However, data source selection is not considered a transformation activity. It refers to the initial step of choosing the appropriate data sources for analysis, rather than altering or modifying the data itself.

29. The following table with purchase orders is created: CREATE TABLE PURCHASE_ORDER PONR CHAR7 NOT NULL PRIMARY KEY, PODATE DATE, SUPNR CHAR4 NOT NULL, FOREIGN KEY SUPNR REFERENCES SUPPLIER SUPNR ON DELETE CASCADE ON UPDATE CASCADE; What happens upon deletion of a supplier?

All purchase order records tied to that supplier are also deleted

The SUPNR of this supplier is replaced by a NULL value in PURCHASE_ORDER.

The SUPNR of this supplier is deleted in PURCHASE_ORDER.

The SUPNR of this supplier is only deleted in SUPPLIER

Upon deletion of a supplier, all purchase order records tied to that supplier are also deleted. This is because of the "ON DELETE CASCADE" clause specified in the foreign key constraint of the PURCHASE_ORDER table. This clause ensures that when a referenced row in the SUPPLIER table is deleted, all corresponding rows in the PURCHASE_ORDER table are also deleted automatically. Therefore, deleting a supplier will result in the deletion of all purchase order records associated with that supplier.

Explanation

Upon deletion of a supplier, all purchase order records tied to that supplier are also deleted. This is because of the "ON DELETE CASCADE" clause specified in the foreign key constraint of the PURCHASE_ORDER table. This clause ensures that when a referenced row in the SUPPLIER table is deleted, all corresponding rows in the PURCHASE_ORDER table are also deleted automatically. Therefore, deleting a supplier will result in the deletion of all purchase order records associated with that supplier.

30. Which ACID propety corespond to the following affimation: "ensures that concurent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially"

Atomicity

Consistency

Isolation

Durability

Isolation is the correct answer because it refers to the property that ensures concurrent transactions do not interfere with each other. It ensures that each transaction is executed in isolation, as if it were the only transaction being executed. This means that the concurrent execution of transactions will not affect the final state of the database, which would have been achieved if the transactions were executed sequentially.

Explanation

Isolation is the correct answer because it refers to the property that ensures concurrent transactions do not interfere with each other. It ensures that each transaction is executed in isolation, as if it were the only transaction being executed. This means that the concurrent execution of transactions will not affect the final state of the database, which would have been achieved if the transactions were executed sequentially.

31. What retieves the following query? SELECT T1.orderNumber, T1.status, SUMT2.quantityOrdered * T2.piceEach) total FROM orders AS T1 INNER JOIN orderdetails AS T2 ON T1.orderNumber = T2.orderNumber GROUP BY T1.orderNumber;

OrderNumber, status, quantityOrdered, and piceEach

OrderNumber, status, and the order average

OrderNumber, status, and the total of the order

OrderNumber, status, the total of each order detail

The given query retrieves the orderNumber, status, and the total of the order. This is achieved by selecting the orderNumber and status from the orders table (aliased as T1) and calculating the total by multiplying the quantityOrdered and piceEach from the orderdetails table (aliased as T2) and then summing them. The result is grouped by orderNumber to get the total for each order.

Explanation

The given query retrieves the orderNumber, status, and the total of the order. This is achieved by selecting the orderNumber and status from the orders table (aliased as T1) and calculating the total by multiplying the quantityOrdered and piceEach from the orderdetails table (aliased as T2) and then summing them. The result is grouped by orderNumber to get the total for each order.

32. What is tue about the CAP theorem?

Relational databases are usually associated with partition tolerance

Most of NoSQL databases will hold consistency and availability

CAP stands for: Critical, Availability, Peformance

You can only pick two propeties

The CAP theorem states that in a distributed system, it is impossible to simultaneously guarantee consistency, availability, and partition tolerance. Therefore, you can only choose two out of the three properties. This means that if you prioritize consistency and availability, you may have to sacrifice partition tolerance, and vice versa. This is why relational databases, which prioritize consistency, are usually associated with partition tolerance, while most NoSQL databases prioritize availability and partition tolerance.

Explanation

The CAP theorem states that in a distributed system, it is impossible to simultaneously guarantee consistency, availability, and partition tolerance. Therefore, you can only choose two out of the three properties. This means that if you prioritize consistency and availability, you may have to sacrifice partition tolerance, and vice versa. This is why relational databases, which prioritize consistency, are usually associated with partition tolerance, while most NoSQL databases prioritize availability and partition tolerance.

33. You are designing a database table to store student data.

I prefer to store the student age (number)

I prefer to store the student date of bith (date)

I will store both

None.

The correct answer is to store the student date of birth (date). Storing the date of birth allows for more accurate and precise information about the student's age. By storing the date of birth, it becomes possible to calculate the student's age at any given point in time, taking into account leap years and other factors. Storing just the age as a number would require constant updating as the student gets older, and it would not provide the same level of detail and flexibility as storing the date of birth.

Explanation

The correct answer is to store the student date of birth (date). Storing the date of birth allows for more accurate and precise information about the student's age. By storing the date of birth, it becomes possible to calculate the student's age at any given point in time, taking into account leap years and other factors. Storing just the age as a number would require constant updating as the student gets older, and it would not provide the same level of detail and flexibility as storing the date of birth.

34. What is true about triggers:

Tiggers do not affect pefomance

They may cause deadlock situations

Appropriate for automatic auditing but not advisable for logging

Tiggers are easy to debug

Triggers are database objects that are automatically executed in response to specified events. Deadlock situations can occur when multiple transactions are waiting for each other to release resources, resulting in a deadlock. Triggers can be a potential cause of deadlocks if they are not designed properly or if they involve complex logic. Therefore, it is true that triggers may cause deadlock situations.

Explanation

Triggers are database objects that are automatically executed in response to specified events. Deadlock situations can occur when multiple transactions are waiting for each other to release resources, resulting in a deadlock. Triggers can be a potential cause of deadlocks if they are not designed properly or if they involve complex logic. Therefore, it is true that triggers may cause deadlock situations.

35. If an entity type is in 3NF, then... (one or more can be selected):

All attributes are functionally dependent on the primary key

All attributes are transitively dependent on the primay key

All attibutes are not transitively dependent on the primary key

Complies with the 1NF, but not necessarily with the 2NF

None of the above

If an entity type is in 3NF, it means that it satisfies certain normalization rules. In 3NF, all attributes are functionally dependent on the primary key, which means that the values of the attributes are determined by the primary key. Additionally, all attributes are not transitively dependent on the primary key, which means that there are no indirect dependencies between attributes. This ensures that the entity type is free from redundancy and anomalies. The statement "Complies with the 1NF, but not necessarily with the 2NF" is incorrect because 3NF implies compliance with both 1NF and 2NF. Therefore, the correct answer is "all attributes are functionally dependent on the primary key, all attributes are not transitively dependent on the primary key."

Explanation

If an entity type is in 3NF, it means that it satisfies certain normalization rules. In 3NF, all attributes are functionally dependent on the primary key, which means that the values of the attributes are determined by the primary key. Additionally, all attributes are not transitively dependent on the primary key, which means that there are no indirect dependencies between attributes. This ensures that the entity type is free from redundancy and anomalies. The statement "Complies with the 1NF, but not necessarily with the 2NF" is incorrect because 3NF implies compliance with both 1NF and 2NF. Therefore, the correct answer is "all attributes are functionally dependent on the primary key, all attributes are not transitively dependent on the primary key."

Submit

36. If the following reasons are tue, for which of them this SQL code raises an eror: INSERT INTO depatment (`DEPARTMENT_ID`,`DEPARTMENT_NAME`,`LOCATION_ID`) VALUES (4, 'MARKETING2', 100, 1000);

You are inserting a DEPARTMENT_ID that already exists and DEPARTMENT_ID is defined as pimay key

The number of columns is different from the number of values

All of the previous reasons

The SQL code raises an error due to all of the previous reasons mentioned. Firstly, inserting a DEPARTMENT_ID that already exists and is defined as a primary key violates the uniqueness constraint of the primary key. Secondly, the number of columns specified in the INSERT statement is different from the number of values provided, causing a mismatch in the data being inserted. Lastly, inserting a value for LOCATION_ID that does not exist in the LOCATION table, where LOCATION_ID is defined as NOT NULL, violates the foreign key constraint.

Explanation

The SQL code raises an error due to all of the previous reasons mentioned. Firstly, inserting a DEPARTMENT_ID that already exists and is defined as a primary key violates the uniqueness constraint of the primary key. Secondly, the number of columns specified in the INSERT statement is different from the number of values provided, causing a mismatch in the data being inserted. Lastly, inserting a value for LOCATION_ID that does not exist in the LOCATION table, where LOCATION_ID is defined as NOT NULL, violates the foreign key constraint.

37. Consider the following query: SELECT P.PRODNR, P.PRODNAME, SUML.QUANTITY AS ORDERED_QUANTITY FROM PRODUCT AS P LEFT JOIN PO_LINE AS L ON P.PRODNR=L.PRODNR GROUP BY P.PRODNR, P.PRODNAME Which of the following statements is not correct?

The query retieves the product number, and product name of each product thanks to the left join.

The query result can never contain NULL values.

The query retieves for each product the ordered quantity.

The query result can contain NULL values because it is using a LEFT JOIN, which means that it will retrieve all records from the "PRODUCT" table, even if there is no matching record in the "PO_LINE" table. In such cases, the columns from the "PO_LINE" table will have NULL values.

Explanation

The query result can contain NULL values because it is using a LEFT JOIN, which means that it will retrieve all records from the "PRODUCT" table, even if there is no matching record in the "PO_LINE" table. In such cases, the columns from the "PO_LINE" table will have NULL values.

38. About SQL

It is an object oiented programming language

It descibes the the set of data to be retieved/modified without specifying how to compute it

Can be embedded in java code

SQL means: Stuctured Quality Language

All of the above

The correct answer is "It describes the set of data to be retrieved/modified without specifying how to compute it" and "Can be embedded in java code". This is because SQL (Structured Query Language) is a language used for managing and manipulating relational databases. It is used to retrieve and modify data from databases by specifying the desired set of data, without specifying the specific steps or computations needed to obtain it. Additionally, SQL can be embedded in programming languages like Java to interact with databases.

Explanation

The correct answer is "It describes the set of data to be retrieved/modified without specifying how to compute it" and "Can be embedded in java code". This is because SQL (Structured Query Language) is a language used for managing and manipulating relational databases. It is used to retrieve and modify data from databases by specifying the desired set of data, without specifying the specific steps or computations needed to obtain it. Additionally, SQL can be embedded in programming languages like Java to interact with databases.

Submit

39. Qual é o sentido da vida?

Comer e ser Comido

Segues sempre em frente e viras à direita

Putas e Vinho Verde

not-available-via-ai

Explanation

not-available-via-ai

40. Which one do you like?

Option 1

Option 2

Option 3

Option 4

The given question asks for personal preference among four options. The answer, Option 4, suggests that the person prefers Option 4 over the other three options.

Explanation

The given question asks for personal preference among four options. The answer, Option 4, suggests that the person prefers Option 4 over the other three options.