SQL - UNION, UNION ALL, INTERSECT, EXCEPT

Question

I am learning SQL with GalaXQL and can't figure out the following question:

Build a query which returns starids from planets. The starids should be selected so that for each starid (x) in the list: - there should exist a planet with a starid that's three times x but - there should not exist a planet with starid two times x. Only use starid:s from the planets table.

What would be a query that would accomplish this using UNION, UNION ALL, INTERSECT, EXCEPT? Please digest this in your answer as I am a beginner. Thank you in advance!

Here is the database schema:

CREATE TABLE stars (starid INTEGER PRIMARY KEY,
                    name TEXT,
                    x DOUBLE NOT NULL,
                    y DOUBLE NOT NULL,
                    z DOUBLE NOT NULL,
                    class INTEGER NOT NULL,
                    intensity DOUBLE NOT NULL);

CREATE TABLE hilight (starid INTEGER UNIQUE);

CREATE TABLE planets (planetid INTEGER PRIMARY KEY,
                      starid INTEGER NOT NULL,
                      orbitdistance DOUBLE NOT NULL,
                      name TEXT,
                      color INTEGER NOT NULL,
                      radius DOUBLE NOT NULL);

CREATE TABLE moons (moonid INTEGER PRIMARY KEY,
                    planetid INTEGER NOT NULL,
                    orbitdistance DOUBLE NOT NULL,
                    name TEXT,
                    color INTEGER NOT NULL,
                    radius DOUBLE NOT NULL);

CREATE INDEX planets_starid ON planets (starid);
CREATE INDEX moons_planetid ON moons (planetid);

Here is my query:

SELECT planets.starid
FROM planets
UNION
SELECT starid*3 FROM planets
EXCEPT
SELECT starid*2 FROM planets

Answer 1

Refer below sample queries for all operators.

NOTE: The MINUS operator is not supported in MS SQL Server. It is equivalent to EXCEPT operator in SQL Server.

SELECT name FROM Employee    /* Fetch the union of queries */
UNION
SELECT name FROM Contacts;

SELECT name FROM Employee    /* Fetch the union of queries with duplicates*/
UNION ALL
SELECT name FROM Contacts;

SELECT name FROM Employee    /* Fetch names from Employee */
EXCEPT                       /* that aren't present in contacts */
SELECT name FROM Contacts;

SELECT name FROM Employee    /* Fetch names from Employee */
INTERSECT                    /* that are present in contacts as well */
SELECT name FROM Contacts;

Answer 2

for more details check link this help me

Answer 3

Easiest is to start with exists/not exists:

SELECT starid
FROM planets p1
WHERE EXISTS (
    SELECT 1 
    FROM planets p2
    WHERE p2.starid = 3*p1.starid
)
AND NOT EXISTS (
    SELECT 1 
    FROM planets p3
    WHERE p3.starid = 2*p1.starid
)

If you want to express this in terms of union/intersect, let A be all starids from planet, and let B be the starids * 3. Since both of these must exists we are talking intesection

SELECT starid
FROM planets
INTERSECT
SELECT 3*starid
FROM planets

From that set we want to exclude starids * 2. That is all elements but starids * 2. This is known as EXCEPT or MINUS:

SELECT starid
FROM planets
EXCEPT
SELECT 2*starid
FROM planets

Since the result should belong to both of these sets we once again apply intersection:

(SELECT starid
FROM planets
INTERSECT
SELECT 3*starid
FROM planets)
INTERSECT
(SELECT starid
FROM planets
EXCEPT
SELECT 2*starid
FROM planets)

Did that help?