Room for Aladdin and John Rennie

Any field that is unique, i.e. no duplicates, can be the primary key, but it is common to create an int field to be the primary key because this is simple and fast.

That's why I suggested you add a new int field topping_id to act as the primary key.

Aladdin

8:25 AM

ahh ok

can a primary key consist of multiple attributes by any chance

or is it single attributes always

John Rennie

Yes, a primary key can be a combination of columns as long as that combination is unique. e.g. you could write:

Aladdin

ahh i checked online and it says it can have multiple attributes

John Rennie

create table TOPPING (topping_name varchar(30) not null,topping_id not null, price int not null, primary key (topping_id, topping_name));

(although there is nothing to be gained from doing this in this particular case)

Aladdin

ok

Aladdin

9:37 AM

@JohnRennie I had a doubt

John Rennie

@Aladdin Hi :-)

Aladdin

so when we use join

select sum(t.price)
from topping t join pizza_topping pt on t.topping_id = pt.topping_id

so does the table look like this

:

let me edit the picture

John Rennie

OK ...

Aladdin

all these attributes will be merged in one table

satisfying the condition t.topping_id = pt.topping_id

John Rennie

Yes. Try select * from topping t join pizza_topping pt on t.topping_id = pt.topping_id and you'll see it is just the two tables combined into a single table with all the fields.

Aladdin

9:44 AM

ok

John Rennie

And if you then join the pizza table you'll find you have a single table with all the fields from all three tables.

Aladdin

ahh

got it

now i think i finally understand how join works

John Rennie

Cool :-)

Aladdin

10:19 AM

@JohnRennie is there a way to get pizzas_id and their cost and topping cost in another table

John Rennie

Can you clarify what you mean?

Aladdin

i think joining all tables is first thing

like each pizza has a cost

then topping cost

John Rennie

Do you mean you want a new table that contains the cost of the toppings for each pizza type?

@Aladdin Yes, this is done using a grouping query.

Aladdin

pizza cost +topping cost

John Rennie

Suppose we have the joins:

pizza p join pizza_topping pt on p.pizza_id = pt.pizza_id join topping t on pt.topping_id = t.topping_id

and now I do the query:

select p.pname, p.price, t.price
from pizza p join pizza_topping pt on p.pizza_id = pt.pizza_id
join topping t on pt.topping_id = t.topping_id
order by p.name, p.price

This will create a recordset with three columns. There will be a record for every combination of pizza and topping.

Is it clear what this means, or shall we take a specific example?

Aladdin

10:30 AM

sorry i went away

let me go through this quickly

John Rennie

OK ...

Aladdin

yes its clear

John Rennie

OK. But we don't want a separate record for every combination of pizza and topping, we want a single record for each pizza, with the sum of the prices for all the toppings used in that pizza. Yes?

Aladdin

yes

John Rennie

The way we do this is:

select p.pname, p.price, sum(t.price)
from pizza p join pizza_topping pt on p.pizza_id = pt.pizza_id
join topping t on pt.topping_id = t.topping_id
group by p.name, p.price
order by p.name, p.price

Note that there are two new things.

1. in the select part we have sum(t.price)

The sum() function sums the values in a set of records.

2. we have group by p.name, p.price

Aladdin

10:35 AM

ok

John Rennie

What this does is take every combination of p.name and p.price, and for that combination it sums up all the topping prices for that combination.

Do you want to go through an example to see how this works?

Aladdin

let me execute it

@JohnRennie It shows me this

can we go through the example for understanding how this works

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

i got this definition from online but i dont see how this works

John Rennie

I'm just answering another question but I should only be five minutes.

Aladdin

no worries.i will wait till u are free

John Rennie

@Aladdin Hi

Let's suppose the pizza table contains:

Aladdin

10:49 AM

hello

John Rennie

ID Name                 Price
1  Chicken Supreme      570
2  Malai Chicken Tikka  560
3  Veg Kebab Surprise   330

And the toppings table contains:

101  Grilled chicken  100
102  Chicken tikka    150
103  Chick peas       50
104  Paneer           50

Now suppose the Chicken supreme pizza has just one topping Grilled chicken. When we do the query (without the sum) we get:

Chicken Supreme      570   100

Aladdin

yes

John Rennie

Now suppose the Malai chick contains two toppings, Grilled chicken and Chicken tikka. When we do the join we get two records:

Malai Chicken Tikka  560  100
Malai Chicken Tikka  560  150

The first two fields are the same in both rows, because they are for the same pizza, but the topping price fields are different because they are the prices for the two different toppings used.

OK so far?

Aladdin

ok

John Rennie

And just for completeness assume the veg pizza has two toppings chick peas and paneer. Then for this pizza we get:

Veg Kebab Surprise   330   50
Veg Kebab Surprise   330   50

For the two toppings. So our complete result set is:

Pizza name          price  topping price
Chicken Supreme      570   100
Malai Chicken Tikka  560   100
Malai Chicken Tikka  560   150
Veg Kebab Surprise   330    50
Veg Kebab Surprise   330    50

@Aladdin OK so far?

Aladdin

10:59 AM

ok

John Rennie

Now, in the grouping query we have group by p.name, p.price

And this splits the result set into groups that have the same values for name and price. So we get this:

Pizza name          price  topping price
Chicken Supreme      570   100

Malai Chicken Tikka  560   100
Malai Chicken Tikka  560   150

Veg Kebab Surprise   330    50
Veg Kebab Surprise   330    50

i.e. we get three groups. Yes?

Aladdin

ahh

John Rennie

Each group has the same values for name and price.

Aladdin

yes

John Rennie

And you can probably see what happens now. The sum(t.price) function sums up the t.price values in each group.

Aladdin

11:03 AM

yea

that explains ir

John Rennie

Pizza name          price  topping price
Chicken Supreme      570   100
                           ---
Chicken Supreme      570   100

Malai Chicken Tikka  560   100
Malai Chicken Tikka  560   150
                           ---
Malai Chicken Tikka  560   250

Veg Kebab Surprise   330    50
Veg Kebab Surprise   330    50
                           ---
Veg Kebab Surprise   330   100

And the final result is just the records with the sum i.e.

Pizza name          price  sum(topping price)
Chicken Supreme      570   100
Malai Chicken Tikka  560   250
Veg Kebab Surprise   330   100

Aladdin

got it

i had one doubt

in your query

John Rennie

You can do other things with grouping queries.

For example suppose we did:

select p.pname, p.price, count(t.price)
from pizza p join pizza_topping pt on p.pizza_id = pt.pizza_id
join topping t on pt.topping_id = t.topping_id
group by p.name, p.price

Can you guess what this does?

Aladdin

find the number of toppings for pizz

pizza

John Rennie

Exactly, yes :-)

Or you could do:

select p.pname, p.price, sum(t.price), count(t.price)
from pizza p join pizza_topping pt on p.pizza_id = pt.pizza_id
join topping t on pt.topping_id = t.topping_id
group by p.name, p.price

And this returns both the total cost and the number of the toppings

Aladdin

11:09 AM

nice

i will go through some more examples on my own

thanks a lot!

John Rennie

OK :-)

Transcript for

Room for Aladdin and John Rennie