#Coalesce create table AAAA(
id int identity(1,1) primary key,
fn nvarchar (123),
mn nvarchar (123),
ln nvarchar (123)
)
insert into AAAA
values ('sam',null,null),(null,'Tod','Tarzan'),(null,null,'Sara')
,('Ben','parkovka',null),('Jamshid','fensi','Ferferro')
select * from AAAA

select id, coalesce(fn,mn,'Fakaferro')
from AAAA

#Intersect CReate table tableC(
ID int identity(1,1),
Name nvarchar (100),
Gender nvarchar(100)
)

insert into tableC
values ('Mark','Male'),
('Mary','Female'),
('Steve','Male')

Create table tableD(
ID int,
name nvarchar(200),
Gender nvarchar(100)
)
insert into tableD
values(2,'Mary','Female'),(3,'Steve','Male')

select ID, Name, Gender from tableC
intersect
select ID, Name, Gender from tableD



select tableC.ID, tableC.Name, tableC.Gender from tableC
inner join tableD on tableC.ID = tableD.ID

WEEK 2 #i11-dars create table Nurbek3(
id int identity (1,1) primary key,
fn nvarchar(123),
ln nvarchar(123),
email nvarchar(123)
)
insert into Nurbek3(fn,ln,email)
values
('sam','sony','sam@aaa.com'),
('ram','barber','ram@aaa.com'),
('sara','sanosky','sara@ccc.com'),
('todd','gartner','todd@bbb.com'),
('john','grover','john@aaa.com'),
('sana','lenin','sana@ccc.com'),
('james','bond','james@bbb.com'),
('rob','hunter','rob@ccc.com'),
('steve','wilson','steve@aaa.com'),
('pam','broker','pam@bbb.com')

select * from Nurbek3

select charindex('@','sara@ccc.com')



select substring('sara@ccc.com', charindex('@','sara@ccc.com')+1,7)



select substring('sara@ccc.com', charindex('@','sara@ccc.com')+1,
len('sara@ccc.com')-charindex('@','sara@ccc.com')
)

select substring(email, charindex('@',email)+1,
len(email)-charindex('@',email)
) as Nurbek_pochta
from Nurbek3

select substring(email, charindex('@',email)+1,
len(email)-charindex('@',email)
) as Nurbek_pochta,
count (email) as total
from Nurbek3
group by substring(email, charindex('@',email)+1,
len(email)-charindex('@',email)
)

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)