The SELECT statement is used to query a database, and it is the most commonly used statement. It is a type of Data manipulation language (DML) statements. Refer here for types of SQL statements.
SELECT can be used to write simple queries as well as complex query. It can also be used with other statements like CREATE, INSERT, UPDATE statements.
Following are a few simple SELECT statements
1. Querying All Columns of a table:
2. Querying Individual Columns:
3. Querying Unique or DISTINCT columns:
4. Querying multiple Unique/DISTINCT columns:
5. SELECT statement with Column Alias:
6. Querying with Arithmetic Operators:
7. SQL Character String Concatenation - Querying with Concatenation Operators:
The two vertical bars || are used for concatenation of characters or string in SQL.
SELECT can be used to write simple queries as well as complex query. It can also be used with other statements like CREATE, INSERT, UPDATE statements.
Following are a few simple SELECT statements
1. Querying All Columns of a table:
hr@ORA10G> SELECT * FROM jobs;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20000 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20000
SA_REP Sales Representative 6000 12000
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2000 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500
19 rows selected.
2. Querying Individual Columns:
hr@ORA10G> SELECT job_title, max_salary FROM jobs;
JOB_TITLE MAX_SALARY
----------------------------------- ----------
President 40000
Administration Vice President 30000
Administration Assistant 6000
Finance Manager 16000
Accountant 9000
Accounting Manager 16000
Public Accountant 9000
Sales Manager 20000
Sales Representative 12000
Purchasing Manager 15000
Purchasing Clerk 5500
Stock Manager 8500
Stock Clerk 5000
Shipping Clerk 5500
Programmer 10000
Marketing Manager 15000
Marketing Representative 9000
Human Resources Representative 9000
Public Relations Representative 10500
19 rows selected.
3. Querying Unique or DISTINCT columns:
hr@ORA10G> SELECT DISTINCT max_salary FROM jobs;
MAX_SALARY
----------
9000
12000
5000
5500
10000
10500
8500
30000
16000
15000
40000
6000
20000
13 rows selected.
4. Querying multiple Unique/DISTINCT columns:
Note that here DISTINCT works on both the columns. The rows where both the selected columns are identical are removed. For e.g. the entry {4200, 9000} appears twice in the entire table.hr@ORA10G> SELECT DISTINCT min_salary, max_salary FROM jobs; MIN_SALARY MAX_SALARY ---------- ---------- 8200 16000 4000 10000 4500 10500 5500 8500 9000 15000 4000 9000 8000 15000 2000 5000 20000 40000 10000 20000 6000 12000 2500 5500 3000 6000 4200 9000 15000 30000 15 rows selected.
5. SELECT statement with Column Alias:
The AS keyword used before the alias is optional. The double quotes (") is mandatory if alias name has space and case preservation is required.hr@ORA10G> SELECT job_title AS "Job Designation", max_salary AS maxsal FROM jobs; Job Designation MAXSAL ----------------------------------- ---------- President 40000 Administration Vice President 30000 Administration Assistant 6000 Finance Manager 16000 Accountant 9000 ................ Human Resources Representative 9000 Public Relations Representative 10500 19 rows selected.
6. Querying with Arithmetic Operators:
We can use all these operators (+, -, *, /) as per the needs.hr@ORA10G> SELECT job_title, max_salary + min_salary AS Sum_MaxMin_Sal FROM jobs; JOB_TITLE SUM_MAXMIN_SAL ----------------------------------- -------------- President 60000 Administration Vice President 45000 Administration Assistant 9000 Finance Manager 24200 ............. Human Resources Representative 13000 Public Relations Representative 15000 19 rows selected.
7. SQL Character String Concatenation - Querying with Concatenation Operators:
The two vertical bars || are used for concatenation of characters or string in SQL.
hr@ORA10G> SELECT 'Our ' || job_title || ' earns max salary ' || max_salary FROM jobs;
'OUR'||JOB_TITLE||'EARNSMAXSALARY'||MAX_SALARY
--------------------------------------------------------------------------------
Our President earns max salary 40000
Our Administration Vice President earns max salary 30000
Our Administration Assistant earns max salary 6000
Our Finance Manager earns max salary 16000
Our Accountant earns max salary 9000
Our Accounting Manager earns max salary 16000
Our Public Accountant earns max salary 9000
Our Sales Manager earns max salary 20000
Our Sales Representative earns max salary 12000
Our Purchasing Manager earns max salary 15000
Our Purchasing Clerk earns max salary 5500
Our Stock Manager earns max salary 8500
Our Stock Clerk earns max salary 5000
Our Shipping Clerk earns max salary 5500
Our Programmer earns max salary 10000
Our Marketing Manager earns max salary 15000
Our Marketing Representative earns max salary 9000
Our Human Resources Representative earns max salary 9000
Our Public Relations Representative earns max salary 10500
19 rows selected.
No comments:
Post a Comment