18、单表数据查询
update products <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

set unitprice=unitprice*0.9

where unitprice>30
 19、设计表结构
字段名
中文意思
字段类型
是否为空
备注
id
记录编号
int
not null
主键
entrytime
通过时间
datetime
not null
 

cardid
通过卡号
char(8)
not null
假设卡号长度为8
 20、考查order by 和group by 以及having子句
select city,count(city) as 客户数

from customers

group by city

having count(city)<3

order by 客户数

 21、考查order by 和group by子句
select  city,count(city) as 客户数

from customers

group by city

order by 客户数 desc

 22、考查distinct 和group by子句
select title as 职位

from emloyees

group by title

或

select distinct title

form employees

 23、考查count函数

select count(*) as 数量

from employees a,employees b

where a.reportsto=b.employees and b.lastname=’fuller’

或

select a.lastname

from employees as a,employees as b

where a.reportsto=b.employeeid and b.lastname=’fuller’

24、考查update子句

update a

set reportsto = b.employeeid

from employees a, employees b

where a.lastname = ‘callahan’ and  b.lastname = ‘buchanan’

 25、考查数据库自联接
select a.lastname as 员工, b.lastname as 下属

from employees as a inner join employees as b

on b.reportsto=a.employeeid

或

select a.lastname as 员工, b.lastname as 下属

from employees as a, employees as b

where b.reportsto=a.employeeid