[Careercup] 15.1 renting Apartment Rental

Write a SQL query to get a list of tenants who is renting more than one apartment.

--TABLE Apartments

+-------+------------+------------+|Aptid|Unitnumber|Buildingid|+-------+------------+------------+|   101 |A1|          1 ||   102 |A2|          2 ||   103 |A3|          3 ||   201 |B1|          4 ||   202 |B2|          5 |+-------+------------+------------+

--TABLE buildings

+------------+-----------+---------------+---------------+|Buildingid|Complexid|Buildingname|Address|+------------+-----------+---------------+---------------+|          1 |         One |Eastern Hills|San Diego, CA||          2 |         A |EastEnd      |Seattle, WA||          3 |         - |North Park|New York||          4 |         - |South Lake|Orlando, FL||          5 |         the |West Forest|Atlanta, GA|+------------+-----------+---------------+---------------+

--TABLE Tenants

+----------+------------+|TenantID|Tenantname|+----------+------------+|      + |Zhang San||     1001 |Li Si||     1002 |Wang Wu||     1003 |Yang Liu|+----------+------------+

--TABLE complexes

+-----------+---------------+|Complexid|Complexname|+-----------+---------------+|         One |Luxuary World||         A |Paradise||         - |Woderland||         - |Dreamland||         the |Lostparis|+-----------+---------------+

--TABLE Apttenants

+----------+-------+|TenantID|Aptid|+----------+-------+|      + |   102 ||     1001 |   102 ||     1002 |   101 ||     1002 |   103 ||     1002 |   201 ||     1003 |   202 |+----------+-------+

--TABLE Requests

+-----------+---------+-------+-------------+|RequestID|Status|Aptid|Description|+-----------+---------+-------+-------------+|         - |Rented|   101 |             ||         - |Pending|   103 |             |+-----------+---------+-------+-------------+

This problem let us rent more than one apartment, then we need two tables tenants and apttenants, other tables are not required, then we can use inner join to correlate two tables, about the various joins of SQL see my previous blog sql left Join, Right join, Inner join, and Natural join various join summaries, and then we also need to use the group by and Count keywords to represent tenantid that occur more than 1 times in the Apttenants table, Then find the name in the Tenants table to return:

Solution One:

SELECT  from Tenants INNER JOIN (SELECT from  apttenantsGROUP by has COUNT(*>1) Con= C.tenantid;

The following method specifies the same column tenantid with the Using keyword:

Solution Two:

SELECT  from Tenants INNER JOIN (SELECT from  apttenantsGROUP by has COUNT(*>1) cusing (TenantID);

Careercup all in one topic summary

[Careercup] 15.1 renting Apartment Rental