[Solved] Problems with Sql queries

[Bhujang]

I have just started Database Basics, so I dnt know much about this field. My teacher said that Sql Queries are Case Insensitive and he was using Ms-Access and Oracle, he showed us on both by writing some simple queries and I saw that queries were running even if he was changing cases in table name and column names, he also showed us that while creating a table was not differentiating between field names with different cases, for example if I have entered SUBJECT as a field name then the table was not accepting subject as another field name.

But on home I have Openoffice.org so I started the base and went through steps--
1- create table in design view
2-Entered some field names and strange thing was both SUBJECT and subject were accepted as field names in a single table.
3- Saved the table with name SAMPLE
4-Went to created Queries in SQL view and when I tried to run a simple query (select *
from sample) it showed error so I have to use SAMPLE as table name then it ran fine. this was not happening in Oracle in my teachers computer.

I also read on the link http://www.w3schools.com/sql/sql_syntax.asp that SQL is not case sensitive, so if someone can tell me in simple language why this is happening, I will be very thankfull to him.

[DACM]

My teacher said that Sql Queries are Case Insensitive...

I also read on the link http://www.w3schools.com/sql/sql_syntax.asp that SQL is not case sensitive...

Your teacher is over-simplifying the issue, or simply failing to mention that enclosing the column name in quotes during creation will allow a case-sensitive name -- per the SQL standards. A quick search reveals this is true of Oracle, PostgreSQL, MySQL, HSQLDB and I presume many others.

However, when we use Base as the front-end to our database, case-sensitivity is imposed by Base in many cases. Specifically, the visual Table Designer in Base adds quotes to all object names during table/column creation, thereby enabling case-sensitivity. And, the Base query parser enforces the use of double-quotes around names (Tables, Columns, etc.), thereby enforcing case-sensitivity even all names are UPPERCASE. You can bypass the query parser in Base but it's not always a good idea, since the parser facilitates writable queries and parameter queries.
see: viewtopic.php?p=199303#p199303
see also: Learning SQL using H2's all-in-one Driver+Engine+Console
...

[eremmel]

Welcome in the world after school! Databases can use different naming-schema. When using a database or any other application you have always check the rules of that application; the same applies to OOo vs. MsOffice: compatibility has also its limitations. Have fun with exploring OOo!

[Bhujang]

Thanks both of you, but why the link http://www.w3schools.com/sql/sql_syntax.asp says that SQL is not case sensitive? and what should I do so that I can use table name without worrying about cases(upper or lower)?

[RoryOF]

The link you cite says

We are using MS Access and SQL Server 2000 and we do not have to put a semicolon after each SQL statement, but some database programs force you to use it.

So their assertion of case insensitivity must be read with respect to those applications.

[Bhujang]

This link was posted by user DACM http://user.services.openoffice.org/en/ ... 03#p199303 which says that Once you have ALL-CAP names throughout your database, you can then refer to those Tables and Columns in SQL using whatever case-convention you prefer -- without quotes -- again because un-quoted names are converted to ALL-CAPS internally. So if the column name is FIRSTNAME, you can refer to it in a [native] query as FirstName, or FIRSTname, or firstname, or whatever camel-case-convention you like, without using quotes.

So I saved my table with all Capital Letters including Table name, but even then using table name in lowercase in query is giving error. why?
se;e

[DACM]

...but why the link http://www.w3schools.com/sql/sql_syntax.asp says that SQL is not case sensitive?

Well perhaps because without utilizing quotes, especially at the point of table/column creation, "SQL is not case sensitive." So if you never use quotes then case-sensitivity is a non-issue.

and what should I do so that I can use table name without worrying about cases(upper or lower)?

A database front-end is another matter. Base is simply a GUI front-end combined with a built-in, back-end, database engine (HSQL). Base actually enforces case-sensitivity in table/column names, for whatever reason, particularly through it's query parser. So if you create your tables/columns with the Base GUI using an ALL-CAP naming-convention, then you can by-pass the Base "parser" in most cases except when you require a writable result-set or parameter query. Simply select 'direct SQL' for Queries or 'Native' when embedding SQL commands (embedded queries for List Content), or utilize 'Tools > SQL...' which always bypasses the Base query parser.

So I saved my table with all Capital Letters including Table name, but even then using table name in lowercase in query is giving error. why?

Because you didn't understand that you can bypass the Base query parser (the operative word is 'native' query), which then passes the SQL directly to the database engine.

This issue bites us all from time-to-time: http://user.services.openoffice.org/en/ ... 92#p220192
...

[Bhujang]

Thanks DACM after some trouble I have understood.

[DACM]

Well several folks added to this discussion...and I wasn't aware of the details until recently, so welcome to the club.

[rudolfo]

Looking at the initial post I can't resist to mention that there is a small file based database SQLite. Its documentation site uses the title SQL As Understood By SQLite indicating that the SQL standard is only too often interpreted differently. If your teacher uses Oracle and the main emphasis is on the SQL syntax OOo Base is not the best tool for learning. SQLite comes with a console database terminal comparable to SQL*Plus from Oracle or the mysql command line client that comes with MySQL.
Although Base has the Tools -> SQL option to directly send Data Definition Language and Data Manipulation commands (update,delete, insert) it can't handle returned results. For queries you have to swap to a query window and run the query there.

If you are working in the SQLite command line tool you are not distracted by case sensitivity or quotes, but you can focus on the SQL logic. I stop my campaign here and mention one of the drawbacks with sqlite: The use of column types is different from other database engines, on their website they talk about "Manifest typing". Particularly if you are dealing with dates and differences between dates quite a lot, this might become a show stopper.

[DACM]

If your teacher uses Oracle and the main emphasis is on the SQL syntax [then] OOo Base is not the best tool for learning...
Although Base has the Tools -> SQL option to directly send Data Definition Language and Data Manipulation commands (update,delete, insert) it can't handle returned results. For queries you have to swap to a query window and run the query there.

I stop my campaign here...sqlite

Very good point. Base is probably inappropriate as an SQL learning tool -- although I'm a Query Builder cripple oftentimes.

Speaking of cute little front-ends for learning SQL (or general database administration):
The H2 Console is built-into the executable h2.jar. So if you have Java and a default web browser installed on virtually any platform, you can simply double-click the h2.jar file and get a nice, graphical, SQL console. All you need is the h2.jar file which you can carry around on a USB flash drive. I just tried it, and I had the Console popup in my browser, with a new database created, and a table with two records, all by clicking on the boilerplate SQL templates/examples -- all in a total of about 30 seconds. I've never seen anything like it -- although now I'm noticing that HSQLDB 2.x has a similar GUI database-manager built-into it's executable as well: hsqldb.jar Very nice! AFAIK, H2 and HSQLDB are the most ANSI-compliant SQL engines available which provides a good learning environment perhaps when combined with H2's rather impressive SQL Grammar documentation. And the H2 Console is cross-database so it can be used with many other databases using JDBC drivers as available.

Here's some other free, cross-platform, cross-database, SQL front-ends:
SQL Workbench/J
SQirreL SQL
SQL Developer
Execute Query
iSQl-Viewer
and the amazing, but not free RazorSQL
among others
...

[rudolfo]

Thanks DACM! The H2 Console looks promising, particularly because it can be configured to use it for other database backends, as well. It's basically really only a doubleclick on the jar file. It is still good to read the tutorial, because then you know that closing the browser doesn't shutdown the H2 database backend, but that this needs to be done on the configuration page with the shutdown button.
But as the backend process occupies only 10 MByte and the tcp port 8082 most user won't recognize it, if they keep it running until they shutdown their computer (or log out).