SQL Queries

1. select all the records from titles table
select * from titles


2. select all the books price>=20 rupees
select title,type,price from titles
where price>=20

3. select all the titles price between 10 and 20 rupees
select title,type,price from titles
where price>=10 and price<=20

select title,type,price from titles
where price not between 10 and 20


4. select all the titles not between 10 and 20 rupees
select title,type,price from titles
where price <10 or price>20

5. select the publishers from AP and DC
select pub_name,state from publishers
where state='AP' or state='DC'

6. select the titles where price is null value
select * from titles
where price is null

7.select the titles having price
select * from titles
where price is not null

8.select all the titles not starting with s
select * from titles
where title not like 's%'

9. select all the titles expect business, mod_cook, trad_cook
select * from titles
where type not in('business' ,'mod_cook' ,'trad_cook')

10. select the sum of prices for the titles grouped by type
select type, sum(price) 'Total' from titles
group by type

select type, count(title_id) 'count of books' from titles
where type in ( 'mod_Cook','psychology','business')
group by type
having count(title_id)>3
order by type desc


11. select titles order by price
select * from titles
order by price

12. using joins
select title,pub_name,au_fname,au_lname
from titles t
join publishers p
on t.pub_id= p.pub_id
join titleauthor ta
on t.title_id=ta.title_id
join authors a
on ta.au_id=a.au_id



select * from publishers
where pub_id not in(select pub_id from titles)


select * from publishers
where pub_id in(select pub_id from titles)


select * from publishers
where pub_id in( select pub_id from titles
group by pub_id
having count(pub_id)>5)


select * from titles
where price in(select min(price) from titles
                       where price in(select top 2 price from titles
                       order by price desc))

=========================Delete duplicate records in the table

select distinct * into #t1
from emp1
delete from emp1

insert into emp1

select * from #t1
=====================