SQL proofreading help?

I'm trying to teach myself SQL and have been presented with writing a query as part of a job application. Does what I've written below look correct? I'm wanting to pull results from the Products table where the CategoryName in the Categories table = 'Condiments'.

SELECT Products.ProductName, Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock, Products.UnitsOnOrder FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE Categories.CategoryName = 'Condiments'

I'm uncertain on the WHERE statement and whether I need to specify the Categories table? Of course, I may have screwed up the rest of it too.

Yeah, Pete, that should work. I, however, would code it:

SELECT P.ProductName,
P.QuantityPerUnit,
P.UnitPrice,
P.UnitsInStock,
P.UnitsOnOrder
FROM Products P
INNER JOIN Categories C ON P.CategoryID = C.CategoryID
WHERE C.CategoryName = 'Condiments'

But, I only do this every day fer Lotus parts, food and stuff like that.

I had an on the phone interview once with Microsoft. The guy asked me "How many tables do you need to do an inner join?" I replied "Well, one if you try hard enough and depending on how your data is structured." He said "Umm... O-Kaaay..."

Thanks Dr. Hess! :D

Can you explain what the "P." before the column name means? I've seen examples formatted like that, but unfortunately never an explanation why.

The "P" is a shorthand substitution for "Products". It comes from here:

  FROM Products P

Likewise, the "C" represents the "Categories" table.

  INNER JOIN Categories C

It lets you specify table.column without having the table name spread all over the place and makes it easier to read.

Yeah, it's an Alias. I kinda shorthand it to:

inner join categories c on ...

full "long hand" would be:

inner join categories as C on ...

You also don't have to specify the table with each column name, but if there is more than one column with the same name in all the tables you're joining together, it will give you an error. For example, we know there are 2 tables with a column named 'categoryID', because we're joining on those columns. If we wanted to include CategoryID in the output, we would have to specify which table it came from and we would be forced to use either C.CategoryID or P.CategoryID in the above example.

SELECT P.ProductName,
P.QuantityPerUnit,
UnitPrice,
P.CategorID,
...

It wouldn't matter which table we chose from in this example, because we are inner joining them, so we know they will both be the same. Note I left the table name off of 'UnitPrice'. Now I'm assuming that only the Pruducts table has a UnitPrice field.

I code almost all my SQL by hand (typing it in.) I find it easier to work with than using something that generates it based on drawing links, etc. Plus those drawing things will screw it up if it is really complex. If I need to code out a select statement with like 50 column names and I don't want to type them all in, I have some code I wrote that will write it for me by reading the system tables.

I thought the AS statement was needed when creating an alias? It's as simple as just listing the alias after the table name?

Oh, and you guys are awesome! Thanks!!!

Yeah, "AS" is optional, even in the Select clause:

SELECT P.ProductName as [This is the Product Name of Our Stuff],
P.QuantityPerUnit QTY,
P.UnitPrice as Price,
P.UnitsInStock InStock,
...

gives an output like:
This is the Product Name of Our Stuff QTY Price InStock
Product1 17 14.58 92

Square brackets let you use spaces and other not allowed characters in a field name, and even though the editor here chops it all up, "This is the Product Name of Our Stuff" comes out as the field name / column heading in the output.

Also note that I work with MS SQL Server 2K and 2K5. Other "flavors" of SQL such as MYSQL may have more rigorous requirements.

Dr. Hess wrote: Also note that I work with MS SQL Server 2K and 2K5. Other "flavors" of SQL such as MYSQL may have more rigorous requirements.

Most database vendors support ANSI 92 so if you try to keep from using individual vendor bells and whistles you can get a bit closer to "portable" code.

That statement you wrote for him should run on just about any platform available. The square brackets are not required... you can just do select a.foo as myfoo from fooey or single quote it if you want to preserve case. In fact, you don't even need the 'as' keyword on SQL Server, Oracle, DB2 (except UDB on mainframes), or Sybase.

Ok, here's another one for you guys, if I can trouble you:

SELECT SUM(UnitPriceQuantity) as “Price”, SUM(UnitPriceQuantity*Discount) as “Discounted Price” FROM OrderDetails

Can I use SUM with other operators as above?

I'm also unclear whether I need quotes around an alias in order for the column to be displayed with it in the results?

Thanks again!

No quotes around an alias. Square brackets if you are using restricted words or spaces, like [this is a field name with spaces].

select CustomerName, sum(unitpricequantity) price, sum(unitpricequantity*discount) as [discounted price]
from order details
where OrderDateID between 20120101 and 20120630
group by CustomerName
order by CustomerName

What if the results aren't actually a field in any of the tables? All I want is two totals, which happen to be the result of unitprice x quantity and unitprice x quantity x discount, for all orders in the database.

Next question, what's the best way to query data from 4-different tables, and return any null values within all tables? The format is something like this:

Table1.PK = Table2. FK

Table3.PK = Table1.FK

Table4.PK = Table2.FK(do not need any data from table 4)

Table5.PK = Table4.FK

Is this a situation where I would use UNION?

The results don't have to be a field. Just based on a field.

select sum(unitprice * quantity) as Gross_Sales, sum(unitprice x quantity x discount) as Net_Sales
from OrderDetails

gives you something like:
1581234 1421233

I'm not exactly sure what you want with the next question. What are you looking for as a NULL? I think you need an outer join and in the where clause "where something is null". Without the actual table structure, I'm having trouble here.

petegossett wrote: Next question, what's the best way to query data from 4-different tables, and return any null values within all tables? The format is something like this: Table1.PK = Table2. FK Table3.PK = Table1.FK Table4.PK = Table2.FK(do not need any data from table 4) Table5.PK = Table4.FK

I think the way I would do this one is something along the lines of:

SELECT
(whatever fields are in the tables)
FROM
Table1 1
LEFT JOIN
Table2 2
on 1.PK = 2.FK
LEFT JOIN
Table3 3
on 1.FK = 3.PK
LEFT JOIN
(SELECT [fields], 5.PK, 4.FK
FROM
Table4 4
INNER JOIN
Table5 5
ON 4.FK = 5.PK
) 4
ON 2.FK = 4.PK
WHERE
1.[field] IS NULL
OR 2.[field] IS NULL
OR 3.[field] IS NULL
OR 4.[field] IS NULL

In reply to Dr. Hess:

I want to be sure no rows are excluded, even if there is no entry in one of the tables. Does that make sense? From what I've read, I need to do a full-join to get all results.

In reply to szeis4cookie:

Unfortunately, not all tables share fields, which is what I was trying to abbreviate with the PK(primary key) and FK(foreign key) above.

Well, I'm going to post the entire Q&A here. This is all based on the Northwind sample DB. The interviewer did state on the phone and in his email that I should search on the internet for any answers, so I don't feel bad about posting it here for review.

Technical Assessment Q&A

Please answer all questions below to the best of your ability. Use any online resources you can find to assist you with the answers. Put your answers in blue text within the body of the email message and reply to this email once complete.

Question 1: What is the result of running the following queries?

Delete MyDataTable

A: The DELETE command as stated above would remove MyDataTable entirely, as well as all data contained within it.

Select MyDataTable.z From MyDataTable inner join MyOtherDataTable on MyDataTable.z = MyOtherDataTable.z Where MyDataTable.z = 'AValueThatExistsOnceInBothTables';

A: The result would display all rows where the value 'AValueThatExistsOnceInBothTables' is found in both the MyDataTable and MyOtherDataTable tables.

Question 2: what is the difference between a clustered and a non clustered index?

A: A clustered index is the default column which a table is sorted by, and generally the same as the primary key. Since a table may only have one clustered index, if queries are often performed using other columns within the table, those queries may be slow to return results. A non-clustered index may be created to add a second(third, etc.) index for other columns, which will expedite results when searching within those columns.

Question 3: What Is the difference between an Inner Join and an Outer Join?

A: An inner join produces the intersect of both tables, displaying only the records they share in common. Outer joins do not require common records, they will display all records from both tables, returning null when no corresponding data from the other table exists, depending on whether the outer join is left-, right- or full.

Question 4: In the attached database schema, write a query that returns the all products of the category “Condiments”. Show product name, Quantity per unit, unit price, units in stock, units on order.

SELECT Products.ProductName, Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock, Products.UnitsOnOrder FROM Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
WHERE Categories.CategoryName = 'Condiments'

Question 5: In the attached database schema, write a query that returns the total amount of all orders placed in the system, the total amount discounted of all orders placed in the system.

SELECT SUM(UnitPriceQuantity) as Price, SUM(UnitPriceQuantity*Discount) as [Discounted Price] FROM OrderDetails

Question 6: In the attached database schema, write a query that returns a list of all orders (order ID, customer company name, customer contact name, order date, shipped date, unit price, quantity, total price, supplier company name) In many records the supplier does not exist so make sure your query returns all orders and NULL values for any that does not have a supplier record.

SELECT Orders.OrderID,
Customers.CompanyName, Customers.ContactName, Orders.OrderDate, Orders.ShippedDate, OrderDetails.UnitPrice, OrderDetails.Quantity,
SUM(OrderDetails.UnitPrice*OrderDetails.Quantity) Suppliers.CompanyName
FROM Orders
FULL JOIN ON Orders.OrderID = OrderDetails.OrderID
FROM Customers
FULL JOIN ON Customers.CustomerID = Orders.CustomerID FROM Products
FULL JOIN
ON Products.ProductID = OrderDetails.ProductID FROM Suppliers
FULL JOIN
ON Suppliers.SupplierID = Products.SupplierID

Question 7: All Shippers in our system are potential Customers. Create a TRIGGER on the table SHIPPERS that when a SHIPPER CompanyName is added, Create a record in the CUSTOMERS table automatically.

CREATE TRIGGER trig_NewCustomer
ON Shippers
FOR INSERT
AS
BEGIN
INSERT INTO Customers
SET Customers.CompanyName = inserted.CompanyName
END

Question 8: In SSIS, what is the difference between what is the difference between Control Flow and Data Flow? The difference between Synchronous and Asynchronous Data Flow Transformations?

A: Control Flow in SSIS processes tasks in either series or parallel, but does not move data. Data Flow consists of sources, transformations and destinations, and deals with the movement and manipulations of data as it is passed between components. Synchronous Data Flow Transformations process each row individually, while passing all rows from the input to the output buffer without change. Asynchronous Data Flow Transformations is required when there is a difference between the number of input and output rows, such as in a Merge or Aggregate transformation.

Question 9: In SSIS, what shape would you use to concatenate two input fields into a single output field?

A: The Derived Column Transformation allows for data from two separate columns to be concatenated into a single derived column.

Question 10: In SSRS describe a matrix report, and why you would use that format.

A: A matrix report is similar to a pivot table or crosstab report, in that it includes summaries for grouped data. A matrix report would be used when more detail is required than could easily be displayed with a table or list report.

Thanks again everyone!!!!

I don't think 1 is correct. I think delete mydatatable will just delete the records in the table. I'm not sure what leaving FROM out will do. DROP TABLE mydatatable will remove the table. Typically, I use DELETE FROM mydatatable WHERE x=1... I'd have to look at the reference "books." And a much better way to delete all records in a table is TRUNCATE TABLE mydatatable. Especially for large tables.

The second part of 1 is going to give you:
z

AValueThatExistsOnceInBothTables

as many times as 'AValueThatExistsOnceInBothTables' exists in both tables. No other fields.

On 5, wouldn't the total amount discounted be the discount and not the total discounted amount? $5 total, $4 after discount, $1 discount?

You syntax is all hosed in 6.

7: I never mess with triggers. We do everything explicitly. However, that insert statement looks wonky.

SSIS packages are THE DEVIL!!! KILL THEM WITH FIRE!!

I will update 1.

On 5, I thought his question may be a typo based on the wording, and should read "total amount discounted off all orders".

I agree question 6 is probably wrong, unfourtunately I have not fount any instructions or examples that involve more than 3-tables.

Yes, every example for triggers I found had different syntax. I'm pretty sure I need a DML trigger, since we're dealing with inserting a new row into an existing table, but I'm unclear whether I need to include variables to allow the trigger to add the row into the second table. Again, each comparable example I found was formatted slightly different than the previous one.

Good to know about SSIS, they apparently use it frequently.

SSIS packages were to be the replacement for DTS packages. Disaster. However, microsoft pushes them hugely as the super-tool, yada-yada. Just try debugging one sometime. Ugh.

Anyway, number 6: Where's the schema?

Question 6: In the attached database schema, write a query that returns a list of all orders (order ID, customer company name, customer contact name, order date, shipped date, unit price, quantity, total price, supplier company name) In many records the supplier does not exist so make sure your query returns all orders and NULL values for any that does not have a supplier record.

Note it just says NULL for Suppliers, not everything.

select order ID, customer company name, customer contact name, order date, shipped date, unit price, quantity, total price, supplier company name
from orders inner join orderdetails ON Orders.OrderID = OrderDetails.OrderID
inner join Customers ON Customers.CustomerID = Orders.CustomerID
inner join Products ON Products.ProductID = OrderDetails.ProductID
left outer join Suppliers ON Products.SupplierID = Suppliers.SupplierID

Use the right field names in the Select part and that should do it.

A big "Thanks!!!" to Dr. Hess, szeis4cookie $ GPS - they emailed me for an in-person interview next week! :D

No problem, pete. Gotta get money for Lotus parts, right? SQL Buys them for me.

Some notes: Yup, delete tablename just deletes the records from tablename, not the table, as I thought.

I THINK your trigger question should be:

CREATE TRIGGER trig_NewCustomer ON Shippers FOR INSERT AS

INSERT INTO Customers (customer_name, customer_address) select inserted.shipper_name, inserted.shipper_address from inserted

That would be about the basics of it, again, without seeing the actual table structures.

Break a Leg!