The basic SELECT statement requires two elements: the SELECT list and FROM clause. These clauses specify what column to retrieve and from where. Here is the basic format.
Syntax:
SELECT [ DISTINCT ] {* | Column,[ expression]....} FROM Table_Name;
Ex.
SQL > SELECT * FROM courses;
SQL > SELECT CoursNumber,CourseName,ReplaceCourse,Description,RetailPrice FROM Courses;
=> It will show all the data from data field.
Note: How ever, make sure that you do not put a comma after that last column. When the last column is followed by a comma, it indicates to Oracle that there is another column in the list to consider and you will receive the following error:
ERROR at line 2:
ORA-00936 : missing expression.
Arithmetic Operation
You may want to combine values project change in prices or salaries and create "what if" projections. One may is to use arithmetic operations. The four arithmetic operators in SQL are listed in the below,
| Describe | symbol |
| Multiple
Divide Add Subtract | *
/ + - |
Ex.
Suppose you want to generate a list that shows how much each instructor charges for a five-day course. You can use the following query:
SQL > SELECT InstructorID, PerDimCost, PerDimCost*5 FROM Instructors;
INSTRUCTORID PERDIEMCOST PERDIMCOST*5
300 500 2500
312 450 2250
100 600 3000
Ex.
SQL > SELECT InstructorID, PerDiemCost+PerDiemExpenses FROM Instructors;
INSTRUCTORID PERDIEMCOST+PERDIEMEXPENSES
300 700
310 650
100 800
Using arithmetic Operators with date Values
Oracle also enable you to perform arithmetic operations on date value. When you add or subtract a number from a date, you return a date value that is that many days before or after the date that you are dealing with.
Ex.
SQL > SELECT CourseNumber, StartDate, DayDuration, StartDate+DayDuration FROM ScheduledClasses;
COURSENUMBER STARTDATE DAYDURATION STARTDATE+DAYDURATION
100 06-JAN-01 4 10-JAN-01
200 13-JAN-01 5 18-JAN-01
Ex.
SQL > SELECT CourseNumber, (StartDate+DayDuration)-StartDate FROM ScheduledClasses;
COURSENUMBER (STARTDATE+DAYSDURATION)-STARTDATE
100 4
200 5
There are some rules for using arithmetic operation with date values.
I You can't use the multiplication or division operators with date.
II You can't add two dates together.
III You can add or subtract a number with date. And it will show a date.
IV It is possible to subtract two date values when you subtract two dates, Oracle provides a numeric value that is the different between the two dates.
Concatenation Columns
In SQL it is also possible to use expressions on character data. You may want to display data stored in multiple columns as a single value in the result set. It is done by concatenation( double pipe "||").
Ex.
First and last names are often stored in separate columns, but you may want a single "name" value the contains both the first and last names.
SQL > SELECT FirstName||LastName FROM Instructors;
FIRSTNAME||LASTNAME
SusantaRoy
TajuddinParvaz
ShovonRoy
Ex.
To Place a space between the first and last names in the previous, example, you concatenate a blank space between the two columns.
SQL > SELECT FirstName||' '||LastName FROM Instructors;
=> Show the name as "Susanta Roy".
Ex.
You can place any string value in the select list and it is repeat in every line.
SQL > SELECT FirstName||' '||LastName||' teaches '||InstructorType FROM Instructor;
FIRSTNAME||' '||LASTNAME||' TEACHES '||INSTRUCTORTYPE
Susanta Roy teaches ORACLE
Tajuddin Parvaz teaches UNIX
Ex.
You can use Arithmetic operation in concatenation columns.
SQL > SELECT 'Instructor '||InstructorID||' charges $'||PerDiemCost+PerDiemExpenses FROM Instructors;
ERROR at line 1:
ORA-01722 : Invalid number.
The problem with this query is that it has been asked to execute both as arithmetic operation and a string operator at the same time. For this reason, we should give the command as below,
SQL > SELECT 'Instructor '||InstructorID||' charges $'||(PerDiemCost+PerDiemExpenses) FROM Instructors;
Adding column aliases
SQL provides a better way of handing column name through the use of aliases. An alias is simply a column-heading name that replaces the value from the select list. There are two ways to add an alias to SQL SELECT statement.
1 The first way is simply to place the alias after the column name in the select list.
2 The second way to add an alias is to include the AS operator. Although the AS keyword is not necessary. It make the code easier to read.
Ex.
SQL > SELECT FirstName||' '||LastName||' teaches '||InstructorType AS Instructors_by_course FROM Instructors;
INSTRUCTOR_BY_COURSE
Susanta Roy teaches ORACLE
Tajuddin Parvaz teaches UNIX
Ex.
SQL > SELECT InstructorID, ((( PerDiemCost+PerDiemExpensses)*5)-(Perdiemcost*5)) / ((PerDiemcost+PerdiemExpesse)*5)) *100 AS Price difference FROM Instructors;
ERROR at lien 2:
ORA-00923 : FROM keyword not found where expected.
=> The reason of the ERROR, the alias name can't include an embedded space. If we want to use a space then the commas as below.
SQL > SELECT FirstName||' '||LastName AS "NAME", InstructorType AS "SPECIALTY", PerDiemCost AS "DAILY RATE" FROM Instructors;
NAME SPECIALTY DAILY RATE
Susanta Roy ORACLE 500
Tajuddin Parvaz UNIX 600
The effect of NULL value on arithmetic and concatenation operation
The presence of NULL in your tables can have an effect on both arithmetic and concatenation operations. A NULL is not a value. It is not considered a zero or a character.
Ex.
SQL > SELECT InstructorID, PerDiemCost, PerDiemExpenses AS "Daily Cost" FROM Instructors;
INSTRUCTORID PERDIEMCOST PERDIEMEXPENSES DAILY COST
300 500 200 700
310 450 200 650
210 400
=> In this example, instructor 210 has a NULL in the PerDiemExpenses column and as a result, also a NULL value under Daily Cast.
Elimination duplication in the result set ( DISTINCT )
When you issue a SELECT statement Oracle returns all rows that match the query. For example, if you issue the following query:
SQL > SELECT City FROM Students;
CITY
Victoria
New York
New York
Toronto
Ottawa
New York
Dallas
San Francisco
San Francisco
This list contain several students from the same city. What if however, you wanted a list only of all the cities from which the company had Enrolled students to create such a list, you have to use the DISTINCT keyword.
Ex.
SQL > SELECT DISTINCT City FROM Students;
CITY
Dallas
New York
Ottawa
San Francisco
Victoria
Toronto
Ex.
SQL > SELECT DISTINCT Country, City FROM Students;
COUNTRY CITY
Canada Dallas
Canada New York
Canada Ottawa
USA San Francisco
USA Victoria
USA Toronto
=> Remember that the DISTINCT operator applies to the entire select list. If you include multiple column in the select list, DISTINCT sorts by the unique occurrence of all columns.
Ordering data in the SELECT statement
When you want the data returned a specify the column or columns that you want as the basis of your ordering. You can also specify whether you want the data sorted in ascending or descending order.
Ex.
SQL > SELECT InstructorID, PerDiewCost FROM Instructors ORDER BY PerDiewCost ASC;
INSTRUCTORID PERDIEWCOST
210 400
310 450
300 500
110 600
=> All the data of PerDiewCost are showing by Ascending and when ASC keyword is used.
Ex.
It is also possible to sort by more then one columns. When Oracle encounters as ORDER BY clause with more then one columns, It orders by the first column and within that column, order by the second.
SQL > SELECT LocationID, CourseName, StartDate FROM ScheduledClasses ORDER BY LocationID ASC, CourseNumber DESC;
LOCATIONID COURSENUMBER STARTDATE
100 100 06-JAN-01
300 200 20-JAN-01
300 100 14-FED-01
Limiting Rows Using the WHERE Clause
In SQL, you limit the number of rows returned with the use of a WHERE clause. In a always placed after the FROM clause.
Syntax:
SELECT [ DISTINCT ] {* | column, [expression]....}FROM Table_Name [ WHERE Condition [ {AND | OR [ NOT ] }Condition2,...]];
Where SQL processes the WHERE clause, it tests the value of each row in a column against a particular value. The query includes only rows that meet the condition in the WHERE clause. Each condition is set with the use of an operator.
Comparison Operators
The most common set of operators used in WHERE clause are the comparison operators. The comparison operator are listed as below:
| Operator | Meaning |
| =
| Equal
Not equal to Greater Then Less then Greater then or equal to Less then or equal to |
Ex.
When you want a list of all instructors who charge more than $500 per day. You can use the following query:
SQL > SELECT InstructorID, PerDiewCost FROM Instrucyors WHERE PerDiewCost > 500;
INSTRUCTORID PERDIEMCOST
100 600
200 750
Using comparison operators with character and date data
If you omit the single quotes, Oracle attempts to interpret the value as a schema object and you receive an error.
Ex.
SQL > SELECT InstructorID, FirstName, LastName FROM Instructors WHERE FirstName=Susan;
ERROR at line1:
ORA-00904: Invalid column name.
Ex.
SQL > SELECT InstructorID, FirstName, LastName FROM Instructors WHERE FirstName=LastName;
=> No syntax error occurs. Oracle simply looks in the FirstName and LastName columns and returns any row that has the same value in both columns.
Ex.
When you want a list of the Instructors from Toronto, you might try the following query:
SQL > SELECT InstructorID, LastName, City FROM Instructors WHERE City='toronto';
no rows selected.
You receive this message because, in the table data in the City column is stored with initial capital letters, as you can see from the following statement:
SQL > SELECT InstructorID, LastName, City FROM Instructors WHERE City='Toronto';
INSTRUCTORID LASTNAME CITY
300 Roy Toronto
310 Parvaz Toronto
=> It you are not sure of the case your data is stored in, you can use certain character function to modify case.
Ex.
You can use any of the comparison operators with character data. When you use the greater than or less than sign, Oracle returns all values that are alphabetically greater or less than a particular value.
SQL > SELECT FirstName FROM Instructors WHERE FirstName < 'Lisa';
FIRSTNAME
David
Kyle
Geoff
=> In the case, all of the instructor name that are alphabetically less than Lisa are returned.
Ex.
SQL > SELECT CourseNumber FROM ScheduledClasses WHERE StartDate='06-JAN-01';
=> The date data must be as "DD-MON-YY" format nor SQL shows a Error. Example:
SQL > SELECT CourseNumber FROM ScheduledClasses WHERE StartDate='January 6, 01';
ERROR at line 1:
ORA-01858 : a non-number character was found where a numeric was expected.
Ex.
You want only those instructors whose weekly cost is greater that $3500. To find this information, you simply place the expression in the WHERE clause:
SQL > SELECT InstructorID, (PerDiemCost + PerDiemExpenses) * 5 AS Weekly_Cost FROM Instructors WHERE ( PerDiemCost + PerDiemExpenses *5 > 3500;
INSTRUCTORID WEEKLY_COST
100 4000
200 5000
=> When you perform queries with an expression in the WHERE clause, you must include the entire expression. You can't place the alias name in its place.
For example. the following query returns an error,
SQL > SELECT InstructorID, ( PerDiemCost + PerDiemExpenses )* 5 AS Weekly_Cost FROM Instructors WHERE Weekly_Cost > 3500;
ERROR at line 3:
ORA-00904 : Invalid column name.
Using logical operators in WHERE clauses
It is possible to have more than one condition in a WHERE clauses. However, to combine conditions, you must use a logical operator. The logical operator are as below:
| Operator | Meaning |
| AND | Returns a row when both condition are TRUE |
| OR | Returns a row when either condition in TRUE |
| NOT | Returns a row when the condition is FALSE |
The AND operators
The AND operator is used to join two or more conditions in one query. In order for a row to be returned in this condition, all of the conditions liked together must return TRUE.
Ex.
SQL > SELECT InstructorID, City, PerDiemCost FROM Instructors WHERE City='New York' AND PerDiemCost > 500;
INSTRUCTORID CITY PERDIEMCOST
100 New York 600
The OR operator
When you combine two or more conditions with the OR operator, Oracle returns any row that meets either condition.
Ex.
SQL > SELECT InstructorID, City, PerDiemCost FROM Instructors WHERE City='New York' OR PerDiemCost > 500;
INSTRUCTORID CITY PERDIEMCOST
100 New York 600
110 New York 500
100 Palo Alto 750
100 New York 400
The NOT operator
Unlike the AND and OR operators, the NOT operator is not used to set multiple condition on a WHERE clauses. Instead, it is a negative operator. It negates a condition so that it returns all rows that do not meet the condition.
The rules of precedence for logical operators
Ex.
Consider, Cust_List is a master customer table for you company that contain over 1000000 customers from across the containment. You are interested in getting the address and phone number of everyone named "Smith" that lives in either "New York" or "Chicago".
SQL > SELECT Name, Address, City, State, Postal, Telephone FROM Cust_List WHERE Name='Smith' AND City='New York' OR City='Chicago';
=> When you execute this query then you will show all the data. Because, it parses the AND condition first and then applies the OR condition. So you should give the below command,
SQL > SELECT Name, Address, City, State, Postal, Telephone FROM Cust_List WHERE Name='Smith' AND ( City='New York' OR City='Chicago');
=> Then OR condition will done first then AND operator.
Additional comparison operators in the WHERE clauses
The comparison operators table as below,
| Operator | Meaning |
| BETWEEN .. .. AND .. .. | Returns all the rows between two values. |
| IN | Returns any row matching a list of values. |
| LIKE | Allows two rows based on a character pattern. |
| IS NULL | Returns all rows where value is NULL. |
Using the BETWEEN ... ... AND .. .. operator
The BETWEEN operator enables you to write a query that returns a ranges of values in single statement.
Ex.
SQL > SELECT # FROM Order_info WHERE OrderDate BETWEEN '01-JAN-01' TO '30-MAR-01';
=> It is important to remember that the BETWEEN operator returns any value between and including the upper and lower limits.
Ex.
When you want to execute a range, you can do so with the NOT operator.
SQL > SELECT * FROM Instructors WHERE PerDiemCost NOT BETWEEN 400 AND 700;
=> Returns all instructors who have a PerDiewCost below $400 or above $700.
Using the IN operator
Ex.
Suppose you want to find instructors from Toronto or New York. Then we can use IN operator with WHERE clauses.
SQL > SELECT InstructorID, City FROM Instructors WHERE City IN ('Toronto','New York');
Ex.
If you want all instructors except those from Toronto or New York, you can use the following query.
SQL > SELECT InstructorID, City FROM Instructors WHERE City NOT IN ('Toronto','New York');
Using the LIKE operator - Wild Card operators in SQL
The give Wild Card operators in SQL are given below,
| Symbol | Meaning |
| % | Any number of characters ( Include none) |
| _ (Underscore) | Any one character. |
You use the underscore ( _ ) to represent a single character. For example, when you are not sure whether a name was entered in a table as "LaGrand" Or "LeGrand", you can find either value with the following condition,
WHERE Name LIKE 'L_Grand';
For another example, you are unsure whether a name is stored as "Hardy" or "Hurdy", you can use the following command,
WHERE Name LIKE 'H__dy';
You can also use the percent ( % ) Wild Card. but it also returns strings that have more then or less than two characters.
Ex.
SQL > SELECT FirstName FROM Instructors WHERE FirstName LIKE '_a%';
FIESTNAME
David
Lana
Ex.
SQL > SELECT FirstName FROM Instructors WHERE FirstName LIKE '_a%';
FIRSTNAME
Michael
Susan
David
Lisa
Lana
Using The IS NULL operator
In the data base, some table can be carry NULL value. SQL includes the IS NULL operator. The IS NULL operator tests each value in a column and determines whether the cell has a value. This is the only way to retrieve rows based on the presence of a NULL.
Ex.
The PerDiemExpenses column for some instructors has a NULL, when you want a list of these instructors, you can use the following query,
SQL > SELECT InstructorID, PerDiemExpenses FROM Instructors WHERE PerDiemExpenses IS NULL;
INSTRUCTORID PERDIEMEXPENSES
210
410
Ex.
To show other columns when PerDiemExpenses have no NULL value from the table Instructors. Then we can use NOT operator.
SQL > SELECT InstructorID, PerDiemExpenses FROM Instructors WHERE PerDiemExpenses IS NOT NULL;
INSTRUCTORID PERDIEMEXPENSES
300 200
310 200
100 200
110 200
200 250
450 200
Including the ROWNUM pseudo-column in the WHERE clauses
ROWNUM returns a numerical value that indicates the order in which the data was returned.
Ex.
SQL > SELECT ROWNUM, InstructorID, City FROM Instructors;
ROWNUM INSTRUCTORID CITY
1 300 Toronto
2 310 Toronto
3 100 New York
4 110 New York
5 200 Palo Alto
6 200 Toronto
=> The value of the pseudo-column is not physically stored anywhere. ROWNUM only display only screen.
Ex.
SQL > SELECT ROWNUM, InstructorID, City FROM Instructors WHERE City = 'Toronto';
ROWNUM INSTRUCTORID CITY
1 300 Toronto
2 310 Toronto
3 200 Toronto
Ex.
SQL > SELECT ROWNUM, InstructorID, City FROM Instructors WHERE ROWNUM <>
1 300 Toronto
2 310 Toronto
3 100 New York
4 110 New York
Ex.
SQL > SELECT ROWNUM, InstructorID, City FROM Instructors WHERE ROWNUM > 5;
no rows selected.
=> The reason is that when the first row is returned, it is given the ROWNUM value of 1, it is tested against the WHERE clauses and then rejected.
Ex.
SQL > SELECT ROWNUM, InstructorID, PerDiemCost FROM Instructors ORDER BY PerDiemCost DESC;
ROWNUM INSTRUCTORID PERDIEMCOST
5 200 750
3 100 600
1 300 500
4 110 500
2 310 450
6 210 450