PT Ganesh’s Post

Leetcode premium SQL Question Level: Medium. Day 4️⃣4️⃣ of Solving SQL Problem 1️⃣ 1️⃣5️⃣ . Q) 1875. Group Employees of the Same Salary Schema: Create table If Not Exists Employees (employee_id int, name varchar(30), salary int) Truncate table Employees insert into Employees (employee_id, name, salary) values ('2', 'Meir', '3000') insert into Employees (employee_id, name, salary) values ('3', 'Michael', '3000') insert into Employees (employee_id, name, salary) values ('7', 'Addilyn', '7400') insert into Employees (employee_id, name, salary) values ('8', 'Juan', '6100') insert into Employees (employee_id, name, salary) values ('9', 'Kannon', '7400') Solution: WITH CTE AS( SELECT MIN(employee_id) AS id, salary FROM Employees GROUP BY salary HAVING COUNT(employee_id) = 1) SELECT *,DENSE_RANK() OVER(ORDER BY salary) AS team_id FROM Employees E WHERE employee_id NOT IN(SELECT id from CTE) order by team_id, employee_id Your Turn: Feel free to share your own solutions or approaches to this problem in the comments below! Let's learn from each other and grow together. Stay tuned for more SQL challenges and solutions in the coming days. Follow me to keep up with my daily posts! #JobSeeker #sql #sqlserver #tsql #pyspark #dataengineer #tsql #Azuredataengineer #learning #solving #leetcode #sqldaily #ProfessionalDevelopment #RemoteJobs #NewOpportunities #JobSearch

In SQL, we often use Nested SubQueries, i.e. query inside a query. For Example, SELECT * FROM Employees WHERE salary IN ( SELECT min(salary) FROM Employees GROUP BY employee_id ); This would simply run the inner (Nested) query first all at once, and then would run the outer query based on whatever the inner query values are. But what if we are in a situation where we want to run the inner query once for each outer row ? For Example, if there are 7 rows in a Table, the inner query would run 7 times, i.e. once for every outer row query. This is where we would use Correlated SubQueries. Taking the same above example and modifying it a bit, this would become a Correlated SubQuery from a Nested SubQuery - SELECT * FROM Employees AS E1 WHERE salary IN ( SELECT min(salary) FROM Employees AS E2 WHERE E1.employee_id = E2.employee_id GROUP BY employee_id ); I hope this helps someone who might be looking for this. https://lnkd.in/dE4FgMJ6

These are 14 hashtag hashtag #SQL questions that were commonly asked in many interviews I gave while switching from my last company. Sharing these here hope they help → How can you fetch the nth highest salary from a table? What is the use of the GROUP BY clause in SQL? How do you use the HAVING clause in SQL? What is the difference between WHERE and HAVING clauses? How do you join three or more tables in SQL? How do you update multiple rows in a table with a single SQL query? What is the purpose of the COALESCE function in SQL? What is a Common Table Expression (CTE) in SQL? What are the benefits of using CTEs over subqueries? How do you reference a CTE in multiple SELECT statements within the same query? Using a recursive CTE, write a query to generate a list of dates from a start date to an end date. Using a CTE, write a query to find all employees in a company’s hierarchy under a given manager. Write a CTE to flatten a parent-child relationship table into a hierarchical structure with levels. Write a query using a CTE to perform a recursive Fibonacci sequence calculation. Now to prepare for Data Analyst roles just solving questions won’t be enough you need to learn concepts as well and make sure to use the right resources. hashtag hashtag #interviewpreparation hashtag hashtag #dataanalyst

Getting 3rd Highest Salary in SQL: -------------------------------------- How many ways do you know, to get the 3rd highest salary in SQL? https://lnkd.in/dK3uu5Gt #sqlinterview #sql #techpoint #techpointfundamentals

Getting 3rd Highest Salary in SQL: -------------------------------------- How many ways do you know, to get the 3rd highest salary in SQL? https://lnkd.in/dK3uu5Gt #sqlinterview #sql #techpoint #techpointfundamentals

Advanced SQL

Hello,Connections! 🔴 𝐅𝐫𝐞𝐪𝐮𝐞𝐧𝐭𝐥𝐲 𝐚𝐬𝐤𝐞𝐝 𝐒𝐐𝐋 𝐇𝐚𝐧𝐝𝐬-𝐨𝐧 𝐪𝐮𝐞𝐬𝐭𝐢𝐨𝐧𝐬 𝐟𝐨𝐫 𝐃𝐚𝐭𝐚 𝐀𝐧𝐚𝐥𝐲𝐬𝐭/𝐃𝐚𝐭𝐚 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐫𝐨𝐥𝐞- - Difference between dense_rank and rank? - Difference between partition by and group by? - Which is faster joins or subquery or cte? - Find count of duplicate rows in table? - How do you find the top 5 customers by sales? - Remove duplicate rows in table? - Find the 2nd highest salary? - Find the Department wise highest salaries? - Outputs of Left, Right, cross joins? 🔴 𝐅𝐫𝐞𝐪𝐮𝐞𝐧𝐭𝐥𝐲 𝐚𝐬𝐤𝐞𝐝 𝐒𝐐𝐋 𝐭𝐡𝐞𝐨𝐫𝐲 𝐛𝐚𝐬𝐞 𝐪𝐮𝐞𝐬𝐭𝐢𝐨𝐧𝐬 𝐟𝐨𝐫 𝐃𝐚𝐭𝐚 𝐀𝐧𝐚𝐥𝐲𝐬𝐭/𝐃𝐚𝐭𝐚 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐫𝐨𝐥𝐞- - What is SQL and what are its main features? - Order of writing SQL query? - Order of execution of SQL query? - What are some of the most common SQL commands? - What’s a primary key & foreign key? - All types of joins and questions on their outputs? - Explain all window functions and difference between them? - What is stored procedure? - Difference between stored procedure & Functions in SQL? - What is trigger in SQL? - Difference between where and having? ⏩ 𝐉𝐨𝐢𝐧 𝐦𝐲 𝐓𝐞𝐥𝐞𝐠𝐫𝐚𝐦 𝐂𝐡𝐚𝐧𝐧𝐞𝐥 𝐭𝐨 𝐥𝐞𝐚𝐫𝐧 𝐃𝐚𝐭𝐚 𝐒𝐜𝐢𝐞𝐧𝐜𝐞 & 𝐀𝐧𝐚𝐥𝐲𝐭𝐢𝐜𝐬: https://t.me/LK_Data_world ⏩ If you found this post informative, save and repost it🔁.. ⏩ follow for more Lovee Kumar 🛎 #sql #sqlserver #sqldatabase #sqlskills #cheatsheet #sqlprogramming #sqllearning #datascience #dataanalyst #interview #linkedinconnections

#SQL Explain the difference between a correlated subquery and a non-correlated subquery in SQL. Answer: Correlated Subquery: - A correlated subquery is a subquery that refers to a column from the outer query. - It executes once for every row processed by the outer query. - It can be used to filter or compare values based on values from the outer query. Example: SELECT name FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department_id = e.department_id ); In this example, the subquery SELECT AVG(salary) FROM employees WHERE department_id = e.department_id is correlated because it references e.department_id from the outer query. Non-correlated Subquery: - A non-correlated subquery is an independent subquery that can execute on its own without relying on the outer query. - It executes only once and returns a single value or set of values. - It can be used independently within a query. Example: SELECT name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ); In this example, the subquery SELECT AVG(salary) FROM employees is non-correlated because it does not reference any columns from the outer query. Tip: Correlated subqueries are generally executed row by row, making them less efficient than non-correlated subqueries. Use non-correlated subqueries when you need to retrieve independent results, and use correlated subqueries when you need to filter results based on conditions from the outer query. #helpEachOtherAlways Thanks Ganesh ch

Hello!!! Part 18 of SQL!!! ❓STORED PROCEDURE 💡A stored procedure is a set of SQL statements that are stored in the database server and can be executed by calling the procedure name. It's like a reusable script or program stored in the database that can perform a specific task or set of tasks. 📍Syntax: CREATE PROCEDURE procedure_name [ @parameter1 data_type [ = default_value ] [ OUT | OUTPUT ] ] [ , @parameter2 data_type [ = default_value ] [ OUT | OUTPUT ] ] [ , ... ] AS BEGIN -- SQL statements to be executed -- END; 📍Example: Create a stored procedure named "GetEmployeeDetails" that retrieves employee details based on the employee ID: CREATE PROCEDURE GetEmployeeDetails   @EmployeeID INT AS BEGIN  SELECT EmployeeID, FirstName, LastName, Department, Salary  FROM Employees  WHERE EmployeeID = @EmployeeID; END; EXEC GetEmployeeDetails @EmployeeID = 1001; 💡BENEFITS:  • Improves Performance • Easy to modify • Reduce Network Traffic • Enhance the Security • Reusable . . Follow my page Krishna Sameera Kota for regular updates. Feel free to share more points or thoughts in the comments. Thankyou! Have a nice day! #learntogether #sql #sqlinterview #sqlskills #database #datanalytics #dataanalyst #sqlupdates #storedprocedure #syntax #careerdevelopment #businessanalyst

🚀 SQL Challenge Q7.The table named EMP has eight columns EMPNO NUMBER(4) NOT NULL: A column named EMPNO that can store up to 4-digit numbers. It cannot be empty. ENAME VARCHAR2(10): A column named ENAME that can store up to 10 characters for the employee's name. JOB VARCHAR2(9): A column named JOB that can store up to 9 characters for the employee's job title. MGR NUMBER(4): A column named MGR that can store up to 4-digit numbers for the manager's ID. HIREDATE DATE: A column named HIREDATE to store the date when the employee was hired. SAL NUMBER(7, 2): A column named SAL to store the employee's salary, with up to 7 digits in total, including 2 decimal places. COMM NUMBER(7, 2): A column named COMM to store the employee's commission, with up to 7 digits in total, including 2 decimal places. DEPTNO NUMBER(2): A column named DEPTNO that can store up to 2-digit numbers for the department number. PRIMARY KEY (EMPNO): This makes sure that each EMPNO value is unique and not empty, serving as the primary identifier for each row in the table. A. WRITE A QUERY TO DISPLAY DEPTNO's IN WHICH DEPTNO MORE THAN 3 EMPLOYEES? B. WRITE A QUERY TO DISPLAY JOB's IN WHICH JOB THE SUM OF SALARY IS LESS THAN 5000? Difficulty Level : Easy #sql #sqlpractice #sqlquestions #sqlquery #sqlchallenge #sqldatabase