Saranya Devi Subramani’s Post

🚀 SQL Challenge Q16 .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 TOP 3 LOWEST SALARIES EMPLOYEE DETAILS FROM EMP TABLE? B.WRITE A QUERY TO DISPLAY DEPARTMENT DETAILS IN WHICH DEPARTMENT EMPLOYEES ARE WORKING ? C.WRITE A QUERY TO DISPLAY DEPARTMENT DETAILS IN WHICH DEPARTMENT EMPLOYEES ARE NOT WORKING ? Difficulty Level : Medium #Oraclesql #SQL #sqlpractice #sqldatabase #mysql #sqlchallenge #sqldatabae #practice #Challenge

📊💻 Excited to share a SQL practice problem that covers all the basics! Whether you're just starting out or looking to refresh your SQL skills, this exercise has got you covered. Check it out below and feel free to give it a try! #SQL #PracticeProblem #DatabaseSkills 🔍 SQL Problem: Employee Management System 🔍 You've been tasked with managing an employee database for a company. Your goal is to write SQL queries to perform various operations on the database. -->Database Schema: 1. Employees: - Columns: id (INT), name (VARCHAR), department (VARCHAR), salary (DECIMAL) 2. Departments: - Columns: id (INT), name (VARCHAR) -->Tasks to Perform: 1. Retrieve all columns from the "employees" table. 2. Retrieve the names of all employees who are in the Sales department. 3. Retrieve the names and salaries of employees in the Marketing department, sorted by salary in descending order. 4. Calculate the average salary of all employees. 5. Retrieve the names of employees and the names of their respective departments from the "employees" and "departments" tables, joining them based on the department ID. 6. Find the total number of employees in each department. 7. Retrieve the names of employees who earn more than the average salary. 8. Increase the salary of all employees in the Sales department by 10%. 9. Add a new employee to the "employees" table. 10. Remove all employees who have a salary less than $30,000. Instructions: 1. Write SQL queries to solve each task. 2. You can assume the database is already set up with sample data. 3. Share your solutions in the comments section. Happy querying! 💻📊

🚀 SQL Challenge Q13 .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 employees whose salary is more than any "salesman" salary? B.Write a Query to Display employees whose salary is more than all "salesman" salaries? Difficulty Level : Easy #Oraclesql #SQL #sqlpractice #sqldatabase #mysql #sqlchallenge #sqldatabase

Handling NULL Values in SQL: An Example Let's take a simple employee table: CREATE TABLE emp ( emp_id INT, name VARCHAR(100), position VARCHAR(100), salary DECIMAL(10, 2) ); INSERT INTO emp (emp_id, name, position, salary) VALUES (1, 'John Doe', 'Manager', 6000.00), (2, 'Jane Smith', 'Developer', 4500.00), (3, 'Sam Wilson', 'Analyst', NULL), (4, 'Alice Johnson', 'HR', 3800.00), (5, 'Bob Brown', 'Developer', NULL); Notice that two employees, Sam Wilson and Bob Brown, have NULL salaries Now, let’s see what happens when we query the table: SELECT * FROM emp WHERE salary = NULL; This query will return no results because in SQL, NULL is not considered a value but a placeholder for unknown or missing data. Using = to compare with NULL doesn't work, as NULL isn't a concrete value. To retrieve records where salary is NULL, we use: SELECT * FROM emp WHERE salary IS NULL; This query will return employees with NULL salaries—Sam Wilson and Bob Brown in this case. Key takeaway: When dealing with NULL values in SQL, always use the IS NULL condition instead of = for accurate results. Mohammad Tanweer khan Karthik K. #SQL #Hive #Dataengineering

🚀 SQL Challenge Q9 .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 EMPLOYEES WHOSE SALARY IS SAME AS THE EMPLOYEE "SCOTT" SALARY? B.WRITE A QUERY TO DISPLAY MANAGERS AND THEIR EMPLOYEES? Difficulty Level : Medium #Oraclesql #SQL #sqlpractice #sqldatabase #mysql #sqlchallenge #sqldatabase

#SQL Here are some widely used SQL window functions along with brief descriptions of their usage: 1. ROW_NUMBER(): - Usage: Assigns a unique sequential integer to rows within a partition. SELECT employee_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num FROM employees; 2. RANK(): - Usage: Assigns ranks to rows within a partition, with gaps for tied rows. SELECT employee_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees; 3. DENSE_RANK(): - Usage: Similar to RANK(), but without gaps between ranks for tied rows. SELECT employee_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank FROM employees; 4. LAG(): - Usage: Accesses data from a previous row within the same partition. SELECT employee_id, salary, LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date) AS prev_salary FROM employees; 5. LEAD(): - Usage: Accesses data from a subsequent row within the same partition. SELECT employee_id, salary, LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date) AS next_salary FROM employees; Thanks #helpingEachOtherAlways

🚀 SQL Challenge Q6.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  NO.OF EMPLOYEES WORKING IN EACH JOB ALONG WITH DEPTNO WISE? B. WAQ TO DISPLAY NO.OF EMPLOYEES,SUM OF SALARIES,AVERAGE SALARY,MINIMUM AND MAXIMUM SALARY FROM EACH DEPTNO WISE? Difficulty Level : Easy #sql #sqlpractice #sqlquestions #sqlquery #sqlchallenge #sqldatabase

#17/100 SQL PROBLEM STATEMENT: Write a SQL query to find employees who earn the top three salaries in each of the department. For the above  tables,your SQL query should return the following rows (order of rows does not matter). CREATE TABLE Department (   Id INT PRIMARY KEY,   Name VARCHAR(50) ); INSERT INTO Department (Id, Name) VALUES (1, 'IT'), (2, 'Sales'); CREATE TABLE Employee (   Id INT PRIMARY KEY,   Name VARCHAR(50),   Salary INT,   DepartmentId INT,   FOREIGN KEY (DepartmentId) REFERENCES Department(Id) ); INSERT INTO Employee (Id, Name, Salary, DepartmentId) VALUES (1, 'Joe', 85000, 1), (2, 'Henry', 80000, 2), (3, 'Sam', 60000, 2), (4, 'Max', 90000, 1), (5, 'Janet', 69000, 1), (6, 'Randy', 85000, 1), (7, 'Will', 70000, 1); WITH CTE1 AS (   SELECT      D.Name AS DepartmentName,     E.Name AS EmployeeName,     E.Salary,     DENSE_RANK() OVER (PARTITION BY E.DepartmentId ORDER BY E.Salary DESC) AS rankEmployee   FROM      Employee E   JOIN      Department D ON E.DepartmentId = D.Id ) SELECT    DepartmentName,   EmployeeName,   Salary FROM    CTE1 WHERE    rankEmployee <= 3; #SQL #SQLCHALLENGE #SQLDAILY #ANALYTICS #BETTEREVERYDAY

#20DaysOfSQLChallenge #Day_8: Understanding SQL Aggregate Functions (COUNT, SUM, AVG) 𝐖𝐡𝐚𝐭 𝐚𝐫𝐞 𝐒𝐐𝐋 𝐀𝐠𝐠𝐫𝐞𝐠𝐚𝐭𝐞 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬? SQL aggregate functions operate on a set of values and return a single value as the result. These functions are often used with the GROUP BY and HAVING clause to group the result set by one or more columns. An aggregate function ignores NULL values when it performs the calculation, except for the count function. Here are some common SQL aggregate functions: ✨ 1. 𝐂𝐎𝐔𝐍𝐓(): The COUNT() function returns the number of rows that matches a specified condition in a database table. 𝐐𝐮𝐞𝐫𝐲 1: 𝑺𝑬𝑳𝑬𝑪𝑻 𝑪𝑶𝑼𝑵𝑻(*) 𝑨𝑺 𝑻𝒐𝒕𝒂𝒍𝑬𝒎𝒑𝒍𝒐𝒚𝒆𝒆𝒔      𝑭𝑹𝑶𝑴 𝑬𝒎𝒑𝒍𝒐𝒚𝒆𝒆;  This query returns the total number of rows in the Employee table. The COUNT(*) function counts all rows in the table, regardless of whether any specific column has NULL values or not. In this case, TotalEmployees is the alias given to the result column. 𝐐𝐮𝐞𝐫𝐲 2: 𝑺𝑬𝑳𝑬𝑪𝑻 𝑫𝒆𝒑𝒂𝒓𝒕𝒎𝒆𝒏𝒕𝑰𝑫, 𝑪𝑶𝑼𝑵𝑻(*) 𝑨𝑺 𝑻𝒐𝒕𝒂𝒍𝑬𝒎𝒑𝒍𝒐𝒚𝒆𝒆𝒔        𝑭𝑹𝑶𝑴 𝑬𝒎𝒑𝒍𝒐𝒚𝒆𝒆        𝑮𝑹𝑶𝑼𝑷 𝑩𝒀 𝑫𝒆𝒑𝒂𝒓𝒕𝒎𝒆𝒏𝒕𝑰𝑫        𝑯𝑨𝑽𝑰𝑵𝑮 𝑪𝑶𝑼𝑵𝑻(*) > 5;  This query returns the total number of employees in each department where the count is greater than 5, using the HAVING clause to filter the results. ✨ 2. 𝐒𝐔𝐌(): The SUM() function returns the total sum of a numeric column. 𝐐𝐮𝐞𝐫𝐲 1: 𝑺𝑬𝑳𝑬𝑪𝑻 𝑺𝑼𝑴(𝑺𝒂𝒍𝒂𝒓𝒚) 𝑨𝑺 𝑻𝒐𝒕𝒂𝒍𝑺𝒂𝒍𝒂𝒓𝒚          𝑭𝑹𝑶𝑴 𝑬𝒎𝒑𝒍𝒐𝒚𝒆𝒆;  This query calculates the total sum of the Salary column in the Employee table. It adds up all the values in the Salary column and returns the result as TotalSalary. 𝐐𝐮𝐞𝐫𝐲 2: 𝑺𝑬𝑳𝑬𝑪𝑻 𝑫𝒆𝒑𝒂𝒓𝒕𝒎𝒆𝒏𝒕𝑰𝑫, 𝑺𝑼𝑴(𝑺𝒂𝒍𝒂𝒓𝒚) 𝑨𝑺 𝑻𝒐𝒕𝒂𝒍𝑺𝒂𝒍𝒂𝒓𝒚        𝑭𝑹𝑶𝑴 𝑬𝒎𝒑𝒍𝒐𝒚𝒆𝒆        𝑮𝑹𝑶𝑼𝑷 𝑩𝒀 𝑫𝒆𝒑𝒂𝒓𝒕𝒎𝒆𝒏𝒕𝑰𝑫        𝑯𝑨𝑽𝑰𝑵𝑮 𝑺𝑼𝑴(𝑺𝒂𝒍𝒂𝒓𝒚) > 100000;  This query calculates the total salary expenditure for each department and returns only those departments where the total salary is greater than 100000, using the HAVING clause to filter the results. ✨ 3. 𝐀𝐕𝐆(): The AVG() function calculates the average of a set of values. 𝐐𝐮𝐞𝐫𝐲 1: 𝑺𝑬𝑳𝑬𝑪𝑻 𝑫𝒆𝒑𝒂𝒓𝒕𝒎𝒆𝒏𝒕𝑰𝑫, 𝑨𝑽𝑮(𝑺𝒂𝒍𝒂𝒓𝒚) 𝑨𝑺 𝑨𝒗𝒆𝒓𝒂𝒈𝒆𝑺𝒂𝒍𝒂𝒓𝒚       𝑭𝑹𝑶𝑴 𝑬𝒎𝒑𝒍𝒐𝒚𝒆𝒆       𝑮𝑹𝑶𝑼𝑷 𝑩𝒀 𝑫𝒆𝒑𝒂𝒓𝒕𝒎𝒆𝒏𝒕𝑰𝑫       𝑯𝑨𝑽𝑰𝑵𝑮 𝑨𝑽𝑮(𝑺𝒂𝒍𝒂𝒓𝒚) > 55000; This query calculates the average salary for each department and returns only those departments where the average salary is greater than 55000, using the HAVING clause to filter the results. These functions can provide valuable insights into the data stored in the Employee table. #sql #dbms #day8 #aggregateFunctionsInSQL #count #sum #avg

🚀 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