Define multiple conditions in a clause : And « Where Clause « SQL / MySQL






  1. SQL / MySQL
  2. Where Clause
  3. And

Define multiple conditions in a clause

       
mysql>
mysql> CREATE TABLE CDs
    -> (
    ->     CDID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     CDName VARCHAR(50) NOT NULL,
    ->     InStock SMALLINT UNSIGNED NOT NULL,
    ->     OnOrder SMALLINT UNSIGNED NOT NULL,
    ->     Reserved SMALLINT UNSIGNED NOT NULL,
    ->     Department ENUM('Classical', 'Popular') NOT NULL,
    ->     Category VARCHAR(20) NOT NULL,
    ->     RowUpdate TIMESTAMP NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO CDs (CDName, InStock, OnOrder, Reserved, Department, Category) VALUES
    -> ('Xml', 10, 5, 3, 'Popular', 'Rock'),
    -> ('Java', 10, 5, 3, 'Classical', 'Opera'),
    -> ('SQL', 17, 4, 1, 'Popular', 'Jazz'),
    -> ('MySQL', 9, 4, 2, 'Classical', 'Dance'),
    -> ('CSS', 24, 2, 5, 'Classical', 'General'),
    -> ('HTML', 16, 6, 8, 'Classical', 'Vocal'),
    -> ('Oracle', 2, 25, 6, 'Popular', 'Blues'),
    -> ('Javascript', 32, 3, 10, 'Popular', 'Jazz'),
    -> ('Data type', 12, 15, 13, 'Popular', 'Country'),
    -> ('Flash', 5, 20, 10, 'Popular', 'New Age'),
    -> ('Ajax', 24, 11, 14, 'Popular', 'New Age'),
    -> ('Photoshop', 42, 17, 17, 'Classical', 'General'),
    -> ('Word', 25, 44, 28, 'Classical', 'Dance'),
    -> ('iPhone', 32, 15, 12, 'Classical', 'General'),
    -> ('MacBook', 20, 10, 5, 'Classical', 'Opera'),
    -> ('Linux', 23, 12, 8, 'Classical', 'General'),
    -> ('Shell', 23, 10, 17, 'Popular', 'Country'),
    -> ('Pascal', 18, 20, 10, 'Popular', 'Jazz'),
    -> ('Ruby', 22, 5, 7, 'Popular', 'Blues'),
    -> ('Sql Server', 28, 17, 16, 'Classical', 'General'),
    -> ('Opera', 10, 35, 12, 'Classical', 'Opera'),
    -> ('Safari', 15, 30, 14, 'Popular', 'Blues'),
    -> ('C', 42, 0, 8, 'Popular', 'Blues'),
    -> ('C++', 16, 8, 8, 'Classical', 'General');
Query OK, 24 rows affected (0.00 sec)
Records: 24  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT CDName, InStock+OnOrder-Reserved AS Available
    -> FROM CDs
    -> WHERE Category='Blues' AND (InStock+OnOrder-Reserved)>30;
+--------+-----------+
| CDName | Available |
+--------+-----------+
| Safari |        31 |
| C      |        34 |
+--------+-----------+
2 rows in set (0.00 sec)

mysql>
mysql> drop table CDs;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.Combine conditions
2.Use AND to combine conditions
3.AND and OR may be intermixed
4.Combine conditions in select clause
5.Use AND for int value
6.The AND construct means that both clauses must be true.
7.To display the author who painted each book
8.retrieve the name of the customer placing order 4
9.Put the entire original expression in parentheses and negate the whole thing with NOT
10.Compare two conditions
11.And 1