Orcale机试题


/*编写SQL语句，根据范例，建立以上表。要求要满足基本的实体完整性。*/
CREATE TABLE Customer(
CusNo VARCHAR(20) PRIMARY KEY,
Cusname VARCHAR(20) NOT NULL,
Address VARCHAR(20),
Tel VARCHAR(20) UNIQUE,
Sex CHAR(2) DEFAULT'男',
Mon INT
)
SELECT *FROM Customer
/*编写SQL语句将示范数据插入到数据库中*/
INSERT INTO Customer VALUES('C001','杨婷','北京','010-5328953','女',180000)
INSERT INTO Customer VALUES('C002','李和平','上海','021-6235965','男',230000)
INSERT INTO Customer VALUES('C003','叶新','成都','024-3222781','男',550000)
INSERT INTO Customer VALUES('C004','冯辰诚','上海','021-8723596','男',700000)
INSERT INTO Customer VALUES('C005','张展','郑州','0371-8907654','男',105000)
INSERT INTO Customer VALUES('C006','王晓丽','苏州','022-8838823','女',89000)
/*将杨婷拥有资产改为188888*/
UPDATE Customer SET Mon=188888 WHERE Cusname='杨婷';
/*客户编号“C007”的姓名“王晨”，地址‘杭州’，电话‘031-8909932’，性别‘男’，拥有资产280000录入时遗漏，请编写SQL语句插入该记录。*/
INSERT INTO Customer VALUES('C007','王晨','杭州','031-8909932','男',280000)
/*查询所有客户的姓名、性别、和地址*/
SELECT Cusname,Sex,Address FROM Customer
/*查询所有客户的姓名、地址和拥有资产，要求安装资产降序排序*/
SELECT Cusname,Sex,Address FROM Customer ORDER BY Mon DESC
/*查询所有男性客户的客户编号、姓名、地址*/
SELECT CusNo,Cusname,Address FROM Customer WHERE Sex='男'
/*28.查询来至上海的客户的姓名、性别、拥有资产*/
SELECT Cusname,Sex,Mon FROM Customer WHERE Address='上海'
/*29.查询资产超过200000的女性的姓名、地址、拥有资产*/
SELECT Cusname,Address,Mon FROM Customer WHERE Mon >=200000 AND Sex='女'
/*30.查询姓李的客户的姓名、地址、性别*/
SELECT Cusname,Address,Sex FROM Customer WHERE Cusname LIKE'李%'
/*31.查询客户表中客户拥有资产的平均值*/
SELECT AVG(Mon) FROM Customer
/*32.查询客户表中客户一共拥有多少资产*/
SELECT SUM(Mon)FROM Customer
/*33.查询客户表中客户资产最少的客户姓名、性别、地址、拥有资产*/
SELECT Cusname,Sex,Address,Mon FROM Customer WHERE Mon=(SELECT MIN(Mon) FROM Customer)
/*34.查询男性和女性分别拥有资产的总数和性别*/
SELECT Sex,SUM(Mon) FROM Customer WHERE Sex='男' UNION SELECT Sex,SUM(Mon) FROM Customer WHERE Sex='女'
/*35.查询姓名为2个字的用户的用户编号、姓名、地址*/
SELECT CusNo,Cusname,Address FROM Customer WHERE Cusname LIKE'__'
/*36.查询电话号码最后一位不是3的客户的姓名、地址、电话号码*/
SELECT Cusname,Address,Tel FROM Customer WHERE Tel NOT LIKE'%3'
/*37.查询客户表中男性客户比女性客户多几个*/
SELECT (SELECT COUNT(Sex)FROM Customer WHERE Sex='男')-(SELECT COUNT(Sex)FROM Customer WHERE Sex='女')
/*38.查询资产超过所有客户平均资产的客户的姓名、性别、地址、拥有资产*/
SELECT Cusname,Sex,Address,Mon FROM Customer WHERE Mon>=(SELECT AVG(Mon) FROM Customer)
/*39.删除上海男性客户的基本信息*/
DELETE FROM Customer WHERE Address='上海'AND Sex='男'
/*40.删除客户表中所有数据*/
DELETE FROM Customer