IM323 - Хичээлийн хөтөлбөр

Total of 70 points:

30 points: Laboratories + Assignment
10 points: Tests #1 - 20 questions with 40 correct answers. Week 8
10 points: Tests #2
15 points: Class Attendance
5 points: Activeness

Student progress is here:
http://im323.blogspot.com/p/im323-student-progress-grades.html

Assignment-30 points

10th week - Acceptance of table design: 10 points out of 30 points.

Basic Requirements:
- Database creation for the corresponding chosen topic
- At least 8 tables
- Sample data with up to 5 records
- Simple tasks like laboraties...

IM323-Test 1 results description

Test 1 - 20 questions with 40 correct answers.
IM323-2011 Students got 11-33 points.

26 is the count of the correct answers for scoring 10 in Test 1.
(Meaning 13 correct answers will get 5 points from Test 1)

Any comments?

Test1-Chapter 1-6

2011-March. Test1 covers Chapter 1-6 of training book.

Lab6

Unless specified otherwise, use the Student_course database to answer the following questions. Also, use appropriate column headings when displaying your output.

1. Develop and execute a query to find the names of students who had HERMANO as an instructor and earned a grade of B or better in the class. Develop the query by first finding sections where HERMANO was the instructor. Save this query. Edit the query and modify it to join the Section table with the Grade_report table. Add the grade constraint.

2. Using the Student table, create a duplicate table called Stutab that contains all rows from the Student table. Hint: Look at the design of the Student table to see the columns and their definitions. Create the Stutab table with a CREATE TABLE command. Insert data into Stutab using the INSERT INTO .. SELECT option.

   Using the newly created Stutab table:

   1. List student names and majors of the juniors and seniors.

   2. List student names of the COSC majors.

   3. Create a view (call it vstu) that contains student names and majors for the COSC majors.

   4. List the student names and majors from vstu in descending order by name.

   5. Modify a row in your view of your table so that a student changes his or her major.

   6. Display of the view. Did modifying the view, vstu, also change the parent table, Stutab?

   7. Try to modify the view again, but this time, change the major to COMPSC--an obviously invalid column in the Stutab table, because the column was defined as four characters. Can you do it? What happens?

   8. Using Stutab, create a local temporary table (call it #stutemp) that contains student names and majors for the COSC majors.

   9. List the student names and majors from #stutemp in ascending order by name.

   10. Modify a row in #stutemp so that a student changes his or her major.

   11. Display the local temporary table. Did modifying your temporary table, #stutemp, also change the parent table, Stutab.

   12. Try to modify the local temporary table again, but this time change the major to COMPSC--again, an obviously invalid field in Stutab, because the field was defined as four characters. Can you do it? What happens?

   13. Using Stutab, create a global temporary table (call it ##gstutemp) that contains student names and majors for the COSC majors.

   14. List the student names and majors from ##gstutemp in ascending order by name.

   15. Modify a row in ##gstutemp so that a student changes his or her major.

   16. Display the global temporary table. Did modifying your temporary table, ##gstutemp, also change the parent table, Stutab.

   17. Try to modify the global temporary table again, but this time change the major to COMPSC--again, an obviously invalid field in Stutab, because the field was defined as four characters. Can you do it? What happens?

   18. Create an inline view (call it invstu) that contains student names and majors for COSC majors.

3. Perform an experiment to determine the precedence in a query with three conditions linked by AND and OR. Which precedence is followed: AND, OR, or left-to-right?

   Run this query:

       SELECT *
      FROM   Student
      WHERE  stno < 100 AND major = 'COSC' OR major = 'ACCT'
   

   
   

   Then run the following two queries and determine which one gives you the same output as the preceding non parenthesized statement:

       SELECT *
      FROM   Student
      WHERE  (stno < 100 AND major = 'COSC') OR major = 'ACCT'
   

   
   

   or:

       SELECT *
      FROM   Student
      WHERE  stno < 100 AND (major = 'COSC' OR major = 'ACCT')
   

   
   

   What happens if you put the OR first instead of the AND and run the query without parentheses?

4. Develop a query to find the instructor name and course name for computer science courses (use the Section table).

   1. Convert your query into a view.

   2. Convert the query into an inline view with column aliases and test it.

   3. Include an ORDER BY clause outside of the inline view in the main query and run your query again.