SQL Coding Question?

 1. SQL Query to find the second highest salary of Employee?

select * from (SELECT * FROM Employee order by salary desc limit N) as temp order by salary asc limit 1

             

select * from (select id, salary, dense_rank() over(order by salary desc)as ab from Employee) as Emp where ab = N;

select * from (select id, salary, row_number() over(order by salary desc)as ab from Employee) as Emp where ab = N;

  select * from (select id, salary, rank() over(order by salary desc)as ab from Employee) as Emp where ab = N;

 

select * from Employee where salary = (select Min(salary) from(select * from Employee order by salary desc limit N)as Emp) 

2. SQL Query to find Max Salary from each department?

Query using Fetch details from Employee table

              SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID.

Query using Fetch details from Employee and Department table to print Department Name

              SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID GROUP BY DeptName;

 

3. Write SQL Query to display the current date?

 

              SELECT GetDate(); //SQL

              SELECT CURRENT_DATE ; //Postgres SQL

             

4. Write an SQL Query to find the number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975?

              SELECT COUNT(*), sex FROM Employees WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975' GROUP BY sex;

 

5. Write an SQL Query to find an employee whose salary is equal to or greater than 10000?

              SELECT EmpName FROM  Employees WHERE  Salary>=10000;

             

6. Write an SQL Query to find the name of an employee whose name Start with ‘M’?

              SELECT * FROM Employees WHERE EmpName like 'M%';

 

7. Write to find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe?

              SELECT * from Employees WHERE UPPER(EmpName) like '%JOE%';

 

8. Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?

              SELECT id, COUNT(id) FROM Employee GROUP BY id HAVING COUNT(id) > 1

             

              SELECT [FirstName], [LastName], [Country], COUNT(*) AS CNT FROM
              [SampleDB].[dbo].[Employee]
              GROUP BY [FirstName], [LastName], [Country] HAVING COUNT(*) > 1;

             

            DELETE FROM [SampleDB].[dbo].[Employee] WHERE ID NOT IN
            (
            SELECT MAX(ID) AS MaxRecordID FROM [SampleDB].[dbo].[Employee] 

            GROUP BY   [FirstName], [LastName], [Country]
            );

             

9. In SQL, I have 3 tables Employee, Department and Hobby. I need to write sql query employee belongs to account Department who has WatchingMovie as   hobby   ?

 

              SELECT Employee.* FROM Employee

              JOIN Department ON Employee.DepartmentID = Department.DepartmentID

              JOIN Hobby ON Employee.EmployeeID = Hobby.EmployeeID

              WHERE Department.DepartmentName = 'Account' AND Hobby.HobbyName = 'WatchingMovie';

 

10.Write Sql Query to find maximum marks wise subject and Class wise?

              SELECT s.Class, s.Subject, s.StudentName, s.Marks FROM Student s
              INNER JOIN (SELECT Class, Subject, MAX(Marks) AS MaxMarks FROM 

              Student GROUP BY Class, Subject) m ON s.Class = m.Class 

              AND s.Subject = m.Subject AND s.Marks = m.MaxMarks;

              SELECT Subject, Name, Marks FROM Student

              WHERE (Subject, Marks) IN (SELECT Subject, MAX(Marks) FROM Student 

              GROUP BY Subject);

11. There is a table which contains two columns Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above-average students?

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

 

12. To find the names and salaries of all employees who make more than the average salary?

              SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees)

             

13. To find the names of all customers who have placed more than 10 orders in the past year?

              SELECT customers.name FROM customers

              JOIN orders ON customers.id = orders.customer_id

              WHERE orders.order_date >= DATE_SUB(NOW(), INTERVAL 1 YEAR)

              GROUP BY customers.id HAVING COUNT(*) > 10

             

14. To Display Country and number of different States in the country?

              SELECT Country, count(DISTINCT State) FROM table1 GROUP BY Country

             

15. To Display the Names of customers for which the Price is greater than the average Price of all customers?

              SELECT Name, sum(Price) FROM table1 GROUP BY Name

              HAVING sum(Price) > (SELECT avg(Price) FROM table1)

             

16. To Display the Name of the customer and sum of Price for the year 2019?

              SELECT Name, sum(price) FROM table1

              WHERE strftime('%Y',OrderDate) = "2019" GROUP BY Name

             

17. To Display the 5 highest Prices grouped by year?

              SELECT strftime('%Y',OrderDate) AS "Year", sum(price) FROM table1 GROUP by Year

              ORDER BY sum(Price) DESC LIMIT 5

 

18. To Display 2 to 6 highest Prices (Leave the top price)?

              SELECT Name, dense_rank() OVER (ORDER by Price DESC) ranker,

    Price FROM (SELECT Name, Price FROM table1 ORDER BY Price LIMIT 6)

              LIMIT 5

             

19. To Write a query to select all employees who make more than $50,000 per year?

              SELECT name, salary FROM employees WHERE salary > 50000;

 

20. To write a query to select the name and price of the most expensive product in each category?

SELECT c.name AS category_name, p.name AS product_name, p.price

FROM products p

              INNER JOIN categories c ON p.category_id = c.category_id

              WHERE p.price = (

                SELECT MAX(price)

                FROM products p2

                WHERE p2.category_id = p.category_id

              ) ORDER BY c.name;

             

21. To write a query to select the total revenue generated in each month of the past year?

              SELECT DATE_FORMAT(date, '%Y-%m') AS month, SUM(amount) AS revenue FROM sales

              WHERE date >= DATE_SUB(NOW(), INTERVAL 1 YEAR)

              GROUP BY month       ORDER BY month;

             

22. To write a query to select the top 10 best-selling books of all time?

              SELECT title, author, num_copies_sold FROM books

              ORDER BY num_copies_sold DESC LIMIT 10;

 

23. To write a query to select the names of all students who are older than 25 and majoring in computer science?

              SELECT name FROM students

              WHERE age > 25 AND major = 'computer science';

 Q24. How to remove duplicate rows in SQL?
If the SQL table has duplicate rows, the duplicate rows must be removed.

ID	Name	Age
1	A	21
2	B	23
2	B	23
4	D	22
5	E	25
6	G	26
5	E	25

The following SQL query removes the duplicate ids from the  table:

DELETE FROM table WHERE ID IN (
SELECT 
ID, COUNT(ID) 
FROM   table
GROUP BY  ID
HAVING 
COUNT (ID) > 1); 

Q25. How can you fetch alternate records from a table?
You can fetch alternate records i.e both odd and even row numbers. 

For example- To display even numbers, use the following command:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0

Now, to display odd numbers:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1

Q26. Name the operator which is used in the query for pattern matching?
LIKE operator is used for pattern matching, and it can be used as -.

1. % – It matches zero or more characters.                                                                                        select * from students where studentname like ‘a%’
2. _ (Underscore) – it matches exactly one character.                                                           select * from student where studentname like ‘abc_’

Q27. How can you select unique records from a table?
You can select unique records from a table by using the DISTINCT keyword.
Select DISTINCT studentID from Student

Q28. How can you fetch first 5 characters of the string?
There are a lot of ways to fetch characters from a string. For example:
Select SUBSTRING(StudentName,1,5) as studentname from student