lab1_2.sql

来自「该代码是一个脚本」· SQL 代码 · 共 45 行

CREATE TABLE PB06210445.CARS
(LICENSE# VARCHAR2(20) NOT NULL,
OWNER VARCHAR2(20) NOT NULL,
MODEL VARCHAR2(20) NOT NULL,
CONSTRAINT PK_CARS PRIMARY KEY(LICENSE#));
CREATE TABLE PB06210445.MOTORCYCLE
(LICENSE# VARCHAR2(20) NOT NULL,
OWNER VARCHAR2(20) NOT NULL,
MODEL VARCHAR2(20) NOT NULL,
CONSTRAINT PK_MOTORCYCLE PRIMARY KEY(LICENSE#));
INSERT INTO PB06210445.CARS VALUES('A0001','JACKLIN','SUV');
INSERT INTO PB06210445.CARS VALUES('A0002','LINSHENG','BMW');
INSERT INTO PB06210445.CARS VALUES('A0003','ZHANG','BMW');
INSERT INTO PB06210445.CARS VALUES('A0004','JACKLIN','BMW');
INSERT INTO PB06210445.MOTORCYCLE VALUES('B0001','JACKLIN','HAOJUE');
INSERT INTO PB06210445.MOTORCYCLE VALUES('B0002','LINSHENG','HAOJUE');
INSERT INTO PB06210445.MOTORCYCLE VALUES('B0003','LIU','LINGMU');
INSERT INTO PB06210445.MOTORCYCLE VALUES('B0004','JACKLIN','LINGMU');

SELECT * FROM CARS;
SELECT * FROM MOTORCYCLE;

SELECT DISTINCT OWNER
FROM CARS
WHERE OWNER NOT IN(SELECT OWNER
                   FROM MOTORCYCLE);

SELECT DISTINCT OWNER
FROM CARS
WHERE OWNER IN(SELECT OWNER
               FROM MOTORCYCLE);

SELECT DISTINCT OWNER
FROM MOTORCYCLE
WHERE OWNER NOT IN(SELECT OWNER
                   FROM CARS);

SELECT OWNER,COUNT(LICENSE#) AS CAR_NUM
FROM CARS
GROUP BY OWNER;

DROP TABLE CARS;
DROP TABLE MOTORCYCLE;

COMMIT;