Scribd
Upload
9 views10 pages

SQL Queries for Nobel Prize Data Analysis

Uploaded by

pranshavji
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views10 pages

SQL Queries for Nobel Prize Data Analysis

Uploaded by

pranshavji
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

DBMS Tutorial – 2

Q-1) Write a SQL query to show all details of the Prime


Ministerial winners after 1972 of Menachem Begin and
Yitzhak Rabin?
 Select category=’Prime ministerial’
from nobel_win where year>1972
and winner in(‘Menachem
begin’,’Yitzhak rabin’);
 Output:

Q-2)Write a SQL query to show all the


details of the winners with first name
Louis?
 Select * from nobel_win where
winner like ‘louis %’;
 Output:
Q-3)Write a SQL query to show all the
winners in physics for 1970 together with
the winner of Economics for 1971?
 Select * from nobel_win where
(subject=’Physics’ and year=1970)
union (select * from nobel_win where
(subject=’Economics’ and
year=1971));
 Output:

Q-4) Write a SQL query to show all the


winners of nobel prize in the year 1970
except the subject Physiology and
Economics?
 Select
subject=”Physiology”,subject=”Econ
omic” from nobel_win where
year=1970;
 Output:
Q-5) Write a SQL query to show the
winners of a 'Physiology' prize in an early
year before 1971 together with winners of
a 'Peace' prize in a later year on and after
the 1974?
 Select * from nobel_win
(subject=’Physiology’ and
year<1971) union (select * from
nobel_win where (subject=’Peace’
and year>=1974));
 Otuput:

Q-6) Write a SQL query to find all details


of the prize won by Johannes Georg
Bednorz?
 Select * from nobel_win where
winner=”Johannes Georg”;
 Output:

Q-7) Write a SQL query to find all the


details of the nobel winners for the subject
not started with the letter 'P' and arranged
the list as the most recent comes first, then
by name in order?
 Select * from nobel_win where
subject not like ‘P%’ order by year
desc,winner;
 Output:

Q-8) Write a SQL query to find all the


details of 1970 winners by the ordered to
subject and winner name but the list
contain the subject Economics and
Chemistry at last?
 Select * from nobel_win where
year=1970 order by case when
subject in(‘Economics’,’chemistry’)
then1 else 0 end asc,subject,winner;
 Output:

Q-9)Write a SQL query to find all the


product with a price between Rs.200 and
Rs.500?
 Select * from item_mast where price
between 200 and 500;
Output:

Q-10)Write a SQL query to calculate the


average the price of all products of the
manufacturer which code is 16?
 Select avg(price) from item_mast where
com = 16;
 Output:

Q-11)Write a SQL query to find the item name


and price in rs?
 Select name,price from item_mast;
 Output:

Q-12)Write a SQL to display the name and


price of all items with a price is equal or more
than RS.250,and the list contain the larger
price first and then by name in ascending
order?
 Select name,price from item_mast order
by ascd;
 Output:

Q-13)Write a SQL query to display the


average price of items for each company,
showing only the company code?
 Select avg(price),com from item_mast
group by com;
 Output:
Q-14)Write a SQL query to find the name and
price of the cheapest item?
 Select name,price from item_mast
where price = (select min(price) from
item_mast);
 Outout:

Q-15)Write a query in SQL to find Last name


of all employees , without duplicates?
 Select distint lname from emp_details;
 Output:
Q-16)Write a query in SQL to find the data of
employee whose last name is “snares”?
 Select * from emp_details where
lname=”snares”;
 Output:

Q-17)Write a query in SQL to display all data


of employees that works in the department
57?
 Select * from emp_details where
dept=57;
 Output:

Common questions

Powered by AI

The DISTINCT keyword is necessary to ensure that only unique last names are selected, which prevents duplicate entries from appearing in the result set. This enhances query accuracy by guaranteeing each last name is listed only once, reflecting the true variety of last names in the dataset .

The query is: 'SELECT * FROM nobel_win WHERE subject NOT LIKE 'P%' ORDER BY year DESC, winner ASC;'. This reveals the necessity of structuring queries to cater to multiple ordering criteria, leveraging both DESC and ASC to organize data effectively by chronology and then alphabetically .

The query 'SELECT * FROM emp_details WHERE dept=57;' must consider specifying the correct department column and ensuring that all relevant columns are selected. Specificity is crucial to retrieve accurate data pertinent to the intended department without including unintended entries .

The method involves the query: 'SELECT name, price FROM item_mast WHERE price = (SELECT MIN(price) FROM item_mast);'. The subquery is essential because it calculates the minimum price, which is then used as a condition for selecting the name and price of the product, combining both selection criteria and aggregate computation .

The approach involves using the query: 'SELECT * FROM item_mast WHERE price BETWEEN 200 AND 500;'. The BETWEEN clause is useful here because it allows you to easily specify a range of values for selection, in this case, all products priced between Rs.200 and Rs.500 .

To calculate the average price of all products from a manufacturer with code 16, use the query: 'SELECT AVG(price) FROM item_mast WHERE com = 16;'. This illustrates the use of aggregate functions in SQL, where AVG() computes the average of column values, providing summarized information about data sets .

To retrieve all Nobel winners for 1970 except those in Physiology and Economics, you use the query: 'SELECT * FROM nobel_win WHERE year=1970 AND subject NOT IN ('Physiology','Economics');'. This query illustrates the use of the NOT IN clause to exclude specific conditions while selecting data from a database .

The SQL query is: 'SELECT * FROM nobel_win WHERE (subject='Physics' AND year=1970) UNION SELECT * FROM nobel_win WHERE (subject='Economics' AND year=1971);'. The UNION operator is appropriate here because it combines the results of two SELECT queries while eliminating duplicate rows, ensuring the winners from both Physics in 1970 and Economics in 1971 are shown together in a single result set .

To ensure a list of items is ordered by larger price first and then by name in ascending order, you use the query: 'SELECT name, price FROM item_mast WHERE price >= 250 ORDER BY price DESC, name ASC;'. This ordering is significant as it prioritizes the list by monetary value while maintaining alphabetical order for items with the same price .

Using UNION in SQL queries, as shown in 'SELECT * FROM nobel_win WHERE (subject='Physiology' AND year<1971) UNION SELECT * FROM nobel_win WHERE (subject='Peace' AND year>=1974);', can effectively combine data sets from different conditions such as combining different years and prize categories. However, potential issues include ensuring both queries return the same number of columns and that data types align across combined sets to avoid runtime errors .

You might also like