How to name a SQL query?

Question

My professor is asking for me to include a query name for each and every query that is involved with this homework. I'm assuming he's referring to an alias, so I've used this code successfully with some questions:

SELECT COUNT(CustomerID) AS Problem1
FROM Customers;

However, I can't us AS when creating tables or deleting columns like this (without an error).

CREATE TABLE TestDB AS Problem6
(
SuggestionID Char(5) NOT NULL PRIMARY KEY,
Suggestion VarChar(100) NOT NULL,
SugContact Char(30) NOT NULL,
SugPhone Char(10),
SugDate Date NOT NULL,
SugPriority INT
)

OR when using INSERT INTO. So my question is this: how do I name a specific query, specifically when creating a database like the above or when deleting a column.

Answer 1

It is not possible to name a query as such - you could put it into a stored procedure or alternatively - and more likely, he'll just want you to label the query with a comment like so:

-- Question 1
CREATE TABLE TestDB( ...

Answer 2

Use with, so for example:

WITH Problem_1 AS (
 SELECT COUNT(CustomerID) as Customers
 from Customer
 )

in your example the when you use as you are just changing the column name.

Answer 3

(SELECT * AS B FROM TBL1)A

A is name of the above query.

You can use A Like this:

SELECT PID 
FROM 
    (SELECT ProductID AS PID, ProductName AS PNAME 
     FROM Products 
     WHERE ProductID = 32) A

A real example:

SELECT 
    cID AS `Customer ID`,
    cNM AS `Customer Name`,
    MAX(SumPrice) AS `Total order `
FROM
    (SELECT 
         Customers.CustomerID AS cID, 
         Customers.CustomerName AS cNM,  
         SUM(Products.Price * OrderDetails.Quantity) AS SumPrice
     FROM
         (((Orders
     INNER JOIN 
         Customers ON Orders.CustomerID = Customers.CustomerID)
     INNER JOIN 
         OrderDetails ON OrderDetails.OrderID = orders.OrderID)
     INNER JOIN 
         Products ON OrderDetails.ProductID = Products.ProductID)
     GROUP BY 
         CustomerName) A

We used result of A query

Result