{
"cells": [
{
"cell_type": "markdown",
"metadata": {
"slideshow": {
"slide_type": "skip"
}
},
"source": [
"NOTE:\n",
"-----\n",
"\n",
"진행하기 전에 다음의 셀을 실행시키고 가자.\n",
"\n",
"Please run the below cells first before proceeding- you'll need them soon!"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"collapsed": false,
"slideshow": {
"slide_type": "skip"
}
},
"outputs": [
{
"data": {
"text/plain": [
"'Connected: @None'"
]
},
"execution_count": 1,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%load_ext sql\n",
"%sql sqlite://"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n",
"Done.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 18,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"DROP TABLE IF EXISTS Movies;\n",
"CREATE TABLE Movies(title VARCHAR(50), year INT, director VARCHAR(50), length INT);\n",
"INSERT INTO Movies VALUES('Database Wars', 1967, 'John Joe', 123);\n",
"INSERT INTO Movies VALUES('The Databaser', 1992, 'John Bob', 190);\n",
"INSERT INTO Movies VALUES('Database Wars', 1998, 'John Jim', 176);"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n",
"Done.\n",
"Done.\n",
"Done.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n"
]
}
],
"source": [
"%sql DROP TABLE IF EXISTS A; DROP TABLE IF EXISTS B;\n",
"%sql CREATE TABLE A (x int, y int); CREATE TABLE B (x int, y int);\n",
"for i in range(1,6):\n",
" %sql INSERT INTO A VALUES (:i, :i+1)\n",
"for i in range(1,11,3):\n",
" %sql INSERT INTO B VALUES (:i, :i+2)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"활동 03a:\n",
"------------\n",
"\n",
"ORDER BY 문법, 집합 연산자와 중첩 질의문\n",
"\n",
"ORDER BY semantics, set operators & nested queries"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"
\n",
" \n",
" title | \n",
" year | \n",
" director | \n",
" length | \n",
"
\n",
" \n",
" Database Wars | \n",
" 1967 | \n",
" John Joe | \n",
" 123 | \n",
"
\n",
" \n",
" The Databaser | \n",
" 1992 | \n",
" John Bob | \n",
" 190 | \n",
"
\n",
" \n",
" Database Wars | \n",
" 1998 | \n",
" John Jim | \n",
" 176 | \n",
"
\n",
"
"
],
"text/plain": [
"[(u'Database Wars', 1967, u'John Joe', 123),\n",
" (u'The Databaser', 1992, u'John Bob', 190),\n",
" (u'Database Wars', 1998, u'John Jim', 176)]"
]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT * FROM movies"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"문제 #1\n",
"-----------\n",
"\n",
"**수업자료에 나온 영화 질의문을 하나의 SFW 질의문으로 바꿔보자**\n",
"\n",
"**Can you write the movie query from lecture as a single SFW query?**\n",
"\n",
"**한 번 이상 제목으로 사용된 모든 영화 제목들**을 찾는 것이 목표이다. 같은 해에는 같은 이름의 제목을 갖는 영화는 없었다고 가정하자. `movies` 테이블의 스키마는 다음과 같다:\n",
"\n",
"Recall that we are trying to find **all movie titles that were used for more than one movie.** You may assume that no two movies in the same year have the same title. Our schema for the `movies` table is:\n",
"\n",
"\n",
"\n",
"> * title STRING\n",
"> * year INT\n",
"> * director STRING\n",
"> * length INT\n",
"\n",
"중첩 질의문으로 해결해보자:\n",
"\n",
"Let's try to write the nested query that solves this from lecture:"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"(sqlite3.OperationalError) near \"SELECT\": syntax error [SQL: u'SELECT m.title \\nFROM Movies m\\nWHERE m.year <> ANY(SELECT year FROM Movie WHERE title = m.title);']\n"
]
}
],
"source": [
"%%sql\n",
"SELECT m.title \n",
"FROM Movies m\n",
"WHERE m.year <> ANY(SELECT year FROM Movie WHERE title = m.title);"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"어라? 동작을 안한다. 왜 그럴까?\n",
"\n",
"What? This doesn't work? Why?\n",
"\n",
"\n",
"**ANY는 SQLite에서 지원하지 않는다!** 중첩을 쓰지 않고 해결할 수 있을까? 질의문을 아래에 작성해보자:\n",
"\n",
"**ANY doesn't exist in SQLite!** Can we do this query without nesting? Write your query here:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"%%sql\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"문제 #2\n",
"--------------------\n",
"\n",
"다음의 릴레이션 $A$ 와 $B$를 살펴보자:\n",
"\n",
"Consider the two relations $A$ and $B$ below:"
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" x | \n",
" y | \n",
"
\n",
" \n",
" 1 | \n",
" 2 | \n",
"
\n",
" \n",
" 2 | \n",
" 3 | \n",
"
\n",
" \n",
" 3 | \n",
" 4 | \n",
"
\n",
" \n",
" 4 | \n",
" 5 | \n",
"
\n",
" \n",
" 5 | \n",
" 6 | \n",
"
\n",
"
"
],
"text/plain": [
"[(1, 2), (2, 3), (3, 4), (4, 5), (5, 6)]"
]
},
"execution_count": 22,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT * FROM A;"
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" x | \n",
" y | \n",
"
\n",
" \n",
" 1 | \n",
" 3 | \n",
"
\n",
" \n",
" 4 | \n",
" 6 | \n",
"
\n",
" \n",
" 7 | \n",
" 9 | \n",
"
\n",
" \n",
" 10 | \n",
" 12 | \n",
"
\n",
"
"
],
"text/plain": [
"[(1, 3), (4, 6), (7, 9), (10, 12)]"
]
},
"execution_count": 23,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT * FROM B;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"중복된 값이 없다는 가정 아래, **$x$ 속성에 대해서만** `INTERSECT` 또는 중첩 질의를 쓰지 않고`INTERSECT` 질의문의 결과를 출력하도록 작성해보자. 아래에 작성해보자:\n",
"\n",
"Assuming no duplicates, can you write an `INTERSECT` query, **just over the $x$ attribute**, without using `INTERSECT` OR nested queries? Write your query here:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"%%sql\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"이런 연산자를 무엇이라 부르는가?\n",
"\n",
"다음으로는, 마찬가지로 집합 연산자를 사용 해서 해결해보자. 속성 $x$가 겹치는 $A$와 $B$의 모든 터플들을 돌려주는 질의문을 작성해보자. 아래에 작성해보자.\n",
"\n",
"What is this operation called?\n",
"\n",
"Next, using set operators again as well, can you return all the _full_ tuples in $A$ and $B$ that overlap in $x$ attributes? Write your query here:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"%%sql\n"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.5"
}
},
"nbformat": 4,
"nbformat_minor": 2
}