[Oracle SQL] select 연습문제 (61~108)

61.

select stu_no, stu_name
from student 
order by stu_dept, stu_gender desc, stu_grade ; 

62. 

select lower(stu_gender)
from student ;

63.

select upper(stu_gender)
from student;

64.

select stu_dept || stu_name 
from student ;

65.

select stu_dept||'과 '||stu_grade||'학년 '||stu_name||'입니다.'
from student;

66.

select stu_name, substr(stu_name, 1, 2)
from student ;

67.

select stu_name, substr(stu_dept, 2, 1)
from student ;

68. 

select stu_name, length(stu_name)
from student ;

69.

select stu_dept, length(stu_dept)
from student ;

70.

select instr(stu_name, '김')
from student ;

71.

select stu_dept, instr(stu_dept, '기')
from student;

72.

select rpad(stu_name, 15, '&')
from student ; 

73.

select lpad(stu_dept, 20, '%')
from student;

74.

select stu_no, stu_name, round(stu_height, -1)
from student;

75.

select stu_no, stu_name, trunc(stu_height, -2)
from student ; 

76.

select mod(stu_weight, 30)
from student ;

77. 

select nvl(to_char(stu_height), '미기록')
from student;

78.

select nvl(stu_height,0)+nvl(stu_weight, 0), stu_no, stu_name
from student;

// 각각 null 처리해서 합쳐야 한다 

79.

select nvl(stu_height, 0)-nvl(stu_weight, 0), stu_no, stu_name
from student;

80.

select nvl(to_char(stu_height), '입력요망'), stu_no, stu_name
from student ;

81.

select nvl(nullif(stu_gender, 'M'), 'NULL')
from student;

82.

select nvl(nullif(stu_gender, 'M'), '남자')
from student;

그룹함수 활용 

85. 

select max(stu_height) 
from student ;

86.

select min(stu_height)
from student;

87.

select min(stu_weight)
from student ;

88.

select min(stu_name), max(stu_name)
from student ;

 

91.

select count(distinct stu_dept)
from student ;

92.

select count(distinct substr(stu_name, 1, 1))
from student;

93.

select count(*)
from student ;

94.

select count(*)
from enrol ;

95.

select stu_dept, count(*)
from student
group by stu_dept ;

96.

select stu_dept, count(*)
from student
group by stu_dept
order by stu_dept desc;

99.

select stu_dept, avg(stu_weight)
from student
group by stu_dept;

100.

select stu_dept, stddev(stu_weight)
from student
group by stu_dept;