Download Oracle Practical: Understanding Case Sensitivity and Conditions in SQL and more Study notes Business Administration in PDF only on Docsity!
Page 1 of 5
APPLYING CONDITIONS
Case in Object Names. Oracle makes it easier to remember table and column names by ignoring whether you type it in upper or lower case. It stores table and column names in it's internal data dictionary in upper case. When you type a query, it instantly converts the table and column names to uppercase, and then checks for them in the dictionary. (Some other RDBMS's are case sensitive).
Case in Data. There are ways using certain commands to find information from the database regardless of whether it is in upper or lower case. But these methods impose an unnecessary burden. With few exceptions such as legal text, it is much easier to store data in the database in uppercase. It makes queries easier and provides a more consistent appearance on reports. When and if some of this data needs to be put into lower case, or mixed upper and lower case, then the Oracle functions that make the conversion can be invoked. It will be less trouble overall, and less confusing, to store and report data in upper case.
Case matters only when SQL*plus or Oracle is checking a value for equality in the database. If you tell Oracle to find a row where province = 'munster' and province is really equal to 'MUNSTER', Oracle won't find it. Aside from this, case is completely irrelevant.
Incidentally, the word 'MUNSTER' as used here is called a literal, meaning that you want province to be tested literally against the word 'MUNSTER'. The single quotes on either side of the word tell Oracle that this is a literal.
Placing Conditions on your queries. At last we build on straightforward SELECT and FROM. Now we will start to apply conditions, using the SQL keyword WHERE.
SELECT column name FROM table name WHERE a certain condition on a column is fulfilled ;
For example,
SELECT table_name, FROM all_tables WHERE owner = ‘SCOTT’;
Note the importance of case. Try using ‘scott’. Do.
When you are setting a condition involving a number, you do not need the single quotes. Try it out on one of Scott’s tables. Describe them first, find a column with NUMBER data.
Also at this stage, you should learn how to apply the not equal condition…
… WHERE owner != ‘SCOTT’;
Or
… WHERE owner <> ‘SCOTT’;
Note that you can also apply the Greater Than and Less Than signs to both Number data and Literal data, where alphabetical order is used to enforce the conditions.
Page 2 of 5
WHERE owner > ‘SCOTT’;
Or
WHERE owner < ‘SCOTT’;
Hopefully at this stage you will have access to a table called ‘WEATHER’ and ‘REGION’ that I have created for the following examples. WEATHER was created using the following DDL.
CREATE TABLE weather (city varchar2(15), temperature number(3), humidity number(3), outlook varchar(15));
Do not attempt to create this yourselves. Conflicts will arise, no two objects can share the same object name. Use DESC to get an idea of what is in the table.
DESC WEATHER
To get an idea of the contents of Weather, select everything from it…
Combining Logic in Conditions The WHERE clause allows a user to set a condition, the additional use of AND and OR allows additional conditions to be specified.
Both AND and OR follow the common sense meaning of the Words. They can be combined in a virtually unlimited number of ways, but care must be taken because ANDs and ORs get convoluted very easily.
Examples...
SELECT *
FROM weather WHERE temperature > 15 AND humidity < 70;
SELECT city FROM weather WHERE temperature > 15 AND temperature < 20;
SELECT city FROM weather WHERE temperature < 15 OR humidity < 70;
What about...
SELECT *
FROM weather WHERE city = 'CORK' OR city = 'TRALEE' AND temperature > 20;
The AND condition is stronger than the use of OR. It binds the logical expressions on either side of it more strongly than the OR does. (It is said to have a higher precedence).
Page 4 of 5
With literals
Describe the REGION table and apply the following examples:
SELECT *
FROM region WHERE province IN ('MUNSTER', 'ULSTER');
WHERE province NOT IN ('MUNSTER', 'ULSTER');
WHERE province BETWEEN 'B' AND 'D';
WHERE province NOT BETWEEN 'B' AND 'D';
Using functions on Literals within WHERE conditions Assume for my sake, that your database accepts commands from an inconsiderate front end user who knows nothing about the backend operations. They might type search criteria into a screen in any case they like, and you must accommodate this sloppiness in your systems design. Therefore, you will have to convert the search criteria into a format that suits your data.
SELECT *
FROM region WHERE province = UPPER(‘Munster’);
The function when applied like this takes the input from the user, (enclosed in single quotes) and automatically converts it to upper case. What if you can’t be sure what case the data within the database is in? Use the following method to guarantee that you are always comparing like with like.
SELECT *
FROM region WHERE UPPER(province) = UPPER(‘Munster’);
Here is an example to carry out yourselves…
List the provinces, without duplication, ordered by province in reverse order, which have Tralee, Dublin and Galway within their bounds. The output should only include the first three characters of each province and have a heading ‘Prv’.
LIKE.
Like is an operator that can be used to add flexibility to SELECT statements. Within Oracle LIKE is used in conjunction with a Wild Card, (% - the percent sign) and a Position Marker (_, the Underscore).
Examples:
SELECT *
FROM WEATHER
WHERE CITY LIKE 'C%';
This Query will return all the Weather details for any city beginning with the letter 'C'.
Page 5 of 5
WHERE CITY LIKE '%N'
Any cities ending with the letter 'N'.
… WHERE CITY LIKE '_____'
Cities with 5 letters in their name.
… WHERE CITY LIKE '%L___'
Cities with 'L' as the third last letter in their name.
… WHERE CITY LIKE '%L%'
Cities with the letter 'L' in them.
%, The Wildcard. The % sign means that anything is acceptable here, One character, One hundred characters, or No characters, or any amount of Numbers.
_, Position Markers. I realise that the layout of the examples above makes it hard to count how many position markers are being used, but one underscore is used to signify the position of one character, whether a literal or Number.
This Pattern matching feature can play an important role in making an application friendlier by simplifying searches for names, products, addresses and only partially remembered items.
