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 ColumnsSelect employee_name from employee Where emp_id > 1
Retrieving multiple ColumnsSelect employee_firstname, employee_lastname from employee Where emp_id > 1
Retrieving ALL ColumnsSelect * from employee Where emp_id > 1
Retrieving Distinct RowsSelect 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%';
