WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!

SQL Interview Questions And Answers

SQL Interview Questions And Answers

1. When is the use of the UPDATE_STATISTICS command recommended? 🔄

  • The UPDATE_STATISTICS command is typically used when there’s been a large processing of data, such as extensive deletions, modifications, or bulk copying into tables. This command helps update the indexes to reflect these changes.

2. What types of joins are possible in SQL Server? 🚀

  • In SQL Server, the types of joins include INNER JOINs, OUTER JOINs (which are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS, and FULL OUTER JOINS), and CROSS JOINs.

3. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? 🤔

  • The HAVING CLAUSE is used for specifying a search condition for a group or an aggregate, typically in conjunction with the GROUP BY clause. In the absence of GROUP BY, it acts like a WHERE clause. The WHERE CLAUSE, on the other hand, is applied to each row before they become part of the GROUP BY function in a query.

4. What is a sub-query and its properties? 🔍

  • A sub-query, also known as a sub-select, is a SELECT statement nested within another SQL statement. It’s executed within parentheses and is used to return a single row as an atomic value or multiple rows using the IN keyword. Key properties include being enclosed in parentheses, placed on the right side of a comparison operator, and not containing an ORDER-BY clause. A sub-query can appear in various places like the SELECT statement column list, FROM, GROUP BY, HAVING, and ORDER BY clauses.

5. What are the types of sub-queries? 🌟

  • Sub-queries can be classified as single-row subqueries (returning only one row), multiple-row subqueries (returning multiple rows), and multiple-column subqueries (returning multiple columns).

6. What is SQL Profiler? 📊

  • SQL Profiler is a graphical tool for monitoring events in Microsoft SQL Server. It allows administrators to capture and analyze data about events, which is useful for identifying performance issues. Filters can be applied to reduce the size of the monitored data.

7. What are User Defined Functions in SQL Server? 🛠️

  • User-Defined Functions in SQL Server are custom functions that accept parameters and return either a scalar value or a table data type. They enable more flexibility in SQL queries and data manipulation.

8. What kinds of User-Defined Functions can be created in SQL Server 2000? 📈

  • There are three types: Scalar (returning a scalar data type), Inline Table-Valued (returning a table data type, similar to a parameterized view), and Multi-statement Table-Valued (also returning a table but with support for multiple T-SQL statements).

9. Which TCP/IP port does SQL Server run on, and how can it be changed? 🔌

  • SQL Server typically runs on port 1433. This can be changed in the Network Utility TCP/IP properties for both client and server.

10. What are the authentication modes in SQL Server, and how can they be changed? 🔐

  • SQL Server has two authentication modes: Windows mode and mixed mode (SQL & Windows). The mode can be changed in SQL Enterprise Manager under SQL Server Configuration Properties.

11. Where are SQL Server usernames and passwords stored? 🗄️

  • Usernames and passwords are stored in the master database, specifically in the sysxlogins table.

12. Which command in Query Analyzer gives you the version of SQL server and operating system? 💻

  • The command is: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY('edition').

13. What is SQL Server agent? 🕵️‍♂️

  • SQL Server Agent is a key component for managing SQL Server. It is used for scheduling tasks and scripts, making the life of a database administrator easier.

14. Can a stored procedure call itself or be recursive? How many levels of SP nesting are possible? 🔄

  • Yes, stored procedures can be recursive, meaning they can call themselves. Up to 32 levels of nesting are possible.

15. What is @@ERROR?

  • @@ERROR is an automatic variable in SQL Server that returns the error code of the last Transact-SQL statement executed. If there is no error, it returns zero. To use it further in the process, its value should be saved to a variable immediately after the statement.

16. What is RDBMS? 💾

  • Relational Database Management Systems (RDBMS) maintain data records and indices in tables. They allow the creation and maintenance of relationships across different data and tables using tables, where relationships are expressed by data values rather than pointers, ensuring a high degree of data independence.

17. What is normalization in databases? 📐

  • Database normalization is the process of organizing data in a database to minimize redundancy. It typically involves dividing a database into multiple tables and defining relationships between them, with the goal of isolating data so that changes in one field are made in just one table and then propagated through the rest.

18. What are the different normalization forms? 📋

  • The different forms of normalization are:
    • 1NF (First Normal Form): Eliminate repeating groups.
    • 2NF (Second Normal Form): Eliminate redundant data.
    • 3NF (Third Normal Form): Eliminate columns not dependent on the primary key.
    • BCNF (Boyce-Codd Normal Form): Handle certain types of dependency anomalies.
    • 4NF (Fourth Normal Form): Isolate independent multiple relationships.
    • 5NF (Fifth Normal Form): Isolate semantically related multiple relationships.
    • ONF (Optimal Normal Form): Limited to simple (elemental) facts.
    • DKNF (Domain-Key Normal Form): Free from all modification anomalies.

19. What is a Stored Procedure? 🔄

  • A stored procedure is a pre-written SQL script saved in the database that performs operations in the database. They accept input parameters and can return values. They reduce network traffic, improve performance, and ensure database integrity.

20. What is a Trigger in SQL? 🎯

  • A trigger is an SQL procedure that initiates an action in response to an event (INSERT, DELETE, or UPDATE). Triggers maintain data integrity, can’t be directly executed, and can activate stored procedures.

21. What is a View in SQL? 👀

  • A view is a subset of a table and can be used for data retrieval, updating, or deleting. Views reflect the current data in the table they are derived from, and changes in the view affect the source table.

22. What is an Index in SQL? 🔍

  • An index is a database structure that speeds up data retrieval from a table by creating pointers to the data. There are two types: clustered indexes (reorganizing the physical order of the table) and non-clustered indexes (external to the table).

23. What is the difference between clustered and non-clustered indexes? 📈

  • A clustered index sorts and stores the data rows of the table in order based on the index key. A non-clustered index, however, creates a separate structure to maintain data pointers to the original table rows.

24. What are the different index configurations a table can have? ⚙️

  • A table’s index configurations can include no indexes, a clustered index, a clustered index with many non-clustered indexes, a non-clustered index, or many non-clustered indexes.

25. What are cursors in SQL? 🖱️

  • Cursors are objects used to manipulate data in a set on a row-by-row basis. They involve several steps: declare, open, fetch, process, close, and deallocate the cursor.

26. What is the use of DBCC commands? 🛠️

  • DBCC (Database Consistency Checker) commands are used to ensure the integrity of databases, including maintenance, validation tasks, and status checks, such as DBCC CHECKDB, CHECKALLOC, and CHECKFILEGROUP.

27. What is a Linked Server in SQL? 🔗

  • Linked Servers in SQL Server allow connection and querying of other SQL Server databases. They facilitate easy and clean SQL statements for remote data retrieval and manipulation.

28. What is Collation in SQL? 📜

  • Collation refers to the set of rules that determine how data is sorted and compared, particularly in terms of character sequence, case-sensitivity, accent marks, and character width.

29. What are different types of Collation Sensitivity? 🔠

  • Collation sensitivity types include case sensitivity, accent sensitivity, Kana sensitivity (for Japanese characters), and width sensitivity (difference in single-byte and double-byte characters).

30. What’s the difference between a primary key and a unique key? 🔑

  • Both ensure uniqueness, but a primary key creates a clustered index and doesn’t allow NULLs, whereas a unique key creates a non-clustered index and allows one NULL.

31. How to implement one-to-one, one-to-many, and many-to-many relationships in table design? 🔄

  • One-to-One relationships can be implemented as a single table or two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary and foreign key relationships. Many-to-Many relationships use a junction table with composite primary keys.

32. What is NOLOCK? 🚫🔒

  • NOLOCK is a query optimizer hint that allows dirty reads by not taking locks when reading data. It improves concurrency but risks reading data that might be in the process of being updated.

33. What is the difference between DELETE and TRUNCATE commands? 🗑️

  • DELETE removes rows based on a condition and logs each deletion. TRUNCATE removes all rows by deallocating data pages, is faster, does not log each deletion, and cannot be used on tables referenced by foreign key constraints.

34. What’s the difference between a Function and Stored Procedure? 🤔

  • User-Defined Functions (UDFs) can be used in SQL statements and can return tables, allowing them to be used in joins. Stored procedures are not event-driven and can’t be used in the same way as UDFs in SQL statements.

Here you will find answers to questions on everything from the features you can expect, all of which makes up part of the PLSQL training from Asha24.


Question and Answer

Master data management (MDM) is a comprehensive method of enabling an enterprise to link all of its critical data to one file, called a master file, that provides a common point of reference. When properly done, MDM streamlines data sharing among personnel and departments.

  • There are two types of table involved in Dimensional Modeling and this model concept is different from the third normal form. Dimensional data model concept makes use of facts table containing the measurements of the business and dimension table containing the measurement context.

A data movement mode determines how the power center server handles the character data. We choose the data movement in the Informatica server configuration settings. Two types of data movement modes available in Informatica.

It’s a matter of awareness and the problem becoming urgent. We are seeing budgets increased and greater success in closing deals, particularly in the Pharmaceutical and Financial services industries. Forrester predicts MDM will be $6 billion markets by 2010, which is a 60 percent growth rate over the $1 billion MDM market last year. Gartner forecasted that 70 percent of Global 2000 companies will have an MDM solution by the year 2010. These are pretty big numbers

We can export repository and import into the new environment
We can use Informatica deployment groups
We can Copy folders/objects
We can Export each mapping to XML and import in a new environment

It is a repository object that helps in generating, modifying or passing data. In a mapping, transformations make a representation of the operations integrated with service performs on the data. All the data goes by transformation ports that are only linked with maple or mapping.

Foreign keys of dimension tables are the primary keys of entity tables.
Foreign keys of facts tables are the primary keys of dimension tables.

A Mapplet is a reusable object that contains a set of transformations and enables to reuse that transformation logic in multiple mappings.

There are two different ways to load data in dimension tables.
Conventional (Slow) – All the constraints and keys are validated against the data before, it is loaded; this way data integrity is maintained.
Direct (Fast) – All the constraints and keys are disabled before the data is loaded. Once data is loaded, it is validated against all the constraints and keys. If data is found invalid or dirty it is not included in the index and all future processes are skipped on this data. 

Designed by Informatica Corporation, it is data integration software providing an environment that lets data loading into a centralized location like a data warehouse. From here, data can be easily extracted from an array of sources, also can be transformed as per the business logic and then can be easily loaded into files as well as relation targets.

Connect with CloudCertification