--database create
create database Restaurant
--table create
create table customers(
CUSTID int primary key,
custname varchar(44) not null,
addresses varchar(33),
nationality varchar(22) default 'Bangladeshi',
phone int unique
)
--data insert
insert into customers(CUSTID, custname, addresses, phone)
values(3, 'tanvin', 'bogra', 01678909764),
(4, 'Tahsin', 'norail', 0123456781),
(5, 'Mowna', 'Dhaka', 23456),
(6, 'Shahed', 'Bogra', 09876)
--data show
select * from customers
--particular column
select custid, custname, phone
from customers
--condition
--find out the customers information who lives in bogra
select *
from customers
where addresses='bogra'
--Find out customernmae who are from rajshahi.
create table orders(
orderid int not null,
orderdate date,
custid int foreign key references customers(custid),
)
select * from orders
select * from customers
insert into orders
values(14, '2021-02-07', 7)
--drop table
drop table orders
--alter table: add column
alter table orders add quantity varchar(22)
select * from orders
--alter table-drop column
alter table orders drop column quantity
--column data type modify
alter table orders alter column quantity int
create table items(
itemno varchar(22) primary key,
itemdes varchar(23) not null,
price float not null,
foodtypes varchar(21)
)
insert into items
values ('I-121', 'Lachi', 77 , 'drinks'),
('I-122', 'Coca-Cola', 30, 'drinks'),
('I-123', 'Fanta', 30 , 'drinks'),
('I-124', 'Beef Burger', 299 , 'Burger'),
('I-125', 'Chicken Burger', 255 , 'Burger')
select * from orders
alter table orders
add itemno varchar(22) foreign key references items(itemno)
insert into orders
values(16, '2021-02-05', 3, 6, 'I-122'),
(16, '2021-02-05', 3, 2, 'I-123'),
(16, '2021-02-05', 3, 1, 'I-124'),
(16, '2021-02-05', 3, 3, 'I-125')
--update
update orders set quantity= 6, itemno='I-123' where orderid=11 and orderdate='2021-02-07'
--NULL
--Find those orders Id which didn't specify itemno.
select orderid from orders where itemno is NULL
--Find those orders Id which specify itemno.
select orderid from orders where itemno is not NULL
select * from customers
select * from orders
--TOP
select Top 1 * from customers where addresses='Bogra'
--join
--inner join
select * from
customers join orders
on customers.CUSTID=orders.custid
--left outer join
select * from
customers left outer join orders
on customers.CUSTID=orders.custid
--right outer join
select * from
customers right outer join orders
on customers.CUSTID=orders.custid
--full outer join
select * from
customers full outer join orders
on customers.CUSTID=orders.custid
--self join
--find out the information of those customers who are from same city
select cr1.custid, cr1.custname, cr1. addresses
from customers cr1, customers cr2
where cr1.addresses=cr2.addresses
and cr1.custid <> cr2.custid
--order by
select * from customers order by addresses desc
--min, max, sum, count, avg
select * from orders
select * from customers
select * from items
--Find out lowest price of items in you restaurant
select min(price) as prc from items
----Find out highest price of items in you restaurant
select max(price) as prc from items
--Find out average price of items in you restaurant
select avg(price) as average_price from items
--count
select count(custid) from customers
--find out the number of area we cover from our restaurant.
select count(distinct addresses) from customers
--Find out total price for all items
select sum(price) from items
--find out the total quantity of items that is ordered by customer
select sum(quantity) from orders
--find out the number of items that is ordered by customer
select count(distinct itemno) from orders
--like
--pattern matching
--find out the name of those customers whose name starts with a
select distinct custname from customers where custname like 'ma%'
--find out the name of those customers whose name starts with a
select distinct custname from customers where custname like '%a'
--find out the name of those customers whose contains a
select distinct custname from customers where custname like '%n%'
--2nd character a
select distinct custname from customers where custname like '__a%'
select * from orders
select * from customers
--