SQL INTERVIEW QUESTIONS
What is SQL ?
- Structured Query Language(SQL) is a language designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard.
What are the different type of SQL's statements ?
SQL statements are broadly classified into three. They are
DDL Data Definition Language
DML Data Manipulation Language
DCL Data Control Language
what is a field in a database ?
- A field is an area within a record reserved for a specific piece of data. Examples: Employee Name, Employee ID, etc.
What is a database transaction?
Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.
What is a Database Lock ?
Database lock tells a transaction, if the data item in questions is currently being used by other transactions.
What is a primary key?
A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. To define a field as primary key, following conditions had to be met :
1. No two rows can have the same primary key value.
2. Every row must have a primary key value.
3. The primary key field cannot be null.
4. Value in a primary key column can never be modified or updated, if any foreign key refers to that primary key.
What is a Composite Key ?
A Composite primary key is a type of candidate key, which represents a set of columns whose values uniquely identify every row in a table.
For example – if “Employee_ID” and “Employee Name” in a table is combined to uniquely identify a row its called a Composite Key.
What is a Foreign Key ?
When a “one” table’s primary key field is added to a related “many” table in order to create the common field which relates the two tables, it is called a foreign key in the “many” table.
For example, the salary of an employee is stored in salary table. The relation is established via foreign key column Employee_ID_Ref which refers Employee_ID field in the Employee table.
Define SQL Update Statement ?
SQL Update is used to update data in a row or set of rows specified in the filter condition. The basic format of an SQL UPDATE statement is, Update command followed by table to be updated and SET command followed by column names and their new values followed by filter condition that determines which rows should be updated.
Define Join and explain different type of joins?
In order to avoid data duplication, data is stored in related tables. Join keyword is used to fetch data from related tables. “Join” return rows when there is at least one match in both table. Type of joins are
What is Self-Join?
Self-join is query used to join a table to itself. Aliases should be used for the same table comparison.
What is Cross Join?
Cross Join will return all records where each row from the first table is combined with each row from the second table.
What is a view?
The views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.
What is a materialized view?
Materialized views are also a view but are disk based. Materialized views get updates on specific duration, base upon the interval specified in the query definition. We can index materialized view.
Toggle tWhat are the advantages and disadvantages of views in a database?
1. Views don’t store data in a physical location.
2. The view can be used to hide some of the columns from the table.
3. Views can provide Access Restriction, since data insertion, update and deletion is not possible with the view.
1. When a table is dropped, associated view become irrelevant.
2. Since the view is created when a query requesting data from view is triggered, its a bit slow.
3. When views are created for large tables, it occupies more memory.
What is a stored procedure?
- Stored Procedure is a function which contains a collection of SQL Queries. The procedure can take inputs , process them and send back output.
What is a trigger?
Database triggers are sets of commands that get executed when an event(Before Insert, After Insert, On Update, On delete of a row) occurs on a table, views.
What are the advantages of a stored procedure?
Stored Procedures are precomplied and stored in the database. This enables the database to execute the queries much faster. Since many queries can be included in a stored procedure, round trip time to execute multiple queries from source code to database and back is avoided.
Explain the difference between DELETE , TRUNCATE and DROP commands?
Once delete operation is performed, Commit and Rollback can be performed to retrieve data.
Once the truncate statement is executed, Commit and Rollback statement cannot be performed. Where condition can be used along with delete statement but it can’t be used with truncate statement.
Drop command is used to drop the table or keys like primary,foreign from a table.
What is the difference between Cluster and Non cluster Index?
A clustered index reorders the way records in the table are physically stored. There can be only one clustered index per table. It makes data retrieval faster.
A non clustered index does not alter the way it was stored but creates a completely separate object within the table. As a result insert and update command will be faster.
What is Union, minus and Interact commands?
MINUS operator is used to return rows from the first query but not from the second query. INTERSECT operator is used to return rows returned by both the queries.
What is the difference between SQL and PL/SQL?
PL/SQL is a dialect of SQL that adds procedural features of programming languages in SQL. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
Is a NULL value same as zero or a blank space? If not then what is the difference?
A NULL value is not same as zero or a blank space. A NULL value is a value which is unavailable, unassigned, unknown or not applicable. Whereas, zero is a number and blank space is a character.
What is the purpose of the condition operators BETWEEN and IN?
The BETWEEN operator displays rows based on a range of values. The IN condition operator checks for values contained in a specific set of values.
What is the purpose of the group functions in SQL? Give some examples of group functions.
Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.
Whats wrong in the following query?
It doesnt have a GROUP BY clause. The subject_code should be in the GROUP BY clause.
SELECT subject_code, count(name)
GROUP BY subject_code;
What do you understand by a subquery? When is it used?
A subquery is a SELECT statement embedded in a clause of another SELECT statement. It is used when the inner query, or the subquery returns a value that is used by the outer query. It is very useful in selecting some rows in a table with a condition that depends on some data which is contained in the same table.
How do you insert null values in a column while inserting data?
Null values can be inserted into a table by one of the following ways –
Implicitly by omitting the column from the column list.
Explicitly by specifying the NULL keyword in the VALUES clause.
What is the difference between VARCHAR2 AND CHAR datatypes?
- VARCHAR2 represents variable length character data, whereas CHAR represents fixed length character data.
What is the pupose of DML statements in SQL?
The DML statements are used to add new rows to a table, update or modify data in existing rows, or remove existing rows from a table.
What is RDBMS ?
Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed or reassembled in many different ways without having to reorganize the database tables. Data from relational database can be accessed using an API , Structured Query Language (SQL).
Define SQL Insert Statement ?
SQL INSERT statement is used to add rows to a table. For a full row insert, SQL Query should start with insert into statement followed by table name and values command, followed by the values that need to be inserted into the table. The insert can be used in several ways:
1. To insert a single complete row.
2. To insert a single partial row.