TERADATA SQL-1

1.How to generate row number in SQL Without ROWNUM

Generating a row number – that is a running sequence of numbers for each row is not easy using plain SQL. In fact, the method I am going to show below is not very generic either. This method only works if there is at least one unique column in the table. This method will also work if there is no single unique column, but collection of columns that is unique. Anyway, here is the query:

SELECT name, sal, (SELECT COUNT(*)  FROM EMPLOYEE i WHERE o.name >= i.name) row_num
FROM EMPLOYEE o
order by row_num
NAME SAL ROW_NUM
Anno 80 1
Bhuti 60 2
Darl 80 3
Hash 100 4
Inno 50 5
Meme 60 6
Pete 70 7
Privy 50 8
Robo 100 9
Tomiti 70 10

The column that is used in the row number generation logic is called “sort key”. Here sort key is “name” column. For this technique to work, the sort key needs to be unique. We have chosen the column “name” because this column happened to be unique in our Employee table. If it was not unique but some other collection of columns was, then we could have used those columns as our sort key (by concatenating those columns to form a single sort key).

Also notice how the rows are sorted in the result set. We have done an explicit sorting on the row_num column, which gives us all the row numbers in the sorted order. But notice that name column is also sorted (which is probably the reason why this column is referred as sort-key). If you want to change the order of the sorting from ascending to descending, you will need to change “>=” sign to “<=” in the query.

As I said before, this method is not very generic. This is why many databases already implement other methods to achieve this. For example, in Oracle database, every SQL result set contains a hidden column called ROWNUM. We can just explicitly select ROWNUM to get sequence numbers.

2,How to select first 5 records from a table?

This question, often asked in many interviews, does not make any sense to me. The problem here is how do you define which record is first and which is second. Which record is retrieved first from the database is not deterministic. It depends on many uncontrollable factors such as how database works at that moment of execution etc. So the question should really be – “how to select any 5 records from the table?” But whatever it is, here is the solution:

In Oracle,

SELECT * 
FROM EMP
WHERE ROWNUM <= 5;

In SQL Server,

SELECT TOP 5 * FROM EMP;

Generic solution,

I believe a generic solution can be devised for this problem if and only if there exists at least one distinct column in the table. For example, in our EMP table ID is distinct. We can use that distinct column in the below way to come up with a generic solution of this question that does not require database specific functions such as ROWNUM, TOP etc.

SELECT  name 
FROM EMPLOYEE o
WHERE (SELECT count(*) FROM EMPLOYEE i WHERE i.name < o.name) < 5
name
Inno
Anno
Darl
Meme
Bhuti

I have taken “name” column in the above example since “name” is happened to be unique in this table. I could very well take ID column as well.

In this example, if the chosen column was not distinct, we would have got more than 5 records returned in our output.

Do you have a better solution to this problem? If yes, post your solution in the comment.

3.What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?

ROWNUM is a pseudo column present in Oracle database returned result set prior to ORDER BY being evaluated. So ORDER BY ROWNUM does not work.

ROW_NUMBER() is an analytical function which is used in conjunction to OVER() clause wherein we can specify ORDER BY and also PARTITION BY columns.

Suppose if you want to generate the row numbers in the order of ascending employee salaries for example, ROWNUM will not work. But you may use ROW_NUMBER() OVER() like shown below:

SELECT name, sal, row_number() over(order by sal desc) rownum_by_sal
FROM EMPLOYEE o
name Sal ROWNUM_BY_SAL
Hash 100 1
Robo 100 2
Anno 80 3
Darl 80 4
Tomiti 70 5
Pete 70 6
Bhuti 60 7
Meme 60 8
Inno 50 9
Privy 50 10

4.What are the differences among ROWNUM, RANK and DENSE_RANK?

ROW_NUMBER assigns contiguous, unique numbers from 1.. N to a result set.

RANK does not assign unique numbers—nor does it assign contiguous numbers. If two records tie for second place, no record will be assigned the 3rd rank as no one came in third, according to RANK. See below:

SELECT name, sal, rank() over(order by sal desc) rank_by_sal
FROM EMPLOYEE o
name Sal RANK_BY_SAL
Hash 100 1
Robo 100 1
Anno 80 3
Darl 80 3
Tomiti 70 5
Pete 70 5
Bhuti 60 7
Meme 60 7
Inno 50 9
Privy 50 9

DENSE_RANK, like RANK, does not assign unique numbers, but it does assign contiguous numbers. Even though two records tied for second place, there is a third-place record. See below:

SELECT name, sal, dense_rank() over(order by sal desc) dense_rank_by_sal
FROM EMPLOYEE o
name Sal DENSE_RANK_BY_SAL
Hash 100 1
Robo 100 1
Anno 80 2
Darl 80 2
Tomiti 70 3
Pete 70 3
Bhuti 60 4
Meme 60 4
Inno 50 5
Privy 50 5

What does UNION do? What is the difference between UNION and UNION ALL?

UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

Consider the following two query results:

sql> select Count(*) as Total from Orders;
+-------+
| Total |
+-------+
|  100  |
+-------+

sql> select Count(*) as Cust123Total from Orders where customer_id = '123'
+--------------+
| Cust123Total |
+--------------+
|      15      |
+--------------+

Given the above query results, what will be the result of the query below?

sql> select Count(*) from Orders where customer_id <> '123'

Answering that the result will be 85 (i.e., 100 – 15) is not necessarily correct. Specifically, any records with a customer_id of NULL willnot be included in either count (i.e., they won’t be included in the count of records that do have a customer_id of 123, nor will they be included in the count of records that do not have a customer_id of 123).

5.What will be the result of the query below? Explain your answer and provide a version that behaves correctly.

select case when null = null then 'Yup' else 'Nope' end as Result;

This query will actually yield “Nope”, seeming to imply that null is not equal to itself! The reason for this is that the proper way to compare a value to null in SQL is with the is operator, not with =.

Accordingly, the correct version of the above query that yields the expected result (i.e., “Yup”) would be as follows:

select case when null is null then 'Yup' else 'Nope' end as Result;

Given the following tables:

sql> SELECT * FROM runners;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | John Doe     |
|  2 | Jane Doe     |
|  3 | Alice Jones  |
|  4 | Bobby Louis  |
|  5 | Lisa Romero  |
+----+--------------+

sql> SELECT * FROM races;
+----+----------------+-----------+
| id | event          | winner_id |
+----+----------------+-----------+
|  1 | 100 meter dash |  2        |
|  2 | 500 meter dash |  3        |
|  3 | cross-country  |  2        |
|  4 | triathalon     |  NULL     |
+----+----------------+-----------+

6.What will be the result of the query below?

select * from runners where id not in (select winner_id from races)

Explain your answer and also provide an alternative version of this query that will avoid the issue that it exposes.

Surprisingly, given the sample data provided, the result of this query will be an empty set. The reason for this is as follows: If the set being evaluated by the SQL NOT IN condition contains any values that are null, then the outer query here will return an empty set, even if there are many runner ids that match winner_ids in the races table.

Knowing this, a query that avoids this issue would be as follows:

select * from runners where id not in (select winner_id from races where winner_id is not null)
 

7.Given two tables created and populated as follows:

create table dbo.envelope(id int, user_id int)
create table dbo.docs(idnum int,pageseq int,doctext varchar(100))

Insert into dbo.envelope values(1,1),(2,2),(3,3)

insert into dbo.docs(idnum,pageseq) values(1,5),(2,6),(null,0)

What will the result be from the following query:

update docs set doctext=pageseq from docs inner join envelope on envelope.id=docs.idnum
where exists (select 1 from dbo.docs where id=envelope.id)

Explain your answer.

The result of the query will be as follows:

idnum	pageseq	doctext
1	5	5
2	6	6
NULL	0	NULL

The EXISTS clause in the above query is a red herring. It will always be true since ID is not a member of dbo.docs. As such, it will refer to the envelope table comparing itself to itself!

The idnum value of NULL will not be set since the join of NULL will not return a result when attempting a match with any value of envelope.

8.What is wrong with this SQL query? Correct it so it executes properly.

SELECT Id, YEAR(BillingDate) AS BillingYear 
FROM Invoices
WHERE BillingYear >= 2010;
 

The expression BillingYear in the WHERE clause is invalid. Even though it is defined as an alias in the SELECT phrase, which appears before the WHERE phrase, the logical processing order of the phrases of the statement is different from the written order. Most programmers are accustomed to code statements being processed generally top-to-bottom or left-to-right, but T-SQL processes phrases in a different order.

The correct query should be:

SELECT Id, YEAR(BillingDate) AS BillingYear
FROM Invoices
WHERE YEAR(BillingDate) >= 2010;
 

9.Considering the database schema displayed in the SQLServer-style diagram below, write a SQL query to return a list of all the invoices. For each invoice, show the Invoice ID, the billing date, the customer’s name, and the name of the customer who referred that customer (if any). The list should be ordered by billing date.

SELECT i.Id, i.BillingDate, c.Name, r.Name AS ReferredByName
FROM Invoices i
 JOIN Customers c ON i.CustomerId = c.Id
 LEFT JOIN Customers r ON c.ReferredBy = r.Id
ORDER BY i.BillingDate;

This question simply tests the candidate’s ability take a plain-English requirement and write a corresponding SQL query. There is nothing tricky in this one, it just covers the basics:

  • Did the candidate remember to use a LEFT JOIN instead of an inner JOIN when joining the customer table for the referring customer name? If not, any invoices by customers not referred by somebody will be left out altogether.
  • Did the candidate alias the tables in the JOIN? Most experienced T-SQL programmers always do this, because repeating the full table name each time it needs to be referenced gets tedious quickly. In this case, the query would actually break if at least the Customer table wasn’t aliased, because it is referenced twice in different contexts (once as the table which contains the name of the invoiced customer, and once as the table which contains the name of the referring customer).
  • Did the candidate disambiguate the Id and Name columns in the SELECT? Again, this is something most experienced programmers do automatically, whether or not there would be a conflict. And again, in this case there would be a conflict, so the query would break if the candidate neglected to do so.

10.Assume a schema of Emp ( Id, Name, DeptId ) , Dept ( Id, Name).

If there are 10 records in the Emp table and 5 records in the Dept table, how many rows will be displayed in the result of the following SQL query:

Select * From Emp, Dept

Explain your answer.

The query will result in 50 rows as a “cartesian product” or “cross join”, which is the default whenever the ‘where’ clause is omitted.

 

11.Given a table SALARIES, such as the one below, that has m = male and f = femalevalues. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table.

Id	Name Sex	Salary
1     A   m      2500
2     B   f      1500
3     C   m      5500
4     D   f       500
UPDATE SALARIES SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END
Table Name : Employee
Employee_id First_name Last_name Salary Joining_date Department
1 John Abraham 1000000 01-JAN-13 12.00.00 AM Banking
2 Michael Clarke 800000 01-JAN-13 12.00.00 AM Insurance
3 Roy Thomas 700000 01-FEB-13 12.00.00 AM Banking
4 Tom Jose 600000 01-FEB-13 12.00.00 AM Insurance
5 Jerry Pinto 650000 01-FEB-13 12.00.00 AM Insurance
6 Philip Mathew 750000 01-JAN-13 12.00.00 AM Services
7 TestName1 123 650000 01-JAN-13 12.00.00 AM Services
8 TestName2 Lname% 600000 01-FEB-13 12.00.00 AM Insurance
Table Name : Incentives
Employee_ref_id Incentive_date Incentive_amount
1 01-FEB-13 5000
2 01-FEB-13 3000
3 01-FEB-13 4000
1 01-JAN-13 4500
2 01-JAN-13 3500

SQL Queries Interview Questions and Answers on “SQL Select”

12. Get all employee details from the employee table
 Select * from employee
13. Get First_Name,Last_Name from employee table
 Select first_name, Last_Name from employee
14. Get First_Name from employee table using alias name “Employee Name”
 Select first_name Employee Name from employee
15. Get First_Name from employee table in upper case
 Select upper(FIRST_NAME) from EMPLOYEE
16. Get First_Name from employee table in lower case
Select lower(FIRST_NAME) from EMPLOYEE
17. Get unique DEPARTMENT from employee table
select distinct DEPARTMENT from EMPLOYEE

 

18. Select first 3 characters of FIRST_NAME from EMPLOYEE

Oracle Equivalent of SQL Server SUBSTRING is SUBSTR, Query : select substr(FIRST_NAME,0,3) from employee

SQL Server Equivalent of Oracle SUBSTR is SUBSTRING, Query : select substring(FIRST_NAME,0,3) from employee


19. Get position of ‘o’ in name ‘John’ from employee table

Oracle Equivalent of SQL Server CHARINDEX is INSTR, Query : Select instr(FIRST_NAME,'o') from employee where first_name='John'

SQL Server Equivalent of Oracle INSTR is CHARINDEX, Query: Select CHARINDEX('o',FIRST_NAME,0) from employee where first_name='John'



20. Get FIRST_NAME from employee table after removing white spaces from right side

select RTRIM(FIRST_NAME) from employee


21. Get FIRST_NAME from employee table after removing white spaces from left side

select LTRIM(FIRST_NAME) from employee


22. Get length of FIRST_NAME from employee table

Oracle,MYSQL Equivalent of SQL Server Len is Length , Query :select length(FIRST_NAME) from employee

SQL Server Equivalent of Oracle,MYSQL Length is Len, Query :select len(FIRST_NAME) from employee


23. Get First_Name from employee table after replacing ‘o’ with ‘$’

select REPLACE(FIRST_NAME,'o','$') from employee


24. Get First_Name and Last_Name as single column from employee table separated by a ‘_’

Oracle Equivalent of MySQL concat is '||', Query : Select FIRST_NAME|| '_' ||LAST_NAME from EMPLOYEE

SQL Server Equivalent of MySQL concat is '+', Query : Select FIRST_NAME + '_' +LAST_NAME from EMPLOYEE



25. Get FIRST_NAME ,Joining year,Joining Month and Joining Date from employee table

SQL Queries in Oracle, Select FIRST_NAME, to_char(joining_date,'YYYY') JoinYear , to_char(joining_date,'Mon'), to_char(joining_date,'dd') from EMPLOYEE

SQL Queries in SQL Server, select SUBSTRING (convert(varchar,joining_date,103),7,4) , SUBSTRING (convert(varchar,joining_date,100),1,3) , SUBSTRING (convert(varchar,joining_date,100),5,2) from EMPLOYEE

 “SQL Order By” Interview Questions

26. Get all employee details from the employee table order by First_Name Ascending
Select * from employee order by FIRST_NAME asc


27. Get all employee details from the employee table order by First_Name descending

Select * from employee order by FIRST_NAME desc
28. Get all employee details from the employee table order by First_Name Ascending and Salary descending
Select * from employee order by FIRST_NAME asc,SALARY desc

“SQL Where Condition” Interview Questions

29. Get employee details from employee table whose employee name is “John”
Select * from EMPLOYEE where FIRST_NAME='John'

30. Get employee details from employee table whose employee name are “John” and “Roy”
Select * from EMPLOYEE where FIRST_NAME in ('John','Roy')

31. Get employee details from employee table whose employee name are not “John” and “Roy”
Select * from EMPLOYEE where FIRST_NAME not in ('John','Roy')

“SQL Wild Card Search” Interview Questions

32. Get employee details from employee table whose first name starts with ‘J’

Select * from EMPLOYEE where FIRST_NAME like 'J%'


33. Get employee details from employee table whose first name contains ‘o’

Select * from EMPLOYEE where FIRST_NAME like '%o%'


34. Get employee details from employee table whose first name ends with ‘n’

Select * from EMPLOYEE where FIRST_NAME like '%n'

“SQL Pattern Matching” Interview Questions


35. Get employee details from employee table whose first name ends with ‘n’ and name contains 4 letters

Select * from EMPLOYEE where FIRST_NAME like '___n' (Underscores)


36. Get employee details from employee table whose first name starts with ‘J’ and name contains 4 letters

Select * from EMPLOYEE where FIRST_NAME like 'J___' (Underscores)


37. Get employee details from employee table whose Salary greater than 600000

Select * from EMPLOYEE where Salary >600000


38. Get employee details from employee table whose Salary less than 800000

Select * from EMPLOYEE where Salary <800000


39. Get employee details from employee table whose Salary between 500000 and 800000

Select * from EMPLOYEE where Salary between 500000 and 800000


40. Get employee details from employee table whose name is ‘John’ and ‘Michael’

 

Select * from EMPLOYEE where FIRST_NAME in ('John','Michael')

Interview Questions on “SQL DATE Functions”

41. Get employee details from employee table whose joining year is “2013”

SQL Queries in Oracle, Select * from EMPLOYEE where to_char(joining_date,'YYYY')='2013'

SQL Queries in SQL Server, Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,103),7,4)='2013'

42. Get employee details from employee table whose joining month is “January”
SQL Queries in Oracle, Select * from EMPLOYEE where to_char(joining_date,'MM')='01' or Select * from EMPLOYEE where to_char(joining_date,'Mon')='Jan'

SQL Queries in SQL Server, Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,100),1,3)='Jan'

43. Get employee details from employee table who joined before January 1st 2013
SQL Queries in Oracle, Select * from EMPLOYEE where JOINING_DATE <to_date('01/01/2013','dd/mm/yyyy')

SQL Queries in SQL Server (Format - “MM/DD/YYYY”), Select * from EMPLOYEE where joining_date <'01/01/2013'


44. Get employee details from employee table who joined after January 31st
SQL Queries in Oracle, Select * from EMPLOYEE where JOINING_DATE >to_date('31/01/2013','dd/mm/yyyy')

SQL Queries in SQL Server and MySQL (Format - “MM/DD/YYYY”), Select * from EMPLOYEE where joining_date >'01/31/2013'

SQL Queries in MySQL (Format - “YYYY-DD-MM”), Select * from EMPLOYEE where joining_date >'2013-01-31'


45. Get Joining Date and Time from employee table

SQL Queries in Oracle, select to_char(JOINING_DATE,'dd/mm/yyyy hh:mi:ss') from EMPLOYEE

SQL Queries in SQL Server, Select convert(varchar(19),joining_date,121) from EMPLOYEE


46. Get Joining Date,Time including milliseconds from employee table

SQL Queries in Oracle, select to_char(JOINING_DATE,'dd/mm/yyyy HH:mi:ss.ff') from EMPLOYEE . Column Data Type should be “TimeStamp”

SQL Queries in SQL Server, select convert(varchar,joining_date,121) from EMPLOYEE

47. Get difference between JOINING_DATE and INCENTIVE_DATE from employee and incentives table

Select FIRST_NAME,INCENTIVE_DATE - JOINING_DATE from employee a inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID


48. Get database date

SQL Queries in Oracle, select sysdate from dual

SQL Queries in SQL Server, select getdate()

“SQL Escape Characters” Interview Questions

49. Get names of employees from employee table who has ‘%’ in Last_Name. Tip : Escape character for special characters in a query.

SQL Queries in Oracle, Select FIRST_NAME from employee where Last_Name like '%?%%'

SQL Queries in SQL Server, Select FIRST_NAME from employee where Last_Name like '%[%]%'


50. Get Last Name from employee table after replacing special character with white space
SQL Queries in Oracle, Select translate(LAST_NAME,'%',' ') from employee

SQL Queries in SQL Server and MySQL, Select REPLACE(LAST_NAME,'%',' ') from employee

“SQL Group By Query” Interview Questions and Answers

51. Get department,total salary with respect to a department from employee table.
Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by department

52. Get department,total salary with respect to a department from employee table order by total salary descending
Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending

SQL Queries Interview Questions and Answers on “SQL Mathematical Operations using Group By”

53. Get department,no of employees in a department,total salary with respect to a department from employee table order by total salarydescending

Select DEPARTMENT,count(FIRST_NAME),sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending


54. Get department wise average salary from employee table order by salaryascending

select DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalary asc


55. Get department wise maximum salary from employee table order by salaryascending

select DEPARTMENT,max(SALARY) MaxSalary from employee group by DEPARTMENT order by MaxSalary asc


56. Get department wise minimum salary from employee table order by salary ascending

select DEPARTMENT,min(SALARY) MinSalary from employee group by DEPARTMENT order by MinSalary asc


57. Select no of employees joined with respect to year and month from employee table

SQL Queries in Oracle, select to_char (JOINING_DATE,'YYYY') Join_Year,to_char (JOINING_DATE,'MM') Join_Month,count(*) Total_Emp from employee group by to_char (JOINING_DATE,'YYYY'),to_char(JOINING_DATE,'MM')

SQL Queries in SQL Server, select datepart (YYYY,JOINING_DATE) Join_Year,datepart (MM,JOINING_DATE) Join_Month,count(*) Total_Emp from employee group by datepart(YYYY,JOINING_DATE), datepart(MM,JOINING_DATE)
SQL Queries in MySQL, select year (JOINING_DATE) Join_Year,month (JOINING_DATE) Join_Month,count(*) Total_Emp from employee group by year(JOINING_DATE), month(JOINING_DATE)


58. Select department,total salary with respect to a department from employee table where total salary greater than 800000 order by Total_Salary descending

Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT having sum(SALARY) >800000 order by Total_Salary desc