This is a set of 15 questions SQL Interview Questions and Answers. For the tutorial of PL/SQL, you can visit our site’s PL/SQL section.
Also, Read:=PL/SQL Interview Questions
1. What is Database?
A Database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. The database can be vast and complex and such databases are developed using fixed design and modeling approaches.
2. What is SQL?
SQL is a structured query language that provides the interface to Relational Database System. SQL was developed in 1970 by IBM. In common usage of SQL also encompasses DML(insert, update, delete) and DDL used for creating and modifying tables and other database structures.
3. What are tables and fields?
A table is an organized collection of data stored in the form of rows and columns. columns can be categorized as vertical and rows as horizontal. The columns are called fields and rows are called records in SQL.
4. What are the constraints in SQL?
Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in a SQL table during the creation of the table or after creating using the after table command. The constraints are:
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
5. What is Unique and Primary Key? What is the difference between them?
Primary key: Primary key is a column of the table which uniquely identifies each row in the table only one primary key is allowed to work on the table. The Primary key can not accept duplicate and Null values.
Unique Key: Unique key constraints also identify an individual tuple uniquely in relation to or table. A table can have more than one unique key, unlike a primary key. Unique key constraints can accept only one NULL value for the column. Unique constraints are also referenced by the foreign key of another table.
The main Key Difference between them are:
- Primary key will not accept null values where as unique key can accepts the null values.
- A table can have only one Primary key where as there can be multiple unique key on a table.
- A Primary key used to serve as a unique identifier for each row in a table where as Unique key uniquely decides a row which isn’t primary key.
- A clustered index automaticly created when a primary key defined in a table.
- Unique key generates the non-clustered index in a table.
6. What is index in SQL?
An index is a schema object created on a specific set of one or more columns on a specific database table. Indexes are automatically used by the Oracle database to improve performance for certain queries. The Oracle database supports several types of indexes.
Syntax of Creating index:
CREATE INDEX index_name ON table_name; Drop Command: DROP INDEX index_name;
2 types of the index are present in SQL Server:
- The clustered indexes are indexes which are physically stored in order means it stores in ascending or descending order in Database.
- Clustered indexes are created one for each table.
- When primary key is created then clustered index has been automatically created in the table.
- If table is under heavy data modifications the clustered indexes are preferable to use.
- The clustered indexes are used for searching purpose as we can create clustered indexes where primary is is defined.But Non clustered indexes are indexes which will be created on the multiple joining conditions,multiple filters used in query.
- We can create 0 to 249 non-clustered indexes on single table.
- Foreign keys should be non clustered.When user wants to retrieve heavy data from fields other than primary key the non clustered indexes are useful.
7. What are DML, DDL, and DCL ? give example of them.
As we all know that SQL is the database language by the use of which we can perform certain operations on the existing database and also use this language to create a database. In SQL, we use certain commands like Create, Drop, Insert, etc. to do the required tasks.
These SQL commands are mainly categorized into four categories:
- DDL – Data Definition Language
- DQl – Data Query Language
- DML – Data Manipulation Language
- DCL – Data Control Language
8. What is the difference between in SQL window and Command Window in PL/SQL Developer tool.
In the Command window, the command will execute by pressing the ‘enter’ button other than the SQL window.
9. What is Heap Table in SQL?
A regular table in the Oracle database, also known as a heap table, contains an unordered set of records.
10. What is Normalization in SQL? How its works?
Normalization is a method of organizing the data in the database. It entails organizing the columns and tables of a database to assure that their dependencies are appropriately enforced by database integrity constraints. It usually divides a large table into smaller ones, so it can more efficient. Normalization has two main purposes first, it is used to remove the repeated data, the second one is to ensure the data dependencies make some logical sense.
11. What is the default constraint?
The DEFAULT constraint of SQL is used to set a default value into a column. The default value will be added to all new records if no WHERE clause is specified for that.
12. what are unique constraints?
The UNIQUE constraint uniquely identifies each record in a table, It assures that all values in the column are different.
13. What is a foreign key?
FOREIGN KEY is a field (or group of fields) in a table, that directs to the PRIMARY KEY in another table.
The table with the foreign key is called the child table, and the table with the primary key is called the parent table.
14. What is Not Null Contraints?
NOT NULL constraint executes a column to NOT accept the NULL values.
15. Why do we use the FLOOR function in SQL Server?
The FLOOR() function of SQL helps us to find the largest integer value to a given number that is smaller than or equal to a number.