How Would You Improve SQL? 271
theodp asks: "It was the best of languages, it was the worst of languages. SQL's handy, but it can also drive you nuts. For example, if you want all 100 columns from a table, 'SELECT *' works quite nicely. However, if you want all but 1 of the 100 columns, be prepared to spell out 99 column names. Wouldn't it not make sense to provide a Google-like shorthand notation like 'SELECT * -ColumnName' (or DROP=syntax like SAS)? So how would you improve SQL?"
Check out LINQ... (Score:3, Informative)
http://msdn.microsoft.com/netframework/future/lin
LINQ (Language Integrated Query) project for c# 3.0. Some cool stuff tht i never really thought about.
For example, their select statements go backwords ie from table, select column1, n2, n3 etc... Seems kinda wacky at first, but it makes sense since you really should know what table your'e selecting from before you specify the columns.
ex.
public void Linq3() {
List products = GetProductList();
var expensiveInStockProducts =
from p in products
where p.UnitsInStock > 0 && p.UnitPrice > 3.00M
select p;
Console.WriteLine("In-stock products that cost more than 3.00:");
foreach (var product in expensiveInStockProducts) {
Console.WriteLine("{0} is in stock and costs more than 3.00.", product.ProductName);
}
}
UPDATE, INSERT look different (Score:3, Informative)
Fundamentally, though, what we need is much better interfaces to our applications. Having to convert C++ data into some format appropriate for SQL and back again is a pain in the behind. Every existing interface between the two that I've seen is crap. Heck, most of the C++ interfaces don't even let you use the std::string type. What are we, back in 1995? Forget vectors and maps, or the fancy boost multiindexed templates. Anyway, these really aren't problems for SQL to solve. And yes, there are object-oriented databases and the like. But relational databases are still pretty much universal. I just mention it because it is a pain in the behind.
Re:Be practical (Score:5, Informative)
If you really can't stand to write queries containing the actual column names, you should be using some type of abstraction layer in whatever language you're writing your code in.
If you're not writing code and just making queries by hand to test the results, then you're even further off your rocker. (this also applies in general to the statement you made) Why would you ever NOT want to select that last value out of 100? is it going to keep your output from wrapping? (lol)
Also, those of you saying that you should never have 100 columns in your table, you're certifiable lunatics as well. If you have 100 columns that are used in every record and have very little or no duplication per row, there is no reason you should break this up into multiple tables!!! Then the database has to do joins, which again require more processing power and disk usage. It's also hard to maintain multiple tables when you really have one table after you normalize it.
For those of you that say this isn't normalized... I'm not even really sure how to answer that.... If you have several tables all with a strict 1:1 relationship, they should be in ONE table. Anything else is considered denormalized, not yet normalized. (aside from being just plain BAD!)
For those of you that say you'd never need that many columns in one table or split across multiple tables, however you'd like to think the world should work. I have an example of just that. My wife does genetic research, primarily statistical analysis of sequence data (in various forms, but that's the easiest way to sum it up). We've had discussions on this particular topic, where she had been told by someone else that she would get better performance in Oracle if she split her one table into several tables containing a smaller number of columns, each.
This is just simply not true. It also is a perfect example of a situation where you would actually need a large number of columns. There were specific bits of data that needed to be looked up quickly (like, 45'ish). You can't store it all in one column (or even just a few) and use regexes to find the bits you're looking for. You also don't want to be doing a lot of joins unless you really need to, you know.. when you actually have data that would fit into some form of normalization. Technically, you CAN do this stuff, but not if you want decent performance. If you didn't want decent performance, you could just leave the data in a text file and shell out a grep command. *sigh*
Anyway, enough ranting, but seriously people... Get a clue. Get some experience with these issues. Don't just pipe up because "hey, I've worked with databases and while I probably don't understand them very well, I don't know anybody else that understands them at all, so I'm kind of an expert!"
Re:Infuriating (Score:3, Informative)
For other DBs that can use triggers and sequence generators (Oracle, PostGres, Interbase/Firebird), it can be a bit easier, as these DBs have ways to query the sequence generator for its current or next value (as long as you're still in the same transaction scope). In the case of Oracle, getting the Next value off of a sequence increments the sequence, so as long as you hold onto that value, it's going to be unique. No more silly "select max(id) from my table" queries after you insert a record...
In both cases, though, it generally requires some way that can peek at the record being added, in a state where it's "added" to the table but before it's locked down. Generally the most expedient method to do this is with a stored proc/function that has the actual INSERT statement in it, but returns the autogen'd field value, however you can get it, and return it to the layer you called the proc from.
It all depends on your database and your database access layer (e.g., ADO, ODBC, OleDB, direct-to-driver, etc).
MIDAS (database caching layer from Borland 5 Enterprise and later) really does this well. There are other similar products as well.
Re:Drop it for something relational (Score:3, Informative)
Not for those who haven't learnt data fundamentals.
By defining and manipulating relations.
It is not. Lisp is, Scheme, Haskell, not SQL. Never was, nor intended to be.
SQL tables are not relations. The very words relation and relational have been dropped from the ISO SQL standards since 1999 at least.
Re:Better NULL handling? (Score:3, Informative)
And you're completely misunderstanding what they're saying.
I'm talking about a list of the questions, and whether or not they are completed on a main page. Simple as that.
If you're just displaying a list of questions that's not gonna change, why are you using SQL at all? Why are you not simply using a flat text file?
my way is quick and to the point
And wrong.
Re:Infuriating (Score:2, Informative)
insert into tab (col1, col2, col3,
returning expr1, expr2,
into var1, var2,
or
update tab
set col1=v1, col2=v2, col3=v3,
returning expr1, expr2,
into var1, var2,
Re:Better NULL handling? (Score:3, Informative)
Well from my experience, breaking up the questionaire into seperate tables does accomplish making maintenance of the app simpler. Which depending on whether or not you want to be stuck supporting the same dumb app for the rest of your life or not, is important.
Now you auto generate your questionaire based on a Questions table... and as an added bonus when someone decides they really need to ask Question #101, they can input it through a maintenance screen.
Your way, in order to add a new question, you have to modify the application.
Granted, querying the results get's to be a bit more complicated. You have to "pivot" the data into a matrix. It's not terribly hard to do. You can do it in your app code, or using a stored procedure you can parse the data and send back one answer per column.
A lot of reporting tools have this kind of function built in, so it's not that hard to utilize.
some solutions (Score:2, Informative)
in MySQL, the statement REPLACE INTO will perform an update or an insert, depending on whether the primary key value exists in the table. It performs exactly like your WRITE command would.
in MySQL, you can perform a SELECT LAST_INSERT_ID() to get the last inserted value.
in MSSQL, use a SELECT @@IDENTITY to get the same. (check in the docs whether you need @@IDENTITY OR @SCOPE_IDENTITY or the third version, I always forget).
Re:Better NULL handling? (Score:4, Informative)
No, it simplifies them. RDBMS's make it very easy to do things on a row basis, not so much on a column basis. It is utterly trivial to get "usefull" [sic] data out of a table structure that's properly designed, but nightmarish to get it if it's not. For instance, if you have 100 columns how do you answer the question "what percentage of respondants answered at least 80% of all questions?". You can't easily. And if you add or remove a question then you will have to touch a great deal more code than if you had implemented the table sanely in the first place.
he OPs point stands - these are not normalization issues
Yes they are! This is practically a poster child of normalization (or lack thereof). In fact it's an utterly trivial example of normalization that doesn't actually raise any difficult issues over speed, accessibility, etc. where you often run into problems with normalization.
is a real weakness of the relational model compared to OO design
There's a vast difference between relational databases and object oriented databases. I've yet to see anyone (including Oracle) do objects-in-relational decently. And SQL is inherently a relational paradigm -- you wouldn't want to use it for an OODB because it would just be inappropriate.
Re:Problem Not SQL, It's DB Implementations (Score:2, Informative)
SELECT * FROM table LIMIT y OFFSET x
That would make it even usable with PostGreSQL and most DB, since the x,y is a mysql extention.