---#datepart hafta , kun , oydagi nechanchiligini chiqaradi yana vaziyatga qarab boshqa xususiyati ham bo'lishi mumkin
select datepart (day ,getdate())
select datepart (weekday ,getdate())--- nechanchi hafta ekanligi

---#dateadd -- kun ,oy,..... qo'shish yoki ayirish
select datename (day ,getdate())

---#datediff 2 vaqt orasidagi farqni hisoblash

#ipotant thing Calculating age

create table Age1 (
id int identity(1,1) primary key,
name nvarchar(123),
dateOf datetime
)
insert into Age1(name, dateOf)
values
('Sam','1980-12-30 00:00:00.000'),
('Umid','2000-01-27 00:00:00.000'),
('John','1985-08-22 12:03:30.370'),
('Sara','1979-11-29 12:59:30.670')

select * from age1

declare @DOB datetime , @tmpdate datetime , @years int, @months int, @days int
set @DOB = '10/08/1982'

select @tmpdate = @DOB

select @years = datediff(year,@tmpdate, getdate())-
case
when (month(@DOB)>month(getdate())) or
(month(@DOB)=month(getdate()) and day(@DOB) > day(getdate()))
then 1 else 0
end
select @tmpdate = dateadd(year, @years,@tmpdate)

select @months = datediff(month,@tmpdate ,getdate())-
case
when day(@dob)>day(getdate())
then 1 else 0
end
select @tmpdate = dateadd(month, @months, @tmpdate)

select @days = datediff(day, @tmpdate, getdate())

select @years as Years, @months as Months, @days as [Days]

BI learners degan gurpa ochdim shu guruhda dars bo'ladi Seshanba Payshanba yangi kirgan bacha silaga sqlda tushuncha beradi keyin Yakshanba muhokama qilamiz vazifalarni bu yog'iga qattiq olasila

xay domlo

Style 101, 102,103,104,105 sanani yozish turlari misol: 101=mm/dd/yyyy m=month d=day y=year shu ketmaketlikda yozilsa 101 kodli usulda yozilgan deyish mumkin

#cast and #convert malumot tipini o'zgartirish

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