650 lines
14 KiB
HTML
650 lines
14 KiB
HTML
<!DOCTYPE html>
|
||
<html>
|
||
<head>
|
||
<title>MySQL SQL問題集2</title>
|
||
<meta http-equiv="Content-type" content="text/html;charset=UTF-8">
|
||
|
||
<style>
|
||
/*---------------------------------------------------------------------------------------------
|
||
* Copyright (c) Microsoft Corporation. All rights reserved.
|
||
* Licensed under the MIT License. See License.txt in the project root for license information.
|
||
*--------------------------------------------------------------------------------------------*/
|
||
|
||
body {
|
||
font-family: "Segoe WPC", "Segoe UI", "SFUIText-Light", "HelveticaNeue-Light", sans-serif, "Droid Sans Fallback";
|
||
font-size: 14px;
|
||
padding: 0 12px;
|
||
line-height: 22px;
|
||
word-wrap: break-word;
|
||
}
|
||
|
||
#code-csp-warning {
|
||
position: fixed;
|
||
top: 0;
|
||
right: 0;
|
||
color: white;
|
||
margin: 16px;
|
||
text-align: center;
|
||
font-size: 12px;
|
||
font-family: sans-serif;
|
||
background-color:#444444;
|
||
cursor: pointer;
|
||
padding: 6px;
|
||
box-shadow: 1px 1px 1px rgba(0,0,0,.25);
|
||
}
|
||
|
||
#code-csp-warning:hover {
|
||
text-decoration: none;
|
||
background-color:#007acc;
|
||
box-shadow: 2px 2px 2px rgba(0,0,0,.25);
|
||
}
|
||
|
||
|
||
body.scrollBeyondLastLine {
|
||
margin-bottom: calc(100vh - 22px);
|
||
}
|
||
|
||
body.showEditorSelection .code-line {
|
||
position: relative;
|
||
}
|
||
|
||
body.showEditorSelection .code-active-line:before,
|
||
body.showEditorSelection .code-line:hover:before {
|
||
content: "";
|
||
display: block;
|
||
position: absolute;
|
||
top: 0;
|
||
left: -12px;
|
||
height: 100%;
|
||
}
|
||
|
||
body.showEditorSelection li.code-active-line:before,
|
||
body.showEditorSelection li.code-line:hover:before {
|
||
left: -30px;
|
||
}
|
||
|
||
.vscode-light.showEditorSelection .code-active-line:before {
|
||
border-left: 3px solid rgba(0, 0, 0, 0.15);
|
||
}
|
||
|
||
.vscode-light.showEditorSelection .code-line:hover:before {
|
||
border-left: 3px solid rgba(0, 0, 0, 0.40);
|
||
}
|
||
|
||
.vscode-dark.showEditorSelection .code-active-line:before {
|
||
border-left: 3px solid rgba(255, 255, 255, 0.4);
|
||
}
|
||
|
||
.vscode-dark.showEditorSelection .code-line:hover:before {
|
||
border-left: 3px solid rgba(255, 255, 255, 0.60);
|
||
}
|
||
|
||
.vscode-high-contrast.showEditorSelection .code-active-line:before {
|
||
border-left: 3px solid rgba(255, 160, 0, 0.7);
|
||
}
|
||
|
||
.vscode-high-contrast.showEditorSelection .code-line:hover:before {
|
||
border-left: 3px solid rgba(255, 160, 0, 1);
|
||
}
|
||
|
||
img {
|
||
max-width: 100%;
|
||
max-height: 100%;
|
||
}
|
||
|
||
a {
|
||
color: #4080D0;
|
||
text-decoration: none;
|
||
}
|
||
|
||
a:focus,
|
||
input:focus,
|
||
select:focus,
|
||
textarea:focus {
|
||
outline: 1px solid -webkit-focus-ring-color;
|
||
outline-offset: -1px;
|
||
}
|
||
|
||
hr {
|
||
border: 0;
|
||
height: 2px;
|
||
border-bottom: 2px solid;
|
||
}
|
||
|
||
h1 {
|
||
padding-bottom: 0.3em;
|
||
line-height: 1.2;
|
||
border-bottom-width: 1px;
|
||
border-bottom-style: solid;
|
||
}
|
||
|
||
h1, h2, h3 {
|
||
font-weight: normal;
|
||
}
|
||
|
||
h1 code,
|
||
h2 code,
|
||
h3 code,
|
||
h4 code,
|
||
h5 code,
|
||
h6 code {
|
||
font-size: inherit;
|
||
line-height: auto;
|
||
}
|
||
|
||
a:hover {
|
||
color: #4080D0;
|
||
text-decoration: underline;
|
||
}
|
||
|
||
table {
|
||
border-collapse: collapse;
|
||
}
|
||
|
||
table > thead > tr > th {
|
||
text-align: left;
|
||
border-bottom: 1px solid;
|
||
}
|
||
|
||
table > thead > tr > th,
|
||
table > thead > tr > td,
|
||
table > tbody > tr > th,
|
||
table > tbody > tr > td {
|
||
padding: 5px 10px;
|
||
}
|
||
|
||
table > tbody > tr + tr > td {
|
||
border-top: 1px solid;
|
||
}
|
||
|
||
blockquote {
|
||
margin: 0 7px 0 5px;
|
||
padding: 0 16px 0 10px;
|
||
border-left: 5px solid;
|
||
}
|
||
|
||
code {
|
||
font-family: Menlo, Monaco, Consolas, "Droid Sans Mono", "Courier New", monospace, "Droid Sans Fallback";
|
||
font-size: 14px;
|
||
line-height: 19px;
|
||
}
|
||
|
||
body.wordWrap pre {
|
||
white-space: pre-wrap;
|
||
}
|
||
|
||
.mac code {
|
||
font-size: 12px;
|
||
line-height: 18px;
|
||
}
|
||
|
||
pre:not(.hljs),
|
||
pre.hljs code > div {
|
||
padding: 16px;
|
||
border-radius: 3px;
|
||
overflow: auto;
|
||
}
|
||
|
||
/** Theming */
|
||
|
||
.vscode-light,
|
||
.vscode-light pre code {
|
||
color: rgb(30, 30, 30);
|
||
}
|
||
|
||
.vscode-dark,
|
||
.vscode-dark pre code {
|
||
color: #DDD;
|
||
}
|
||
|
||
.vscode-high-contrast,
|
||
.vscode-high-contrast pre code {
|
||
color: white;
|
||
}
|
||
|
||
.vscode-light code {
|
||
color: #A31515;
|
||
}
|
||
|
||
.vscode-dark code {
|
||
color: #D7BA7D;
|
||
}
|
||
|
||
.vscode-light pre:not(.hljs),
|
||
.vscode-light code > div {
|
||
background-color: rgba(220, 220, 220, 0.4);
|
||
}
|
||
|
||
.vscode-dark pre:not(.hljs),
|
||
.vscode-dark code > div {
|
||
background-color: rgba(10, 10, 10, 0.4);
|
||
}
|
||
|
||
.vscode-high-contrast pre:not(.hljs),
|
||
.vscode-high-contrast code > div {
|
||
background-color: rgb(0, 0, 0);
|
||
}
|
||
|
||
.vscode-high-contrast h1 {
|
||
border-color: rgb(0, 0, 0);
|
||
}
|
||
|
||
.vscode-light table > thead > tr > th {
|
||
border-color: rgba(0, 0, 0, 0.69);
|
||
}
|
||
|
||
.vscode-dark table > thead > tr > th {
|
||
border-color: rgba(255, 255, 255, 0.69);
|
||
}
|
||
|
||
.vscode-light h1,
|
||
.vscode-light hr,
|
||
.vscode-light table > tbody > tr + tr > td {
|
||
border-color: rgba(0, 0, 0, 0.18);
|
||
}
|
||
|
||
.vscode-dark h1,
|
||
.vscode-dark hr,
|
||
.vscode-dark table > tbody > tr + tr > td {
|
||
border-color: rgba(255, 255, 255, 0.18);
|
||
}
|
||
|
||
.vscode-light blockquote,
|
||
.vscode-dark blockquote {
|
||
background: rgba(127, 127, 127, 0.1);
|
||
border-color: rgba(0, 122, 204, 0.5);
|
||
}
|
||
|
||
.vscode-high-contrast blockquote {
|
||
background: transparent;
|
||
border-color: #fff;
|
||
}
|
||
</style>
|
||
|
||
<style>
|
||
/* Tomorrow Theme */
|
||
/* http://jmblog.github.com/color-themes-for-google-code-highlightjs */
|
||
/* Original theme - https://github.com/chriskempson/tomorrow-theme */
|
||
|
||
/* Tomorrow Comment */
|
||
.hljs-comment,
|
||
.hljs-quote {
|
||
color: #8e908c;
|
||
}
|
||
|
||
/* Tomorrow Red */
|
||
.hljs-variable,
|
||
.hljs-template-variable,
|
||
.hljs-tag,
|
||
.hljs-name,
|
||
.hljs-selector-id,
|
||
.hljs-selector-class,
|
||
.hljs-regexp,
|
||
.hljs-deletion {
|
||
color: #c82829;
|
||
}
|
||
|
||
/* Tomorrow Orange */
|
||
.hljs-number,
|
||
.hljs-built_in,
|
||
.hljs-builtin-name,
|
||
.hljs-literal,
|
||
.hljs-type,
|
||
.hljs-params,
|
||
.hljs-meta,
|
||
.hljs-link {
|
||
color: #f5871f;
|
||
}
|
||
|
||
/* Tomorrow Yellow */
|
||
.hljs-attribute {
|
||
color: #eab700;
|
||
}
|
||
|
||
/* Tomorrow Green */
|
||
.hljs-string,
|
||
.hljs-symbol,
|
||
.hljs-bullet,
|
||
.hljs-addition {
|
||
color: #718c00;
|
||
}
|
||
|
||
/* Tomorrow Blue */
|
||
.hljs-title,
|
||
.hljs-section {
|
||
color: #4271ae;
|
||
}
|
||
|
||
/* Tomorrow Purple */
|
||
.hljs-keyword,
|
||
.hljs-selector-tag {
|
||
color: #8959a8;
|
||
}
|
||
|
||
.hljs {
|
||
display: block;
|
||
overflow-x: auto;
|
||
color: #4d4d4c;
|
||
padding: 0.5em;
|
||
}
|
||
|
||
.hljs-emphasis {
|
||
font-style: italic;
|
||
}
|
||
|
||
.hljs-strong {
|
||
font-weight: bold;
|
||
}
|
||
</style>
|
||
|
||
<style>
|
||
/*
|
||
* Markdown PDF CSS
|
||
*/
|
||
|
||
body {
|
||
font-family: "Meiryo", "Segoe WPC", "Segoe UI", "SFUIText-Light", "HelveticaNeue-Light", sans-serif, "Droid Sans Fallback";
|
||
}
|
||
|
||
pre {
|
||
background-color: #f8f8f8;
|
||
border: 1px solid #cccccc;
|
||
border-radius: 3px;
|
||
overflow-x: auto;
|
||
white-space: pre-wrap;
|
||
overflow-wrap: break-word;
|
||
}
|
||
|
||
pre:not(.hljs) {
|
||
padding: 23px;
|
||
line-height: 19px;
|
||
}
|
||
|
||
blockquote {
|
||
background: rgba(127, 127, 127, 0.1);
|
||
border-color: rgba(0, 122, 204, 0.5);
|
||
}
|
||
|
||
.emoji {
|
||
height: 1.4em;
|
||
}
|
||
|
||
/* for inline code */
|
||
:not(pre):not(.hljs) > code {
|
||
color: #C9AE75; /* Change the old color so it seems less like an error */
|
||
font-size: inherit;
|
||
}
|
||
|
||
/* Page Break : use <div class="page"/> to insert page break
|
||
-------------------------------------------------------- */
|
||
.page {
|
||
page-break-after: always;
|
||
}
|
||
|
||
</style>
|
||
|
||
</head>
|
||
<body>
|
||
<h1>MySQL SQL問題集 part２</h1>
|
||
<p><strong>SQL応用</strong></p>
|
||
<p><a href="../Question1/MySQL_Questions1.html">MySQL SQL問題集 part１</a></p>
|
||
<h2><strong>問１ テーブル作成</strong></h2>
|
||
<p>図のようなテーブルを作成して、データを登録してください。</p>
|
||
<h3>テーブル名</h3>
|
||
<p><strong>item_category</strong></p>
|
||
<h3>カラム</h3>
|
||
<table>
|
||
<thead>
|
||
<tr>
|
||
<th style="text-align:left">物理カラム名</th>
|
||
<th style="text-align:center">論理カラム名</th>
|
||
<th style="text-align:center">型</th>
|
||
<th style="text-align:center">主キー</th>
|
||
<th style="text-align:center">NOT NULL</th>
|
||
<th style="text-align:center">その他</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td style="text-align:left">category_id</td>
|
||
<td style="text-align:center">カテゴリID</td>
|
||
<td style="text-align:center">INT</td>
|
||
<td style="text-align:center">〇</td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center">自動採番</td>
|
||
</tr>
|
||
<tr>
|
||
<td style="text-align:left">category_name</td>
|
||
<td style="text-align:center">カテゴリ名</td>
|
||
<td style="text-align:center">VARCHAR(256)</td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center">〇</td>
|
||
<td style="text-align:center"></td>
|
||
</tr>
|
||
<tr>
|
||
<td style="text-align:left"></td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center"></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<p><img src="../img/question2/図1.png" alt="図1"></p>
|
||
<h2><strong>問２ テーブル作成</strong></h2>
|
||
<p>図のようなテーブルを作成して、データを登録してください。</p>
|
||
<h3>テーブル名</h3>
|
||
<p><strong>items</strong></p>
|
||
<h3>カラム</h3>
|
||
<table>
|
||
<thead>
|
||
<tr>
|
||
<th style="text-align:left">物理カラム名</th>
|
||
<th style="text-align:center">論理カラム名</th>
|
||
<th style="text-align:center">型</th>
|
||
<th style="text-align:center">主キー</th>
|
||
<th style="text-align:center">外部キー</th>
|
||
<th style="text-align:center">NOT NULL</th>
|
||
<th style="text-align:center">その他</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td style="text-align:left">item_id</td>
|
||
<td style="text-align:center">アイテムID</td>
|
||
<td style="text-align:center">INT</td>
|
||
<td style="text-align:center">〇</td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center">自動採番</td>
|
||
</tr>
|
||
<tr>
|
||
<td style="text-align:left">name</td>
|
||
<td style="text-align:center">アイテム名</td>
|
||
<td style="text-align:center">VARCHAR(256)</td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center">〇</td>
|
||
<td style="text-align:center"></td>
|
||
</tr>
|
||
<tr>
|
||
<td style="text-align:left">price</td>
|
||
<td style="text-align:center">価格</td>
|
||
<td style="text-align:center">INT</td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center">〇</td>
|
||
<td style="text-align:center"></td>
|
||
</tr>
|
||
<tr>
|
||
<td style="text-align:left">category_id</td>
|
||
<td style="text-align:center">カテゴリID</td>
|
||
<td style="text-align:center">INT</td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center">item_categoryのcategory_id</td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center"></td>
|
||
</tr>
|
||
<tr>
|
||
<td style="text-align:left"></td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center"></td>
|
||
<td style="text-align:center"></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<p><img src="../img/question2/図2.png" alt="図2"></p>
|
||
<h2><strong>問３ SELECT文作成</strong></h2>
|
||
<p>図のような検索結果になるSQLを作成してください。</p>
|
||
<table>
|
||
<thead>
|
||
<tr>
|
||
<th style="text-align:left">条件</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td style="text-align:left">itemsテーブルとitem_categoryテーブルを結合</td>
|
||
</tr>
|
||
<tr>
|
||
<td style="text-align:left">category_idのカラムは一個だけしか出さないこと</td>
|
||
</tr>
|
||
<tr>
|
||
<td></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<p><img src="../img/question2/図3.png" alt="図3"></p>
|
||
<p>※A5SQLを使用した場合は図と同じ表示になりません。その場合、一番右側のカラム名はname_1で構いません。</p>
|
||
|
||
<h2><strong>問４ SELECT文作成</strong></h2>
|
||
<p>図のような検索結果になるSQLを作成してください。</p>
|
||
<table>
|
||
<thead>
|
||
<tr>
|
||
<th style="text-align:left">条件</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td style="text-align:left">priceを低い⇒高いに並びかえる</td>
|
||
</tr>
|
||
<tr>
|
||
<td></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<p><img src="../img/question2/図4.png" alt="図4"></p>
|
||
<p>※A5SQLを使用した場合は図と同じ表示になりません。その場合、一番右側のカラム名はname_1で構いません。</p>
|
||
|
||
<h2><strong>問５ SELECT文作成</strong></h2>
|
||
<p>図のような検索結果になるSQLを作成してください。</p>
|
||
<table>
|
||
<thead>
|
||
<tr>
|
||
<th style="text-align:left">条件</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td style="text-align:left">カテゴリが家具のデータのみ抽出</td>
|
||
</tr>
|
||
<tr>
|
||
<td></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<p><img src="../img/question2/図5.png" alt="図5"></p>
|
||
<p>※A5SQLを使用した場合は図と同じ表示になりません。その場合、一番右側のカラム名はname_1で構いません。</p>
|
||
|
||
<h2><strong>問６ SELECT文作成</strong></h2>
|
||
<p>図のような検索結果になるSQLを作成してください。</p>
|
||
<table>
|
||
<thead>
|
||
<tr>
|
||
<th style="text-align:left">条件</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td style="text-align:left">金額が1,000円から50,000円のものだけ抽出</td>
|
||
</tr>
|
||
<tr>
|
||
<td></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<p><img src="../img/question2/図6.png" alt="図6"></p>
|
||
<p>※A5SQLを使用した場合は図と同じ表示になりません。その場合、一番右側のカラム名はname_1で構いません。</p>
|
||
|
||
<h2><strong>問７ SELECT文作成</strong></h2>
|
||
<p>図のような検索結果になるSQLを作成してください。</p>
|
||
<table>
|
||
<thead>
|
||
<tr>
|
||
<th style="text-align:left">条件</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td style="text-align:left">itemsのレコード数を抽出</td>
|
||
</tr>
|
||
<tr>
|
||
<td style="text-align:left">別名をつけて抽出</td>
|
||
</tr>
|
||
<tr>
|
||
<td></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<p><img src="../img/question2/図7.png" alt="図7"></p>
|
||
<h2><strong>問８ SELECT文作成</strong></h2>
|
||
<p>図のような検索結果になるSQLを作成してください。</p>
|
||
<table>
|
||
<thead>
|
||
<tr>
|
||
<th style="text-align:left">条件</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td style="text-align:left">items内の一番価格が高いレコードを抽出</td>
|
||
</tr>
|
||
<tr>
|
||
<td style="text-align:left">サブクエリを使うこと</td>
|
||
</tr>
|
||
<tr>
|
||
<td></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<p><img src="../img/question2/図8.png" alt="図8"></p>
|
||
<p>※A5SQLを使用した場合は図と同じ表示になりません。その場合、一番右側のカラム名はname_1で構いません。</p>
|
||
|
||
<h2><strong>問９ SELECT文作成</strong></h2>
|
||
<p>図のような検索結果になるSQLを作成してください。</p>
|
||
<table>
|
||
<thead>
|
||
<tr>
|
||
<th style="text-align:left">条件</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td style="text-align:left">nameから「の」を含むものだけ抽出</td>
|
||
</tr>
|
||
<tr>
|
||
<td></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<p><img src="../img/question2/図9.png" alt="図9"></p>
|
||
<p>※A5SQLを使用した場合は図と同じ表示になりません。その場合、一番右側のカラム名はname_1で構いません。</p>
|
||
|
||
</body>
|
||
</html>
|