CODEWITHSUNDEEP
sqlpostgresql
mastergoo
mastergoo

Reputation: 53

SQL, select in tables

Hey I don't know how to do it....

Its a homework: List the code and the consumers who bought cars only 'Argentina' country.

Table sell:

customer | resell  | veicle    |    date    |  value
---------+---------+-----------+------------+----------
 02      | 01      | 03        | 2010-02-05 | 17500.00
 04      | 02      | 01        | 2010-01-07 | 28000.00
 01      | 03      | 08        | 2010-02-15 | 28000.00
 02      | 03      | 02        | 2010-03-12 | 42000.00
 03      | 04      | 06        | 2010-02-06 | 11500.00
 03      | 02      | 05        | 2010-01-25 | 22100.00
 01      | 01      | 04        | 2010-01-21 | 15500.00

Table customer:

 cod    |    name    | lastname
--------+------------+------------
 01     | Jose       | Alves
 02     | Paulo      | Cunha
 03     | Maria      | DPaula
 04     | Joana      | Silveria

Table veicle:

 cod    |manufacturer|     model       | year |  country  |  price
--------+------------+-----------------+------+-----------+----------
 01     | 01         | Gol             | 2000 | Brasil    | 25000.00
 02     | 01         | Golf            | 2005 | Argentina | 39000.00
 03     | 04         | Ford Ka         | 1990 | Brasil    | 15000.00
 04     | 03         | Corsa Seda      | 1995 | Brasil    | 12500.00
 05     | 04         | Fiesta          | 2003 | Argentina | 20000.00
 06     | 03         | Corsa Seda      | 1995 | Argentina | 10000.00
 07     | 05         | Palio           | 2002 | Brasil    | 15000.00
 08     | 05         | Siena           | 2006 | Brasil    | 26000.00

I guess it to start:

SELECT customer.cod, customer.name 
FROM sell, customer 
WHERE (SELECT cod 
        FROM veicle 
        WHERE veicle.country = 'Argentina') = sell.veicle;

To select only the argentine cars... Please guide-me to the answer.

Upvotes: 0

Views: 128

Answers (5)

wildplasser
wildplasser

Reputation: 44240

WITH minima AS (
        SELECT DISTINCT s.customer
        , MIN(v.country) AS mi
        , MAX(v.country) AS ma
        FROM sell s
        JOIN veicle v ON s.resell = v.cod
        GROUP BY s.customer
        )
SELECT cu.* FROM customer cu
JOIN minima mm ON mm.customer = cu.cod
WHERE mm.mi = 'Argentina'
AND mm.ma = 'Argentina'
        ;

Upvotes: 0

Nishu Tayal
Nishu Tayal

Reputation: 20830

Try this :

 SELECT c.cod, c.name,c.last_name,v.model
 FROM sell as s JOIN customer as c JOIN veicle as v ON s.veicle = v.cod and s.customer = c.cod
 WHERE v.country = 'Argentina'

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

SELECT c.*
FROM   customer c
WHERE EXISTS (
    SELECT 1
    FROM   sell s
    JOIN   veicle v ON v.cod = s.veicle
    WHERE  s.customer = c.cod
    AND    v.country = 'Argentina'
    )
AND NOT EXISTS (
    SELECT 1
    FROM ...
    );

I'll leave the rest for you, as this is homework. Fill in for ... to exclude customers that have bought vehicles that are not from Argentina - very similar to the first EXISTS clause. If you have understood the first, you can complete the rest.

Upvotes: 2

P Varga
P Varga

Reputation: 20229

Try this SELECT DISTINCT customer.cod, customer.name || customer.lastname FROM customer, veicle, sell WHERE customer.cod = sell.customer AND veicle.cod = sell.veicle AND veicle.country = 'Argentina'

Upvotes: 1

Kyra
Kyra

Reputation: 5407

Look into table joins. That way instead of doing a subquery (what is in your where statement) you can instead join on the tables (kindof like your From clause but you should specify what it joins on) and then in your where you can just put veicle.country = 'Argentina'.

And FYI veicle is spelled wrong. It should be vehicle.

Upvotes: 0

Related Questions