Could anyone help me with these two SQL queries on my Items table?

Question

I made a SQL project for myself to learn.

I am trying to answer these two questions:

1. How many total items were sold between 1970 and 2000?
2. What state in the US had the most items sold overall?

I am using the databricks community edition. I am trying to teach myself how to use a big data tool. I uploaded a csv file from a dataset website. I was able to generate the table just fine. I even made a couple of SQL queries that I was able to write on my own and got results.

I am having issues with these last two queries. Below is the table information, and my query for the 1st question.

Table name: Items

Columns:

datetime (Date format is 10/10/1972 19:00)
City
state
country
item

My effort for the first question (How many total items were sold between 1970 and 2000?) that isn't working and gets errors:

SELECT COUNT(*)
FROM [Items]
WHERE Date BETWEEN '01-01-1970' AND '12-31-2000`

Error in SQL statement: ParseException: mismatched input '[' expecting {, ';'}(line 2, pos 5) == SQL == SELECT COUNT(*) FROM [items] -----^^^ WHERE Date BETWEEN '01-01-1970' AND '12-31-2000`

What am I doing wrong? Also, I am not sure how to even begin the second question SQL query.

Answer 1

Your where statement needs to look for the actual column name, and if that column name is a reserved word, like 'Date' it needs to be dotted like [Items].[Date]