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
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
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?
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:
UPPER: This function returns the string in uppercase. It takes a string as an argument and returns it by converting it into uppercase. Syntax:
INITCAP: This function returns the string with the first letter in uppercase and the rest of the letters in lowercase. Syntax:
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.
11. What is an ALIAS command?
Answer: ALIAS name can be given to any table or a column. This alias name can be referred in WHERE clause to identify a particular table or a column.
Select emp.empID, dept.Result from employee emp, department as dept where emp.empID=dept.empID
In the above example, emp refers to alias name for employee table and dept refers to alias name for department table.
12. What are the aggregate and scalar functions?
Answer: Aggregate functions are used to evaluate mathematical calculation and return a single value. These calculations are done from the columns in a table. For example- max(),count() are calculated with respect to numeric.
Scalar functions return a single value based on the input value. For example – UCASE(), NOW() is calculated with respect to a string.
13. What is a Stored Procedure?
Answer: A Stored Procedure is a function which consists of many SQL statements to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required which saves time and avoid writing code again and again.
14. List some advantages and disadvantages of Stored Procedure?
A Stored Procedure can be used as modular programming which means create once, store and call for several times whenever it is required. This supports faster execution. It also reduces network traffic and provides better security to the data.
The only disadvantage of Stored Procedure is that it can be executed only in the database and utilizes more memory in the database server.
15. List all the types of user-defined functions?
There are three types of user-defined functions, namely:
- Scalar Functions
- Inline Table-valued functions
- Multi-statement valued functions
- Scalar returns the unit, variant defined the return clause. Other two types of defined functions return table.
16. What do you mean by Collation?
Answer: Collation is defined as a set of rules that determine how data can be sorted as well as compared. Character data is sorted using the rules that define the correct character sequence along with options for specifying case-sensitivity, character width, etc.
17. What are the different types of Collation Sensitivity?
Following are the different types of collation sensitivity:
Case Sensitivity: A and a and B and b.
Kana Sensitivity: Japanese Kana characters.
Width Sensitivity: Single byte character and double-byte character.
Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on this technology, you can opt for structured training from SVR
18. What are the different authentication modes in SQL Server? How can it be changed?
Answer: Windows mode and Mixed Mode – SQL and Windows. You can go to the below steps to change authentication mode in SQL Server:
Click Start> Programs> Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group.
Then select the server from the Tools menu.
Select SQL Server Configuration Properties, and choose the Security page.
19. What is a Data warehouse?
Answer: Datawarehouse refers to a central repository of data where the data is assembled from multiple sources of information. Those data are consolidated, transformed and made available for the mining as well as online processing. Warehouse data also have a subset of data called Data Marts.
20. What are Local and Global variables?
These variables can be used or exist only inside the function. These variables are not used or referred by any other function.
These variables are the variables which can be accessed throughout the program. Global variables cannot be created whenever that function is called.
21. How can you fetch alternate records from a table?
Answer: You can fetch alternate records i.e both odd and even row numbers. For example- To display even numbers, use the following command:
A select student from (Select row, students from student) where mod(row,2)=0
Now, to display odd numbers:
Select student from (Select row, a student from student) where mod(row,2)=1
22. When are we going to use truncate and delete?
Answer: TRUNCATE is a DDL command, whereas DELETE is a DML command.
We can’t execute a trigger in case of TRUNCATE whilst with DELETE, we can accomplish a trigger.
TRUNCATE is quicker than DELETE, for the reason that when we use DELETE to delete the data, at that time it stores the whole statistics in the rollback gap on or after where we can get the data back after removal. In the case of TRUNCATE, it will not store data in rollback gap and will unswervingly rub it out. TRUNCATE do not recover the deleted data.
We can use any condition in WHERE clause using DELETE but it is not possible with TRUNCATE.5.If a table is referenced by any foreign key constraints, then TRUNCATE won’t work.
Go through this SQL tutorial to learn more about SQL commands.
23. What is the SQL CASE statement used for? Explain with an example?
It allows you to embed an if-else like a clause in the SELECT clause.
SELECT Employee_Name, CASE Location
WHEN ‘Alex’ THEN Bonus * 2
WHEN ‘robin’ THEN Bonus *, 5
Read this blog to learn why SQL Optimization has always been an important aspect of database management.
24. How do you maintain database integrity where deletions from one table will automatically cause deletions in another table?
You can create a trigger that will automatically delete elements in the second table when elements from the first table are removed.
25. How to find the second highest salary of an Employee?
Answer: There are many ways to find the second highest salary of Employees in SQ. You can either use SQL Join or Subquery to solve this problem.
Here is SQL query using Subquery :
Select MAX(Salary) from Intellipaat_emplyee WHERE Salary NOT IN ( select MAX.
26. When is the Explicit Cursor Used?
Answer: If the developer needs to perform the row by row operations for the result set containing more than one row, then he unambiguously declares a pointer with a name. They are managed by OPEN, FETCH and CLOSE.%FOUND, %NOFOUND, %ROWCOUNT, and %ISOPEN characteristics are used in all types of pointers.
27. Explain correlated query work?
Answer: It’s most important to be attentive to the arrange of operations in an interrelated subquery.
First, a row is processed in the outer doubt.
Then, for that exacting row, the subquery is executed – as a result for each row processed by the outer query, the subquery will also be processed. In a correlated subquery, each time a line is worked for Emp1, the subquery will also make a decision on the exacting row’s value for Emp1. Salary and run. And the outer query will move on to the next row, and the subquery will execute for that row’s value of Emp1.Salary.
It will persist in anticipation of the “WHERE (1) = (… )” state is pleased.
28. When is the UPDATE_STATISTICS command used?
Answer: This command is used, ones the processing of large data is done.
When we delete a large number of files, alteration or reproduction takes place in the tables, to be concerned about these changes we need to restructure the indexes This is done UPDATE_STATISTICS.
29. What is a Subquery?
Answer: A Subquery is a SQL query within another query. It is a subset of a Select statement whose return values are used in filtering the conditions of the main query.
30. What are SQL constraints?
Answer: SQL constraints are the set of rules that impose some restriction while insertion, deletion or updation of data in the databases. In SQL we have both column level as well as table level constraints which are applied at columns and tables respectively. Some of constraints in SQL are – Primary Key, Foreign Key, Unique Key, Not NULL, DEFAULT, CHECK and Index constraint.
31. What are the different DCL commands in SQL?
Answer: DCL commands are used to create roles, grant permission and control access to the database objects.
GRANT: To provide user access
DENY: To deny permissions to users
REVOKE: To remove user access
32. What is a Primary Key?
Answer: A primary key is a column or a combination of columns which uniquely identifies a record in the database. A primary key can only have unique and not NULL values and there can be only one primary key in a table.
33. How to add code to the existing article (Using Improve Article)?
Answer: Recursive Practice Problems with Solutions
Run Levels in Linux
34. Explain Boyce and Codd Normal Form(BCNF)?
Answer: BCNF is the advanced or stricter version of 3NF.
For each functional dependency X -> Y-
X should be the super key
35. Explain the First Normal Form(1NF)?
Answer: According to First Normal Form, a column cannot have multiple values, each value in the columns must be atomic.
36. What are scalar functions in SQL?
Answer: Scalar functions are the functions that return a single value by processing a single value in SQL. Some of the widely used SQL functions are-
UCASE() – USed to convert a string to upper case
LCASE() – Used to convert a string to lower case
ROUND() – Used to round a number to the decimal places specified
NOW() – Used to fetch current system date and time
LEN() – Used to find the length of a string
SUBSTRING() or MID() – MID and SUBSTRING are synonyms in SQL. They are used to extract a substring from a string by specifying the start and end index. Syntax – SUBSTRING(ColumnName,startIndex,endIndex).
LOCATE() – Used to find the index of the character in a string. Syntax – LOCATE(character,ColumnName)
LTRIM() – Used to trim spaces from left
RTRIM() – Used to trim spaces from right
37. What is a coalesce function?
Answer: Coalesce function is used to return the first, not NULL value out of the multiple values or expressions passed to the coalesce function as parameters.Example-
COALESCE(NULL, NULL, 5, ‘ArtOfTesting’) will return the value 5.
COALESCE(NULL, NULL, NULL) will return NULL value as no not NULL value is encountered in the parameters list.
38. What are the different types of joins?
Types of Joins are as follows:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- OUTER JOIN
- View Complete Post
39. What do you mean by Subquery?
Answer: Query within another query is called as Subquery. A subquery is called inner query which returns output that is to be used by another query.
40. What is a Unique key?
Answer: Uniquely identifies a single row in the table.
Multiple values allowed per table.
Null values allowed.
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!
41. What do you mean by data integrity?
Answer: Data Integrity defines accuracy as well as the consistency of the data stored in a database. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.
42. Write a SQL query to display the current date?
Answer: In SQL, there is a built-in function called GetDate() which helps to return the current timestamp/date.
43. What is DBMS?
Answer: Database Management System is a collection of programs that enables a user to store, retrieve, update and delete information from a database.
44. What are the different DDL commands in SQL?
Answer: DDL commands are used to define or alter the structure of the database.
CREATE: To create databases and database objects
ALTER: To alter existing database objects
DROP: To drop databases and databases objects
TRUNCATE: To remove all records from a table but not its database structure
RENAME: To rename database objects
45. What are Constraints?
Answer: Constraints are used to specify the limit on the data type of the table. It can be specified while creating or altering the table statement.
The sample of constraints are:
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
46. Explain different types of index?
There are three types of index namely:
This index does not allow the field to have duplicate values if the column is unique indexed. If a primary key is defined, a unique index can be applied automatically.
This index reorders the physical order of the table and searches based on the basis of key values. Each table can only have one clustered index.
Non-Clustered Index does not alter the physical order of the table and maintains a logical order of the data. Each table can have many nonclustered indexes.
47. Explain different types of Normalization?
Answer: There are many successive levels of normalization. These are called normal forms. Each consecutive normal form depends on the previous one.
The first three normal forms are usually adequate.
First Normal Form (1NF) – No repeating groups within rows
Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.
Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.
48. What is ACID property in a database?
Answer: ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data transactions are processed reliably in a database system.
Atomicity: Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.
Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.
Isolation: The main goal of isolation is concurrency control.
Durability: Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.
49. What do you mean by “Trigger” in SQL?
Answer: Trigger in SQL is are a special type of stored procedures that are defined to execute automatically in place or after data modifications. It allows you to execute a batch of code when an insert, update or any other query is executed against a specific table.
50. What are the different operators available in SQL?
Answer: There are three operators available in SQL, namely:
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!