SQL Puzzle (DB)

I am sure a typical mind will think of group by for [1] & [2] any other approach ?...

[1]Find all customers who have at least two accounts at the AmoKio
branch.
Given:
MemberAccount(Account_Number, BranchName, DateCreate)
Reader(Account_Number,Name,Address)

select distinct T.name
from reader as T
where not unique (
select R.name
from MemberAccount, reader as R
where T.name = R.name AND
R.account_number = MemberAccount.account_number and
MemberAccount.branch_name = ‘AmoKio’)

[2]Given ActedIn(actorName, movieTitle)

Write a query to find the names of actors who acted in at least two different movies.

select A.actorName
from ActedIn as A, ActedIn As B
where A.ActorName=B.ActorName and A.movieTitle<>B.MovieTitle

[3]Find the company that has the smallest payroll:

Select COY_NAME
FROM works
GROUP BY COY_NAME
HAVING SUM(salary) <= all (SELECT sum(Salary) FROM works GROUP BY COY_NAME)

[4]Find the names of Singers who are male or appear in "AmericanIdol".

TvProgram(Program, Channel , Singer)
Singer(name, address, gender, birthdate)

You might be tempted to write that the correct answer is

SELECT name
FROM Singer, TvProgram
WHERE (name=Singer AND Program='AmericanIdol') OR gender='M'


But this is not correct because if TvProgram is empty then we'll get an empty answer even if there are male Singer. The correct answer is

SELECT name FROM Singer WHERE gender='M'
UNION
SELECT T.Singer as name FROM Singer, TvProgram as T
WHERE name=T.Singer AND Program='AmericanIdol')

[5] What is the second highest points earned by a player ?
Player (id,name,points,difficulty)


What are you thinking ? Using Aggregate function max ? then use top ...


SELECT id
FROM Player as A
WHERE 1= (SELECT count(*) FROM Player as B
WHERE A.points '<' B.points


If really get it you can solve the next one ....


[6] What is the ten most highest points earned by a player ?
Player (id,name,points,difficulty)

SELECT id
FROM Player as A
WHERE 9 = (SELECT count(*) FROM Player as B
WHERE A.points '<' B.points

[7]Given :

CREATE TABLE ABC (
F int not null,
U int not null,
N int not null,
Man int not null).

Using SQL, display 1 if Table ABC obey the functional dependency FUN->Man,
and any other result beside 1 if it does not.




For a ABC table like

F U N Man (invalid)
---------------------
f1 u1 n1 Man1
f1 u1 n1 man2


F U N Man (valid)
---------------------
f1 u1 n1 man1
f2 u2 n2 man2

Note: a suggested answer not tested

(SELECT Distinct A.F,Distinct A.U,Distinct A.N,Man
FROM ABC ) as Record

(Select Count(*) as C from ABC) As CountABC

(Select Count(*) as C from Record) As CountRecord

Select A.C=B.C From CountABC as A,CountRecord as B


[8] Given A(ID, Name,DOB,Occupation)
What is the difference between count(*) and count( Occupation) ?

The Aggregate functions(sum,avg,max..) expect count ignore null values in their computation.Count (*) will give all results of all records inclusive of occupation that are null

while ...

Count(Occupation) will only compute values that are not null.