Question

8. Write and execute a query that will delete all countries that are not assigned to an office or a client. You must do this in a single query to receive credit for this question. Write the delete query below and then execute the following statement in SQL Server: Select * from Countries. Take a screenshot of your select query results and paste them below your delete query that you constructed.

Answer 1

The task involves writing a SQL DELETE query to remove countries not linked to an office or client, followed by a SELECT query to show remaining entries.

Step-by-step explanation:

The question asks for a SQL query to delete rows from a database table where certain conditions are met. Specifically, the task is to write and execute a query that will remove all countries from a table named Countries that do not have a corresponding entry in either an office or a client table. Assuming that we have foreign key relationships that would prevent deletion if an associated office or client exists, the SQL DELETE statement would look something like this:

DELETE FROM Countries WHERE CountryID NOT IN (SELECT CountryID FROM Offices) AND CountryID NOT IN (SELECT CountryID FROM Clients);

After executing the delete query, the following Select query is used to display the remaining countries:

SELECT * FROM Countries;

As per the instruction, a screenshot of the result of the select query should be taken and provided below the delete query to complete the task.

Answer 2

The Countries which are not assigned any Office means that the values are Null or Blank:

I created a table:

my sql> select*from Country; + | Country Name | Office | - + | Yes | NULL | Yes | Croatia | Argentina Sweden Brazil Sweden | Au

Here in this table there is Country Name and a Office Column where it is Yes, Null and Blank.

So, we need to delete the Blank and Null values as these means that there are no office assigned to those countries.

The SQL statement:

We will use the delete function,

delete from Country selects the Country table.

where Office is Null or Office = ' ' ,checks for values in Office column which are Null or Blank and deletes it.

Code:

mysql> delete from Country -> where Office is Null or Office = ''; Query OK, 3 rows affected (0.01 sec)

Code Image:

mysql> delete from Country -> where Office is Null or Office Query OK, 3 rows affected (0.01 sec) =

Output:

mysql> select*from Country; + | Country Name | Office | + | Croatia Sweden Sweden | India | Yes | Yes Yes | Yes + 4 rows in s

You can see that all the countries with Null and Blank values are deleted