SQL Queries on Date function

Question

Post reply

SQL Queries on Date function

Saroj-747615

SSC Journeyman

Points: 76
More actions
June 24, 2008 at 5:48 pm

#122111

Below is the desc:-
Authors Books
Pk Author Pk ISBN
Nationality Author
Birthdate Title
First Language Publish date
Second Language Publish date
1)write a query to retrieve all the books which was published during jan 2007 and with title containing INDIA or published in language Hindi.
2)write a query to retrieve the authors who has not published any books during the year 2006?
3)write a query to retrieve the authors who has published books on their birthdates.write this query in 2 ways(a)using joins(b)using subquery
4)write a query to retrieve the authors and the number of books published by them,only those authors who have published more than 1 book.
5)Write a query to retrieve the number of books published each year in various languages.
Regards,
--Saroj

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Please read: ... http://www.sqlservercentral.com/articles/Best+Practices/61537/

Please note that your books table appears to have two columns with the same column name -

Books
Pk ISB
Author
Title
Publish date
Publish date

(refer to above link as to how to present your tables and/data so we can attempt to do something to help you)

Using my assumptions the following MIGHT work -

SELECT Authors.Author, Books.Title, Books.PublishDate1, Books.PublishDate2, Authors.FirstLanguage,

Authors.SecondLanguage

FROM Authors INNER JOIN Books ON Authors.Author = Books.Author

WHERE Books.Title = 'LIKE%India%' OR

((Authors.FirstLanguage = 'hindu') AND (PublishDate1 >= 1/1/07 AND publishdate1 <= 1/30/07)) OR

((Authors.SecondLanguage = 'hindu') AND (PublishDate2 >=1/1/07 AND publishdate1 <= 1/30/07))

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

Saroj (6/24/2008)
Below is the desc:-
Authors Books
Pk Author Pk ISBN
Nationality Author
Birthdate Title
First Language Publish date
Second Language Publish date
1)write a query to retrieve all the books which was published during jan 2007 and with title containing INDIA or published in language Hindi.
2)write a query to retrieve the authors who has not published any books during the year 2006?
3)write a query to retrieve the authors who has published books on their birthdates.write this query in 2 ways(a)using joins(b)using subquery
4)write a query to retrieve the authors and the number of books published by them,only those authors who have published more than 1 book.
5)Write a query to retrieve the number of books published each year in various languages.
Regards,
--Saroj

This is homework and while I certainly don't have a problem helping folks with homework, at least show us that you've tried... after all, we won't get the "A".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)