Basic SQL Interview Questions And Answers Pdf

1. What are the properties of the transaction?
Answer: Properties of the transaction are known as ACID properties, such as
Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully if not then transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes undone
Consistency: Ensures that all changes made through successful transaction are reflected properly on database
Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on other
Durability: Ensures that the changes made in the database with committed transactions persist as it is even after a system failure.

2. Explain SQL Data Types?
Answer: In SQL Server, each column in a database table has a name and a data type. We need to decide what type of data to store inside each and every column of a table while creating a SQL table.

3. Explain the working of SQL Privileges?
Answer: SQL GRANT and REVOKE commands are used to implement privileges in SQL multiple user environments. The administrator of the database can grant or revoke privileges to or from users of database object like SELECT, INSERT, UPDATE, DELETE, ALL, etc.
GRANT Command: This command is used to provide database access to user apart from an administrator.
Syntax: GRANT privilege_name
ON object_name
TO {user_name|PUBLIC|role_name}
In the above syntax WITH GRANT, OPTIONS indicates that the user can grant access to another user too.
REVOKE command: This command is used to provide database deny or remove access to database objects.
Syntax: REVOKE privilege_name
ON object_name
FROM {user_name|PUBLIC|role_name};

4. Difference between TRUNCATE, DELETE and DROP commands?
Answer: DELETE removes some or all rows from a table based on the condition. It can be rolled back.
TRUNCATE removes ALL rows from a table by de-allocating the memory pages. The operation cannot be rolled back
DROP command removes a table from the database completely.

5. What is Referential Integrity?
Answer: Set of rules that restrict the values of one or more columns of the tables based on the values of the primary key or unique key of the referenced table.

6. What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?
Answer: BETWEEN operator is used to displaying rows based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.
Example of BETWEEN:
SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;
Example of IN:
SELECT * FROM students where ROLL_NO IN (8,15,25);

7. Why are SQL functions used?
Answer: SQL functions are used for the following purposes:
To perform some calculations on the data
To modify individual data items
To manipulate the output
To format dates and numbers
To convert the data types

8. What do you mean by recursive stored procedure?
Answer: Recursive stored procedure refers to a stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps the programmers to use the same set of code n number of times.

9. What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?
Answer: HAVING clause can be used only with SELECT statement. It is usually used in a GROUP BY clause and whenever GROUP BY is not used, HAVING behaves like a WHERE clause.
Having Clause is only used with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query.

10. List some case manipulation functions in SQL?
Answer:
There are three case manipulation functions in SQL, namely:
LOWER: This function returns the string in lowercase. It takes a string as an argument and returns it by converting it into lower case. Syntax:
LOWER(‘string’)
UPPER: This function returns the string in uppercase. It takes a string as an argument and returns it by converting it into uppercase. Syntax:
UPPER(‘string’)
INITCAP: This function returns the string with the first letter in uppercase and the rest of the letters in lowercase. Syntax:
INITCAP(‘string’)
Apart from this SQL Interview Questions blog, if you want to get trained from professionals on this technology, you can opt for a structured training from SVR! Click below to know more.