YuWebdesign



SQL Interview Questions (Query)

By YuwebDesign


INSERT INTO table_name
(column1, column2, column3,...)
VALUES
('value1',' value2',' value3',...),
('value1',' value2',' value3',...),
('value1',' value2',' value3',...);

INSERT into Employee_Details (Employee_Name, Salary, Age)
VALUES (‘John’, 5500 , 29);
--insert record Employee_Name: John, Salary: 5500, Age: 29

DELETE FROM table_name
WHERE condition;

If you omit the WHERE clause, all records/every row from your table will be deleted.

ALTER TABLE table_name
ADD column_name datatype [NULL|NOT NULL] [CONSTRAINTS];

ALTER TABLE Employee_Details
ADD (Salary);
--add a column ‘Salary’ to a table Employee_Details

ALTER TABLE table_name DROP (column_name);

UPDATE Employee_Details SET Salary = 7500 WHERE Employee_Name = ‘John’;
--change a value of the field ‘Salary’ as 7500
--for an Employee_Name ‘John’ in a table Employee_Details

SELECT column_name AS new_name FROM table_name;

The SELECT INTO statement copies data from one table into a new table.

The new table will be created with the column-names and types as defined in the old table.

You can create new column names using the AS clause.

SELECT * INTO newtable
FROM oldtable
WHERE condition;

The SELECT TOP/LIMIT/ROWNUM clause is used to limit the number of records to return.
It is useful for when returning a large number of records can impact on performance.

  1. Oracle uses ROWNUM
  2. MySQL supports the LIMIT clause
  3. SQL servers use SELECT TOP clause

To display the maximum salary in SQL, you can use the built-in function called MAX().

  1. SELECT max(salary)
    FROM employees
    WHERE salary IN (
    SELECT salary
    FROM employees
    MINUS
    SELECT max(salary)
    FROM employees
    );
    
  2. SELECT max(salary)
    FROM employees
    WHERE salary <> (SELECT max(salary) FROM employees);
    

  1. Using QL SERVER TOP function
    SELECT TOP 1 salary 
    FROM (SELECT DISTINCT TOP N salary 
            FROM Employee 
            ORDER BY salary DESC) 
    AS temp 
    ORDER BY salary
    
    SELECT TOP 1 salary
    FROM (SELECT TOP 3 salary
            FROM employee_table
            ORDER BY salary DESC)
    AS emp
    ORDER BY salary ASC;
    //selects 3rd highest salary
    

    By default ORDER BY clause print rows in ascending order, since we need the highest salary at the top, we have used ORDER BY DESC, which will display salaries in descending order. DISTINCT is used to remove duplicates. The outer query will then pick the top most salary, which would be your Nth highest salary.

  2. Using MySQL/PostgreSQL LIMIT function
    • SELECT Salary FROM
      (
          SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N
      ) AS Emp ORDER BY Salary LIMIT 1;
      
    • SELECT DISTINCT(salary)
      FROM employee
      ORDER BY salary DESC LIMIT n-1,1
      
      SELECT DISTINCT(salary)
      FROM employee
      ORDER BY salary DESC LIMIT 6,1
      --finds 7th largest salary
      
  3. Using Oracle ROW_NUMBER()
    SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) rn 
    FROM Employee e ) 
    WHERE rn = N; 
    
    SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) rn FROM Employee e ) WHERE rn = 2; 
    --2nd highest salary
    

    ROW_NUMBER() is not handling duplicate values properly.

  4. Using subquery
    SELECT e1.name, e1.salary
    FROM Employee e1
    WHERE N-1 = (SELECT COUNT(DISTINCT (e2.salary))
    FROM Employee e2
    WHERE e2.salary > e1.salary
    )
    
    SELECT Emp1.Name
    FROM Employee Emp1
    WHERE 2 = (SELECT COUNT(DISTINCT(Emp2.Salary))
    FROM Employee Emp2
    WHERE Emp2.Salary > Emp1.Salary
    )
    --displays 3rd highest salary from a table employee
    --number of people with salary higher than this is 2
    

    The distinct keyword removes duplicate salaries.
    In order to find the Nth highest salary, we are only considering unique salaries.

    Highest salary means no salary is higher than it,
    Second highest means only one salary is higher than it,
    3rd highest means two salaries are higher than it,
    similarly Nth highest salary means N-1 salaries are higher than it.

    Pros: this generic solution works in all database including Oracle, MySQL, SQL SERVER and PostgreSQL.

    Cons: it is slow, because the inner query will run for every row processed by the outer query.

  5. Using subquery
    SELECT name, salary
    FROM Employee e1
    WHERE N <= (SELECT COUNT(*) FROM Employee e2 WHERE e1.salary = e2.salary)
    
    SELECT Name, Marks
    FROM Student s1
    WHERE 3 <= (SELECT COUNT(*)
    FROM Students s2
    WHERE s1.marks = s2.marks)
    --gets name and marks of the top 3 students
    

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking.

In such cases, RANK() will assign non-consecutive “ranks” to the values in the set
(resulting in gaps between the integer ranking values when there is a tie),
whereas DENSE_RANK() will assign consecutive ranks to the values in the set
(so there will be no gaps between the integer ranking values in the case of a tie).

For example, consider the set {25, 25, 50, 75, 75, 100}.

  1. For such a set, RANK() will return {1, 1, 3, 4, 4, 6}
    (note that the values 2 and 5 are skipped),
  2. whereas DENSE_RANK() will return {1,1,2,3,3,4}.

We can get the desired output by using TIMESTAMPDIFF() function.

SELECT *, TIMESTAMPDIFF(year, Hiredate, current_date()) AS
DiffYear
FROM employees
WHERE TIMESTAMPDIFF(year, Hiredate, current_date())
BETWEEN 1 and 5
ORDER BY Hiredate DESC;
--Employees who have been hired in the last 1 to 5 years

We can get the desired output by using TIMESTAMPDIFF() function.

SELECT *, TIMESTAMPDIFF(month, Hiredate, current_date()) AS
DiffMonth
FROM employees
WHERE TIMESTAMPDIFF(month, Hiredate, current_date())
BETWEEN 1 and 5
ORDER BY Hiredate DESC;
--Employees who have been hired in the last 1 to 5 months

We can get the desired output by using DATEDIFF() function.

SELECT *, DATEDIFF(current_date(), Hiredate) AS
DiffDay
FROM employees
WHERE DATEDIFF(current_date(), Hiredate)
BETWEEN 1 and 30
ORDER BY Hiredate DESC;
--Employees who have been hired in the last 1 to 30 days

In SQL, there is a built-in function called GetDate() which helps to return the current timestamp/date.

SELECT GetDate();

  1. The LIKE operator of SQL is used to fetch filtered data by searching for a particular pattern in WHERE clause.
    SELECT * FROM Employees WHERE employee_name LIKE 'A%';
  2. SELECT * FROM Employees WHERE left(employee_name, 1)='A';
  3. SELECT * FROM Employees WHERE substring(employee_name, 1, 1)='A';

To count the number of records in a table, you can use the below commands:

SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

SELECT * INTO studentcopy FROM student WHERE 1=2

Copy student table to another table with the same structure with no rows copied.

SELECT studentID FROM student
INTERSECT
SELECT StudentID FROM Exam

Using a SAMPLE clause we can select random rows.

SELECT * FROM table_name SAMPLE(10);

Records can be fetched for both Odd and Even row numbers.

  1. Displays even numbers:
    SELECT * FROM table WHERE id % 2 = 0
    SELECT studentId FROM (SELECT rowno, studentId FROM student)
    WHERE mod(rowno,2)=0
  2. Displays odd numbers:
    Select * from table where id % 2 != 0
    SELECT studentId FROM (Select rowno, studentId from student)
    WHERE mod(rowno,2)=1
    FROM (SELECT rowno, studentId FROM student)
    WHERE mod(rowno,2)=1

Given a table dbo.users where the column user_id is a unique numeric identifier, how can you efficiently select the first 100 odd user_id values from the table?

SELECT TOP 100 user_id FROM dbo.users WHERE user_id % 2 = 1 ORDER BY user_id

You can select unique records from a table by using the DISTINCT keyword.
Syntax:

SELECT DISTINCT column1, column2
FROM table_name;
SELECT DISTINCT StudentID, StudentName
FROM Student;
--selects distinct student records from table Student

You can use the COALESCE function to return the first non-null value from the table.

SELECT
StudentID,
COALESCE(FirstName, MiddleName, LastName)
AS Name
FROM Students;

DELETE M1 from managers M1, managers M2
WHERE M2.Name = M1.Name AND M1.Id > M2.Id;

  1. SELECT Name
    FROM Employees
    GROUP BY Name
    HAVING COUNT(Name) > 1
    --single column
    --use GROUP BY and HAVING condition
    --efficient and fast query
  2. SELECT Fname, Lname
    FROM Employees
    GROUP BY Fname, Lname
    HAVING COUNT(*) > 1
    --multiple columns
    --use GROUP BY and HAVING condition
    --efficient and fast query
  3. SELECT name FROM
    (SELECT name, count(name) AS num
    FROM Person
    GROUP BY name
    ) AS statistic
    WHERE num > 1;
    --create a temporary table that counts all the names in a table
    --filter occurence > 1

SELECT student, marks
FROM table
WHERE marks > SELECT AVG(marks) from table;

  1. SELECT SUBSTRING(StudentName,1,5) AS studentname FROM student
  2. SELECT RIGHT(Studentname,5) AS studentname FROM student

LIKE operator is used for pattern matching.

  1. % – Matches zero or more characters.
  2. _(Underscore) – Matches exactly one character.
SELECT * FROM Student WHERE studentname LIKE 'a%'
SELECT * FROM Student WHERE studentname LIKE 'ami_'

  1. LOWER function takes a string as an argument and returns it converted to lower case.

    Syntax:

    LOWER(‘string’)
  2. UPPER function takes a string as an argument and returns it converted to uppercase.

    Syntax:

    UPPER(‘string’)
  3. INITCAP function returns the string with the first letter in uppercase and rest of the letters in lowercase.

    Syntax:

    INITCAP(‘string’)

STUFF Function overwrites existing character or inserts a string into another string.
Syntax:

STUFF(string_expression,start, length, replacement_characters)

string_expression: string with substituted characters
start: starting position
length: number of substituted characters in the string
replacement_string: new characters injected in the string

REPLACE function replaces the existing characters of all the occurrences.

Syntax:

REPLACE (string_expression, search_string, replacement_string)

Every search_string in the string_expression will be replaced with the replacement_string.

To validate emails you can use the regular expressions function (REGEXP_LIKE).

SELECT Email
FROM Employee
WHERE NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

To send an email from the database, you can use the STORED PROCEDURE.

    Follow the below procedure to send the emails:

  1. Configure your SQL Server Mail account.
  2. Enable the database mail.
  3. Write a script to send an email.
    USE [YourDB]
    EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'abc@example.com; def@example.com;xyz@example.com’
    @body = ' Sample Body Text',
    @subject = 'Example Email' ;
    GO

You have the following three tables which have to be linked together.
Department(Ssn, EmployeeName, EmployeeAge)
EmployeeContactDetails(Ssn, DepartmentID,desc,Ord)
EmployeeAddress(Ssn,DepartmentID, desc, Ord)

To solve this problem statement you can perform a JOIN on Department.Ssn and DepartmentID in the other tables.
If you are sure that the Ssn exists in all the three considered tables, then you can use the INNER JOIN.
If you are not sure that you have matching rows, then you can use the LEFT JOIN.

SELECT d.Ssn, d.EmployeeName, c.desc ContactDetailsDesc, a.desc AddressDetailsDesc
from Department d
INNER JOIN EmployeeContactDetails c
ON d.id = c.DepartmentID
INNER JOIN address a
ON d.id = a.DepartmentID
WHERE d.EmployeeName = 'abc' AND c.ord = 1 AND a.ord = 1

To check the procedures:

SELECT * FROM SampleSource
WHERE Type=’PROCEDURE’
AND NAME IN (‘SP_CONNECTED_AGG’,’SP_UNCONNECTED_AGG’);

To find the procedures columns information:

SELECT OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT from ALL_ARGUMENTS order by OWNER, OBJECT_NAME, SEQUENCE;

  1. WHERE col * 4 < 16
  2. WHERE col < 16 / 4

If we compare both the statements, then the second WHERE clause would be comparatively faster than the first one.

That is because, for the first statement, MYSQL would retrieve the value of ‘col’ for each and every row, multiplied by four. After that, it would compare the result to 16.

Also, in the first case no Index can be used, and hence it makes it further slow.

Usage of index completely depends on if you consider the primary index or not.

Consider you have a student table.
Now, suppose if an Index is present on StudentID, StudentFirstName, and StudentLastName then you can consider a query as follows:

SELECT * FROM StudentDetails WHERE StudentID=3 and StudentFirstName='Jatin'
  1. If StudentID and StudentFirstName are primary columns, an Index will be invoked.
  2. If the above two columns in the query are the secondary index columns, the index will not be invoked.
  3. If the above two columns contain the first column while creating an index (i.e. the primary index), then the index will definitely be invoked.

  1. Implicitly by omitting column from column list.
  2. Explicitly by specifying NULL keyword in the VALUES clause

A NULL value is a field with no value present in that particular field.
Since the NULL value cannot be compared to any other NULL values, you cannot use the comparison operators such as =, <, or <>.
To compare the fields with NULL values, you have to use the IS NULL and IS NOT NULL operator.

Syntax:

  1. SELECT column_names FROM table_name WHERE column_name IS NULL;
  2. SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

All of them are used to replace the NULL value with another value.

  1. ORACLE users use the NVL function,
  2. MySQL users use the IFNULL function
  3. SQL servers use the ISNULL function

E.g., let’s say column_3 has NULL values. You will be unable to perform mathematical calculations as then the output would produce NULL.

SELECT column_1 * (column_2 + column_3) FROM Example_Table

You can overcome this by replacing NULL values with zero:

  1. SELECT column_1 * (column_2 + NVL(column_3,0)) FROM Example_Table
  2. SELECT column_1 * (column_2 + IFNULL(column_3,0)) FROM Example_Table
  3. SELECT column_1 * (column_2 + ISNULL(column_3,0)) FROM Example_Table

Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null.

With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1.

With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.

CREATE OR REPLACE TRIGGER update_trig
AFTER UPDATE ON Dept
FOR EACH ROW
DECLARE
CURSOR emp_cur IS SELECT * FROM Emp;
BEGIN
FOR i IN emp_cur LOOP
IF i.dept_no = :NEW.dept_no THEN
DBMS_OUTPUT.PUT_LINE(i.emp_no); -- for printing those
UPDATE Emp -- emp number which are
SET sal = i.sal + 100 -- updated
WHERE emp_no = i.emp_no;
END IF;
END LOOP;
END;

>USE TestDB
GO
SELECT * FROM sys.Tables
GO

DELETE from Players WHERE Player_Name = ‘Sachin’;

  1. In SQL Server, PostgreSQL, and SQLite, this can be done using the except keyword as follows:
    select * from test_a
    except
    select * from test_b;
    
  2. In Oracle, the minus keyword is used instead. Note that if there are multiple columns, say ID and Name, the column should be explicitly stated in Oracle queries:
    Select ID from test_a minus select ID from test_b
  3. MySQL does not support the except function. However, there is a standard SQL solution that works in all of the above engines, including MySQL:

    select a.id
    from test_a a
    left join test_b b on a.id = b.id
    where b.id is null;

SELECT Employee_Name FROM Employee_Details WHERE Salary>=5000;

SSP_RENAME TABLE 'SCOREBOARD', 'OVERALLSCORE';

sp_rename OldTableName,NewTableName
sp_rename 'TableName.OldColumnName', 'NewColumnName'

Use the following table with one-to-many relationship:

EmployeeeID Name ManagerID
1 Name1 3
2 Name2 3
3 Name3 NULL
4 Name4 2
5 Name5 2
  1. INNER JOIN:
    SELECT e1.Name EmployeeName, e2.Name AS ManagerName
    FROM Employee e1
    INNER JOIN Employee e2
    ON e1.ManagerID = e2.EmployeeID
  2. OUTER JOIN:
    SELECT e1.Name EmployeeName, ISNULL(e2.name, 'Top Manager') AS ManagerName
    FROM Employee e1
    LEFT JOIN Employee e2
    ON e1.ManagerID = e2.EmployeeID
  3. SELF JOIN:
    SELECT a.EmployeeID AS "Emp_ID", a.EmployeeName AS "Employee Name",
    b.ManagerID AS "Supervisor ID", b.EmployeeName AS "Supervisor Name"
    FROM employee a, employee b
    WHERE a.ManagerID = b.EmployeeID;

SQL Case statement allows embedding an if-else like clause in the SELECT statement.

SELECT Gender from TestTable
Gender
M
F
NULL
m
f
g
H
i
SELECT Gender,
case 
when Gender='i' then 'U' 
when Gender='g' then 'U'
when Gender='H' then 'U'
when Gender='NULL' then 'N'
else upper(Gender)
end as newgender from TestTable GROUP BY Gender

This query returns “False”. In the above question, we could see null = null is not the proper way to compare a null value. To compare a value with null, we use IS operator in SQL.

The correct way:

select case when null is null then 'True' else 'False' end as Result;

UPDATE TestTable SET Gender = CASE Gender WHEN 'F' THEN 'M' ELSE 'F' END

Given a table TBL with a field Nmbr that has rows with the following values:

1, 0, 0, 1, 1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 0, 1

This can be done as follows:

update TBL set Nmbr = case when Nmbr = 0 then Nmbr+2 else Nmbr+3 end;

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

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)

Note, this is assuming the standard SQL behavior that you get without modifying the default ANSI_NULLS setting.

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.

Given these contents of the Customers table:

Id Name ReferredBy
1 John Doe NULL
2 Jane Smith NULL
3 Anne Jenkins 2
4 Eric Branford NULL
5 Pat Richards 1
6 Alice Barnes 2

Although there are 4 customers not referred by Jane Smith (including Jane Smith herself), the query will only return one: Pat Richards. All the customers who were referred by nobody at all (and therefore have NULL in their ReferredBy column) don’t show up. But certainly those customers weren’t referred by Jane Smith, and certainly NULL is not equal to 2, so why didn’t they show up?

SQL Server uses three-valued logic, which can be troublesome for programmers accustomed to the more satisfying two-valued logic (TRUE or FALSE) most programming languages use. In most languages, if you were presented with two predicates: ReferredBy = 2 and ReferredBy <> 2, you would expect one of them to be true and one of them to be false, given the same value of ReferredBy. In SQL Server, however, if ReferredBy is NULL, neither of them are true and neither of them are false. Anything compared to NULL evaluates to the third value in three-valued logic: UNKNOWN.

The query should be written in one of two ways:

  1. SELECT Name FROM Customers WHERE ReferredBy IS NULL OR ReferredBy <> 2
  2. SELECT Name FROM Customers WHERE ISNULL(ReferredBy, 0) <> 2; -- (Or COALESCE() )

Watch out for the following, though!

SELECT Name FROM Customers WHERE ReferredBy = NULL OR ReferredBy <> 2

This will return the same faulty set as the original. Why? We already covered that: Anything compared to NULL evaluates to the third value in the three-valued logic: UNKNOWN. That “anything” includes NULL itself! That’s why SQL Server provides the IS NULL and IS NOT NULL operators to specifically check for NULL. Those particular operators will always evaluate to true or false.

Even if a candidate doesn’t have a great amount of experience with SQL Server, diving into the intricacies of three-valued logic in general can give a good indication of whether they have the ability learn it quickly or whether they will struggle with it.

SQL interview questions

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:

  1. 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.
  2. 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).
  3. 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.

Note that this query will not return Invoices that do not have an associated Customer. This may be the correct behavior for most cases (e.g., it is guaranteed that every Invoice is associated with a Customer, or unmatched Invoices are not of interest). However, in order to guarantee that all Invoices are returned no matter what, the Invoices table should be joined with Customers using LEFT JOIN:

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

You can avoid duplicates using UNION ALL and still run much faster than UNION DISTINCT (which is actually same as UNION) by running a query like this:

SELECT * FROM mytable 
WHERE a=X 
UNION ALL 
SELECT * FROM mytable 
WHERE b=Y AND a!=X

The key is the AND a!=X part.
This gives you the benefits of the UNION (a.k.a., UNION DISTINCT) command,
while avoiding much of its performance hit.

Given the following tables:

SELECT * FROM users;
user_id username
1 John Doe
2 Jane Doe
3 Alice Jones
4 Lisa Romero
SELECT * FROM training_details;
user_training_id user_id training_id training_date
1 1 1 “2015-08-02″
2 2 1 “2015-08-03″
3 3 2 “2015-08-02″
4 4 2 “2015-08-04″
5 2 1 “2015-08-03″
6 1 1 “2015-08-02″
7 3 2 “2015-08-04″
8 4 3 “2015-08-03″
9 1 4 “2015-08-03″
10 3 1 “2015-08-02″
11 4 2 “2015-08-04″
12 3 2 “2015-08-02″
13 1 1 “2015-08-02″
14 4 3 “2015-08-03″
SELECT
      u.user_id,
      username,
      training_id,
      training_date,
      count( user_training_id ) AS count
  FROM users u JOIN training_details t ON t.user_id = u.user_id
  GROUP BY u.user_id,
           username,
           training_id,
           training_date
  HAVING count( user_training_id ) > 1
  ORDER BY training_date DESC;

Result:

user_id username training_id training_date count
4 Lisa Romero 2 August, 04 2015 00:00:00 2
4 Lisa Romero 3 August, 03 2015 00:00:00 2
1 John Doe 1 August, 02 2015 00:00:00 3
3 Alice Jones 2 August, 02 2015 00:00:00 2

Suppose we have a Customer table containing the following data:

CustomerID CustomerName
1 Prashant Kaurav
2 Ashish Jha
3 Ankit Varma
4 Vineet Kumar
5 Rahul Kumar

Write a single SQL statement to concatenate all the customer names into the following single semicolon-separated string:

Prashant Kaurav; Ashish Jha; Ankit Varma; Vineet Kumar; Rahul Kumar
SELECT CustomerName+ '; ' 
From Customer
For XML PATH('')

This is close, but will have an undesired trailing ;. One way of fixing that could be:

SELECT top 1
LTRIM(STUFF((SELECT '; ' + c1.CustomerName FROM Customer c1 FOR XML PATH ('')), 1, 1,'')) as SSV
from Customer c2;

In PostgreSQL one can also use this syntax to achieve the fully correct result:

SELECT array_to_string(array_agg(CustomerName), '; '::text) 
FROM Customer

“Order by 2” is only valid when there are at least two columns being used in select statement. However, in this query, even though the Employee table has 2 columns, the query is only selecting 1 column name, so “Order by 2” will cause the statement to throw an error while executing the above sql query.

This query will return 10 records as TRUNCATE was executed in the transaction.
TRUNCATE does not itself keep a log but BEGIN TRANSACTION keeps track of the TRUNCATE command.


Leave a Reply or Comment

Your email address will not be published. Required fields are marked *