SQL Interview Questions
Introduction
SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS) or stream processing in a relational data stream management system (RDSMS). It is beneficial in handling structured data, i.e. data incorporating relations among entities and variables.
What is SQL?
Structured Query Language is a database tool used to create and access databases to support software applications.
2. What are the different types of statements supported by SQL?
There are three types of SQL statements:
- DDL (Data Definition Language): It`s used to define the database as tables. It has three statements such as create, Alter, and Drop.
Some of the DDL Commends are Listed below:
CREATE TABLE & amp; amp ; nbsp; table_name
columns_name1 data_type(size),
columns_name2 data_type(size),
columns_name3 data_type(size),
ALTER: The ALTER table is used for modifying the existing table object in the database.
2. DML(Data Manipulation Language): Three statements are used to manipulate the data in records. Commonly used DML statements are Insert, Update, and Deletion.
The SELECT statement is used for the DML statement that is used for all or relevant records in the table.
3. DCL (Data Control Language): These statements are used to set privileges such as Grant as Revoke database access permission to the specific User.
3. What is DBMS?
A Database Management System (DBMS) is a program that controls the creation, maintenance, and use of a database.
DBMS can be termed as a File Manager that manages data in a database rather than saving it in file systems.
Another definition:
4. What is RDBMS?
RDBMS stands for Relational Database Management System. RDBMS stores the data in the collection of tables, which are related by common fields between the columns of the table. It also provides relational operators to manipulate the data stored in the tables.
5. Why do we use SQL constraints? Which constraints we can use while creating a database in SQL?
Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it.
Constraints are defined while creating the database itself with CREATE TABLE statement or even after the table is created once with ALTER.
TABLE statement: There are 5 major constraints used in SQL.
NOT NULL: That indicates that the column must have some value and cannot be left null.
UNIQUE: This constraint is used to ensure that each row and column has a unique value and no value is being repeated in any other row or
column
PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE: constraints such as on one or the combination of more than one column to identify the particular record with a unique identity.
FOREIGN KEY: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key
CHECK: It is used to ensure whether the value in columns fulfills the specified condition
7. What is normalization?
Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify fields that can be made in a single table.
8. What are all the different normalizations?
The normal forms can be divided into 4 forms, and they are explained below -.
1. First Normal Form (1NF): This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.
2. Second Normal Form (2NF): Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.
3. Third Normal Form (3NF): This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.
4. Fourth Normal Form (4NF): Meeting all the requirements of the third normal form and it should not have multivalued dependencies.
9. How many Aggregate Functions are available there in SQL?
SQL Aggregate Functions calculates values from multiple columns in a table and returns a single value.
There are 7 aggregate functions we use in SQL
AVG(): Returns the average value from specified columns
COUNT(): Returns the number of table rows
MAX(): Returns the largest value among the records
MIN(): Returns the smallest value among the records
SUM(): Returns the sum of specified column values
FIRST(): Returns the first value
LAST(): Returns Last value
10. What is an Index? What are all the different types of indexes?
An index is a performance tuning method for allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data. This indexing does not allow the field to have duplicate values if the column is uniquely indexed. A unique index can be applied automatically when the primary key is defined.
1. Clustered Index: This type of index reorders the physical order of the table and searches based on the key values. Each table can have only one clustered index.
2. Non-Clustered Index: Non-Clustered Index does not alter the physical order of the table and maintains the logical order of data. Each table can have 999 non-clustered indexes.
Conclusion:
In this article, we have put some questions that are considered the basics in SQL, and we will address them in the next articles to progress by developing questions that clarify this point.
