Sheet 5 - Problem 4
BME B59 🫡itsLu
sql
a year ago
2.8 kB
32
Indexable
--Creating table: CREATE TABLE nobel_win (YEAR INT NOT NULL, SUBJECT VARCHAR(30) NOT NULL, WINNER VARCHAR(30) NOT NULL, COUNTRY VARCHAR(30) NOT NULL, CATEGORY VARCHAR(30) NOT NULL); --Populating nobel_win table: INSERT INTO nobel_win VALUES (1970, 'Physics', 'Hannes Alfven', 'Sweden', 'Scientist'); INSERT INTO nobel_win VALUES (1970, 'Physics', 'Louis Neel', 'France', 'Scientist'); INSERT INTO nobel_win VALUES (1970, 'Chemistry', 'Luis Federico Leloir', 'France', 'Scientist'); INSERT INTO nobel_win VALUES (1970, 'Physiology', 'Ulf von Euler', 'Sweden', 'Scientist'); INSERT INTO nobel_win VALUES (1970, 'Physiology', 'Bernard Katz', 'Germany', 'Scientist'); INSERT INTO nobel_win VALUES (1970, 'Literture', 'Aleksandr Solzhenitsyn', 'Russia', 'Linguist'); INSERT INTO nobel_win VALUES (1970, 'Economics', 'Paul Samuelson', 'USA', 'Economist'); INSERT INTO nobel_win VALUES (1970, 'Physiology', 'Julius Axelrod', 'USA', 'Scientist'); INSERT INTO nobel_win VALUES (1971, 'Physics', 'Dennis Gabor', 'Hungary', 'Scientist'); INSERT INTO nobel_win VALUES (1971, 'Chemistry', 'Gerhard Herzberg', 'Germany', 'Scientist'); INSERT INTO nobel_win VALUES (1971, 'Peace', 'Willy Brandt', 'Germany', 'Chancellor'); INSERT INTO nobel_win VALUES (1971, 'Literature', 'Pablo Neruda', 'Chile', 'Linguist'); INSERT INTO nobel_win VALUES (1971, 'Economics', 'Simon Kuznets', 'Russia', 'Economist'); INSERT INTO nobel_win VALUES (1978, 'Peace', 'Anwar al-Sadat', 'Egypt', 'President'); INSERT INTO nobel_win VALUES (1978, 'Peace', 'Menachem Begin', 'Israel' /*or NULL, they both mean nothing*/, 'Prime Minister'); INSERT INTO nobel_win VALUES (1987, 'Chemistry', 'Donald J. Cram', 'USA', 'Scientist'); INSERT INTO nobel_win VALUES (1987, 'Chemistry', 'Jean-Marie Lehn', 'France', 'Scientist'); INSERT INTO nobel_win VALUES (1987, 'Physiology', 'Susumu Tonegawa', 'Japan', 'Scientist'); INSERT INTO nobel_win VALUES (1994, 'Economics', 'Reinhard Selten', 'Germany', 'Economist'); INSERT INTO nobel_win VALUES (1994, 'Peace', 'Yitzhak Rabin', 'Isreal' /*اللهم دمرهم تدميرًا*/, 'Prime Minister'); INSERT INTO nobel_win VALUES (1987, 'Physics', 'Johannes Georg Bednorz', 'Germany', 'Scientist'); INSERT INTO nobel_win VALUES (1987, 'Literature', 'Joseph Brodsky', 'Russia', 'Linguist'); INSERT INTO nobel_win VALUES (1987, 'Economics', 'Robert Solow', 'USA', 'Economist'); INSERT INTO nobel_win VALUES (1994, 'Literature', 'Kenzaburo Oe', 'Japan', 'Linguist'); --Query a) Write a SQL query to show all the winners of Nobel prize in the year 1970 except the subject Physiology and Economic: SELECT * FROM nobel_win WHERE (YEAR = 1970) AND NOT (SUBJECT = 'Physiology' OR SUBJECT = 'Economics'); SELECT * FROM nobel_win WHERE (YEAR = 1970) AND SUBJECT NOT IN ('Physiology', 'Economics');
Editor is loading...
Leave a Comment