#Except 3

Google Lens orqali tarjima qilindi. Sinab ko‘ring!
https://g.co/lenstranslate

SQL HAVING Tutorial | LearnSQL.com
https://learnsql.com/blog/sql-having-clause-tutorial/

#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