Select Operation (Query)
We have already seen the very basics of SELECT command. Basically it is used to retrieve the data from the database. We can retrieve only selected categories or subset of data from one or more tables or views or materialized views. It either pulls the entire data from the table/view or pulls specific records based on the condition.
The basic SELECT command is
SELECT * FROM table_name; -- retrieves all the rows and columns from table table_name and displays it in tabular form. SELECT COLUMN1, COLUMN2, COLUMN3 FROM table_name; -- retrieves only 3 columns from table table_name SELECT t1.COLUMN1, t2.COLUMN1 FROM table_name1 t1, table_name2 t2 WHERE t1.COLUMN2 = t2.COLUMN2; -- Combines 2 <a class="wpil_keyword_link " href="https://tutorialcup.com/dbms/tables.htm"target="_blank" rel="noopener" title="tables" data-wpil-keyword-link="linked">tables</a> and retrieves specific columns from both the tables.
Some examples of SELECT:
SELECT * FROM STUDENT; — All the columns are retrieved and displayed in below format
SELECT STUDENT_NAME, ADDRESS FROM STUDENT; — Retrieves only name and address from STUDENT table
SELECT e.EMPLOYEE_NAME, e.ADDRESS, e.PHONE_NUMBER, d.DEPARTMENT_NAME
FROM EMPLOYEE e, DEPARTMENT d
WHERE E.DEPARTMENET_ID = D.DEPARTMENT_ID; — Displays employee name, address, phone number and the department name for which he works, by joining EMPLOYEE and DEPARTMENT tables.
We can combine two or more columns into one column by using || in select statement.
SELECT EMP_FIRST_NAME || ‘ ‘ || EMP_LAST_NAME AS emp_name
FROM EMPLOYEE WHERE EMP_ID = 1001; — Here first name and last name of employee with id 1001 to show it as emp_name
General syntax of SELECT is:
SELECT column_list FROM table_name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];
Where
WHERE Clause– here we can specify the filter conditions to the query. We can add any number of conditions.
GROUP BY– Groups the same categories of data together and displays sum, total count, maximum and minimum values, average etc
ORDER BY- sorts the result records in ascending or descending order.