SQL練習プログラムで公開しています。SQLiteを使ったテストのtipsを読んでSQLiteをオンメモリで使えるということを知りました。早速SQLの練習用Perlスクリプトに反映しました。最初はPerlスクリプト中にSQLを埋め込む形だったのですけどなんだかんだでやっぱ…

今ではSQL Server Express EditionなんていうのもあってSQLを練習するための環境は結構簡単に用意できるのですが、ちょっとSQLの練習をしたいというときにそんな重たいソフトを起動するのも面倒ですし、テーブルを作ったりテストデータを登録したりというの…

これまでの考え方の延長なので簡単です。 DELETE FROM Products WHERE ProductID NOT IN ( SELECT ProductID FROM Sales ) ; 相関副問い合わせも使えます。 DELETE FROM Products WHERE NOT EXISTS ( SELECT 'X' FROM Sales WHERE Sales.ProductID = Product…

WHERE句で削除するレコードの条件を指定します。 DELETE FROM Employees WHERE EmployeeID = 17 ;

DELETE FROM Salary ; 全件削除の場合はtruncateを使っているのでこのパターンはあまり使いません。DELETE と TRUNCATE TABLE の違いを見ると、Oracleの場合運用系でのtruncateは注意した方が良いみたいですね。SQL Serverはどうなんでしょう？

UPDATE Salary SET Amount = Amount + ( SELECT (2007 - e.HireFiscalYear) * 1000 FROM Employees AS e WHERE Salary.EmployeeID = e.EmployeeID ) WHERE PayDate = '2008-02-14' AND EXISTS ( SELECT 'X' FROM Employees AS e WHERE Salary.EmployeeID = e…

UPDATE Products SET Price = Price * 1.01 WHERE ProductID IN ( SELECT ProductID FROM Sales GROUP BY ProductID HAVING SUM( Quantity ) > 100 ) ; SELECTの時の副問い合わせと特に変わることはないので難しくはありません。

UPDATE Customers SET Address = '世田谷区たがやせ1丁目' WHERE CustomerID = 5 ; 書き順は1)UPDATE、2)テーブル名、3)WHERE、4)条件、5)SET、6)更新内容、です。SELECTのときと同じで先に条件を書いてから、対象となる項目を書きます。

updateです。簡単。 UPDATE Products SET Price = Price * 0.97 ;

INSERT INTO Salary ( SalaryID , EmployeeID , PayDate , Amount ) SELECT s.EmployeeID + 100000 , s.EmployeeID , '2008-02-14' , SUM( s.Quantity * p.Price * 0.001 ) FROM Sales AS s JOIN Products AS p ON s.ProductID = p.ProductID GROUP BY s.Emp…

INSERTです。簡単。 INSERT INTO Products ( ProductID , ProductName , Price , CategoryID ) VALUES ( 101 , 'サカナまっしぐら' , 270 , 5 ) ; というのが本で推奨する書き方ですが、下の書き方の方が読むのも書くのも慣れています。開始の括弧を改行後の…

差集合。使ったことがありません。Oracleの場合はminus。 SELECT EmployeeName FROM Employees EXCEPT SELECT e.EmployeeName FROM Sales AS s JOIN Employees AS e ON s.EmployeeID = e.EmployeeID ;

積集合。使ったことありません。 SELECT e.EmployeeName AS 氏名 , s.SaleDate AS 日付 FROM Sales AS s JOIN Employees AS e ON s.EmployeeID = e.EmployeeID INTERSECT SELECT e.EmployeeName AS 氏名 , s.PayDate AS 日付 FROM Salary AS s JOIN Employee…

SELECT CustomerName AS 氏名 FROM Customers UNION SELECT EmployeeName AS 氏名 FROM Employees ; UNION ALL同様selectの結果をまとめます。重複があった場合は一つにまとめます。まとめる処理がある分UNION ALLよりかは処理コストがかかるので、重複がな…

SELECT CustomerName AS 氏名 FROM Customers UNION ALL SELECT EmployeeName AS 氏名 FROM Employees ; 複数のselectの結果をまとめます。重複があった場合それぞれ結果として残ります。

意外とよく使う相関副問い合わせです。各レコードの値に対してSQLを実行するというというイメージ。以下の例ではs1の各レコードに対してWHERE句の中にある括弧内のSQLを実行するというような感じです。 SELECT p.ProductName , s1.SaleDate FROM Sales AS s1…

同じテーブル同士を結合するというだけで、通常の結合と同じです。結合対象のテーブル名が同じなので、別名を付けることが必要になります。 SELECT p1.ProductName AS 商品名1 , p2.ProductName AS 商品名2 , (p1.Price + p2.Price) AS セット価格 FROM Prod…

SELECT p.ProductName , AVG( p.Price * CASE WHEN s.Quantity IS NULL THEN 0 ELSE s.Quantity END ) AS 平均販売価格 FROM Products AS p LEFT OUTER JOIN Sales AS s ON s.ProductID = p.ProductID GROUP BY p.ProductName ; JOINのところがLEFT OUTER JO…

GROUP BYとの組み合わせ。普通に書けば良いです。 SELECT d.DepartmentName AS 部門名 , AVG(s.Amount) AS 部門別平均給与額 FROM Salary AS s JOIN BelongTo AS b ON s.EmployeeID = b.EmployeeID JOIN Departments AS d ON b.DepartmentID = d.DepartmentI…

この辺りからややこしくなってきます。 SELECT Customers.PrefecturalID , Prefecturals.PrefecturalName AS 都道府県名 , COUNT(*) AS 顧客数 FROM Customers JOIN Prefecturals ON Customers.PrefecturalID = Prefecturals.PrefecturalID GROUP BY Custome…

最初はJOINだと思っていたら、いきなり副問い合わせだったので予想外。 SELECT * FROM Products WHERE ProductID NOT IN ( SELECT ProductID FROM Sales ) ; 練習問題でこんな使い方が出てきました。 SELECT SaleID , Quantity , CustomerID , ( SELECT Cust…

DISTINCTです。結構便利。 SELECT DISTINCT Address AS 住所 FROM Customers ; 書く順番は列名の後。「CustomersテーブルからAddressを取り出して重複を排除する」という順番。

ORDER BYです。 SELECT ProductName FROM Products ORDER BY Price ; 書く順番はHAVINGの後。並び順の制御として、昇順の場合ASC、降順の場合DESCを使います。ASCは省略可能。本ではASCはascendの略、DESCはdescendの略となっていました。私が以前聞いた時に…

SELECT HireFiscalYear AS 入社年度 , SUM( CASE WHEN BloodType = 'A' THEN 1 ELSE 0 END ) AS A型 , SUM( CASE WHEN BloodType = 'B' THEN 1 ELSE 0 END ) AS B型 , SUM( CASE WHEN BloodType = 'O' THEN 1 ELSE 0 END ) AS O型 , SUM( CASE WHEN BloodTyp…

SELECT PrefecturalID AS 都道府県 , COUNT(*) AS 顧客数 FROM Customers WHERE CustomerClassID = 1 GROUP BY PrefecturalID HAVING COUNT(*) >= 2 ; WHERE句とHAVING句の組み合わせ。書き順は1)SELECT、2)FROM、3)テーブル名、4)WHERE、5)条件、6)GROUP BY…

SELECT PrefecturalID AS 都道府県 , COUNT(*) AS 顧客数 FROM Customers GROUP BY PrefecturalID HAVING COUNT(*) >= 3 ; HAVING句の登場。書き順は1)SELECT、2)FROM、3)テーブル名、4)GROUP BY、5)グループ化列名(GROUP BYの方)、6)グループ化列名(SELECT…

SELECT PrefecturalID AS 都道府県 , COUNT(*) AS 顧客数 FROM Customers GROUP BY PrefecturalID ; GROUP BY句の登場。書き順は1)SELECT、2)FROM、3)テーブル名、4)GROUP BY、5)グループ化列名(GROUP BYの方)、6)グループ化列名(SELECTの方)、7)集計関数、…

SELECT ProductName AS 商品名 , CASE WHEN Price < 1000 THEN 'C' WHEN Price < 2000 THEN 'B' ELSE 'A' END AS ランク FROM Products ; CASEが出てきました。便利ですね。Oracle8iを使っていた頃に上記のSQLと似たようなことをしようとしてDECODEとSIGNを…

SELECT count(*) AS 子のつく社員の人数 FROM Employees WHERE EmployeeName LIKE '%子' ; LIKEが出てきました。LIKEを使った場合の文字列の中の%と_は特別な意味があって、%は任意の文字列に一致、_は任意の一文字に一致します。

SELECT EmployeeName as 氏名 FROM Employees WHERE Height >= 180 ; WHERE句の登場です。書き順は1)SELECT、2)FROM、3)テーブル名、4)WHERE、5)条件、6)列名、の順です。「EmployeesテーブルからHeightが180以上の氏名を取り出す。氏名の列名はEmployeeName…