[2차 실기(작업형)] 예제문제 (과정평가형 정보처리산업기사)

2021. 9. 8. 00:55

728x90

github

https://github.com/LUNA-Jun/License_exam.git

SQL

CREATE TABLE member_tbl_02(
custno number(6) PRIMARY KEY,
custname varchar2(20),
phone varchar2(13),
address varchar2(60),
joindate date,
grade char(1),
city char(2)
)

create sequence seq_no increment by 1 start with 100001;

insert into member_tbl_02 values(seq_no.nextval, '김행복', '010-1111-2222', '서울 동대문구 휘경 1동', '20151202', 'A', '01');
insert into member_tbl_02 values(seq_no.nextval, '이축복', '010-1111-3333', '서울 동대문구 휘경 2동', '20151206', 'B', '01');
insert into member_tbl_02 values(seq_no.nextval, '장믿음', '010-1111-4444', '울릉군 울릉읍 독도 1리', '20151001', 'B', '30');
insert into member_tbl_02 values(seq_no.nextval, '최사랑', '010-1111-5555', '울릉군 울릉읍 독도 2리', '20151113', 'A', '30');
insert into member_tbl_02 values(seq_no.nextval, '진평화', '010-1111-6666', '제주도 제주시 외나무골', '20151225', 'B', '60');
insert into member_tbl_02 values(seq_no.nextval, '차공단', '010-1111-7777', '제주도 제주시 감나무골', '20151211', 'C', '60');

CREATE TABLE money_tbl_02(
custno number(6),
salenol number(8),
pcost number(8),
amount number(4),
price number(8),
pcode varchar2(4),
sdate date
)

insert into money_tbl_02 values(100001, 2016001, 500, 5, 2500, 'A001', '20160101');
insert into money_tbl_02 values(100001, 2016002, 1000, 4, 4000, 'A002', '20160101');
insert into money_tbl_02 values(100001, 2016003, 500, 3, 1500, 'A008', '20160101');
insert into money_tbl_02 values(100002, 2016004, 2000, 1, 2000, 'A004', '20160102');
insert into money_tbl_02 values(100002, 2016005, 500, 1, 500, 'A001', '20160103');
insert into money_tbl_02 values(100003, 2016006, 1500, 2, 3000, 'A003', '20160103');
insert into money_tbl_02 values(100004, 2016007, 500, 2, 1000, 'A001', '20160104');
insert into money_tbl_02 values(100004, 2016008, 500, 1, 300, 'A005', '20160104');
insert into money_tbl_02 values(100004, 2016009, 600, 1, 600, 'A006', '20160104');
insert into money_tbl_02 values(100004, 2016010, 3000, 1, 3000, 'A007', '20160106');

select m1.custno, custname, grade, sum(price)
from MEMBER_TBL_02 m1 inner join money_tbl_02 m2
on m1.custno=m2.custno
group by m1.custno, m1.custname, m1.grade order by sum(price) desc;

JSP

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
	header{
		background-color: blue;
		width: 100%;
		text-align: center;
		color: white;
		line-height: 100px;
		height:100px;
		padding: 0;
		margin: 0;
	}
	nav{
		background-color: skyblue;
		height: 50px;
		line-height: 50px;
	}
	
	footer{
		background-color: blue;
		text-align: center;
		color: white;
		height: 100px;
		line-height: 100px;
		bottom: 0;
	}
</style>
</head>
<body>
<header>
	<h1>쇼핑몰 회원관리 ver1.0</h1>
</header>
<nav>
	&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="joinForm.jsp">회원등록</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="memberForm.jsp">회원목록조회/수정</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="SaleForm.jsp">회원매출조회</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="index.jsp">홈으로</a>&nbsp;&nbsp;&nbsp;&nbsp;
</nav>
<section>
	<h2 style="text-align: center;">쇼핑몰회원관리 프로그램</h2>
	<pre>
		쇼핑몰 회원정보의 회원매출정보 데이터베이스를 구축하고 회원관리 프로그램을 작성하는 프로그램이다.
		프로그램 작성 순서
		1. 회원정보 테이블을 생성한다.
		2. 매출정보 테이블을 생성한다.
		3.회원정보, 매출정보 테이블에 서시된 문제지의 참조데이터를 추가생성한다.
		4.회원정보 입력 화면 프로그램을 작성한다.
		5.회원정보 조회 프로그램을 작성한다.
		6.회원매출정보 조회 프로그램을 작성한다.
	</pre>
</section>
<footer>
	<p>HRDKOREA Copyright&copy;2016 All rights reserved. Human Resources Development Service of Korea </p>
</footer>
</body>
</html>

joinForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*, javax.sql.*" %>
<%
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;

try{
	Class.forName("oracle.jdbc.OracleDriver");
	con=DriverManager.getConnection
			("jdbc:oracle:thin:@//localhost:1521/xe","system","1234");
	
	pstmt=con.prepareStatement("select max(custno) from member_tbl_02");
	
	rs=pstmt.executeQuery();
	
	int custno=100001;
	if(rs.next()){
		custno=rs.getInt(1)+1;
	}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<style>
	header{
		background-color: blue;
		width: 100%;
		text-align: center;
		color: white;
		line-height: 100px;
		height:100px;
		padding: 0;
		margin: 0;
	}
	nav{
		background-color: skyblue;
		height: 50px;
		line-height: 50px;
	}
	
	footer{
		background-color: blue;
		text-align: center;
		color: white;
		height: 100px;
		line-height: 100px;
		bottom: 0;
	}
</style>
<script>
function checkForm(f) {
	if(f.custname.value.trim()==""){
		alert("회원성명이 입력되지 않았습니다.");
		f.custname.focus();
		return false;
	}
	if(f.phone.value.trim()==""){
		alert("회원전화번호가 입력되지 않았습니다.");
		f.phone.focus();
		return false;
	}
	if(f.address.value.trim()==""){
		alert("회원주소가 입력되지 않았습니다.");
		f.address.focus();
		return false;
	}
	if(f.joindate.value.trim()==""){
		alert("가입일자가 입력되지 않았습니다.");
		f.joindate.focus();
		return false;
	}
	if(f.grade.value.trim()==""){
		alert("고객등급이 입력되지 않았습니다.");
		f.grade.focus();
		return false;
	}
	if(f.city.value.trim()==""){
		alert("도시코드가 입력되지 않았습니다.");
		f.city.focus();
		return false;
	}	
}
</script>
<body>
<header>
	<h1>쇼핑몰 회원관리 ver1.0</h1>
</header>
<nav>
	&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="joinForm.jsp">회원등록</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="memberForm.jsp">회원목록조회/수정</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="SaleForm.jsp">회원매출조회</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="index.jsp">홈으로</a>&nbsp;&nbsp;&nbsp;&nbsp;
</nav>
<section>
	<h2 style="text-align: center;">홈쇼핑 회원등록</h2>
	<form name="f" action="joinProcess.jsp" method="post" onsubmit="return checkForm(this)">
		<table border="1" style="margin-left: auto; margin-right: auto;">
			<tr>
				<td>회원번호(자동생성)</td>
				<td><input type="text" name="custno" value="<%=custno%>"></td>				
			</tr>
			<tr>
				<td>회원성명</td>
				<td><input type="text" name="custname"></td>				
			</tr>
			<tr>
				<td>회원전화</td>
				<td><input type="text" name="phone"></td>				
			</tr>
			<tr>
				<td>회원주소</td>
				<td><input type="text" name="address"></td>				
			</tr>
			<tr>
				<td>가입일자</td>
				<td><input type="text" name="joindate"></td>				
			</tr>
			<tr>
				<td>고객등급[A:VIP, B:일반, C:직원]</td>
				<td><input type="text" name="grade"></td>				
			</tr>
			<tr>
				<td>도시코드</td>
				<td><input type="text" name="city"></td>				
			</tr>
			<tr>
				<td colspan="2" style="text-align: center;">
					<input type="submit" value="등  록"/>
					<input type="button" value="조   회" onclick="location.href='memberForm.jsp'"/>
				</td>			
			</tr>
		</table>
	</form>
</section>
<footer>
	<p>HRDKOREA Copyright&copy;2016 All rights reserved. Human Resources Development Service of Korea </p>
</footer>
</body>
</html>
<%
}catch(Exception e){
}finally{
	if(rs!=null) rs.close();
	if(pstmt!=null) pstmt.close();
	if(con!=null) con.close();
}
%>

joinProcess.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*, javax.sql.*" %>
<%
request.setCharacterEncoding("utf-8");
String custno = request.getParameter("custno");
String custname = request.getParameter("custname");
String phone = request.getParameter("phone");
String address = request.getParameter("address");
String joindate = request.getParameter("joindate");
String grade = request.getParameter("grade");
String city = request.getParameter("city");

String sql="insert into member_tbl_02 values(?,?,?,?,?,?,?)";

Connection con=null;
PreparedStatement pstmt=null;
try{
	Class.forName("oracle.jdbc.OracleDriver");
	con=DriverManager.getConnection
			("jdbc:oracle:thin:@//localhost:1521/xe","system","1234");
	
	pstmt=con.prepareStatement(sql);
	pstmt.setString(1, custno);
	pstmt.setString(2, custname);
	pstmt.setString(3, phone);
	pstmt.setString(4, address);
	pstmt.setString(5, joindate);
	pstmt.setString(6, grade);
	pstmt.setString(7, city);
	int result=pstmt.executeUpdate();
	
	if(result>0){
%>
<script>
	alert("회원등록이 완료되었습니다.");
	history.back();
</script>
<%} else{%>
<script>
	alert("회원등록이 실패했습니다.");
	history.back();
</script>
<%
}}catch(Exception e){
	}finally{
		if(pstmt!=null) pstmt.close();
		if(con!=null) con.close();
}
%>

memberForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*, javax.sql.*" %>
<%
	Connection con=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	
	try{
		Class.forName("oracle.jdbc.OracleDriver");
		con=DriverManager.getConnection
				("jdbc:oracle:thin:@//localhost:1521/xe","system","1234");
		
		pstmt=con.prepareStatement("select * from member_tbl_02");
		
		rs=pstmt.executeQuery();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
	header{
		background-color: blue;
		width: 100%;
		text-align: center;
		color: white;
		line-height: 100px;
		height:100px;
		padding: 0;
		margin: 0;
	}
	nav{
		background-color: skyblue;
		height: 50px;
		line-height: 50px;
	}
	
	footer{
		background-color: blue;
		text-align: center;
		color: white;
		height: 100px;
		line-height: 100px;
		bottom: 0;
	}
</style>
</head>
<body>
<header>
	<h1>쇼핑몰 회원관리 ver1.0</h1>
</header>
<nav>
	&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="joinForm.jsp">회원등록</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="memberForm.jsp">회원목록조회/수정</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="SaleForm.jsp">회원매출조회</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="index.jsp">홈으로</a>&nbsp;&nbsp;&nbsp;&nbsp;
</nav>
<section>
	<h2 style="text-align: center;">회원목록조회/수정</h2>
	<table border="1" style="margin-left: auto; margin-right: auto; text-align: center;">
		<tr>
			<td>회원번호</td>
			<td>회원성명</td>
			<td>전화번호</td>
			<td>주소</td>
			<td>가입일자</td>
			<td>고객등급</td>
			<td>거주지역</td>
		</tr>
<%
	if(rs.next()){
		do{
			String grade="직원";
			if(rs.getString(6).equals("A")) grade="VIP";
			else if(rs.getString(6).equals("B")) grade="일반";
%>
		<tr>
			<td><a href="modForm.jsp?custno=<%=rs.getString(1)%>"><%=rs.getString(1) %></a></td>
			<td><%=rs.getString(2) %></td>
			<td><%=rs.getString(3) %></td>
			<td><%=rs.getString(4) %></td>
			<td><%=rs.getString(5).substring(0,10) %></td>
			<td><%=grade %></td>
			<td><%=rs.getString(7) %></td>
		</tr>
<%
		}while(rs.next());
	}else{
	}		
%>
	</table>
</section>
<footer>
	<p>HRDKOREA Copyright&copy;2016 All rights reserved. Human Resources Development Service of Korea </p>
</footer>
</body>
<%
	}catch(Exception e){
	}finally{
		if(rs!=null) rs.close();
		if(pstmt!=null) pstmt.close();
		if(con!=null) con.close();
	}
%>
</html>

SaleForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*, javax.sql.*" %>
<%
	String sql="select m1.custno, custname, grade, sum(price)"
	+" from MEMBER_TBL_02 m1 inner join money_tbl_02 m2"
	+" on m1.custno=m2.custno"
	+" group by m1.custno, m1.custname, m1.grade order by sum(price) desc";

	Connection con=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	
	try{
		Class.forName("oracle.jdbc.OracleDriver");
		con=DriverManager.getConnection
				("jdbc:oracle:thin:@//localhost:1521/xe","system","1234");
		
		pstmt=con.prepareStatement(sql);
		rs=pstmt.executeQuery();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
	header{
		background-color: blue;
		width: 100%;
		text-align: center;
		color: white;
		line-height: 100px;
		height:100px;
		padding: 0;
		margin: 0;
	}
	nav{
		background-color: skyblue;
		height: 50px;
		line-height: 50px;
	}
	
	footer{
		background-color: blue;
		text-align: center;
		color: white;
		height: 100px;
		line-height: 100px;
		bottom: 0;
	}
</style>
</head>
<body>
<header>
	<h1>쇼핑몰 회원관리 ver1.0</h1>
</header>
<nav>
	&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="joinForm.jsp">회원등록</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="memberForm.jsp">회원목록조회/수정</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="SaleForm.jsp">회원매출조회</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="index.jsp">홈으로</a>&nbsp;&nbsp;&nbsp;&nbsp;
</nav>
<section>
	<h2 style="text-align: center;">회원매출조회</h2>
	<table border="1" style="margin-left: auto; margin-right: auto; text-align: center;">
		<tr>
			<td>회원번호</td>
			<td>회원성명</td>
			<td>고객등급</td>
			<td>매출</td>
		</tr>
<%
	if(rs.next()){
		do{
			String grade="직원";
			if(rs.getString(3).equals("A")) grade="VIP";
			else if(rs.getString(3).equals("B")) grade="일반";
%>
		<tr>
			<td><%=rs.getString(1) %></td>
			<td><%=rs.getString(2) %></td>
			<td><%=grade %></td>
			<td><%=rs.getString(4) %></td>
		</tr>
<%
		}while(rs.next());
		}else{
	}
%>
	</table>
</section>
<footer>
	<p>HRDKOREA Copyright&copy;2016 All rights reserved. Human Resources Development Service of Korea </p>
</footer>
<%
	}catch(Exception e){
	}finally{
		if(rs!=null) rs.close();
		if(pstmt!=null) pstmt.close();
		if(con!=null) con.close();
	}
%>
</body>
</html>

modForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*, javax.sql.*" %>
<%
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
request.setCharacterEncoding("utf-8");
String custno=request.getParameter("custno");

try{
	Class.forName("oracle.jdbc.OracleDriver");
	con=DriverManager.getConnection
			("jdbc:oracle:thin:@//localhost:1521/xe","system","1234");
	
	pstmt=con.prepareStatement("select * from member_tbl_02 where custno=?");
	pstmt.setString(1, custno);
	rs=pstmt.executeQuery();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<style>
	header{
		background-color: blue;
		width: 100%;
		text-align: center;
		color: white;
		line-height: 100px;
		height:100px;
		padding: 0;
		margin: 0;
	}
	nav{
		background-color: skyblue;
		height: 50px;
		line-height: 50px;
	}
	
	footer{
		background-color: blue;
		text-align: center;
		color: white;
		height: 100px;
		line-height: 100px;
		bottom: 0;
	}
</style>
<script>
function checkForm(f) {
	if(f.custname.value.trim()==""){
		alert("회원성명이 입력되지 않았습니다.");
		f.custname.focus();
		return false;
	}
	if(f.phone.value.trim()==""){
		alert("회원전화번호가 입력되지 않았습니다.");
		f.phone.focus();
		return false;
	}
	if(f.address.value.trim()==""){
		alert("회원주소가 입력되지 않았습니다.");
		f.address.focus();
		return false;
	}
	if(f.joindate.value.trim()==""){
		alert("가입일자가 입력되지 않았습니다.");
		f.joindate.focus();
		return false;
	}
	if(f.grade.value.trim()==""){
		alert("고객등급이 입력되지 않았습니다.");
		f.grade.focus();
		return false;
	}
	if(f.city.value.trim()==""){
		alert("도시코드가 입력되지 않았습니다.");
		f.city.focus();
		return false;
	}	
}
</script>
<body>
<header>
	<h1>쇼핑몰 회원관리 ver1.0</h1>
</header>
<nav>
	&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="joinForm.jsp">회원등록</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="memberForm.jsp">회원목록조회/수정</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="SaleForm.jsp">회원매출조회</a>&nbsp;&nbsp;&nbsp;&nbsp;
	<a href="index.jsp">홈으로</a>&nbsp;&nbsp;&nbsp;&nbsp;
</nav>
<section>
	<h2 style="text-align: center;">홈쇼핑 회원 정보 수정</h2>
	<form name="f" action="modProcess.jsp" onsubmit="return checkForm(this)">
		<table border="1" style="margin-left: auto; margin-right: auto;">
<%
	if(rs.next()){ 
%>
			<tr>
				<td>회원번호(자동생성)</td>
				<td><input type="text" name="custno" value="<%=rs.getString(1)%>" readonly></td>				
			</tr>
			<tr>
				<td>회원성명</td>
				<td><input type="text" name="custname" value="<%=rs.getString(2)%>"></td>				
			</tr>
			<tr>
				<td>회원전화</td>
				<td><input type="text" name="phone" value="<%=rs.getString(3)%>"></td>				
			</tr>
			<tr>
				<td>회원주소</td>
				<td><input type="text" name="address" value="<%=rs.getString(4)%>"></td>				
			</tr>
			<tr>
				<td>가입일자</td>
				<td><input type="text" name="joindate" value="<%=rs.getString(5).substring(0, 10)%>"></td>				
			</tr>
			<tr>
				<td>고객등급[A:VIP, B:일반, C:직원]</td>
				<td><input type="text" name="grade" value="<%=rs.getString(6)%>"></td>				
			</tr>
			<tr>
				<td>도시코드</td>
				<td><input type="text" name="city" value="<%=rs.getString(7)%>"></td>				
			</tr>
			<tr>
				<td colspan="2" style="text-align: center;">
					<input type="submit" value="수  정"/>
					<input type="button" value="조   회" onclick="location.href='memberForm.jsp'"/>
				</td>			
			</tr>
		</table>
	</form>
<% 
	}
%>
</section>
<footer>
	<p>HRDKOREA Copyright&copy;2016 All rights reserved. Human Resources Development Service of Korea </p>
</footer>
</body>
</html>
<%
}catch(Exception e){
}finally{
	if(rs!=null) rs.close();
	if(pstmt!=null) pstmt.close();
	if(con!=null) con.close();
}
%>

modProcess.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*, javax.sql.*" %>
<%
request.setCharacterEncoding("utf-8");
String custno = request.getParameter("custno");
String custname = request.getParameter("custname");
String phone = request.getParameter("phone");
String address = request.getParameter("address");
String joindate = request.getParameter("joindate");
String grade = request.getParameter("grade");
String city = request.getParameter("city");

String sql="update member_tbl_02 set"
	+" custname=?, phone=?, address=?, joindate=?, grade=?, city=?"
	+" where custno=?";

Connection con=null;
PreparedStatement pstmt=null;
try{
	Class.forName("oracle.jdbc.OracleDriver");
	con=DriverManager.getConnection
			("jdbc:oracle:thin:@//localhost:1521/xe","system","1234");
	
	pstmt=con.prepareStatement(sql);
	pstmt.setString(1, custname);
	pstmt.setString(2, phone);
	pstmt.setString(3, address);
	pstmt.setString(4, joindate);
	pstmt.setString(5, grade);
	pstmt.setString(6, city);
	pstmt.setString(7, custno);
	int result=pstmt.executeUpdate();
	
	if(result>0){
%>
<script>
	alert("회원수정이 완료되었습니다.");
	history.back();
</script>
<%} else{%>
<script>
	alert("회원수정이 실패했습니다.");
	history.back();
</script>
<%
}}catch(Exception e){
	}finally{
		if(pstmt!=null) pstmt.close();
		if(con!=null) con.close();
}
%>

728x90

'개발 ·공부 > 자격증' 카테고리의 다른 글

[1차 필기] UI 테스트(과정평가형 정보처리산업기사) (0)	2021.09.08
[1차 필기] SQL활용(과정평가형 정보처리산업기사) (0)	2021.09.08
[1차 필기] 애플리케이션 테스트 수행(과정평가형 정보처리산업기사) (0)	2021.09.08
[1차 필기] 프로그래밍 언어 활용(과정평가형 정보처리산업기사) (0)	2021.09.08
[1차 필기] 응용 SW 기초 기술 활용(과정평가형 정보처리산업기사) (0)	2021.09.08

LUNA Dev 📁

[2차 실기(작업형)] 예제문제 (과정평가형 정보처리산업기사)

github

SQL

JSP

'개발 ·공부 > 자격증' 카테고리의 다른 글

+ Recent posts

티스토리툴바