Q 1. What are the subsets of SQL? Explain them.
The following are the three subsets of SQL:
Data Definition Language (DDL) – It allows end-users to CREATE, ALTER, and DELETE database objects.
Data Manipulation Language (DML) – With this, you can access and manipulate data. It allows you to Insert, Update, Delete, and Retrieve data from the database.
Data Control Language (DCL) – This lets you control access to the database. It includes the Grant and Revoke permissions to manipulate or modify the database.
Q 2. What is the primary key?
1. A primary key constraint uniquely identifies each row/record in a database table.
2. Primary keys must contain unique values.
3. Null value and duplicate values are not allowed to be entered in the primary key column.
4. A table can have only one primary key. It can consist of single or multiple fields.
Q 3. What is a foreign key?
A foreign key (referencing key) is used to link two tables together. It is a column or a combination of columns whose values match a Primary Key in a different table.
It acts as a cross-reference between tables because it references the primary key of another table and established a link between them.
Q 4. Explain the different types of indexes in SQL.
There are three types of indexes in SQL:
1. Unique Index – It does not allow a field to have duplicate values if the column is unique indexed.
2. Clustered Index – This index defines the order in which data is physically stored in a table. It reorders the physical order of the table and searches based on key values. There can be only one clustered index per table.
3. Non-Clustered Index – It does not sort the physical order of the table and maintains a logical order of the data. Each table can have more than one non-clustered index.
Q 5. What is the difference between TRUNCATE and DELETE?
This is one of the most commonly asked SQL interview questions.
Q 6. What is the difference between below cmds.
SELECT * FROM MyTable WHERE MyColumn <> NULL
SELECT * FROM MyTable WHERE MyColumn IS NULL
The first syntax will not work because NULL means ‘no value’, and you cannot use scalar value operators. This is why there is a separate IS – a NULL predicate in SQL.
Q 7. Can you name different types of MySQL commands?
SQL commands are divided into the following categories –
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)
Transaction Control Language (TCL)
Q 8. Explain different DDL commands in MySQL.
DDL commands include –
CREATE – Used to create the database or its objects like table, index, function, views, triggers, etc.
DROP – Used to delete objects
ALTER – Used to change database structures
TRUNCATE – It erases all records from a table, excluding its database structure
COMMENT – Used to add comments to the data dictionary
RENAME – Used to rename a database object
Q 9. Explain different DML commands in MySQL.
This is one of the most popularly asked SQL interview questions.
DML commands include –
SELECT – Used to select specific database data
INSERT – Used to insert new records into a table
UPDATE – It helps in updating existing records
DELETE – Used to delete existing records from a table
MERGE – Used to UPSERT operation (insert or update)
CALL – It is used when you need to call a PL/SQL or Java subprogram
EXPLAIN PLAN – Used to interpret data access path
LOCK TABLE – Used to control concurrency
Q 10. Explain different DCL commands in MySQL.
DCL commands are –
GRANT – It provides user access privileges to the database
DENY – Used to deny permissions to users
REVOKE – Used to withdraw user access by using the GRANT command
Q 11. Explain different TCL commands in MySQL.
DCL commands include –
COMMIT – Used to commit a transaction
ROLLBACK – Used to roll back a transaction
SAVEPOINT – Used to roll back the transaction within groups
SET TRANSACTION – Used to specify transaction characteristics
Q 12. What are the different types of Database relationships in MySQL?
There are three types of Database Relationship –
One-to-one – Both tables can have only one record
One-to-many – The single record in the first table can be related to one or more records in the second table
Many-to-many – Each record in both the tables can be related to any number of records
Q 13. What is Normalization?
Normalization is a database design technique to organize tables to reduce data redundancy and data dependency.
Q 14. What are the different types of Normalization?
There are six different types of Normalization –
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Fourth Normal Form (4NF)
Fifth Normal Form (5NF)
Q 15. How many TRIGGERS are allowed in the MySQL table?
Ans: 6 triggers are allowed in the MySQL table.
BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE
Q 16. What is a constraint, and how many levels of constraints are there?
Constraints are the representation of a column to enforce data entity and consistency.
There are two levels of constraint –
Column level – Limits only column data
Table level – Limits whole table data
Following are the most used constraints that can be applied to a table:
NOT NULL
UNIQUE
CHECK
DEFAULT
PRIMARY KEY
FOREIGN KEY
Q 17. What is Data Integrity?
Data integrity attributes to the accuracy, completeness, and consistency of the data in a database. It also refers to the safety and security of data and is maintained by a collection of processes, rules, and standards that were implemented during the design phase.
Three types of data integrity are:
Column Integrity
Entity Integrity
Referential Integrity
Q 18. What is Referential Integrity?
Referential integrity is a relational database concept that suggests that the accuracy and consistency of data should be maintained between primary and foreign keys.
Q 19. What is the ACID property in SQL?
ACID is short for Atomicity, Consistency, Isolation, Durability.
It ensures Data Integrity during a transaction.
Atomicity: It means either all the operations (insert, update, delete) inside a transaction take place or none.
Consistency: Consistency ensures that the data must meet all the validation rules. Irrespective of whatever happens in the middle of the transaction, Consistency property will never leave your database in a half-completed state.
Isolation: It means that every transaction is individual. One transaction can’t access the result of other transactions until the transaction is completed.
Durability: It implies that maintaining updates of committed transactions is important. It refers to the ability of the system to recover committed transaction in case of failure.
Q 20. Explain string functions in SQL?
SQL string functions are used for string manipulation.
Following are the extensively used SQL string functions:
UPPER(): Converts character data to upper case
LOWER(): Converts character data to lower case
SUBSTRING() : Extracts characters from a text field
RTRIM(): Removes all whitespace at the end of the string
LEN(): Returns the length of the value in a text field
REPLACE(): Updates the content of a string.
LTRIM(): Removes all whitespace from the beginning of the string
CONCAT(): Concatenates function combines multiple character strings
Q 21. How can you create an SQL table from another table without copying any values from the old table?
Syntax:
CREATE TABLE new_table
AS (SELECT *
FROM old_table WHERE 1=2);
This will create a new table with the same structure as the old table with no rows copied.
Q 22. Write the SQL query to update the student names by removing leading and trailing spaces.
This can be done by using ‘Update’ command with ‘LTRIM’ and ‘RTRIM’ function.
Syntax:
UPDATE StudentDetails
SET FullName = LTRIM(RTRIM(FullName));
Q 23. Write the SQL query to fetch alternate records from a table
Records can be fetched for odd and even row numbers:
Syntax to fetch even numbers:
Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=0
Syntax to fetch odd numbers:
Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=1
Q 24. How do you return a hundred books starting from the 15th?
The syntax will be:
SELECT book_title FROM books LIMIT 15, 100.
The first number in LIMIT is the offset, and the second is the number.
Q 25. Name the default port for the MySQL server.
The default port for the MySQL server is 3306.
Q 26. How do you implement Enums and sets internally in MySQL?
To implement an ENUM column, use the given syntax:
CREATE TABLE table_name ( … col ENUM (‘value1′,’value2′,’value3’), … );
Q 27. What are the different groups of data types in MySQL?
There are three groups of data types in MySQL, as listed below:
String Data Types – BINARY, VARBINARY, TINYBLOB, CHAR, NCHAR, VARCHAR, NVARCHAR, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, LONGBLOB, LONGTEXT, ENUM, SET, MEDIUMTEXT.
Numeric Data Types – MEDIUMINT, INTEGER, BIGINT, FLOAT, BIT, TINYINT, BOOLEAN, SMALLINT, DOUBLE, REAL, DECIMAL.
Date and Time Data Types – TIMESTAMP, TIME, DATE, DATETIME, YEAR.
Q 28. What is the default storage engine in MySQL?
InnoDB is the default storage engine in MySQL.
Q 29. Explain the use of the NVL() function.
The NVL() function converts the Null value to the other value.
Q 30. What is join in SQL?
Joins are used to combine rows from two or more tables, based on a related column between them.
Types of Joins:
• INNER JOIN − Returns rows when there is a match in both tables.
• LEFT JOIN − Returns all rows from the left table, even if there are no matches in the right table.
• RIGHT JOIN − Returns all rows from the right table, even if there are no matches in the left table.
• FULL OUTER JOIN − Returns rows when there is a match in one of the tables.
• SELF JOIN − Used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
Example of Inner Join:
SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
INNER JOIN table2
ON table1.commonfield = table2.commonfield;
Similarly you can apply all joins.
Q 31. Difference between DBMS and RDBMS?
RDBMS: Data is stored in table format
DBMS: Data is stored in file format
RDBMS: Multiple data elements are accessible together
DBMS: Individual access of data elements is possible
RDBMS: Data in the form of a table are linked together
DBMS: There is no connection between the data
RDBMS: It supports multiple users
DBMS: It supports single user
RDBMS: It features multiple level of security while handling data
DBMS: There is low security while handling data
RDBMS: Example: Oracle, SQL Server
DBMS: Example: XML, Microsoft Access
These questions will definitely help you in preparing for interviews.
All the Best!!!