How to use a set algebra operation in SQL

user_12062972 0
Viewed 231

I need to find the distinct drinkers who like either BEER or RED WINE. However, the query must be implemented with a set algebra operation.

Here is what I have so far: 

SELECT DRINKER 
FROM LIKES 
WHERE DRINK = 'BEER' OR 'RED WINE';

This is not using a set algebra operation though. I'm really new to SQL so any help with this would be greatly appreciated.

Here is the table I'm getting data from: 

CREATE TABLE LIKES( /* Drinkers like drinks */
DRINKER     VARCHAR(30) NOT NULL,   /* Drinker name */
DRINK       VARCHAR(30) NOT NULL,   /* Drink name   */
RATING      DECIMAL(1)  NOT NULL,   /* Rating of the drink  */
    CONSTRAINT LIKES_PKEY PRIMARY KEY(DRINKER, DRINK),
    CONSTRAINT LIKES_FKEY1 FOREIGN KEY(DRINK) REFERENCES ALLDRINKS(DRINK),
    CONSTRAINT LIKES_DKEY2 FOREIGN KEY(DRINKER) REFERENCES DRINKERS(DRINKER));

As well as the table 'DRINKERS': 

CREATE TABLE DRINKERS ( /* All drinkers */
DRINKER VARCHAR(30) NOT NULL,
    CONSTRAINT DRINKERS_PKEY PRIMARY KEY (DRINKER));

INSERT statements: 

INSERT INTO LIKES VALUES('TOM', 'BEER', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'VODKA', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'RUM', 5);
INSERT INTO LIKES VALUES('JANUSZ', 'BEER', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'CHAMPAGNE', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'RED WINE', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'WHITE WINE', 5);
INSERT INTO LIKES VALUES('JANUSZ', 'PORT', 5);
INSERT INTO LIKES VALUES('PETER', 'CHAMPAGNE', 4);
INSERT INTO LIKES VALUES('PETER', 'COGNAC', 3);
INSERT INTO LIKES VALUES('PETER', 'RUM', 3);
INSERT INTO LIKES VALUES('PETER', 'WHISKY', 6);
INSERT INTO LIKES VALUES('MARY', 'CHAMPAGNE', 5);
INSERT INTO LIKES VALUES('MARY', 'VODKA', 1);
INSERT INTO LIKES VALUES('MARY', 'COGNAC', 4);
INSERT INTO LIKES VALUES('JOHN', 'CHAMPAGNE', 4);
INSERT INTO LIKES VALUES('JOHN', 'VODKA', 2);
INSERT INTO LIKES VALUES('JOHN', 'RUM', 6);
INSERT INTO LIKES VALUES('JOHN', 'WHISKY', 1);
INSERT INTO LIKES VALUES('JOHN', 'BEER', 6);
INSERT INTO LIKES VALUES('JAMES', 'CHAMPAGNE', 6);
INSERT INTO LIKES VALUES('JAMES', 'COGNAC', 5);
INSERT INTO LIKES VALUES('JAMES', 'RUM', 4);
INSERT INTO LIKES VALUES('SERGIEY', 'VODKA', 6);
INSERT INTO LIKES VALUES('SERGIEY', 'RUM', 6);
INSERT INTO LIKES VALUES('SERGIEY', 'CHAMPAGNE', 3);
INSERT INTO LIKES VALUES('CLAUDE', 'CHAMPAGNE', 6);
INSERT INTO LIKES VALUES('CLAUDE', 'WHITE WINE', 5);
INSERT INTO LIKES VALUES('CLAUDE', 'COGNAC', 4);
INSERT INTO LIKES VALUES('CLAUDE', 'WHISKY', 3);
INSERT INTO LIKES VALUES('CLAUDE', 'RED WINE', 6);

Desired output: 

DRINKER

JANUSZ
JOHN
TOM
algebra relational-algebra select mysql sql
2 Answers

UNION is a set algebra operation supported by MySQL in two forms: UNION and UNION ALL. The former returns only distinct values from the set, the latter all values (so with your sample data, would return JANUSZ twice since they like BEER and RED WINE). So, a set algebra like query to find drinkers who like BEER or RED WINE would be:

SELECT DRINKER
FROM LIKES
WHERE DRINK = 'BEER'
UNION
SELECT DRINKER 
FROM LIKES 
WHERE DRINK = 'RED WINE'

Output for your sample data:

DRINKER
TOM
JANUSZ
JOHN
CLAUDE

(Note that CLAUDE also likes RED WINE)

Demo on dbfiddle

You can add an ORDER BY clause to a UNION to order the final result. Without one, the order of results is not guaranteed. For example to sort alphabetically:

SELECT DRINKER
FROM LIKES
WHERE DRINK = 'BEER'
UNION
SELECT DRINKER 
FROM LIKES 
WHERE DRINK = 'RED WINE'
ORDER BY DRINKER

Output:

DRINKER
CLAUDE
JANUSZ
JOHN
TOM

Demo on dbfiddle

user_94737640

In standard SQL, Set Algebra operations are UNION, INTERSECT and EXCEPT. For your needs, I think UNION would be the best choice.

Since you did not specify what rating constitutes whether someone likes a drink or not, for this query we'll assume <=5 means they don't like it and >5 means they do like it.

SELECT DISTINCT DRINKER
FROM (SELECT DISTINCT DRINKER
      FROM LIKES
      WHERE DRINK="BEER"
      AND RATING>5
      UNION
      SELECT DISTINCT DRINKER
      FROM LIKES
      WHERE DRINK="RED WINE"
      AND RATING>5);

Using the assumption of "Likes" is a rating >5, you would get this output from your chosen INSERT statemements:

CLAUDE
JANUSZ
JOHN
TOM

You can read more about Set operations here:

https://en.wikipedia.org/wiki/Set_operations_(SQL)

user_55840370
Related