SQL Interview Questions
This website contains SQL interview questions with answers. We have given SQL interview questions faced by freshers and experienced in real interviews in IT industry. Dear readers, these SQL Programming Interview questions have been specially designed so that you can get acquainted with the nature of the questions you may be ask during your interview.
1. What is DBMS?
Answer:- A Database is a shared collection of logically related data and description of these data, designed to meet the information needs of an organization.
A Database Management System is a software system that enables users to define, create, maintain, and control access to the database. Database Systems typically have high cost and they require high-end hardware configurations.
An Application Program interacts with a database by issuing an appropriate request (typically a SQL statement).
2. What are the function of DBMS?
Answer:- Database management systems provide many functions that help us to overcome the problems associated with file-based systems.Some of the functions of DBMS are as follows:-
1. Utilities:Data import/export, user management logging and audit.
2. Concurrency Control:Data concurrency means that many users can access data at the same time.
3. Integrity:Minimizing data redundancy and maximizing data consistency.
4. Security: Security rules determine which users can access the database, which data items each user can access, and which data operations (read, add, delete, or modify) users can perform.
5. Data Management: Store retrieve and modify data.
6. Transaction:Modification must be done successfully or not done in the database at all.
3. What are the types of DBMS?
Answer:- Database systems are categorized into four types based upon the underlying structure used to store data.
1. Hierarchical
2. Network
3. Relational
4. NoSql
4. What is RDBMS?
Answer:- RDBMS stands for Relational Database Management System. Relational database stores database data in the form of relations known as tables. Each relations must have a name.
5. What is SQL?
Answer:-SQL is for Structured Query Language (SQL) which is used to manage data in all relational databases like DB2, Oracle, SQL Server etc. It is a standard language that is used to perform tasks such as data retrieval, updation, deletion of data in the Database.
6. What are tables and Fields??
Answer:-
TABLE:-A table is a group of data that is organized into a model with columns and rows.
FIELD:-A table has a specific number of columns known as fields, and any number of rows can be called records.
Example:
Table: Student.
Field: Student ID, Student Name, Date of Birth.
Data: 206958, John, 10/02/1960.
7. What is a Candidate Key?
Answer:-A candidate key is a minimal set of columns that are used to uniquely identify a single tuple in a given relations.Candidate key is determined during database design.
8. What is a Primary Key?
Answer:- A primary key is a combination of fields that specifies a row uniquely. The primary key is a candidate key and must be unique and cannot contain a null value and only one primary key constraint has been set per table.
9. What is a unique key?
Answer:-The unique constraint is used to uniquely identify each record. The primary key has an automatic unique Contraint. We can define infinite unique Contraint in a table, but only one primary key constraint has been set per table.Unique Contraint can contain null value but Primary key can not.
10. What is a foreign key?
Answer:-A foreign key is a set of one or more columns in the child table whose values are required to match with corresponding columns in the parent table. Foreign key establishes a relationship between these two tables. Foreign key columns on child tables must be primary key or unique on the parent table. The child table can contain NULL values.
11. What is UNION And UNION ALL?
Answer:- UNION and UINON ALL are used to combine the result of two or more select queries. Select queries can be applied to the same or different tables, but they must have the same number of columns and their data types in the same position in the query.
12. What is Join?
Answer:-The SQL join statement is used to join the data or records of two or more tables based on the corresponding column between them.There are four different types of SQL joins: inner, left, right, and full.The easiest and most common way to learn the difference between Sql joins is to use the Venn diagram.
13. What are the types of join and explain each?
Answer:-There are different types of joins that can be used to obtain data and it depends on the relationship between the tables.
Inner Join: SQL inner joins are used to join data or records of two or more tables based on matching values in both tables. The Inner Join will create the resulting set until the condition is satisfied by combining all rows from both tables.
Right Join:SQL Right Join is used to return all data or records from the Table 2 plus match records to tables 1. If there is no match, the result from the left will be NULL.This is also known as RIGHT (OUTER) JOIN.
Left Join: SQL Left Join is used to return all data or records from the Table 1 plus match records to tables 2. If there is no match, the result from the right will be NULL. This is also known as LEFT (OUTER) JOIN.
Full Join:SQL Full Join is used to return all data or records when a match occurs in a Table 1 or Table 2 record and NULLs have to be filled in for missing matches on either side. It is also known as Full (OUTER) JOIN.
Self Join:SQL Self Join is used to join a table with itself, calling it Unary relationships. Each row of the table is associated with itself and every other row in the table.
14. What is Normalization?
Answer:- Normalization is the process of reorganizing data in a database so that data redundancy is reduced. All data should be stored at only one place. Data dependencies are logical. All related data items are stored together.The main aim of Normalization is to add, delete or modify field that can be made in a single table.
15. What is Denormalization?
Answer:- Denormalization is a technique that is used to access data in higher to lower common forms of a database. It is also the process of introducing redundancy into a table by incorporating data from related tables.
16. What are the different types of normalization?
Answer:- Common forms of normalizations can be divided into many forms, some of them are explained below -
First Normal Form:A relation R is said to be in the First Normal Form (3NF) if and only if : Each attribute contains atomic values (that cannot be split further), and the Value of each attribute contains single value from domain i.e. no multivalued attributes.
Second Normal Form:A relation R is said to be in the Second Normal Form (3NF) if and only if : Relation is already in 1NF, and there is no partial dependency between non-key attributes and key attributes.
Third Normal Form:A relation R is said to be in the Third Normal Form (3NF) if and only if : It is in 2NF and Transitive dependency does not exists between key attributes and non-key attributes through another non-key attribute.
Fourth Normal Form:A relation R is said to be in the Fourth Normal Form (3NF) if and only if : Fulfill all requirements of the third normal form and should not have multi-dependent dependencies.
17. What is a View?
Answer:- A view is a logical subset of a real table, which selects some rows or some columns from the actual table.Views are pseudo-tables. It does not have its own physical memory except for materialized views.A view can even represent joined tables. Views represent data that is selected from one or more tables using the SELECT statement.
18. What is an Index?
Answer:- An index is a database object which is used for random access. Indexes are specialized lookup tables that database search engines can use to speed up data retrieval. Instead of going for sequential search, the system will use the index directly to get the desired result. An index is a pointer to data in a table.
19. What are the different types of indexes?
Answer:- Common forms of normalizations can be divided into many forms, some of them are explained below -
Single-Column Indexes:It is the Index that is created based on more than one column of a table.
Unique Indexes:It is an index that is not only used for performance, but is also used for data integrity. This does not allow any duplicate values to be inserted into the table.
20. What is Cursor?
Answer:- A cursor can be thought of as a pointer to a specific row within the query result. The pointer can be moved from one row to another. Depending on the type of cursor, you may also be able to move it to the previous row.
21. What is query?
Answer:-Subquery is a query within a query. The outer query is called as main query, and inner query is called subquery. A subquery must be enclosed in brackets and can be used in SELECT, FROM, WHERE and HAVING clauses.
22. What is subquery?
Answer:- getchar() is a standard function that gets a character from the stdin while getch() is non-standard it gets a character from the keyboard (which may be different from stdin) and does not echo it.
23. What is Independent Subquery?
Answer:- In an independent Subquery, internal and external queries are independent of each other. You can run an internal query and observe its result independent of the external query. Independent Subquery are classified into a single row and into multiple row types, based on the number of rows returned.
24. What is a Correlated Subquery?
Answer:- A correlated subquery is one in which the internal query that relies on the external query for execution. In particular it uses a column from one of the tables in the external query. The internal query is executed iteratively for each selected row of the external query. In the case of independent Subquery, the internal query executes just once.
25. What is a Auto Increment?
Answer:- Auto-increment allows a unique number to be automatically generated when a new record is inserted into a table.Often this is the primary key field that we would like to create automatically every time a new record is inserted.
26. What is Triggers?
Answer:- Whenever a specified database event is occurs, there are database callback functions, which are automatically invoked known as Triggers.
Example: When a new employee is added to the employee database, new records should be created in the related tables like Salary, Department and Attendance tables.
27. What is Data Integrity?
Answer:- Data integrity refers to maintaining and assuring the accuracy and consistency of data over its entire life-cycle. Database Systems ensure data integrity through constraints which are used to restrict data that can be entered or modified in the database.
28. What is Transactions?
Answer:- A unit of work performed against a database is known as Transactions.
For example, if you are creating a record, updating a record, or deleting a record from a table, you are doing transactions on the table. Controlling transactions is important to handle database errors and data integrity.
29. What is Online Transaction Processing (OLTP)?
Answer:- Online Transaction Processing (OLTP) manages transaction based applications which can be used for data entry, data retrieval and data processing. Data in OLTP is dynamic (day to day transactions), and stored at a micro level.
Example :- Bank Transactions on a daily basis.
30.What is OLAP (Online Analytical Processing)?
Answer:-Data in OLAP is static (means historical data), and data is collected or summarized and stored at a high level. Operations in OLAP is performed using large amounts of data.OLAP databases are divided into one or more cubes. The cubes are designed in such a way that creating and viewing reports become easy.
Example:- Update project details of an employee.
Also check :
Discussion