Select Operation in DBMS

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.

Translate »