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
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)
Select * From dbo.employees
Where salary>(Select AVG(salary) From dbo.employees)
2.
Select * From dbo.employees e
where e.salary = (
Select MAX(salary) as salary From dbo.employees
where department_id=e.department_id
)
Select * From dbo.employees e
where e.salary = (
Select MAX(salary) as salary From dbo.employees
where department_id=e.department_id
)
3.
SELECT department_id from dbo.departments
WHERE department_id NOT IN (Select DISTINCT department_id From dbo.employees)
SELECT department_id from dbo.departments
WHERE department_id NOT IN (Select DISTINCT department_id From dbo.employees)
4. select employee_id from [dbo].[employees]
where department_id NOT IN
(select department_id from [dbo].[departments])
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])
select employee_id from [dbo].[employees]
where department_id NOT IN (select department_id from [dbo].[departments])
Select employee_id, first_name, last_name,department_id,
(Select department_name From dbo.departments
Where department_id=e.department_id ) as department_name
From employees e.
(Select department_name From dbo.departments
Where department_id=e.department_id ) as department_name
From employees e.
#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)
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)