MySQL SELECT Statement

Explain SELECT Statement by providing examples for the following.

1. Retrieving Individual Columns
2. Retrieving multiple Columns
3. Retrieving ALL Columns
4. Retrieving Distinct Rows

Select statement allows selecting and retrieving data from tables.

Syntax:
Select * | column_name from table name

Retrieving Individual Columns
Select employee_name from employee Where emp_id > 1

Retrieving multiple Columns
Select employee_firstname, employee_lastname from employee Where emp_id > 1

Retrieving ALL Columns
Select * from employee Where emp_id > 1

Retrieving Distinct Rows
Select disctint mobile_number From employee

State and explain generic SQL syntax of SELECT command along with GROUP BY clause to sort data from MySQL table.

Group By is used along with aggregate functions to group the result-set by one or more columns.

Here, the employees are sorted by employee name.

Example : The query below will display employees along with their salaries.
SELECT employee_name,SUM(salary) FROM employee
GROUP BY employee_name

Explain how to use ORDER BY clause inside PHP Script.

ORDER BY clause in PHP is used to sort the data in the result set. It sorts data in ascending manner by default. To sort data in descending order, DESC is used.

Example:
//connect to database
mysql_select_db("my_db", $con);
//Query to list employees ordered by AGE
$result = mysql_query("SELECT * FROM Employee ORDER BY age");
?>

Explain how to use ORDER BY clause at Command Prompt.

ORDER BY clause in command prompt should usually be used when a ON syntax on a column name is used. Usually the columns used in the BREAK command should be in the same order if ORDER BY is used. This is not necessary if vice versa.

Example:
SELECT DEPARTMENT_ID, JOB_ID, SALARY, LAST_NAME
FROM EMP_DETAILS
WHERE SALARY > 10000
ORDER BY DEPARTMENT_ID, JOB_ID, SALARY, LAST_NAME;
The above syntax returns valid results if BREAK is used before executing.
State generic SQL syntax and an example of SELECT command along with LIKE clause to fetch data from MySQL table.

LIKE clause is used for pattern matching. % is used to match any string of any length where as _ allows you to match on a single character.

Syntax:
SELECT * FROM table_name
WHERE column_name like 'pattern%';
Example:
SELECT * FROM employee
WHERE emp_name like 'ma%';
MySQL sorting data
Sort data: The ORDER BY clause in MySQL can be used to sort the specified column.
MySQL where clause
MySQL where clause - Explain the use of Where clause
MySQL operator - AND, OR and IN operator
MySQL operator - The LIKE operator is used to search for a specified pattern in a column.
Post your comment

You may like