9

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.

3
  • Should I let the mods do it, or should I delete and repost at dba?
    – Charles W
    May 4, 2012 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. May 4, 2012 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, 2012 at 3:45

6 Answers 6

5

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.

1
  • 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, 2012 at 12:38
7

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

4
  • 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, 2012 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/… May 4, 2012 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, 2012 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. :-) May 4, 2012 at 21:00
4

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).

1
  • 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, 2012 at 20:33
1

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.

2
  • Am I wrong in understanding that MS SQL Server Express must be set up on a server?
    – Charles W
    May 4, 2012 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. May 4, 2012 at 20:17
0

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.

0

LEARN SQL:

I would highly recommend not going the Microsoft route and opting for learning SQL. You can easily set up a development environment on your existing hardware (whether you are running Windows, Mac or Linux). After setting up your development environment you can take a look at a few well-made free resources online to teach you the basics of SQL.


STEP 1: Setting up your development environment

Windows: If you are using a Windows-based machine I recommend going with WampServer, which can be downloaded from wampserver.com. Wamp stands for Windows + Apache + MySQL + PHP. The advantage of this package is it will set up a self-contained environment with all the parts you need to test out development - or, in our case, learn MySQL in a hands-on manner. It also comes with phpMyadmin for an graphically based alternative to interacting with your databases.

NOTE: Make sure to have the all the appropriate Microsoft Visual C++ Redistributable packages installed on your system. You can find these on the WAMP website, or you can see these recommendations during the WAMP installation - along with all necessary download links for the packages you are missing. Make sure to install them first before finishing the WAMP install.

Linux: If you are running Linux I would recommend installing LAMP. Instructions for installing LAMP under Ubuntu can be found here: http://howtoubuntu.org/how-to-install-lamp-on-ubuntu. Alternatively, if you are running Ubuntu Server you could easily install LAMP via Tasksel. You can do that like this:

sudo apt-get update
sudo apt-get install tasksel
sudo tasksel install lamp-server

Mac If you are using a Mac you could go with a number of alternatives. I recommend ampps, which can be downloaded from http://www.ampps.com/download


STEP 2: Working with SQL in your new environment

I'm going to continue this tutorial assuming you went with the WampServer solution (based on your talk of Microsoft solutions).

After WampServer is installed launch it and wait until the server shows up in a green color in your taskbar. This means it is active and working properly.

Fully Functioning WampServer

OPTIONAL: Now you could do some web searches for sample databases or load your own csv files. Some spots on the web with decent sample databases in csv format are as follows: https://support.spatialkey.com/spatialkey-sample-csv-data/
https://catalog.data.gov/dataset?res_format=CSV
http://www.sample-videos.com/download-sample-csv.php

For simplicity you could load these CSV files via phpMyAdmin. Just left-click on the WampServer icon in your taskbar and then click on "phpMyAdmin":

Launch WampServer's installation of phpMyAdmin

Next, login to phpMyAdmin with the defult username and password, which is "root" (without quotes) and a blank password.

Log into phpMyAdmin

And, finally: load your CSV file via the "Import" tab:

Importing your CSV in phpMyAdmin


STEP 3: Practicing your SQL

Now you can start practicing. Open a terminal and navigate to your MySQL installation directory under WampServer. For me, this is "C:\wamp64\bin\mysql\mysql5.7.19\bin" but this will vary depending on whether you installed WampServer 32bit or WampServer 64 bit. It will also depend on what version of mySQL was installed at the time you read and follow these instructions. I trust you can find the directory yourself. If you are stuck, from your root directory type in the following and note the output directory:

dir mysqld.exe /s

Type the following to start working in your MySQL environment:

mysql -u root -p

This will launch mysql with the username "root". You will be prompted for your password (which is blank by default). Simply press ENTER.

Using the Command Line to Work in your MySql Environment


STEP 4: Upgrade your SQL Skills

Now that you have a working MySQL environment you can proceed to learning how to be a master in that environment! I suggest checking out the following:

Derek Bana's Youtube channel: https://www.youtube.com/watch?v=yPu6qV5byu4
Codecademy: https://www.codecademy.com/articles/sql-commands
Tutorialzine: https://tutorialzine.com/2016/01/learn-sql-in-20-minutes
W3Schools: https://www.w3schools.com/sql/sql_quickref.asp


GOOD LUCK TO YOU!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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