SQL Server实践性练习之创建库表及条件查询

坦白说，SQL Server数据库的学习过程是比较枯燥的，因为我们不但要熟练掌握企业管理器的操作，还要熟练掌握SQL语句的查询。其实这一过程是通过做大量的实践练习来熟练掌握的。本文开始我们就介绍几篇关于SQL Server数据库实践性练习的文章，希望能够对您有所帮助。本篇我们先介绍一些SQL Server数据库创建数据库和创建表以及条件查询方面的操作实例，接下来就让我们一起来了解一下这部分内容吧。

1、创建数据库以及创建表

create database CAP  
 
create table CUSTOMERS  
(  
cid varchar(10) primary key,  
canme varchar(10),  
city varchar(20),  
discnt money  
)  
 
insert into CUSTOMERS values ('c001','TipTop','Duluth',10.00)  
insert into CUSTOMERS values ('c002','Basics','Dallas',12.00)  
insert into CUSTOMERS values ('c003','Allied','Dallas',8.00)  
insert into CUSTOMERS values ('c004','ACME','Duluth',8.00)  
insert into CUSTOMERS values ('c006','ACME','Kyoto',0.00) 

create table PRODUCTS  
(  
pid varchar(10)primary key,  
pname varchar(10),  
city varchar(20),  
quantity varchar(10),  
price money  
)  
insert into PRODUCTS values ('p01','comb','Dallas','111400',0.50)  
insert into PRODUCTS values ('p02','brush','Newark','203000',0.50)  
insert into PRODUCTS values ('p03','razor','Duluth','150600',1.00)  
insert into PRODUCTS values ('p04','pen','Duluth','125300',1.00)  
insert into PRODUCTS values ('p05','pencil','Dallas','221400',1.00)  
insert into PRODUCTS values ('p06','folder','Dallas','123100',2.00)  
insert into PRODUCTS values ('p07','case','Newark','100500',1.00) 

create table AGENTS  
(  
aid varchar(4) primary key,  
aname varchar(10),  
city varchar(10),  
[percent] int   
)  
insert into AGENTS values('a01','Smith','New York',6)  
insert into AGENTS values('a02','Jones','Newark',6)  
insert into AGENTS values('a03','Brown','Tokyo',7)  
insert into AGENTS values('a04','Gray','New York',6)  
insert into AGENTS values('a05','Otasi','Duluth',5)  
insert into AGENTS values('a06','Smith','Dallas',5) 

create table ORDERS  
(  
ordno varchar(4),  
[month] varchar(3),  
cid varchar(10),  
aid varchar(4),  
pid varchar(10),  
qty int,  
dollars money  
)  
 
insert into ORDERS values('1011','jan','c001','a01','p01',1000,450.00)  
insert into ORDERS values('1012','jan','c001','a01','p01',1000,450.00)  
insert into ORDERS values('1019','feb','c001','a02','p02',400,180.00)  
insert into ORDERS values('1017','feb','c001','a06','p03',600,540.00)  
insert into ORDERS values('1018','feb','c001','a03','p04',600,540.00)  
insert into ORDERS values('1023','mar','c001','a04','p05',500,450.00)  
insert into ORDERS values('1022','mar','c001','a05','p06',400,720.00)  
insert into ORDERS values('1025','apr','c001','a05','p07',800,720.00)  
insert into ORDERS values('1013','jan','c002','a03','p03',1000,880.00)  
insert into ORDERS values('1026','may','c002','a05','p03',800,704.00)  
insert into ORDERS values('1015','jan','c003','a03','p05',1200,1104.00)  
insert into ORDERS values('1014','jan','c003','a03','p05',1200,1104.00)  
insert into ORDERS values('1021','feb','c004','a06','p01',1000,460.00)  
insert into ORDERS values('1016','jan','c006','a01','p01',1000,500.00)  
insert into ORDERS values('1020','feb','c006','a03','p07',600,600.00)  
insert into ORDERS values('1024','mar','c006','a06','p01',800,400.00) 

2、sql语句的编写

–题1：找出住在纽约的代理商的aid值和名字

select aid ,aname from AGENTS where city='New York' 

–题2：检索订货记录中所有零件的pid值

select distinct pid from ORDERS 

–题3:检索所有满足以下条件的顾客-代理商姓名对(cname,aname),其中的cname 通过aname订了货(曾经出过错)

create table b  
(cid varchar(10),  
aid varchar(4)  
)  
insert into b  
select cid,aid  
from ORDERS group by cid,aid 

select distinct aname,cname from AGENTS,CUSTOMERS,b where AGENTS.aid=b.aid and CUSTOMERS.cid=b.cid 

–上面是通过建临时表得出结果,如果直接写呢?(注意一定还要有distinct)

select distinct cname,aname from agents,customers,orders where agents.aid=orders.aid and customers.cid=orders.cid 

–答案：

select distinct CUSTOMERS.cname,AGENTS.aname from CUSTOMERS,AGENTS,ORDERS where CUSTOMERS.cid=ORDERS.cid and AGENTS.aid=ORDERS.aid 

–可以看出,答案非常简单

–题4:在orders表的基础上生成含有列ordno,cid,aid,pid和profit的”表”,其中的profit是由quantity和price计算所得,方法是全部销售收入减去60%的销售收入.顾客的折扣以及代理商的酬金百分率
–答案：此题告诉我们关键字加[]的重要性，不然一直出现“关键字 ‘percent’ 附近有语法错误。”

select ordno,x.cid,x.aid,x.pid,40*(x.qty*p.price)-01*(c.discnt+a.[percent])*(x.qty*p.price) as profit  
from orders as x,customers as c,agents as a,products as p   
where c.cid=x.cid and a.aid=x.aid and p.pid=x.pid; 

–题5：求出住在同一城市的顾客对。

select distinct c1.cname,c2.cname,c1.city from customers c1, customers c2 where c1.city=c2.city and c1.cname<c2.cname 
select c1.cid,c2.cid from customers c1,customers c2  
where c1.city=c2.city and c1.cid<c2.cid 

–此题的关键点即为可以给一个表取两个别名.如果题目要求在一个表中的同一列取出匹配项的话都可以用这种方法。

–题6：找出至少被两个顾客订购的产品的pid值
select pid,count(cid) as 订购产品的顾客数量 from orders group by pid having count(cid)>=2
–此时用此法更好一些
–答案：

select distinct x1.pid from orders x1, orders x2  
where x1.pid=x2.pid and x1.cid<x2.cid 

—总结：对一个表的操作大多可以通过去别名来完成，这里应该会有冗余

–题6:查询那些订购了某个被代理商a06订购过的产品的顾客的cid值

select distinct cid from orders where pid in (select pid from orders where aid='a06') 

–这时用in感觉浑然天成，因为这里要去的是“某一个”被代理商a06订购过的产品

–同理,对一个表里面的这种操作都可以通过取别名(且这种方式更简单)

select distinct y.cid from orders x,orders y  
where y.pid=x.pid and x.aid='a06' 

—总结:可以用一次查询的就用一次查询,通过表取别名。

关于SQL Server数据库实践性练习之创建数据库和创建表以及条件查询方面的实例就介绍到这里了，希望本次的介绍能够对您有所收获吧。

SQL Server实践性练习的相关文章：

SQL Server实践性练习之子查询实例

SQL Server实践性练习之高级SQL查询

【编辑推荐】

1. SQL Server 2008数据库学习笔记
2. SQL Server 2005数据库nolock使用详解
3. SQL Server如何启用Ad Hoc Distributed Queries？
4. SQL Server 2008用存储过程实现插入更新数据的实例
5. 含有GROUP BY子句的查询中如何显示COUNT()为0的结果