Is my answer correct

Question

1.00/5 (2 votes)

See more:

Write a query to display the names of the users who have posted posts and the contents of posts, sorted by name, then by post date and then by post content.

What I have tried:

SQL

selectuser.name,post.content from user
inner join post
on user.id=post.user_id
order by user.name,date(date);

Posted 30-Mar-16 16:31pm

Member 12427137

Updated 30-Mar-16 20:04pm

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 30-Mar-16 23:05pm

Which database system?

Karthik_Mahalingam 31-Mar-16 0:01am

HI PiebalDconsult,
From the Tag, it says SQL.

PIEBALDconsult 31-Mar-16 0:25am

That's a language; not a system.

Karthik_Mahalingam 31-Mar-16 0:31am

ok

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-03-30T20:04:00

Solution 1

Well, it needs a space between "select" and "user.name", and the terminating semicolon is redundant - but the only way to be sure is to try it.
And I'd prefer to see SQL keywords in upper case.

SQL

SELECT user.name,post.content FROM user
INNER JOIN post
ON user.id = post.user_id
ORDER BY user.name,date(date)

To complete the question though, you should be sorting by content as well, shouldn't you?

Posted 30-Mar-16 20:04pm

OriginalGriff

Comments

jaket-cp 31-Mar-16 6:17am

Just wandering why you would say "the terminating semicolon is redundant"?

OriginalGriff 31-Mar-16 6:33am

Because you don't need it! :laugh:
Semicolon in SQL is a terminator, that is normally only used as a separator if you have multiple statements in a single command (and most of the time, you don't need one there either).
You must use the semicolon if you use a Common Table Expression (CTE) or a Service Broker statement, and it's not the first statement in the batch.
I rarely use them, and for a single statement they are normally discarded.

jaket-cp 31-Mar-16 6:39am

Thanks for that :)
I try to put them in as good practice even though they are not required.
Maybe it is not good practice then :(

I read somewhere that the semicolon is required to terminate all statements in possible future releases of MSSQL server.

Let me see if I can find the link.

jaket-cp 31-Mar-16 6:42am

Here it is:

"Not ending Transact-SQL statements with a semicolon" has been put in the list of
Features Not Supported in a Future Version of SQL Server
https://msdn.microsoft.com/en-us/library/ms143729(v=sql.120).aspx

I have been recommending to possibly get in the habit of using the semicolon statement terminator for all statements.

Maybe I shouldn't be doing that anymore?

OriginalGriff 31-Mar-16 6:59am

I don't think they will do it - it would break too much existing code! :laugh:
Probably a good idea to get into the habit though, I guess.
Thanks for the link!

jaket-cp 31-Mar-16 7:05am

Yeah you are probably right, but after reading it, I started putting them everywhere.
And also because I started using cte more.
For some reason don't like the look of
;with
it just don't look right :)

CHill60 31-Mar-16 20:21pm

It does make your t-sql portable-ish to MySQL, oracle, others. Although I agree MS will probably not go down that line.
Not liking the look of ";with" thought ... :shakes head: ... it's my favourite thing :)

Member 12427137 2-Apr-16 3:10am

As per the requirement, there should be three columns based on which you need to sort the resultset whereas you have specified only two in the order by clause. HOW TO RECTIFY THIS ?

OriginalGriff 2-Apr-16 3:28am

Why are you shouting at me?