- 40
- 5 041
Code SQL
India
เข้าร่วมเมื่อ 26 ก.ย. 2020
Welcome to @codesql! This channel is your go-to resource for mastering SQL, whether you're a beginner or looking to enhance your skills. As a Senior Statistical Programmer with experience at Parexel and Fortrea, I specialize in clinical data analysis and am excited to share my knowledge with you.
Here, you'll find step-by-step tutorials that break down SQL concepts into manageable parts, making learning easy and enjoyable. We’ll cover real-life examples from the clinical research field, showcasing how SQL is used to solve actual data problems. Expect tips and tricks to enhance your efficiency, as well as best practices for data management and analysis.
Join our community of learners, ask questions, and engage in discussions. Whether you're starting a new career or looking to improve your skills, @codesql is here to support you. Subscribe and hit the notification bell to stay updated on our latest content. Let’s unlock the power of SQL together!
Here, you'll find step-by-step tutorials that break down SQL concepts into manageable parts, making learning easy and enjoyable. We’ll cover real-life examples from the clinical research field, showcasing how SQL is used to solve actual data problems. Expect tips and tricks to enhance your efficiency, as well as best practices for data management and analysis.
Join our community of learners, ask questions, and engage in discussions. Whether you're starting a new career or looking to improve your skills, @codesql is here to support you. Subscribe and hit the notification bell to stay updated on our latest content. Let’s unlock the power of SQL together!
Complex SQL Query | Calculating Team Statistics | #sql
DROP TABLE CricketMatchResults;
-- Create the CricketMatchResults table
CREATE TABLE CricketMatchResults (
Team1 VARCHAR(50),
Team2 VARCHAR(50),
Winner VARCHAR(50)
);
-- Insert data into the CricketMatchResults table
INSERT INTO CricketMatchResults (Team1, Team2, Winner) VALUES
('India', 'Australia', 'India'),
('Australia', 'England', 'England'),
('South Africa', 'New Zealand', 'New Zealand'),
('England', 'South Africa', 'South Africa'),
('Australia', 'India', 'India');
select * from CricketMatchResults;
In this problem, we aim to calculate and analyze the performance statistics of various cricket teams based on match results. The dataset includes information about matches played between different teams, including the names of the teams, the outcomes of each match, and the winners.
The objective is to derive key statistics for each team, including:
Total Matches Played: The number of matches each team has participated in.
Total Wins: The number of matches each team has won.
Total Losses: The number of matches each team has lost.
By processing the match results, we will create a summary table that provides a clear overview of each team's performance. This analysis will help in understanding the strengths and weaknesses of the teams, enabling better strategic decisions in future matches.
The solution will involve using SQL queries to aggregate the data, ensuring accurate calculations of wins and losses for each team. This problem is essential for sports analysts, coaches, and team managers who seek to evaluate team performance and make informed decisions based on statistical insights.
-- Create the CricketMatchResults table
CREATE TABLE CricketMatchResults (
Team1 VARCHAR(50),
Team2 VARCHAR(50),
Winner VARCHAR(50)
);
-- Insert data into the CricketMatchResults table
INSERT INTO CricketMatchResults (Team1, Team2, Winner) VALUES
('India', 'Australia', 'India'),
('Australia', 'England', 'England'),
('South Africa', 'New Zealand', 'New Zealand'),
('England', 'South Africa', 'South Africa'),
('Australia', 'India', 'India');
select * from CricketMatchResults;
In this problem, we aim to calculate and analyze the performance statistics of various cricket teams based on match results. The dataset includes information about matches played between different teams, including the names of the teams, the outcomes of each match, and the winners.
The objective is to derive key statistics for each team, including:
Total Matches Played: The number of matches each team has participated in.
Total Wins: The number of matches each team has won.
Total Losses: The number of matches each team has lost.
By processing the match results, we will create a summary table that provides a clear overview of each team's performance. This analysis will help in understanding the strengths and weaknesses of the teams, enabling better strategic decisions in future matches.
The solution will involve using SQL queries to aggregate the data, ensuring accurate calculations of wins and losses for each team. This problem is essential for sports analysts, coaches, and team managers who seek to evaluate team performance and make informed decisions based on statistical insights.
มุมมอง: 11
วีดีโอ
RANK, DENSE_RANK, ROW_NUMBER SQL Analytical Functions Simplified #dataanalysis
มุมมอง 162 ชั่วโมงที่ผ่านมา
Welcome to my channel! Here, we simplify complex SQL concepts, focusing on analytical functions like RANK, DENSE_RANK, and ROW_NUMBER. Whether you're a beginner or an experienced data analyst, our videos break down these powerful SQL functions to help you understand how to rank and organize your data effectively. What You Can Expect: Clear Explanations: We simplify the intricacies of SQL analyt...
SQL | Find Each Flight's Source and Destination | Capgemini | #DataEngineering | #PowerBI | #sql
มุมมอง 152 ชั่วโมงที่ผ่านมา
Problem: th-cam.com/video/NuxMSyB07Ac/w-d-xo.html Lead Lag video: th-cam.com/video/PfAlQstJ26Y/w-d-xo.htmlsi=S6XnoN3Qx2RF4Nw2 DROP TABLE flight_info; CREATE TABLE flight_info ( id INT, source VARCHAR(50), destination VARCHAR(50) ); Step 2: Insert data into the flight_info table INSERT INTO flight_info (id, source, destination) VALUES (1, 'Delhi', 'Kolkata'), (2, 'Kolkata', 'Banglore'), (3, 'Mum...
SQL | From Transactions to Trends | #dataanalyst #sql
มุมมอง 82 ชั่วโมงที่ผ่านมา
Step 1: Create the Results table CREATE TABLE trns ( id INT PRIMARY KEY, country VARCHAR(50), state VARCHAR(50), amount DECIMAL(10, 2), frans_date DATE ); Step 2: Insert data into the Results table INSERT INTO trns (id, country, state, amount, frans_date) VALUES (11, 'US', 'approved', 1000, '2023-12-18'), (2, 'US', 'declined', 2000, '2023-12-19'), (3, 'US', 'approved', 2000, '2024-01-01'), (4, ...
SQL | Lead and Lag Function
มุมมอง 274 ชั่วโมงที่ผ่านมา
The LEAD and LAG functions in SQL are powerful window functions that allow users to access data from subsequent or preceding rows within the same result set. The LEAD function retrieves values from a specified number of rows ahead, while the LAG function fetches values from a specified number of rows behind. These functions are particularly useful for analyzing trends, calculating differences b...
SQL | Calculate Working Days Excluding Holidays | #datascience #sas
มุมมอง 3507 ชั่วโมงที่ผ่านมา
Step 1: Create the ticket table CREATE TABLE ticket ( ticket_id INT PRIMARY KEY, issue_date DATE, resolve_date DATE ); Step 2: Insert data into the ticket table INSERT INTO ticket (ticket_id, issue_date, resolve_date) VALUES (1, '2024-12-18', '2025-01-07'), (2, '2024-12-20', '2025-01-10'), (3, '2024-12-22', '2025-01-11'), (4, '2025-01-02', '2025-01-13'); Step 3: Create the holiday table CREATE ...
#sql | PIVOTAL table format | Interview Problem | Exl Services #dataanalyst #dataengineering
มุมมอง 1387 ชั่วโมงที่ผ่านมา
Description: In this SQL program, we will demonstrate how to convert employee salary data into a pivot table format, where each salary component (salary, bonus, hike percentage) becomes a separate column. We will then show how to convert this pivot table back to the original data format. This process is useful for data analysis and reporting, allowing for easier interpretation of salary compone...
SQL | Names of Managers who manage more than 4 employees | #sql #data
มุมมอง 4397 ชั่วโมงที่ผ่านมา
Step 1: Create the Employee table CREATE TABLE Employee ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), manager_id INT NULL ); Step 2: Insert data into the Employee table INSERT INTO Employee (id, name, department, manager_id) VALUES (1, 'John', 'HR', NULL), (2, 'Bob', 'HR', 1), (3, 'Olivia', 'HR', 1), (4, 'Emma', 'Finance', NULL), (5, 'Sophia', 'HR', 1), (6, 'Mason', 'Finance',...
SQL | Find each flight's source and destination | Capgemini | #dataengineering | #powerbi | #sql
มุมมอง 5477 ชั่วโมงที่ผ่านมา
DROP TABLE flight_info; CREATE TABLE flight_info ( id INT, source VARCHAR(50), destination VARCHAR(50) ); Step 2: Insert data into the flight_info table INSERT INTO flight_info (id, source, destination) VALUES (1, 'Delhi', 'Kolkata'), (2, 'Kolkata', 'Banglore'), (3, 'Mumbai', 'Pune'), (4, 'Pune', 'Goa'), (5, 'Kolkata', 'Delhi'), (6, 'Delhi', 'Srinagar'); select * from flight_info; In this video...
SQL | Find the minimum and maximum salary in each company | #sql | Interview Problem | #dataanalyst
มุมมอง 5417 ชั่วโมงที่ผ่านมา
DROP table employee; Create the employee table CREATE TABLE employee ( emp_id VARCHAR(10), company CHAR(1), salary INT, dept VARCHAR(50) ); Insert data into the employee table INSERT INTO employee (emp_id, company, salary, dept) VALUES ('emp1', 'X', 1000, 'Sales'), ('emp2', 'X', 1020, 'IT'), ('emp3', 'X', 870, 'Sales'), ('emp4', 'Y', 1200, 'Marketing'), ('emp5', 'Y', 1500, 'IT'), ('emp6', 'Y', ...
SQL | find max, min population city names by state | SQL Interview Question | #dataanalyst | #sql
มุมมอง 4067 ชั่วโมงที่ผ่านมา
Learn how to write a SQL query to find the city with the maximum and minimum population for each state. Create the CityPopulation table CREATE TABLE CityPopulation ( id INT PRIMARY KEY, state VARCHAR(50), city VARCHAR(50), population INT ); Insert sample data into the CityPopulation table INSERT INTO CityPopulation (id, state, city, population) VALUES (1, 'Maharashtra', 'Mumbai', 1000), (2, 'Ma...
SAS | Counting Consecutive Free Seats: A SAS Solution | #sas #interview #programming #problem
มุมมอง 5312 ชั่วโมงที่ผ่านมา
In this video, we explore how to efficiently count consecutive free seats using SAS programming. We will walk you through a step-by-step process to analyze seat availability data, identify blocks of free seats, and present the results in a clear and concise manner. Whether you're a beginner or looking to enhance your SAS skills, this tutorial will provide valuable insights and practical techniq...
Identifying Employees Currently in the Office | #accenture | #sas | #interview #programming
มุมมอง 6814 ชั่วโมงที่ผ่านมา
In this video, we dive into the world of employee attendance tracking using SAS. We will explore a dataset called EmployeeTimeData, which contains information about employees' check-in and check-out times. Join us as we analyze the data to determine which employees are currently in the office. We will cover: How to read and manipulate time data in SAS. Techniques for filtering records to identi...
Ernst & Young | Transform Data with SAS | Pivoting and Aggregating | EY | Interview Problem | #sas
มุมมอง 8014 ชั่วโมงที่ผ่านมา
Learn how to transform and organize your data efficiently using SQL! In this video, we'll show you how to pivot a dataset with products, amounts, and countries into a summarized table by country. We'll cover step-by-step how to aggregate and transpose your data to get the desired format. Whether you're working with SQL for data analysis or building reports, this tutorial will make pivoting in S...
SAS | Employees Earning More Than Their Managers | SQL Interview Question | cognizant #dataanalyst
มุมมอง 61114 ชั่วโมงที่ผ่านมา
Welcome to our channel! In this video, we tackle an intriguing #sql #interview #question : "Employees Earning More Than Their Managers." This problem is essential for #dataanalyst and #sql enthusiasts, especially those preparing for #interviews at top companies like #cognizant. In this tutorial, we will guide you through the process of creating an employees table and inserting sample data to an...
Find rows in one table that are not in another table | SAS | PROC SQL
มุมมอง 7821 ชั่วโมงที่ผ่านมา
Find rows in one table that are not in another table | SAS | PROC SQL
Understanding UNION and UNION ALL in SAS | Essential Interview Question
มุมมอง 50วันที่ผ่านมา
Understanding UNION and UNION ALL in SAS | Essential Interview Question
How to delete duplicates from tables I PROC SQL I SAS I Interview Problem
มุมมอง 137วันที่ผ่านมา
How to delete duplicates from tables I PROC SQL I SAS I Interview Problem
Find Duplicate Rows Using PROC SQL I SAS Interview Preparation I SAS
มุมมอง 132วันที่ผ่านมา
Find Duplicate Rows Using PROC SQL I SAS Interview Preparation I SAS
Barclays Interview Challenge | SAS Interview Question | Convert "Have" to "Want" Dataset
มุมมอง 165วันที่ผ่านมา
Barclays Interview Challenge | SAS Interview Question | Convert "Have" to "Want" Dataset
How to Extract Domains from Email Addresses | Barclays Interview Question | #sas
มุมมอง 275วันที่ผ่านมา
How to Extract Domains from Email Addresses | Barclays Interview Question | #sas
Second Highest Salary I SAS I Interview Problem
มุมมอง 56414 วันที่ผ่านมา
Second Highest Salary I SAS I Interview Problem
String Functions I COMPRESS, COMPBL, TRIM, and STRIP I SAS
มุมมอง 3014 วันที่ผ่านมา
String Functions I COMPRESS, COMPBL, TRIM, and STRIP I SAS
Understanding SCAN( ) Function in SAS I Interview Question
มุมมอง 4814 วันที่ผ่านมา
Understanding SCAN( ) Function in SAS I Interview Question
Translate and Tranward I SAS I Difference between TRANSLATE and TRANWRD Functions in SAS
มุมมอง 3114 วันที่ผ่านมา
Translate and Tranward I SAS I Difference between TRANSLATE and TRANWRD Functions in SAS
PROC SUMMARY vs PROC PROC I SAS Interview Question
มุมมอง 2514 วันที่ผ่านมา
PROC SUMMARY vs PROC PROC I SAS Interview Question
PROC SQL I 5.Where Clause and Filter Condition I SAS
มุมมอง 1414 วันที่ผ่านมา
PROC SQL I 5.Where Clause and Filter Condition I SAS
PROC SQL I 4.UNIQUE and DISTINCT I SAS
มุมมอง 1514 วันที่ผ่านมา
PROC SQL I 4.UNIQUE and DISTINCT I SAS
1 to Yes and 0 to No Conversion I SAS I Interview Problem
มุมมอง 1921 วันที่ผ่านมา
1 to Yes and 0 to No Conversion I SAS I Interview Problem
Make video about danse rank thing in sql by u
Thank you for the suggestion! I’ve already made a video covering DENSE_RANK, RANK, and ROW_NUMBER in SQL. You can check it out here: th-cam.com/video/JobstiSrhfc/w-d-xo.htmlsi=NrIPsd2sI8js4_II. Let me know your thoughts or if you'd like any additional topics explained!
We can also do this using array
If you have a solution using arrays, please feel free to share it here so others can benefit as well. Thanks for contributing to the discussion!
👌
😊
Hi,why you didn't excluded weekend days? I mean 5 days working
Thank you for your suggestion! I appreciate your feedback. I will definitely consider creating another video that focuses on excluding weekend days to reflect a 5-day workweek. Stay tuned for more content, and thanks for your continued support!
select * from ( select p1.state,p1.city as maxPopulation ,p2.city as minPopulation, row_number() over (partition by p1.state order by p1.state, p1.population-p2.population desc)as rn from CityPopulation p1 inner join CityPopulation p2 on p1.state = p2.state and p1.population > p2.population ) where rn=1 ; ---- SELECT DISTINCT p1.state, FIRST_VALUE(p1.city) OVER (PARTITION BY p1.state ORDER BY p1.population DESC) AS MaxPopulation, FIRST_VALUE(p2.city) OVER (PARTITION BY p1.state ORDER BY p2.population ASC) AS minPopulation FROM CityPopulation p1 INNER JOIN CityPopulation p2 ON p1.state = p2.state AND p1.population > p2.population;
???we can use lead lag??why make it complicated
Thanks for your suggestion! I'll definitely focus on making future videos simpler. Using LEAD and LAG is a great idea, and I'll include those concepts in an upcoming video. Appreciate your feedback!
Please find the new solution using LEAD and LAG here: th-cam.com/video/SSa5hwsfS2I/w-d-xo.html
@@CodeSQL great!
Thanks for uploading question. Hope to see more Vedios of solving problem.
I appreciate you watching! Stay tuned for more SQL interview problems.
Informative thank you , looking forward for more such informative content.😊
I'm glad you found it helpful! I'll keep creating more videos like this. 🙏
Grt one👍.......keep it up bro😍💯
Thanks for the support! I appreciate it! 🙏
i have a different approach of solving this problem. First, i performed inner join and found out the manager name for each employee. Then i used "where" clause to find out the salary comparision. select x.emp_id,x.name,x.sal as emp_sal,y.name as manager,y.sal as mgr_sal from a as x inner join a as y on x.mngr_id=y.emp_id where emp_sal>mgr_sal;
I have solved this problem earlier also but your way of explaining it with the diagram was fabulous, joins always create some kind of confusion while solving, specially self join, but the diagrammatic explanation was good, thank you.
I'm glad the diagram helped clear things up! 😊
Very helpful. ...keep posting sir
Thanks for watching! I'm glad you found it helpful.
excellent teaching
Thanks and welcome
@@CodeSQL bhaiya can you give me some advice . i am 2021 batch pass out . i have right now i have 4 years gap after graduation. currently my skills are PYTHON SQL NUMPY PANDAS and now i focus on POWERBI . IS THIS SKILLS IS GOOD TO GET A JOB BECAUSE I APPLY IN SO MANY COMPANY BUT NONE OF THEM REPLY . can you tell me what should i have to do and how much focus and how many projects should i make . or should i have to learn other skills
Focus on building 4-5 strong projects using Python, SQL, Pandas, and Power BI (e.g., data analysis, dashboards). Host them on GitHub or a portfolio. Add skills like Tableau, stats, or ML basics for better chances. Be honest about your gap-show how you’ve used the time to grow. Optimize LinkedIn, network, and apply for internships or entry-level roles. Stay consistent-results will come!
@@CodeSQL thankyou bhaiya i will work on this
What job position will ask this question? The software engineer or the Analyst? Thank you!
This question is asked for the Analyst role, specifically targeting candidates with 3-5 years of experience.
So, is this an Excel not PowerPoint? Looks very convenient.
Yes, this is Excel, not PowerPoint.
Grt 👍
I appreciate you watching! It means a lot to have your support. 🙏
Bro you doing good....please keep creating more videos. I have one request to you please create some questions on sas macros.
Thank you for your support! I'm glad you found the video helpful, and I'll definitely consider creating a video on SAS macros.
We can use scan (email,2,'@,.');
Thank you for this! We can definitely use scan(email, 2, '@,.') to extract the desired token from the email. Appreciate your input!
You can also try using scan function Scan(Scan(email, 2,"@"),1,".") ; Its simple and effective.
That's a great suggestion! The SCAN function is indeed a simple and effective way to extract parts of a string. In this case, using Scan(Scan(email, 2, "@"), 1, ".") will help extract the domain name from the email address, specifically up to the first dot after the "@" symbol. Thanks for sharing!
Sir kindly upload more interview questions and topic videos thank you 🙏
Thank you for your kind suggestion and support! 😊 I'll definitely work on creating more videos covering interview questions and important topics. Stay tuned, and feel free to share any specific areas you'd like me to focus on. Your feedback means a lot! 🙏
Bro I would like to know how you edited videos and presented.I would like to do for statistics videos.
@@venkatnaveen3267 You can use OBS Studio to edit your videos. It’s a great tool that allows you to record, stream, and make various edits to your footage. It should help you with most of your video editing needs!