[SQL]-(111) 구구단 2단 출력

---

[Reference] 초보자를 위한 SQL 200제

---

[SQL 문법]

SQL로 구구단 2단을 출력해보자

WITH LOOP_TABLE as (SELECT LEVEL as NUM
                        FROM DUAL
                        CONNECT BY LEVEL <= 9)
    SELECT '2' || ' X ' || NUM || ' = ' || 2 * NUM AS "2단"
        FROM LOOP_TABLE

[예시]

# 오라클 연동 및 접속
import pandas as pd
import cx_Oracle
dsn=cx_Oracle.makedsn('localhost',1521,'orcl')
db=cx_Oracle.connect('scott','tiger')
cursor=db.cursor()

# SQL 문법
cursor.execute("""
WITH LOOP_TABLE as (SELECT LEVEL as NUM
                        FROM DUAL
                        CONNECT BY LEVEL <= 9)
    SELECT '2' || ' X ' || NUM || ' = ' || 2 * NUM AS "2단"
        FROM LOOP_TABLE
"""
)

row=cursor.fetchall()
colname=cursor.description
col=[]

for i in colname:
    col.append(i[0])

# pandas를 사용한 데이터 프레임 형식으로 변환
emp=pd.DataFrame(row,columns=col)
print(emp)

[결과]

           2단
0   2 X 1 = 2
1   2 X 2 = 4
2   2 X 3 = 6
3   2 X 4 = 8
4  2 X 5 = 10
5  2 X 6 = 12
6  2 X 7 = 14
7  2 X 8 = 16
8  2 X 9 = 18