- Видео 45
- Просмотров 37 451
@Sunny Tech Guru
Индия
Добавлен 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(...
• 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(...
Просмотров: 154
Видео
SQL Query to find consecutive numbers. Session 10
Просмотров 11928 дней назад
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
Просмотров 140Месяц назад
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
Просмотров 129Месяц назад
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
Просмотров 55Месяц назад
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
Просмотров 35Месяц назад
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
Просмотров 48Месяц назад
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
Просмотров 58Месяц назад
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
Просмотров 1123 месяца назад
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
Просмотров 1003 месяца назад
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
Просмотров 874 месяца назад
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
Просмотров 674 месяца назад
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
Просмотров 3004 месяца назад
Stored Procedure with Input Parameter (Part 1). Session 22
Temporary Table (Global and Temporary) Session 20
Просмотров 1134 месяца назад
Temporary Table (Global and Temporary) Session 20
SQL Server Functions Part 1. Session 19
Просмотров 6724 месяца назад
SQL Server Functions Part 1. Session 19
SQL Constraints (Not Null, Unique, Check, Default, Primary and Foreign Key). Session 18
Просмотров 3065 месяцев назад
SQL Constraints (Not Null, Unique, Check, Default, Primary and Foreign Key). Session 18
Group By and Having Clause. Session 17
Просмотров 1415 месяцев назад
Group By and Having Clause. Session 17
Cast and Convert function. Session 16
Просмотров 1106 месяцев назад
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
Просмотров 1186 месяцев назад
SQL Query for Random and Round function. Session 7
SQL Server Numeric Functions (Session 13)
Просмотров 2086 месяцев назад
SQL Server Numeric Functions (Session 13)
SQL Server String Functions. Session 12
Просмотров 3827 месяцев назад
SQL Server String Functions. Session 12
SQL Basic and commonly asked SQL Queries. Session 6
Просмотров 1 тыс.8 месяцев назад
SQL Basic and commonly asked SQL Queries. Session 6
SQL Commands part 4(Transaction, Rollback & Commit) Session 11
Просмотров 2909 месяцев назад
SQL Commands part 4(Transaction, Rollback & Commit) Session 11
SQL Commands Part 3(Grant and Revoke). Session 10
Просмотров 8559 месяцев назад
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..