🔑로그인

• [ ] 로그인 기능

  SELECT c.customer_id FROM customer c WHERE login_id = 'geymh0c' AND login_pw='bg0hE*R7niz~H';

  String c_id = 쿼리결과

• [x] 손님 본인의 예약목록확인

  SELECT r.status, r.special_request, r.party_size, r.reservation_date, r.star_rating, r.reservation_time

  FROM Reservation r

  WHERE r.rv_customer_id =”손님번호” — c_id

  SELECT rv.reservation_date,rt.restaurant_name, rv.reservation_time,rv.star_rating, rv.party_size, rv.special_request, rv.status FROM Reservation rv, Restaurant rt WHERE rv.rv_customer_id = 'C000001' and rt.restaurant_id= rv.rv_restaurant_id;

• [x] 탈퇴 기능

  DELETE FROM customer

  WHERE customer_id = ‘손님번호’

  DELETE FROM customer WHERE customer_id = 'C000001';

✍매니저

• [x] 매니저 본인이 운영하는 가게의 일별 예약목록보기

  SELECT manager_id

  FROM Manager

  WEHRE login_id =’’

  AND login_pw =’’

  String m_id =쿼리

  SELECT a.reservation_time, b.restarant_name, b.reservation_date, b.special_request, b.party_size, b.status

  FROM Reservation a, Restaurant b

  WHERE b.rt_manager_id = ‘매니저 id’ — m_id

  AND b.reservation_da = ‘입력한 시간’

  SELECT rv.reservation_date, rv.reservation_time, rt.restaurant_name, rv.special_request, rv.party_size, rv.status FROM Reservation rv , Restaurant rt WHERE rv.rv_restaurant_id=rt.restaurant_id AND rt.rt_manager_id = 'M000544' AND rv.reservation_date='23/12/13';

• [x] 탈퇴 기능

  DELETE FROM manager

  WHERE manager_id = ‘M000446’;

  yemin_schema.sql Line78 on delete cascade 추가 - Phase3에서 매니저 계정 삭제시 레스토랑 삭제되도록 수정

  /*ALTER TABLE reservation drop constraint sys_C008971

  ALTER TABLE reservation add foreign key (rv_restaurant_id) REFERENCES Restaurant(restaurant_id) on delete cascade*/

🔍금색기능

• [x] 카테고리 검색

  select restaurant_name, restaurant_address, phone from restaurant,category where rt_category_id=category_id and category_name='한식';

• [ ] 식당이름 검색

  SELECT restaurant_name, restaurant_address, phone, open_time, last_order_time, total_party_size

  FROM restaurant

  WHERE restaurant_name LIKE '%갈비%';

• [ ] 인원수에 맞는 식당 검색

  SELECT restaurant_name,restaurant_address phone, open_time, last_order_time, total_party_size FROM restaurant WHERE total_party_size >= 70;

• [x] 동네 검색

  SELECT restaurant_name, restaurant_address, phone, open_time, last_order_time, total_party_size

  FROM restaurant WHERE restaurant_address LIKE '%신천%';

• [ ] 메뉴 검색

  SELECT restaurant_name, menu_item_name FROM restaurant, menu WHERE menu_item_name LIKE '%돼지갈비%' AND restaurant_id = m_restaurant_id;

🧮정렬 기능

• [x] 별점 순 정렬

  SELECT restaurant_id, restaurant_name, avg(star_rating)

  FROM Restaurant, Reservation

  WHERE restaurant_id = rv_restaurant_id

  AND star_rating IS NOT NULL Group by restaurant_id, restaurant_name

  Order by avg(star_rating) DESC;

• [ ] 리뷰 많은 순 정렬

  SELECT restaurant_id, restaurant_name, count(review)

  FROM Restaurant, Reservation

  WHERE restaurant_id = rv_restaurant_id

  AND review is NOT NULL

  Group by restaurant_id, restaurant_name

  Order by count(review) DESC;

• [ ] 카테고리 별 예약 많은 순 정렬

  SELECT restaurant_id, restaurant_name, count(review)

  FROM Restaurant, Reservation, Category

  WHERE category_name = '양식'

  AND rt_category_id = category_id

  AND restaurant_id = rv_restaurant_id

  AND review is NOT NULL

  Group by restaurant_id,restaurant_name

  Order by count(review) DESC;

사용할 쿼리문

1. O

-- t2-3로그인기능
SELECT c.customer_id
FROM customer c
WHERE login_id = 'geymh0c'
AND login_pw='bg0hE*R7niz~H';

1. O

-- 손님탈퇴
DELETE FROM customer
WHERE customer_id = 'C000001';

1. O

--t1-1) 식당 이름 검색 기능
SELECT restaurant_name
FROM Restaurant
WHERE restaurant_name LIKE '%짬뽕%';
-- rerstaurant_name에 짬뽕이 포함된 restaurant의 이름을 찾아라

1. O

--t1-2) 인원수에 따른 식당 검색 기능
SELECT restaurant_name, phone, open_time, total_party_size, restaurant_address
FROM Restaurant
WHERE total_party_size >= 80;
-- total_party_size가 80 이상인 restaurant의 이름, 전화번호, 오픈시간, 총 테이블 수, 주소를 찾아라

1. O