Given the following tables: • students(sid,name,age,gpa) • courses(cid,deptid, d

Question

Given the following tables:

• students(sid,name,age,gpa)

• courses(cid,deptid, description)

• professors(ssn,name,address,phone,deptid)

• enrollment(sid,cid,section,grade). cid makes reference to the courses table.

• teaches(cid,section,ssn). cid, section makes reference to the enrollment table

Provide SQL instructions for each of the following questions

1. Create the database schema.

2. Find the name of professors that work for the cs department.

3. Find those students (sid) enrolled in courses in the cs department.

Explanation / Answer

/* To create Database */

CREATE DATABASE College;

/* To create Students Table */

CREATE TABLE Students (

sid int NOT NULL,

name varchar(255) NOT NULL,

age int,

gpa varchar(100),

PRIMARY KEY (sid)

);

/* To create Courses Table */

CREATE TABLE Courses (

cid int NOT NULL,

deptid varchar(15) NOT NULL,

description varchar(255),

PRIMARY KEY (cid)

);

/* To create ProfessorsTable */

CREATE TABLE Professors(

ssn int NOT NULL,

name varchar(255) NOT NULL,

address varchar(255) NOT NULL,

phone varchar(15),

deptid varchar(15) NOT NULL,

PRIMARY KEY (ssn)

);

/* To create enrollment Table */

CREATE TABLE enrollment(

sid int NOT NULL,

cid int NOT NULL,

section varchar(15) NOT NULL,

grade varchar(15)

);

/* To create teaches Table */

CREATE TABLE teaches(

cid int NOT NULL,

section varchar(15) NOT NULL,

ssn int NOT NULL,

);

/* Instruction/Query to retrieve professor names under cs */

Select name from Professors where deptid='cs'

/* Instruction/Query to retrieve student id under cs */

Select e.sid from enrollment e,Courses c where e.cid=c.cid and c.deptid='cs'

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.