본문 바로가기

Welcome.

:: DBMS 🚩23

JOIN>보호소에서 중성화한 동물 -- 서브쿼리 사용 SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME FROM ANIMAL_INS A JOIN ( SELECT ANIMAL_ID, ANIMAL_TYPE, NAME FROM ANIMAL_OUTS WHERE SEX_UPON_OUTCOME NOT LIKE 'Intact %' ) B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE A.SEX_UPON_INTAKE LIKE 'Int%' ORDER BY A.ANIMAL_ID -- 조인만 사용. SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME FROM ANIMAL_INS A JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE A.SEX_UPO.. 2023. 2. 26.

JOIN>5월 식품들의 총매출 조회하기 SELECT A.PRODUCT_ID, A.PRODUCT_NAME, **SUM**(B.AMOUNT*A.PRICE) AS TOTAL_SALES FROM FOOD_PRODUCT A JOIN FOOD_ORDER B ON A.PRODUCT_ID = B.PRODUCT_ID WHERE B.PRODUCE_DATE LIKE '2022-05-%' **GROUP BY PRODUCT_ID** ORDER BY TOTAL_SALES DESC, PRODUCT_ID 2023. 2. 26.

JOIN>주문량이 많은 아이스크림들 조회하기 -- 직접 짠 코드. SELECT C.FLAVOR FROM ( SELECT A.SHIPMENT_ID, A.FLAVOR, SUM(A.TOTAL_ORDER + B.TOTAL_ORDER) AS TOTAL FROM FIRST_HALF A JOIN JULY B ON A.FLAVOR = B.FLAVOR GROUP BY FLAVOR ORDER BY TOTAL DESC ) AS C ORDER BY C.TOTAL DESC LIMIT 3 -- GPT로부터 첨삭받음. SELECT C.FLAVOR FROM ( SELECT A.FLAVOR, SUM(A.TOTAL_ORDER + B.TOTAL_ORDER) AS TOTAL FROM FIRST_HALF A JOIN JULY B ON A.FLAVOR = B.FLAVOR GROUP BY.. 2023. 2. 26.

JOIN>그룹별 조건에 맞는 식당 목록 출력하기 -- 코드를 입력하세요 select member_name, review_text, review_date from member_profile A join rest_review B on A.member_id = B.member_id where member_name like ( select /*member_id,*/ rank() over (order by count(member_id)) as cnt from rest_review group by member_id order by cnt desc ) -- select * from rest_review select member_id, rank() over (order by count(*)) as cnt from rest_review group by member_i.. 2023. 2. 26.

JOIN>오랜 기간 보호한 동물(1) -- JOIN 미사용 SELECT NAME, DATETIME FROM ANIMAL_INS WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS) ORDER BY DATETIME LIMIT 3 --JOIN 사용 SELECT A.NAME, A.DATETIME FROM ANIMAL_INS A LEFT JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE B.ANIMAL_ID IS NULL ORDER BY DATETIME LIMIT 3 2023. 2. 26.

JOIN>있었는데요 없었습니다 -- 코드를 입력하세요 SELECT B.ANIMAL_ID, B.NAME FROM ANIMAL_INS A JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE A.DATETIME > B.DATETIME ORDER BY A.DATETIME ; 2023. 2. 26.

JOIN>없어진 기록 찾기 -- 코드를 입력하세요 SELECT **DISTINCT**(B.ANIMAL_ID), B.NAME FROM ANIMAL_INS A **JOIN ANIMAL_OUTS B** **WHERE B.ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_INS)** ORDER BY ANIMAL_ID; 2023. 2. 26.

JOIN>상품 별 오프라인 매출 구하기 mysql -- 코드를 입력하세요 SELECT A.PRODUCT_CODE, **SUM**(A.PRICE * B.SALES_AMOUNT) AS SALES FROM PRODUCT A **JOIN** OFFLINE_SALE B ON A.PRODUCT_ID = B.PRODUCT_ID **GROUP BY PRODUCT_CODE** ORDER BY SALES DESC, PRODUCT_CODE JOIN = INNER JOIN 2023. 2. 26.

JOIN>조건에 맞는 도서와 저자 리스트 출력하기 mysql -- 코드를 입력하세요 SELECT A.BOOK_ID, B.AUTHOR_NAME, DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d') FROM BOOK A INNER JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID WHERE A.CATEGORY LIKE '경제' ORDER BY A.PUBLISHED_DATE 2023. 2. 26.

GROUP BY>대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 mysql -- SELECT DATE_FORMAT(START_DATE, '%m') AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE CAR_ID IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10' GROUP BY CAR_ID HAVING COUNT(CAR_ID) >= 5 ) GROUP BY MONTH, CAR_ID HAVING RECORDS >= 0 ORDER BY MONTH, CAR_ID DESC; SELECT MO.. 2023. 2. 26.

GROUP BY>카테고리 별 도서 판매량 집계하기 mysql -- 코드를 입력하세요 SELECT A.CATEGORY, SUM(B.SALES) AS TOTAL_SALES FROM BOOK A INNER JOIN BOOK_SALES B ON A.BOOK_ID = B.BOOK_ID WHERE DATE_FORMAT(B.SALES_DATE, '%Y-%m') LIKE '2022-01' GROUP BY CATEGORY ORDER BY CATEGORY; 2023. 2. 26.

GROUP BY>성분으로 구분한 아이스크림 총 주문량 mysql 입니당 -- 코드를 입력하세요 SELECT B.INGREDIENT_TYPE, SUM(A.TOTAL_ORDER) AS TOTAL_ORDER FROM FIRST_HALF A INNER JOIN ICECREAM_INFO B WHERE A.FLAVOR = B.FLAVOR GROUP BY B.INGREDIENT_TYPE ORDER BY TOTAL_ORDER; 2023. 2. 26.

GROUP BY>진료과별 총 예약 횟수 출력하기 -- 코드를 입력하세요 SELECT MCDP_CD AS '진료과 코드', COUNT(*) AS '5월예약건수' FROM APPOINTMENT WHERE APNT_YMD LIKE '2022-05-%' GROUP BY MCDP_CD ORDER BY COUNT(*), MCDP_CD; 2023. 2. 26.

[MySQL] - [1] - [ DB, table 생성, insert, use, show, select ] drop database if exists bookdb; create database bookDB; show databases; use bookDB; show tables; create table booktbl( id int auto_increment primary key, -- id | int형 | 자동 오름차순 출력 -- primary는 table당 하나이며, row의 대푯값이다. bookname varchar(20) not null, -- not null : 공백 불가. publisher varchar(20) not null, price int not null ); insert into booktbl values(null, 'book100', 'com100', 100); -- id칸에 null을 썼.. 2022. 1. 22.

[DBMS] SQL - 개념 - 3 - [SQL - TCL / SQL - DCL] 1. SQL - TCL 2. SQL - DCL 3. 함수 ( NULL 처리 함수, 단일 함수 ) ================================================================================= 1. TCL ( Transaction Control Language ) - 트랜잭션 제어 언어. - 트랜잭션은 데이터베이스의 논리적 연산 단위이다. - 트랜잭션의 대상이 되는 SQL문은 UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML문이다. - 하나의 트랜잭젼에는 하나 이상의 SQL문장이 포함되며 분할할 수 없는 최소의 단위이다. 즉 ALL OR NOTHING 이다. - 트랜잭션의 특징 1. 원자성 : 트랜잭션에서 정의된 연산들은 모두 성공적으.. 2021. 3. 23.

이전 1 2 다음

728x90

티스토리툴바