is it possible to get advanced SQL knowledge in one day?

[s0me0nesmind1]

contact tag
1 ----- 1
1 ----- 2
2 ----- 1
2 ----- 4
3 ----- 4

Write a query to select contacts which don't have a tag of 1 and 2.Contact 1 has both 1 and 2 so it should return 2 and 3.

Use spoiler tag for solution.

Spoiler

SELECT contact
FROM tbl
WHERE (tbl.tag <> 1 AND tbl.tag <> 2)

Simple - no? What the hell are you thinking bober?

Either that - or I'm guessing that it would return it in multiple contacts and thus return contact - perhaps you mean with a group by?

Spoiler

SELECT contact
FROM tbl
GROUP BY contact
HAVING tag <> 1 AND tag <> 2

I'm too lazy to actually test for accuracy, so I don't expect these to be right, just off the top of my head

 

[ZaneNBK]

People that think SQL is easily learned and done, don't know SQL. I took two terms of Oracle DBA, plus another two terms of database design and queries.

Sure there are only a handful of commands, but in large DB's being to liberal with your structure can bring a company to a halt.

There are a handful of basic commands but a ton of built in functions and system stored procs that can be used to make things perform better or simplify your code, such as being able to build and execute dynamic SQL in stored procs.

For me optimization was the most interesting part. Basically trying to take and combine as much work into a single query as possible, then using optimizer hints to improve performance by telling the optimizer which indexes to use when it wasn't using the correct ones, etc...

In one of my bigger projects I had a query that was probably a page and a half long with multiple levels of correlated subqueries and optimizer hints. I document the crap out of my code but even then it would take several minutes of study to understand the query again when I needed to change it. Sometimes performance is much more important than maintainability though.

Another project I worked on we had a clueless DBA that insisted we used natural keys (primary keys made up solely of real table data, not identity, sequence or autonumber type columns). So for one to many relationships the child table would contain the full primary key of the parent table plus the natural key for the child to make it's primary key. Some tables had 16 column primary keys due to this. Not only was it a nightmare to write queries for, nightly processing took forever to run. I found articles explaining how surrogate keys can be much better for performance and how natural keys are generally a mistake to use as primary keys since they're based on business logic (and business logic changes) and forwarded those to the DBA hoping to change her mind. It didn't. Finally I went down with the other developer to talk to her about it and ask her why she won't let us use surrogate keys. Her answer was "Because of my 8 years of DBA experience."

I was so mad I was shaking, but just said "OK." and walked away. Since I was a contractor I didn't push the issue any further as I try and stay out of politics when working on contract.

 

[s0me0nesmind1]

Learning SQL and learning how to optimize your SQL are 2 very different things IMO - and a lot of people are putting the 2 together.

 

[alkemyst]

There are a handful of basic commands but a ton of built in functions and system stored procs that can be used to make things perform better or simplify your code, such as being able to build and execute dynamic SQL in stored procs.

For me optimization was the most interesting part. Basically trying to take and combine as much work into a single query as possible, then using optimizer hints to improve performance by telling the optimizer which indexes to use when it wasn't using the correct ones, etc...

In one of my bigger projects I had a query that was probably a page and a half long with multiple levels of correlated subqueries and optimizer hints. I document the crap out of my code but even then it would take several minutes of study to understand the query again when I needed to change it. Sometimes performance is much more important than maintainability though.

Another project I worked on we had a clueless DBA that insisted we used natural keys (primary keys made up solely of real table data, not identity, sequence or autonumber type columns). So for one to many relationships the child table would contain the full primary key of the parent table plus the natural key for the child to make it's primary key. Some tables had 16 column primary keys due to this. Not only was it a nightmare to write queries for, nightly processing took forever to run. I found articles explaining how surrogate keys can be much better for performance and how natural keys are generally a mistake to use as primary keys since they're based on business logic (and business logic changes) and forwarded those to the DBA hoping to change her mind. It didn't. Finally I went down with the other developer to talk to her about it and ask her why she won't let us use surrogate keys. Her answer was "Because of my 8 years of DBA experience."

I was so mad I was shaking, but just said "OK." and walked away. Since I was a contractor I didn't push the issue any further as I try and stay out of politics when working on contract.

http://bigocheatsheet.com/

Know your algorithms/queries

 

[alkemyst]

Learning SQL and learning how to optimize your SQL are 2 very different things IMO - and a lot of people are putting the 2 together.

You pretty much have said it doesn't matter.

Are you learning more now on how it really does when you are dealing with big data heaps?

 

[ZaneNBK]

Been there, done that. There was one that they'd run at cob that still didn't finish until 8am the NEXT DAY. People would come in early and complain their computer wasn't working (due to the Front End application that tied to the database that was pegged) I developed a better statement that ran in like 20minutes and didn't bring things to a halt when it ran. The managers would just kick off the report themselves with any new data that they had their workers put in early on. As long as it was in by noon, it counted for that day not so important most of the time except on a end of a quarter that was craptastic and the last 24 hours made a huge gain.

Yeah, the same company with the 8 hours process also had a really AWESOME VB app that generated some spreadsheets based on the same database. That app took 30 minutes to an hour to run. When I looked at the code all the variables were named "x, y, z, crap, bs, stuff" etc.. and it was just straight pulling back all data from all the tables and combining it in code. D: The updated version of the app ran in a few seconds.

It amazed me how many times I see people pulling back data sets and then filtering them or aggregating them client side. The database is designed SPECIFICALLY FOR THAT! Argh!

I ported Moodle, an open source online training app, to MSSQL for use at a different company so we could integrate easily with other MSSQL systems. Turns out the reports in Moodle that do aggregation were doing it all in PHP instead of the queries, which is why they ran soooo sloooow. Hopefully they've fixed that since then, that was in the early 2000's.

 

[ZaneNBK]

Learning SQL and learning how to optimize your SQL are 2 very different things IMO - and a lot of people are putting the 2 together.

Advanced SQL knowledge should include knowing how to optimize. You may not need to know how to optimize the schema, as the company may have good DBAs that do that, but it helps.

Basic SQL knowledge is doing simple queries and aggregations and basic database design principles.

 

[alkemyst]

Yeah, the same company with the 8 hours process also had a really AWESOME VB app that generated some spreadsheets based on the same database. That app took 30 minutes to an hour to run. When I looked at the code all the variables were named "x, y, z, crap, bs, stuff" etc.. and it was just straight pulling back all data from all the tables and combining it in code. D: The updated version of the app ran in a few seconds.

It amazed me how many times I see people pulling back data sets and then filtering them or aggregating them client side. The database is designed SPECIFICALLY FOR THAT! Argh!

I ported Moodle, an open source online training app, to MSSQL for use at a different company so we could integrate easily with other MSSQL systems. Turns out the reports in Moodle that do aggregation were doing it all in PHP instead of the queries, which is why they ran soooo sloooow. Hopefully they've fixed that since then, that was in the early 2000's.

Damn, that is the worst. I was an ASP guy as well. A lot of ASP guys apparently don't understand the impacts SQL statements can have on a database.

Seeing stuff in a long loop like:

sql=SELECT firstname FROM EMPLOYEES;

FirstName=sql

sql=SELECT lastname FROM EMPLOYEES;

LastName=sql

etc.

Then seeing them again in the same code.

Build an array, get your data in code and model it.

 

[brianmanahan]

lol, who writes sql anymore? Why not just use jpa/linq/etc.

 

[Albatross]

Just curious how much a DB admin is payed in the USA?Or is all outsourced out the wazoo?

 

[ZaneNBK]

lol, who writes sql anymore? Why not just use jpa/linq/etc.

That works for the simple stuff, but when you get into large databases that are highly normalized then you'll likely be writing queries by hand, especially for aggregation of data from multiple tables and/or reporting.

It's also important to know for writing logic that resides on the database side in stored procs and triggers.

 

[alkemyst]

Just curious how much a DB admin is payed in the USA?Or is all outsourced out the wazoo?

DB Admin is a broad topic. Your certification makes a big difference

http://www.globalknowledge.com/training/generic.asp?pageid=3202&country=United+States

DB admin is both outsourced and in-house in the US.

The higher your skillset the more willing a company will hire you to sit on the bench even with only 25% utilization.

Great DBA's can save the day with a better report that matches legal criteria, makes the entire workforce more productive, detects losses in revenue, etc.

 

[skyking]

all this talk of security reminded me of little Bobby Tables
http://xkcd.com/327/

 

[Albatross]

DB Admin is a broad topic. Your certification makes a big difference

http://www.globalknowledge.com/training/generic.asp?pageid=3202&country=United+States

DB admin is both outsourced and in-house in the US.

The higher your skillset the more willing a company will hire you to sit on the bench even with only 25% utilization.

Great DBA's can save the day with a better report that matches legal criteria, makes the entire workforce more productive, detects losses in revenue, etc.

Thanks.

 

[sdifox]

all this talk of security reminded me of little Bobby Tables
http://xkcd.com/327/

Brings back great memories :biggrin: