Ansi sql, access sql

[jovana]

Hi, I am a beginner so please forgive my lack of knowledge on the subject.

I have created a database for an assignment with various queries. I understand that there are different ways a query can be created, via the QBE gird (which is the easiest way), by ANSI SQL and by Access SQL.

I am trying to find a website that shows a full list of commands for ANSI SQL but haven't been any that specifically mention ANSI SQL.

Is ANSI SQL sometimes just called SQL? I have found a lot of websites showing the commands for SQL but not specifically ANSI SQL.

I think my tutor would prefer we create queries etc using ANSI SQL rather than the QBE grid.

 

[CJ_London]

personally I wouldn't bother with ANSI Sql - it is a hybrid which does not translate to other systems on a like for like basis - i.e. you don't bring in the SQL functionality of other dbs.

The main practical difference in my opinion is the wildcards - e.g. use % rather than * in a like statement and ANSI does not support crosstabs

This link should explain the differences and also compares with SQL Server

https://msdn.microsoft.com/en-us/library/bb208890(v=office.12).aspx

 

[The_Doc_Man]

You didn't say which version of Access you are using, but you can use Access 2010 to create queries that are ANSI 92 compatible. In the File >> Options tab from the main ribbon, you can select "Object Designers" then scroll down to "Query Design."

Now find where it checks SQL Server Compatible Syntax (ANSI 92) and check the box for "This Database."

Now use the QBE grid and create your query. (No, I'm not yet circumventing your instructor... stay with me here.) Now switch from Query Design view to SQL view and see what it built. Try that for a few queries and you will start to get the hang of using ANSI SQL. You can also have a second database where that SQL option is NOT checked. Then you can use the same grid and flip to SQL view to see what, if anything, is different between the two SQL views. The odds are pretty high that most of the time, you will NOT see major differences. Not all the time - but most of the time.

 

[jovana]

Thanks for the replies.

Once a query has been created is there anyway of someone knowing whether it was created using the QBE grid or SQL?

 

[CJ_London]

often yes - some queries can only be written in sql - e.g. union queries and qbe generated sql often contains more square and round brackets than is necessary