SQL SELECT Tutorial : Searching & Querying the database

The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name. The whole query is called SQL SELECT Statement.

Syntax of SQL SELECT Statement:

SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

[info]In case you are new to databases, Follow this tutorial to download a DBMS and create a database table[/info]
1) Let’s suppose, we have created the following table:

    CREATE TABLE IF NOT EXISTS contacts (
    contacts_id int auto_increment primary key,
    name varchar(45) NOT NULL ,
    lastname varchar(45) NOT NULL,
    email text ,
    age int,
    phone varchar(45) NOT NULL
    )

2) Let’s insert some data into this table : contacts :

    INSERT INTO contacts
    (name,lastname,email,age,phone)
    VALUES
    ('Jerry', 'Yang','jerry@yahoo.com','41','0911'),
    ('Larry', 'Page','larry@google.com','45','0999'),    
    ('Bill', 'Gates','bill@live.com','45','0999'),    
    ('Michael', 'Dell','michael@dell.com','39','9838'),   
    ('Pierre', 'Omidyar','michael@dell.com','46','9838'), 
    ('Charles', 'Flint','charles@ibm.com','120','9838'),
    ('Lee', 'Byung-chull','lee@samsung.com','120','9838'),
    ('Steve', 'Jobs','steve@apple.com','120','9838'),
    ('Bill', 'Hewlett','bill@hp.com','120','9838');

3)Let’s search now for all data:

contacts_id name lastname email age phone
1 Jerry Yang jerry@yahoo.com 41 0911
2 Larry Page larry@google.com 45 0999
3 Bill Gates bill@live.com 45 0999
4 Michael Dell michael@dell.com 39 9838
5 Pierre Omidyar michael@dell.com 46 9838
6 Charles Flint charles@ibm.com 120 9838
7 Lee Byung-chull lee@samsung.com 120 9838
8 Steve Jobs steve@apple.com 120 9838
9 Bill Hewlett bill@hp.com 120 9838

 

4)Search for people whose age is : 45

contacts_id name lastname email age phone
2 Larry Page larry@google.com 45 0999
3 Bill Gates bill@live.com 45 0999

 

5)show only name and last name for people whose age is : 45

name lastname
Larry Page
Bill Gates

 

6)Search for people whose name starts with B

contacts_id name lastname email age phone
3 Bill Gates bill@live.com 45 0999
9 Bill Hewlett bill@hp.com 120 9838

 

7)Search for people whose name ends with e

contacts_id name lastname email age phone
5 Pierre Omidyar michael@dell.com 46 9838
7 Lee Byung-chull lee@samsung.com 120 9838
8 Steve Jobs steve@apple.com 120 9838

 

8)Search for people whose name contains with l or L

contacts_id name lastname email age phone
2 Larry Page larry@google.com 45 0999
3 Bill Gates bill@live.com 45 0999
4 Michael Dell michael@dell.com 39 9838
6 Charles Flint charles@ibm.com 120 9838
7 Lee Byung-chull lee@samsung.com 120 9838
9 Bill Hewlett bill@hp.com 120 9838

 

9)Search for people whose age is either 41, 45 OR 120

contacts_id name lastname email age phone
1 Jerry Yang jerry@yahoo.com 41 0911
2 Larry Page larry@google.com 45 0999
3 Bill Gates bill@live.com 45 0999
6 Charles Flint charles@ibm.com 120 9838
7 Lee Byung-chull lee@samsung.com 120 9838
8 Steve Jobs steve@apple.com 120 9838
9 Bill Hewlett bill@hp.com 120 9838

 

10)Search for people whose age is 45 and their name is Bill

contacts_id name lastname email age phone
3 Bill Gates bill@live.com 45 0999

 

11)Search for people whose age is over 42 , order them by their last name (Ascending).

contacts_id name lastname email age phone
3 Bill Gates bill@live.com 45 0999
9 Bill Hewlett bill@hp.com 120 9838
6 Charles Flint charles@ibm.com 120 9838
2 Larry Page larry@google.com 45 0999
7 Lee Byung-chull lee@samsung.com 120 9838
5 Pierre Omidyar michael@dell.com 46 9838
8 Steve Jobs steve@apple.com 120 9838

 

12)Search only for TWO people whose age is over 42 , order them by their last name (Descending).

contacts_id name lastname email age phone
8 Steve Jobs steve@apple.com 120 9838
5 Pierre Omidyar michael@dell.com 46 9838
No Responses

Leave a Reply

Your email address will not be published. Required fields are marked *