面接のための SQL の基本および中級の質問-mysql チュートリアル-php.cn

本題に入りましょう。

面接の質問に備え、SQL クエリを練習するために、Awesome SQL Interview GitHub リポジトリを作成しました。 SQL クエリを、基本 (L0)、中級 (L1)、および上級 (L2) の 3 つのセクションに分割しました。これが基本セクションの解決策です。

これは練習用の L1 (中間) SQL クエリです。より良い練習のために、最初に L0 を参照してください。

注: これらの例は MySQL でテストされています。 MS-SQL や Oracle などの他のデータベースでは構文が異なる場合があります。

L1: 中間 SQL

JOIN、GROUP BY、HAVING、および複雑な WHERE 条件を使用した、複数のテーブルの操作を伴うクエリ。
サブクエリ、集計関数、case ステートメントの概要。

質問:

「米国」と「フランス」の顧客の顧客名と都市を取得するクエリを作成します。
「サンフランシスコ」オフィスで働くすべての従業員のemployeeNumber、lastName、officeCodeを取得するにはどうすればよいですか?
注文テーブルと顧客テーブルを使用して、各顧客の注文の合計数を見つけるクエリを作成します。
10 回以上注文された製品の productName、quantityInStock、buyPrice を取得するにはどうすればよいですか?
customerNumber が 103 の顧客が行った注文の orderNumber、status、customerName を取得するクエリを作成します。
orderdetails テーブル内の各注文の合計売上額 (quantityOrdered * PriceEach) を見つけるクエリを作成します。
orderdetail テーブル内の各 orderNumber の平均数量Ordered を見つけるにはどうすればよいですか?
orderdetails テーブルで合計収益 (quantityOrdered * PriceEach) が最も高い productLine をリストするクエリを作成します。
employee テーブルと office テーブルを結合して、employeeNumber、firstName、lastName、および従業員が勤務するオフィス名を表示するクエリを作成します。
注文したことがない顧客をどのように見つけますか?
customerName と各顧客 (まだ注文していない顧客も含む) による注文の合計数を取得するクエリを作成します。
注文された製品の数量が 50 を超えるすべての注文について、productName とquantityOrdered を検索するクエリを作成します。
注文した顧客に営業担当者として割り当てられた従業員の従業員番号、名、注文番号を取得します。
buyPrice に基づいて製品テーブル内の製品の平均価格を計算するクエリを作成します。
製品テーブルで最も高価な製品のトップ 3 を取得するにはどうすればよいですか?
ステータスが「発送済み」であるすべての注文の customerName、orderNumber、orderDate を取得するクエリを作成します。
各製品ラインの販売製品の総数を表示するにはどうすればよいですか?
employeeNumber = 1143 の従業員の直属の従業員を検索するクエリを作成します。
注文テーブル内の注文の合計数をステータスごとにグループ化して計算するクエリを作成します。
従業員をマネージャーの名前とともにリストします。

間違ったことについても言及します。何をすべきかを知ることは重要ですが、何をしてはいけないのか、どこで間違いを犯すのかを知ることも非常に重要です。もう一度本題に戻りましょう...

必要な場所に説明を含む解決策

「米国」と「フランス」の顧客の顧客名と都市を取得するクエリ。
- または ->条件が多い場合、クエリは各条件を 1 つずつチェックするため、少し遅くなります。
- IN ->特に長いリストの場合、データベースエンジンによって内部的にわずかに最適化されます。
- 2 ～ 3 つの条件ではどちらも問題ありません。読みやすさとスケーラビリティの点では、特に大きな値のリストを処理する場合は IN の方が優れています。
- IS は、文字列比較ではなく、IS NULL や IS NOT NULL などの条件をチェックするために使用されます。
「サンフランシスコ」オフィスで働くすべての従業員のemployeeNumber、lastName、officeCodeを取得します。
注文テーブルと顧客テーブルを使用して、各顧客の注文の合計数を見つけるクエリを実行します。
- クエリで集計関数を使用する場合は、常に GROUP BY 句に非集計列を含めてください。
- これにより、SQL が行をグループ化する方法を認識し、追加の列を選択する際の曖昧さを回避できるようになります。
- この例では、COUNT(*) と一緒に選択しているため、customerNumber と customerName は両方とも GROUP BY 句に含まれている必要があります。
?黄金律:
SELECT リストのすべての列は次のいずれかを行う必要があります:
GROUP BY 句内にある、または
COUNT()、SUM() などの集計関数を使用します。
10 回以上注文された製品の productName、quantityInStock、buyPrice を取得しますか?
- このクエリは中小規模のデータベースでは効率的ですが、大規模なサイズの場合はインデックスを使用し、HAVING 句のみに依存するのではなく WHERE 句を使用してスキャンされるデータを減らすことができます
customerNumber が 103 の顧客が発注した注文の orderNumber、status、customerName を取得します。

説明:
- 使用されるテーブル:
  - orders: orderNumber と status が含まれます。
  - customers: customerName が含まれます。
- 内部結合:
  - customerNumber 列 (共通キー) を使用して、orders テーブルと customer テーブルを結合します。
- WHERE 句:
  - customerNumber = 103 のレコードのみを含むようにデータをフィルターします。
- 選択された列:
  - o.orderNumber: 注文番号。
  - o.status: 注文ステータス。
  - c.customerName: 注文を行った顧客の名前。
orderdetails テーブルで各注文の合計売上額 (quantityOrdered * PriceEach) を見つけます。
orderdetails テーブル内の各 orderNumber の平均数量Ordered を見つけます。
- 説明:
- 注文番号:
  - orderNumber で行をグループ化します。
- AVG(注文数量):
  - 同じ orderNumber に属するすべての行の平均数量Ordered を計算します。
- グループ化:
  - 注文番号ごとに平均が個別に計算されるようにします。
orderdetails テーブル内の総収益 (quantityOrdered * PriceEach) が最も高い productLine をリストするクエリ。
- 説明:
- 製品ライン:
  - 製品を「オートバイ」や「飛行機」などのさまざまなラインに分類します。
- SUM(od.quantityOrdered * od.priceEach):
  - 各製品ラインの総収益を計算します。
- 内部結合:
  - productCode で製品テーブルと orderdetail テーブルを結合し、製品ラインを注文詳細に関連付けます。
- p.productLine ごとにグループ化:
  - 各製品ラインごとに結果をグループ化します。
- 合計収益 DESC で注文:
  - グループ化された結果を収益の降順に並べ替えるので、最も高い収益が最初に表示されます。
- 制限 1:
  - 収益が最も高い productLine のみに結果を制限します。
従業員テーブルとオフィステーブルを結合することにより、従業員番号、名、姓、および従業員が勤務するオフィス名を表示するクエリ。
- CONCAT(列, 'セパレータ', 列, 'セパレータ', 列)
- CONCAT_WS('セパレータ', 列)
注文したことがない顧客を検索

説明:
- LEFT JOIN: 注文テーブルに一致する行があるかどうかに関係なく、顧客テーブルからすべての顧客を取得します。
- o.orderNumber IS NULL: 対応する注文がない顧客を識別します (つまり、注文テーブルに一致するものがないため、orderNumber は NULL です)。
- 列:
  - customerNumber: 顧客の一意の識別子。
  - customerName: 顧客の名前。
customerName と各顧客 (まだ注文していない顧客も含む) による注文の合計数を取得するクエリ。
注文された製品の数量が 50 を超えるすべての注文について、productName と数量Ordered を検索します。
注文した顧客に営業担当者として割り当てられた従業員の従業員番号、名、注文番号を取得します。

説明:
- 従業員から:
  - 従業員の詳細、特に従業員番号と名が必要なため、従業員テーブル (エイリアスは e) から始めます。
- 顧客を結合します。 c ON e.employeeNumber = c.salesRepEmployeeNumber:
  - 従業員からのemployeeNumberと顧客からのsalesRepEmployeeNumberでcustomersテーブル(cというエイリアス)に結合します。これにより、従業員 (営業担当者) と顧客との関係が構築されます。これで、各顧客にどの従業員が割り当てられているかを識別できるようになりました。
- JOIN 命令 o ON c.customerNumber = o.customerNumber:
  - さらに、customerNumber を使用して、orders テーブル (o というエイリアス) を customer テーブルと結合します。これにより、各顧客が行った注文がわかります。
- SELECT e.employeeNumber、e.firstName、o.orderNumber:
  - 最後に、従業員テーブル (営業担当者) から従業員番号と名を選択し、注文を行った各顧客の注文テーブルから注文番号を選択します。
buyPrice に基づいて製品テーブル内の製品の平均価格を計算するクエリ。
製品テーブルで最も高価な製品のトップ 3 を取得しますか?
ステータスが「発送済み」であるすべての注文の customerName、orderNumber、orderDate を再取得します。
各 productLine の販売製品の総数を表示します
employeeNumber = 1143 の従業員の直属の従業員を検索します。
ステータスごとにグループ化された、注文テーブル内の注文の合計数を計算するクエリ。
従業員をマネージャーの名前とともにリストします。