Space bo'sh joy qo'shish

SELECT email,patindex('%@aaa.com', email) as firstOccurence
from Nurbek3
where patindex('%@aaa.com', email) >0


SELECT email,patindex('%@%', email) as firstOccurence
from Nurbek3
where patindex('%@%', email) >0


--- #Patindex bu ixtiyoriy belgini matndagi(so'zdagi)
--- nechinchi o'rindaligini chiqaradi

select email, replace(email,'.com','.uz') as convertedemail
from Nurbek3

select email from Nurbek3

select replace(email,'.com','.uz') as convertedemail
from Nurbek3

--- #replace so'zni malum bir qismini o'zgartirib chiqarishda ishlatiladi

select fn,ln,email,stuff(email,2,3,'*****') as stuff_email from Nurbek3

--- #Stuff replace ga o'xshash vazifasi
--- misolda emailda 2-belgidan boshlab 3 ta belgini o'rniga * qo'yish ko'rsatilgan

select GETDATE(),

create table Datetype (
time_ time,
date_ date,
smalldatetime_ smalldatetime,
datetime_ datetime,
datetime2_ datetime2,
datetimeoffset_ datetimeoffset
)
insert into Datetype
values (GETDATE(),GETDATE(),GETDATE(),GETDATE(),GETDATE(),GETDATE())

select * from Datetype

---#Datatypes

#datename oy yoki kun nomini chiqaradi

---#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])