Видео

Great explanation sir❤

IN SQL SERVER (SSMS) -- List all authors and number of books written select a.AuthorName, COUNT(b.BookID) as [Number of Books] from Authors as a LEFT OUTER JOIN Books as b on a.AuthorID = b.AuthorID group by a.AuthorName order by a.AuthorName; -- Find books published in the last year and corresponding authors select b.BookTitle, a.AuthorName, YEAR(b.PublicationYear) as PublicationYear, YEAR(GETDATE()) as CurrentYear, YEAR(GETDATE()) - 1 as LastYear from Authors as a inner join Books as b on a.AuthorID = b.AuthorID where YEAR(b.PublicationYear) = YEAR(GETDATE()) - 1; happy quering :)

in sql server (ssms) -- Retrieve the total revenue generated from each product. ;with cte_totalrevenue as ( select p.ProductName, p.ProductPrice * o.Quantity as Revenue from products as p inner join orders as o on p.productid = o.productid ) select ProductName, SUM(Revenue) as Total_Revenue from cte_totalrevenue group by ProductName order by ProductName; select p.ProductName, SUM(p.ProductPrice * o.Quantity) as Total_Revenue from Products as p inner join Orders as o on p.ProductID = o.ProductID group by p.ProductName order by p.ProductName; -- Find products that have not been ordered at all. select p.ProductName, o.OrderID from products as p left outer join orders as o on p.productid = o.productid where o.OrderID is null; -- using subquery select p.ProductName from Products as p where p.ProductID not in (select o.ProductID from Orders as o)

IN SQL SERVER (SSMS) select CAST(reading_time as date) as reading_date, CAST(AVG(temperature) as decimal(10, 2)) as avg_temperature from temperature_readings group by CAST(reading_time as date);

in sql server using t-sql (ssms) -- find the highest salary select * from Employees_Data where Salary = ( select MAX(Salary) as Highest_Salary from Employees_Data) ;with cte_temp as ( select *, DENSE_RANK() OVER (ORDER BY Salary DESC) as Drnk from Employees_Data ) select EmployeeId, FirstName, LastName, Gender, Salary from cte_temp where cte_temp.Drnk = 1 select EmployeeId, FirstName, LastName, Gender, Salary from( select *, DENSE_RANK() OVER (ORDER BY Salary DESC) as Drnk from Employees_Data ) as temp where temp.Drnk = 1 -- find the 2nd highest salary select * from Employees_Data where Salary = ( select MAX(salary) from Employees_Data where Salary <( select MAX(Salary) from Employees_Data)) -- select * from Employees_Data where Salary = ( select top 1 salary from ( select distinct top 2 Salary from Employees_Data order by Salary desc) as temp order by Salary asc); -- ;with cte_temp as ( select *, DENSE_RANK() OVER (ORDER BY Salary DESC) as DRnk from Employees_Data ) select EmployeeId, FirstName, LastName, Gender, Salary from cte_temp where cte_temp.DRnk = 2 -- select * from Employees_Data as e1 where 2 = (select COUNT(DISTINCT Salary) from Employees_Data as e2 where e2.Salary >= e1.Salary) -- 2nd or 3rd or 10th declare @n int set @n = 2 select * from Employees_Data as e1 where @n = (select COUNT(DISTINCT Salary) from Employees_Data as e2 where e2.Salary >= e1.Salary) happy querying :)

in sql server using t-sql (SSMS) -- using substring function select EmployeeID, FirstName+', '+LastName as EmployeeName, Salary, DateHired, EmailAddress, SUBSTRING(EmailAddress, CHARINDEX('@', EmailAddress) + 1, LEN(EmailAddress)) as [DomainName] from Employees_Domains -- using right function select EmployeeID, FirstName+', '+LastName as EmployeeName, Salary, DateHired, EmailAddress, RIGHT(EmailAddress, LEN(EmailAddress) - CHARINDEX('@', EmailAddress)) as DomainName from Employees_Domains -- get the user name using the substring function select EmployeeID, FirstName+', '+LastName as EmployeeName, Salary, DateHired, EmailAddress, SUBSTRING(EmailAddress, 1, CHARINDEX('@', EmailAddress) - 1) as [UserName] from Employees_Domains -- get the user name using the left function select EmployeeID, FirstName+', '+LastName as EmployeeName, Salary, DateHired, EmailAddress, LEFT(EmailAddress, CHARINDEX('@', EmailAddress) - 1) as UserName from Employees_Domains

in sql server, -- using the pivot operator select month, [Clothing], [Electronics] from ( select month, category, amount from sales_data ) as Source_Data PIVOT ( SUM(amount) FOR category IN ([Clothing], [Electronics]) ) as Pivot_Data; -- without using the pivot operator select month, SUM(case when category = 'Clothing' THEN amount else null end) as [Clothing], SUM(case when category = 'Electronics' THEN amount else null end) as [Electronis] from sales_data group by month;

for people who want the queries in T-SQL (working in SSMS) -- Find the top 3 highest-paid employees in each department. ;with cte_temp as ( select CONCAT(emp.FirstName, ' ', emp.LastName) as [Employee Name], emp.Salary, dept.DepartmentName, ROW_NUMBER() OVER (PARTITION BY emp.DepartmentID ORDER BY emp.Salary DESC) as RNo from Employees as emp inner join Departments as dept on emp.DepartmentID = dept.DepartmentID ) select [Employee Name], Salary from cte_temp where cte_temp.RNo <=3; -- find top employee in each department ;with cte_temp as ( select CONCAT(emp.FirstName, ' ', emp.LastName) as [Employee Name], emp.Salary, dept.DepartmentName, ROW_NUMBER() OVER (PARTITION BY emp.DepartmentID ORDER BY emp.Salary DESC) as RNo from Employees as emp inner join Departments as dept on emp.DepartmentID = dept.DepartmentID ) select [Employee Name],DepartmentName, Salary from cte_temp where cte_temp.RNo = 1; -- Find the average salary of employees hired in the last 5 years. select EmployeeID, FirstName+' '+LastName as EmployeeName, Salary, DateHired, YEAR(DateHired) as Hired_Year, YEAR(GETDATE()) as Current_Year, YEAR(GETDATE()) - YEAR(DateHired) as Year_Diff from Employees where YEAR(GETDATE()) - YEAR(DateHired) <= 5 select CAST(AVG(Salary) as decimal(10, 2)) as Avg_Salary from Employees where YEAR(GETDATE()) - YEAR(DateHired) <= 5 -- Find the employees whose salry is less than the average salary of employees hired in the last 5 years. select EmployeeID, FirstName+', '+LastName as [EmployeeName], Salary, DateHired from Employees where Salary < ( select CAST(AVG(Salary) as decimal(10, 2)) as Avg_Salary from Employees where YEAR(GETDATE()) - YEAR(DateHired) <= 5 ) thank you for the script which made my job easy... :)

WITH CTE AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY student_name ORDER BY marks DESC) AS rnk FROM students ) SELECT student_name, SUM(marks) AS marks FROM CTE WHERE rnk <= 2 GROUP BY student_name;

Select month, [electronics], [clothing] from ( select month, category,amount from input_tbl) as source PIVOT(sum(amount) for category in [(electronics),(clothing)] as pivot_table

Super sir.please write the query to get the height salary name and development wise.

Please put he create ans insert command also

select * from sales_data pivot (sum(amount) for category in ([Electronics],[Clothing]))as piv_tbl;

select month, SUM(case when category = 'Clothing' then amount end) as "Clothing", SUM(case when category = 'Electronics' then amount end) as "Electronics" from sales group by month order by month;

Most simple way to solve the question: SELECT student_name, SUM(marks) [Total Marks] FROM students GROUP BY student_name

with cte_topmarks as ( Select *,row_number() over(partition by student_name order by marks desc) as rnk from students ) Select student_name,sum(marks) as total_marks from cte_topmarks where rnk in(1,2) group by student_name

with t as ( select generate_series(1,20) as e ) select e as order_id from t where e not in (select order_id from order_0108);

select month, sum(case when category = 'Clothing' then Amount end) as Clothing, sum(case when category = 'Electronics' then Amount end) as Electronics from sales_data group by month order by month;

with t as ( select student_name, marks,dense_Rank()over(partition by student_name order by marks desc) as rnk from student_0108 ), t1 as ( select * from t where rnk <=2 ) select student_name,sum(marks) as marks from t1 group by student_name;

select reading_time,round(avg(temperature),2) as average_temperature from temperature_readings group by reading_time

q1 ) select p1.ProductName,p1.productid,sum(p1.productprice * p2.quantity) as total from products p1 inner join orders p2 on p1.productid=p2.productid group by p1.ProductName,p1.productid q2) select * from Products where ProductID not in (select productid from orders)

SELECT month, SUM(CASE WHEN category='Clothing' then amount end) as Clothing, SUM(CASE WHEN category='Electronics' then amount end) as Electronics FROM sales_data GROUP BY month;

WITH cte_table AS( SELECT month, category, amount, CASE WHEN month = 'January' THEN 1 WHEN month = 'February' THEN 2 WHEN month = 'March' THEN 3 WHEN month = 'April' THEN 4 END AS month_order FROM sales_data ) SELECT month,Clothing,Electronics FROM ( SELECT * FROM cte_table) AS source_table PIVOT( MAX(AMOUNT) FOR CATEGORY IN(Clothing,Electronics) ) AS pivot_table ORDER BY month_order

WITH orders_cte as( select order_id, LAG(order_id) over(order by order_id ) as prev_order_id from dsc_orders ) select order_id,prev_order_id+1 AS missing_order_id from orders_cte WHERE prev_order_id IS NOT NULL AND prev_order_id + 1 <> order_id AND prev_order_id + 1 < order_id ORDER BY missing_order_id;

Solve more questions on join and analytical functions

Date ( reading_ time ) is it work in Oracle SQL developer

Nice explanation 👍

select value as order_id from generate_series(1,20) except select order_id from orders or with cte as( select *, ROW_NUMBER() over (order by order_id) RN, LEAD(order_id) over (order by order_id) - order_id as diff from orders) select order_id+1 order_id from cte where diff>1

How to solve in SQL developer

Amazing!

use Dense_rank instead of row_number because your logic fail when two employees have same salary in same department ----Assuming you want different salary top 3 salary employees But if you want only top 3 employees regardless of their same salary or different salary then row number is correct

Would be great if you could provide table creation vommand in description

instead of using pivot/ cross tab we can also do the below which is can run on any platform SELECT months, sum(case when category='Clothing' then Amount end) as Clothing, sum(case when category='Electronic' then Amount end) as Electronic from Months group by months;

why you are not providing create & insert statement after every video in the description box. I have given this a feedback.

Sir thoda sa font size increase kro please.

Select *,concat(replace(Name," ","."),"@",domins) as email from domin;

RIGHT(email,(len(email)-locate('@',domain))) should this work as well? i dont have Sever to test

Please upload a video on display domain name from the given email id

at 6:09 isnt it pointless to do it with a subquery, you have already ordered them and they are distinct as well, just directly add limit and offset to the subquery and we'll get the answer. Also to fetch nth highest salary, offset should be n-1*

Great work! Keep making more!

Great work! Keep making more!

Looking forward the next part. Thank you