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:
4)Search for people whose age is : 45
5)show only name and last name for people whose age is : 45
6)Search for people whose name starts with B
7)Search for people whose name ends with e
8)Search for people whose name contains with l or L
9)Search for people whose age is either 41, 45 OR 120
10)Search for people whose age is 45 and their name is Bill
11)Search for people whose age is over 42 , order them by their last name (Ascending).
12)Search only for TWO people whose age is over 42 , order them by their last name (Descending).