close

Interview Questions for SQL Developers: Common SQL Interview Q&A - Part 4

Explain the concept of creating function and procedure in SQL and the benefits of using them.

Answer: Function and procedure in SQL are named code blocks that can be called from other queries or applications.

  • Function: Returns a value and is often used for calculations and returning results.
  • Procedure: Does not return a value and is used to perform data processing or storage tasks.

The benefits of using functions and procedures include:

  • Reducing code duplication, making it easier to maintain and manage code.
  • Increasing reusability, allowing code to be reused in multiple places.
  • Improving performance, as functions and procedures are often compiled once and reused multiple times.

 

How to use RECURSIVE queries and COMMON TABLE EXPRESSION (CTE) in SQL.

Answer: RECURSIVE queries and COMMON TABLE EXPRESSION (CTE) are used to handle recursive queries and reuse a part of a query in SQL.

  • RECURSIVE: Allows performing recursive queries in the database.
  • CTE: Acts as a temporary result set, breaking a query into smaller, more manageable parts.
WITH RECURSIVE RecursiveCTE (ID, ParentID, Level) AS (
  SELECT ID, ParentID, 0 AS Level FROM Categories WHERE ParentID IS NULL
  UNION ALL
  SELECT C.ID, C.ParentID, Level + 1 FROM Categories C
  INNER JOIN RecursiveCTE RC ON C.ParentID = RC.ID
)
SELECT * FROM RecursiveCTE;

 

How to handle duplicate data and invalid data cases in SQ

Answer: To handle duplicate and invalid data in SQL, we can use SQL statements like DISTINCT, GROUP BY, HAVING, and UNIQUE constraints to ensure data uniqueness. Additionally, we can use UPDATE or DELETE statements to eliminate duplicate or invalid records.

 

Here's the translation of the special data types in SQL Server

Các kiểu dữ liệu đặc biệt như XML, GEOGRAPHY, và GEOMETRY trong SQL Server được sử dụng để lưu trữ và làm việc với dữ liệu đặc thù và phức tạp. Dưới đây là mô tả về từng kiểu dữ liệu này:

XML:

  • The XML data type in SQL Server allows storing and working with data in Extensible Markup Language format.
  • XML data can contain rich structures, enabling the storage of well-organized and flexible information.
  • SQL Server provides functions and methods to manipulate XML data, allowing for querying, creating, and transforming XML data.

GEOGRAPHY And GEOMETRY:

  • The GEOGRAPHY and GEOMETRY data types in SQL Server are used to store geographic and geometric information.
  • GEOGRAPHY is used to represent geographic objects such as points, lines, regions, and polygons on the earth's surface.
  • GEOMETRY is used to represent geometric objects such as points, lines, regions, and polygons in a flat space.

Both data types support special operations and functions for querying and analyzing geographic and geometric data.

 

Explain functions and functions for handling date and time data in SQL

Functions and features for processing date and time data in SQL are used to manipulate and perform tasks related to dates and times in the database. Here is a detailed description of some common functions and features:

DATEPART():This function is used to extract a specific component (e.g., day, month, year, hour, minute, second) from a date or time value.

SELECT DATEPART(YEAR, '2023-07-19'); -- Result: 2023

DATEDIFF(): This function calculates the time difference between two date or time values.

SELECT DATEDIFF(DAY, '2023-07-01', '2023-07-19'); -- Result: 18 (number of days between two dates)

DATEADD(): This function adds a certain number of days, months, years, or time to a date or time value.

SELECT DATEADD(DAY, 7, '2023-07-19'); -- Result: '2023-07-26' (adding 7 days)

GETDATE(): This function returns the current date and time of the system.

SELECT GETDATE(); -- Result: '2023-07-19 12:34:56.789'

CONVERT(): This function is used to convert date or time values from one format to another.

SELECT CONVERT(VARCHAR, '2023-07-19', 103); -- Result: '19/07/2023'

FORMAT(): This function is used to format date or time values according to a predefined pattern.

SELECT FORMAT('2023-07-19', 'dd/MM/yyyy'); -- Result: '19/07/2023'