I have a relation Presidents(firstName,lastName,beginTerm,endTerm)that gives information about US Presidents. Attribute firstName is a stringwith the first name, and in some cases, one or moremiddle initials.
Columns derived in query cannot be used for Filtering through WHERE clause, though can be used in ORDER BY. See below order of execution, 1. CUBE ROLLUP 7. So, you can use a derived table or CTE. SQL USING Clause. If several columns have the same names but the datatypes do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an EQUIJOIN. USING Clause is used to match only one column when more than one column matches.
Attribute lastName is a string with the last name of the president. For example,the previous president has firstName = 'George W.' and his father has firstName = 'George H.W.'; both have lastName = 'Bush'. The last 2 attributes, beginTerm and endTerm, are the years the president entered and left office, respectively.
One subtlety is that Grover Cleveland served 2 noncontiguousterms. He appears in 2 tuples, one with the beginning and ending years of his first term and the other for the second term.
The question I have is below:
There are 2 pairs of presidents that were father and son. But there area number of other pairs of presidents that shared a last name. Find all the last names belonging to 2 or more Presidents. Do not repeat a last name, and remember that the same person serving 2 different terms (e.g., Grover Cleveland) does not constitute a case of 2 presidents with the same last name.
I first thought the answer might be:
I'm not too sure if the COUNT() function can be used in the WHERE clause though.Is this possible?
Thanks!
3 Answers
Use HAVING instead of WHERE when checking against Group functions.
However, when solving SQL-puzzles likes this, you should never take into account the actual data. It should work for all consistent> Not the answer you're looking for? Browse other questions tagged sqlcountwhere or ask your own question.