Interview Questions for SQL
Introduction on SQL Questions:
- Relational databases
- Object-oriented databases
- Distributed databases
- Data warehouses
- NoSQL databases
- Graph databases
2. What is DBMS?
3. What is RDBMS? How is it Different from DBMS?
RDBMS | DBMS |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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
5. Difference between SQL Vs DBMS?
SQL | DBMS |
|
|
|
|
|
|
|
|
|
|
|
|
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.
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.
DML stands for Data Manipulation Language where the commands are used for manipulating the data in the database.
- 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.
10. What is JOINT in SQL and Explain any 2 Types?
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.
Syntax Left Join:
SELECT *
FROM Table_A A
LEFT JOIN Table_B B
ON A.col = B.col;
Right Join: In Right Join of SQL all the rows from the right table are returned but only the matching rows from the left table where the join condition is fulfilled.
Syntax right Join:
SELECT *
FROM Table_A A
RIGHT JOIN Table_B B
ON A.col = B.col;
Full Join: In Full join of SQL all the records are returned when there is a match in any of the tables. Therefore, it returns all the rows from the left-hand side table and all the rows from the right-hand side table.
Syntax Full Join:
SELECT *
FROM Table_A A
FULL JOIN Table_B B
ON A.col = B.col;
There are a plethora of types of joins, as you can refer to below. Below is a cheat sheet for various types of JOINT in SQL.
11. What is a Primary Key in SQL?
We can define a primary key in a student table as follows:
CREATE TABLE Student (
roll_number INT PRIMARY KEY,
name VARCHAR(45),
);
12. What is a Foreign Key?
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)
);
MCQ
1. Which of the following are some common RDBMS in use?
A. SQL
B. Oracle
C. HeidiSQL
D. All of the above
ANSWER: D (All of the above)
2. What is the full form of SQL?
A. Structured Query Language
B. Structured Query List
C. Sample Query Language
D. None of these.
ANSWER: A (Structured Query Language)
3. Which command do we use to create a new table in SQL?
A. BUILD TABLE
B. 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.
4. Which of the following is not a valid SQL type?
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.
5. Which of the following commands delete all the rows from the table?
A. DELETE
B. 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.
6. From the option which command is a part of Data Control Language?
A. Revoke
B. Grant
C. Both
D. None of this
ANSWER: C (Both)
Explanation: REVOKE and GRANT are the commands for the Data control language.
7. Which of the following SQL functions compares the similarities of 2 strings and returns the result as a 4 character code?
A. DIFFERENCE
B. SOUNDEX
C. COCNAT
D. FIND
ANSWER: B(SOUNDEX)
8. Primary key can not be?
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.
9. How Many Primary keys can have in a table?
A. Only 1
B. 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.
10. What are Rows of Relation Known as?
A. Tuple
B. 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.
11. Which of the following is the full form of DDL?
A. Dynamic data language
B. Data derivation language
C. Data definition language
D. Detailed data language
ANSWER: C(Data definition language)
12. Which of the following are TCL commands?
A. COMMIT and ROLLBACK
B. UPDATE and INSERT
C. SELECT and INSERT
D. GRANT and COMMIT
ANSWER: A (COMMI and ROLLBACK)
13. 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. 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.
14. Which data manipulation command is used to combine the rows from one or more tables?
A. JOIN
B. CONCATE
C. PRODUCT
D. MULTIPLY
ANSWER: A (JOIN)
15. Which of the following command is not in SQL
A. ALTER
B. UNION
C. CHECK
D. B and C
ANSWER: B (UNION)
Conclusion on SQL Questions
This blog consists of numerous questions related to the topic such as database, SQL, different types of Joint, and many more. Whenever you are giving your interview always lay your answer in a systemic manner such as giving first the definition and following the explanation with a situation that has an example. Lastly, give the answers with the syntax of the SQL question so the interviewer knows you have an immense and clear knowledge of the given topic.
Comments
Post a Comment