ZuoTi.Pro
Question

Database concepts Find the names and ids of the students who have taken at most one...

Database concepts

Find the names and ids of the students who have taken at most one course in the Spring 2010 semester. Notice, at most one means one or zero. So, the answer should include students who did not take any course during that semester

The data:

create table classroom

    (building       varchar(15),

     room_number        varchar(7),

     capacity       numeric(4,0),

     primary key (building, room_number)

    );

create table department

    (dept_name      varchar(20),

     building       varchar(15),

     budget      numeric(12,2) check (budget > 0),

     primary key (dept_name)

    );

create table course

    (course_id      varchar(8),

     title          varchar(50),

     dept_name      varchar(20),

     credits        numeric(2,0) check (credits > 0),

     primary key (course_id),

     foreign key (dept_name) references department

        on delete set null

    );

create table instructor

    (ID         varchar(5),

     name           varchar(20) not null,

     dept_name      varchar(20),

     salary         numeric(8,2) check (salary > 29000),

     primary key (ID),

     foreign key (dept_name) references department

        on delete set null

    );

create table section

    (course_id      varchar(8),

sec_id         varchar(8),

     semester       varchar(6)

        check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),

     year           numeric(4,0) check (year > 1701 and year < 2100),

     building       varchar(15),

     room_number        varchar(7),

     time_slot_id       varchar(4),

     primary key (course_id, sec_id, semester, year),

     foreign key (course_id) references course

        on delete cascade,

     foreign key (building, room_number) references classroom

        on delete set null

    );

create table teaches

    (ID         varchar(5),

     course_id      varchar(8),

     sec_id         varchar(8),

     semester       varchar(6),

     year           numeric(4,0),

     primary key (ID, course_id, sec_id, semester, year),

     foreign key (course_id,sec_id, semester, year) references section

        on delete cascade,

     foreign key (ID) references instructor

        on delete cascade

    );

create table student

    (ID         varchar(5),

     name           varchar(20) not null,

     dept_name      varchar(20),

     tot_cred       numeric(3,0) check (tot_cred >= 0),

     primary key (ID),

     foreign key (dept_name) references department

        on delete set null

    );

create table takes

    (ID         varchar(5),

     course_id      varchar(8),

     sec_id         varchar(8),

     semester       varchar(6),

     year           numeric(4,0),

     grade       varchar(2),

     primary key (ID, course_id, sec_id, semester, year),

     foreign key (course_id,sec_id, semester, year) references section

        on delete cascade,

     foreign key (ID) references student

        on delete cascade

    );

create table advisor

    (s_ID           varchar(5),

     i_ID           varchar(5),

     primary key (s_ID),

     foreign key (i_ID) references instructor (ID)

        on delete set null,

     foreign key (s_ID) references student (ID)

        on delete cascade

    );

create table time_slot

    (time_slot_id       varchar(4),

     day            varchar(1),

     start_hr       numeric(2) check (start_hr >= 0 and start_hr < 24),

     start_min      numeric(2) check (start_min >= 0 and start_min < 60),

     end_hr         numeric(2) check (end_hr >= 0 and end_hr < 24),

     end_min        numeric(2) check (end_min >= 0 and end_min < 60),

     primary key (time_slot_id, day, start_hr, start_min)

    );

create table prereq

    (course_id      varchar(8),

     prereq_id      varchar(8),

     primary key (course_id, prereq_id),

     foreign key (course_id) references course

        on delete cascade,

     foreign key (prereq_id) references course

    );

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1 
insert into classroom values ('Packard', '101', '500');
insert into classroom values ('Painter', '514', '10');
insert into classroom values ('Taylor', '3128', '70');
insert into classroom values ('Watson', '100', '30');
insert into classroom values ('Watson', '120', '50');
insert into department values ('Biology', 'Watson', '90000');
insert into department values ('Comp. Sci.', 'Taylor', '100000');
insert into department values ('Elec. Eng.', 'Taylor', '85000');
insert into department values ('Finance', 'Painter', '120000');
insert into department values ('History', 'Painter', '50000');
insert into department values ('Music', 'Packard', '80000');
insert into department values ('Physics', 'Watson', '70000');
insert into course values ('BIO-101', 'Intro. to Biology', 'Biology', '4');
insert into course values ('BIO-301', 'Genetics', 'Biology', '4');
insert into course values ('BIO-399', 'Computational Biology', 'Biology', '3');
insert into course values ('CS-101', 'Intro. to Computer Science', 'Comp. Sci.', '4');
insert into course values ('CS-190', 'Game Design', 'Comp. Sci.', '4');
insert into course values ('CS-315', 'Robotics', 'Comp. Sci.', '3');
insert into course values ('CS-319', 'Image Processing', 'Comp. Sci.', '3');
insert into course values ('CS-347', 'Database System Concepts', 'Comp. Sci.', '3');
insert into course values ('EE-181', 'Intro. to Digital Systems', 'Elec. Eng.', '3');
insert into course values ('FIN-201', 'Investment Banking', 'Finance', '3');
insert into course values ('HIS-351', 'World History', 'History', '3');
insert into course values ('MU-199', 'Music Video Production', 'Music', '3');
insert into course values ('PHY-101', 'Physical Principles', 'Physics', '4');
insert into instructor values ('10101', 'Srinivasan', 'Comp. Sci.', '65000');
insert into instructor values ('12121', 'Wu', 'Finance', '90000');
insert into instructor values ('15151', 'Mozart', 'Music', '40000');
insert into instructor values ('22222', 'Einstein', 'Physics', '95000');
insert into instructor values ('32343', 'El Said', 'History', '60000');
insert into instructor values ('33456', 'Gold', 'Physics', '87000');
insert into instructor values ('45565', 'Katz', 'Comp. Sci.', '75000');
insert into instructor values ('58583', 'Califieri', 'History', '62000');
insert into instructor values ('76543', 'Singh', 'Finance', '80000');
insert into instructor values ('76766', 'Crick', 'Biology', '72000');
insert into instructor values ('83821', 'Brandt', 'Comp. Sci.', '92000');
insert into instructor values ('98345', 'Kim', 'Elec. Eng.', '80000');
insert into section values ('BIO-101', '1', 'Summer', '2009', 'Painter', '514', 'B');
insert into section values ('BIO-301', '1', 'Summer', '2010', 'Painter', '514', 'A');
insert into section values ('CS-101', '1', 'Fall', '2009', 'Packard', '101', 'H');
insert into section values ('CS-101', '1', 'Spring', '2010', 'Packard', '101', 'F');
insert into section values ('CS-190', '1', 'Spring', '2009', 'Taylor', '3128', 'E');
insert into section values ('CS-190', '2', 'Spring', '2009', 'Taylor', '3128', 'A');
insert into section values ('CS-315', '1', 'Spring', '2010', 'Watson', '120', 'D');
insert into section values ('CS-319', '1', 'Spring', '2010', 'Watson', '100', 'B');
insert into section values ('CS-319', '2', 'Spring', '2010', 'Taylor', '3128', 'C');
insert into section values ('CS-347', '1', 'Fall', '2009', 'Taylor', '3128', 'A');
insert into section values ('EE-181', '1', 'Spring', '2009', 'Taylor', '3128', 'C');
insert into section values ('FIN-201', '1', 'Spring', '2010', 'Packard', '101', 'B');
insert into section values ('HIS-351', '1', 'Spring', '2010', 'Painter', '514', 'C');
insert into section values ('MU-199', '1', 'Spring', '2010', 'Packard', '101', 'D');
insert into section values ('PHY-101', '1', 'Fall', '2009', 'Watson', '100', 'A');
insert into teaches values ('10101', 'CS-101', '1', 'Fall', '2009');
insert into teaches values ('10101', 'CS-315', '1', 'Spring', '2010');
insert into teaches values ('10101', 'CS-347', '1', 'Fall', '2009');
insert into teaches values ('12121', 'FIN-201', '1', 'Spring', '2010');
insert into teaches values ('15151', 'MU-199', '1', 'Spring', '2010');
insert into teaches values ('22222', 'PHY-101', '1', 'Fall', '2009');
insert into teaches values ('32343', 'HIS-351', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-101', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-319', '1', 'Spring', '2010');
insert into teaches values ('76766', 'BIO-101', '1', 'Summer', '2009');
insert into teaches values ('76766', 'BIO-301', '1', 'Summer', '2010');
insert into teaches values ('83821', 'CS-190', '1', 'Spring', '2009');
insert into teaches values ('83821', 'CS-190', '2', 'Spring', '2009');
insert into teaches values ('83821', 'CS-319', '2', 'Spring', '2010');
insert into teaches values ('98345', 'EE-181', '1', 'Spring', '2009');
insert into student values ('00128', 'Zhang', 'Comp. Sci.', '102');
insert into student values ('12345', 'Shankar', 'Comp. Sci.', '32');
insert into student values ('19991', 'Brandt', 'History', '80');
insert into student values ('23121', 'Chavez', 'Finance', '110');
insert into student values ('44553', 'Peltier', 'Physics', '56');
insert into student values ('45678', 'Levy', 'Physics', '46');
insert into student values ('54321', 'Williams', 'Comp. Sci.', '54');
insert into student values ('55739', 'Sanchez', 'Music', '38');
insert into student values ('70557', 'Snow', 'Physics', '0');
insert into student values ('76543', 'Brown', 'Comp. Sci.', '58');
insert into student values ('76653', 'Aoi', 'Elec. Eng.', '60');
insert into student values ('98765', 'Bourikas', 'Elec. Eng.', '98');
insert into student values ('98988', 'Tanaka', 'Biology', '120');
insert into takes values ('00128', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('00128', 'CS-347', '1', 'Fall', '2009', 'A-');
insert into takes values ('12345', 'CS-101', '1', 'Fall', '2009', 'C');
insert into takes values ('12345', 'CS-190', '2', 'Spring', '2009', 'A');
insert into takes values ('12345', 'CS-315', '1', 'Spring', '2010', 'A');
insert into takes values ('12345', 'CS-347', '1', 'Fall', '2009', 'A');
insert into takes values ('19991', 'HIS-351', '1', 'Spring', '2010', 'B');
insert into takes values ('23121', 'FIN-201', '1', 'Spring', '2010', 'C+');
insert into takes values ('44553', 'PHY-101', '1', 'Fall', '2009', 'B-');
insert into takes values ('45678', 'CS-101', '1', 'Fall', '2009', 'F');
insert into takes values ('45678', 'CS-101', '1', 'Spring', '2010', 'B+');
insert into takes values ('45678', 'CS-319', '1', 'Spring', '2010', 'B');
insert into takes values ('54321', 'CS-101', '1', 'Fall', '2009', 'A-');
insert into takes values ('54321', 'CS-190', '2', 'Spring', '2009', 'B+');
insert into takes values ('55739', 'MU-199', '1', 'Spring', '2010', 'A-');
insert into takes values ('76543', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('76543', 'CS-319', '2', 'Spring', '2010', 'A');
insert into takes values ('76653', 'EE-181', '1', 'Spring', '2009', 'C');
insert into takes values ('98765', 'CS-101', '1', 'Fall', '2009', 'C-');
insert into takes values ('98765', 'CS-315', '1', 'Spring', '2010', 'B');
insert into takes values ('98988', 'BIO-101', '1', 'Summer', '2009', 'A');
insert into takes values ('98988', 'BIO-301', '1', 'Summer', '2010', null);
insert into advisor values ('00128', '45565');
insert into advisor values ('12345', '10101');
insert into advisor values ('23121', '76543');
insert into advisor values ('44553', '22222');
insert into advisor values ('45678', '22222');
insert into advisor values ('76543', '45565');
insert into advisor values ('76653', '98345');
insert into advisor values ('98765', '98345');
insert into advisor values ('98988', '76766');
insert into time_slot values ('A', 'M', '8', '0', '8', '50');
insert into time_slot values ('A', 'W', '8', '0', '8', '50');
insert into time_slot values ('A', 'F', '8', '0', '8', '50');
insert into time_slot values ('B', 'M', '9', '0', '9', '50');
insert into time_slot values ('B', 'W', '9', '0', '9', '50');
insert into time_slot values ('B', 'F', '9', '0', '9', '50');
insert into time_slot values ('C', 'M', '11', '0', '11', '50');
insert into time_slot values ('C', 'W', '11', '0', '11', '50');
insert into time_slot values ('C', 'F', '11', '0', '11', '50');
insert into time_slot values ('D', 'M', '13', '0', '13', '50');
insert into time_slot values ('D', 'W', '13', '0', '13', '50');
insert into time_slot values ('D', 'F', '13', '0', '13', '50');
insert into time_slot values ('E', 'T', '10', '30', '11', '45 ');
insert into time_slot values ('E', 'R', '10', '30', '11', '45 ');
insert into time_slot values ('F', 'T', '14', '30', '15', '45 ');
insert into time_slot values ('F', 'R', '14', '30', '15', '45 ');
insert into time_slot values ('G', 'M', '16', '0', '16', '50');
insert into time_slot values ('G', 'W', '16', '0', '16', '50');
insert into time_slot values ('G', 'F', '16', '0', '16', '50');
insert into time_slot values ('H', 'W', '10', '0', '12', '30');
insert into prereq values ('BIO-301', 'BIO-101');
insert into prereq values ('BIO-399', 'BIO-101');
insert into prereq values ('CS-190', 'CS-101');
insert into prereq values ('CS-315', 'CS-101');
insert into prereq values ('CS-319', 'CS-101');
insert into prereq values ('CS-347', 'CS-101');
insert into prereq values ('EE-181', 'PHY-101');
commit;
0 0
Add a comment
Know the answer?
Add Answer to:
Database concepts Find the names and ids of the students who have taken at most one...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • This is database system concept. 1.Find the ids of instructors who are also students using a...

    This is database system concept. 1.Find the ids of instructors who are also students using a set operation. Assume that a person is identified by her or his id. So, if the same id appears in both instructor and student, then that person is both an instructor and a student. Remember: set operation means union, intersect or set difference. 2.Find the ids of instructors who are also students using the set membership operator. 3.Find the ids of instructors who are...

  • Assume that the takes relation has not yet been defined. Give an SQL statement that adds...

    Assume that the takes relation has not yet been defined. Give an SQL statement that adds the takes relation to the database. Assume that: •Id is an integer •course_id is a string of up to10 characters. •sec_id should be a non-negative integer. •the semester is a one-character field, equal to either ‘F’ or ‘S’, and the •year is an integer. •grade can be null, but if not, it should be a letter in the string ‘ABCDF’. Your statement must ensure...

  • Write appropriate PL/SQL statements to create a procedure to check that no section has more students...

    Write appropriate PL/SQL statements to create a procedure to check that no section has more students than the capacity of the classroom in which the section is scheduled. Display the course_id, sec_id, semester, year, building, and room_number for the sections that have more students than the capacity of the classroom in which the section is scheduled.   The following are the tables needed: classroom (building, room_number, capacity) section (course_id, sec_id, semester, year, building, room_number, time_slot_id) takes (student_id, course_id, sec_id, semester, year,...

  • SQL question, you can right-click the picture and select open in a new tab for a...

    SQL question, you can right-click the picture and select open in a new tab for a better-looking experience. Write an SQL query using the university schema to find the ID of each student who has never taken a course at the university. Do this using no subqueries and no set operations (use an outer join) University Schema student takes ID ID пате course_id sec id semester уеar grade dept_name tot_cred section course |department course id sec_id semester advisor course_id title...

  • SQL Query Question: I have a database with the tables below, data has also been aded...

    SQL Query Question: I have a database with the tables below, data has also been aded into these tables, I have 2 tasks: 1. Add a column to a relational table POSITIONS to store information about the total number of skills needed by each advertised position. A name of the column is up to you. Assume that no more than 9 skills are needed for each position. Next, use a single UPDATE statement to set the values in the new...

  • Consider the Schema diagram for a university database a. Find the IDs of all students who...

    Consider the Schema diagram for a university database a. Find the IDs of all students who have taken all of the CMPSC courses. Use the division operation. b. Do the same operation as in problem a, using only fundamental operations. student takes ID course id sec id semester 1er 4112 dept_name tot cred grade advisor section course_id sec id semester year building TOOM_10 time_slof_id course course id title dept name credits department dept name building budget time slot time slot...

  • Using SQL and the following info Question 3: Find the people who do not have Viper...

    Using SQL and the following info Question 3: Find the people who do not have Viper Certification but are still assigned to Viper class ship Find the fname, lname, and ship_instance_id for all people who do not have Viper certification but are assigned to at least one instance of a Viper class ship (this includes all variants of Viper class ships). Return a row for every ship/person combination. Order your results by fname in ascending order. Use the BSG database...

  • Using SQL and the following info Question 3: Find the people who do not have Viper...

    Using SQL and the following info Question 3: Find the people who do not have Viper Certification but are still assigned to Viper class ship Find the fname, lname, and ship_instance_id for all people who do not have Viper certification but are assigned to at least one instance of a Viper class ship (this includes all variants of Viper class ships). Return a row for every ship/person combination. Order your results by fname in ascending order. Use the BSG database...

  • NEED HELP IN INSERT STATEMENTS FOR THE TABLES CAN YOU PLEASE ADD SOME INSERT STATEMENTS FOR...

    NEED HELP IN INSERT STATEMENTS FOR THE TABLES CAN YOU PLEASE ADD SOME INSERT STATEMENTS FOR A COMPANY SCHEMA SCRIPT. PLEASE ADN THANK YOU DROP TABLE dependent; DROP TABLE works_on; DROP TABLE project; DROP TABLE dept_locations; DROP TABLE department; DROP TABLE employee; CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary numeric(10,2), superssn char(9), dno numeric, primary key (ssn), foreign key (superssn) references employee(ssn) ); CREATE...

  • Write an SQL query to return the users who posted the most number of jokes on...

    Write an SQL query to return the users who posted the most number of jokes on 1/1/2019. Database: create table User( userid varchar(30) not null, password varchar(30), firstname varchar(30), lastname varchar(50), email varchar(50), gender char(1), age integer, banned boolean, primary key(userid), unique(email)); create table MyFriends( userid varchar(30), friendid varchar(30), primary key(userid,friendid), foreign key(userid) references User, foreign key(friendid) references User(userid)); Create table Jokes( Jokeid integer, title varchar(100), description varchar(255), authorid varchar(30) not null, primary key(jokeid), posttime Datetime, foreign key(authorid) references User(userid));...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT