SQL语句复习

通配符

1.查询姓名以A开头的顾客

SELECT  CID,CNAME
FROM Customers
WHERE CNAME LIKE 'A%';

排序检索数据

1.查询地址为空的顾客信息，按姓名降序排列

SELECT CID,CNAME
FROM Customers
WHERE ADDRESS IS　NULL
ORDER BY CNAME DESC

过滤数据

1.查询薪水大于3000的销售员信息，薪水为空按3000计算（薪水为空怎么处理）

SELECT SID,SNAME
FROM Sales
WHERE SALARY > 3000 AND SALARY IS NOT NULL;

高级链结

1.找出比Smith工资高的销售员（至少有两个Smith，工资不一样）（两个以上不会实现）

SELECT SID,SNAME
FROM Customers
WHERE SALARY>ALL(SELECT SALARY
		FROM Customers
		WHERE CNAME='Smith');

使用存储过程

1.编写一个存储过程，计算顾客在给定的时间前一年对某种商品的总购买量

CREATE PROCEDURE COMPUTE(
			v_cid IN CHAR(4) ,
			v_sum OUT NUMBER(6,2),
			v_date IN DATE DEFAULT SYSDATE)
			AS
			BEGIN
			SELECT SUM(QTY) INTO v_sum
			FROM orders
			WHERE cid=v_cid AND
			DATE_BUY BETWEEN v_date-365 AND v_data;
			END;

2.编写一个存储过程，计算用户在给定时间前一年对某首歌曲的收听次数

CREATE PROCEDURE NUM(
			v_uid IN NUMBER(10),
			v_sid IN NUMBER(10),
			v_sum OUT NUMBER,
			v_date IN DATE DEFAULT SYSDATE
			)
			AS
			BEGIN
			SELECT COUNT(*) INTO v_sum
			FROM listen_history
			WHERE uid=v_uid AND
			sid=v_sid AND
			date_listen BETWEEN v_date-365 AND v_date;
			END;

3.设计一个存储过程，计算某学生在给定时间前一年的总借书数量。

CREATE PROCEDURE NUM(
	V_SNO IN NUMBER(10),
	V_DATE IN DATE DEFAULT SYSDATE,
	V_SUM OUT NUMBER)
	AS 
	BEGIN 
	SELECT SUM(LQTY) INTO V_sum
	FROM lend_return 
	WHERE V_SNO=SNO AND
	DATE BETWEEN V_DATE-365 AND V_DATE;
	END;

函数（使用到视图）

1.编写一个函数，以歌曲名称和听歌日期为参数，计算出用户对某一首歌曲在一段时间内的受欢迎程度，例如可按周或月计算，构建一个视图，该视图能反映用户最近一次听过的打分数最高的歌曲，查询出这歌曲在一年内的受欢迎程度

创建表，数据类型和约束条件

1.写出创建user表的ＤＤＬ语句，包括准确的数据类型和所有约束条件。

游标

1.设计一个存储过程，输入学生姓名，计算返回其至今的平均借书时间，要求使用游标（按平均每本书计算？）

CREATE PROCEDURE NUMT(
 V_SNAME IN NUMBER(10),
 V_DATE IN DATE DEFAULT SYSDATE,
 V_AVTIME OUT NUMBER(6.2) )
 AS 
 BEGIN 
 DECLARE CURSOR c1 IS
 SELECT SNAME,SUM(RDATE-LDATE) AS SUMTIME,SUM(LQTY) AS SUMBOOK
 FROM lend_return ,students
 WHERE students.SNAME=V_SNAME AND
 students.SNO=lend_return.SNO AND
 LDATE <= V_DATE
 GROUP BY lend_return.SNO;
 V_SUMBOOK NUMBER; 
 V_SUMSUMBOOK NUMBER;
 V_SUMTIME DATE;
 V_SUMSUMTIME DATE;
 BEGIN
 OPEN c1;
 LOOP 
 FETCH c1 INTO V_SNAME,V_SUMTIME,V_SUMBOOK;
 V_SUMSUMBOOK:=V_SUMSUMBOOK+V_SUMBOOK;
 V_SUMSUMTIME:=V_SUMSUMTIME+V_SUMTIME;
 EXIT WHEN c1%NOTFOUND;
 V_AVTIME=V_SUMSUMBOOK/V_SUMSUMTIME;
 END LOOP;
 CLOSE c1;
 END;

2.设计一个游标，为年平均借书数量高于100本的学生的可借本数增加10本。

	DECLARE
	V_SNO NUMBER(10);
	V_canlended NUMBER(2);
	V_sum NUMBER;
	V_date DATE DEFAULT SYSDATE;
	CURSOR plus IS
	SELECT SNO,sum(LQTY) AS sumbook 
	FROM lend_return
	WHERE LDATE BETWEEN V_date-365 AND V_date
	GROUP BY SNO;
	BEGIN
	OPEN plus;
	LOOP
	FETCH plus INTO V_SNO,V_sum,V_canlended;
	EXIT WHEN c1%NOTFOUND;
	IF V_sum>100 
	THEN V_canlended=V_canlended+10;
	UPDATE students SET canlended =V_canlended WHERE CURRENT OF PLUS;
	END IF;
	END LOOP;
	CLOSE plus;
	END；

3.建立一个函数，以购买时间和购买数量为参数，计算出顾客对某种商品在单位时间内的购买数量，可按月计算，并使用游标，查询出商品一年内的销售旺季

在这里插入代码片

列出一列数据中第n个，前n个

列出第二高的单笔销售额

SELECT  QTY*DOLLARS AS SELL 
FROM orders 
ORDER BY SELL DESC LIMIT 1,2;

授权

授权用户Tom具有插入和删除user_links表的权限

GRANT INSERT,DELETE ON user_links TO Tom;

组合查询

列出用户名为Smith，最近一年内听过的所有歌曲名和打分。

SELECT sid,score
FROM listen_history,users
WHERE listen_history.uid=users.uid AND
users.uname='Smith' AND
date BETWEEN SYSDATE-365 AND SYSDATE;

统计听过歌曲‘xxx‘的用户中居住在‘yyy’省的人数。

SELECT COUNT(*)
FROM users,listen_history
WHERE users.uname='XXX' AND
users.uid=listen_histery.uid AND
users.province='YYY';