Dbmsday4.txt

SET AUTOCOMMIT ON;

SET PAGESIZE 200;
CREATE TABLE SAILOR(SID VARCHAR2(4),SNAME VARCHAR2(15),MNAME VARCHAR2(15),SURNAME VARCHAR2(15),RATING NUMBER(2),AGE NUMBER(3,1));

//to drop any constraint write -> alter table table_name drop constraint constraint_name;

ALTER TABLE SAILOR ADD CONSTRAINT PK_SID PRIMARY KEY(SID);

ALTER TABLE SAILOR ADD CONSTRAINT START_S CHECK(SID LIKE 's%');

ALTER TABLE SAILOR ADD CONSTRAINT SNAME_INITCAP CHECK(SNAME=INITCAP(SNAME));

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE TABLE_NAME='SAILOR';
-------------------------------------------------------------------------------------------------------------------------
CREATE  TABLE BOAT(BID NUMBER(3),BNAME VARCHAR2(10),COLOR VARCHAR2(6));

ALTER TABLE BOAT ADD CONSTRAINT PK_BID PRIMARY KEY(BID);

ALTER TABLE BOAT ADD CONSTRAINT BNAME_LOWER CHECK(BNAME=LOWER(BNAME));

ALTER TABLE BOAT ADD CONSTRAINT COLOR_SET CHECK(COLOR IN ('blue','red','green'));

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE TABLE_NAME='BOAT';

CREATE TABLE RESERVE(SID VARCHAR2(4),BID NUMBER(3),DAY DATE);

ALTER TABLE RESERVE ADD CONSTRAINT SID_REF_SAILOR FOREIGN KEY(SID) REFERENCES SAILOR(SID);

ALTER TABLE RESERVE ADD CONSTRAINT BID_REF_BOAT FOREIGN KEY(BID) REFERENCES BOAT(BID);

ALTER TABLE RESERVE ADD CONSTRAINT DAYIN_20TH_CENTURY CHECK(DAY<'01-JAN-2000');

ALTER TABLE RESERVE ADD CONSTRAINT COMPOSITE_SID_BID PRIMARY KEY(SID,BID);

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE TABLE_NAME='RESERVE';

INSERT INTO SAILOR VALUES('&SID','&SNAME','&MNAME','&SURNAME','&RATING','&AGE');

INSERT INTO BOAT VALUES('&BID','&BNAME','&COLOR');

INSERT INTO RESERVE VALUES('&SID','&BID','&DAY');

//cartesian product
SELECT * FROM DEPT,CUST_100;

//inner join
SELECT * FROM DEPT,CUST_100 WHERE DEPT.DEPT_ID=CUST_100.DEPT_ID;

SELECT * FROM DEPT INNER JOIN CUST_100 ON DEPT.DEPT_ID=CUST_100.DEPT_ID;

//Natural join does common attribute join naturally once
SELECT * FROM DEPT NATURAL JOIN CUST_100;