Learning SQL

Carlosinfl

Loves the juice
Joined
Sep 25, 2002
Messages
6,633
Hello all,

I think I'm finally going to try and become a DBA. I recently quit my Linux System Administration gig where I managed SaaS clusters in EC2 for a Jr. DBA position. I'm starting at the bottom and working my way to the top and so far love everything I learn about being a DBA and learning SQL. My employer is requiring I take/pass Database Fundamentals 98-364. It's been very interesting so far but don't want to under-estimate the exam especially since work is expecting it so if you have any advice / suggestions.

Also, I've deployed a PostgreSQL dedicated server and I've loaded sample databases and played w/ the sample data set however I'd like to point some application that will write live data to my cloud database server. I'm not talented enough to write anything that will connect and write data to the backend database. Anyone have any suggestions for any app I can point at my PostgreSQL box. Any suggestions?
 
Being realistic. I have some large shoes to fill in the next 90 days at work. I don't think going to Amazon and buying 'Learn Python in 10 min.' is going to happen right now so thats why I asked if maybe someone is familiar with a method of gathering live data and/or something on Github I can use. Just want to point anything at my database to understand how it works while I study for my new role.
 
FlayedMan is right. Pick up some scripting language as he said, write a simple script to interact with your DB. This is the best way to learn how it interacts. Just running something thats already written really gain you nothing. Doesnt take 'dev' skills to do this level of programming/scripting.
 
Why are you playing with PostgresSQL when your goal is passing a Microsoft training course?
 
I'm supporting both MS SQL / PostgreSQL. The certification is a main priority however sometimes it's easier to gain access to a DBMS that runs on all my systems. It's hard to always get access to a instance of MS SQL Server.
 
How so? You can install SQL Server Developer Edition for free and it'll run on your laptop or desktop. Easy to use in a VM, and so on. (If you don't have an MSDN or TechNet account, you can order a copy of SQL Server Developer Edition for about $40.)
 
How so? You can install SQL Server Developer Edition for free and it'll run on your laptop or desktop. Easy to use in a VM, and so on. (If you don't have an MSDN or TechNet account, you can order a copy of SQL Server Developer Edition for about $40.)

On top of that, even if you're unwilling to shell out any money at all, there's always SQL Server Express. So depending on how in-depth the particular certification in question goes, that might do everything you need to play with to pass the cert.
 
If I'm remembering right, you can always play with the Northwind database that comes with SQL Server (well, at least it used to). Put the MSDE version on your desktop or a VM and have at it. As you're focusing on DBA skills, I'm not sure learning a programmatic scripting language will be the best thing to do (however, learning T-SQL would be a big help).

Another option would be to grab the old (yet should still be free) MS Accounting 2009 program, which sits on SQL Express/SQL. You can then use the application from the business side and correlate what's happening with the DB as you interact with it (and can generate your own data). Another option can be to load up some forum software (i.e. PHPBB) or perhaps Wordpress, as they all have database backends.

Since you're taking the M$ test, I would suggest practicing on M$ tools, as their T-SQL is different from Oracle's PL-SQL is different from whatever other vendor's SQL that's out there. Conceptually, they're more or less the same for select, insert, update, deletes and joins, but there are some nuances between them.
 
LOL, "M$"! Ha ha ha ha, I see what you did there! Too funny!

Meanwhile, the differences between PL-SQL and T-SQL are far less subtle than nuances. One thing you seem to be missing is that they're both procedural languages. I think an administrator who doesn't know how to write proedural code in their database's language, or write simple scripts in a shell or scripting language, is worth much.
 
I understand they're both procedural languanges. Just one is more optimized for a specific RDBMS engine like SQL Server...

I think I'm doing the right thing by just focusing on:

- Relational Database Theory Fundamentals
- SQL Server 2012/2014 T-SQL study material
- ANSI SQL
 
Point is, saying "I'm not sure learning a programmatic scripting language will be the best thing to do (however, learning T-SQL would be a big help)." doesn't make much sense because T-SQL is, it's own self, a "programmatic" language. There are parts that are imperative, but you'll want to know the procedural parts, too.

There's really no such thing as "ANSI SQL". I mean, sure: there's a standard, and a bunch of people who meet every once in a while to make a new standard. But there's very little traction for the standard, and every DBMS vendor implements something slightly different -- well, something substantially different, really. Knowing if some technique or idiom is different in Oracle than ANSI SQL really doesn't tell you much. Knowing if that technique is different in Oracle, SQL Server, and MySQL -- that can help, if you're in the rare situation of moving database back-ends.

Picking a platform and learning it, and adding relational theory on top of it sounds like a great plan to start, though. I'd add procedural programming on top of it for the stated reason about the vendor-specific languages being procedural.

But also because, as a DBA, you're going to find yourself with repeatable tasks that you want to script: moving 3500 files from here to there; taking many little files and running a tool on them to push them into the database; configuring, managing, merging many different systems or schemas or hosts or whatever; and so on.

You've got plenty to cover, so it's fine to not make programming a priority. But you'll need at least a little bit of it sooner or later.
 
I'm supporting both MS SQL / PostgreSQL. The certification is a main priority however sometimes it's easier to gain access to a DBMS that runs on all my systems. It's hard to always get access to a instance of MS SQL Server.

I majored in DBA at SSCT (Stark State College) in Stark County Ohio and I can honestly tell you for a technical school they do not expect you to know PL-SQL and any other programming or scripting language as a DBA other than the Windows or LInux/UNIX shell and how to script in using SQL. If your referring to MS-SQL your on your own because that class was replaced for me because they didn't feel it was necessary to teach it. As for MySQL if your referring to that for any reason I never had the opportunity to master it, so once again your on your own.

At SSCT PL-SQL is taught to the programming majors and that seems to be the norm, but if you must know it then by all means pursue it because the knowledge will come in handy as a DBA. After all I'm minoring in programming, so I picked up a book on PL-SQL. To bad it was for Oracle 10g and the current is 12c. Meaning you'll want to pick up books by Oracle Press instead Course Technology. I can't speak for you on what your needs are either, so maybe you need to learn how to program your database and script into it using SQL scripts and who knows else to help you automate the process. Good Luck though and I hope my reply helped.
 
I majored in DBA at SSCT (Stark State College) in Stark County Ohio and I can honestly tell you for a technical school they do not expect you to know PL-SQL and any other programming or scripting language as a DBA other than the Windows or LInux/UNIX shell and how to script in using SQL.
Are you saying that someone who graduates from this college with a major in DBA doesn't know SQL, nor do they know any other programming language?

Is this the curriculum in question?

What does someone who completes this program know? What's the point of that major?
 
Are you saying that someone who graduates from this college with a major in DBA doesn't know SQL, nor do they know any other programming language?

Is this the curriculum in question?

What does someone who completes this program know? What's the point of that major?

No they know SQL and DBA administration, but not PL-SQL and the point of the major is that you don't need to know how to program. If you want to program you major in programming or minor in it. Unless you want to be a jack of all trades then you do whatever you want.
 
No they know SQL and DBA administration, but not PL-SQL and the point of the major is that you don't need to know how to program. If you want to program you major in programming or minor in it. Unless you want to be a jack of all trades then you do whatever you want.

That's interesting. I didn't know you could be a good DBA without being able to program, or at minimum, having a good understanding of the computer science behind programming.
 
SQL isn't a procedural language. How does one "script in using SQL"?

I can't figure out what you mean when you say the point of the major is that "you don't need to program". Someone can take Art History, or a zillion other majors, if they don't want to program.
 
Last edited:
My background is more in MySQL, but I always used MySQL Workbench for ad-hoc queries and admin tasks. It looks like pgAdmin ( http://www.pgadmin.org/screenshots/) is a pretty good analog for PostgreSQL. You can use this to insert data, run scripts, load data from CSVs, et cetera.

On the Microsoft side, rather than having to set up a dedicated server, you may want to give Azure a shot (the free demo should be sufficient for your needs). They have a nice web-based admin interface, or you can use things like SQL Server Management Studio- https://azure.microsoft.com/en-us/documentation/articles/sql-database-manage-azure-ssms/
 
Also, to built on the points made by others, good DBAs should really have at least a decent understanding of computer science & development. Aside from that fact that it will make you more useful to your colleagues, it will make you more marketable in the long term, like when document databases become even more prevalent. Good luck understanding map/reduce without some rudimentary programming experience.
 
That's interesting. I didn't know you could be a good DBA without being able to program, or at minimum, having a good understanding of the computer science behind programming.

Why do you need to know how to program or be have a good understanding of computer science to be a DBA if you not trying to be a jack of all trades or a computer scientist. Have you even been to college or looked deeply into these job description and what they had to know then or now for at least high school as a research paper. I know you probably read about it, but have you actively studied it meaning take notes or write about what exactly these professions have to do or do you have a deep background in computers that may mean you don't need to.

Why do you think a DBA needs to know how to program when I said only programmers who can also be DBA's only need to know that because if your a network Admin Major you don't need to know how to program and only need to know how to script? It may not always be that way in the real world, but that's what your taught in college or at least the one I went to. If that's what you where getting from when I said I'm sorry if I confused you. That's just what SSCT (Stark State College of Technology) does and not what Major Universities like the UA (University of Akron) do because the UA doesn't even teach DBA as part of CNAS for some reason.

Probably because the feel that diversity, human relations, humanities, and communication are more important than major specific classes. Heck the UA doesn't even consider math up to Pre-Cal to be important because they only teach finite mathmatics, which doesn't make sense to me because how could you understand the basic's of computer science let alone have a good understanding of computer science and programming. Don't get me wrong as a DBA majoring in CNAS you need to know programming logic and problem solving, which SSCT cut and replaced with cyptography. I didn't like that they replaced programming logic and problem solving with cytography because automation is still important and why should preparation for scripting not be taught when most of the same principles, like decisions, loops, functions, and arrays still apply.
 
SQL isn't a procedural language. How does one "script in using SQL"?

I can't figure out what you mean when you say the point of the major is that "you don't need to program". Someone can take Art History, or a zillion other majors, if they don't want to program.

I'll give you an example that works in Windows at least if not Linux/UNIX, even though it should work in both. It is as follows:

spool xCH02_name1016.txt
set echo on
clear screen
TTITLE OFF
BTITLE OFF
CLEAR BREAKS
CLEAR COLUMNS

/* This file is a sample which
may be used by the student as
a pattern for the assignment scripts.

Student may choose any acceptable method
for entering remarks/comments.
*/

rem SQL Solutions for Chapter 02

set linesize 80
set pagesize 60

-- problem 2-1

SELECT *
FROM books
ORDER BY ISBN;

spool off

-----------------------------------------------------------------------------

Does this explain it. After all the only reason one needs to know how to program is to script in Windows or Linux. It may be helpful to know how to script in SQL, but it's usually not necessary. However, I believe it can be done though if I'm not mistaken. Most of a SQL scripts contents are queries as well.
 
Last edited:
I'm supporting both MS SQL / PostgreSQL. The certification is a main priority however sometimes it's easier to gain access to a DBMS that runs on all my systems. It's hard to always get access to a instance of MS SQL Server.

Whoops then maybe you can ignore my other replies and quotes because I thought you were referring to Oracle SQL because it's usually referred to as just SQL and Microsoft SQL is generally referred to as MS SQL.
 
Why do you need to know how to program or be have a good understanding of computer science to be a DBA if you not trying to be a jack of all trades or a computer scientist.

It's a matter of understanding your customer's needs in order to properly support them. A DBA's customers are generally programmers, so to start with, they need to understand the needs they're trying to support.

Additionally, I don't know how it's possible to be a good DBA without having a general understanding of data structures and algorithms. You need to understand how a database stores and retrieves data to be a good DBA, and in order to do that, you need to understand computer science. A DBA who doesn't know how B+ trees operate is not going to be a very good DBA...

Good DBAs serve as a knowledge center. Good DBAs are the people I go to when I want to know the best way to implement my data model on a given vendor's database platform, which features, hints and data types to use, whether or not I should be using a hash strategy or a lookup strategy for sharding, how to diagnose a seemly configuration based performance problem, etc.

A good DBA needs to be an expert in storing and retrieving data, not just an expert on clicking through MySQL's install wizard. We have no need for people who do basic install and config tasks for us as DBAs...our puppet scripts can do that. We need people who solve high level problems, and there's a lot of computer science concepts that can't be lost on those people.

TLDR; Good DBAs are useful to me because they can explain what the database is actually doing. That requires a good understanding of computer science.
 
Last edited:
It's a matter of understanding your customer's needs in order to properly support them. A DBA's customers are generally programmers, so to start with, they need to understand the needs they're trying to support.

Additionally, I don't know how it's possible to be a good DBA without having a general understanding of data structures and algorithms. You need to understand how a database stores and retrieves data to be a good DBA, and in order to do that, you need to understand computer science. A DBA who doesn't know how B+ trees operate is not going to be a very good DBA...

Good DBAs serve as a knowledge center. Good DBAs are the people I go to when I want to know the best way to implement my data model on a given vendor's database platform, which features, hints and data types to use, whether or not I should be using a hash strategy or a lookup strategy for sharding, how to diagnose a seemly configuration based performance problem, etc.

A good DBA needs to be an expert in storing and retrieving data, not just an expert on clicking through MySQL's install wizard. We have no need for people who do basic install and config tasks for us as DBAs...our puppet scripts can do that. We need people who solve high level problems, and there's a lot of computer science concepts that can't be lost on those people.

TLDR; Good DBAs are useful to me because they can explain what the database is actually doing. That requires a good understanding of computer science.

Yes I get that now that you mention it and yes if your going to the programming side of DBA then you need to program as I was stating, but if your doing the Network Admin side then not so much. However, as a Network Admin one should know how to program in the most important languages if not all of them and minor in them if going to college. As a Network Admin Student math is still important too, which is why I had to take up to PreCalculus while earning my associates. However, the University I'm going to now I believe only expects students to take Finite Mathematics. Something I'm not sure where it falls in that category. As a Network Admin student or in the field though I absolutely agree that you need to understand computer science, hence the reason I stated programming logic and problem solving. You obviously know what your talking about, but I'm just telling you what areas you would be considered in if you were attending college in today's schools or at least technical schools and what you should be expected to know in your field according to today's college curriculum.
 
Last edited:
Back
Top