SQL is an important database language which is essential in a software developer’s career.
If you’re preparing for SQL, then following are some of the important interview questions and answers that you should know.
1. What is SQL?
SQL is an abbreviation for Structured Query Language, which is a language created to communicate with the database.
SQL is used to retrieve information, make changes, update and delete data from the database.
2. What are the tables and fields?
Tables and fields are an important part of the database. Tables are sets of data which is organized in a model by rows and columns.
The columns are specified in numbers for a table which is called a field.
3. What is a database?
A database is essentially an organized form of data. A database organizes data in such a way that it can be easily accessed, edited, retrieved and managed.
4. What is DBMS?
DBMS or Database Management System is a created program to manage, access and edit any database. It can be considered as a file manager which helps you manage your files.
5. What is a unique key?
A unique key is set of more than one fields that uniquely identify a record in the database.
6. What is a primary key?
A primary key is a field in a table which identifies each row uniquely within a database.
7. What is RDBMS?
RDBMS is responsible for storing the data into a collection of tables.
RDBMS or Relational Database Management System also allows relational operators to manipulate data stored in the tables.
8. What is a join?
A join in SQL is a keyword which is used to query data from tables.
It is essentially a clause which is used to combine two or more rows from different tables based on a related column among them.
9. What are the types of join? Define each.
When it comes to different types of joins, it depends on the relationship between tables.
- Inner join: It selects all rows from both tables if the columns have a match.
- Left join: Left join returns all rows from the left side table even if there’s no match with the right side table.
- Right join: Right join returns all rows from the right side table even if there’s no match with the left side table.
- Full join: Full join returns all rows from all tables when there are matching rows in any of the tables.
10. What is normalization and what are its types?
Normalization is used to minimize dependency and redundancy by arranging tables and fields of a database.
Normalization can be classified into 5 types:
- First Normal Form (1NF): First Normal Form or 1NF ensures that there are no multi-valued attributes in a table. A relation is in 1NF when all the attributes in a relation are singled out.
- Second Normal Form (2NF): Second Normal Form needs to first fulfil the requirement of 1NF before it can be normalized as 2NF. Second Normal Form places subsets of data into separate tables. It creates relationships between those tables using a primary key.
- Third Normal Form (3NF): Third Normal Form or 3NF should fulfil the requirement of 2NF. It removes the columns that are not dependent on primary key constraints.
- Fourth Normal Form (4NF): Similar to previous forms, 4NF needs to fulfil the requirement of 3NF and should not have multi-valued dependencies.
11. What is the difference between SQL and My SQL?
While SQL is used in accessing, manipulating and changing the data in a database, MySQL is a Relational Database Management System which allows the data to be kept organized within a database.
12. What is the difference between CHAR & VARCHAR2 datatype in SQL?
Both datatypes are used for characters, however, the core difference both is that VARCHAR2 is used for a character string of variable length while CHAR is used for a fixed length.
13. Name all types of case manipulation functions in SQL?
Mainly, there are a total of 3 types of case manipulation functions in SQL:
LOWER: This function returns the string in lowercase. It takes the string as an argument and returns the string into lowercase.
Syntax:
LOWER( ‘string’ )
UPPER: Similar to the LOWER function, the UPPER function returns the string in uppercase.
Just like the LOWER function, it takes the string as an argument and returns the string into uppercase.
Syntax:
UPPER( ‘string’ )
INITCAP: This function is a combination of both UPPER and LOWER functions as it returns the string with the first letter in uppercase and the remaining in lowercase.
Syntax:
INITCAP( ‘string’ )
14. Define Data Manipulation Language or DML?
As the name suggests DML (Data Manipulation Language) is utilized to access and/or manipulate data in the database.
DML allows you to do the following listed functions:
- Update data in the database
- Delete data in the database
- Retrieve data from the database
- Insert data or rows in the database
15. Describe a view in SQL?
Views can be described as virtual tables which have rows and columns similar to a real table in a database.
A view can be created by selecting fields from multiple tables in a database.
A view can have all or certain rows from a table depending upon the conditions.
A view is created by the statement CREATE VIEW.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;view_name: Name for the View
table_name: Name of the table
condition: Condition to select rows
16. What is an Index?
A database index enhances the speed of data retrieval operations.
It’s a data structure which improves the retrieval speed at the cost of additional writes and storage space.
Even though indexes need extra space on the storage, they allow a faster search.
17. Define Data Definition Language or DDL?
CREATE, DROP and ALTER are the queries you can execute with DDL or Data Definition Language. In a nutshell, it allows executing queries that define data.
18. What is the difference between operators BETWEEN and IN in SQL?
BETWEEN operator is used to getting rows based on various range of values.
For example:
Select * FROM Patients
WHERE ROOM_NO BETWEEN 7 and 20;
The above query will select the rows from the table Patients where the value of the field ROOM_NO is between 7 and 20.
On the other hand, the IN operator is used to check for values in specific sets.
For example:
Select * FROM Patients
WHERE ROOM_NO IN (8,10,14);
The above query will only select the rows from the table Patients where the value of the field ROOM_NO is either 8 or 10 or 14.
19. Does SQL support programming language features?
Even though SQL is a language, it, however, does not support programming language features.
SQL is a command language which is used to query, update, access, delete and more in database.
20. List out some differences between SQL and PL/SQL?
Following are some of the differences between SQL and PL/SQL:
| SQL | PL/SQL |
|---|---|
| SQL is a command language | PL/SQL is a complete programming language |
| Embedding SQL in PL/SQL is possible | Embedding PL/SQL in SQL is impossible |
| It’s data oriented | It’s procedural language |
| SQL tells what to do | PL/SQL tells how to do it |
| It is used for manipulating data | It’s used for creating applications |
| Only one statement can be executed at a time | Block of statements can be executed at a time |
21. What is a relationship? List the various relationships.
A relationship can be defined as the relation between tables in a database. Following are the database relationships:
- One to One
- One to Many
- Many to One
- Self-Referencing
