BEFORE YOU START

1. Connect to SQL server management studio.

2. Click the arrow next to your server.

3. Right-click the Databases folder.

4. In the context menu, select New Database.

5. Enter the database name as TinyCollege.

6. Click on New Query in the top ribbon, make sure the TinyCollege database is selected.

7. Copy and paste the content of the TinyCollege_SQL.txt in the SQL query canvas, then click Execute (or hit F5).

8. Right click on Databases and refresh. You can now see the TinyCollege database created.

9. Expand the TinyCollege database, then expand Tables, you should see the following tables: CLASS, COURSE, ENROLL and STUDENT.

INSTRUCTIONS [20PTS]

Using TinyCollege database, write the sql queries to answer each of the following questions. Your answer should consist of the SQL query and also the result of the query in a table form.

1. Display all courses in the COURSE table.

2. Display the courses whose credit is 4 (use CRS_CREDIT column in the conditional restriction).

3. Display the courses that relate to the accounting department (use DEPT_CODE column in the conditional restriction to be equal ACCT)

4. Display the courses offered by CIS department and whose credit is 4.

5. Display the courses that contain the term Account or Statistic anywhere in their descriptions.

6. Given the course code ACCT-211, use the class table to list the class section, class time and class room.

7. Use the ENROLL table and list the distinct class codes.

8. A course that is currently offered in a term, should appear in at least one entry in the class table. Courses that are not offered in a term, would have an entry only in the COURSE table and do not show up in any entry of the CLASS table. Use the NOT IN operator to display the courses that are not currently offered, meaning, display the courses from the COURSE table, that do not show in the CLASS table. Refer to slide 22, in M4_SQL_partI

9. Display the student records alphabetically in ascending order based their last name

10. Display the students from the ENROLL table whose grade is A or B. Use the in operator. Also use alias to show the grade column name in the result set as "Grade" instead of "ENROLL_GRADE"

TXT FILE:

/* Created with SQL Script Builder v.1.5 */ /* Type of SQL : SQL Server */ use TinyCollege; /* Created with SQL Script Builder v.1.5 */ CREATE TABLE COURSE ( CRS_CODE varchar(10) not null, DEPT_CODE varchar(10) not null, CRS_DESCRIPTION varchar(35), CRS_CREDIT numeric(8), PRIMARY KEY (CRS_CODE) ); INSERT INTO COURSE VALUES('ACCT-211','ACCT','Accounting I','3'); INSERT INTO COURSE VALUES('ACCT-212','ACCT','Accounting II','3'); INSERT INTO COURSE VALUES('CIS-220','CIS','Intro. to Microcomputing','3'); INSERT INTO COURSE VALUES('CIS-420','CIS','Database Design and Implementation','4'); INSERT INTO COURSE VALUES('QM-261','CIS','Intro. to Statistics','3'); INSERT INTO COURSE VALUES('QM-362','CIS','Statistical Applications','4'); /* Created with SQL Script Builder v.1.5 */ CREATE TABLE CLASS ( CLASS_CODE varchar(5), CRS_CODE varchar(10) not null, CLASS_SECTION varchar(2) not null, CLASS_TIME varchar(20), CLASS_ROOM varchar(8), PROF_NUM numeric(8), PRIMARY KEY (CLASS_CODE), FOREIGN KEY (CRS_CODE) REFERENCES COURSE (CRS_CODE) ); INSERT INTO CLASS VALUES('10012','ACCT-211','1','MWF 8:00-8:50 a.m.','BUS311','105'); INSERT INTO CLASS VALUES('10013','ACCT-211','2','MWF 9:00-9:50 a.m.','BUS200','105'); INSERT INTO CLASS VALUES('10014','ACCT-211','3','TTh 2:30-3:45 p.m.','BUS252','342'); INSERT INTO CLASS VALUES('10015','ACCT-212','1','MWF 10:00-10:50 a.m.','BUS311','301'); INSERT INTO CLASS VALUES('10016','ACCT-212','2','Th 6:00-8:40 p.m.','BUS252','301'); INSERT INTO CLASS VALUES('10017','CIS-220','1','MWF 9:00-9:50 a.m.','KLR209','228'); INSERT INTO CLASS VALUES('10018','CIS-220','2','MWF 9:00-9:50 a.m.','KLR211','114'); INSERT INTO CLASS VALUES('10019','CIS-220','3','MWF 10:00-10:50 a.m.','KLR209','228'); INSERT INTO CLASS VALUES('10020','CIS-420','1','W 6:00-8:40 p.m.','KLR209','162'); INSERT INTO CLASS VALUES('10021','QM-261','1','MWF 8:00-8:50 a.m.','KLR200','114'); INSERT INTO CLASS VALUES('10022','QM-261','2','TTh 1:00-2:15 p.m.','KLR200','114'); INSERT INTO CLASS VALUES('10023','QM-362','1','MWF 11:00-11:50 a.m.','KLR200','162'); INSERT INTO CLASS VALUES('10024','QM-362','2','TTh 2:30-3:45 p.m.','KLR200','162'); /* Created with SQL Script Builder v.1.5 */ CREATE TABLE STUDENT ( STU_NUM int not null, STU_LNAME varchar(15), STU_FNAME varchar(15), STU_INIT varchar(1), STU_DOB datetime, PRIMARY KEY (STU_NUM) ); INSERT INTO STUDENT VALUES('321452','Bowser','William','C','2/12/1972'); INSERT INTO STUDENT VALUES('324257','Smithson','Anne','K','11/15/1977'); INSERT INTO STUDENT VALUES('324258','Brewer','Juliette','','8/23/1966'); INSERT INTO STUDENT VALUES('324269','Oblonski','Walter','H','9/16/1973'); INSERT INTO STUDENT VALUES('324273','Smith','John','D','12/30/1955'); INSERT INTO STUDENT VALUES('324274','Katinga','Raphael','P','10/21/1976'); INSERT INTO STUDENT VALUES('324291','Robertson','Gerald','T','4/8/1970'); INSERT INTO STUDENT VALUES('324299','Smith','John','B','11/30/1983'); CREATE TABLE ENROLL ( CLASS_CODE varchar(5) not null, STU_NUM int not null, ENROLL_GRADE varchar(50), PRIMARY KEY(CLASS_CODE, STU_NUM), FOREIGN KEY(CLASS_CODE) REFERENCES CLASS (CLASS_CODE), FOREIGN KEY(STU_NUM) REFERENCES STUDENT (STU_NUM) ); INSERT INTO ENROLL VALUES('10014','321452','C'); INSERT INTO ENROLL VALUES('10014','324257','B'); INSERT INTO ENROLL VALUES('10018','321452','A'); INSERT INTO ENROLL VALUES('10018','324257','B'); INSERT INTO ENROLL VALUES('10021','321452','C'); INSERT INTO ENROLL VALUES('10021','324257','C');