SQL | Data Analytics

Two-part article about using #Git, version control, and #CI/CD, starting from a single team member and a few SQL queries, and scaling up to a larger organization with a bigger team and different dynamics. The highlighted challenges and best practices from software engineering that can be applied by data analysts to improve their work and strive for engineering excellence. Part 1: https://nastengraph.substack.com/p/part-1-how-to-work-with-sql-queries Part 2: https://nastengraph.substack.com/p/part-2-how-to-work-with-sql-queries #analytics #dataengineer #dataanalyst #sql #Surfalytics

SQL tips and tricks A set of basic #SQL tips. Useful for beginners, experienced developers can add their tips to this repository.

🚀IBM is offering a free 10-hour course called "Data Analyst Career Guide and Interview Preparation." Designed for entry-level data analysts, this course provides essential insights into the job market and the employment process. What you will learn: 📍Part 1: 1. How to create a compelling resume and portfolio. 2. Techniques for presenting yourself effectively. 3. Understanding the various types of data analyst roles. 📍Part 2: 1. How to research companies where you'd like to interview. 2. Evaluating job postings to find the right fit. 3. Rehearsing and preparing for interviews. 📍Part 3: 1. Insights into how the interview process works. 2. Tips for handling coding challenges. 3. Common interview questions and how to answer them. 4. What steps to take after the interview. You can access the course here. #sql #data #analyst

🚀 MAX_BY() - One of the most useful SQL functions MAX_BY() is similar to MAX(), but it helps simplify queries that involve grouping. Here's how it works: - MAX(price) — returns the largest value in the "price" column. - MAX_BY(vegetable, price) — returns the value in the "vegetable" column where the price = MAX(price). 💡 Without MAX_BY(), your query would be more complicated! To find the name of the vegetable with the highest price, you'd have to use a subquery in the WHERE clause, which is harder to read. ⚠️ Important note: If there’s a tie, MAX_BY() will only return one row, but using a WHERE clause could return multiple rows. #dataengineering #sql #dataanalyst

💡Difference between UNION and UNION ALL in SQL When working with SQL, both UNION and UNION ALL are used to combine the results of two or more SELECT queries. However, they have key differences in how they handle duplicate rows and performance. ⚙️ 1. UNION: - Duplicates Removed: UNION eliminates any duplicate rows in the final result set. After combining the results of the SELECT statements, it performs a sorting operation to remove any duplicates. - Performance: Since UNION has to perform the additional step of checking for and removing duplicates, it is generally slower compared to UNION ALL, especially with large datasets. 🚀 2. UNION ALL: - All Rows Included: UNION ALL does not remove duplicates. It simply combines the results of the SELECT statements and returns all rows, including duplicates. - Performance: Since it skips the duplicate-checking process, UNION ALL is typically faster and more efficient, especially when you know there are no duplicates or you want to keep them. 📍 When to use: - Use UNION when you want to remove duplicates and get a distinct result set. - Use UNION ALL when performance is important, and you don’t care about duplicates, or you are sure there won’t be any duplicates.

💻 String Functions in SQL If you're working with databases, string manipulation is a must have! Here is a quick overview of common SQL string functions 👇 --- 📝 1. CONCAT() - Description: Concatenates two or more strings. - Syntax: SELECT CONCAT(string1, string2, ...) AS concatenated_string - Example: SELECT CONCAT(first_name, ' ', last_name) AS full_name --- 📝 2. SUBSTRING()/SUBSTR() - Description: Extracts a substring from a string. - Syntax: SELECT SUBSTRING(string FROM start_position FOR length) AS substring - Example: SELECT SUBSTRING(product_name FROM 1 FOR 5) AS short_name --- 📝 3. CHAR_LENGTH()/LENGTH() - Description: Returns the length of a string. - Syntax: SELECT CHAR_LENGTH(string) AS length - Example: SELECT CHAR_LENGTH(product_name) AS product_name_length --- 📝 4. UPPER() - Description: Converts all characters to uppercase. - Syntax: SELECT UPPER(string) AS uppercase_string - Example: SELECT UPPER(first_name) AS upper_name --- 📝 5. LOWER() - Description: Converts all characters to lowercase. - Syntax: SELECT LOWER(string) AS lowercase_string - Example: SELECT LOWER(last_name) AS lower_name --- 📝 6. TRIM() - Description: Removes specified prefixes/suffixes or whitespace from a string. - Syntax: SELECT TRIM([LEADING | TRAILING | BOTH] characters FROM string) AS trimmed_string - Example: SELECT TRIM(TRAILING ' ' FROM full_name) AS trimmed_name --- 📝 7. LEFT() - Description: Returns a specified number of characters from the left of a string. - Syntax: SELECT LEFT(string, num_characters) AS left_string - Example: SELECT LEFT(product_name, 5) AS left_product_name --- 📝 8. RIGHT() - Description: Returns a specified number of characters from the right of a string. - Syntax: SELECT RIGHT(string, num_characters) AS right_string - Example: SELECT RIGHT(order_number, 4) AS right_order_number --- 📝 9. REPLACE() - Description: Replaces occurrences of a substring within a string. - Syntax: SELECT REPLACE(string, old_substring, new_substring) AS replaced_string - Example: SELECT REPLACE(description, 'old', 'new') AS updated_description #SQL #StringFunctions #Database #SQLCheatSheet

#SQL joins

Easy to understand

Difference between DELETE, TRUNCATE, and DROP in SQL 1. `DELETE`: Removes specific rows. Slower for large tables but can be rolled back within a transaction. Use when you need to delete targeted data.

   DELETE FROM employees WHERE department = 'Sales';
   

2. `TRUNCATE`: Quickly removes all rows from a table. Cannot be rolled back. Faster than DELETE, but resets auto-increment values.

   TRUNCATE TABLE employees;
   

3. `DROP`: Completely deletes the table, including its structure and data. Irreversible and removes all dependent objects.

   DROP TABLE employees;

🔥Looking for a fun and engaging way to sharpen your SQL skills? 🚩Check out SQL Murder Mystery! This interactive website turns learning SQL into an exciting detective game where you use your SQL abilities to solve a thrilling murder mystery. 💡Ready to become a data detective?

True story =)

5⃣ frequently Asked SQL Interview Questions with Answers in data analyst interviews 📍1. Write a SQL query to find the average purchase amount for each customer. Assume you have two tables: Customers (CustomerID, Name) and Orders (OrderID, CustomerID, Amount).

SELECT c.CustomerID, c. Name, AVG(o.Amount) AS AveragePurchase FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c. Name;

📍2. Write a query to find the employee with the minimum salary in each department from a table Employees with columns EmployeeID, Name, DepartmentID, and Salary.

SELECT e1.DepartmentID, e1.EmployeeID, e1 .Name, e1.Salary FROM Employees e1 WHERE Salary = (SELECT MIN(Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID);

📍3. Write a SQL query to find all products that have never been sold. Assume you have a table Products (ProductID, ProductName) and a table Sales (SaleID, ProductID, Quantity).

SELECT p.ProductID, p.ProductName FROM Products p LEFT JOIN Sales s ON p.ProductID = s.ProductID WHERE s.ProductID IS NULL;

📍4. Given a table Orders with columns OrderID, CustomerID, OrderDate, and a table OrderItems with columns OrderID, ItemID, Quantity, write a query to find the customer with the highest total order quantity.

SELECT o.CustomerID, SUM(oi.Quantity) AS TotalQuantity FROM Orders o JOIN OrderItems oi ON o.OrderID = oi.OrderID GROUP BY o.CustomerID ORDER BY TotalQuantity DESC LIMIT 1;

📍5. Write a SQL query to find the earliest order date for each customer from a table Orders (OrderID, CustomerID, OrderDate).

SELECT CustomerID, MIN(OrderDate) AS EarliestOrderDate FROM Orders GROUP BY CustomerID;