-1

I need to query the number of apartments where all rental contract are signed by occupants from the same nationality

I tried something like this: 

π numberapartments
y nationality; numberapartments<--Count(a_id)

And I also need some joins somewhere, I don't know.

How could I do this query?

Thanks.

You can find the schema here

  • There are many RAs (relational algebras).They differ in operators & even what a relation is. Give a reference and/or definition for yours. Eg textbook name, edition & page. Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Make your post self-contained. – philipxy Apr 13 '19 at 17:54
  • 1
    Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. What parts are you able to do? Where are you stuck? What did your textbook or the documentation say about anything relevant? Please in code questions give a [mcve]--cut & paste & runnable code plus desired output plus clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS RA is a programming language--even if you are working by hand. PS Google 'execute relational algebra online'. – philipxy Apr 13 '19 at 18:22
  • 1
    What question are you trying to ask about count? It is not "How could I do this query?". PS Queries that use "where ...all..." usually involve relational division. Which your textbook will have told you in the sections that apply to the lectures you had before getting this assignment. Did you read it? Tag: [tag:relational-division] [Re relational algebra querying.](https://stackoverflow.com/a/24425914/3404097) PS "And I also need some joins somewhere, I don't know." That is not helpful. It is a vague version of practically every question tagged [tag:relational-algebra] or [tag:sql]. – philipxy Apr 13 '19 at 20:01
  • 1
    Relational queries return relations. But you say you want "I need to query the number of apartments where ...". What do you actually want returned? Also, your query specification is not clear about "all": if an apartment has no rentals signed, are "all rental contract signed by occupants from the same nationality" for that apartment? This is a classic natural-language ambiguity discussed in the context of relational division. – philipxy Apr 14 '19 at 09:07

1 Answers1

2

Here are some questions to guide you through a composition of a query like this homework.

When giving tables say exactly what a row says about the business situation in terms of its column values when it is in the table. Also when describing a query result.

What is a query returning rows where

  1. occupant O rents apartment A from date S to date E? Why?

  2. O rents A from a date to a date? Why?

  3. O rents A? Why?

  4. O from nation N rents A? Why?

  5. an occupant from N rents A? Why?

  6. C = the # of nations where an occupant from one rents A? Why?

  7. C = the # of nations where an occupant from one rents A AND C = 1? Why?

  8. a # = the # of nations where an occupant from one rents A & that # = 1? Why?

  9. (the # of nations where an occupant from one rents A) = 1? Why?

What rows are in

  1. Rental?

  2. Occupant?

  3. the result of your desired query? Why?

Re relational querying.

It isn't actually necessary to use counting or grouping to write your query. Such queries that are of the form "rows where … all …" can typically be written using (some variant of) relational division or associated idioms.

philipxy
  • 14,867
  • 6
  • 39
  • 83