|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 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);
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
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 should be used for storing fixed length character strings. String values will be space/blank padded before stored on disk. If this type is used to store variable length strings, it will waste a lot of disk space.
|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?|
|its associated with table but its a not a part of the table. for example SYSDATE, USER, UID, CURVAL, NEXTVAL, ROWID, ROWNUM|
|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;
|General Knowledge of India|
Which of the following dance forms was introduced by the great Vaishnava saint and reformer Mahapurusha Sankaradeva ?
Which of the following is the only classical dance of india having links with Muslim culture and represents a unique synthesis of Hindu and Muslim genius in art ?
|Time and Distance|
Starting from the same point at the same time, Ruhan and Yuvan had to reach the same destination. While Ruhan drove at 45 km/hr, Yuvan was slower by 5 km/hr. If Yuvan arrived at the destination an hour after Ruhan did, the distance covered by each, in Kilometres, was ?
A sum of money was invested at a certain rate of simple interest for 4 years. Had the sum been invested at a rate 3% higher than the actual rate, the interest earned would have been 720 Rs more. Find the sum initially invested?
|Ratio and Proportion|
A bag contains red, blue and black balls. The ratio of red to blue balls is 3 : 5. The ratio of black to red balls is 3 : 4. What is the ratio of blue to black balls?
|Profit and Loss|
Selling a pack of incense sticks for 136 Rs resulted in a loss of 15%. To make a profit of 10%, the selling price should be ?
23% of a bucket full of diluted milk was water. If the quantity of pure milk in the bucket was 15.4 liter, then the capacity of the bucket was ?
The average of the marks obtained by three girls in a test was twice the average of the marks obtained by eight boys in that test. If the total marks obtained by all these examinees was 168, find the average marks each of the girls obtained ?
|Pipes and Cistern|
44 pipes can fill a large water tank in 27 hours. How many hours it take for 66 pipes to fill four such tanks ?
|HCF and LCM|
A natural number, when divided by 3, 4, 5, 6, and 7 respectively, leaves corresponding remainder of 2, 3, 4, 5 and 6. What is the smallest of all such numbers fulfilling the above condition ?
If in a certain code language, PRINT is written as 34825 and TRUE is written as 5479. Then, how will TENURE be written in that language?
Arrange the given words from smallest to biggest-
|Statement and Conclusion|
The difference between the place value of 4 and 2 in the number 833749502 is ?
GIKM : HJLN :: PRTV : .........?
In a row of girls, Nanda is eighth from the left and Chanda is nineteenth from right. When Nanda and Chanda interchange their positions, Chanda becomes twnty-third form right. How many girls are there in the row?
Summer: Day :: Winter : ........?
Bird:Winges :: Fish:........?
Reserve Bank of India (RBI) - 199 Posts - 11/10/2019 Last date
Life Insurance Corporation of India (LIC) - 7942 Posts - 01/10/2019 Last date
State Bank of India (SBI) - CRPD/APPR - 700 Posts - 06/10/2019 Last date
|Jobs in Jharkhand|
Jharkhand Staff Selection Commission (JSSC) -JANMCE - 1985 Posts - 04/10/2019 Last date
|Jobs in Madhya Pradesh|
National Health Mission (NHM) - 1392 Posts - 05/10/2019 Last date
|Jobs in Bihar|
Bihar Vidhan Parishad - 136 Posts - 11/10/2019 Last date
NABARD - 91 Posts - 02/10/2019 Last date
Institute of Banking Personnel Selection (IBPS) - CRP Clerks-IX - 12075 Posts - 09/10/2019 Last date
Haryana State Cooperative Apex Bank Ltd - 978 Posts - 31/08/2019 Last date
Kanchipuram Central Cooperative Bank - 238 Posts - 05/09/2019 Last date