0

I'm very new to all SQL query language and SQL databases in general So after digging for an eternity seems i still can't solve these

I have a set of tables

PERSON(SSN, Name, Surname, BirthDate)
EVALUATION(CodE, Date, City, CompetitionType)
BID(SSN, CodE)
RANKING(CodE, Position, SSN )

And I need to make and run these 3 queries

1) Show the type of evaluations that, during January 2010, always took place in the same city.

2) For each person that bid for evaluations of type 'Level 3 employee', but does not appear in the final ranking, show SSN, name, surname, and the code of the evaluations for which she does not appear in the ranking.

3) Show the cities where there took place at least 10 evaluations of the same type, each having at least 50 bids.

So far I've tried

1) 

SELECT E.CompetitionType
FROM Evaluation E
WHERE E.Date>="01.01.2010" and E.Date <="31.01.2010"```

// I don't know how to check for the same cities

2)

SELECT P.SSN, P.Name, P.Surname, E.CodE,
FROM PERSON P

And after that just blank...

3)

SELECT *
FROM EVALUATION E
JOIN BID where BID.CodE = E.CodE
HAVING COUNT(*) > 50

And again can't even check for the same type

Updated to show what i've tried so far

Jay
  • 29
  • 1
  • 7
  • 3
    What have you tried so far? You are supposed to show the effort you made before asking. – GMB Jun 07 '20 at 18:33
  • Could you add whatever SQL queries you came up with for the 3 questions? Don't worry about its correctness. It'll be great to see what you have attempted. You might be closer to the solutions than you think. – zedfoxus Jun 07 '20 at 18:34
  • Are you trying to solve this using only SQL or do you intend to use any other language like PHP with it? – Ahijit Jun 07 '20 at 18:38
  • Thank you for quick response! I updated the question, with queries that i've tried – Jay Jun 07 '20 at 18:41
  • @Ahijit For now just lear SQL by it self, and later maybe use in with NodeJS or Python, Coming from NoSQL Database such as MongoDB found SQL quite hard... – Jay Jun 07 '20 at 18:44
  • For #3, `GROUP BY evaluation.city` (I tend to keep table names / field names lowercase). Start by running a simple select query (helps to have an actual DB set up!), and work your way from there, morphing your query as you need. You'll get better at it as you pick up the concepts behind the queries. – Rogue Jun 07 '20 at 18:48

1 Answers1

1

Question 1 is a straight aggregation query, with filtering in a having clause

select competitionType
from evaluation
where date >= '2010-01-01' and date < '2020-01-01
group by competitionType
having min(city) = max(city)

I would phrase query 2 with a join, and not exists:

select p.ssn, p.name, p.surname, b.code
from person p
inner join bid b on b.ssn = p.ssn
where not exists (select 1 from ranking r where r.ssn = b.ssn and r.code = b.code)

As for query 3, you could start from evaluation, flter with a correlated subquery on bid in the where clause, then aggregate by city and competition and filter with a having clause:

select e.city, e.competitionType
from evaluation e
where (select count(*) from bid b where b.code = e.code) >= 50
group by e.city, e.competitionType
having count(*) >= 10
GMB
  • 216,147
  • 25
  • 84
  • 135
  • In Question #1 Can i Group by City? Without "Having" – Jay Jun 07 '20 at 18:54
  • Good practice is to use UPPER CASE FOR SYNTAX: `SELECT column_name FROM table_name AS tn WHERE column_name='value';`. – John Jun 07 '20 at 18:55
  • @John I've always thought that upper casing keywords is a matter of preference. With IDEs highlighting keywords, it is easy to know what's keywords and what's not for the most part. I find it faster to type in lowercase; just my preference. https://stackoverflow.com/questions/292026/is-there-a-good-reason-to-use-upper-case-for-sql-keywords has some good debate surrounding that. – zedfoxus Jun 07 '20 at 19:04