Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

I have a project that could benefit from using a database, but I have no experience with databases, don't have access to a server, and have relatively little experience working with things living server-side.

If I'm going to have to tackle a learning curve, I'd prefer to learn something with broad applicability (such as SQL) but would settle for learning something like Access if it is sufficiently powerful for the task I'm currently trying to tackle. Of course, I'd also rather not drop $150 on Access if it can be helped since I'm just tinkering.

I've downloaded LibreOffice Base as well as something called SQLiteBrowser, but I wanted to check first before I invest time learning those particular applications and their flavors of SQL whether those tools will be sufficient for what I want to do.

I want to be able to:

  • import data from a CSV or from Excel
  • run queries that equate to "select x where this is that and this contains that and any of these contain that"
  • write(?) a new field which indicates those results which match a given query

Again, I'm willing to learn, but it would be nice not to have to learn a bunch of intermediate stuff about IT before I can focus on learning databases and, if necessary, the particulars of a given application.

share|improve this question
Should I let the mods do it, or should I delete and repost at dba? – Charles W May 4 '12 at 0:24
You want to just build a database and use some queries or you want to use it for some application development like for Android app or desktop / web app. – vaichidrewar May 4 '12 at 3:24
I want to build a database to run queries. The tool my colleagues are using is MS Excel, but I think there's much more sophisticated analysis we could do with a better tool. – Charles W May 4 '12 at 3:45

migrated from superuser.com May 4 '12 at 1:18

5 Answers

up vote 3 down vote accepted

First you will have to use query language. All the flavors of SQL use the almost same SQL query syntax. So you will first have to learn SQL query language. For eg. you can start with tutorial at www.w3schools.com/sql/default.asp

Stanford University had offered public course on databases last Fall. They had nice tutorial on how to set up SQLite.

Once you start learning SQL you will need some tool using which you can build a database and run queries. SQLite is free software library which can be used to create databases and run queries. It can used as an independent tool i.e server setup is not required for using it. It also supports importing csv files.

Once you are familiar with SQL, you can use MySQL if you want to design some web-application or access to build desktop applications. I do not think Microsoft Access is at all necessary (as it is not free) if you just want to get started with.

share|improve this answer
SQLite by itself is command line only, correct? That's not necessarily a problem, I'm just looking for clarification. I mentioned DatabaseBrowser - it comes with SQLite already baked in, but it also adds a GUI application on top of it. Are you familiar with this kind of program? Is there a name for that sort of intermediate interface software in the DB world? – Charles W May 4 '12 at 12:38
up vote 6 down vote

Charles,

You mentioned MS Excel in your comment so it's pretty much safe to assume you're in a Microsoft environment. You definitely have much power if you know how to mess with a database management system.

If you're doing some serious data analysis, I'd say go for enterprise databases like Oracle, SQL Server, MySQL, DB2, etc., which are Relational Databases. It's good to note that there are also non-relational databases out there that are gaining traction in the database market.

Since you're probably on a Microsoft environment, I suggest you go for SQL Server. You can ask your IT department if you have an enterprise database already in place. If none, you can download the "express" version of SQL Server (see link on the other comment). While you're there read about the limitation of an express edition. The express edition is FREE.

The express edition is a fully functioning, production-ready version of the SQL Server although it's limited in some ways (storage capacity, memory usage, etc). You can even run a Reporting Services off the express version. Look for "SQL Server Express with Advanced Services (contains the database engine, Express Tools, Reporting Services, and Full Text Search" --> download here: http://www.microsoft.com/sqlserver/en/us/editions/2012-editions/express.aspx

If you think that there is a necessity for an enterprise-wide database in your company (assuming your company doesn't have it yet), go ahead and buy the Developer version. The developer version contains everything that the Enterprise version of SQL Server has. If you are testing what SQL Server can do in an enterprise setting, this is the version you want to get your hands on.

Note that you cannot use the developer version for production. It's intended for testing only. The good thing is, when your company database is ready for prime time - production - you can easily "switch" the developer version to the licensed SQL Server instance.

As you start learning SQL Server, it is just fitting to start learning TSQL, which is SQL Server's implementation of the SQL language.

You mentioned you want to learn running some queries, so I suggest you start with:

  • Data Manipulation Language (DML) - SELECT, INSERT, UPDATE, DELETE, MERGE
  • Data Definition Language (DDL) - CREATE, ALTER, DROP database objects (Tables, Views, Constraints, Index, Stored Procedure, etc)

The SQL Server 2012 Books online is also a great starting point in learning SQL Server: http://msdn.microsoft.com/en-us/library/ms130214.aspx

The SQL Server community is also a great resource. Follow me on Twitter @MarlonRibunal. Some tips on where to find necessary SQL Server skills:

  1. Books
  2. Blogs

  3. User Groups - there is this Professional Association for SQL Server (PASS). Good resource for anything about SQL Server - events, people, etc. Explore the site to learn what the organization is all about.

  4. Events

  5. #sqlhelp hashtag on Twitter - your helpline on Twitter. I suggest you start following those people that used the #sqlhelp tag - either they were asking for help related to SQL Server or answering a question.

  6. SQL University - collection of topic-specific blogs

  7. SQL Server Study Group Meetup - assemble your members and study for SQL Server Certification exams or just for enhancement of SQL Server knowledge

share|improve this answer
I appreciate the detailed response, Marlon. As it happens, I'm not in a typical IT environment of any kind. I am an independent contractor and my associates work in a small company. We use Excel for the same reason we use Word - they are ubiquitous productivity applications. All that to say, there is no IT person for me to appeal to here. Whatever I do will have to be something I roll on my own which is why I mentioned my lack of server-side skills. – Charles W May 4 '12 at 12:23
1  
You're on the right track. As an independent contractor, you need to stack up skills so you can offer more services. I suggest you take on a personal project: Migrate the Excel data into a SQL Server 2012 Express. Install the Reporting Services and create reports. I'm sure there's a need there for some sort of reporting. I've written a step-by-step how-to on Reporting Services in my old blog. It's for SQL Server 2005 but you can apply the steps on versions 2008 or even 2012: dbalink.wordpress.com/2009/01/17/… – MarlonRibunal May 4 '12 at 19:52
Thanks again, Marlon. I'll take some time over the next day or so to digest your response and compare it with some of what others have suggested here. Now that you know that I'm not in a MS environment, per se, would you still recommend the MS option? – Charles W May 4 '12 at 20:30
1  
If I say you go for SQL Server, much of that is my bias toward SQL Server :-) Give it a try, though. Or try multiple platform and see which one you like best. Or, better still, look at your clients' profile - what platform are they using? If 95% of them are on Microsoft stack, then I'll give SQL Server a try more than the other platform. You get the picture. :-) – MarlonRibunal May 4 '12 at 21:00
up vote 3 down vote

If you want to learn Access then go for it, but if your eventual destination will be an Enterprise database then it won't help you as quickly in that direction as other options due to its extensive GUI hiding the SQL.

I know the title of your question including the word "easiest", but hear me out regarding the Oracle route. Not only is Oracle Express Edition (Oracle XE) free and available on Windows, but 99% of what you learn on Express Edition can be applied to Oracle licensed editions including Oracle Enterprise Edition. In addition, there are several features that make Oracle a good choice for small projects.

Installing Oracle XE is easy and using SQL on Oracle is no more difficult than using SQL on most other platforms. The more difficult parts of Oracle come with larger systems that need administration. The things that make being a database administrator hard mostly do not apply to people in your situation.

Oracle XE does not require a server (your workstation will most likely work fine).

share|improve this answer
Thank you for paying attention to the specifics of my question and tailoring your response accordingly. I'll be taking the next day or two to review some of these different options (MS vs SQLite vs Ocacle). I appreciate it! – Charles W May 4 '12 at 20:33
up vote 1 down vote

If you want to head down the Microsoft path, you can get started using MS SQL Server Express which is a free edition. There are obviously some limitations compared to Standard edition and above - you can read more about SQL Express here: http://www.microsoft.com/sqlserver/en/us/editions/2012-editions/express.aspx

You can download SQL 2012 Express and the Management Studio client tools here: http://www.microsoft.com/en-us/download/details.aspx?id=29062

If you're interested in the differences between editions for 2012, there is a table here: http://msdn.microsoft.com/en-us/library/cc645993%28v=SQL.110%29.aspx

This page provides links to the sample database "Adventure Works" which you can use to tinker with: http://msdn.microsoft.com/en-us/library/hh231699.aspx

If you want to use your data from the csv or xlsx then you can read up on importing data from CSV, xlsx, etc. into MSSQL. There is a lot of resources on this so I won't link them here but a google search would suffice.

share|improve this answer
Am I wrong in understanding that MS SQL Server Express must be set up on a server? – Charles W May 4 '12 at 12:30
1  
Yes. You are wrong. You can install SQL Server Express on a desktop or laptop computer. There are certain operating system limitations with each version of SQL Exress, but if you are using Windows Vista or Windows 7 on your computer, you shouldn't have any problems. – G Mastros May 4 '12 at 20:17
Excellent - thank you. – Charles W May 4 '12 at 20:33
up vote 0 down vote

It may be possible to solve your problems with some sophisticated excel features. Have you looked into Excel's Autofilter feature? It will allow you to narrow your results and answer the question show x where this is that and this contains that.... In order to get to "any of these contain that" I would suggest an additional column with a formula using the if() and find() functions which could then be filtered on.

I use both Access and Excel frequently, and I'm not sure the complexity of your question would be enough for me to pull a spreadsheet into access, especially if the "source data" needs to stay in Excel for reporting and/or use by folks without access.

Of course, your questions may be much more complex than your example suggests, and then database might be the way to go.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.