Fourth Quiz

What does the following query return?
SELECT Name, Price

FROM Products

WHERE ProductTypeId = -1;

• All product names and prices where the brand is Adidas.

• All product names and prices where the product type is Shoes.

• All product names and prices where the product price is less than 1.

• An error because ProductTypeId is negative.

What is the purpose of the ORDER BY Price DESC clause in the following query?

SELECT Name, Price

FROM Products

WHERE Price > 5000

ORDER BY Price DESC;

• Sort the products by name in descending order.

• Sort the products by price in ascending order.

• Sort the products by price in descending order.

• Remove duplicates from the result.

What does the following query return?

SELECT COUNT(*) AS TotalProducts

FROM Products;

• The total number of rows in the Products table.

• The number of distinct products in the Products table.

• The total price of all products in the Products table.

• The total number of columns in the Products table.

What is the purpose of the following query?

SELECT

pb.Name AS BrandName,

SUM(p.Price) AS TotalPrice

FROM Products p

INNER JOIN ProductBrands pb ON p.ProductBrandId = pb.Id

GROUP BY pb.Name;

• Retrieve the total number of products for each brand.

• Retrieve the total price of products for each brand.

• Retrieve the most expensive product for each brand.

• Retrieve all product brands with no associated products.

What will the following query return?

SELECT p.Name AS ProductName, pb.Name AS BrandName FROM Products p INNER JOIN ProductBrands pb ON p.ProductBrandId = pb.Id WHERE p.Name LIKE '%Tennis%';

• All products with names containing "Tennis" and their brand names.

• All products with brand names containing "Tennis".

• All products with names starting with "Tennis".

• All products priced above 5000 with brand names containing "Tennis".

What does the LEFT JOIN clause do in this query?

SELECT

pb.Name AS BrandName,

COALESCE(SUM(p.Price), 0) AS TotalPrice

FROM ProductBrands pb

LEFT JOIN Products p ON pb.Id = p.ProductBrandId

GROUP BY pb.Name;

• Includes only brands that have associated products.

• Excludes brands that have no associated products.

• Includes all brands, even those without associated products.

• Includes only brands where the total price is not null.

What does the following query return?

SELECT

pt.Name AS ProductTypeName,

COALESCE(COUNT(p.Id), 0) AS TotalProducts

FROM ProductTypes pt

RIGHT JOIN Products p ON pt.Id = p.ProductTypeId

GROUP BY pt.Name;

• All product types and their total products, even if a type has no products.

• All product types and their total products, only if a type has at least one product.

• Only product types with no products.

• All product types and products, even if they do not match.

What is the purpose of the subquery in the following query?

SELECT

p.Name AS ProductName,

(

SELECT pb.Name

FROM ProductBrands pb

WHERE pb.Id = p.ProductBrandId

) AS BrandName

FROM Products p;

• Retrieve all product names.

• Retrieve the brand name for each product.

• Retrieve the total number of products for each brand.

• Retrieve the most expensive product for each brand.

What is the purpose of the HAVING clause in the following query?

SELECT

pt.Name AS ProductTypeName,

COUNT(p.Id) AS TotalProducts

FROM ProductTypes pt

INNER JOIN Products p ON pt.Id = p.ProductTypeId

GROUP BY pt.Name

HAVING COUNT(p.Id) > 10;

• Filter rows where TotalProducts is greater than 10.

• Filter rows where ProductTypeName is greater than 10 characters.

• Sort the results by TotalProducts.

• Filter rows where the ProductTypeId is greater than 10.

What is the purpose of the self-join in the following query?

SELECT

p1.Name AS ProductName,

p1.Price AS ProductPrice

FROM Products p1

LEFT JOIN Products p2

ON p1.ProductTypeId = p2.ProductTypeId

AND p1.Price < p2.Price

WHERE p2.Price IS NULL;

• Retrieve all products with the lowest price in their product type.

• Retrieve all products with the highest price in their product type.

• Retrieve all products with prices greater than the average price in their product type.

• Retrieve all products with no associated product type.