数据库点滴：Some useful ideas of database

1 The members having the same favourate category

 

Query the members having the same favourate category:

select category.category,md1.fistname,md1.lastname,md2.firstname,md2.lastname

from favcategory fc1 inner join favcategory fc2 on fc1.categoryid=fc2.categoryid

inner join category on category.categoryid=fc1.categoryid

inner join memberdetails md1 on md1.memberid=fc1.memberid

inner join memberdetails md2 on md2.memberid=fc2.menberid

where fc1.memberid<fc2.memberid

 

2 After Granting

 

revoke update on tblpersonnel from usrJim,usrTom

if another user and you  grant some privileges to usr1,

even if you revoke the privileges you grant,other privileges can work.

 

the grant list:

if a grant b with grant option,and b grant c with grant option,

then revoke from b,c will revoke.

cascade and restrict:

revoke select on tblpersonnel from b restrict,

it will warn you that you are likely to revoke a privilege you are not supposed to revoke.

then you can do as follows:

revoke select on tblpersonnel from b cascade

 

revoke grant option:

revoke grant option on select,delete on tblpersonnel from b cascade

 

3 Grant Privilege of A Table To An User

 

All Privileges:

grant all privileges on tblpersonnel to usepersonnel

 

Public:

grant select on tblholidays to public

 

Some Privileges:

grant select,insert,update,on attendance to dataentry

go

grant select on attendence to management

go

 

SQL2 has extened SQ1:

insert and references besides update

some columns:

grant update (shipdate,quantity) on tblorderitem to usrshipping

possible extensions:

grant select (name,ssn,salary) on tblpersonnel to usrpayroll

 

With Grant Option:

create view vproductioneastcoast as select * from tblproduction

where region='east'

 

greant select on vproductioneastcoast to usrBob,usrSharon

with grant option

 

4 Delete Query Having Subquery

 

delete from location

where (

select count(*) from memberdetails

where location.city=memberdetails.city

and location.state=memberdetails.state

group by city,state)

<=1

and locationid  not in

(select locationid from attendance)

 

the result is the location where one or less person live and no meeting is held

 

5 Insert Has Subquery

 

The persons like thriller may like noir.

Noir's categoryid is 9 and thriller's categoryid is 1.

Insert the new category noir into memberdetail.

 

insert  into favcategory(categoryid,memberid)

select 9,memberid from memberdetail as md1

where exists

(select *from favcategory as f1

where f1.categoryid=1 and f1.memberid=md1.memberid

and not exists

(select * from favcategory as f2

where f2.categoryid=9 and f2.memberid=f1.memberid)

);

 

6 Correlated Subquery

 

Film table has Filmname,Rating,DVDPrice,CategoryId while Category table has Category and CategoryId.

 

Now these sentence can get the category of the films whose Rating are highest

and DVDPrice  are cheapest.

 

select filmname,rating,dvdprice,category from

film as fm1 inner join category as c on c.categoryid=fm1.categoryid

where fm1.dvdprice=

(select min(dvdprice) from film as fm2

where fm.dvdprice is not null

and fm1.categoryid=fm2.categoryid

and fm2.rating=

(select max(rating) from film as fm3

where fm3.dvdprice is not null

and fm2.cetegoryid=fm3.categoryid

group by fm3.categoryid)

group by fm2.categoryid)

order by fm1.categoryid;