SQL beginner, need help with WHERE syntax error [closed]

Question

I am having a problem with a sql query that is supposed to find authors that have published at least one book priced $13 or more. IT is supposed to display first and last name, sorted by last name as long as the author has published at least one book priced $13 or more. Below is my query so far:

SELECT authors.au_fname, authors.au_lname, titles.price  
FROM authors, titles  ORDER BY au_lname ASC 
WHERE titles.price > "13";

The first picture shows the db's first table authors and the second shows titles, which holds the price column.

What am I doing wrong? I cannot figure out what is wrong with my syntax.

Thanks!

Answer 1

Strings are denoted with Single Quotes.

WHERE titles.price > '13'

If your price column is an int/decimal/float (or any numeric) datatype, you don't have to use a string.

WHERE titles.price > 13

Probably better to use the second WHERE, as it is comparing the NUMERIC values, rather than the string values. Even if the price column IS a string, it will CONVERT the value to a number in order to compare it against the 13. It doesn't perform as well, but it is more accurate vs. comparing strings.

Also, your WHERE goes before your ORDER BY.

SELECT
FROM
JOIN
WHERE 
GROUP BY
HAVING
ORDER BY

Edit: the code below explains the difference between comparing numbers implicitly and comparing numbers as strings. The commented WHERE clause yields different results due to the fact that it CONVERTS the price (StringNumber) column to a number before it compares it.

WITH CTE_NumberStrings
AS
(SELECT '1' AS StringNumber
 UNION SELECT '2'
 UNION SELECT '3'
 UNION SELECT '12'
 UNION SELECT '23'
 )
 SELECT StringNumber 
 FROM CTE_NumberStrings
 WHERE StringNumber > '13'
 --WHERE StringNumber > 13

Answer 2

You are doing a cartesian join between the authors and titles tables (i.e. joining every row in authors to every row in titles), which is almost never what you want to do.

You need to specify how to "link" the rows from each table with each other, and in this case it looks like your tables have a many-to-many relationship with the join table titleauthor, so the query should look something like

SELECT   a.au_fname
        ,a.au_lname
        ,t.price  
FROM     authors a
JOIN     titleauthor ta ON a.au_id = ta.au_id
JOIN     titles t ON t.title_id = ta.title_id
WHERE    t.price > 13
ORDER BY a.au_lname
;

Personally I don't write INNER JOIN for the same reason as I don't write LEFT OUTER JOIN or use ASC in an ORDER BY - it's a longer way of writing the same thing. I do appreciate that this is just a matter of style preference though.

Answer 3

According to the official documentation, the syntax for a SELECT statement is similar to this...

SELECT select_list [ INTO new_table ]

[ FROM table_source ] [ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

So the ORDER BY comes after the WHERE clause.

Try this:

SELECT authors.au_fname, authors.au_lname, titles.price  
FROM authors, titles
WHERE titles.price > '13'
ORDER BY au_lname;

Personally... I would re-write it like this

SELECT a.au_fname,
       a.au_lname, 
       t.price  
FROM authors AS a, titles AS t
WHERE t.price > '13'
ORDER BY 2

Get used to using SQL aliases. To me this is cleaner to read and makes more sense. But without knowing your table structures in the database, I cannot show you the INNER or LEFT JOIN version.