Q1: What is SQL and also describe types of SQL statements?
Answer: SQL stands for Structured Query Language. SQL is a language used to communicate with the server to access, manipulate and control data.
There are 5 different types of SQL statements.
- Data Retrieval: SELECT
- Data Manipulation Language (DML): INSERT, UPDATE, DELETE, MERGE
- Data Definition Language (DDL): CREATE, ALTER, DROP, RENAME, TRUNCATE.
- Transaction Control Statements: COMMIT, ROLLBACK, SAVEPOINT
- Data Control Language (DCL): GRANT, REVOKE
Q2: How will you differentiate between VARCHAR & VARCHAR2?
Answer: Both VARCHAR & VARCHAR2 are Oracle data types that are used to store character strings of variable length.
VARCHAR can store characters up to 2000 bytes while VARCHAR2 can store up to 4000 bytes.
VARCHAR will hold the space for characters defined during declaration even if a
Q3: What is the difference between TRUNCATE & DELETE command?
Answer: Both the commands are used to remove data from a database.
The finer differences between the two include:
- TRUNCATE is a DDL operation while DELETE is a DML operation.
- TRUNCATE drops the structure of a database and hence cannot be rolled back while DELETE command can be rolled back.
- The TRUNCATE command will free the object storage space while the DELETE command does not.
Q4: What is meant by Joins? List out the types of joins.
Answer: Joins are used to extract data from multiple tables using some common column or condition.
There are various types of Joins as listed below:
- INNER JOIN
- OUTER JOIN
- CROSS JOINS or CARTESIAN PRODUCT
- EQUI JOIN
- ANTI JOIN
- SEMI JOIN
Q5: What is a Database?
Answer: A database is a collection of information in an organized form for faster and better access, storage and manipulation. It can also be defined as a collection of tables, schema, views and other database objects.
Q6: What is a Table in a Database?
Answer: A table is a database object used to store records in a field in the form of columns and rows that holds data.
Q7: What is DBMS?
Answer: Database Management System is a collection of programs that enables a user to store, retrieve, update and delete information from a database.
Q8: What is a Field in a Database?
Answer: A field in a Database table is a space allocated to store a particular record within a table.
Q9: What is a column in a Table?
Answer: A column is a vertical entity in a table that contains all information associated with a specific field in a table.
Q10: What is a Record in a Database?
Answer: A record (also called a row of data) is an ordered set of related data in a table.
Q11: What is a Join?
Answer: Join is a query, which retrieves related columns or rows from multiple tables.
Q12: What are SQL constraints?
Answer: SQL constraints are the set of rules that enforced some restriction while inserting, deleting or updating of data in the databases.
Q13: What are the different types of joins?
Answer: Types of Joins are as follows:
INNER JOIN
LEFT JOIN
RIGHT JOIN
OUTER JOIN
Q14: What is the difference between an inner and outer join?
Answer: An inner join returns rows when there is at least some matching data between two (or more) tables that are being compared.
An outer join returns rows from both tables that include the records that are unmatched from one or both the tables.
Q15: What are the constraints available in SQL?
Answer: Some of the constraints in SQL are – Primary Key, Foreign Key, Unique Key, SQL Not Null, Default, Check and Index constraint.
Q16: What is a Primary Key?
Answer: A PRIMARY KEY constraint uniquely identifies each record in a database table. All columns participating in a primary key constraint must not contain NULL values.
Q17: What is a Composite PRIMARY KEY?
Answer: Composite PRIMARY KEY is a primary key created on more than one column (combination of multiple fields) in a table
Q18: What is a FOREIGN KEY?
Answer: A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY in a table is linked with the PRIMARY KEY of another table.
Q20: What is a NULL value?
Answer: A field with a NULL value is a field with no value. A NULLvalue is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation. Assume, there is a field in a table is optional and it is possible to insert a record without adding a value to the optional field then the field will be saved with a NULL value.
Q21:What is Self join?
Answer : Self join is nothing but the table joins with itself. There are lot of tables which contains more than one functionality at that time the concept of self join comes to the picture.
Q22: What is mean by TCL? Explain with examples.
Answer: A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations performed like Creating, updating, deleting records in the database comes from transactions.
In simple word, we can say that a transaction means a group of SQL queries executed on database records.
Q23: What are different types of indexes?( 80 % asked in SQL Developer Interview Questions )
Answer:
There are following types of indexes:
1.Normal Indexes
2.Bit map indexes
3.B-tree Indexes
4.Unique Indexes
5.Function Based Indexes.
Q24: What is WITH CHECK OPTION?
Answer: The WITH CHECK option clause specifies check level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the sub query.
Q25: What do you mean by GROUP BY Clause?
Answer: A GROUP BY clause can be used in select statement where it will collect data across multiple records and group the results by one or more columns.
Q26: What is the use of Aggregate functions in Oracle?
Answer: Aggregate function is a function where values of multiple rows or records are joined together to get a single value output. Common aggregate functions are –
- Average
- Count
- Sum
Q27: What are temporal data types in Oracle?
Answer: Oracle provides following temporal data types:
- Date Data Type – Different formats of Dates
- TimeStamp Data Type – Different formats of Time Stamp
- Interval Data Type – Interval between dates and time What are SET operators?
- SET operators are used with two or more queries and those operators are Union, Union All, Intersect and Minus.
Q28: What is VArray?
Answer: VArray is an oracle data type used to have columns containing multivalued attributes and it can hold bounded array of values.
Q29: What is an ALERT?
Answer: An alert is a window which appears in the center of the screen overlaying a portion of the current display.
Q30: What are the differences between LOV and List Item?
Answer: LOV is property whereas list items are considered as single item. List of items is set to be a collection of list of items. A list item can have only one column, LOV can have one or more columns.