1. Please explain what is a NULL value?
A field with a NULL value is a field with no value. A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation. Assume, there is a field in a table is optional and it is possible to insert a record without adding a value to the optional field then the field will be saved with a NULL value.
2. Tell us how to Test for NULL Values?
A field with a NULL value is a field with no value. NULL value cannot be compared with other NULL values. Hence, It is not possible to test for NULL values with comparison operators, such as =, <, or <>. For this, we have to use the IS NULL and IS NOT NULL operators.
3. Tell me what are the advantages of Views?
Some of the advantages of Views are
☛ Views occupy no space
☛ Views are used to simply retrieve the results of complicated queries that need to be executed often.
☛ Views are used to restrict access to the database or to hide data complexity.
4. Tell me what is the difference between NULL value, Zero, and Blank space?
As I mentioned earlier, Null value is field with no value which is different from zero value and blank space.
Null value is a field with no value.
Zero is a number
Blank space is the value we provide. The ASCII value of space is CHAR(32).
5. Explain me what is the difference between Rename and Alias?
‘Rename' is a permanent name given to a table or column
‘Alias' is a temporary name given to a table or column.
6. Tell me can a table contain multiple PRIMARY KEY's?
The short answer is no, a table is not allowed to contain multiple primary keys but it allows to have one composite primary key consisting of two or more columns.
An index is used to speed up the performance of queries. It makes faster retrieval of data from the table. The index can be created on one column or a group of columns.
8. Can you tell me the ACID properties and explain?
Following are the four properties of ACID. These guarantees that the database transactions are processed reliably.
☛ Atomicity
☛ Consistency
☛ Isolation
☛ Durability
9. Tell us what are the different DML commands in SQL?
DML commands are used for managing data present in the database.
☛ SELECT: To select specific data from a database
☛ INSERT: To insert new records into a table
☛ UPDATE: To update existing records
☛ DELETE: To delete existing records from a table
10. Explain me what is Stored procedure?
A Stored Procedure is a collection of SQL statements that have been created and stored in the database to perform a particular task. The stored procedure accepts input parameters and processes them and returns a single value such as a number or text value or a result set (set of rows).
11. Tell me how to avoid duplicate records in a query?
The SQL SELECT DISTINCT query is used to return only unique values. It eliminates all the duplicated values.
12. Please explain what is DBMS?
Database Management System is a collection of programs that enables a user to store, retrieve, update and delete information from a database.
13. Do you know what are the constraints available in SQL?
Some of the constraints in SQL are – Primary Key, Foreign Key, Unique Key, SQL Not Null, Default, Check and Index constraint.
14. What are the steps needed to Create the scheduled job?
Steps to create a Scheduled Job :
☛ Connect to the database of SQL server in SQL Server Management Studio. On the SQL Server Agent, we will find a Jobs folder.
☛ Right click on jobs and choose Add New.
☛ A New Job window will come into view. Give an associated name for the same.
☛ Click next on the “Steps” in the left list of options. An SQL job can have multiple steps either in the form of SQL declaration or a stored practice call.
☛ Click on the “Schedules” in the left list of options. An SQL job can comprise of one or supplementary schedules. It is basically the instance at which SQL job will jog itself. We can spell out returning schedules also.
15. Please explain what are string functions in SQL?
SQL string functions are used primarily for string manipulation. Some of the widely used SQL string functions are
☛ LEN() – It returns the length of the value in a text field
☛ LOWER() – It converts character data to lower case
☛ UPPER() – It converts character data to upper case
☛ SUBSTRING() – It extracts characters from a text field
☛ LTRIM() – It is to remove all whitespace from the beginning of the string
☛ RTRIM() – It is to remove all whitespace at the end of the string
☛ CONCAT() – Concatenate function combines multiple character strings together
☛ REPLACE() – To update the content of a string.
Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query:
SELECT * FROM sys.dm_os_wait_stats;
Another example is examining current sessions, much like the sp_who2 command:
SELECT * FROM sys.dm_exec_sessions;
17. Explain me what is a column in a Table?
A column is a vertical entity in a table that contains all information associated with a specific field in a table.
18. Tell me what are Operators available in SQL?
SQL Operator is a reserved word used primarily in an SQL statement's WHERE clause to perform operations, such as arithmetic operations and comparisons. These are used to specify conditions in an SQL statement.
There are three types of Operators.
☛ Arithmetic Operators
☛ Comparison Operators
☛ Logical Operators
SQL Overview: SQL stands for Structured Query Language. It is an American National Standard Institute (ANSI) standard. It is a standard language for accessing and manipulating databases. Using SQL, some of the action we could do are to create databases, tables, stored procedures (SP's), execute queries, retrieve, insert, update, delete data against a database.
20. Tell us what is the difference between an inner and outer join?
An inner join returns rows when there is at least some matching data between two (or more) tables that are being compared.
An outer join returns rows from both tables that include the records that are unmatched from one or both the tables.
21. Tell us what is a Trigger?
A Trigger is a SQL procedure that initiates an action in response to an event (Insert, Delete or Update) occurs. When a new Employee is added to an Employee_Details table, new records will be created in the relevant tables such as Employee_Payroll, Employee_Time_Sheet etc.,
22. Tell me referential integrity and where it can be enforced?
If they stumble on the question, circle back to the Orders and OrderDetails tables we used as examples earlier. What's an orphan? How do we make sure that we don't end up with OrderDetails for records with no matching Order record? Where are all the places we could enforce referential integrity? (Think foreign keys, triggers, the application, or not at all.) Have you worked in places where there was no referential integrity, and what problems did you run into?
23. Explain me what is a Primary Key?
A PRIMARY KEY constraint uniquely identifies each record in a database table. All columns participating in a primary key constraint must not contain NULL values.
24. Tell us what are the different types of SQL commands?
SQL commands are segregated into following types:
☛ DDL – Data Definition Language
☛ DML – Data Manipulation Language
☛ DQL – Data Query Language
☛ DCL – Data Control Language
☛ TCL – Transaction Control Language
25. Tell me why DBAs don't like cursors?
I like to phrase this interview question this way because I'm not saying the DBA is right – I'm just asking the developer to explain the DBA's point of view. I don't have a problem with the developer rolling their eyes as they explain the answer, but I have a problem with the developer being surprised by the question.
The candidate gets bonus points if they seem even vaguely aware of the terms “set-based processing” and “row-based processing”, but that's purely a bonus. (I wish I could say that these concepts are requirements, but in today's economic market, companies don't always want to pay top dollar to get the best candidates.)
26. Explain me what is the difference between Union and Union All command?
This is one of the tricky SQL Interview Questions. Interviewer may ask you this question in another way as what are the advantages of Union All over Union.
Both Union and Union All concatenate the result of two tables but the way these two queries handle duplicates are different.
Union: It omits duplicate records and returns only distinct result set of two or more select statements.
Union All: It returns all the rows including duplicates in the result set of different select statements.
Performance wise Union All is faster than Union, Since Union All doesn't remove duplicates. Union query checks the duplicate values which consumes some time to remove the duplicate records.
Assume: Table1 has 10 records, Table2 has 10 records. Last record from both the tables are same.
If you run Union query.
SELECT * FROM Table1
UNION
SELECT * FROM Table2
Output: Total 19 records
If you run Union query.
SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
Output: Total 20 records
Data type of all the columns in the two tables should be same.
27. Tell me what is a Record in a Database?
A record (also called a row of data) is an ordered set of related data in a table.
28. Explain me how are transactions used?
Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.
30. Tell me what is SQL NOT NULL constraint?
NOT NULL constraint is used to ensure that the value in the filed cannot be a NULL
31. Tell us what is a Field in a Database?
A field in a Database table is a space allocated to store a particular record within a table.
32. Tell us what are Constraints?
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
☛ CHECK
☛ DEFAULT
☛ UNIQUE
☛ PRIMARY KEY
☛ FOREIGN KEY
33. Tell me what is Normalization?
Normalization is the process of table design to minimize the data redundancy. There are different types of Noramalization forms in SQL.
☛ First Normal Form
☛ Second Normal Form
☛ Third Normal Form
☛ Boyce and Codd Normal Form
34. Tell us what is a Unique constraint?
A unique constraint is used to ensure that there are no duplication values in the field/column.
35. Do you know what is a Join?
Join is a query, which retrieves related columns or rows from multiple tables.
