TCS NQT SQL Coding

Imagine building the foundation of an enterprise human resources system. You need a data structure that uniquely tracks team members while safeguarding data types. To achieve this, execute a CREATE TABLE statement defining the foundational attributes. You must declare Employee_ID as the PRIMARY KEY to prevent duplicate identity tracking, pair the alphanumeric descriptive text columns with standard VARCHAR constraints, and assign an appropriate numeric type like INT or DECIMAL to hold financial compensation values. Example: CREATE TABLE Employee ( Employee_ID INT PRIMARY KEY, Name VARCHAR(100) NOT NULL, Department VARCHAR(50), Salary DECIMAL(10, 2) ); Interview Tip: Mentioning NOT NULL constraints on critical data points like the name demonstrates production-grade schema habits to an interviewer.

Imagine onboarding your first batch of workers into the newly created system database and needing to map their personal profiles directly into the relational columns. You can populate the schema by running an INSERT INTO command. It is best practice to explicitly state the column layout order directly before the VALUES block, ensuring that even if the structural order of the table changes down the line, your script remains entirely safe from type mismatch crashes. Example: INSERT INTO Employee (Employee_ID, Name, Department, Salary) VALUES (1, 'Amit Sharma', 'IT', 60000.00), (2, 'Priya Patel', 'HR', 45000.00), (3, 'Rohan Das', 'IT', 58000.00);

Imagine expanding your system architecture to normalize corporate entities, replacing repeating department text fields with a dedicated primary reference table. Use a CREATE TABLE command to spin up an independent Department table. Establish a Department_ID primary key column alongside a Department_Name string field. This creates a clean structural reference point that other operational tables can link to using foreign keys. Example: CREATE TABLE Department ( Department_ID INT PRIMARY KEY, Department_Name VARCHAR(50) NOT NULL );

Imagine creating the foundational department entries that define your organizational categories before linking your staff records to them. You will use the INSERT INTO statement to load your master data records. List the key fields explicitly, followed by the individual department labels that your company recognizes. Example: INSERT INTO Department (Department_ID, Department_Name) VALUES (101, 'IT'), (102, 'HR'), (103, 'Finance');

Imagine pulling a quick raw dump of your corporate database to check that your system initialization scripts successfully committed all worker columns. To pull every individual column and row out of the target table, execute a fast SELECT * statement. While this wildcard shorthand is perfect for quick manual debugging checks, you should avoid embedding it inside production backend code blocks to keep query footprints small. Example: SELECT * FROM Employee;

Imagine designing a public corporate directory widget where you need to display staff names and their team assignments while keeping sensitive data hidden. Instead of pulling the full data block with a wildcard, explicitly name the columns you want inside your SELECT clause. This minimizes unnecessary network transfer sizes and prevents application memory bloat. Example: SELECT Name, Department FROM Employee;

Imagine an IT systems administrator who needs a quick list of all technology personnel to organize a software deployment patch window. You can filter down your dataset by adding a WHERE clause that checks the value of the department column against your target string literal. Ensure your search terms match the target data precisely to capture the right rows. Example: SELECT * FROM Employee WHERE Department = 'IT';

Imagine a compensation analyst who wants to review high-earning staff profiles to ensure compensation plans line up across different roles. To filter your dataset based on a numeric range, use the standard greater-than operational sign inside your WHERE filter, passing the numeric threshold without any formatting commas or currency symbols. Example: SELECT * FROM Employee WHERE Salary > 55000;

Imagine looking for mid-level salary records to assess budget impacts for a specific compensation bracket. You can isolate values within a range by leveraging the BETWEEN operator in your filter predicate. Keep in mind that the BETWEEN operator is fully inclusive, meaning it will grab records that match the exact boundary limits as well. Example: SELECT * FROM Employee WHERE Salary BETWEEN 50000 AND 60000; Interview Tip: You can also write this logic using >= and <= operators, but the BETWEEN syntax is cleaner and highly readable.

Imagine trying to spot team members who earn more than the typical corporate average across the company. Because SQL prevents you from mixing aggregate calculations directly inside a standard WHERE filter condition, you must deploy a subquery. The inner query computes the baseline average salary across the company, and the outer query evaluates each employee row against that calculation. Example: SELECT * FROM Employee WHERE Salary > (SELECT AVG(Salary) FROM Employee);

Imagine compiling a feature report for executive management highlighting the single top earner in the organization. To handle cases where multiple employees might be tied for the absolute highest pay, use a subquery that finds the maximum salary value. The outer query then pulls all matching employee profiles that meet that metric. Example: SELECT * FROM Employee WHERE Salary = (SELECT MAX(Salary) FROM Employee);

Imagine a compensation review where management wants to check the second-highest compensation bracket without manually filtering the data. You can find this value by selecting the maximum salary that is strictly less than the absolute maximum salary in the table. This subquery approach works reliably across different database platforms without relying on vendor-specific syntax extensions. Example: SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee);

Imagine an HR executive analyzing compensation ceilings across different departments to ensure equitable salary caps. You can group your records by department name and apply the MAX aggregate function to calculate the highest salary within each unique cluster. Example: SELECT Department, MAX(Salary) as Max_Salary FROM Employee GROUP BY Department;

Imagine draft budgeting for next year's operational expenses where you need to calculate the standard average employee cost across the entire business. Use the built-in AVG aggregate function within your selection statement to process all active compensation entries into a single average value. Example: SELECT AVG(Salary) as Average_Salary FROM Employee;

Imagine an executive board checking which internal department has the largest total salary footprint on the company balance sheet. To find this, group your data by department, sum up the total salaries for each group, order the final output in descending order, and use your engine's row limiter to grab only the top record. Example: SELECT Department, SUM(Salary) as Total_Payout FROM Employee GROUP BY Department ORDER BY Total_Payout DESC LIMIT 1;

Imagine completing an end-of-quarter census report where you need to confirm the exact total headcount currently active in the database. Use the COUNT(*) aggregate expression to read all matching records across the entire dataset, which will give you your total headcount. Example: SELECT COUNT(*) as Total_Employees FROM Employee;

Imagine auditing resource allocation across your organization to see how team members are distributed between different teams. Group your data rows by the department field, and use the COUNT(*) function to get a clean breakdown of the headcount within each department group. Example: SELECT Department, COUNT(*) as Headcount FROM Employee GROUP BY Department;

Imagine an operations manager who wants to filter out single-person teams and look only at larger departments that have multiple staff members assigned. Because standard WHERE clauses filter individual data rows before any groupings happen, you need to use a HAVING clause to filter aggregate calculations after the data has been grouped by department. Example: SELECT Department, COUNT(*) as Headcount FROM Employee GROUP BY Department HAVING COUNT(*) > 1;

Imagine auditing an employee database to see if any names appear multiple times, which could point to data entry errors or duplicate onboarding records. Group your records by the name column, then use a HAVING COUNT(*) greater than one condition to highlight entries that show up more than once. Example: SELECT Name, COUNT(*) FROM Employee GROUP BY Name HAVING COUNT(*) > 1;

Imagine checking your structural logging records to ensure that individual department labels haven't been duplicated across different primary database rows. Group the dataset entries by their official department string values, and use the HAVING filter to catch any instances where the exact same department label appears more than once. Example: SELECT Department, COUNT(*) FROM Employee GROUP BY Department HAVING COUNT(*) > 1;

Imagine building a clean, uncluttered drop-down menu for a frontend interface that shows all the unique departments currently available in the company. Use the DISTINCT keyword directly inside your select query. This strips out any recurring values and leaves you with a clean list of unique department names. Example: SELECT DISTINCT Department FROM Employee;

Imagine combining a historic legacy database with a modern department control master list to generate a complete master directory of all unique department names. You can combine these datasets using the UNION operator. The UNION operator automatically removes duplicate values across both tables, giving you a clean, unified list of unique names. Example: SELECT Department FROM Employee UNION SELECT Department_Name FROM Department; Interview Tip: If you want to keep all duplicate values for auditing purposes, use UNION ALL instead, as it avoids the extra deduplication processing step.

Imagine an HR director compiling a compensation overview where the highest-paid team members need to appear at the very top of the document. You can sort your query results by using the ORDER BY clause on the target column, along with the DESC keyword to order the data from highest value to lowest. Example: SELECT * FROM Employee ORDER BY Salary DESC;

Imagine structuring an internal audit report where you need to organize your data alphabetically by department first, and then list the highest earners within each department at the top of their group. You can pass multiple columns into your ORDER BY clause, separated by commas. The engine sorts the rows by the first column parameter, and then applies the secondary sorting rule to any matching rows. Example: SELECT * FROM Employee ORDER BY Department ASC, Salary DESC;

Imagine a recognition program where you need to quickly identify and pull profiles for the top three highest-paid employees in the company. Sort the database records by salary in descending order, and append a row-limiting keyword like LIMIT or TOP depending on your SQL dialect to keep only the first three records. Example: SELECT * FROM Employee ORDER BY Salary DESC LIMIT 3;

Imagine building a comprehensive staff report that pulls the employee's name from one table and matches it with their corresponding department name from another table. You can link these related tables by using an INNER JOIN statement, which matches the rows based on a shared department identifier column present in both tables. Example: SELECT e.Name, d.Department_Name FROM Employee e INNER JOIN Department d ON e.Department_ID = d.Department_ID;

Imagine reviewing your personnel files to find any contractors or new hires who haven't been assigned to an official department yet. You can find these unassigned records by using a LEFT JOIN to look up department relationships, and adding a WHERE clause that catches instances where the joined table's identifier column comes up completely NULL. Example: SELECT e.Name FROM Employee e LEFT JOIN Department d ON e.Department_ID = d.Department_ID WHERE d.Department_ID IS NULL;

Imagine an operational review where you need to spot empty departments that don't have any employees assigned to them so you can clean up the system. Start your query from the master Department table, LEFT JOIN it over to the Employee table, and filter for rows where the employee identifier evaluates to NULL to find the empty groups. Example: SELECT d.Department_Name FROM Department d LEFT JOIN Employee e ON d.Department_ID = e.Department_ID WHERE e.Employee_ID IS NULL;

Imagine auditing your compensation records to flag any unusual instances where an employee's salary is actually higher than the salary of their direct manager. You can solve this by performing a self-join, which treats your single Employee table as two distinct logical entities—one representing the staff member and the other representing the manager—and then comparing their salary values directly. Example: SELECT e.Name as Employee_Name, e.Salary as Emp_Salary, m.Name as Manager_Name, m.Salary as Mgr_Salary FROM Employee e JOIN Employee m ON e.Manager_ID = m.Employee_ID WHERE e.Salary > m.Salary;

Imagine generating a clean company hierarchy directory that shows every individual employee's name right next to the name of their direct manager. Use a self-join by linking the table back to itself. Map the employee's manager ID column directly to the primary employee ID column of the second logical copy of the table. Example: SELECT e.Name as Employee, m.Name as Manager FROM Employee e LEFT JOIN Employee m ON e.Manager_ID = m.Employee_ID;

Imagine a departmental payroll analysis where you need to assign a clear rank to each employee based on their earnings inside their specific team. You can use the RANK window function combined with a PARTITION BY clause. This breaks your data down by department and computes the ranks within each separate group without collapsing your rows. Example: SELECT Name, Department, Salary, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) as Salary_Rank FROM Employee;

Imagine tracking departmental budget spending over time and needing to show a running, cumulative total of salary costs as you add up each individual employee's pay. Use the SUM window function alongside a PARTITION BY clause to group by department, and include an ORDER BY statement to calculate the running total smoothly across the rows. Example: SELECT Department, Name, Salary, SUM(Salary) OVER (PARTITION BY Department ORDER BY Employee_ID) as Cumulative_Salary FROM Employee;

Imagine creating a summary report for management that extracts the complete profile of the single highest-paid individual inside every individual department. Wrap a window function like ROW_NUMBER or DENSE_RANK inside a CTE, partition the data by department, order it by salary descending, and then query the outer layer for rows where the rank evaluates to one. Example: WITH TopEarners AS ( SELECT Name, Department, Salary, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) as rn FROM Employee ) SELECT Name, Department, Salary FROM TopEarners WHERE rn = 1;

Imagine an HR manager pulling a list of recent hires from the last six months to schedule their upcoming performance reviews. You can filter your date fields by comparing your onboarding date column with the current system date minus a six-month time interval parameter. Example: SELECT Name, Join_Date FROM Employee WHERE Join_Date >= CURRENT_DATE - INTERVAL '6 month';

Imagine a targeted marketing campaign where you want to find registered users who haven't made a single purchase yet so you can send them a welcome discount code. Run a LEFT JOIN from your primary customer database over to your sales transaction log, and use a WHERE filter to isolate the profiles where the sales records show up completely NULL. Example: SELECT c.Customer_ID, c.Customer_Name FROM Customers c LEFT JOIN Sales s ON c.Customer_ID = s.Customer_ID WHERE s.Sale_ID IS NULL;

Imagine a lifecycle marketing review where you want to check when each customer made their very first purchase and their most recent transaction. Group your sales records by customer identifier, and apply both the MIN and MAX aggregate operations to capture the two boundary dates. Example: SELECT Customer_ID, MIN(Purchase_Date) as First_Purchase, MAX(Purchase_Date) as Latest_Purchase FROM Sales GROUP BY Customer_ID;

Imagine an internal directory audit where you want to pull all employee names that begin with the letter A to organize a grouped review list. Use the LIKE operator along with a wildcard percentage symbol placed after the letter to find strings that start with that character. Example: SELECT Name FROM Employee WHERE Name LIKE 'A%';

Imagine running a text pattern match across your employee records to find names that end with a specific character suffix. Place the wildcard percentage symbol directly before your search character inside a LIKE filter predicate to look for matches at the end of the string. Example: SELECT Name FROM Employee WHERE Name LIKE '%e';

Imagine a flexible keyword search bar where a user types in a text fragment, and the system needs to find any names that contain that string anywhere inside them. Surround your search character or text fragment with percentage wildcards on both sides within a LIKE clause to pull any matching rows. Example: SELECT Name FROM Employee WHERE Name LIKE '%a%';

Imagine formatting a payroll report for human presentation where numeric currency figures need to be explicitly displayed with a clear localized currency prefix. You can format the text output by using a string concatenation function to prefix the raw numeric data value with the appropriate currency symbol. Example: SELECT Name, CONCAT('₹', CAST(Salary AS CHAR)) as Formatted_Salary FROM Employee;

Imagine an internal quality control check where you need to pull one random employee profile from the database to run an unbiased security access audit. You can randomize your row sorting by passing a dynamic ordering command like RAND() or NEWID() to your ORDER BY clause, and pairing it with a row limiter to grab a single row. Example (MySQL flavor): SELECT * FROM Employee ORDER BY RAND() LIMIT 1;

Imagine generating an analytical projection report where you need to simulate swapping two status values on the fly without making permanent changes to the underlying data on disk. You can handle this conditional swap directly within your SELECT statement by using a CASE expression to evaluate and replace the values dynamically as the rows are read. Example: SELECT Name, Shift, CASE WHEN Shift = 'A' THEN 'B' WHEN Shift = 'B' THEN 'A' ELSE Shift END as Simulated_Shift FROM Employee;

Imagine taking a quick snapshot copy of your production table before testing a heavy batch data modification script, so you have an easy recovery point. You can copy both the table structure and all underlying data in one step by using a SELECT INTO statement or a CREATE TABLE AS query structure. Example: CREATE TABLE Employee_Clone AS SELECT * FROM Employee;

Imagine needing to deploy an identical, empty staging table to test your data pipeline tools without copying over any actual live user records. You can copy just the schema design by running a conditional clone query with a filtering rule that always evaluates to false, like WHERE 1=0. This sets up the structural headers without pulling in any rows. Example: CREATE TABLE Empty_Staging_Employee AS SELECT * FROM Employee WHERE 1 = 0;

Imagine a combinatorial system attempting to map out every possible pairing between a products table and an locations table. Because a CROSS JOIN calculates a Cartesian product by multiplying the rows of the first table by the rows of the second table ($M \times N$), if either table contains zero rows, the equation resolves to zero ($M \times 0 = 0$), resulting in an empty output set. Interview Tip: Explain that a Cartesian product requires valid pairs from both sides. If one side has no elements, no pairs can be formed, yielding zero records.

Imagine generating a fast sequential index array up to one hundred entirely within a declarative SQL environment without using procedural loop scripts. You can build this sequence cleanly by using a recursive Common Table Expression. Establish a baseline anchor value of one, and write a recursive step that adds increments of one until your terminating condition hits one hundred. Example: WITH RECURSIVE Numbers AS ( SELECT 1 as n UNION ALL SELECT n + 1 FROM Numbers WHERE n &lt; 100 ) SELECT n FROM Numbers;

Imagine writing a procedural database script where you need to explicitly control an execution counter using a traditional iterative loop control structure. Within a procedural extension block like T-SQL, declare an integer counter variable, set up a WHILE loop block, and increment your counter inside the loop body. Example: DECLARE @Counter INT = 1; WHILE @Counter &lt;= 100 BEGIN PRINT @Counter; SET @Counter = @Counter + 1; END;

Imagine analyzing user input text data to audit formatting errors by checking exactly how many times a specific character appears inside a data string. You can calculate this by measuring the full original length of the string, subtracting the length of the string after removing the target character with a REPLACE function, and looking at the difference. Example: SELECT LEN('database admin') - LEN(REPLACE('database admin', 'a', '')) as Character_Count;

Imagine merging multiple subqueries together into a single virtual inline table to run aggregate summaries across all input records. A derived table built with a UNION ALL operator will combine all rows from both subqueries, keeping all duplicate records intact. Remember that relational engines require you to assign a clear alias name to any inline derived tables to keep the query valid. Example: SELECT AVG(Summary.Salary) FROM ( SELECT Salary FROM Employee UNION ALL SELECT Salary FROM Historical_Employee ) as Summary;

Imagine running a financial audit total across a column where some new records haven't had their values filled in yet, leaving them as NULL. Standard built-in aggregate functions like SUM, AVG, and MIN naturally ignore NULL values during their calculations. They will sum up all valid numbers and skip the blank entries completely, rather than returning a broken NULL total. Interview Tip: Keep in mind that if *every single row* in the column you are summing contains a NULL value, the SUM function will return NULL.

Imagine calculating success ratios or metrics where dividing two integers will truncate your decimal values if you don't adjust the data types. Because database engines will truncate integer division results towards zero, you need to cast at least one of your operands to a FLOAT or DECIMAL data type before running the division. Example: SELECT CAST(5 AS FLOAT) / 2 as Division_Result;

Imagine a complex routing engine evaluating multiple cascading operational rules to categorize individual transaction records. A CASE expression processes rules sequentially from top to bottom, returning the output value of the very first true condition it hits and ignoring any subsequent rules. If no conditions match, it defaults to the value provided in the ELSE clause. Example: SELECT Name, CASE WHEN Salary > 55000 THEN 'Tier 1' WHEN Salary > 40000 THEN 'Tier 2' ELSE 'Tier 3' END as Category FROM Employee;

Imagine building a fast, isolated scratchpad table within a complex multi-stage database transaction pipeline to store working records that automatically clean themselves up when the session ends. You can spin up a temporary table using your engine's specific temp syntax. In SQL Server, prefix the table name with a hashtag symbol (#). In PostgreSQL or MySQL, use the explicit CREATE TEMPORARY TABLE syntax. Example: CREATE TEMPORARY TABLE Temp_Bonus_Calculation ( Emp_ID INT, Calculated_Bonus DECIMAL(10,2) );

Imagine loading calculated processing values into your temporary session workspace table so you can run complex filtering queries against them later. You use standard INSERT INTO syntax to push records into your temporary table workspace just like you would with a permanent table structure. Example: INSERT INTO Temp_Bonus_Calculation (Emp_ID, Calculated_Bonus) VALUES (1, 1500.00), (3, 2200.00);

Imagine generating a clean external data extract report where internal system codes like numerical flags need to be translated into friendly human-readable text labels on the fly. You can handle these transformations inside your SELECT statement by embedding a CASE expression that maps specific column values to their text labels. Example: SELECT Name, CASE WHEN Department = 'IT' THEN 'Technology Services' WHEN Department = 'HR' THEN 'Human Capital' ELSE 'General Operations' END as Department_Full_Name FROM Employee;

Imagine a quick corporate dashboard widget that needs to pull and display the single highest active compensation figure across the whole company. You can pull the single highest numeric value from the column by applying the MAX aggregate function across the target table dataset. Example: SELECT MAX(Salary) as Top_Salary FROM Employee;

Imagine auditing entry-level compensation baseline parameters to make sure the lowest pay rate in the table meets minimum wage requirements. Use the MIN aggregate function within your selection query to find the absolute lowest value present in the column. Example: SELECT MIN(Salary) as Base_Salary FROM Employee;

Imagine compiling an end-of-year financial audit report to calculate the total combined amount spent on salaries across the entire workforce. Apply the SUM aggregate function to add up all values across the column, giving you a clean total operating footprint. Example: SELECT SUM(Salary) as Total_Payroll FROM Employee;