This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hello. Please tell me how to write the correct request. I created a database and placed 3 tables in them. Now I have to display the data. How can I display rows where there is an order for a product with ID=1 and where the person's age is greater than 30?Can we use a non-clustered index here?
When you write queries in the sense that you want to get data, indexes are of no interest. When you find that the query is slow, that's when you start think about indexes.
You query would be a simple join bteween the three tables, adding a WHERE clause for the two conditions. I leave it as an exercise to you to actually write the queries.
And for the future: Don't post code as images. We can't copy and paste from images. Please always post code as text.
SELECT o.CustomerId, c.FirstName, c.LastName, o.ProdactId,
p.StockQuantity, p.Price
FROM Orders AS o
JOIN Customers AS c
ON o.CustomerId=c.Id
JOIN Products AS p
ON o.ProdactId=p.Id
WHERE c.Age>20 AND o.ProdactId=1
That seems to be it!
and how to apply a non-clustered index for practice?
To practice with indexes, you would need a case where performance actually matters. The tables you have are a bit too far away from reality for an index exercise to be meaningful.
Here you have product and customer both in the Orders table, but in a real-world scenario, you would have the products in an OrderDetails table, since an order in very most cases can comprise more than one item. But, yes, you would typically index the customerID column, since there is often a need to find all orders for a customers. And in many cases, you also need to index the ProductID column.
There is another thing which is funny in your tables. You have defined the foreign keys with ON CASCADE DELETE. This is a powerful mechanism, but is something you need to apply with some thought. For instance, if you have an OrderDetails table it would have
OrderID int NOT NULL REFERENCES Order ON DELETE CASCADE
That is, if you delete the order, you want the detail rows to be deleted as well.
But here you have used ON DELETE CASCADE for the customer and product IDs. If someone tries to delete a customer that has an order, that is likely to be an error. It is not very likely that you want all orders for the customer to be deleted. Even more so, if someone tries to delete a product. So in a real business case, these foreign keys should have ON DELETE NO ACTION. (Which means that there should be an error.)
Finally, I note this:
Id SERIAL PRIMARY KEY
The keyword SERIAL, tells me that you are not using SQL Server which is the topic for this forum, but another product. For this basic question it does not matter, but the more specific you question is, the higher risk that you get an answer that does not run on your platform. While SQL is a standard, many products to don't implement the full standard, and at the same time vendors add their own extension. Therefore if you are working with, say, MySQL, you should ask in a forum for MySQL for the best response.