As my journey in becoming a competent data analytics, my SQL knowledge continues to deepen and as a result, I also publish few things pick up through the process. DDL, DML and Data Manipulation. Read more about SQL fundamentals from this link Click here to visit dev.to. Building on SQL skills and data analysis, I have come to know you can work on different tables at the same time through the help of SQL joins and SQL Functions. A JOIN in SQL is used to link or combine rows from two or more tables based on related column between them and it is usually a Primary Key and Foreign Key. JOIN will help you see which student took which course. LEFT JOIN (LEFT OUTER JOIN) This type of returns all records from Left table and matching records from the right table Example: SELECT s.name, c.course_name FROM students s LEFT JOIN courses c ON s.course_id = c.id; All students appear, even if they are not assigned a course. INNER JOIN JOIN returns only matching records from both tables. Example: SELECT s.name, c.course_name FROM students s INNER JOIN courses c ON s.course_id = c.id; RIGHT JOIN (RIGHT OUTER JOIN) JOIN returns all records from the right table and matching ones from the left. Example SELECT s.name, c.course_name FROM students s RIGHT JOIN courses c ON s.course_id = c.id; All courses appear, even if no student is enrolled. FULL JOIN(FULL OUTER JOIN) JOIN returns all records when there is a match in either table. Example: SELECT s.name, c.course_name FROM students s FULL JOIN courses c ON s.course_id = c.id; JOINs are about relationships between tables Without JOINs, databases would be much less powerful What are Window Functions? Example: SELECT name, department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_salary FROM employees; The output from this query, every employee still appears, which also includes department average. COUNT(): The function counts rows present in a given table. Example: SELECT COUNT(*) FROM students; Counts total number of students SUM(): This function is used to add numeric values. Example: SELECT SUM(salary) FROM employees; This will get the total salary. AVERAGE(): The function is used to get Average values like school exam result performance. Example: SELECT AVG(marks) FROM exams; The output will give the average marks. UPPER()/LOWER(): The function is used to get or change text case, UPPER() is used to change text into upper case while LOWER() is used to change text into lower case. Example: SELECT UPPER(first_name), LOWER(last_name) FROM students; The student's first name will be in upper case and second name will be in lower case. NOW()/CURRENT_DATE(): Example: SELECT CURRENT_DATE; In SQL functions are many, the highlighted functions are most common and every beginner should know and understand how they work in order to ease the work as data analysts, data engineer or scientist. What is Subquery in SQL: Subquery is a query written inside another SQL Query, It executes first and its result is used by the outer query. Example: SELECT name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ); The inner query calculates average salary and the outer query compares the employee's salary against average Types of Subquery Scalar Subquery: It is used to return single value. Example: SELECT * FROM products WHERE price > ( SELECT AVG(price) FROM products ); Multiple-row Subquery: This subquery returns multiple rows as its name suggests. Example: SELECT name FROM employees WHERE department_id IN ( SELECT id FROM departments ); Correlated Subquery: This query references one or more columns from the outer(main) query, it depends on the outer query. Example: SELECT e1.name, e1.salary FROM employees e1 WHERE salary > ( SELECT AVG(e2.salary) FROM employees e2 WHERE e1.department = e2.department ); The query compares each employee to the average salary in their department. What is CTE? With clause. Example: same query using a CTE WITH avg_salary AS ( SELECT AVG(salary) AS avg_sal FROM employees ) SELECT name, salary FROM employees WHERE salary > ( SELECT avg_sal FROM avg_salary ); The need for better readability Simply complex queries The need for organized structure If you need to reuse intermediate results CTE with Multiple Steps Example: Monthly sales analysis WITH monthly_sales AS ( SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS revenue FROM sales GROUP BY month ), ranked_sales AS ( SELECT *, RANK() OVER (ORDER BY revenue DESC) AS sales_rank FROM monthly_sales ) SELECT * FROM ranked_sales; This will calculate monthly revenue, rank months by revenue and return final results. SQL Fundamentals JOINs Group BY Window Functions SQL Functions Subqueries CTEs These will be core concepts needed for: Preparing for SQL technical interview Writing quality SQL scripts Dashboard preparation for tools like Ms Power BI Data analysis Since now as a data scientist, data manipulation and analysis is easier now that I have learnt and understood SQL Functions, Window Function and also Joins. Also the use of CTEs and Subquery makes SQL query to be easier to read and organization. Note: If a subquery starts to become difficult to read, convert it into a CTE