0

I have a Classes table which is as follows:

Classes(classid, dept_code, course#, sect#, year, semester, limit, class_size, room, TA_B#)

The qeustion is: Find the classid, dept_code and course# of each undergraduate class (i.e., course# < 500) that was offered in Spring 2017. For each such class, also list the number of seats available (computed by limit – class_size) under the header “seats_available”.

I tried this simple approach:

select classes.classid, classes.dept_code, classes.course#,
classes.limit-classes.class_size as'seats_available'
from classes
where limit>class_size and year='2017' and semester='Spring'and course# < 500;

0 But I am getting an error:

ERROR at line 1:
ORA-00923: FROM keyword not found where expected

What am I missing? This error will go if I remove this code of line:classes.limit-classes.class_size as'seats_available'

I am using Oracle database

Vagabond
  • 521
  • 1
  • 5
  • 12

1 Answers1

1

In the SQL standard identifiers need to be enclosed in double quotes ("). And the Oracle database complies with the standard there.

Single quotes (') are for character constants e.g. as you did in semester='Spring', they can not be used for identifiers like column names, table names or a column alias.

So you need to use:

classes.limit-classes.class_size as "seats_available"

You don't actually need the double quotes though, as your identifier does not contain any invalid characters, so 

classes.limit-classes.class_size as seats_available

will work just as well

Unrelated, but: numbers should not be enclosed in single quotes 2017 is a number constant, '2017' is a string constant. So you should use where year = 2017 instead

  • I was using single quotes. Then why am I getting an error? – Vagabond Oct 26 '18 at 07:05
  • @Vagabond: as I said: an identifier can not be enclosed in single quotes in SQL. You need to either use **double** quotes (`"`) or no quotes at all for an identifier –  Oct 26 '18 at 07:06
  • @a_horse_with_no_name . . . Unfortunately, lots of databases support single quotes for defining column aliases (even some databases owned by Oracle). I just added a small clarification to the answer. – Gordon Linoff Oct 26 '18 at 11:27
  • @GordonLinoff: the claim that "SQL" requires double quotes for identifiers is still true. This is what the SQL standard mandates. To my knowledge only MySQL and SQL Server violate that requirement. What are the other "lots of databases" that support this? –  Oct 26 '18 at 11:30