데이터베이스 프로그래밍 능력단위 평가

1번 

 

1. gamedb 스키마를 생성하고

회원가입을위한 유저 테이블과

점수 저장을위한 점수 테이블을 생성한다

​

유저 테이블에 필요한 필드는

유저아이디, 닉네임, 가입 날짜다

​

점수테이블에 필요한 필드는

유저 아이디, 점수다

​

제출 : erd 스샷

gameDB_create_tables.sql 파일

 

gameDB_create_tables.sql

0.00MB

CREATE DATABASE gamedb;

USE gamedb;


CREATE TABLE usertbl (
    user_id VARCHAR(10) PRIMARY KEY,
    nickname VARCHAR(50) NOT NULL,
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE scoretbl (
    score_id INT PRIMARY KEY,
    user_id VARCHAR(10) NOT NULL,
    score INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES usertbl(user_id)
);

select * from usertbl;

select * from scoretbl;

 

 

 

 

+++

2번

2. 유저 테이블에 CRUD쿼리를 만드세요

​

삽입(Create) : Insert

읽기(Read) : Select

수정(Update) : Update

삭제(Delete) : Delete

​

제출 :

각 쿼리당 1개의 스크린샷

gameDB_usertbl_crud.sql 파일

gameDB_usertbl_crud.sql

0.00MB

use gamedb;


INSERT INTO usertbl (user_id, nickname)
VALUES ('user123', 'new_nickname');
SELECT * FROM usertbl;


UPDATE usertbl
SET nickname = 'updated_nickname'
WHERE user_id = 'user123';
SELECT * FROM usertbl;



DELETE FROM usertbl WHERE user_id = 'user123';
SELECT * FROM usertbl;

 

 

insert

 

update

 

delete

 

+++

 

3번

점수테이블에 레코드를 삽입하고

내부조인을 사용해서 유저정보와 점수를 출력하세요

​

유저 ID | 닉네임 | 점수

hong 홍길동 1000

​

​

제출 :

조인결과 스크린샷

gameDB_inner_join_usertbl_scorestbl.sql

gameDB_inner_join_usertbl_scorestbl.sql

0.00MB

use gamedb;

insert into usertbl(user_id,nickname,date)
values('hong','홍길동','2024-05-31 12:00:00');
select * from usertbl;

INSERT INTO scoretbl (score_id, user_id, score)
VALUES (1, 'hong', 1500);
select * from scoretbl;

SELECT u.user_id as '유저ID', u.nickname as '닉네임', s.score as '점수'
FROM usertbl u
INNER JOIN scoretbl s ON u.user_id = s.user_id;

 

+++

 

4번 

프로시저를 만들어야 합니다

​

매개변수 하나를 받아서 1등 부터 ~ n등 까지 유저정보와 점수를 출력해야 한다

​

제출

스크린샷

gameDB_procedure_rank.sql

 

gameDB_procedure_rank.sql

0.00MB

전체 유저와 점

 

 

프로시져 호출 rankProc(3)

 

+++

 

5번

함수를 만들어야 합니다

id를 매개변수로 받아서 몇등인지 반환 해야 합니다.

​

제출 : 스크린샷

gameDB_function_ranking.sql

gameDB_function_ranking.sql

0.00MB

전체 유저의 등수

고길동의 랭크

 

홍길동의 랭크

 

use gamedb;

DELIMITER //

CREATE FUNCTION GetUserRank(userID VARCHAR(10)) RETURNS INT
BEGIN
    DECLARE userRank INT;

    SELECT ranking INTO userRank
    FROM (
        SELECT user_id, score, 
               RANK() OVER (ORDER BY score DESC) AS ranking
        FROM scoretbl
    ) rankedScores
    WHERE user_id = userID;

    RETURN userRank;
END //

DELIMITER ;

SELECT GetUserRank('gogildong');

SELECT u.user_id , s.score, GetUserRank(u.user_id) as ranking
FROM usertbl u
INNER JOIN scoretbl s ON u.user_id = s.user_id
order by ranking asc;

select GetUserRank('hong');