SQL Summary Notes for SQL Test PDF

Preview

Summary

Some SQL examples which may be helpful for the SQL final test...

Description

PQ1 1.

List pizzas with the substring 'i' anywhere within the pizza name. select pizza from menu where pizza like '%i%'

2.

Give the average price of pizzas from each country of origin. Change the column name related to average price to "average". select country, avg(price) as average from menu where country is not null group by country

3.

Give the average price of pizzas from each country of origin, do not list countries with only one pizza. select country, avg(price) from menu where country is not null and country = 'italy' group by country

4.

List all ingredients and their types for the 'margarita' pizza. Do not use a subquery. select ingredient, type from items natural join recipe natural join menu where pizza = 'margarita'

5.

Give pizzas and prices for pizzas that are more expensive than all Italian pizzas. select pizza, price from menu where price >any(select max(price) from menu where country = 'italy')

6.

Give all pizzas that originate from the same country as the 'siciliano' pizza. Do not include 'siciliano' pizza in your result table. select pizza from menu where country in (select country from menu where pizza = 'siciliano') and pizza != 'siciliano'

7.

List each ingredient and the pizza that contains the largest amount of this ingredient. select recipe.ingredient, recipe.pizza, recipe.amount from recipe inner join (select max(amount) amt, ingredient from recipe group by ingredient) rcp on recipe.ingredient = rcp.ingredient and recipe.amount = rcp.amt

PQ2 1.

List all pizzas, giving pizza name, price and country of origin where the country of origin has NOT been recorded (i.e. is missing).

select pizza, price, country from menu where country is null-- Type query below

2.

Give the most expensive pizza price from each country of origin. Sort your results by country in ascending order. select country, max(price) as most from menu where country is not null group by country

3.

Give the average price of pizzas from each country of origin, only list countries with 'i' in the country's name. Sort your results based on country in ascending order. select country, avg(price) from menu where country like '%i%' and country is not null group by country-- Type query below

4.

List all 'fish' ingredients used in pizzas, also list the pizza names. Do not use a subquery. select ingredient, pizza from items natural join recipe natural join menu where type = 'fish'

5.

List all ingredients for the Mexican pizza (i.e. country = 'mexico'). You must use a subquery. select distinct(ingredient) from recipe where pizza IN (select pizza from menu where country = 'mexico');

6.

List all pizzas that cost more than 'stagiony' pizza, also give their prices. select pizza, price from menu where price >any(select max(price) from menu where pizza = 'stagiony')

7.

List ingredients used in more than one pizza. Sort your results in ascending order. select ingredient from recipe group by ingredient, ingredient having count(pizza) > 1 order by 1

PQ3 1.

List all price categories recorded in the MENU table, eliminating duplicates. select distinct(price) from menu-- Type query below

2.

Give the cheapest pizzas from each country of origin. Sort your results by country in ascending order. select country, min(price) as least from menu where country is not null group by country order by 1

3.

Give cheapest price of pizzas from each country of origin, only list countries with cheapest price of less than $7.00 select country, min(price) as min from menu where country is not null group by country having min(price) < 7 order by 1

4.

List all 'meat' ingredients used in pizzas, also list the pizza names. Do not use a subquery. select ingredient, pizza from items natural join recipe natural join menu where type = 'meat'

5.

List pizzas with at least one 'meat' ingredient.You must use a subquery. select distinct(pizza) from items natural join recipe natural join menu where type = 'meat'

6.

List all pizzas that cost less than 'siciliano' pizza, also give their prices. select pizza, price from menu where price 75

11. List subject number, name and quota for subjects with an average mark less than 60 and more than 2 students enrolled. List the results in descending subject number order.

select subno,avg(mark) from enroll group by subno having count(sno)>2 order by subno desc;

12. Give the subject number, and the highest mark for each subject (as ‘highest_mark’). Only consider the marks of students with the number ‘1’in their IDs. List the results in subject number order.

select subno,max(mark) as 'Highest_mark' from subject where sno like '%1%' group by subno order by subno;...