Question

/* I am executing following SQL statement for the below code but I am getting error. Pls, upload screenshot after running it.

SQL Statement:

SELECT OfferNo, CourseNo FROM Offering WHERE OfferTerm = 'Sum' AND OfferYear = 2012 AND FacSSN IS NULL; */

CREATE TABLE STUDENT(

StdSSN INT NOT NULL PRIMARY KEY,

StdFName VARCHAR2(50),

StdLName VARCHAR2(50),

StdCity VARCHAR2(50),

StdState VARCHAR2(2),

StdZip VARCHAR2(10),

StdMajor VARCHAR2(15),

StdYear VARCHAR2(20)

);

CREATE TABLE FACULTY(

FacSSN INTEGER NOT NULL PRIMARY KEY,

FacFName VARCHAR(50),

FacLName VARCHAR(50),

FacCity VARCHAR(30),

FacState CHAR(2),

FacZip VARCHAR(10),

FacStartDate DATE,

FacDept VARCHAR(10),

FacRank VARCHAR(10),

FacSalary INTEGER,

FacSupervisor INTEGER

);

CREATE TABLE OFFERING(

OfferNo INT NOT NULL PRIMARY KEY,

CourseNo VARCHAR2(10),

OfferTerm VARCHAR2(5),

OfferYear INTEGER,

OfferLocation VARCHAR2(7),

OfferTime VARCHAR2(10),

OfferDays VARCHAR(10),

FacSSN INT

);

CREATE TABLE COURSE(

CourseNo VARCHAR2(10) NOT NULL PRIMARY KEY,

CourseDescription VARCHAR2(50),

CourseCredit INTEGER

);

CREATE TABLE ENROLLMENT(

OfferNo INTEGER,

StdSSN INTEGER,

Grade REAL

);

INSERT INTO STUDENT VALUES(123456791, 'Hanes', 'Savon','OHIO', 'OK', '68123-1113', 'IT', 'Freshman');

SELECT * FROM STUDENT;

SELECT * FROM FACULTY;

SELECT * FROM OFFERING;

SELECT * FROM COURSE;

SELECT * FROM ENROLLEMENT;

ALTER TABLE OFFERING ADD FOREIGN KEY(FacSSN) REFERENCES FACULTY(FacSSN);

ALTER TABLE OFFERING ADD FOREIGN KEY(CourseNo) REFERENCES COURSE(CourseNo);

ALTER TABLE ENROLLMENT ADD FOREIGN KEY(OfferNo) REFERENCES OFFERING(OfferNo) ON DELETE CASCADE;

ALTER TABLE ENROLLMENT ADD FOREIGN KEY(StdSSN) REFERENCES STUDENT(StdSSN);

ALTER TABLE FACULTY ADD FOREIGN KEY(FacSupervisor) REFERENCES FACULTY(FacSSN);

Answer 1

SELECT OfferNo, CourseNo FROM Offering WHERE OfferTerm = 'Sum' AND OfferYear = 2012 AND FacSSN IS NULL;

/* You can not use 'FacSSN IS NULL' because in 'FACULTY' table you have set NOT NULL constraint to 'FacSSN' column and your 'OFFERING(FacSSN)' is linked to 'FACULTY(FacSSN)'. */