1.Employee tabledagi o'rtacha oylikdan yuqori maosh oladiganlarni ro'yxatini chiqaring.
2.Find the employes who earn the highest salary in each department
3.Find department who do not have any department
4.Find the employees in each department who earn more than the average salary in that department
5.Find department who do not have any employees

1.
Select * From dbo.employees
Where salary>(Select AVG(salary) From dbo.employees)

4. select employee_id from [dbo].[employees]
where department_id NOT IN
(select department_id from [dbo].[departments])

5.
select employee_id from [dbo].[employees]
where department_id NOT IN (select department_id from [dbo].[departments])

#Subquery table create table mahsulot(
id int primary key identity(1,1),
nomi nvarchar(99),
malumot nvarchar(99)
)
insert into mahsulot values
('tv','52 dyumli LCD'),
('Notebook','ofisniy oddiy'),
('Desktop','Stolniy kompyuter')

create table sotuv (
id int primary key identity,
mahsulot_id int not null,
narxi int not null,
soni int not null
)
insert into sotuv values
(3,450,5),
(2,250,7),
(3,450,4),
(3,450,9)


Create Table tblProducts
(
[Id] int identity primary key,
[Name] nvarchar(50),
[Description] nvarchar(250)
)

Create Table tblProductSales
(
Id int primary key identity,
ProductId int foreign key references tblProducts(Id),
UnitPrice int,
QuantitySold int
)

Insert into tblProducts values ('TV', '52 inch black color LCD TV')
Insert into tblProducts values ('Laptop', 'Very thin black color acer laptop')
Insert into tblProducts values ('Desktop', 'HP high performance desktop')

Insert into tblProductSales values(3, 450, 5)
Insert into tblProductSales values(2, 250, 7)
Insert into tblProductSales values(3, 450, 4)
Insert into tblProductSales values(3, 450, 9)

Bugun soat 3 da dars bor

WEEK 3 Join

left join

right join

Full join

cross join uchun misol

Join hammasi

Join davomi ikki tablda umumiy qismdan tashqi qisimlarni olish

Self join

join va subquery birgalikda ishlatilish shakli

Merge