INFORMIX SQL

p4-29 練習先安裝 stores7 在Linux OS下 isqldemo
#若無法安裝可能是欄位區隔符號的問題
#先 echo $DBDELIMITER #( unload , load text file 時的欄位區隔符號 !)

#目前 是 ^A , Defult 是 | DBDELIMITER=”|”; export DBDELIMITER echo $DBDELIMITER
#—-> 若是 = | , 即可 Run isqldemo

執行 select * from customer
—————————————————————-
結果
customer_num 118
fname Dick
lname Baxter
company Blue Ribbon Sports
address1 5427 College
address2 city Oakland
state CA
zipcode 94609
phone 415-655-0011

18 row(s) retrieved.

===================================================
查詢前10筆的資料 執行 select FRIST 10 customer_num,lname FROM customer
———————————————————————–
結果
customer_num lname
101 Pauli
102 Sadler
103 Currie
104 Higgins
105 Vector
106 Watson
107 Ream
108 Quinn
109 Miller
110 Jaeger

10 row(s) retrieved.
===================================================
將庫存單位是”case”,依stock_num, manu_code, description, unit排列出來
執行 SELECT stock_num, manu_code, description, unit FROM stock WHERE unit = “case”
———————————————————————–
結果
stock_num manu_code description unit
1 HRO baseball gloves case
1 HSK baseball gloves case
1 SMT baseball gloves case
2 HRO baseball case
3 HSK baseball bat case
4 HSK football case
4 HRO football case
6 SMT tennis ball case
6 ANZ tennis ball case
7 HRO basketball case
8 ANZ volleyball case

11 row(s) retrieved.
===================================================
將庫存單位不是”case”,依stock_num, manu_code, description, unit排列出來
執行 SELECT stock_num, manu_code, description, unit FROM stock WHERE unit != “case”
———————————————————————–
結果
stock_num manu_code description unit
5 NRG tennis racquet each
5 SMT tennis racquet each
5 ANZ tennis racquet each
9 ANZ volleyball net each

4 row(s) retrieved.
===================================================
將customer中address2是NULL的,依 lname, phone排列出來
執行 SELECT lname, phone FROM customer WHERE address2 IS NULL;
————————————————————————-
結果
lname phone
Pauli 408-789-8075
Sadler 415-822-1289
Vector 415-776-3249
Watson 415-389-8789
Ream 415-356-9876
Quinn 415-544-8729
Jaeger 415-743-3611
Keyes 408-277-7245
Lawson 415-887-7235
Beatty 415-356-9982
Albertson 415-886-6677
Grant 415-356-1123
Parmelee 415-534-8822
……… …………
lname phone
Sipes 415-245-4578
Baxter 415-655-0011

15 row(s) retrieved.
===================================================
將customer中address2不是NULL的,依 lname, phone排列出來
執行 SELECT lname, phone FROM customer WHERE address2 IS NOT NULL;
———————————————————————————————————
結果
lname phone
Currie 415-328-4543
Higgins 415-368-1100
Miller 408-723-8789

3 row(s) retrieved.
===================================================
將customer中,CITY是Los Altos 和state 是CA依 fname,lname排列出來
執行 SELECT fname,lname FROM customer WHERE city = “Los Altos” AND state =”CA”;
———————————————————————————————————
結果
fname lname
Raymond Vector
Margaret Lawson

2 row(s) retrieved.

===================================================
將customer中,state是CA或AZ, 依 fname,lname排列出來
執行 SELECT fname,lname FROM customer WHERE state =”CA” OR state =”AZ”;
———————————————————————————————————
結果
fname lname
Ludwig Pauli
Carole Sadler
Philip Currie
Anthony Higgins
Raymond Vector
George Watson
Charles Ream

……. …… 18 row(s) retrieved.
===================================================
將customer中,stateCITY是Los Altos 或state 是CA, 依 fname,lname排列出來
執行 SELECT fname,lname FROM customer WHERE city =”Los Altos” OR state =”AZ”;
———————————————————————-
結果
fname lname
Raymond Vector
Margaret Lawson

2 row(s) retrieved.

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料

分類
BlogUpp!