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>
<a href="joinForm.jsp">νμλ±λ‘</a>
<a href="memberForm.jsp">νμλͺ©λ‘μ‘°ν/μμ </a>
<a href="SaleForm.jsp">νμ맀μΆμ‘°ν</a>
<a href="index.jsp">νμΌλ‘</a>
</nav>
<section>
<h2 style="text-align: center;">μΌνλͺ°νμκ΄λ¦¬ νλ‘κ·Έλ¨</h2>
<pre>
μΌνλͺ° νμμ 보μ νμ맀μΆμ 보 λ°μ΄ν°λ² μ΄μ€λ₯Ό ꡬμΆνκ³ νμκ΄λ¦¬ νλ‘κ·Έλ¨μ μμ±νλ νλ‘κ·Έλ¨μ΄λ€.
νλ‘κ·Έλ¨ μμ± μμ
1. νμμ 보 ν
μ΄λΈμ μμ±νλ€.
2. 맀μΆμ 보 ν
μ΄λΈμ μμ±νλ€.
3.νμμ 보, 맀μΆμ 보 ν
μ΄λΈμ μμλ λ¬Έμ μ§μ μ°Έμ‘°λ°μ΄ν°λ₯Ό μΆκ°μμ±νλ€.
4.νμμ 보 μ
λ ₯ νλ©΄ νλ‘κ·Έλ¨μ μμ±νλ€.
5.νμμ 보 μ‘°ν νλ‘κ·Έλ¨μ μμ±νλ€.
6.νμ맀μΆμ 보 μ‘°ν νλ‘κ·Έλ¨μ μμ±νλ€.
</pre>
</section>
<footer>
<p>HRDKOREA Copyright©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>
<a href="joinForm.jsp">νμλ±λ‘</a>
<a href="memberForm.jsp">νμλͺ©λ‘μ‘°ν/μμ </a>
<a href="SaleForm.jsp">νμ맀μΆμ‘°ν</a>
<a href="index.jsp">νμΌλ‘</a>
</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©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>
<a href="joinForm.jsp">νμλ±λ‘</a>
<a href="memberForm.jsp">νμλͺ©λ‘μ‘°ν/μμ </a>
<a href="SaleForm.jsp">νμ맀μΆμ‘°ν</a>
<a href="index.jsp">νμΌλ‘</a>
</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©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>
<a href="joinForm.jsp">νμλ±λ‘</a>
<a href="memberForm.jsp">νμλͺ©λ‘μ‘°ν/μμ </a>
<a href="SaleForm.jsp">νμ맀μΆμ‘°ν</a>
<a href="index.jsp">νμΌλ‘</a>
</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©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>
<a href="joinForm.jsp">νμλ±λ‘</a>
<a href="memberForm.jsp">νμλͺ©λ‘μ‘°ν/μμ </a>
<a href="SaleForm.jsp">νμ맀μΆμ‘°ν</a>
<a href="index.jsp">νμΌλ‘</a>
</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©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();
}
%>