Round #586

Use EXISTS to find customers with at least one cancelled order.

```csharp using System; using System.Data; using System.Data.SqlClient; class Program { static void Main() { // Create a connection to the database string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"; SqlConnection connection = new SqlConnection(connectionString); // Create a query string query = @" SELECT c.CustomerName FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID AND o.OrderStatus = 'Cancelled' ) "; // Create a command SqlCommand command = new SqlCommand(query, connection); // Open the connection connection.Open(); // Execute the query and read the results SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine(reader["CustomerName"]); } // Close the reader and connection reader.Close(); connection.Close(); } } ```

```csharp using System; using System.Data.SqlClient; class Program { static void Main() { // Connection string to the database string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"; // Query to find customers with at least one cancelled order string query = @" SELECT c.CustomerID, c.CustomerName FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID AND o.OrderStatus = 'Cancelled' ) "; try { // Create a new SqlConnection object using (SqlConnection connection = new SqlConnection(connectionString)) { // Open the connection connection.Open(); // Create a new SqlCommand object using (SqlCommand command = new SqlCommand(query, connection)) { // Execute the query and read the results using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Console.WriteLine($"Customer ID: {reader["CustomerID"]}, Customer Name: {reader["CustomerName"]}"); } } } } } catch (SqlException ex) { Console.WriteLine("Error: " + ex.Message); } } } ``` This C# code uses the `EXISTS` clause in a SQL query to find customers with at least one cancelled order. Here's how it works: 1. We establish a connection to the database using a `SqlConnection` object. 2. We define a SQL query that selects customers from the `Customers` table where there exists at least one order in the `Orders` table with a status of 'Cancelled' for that customer. 3. We execute the query using a `SqlCommand` object and read the results using a `SqlDataReader`. 4. We print the customer ID and name for each customer with at least one cancelled order. The `EXISTS` clause is used to check if a subquery returns any rows. In this case, the subquery checks if there are any orders with a status of 'Cancelled' for each customer. If the subquery returns any rows, the `EXISTS` clause returns `TRUE`, and the customer is included in the results. **Note:** Replace the placeholders in the connection string (`myServerAddress`, `myDataBase`, `myUsername`, `myPassword`) with your actual database credentials. Also, make sure to adjust the table and column names according to your database schema.