Help. I need to study SQL and my Access 97 died and won’t reload. There are free programs such as “SQL Server 2005 Express Edition” that I can download. Is this what I need? How is this different than Access or other SQL based programs such as Oracle? I’ve got a nice book to teach SQL Server 2000 so I would like to use what’s available without investing a lot of money.
I’ve used Enterprise Manager and similar programs in the past but I basically used the wizards to grind out something that could be downloaded to Excel. I don’t have access to those programs anymore to practice with and I need to understand SQL on a much higher level.
Every relational database product implements a slightly different flavor of SQL, but the basic concepts are the same. If you need to learn how to write complicated declarative queries with tons of joins and manipulation functions, it won’t matter what you use because you can always look up the specific functions and syntax in the manual.
If you need to learn how to write stored procedures and triggers and such, then it gets more specific. Oracle PL/SQL is fairly different from Microsoft/Sybase T-SQL.
Those free SQL engines (express 2005 or 2008) will get you an engine, and you will need a query tool to write queries with. You will need to study up on the syntax of managing DBs as well, unless you can score an evaluation or developer edition of Management Studio (Enterprise Manager is for versions SQL 2000 or earlier, you cannot manage SQL 2005 or 2008 DBs with Enterprise Manager). Management Studio (officially SQL Server Management Studio, or SSMS) will also let you write queries. It’s a bloated hog of a tool, though, so I always keep a separate query tool on hand for when I jsut want to fire up and run a query instead of do all the management stuff (backup, restore, user management, etc).
I can give you some book recommendations for Microsoft’s relational engine, SQL Server, and its language, Transact-SQL, if you like.
Or you can download SQL Server Express 2008 with Tools. The tools include the Management Studio which will allow you to write and execute queries, stored procedures etc.
If data integrity is important, I wouldn’t use MySQL unless InnoDB were the only available engine installed. MyISAM is not ACID-compliant by default; Postgres is.
You can download xamppwhich allows you to run MySQL server on your hard disk and even comes with a PHP installation which you can use PhpMyAdmin with. The lite version is probably all that you need and there is no installation required.
IIRC, you can switch between InnoEngine and ISAM when creating databases/tables in MySQL?
Quite true, and I should note that I’m not really recommending against MySQL or even MyISAM per se—I use MySQL quite a bit for numerous things, actually, though I do prefer to specify InnoDB when I do use it, mainly so I can actually use foreign keys. But MyISAM, with all its flaws, is the default, which would make me a bit wary if I discovered that, say, my bank was using MySQL to store my account information. But MySQL is generally an above average RDBMS in most cases, and I do like MyISAM’s fulltext searching.
Thank you for your responses. I will have more questions when I can figure out what to ask. I downloaded Microsoft SQL Server 2005 (the engine?), Microsoft Visual Studio 2005 (The query tool?) and some sample queries. Given the amount of information transfered to my hard drive I may have downloaded part of the Library of Congress.
My first question, do I have enough to start writing basic scripts and will the book I have on SQL Server 2000 work or is 2005/2008 radically different in language? I’m not worried about bells and whistles, I just want to be able to build up a cheat sheet of useful tools as a start so SQL makes sense.
For example, if I were to say to that your first goal when learning SQL (regardless of which software you are using) should be to CREATE a table, and then try and SELECT some data from it, would that make any sense to you?
Do you understand what a query does?
If yes, then your goal for now (regardless which software you are using), should be to get to the point that you can start executing your own queries, written by you.
If not, then a lot of the responses you’ve gotten so far must seem mighty cryptic.
You’re better off using Management Studio Express for writing queries (see ZipperJJ’s post above for a link). It is more suitable than Visual Studio and is in fact the equivalent of Enterprise Manager in older versions of SQL Server.
Visual Studio is a huge piece of software designed for general programming. One of the many things it can do is connect to an SQL Server database and allow you to write queries.
But Management Studio is better suited for you since it is a simpler tool which is designed solely for manipulating SQL Server databases.
Yeah if you’re just going to play with queries, don’t even bother installing Visual Studio. You’re adding an extra layer of complexity to your learning. If you want to hook up queries to a desktop or web app later on, then get into VS.
KellyCritereon already asked about your skill level…but with regards to the book you have, the language is still the same between 2000/2005/2008 but once you get past the surface there are differences between the three. You can, however, find out most of what you want to know from Google. There are new/more features in 2005 over 2000, and even more with 2008. There’s also a feature difference between full-blown SQL Server and Express.
So, is your goal to be a developer, or to be a DBA? Because the route to take for each discipline has a lot of crossover, but the philosophy is readically different. It’s like the crew chief vs the race car driver…you both have to know some stuff about cars, but you have wildly different jobs focused on the same tool.
I’ve used Access on and off for years and understand the very basics but, unlike Excel, I find it difficult to pick it back up. Excel is sooooo easy to use because most of it is staring you in the face. If I don’t understand someone else’s formula I can reverse engineer it.
So to answer your question, yes, I have a rudimentary understanding of Access (tables queries, forms etc…) but I’ve had problems dealing with Oracle, MySQL, MSSQL, and all the different engines/query tools involved. I expect them to be similar to Excel or Access in that one program does everything. I get overwhelmed by all the different screens and terminologies I’ve had to deal with using different platforms.
Because of this I’ve never mastered the basic query writing skills needed to be functional with any of the software. All of my job functions in the past have revolved around analyzing data for business decisions and most of that is done in Excel. I use queries to grind large chunks of information down to chunks I can analyze. I’ve always had programmers available to assist me but that isn’t going to cut it for future job opportunities.
My goal is as follows:
Learn DB structure/methodology (truly understand why tables and connections are set up a certain way)
Learn to formulate queries to mine data
Understand Administrative structure
Advanced tools for analysis
I may not have worded that precisely but I figure the first 2 go hand in hand. I feel I need to understand this before I can move forward. I’m not trying to be a programmer per se but I would like to hit a critical mass of knowledge so when a challenge arises I have the basic skills I have now with Excel to research a solution. I would like to be able to reverse engineer other people’s work when I come across something that does what I want it to.
So, should I abandon everything I’ve downloaded and use Management Studio Express? Is this a stand alone program or is it using the SQL server 2005 engine I downloaded?
Again, thanks to all who have posted. I have not responded to each post but I’m reading/re-reading them all and I appreciate links to software.
If by DBA you mean Data Base Analyst then that is my goal. Past experience has shown that I truly need to understand the structure of stuff, even if it is already built. That way I can mold it into a useable. I use to get really large data files from customers that, if my memory is correct, were too large for Access or Oracle. That’s why I was forced into using different platforms. It made things very confusing for me.
DBA in this context usually means Database Administrator - the person who is in charge of the database server as well as the databases it contains. DBAs normally deal with space allocation, logical and physical database design, tuning and performance issues and other systems-level activity, rather than business-oriented analysis of databases.
Yes, a Database Administrator has a real understanding of what “relational” means in a relational database, what first, second, and third normal form are, and how the particular DBMS they are working with uses the various resources available to it.
Some GREAT sites to peruse, once you get a bit of understanding, are sqlteam.com and sqlservercentral.com. You can also try sqlpass.org, and check to see if there is a local PASS (professional association for sql server) chapter in your area, and if they hold meetings. The meetings are often educational.
In my experience, people who have a DBA background, or who take the time to really learn what is happening behind the scenes, make for better SQL programmers down the road as well.
ETA: The sites I have linked to focus on Microsoft SQL Server, not Oracle. Oracle is a little harder to get established in than SQL Server, but is also holds a bit more cachet as a high-volume DBMS. Despite advancement in leaps and bounds from Microsoft, Oracle really is a more robust enterprise class DBMS. That being said, MSSQL can do about 95% of the things Oracle can do.
They were huge files, complete data dumps for a year of product movement. We had problems just getting the file through the email system. It wasn’t a function of tables it was a function of lines within a single table. It was millions of lines.
I certainly don’t need to handle large files to practice with. So is Management Studio Express a stand alone program, does it require an “engine” and if so, will MS SQL server 2005 work?