How to view specific records from a table?

To view specific records from table, we need to specify the criteria by using special operators called as relational and logical operators.

The relational operators are used to compare two values and logical operators are used to combine two conditions.

SQL provides WHERE clause to apply the criteria or the condition.

Relational Operators

=                                             Equals to

<>                                           Not equals to

>                                             Greater than

>=                                           Greater than or equals to

<                                             Less than

<=                                           Less than or equals to

IN                                           To define choice of values          
BETWEEN…AND                To define range of values

LIKE                                        To specify the search pattern with %

 

Logical Operators

and                                        both conditions must be true

or                                            any one condition must be true

not                                         negate result of a condition (true becomes false and false becomes true)

 

Syntax 1: All columns with all records (no criteria)

SELECT * FROM tablename;

Example

Write SQL statements to view all columns of all the records in book table.

USE demodb;

SELECT * FROM book;

 

Syntax 2: Specific columns with all records (no criteria)

SELECT columnname1, columnname2,… FROM tablename;

Example

Write SQL Statements to display booknumber, title and price of all the books.

USE demodb;

SELECT booknumber, title, price FROM book;

 

Syntax 3: Display all columns with criteria based records

SELECT * FROM tablename WHERE condition;

 

Example

Write SQL statement to display all the columns from book table having price greater than or equals to 500

USE demodb;

SELECT * FROM book WHERE price>=500;

 

Write SQL statement to display all the columns from book table having price 500 or 900 

USE demodb;

SELECT * FROM book WHERE price=500 or price=900;

 

Alternate version

USE demodb;

SELECT * FROM book WHERE price IN (500, 900);

 

Write SQL statement to display the book records having word ‘Data’ somewhere in title.

USE demodb;

SELECT * FROM book WHERE title LIKE '%Data%';

 

Write SQL statement to display all the books having price ranging from 400 to 700.

USE demodb;

SELECT * FROM book WHERE price BETWEEN 400 AND 700;