UNION – COMBINING ROWS FROM TWO QUERIES WITHOUT DUPLICATES Using Set operators, we can combine the results of multiple queries into a single result set. For example, select Call one from Tab One will give this result. Select call three from tab two will give this result. Now I can combine both into a single result set by using Set operations. Union is one of the Set operators. Let us explore it. Now, if I do, select call one from tab one. Union select. Call three from tab two. Then…

SUBQUERIES – DEFINITION AND SUBQUERY IN THE SELECT CLAUSE Sub queries. Let us assume that I want to see today’s Date, the number of employees I have and how much salary I pay. I can do all this by running three queries separately select Sys Date from Dual gives me today’s Date select Count Star from Employees gives me the total number of employees I have and Select Some Salary from Employees gives me the total salary that I am paying. But these are three separate queries with separate results….

SUBSTITUTIONS – USING AMPERSANDS, DEFINING AND UNDEFINING THE VARIABLES This is a simple sequel where we know the condition which is first name equals to James. But in real life, mostly in web applications, we would need to capture the user’s input and fetch rows accordingly. In other words, if they type Stephen, then we need to fetch rows where the first name is Stephen. Or if type Nina then we need to fetch rows related to Nina. This can be achieved by using substitutions. A single ampersand can be…

LIKE OPERATOR – WITH WILDCARDS % AND _ (UNDERSCORE) We can also fetch rows based on certain patterns. We need to use like operator for that. Like operators may use any of the following wildcard characters percentage, symbol and underscore. Symbol percentage can be replaced with zero or more characters of any characters, while pattern matching for example or percentage can represent oracle, organization, orange, arbitrary, etca. If you notice there is no limit on the number of characters, also it can represent zero characters as in the example R….

INSERT – VARIOUS METHODS OF INSERTING DATA Using an insert statement we can insert a row or using a combination of an insert and select statement, we can insert multiple rows. Let us explore. Select call one, call two from tab one. Now I am going to insert arrow with value eleven for call one and A for call two. Since I know the order of the columns in the table, I am just using this SQL. Insert into tab one values eleven a. Let us verify it, but this…

FUNCTIONS – INTRODUCTION Look at this following sequels select Upper. This is a test from Dual has converted the string to an all uppercase string. Select Sqrt 36 from Dual has given us the square root of 36, which is six. So what is this upper and square root? These are functions provided by Oracle. Functions are nothing but programs supplied by Oracle that does a certain operation on the input and gives an output. Now, do I need to know the programming code behind upper R square root not…

BASICS OF A JOIN – CARTESIAN PRODUCT OR CROSS JOIN We have been reading data from one table. However, often a database developer would be required to fetch data from multiple tables. To do that, those tables need to be joined in the sequel. Let us look at the basics of a join. Here we have two tables called tab one and tab two. Tab one has has two columns, call one and call two. Tab two has two columns, call three and call four. And the data is like…

NULL – DEFAULT Create table cons one call, one number. Let me query it. As expected, it doesn’t have any rows. Now, let me insert a null value. I can do it by just specifying two single quotes. Query it. We see the null value here. What this means is, while creating a table, if we just specify the column name and its data type, by default it accepts null values. Now, let me insert a number. I know it is a numerical column. Verify it. We see the null…

CONDITIONAL FUNCTIONS – NVL, NVL2 and NULLIF Circle provides functions which act only if certain conditions are met. Let us take a look at this SQL. This is a simple sequel. We see some null values in the state province column. Instead of displaying as null, I can display it as another string using NVL function NVL state province not applicable. Replaces null values with not applicable string. That’s the condition. If there are null values, then display them as not applicable. Otherwise display the original non null value. What…

INEQUALITY OPERATORS – GREATER THAN & LESS THAN We saw equality operations done by the equals to sign. Now let us explore Inequality Operators, which actually gets us results based on a range of matching values. This is less than sign and this brings rows that are lesser than the condition select First Name Last Name Salary Commission Percentage from employees where commission percentage is less than 00:25. This is greater than symbol. We can use this to get rows that are greater than the condition. The same query with…