select statement in SQL

Select Statement-:A select statement retrieves information from the database. Using a SELECT statement, We can do the following:
Select : We can use the selection capability in SQL to choose the rows in a table that we want returned by a query. We can use various criteria to selectively restrict the rows that we see.
Projection : We can use the projection capability in SQL to choose the columns in a table that we want returned by your query. We can choose as few or as many columns of the table as you require.
Join : We can use the join capability in SQL to bring together data that is stored in different tables by creating a link between them.
In its simplest form, a SELECT statement must include the following:
⦁ A SELECT clause, which specifies the columns to be displayed
⦁ A FROM clause,which specifies the table containing the columns listed in the SELECT clause.

Select All Columns, All Rows

SELECT  * FROM dept;
We can display all columns of data in a table by following the SELECT keyword with an asterisk (*). In the example, the department table contains three columns. DEPTNO,DNAME, and LOC. The table contains four rows, one for each department.

Selecting Specific Columns in All Rows-:
We can use the SELECT statement to display specific columns of the table by specific the column names, separated by commas. In SELECT clause, specify the columns that you want to see, in the order in which we want them to appear in the output.
SELECTloc, deptno FROM dept;


Default Columns Heading-:
Character column heading and data as well as date column heading and data are left-justified within a column width. Number headings and data are right-justified.
SELECT ename, hiredate, sal FROM emp;

Arithmetic Expressions
We may need to modify the way in witch data is displayed, perform calculations, or look at what-if scenarios. This possible using arithmetic expressions. An arithmetic expression may contain column names, constant numeric values, and the arithmetic operators.

here are arithmetic operators-:

Add      (  +), Subtract (-), Multiply (*), division(/). These are operators.

Arithmetic Operators
We can use arithmetic operators in any clause of a SQL statement except the FROM clause.

Operator Precedence
⦁ Multiplication and division take priority over addition and subtraction.
⦁ Operators of the same priority are evaluated from left to right.
⦁ Parentheses are used to force prioritized evaluation and to clarify statements.

Defining a Null Value -: If a row lacks the data value for a particular column, that value is said to be null, or to contain hull. A null value is a value that is unavailable, unassigned, unknown, or inapplicable. A null value is not the same as zero or a space. Zero is a number, and a space is a character.

Defining a Column Alias -:

⦁ Renames a column heading.
⦁ Is useful with calculations.
⦁ Immediately follows column name; optional AS keyword between column nae=me and alias.
⦁ Requires double quotation marks if it contains spaces or special characters(such as # or $), or is case sensitive.
Concatenation Operator
⦁ Concatenates columns or character strings to other columns.
⦁ Is represented by two vertical bars(::).
⦁ creates a resultant column that is a character expression.
Literal Character Strings
⦁ A literal is a character, a number, or a date included in the SELECT list.
⦁ Date and character literal values must be enclosed within single quotation marks.
⦁ Each character string is output once for each row returned.

Duplicate Rows-:The default display of queries is all rows, including duplicate rows.Unless we indicate otherwise, SQL * Plus displays the results of a query without eliminating duplicate rows.
To eliminate duplicate rows in the result, include DISTINCT keyword in the SELECT clause immediately after the SELECT keyword.

 

 

Leave a Comment