Using SQL Server Profiler to Observe SQL Query Plans

create table project
(
pname varchar(50)
, pnumber int
, plocation varchar(50)
, dnum int
)

insert into project values ('productx', 1, 'bellaire', 5)
insert into project values ('producty', 2, 'sugarland', 5)
insert into project values ('productz', 3, 'houston', 5)
insert into project values ('computerization', 10, 'stafford', 4)
insert into project values ('reorganization', 20, 'houston', 1)
insert into project values ('newbenefits', 30, 'stafford', 4)



select * from project where plocation = 'stafford'
select plocation, dnum from project

create table r
(
a int
, b int
)

create table s
(
c int,
d int,
e int
)

insert into r values (1, 2)
insert into r values (3, 4)
insert into s values (5, 6, 7)
insert into s values (8, 9, 10)
insert into s values (11, 12, 13)

select * from r,s


create table rj
(
a int
, b int
)

create table sj
(
b int,
c int,
d int
)

insert into rj values (1, 2)
insert into rj values (3, 4)
insert into rj values (5, 6)
insert into sj values (4, 5, 6)
insert into sj values (6, 7, 8)
insert into sj values (8, 9, 10)

select * from rj, sj where rj.b = sj.b
select * from rj join sj on rj.b = sj.b

create table person
(
pid int
, name varchar(50)
)

create table car
(
pid int
, brand varchar(50)
)

insert into person values(1273, 'dylan')
insert into person values(2244, 'cohen')
insert into person values(3456, 'reed')
insert into car values (1273, 'cadillac')
insert into car values (1273, 'vw beetle')
insert into car values (3456, 'stutz bearcat')

select distinct person.pid, person.name from person join car on person.pid = car.pid

select r.a, r.b, NULL c, NULL d, NULL e from R
UNION
select NULL a, NULL b, s.c, s.d, s.e from S

select r.a, r.b, NULL c, NULL d, NULL e from R
UNION ALL
select NULL a, NULL b, s.c, s.d, s.e from S

select rj.b from rj
intersect
select sj.b from sj

select rj.b from rj
except
select sj.b from sj

create table instructor
(
id int
, name varchar(50)
, dept_name varchar(50)
, salary int
)

insert into instructor values (76766, 'crick', 'biology', 72000)
insert into instructor values (45565, 'katz', 'cs', 75000)
insert into instructor values (10101, 'srinivasan', 'cs', 65000)
insert into instructor values (83821, 'barndt', 'cs', 92000)
insert into instructor values (98345, 'kim', 'electrical', 80000)
insert into instructor values (12121, 'wu', 'finance', 90000)

select avg(salary) as avg_salary from instructor
select dept_name, avg(salary) as avg_salary from instructor group by dept_name
select i.dept_name, avg(i.salary) as avg_salary from instructor i group by i.dept_name

select dept_name, avg(salary) as avg_salary from instructor group by dept_name
having MIN(salary) > 80000

select rj.a from rj
where exists (
select COUNT(sj.c) from sj
where rj.b = sj.b
having count(sj.c) = rj.a
)