Question

SQL

- Write SELECT INTO statements to create two test tables named Customers and Orders that are complete copies of the Customers and Orders tables. BUT FIRST code two DROP TABLE statements to delete Customers and Orders.

- Write and execute the following Select statement: Select * into CustomersUSA from Customers where 1=2 Please note that the statement above will create a new table with NO records in it since 1=2 is a condition that will never be true. This is a technique used to simply create a table structure from an existing table with no data. Now create an Insert statement that will select all USA customers from your Customers table and append them to CustomersUSA.

- Write a Delete statement that will remove all orders from Orders table where the Customer zipcode is from 12209. Since the zipcode is in the Customers table use a subquery or a join within the Delete statement

- Write a DELETE statement that deletes all orders from Orders table that had an order date within the range of 10/10/2016 and 10/25/2016 and shipped via United Package.

Answer 1

To fulfill the SQL tasks, we first drop existing Customers and Orders tables, then create copies with no data using SELECT INTO statements. We populate a new table, CustomersUSA, with data from the existing Customers table and perform DELETE operations on the Orders table with conditions based on customer zip code and order date range.

Step-by-step explanation:

To address the SQL tasks mentioned, we'll start by writing and executing SELECT INTO statements to create two test tables named Customers and Orders that are copies of the existing tables. First, we must ensure that there are no tables with the same names by using DROP TABLE IF EXISTS statements. Please note that in a production environment, dropping tables should be done with caution after ensuring that backup data is available, as this operation is irreversible.

Once the tables are dropped, we'll use SELECT INTO statements to duplicate the table structures with no records included. Following that, we will populate the CustomersUSA table with data from the Customers table where the country is the USA. Finally, we'll write a series of DELETE statements, one using a subquery or a join to remove orders based on customer zip code, and another to delete orders within a specific date range and shipped by a specific carrier. Here are the necessary SQL commands:

DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Orders;

SELECT * INTO Customers FROM Customers WHERE 1=0;
SELECT * INTO Orders FROM Orders WHERE 1=0;

SELECT * INTO CustomersUSA FROM Customers WHERE 1=2;

INSERT INTO CustomersUSA
SELECT * FROM Customers WHERE Country = 'USA';

DELETE FROM Orders
WHERE CustomerID IN (
SELECT CustomerID FROM Customers WHERE Zipcode = 12209
);

DELETE FROM Orders
WHERE OrderDate BETWEEN '2016-10-10' AND '2016-10-25' AND ShipVia = 'United Package';

Note that 'United Package' is assumed to be a representation of the carrier in the Orders table. The actual value should be the ID or exact name as recorded in the Orders table that corresponds to the United Package carrier.