|Hypertext Markup LanguageCascading Style SheetsExtensible Markup LanguageJava ScriptjQueryAngularJSOracle SQLOracle PL/SQLOracle D2K FormsOracle D2K ReportsOracle DatabaseJavaJava Database ConnectivityJava ServletJava Server PagesHibernateApache TomcatCC++PHPMy-SqlUnix/LinuxMS-DOSHR Interviews|
What the difference between UNION and UNION ALL?
Both UNION and UNION ALL is used to combine results of two SELECT queries.
select * from T1 A 1 1 1 /*union example*/ select * from T1 union select * from T1 A 1 /*union all example*/ select * from T1 union all select * from T1 A 1 1 1 1 1 1
Thanks for your comment.!
Write a comment(Click here) ...
What is SQL?
Structured Query Language (SQL) is a language that provides an interface to relational database systems.
|Difference between TRUNCATE, DELETE and DROP commands?|
DELETE command is used to remove some or all rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.
DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
DELETE will not free up used space within a table. This means that repeated DELETE commands will severely fragment the table and queries will have to navigate this "free space" in order to retrieve rows.
How does one eliminate duplicate rows from a table?
To remove duplicate rows of data, use the following statement: it will remove duplicate rows from a table and leaving only unique records in the table:
Delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
How to Change a SQL prompt name?
SQL> set prompt oracle oracle>
Find out nth highest salary from emp table?
With the help of correlated sub-query, row_number(), dense_rank() and rank() , we can find nth highest salary -
SELECT * FROM Employee order by 2 NAME SALARY Ram 3000 Tom 3000 Vinod 4000 Venky 5000 Manoj 7000 /*correlated sub-query example */ SELECT DISTINCT (a.salary) FROM Employee A WHERE &N = (SELECT COUNT(DISTINCT(b.salary)) FROM Employee B WHERE a.salary <= b.salary); N= 2 -- to identify 2nd highest salary SALARY 5000 /*row_number example */ SELECT DISTINCT (salary) FROM (SELECT e.*, ROW_NUMBER() OVER(ORDER BY salary DESC) rn FROM Employee e) WHERE rn = &N; N= 3 -- to identify 3rd highest salary SALARY 4000 /*correlated sub-query example */ SELECT * FROM (SELECT name, salary, DENSE_RANK() OVER(ORDER BY salary DESC) nth_highest_sal FROM Employee ) WHERE nth_highest_sal = &n; N= 3 -- to identify 3rd highest salary SALARY 4000 /*rank example */ SELECT * FROM (SELECT name, salary, RANK() OVER(ORDER BY salary DESC) nth_highest_sal FROM Employee ) WHERE nth_highest_sal = &n; N= 3 -- to identify 3rd highest salary SALARY 4000
What is the difference between CHAR, VARCHAR and VARCHAR2 data types?
CHAR, VARCHAR and VARCHAR2 are used to store the character string values -
CHAR should be used for storing fixed length character strings. String values will be space/blank padded before stored on disk. CHAR(n) will ALWAYS be n bytes long. If the string length is <n, it will be blank padded upon insert to ensure a length of n.
|What is difference between Co-related sub query and nested sub query?|
Correlated sub query runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.
Select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max (basicsal) from emp e2 where e2.deptno = e1.deptno)
Nested sub query runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.
Select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max (basicsal) from emp group by deptno)
What are the more common pseudo-columns?
A pseudo-column behaves like a table column but it is not actual column of the table. We can select from pseudo-columns, but we cannot insert, update, or delete their values.
For example - SYSDATE, USER, UID, CURVAL, NEXTVAL, ROWID, ROWNUM, LEVEL
|What is the difference between group by and order by?|
|Group by controls the presentation of the rows, Order by controls the presentation of the columns.|
|What keyword does an SQL SELECT statement use for a string search?|
|The LIKE keyword used for string searches. The % sign is used as a wildcard and _ for single.|
|What are various constraints / Integrity Constraints used in SQL?|
Data integrity allows defining certain data quality requirements that the data in the database needs to meet.
|What is difference between UNIQUE and PRIMARY KEY constraints?|
Difference is that Primary Key have an implicit NOT NULL constraint while Unique Keys do not.
|When do you use WHERE clause and when do you use HAVING clause?|
|WHERE clause applies to the individual rows. HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause.|
|What are the difference between DDL, DML and DCL commands?|
DDL - Data Definition Language: statements used to define the database structure or schema. Some examples:
|What are sequences? Explain with syntax?|
A field in oracle can be kept as auto incremented by using sequence. it can be used to create a number sequence.
e_seq will cache up to 20 values for performance. Starts from one.
CREATE SEQUENCE e_seq MINVALUE 1 MAXVALUE 99999 START WITH 1 INCREMENT BY 1 CACHE 20;
|What is a view?|
A View in Oracle and in other database systems is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used.
First we increased the denominator of a positive fraction by 3 and then decrease it by 5.The sum of the resulting fractions proves to be equal to 2/3. Find the denominator of the fraction if its numerator is 2?
If we divide a two digit number by the sum of its digits we get 4 as a quotient and 3 as a remainder. Now if we divide that two digit number by the product of its digits we get 3 as a quotient and 5 as a remainder . Find the two digit number?
The denominators of an irreducible fraction is greater than the numerator by 2.If we reduce the numerator of the reciprocal fraction by 3 and subtract the given fraction from the resulting one,we get 1/15.Find the given fraction?
If we add the square of the digit in the tens place of the positive two digit number to the product of the digits of that number we get 52,and if we add the square of the digit in the unit's place to the same product of the digits we get 117.Find the two digit number?
The sum of squares of the digits constituting a positive two digit number is 13, If we subtract 9 from that number we shall get a number written by the same digits in the reverse order. Find the number?
The arithmetic mean of two numbers is smaller by 24 than the larger of the two numbers and the GM of the same numbers exceeds by 12 the smaller of the numbers. Find the numbers?
A number being successively divided by 3,5,8 leaves remainder 1,4,7 respectively. Find the respective remainders if the order of divisors are reversed?
The sum of all possible two digit numbers formed from three different one digit natural numbers when divided by the sum of the original three numbers is equal to?
|HCF and LCM|
Find the HCF and LCM of the polynomial x2-5x+6 and x2-7x+10?
When we multiply a certain two digit number by the sum of its digits , 405 is achieved. If we multiply the number written in reverse order of the same digits by the sum of the digits, we get 486. Find the number?
The sum of two numbers is 15 and their geometric mean is 20% lower than their arithmetic mean. Find the numbers?
The difference of 1025-7 and 1024+x is divisible by 3. find x=?
A number when divided by the sum of 555 and 445 gives two times their difference as quotient and 30 as remainder . The number is?
The sum of three prime numbers is 100.If one of them exceeds another by 36 then one of the numbers is?
Find the number of zeros in the factorial of the number 18?
If a number is multiplied by 22 and the same number is added to it then we get a number that is half the square of that number. Find the number?
In doing a division of a question with zero remainder,a candidate took 12 as divisor instead of 21.The quotient obtained by him was 35. The correct quotient is?
A number when divided by 342 gives a remainder 47.When the same number is divided by 19 what would be the remainder?
|Jobs in West Bengal|
इंडियन पोस्ट सर्विस Indian Post Service - 2357 पद - 19/08/2021 अंतिम तिथि - पश्चिम बंगाल पोस्टल सर्कल
भारतीय स्टेट बैंक State Bank of India(SBI) - 6100 पद - 26/07/2021 अंतिम तिथि - CRPD/APPR/2021-22/10
कर्मचारी चयन आयोग Staff Selection Commission(SSC) - 25271 पद - 31/08/2021 अंतिम तिथि
|Jobs in Madhya Pradesh|
राष्ट्रीय स्वास्थ्य मिशन मध्य प्रदेश National Health Mission MP(NRHM) - 5215 पद - 22/06/2021 अंतिम तिथि
|Jobs in Punjab|
पंजाब स्टेट पावर कॉर्पोरेशन लिमिटेड Punjab State Power Corporation Limited (PSPCL) - 2632 पद - 20/06/2021 अंतिम तिथि
|Jobs in Chhattisgarh|
स्वास्थ्य सेवा निदेशालय, छत्तीसगढ़ Department of Health & Family Welfare, Chhattisgarh - 267 पद - 26/06/2021 अंतिम तिथि
दिल्ली सबऑर्डिनेट सर्विस सिलेक्शन बोर्ड Delhi Subordinate Services Selection Board(DSSSB) - 5807 पद - 03/07/2021 अंतिम तिथि
|Jobs in Madhya Pradesh|
राष्ट्रीय स्वास्थ्य मिशन मध्य प्रदेश National Health Mission MP(NRHM) - 2850 पद - 31/05/2021 अंतिम तिथि
इंडियन पोस्ट सर्विस Indian Post Service - 4368 पद - 29/05/2021 अंतिम तिथि
दिल्ली सबऑर्डिनेट सर्विस सिलेक्शन बोर्ड Delhi Subordinate Services Selection Board(DSSSB) - 7236 पद - 24/06/2021 अंतिम तिथि