# SQL teamB

### ToDoリスト
- 値が空ではないデータ取得方法を調べる
- 空でないデータを表示
- アクセスログID、郵便番号、都道府県、施設名、氏名を表示
- ジッコウ

### ToDoリスト
- prefectureが佐賀県かつ、entryPageがlive400の人を取得する
- アクセスログID、都道府県、氏名、エントリーページを表示
- ジッコウ

SELECT
accessLogId,
prefecture,
name,
entryPage
FROM
access_logs
WHERE
prefecture='佐賀県'
AND
entryPage='live400'
;

佐賀県＋live400

### ToDoリスト
- accessTimeが3時～4時のログを取得
- ログの数をカウントして出力（SQLカウント）
- ジッコウ

SELECT
COUNT( * )
FROM
access_logs
WHERE
accessTime
BETWEEN
'2023-01-22 03:00:00'
AND
'2023-01-22 04:59:59'
;

### ToDoリスト
- 講演会名「テスト29講演会」のアクセスログを取得
- 取得したデータを入室時間の昇順で表示
- ジッコウ

SELECT
*
FROM
access_logs
WHERE
conference_info_id = 29
ORDER BY
accessTime
ASC
;

### ToDoリスト
- 参加登録の都道府県が「東京都」と「北海道」のログを取得
- ログの数をカウントして出力（SQLカウント）
- カラムの名前を分かりやすい名前に変更（asで）
- 都道府県と都道府県の数を表示
- ジッコウ

SELECT
COUNT( * )
FROM
access_logs
WHERE
prefecture = '東京都'
;

SELECT
prefecture as 都道府県,
COUNT(prefecture) as カウント
FROM
access_logs
WHERE
prefecture = '東京都'
OR
prefecture = '北海道'
GROUP BY
prefecture
;

東京　31
北海道31

### ToDoリスト
- 都道府県ごとのアクセスログで40人以上アクセスのログを取得
- ログの数をカウントして出力（SQLカウント）
- カラムの名前を分かりやすい名前に変更（asで）
- 都道府県と都道府県の数を表示
- ジッコウ

SELECT
prefecture ,
COUNT(prefecture) AS count
FROM
access_logs
GROUP BY
prefecture
HAVING
count >= 40
;

### ToDoリスト
- エイリアス付与とは何か調べる
- 別名をつけること
- SQL結合の仕方を調べる
- 内部結合 INNER JOIN
- access_logsを基準として、enquete_answersが結合された全てのaccess_logsを表示する
- 表示するカラム名は({access_logs: [ accessLogID, uid, name, created_at]}, { enquete_answers: [ id, uid]})
- カラム名はすべてエイリアスを付与
- 実行

SELECT
  *
FROM
  table1
INNER JOIN
  table2
ON
  table1.id = table2.id
;


SELECT
  access_logs.accessLogID as accses_log_id
, access_logs.uid as access_log_uid
, access_logs.name as access_log_name
, access_logs.created_at as access_log_created_at
, enquete_answers.id as ebquete_answer_id
FROM
  access_logs
, enquete_answers
WHERE
  access_logs.accessLogId = enquete_answers.access_log_id


SELECT
  access_logs.accessLogID AS accesslog_ID,
  access_logs.uid AS userID,
  access_logs.name AS name,
  access_logs.created_at AS created_at,
  enquete_answers.id AS enquete_answers_ID,
  enquete_answers.uid AS enquete_answers_uid
FROM
  access_logs,
  enquete_answers
WHERE
  access_logs.accessLogId = enquete_answers.access_log_id



SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.id;

### ToDoリスト
- SQL結合の仕方を調べる（３つ）
-
- access_logsを基準として、enquete_answers, user_listsが結合された全てのaccess_logsを表示する（全部表示）
- 表示するカラム名は({access_logs: [ accessLogID, uid, name, created_at]}, { enquete_answers: [ id, uid]}, {user_list: [userID, userPassword]})
- カラム名はすべてエイリアスを付与
- 実行

SELECT
access_logs.accessLogID AS accesslog_ID,
access_logs.uid AS userID,
access_logs.name AS name,
access_logs.created_at AS created_at,
enquete_answers.id AS enquete_answers_ID,
enquete_answers.uid AS enquete_answers_uid,
user_lists.userID AS user_list_userID,
user_lists.userPassword AS user_list_userPassword
FROM
access_logs
/* 2つめ */
LEFT OUTER JOIN
enquete_answers
ON
access_logs.accessLogId = enquete_answers.access_log_id
/* 3つめ */
LEFT OUTER JOIN
user_lists
ON
access_logs.user_list_id = user_lists.userListId
;

### ToDoリスト
- SQL結合の仕方を調べる（３つ）
-
- access_logsを基準として、enquete_answers, user_listsが結合された全てのaccess_logsを表示する（全部表示）
- 表示するカラム名は({access_logs: [ accessLogID, uid, name, created_at]}, { enquete_answers: [ id, uid]}, {user_list: [userID, userPassword]})
- カラム名はすべてエイリアスを付与
- 実行

### ToDoリスト
- 表示するカラム名は({access_logs: [ conference_info_id, accessLogID, uid, name, accessTime, stopTime]}, {user_lists: [userID, userPassword]})
- カラム名はすべてエイリアスを付与
- ユニークで表示について調べる
- 「accessTime」は最も値の小さいもの、「stopTime」は最も値の大きいものを表示
- 「テスト25講演会」のみを表示する
- 実行

### ToDoリスト
- アクセスログにアンケートログを結合(LEFT JOIN)
- 表示するカラムは({access_logs: [ uid, prefecture, facility, name]}, { enquete_answers: [ answer1, answer2, answer3, answer4, answer5]})
- 都道府県と施設をを繋げて表示する方法を調べる
- 非表示（INNER JOIN??）
- カラム名のエイリアスは適宜付与
- 「テスト3講演会」のアクセスログのみ表示
- ジッコウ

SELECT
  access_logs.uid
, CONCAT(access_logs.prefecture,access_logs.facility) AS location
, access_logs.name
, enquete_answers.answer1
, enquete_answers.answer2
, enquete_answers.answer3
, enquete_answers.answer4
, enquete_answers.answer5
, COUNT(*)
FROM
  access_logs
INNER JOIN
  enquete_answers
ON
  access_logs.accessLogId = enquete_answers.access_log_id
WHERE
  access_logs.conference_info_id = 3
;

COUNT(prefecture) AS prefecture_count

### ToDoリスト
- アクセルログの「テスト10講演会」を表示
- 都道府県の奈良県と群馬県を取得
- 都道府県(奈良県と群馬県)、参加人数の平均（AVG）
- 参加人数の合計（SUM）
- 参加人数の平均は小数点以下切り捨ての方法を調べる
- カラム名のエイリアスは適宜付与
- 実行

SELECT
access_logs.prefecture
, FLOOR(AVG(access_logs.participants_number)) AS average
, SUM(access_logs.participants_number) AS summation
FROM
access_logs
WHERE
access_logs.conference_info_id = 10
AND
prefecture = "奈良県"
OR
prefecture = "群馬県"

GROUP BY
prefecture
;

### ToDoリスト
- アクセスログの「テスト29講演会」を表示
- IPアドレスのユニークな人数を表示
- ユーザーエージェントは「Firefox」からのアクセスを除外
- 本番視聴のログのみを表示
- カラム名のエイリアスは適宜付与
- 実行

SELECT

FROM
access_logs
WHERE
access_logs.conference_info_id = 29
AND
entryPage = "live400"
OR
prefecture = "群馬県"

GROUP BY
prefecture
;

entryPage

live400

and

userAgent

Firefox

SELECT
COUNT(DISTINCT(access_logs.hostIP)) as hostIP
FROM
access_logs
WHERE
access_logs.conference_info_id = 29
AND
entryPage LIKE '%live400%'
AND
not userAgent LIKE '%Firefox%'
;

### ToDoリスト
- アクセスログの滞在時間を計算(退室時間－入室時間＝滞在時間)
- 滞在時間のフォーマットは'HH:mm'で表示
- カラムはアクセスログIDと入室時間と退室時間と滞在時間
- カラム名のエイリアスは適宜付与
- 実行

accessTime
stopTime
accessLogId

SELECT
access_logs.accessLogId
, access_logs.accessTime
, access_logs.stopTime
, access_logs.stopTime - access_logs.accessTime
FROM
access_logs
WHERE

;

### ToDoリスト
- アンケートのアンサーごとの合計と平均の出し方を調べる
- 条件でイベントID毎と全てのイベントでの平均を出す
- カラムは「イベントID」、「イベントID毎：アンケート１の平均値」、「全てのイベント：アンケート１の平均値」...（answer1からanswer5まで）
- 実行

SELECT
count(*) as count_unique
FROM
(
SELECT
enquete_answers.conference_info_id as eventid,
AVG(enquete_answers.answer1) as answer1AVG
FROM
enquete_answers
GROUP BY
conference_info_id
;

SELECT
enquete_answers.conference_info_id as eventid,
AVG(enquete_answers.answer1) as answer1avg,
(SELECT
enquete_answers.conference_info_id as eventid,
AVG(enquete_answers.answer1) as answer1AVG
FROM
enquete_answers
)
FROM
enquete_answers
GROUP BY
conference_info_id
;

enquete_answers.answer2,
enquete_answers.answer3,
enquete_answers.answer4,
enquete_answers.answer5,

SELECT
  enquete_answers.conference_info_id as eventid,
  AVG(enquete_answers.answer1) as answer1avg,
  (
    SELECT
      AVG(enquete_answers.answer1)
    FROM
      enquete_answers
  ) as answer1AVG,
FROM
  enquete_answers
GROUP BY
  conference_info_id
;

### ToDoリスト
- access_logsテーブルで「テスト1講演会」のレコードを全て出力する
- 「入室時間」のフォーマットを「%Y-%m-%d %k:%i」で表示
- 「入室が2023-01-27 19:10:00まで～」のカラムに19:10:00までなら○、19:10:01以降なら×を表示する
- カラムは「アクセスログID」、「入室時間」、「入室が2023-01-27 19:10:00までのカラム」
- 実行

SELECT
  access_logs.accessLogId as accessLog_id
, DATE_FORMAT(access_logs.accessTime, '%Y-%m-%d %k:%i') as accessTime
, IF(accessTime > '2023-01-27 19:10', "TRUE", "FASE") 
FROM
  access_logs
WHERE
  conference_info_id = 1
;

### ToDoリスト
- access_logsテーブルで最後にアクセスしたレコードを講演会毎に出力
- 表示するカラムは「講演会ID」、「アクセスログID」、「入室時間」
- 「入室時間」のフォーマットは「%Y-%m-%d %k:%i」
- 実行

SELECT
  access_logs.conference_info_id as conference_id
, access_logs.accessLogId as accessid
, DATE_FORMAT(MAX(access_logs.accessTime), '%Y-%m-%d %k:%i') as accessTime
FROM
  access_logs
GROUP BY
  
;

サブクエリを使う

### ToDoリスト
- レコードを１件追加を調べる
- SQL練習用テーブルにレコードを１件追加
- データ作成者にチーム名を入力
- 講演会名に「班員の誰かの名前 + 講演会」 を入力
- データ作成時間とデータ更新時間には現在時刻を入力
- その他のカラムはNullとする

### ToDoリスト
- レコードを更新する方法を調べる
- 講演会情報に適当な文を入力
- データ更新時間に現在時刻を入力

### ToDoリスト（3）
- SQL練習用テーブルにレコードを３件追加
- 本日の日付出し方を調べる
- ＋1日、＋1ヶ月の計算方法を調べる
- 1時間後を調べる
- データ作成者にチーム名を入力
- 講演会名に「班員の誰かの名前 + 講演会」 を入力
- 講演会日に（データ1件目: 本日の日付）（データ2件目: 明日の日付）（データ3件目: １か月後の日付）
- 講演会時間に（データ1件目: 現在時刻から１時間後）（データ2件目: 現在時刻から1日後）（データ3件目: 現在時刻から1か月後）
- 入室時間はデータ3件それぞれに任意の時間を入力(講演会日、講演会時間との関係は考えなくて良い)
- 退室時間はデータ3件それぞれに任意の時間を入力(入室時間より遅い時間を設定・講演会日、講演会時間との関係は考えなくて良い)
- 参加人数は班員の人数を入力
- データ作成時間とデータ更新時間には現在時刻を入力
- その他のカラムはNullとする

データ1件目: 中浜
データ2件目: 北原
データ3件目: 諸熊

|　　データ作成者　　|　　講演会名　　| 講演会日 | 入室時間 | 退室時間 |
| ---- | ---- | ---- | ---- | ---- |
|　　人　　|　　人＋講演会　　| 本日の日付 | 任意 | 入室時間よりあとの時間 |
|　　人　　|　　人＋講演会　　| 明日の日付 | 任意 | 入室時間よりあとの時間 |
|　　人　　|　　人＋講演会　　| 1か月後の日付 | 任意 | 入室時間よりあとの時間 |

### ToDoリスト（4）
- 問題３で作成した３つのレコードを更新
- 本日講演に本日講演の場合はtrue、違う場合はfalseと入力(講演会日や講演会時間を参考に)
- 滞在時間を入力(入室時間・退室時間から計算)
- データ更新時間に現在時刻を入力

### ToDoリスト（2/21 - 1）
- カラムは全て取得
- conference_info_idが3 かつ
- １問目の回答が「2」 かつ
- ２問目の回答が「3」 かつ
- ３問目の回答が「3」 かつ
- ４問目の回答が「3」 かつ
- ５問目の回答が「1」

テーブル「enquete_answers」内のデータから

SELECT
  *
FROM
  enquete_answers
WHERE
  conference_info_id = 3
AND
  answer1 = 2
AND
  answer2 = 3
AND
  answer3 = 3
AND
  answer4 = 3
AND
  answer5 = 1
;

### ToDoリスト（2/21 - 2）
- カラムは「都道府県 - 施設 / 氏名」「accessTime」「stopTime」を取得（例：全て空のとき「 - / 」、都道府県が空のとき「 - 施設名 / 氏名」...その他の場合も一緒）
- カラムには適宜エイリアスがつける
- 「stopTime」で降順、10件だけ表示
- 日付のフォーマット 「%Y-%m-%d %k:%i:%s」

テーブル「access_logs」内のデータから

SELECT
  CONCAT(IFNULL(access_logs.prefecture, '' ),' - ', IFNULL(access_logs.facility, '' ) , ' / ', IFNULL(access_logs.name, '' )) AS facility
 ,DATE_FORMAT(access_logs.accessTime, '%Y-%m-%d %k:%i:%s') AS access
 ,DATE_FORMAT(access_logs.stopTime, '%Y-%m-%d %k:%i:%s') AS stop
FROM
  access_logs
ORDER BY
  stopTime DESC
LIMIT
  10
;

A = 'takeshi'
B = null

resultA = IFNULL(A, '')
resultB = IFNULL(B, '')
resultA => 'takeshi'
resultB => ''

CONCAT('yamasaki', '-', 'shunsuke', '/', '31歳')
=> yamasaki-shunsuke/31歳
欲しい答え「都道府県 - 施設 / 氏名」

IFNULL(, '' )
ifnull( null, '' )

### ToDoリスト（2/21 - 3）
- アクセスログテーブルの都道府県ごとの参加人数(participants_number)を集計
- 都道府県ごとの参加人数が500人以上のレコードのみを表示
- 都道府県がnullのレコードは表示なし
- 表示するカラムは都道府県と参加人数の合計を表示

SELECT
  prefecture
 ,SUM(participants_number) AS particsum
FROM
  access_logs
WHERE
  prefecture IS NOT NULL
GROUP BY
  prefecture
HAVING
  particsum >= 500
;

### ToDoリスト（2/21 - 4）
- アクセスログの2023年1月27日の19:15～19:30の間にアクセスされた
- IP(142.53.10.72)でFirefoxを使って視聴している方のログインIDとパスワードを表示
- カラムはログインIDとパスワードのみ表示

SELECT
  user_lists.userID AS ID
 ,user_lists.userPassword AS PW
FROM
  access_logs
LEFT JOIN
  user_lists
ON
  user_lists.userListId = access_logs.user_list_id
WHERE
  access_logs.hostIP = '142.53.10.72'
AND
  access_logs.userAgent LIKE '%Firefox%'
AND
  access_logs.accessTime
BETWEEN
  '2023-01-27 19:15:00'
AND
  '2023-01-27 19:30:00'
;

### ToDoリスト（2/21 - 5）
- ログインID「RKCNo」でログインされ
- アンケートの問１の答えを2と回答されたアクセスログ
- カラムはアクセスログテーブル(都道府県、施設、氏名)、アンケートログテーブル(問１の答え)、ユーザーIDリストテーブル(ログインID)
-

SELECT
  access_logs.prefecture AS 
 ,access_logs.facility
 ,access_logs.name
 ,enquete_answers.answer1
 ,user_lists.userID
FROM
  access_logs
LEFT JOIN
  user_lists
ON
LEFT JOIN
  user_lists.userListId = access_logs.user_list_id
WHERE
  user_lists.userID = 'RKCNo'
AND
  enquete_answers.answer1 = 2
;