.... lr,J "'t: 1 H IQ::.LC I l;!:1" 1 t1:tt o '"-".

EJ create table pproducts ( pid int primary key,

pname varchar(20) not null, brandid i nt unique ,
cid int fore i gn key references pcategories (cid ),
modelyear int not null, price int not null)
E] create table pcategories ( cid int primary key,
l cname varchar (20) not null)
select * from pproducts
select * from pcategories
EJ insert into pproducts values (12, ' soap' , 845 , 63 , 2000, 52 ),
( 10, 'laptop ' , 451 , 21 , 2006 , 35000 ),( 14, ' sofa' , 615 , 13 , 1990, 15000 ),
(42 , 'bed' , 91 , 19, 1996, 10000 ),( 85 , ' biscuit' , 85 , 79 , 2003 , 25 ),
(34, 'tiles' , 121, 59 , 2016, 3000 )
E] i nsert into pcategories values (63 , 'toil etries' ),

l (21 , 'el ect ron i cs' ),(13, 'leat her' ),( 19, 'wood' ),
( 79 , 'eatable' ),( 59 , 'constructi on' )
 f]itd pna:me brandid cid modEfy.car pnce
~I
II l ,til [aptnp 451 2.1 2IK16 35000

...
~
2 12 soap 845 6J
13
2000 52
·1sOCHl
3 14 safa 615 1900
4 ]4. fiih~s 121 59 2D16 JOOO
II 5 42 hed g1 19 1996 10000
6 85 oiseur~ 85 79 200] 25
[ill

, cld cname
['131re~lher
2 19 wood
IJ 2, el'ec:tronrSJ
4 59 conslruction
5 63 u,111elries
15 79 ealobfe
 - ' -
EJ create table pproducts (pid int primary key,
pname varchar (20 ) not null, brandid int unique ,
cid int foreign key references pcategories (cid ),
modelyear int not null, price int not null)
E) create table pcategories ( cid int primary key ,
l cname varchar (20) not null)
select * from pproducts
select * from pcategories
EJ insert into pproducts values
(15, ' smart watch' , 452, 21, 2006, 3000 ) , (14, 'sofa' , 615 , 13 , 1990, 1500
(42, ' bed ' , 91 , 19, 1996, 10000 ),( 85 , 'biscui t ' , 85 , 79, 2003 , 25 ),
( 34, ' tiles ' , 121 , 59 , 2016 , 3000 )
E) insert into pcategories values (63 , ' toiletries ' ),

l (21 , ' electronics' ),( 13, ' leather ' ),( 19, ' wood ' ),
(79 , ' eatable' ),( 59 , 'construction' )
~ select pname , cname , price from pproducts inner join pcategories
Lon pproducts . cid=pcategories . cid
E] select c . cname , p . pname , count (p . pid ) from pproducts p

l left join pcategories c on p. cid=c . cid group by

c . cname , p. pname order by c . cname , p . pname
gJ rµname cname
t ,e fed:ro
E .
price
l 50DD
--· Il ] h~ptop
E
·- nu::s

2 mabile efe:ctmnics 3□00

➔
4--
3 ~p to~telries 52
~ ~chi leather 79000
111 6 sm-anwab:h etecttoniec 3000
16 li~e3 construdi on· 3000
@I
? bed vtood 10DDD
8 btscuil ,ealabte 25

cname pname (No oo-1 u mn narn_

1 ] ronslrticlion ft Iiies 1
2 eatab !e biscun 1
3 e ledronics lo!p:mp 1
4 elecb"on,cs mob1fe ,
,
6
16
e-1ectron,a
!leather
!tmairl wateh
cmfa ,,
7
8
m1 lelrie-s
1

wood
~p
bed ,
E] create table employee ( empid int primary key ,

l
efname varchar ( 20 ) not null, elname varchar (20 ) not null,
age int check(age>21 ), emailid varchar (25 ) un ique ,
phno bigint not null, address varchar (20 ) not null)
select * f rom employee
insert into employee values (l , 'vardhan ' , 'kuma r' , 22, 'vardy@abc.com ' , 9876543210, 'delhi ' )
insert into employee values (2, 'himani' , 'sharma ' , 32, ' himani@abc.corn ' , 9977554422, ' mumbai' )
insert into employee values ( 3, 'aayushi' , ' shreshth ' , 24, ' aayushi@abc.com ' , 9977555121, 'kolkata' )
insert into employee values (4, 'hemanth' , ' sha r ma ' , 25 , ' hemanth@abc.corn' , 9876545666, ' bengaluru' )
insert into employee values (5, 'swatee' , 'kapoor' , 26, ' swatee@abc.corn' , 9544567777, 'hyde r abad' )
E] create table project (pid int unique , empid int f oreign key references employee (empid ),
l cid int not null, pname varchar (20 ) not null, psd date default getdate ())
select * f rom project
a insert into project ( pid, empid, cid, pname ) values (111, 1, 3, 'project 1 ' ),( 222, 2, 1, 'pr oject 2' ),
l( 333, 3, 5, 'project 3 ' ),( 444, 3, 2, 'project ~ ' ),( 555 , 5, 4, ' project 5' )
..1..
empid efname elname age emailid phno a ddress
...
• i;t
1 I, j vardhan kumar 22 vardy@abc.com 9876543210 delhi

...... 2
3
2
3
himani
aayushi
sharma
s hreshth
32
24
hima ni@abc.com
aayushi@abc.com
9977554422
9977555121
mumbai
kolkala
4 4 hemanth sharma 25 h emanlh@abc.com 9876545666 bengaluru
IIBl 5 5 swatee kapoor 26 .swatee@abc.com 9544567777 hyderabad

( li!l

pod emp .. e,d pname psd

1 D2I] 1 3 projeel 1 2023-09-18
2 222 2 1 project 2 2023-09-18
3 333 3 5 projeel 3 2023-09-18
4 444 3 2 project 4 2023-09-18
5 555 5 4 project 5 2023-09-18
 V J:i.l ~'l l l lld:)lt:I .I El' I - - 6 ~ :

[ phno bigint not null , address varchar (20 ) not null)

insert into employee values (l , 'vardhan' , ' kumar ' , 22 , 'vardy@abc.eom' , 9876543210, 'delhi' )
insert into employee values (2, 'himani' , ' sharma' , 32 , 'himani@abc.eom' , 9977554422, 'mumbai' )
insert into employee values (3, 'aayushi' , ' shreshth' , 24, ' aayushi@abc.eom' , 9977555121, 'kolkata ' )
insert into employee values (4, 'hemanth ' , ' sharma' , 25, ' hemanth@abc.com' , 9876545666, ' bengaluru ' )
insert into employee values ( 5, 'swatee' , 'kapoor ' , 26, ' swatee@abc.com ' , 9544567777, 'hyderabad ' )
E create table project (pid int unique , empid int foreign key references employee (empid ),
l cid int not null , pname varchar (20 ) not null, psd date default getdate ())
select · from employee
select * from project
a insert into project (pid, empid, cid, pname ) values (111, 1, 3, ' project 1' ),( 222, 2, 1, ' project 2 ' ),
l (333, 3, 5, 'project 3' ),(444, 3, 2, 'project 4' ),( 555 , 5, 4 , 'project 5' )
E select efname+' ' +elname, pid, pname from employee left join project
ll on employee . empid=project . empid

135 % •
~ T-SQL rl Ill Results I ll!l Message
(No column Mm._ pid pneme
1 Iverdhlln kumar j 111 project 1
2 h1mani sharma 222 project 2
3 aayush, shreshlh 333 project 3
4 a ayushi slveshlh 444 project4
5 hemanlh sharma NULL NULL
6 swatee kaPoO< 555 project 5
 ~ create table project ( pid int unique, empid int foreign key references employee ( empid ),
Leid int not null, pname varchar ( 20) not null, ps d date default getdate ( ) )
select • from employee
select • from project
EJ insert into project ( pid , empid, cid, pname ) values ( 111, 1, 3, 'project 1' ) ,(222, 2, 1, ' project 2' ),
l (333, 3, 5, 'project 3' ) , (444, 3, 2, ' project 4' ) , ( 555, 5, 4, 'project 5' )
~ select efname+ ' ' +elname , pid, pname from employee left join proj ect
L on employee . empid=pr oj ect . empid

E] select e . empid, e . efname , count ( p . pid ) as pcount from employee e left join project p
l on e . empid=p. empid group by e . empid, e . efname order by pcount desc

35% ·I
g T-SQL ti Iii Results @I Message
emp1d efname pcount
I [i___j "ayushl 2
1 vardhan 1
2 himani
5 swatee 1
4 hemanU, 0