Create 5 tables and show the SQL statements:

1. Output the student_ids, first and last names of all students taking Java.

2. Output all Student_Ids, First Names, and last Names of all students whose advisor is Jerry Johnson.

3. Output the names of all advisors who belong to the Accounting department.

4. Show the number of courses in the Course table.

5. Show the names of all courses in the Course table sorted by the Course name.

6. Output the first and last names of all students taking Economics or Biology.

7. Show the course ids and course names of all courses taken by the student David Fuller.

8. Update the Course name "Chemistry" in the Course table to "Biochemistry".

9. Output ALL the Student_Ids from the Student_Course table making sure no Student_Id is repeated (hint: used the DISTINCT keyword).

10. Delete the Student named "Carlos Carbo" from the Student table. If necessary delete him from any join table where his record is referenced.

-- Online SQL Editor to Run SQL Online. -- Use the editor to create new tables, insert data and all other SQL operations. CREATE TABLES ------------- CREATE TABLE ADVISOR ( ADVISOR_ID INTEGER PRIMARY KEY, ADVISOR_FIRST_NAME CHAR(33) NOT NULL, ADVISOR_LAST_NAME CHAR(33) NOT NULL, ADVISOR_PHONE CHAR(12), DEPARTMENT_ID CHAR(4) ); CREATE TABLE COURSE ( COURSE_ID INTEGER PRIMARY KEY, COURSE_NAME CHAR(33) NOT NULL ); CREATE TABLE DEPARTMENT ( DEPARTMENT_ID INTEGER PRIMARY KEY, DEPARTMENT_NAME CHAR(30) NOT NULL, DEPARTMENT_EMAIL CHAR(30) ); CREATE TABLE STUDENT ( STUDENT_ID INTEGER PRIMARY KEY, STUDENT_FIRST_NAME CHAR(30) NOT NULL, STUDENT_LAST_NAME CHAR(30) NOT NULL, STUDENT_EMAIL CHAR(30) ); CREATE TABLE STUDENT_COURSE ( STUDENT_COURSE_ID INTEGER PRIMARY KEY, STUDENT_ID INTEGER, COURSE_ID INTEGER, SCORE INTEGER ); CREATE TABLE STUDENT_ADVISOR ( STUDENT_ADVISOR_ID INTEGER PRIMARY KEY, STUDENT_ID INTEGER, ADVISOR_ID INTEGER ); INSERT DATA INTO TABLES

------------------------ Student INSERT INTO STUDENT VALUES (800111111,'John','Smith','jsmith@rollins.edu'); INSERT INTO STUDENT (STUDENT_ID, STUDENT_LAST_NAME, STUDENT_FIRST_NAME) VALUES (800111112,'Elmore','Martha'); INSERT INTO STUDENT VALUES (800112112,'Jonathan','Arp'); INSERT INTO STUDENT VALUES (800222222, 'Jill','Boyd','jboyd@rollins.edu'), (800232323, 'Albert','Einstein III','aeinstein@rollins.edu'); INSERT INTO STUDENT VALUES (800333333, 'Maurice','Kaprow','mkaprow@rollins.edu'), (800444444, 'Danielle','Register','dregister@rollins.edu'), (800555555, 'Allison','Oslund','aoslund@rollins.edu'), (800666666,'Lacy','Baze','lbaze@rollins.edu'), (800777777, 'Carlos','Carbo','ccarbo@rollins.edu'), (800888888, 'David','Fuller','dfuller@rollins.edu'), (800998877, 'Jim','Cramer','jcramer@rollins.edu'), (800999999, 'Linda','Davidson','ldavis@rollins.edu');

Advisor INSERT INTO ADVISOR VALUES(700000001,'Barbara','Jones','386-822-1111',12); INSERT INTO ADVISOR VALUES(700000002,'Jerry','Johnson','386-822-1205',13); INSERT INTO ADVISOR VALUES(700000003,'Katherine','Anderson','386-822- 1299',11); INSERT INTO ADVISOR VALUES(700000004,'Rose','Taylor','386-822-1252',11); INSERT INTO ADVISOR VALUES(700000005,'Kenneth','Murray','386-822- 1158',13); INSERT INTO ADVISOR VALUES(700000022,'Drew','Brees','386-822-7894',11); INSERT INTO ADVISOR VALUES(700000026,'Albert','Jones','',11); INSERT INTO ADVISOR VALUES(700000030,'Mark','Jones','386-822-7894',11); Course INSERT INTO COURSE VALUES(10107,'Analytics'); INSERT INTO COURSE VALUES(10708,'Economics'); INSERT INTO COURSE VALUES(13503,'Sociology'); INSERT INTO COURSE VALUES(14301,'Biology'); INSERT INTO COURSE VALUES(14711,'Chemistry'); INSERT INTO COURSE VALUES(15902,'Physics'); INSERT INTO COURSE VALUES(20101,'Statistics'); INSERT INTO COURSE VALUES(21102,'Java'); INSERT INTO COURSE VALUES(21401,'History'); Department INSERT INTO DEPARTMENT VALUES(11,'Accounting','accounting@rollins.edu'); INSERT INTO DEPARTMENT VALUES(12,'Business Analytics','bsan@rollins.edu'); INSERT INTO DEPARTMENT VALUES(13,'Marketing','mktg@rollins.edu'); INSERT INTO DEPARTMENT VALUES(14,'Political Science','pols@rollins.edu');

Student_Advisor INSERT INTO STUDENT_ADVISOR VALUES(1,800111111,700000001); INSERT INTO STUDENT_ADVISOR VALUES(2,800222222,700000002); INSERT INTO STUDENT_ADVISOR VALUES(3,800333333,700000003); INSERT INTO STUDENT_ADVISOR VALUES(4,800444444,700000004); INSERT INTO STUDENT_ADVISOR VALUES(5,800555555,700000003); INSERT INTO STUDENT_ADVISOR VALUES(6,800666666,700000002); INSERT INTO STUDENT_ADVISOR VALUES(7,800777777,700000005); INSERT INTO STUDENT_ADVISOR VALUES(8,800888888,700000001); INSERT INTO STUDENT_ADVISOR VALUES(9,800999999,700000003); INSERT INTO STUDENT_ADVISOR VALUES(10,800111112,700000001); INSERT INTO STUDENT_ADVISOR VALUES(11,800232323,700000005); INSERT INTO STUDENT_ADVISOR VALUES(12,800112112,700000005); Student_Course INSERT INTO STUDENT_COURSE VALUES(1,800111111,10107,90); INSERT INTO STUDENT_COURSE VALUES(2,800222222,20101,87); INSERT INTO STUDENT_COURSE VALUES(3,800333333,10107,65); INSERT INTO STUDENT_COURSE VALUES(4,800444444,10708,77); INSERT INTO STUDENT_COURSE VALUES(5,800555555,10107,88); INSERT INTO STUDENT_COURSE VALUES(6,800666666,10107,99); INSERT INTO STUDENT_COURSE VALUES(7,800777777,20101,65); INSERT INTO STUDENT_COURSE VALUES(8,800888888,10107,82); INSERT INTO STUDENT_COURSE VALUES(9,800999999,10708,90); INSERT INTO STUDENT_COURSE VALUES(10,800111111,14301,65); INSERT INTO STUDENT_COURSE VALUES(11,800222222,21102,97); INSERT INTO STUDENT_COURSE VALUES(12,800333333,14301,87); INSERT INTO STUDENT_COURSE VALUES(13,800444444,13503,77); INSERT INTO STUDENT_COURSE VALUES(14,800555555,14301,67); INSERT INTO STUDENT_COURSE VALUES(15,800666666,14301,64); INSERT INTO STUDENT_COURSE VALUES(16,800777777,21102,74); INSERT INTO STUDENT_COURSE VALUES(17,800888888,14301,84); INSERT INTO STUDENT_COURSE VALUES(18,800999999,13503,94); INSERT INTO STUDENT_COURSE VALUES(19,800111111,15902,88); INSERT INTO STUDENT_COURSE VALUES(20,800222222,21401,98); INSERT INTO STUDENT_COURSE VALUES(21,800333333,15902,78); INSERT INTO STUDENT_COURSE VALUES(22,800444444,14711,68); INSERT INTO STUDENT_COURSE VALUES(23,800555555,15902,58); INSERT INTO STUDENT_COURSE VALUES(24,800666666,15902,92); INSERT INTO STUDENT_COURSE VALUES(25,800777777,21401,82); INSERT INTO STUDENT_COURSE VALUES(26,800888888,15902,72); INSERT INTO STUDENT_COURSE VALUES(27,800999999,14711,62); INSERT INTO STUDENT_COURSE VALUES(28,800111112,10107,79); INSERT INTO STUDENT_COURSE VALUES(29,800232323,10107,89); INSERT INTO STUDENT_COURSE VALUES(30,800112112,10107,90); INSERT INTO STUDENT_COURSE VALUES(31,800112112,14711,90); SELECT SQL EXAMPLES