How to start using SQL?

[AlvaroF1]

I'm currently using an Access database and I'd like to start using SQL.

What do I need? I mean, what programs do I have to install? What code changes do I have to do?


Thanx.

[alextyx]

Dear AlvaroF1, I use database .mdb, the default one used by ACCESS (ACCESS can manipulate also SQL SERVER database) and I use ADO to read and write data. I refused to use ADO.NET because it does not permit to pessimisticly lock the record. So if you use ADO.NET, perhaps I'm not the right person to lead you. If I well understand you want to use an SQL SERVER (or MSDE) database. I have used it. A program of mine, is able to use the two kinds of database, but I prefer to use the MDB.
Consider that you can obtain a backup of an MDB database, only with a copy file of the MDB. An SQL server database is composed by more files and you have to use administration tools, or transact SQL commands to have the same result. If we assume that administration and manteinance of database is not a problem, you will have a difference, for example, in the way of locking. Two users can point the same record with a pessimistic locking, if they use Jet (for MDB database), because the record will be locked only when an user will write something in that record (also rewriting the same value present in a field). With SQL, if someone point a record, it will be locked. More, if you create a new record with JET, you will be able to read and write in it, also before UPDATE it for the first time. To write in a record created in a SQL DB, you have to update it,leave it and then repositioning. Obviously, in SQL you have real Transactions and more stability, but you pay this with complications that you have not to face in MDB DB. Consider that SQL server it's not cheap. If you chose MSDE, you will have not visual administration tools and if more than 5 users try to acces the DB, it's performance will be very bad. ACCESS and MSDE have the same limit 2 GBytes of DB.
I hope my post will be useful to you in order to make your choice!
Good job

[Triple A]

Can you recommend a good book about SQL? I want to use SQL to select entries made between two dates and to add values an such but I dont know what commands to use. I also would like to learn more about SQL syntaxis.

[alextyx]

It's possible I confused SQL with SQLSever DB. I spoke a lot about a DB managed by SQLServer, but if you only want to know something about the Structured Query Language, I was completely out!
Anyway I know(and I bought) one or two good books about SQL and Database, but they are italian or italian version, anyway, so I can't give affordable information, because they could not exist in other countries. I'm sorry!

[mendhak]

You're going to use Access with your .NET application, which is why I assume you have posted here.

You will be using SQL statements in your application if you wish to query the database or directly update, add or delete to/from it.

You don't need much for it. Access, the .NET IDE, and MDAC


Here are some SQL tutorials:

http://www.sqlcourse.com/
http://www.w3schools.com/sql/

[Triple A]

Thanks mendhak, those were very useful! I think I will be using them frequently.

But unfortunately they dont have the answer to my question. I want to seleft entries with dates between 2-2-2004 and 21-2-2004 and the I used the following queries:

SELECT * FROM table WHERE date BETWEEN '2-2-2004' AND '21-2-2004'

and

SELECT * FROM table WHERE date BETWEEN 2-2-2004 AND 21-2-2004

The first gives an error and the second returns no records at all. A think that maybe the date format is incorrect. Is there a way to correct that?


THX

[shadowfyre]

Assuming the datatype of "date" in your DB is DateTime, then you need to enclose the date with "#" as in

SELECT * FROM table WHERE date BETWEEN #2-2-2004# AND #21-2-2004#

[Triple A]

Thanks. I'll test it tonight