Room for Aladdin and John Rennie

Aladdin

8:33 AM

@JohnRennie Hello

John Rennie

9:20 AM

@Aladdin Hi :-)

Sorry for the slow reply. I had to go out.

Aladdin

hello

@JohnRennie i had a doubt

John Rennie

What's the question?

Aladdin

so my sql table i created like this:

drop database pizza_management;
create database pizza_management;

\c pizza_management

create table PIZZA (pname varchar(30) not null, quantity int, size varchar(3), price int, veg_nonveg varchar(10), ingredients varchar, primary key(pname));

create table LOGIN_TABLE (username_ varchar(30) not null, password_ varchar(30) not null, staff char, customer char, primary key (username_));

create table PAYMENTS (payment_id varchar(10) not null, mode varchar(20), total_cost int not null, primary key(payment_id));

when i need to populate the tables

do i need to add constraints again

John Rennie

No, once you have created all the tables with the constraints you can just add data to them.

Though when you add data you have to make sure your data satisfies the constraints you have created.

Aladdin

ok

@JohnRennie Is there a way i can add constraint that pizza prices should be between a limit

John Rennie

9:34 AM

You mean in the PIZZA table you want to set a maximum value for the price field?

Aladdin

yes

and a minimum too i think

John Rennie

Yes, that's called a "check constraint".

Is it Postgresql you are using?

Aladdin

yes

John Rennie

postgresqltutorial.com/postgresql-check-constraint

Aladdin

ah got it

@JohnRennie i want to get details of all the pizza with minimum price

so i tried this query

SELECT pname,quantity,size,veg_nonveg,ingredients

from pizza;

where pizza.price>350;

but is there a way i can get minimum price of pizza and use it in this query

350 is the minimum cost of pizza

John Rennie

9:49 AM

You mean select by the value used in the check constraint?

Aladdin

no like i added these values in the pizza table:

ALTER TABLE PIZZA
ADD CHECK (price>=330 AND price<=690);

INSERT into PIZZA values('Veg Kebab Surprise Small',1,'S',330,'Veg','Veg kebab,Onion,Green Capsicum,Tomato&Sweet');
INSERT into PIZZA values('Veg Kebab Surprise Medium',2,'M',529,'Veg','Veg kebab,Onion,Green Capsicum,Tomato&Sweet');
INSERT into PIZZA values('Veg Kebab Surprise Large',3,'L',625,'Veg','Veg kebab,Onion,Green Capsicum,Tomato&Sweet');
INSERT into PIZZA values('Chicken Supreme Small',1,'S',359,'Non-Veg','Herbed Chicken,Schezwan Chicken Meatball,Chicken Tikka');
INSERT into PIZZA values('Chicken Supreme Medium',2,'M',570,'Non-Veg','Herbed Chicken,Schezwan Chicken Meatball,Chicken …

John Rennie

So you want to find the minimum price, then list all the pizzas that have this minimum price?

Aladdin

yes

John Rennie

select * from pizza
where price =
( select min(price) from pizza )

Aladdin

ohh

what is the bracket thing called

John Rennie

9:55 AM

That's a "subquery"

A subquery returns a value, and you can use the value returned by the subquery in your main query.

Aladdin

got it

John Rennie

You can also use subqueries to return a temporary record set.

Aladdin

@JohnRennie so in login table i have a coloumn which shows if user is staff or not

John Rennie

OK ...

Aladdin

how do i count the number of users which are not staff

select * from LOGIN_TABLE
pizza_management-# where
pizza_management-# staff=F;

this should give number of users who are not staff

John Rennie

10:02 AM

You use a function.

In the example we above we used a function called min() i.e. select min(price) selects the minimum of the price field. OK so far?

Aladdin

ok

John Rennie

There are lots of functions in SQL, and one of them is count(), which counts the number of rows returned.

So you can write:

select count(*) from login_table where staff = 'F';

This returns a single number which is the number of records that have staff = 'F'.

Aladdin

ahh

pizza_management=# select count(*) from login_table where staff = 'F';
count
-------
4
(1 row)

it worked!

John Rennie

:-)

Aladdin

@JohnRennie do. u know what are complex queries

John Rennie

10:13 AM

I don't think "complex query" is a formal term, it just means a query that is complicated e.g. a query that joins two or more tables.

Aladdin

ohh

Aladdin

10:29 AM

@JohnRennie can u give an example of how a complex query looks like in my database

i googled it and it looks complicated

John Rennie

Suppose you want a list of each username and that user's phone number.

In the customer table you have the username but you don't have the phone number.

Aladdin

yes

John Rennie

In the customer_phone_no table you have the phone number but not the username.

So how can you get the username and phone together from a query?

Do you know?

Aladdin

we can join both tables

then select phone numbers and customer_name?

John Rennie

Yes, you just join the tables so you can select the data from both the tables.

This would count as a complex query, though it's a fairly simple complex query.

Have you done joining tables in SQL?

Aladdin

10:36 AM

i have tried a query on joining

John Rennie

We can go through how to write this query if you want ...

Aladdin

let me try once

John Rennie

OK :-)

Aladdin

@JohnRennie select Customer.fname, customer_phone_no.phone_no
from customer join customer_phone_no on customer.fname = customer_phone_no.fname

something like this?

i wanted to check for fname and lname tho

John Rennie

If you look at the definition of the phone table it has:

create table CUSTOMER_PHONE_NO (
phone_no varchar(10),
fname varchar(30),
lname varchar(30),
primary key(phone_no),
foreign key (fname, lname) references CUSTOMER(fname, lname)
);

So note that the link to the customer table, i.e. the foreign key constraint, is on both fname and lname.

Aladdin

10:47 AM

yes

John Rennie

So your join condition has to include both fname and lname.

So you need to write:

from customer c join customer_phone_no p on c.fname = p.fname and c.lname = p.lname

Aladdin

ok

John Rennie

Also I've used aliases for the table names to reduce the amount of typing needed.

If you write from customer c this makes "c" an alias for "customer" so you can type c.whatever instead of customer.whatever.

Aladdin

select Customer.fname, customer_phone_no.phone_no
from customer c join customer_phone_no p on c.fname = p.fname and c.lname=p.lname
order by customer.fname

John Rennie

Likewise join customer_phone_no p makes "p" an alias for "customer_phone_no".

Aladdin

10:50 AM

ohk

but for some reason my query is not working

John Rennie

select c.fname, p.phone_no
from customer c join customer_phone_no p on c.fname = p.fname and c.lname=p.lname
order by c.fname

Aladdin

ok

John Rennie

You can use the aliases everywhere. It saves a lot of typing :-)

Aladdin

ok!

John Rennie

Do you get an error when you run the query?

Aladdin

10:53 AM

no

this works

John Rennie

OK :-)

Aladdin

i need to do one more complex query

can u give an example also

like this

John Rennie

If I look at your table definitions you don't have that many relationships defined.

You have chef and chef_phone_no tables that you could link in a similar way, but I can't see any other foreign keys.

Aladdin

ahh

John Rennie

Were you given this schema or did you design it yourself?

Because the schema seems as though it is missing parts.

Aladdin

11:00 AM

i can also do customer username

in login table

me and my teamates designed like this

John Rennie

You could, but then the username_ field is in the customer table anyway so you don't need to link the tables.

Aladdin

ah yea

John Rennie

It's just that I would expect to see an orders table. e.g. if I order a pizza from you then there should be a record in the orders table that links to my login to say who placed the order, and links to the pizza table to show what pizza was on the order.

Aladdin

ahh yea

ur right

John Rennie

Also you have a topping table, and presumably a pizza should link to the topping table so you can see what topping(s) are on the pizza.

Aladdin

11:04 AM

we have a topping table

it should link to pizza name

John Rennie

But how does it link?

Aladdin

yea that thing is missing

we would need to add it in our table

John Rennie

You can't link by pizza name, because the same topping might be used on more than one pizza.

Aladdin

oh

John Rennie

In your pizza table you have a ingredients field, but this is just a string with all the ingredients so it isn't very useful.

Typically the way you do this is that the topping table looks like this:

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

where topping_id is a number that uniquely identifies each topping.

e.g. you could have:

1, "Herbed Chicken", 123
2, "Schezwan Chicken Meatball", 456
3, "Chicken Tikka", 789

Aladdin

11:10 AM

ohh

John Rennie

(I've made up prices since I have no idea what the prices would be)

Then in the pizza table you'd have:

create table PIZZA (
pizza_id int not null,
pname varchar(30) not null,
quantity int,
size varchar(3),
price int,
veg_nonveg varchar(10),
primary key(pizza_id)
);

Aladdin

we don't have topping id in pizza?

John Rennie

Aha! This is where it gets cool! :-)

We have a third table pizza_topping that looks like:

create table pizza_topping (
topping_id int not null,
pizza_id int not null
);

Aladdin

ok!

that makes sense

John Rennie

Now suppose you have pizza 1, i.e. pizza_id = 1, called "Chicken special" that has the two toppings Herbed Chicken and Chicken Tikka.

Then you add two records to the pizza_topping table:

topping_id  pizza_id
1           1
3           1

This table is linking the pizza and the topping table.

What it says is that pizza id 1 has the two toppings with ids 1 and 3.

@Aladdin OK so far?

Aladdin

11:18 AM

ok

John Rennie

Now suppose you want to find out what toppings are on pizza 1. You can do:

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

So you have two joins: topping ⟶ pizza_topping ⟶ pizza

Aladdin

ohh

how does double join work

John Rennie

The pizza_topping table is called a "link table" because it links the two tables topping and pizza.

@Aladdin the double join works just like a single join. I'll put some brackets in to make it a bit clearer:

select t.topping_name
from (topping t join pizza_topping pt on t.topping_id = pt.topping_id)
join pizza p on pt.pizza_id = p.pizza_id

What happens is that first we do the join (topping t join pizza_topping pt on t.topping_id = pt.topping_id) and this creates a joined table consisting of the two tables topping and pizza_topping.

Then this joined table gets joined again to pizza.

Aladdin

ohk

John Rennie

The joins look like this:

Aladdin

11:27 AM

ok

John Rennie

Suppose your accountant wanted to know the cost of the toppings on the "Chicken special" pizza.

You could write:

select sum(t.price)
from topping t join pizza_topping pt on t.topping_id = pt.topping_id
join pizza p on pt.pizza_id = p.pizza_id
where p.pname = 'Chicken special';

Can you see how this works?

Aladdin

so the first join joins the records of topping and topping id

where the ids are same

then we join with pizza records and topping records

where the pizza_id are same

like this?

John Rennie

It joins the records of topping and pizza_topping where the topping_id values are the same.

Then it joins pizza_topping and pizza where the pizza_id values are the same.

Aladdin

yes