Crowdly

Add to Chrome

DataBase Fundamentals new

Looking for DataBase Fundamentals new test answers and solutions? Browse our comprehensive collection of verified answers for DataBase Fundamentals new at softserve.academy.

Get instant access to accurate answers and detailed explanations for your course questions. Our community-driven platform helps students succeed!

Suppose you have a PostgreSQL database that contains three tables, orders, order_details and customers. The orders table contains basic information about each order, including the order ID and customer ID, customers table contains information about customer, while the order_details table contains the details of each order, including the product ID, quantity, and price. You want to create a view that shows the order ID, customer name, product name, quantity, and price for all orders.

Example of the orders table:

order_id	customer_id	order_date
1	1	2022-03-01
2	2	2022-03-02
3	1	2022-03-03
4	3	2022-03-04

Example of the order_details table:

order_id	product_id	quantity	price
1	1	3	10.00
2	2	5	15.00
1	3	2	20.00
4	1	1	8.50

Example of the customers table:

customer_id	customer_name	city
1	Laurence Lebihan	London
2	Diego Roel	Madrid

Which of the following CREATE VIEW statements would accomplish this?

CREATE VIEW order_details_view AS

SELECT o.order_id, c.customer_name, d.product_name, d.quantity, d.price

FROM orders o

JOIN order_details d ON o.order_id = d.order_id

JOIN customers c ON o.customer_id = c.customer_id;

✅

CREATE VIEW order_details_view AS

SELECT order_id, customer_name, product_name, quantity, price

FROM orders

JOIN order_details ON orders.order_id = order_details.order_id

JOIN customers ON orders.customer_id = customers.customer_id;

❌

CREATE VIEW order_details_view AS

SELECT order_id, customer_name, product_name, quantity, price

FROM orders

INNER JOIN order_details ON orders.order_id = order_details.order_id

INNER JOIN customers ON orders.customer_id = customers.customer_id;

❌

CREATE VIEW order_details_view AS

SELECT o.order_id, c.customer_name, d.product_name, d.quantity, d.price

FROM orders o, order_details d, customers c

WHERE o.order_id = d.order_id

AND o.customer_id = c.customer_id;

✅

View this question

Suppose you have two tables in a PostgreSQL database, orders and products. You want to create a view that shows the order ID, product name, and quantity for all orders that include a particular product. Which of the following CREATE VIEW statements would accomplish this?

Table Example:

orders table:

| order_id | product_id | quantity |

|----------|------------|----------|

| 1 | 1 | 3 |

| 2 | 2 | 5 |

| 3 | 1 | 2 |

| 4 | 3 | 1 |

products table:

| id | product_name |

|----|--------------|

| 1 | Widget A |

| 2 | Widget B |

| 3 | Widget C |

CREATE VIEW order_product AS

SELECT order_id, product_name, quantity

FROM orders

JOIN products ON orders.product_id = products.id

WHERE product_name = 'example_product';

✅

CREATE VIEW order_product AS

SELECT orders.order_id, products.product_name, orders.quantity

FROM orders, products

WHERE orders.product_id = products.id

AND products.product_name = 'example_product';

✅

CREATE VIEW order_product AS

SELECT orders.order_id, products.product_name, orders.quantity

FROM orders, products

WHERE orders.product_id = products.id

AND products.product_name LIKE '%example_product%';

❌

CREATE VIEW order_product AS

SELECT order_id, product_name, quantity

FROM orders

JOIN products ON orders.product_id = products.id

HAVING product_name = 'example_product';

❌

View this question

What is the difference between a regular view and a materialized view in PostgreSQL?

Regular views are read-only, while materialized views allow data modification

❌

Materialized views are not updated automatically when underlying data changes, while regular views always reflect real-time data

✅

There is no difference; both terms are used interchangeably in PostgreSQL

❌

Regular views store data physically on disk, while materialized views are virtual

❌

View this question

What is the benefit of using materialized (indexed) views?

They can materialize (persist) the the results of view in a table-like form

✅

They can help to simplify complex queries and join data from multiple tables

❌

They can combine data from one or more base tables (or views) into a new virtual table

❌

They can join horizontally partitioned data from one or more base tables across one or more servers

❌

View this question

Which type of view combines data from one or more base tables (or views) into a new virtual table?

Partitioned views

Indexed views

Logical views

Standard views

What is a SQL View?

A group of related tables that are linked together

❌

A query that retrieves and displays data from one or more tables

✅

A stored procedure that performs a set of operations on data

❌

A physical table that stores data

❌

View this question

What is the correct syntax for deleting records from table_name with a specific condition in PostgreSQL?

DELETE WHERE condition SET column = value;

❌

DELETE FROM table_name WHERE condition;

✅

DELETE WHERE condition from table_name;

❌

DELETE SET column = value WHERE condition;

❌

View this question

Suppose you have a table named sales with columns id, product_name, quantity, and price. You want to update the column total_price by multiplying the quantity and price columns. Which SQL statement will achieve this?

UPDATE FROM sales SET total_price = (quantity * price);

❌

UPDATE TABLE sales SET total_price = quantity * price;

❌

UPDATE sales SET total_price = quantity - price;

❌

UPDATE sales SET total_price = quantity * price;

✅

View this question

How can you insert the result of a SELECT query with a GROUP BY clause and an ORDER BY the sum in descending order into a new table in PostgreSQL?

INSERT INTO new_table (column1, column2, total)

SELECT column1, column2, SUM(column3) AS total

FROM original_table

GROUP BY column1, column2

ORDER BY total DESC;

✅

INSERT INTO new_table

SELECT column1, column2, SUM(column3)

FROM original_table

GROUP BY column1, column2

ORDER BY column3 ASC;

INSERT INTO new_table

SELECT column1, column2, SUM(column3)

FROM original_table

GROUP BY column1, column2

ORDER BY column3 DESC;

INSERT INTO new_table

SELECT column1, column2, AVG(column3)

FROM original_table

GROUP BY column1, column2

ORDER BY column3 ASC;

INSERT INTO new_table (column1, column2, total)

SELECT column1, column2, SUM(column3)

FROM original_table

GROUP BY column1, column2

ORDER BY SUM(column3) DESC;

View this question

How to insert records into a table using a nested subquery and the ANY operator in PostgreSQL?

INSERT INTO table1 (col1, col2)

SELECT col1, col2

FROM table2

WHERE col3 NOT ANY (SELECT col3 FROM table3);

❌

INSERT INTO table1 (col1, col2)

SELECT col1, col2

FROM table2

WHERE col3 IN (SELECT col3 FROM table3);

❌

INSERT INTO table1 (col1, col2)

SELECT col1, col2

FROM table2

WHERE col3 = (SELECT col3 FROM table3);

❌

INSERT INTO table1 (col1, col2)

SELECT col1, col2

FROM table2

WHERE col3 = ANY (SELECT col3 FROM table3);

✅

View this question

Want instant access to all verified answers on softserve.academy?

Get Unlimited Answers To Exam Questions - Install Crowdly Extension Now!

Add to Chrome

Telegram Instagram TikTok Question Bank