本实验基于StoreSales数据库中的各个表,用T-SQL命令按要求完成下列查询,并给出正确执行后的结果截图。
1、查询Employee中所有员工的员工号、姓名、地址和部门号。
SELECT EmplID,EmplName,Address, DeptID
FROM Employee
2、查询SalesOrder中所有订单的信息。
SELECT *
FROM SalesOrder
3、查询Employee中包含的部门号(结果去掉重复值)。
SELECT DISTINCT DeptID
FROM Employee
4、查询SalesOrder中的订单号和员工号,将结果中各列的标题分别修改"订单号:和员工号:"。
SELECT OrderID AS '订单号',EmplID AS '员工号'
FROM SalesOrder
5、查询Employee中月工资在3000元至4000元之间的员工的员工号、姓名和工资。(两种方法实现)
SELECT EmplID,EmplName,Wages
FROM Employee
WHERE Wages>=3000 AND Wages<=4000
//另一种方法
SELECT EmplID,EmplName,Wages
FROM Employee
WHERE Wages between 3000 AND 4000
6、查询Employee中月工资大于5000元或性别为男的员工信息。
SELECT *
FROM Employee
WHERE Wages<'5000' OR Sex='男'
7、查询OrderDetail中总价不在10000和20000之间的订单号、商品号和总价。(两种方法实现)
SELECT OrderID,GoodsID,Total
FROM OrdertDetail
WHERE Total NOT BETWEEN 10000 AND 20000
//另一种方法
SELECT OrderID,GoodsID,Total
FROM OrdertDetail
WHERE Total<10000 or Total>20000
8、查询SalesOrder中订单号为S00001和S00002的订单信息。(两种方法实现)
SELECT *
FROM SalesOrder
WHERE OrderID IN ('s00001','s000002')
//另一种方法
SELECT *
FROM SalesOrder
WHERE OrderID='s00001' or OrderID='s00002'
9、查询Employee中姓名以李开头且为2个字符的员工的员工号和姓名。
SELECT EmplID,EmplName
FROM Employee
WHERE EmplNAme LIKE '李_'
10、查询Employee中地址不为空的员工信息。
SELECT *
FROM Employee
WHERE Address IS NOT NULL
11、查询OrderDetail中所有订单信息,并按销售单价降序排列,总价升序排列。
//按销售单价降序排列
SELECT *
FROM OrderDetall
ORDER BY SaleUnitPrice DESC
//按总价升序排列
SELECT *
FROM OrderDetall
ORDER BY Total
12、查询男员工的最高工资、最低工资。
SELECT max(Wages)as'最高工资',min(Wages)as'最低工资'
FROM Employee
WHERE Sex='男'
13、查询所有员工的平均工资。
SELECT avg(Wages)as'平均工资'
FROM Employee
14、查询部门号为D001的员工总人数。
SELECT count(*)as'人数'
FROM Employee
WHERE DeptID='D001'
15、查询所有女员工工资总额。
SELECT sum(Wages)as'工资总额'
FROM Employee
WHERE Sex='男'
16、查询Employee中每个部门的人数。
SELECT DeptID as'部门号',count(*)as'人数'
FROM Employee
GROUP BY DeptID
17、查询查询男女员工的平均工资,并输出平均工资大于400的信息。
SELECT Sex,AVG(Wages)as'平均工资'
FROM Employee
GROUP BY Sex
HAVING AVG(Wages)>4000