Some questions about SQL

[ibex333]

What is the difference between MySQL and SQL?
How do they relate to PHP and what is PHP?
If I know SQL does it mean that it will be easy for me to use SQL Server and Oracle?
Is SQL server a program used to program in SQL or is it actually a server OS like Server 2008 but which is used to manage, store and centralize databases?

Where can I find some good SQL tutorials? I am currently trying to understand "normalization" but every source I google doesn't explain it very well. I wish they would show it with pictures of tables, but instead they explain it with text. Somehow it just doesn't click yet.

Slightly off topic... Is it possible to find a low paying job with just basic knowledge of SQL, or does one have to be a certified DBA/College graduate? This may be a dumb question, but my knowledge of SQL is very limited right now, and I am curious to find out just how good someone must be with this stuff to be "in demand".

Thanks!

 

[Train]

What is the difference between MySQL and SQL?

MySQL is owned by Oracle, SQL is a generic term, though often used to mean MSSQL, which is Microsoft's Sql Server, both relational database management systems (RDBMS) that use the Structured Query Language (SQL)

How do they relate to PHP and what is PHP?

They dont, though you will typically find a data driven web app that uses PHP has a backend in some sort of RDBMS, typically MySQL

If I know SQL does it mean that it will be easy for me to use SQL Server and Oracle?

Yes

Is SQL server a program used to program in SQL or is it actually a server OS like Server 2008 but which is used to manage, store and centralize databases?

All of the above. mostly it is a server, but it comes with some pretty handy tools that help you write SQL as well.

Where can I find some good SQL tutorials? I am currently trying to understand "normalization" but every source I google doesn't explain it very well. I wish they would show it with pictures of tables, but instead they explain it with text. Somehow it just doesn't click yet.

Either keep looking for good tutorials, or find someone who is good with DB design to tutor you. The best DB knowledge I have was because I got to work beside an Oracle wizard for a year in the 90's

Slightly off topic... Is it possible to find a low paying job with just basic knowledge of SQL, or does one have to be a certified DBA/College graduate? This may be a dumb question, but my knowledge of SQL is very limited right now, and I am curious to find out just how good someone must be with this stuff to be "in demand".

Thanks!

If all you know is a little SQL... not much in the way of jobs. Usually people who are "ok" with SQL use it in tandem with front end r middle tier app programming, like PHP, Cold Fusion, Ruby, ASP.Net, WinForms, XML services, etc.

If you want to do only SQL for a living, you can get there but you better be damn good with it. An at that point you would also have to know a lot about backing up, replicating, hosting uber large DB's, using virtualization, possibly even cloud services, and some network knowledge as well.

 

[LokutusofBorg]

Most entry level SQL jobs are going to be writing reports.

Most DBAs without development experience are sysadmins that moved into being a DBA.

And in my experience, not many companies actually have dedicated DBAs on staff. If they do, they're very low in number compared to other positions (e.g. 1 DBA for a 12 person dev team, or 1 DBA on a team of 10 sysadmins, network admins, desktop support, etc.).

 

[brandonb]

Normalizing really quick means:

Remove column(s) from a database that is usually very optional and not really populated very much and only looked at in special circumstances. If you are holding account information and the email field is optional, and only filled in 1% of the time, there is no point storing that information in the account table because you are just wasting space and slowing down the account table. Take as many of these very optional fields, and place them in an "account_extrainfo" table which stores the account id from the main table. That way if you need the info, you can search the account_extrainfo table if you need to, but it doesn't store all the information in the account table for performance reasons.

Normalization = Creating more tables, less columns in each.
Denormalization = Creating Less tables, more columns in each.

The ultimate goal in database designed with a relational database, is to have the least amount of data in a table. Because the way the system works is that data is stored in 8k pages on the disk (for MSSQL). If each row contains 20 bytes, and you have 8k available, that 400 records per page. The database can read that page, read the first and last row (that is sorted) and say "is the record im looking for on this page?" or "skip to the next page to search for the record"... The more records you can have per page, the faster the searching goes to find the data. So if you are denormalized, and have 8k worth of data per row, each row is basically on it's own page, and you will be really slow. If you are normalized and have 20 bytes per record, then performance will go very fast as there are 400 records per page.

(its a bit more complex than that, but you can get the idea)

 

[Markbnj]

Normalizing really quick means:

Remove column(s) from a database that is usually very optional and not really populated very much and only looked at in special circumstances. If you are holding account information and the email field is optional, and only filled in 1% of the time, there is no point storing that information in the account table (snip)

I know what you're trying to say, but I have a little bit of a problem with that description of normalization. The point of normalization is to minimize artificial dependencies between independent entities. You should always start from a normalized schema where each independent entity occupies its own table, and then denormalize as necessary for performance or other reasons... imho.

 

[Ka0t1x]

I don't mean to come off as a jerk but Normalization is very well summed up at Wiki:

http://en.wikipedia.org/wiki/Database_normalization

As is a lot of these other topics, spend about an hour and crawl some pages.

 

[KIAman]

From my experience, you'll need minimal knowledge of transact SQL to get a job as a DBA.

The really good SQL guys consult.

This pisses me off because I have to work with people who don't know the bare minimum in their job description and more often than not, I have to tell them what to do while they hold the keys to do it.

Please don't be one of "those" guys. Get some real learning. Stop posting on forums unless it supplements your real training.

 

[wantedSpidy]

hey KIAman!! Stop posting, and get to work!!!

 

[aceO07]

The best way to learn it is to read, then apply what you've learn. If you can program a bit, making a program interact with a database would be a good way to learn and understand how things fit together.

Maybe find a really entry level position that will train.

 

[Sphexi]

I have a more technical SQL question, unfortunately I have zero TSQL skills right now so it's a bit beyond me.

Basically I have a system that has a login feature, when you enter the password it submits it to some code that runs it through a MD5 hash and spits out a hex string. I then compare this string to the stored password in SQL (which I initially stored as a hex MD5 string rather than a text password).

I'm not playing with the hashbytes feature, but my requirement of it being a hex string is throwing me off. This is what I've tried (unsuccessfully) so far:

SELECT * FROM users
WHERE userPassHash = master.dbo.fn_varbintohexstr(CAST(HASHBYTES('MD5','password') AS VARBINARY))

It doesn't throw an error, but at the same time it doesn't find the record I'm looking for.

 

[KIAman]

hey KIAman!! Stop posting, and get to work!!!

I wasn't suggesting the OP never post here, just that this forum isn't really a place to learn anything. If he's serious about learning SQL, there are plenty of self-paced literature or instructor-led courses available.

I'm just bitter about some recent "DBAs" I've worked with who's only credentials are that they play WOW.

 

[Sphexi]

I wasn't suggesting the OP never post here, just that this forum isn't really a place to learn anything. If he's serious about learning SQL, there are plenty of self-paced literature or instructor-led courses available.

I'm just bitter about some recent "DBAs" I've worked with who's only credentials are that they play WOW.

Hey now, being a progression-ready DK tank totally qualifies me to manage your database.

 

[wantedSpidy]

I wasn't suggesting the OP never post here, just that this forum isn't really a place to learn anything. If he's serious about learning SQL, there are plenty of self-paced literature or instructor-led courses available.

I'm just bitter about some recent "DBAs" I've worked with who's only credentials are that they play WOW.

You're correct sir, few ppl seem to know what they are doing in the IT industry!

 

[Markbnj]

I wasn't suggesting the OP never post here, just that this forum isn't really a place to learn anything. If he's serious about learning SQL, there are plenty of self-paced literature or instructor-led courses available.

I'm just bitter about some recent "DBAs" I've worked with who's only credentials are that they play WOW.

I take that personally . I think you can learn some things in this forum, otherwise why have it?

 

[degibson]

I take that personally . I think you can learn some things in this forum, otherwise why have it?

Obviously, this forum exists only to argue about the relative merits of programming languages.

 

[Cogman]

Obviously, this forum exists only to argue about the relative merits of programming languages.

And C++ is obviously the winner on all counts

 

[Markbnj]

C++ drools. Ada rules!

 

[Cogman]

C++ drools. Ada rules!

Ada's for fools, ASM's for the cool!

(ok, ok, I'll stop derailing.)

 

[stlcardinals]

OP, you said you had problems learning with just text, so you should take a look at something like Head First SQL

 

[Zargon]

I wasn't suggesting the OP never post here, just that this forum isn't really a place to learn anything. If he's serious about learning SQL, there are plenty of self-paced literature or instructor-led courses available.

I'm just bitter about some recent "DBAs" I've worked with who's only credentials are that they play WOW.

I am a pretty bad DBA, atleast IMO. but the guy I took over for was utter shit

I follow @BrentO on twitter and a few others to try and pick up tips and notifications of free training webcasts ETC.

 

[crackerjacks]

pure sql alone won't get you a job, you'll need to combine those skills with a middle tier product like Microstrategy, Hyperion, Business Objects etc. From there you can learn if you enjoy just writing queries and generating reports or move onto application development or database administration.

You really don't even need to be that good at SQL to make good money doing Business Intelligence and Reporting. Most of the stuff you'll do will be pretty easy and anytime you come into a difficult query that runs like garbage you'll just submit a support ticket to your overworked, under appreciated DBA saying there is a problem with the database and they'll tune your SQL for you

 

[ForumMaster]

pure sql alone won't get you a job, you'll need to combine those skills with a middle tier product like Microstrategy, Hyperion, Business Objects etc. From there you can learn if you enjoy just writing queries and generating reports or move onto application development or database administration.

You really don't even need to be that good at SQL to make good money doing Business Intelligence and Reporting. Most of the stuff you'll do will be pretty easy and anytime you come into a difficult query that runs like garbage you'll just submit a support ticket to your overworked, under appreciated DBA saying there is a problem with the database and they'll tune your SQL for you

frankly i disagree with you, but it varies with the size and needs of the organization. i am a dba on a team of around 20 people (all oracle and 4 whom are sql server).
we manage hundreds of databases. we help developers design their systems properly so that the developers don't have to research all the elaborate ways to do things. the dba knows the best way.

A dba is more then knowing sql, but we don't deal with BI. atleast not in my workplace. a DBA does need to know how to program, how to tune, how to backup, to design and implement a system. These aren't trivial or easy things.

If a developer comes to you with extreme requirements (hundreds to thousands of users per database server, hundreds of millions of records per hour that need to be inserted and so on),
it isn't trivial to deliver, and you can't just throw a mainframe at it.

 

[KIAman]

Heh, this forum is a place to learn, in the broader sense, but it should not go on your resume.

 

[hooflung]

T-SQL and PL/SQL are definitely needed in an enterprise environment especially when there are multiple development teams working from a single source, such as a mainfraime, and transitioning data to a Database like SQL Server or Oracle, respectively.

I think this is starting to be the case for many older companies who have decided it is time to move off the mainfraime.

In honesty you have to know these things. Companies rather hire people who are moderately educated to hit certain employment cost points and source out to the vendor or a certified consultant when their needs grow beyond the scope of knowledge. You can write off the cost of that momentary need easier than a hire that demands more.

This is why most companies bawk at MySQL and Postgresql systems with a traditional IT structure. The fear of losing that guru and having to find another out weighs the cost lost investing into Oracle and MSSQL.

I haven't used or seen DB2 since I worked around AIX and AS/400's years ago but I am sure the same is the case for DB2 as well since IBM is definitely selling the Power series still.

 

[Seero]

What is the difference between MySQL and SQL?

SQL is a generic name of database queries, Structured Query Language. MSSQL is a name of a database manage system, which uses SQL. In fact, it isn't MSSQL, it is MS SQL Server. People dropped the word server and sometimes replace it with the word database.

How do they relate to PHP and what is PHP?

PHP is a scripting language like JavaScript. It can construct connections to database as well as communicate to it. In a nutshell PHP is good for server-side coding and Javascript is good for client-side coding. Instead of dynamically construct a new HTML depending on input/result, scripting language allows different results to be displayed under the with the same PHP file. Server reads the PHP file, computes its contents, and generates a HTML file and sent towards browser (client). That means, using PHP + database, it is possible to construct a web page where the contents are based upon the data stored within a database.

If I know SQL does it mean that it will be easy for me to use SQL Server and Oracle?

Yes and no. Basic SQL knowledge will allows you to write simple reports and views. For usual reports, you will also have knowledge on Java and PLSQL as well as the structure of the database itself. To understand the structure of a database, you will need to know how to read ER-diagrams and stuffs.

Is SQL server a program used to program in SQL or is it actually a server OS like Server 2008 but which is used to manage, store and centralize databases?

See above.

Where can I find some good SQL tutorials? I am currently trying to understand "normalization" but every source I google doesn't explain it very well. I wish they would show it with pictures of tables, but instead they explain it with text. Somehow it just doesn't click yet.

Normalization has 2 folds. One is to reduce the occurrence of storing the same data on different places, the other is to avoid wasting spaces by reducing the occurrence of blank columns. You will need to read books about Normalization on how to do it effectively.

Slightly off topic... Is it possible to find a low paying job with just basic knowledge of SQL, or does one have to be a certified DBA/College graduate? This may be a dumb question, but my knowledge of SQL is very limited right now, and I am curious to find out just how good someone must be with this stuff to be "in demand".
Thanks!

I won't say it is possible.