Guide for Interview Questions for SQL

RDBMS	DBMS
RDBMS stands for Relational Database Management   System.	DBMS stands for Database Management    System.
It stores the data in the rows & col in table format.	It stores the data in the format of files.
It is designed to handle large amounts of data.	It is designed to handle a small amount of data.
Multiple data elements are accessible together	Individual access to data elements is possible.
RDBMS support multiple users.	DBMS doesn’t support multiple users.
A distributed database is supported.	A distributed database is not supported.
In RDBMS normalization is not achievable.	In DBMS normalization is achievable.

The major applications of SQL include:

• Writing data integration scripts by the developers and database administrator.
• Setting and running analytical queries on the regular basis and making new datasets from the original data.
• Retrieving subsets of information within an original database for analytics and visualization purposes
• Most common use is Adding, updating, and deleting rows and columns of data in a database

SQL	DBMS
SQL stands for Structured Query Language.	DBMS stands for Database Management   System.
It is a query language.	It is a database.
SQL is designed for managing the database	DBMS is designed for providing the security to  database.
It allows the user to create a view of data.	It contains automatic backup and database recovery.
SQL consist of various types of Languages such as DDL, and DML.	It reduced the complexity of the relationship between the data.
For example:. SQL, SQL Server.	For example:, MySQL, Oracle

A subquery in SQL is a query in another query. We can also say it as the nested query or an inner query. Mostly subqueries are used to enhance the data to be queried by the main query.

The SELECT command is used to display the rows from the database based on the query. The SELECT command is a data manipulation language (DML) command

For example:. We have a student database for a school and we have multiple columns some are StuID, and StuName, and the query is to display the student name.

Query: SELECT * from student;

There are 4 subsets of SQL:

• Data definition language (DDL): DDL consists of SQL commands which can be used for defining database schema. DDL deals with the description of the database, update, and delete of the database where it consists of commands like CREATE, ALTER, TRUNCATE, and COMMENT.
• Data manipulation language (DML): DML is used to manipulate the existing data in the database. The DML commands are SELECT, UPDATE, INSERT, etc.
• Data control language (DCL): DCL controls the access to the data stored in the database and the DCL commands are GRANT and REVOKE.
• Transaction Control Language (TCL): TCL is used to deal with the transaction operations in the database. The TCL commands are COMMIT, ROLLBACK, SET TRANSACTION, SAVEPOINT, etc.

There are 5 subsets in SQL:

1. Data Definition Language

DDL stands for Data Definition Language where the commands are used to define the database schema. DDL is mostly used to describe the database schema to developers and to create, and modify the overall structure of the database.

The examples of DDL commands are: 

• CREATE – Using the create command we can create the database or its objects such as a table, index, function, and views.
• DROP – Using the drop command we can delete objects from the database.
• ALTER – Using the alter command we can change the structure of the database.
• TRUNCATE – Using the truncate command we can remove all the records from a table, including all spaces allocated for the records are removed.
• COMMENT – Using the comment command we can add comments to the data dictionary.
• RENAME – Using the rename command we can rename an object which exists in the database.

2. Data Manipulation Language

DML stands for Data Manipulation Language where the commands are used for manipulating the data in the database.

The examples of DML commands are:

• SELECT – Using the select command to retrieve data from the database.
• INSERT– Using the insert command to insert data into a table.
• UPDATE– Using the update command to update existing data within a table.
• DELETE– Using the delete command to delete records from a database table.

A JOIN clause is used to combine rows from more than one table based on the same column from both tables.  The two tables are merged and we will retrieve new data from that.

Inner Join: Most of the common types of SQL is Inner Join. Inner Join will return all the rows from multiple tables when the condition is satisfied.

Syntax Inner Join:

SELECT *
FROM Table_A
JOIN Table_B; 
SELECT *
FROM Table_A 
INNER JOIN Table_B;

Left Join: In Left Join of SQL only rows from the left table are returned and the union of left and right table where the condition is satisfied.

A primary key is a field or the combination of fields that uniquely identify each record in the table. The primary key is a unique key as the table can have only one primary key and it can not be null. For example, we have student data of a university or college where the columns are roll number and name and we want to display the unique kids having their roll number.  Here, the ROLL Number can be treated as the primary key for a student.

We can define a primary key in a student table as follows:

CREATE TABLE Student (
roll_number INT PRIMARY KEY,
name VARCHAR(45),
);

The foreign key is also known as the referencing key. We use a foreign key to link one or more tables together from the database.

A foreign key is often specified as a key that is related to the primary key of another table in simple terms it means that the foreign key field in one table refers to the primary key field of another table. It maintains referential integrity. ACID properties are maintained by the primary key-foreign key relationship. Foreign key also prevents actions that would destroy links between the child and parent tables from the database.

For example, we have student data of a university or college where the columns are roll number and name and we want to display the unique kids having their roll number. Here, the ROLL Number can be treated as the primary key for a student.

We can define a foreign key in a student table as follows:

CREATE TABLE Students ( 
roll_number INT NOT NULL 
name VARCHAR(255) 
LibraryID INT 
PRIMARY KEY (roll_number) 
FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID) 
);

A. SQLB. Oracle

C. HeidiSQL

D. All of the above

ANSWER: D (All of the above)

A. Structured Query Language

B. Structured Query List

C. Sample Query Language

D. None of these.

ANSWER: A (Structured Query Language)

A. BUILD TABLEB. CREATE TABLE

C. INSERT TABLE

D. SELECT TABLE

ANSWER: B (CREATE TABLE)

Explanation: CREATE TABLE function is used to create the table in SQL database.

A. FLOAT

B. NUMERIC

C. DECIMAL

D. CHARACTER

ANSWER: C (DECIMAL)

Explanation: DECIMAL is not a valid SQL type because in SQL it is a numeric type.

A. DELETEB. TRUNCATE

C. DROP

D. ALTER

ANSWER: B (TRUNCATE)

Explanation: TRUNCATE command is used to delete all the rows without removing the individual rows from the table. TRUNCATE statement is similar to the DELETE statement in SQL just without the WHERE clause in the query.

A. RevokeB. Grant

C. Both

D. None of this

ANSWER: C (Both)

Explanation: REVOKE and GRANT are the commands for the Data control language.

A. DIFFERENCEB. SOUNDEX

C. COCNAT

D. FIND

ANSWER: B(SOUNDEX)

A. Depends on the situation

B. Not Null

C. Both Null and Not Null

D. Null

ANSWER: D(Null)

Explanation: A primary key is a field or the combination of fields that uniquely identify each record in the table. The primary key is a unique key as the table can have only one primary key and it can not be null.

A. Only 1B. Only 2

C. Depends on the Columns

D. Depends on the situation

ANSWER: A (Only 1)

Explanation: A primary key is a field or the combination of fields that uniquely identify each record in the table. The primary key is a unique key as the table can have only one primary key and it can not be null.

A. TupleB. Degree

C. Entity

D. None of this

ANSWER: A(Tuple)
Explanation: The collection of rows & columns is called the table, whereas a table is known as the relation in the SQL therefore in a relation rows are called the tuples.

A. Dynamic data languageB. Data derivation language

C. Data definition language

D. Detailed data language

ANSWER: C(Data definition language)

A. COMMIT and ROLLBACK

B. UPDATE and INSERT

C. SELECT and INSERT

D. GRANT and COMMIT

ANSWER: A (COMMI and ROLLBACK)

A. DESC

B. ASC

C. NOT DEFINED FORMAT

ANSWER: B (ASC)

Explanation: In the command, if we are not specifying ASC or DESC after a SQL ORDER BY clause, the result is displayed in which order.

A. JOINB. CONCATE

C. PRODUCT

D. MULTIPLY

ANSWER: A (JOIN)

A. ALTERB. UNION

C. CHECK

D. B and C

ANSWER: B (UNION)

Conclusion on SQL Questions