- 45
- 37 276
@Sunny Tech Guru
India
เข้าร่วมเมื่อ 13 พ.ค. 2013
SQL query to find customers who have not placed any order. Session 11
SQL Queries and Interview Questions
• How to find customers who have not placed any order.
-------------------------------------------------------------------
create table customer
(
cust_id int,
cust_name varchar(20),
cust_address varchar(20)
)
insert into customer values(1, 'sunny', 'Noida')
insert into customer values(2, 'yogesh', 'delhi')
insert into customer values(3, 'nisha', 'greaternoida')
insert into customer values(4, 'daksh', 'gurgaon')
insert into customer values(5, 'rahul', 'goa')
create table orders
(
order_id int,
order_item varchar(20),
cust_id int
)
insert into orders values(1,'tv',4)
insert into orders values(2,'washingMachine',4)
insert into orders values(3,'bed',2)
insert into orders values(4,'sofa',2)
insert into orders values(5,'almira',3)
insert into orders values(6,'matress',3)
insert into orders values(7,'soap',4)
insert into orders values(8,'ac',4)
---------------------------------------------------------------------------------------
select * from customer where cust_id not in(select cust_id from orders)
select * from customer where cust_id in(select cust_id from orders)
select * from customer left join orders
on customer.cust_id= orders.cust_id where orders.cust_id is not null
select * from customer left join orders
on customer.cust_id= orders.cust_id where orders.cust_id is null
• How to find customers who have not placed any order.
-------------------------------------------------------------------
create table customer
(
cust_id int,
cust_name varchar(20),
cust_address varchar(20)
)
insert into customer values(1, 'sunny', 'Noida')
insert into customer values(2, 'yogesh', 'delhi')
insert into customer values(3, 'nisha', 'greaternoida')
insert into customer values(4, 'daksh', 'gurgaon')
insert into customer values(5, 'rahul', 'goa')
create table orders
(
order_id int,
order_item varchar(20),
cust_id int
)
insert into orders values(1,'tv',4)
insert into orders values(2,'washingMachine',4)
insert into orders values(3,'bed',2)
insert into orders values(4,'sofa',2)
insert into orders values(5,'almira',3)
insert into orders values(6,'matress',3)
insert into orders values(7,'soap',4)
insert into orders values(8,'ac',4)
---------------------------------------------------------------------------------------
select * from customer where cust_id not in(select cust_id from orders)
select * from customer where cust_id in(select cust_id from orders)
select * from customer left join orders
on customer.cust_id= orders.cust_id where orders.cust_id is not null
select * from customer left join orders
on customer.cust_id= orders.cust_id where orders.cust_id is null
มุมมอง: 150
วีดีโอ
SQL Query to find consecutive numbers. Session 10
มุมมอง 11821 วันที่ผ่านมา
SQL Queries and Interview Questions • How to find consecutive numbers create table test2 ( id int ) INSERT INTO test2 values (1), (2), (3), (6), (7), (9), (10), (11); with consecutive as ( select id, id-rank() over(order by id) as groupid from test2 ) select min(id) as startnumber, max(id) as endnumber from consecutive group by groupid
SQL Query to find employee with max salary for each department. Session 8
มุมมอง 14021 วันที่ผ่านมา
SQL Queries and Interview Questions How to find employee with max salary for each department create table emp ( empid int, firstname varchar(20), lastname varchar(20), salary int, age int, emailid varchar(20), managerid int, dob date, dept varchar(10) ) insert into emp values(1,'varun','dhawan',1000,30,'varun@123',5,'03/25/1990','IT') insert into emp values(2,'ram','lal',2000,31,'ram@123',3,'03...
SQL server Profiler. Session 32
มุมมอง 12821 วันที่ผ่านมา
SQL Server Profiler • It is a powerful tool used to monitor and capture the events occurring in SQL Server. • Used for troubleshooting performance issues, analyzing the workload, and auditing SQL Server activities.
SQL Server Multiple statement table valued function. Session 31
มุมมอง 108หลายเดือนก่อน
Multiple statement table valued function • Similar to inline table valued function with few differences • Return clause can contain the structure of the table • Multiple statement table valued function has begin and end block • Less performance than inline table valued function select * from empdetails create function mstvf() returns @table table(name1 varchar(20), grade1 varchar(10)) as begi...
SQL Server Table Valued Function. Session 30
มุมมอง 54หลายเดือนก่อน
Inline table valued functions • Returns a table. • Not enclosed between begin and end. • Can also be used in joins with other tables. select * from empdetails select * from emp create function regiondata(@place varchar(20)) returns table as return select * from empdetails where place=@place select * from dbo.regiondata('delhi') select e.id,e.name,r.grade from emp e left join dbo.regiondata('d...
SQL Server Scaler Function. Session 29
มุมมอง 34หลายเดือนก่อน
Scaler functions • May or may not have parameters, but always return a single value. select square(3) select getdate() declare @dob date declare @age int set @dob='03/25/1890' set @age=DATEDIFF(year,@dob,getdate()) select @age create function calculateage(@dob date) returns int as begin declare @age int set @age=DATEDIFF(year,@dob,getdate()) return @age end select dbo.calculateage('03/25/1990')
SQL Server Null Function and Comment. Session 28
มุมมอง 52หลายเดือนก่อน
Null Functions ISNULL () ISNULL () function is used to replace NULL values. Syntax: SELECT column(s) FROM table_name WHERE ISNULL(column_name); NULLIF () The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned. Syntax: SELECT column(s), NULLIF(expression1, expression2) FROM table_name; SELECT col1,col2, IFNULL(col...
SQL Server Cross Join. Session 27
มุมมอง 47หลายเดือนก่อน
Cross Join Cross Join in SQL produces a result set that contains the cartesian product of two or more tables. Cross join is also called a Cartesian Join. select * from a select * from b select * from a cross join b
SQL Server Self Join. Session 29
มุมมอง 75หลายเดือนก่อน
Self Join A self join is a regular join, but the table is joined with itself. create table company ( id int, empname varchar(20), empsalary int, managerid varchar(20) ) insert into company values(1, 'sunny',1000, 4) insert into company values(2, 'yogesh',1000, 3) insert into company values(3, 'nisha',1000, 2) insert into company values (4, 'ambika',1000, 1) select * from company select emp.id...
SQL Server Joins. Session 26
มุมมอง 54หลายเดือนก่อน
SQL Joins Inner Join Left Join Right Join Full Join SQL Joins Used to combine two or more tables, based on a related column between them select * from table1 select * from table2 select t1.id,t1.name,t2.place from table1 t1 join table2 t2 on t1.id=t2.id select * from table1 select * from table2 select t1.id,t1.name,t2.place from table1 t1 left join table2 t2 on t1.id=t2.id select * from...
Triggers for Update (Part 2). Session 25
มุมมอง 1122 หลายเดือนก่อน
Trigger It is a specialized category of stored procedure that is called automatically when a data modification event occurs against a table. Given below are various types of triggers that are getting used in SQL server. • After insert • After delete • After Update create table customer ( id int, name varchar(20), fathername varchar(30), address varchar(50), gender varchar(20) ) insert into cust...
Triggers for Insert and Delete (Part1). Session 24
มุมมอง 1002 หลายเดือนก่อน
Trigger It is a specialized category of stored procedure that is called automatically when a data modification event occurs against a table. Given below are various types of triggers that are getting used in SQL server. • After insert • After delete • After Update 1. CREATE TRIGGER trInsertEmployee 2. ON Employee 3. FOR INSERT 4. AS 5. BEGIN 6. Declare @Id int 7. SELECT @Id = Id from inserted 8...
Stored Procedure with Return Parameter (Part 3). Session 23
มุมมอง 873 หลายเดือนก่อน
Return parameter in stored procedure The Return parameter is used to return integer value from the stored procedure. if a stored procedure returns a value of 0, it means the execution is successful. Return Codes or values are commonly used in the control-of-flow blocks within procedures. We cannot return string value from the return parameter We cannot return multiple values from the re...
Stored Procedure with Output parameter. (Part 2). Session 24
มุมมอง 653 หลายเดือนก่อน
Output parameter in stored procedure The OUTPUT parameter is used when you want to return some value from the stored procedure. create procedure parameter_sp @id int, @outputvariable varchar(20) output as begin select @outputvariable = name from empdetails where id=@id end declare @outputvalue varchar(20) execute parameter_sp 1,@outputvalue out select @outputvalue alter procedure parameter_sp @...
Stored Procedure with Input Parameter (Part 1). Session 22
มุมมอง 2994 หลายเดือนก่อน
Stored Procedure with Input Parameter (Part 1). Session 22
Temporary Table (Global and Temporary) Session 20
มุมมอง 1094 หลายเดือนก่อน
Temporary Table (Global and Temporary) Session 20
SQL Server Functions Part 1. Session 19
มุมมอง 6714 หลายเดือนก่อน
SQL Server Functions Part 1. Session 19
SQL Constraints (Not Null, Unique, Check, Default, Primary and Foreign Key). Session 18
มุมมอง 3055 หลายเดือนก่อน
SQL Constraints (Not Null, Unique, Check, Default, Primary and Foreign Key). Session 18
Group By and Having Clause. Session 17
มุมมอง 1405 หลายเดือนก่อน
Group By and Having Clause. Session 17
Cast and Convert function. Session 16
มุมมอง 1085 หลายเดือนก่อน
Cast and Convert function. Session 16
SQL Server Date Functions. Session 14
มุมมอง 4316 หลายเดือนก่อน
SQL Server Date Functions. Session 14
SQL Query for Random and Round function. Session 7
มุมมอง 1176 หลายเดือนก่อน
SQL Query for Random and Round function. Session 7
SQL Server Numeric Functions (Session 13)
มุมมอง 2066 หลายเดือนก่อน
SQL Server Numeric Functions (Session 13)
SQL Server String Functions. Session 12
มุมมอง 3777 หลายเดือนก่อน
SQL Server String Functions. Session 12
SQL Basic and commonly asked SQL Queries. Session 6
มุมมอง 1K8 หลายเดือนก่อน
SQL Basic and commonly asked SQL Queries. Session 6
SQL Commands part 4(Transaction, Rollback & Commit) Session 11
มุมมอง 2828 หลายเดือนก่อน
SQL Commands part 4(Transaction, Rollback & Commit) Session 11
SQL Commands Part 3(Grant and Revoke). Session 10
มุมมอง 8179 หลายเดือนก่อน
SQL Commands Part 3(Grant and Revoke). Session 10
Very nice explain sir keep doing
Nice explanation
Nice video sir 👍
Awesome sir Please upload SQL videos like this👍
Nice explanation sir
Very well done 👍
Keep it up sir 👍
👍👍👍👍
Great 👍
Guys i have doubt please explain how it gives correct manager name
Hi, thanks for your comment, it will give us the manager-name on basis of manager-id column that we have in this table. Suppose If manager-id is 3 for an employee, it will check emp-name whose ID is equal to 3 and will gives us the empname for that ID and which will be manage-name for that employee.
@@sunnypandita1498 thank you so much sir
Keep it up sir
Great sir 👍
Expecting window functions
Very usefull sir
Thank You Sir
Nice video 👍
Great sir 👍
❤❤🎉🎉
Very well done 👍 keep doing 😊
Sir, videos for user defined functions to practtise thank you
Im waiting for ur next sessions
Great video sir 👍
Well explained sir .
Great sir 👍
Great video sir keep it up
Following from Long Sir, ur Content is Awesome
Very well done sir very nice video very well explained keep it up
❤❤ thanks
Great sir 👍 keep it up
Superb sir
Nice 👍 keep it up
Very nice video
Very well explained
nice, covered in detail. Thanks
Nice video sir keep it up 👍
Great video sir
Super
Great sir
Great video sir
Thankyou for keeping so simple
Nice video
Very nice explain keep it up
Thank you Sunny for sharing.
Hello Sir... you explain well, easy to understand and follow. how you create more videos :) it helps alot :)
New subscriber here from Dallas. I like how you make it look simple
😑 *Promo SM*
In mysql when trying to delete the duplicates, Im getting a message that says"table doesn't exist". How are you able to delete from a cte?
Hi, thanks for your comment. Table doesn't exist means there is no existing table. So, first need to create the table with same structure which is used in the session and next we need to execute CTE and next query together to perform the deletion..
bro atleast tag the person from where you taken the content .
Thanks for your comment, but focus should be on learning and to understand concepts. Regards content, there is nothing new, everything is existing, just way of showing things is different. My way is different hope you will like it and there might be resemblence but it is my own...
Great video sir
Its very good to understand.keep upload new challenges in sql sever.tq..