slqserver笔记3

1.group by语句

select COUNT(*),publish from book group by publish

select COUNT(book_id),reader_ID from Borrow  group by reader_ID

select publish from book group by publish having AVG(price)>30

2.嵌套子查询

select * from Reader where birthdate = (select birthdate from Reader where name='王旭')

SELECT book_ID FROM Borrow WHERE reader_ID IN (
        SELECT reader_ID FROM Reader WHERE   
        name='王旭')

select name from book where book_id in (select book_id from Borrow where reader_ID =(select reader_ID from Reader where name = '王旭'))

select * from book where price >some(select price from book)--价钱大于某一个肯定不是最低的

第二种方法

select * from book where price >(select MIN(price) from book )

SELECT * FROM Book WHERE NOT( price>SOME(SELECT price FROM Book))

SELECT * FROM Book WHERE price >=all(select price from book)

第二种方法

select * from book where price >=(select max(price) from book )