PCS Interview Coding (Part 2)

  1. Write an SQL query to fetch 'First_Name from worker table using the alias name as <Worker_Name>
    • Select First_Name as Worker_Name 
    • from Worker;
  2. Write an SQL query to fetch 'First_Name' from Worker table in Upper Case
    • Select upper( First_Name ) 
    • from Worker;
  3. Write an SQL query to fetch unique values of Department from Worker table
    • Select distinct Department 
    • from Worker;
  4. Write an SQL query to print the first three characters of First_Name from Worker table
    • Select substring( First_Name, 1,3 )
    • from Worker;
  5. Write an SQL query to find the position of the alphabet ('A') in the first name column 'Amitabh' from Worker table
    • Select INSTR( First_Name, BINARY 'a' )
    • From Worker
    • Where First_Name = 'Amitabh';
  6. Write an SQL query to print the First_Name From Worker Table after removing white spaces from the right side
    • Select RTTRIM( First_Name )
    • From Worker;
  7. Write an SQL query to print the Department from Worker Table after removing white spaces from the left side
    • Select LTRIM( Department )
    • From Worker;
  8. Write an SQL query to print the First_Name From Worker Table after replacing 'a' with 'A'
    • Select REPLACE( First_Name, 'a', 'A')
    • From Worker;
  9. Write an SQL query to print the First_Name and Last_Name from Worker table into a single column Complete_Name. A space char should separate them
    • Select Concat( First_Name, ' ' , Last_Name ) As 'Compelete_Name')
    • From Worker;
  10. Write an SQL query to print all Worker details from the Worker table order by First_Name ascending
    • Select (*)
    • From Worker
    • Order by First_Name asc;
  11. Write an SQL query to print all Worker details from the Worker table order by First_Name ascending and Department Descending
    • From Worker
    • Order by First_name asc, Department desc;
  12. Write an SQL query to print details for Workers excluding the First_Name as 'Vipul' and 'Satish' from Worker table
    • Select (*)
    • From Worker
    • where First_Name NOT IN ( 'Vipul', 'Satish' );
  13. Write an SQL query to print details for Workers within the First_Name as 'Vipul' and 'Satish' from Worker table
    • Select (*)
    • From Worker
    • Where First_Name in ( 'Vipul', 'Satish' );
  14. Write an SQL query to print details of Workers with Department name as 'Admin'
    • Select (*)
    • From Worker
    • Where Department like 'Admin%';
  15. Write an SQL query to print details of the Workers whose First_Name contains 'A'
    • Select (*)
    • From Workers
    • Where First_Name like '%A%';
  16. Write an SQL query to print details of the Workers whose First_Name ends with 'A'
    • Select (*)
    • From Workers
    • Where First_Name like '%A'';
  17. Write an SQL query to print details of the Workers whose Salary lies between 100,000 and 500,000
    • Select (*)
    • From Workers
    • Where Salary between 100000 and 500000;
  18. Write an SQL query to print details of the Workers who have joined in Feb '2014
    • Select (*)
    • From Worker
    • Where year( Joining_Date) = 2014 and month( Joining_Date) = 2;

    • NOTE:
    • month( [month_object] ) = [numerical month number Jan – Dec is 1 – 12 ]
  19. Write an SQL query to fetch the count of Employees working in the Department 'Admin'
    • Select Count(*)
    • From Worker
    • Where Department = 'Admin'
  20. Write an SQL query to fetch Worker names with Salaries >= 50,000 and <= 100,000
    • Select Concat( First_Name, ' ', Last_Name, ' ' ) As Worker_Name, Salary
    • From Worker
    • Where Worker_ID In (Select Worker_ID From Worker Where Salary between 50000 and 100000);
  21. Write an SQL query to fetch the No_of_Workers for each Department in descending order
    • Select Department, count(Worker_ID) No_of_Workers
    • Group by Department
    • Order by No_Of_Workers desc;
  22. Write an SQL query to print details of the Workers who are also managers
    • Select Distinct W.First_Name, T.Worker_Title
    • From Worker W
    • Inner Join Tittle T
    • On W.Worker_ID = T.Worker_Ref_ID And T.Worker_Title in ('Manager');
  23. Write an SQL query to fetch duplicate records Having Matching Data in some fields of a table
    • Select Worker_Title, Affected_From, Count(*)
    • From Title
    • Group By Worker_Title, Affected_From
    • Having Count(*) > 1;
  24. Write an SQL query to show only odd rows From a table
    • Select *
    • From Worker
    • Where Mod( Worker_ID, 2 ) <> 0;
  25. Write an SQL query to show only even rows from a table
    • Select *
    • From Worker
    • Where Mod( Worker_ID, 2 ) = 2;
  26. Write an SQL query to clone a new table from another table
    • Select * Into WorkerClone
    • From Worker;
  27. Write an SQL query t fetch intersecting records of two tables
    • (Select * From Worker )
    • Intersect
    • (Select * From WorkerClone);
  28. Write an SQL query to show records from one table that another table does not have
    • Select * From Worker
    • Minus
    • Select * From Title;
  29. Write an SQL query to show the current date and time
    • Select curdate(); [query returns the current date and time]
    • Select Now(); [query returns the current date and time]
    • Select getdate(); [query returns the current date and time]
    • Select SYSDATE From DUAL;
  30. Write an SQL query to show the top N records of a table (N = 10)
    • Select * From Worker
    • Order by Salary Desc Limit 10;
  31. Write an SQL query to determine the Nith highest salary from a table (N=5)
    • MySQL:
    • Select Salary From Worker
    • Order by Salary
    • Desc Limit n-1, 1;


    • SQL Server:
    • Select Top5 Salary
    • From (
    •    Select Distinct Top N Salary
    •    From Worker Salary
    •    Order By Salary Desc )
    • Order By Asc;
  32. Write an SQL query to determine the 5th highest salary without using Top or Limit method query using the correlated subquery to return the 5th highest salary
    • Select Salary
    • From Worker W1
    • Where 4 = (
    • Select Count( Distinct (W2.Salary ) )
    • From Worker W2 Where W2.Salary >= W1.Salary );
  33. Write an SQL query to fetch the list of Employees with the same Salary
    • select distinct W.Worker_ID, W.First_Name, W.Salary
    • from Worker W, Worker W1
    • where W.Salary = W1.Salary and W.Worker_ID != W1.Worker_ID;
  34. Write an SQL query to show the second highest salary from a table
    • Select max( Salary ) from Worker
    • where Salary not in( Select max(Salary) from Worker);
  35. Write an SQL query to show one row twice in results from a table
    • Select First_Name, Department
    • from worker W where W.Department = 'HR'
    • union all
    • Select First_Name, Department from
    • Worker W1 where W1.Department = 'HR';
  36. Write an SQL query to fetch intersection records of two tables
    • (Select * From Worker)
    • Intersect
    • (Select * WorkerClone);
  37. Write an SQL query to fetch the Departments that have less than 5 people in it
    • select *
    • from Worker
    • where worker_id <= 5
    • union
    • select *
    • from (select * from Worker W order by W.worker_id desc) as W1
    • where W1.worker_id <= 5;
  38. Write an SQL query to show all Departments along with the number of people in there
    • Select Department, Count(Department) as 'Number of Workers'
    • From Worker
    • Group By Department
  39. Write an SQL query to show the last record from a table
    • Select * from Worker
    • where Worker_ID = (Select max(Worker_ID) from worker);
  40. Write an SQL query to fetch the first row of a table
    • Select * from Worker
    • where Worker_ID = (Select min(Worker_ID) from Worker);
  41. Write an SQL query to fetch the last 5 records from a table
    • Select * From Worker Where Worker_ID <= 5
    • Union
    • Select * From (Select * From Worker W order by W.Worker_ID Desc )
    • As W1 Where W1.Worker_ID <= 5;
  42. Write an SQL query to print the name of employees having the highest salary in each department
    • Select t.Department, t.First_Name, t.Salary
    • from(Select max(Salary) as TotalSalary,
    •    Department from Worker group by Department) as TempNew
    • Inner Join Worker t on
    • TempNew.Department = t.Department
    •    and TempNew.TotalSalary = t.Salary;
  43. Write an SQL query to fetch 3 max salaries from a table
    • Select distinct Salary
    • from Worker a
    • Where 3>= (Select count(distinct Salary)
    •    from worker b Where a.Salary <= b.Salary)
    • order by a.Salary desc;
  44. Write an SQL query to fetch Nth max salaries from a table
    • Select distinct Salary
    • from worker a
    • Where n>= (Select count(distinct Salary)
    •    from worker b Where a.Salary <= b.Salary)
    • order by a.Salary desc;
  45. Write an SQL query to fetch departments along with the total salaries paid for each of them
    • Select Department, sum(Salary)
    • From Worker
    • Group by Department;
  46. Write an SQL query to fetch the names of Workers who earn the highest salary
    • Select First_Name, Salary
    • From Worker
    • Where Salary = (Select max(Salary) from worker;
Author
wrathes
ID
350965
Card Set
PCS Interview Coding (Part 2)
Description
coding
Updated