IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 1 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New SQL snobbery
A lot of people who just about live and breathe SQL and have for years have developed preferences for How It Is Done. I know I try to have a reason for the way I prefer to see SQL being written, but not everyone does.

All it might take is the SQL "expert" to dislike how you used (or didn't use) something like table aliases for it to be "unexpected".

Wade.
New Temp tables is my bet.
Somebody probably wanted to see a solution (or solutions) without using them. IME, most of the stuff that is done with temp tables can be done without them and for a lot of dba's I've known over the years, temp tables are a no-no.
New Re: Temp tables is my bet.
Probably... temp tables can be performance killers, except when they aren't.

I use a temp table only if the thing I'm doing is complex enough that the query optimizer can't handle it correctly, or if I'm reusing the results of an expensive query repeatedly.
Regards,
-scott
Welcome to Rivendell, Mr. Anderson.
New Setting up temp tables correctly can be difficult.
At least in MySQL it is where there is a distinct permission for creating temporary tables but not for modifying them (such as adding keys). :-/ If the only permissions you have are Select and Create Temporary Table, you have to create a temporary table, index it and fill it with data all in one statement.

That said, I am usually willing to explore options that require post-processing of the data. Some database programmers don't like that.

Wade.
New If you guys say "table" one more time...
...particularly if you're looking in the mirror, he will appear.

And tablize your face.
New :-)
New Only an uns*table* person would think of it! :)
Alex

"There is a cult of ignorance in the United States, and there has always been. The strain of anti-intellectualism has been a constant thread winding its way through our political and cultural life, nurtured by the false notion that democracy means that "my ignorance is just as good as your knowledge."

-- Isaac Asimov
New Ahh.. The Green Table, then: you're a balletomane..
New Here is the easiest question I've received on SQL
from one of the two SQL skills evaluations sent by potential employers as the first stage. First evaluation had 6 questions, the other had 10. If you complete them to their satisfaction, THEN you can progress to an actual telephone interview stage.

DDL to create the tables:

CREATE TABLE [dbo].[Asset] (
[asset_id] [int] NOT NULL ,
[issue_id] [int] NULL ,
[deal_id] [int] NULL ,
[purchase_date] [smalldatetime] NULL ,
[par_amount] [money] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[issue] (
[issue_id] [int] NOT NULL ,
[issuer_id] [int] NULL ,
[issue_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[security_type] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[defaulted] [tinyint] NULL ,
[default_date] [smalldatetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[asset] ADD
CONSTRAINT [PK_Asset] PRIMARY KEY CLUSTERED
(
[asset_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[issue] ADD
CONSTRAINT [PK_issue] PRIMARY KEY CLUSTERED
(
[issue_id]
) ON [PRIMARY]
GO

Populate the two tables:
/* insert issue data */
insert issue (issue_id, issuer_id, issue_name, security_type, currency_code, defaulted, default_date)
values (1,1,'Bond', 'B', 'USD', null, '2006-03-01 00:00:00')
insert issue (issue_id, issuer_id, issue_name, security_type, currency_code, defaulted, default_date)
values (2,1,'Term Loan A', 'B', 'USD', null, '2007-01-01 00:00:00')
insert issue (issue_id, issuer_id, issue_name, security_type, currency_code, defaulted, default_date)
values (3,2,'Revolver', 'B', 'USD', null, null)
insert issue (issue_id, issuer_id, issue_name, security_type, currency_code, defaulted, default_date)
values (4,3,'Term Loan B', 'B', 'USD', null, null)

/*Insert asset data*/
insert asset (asset_id,issue_id,deal_id,purchase_date,par_amount) values (1, 1, 1, '2006-01-31 00:00:00', 200000.0000)
insert asset (asset_id,issue_id,deal_id,purchase_date,par_amount) values (2, 2, 1, '2006-01-01 00:00:00', 300000.0000)
insert asset (asset_id,issue_id,deal_id,purchase_date,par_amount) values (3, 1, 1, '2005-12-15 00:00:00', 400000.0000)
insert asset (asset_id,issue_id,deal_id,purchase_date,par_amount) values (4, 1, 2, '2001-12-01 00:00:00', 3760000.0000)
insert asset (asset_id,issue_id,deal_id,purchase_date,par_amount) values (5, 3, 2, '2004-12-10 00:00:00', 50000.0000)

Now, here's the question:


"Write a stored procedure, Deal_compare, that compares issue par_amount for two different deals. The procedure takes two parameters, @deal_id_1, @deal_id_2 and returns the following fields: issue_id, issue_name, deal_1_par_amount, deal_2_par_amount, and par_difference. Each deal may invest in different set of issues. The result should represent all issues which are in either deal, and those that are in both should appear on one line."



I didn't see how to produce an answer without using a temp table.




Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous.
- - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
New Re: Here is the easiest question I've received on SQL
As in PostgreSQL.

:deal_id_1 and :deal_id_2 are the variables that would be procedure parameters.

This can be tested in psql by setting them so:

\set deal_id_1 1
\set deal_id_2 2


select s.issue_id,
ii.issue_name,
s.d1_par,
s.d2_par,
abs(coalesce(s.d1_par, 0.0) - coalesce(s.d2_par, 0.0)) as par_difference
from issue ii,
(select a.issue_id,
sum(d1.par_amount) d1_par,
sum(d2.par_amount) d2_par
from issue i join asset a on a.issue_id = i.issue_id
left outer join asset d1 on a.issue_id = d1.issue_id and d1.deal_id = :deal_id_1
left outer join asset d2 on a.issue_id = d2.issue_id and d2.deal_id = :deal_id_2
group by a.issue_id) s
where s.issue_id = ii.issue_id;

Regards,
-scott
Welcome to Rivendell, Mr. Anderson.
Expand Edited by malraux March 10, 2015, 11:20:47 AM EDT
New deal_1_par_amount, deal_2_par_amount not displayed
I modified the top SELECT statement because these 2 fields are requested to be displayed:

select s.issue_id, ii.issue_name,d1.par_amount as deal_1_par_amount,d2.par_amount as deal_2_par_amount, abs(coalesce(s.d1_par, 0.0) - coalesce(s.d2_par, 0.0)) as par_difference

[...]

However, T-SQL throws the following errors:

"Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "d1.par_amount" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "d2.par_amount" could not be bound."

because those two fields are in the nested SELECT.





Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous.
- - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
New Re: deal_1_par_amount, deal_2_par_amount not displayed
s.d1_par and s.d2_par are what you want. Note that they are already being used in the par_difference calculation.
Regards,
-scott
Welcome to Rivendell, Mr. Anderson.
New FWIW I hate overly-terse table names like that
"i" "ii" and "s" are plenty easy to type, and they're probably the initials of the logical table identifier in your head, but I picked different names and the short names you used make it hard for me to read.
--

Drew
New Eh.
Not what I would use in actual SQL.

And actually, I so rarely write raw SQL these days that I couldn't care less what they're named. That's what the ORM is for.
Regards,
-scott
Welcome to Rivendell, Mr. Anderson.
New Ignorant question
I've never used a SQL generating tool or OR mapper. The last time I looked at them, the ones that were available to me (meaning free/OSS) generated nasty unreadable crap.

So, have the tools gotten so good that writing SQL by hand isn't really needed any more? Or are they just good enough, often enough that you take what they give you and only dig into the SQL when the profiler has a problem it can't fix?
--

Drew
New I use Django's ORM these days
On rare occasions I may come across a reporting query that I want to run that isn't easily represented by the ORM. Additionally, I only look at the generated SQL when there's a problem to debug, which is pretty rare.

Customer.objects.filter(status=Customer.STATUS.active, subscription__cancelation_date__lte=some_date)


SELECT * from customer INNER JOIN subscription ON subscription.customer_id = customer.id
WHERE customer.status = 'active' AND subscription.cancelation_date >= :some_date:;


A very large benefit of using the Django ORM, beyond the automatic CRUD operations and query generation, is the database migration system. I make a change to the class, like adding a new CharField, and run a schemamigration command that creates a migration script with forward and backward migrations. In each of my environments, the script runs and automatically applies the migration if it hasn't been already.

Our deployments are 100% automated including database changes.
Regards,
-scott
Welcome to Rivendell, Mr. Anderson.
New The tools have definitely gotten better, then
I actually worked on two different DB abstraction classes for PHP before the PEAR one was good enough to switch to it. Now, that's one of those things that you'd be a fool to write yourself.

Of course, that just abstracts the connections and the basic operations. You still write your own SQL.
--

Drew
New PHP ORMs are execrable.
We used Doctrine here... terrible.

I've been using the Django ORM for years, since 2009 or so. It hasn't changed much... PHP is just full of crap libraries, IMO.
Regards,
-scott
Welcome to Rivendell, Mr. Anderson.
New Only PHP library I care about now is WordPress
Yeah, it's a beast, but it does most of what I need.
--

Drew
New This is unfortunately true.
Back when PEAR was in its infancy, I looked at what was on offer and all were shit. I wrote my own DB handler and my own ORM on top of it. Have re-implemented both a few times at successive jobs.

I wold posit that many PHP programmers just don't really understand how to write scalable, memory-efficient code. Sometimes you just can't give your script 2Gb of memory.

Wade.
New Newbie ORM question.
Are all connections to the database made in the same user context? Here's why I'm asking. Way back in the mid-1990's I worked in MS Sql Server transaction environments where *no one* had access to base tables. Insert/Update/Delete was done exclusively through stored procedures and selects were done exclusively on stored procedures or views (there was a ton-o-regulation in this particular industry and still is - even every select had to be logged with timestamp and current_user). I haven't been coding in quite some time and having virtually no knowledge of the "proper" way to use ORM. How is the data security I was introduced to 20 years ago maintained now? I mean, you can't give every user of some application select/update/delete permissions on the base tables, right? Are the apps themselves responsible for all the logging of user data access?
New Depends on the ORM, naturally
Generally speaking application servers will use a single or low number of database users, by context. That is, you may have a single application user named 'app' with CRUD permissions on all of the application tables. This enables connection pooling. If there are audit requirements, they can be built into the application, which is a trusted party in this mode of operation.

If that's not sufficient, then any connection pooling framework, ORM or otherwise, may not be indicated. Generally speaking only internal applications require this level of scrutiny, and a public-facing ecommerce site (or extranet situation) won't need it.

Note that Oracle has a means of operation that enables tagging of connections with audit information passed through from the application. This is another option if your RDBMS supports it.
Regards,
-scott
Welcome to Rivendell, Mr. Anderson.
New Thanks.
     Interesting - (lincoln) - (31)
         Re: Interesting - (malraux) - (1)
             Re: Interesting - (lincoln)
         SQL snobbery - (static) - (22)
             Temp tables is my bet. - (mmoffitt) - (6)
                 Re: Temp tables is my bet. - (malraux) - (5)
                     Setting up temp tables correctly can be difficult. - (static)
                     If you guys say "table" one more time... - (pwhysall) - (3)
                         :-) -NT - (scoenye)
                         Only an uns*table* person would think of it! :) -NT - (a6l6e6x)
                         Ahh.. The Green Table, then: you're a balletomane.. -NT - (Ashton)
             Here is the easiest question I've received on SQL - (lincoln) - (14)
                 Re: Here is the easiest question I've received on SQL - (malraux) - (13)
                     deal_1_par_amount, deal_2_par_amount not displayed - (lincoln) - (1)
                         Re: deal_1_par_amount, deal_2_par_amount not displayed - (malraux)
                     FWIW I hate overly-terse table names like that - (drook) - (10)
                         Eh. - (malraux) - (9)
                             Ignorant question - (drook) - (8)
                                 I use Django's ORM these days - (malraux) - (7)
                                     The tools have definitely gotten better, then - (drook) - (3)
                                         PHP ORMs are execrable. - (malraux) - (2)
                                             Only PHP library I care about now is WordPress - (drook)
                                             This is unfortunately true. - (static)
                                     Newbie ORM question. - (mmoffitt) - (2)
                                         Depends on the ORM, naturally - (malraux) - (1)
                                             Thanks. -NT - (mmoffitt)
         Well, they didn't like my using tables - (lincoln) - (5)
             Unlucky - (pwhysall)
             Re: "more technical" - (a6l6e6x) - (3)
                 Oh, it gets even better - (lincoln) - (2)
                     I've taken coding tests for executive positions - (malraux)
                     I used to be on the other side - (crazy)

Because you should never underestimate the unoriginality of the very stupid.
256 ms