𝗠𝗼𝘀𝘁 𝗔𝘀𝗸𝗲𝗱 𝗦𝗤𝗟 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻𝘀 𝗮𝘁 𝗠𝗔𝗔𝗡𝗚 𝗖𝗼𝗺𝗽𝗮𝗻𝗶𝗲𝘀🔥🔥
1. How do you retrieve all columns from a table?
SELECT * FROM table_name;
2. What SQL statement is used to filter records?
SELECT * FROM table_name
WHERE condition;
The WHERE clause is used to filter records based on a specified condition.
3. How can you join multiple tables? Describe different types of JOINs.
SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column;
Types of JOINs:
1. INNER JOIN: Returns records with matching values in both tables
SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;
2. LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table. Unmatched records will have NULL values.
SELECT * FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table. Unmatched records will have NULL values.
SELECT * FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
4. FULL JOIN (or FULL OUTER JOIN): Returns records when there is a match in either left or right table. Unmatched records will have NULL values.
SELECT * FROM table1
FULL JOIN table2 ON table1.column = table2.column;
4. What is the difference between WHERE and HAVING clauses?
WHERE: Filters records before any groupings are made.
SELECT * FROM table_name
WHERE condition;
HAVING: Filters records after groupings are made.
SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > value;
5. How do you count the number of records in a table?
SELECT COUNT(*) FROM table_name;
This query counts all the records in the specified table.
6. How do you calculate average, sum, minimum, and maximum values in a column?
Average: SELECT AVG(column_name) FROM table_name;
Sum: SELECT SUM(column_name) FROM table_name;
Minimum: SELECT MIN(column_name) FROM table_name;
Maximum: SELECT MAX(column_name) FROM table_name;
7. What is a subquery, and how do you use it?
Subquery: A query nested inside another query
SELECT * FROM table_name
WHERE column_name = (SELECT column_name FROM another_table WHERE condition);
Till then keep learning and keep exploring 🙌
1. How do you retrieve all columns from a table?
SELECT * FROM table_name;
2. What SQL statement is used to filter records?
SELECT * FROM table_name
WHERE condition;
The WHERE clause is used to filter records based on a specified condition.
3. How can you join multiple tables? Describe different types of JOINs.
SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column;
Types of JOINs:
1. INNER JOIN: Returns records with matching values in both tables
SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;
2. LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table. Unmatched records will have NULL values.
SELECT * FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table. Unmatched records will have NULL values.
SELECT * FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
4. FULL JOIN (or FULL OUTER JOIN): Returns records when there is a match in either left or right table. Unmatched records will have NULL values.
SELECT * FROM table1
FULL JOIN table2 ON table1.column = table2.column;
4. What is the difference between WHERE and HAVING clauses?
WHERE: Filters records before any groupings are made.
SELECT * FROM table_name
WHERE condition;
HAVING: Filters records after groupings are made.
SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > value;
5. How do you count the number of records in a table?
SELECT COUNT(*) FROM table_name;
This query counts all the records in the specified table.
6. How do you calculate average, sum, minimum, and maximum values in a column?
Average: SELECT AVG(column_name) FROM table_name;
Sum: SELECT SUM(column_name) FROM table_name;
Minimum: SELECT MIN(column_name) FROM table_name;
Maximum: SELECT MAX(column_name) FROM table_name;
7. What is a subquery, and how do you use it?
Subquery: A query nested inside another query
SELECT * FROM table_name
WHERE column_name = (SELECT column_name FROM another_table WHERE condition);
Till then keep learning and keep exploring 🙌
👍52❤9🤣1
After learning SQL 😂👇
https://www.instagram.com/reel/C-RnCrTSrqn/?igsh=MWk4cnVqa2VxZXZpaw==
https://www.instagram.com/reel/C-RnCrTSrqn/?igsh=MWk4cnVqa2VxZXZpaw==
🤣10👍7
Complete 14-day roadmap to learn SQL learning:
Day 1: Introduction to Databases
- Understand the concept of databases and their importance.
- Learn about relational databases and SQL.
- Explore the basic structure of SQL queries.
Day 2: Basic SQL Syntax
- Learn SQL syntax: statements, clauses, and keywords.
- Understand the SELECT statement for retrieving data.
- Practice writing basic SELECT queries with conditions and filters.
Day 3: Retrieving Data from Multiple Tables
- Learn about joins: INNER JOIN, LEFT JOIN, RIGHT JOIN.
- Understand how to retrieve data from multiple tables using joins.
- Practice writing queries involving multiple tables.
Day 4: Aggregate Functions
- Learn about aggregate functions: COUNT, SUM, AVG, MIN, MAX.
- Understand how to use aggregate functions to perform calculations on data.
- Practice writing queries with aggregate functions.
Day 5: Subqueries
- Learn about subqueries and their role in SQL.
- Understand how to use subqueries in SELECT, WHERE, and FROM clauses.
- Practice writing queries with subqueries.
Day 6: Data Manipulation Language (DML)
- Learn about DML commands: INSERT, UPDATE, DELETE.
- Understand how to add, modify, and delete data in a database.
- Practice writing DML statements.
Day 7: Data Definition Language (DDL)
- Learn about DDL commands: CREATE TABLE, ALTER TABLE, DROP TABLE.
- Understand constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL.
- Practice designing database schemas and creating tables.
Day 8: Data Control Language (DCL)
- Learn about DCL commands: GRANT, REVOKE for managing user permissions.
- Understand how to control access to database objects.
- Practice granting and revoking permissions.
Day 9: Transactions
- Understand the concept of transactions in SQL.
- Learn about transaction control commands: COMMIT, ROLLBACK.
- Practice managing transactions.
Day 10: Views
- Learn about views and their benefits.
- Understand how to create, modify, and drop views.
- Practice creating views.
Day 11: Indexes
- Learn about indexes and their role in database optimization.
- Understand different types of indexes (e.g., B-tree, hash).
- Practice creating and managing indexes.
Day 12: Optimization Techniques
- Explore optimization techniques such as query tuning and normalization.
- Understand the importance of database design for optimization.
- Practice optimizing SQL queries.
Day 13: Review and Practice
- Review all concepts covered in the previous days.
- Work on sample projects or exercises to reinforce learning.
- Take practice quizzes or tests.
Day 14: Final Review and Projects
- Review all concepts learned throughout the 14 days.
- Work on a final project to apply SQL knowledge.
- Seek out additional resources or tutorials if needed.
Here are some practical SQL syntax examples for each day of your learning journey:
Day 1: Introduction to Databases
- Syntax to select all columns from a table:
Day 2: Basic SQL Syntax
- Syntax to select specific columns from a table:
Day 3: Retrieving Data from Multiple Tables
- Syntax for INNER JOIN to retrieve data from two tables:
Day 4: Aggregate Functions
- Syntax for COUNT to count the number of rows in a table:
Day 5: Subqueries
- Syntax for using a subquery in the WHERE clause:
Day 6: Data Manipulation Language (DML)
- Syntax for INSERT to add data into a table:
Day 1: Introduction to Databases
- Understand the concept of databases and their importance.
- Learn about relational databases and SQL.
- Explore the basic structure of SQL queries.
Day 2: Basic SQL Syntax
- Learn SQL syntax: statements, clauses, and keywords.
- Understand the SELECT statement for retrieving data.
- Practice writing basic SELECT queries with conditions and filters.
Day 3: Retrieving Data from Multiple Tables
- Learn about joins: INNER JOIN, LEFT JOIN, RIGHT JOIN.
- Understand how to retrieve data from multiple tables using joins.
- Practice writing queries involving multiple tables.
Day 4: Aggregate Functions
- Learn about aggregate functions: COUNT, SUM, AVG, MIN, MAX.
- Understand how to use aggregate functions to perform calculations on data.
- Practice writing queries with aggregate functions.
Day 5: Subqueries
- Learn about subqueries and their role in SQL.
- Understand how to use subqueries in SELECT, WHERE, and FROM clauses.
- Practice writing queries with subqueries.
Day 6: Data Manipulation Language (DML)
- Learn about DML commands: INSERT, UPDATE, DELETE.
- Understand how to add, modify, and delete data in a database.
- Practice writing DML statements.
Day 7: Data Definition Language (DDL)
- Learn about DDL commands: CREATE TABLE, ALTER TABLE, DROP TABLE.
- Understand constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL.
- Practice designing database schemas and creating tables.
Day 8: Data Control Language (DCL)
- Learn about DCL commands: GRANT, REVOKE for managing user permissions.
- Understand how to control access to database objects.
- Practice granting and revoking permissions.
Day 9: Transactions
- Understand the concept of transactions in SQL.
- Learn about transaction control commands: COMMIT, ROLLBACK.
- Practice managing transactions.
Day 10: Views
- Learn about views and their benefits.
- Understand how to create, modify, and drop views.
- Practice creating views.
Day 11: Indexes
- Learn about indexes and their role in database optimization.
- Understand different types of indexes (e.g., B-tree, hash).
- Practice creating and managing indexes.
Day 12: Optimization Techniques
- Explore optimization techniques such as query tuning and normalization.
- Understand the importance of database design for optimization.
- Practice optimizing SQL queries.
Day 13: Review and Practice
- Review all concepts covered in the previous days.
- Work on sample projects or exercises to reinforce learning.
- Take practice quizzes or tests.
Day 14: Final Review and Projects
- Review all concepts learned throughout the 14 days.
- Work on a final project to apply SQL knowledge.
- Seek out additional resources or tutorials if needed.
Here are some practical SQL syntax examples for each day of your learning journey:
Day 1: Introduction to Databases
- Syntax to select all columns from a table:
SELECT * FROM table_name;
Day 2: Basic SQL Syntax
- Syntax to select specific columns from a table:
SELECT column1, column2 FROM table_name;
Day 3: Retrieving Data from Multiple Tables
- Syntax for INNER JOIN to retrieve data from two tables:
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
Day 4: Aggregate Functions
- Syntax for COUNT to count the number of rows in a table:
SELECT COUNT(*) FROM table_name;
Day 5: Subqueries
- Syntax for using a subquery in the WHERE clause:
SELECT column1, column2
FROM table_name
WHERE column1 IN (SELECT column1 FROM another_table WHERE condition);
Day 6: Data Manipulation Language (DML)
- Syntax for INSERT to add data into a table:
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
👍43❤16
Programming languages in data science 👇👇
https://www.instagram.com/reel/C-YLuq6yI_6/?igsh=Ynd3aHY4bWlsOW00
https://www.instagram.com/reel/C-YLuq6yI_6/?igsh=Ynd3aHY4bWlsOW00
👍8
Majority of top companies hiring for analytic roles (Data Analyst/Business Analyst) focus heavily on SQL understanding as a selection criteria, which according to me, should be the first thing you start your preparation with.
I have divided this SQL roadmap into 3 steps (Basics, Level Up & Practice), and it should take around 1 month to complete.
Step 1 - Basics 🔢 :
➡What is a Relational Database / RDBMS?
➡SQL Data Types - Varchar, text, int, number, date, float, boolean.
➡SQL commands - select, where, like, distinct, between, group by, having, order by, insert into, case when, update, truncate, delete, commit, rollback (basically all the DDL, DML, DCL, TCL commands in SQL).
➡Integrity Constraints - Primary key, foreign key, not null, unique.
➡Operators arithmetic, logical, and comparison operations.
➡Use of distinct, order by, limit, and top.
➡Use of union and union all.
➡Joins in SQL inner, left, right, outer, self, full outer, cross join.
Step 2 - Level up ⬆⬆ :
➡Normalization in SQL
➡Aggregate, date, and string functions
➡Sub-Queries
➡CTE table / with clause
➡In-built SQL functions
➡Window functions
➡Views
Step 3 - Practice SQL Questions on leetcode & hackerrank ✅
Hope it helps :)
I have divided this SQL roadmap into 3 steps (Basics, Level Up & Practice), and it should take around 1 month to complete.
Step 1 - Basics 🔢 :
➡What is a Relational Database / RDBMS?
➡SQL Data Types - Varchar, text, int, number, date, float, boolean.
➡SQL commands - select, where, like, distinct, between, group by, having, order by, insert into, case when, update, truncate, delete, commit, rollback (basically all the DDL, DML, DCL, TCL commands in SQL).
➡Integrity Constraints - Primary key, foreign key, not null, unique.
➡Operators arithmetic, logical, and comparison operations.
➡Use of distinct, order by, limit, and top.
➡Use of union and union all.
➡Joins in SQL inner, left, right, outer, self, full outer, cross join.
Step 2 - Level up ⬆⬆ :
➡Normalization in SQL
➡Aggregate, date, and string functions
➡Sub-Queries
➡CTE table / with clause
➡In-built SQL functions
➡Window functions
➡Views
Step 3 - Practice SQL Questions on leetcode & hackerrank ✅
Hope it helps :)
👍42❤8👏1
TOP CONCEPTS FOR INTERVIEW PREPARATION!!
🚀TOP 10 SQL Concepts for Job Interview
1. Aggregate Functions (SUM/AVG)
2. Group By and Order By
3. JOINs (Inner/Left/Right)
4. Union and Union All
5. Date and Time processing
6. String processing
7. Window Functions (Partition by)
8. Subquery
9. View and Index
10. Common Table Expression (CTE)
🚀TOP 10 Statistics Concepts for Job Interview
1. Sampling
2. Experiments (A/B tests)
3. Denoscriptive Statistics
4. p-value
5. Probability Distributions
6. t-test
7. ANOVA
8. Correlation
9. Linear Regression
10. Logistics Regression
🚀TOP 10 Python Concepts for Job Interview
1. Reading data from file/table
2. Writing data to file/table
3. Data Types
4. Function
5. Data Preprocessing (numpy/pandas)
6. Data Visualisation (Matplotlib/seaborn/bokeh)
7. Machine Learning (sklearn)
8. Deep Learning (Tensorflow/Keras/PyTorch)
9. Distributed Processing (PySpark)
10. Functional and Object Oriented Programming
Hope this helps you 😊
🚀TOP 10 SQL Concepts for Job Interview
1. Aggregate Functions (SUM/AVG)
2. Group By and Order By
3. JOINs (Inner/Left/Right)
4. Union and Union All
5. Date and Time processing
6. String processing
7. Window Functions (Partition by)
8. Subquery
9. View and Index
10. Common Table Expression (CTE)
🚀TOP 10 Statistics Concepts for Job Interview
1. Sampling
2. Experiments (A/B tests)
3. Denoscriptive Statistics
4. p-value
5. Probability Distributions
6. t-test
7. ANOVA
8. Correlation
9. Linear Regression
10. Logistics Regression
🚀TOP 10 Python Concepts for Job Interview
1. Reading data from file/table
2. Writing data to file/table
3. Data Types
4. Function
5. Data Preprocessing (numpy/pandas)
6. Data Visualisation (Matplotlib/seaborn/bokeh)
7. Machine Learning (sklearn)
8. Deep Learning (Tensorflow/Keras/PyTorch)
9. Distributed Processing (PySpark)
10. Functional and Object Oriented Programming
Hope this helps you 😊
👍22👏4❤2
Quick Recap of SQL Concepts
1. What is SQL?
SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases.
2. What are the different types of SQL commands?
- Data Definition Language (DDL): Used to define the structure of database objects (CREATE, ALTER, DROP).
- Data Manipulation Language (DML): Used to manipulate data in the database (SELECT, INSERT, UPDATE, DELETE).
- Data Control Language (DCL): Used to control access and permissions on database objects (GRANT, REVOKE).
3. What is a database schema?
A database schema is a logical structure that represents the layout of the database, including tables, columns, relationships, constraints, and indexes.
4. What is a primary key?
A primary key is a unique identifier for each record in a table. It ensures that each row in the table is uniquely identified and helps maintain data integrity.
5. What is a foreign key?
A foreign key is a column or set of columns in one table that references the primary key in another table. It establishes a relationship between the two tables.
6. What is normalization in SQL?
Normalization is the process of organizing data in a database to reduce redundancy and dependency by dividing large tables into smaller tables and defining relationships between them.
7. What is an index in SQL?
An index is a data structure that improves the speed of data retrieval operations on a database table. It allows for faster searching and sorting of data based on specific columns.
8. What is a JOIN in SQL?
A JOIN is used to combine rows from two or more tables based on a related column between them. Common types of JOINs include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
9. What is a subquery in SQL?
A subquery is a query nested within another query. It allows you to perform complex queries by using the result of one query as input for another query.
10. What is the difference between SQL and NoSQL databases?
- SQL databases are relational databases that store data in structured tables with predefined schemas, while NoSQL databases are non-relational databases that store data in flexible, schema-less formats.
- SQL databases use SQL for querying and manipulating data, while NoSQL databases use various query languages or APIs.
- SQL databases are suitable for complex queries and transactions, while NoSQL databases are better for handling large volumes of unstructured data and scaling horizontally.
Hope it helps :)
1. What is SQL?
SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases.
2. What are the different types of SQL commands?
- Data Definition Language (DDL): Used to define the structure of database objects (CREATE, ALTER, DROP).
- Data Manipulation Language (DML): Used to manipulate data in the database (SELECT, INSERT, UPDATE, DELETE).
- Data Control Language (DCL): Used to control access and permissions on database objects (GRANT, REVOKE).
3. What is a database schema?
A database schema is a logical structure that represents the layout of the database, including tables, columns, relationships, constraints, and indexes.
4. What is a primary key?
A primary key is a unique identifier for each record in a table. It ensures that each row in the table is uniquely identified and helps maintain data integrity.
5. What is a foreign key?
A foreign key is a column or set of columns in one table that references the primary key in another table. It establishes a relationship between the two tables.
6. What is normalization in SQL?
Normalization is the process of organizing data in a database to reduce redundancy and dependency by dividing large tables into smaller tables and defining relationships between them.
7. What is an index in SQL?
An index is a data structure that improves the speed of data retrieval operations on a database table. It allows for faster searching and sorting of data based on specific columns.
8. What is a JOIN in SQL?
A JOIN is used to combine rows from two or more tables based on a related column between them. Common types of JOINs include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
9. What is a subquery in SQL?
A subquery is a query nested within another query. It allows you to perform complex queries by using the result of one query as input for another query.
10. What is the difference between SQL and NoSQL databases?
- SQL databases are relational databases that store data in structured tables with predefined schemas, while NoSQL databases are non-relational databases that store data in flexible, schema-less formats.
- SQL databases use SQL for querying and manipulating data, while NoSQL databases use various query languages or APIs.
- SQL databases are suitable for complex queries and transactions, while NoSQL databases are better for handling large volumes of unstructured data and scaling horizontally.
Hope it helps :)
👍44❤2👏2🎉2
How to learn SQL in 2024: Essential Topics for Beginners 👇👇
https://youtu.be/VCZxODefTIs?si=1XB44uv5DIpcJA4K
https://youtu.be/VCZxODefTIs?si=1XB44uv5DIpcJA4K
👍7
Tackle Real World Data Challenges with These SQL Key Queries...
Scenario 1: Calculating Average
Question:
You have a table Employees with columns EmployeeID, Department, and Salary. Write an SQL query to find the average salary for each department.
Answer:
Assuming the table Employees with columns EmployeeID, Department, and Salary
SELECT Department,
AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;
Scenario 2: Finding Top Performers
Question:
You have a table Sales with columns SalesPersonID, SaleAmount, and SaleDate. Write an SQL query to find the top 3 salespeople with the highest total sales.
Answer:
Assuming the table Sales with columns SalesPersonID, SaleAmount, and SaleDate
SELECT SalesPersonID,
SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID
ORDER BY TotalSales DESC
LIMIT 3;
Scenario 3: Date Range Filtering
Question:
You have a table Orders with columns OrderID, OrderDate, and Amount. Write an SQL query to find the total amount of orders placed in the last 30 days.
Answer:
Assuming the table Orders with columns OrderID, OrderDate, and Amount
SELECT SUM(Amount) AS TotalAmount
FROM Orders
WHERE OrderDate >= CURDATE() - INTERVAL 30 DAY;
Hope it helps :)
Scenario 1: Calculating Average
Question:
You have a table Employees with columns EmployeeID, Department, and Salary. Write an SQL query to find the average salary for each department.
Answer:
Assuming the table Employees with columns EmployeeID, Department, and Salary
SELECT Department,
AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;
Scenario 2: Finding Top Performers
Question:
You have a table Sales with columns SalesPersonID, SaleAmount, and SaleDate. Write an SQL query to find the top 3 salespeople with the highest total sales.
Answer:
Assuming the table Sales with columns SalesPersonID, SaleAmount, and SaleDate
SELECT SalesPersonID,
SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID
ORDER BY TotalSales DESC
LIMIT 3;
Scenario 3: Date Range Filtering
Question:
You have a table Orders with columns OrderID, OrderDate, and Amount. Write an SQL query to find the total amount of orders placed in the last 30 days.
Answer:
Assuming the table Orders with columns OrderID, OrderDate, and Amount
SELECT SUM(Amount) AS TotalAmount
FROM Orders
WHERE OrderDate >= CURDATE() - INTERVAL 30 DAY;
Hope it helps :)
👍29❤6👏3
Some 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;
6. Given a table Employees with columns EmployeeID, Name, ManagerID, write a query to find the number of direct reports for each manager.
SELECT ManagerID, COUNT(*) AS NumberOfReports
FROM Employees
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID;
7. Given a table Customers with columns CustomerID, Name, JoinDate, and a table Orders with columns OrderID, CustomerID, OrderDate, write a query to find customers who placed their first order within the last 30 days.
SELECT c.CustomerID, c. Name
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate = (SELECT MIN(o2.OrderDate) FROM Orders o2 WHERE o2.CustomerID = c.CustomerID)
AND o.OrderDate >= CURRENT_DATE - INTERVAL '30 day';
Hope it helps :)
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;
6. Given a table Employees with columns EmployeeID, Name, ManagerID, write a query to find the number of direct reports for each manager.
SELECT ManagerID, COUNT(*) AS NumberOfReports
FROM Employees
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID;
7. Given a table Customers with columns CustomerID, Name, JoinDate, and a table Orders with columns OrderID, CustomerID, OrderDate, write a query to find customers who placed their first order within the last 30 days.
SELECT c.CustomerID, c. Name
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate = (SELECT MIN(o2.OrderDate) FROM Orders o2 WHERE o2.CustomerID = c.CustomerID)
AND o.OrderDate >= CURRENT_DATE - INTERVAL '30 day';
Hope it helps :)
👍14🤣2
Many people pay too much to learn SQL, but my mission is to break down barriers. I have shared complete learning series to learn SQL from scratch.
Here are the links to the SQL series
Complete SQL Topics for Data Analyst: https://news.1rj.ru/str/sqlspecialist/523
Part-1: https://news.1rj.ru/str/sqlspecialist/524
Part-2: https://news.1rj.ru/str/sqlspecialist/525
Part-3: https://news.1rj.ru/str/sqlspecialist/526
Part-4: https://news.1rj.ru/str/sqlspecialist/527
Part-5: https://news.1rj.ru/str/sqlspecialist/529
Part-6: https://news.1rj.ru/str/sqlspecialist/534
Part-7: https://news.1rj.ru/str/sqlspecialist/534
Part-8: https://news.1rj.ru/str/sqlspecialist/536
Part-9: https://news.1rj.ru/str/sqlspecialist/537
Part-10: https://news.1rj.ru/str/sqlspecialist/539
Part-11: https://news.1rj.ru/str/sqlspecialist/540
Part-12:
https://news.1rj.ru/str/sqlspecialist/541
Part-13: https://news.1rj.ru/str/sqlspecialist/542
Part-14: https://news.1rj.ru/str/sqlspecialist/544
Part-15: https://news.1rj.ru/str/sqlspecialist/545
Part-16: https://news.1rj.ru/str/sqlspecialist/546
Part-17: https://news.1rj.ru/str/sqlspecialist/549
Part-18: https://news.1rj.ru/str/sqlspecialist/552
Part-19: https://news.1rj.ru/str/sqlspecialist/555
Part-20: https://news.1rj.ru/str/sqlspecialist/556
I saw a lot of big influencers copy pasting my content after removing the credits. It's absolutely fine for me as more people are getting free education because of my content.
But I will really appreciate if you share credits for the time and efforts I put in to create such valuable content. I hope you can understand.
Complete Python Topics for Data Analysts: https://news.1rj.ru/str/sqlspecialist/548
Complete Excel Topics for Data Analysts: https://news.1rj.ru/str/sqlspecialist/547
I'll continue with learning series on Python, Power BI, Excel & Tableau.
Thanks to all who support our channel and share the content with proper credits. You guys are really amazing.
Hope it helps :)
Here are the links to the SQL series
Complete SQL Topics for Data Analyst: https://news.1rj.ru/str/sqlspecialist/523
Part-1: https://news.1rj.ru/str/sqlspecialist/524
Part-2: https://news.1rj.ru/str/sqlspecialist/525
Part-3: https://news.1rj.ru/str/sqlspecialist/526
Part-4: https://news.1rj.ru/str/sqlspecialist/527
Part-5: https://news.1rj.ru/str/sqlspecialist/529
Part-6: https://news.1rj.ru/str/sqlspecialist/534
Part-7: https://news.1rj.ru/str/sqlspecialist/534
Part-8: https://news.1rj.ru/str/sqlspecialist/536
Part-9: https://news.1rj.ru/str/sqlspecialist/537
Part-10: https://news.1rj.ru/str/sqlspecialist/539
Part-11: https://news.1rj.ru/str/sqlspecialist/540
Part-12:
https://news.1rj.ru/str/sqlspecialist/541
Part-13: https://news.1rj.ru/str/sqlspecialist/542
Part-14: https://news.1rj.ru/str/sqlspecialist/544
Part-15: https://news.1rj.ru/str/sqlspecialist/545
Part-16: https://news.1rj.ru/str/sqlspecialist/546
Part-17: https://news.1rj.ru/str/sqlspecialist/549
Part-18: https://news.1rj.ru/str/sqlspecialist/552
Part-19: https://news.1rj.ru/str/sqlspecialist/555
Part-20: https://news.1rj.ru/str/sqlspecialist/556
I saw a lot of big influencers copy pasting my content after removing the credits. It's absolutely fine for me as more people are getting free education because of my content.
But I will really appreciate if you share credits for the time and efforts I put in to create such valuable content. I hope you can understand.
Complete Python Topics for Data Analysts: https://news.1rj.ru/str/sqlspecialist/548
Complete Excel Topics for Data Analysts: https://news.1rj.ru/str/sqlspecialist/547
I'll continue with learning series on Python, Power BI, Excel & Tableau.
Thanks to all who support our channel and share the content with proper credits. You guys are really amazing.
Hope it helps :)
👍20❤4👏4🎉2🤔1🤣1
Complete topics & subtopics of SQL for Data Analyst role:-
1. SQL Fundamentals
A. SQL Basics
• SQL Keywords and Syntax
• Data Types (Numeric, String, Date/Time, etc.)
• Operators (Arithmetic, Comparison, Logical)
B. Core SQL Statements
• SELECT
• INSERT
• UPDATE
• DELETE
2. Database Design and Schema
A. Data Definition Language (DDL)
• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• TRUNCATE TABLE
B. Data Constraints
• Primary Key
• Foreign Key
• Unique
• NOT NULL
• CHECK
3. Querying and Data Manipulation
A. Data Manipulation Language (DML)
• SELECT Clauses (SELECT, FROM, WHERE)
• Sorting and Filtering (ORDER BY, GROUP BY, HAVING)
• JOIN Operations (INNER, LEFT, RIGHT, FULL OUTER, SELF, CROSS)
• INSERT, UPDATE, DELETE Operations
B. Aggregate Functions and Grouping
• Functions (SUM, AVG, COUNT, MIN, MAX)
• GROUP BY and HAVING Clauses
4. Advanced Querying Techniques
A. Joins and Subqueries
• Types of Joins and Their Use Cases
• Subqueries (Scalar, Column, Row, Table)
• Nested and Correlated Subqueries
B. Advanced SQL Functions
• String Functions (CONCAT, LENGTH, SUBSTRING, REPLACE, UPPER, LOWER)
• Date/Time Functions (DATE, TIME, TIMESTAMP, DATEPART, DATEADD)
• Numeric Functions (ROUND, CEILING, FLOOR, ABS, MOD)
• Conditional Functions (CASE, COALESCE, NULLIF)
5. Views and Indexes
A. Views
• Creating and Managing Views
• Modifying and Dropping Views
B. Indexes
• Types of Indexes (Single Column, Composite)
• Creating and Using Indexes for Optimization
6. Security and Data Integrity
A. Data Integrity
• Referential and Entity Integrity
• Enforcing Data Constraints
B. Security Management
• GRANT and REVOKE Permissions
• Best Practices for Database Security
7. Stored procedure and functions
A. Stored Procedures
• Creating, Modifying, and Executing Stored Procedures
• Benefits and Use Cases
B. Functions
• User-Defined Functions
• Using Functions in Queries
8. Performance Optimization
A. Query Optimization Techniques
• Index Usage
• Optimizing Joins and Subqueries
• Execution Plans and Query Analysis
B. Performance Tuning Best Practices
• Avoiding Common Pitfalls
• Regular Maintenance and Updates
9. Advanced SQL Features
A. Complex Query Techniques
• Recursive Queries
• Pivot and Unpivot Operations
• Window Functions (ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG)
B. Common Table Expressions (CTEs) and Dynamic SQL
• Using CTEs for Improved Readability
• Implementing Dynamic SQL for Flexible Queries
10. Practical Applications and Case Studies
• Real-World SQL Scenarios
• Data Analysis Case Studies
• Problem-Solving with SQL
Hope it helps :)
1. SQL Fundamentals
A. SQL Basics
• SQL Keywords and Syntax
• Data Types (Numeric, String, Date/Time, etc.)
• Operators (Arithmetic, Comparison, Logical)
B. Core SQL Statements
• SELECT
• INSERT
• UPDATE
• DELETE
2. Database Design and Schema
A. Data Definition Language (DDL)
• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• TRUNCATE TABLE
B. Data Constraints
• Primary Key
• Foreign Key
• Unique
• NOT NULL
• CHECK
3. Querying and Data Manipulation
A. Data Manipulation Language (DML)
• SELECT Clauses (SELECT, FROM, WHERE)
• Sorting and Filtering (ORDER BY, GROUP BY, HAVING)
• JOIN Operations (INNER, LEFT, RIGHT, FULL OUTER, SELF, CROSS)
• INSERT, UPDATE, DELETE Operations
B. Aggregate Functions and Grouping
• Functions (SUM, AVG, COUNT, MIN, MAX)
• GROUP BY and HAVING Clauses
4. Advanced Querying Techniques
A. Joins and Subqueries
• Types of Joins and Their Use Cases
• Subqueries (Scalar, Column, Row, Table)
• Nested and Correlated Subqueries
B. Advanced SQL Functions
• String Functions (CONCAT, LENGTH, SUBSTRING, REPLACE, UPPER, LOWER)
• Date/Time Functions (DATE, TIME, TIMESTAMP, DATEPART, DATEADD)
• Numeric Functions (ROUND, CEILING, FLOOR, ABS, MOD)
• Conditional Functions (CASE, COALESCE, NULLIF)
5. Views and Indexes
A. Views
• Creating and Managing Views
• Modifying and Dropping Views
B. Indexes
• Types of Indexes (Single Column, Composite)
• Creating and Using Indexes for Optimization
6. Security and Data Integrity
A. Data Integrity
• Referential and Entity Integrity
• Enforcing Data Constraints
B. Security Management
• GRANT and REVOKE Permissions
• Best Practices for Database Security
7. Stored procedure and functions
A. Stored Procedures
• Creating, Modifying, and Executing Stored Procedures
• Benefits and Use Cases
B. Functions
• User-Defined Functions
• Using Functions in Queries
8. Performance Optimization
A. Query Optimization Techniques
• Index Usage
• Optimizing Joins and Subqueries
• Execution Plans and Query Analysis
B. Performance Tuning Best Practices
• Avoiding Common Pitfalls
• Regular Maintenance and Updates
9. Advanced SQL Features
A. Complex Query Techniques
• Recursive Queries
• Pivot and Unpivot Operations
• Window Functions (ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG)
B. Common Table Expressions (CTEs) and Dynamic SQL
• Using CTEs for Improved Readability
• Implementing Dynamic SQL for Flexible Queries
10. Practical Applications and Case Studies
• Real-World SQL Scenarios
• Data Analysis Case Studies
• Problem-Solving with SQL
Hope it helps :)
👍15❤4
For a data analytics interview, focusing on key SQL topics can be crucial. Here's a list of last-minute SQL topics to revise:
1. SQL Basics:
• SELECT statements: Syntax, SELECT DISTINCT
• WHERE clause: Conditions and operators (>, <, =, LIKE, IN, BETWEEN)
• ORDER BY clause: Sorting results
• LIMIT clause: Limiting the number of rows returned
2. Joins:
• INNER JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN
• Understanding join conditions and scenarios for each type of join
3. Aggregation and Grouping:
• GROUP BY clause
• HAVING clause: Filtering grouped results
• Aggregate functions: COUNT, SUM, AVG, MIN, MAX
4. Subqueries:
• Nested subqueries: Using subqueries in SELECT, FROM, WHERE, and HAVING clauses
• Correlated subqueries
5. Common Table Expressions (CTEs):
• Syntax and use cases for CTEs (WITH clause)
6. Window Functions:
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD() and LAG()
• PARTITION BY clause
7. Data Manipulation:
• INSERT, UPDATE, DELETE statements
• Understanding transaction control with COMMIT and ROLLBACK
8. Data Definition:
• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL
9. Indexing:
• Purpose and types of indexes
• How indexing affects query performance
10. Performance Optimization:
• Understanding query execution plans
• Identifying and resolving common performance issues
11. SQL Functions:
• String functions: CONCAT, SUBSTRING, LENGTH
• Date functions: DATEADD, DATEDIFF, GETDATE
• Mathematical functions: ROUND, CEILING, FLOOR
12. Stored Procedures and Triggers:
• Basics of writing and using stored procedures
• Basics of writing and using triggers
13. ETL (Extract, Transform, Load):
• Understanding the process and SQL's role in ETL operations
14. Advanced Topics (if time permits):
• Understanding complex data types (JSON, XML)
• Working with large datasets and big data considerations
Hope it helps :)
1. SQL Basics:
• SELECT statements: Syntax, SELECT DISTINCT
• WHERE clause: Conditions and operators (>, <, =, LIKE, IN, BETWEEN)
• ORDER BY clause: Sorting results
• LIMIT clause: Limiting the number of rows returned
2. Joins:
• INNER JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN
• Understanding join conditions and scenarios for each type of join
3. Aggregation and Grouping:
• GROUP BY clause
• HAVING clause: Filtering grouped results
• Aggregate functions: COUNT, SUM, AVG, MIN, MAX
4. Subqueries:
• Nested subqueries: Using subqueries in SELECT, FROM, WHERE, and HAVING clauses
• Correlated subqueries
5. Common Table Expressions (CTEs):
• Syntax and use cases for CTEs (WITH clause)
6. Window Functions:
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD() and LAG()
• PARTITION BY clause
7. Data Manipulation:
• INSERT, UPDATE, DELETE statements
• Understanding transaction control with COMMIT and ROLLBACK
8. Data Definition:
• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL
9. Indexing:
• Purpose and types of indexes
• How indexing affects query performance
10. Performance Optimization:
• Understanding query execution plans
• Identifying and resolving common performance issues
11. SQL Functions:
• String functions: CONCAT, SUBSTRING, LENGTH
• Date functions: DATEADD, DATEDIFF, GETDATE
• Mathematical functions: ROUND, CEILING, FLOOR
12. Stored Procedures and Triggers:
• Basics of writing and using stored procedures
• Basics of writing and using triggers
13. ETL (Extract, Transform, Load):
• Understanding the process and SQL's role in ETL operations
14. Advanced Topics (if time permits):
• Understanding complex data types (JSON, XML)
• Working with large datasets and big data considerations
Hope it helps :)
👍18❤2
SQL books won’t teach you this.
Natural Keys vs. Autoincrement IDs vs. Public IDs. (or maybe all together)
𝗡𝗮𝘁𝘂𝗿𝗮𝗹 𝗞𝗲𝘆𝘀
Natural keys carry intrinsic meaning because they are part of the domain.
They are directly related to the data, making them intuitive and easy to understand. Examples include email addresses or employee IDs.
The problem is that they are usually not good for performance, but they can also be a security risk if you expose them.
𝗔𝘂𝘁𝗼𝗶𝗻𝗰𝗿𝗲𝗺𝗲𝗻𝘁 𝗜𝗗𝘀
Autoincrement IDs automatically generate unique integers to identify rows within a table.
They are often used as primary keys.
Simple integers are fast for the database to index and query. They provide optimal performance.
However, they are vulnerable to enumeration attacks since predicting the next or previous record is easy.
𝗣𝘂𝗯𝗹𝗶𝗰 𝗜𝗗𝘀 (𝗨𝗨𝗜𝗗𝘀)
UUIDs (Universally Unique Identifiers) are 128-bit identifiers used to uniquely identify information without relying on a centralized authority.
They are difficult to guess, making them suitable for public exposure in APIs.
The problem is they are larger and more complex than integers. This can impact performance, particularly in indexing and storage.
𝗙𝗶𝗻𝗱𝗶𝗻𝗴 𝘁𝗵𝗲 𝗦𝘄𝗲𝗲𝘁 𝗦𝗽𝗼𝘁: 𝗔 𝗠𝗶𝘅𝗲𝗱 𝗔𝗽𝗽𝗿𝗼𝗮𝗰𝗵
Combining different types of keys can offer a balanced solution:
• InternalID: Used for internal operations and relationships between tables.
• PublicID: Used in API responses and endpoints to securely reference user records.
• Email (Natural Key): Used to ensure unique identification of users within the business logic.
The mixed approach keeps your system fast, secure, and easy to understand.
Like this post if you need more 👍❤️
Hope it helps :)
Natural Keys vs. Autoincrement IDs vs. Public IDs. (or maybe all together)
𝗡𝗮𝘁𝘂𝗿𝗮𝗹 𝗞𝗲𝘆𝘀
Natural keys carry intrinsic meaning because they are part of the domain.
They are directly related to the data, making them intuitive and easy to understand. Examples include email addresses or employee IDs.
The problem is that they are usually not good for performance, but they can also be a security risk if you expose them.
𝗔𝘂𝘁𝗼𝗶𝗻𝗰𝗿𝗲𝗺𝗲𝗻𝘁 𝗜𝗗𝘀
Autoincrement IDs automatically generate unique integers to identify rows within a table.
They are often used as primary keys.
Simple integers are fast for the database to index and query. They provide optimal performance.
However, they are vulnerable to enumeration attacks since predicting the next or previous record is easy.
𝗣𝘂𝗯𝗹𝗶𝗰 𝗜𝗗𝘀 (𝗨𝗨𝗜𝗗𝘀)
UUIDs (Universally Unique Identifiers) are 128-bit identifiers used to uniquely identify information without relying on a centralized authority.
They are difficult to guess, making them suitable for public exposure in APIs.
The problem is they are larger and more complex than integers. This can impact performance, particularly in indexing and storage.
𝗙𝗶𝗻𝗱𝗶𝗻𝗴 𝘁𝗵𝗲 𝗦𝘄𝗲𝗲𝘁 𝗦𝗽𝗼𝘁: 𝗔 𝗠𝗶𝘅𝗲𝗱 𝗔𝗽𝗽𝗿𝗼𝗮𝗰𝗵
Combining different types of keys can offer a balanced solution:
• InternalID: Used for internal operations and relationships between tables.
• PublicID: Used in API responses and endpoints to securely reference user records.
• Email (Natural Key): Used to ensure unique identification of users within the business logic.
The mixed approach keeps your system fast, secure, and easy to understand.
Like this post if you need more 👍❤️
Hope it helps :)
👍7❤5
Most Asked SQL Interview Questions at MAANG Companies🔥🔥
Preparing for an SQL Interview at MAANG Companies? Here are some crucial SQL Questions you should be ready to tackle:
1. How do you retrieve all columns from a table?
SELECT * FROM table_name;
2. What SQL statement is used to filter records?
SELECT * FROM table_name
WHERE condition;
The WHERE clause is used to filter records based on a specified condition.
3. How can you join multiple tables? Describe different types of JOINs.
SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column;
Types of JOINs:
1. INNER JOIN: Returns records with matching values in both tables
SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;
2. LEFT JOIN: Returns all records from the left table & matched records from the right table. Unmatched records will have NULL values.
SELECT * FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
3. RIGHT JOIN: Returns all records from the right table & matched records from the left table. Unmatched records will have NULL values.
SELECT * FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
4. FULL JOIN: Returns records when there is a match in either left or right table. Unmatched records will have NULL values.
SELECT * FROM table1
FULL JOIN table2 ON table1.column = table2.column;
4. What is the difference between WHERE & HAVING clauses?
WHERE: Filters records before any groupings are made.
SELECT * FROM table_name
WHERE condition;
HAVING: Filters records after groupings are made.
SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > value;
5. How do you calculate average, sum, minimum & maximum values in a column?
Average: SELECT AVG(column_name) FROM table_name;
Sum: SELECT SUM(column_name) FROM table_name;
Minimum: SELECT MIN(column_name) FROM table_name;
Maximum: SELECT MAX(column_name) FROM table_name;
Hope it helps :)
Preparing for an SQL Interview at MAANG Companies? Here are some crucial SQL Questions you should be ready to tackle:
1. How do you retrieve all columns from a table?
SELECT * FROM table_name;
2. What SQL statement is used to filter records?
SELECT * FROM table_name
WHERE condition;
The WHERE clause is used to filter records based on a specified condition.
3. How can you join multiple tables? Describe different types of JOINs.
SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column;
Types of JOINs:
1. INNER JOIN: Returns records with matching values in both tables
SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;
2. LEFT JOIN: Returns all records from the left table & matched records from the right table. Unmatched records will have NULL values.
SELECT * FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
3. RIGHT JOIN: Returns all records from the right table & matched records from the left table. Unmatched records will have NULL values.
SELECT * FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
4. FULL JOIN: Returns records when there is a match in either left or right table. Unmatched records will have NULL values.
SELECT * FROM table1
FULL JOIN table2 ON table1.column = table2.column;
4. What is the difference between WHERE & HAVING clauses?
WHERE: Filters records before any groupings are made.
SELECT * FROM table_name
WHERE condition;
HAVING: Filters records after groupings are made.
SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > value;
5. How do you calculate average, sum, minimum & maximum values in a column?
Average: SELECT AVG(column_name) FROM table_name;
Sum: SELECT SUM(column_name) FROM table_name;
Minimum: SELECT MIN(column_name) FROM table_name;
Maximum: SELECT MAX(column_name) FROM table_name;
Hope it helps :)
👍26❤6👏1
Why SQL Still Rules the Data World?
SQL + Relational Databases = Structured Data Management
SQL + Joins = Seamless Data Integration
SQL + Aggregations = Powerful Data Summarization
SQL + Subqueries = Complex Data Retrieval
SQL + Indexing = Faster Query Performance
SQL + Transactions = Reliable Data Integrity
SQL + Views = Simplified Data Access
SQL + Stored Procedures = Efficient Data Operations
SQL + Triggers = Automated Actions Based on Data Changes
SQL + Constraints = Data Validation and Integrity
SQL + Normalization = Eliminate Redundancy
SQL + Data Warehousing = Scalable Data Storage Solutions
SQL + Data Lakes = Manage Vast Amounts of Raw Data
SQL + ETL Processes = Efficient Data Transformation
SQL + Backup and Recovery = Secure Data Management
SQL + Big Data Integration = Bridging SQL and NoSQL
SQL + Reporting Tools = Generating Insightful Reports
SQL + BI Tools = Business Intelligence Integration
SQL + Analytics = Deep Data Insights
SQL remains unbeatable with its ability to manage, query, and analyze data efficiently.
Hope it helps :)
SQL + Relational Databases = Structured Data Management
SQL + Joins = Seamless Data Integration
SQL + Aggregations = Powerful Data Summarization
SQL + Subqueries = Complex Data Retrieval
SQL + Indexing = Faster Query Performance
SQL + Transactions = Reliable Data Integrity
SQL + Views = Simplified Data Access
SQL + Stored Procedures = Efficient Data Operations
SQL + Triggers = Automated Actions Based on Data Changes
SQL + Constraints = Data Validation and Integrity
SQL + Normalization = Eliminate Redundancy
SQL + Data Warehousing = Scalable Data Storage Solutions
SQL + Data Lakes = Manage Vast Amounts of Raw Data
SQL + ETL Processes = Efficient Data Transformation
SQL + Backup and Recovery = Secure Data Management
SQL + Big Data Integration = Bridging SQL and NoSQL
SQL + Reporting Tools = Generating Insightful Reports
SQL + BI Tools = Business Intelligence Integration
SQL + Analytics = Deep Data Insights
SQL remains unbeatable with its ability to manage, query, and analyze data efficiently.
Hope it helps :)
👍11❤9
SQL best practices:
✔ Use EXISTS in place of IN wherever possible
✔ Use table aliases with columns when you are joining multiple tables
✔ Use GROUP BY instead of DISTINCT.
✔ Add useful comments wherever you write complex logic and avoid too many comments.
✔ Use joins instead of subqueries when possible for better performance.
✔ Use WHERE instead of HAVING to define filters on non-aggregate fields
✔ Avoid wildcards at beginning of predicates (something like '%abc' will cause full table scan to get the results)
✔ Considering cardinality within GROUP BY can make it faster (try to consider unique column first in group by list)
✔ Write SQL keywords in capital letters.
✔ Never use select *, always mention list of columns in select clause.
✔ Create CTEs instead of multiple sub queries , it will make your query easy to read.
✔ Join tables using JOIN keywords instead of writing join condition in where clause for better readability.
✔ Never use order by in sub queries , It will unnecessary increase runtime.
✔ If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better performance
✔ Always start WHERE clause with 1 = 1.This has the advantage of easily commenting out conditions during debugging a query.
✔ Taking care of NULL values before using equality or comparisons operators. Applying window functions. Filtering the query before joining and having clause.
✔ Make sure the JOIN conditions among two table Join are either keys or Indexed attribute.
Hope it helps :)
✔ Use EXISTS in place of IN wherever possible
✔ Use table aliases with columns when you are joining multiple tables
✔ Use GROUP BY instead of DISTINCT.
✔ Add useful comments wherever you write complex logic and avoid too many comments.
✔ Use joins instead of subqueries when possible for better performance.
✔ Use WHERE instead of HAVING to define filters on non-aggregate fields
✔ Avoid wildcards at beginning of predicates (something like '%abc' will cause full table scan to get the results)
✔ Considering cardinality within GROUP BY can make it faster (try to consider unique column first in group by list)
✔ Write SQL keywords in capital letters.
✔ Never use select *, always mention list of columns in select clause.
✔ Create CTEs instead of multiple sub queries , it will make your query easy to read.
✔ Join tables using JOIN keywords instead of writing join condition in where clause for better readability.
✔ Never use order by in sub queries , It will unnecessary increase runtime.
✔ If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better performance
✔ Always start WHERE clause with 1 = 1.This has the advantage of easily commenting out conditions during debugging a query.
✔ Taking care of NULL values before using equality or comparisons operators. Applying window functions. Filtering the query before joining and having clause.
✔ Make sure the JOIN conditions among two table Join are either keys or Indexed attribute.
Hope it helps :)
👍20❤4
One of the favorite topics in SQL interviews is inventory management.
Here is a good example:
𝗧𝗵𝗲 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻:
Imagine you are helping the Inventory department to identify high or low stock levels at specific locations.
𝗧𝗼 𝘀𝗼𝗹𝘃𝗲 𝘁𝗵𝗲 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻, 𝘆𝗼𝘂 𝗻𝗲𝗲𝗱 𝘁𝗼:
• Establish a threshold to define high/low (we will use 10 units for this example).
• Calculate the stock level of each product
• Calculate the average stock level across all stores.
It sounds simple, but think again: You can't use a GROUP BY because you need the details for each product per store.
𝗧𝗵𝗲 𝘀𝗶𝗺𝗽𝗹𝗲𝘀𝘁 𝘄𝗮𝘆 𝘁𝗼 𝘀𝗼𝗹𝘃𝗲 𝘁𝗵𝗶𝘀 𝗽𝗿𝗼𝗯𝗹𝗲𝗺 𝗶𝘀 𝘂𝘀𝗶𝗻𝗴 𝗣𝗔𝗥𝗧𝗜𝗧𝗜𝗢𝗡 𝗕𝗬.
The PARTITION BY clause in SQL divides the result set into partitions or "windows" based on one or more columns.
Each partition is treated separately for calculations performed by a window function.
Unlike GROUP BY, it does not aggregate the data into a single row per group, meaning you can still see all the original rows in the output.
𝗪𝗵𝗮𝘁 𝗵𝗮𝗽𝗽𝗲𝗻𝗲𝗱 𝗶𝗻 𝘁𝗵𝗶𝘀 𝗾𝘂𝗲𝗿𝘆:
• The query calculates the average stock level for each product across all stores using the PARTITION BY clause. This clause groups the data by ProductId to ensure the average is calculated per product.
• These average stock levels are stored temporarily using a Common Table Expression (CTE).
• The query checks each store's stock level against the average within its product group.
• The final result shows each store's stock level, the average stock level for that product (calculated using PARTITION BY), and whether the stock is 'High', 'Low', or 'Normal'.
PARTITION BY is like a GROUP BY, but you keep the details.😉
Hope it helps :)
Here is a good example:
𝗧𝗵𝗲 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻:
Imagine you are helping the Inventory department to identify high or low stock levels at specific locations.
𝗧𝗼 𝘀𝗼𝗹𝘃𝗲 𝘁𝗵𝗲 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻, 𝘆𝗼𝘂 𝗻𝗲𝗲𝗱 𝘁𝗼:
• Establish a threshold to define high/low (we will use 10 units for this example).
• Calculate the stock level of each product
• Calculate the average stock level across all stores.
It sounds simple, but think again: You can't use a GROUP BY because you need the details for each product per store.
𝗧𝗵𝗲 𝘀𝗶𝗺𝗽𝗹𝗲𝘀𝘁 𝘄𝗮𝘆 𝘁𝗼 𝘀𝗼𝗹𝘃𝗲 𝘁𝗵𝗶𝘀 𝗽𝗿𝗼𝗯𝗹𝗲𝗺 𝗶𝘀 𝘂𝘀𝗶𝗻𝗴 𝗣𝗔𝗥𝗧𝗜𝗧𝗜𝗢𝗡 𝗕𝗬.
The PARTITION BY clause in SQL divides the result set into partitions or "windows" based on one or more columns.
Each partition is treated separately for calculations performed by a window function.
Unlike GROUP BY, it does not aggregate the data into a single row per group, meaning you can still see all the original rows in the output.
𝗪𝗵𝗮𝘁 𝗵𝗮𝗽𝗽𝗲𝗻𝗲𝗱 𝗶𝗻 𝘁𝗵𝗶𝘀 𝗾𝘂𝗲𝗿𝘆:
• The query calculates the average stock level for each product across all stores using the PARTITION BY clause. This clause groups the data by ProductId to ensure the average is calculated per product.
• These average stock levels are stored temporarily using a Common Table Expression (CTE).
• The query checks each store's stock level against the average within its product group.
• The final result shows each store's stock level, the average stock level for that product (calculated using PARTITION BY), and whether the stock is 'High', 'Low', or 'Normal'.
PARTITION BY is like a GROUP BY, but you keep the details.😉
Hope it helps :)
👍13❤2