|
Hello- can you give me any example scenarios/questions for writing a complex SELECT statements? My intention is to improve my SQL skills.
|
|
|
|
|
|
|
Sure, try implementing a Turing Machine in SQL.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
|
data modeling guy wrote:
Hello- can you give me any example
scenarios/questions for writing a complex SELECT statements? My intention is to
improve my SQL skills.
- Calculate reputation points of a user.
- Fetch the contibutions-section for a user (see the section on your user-page; it contains some statistics)
- Fetch the ten members from the database that answered most questions last month.
- Fetch the voting-percentages for the straw-poll result for all possible answers.
- Fetch any low-repuation member that posts the same message in multiple places, ordered by the amount of similar messages.
- Fetch the data to show the forum-content for page 3 of this forum, in threaded view, with 50 posts per page.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
No no no these suggestions expect him to do the work and write the queries, it sounds like he wants YOU to write the queries and he will study them.
Good luck getting CM to lend out the database and schema
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Eddy. I Did some more google search and found some more. Cheers!
|
|
|
|
|
|
I tried this
select convert(datetime, 41818) as mydate
result
2014-06-30 00:00:00.000
In Excel, it should be
6/28/2014
It's always 2 days in advance
Please help!
Thanks a lot
|
|
|
|
|
Why do you think it's wrong?
SELECT DATEDIFF ( dd , '1900-01-01' , '2014-06-30' )
SELECT DATEADD ( dd, 41818 , '1900-01-01' )
Miller Nguyen wrote: In Excel, it should be
What do you mean "it should be" ?
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
I don't say it's wrong, that's why I put the QUESTION MARK in the topic.
I just wonder why Excel and SQL are giving different result
|
|
|
|
|
What the f...? You are right, I tested it.
Look at day number 0: with SQL Server, it is Jan 1, 1900; with Excel: Jan 0 , 1900.
The next bug is the leap year: Excel treats 1900 as a leap year (that's wrong!), while SQL Server correctly knows that 1900 is not a leap year.
In sum, those differences account for the 2 days difference in current dates.
|
|
|
|
|
|
Yeah, as I drifted off to sleep last night I recalled that Joel had written about Excel having to support a Lotus 123 issue.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
I love that story.
They brought a f*** counter to all meetings with BillG.
|
|
|
|
|
See my answer to Bernhard
|
|
|
|
|
|
Oh, btw, have a look if this[^] and this[^] would be of any use for you.
|
|
|
|
|
For example, -0.5 and 0.5 both mean noon on 30 December 1899 No, no, no! I do not want to use that function!
|
|
|
|
|
It's not a bug, it's a feature.
|
|
|
|
|
I get around this, and other issues such as collation issues between SQL Server and Excel, by always passing dates to Excel as text in a format of dd-MMM-yyyy e.g. '01-Jan-2014'.
It's not pretty but it has worked so far...
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 30-Jun-14 8:06am.
|
|
|
|
|
Hi all;
I am learning to write stored procs on MySQL and am going crazy.
DELIMITER $$
DROP PROCEDURE IF EXISTS CleanCopyEnvData$$
CREATE PROCEDURE CleanCopyEnvData ()
BEGIN
insert into EnvData(UserDate, XAction, Balance, UserID)
select
CAST(udate as Date)
, CAST(amount1 as Decimal(6,3))
, CAST(amount2 as Decimal(6,3))
, CAST(UID as UnSigned)
from
DataLoad;
END $$
DELIMITER ;
I get an error:
Error starting at line : 17 in command -
END $$
Error at Command Line : 17 Column : 1
Error report -
SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END $$' at line 1
I am using Oracle SQL Developer and the INSERT statement works ok when executed on its own. I can do these things blindfolded with my arms tied on MSSQL. Any help appreciated.
EG.
|
|
|
|
|
You don't need the delimiter expression $$ after your END statement.
DELIMITER $$
DROP PROCEDURE IF EXISTS CleanCopyEnvData$$
CREATE PROCEDURE CleanCopyEnvData ()
BEGIN
insert into EnvData(UserDate, XAction, Balance, UserID)
select
CAST(udate as Date)
, CAST(amount1 as Decimal(6,3))
, CAST(amount2 as Decimal(6,3))
, CAST(UID as UnSigned)
from
DataLoad;
END
Everyone dies - but not everyone lives
|
|
|
|
|