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實踐性練習的相關文章：
【編輯推薦】
