features
Example Advanced 6:
Get all parts whose names begin with the letter C.
SELECT PARTS.* FROM PARTS LIKE 'C%'; WHERE PARTS.PNAME
PN -P5 P6 PNAME COLOR ----- ----CAM BLUE COG RED WEIGHT ---------12 19
CITY --------Paris London
In general, a "LIKE predicate" takes the form
SQL -29
column-name LIKE character-string-constant
Characters within that constant are interpreted as follows :
* The _ character ( break or underscore ) stands for any single character. * The % character (percent) stands for any sequence of n characters ( where n may be zero ) * All other characters simply stand for themselves.
SQL -30
Example 7 :
ADDRESS LIKE '%Berkeley%' will evaluate to true if address contains the sting "Berkeley" anywhere inside it. SNUM LIKE 'S_ _' will evaluate to true if SNUM is exactly three characters long and the first is an "S".
CITY NOT LIKE '%E%'
SQL -31
will evaluate to true if CITY does not contain an "E"
SQL -32
Null Value
1.Value unknown 2.Value inapplicable: For example spouse column for single man. 3.Value Withhold
SQL -33
Null Value
Suppose for the sake of example S5 has a status value NULL, rather than 30. Example 8 : Get supplier numbeer for supplier with status not equal to 25.
SQL -34
SELECT FROM WHERE
SNUM, STATUS SUPPLIERS STATUS > 25 OR NULL = NULL;
The results S# S3 (S5 is not qualified)
SQL> 3 2 4 5 6 7 8
SELECT SNAME FROM SUPPLIERS WHERE SNUM IN ( SELECT SNUM FROM SHIPMENTS WHERE PNUM = 'P2' ) ; SQL -35
SNAME --------Smith Jones Blake Clark
Null Value
None of the following are true
STATUS > 25 STATUS <= 25 STATUS = 25 STATUS <> 25 STATUS =NULL
SQL -36
STATUS <> NULL ***The last two are illegal
(Null has different syntax)
Null Value
A special form: column-name is [not] NULL Example 9
SELECT FROM SNUM SUPPLIERS
SQL -37
WHERE
STATUS is NULL;
The results S# S5 Example 10 :
SELECT FROM WHERE SNUM, STATUS Suppliers STATUS IS NULL;
SQL -38
(S5 is qualified)
The results S#, P# (All are qualified)
SQL -39
Full queries
Example 11 :
Get supplier names for suppliers who does supply part P2.
1) SELECT FROM WHERE SNUN SUPPLIERS SNUM IN (SELECT SNUM FROM SHIPMENTS WHER...