inner and outer join
Inner join returns rows when there is at least one match in both tables
Outer Join, on the other hand, will return matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).
Assuming you're joining on columns with no duplicates, which is a very common case: An inner join of A and B gives the result of A intersect B, i.e. the inner part of a
venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a
Venn diagram union. Examples
Suppose you have two tables, with a single column each, and data as follows: A B -1 3 2 4 3 5 4 6
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B. Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
select * from a INNER JOIN b on a.a = b.b; select a.*,b.* from a,b where a.a = b.b; a | b
--+--3 | --+--3 4 | 4
Left outer join
A left outer join will give all rows in A, plus any common rows in B. select * from a LEFT OUTER JOIN b on a.a = b.b; select a.*,b.* from a,b where a.a = b.b(+); a | b
--+---1 | null
2 | null 3 | 3 4 | 4 Full outer join
A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b; a | b 1 | null 2 | null 3 | 3 4 | 4 null | 6 null | 5
Inner Join:
Full Outer Join:
What command is used to create a table by copying the structure of another table? CREATE TABLE.. AS SELECT command
Explanation:
To copy only the structure, the WHERE clause of the SELECT command should contain a false statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2; What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.
What is the main difference between the IN and EXISTS clause in sub-queries?
The main difference between the IN and EXISTS predicate in sub-query is the way in which the query gets executed.
IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query. The inner query is executed for only once.
EXISTS -- The first row from the outer query is selected, then the inner query is executed and , the outer query output uses this result for checking. This process of inner query execution repeats as many no. of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no. of times.
A column has some negative values and some positive values. It is required to find the sum of negative numbers and the sum of the positive numbers in two separate columns.
1 2 3 4
SELECT
SUM(CASE WHEN num < 0 THEN num ELSE 0 END) neg, SUM(CASE WHEN num > 0 THEN num ELSE 0 END)pos FROM neg_pos;
How to search for strings containing ‘%’ in Oracle? Search for columns containing ‘%’ in Oracle.
In ORACLE , you can use the ESCAPE keyword to search for strings containing ‘%’. Otherwise it would be considered as a META CHARACTER .
Using the escape character ( to search for strings containing like ‘ABC %%TRF’, ‘TR%FF’ or ‘%GH’)
Answer : 1
2
SELECT col_name FROM tbl_name
WHERE col_name LIKE '%?%%' ESCAPE '?';
Here ‘?’ can be replaced with any other character. 1
2
SELECT col_name FROM tbl_name WHERE instr(col_name,'%') > 0
How does one remove special characters in ORACLE? To replace special characters in a string with null use translate : translate(‘string’,'to_replace’,'replace_with’)
for eg: 1 2
SELECT translate
('asdfsd@#@$#$%$sdfg&;','!@#$%^&;*()_+=-`~?><:/.,',' ') FROM dual;
A table has columns with numbers and numbers with alphabets. Write a query to select only those rows which contains alphanumeric values.
1 2
SELECT * from alpha_numeric
where length(trim(translate(col1,'1234567890',' '))>0);
Give a string of format ‘NN/NN’, verify that the first and last two characters are numbers and that the middle character is’/’. Print the expression ‘NUMBER’ if valid, ‘NOT NUM’ if not valid. Use the following values to test your solution. ‘12/34’,’01/1a’, ‘99/98’.
Answer: 1 2 3 4 5 6
SELECT CASE WHEN ascii(substr('99/98',1,1)) BETWEEN 48 AND 57 AND ascii(substr('99/98',2,1)) BETWEEN 48 AND 57
AND substr('99/98',3,1) ='/'
AND ascii(substr('99/98',4,1)) BETWEEN 48 AND 57 AND ascii(substr('99/98',5,1)) BETWEEN 48 AND 57 THEN 'number' ELSE 'not num' END FROM dual;
There are two tables stu_dept and dept_cap. stu_dept contains the student name and the department(consider distinct values). dept_cap contains the capacity for each department. We need to find those departments(DEPT) where the number of students is less than the total capacity of the department.
Answer: 1 2 3 4 5 6
SELECT a.dept,a.capacity-t.cap remaining_seats FROM dept_cap a,
(SELECT dept,COUNT(dept)cap FROM stu_dept GROUP BY dept)t
WHERE a.dept=t.dept AND t.cap<a.capacity;
Some questions on the dual table. Select two rows from dual
1 2 3
select dummy from dual union all
select dummy from dual
To dispaly the numbers 1..10 from dual 1
2
select level from dual connect by level <=10 or
1 2
SELECT ROWNUM FROM dual CONNECT BY ROWNUM <=10
Another tricky question on dual involves the use of decode with NULL. 1SELECT decode(null,null,1,0) from dual;
OUTPUT—1
Although two NULL values are not equal, the output is 1, as decode checks for the existence of NULL and does not compare the two values.
What is difference between DELETE and TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
1. TRUNCATE:
1. TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
2. TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
3. TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
4. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. 5. TRUNCATE cannot be rolled back.
6. TRUNCATE is DDL Command.
7. TRUNCATE Resets identity of the table 2. DELETE:
1. DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
2. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
3. DELETE Can be used with or without a WHERE clause 4. DELETE Activates Triggers.
5. DELETE can be rolled back. 6. DELETE is DML Command.
7. DELETE does not reset identity of the table.
Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by
TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.
1. Properties of Sub-Query
1. A sub-query must be enclosed in the parenthesis.
2. A sub-query must be put in the right hand of the comparison operator, and 3. A sub-query cannot contain an ORDER-BY clause.
4. A query can contain more than one sub-query. 2. Types of Sub-Query
1. Single-row sub-query, where the sub-query returns only one row.
2. Multiple-row sub-query, where the sub-query returns multiple rows,. and 3. Multiple column sub-query, where the sub-query returns multiple columns
