sqlplus s22670236/s22670236@orcl //初始语句
CREATE TABLE S(SNO CHAR(8) PRIMARY KEY,SNAME VARCHAR(8) UNIQUE,SSEX CHAR(2),SBIRTHDATE DATE,SMAJOR VARCHAR(40)); //创建表
insert into S values('22670236','zero','女','20040328','软件工程');
INSERT INTO S SELECT * FROM System.S WHERE SMAJOR LIKE '%科学%' AND TO_CHAR(SBIRTHDATE,'YYYY') <> 2000; //从主表中选择符合条件的数据插入到表S中
UPDATE S SET SMAJOR='人工智能' WHERE SSEX='男'; //将性别为男的所属专业更新为人工智能
DELETE FROM S WHERE SNO='20180030'; //删除此学号的人
CREATE VIEW S_SEX(SSEX,CONUT,MAX,MIN)
AS
SELECT SSEX, COUNT(SNO),MAX(SBIRTHDATE),MIN(SBIRTHDATE)
FROM S
GROUP BY SSEX;
删除:DROP VIEW S_SEX;
查询:
SELECT* FROM S_SEX;