CSMS IM323 - Database Management

Thursday, March 24, 2011

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?

Wednesday, March 16, 2011

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.

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.
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.
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?
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.

Thursday, February 24, 2011

Lab 5

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

Display the COUNT of tuples (rows) in each of the tables Grade_report, Student, and Section. How many rows would you expect in the Cartesian product of all three tables? Display the COUNT (not the resulting rows) of the Cartesian product of all three and verify your result (use SELECT COUNT(*) ...).
Display the COUNT of section-ids from the Section table. Display the COUNT of DISTINCT section-ids from the Grade_report table. What does this information tell you? (Hint: section_id is the primary key of the Section table.)
Write, execute, and print a query to list student names and grades (just two attributes) using the table alias feature. Restrict the list to students that have either As or Bs in courses with ACCT prefixes only.

Here's how to complete this problem:
1. Get the statement to work as a COUNT of a join of the three tables, Student, Grade_report, Section. Use table aliases in the join condition. Note that a join of n tables requires (n - 1) join conditions, so here you have to have two join conditions: one to join the Student and Grade_report tables, and one to join the Grade_report and Section tables. Note the number of rows that you get (expect no more rows than is in the Grade_report table). Why do you get this result?
2. Modify the query and put the Accounting condition in the WHERE clause. Note the number of rows in the resultit should be a good bit less than in question 3a.
3. Again, modify the query and add the grade constraints. The number of rows should decrease again. Note that if you have WHERE x and y or z, parentheses are optional, but then the criteria will be interpreted according to precedence rules.
The reason that we want you to "start small" and add conditions is that it gives you a check on what you ought to get and it allows you to output less nonsense. Your minimal starting point should be a count of the join with appropriate join conditions.
Using the Student table, answer the following questions:
1. How many students have names like Smith?
2. How many have names that contain the letter sequence Smith?
3. How many student names end in LD?
4. How many student names start with S?
5. How many student names do not have "i" as the second letter?
6. Would SELECT * FROM Student WHERE sname LIKE 'Smith%' find someone whose name is:
  
  LA SMITH
  
  SMITH-JONES
  
  SMITH JR.
  
  SMITH, JR
Using the Course table, answer the following questions:
1. List the junior-level COSC courses (LIKE COSC3xxx) and the name of the courses.
2. List all the courses except the junior-level COSC courses (use NOT LIKE).
Using the COUNT feature, determine whether there are duplicate names or student numbers in the Student table.
Assume that all math courses start with MATH. How many math courses are there in the Section table? From the count of courses, does it appear that there any math courses in the Section table that are not in the Course table? Again, using COUNTs, are there any math courses in the Course table that are not in the Section table? Does it appear that there are any courses at all that are in the Grade_report, Section, or Course tables that are not in the others? (We will study how to ask these questions in SQL in a later chapter.) Note that a query like the following would not work:
```
    SELECT g.section_id
   FROM Grade_report g, Section t
   WHERE g.section_id <> t.section_id
```
Explain why WHERE .. <> .. will not work to produce the desired output.

For every table in the Student_course database, we would like to compile the following information: attributes, number of rows, number of distinct rows, and rows without nulls. Find this information using different queries and compile the information in a table as shown here:

Table	Attribute	Rows	Distinct Rows	Rows without Nulls
`Student`	`Stno`	48	48	48
	`Sname`	48	47	48
	`Major`	48	8
	`Class`	etc.	etc.	etc.
`Section`	`Section_id`	etc.	etc.	etc.

The other tables in the Student_course database are Grade_report, Dependent, Section, Room, Course, Prereq, and Department_to_major.

Hint: You can use the following query:

    SELECT COUNT(*)
   FROM Student
   WHERE sname IS NULL

Find the count, sum, average, minimum, and maximum capacity of rooms in the database. Format the output using the STR function.
1. Where there is a null value for the capacity, assume the capacity to be 40, and find the average room size again.
Using the Student table, display the first 10 rows with an appended initial. For the appended initial, choose the halfway letter of the name, so that if a name is Evans, the initial is A (half of the length +1). If the name is Conway, the initial is W (again, (half of the length +1)). You do not need to round up or down, just use (LEN(Name)/2)+1 as the starting place to create the initial. Use appropriate column aliases. Your result should look like this (actual names may vary depending on the current database):
```
    PERSON#   NAMES
   --------- ------------------------
   1         Lineas, E.
   2         Mary, R.
   3         Brenda, N.
   4         Richard, H.
   5         Kelly, L.
   6         Lujack, A.
   7         Reva, V.
   8         Elainie, I.
   9         Harley, L.
   10        Donald, A.
```
1. Display the preceding output in all capital letters.
Find the names of the bottom 50 percent of the students, ordered by grade.
1. Find the names of the top 25 percent of the seniors, ordered by grade.
2. Now use the WITH TIES option with part (b). Is there any difference in the output?
Count the number of courses taught by each instructor.
1. Count the number of distinct courses taught by each instructor.
Count the number of classes each student is taking.
Display all the names that are less than five characters long from the Student table.
List all the students with student numbers in the 140s range.
Find all the students (the student names should be listed only once) who received As and Bs.
Would you call TOP an aggregate function? Why or why not?
Add an asterisk (*) to the names of all juniors and seniors who received at least one A. (This question will take a few steps, and you will have to approach this problem in a step-by-step manner.)
In this chapter, we used a table called Employee. Add a birthdate column and an employment_date column to the Employee table. Insert values into both the columns.
1. Display the current ages of all the employees.
2. Find the youngest employee.
3. Find the oldest employee.
4. Find the youngest employee at the time of employment.
5. Find the oldest employee at the time of employment.
6. Add five years to the current ages of all employees. Will any of the employees be over 65 in five years?
7. List the birth months and names of all employees.

Lab 4

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

Create two tables, Stu(name, majorCode) and Major(majorCode, majorDesc), with the following data. Use VARCHAR for the codes and appropriate data types for the other columns.

Stu

name

majorCode

Jones

CS

Smith

AC

Evans

MA

Adams

CS

Sumon

Major

majorCode

majorDesc

AC

Accounting

CS

Computer Science

MA

Math

HI

History
1. Display the Cartesian product (no WHERE clause) of the two tables. Use SELECT *.... How many rows did you get? How many rows will you always get when combining two tables with n and m rows in them (Cartesian product)?
2. Display an equi-join of the Stu and Major tables on majorCode. First do this using the INNER JOIN, and then display the results using the equi-join with an appropriate WHERE clause. Use appropriate table aliases. How many rows did you get?
3. Display whatever you get if you leave off the column qualifiers (the aliases) on the equi-join in question 1b. (Note: This will give an error because of ambiguous column names.)
4. Use the COUNT(*) function instead of SELECT * in the query. Use COUNT to show the number of rows in the result set of the equi-join.
5. Display the name, majorCode, and majorDesc of all students regardless of whether or not they have a declared major (even if the major column is null). (Hint: You need to use a LEFT OUTER JOIN here if Stu is the first table in your equi-join query.)
6. Display a list of majorDescs available (even if the majorDesc does not have students yet) and the students in each of the majors. (Hint: You need to use a RIGHT OUTER JOIN here.)
7. Display the Cartesian product of the two tables using a CROSS JOIN.
Create two tables, T1(name, jobno) and T2(jobno, jobdesc). Let jobno be data type INT, and use appropriate data types for the other columns. Put three rows in T1 and two rows in T2. Give T1.jobno values 1, 2, 3 for the three rows: <..., 1>,<..., 2,>,<..., 3>, where ... represents any value you choose. Give T2.jobno the values 1, 2: <1,...>,<2,...>.
1. How many rows are in the equi-join (on jobno) of T1 and T2?
2. If the values of T2.jobno were <2,...>, <2,...> (with different jobdesc values), how many rows would you expect to get, and why? Why would the rows have to have different descriptions?
3. If the values of T2.jobno were 4, 5 as in <4,...>,<5,...>, how many rows would you expect to get?
4. If the values of T1.jobno were <..., 1>,<..., 1>,<..., 1> (with different names) and the values of T2.jobno were <1,...>,<1...> with different descriptions, how many rows would you expect to get?
5. If you have two tables, what is the number of rows you may expect from an equi-join operation (and with what conditions)? A Cartesian product?
6. The number of rows in an equi-join of two tables, whose sizes are m and n rows, is from ___ to ____ depending on these conditions: _________ .
Use tables T1 and T2 in this exercise. Create another table called T3(jobdesc, minpay). Let minpay be of data type SMALLMONEY. Populate the table with at least one occurrence of each jobdesc from table T2 plus one more jobdesc that is not in T2. Write and display the result of a triple equi-join of T1, T2, and T3. Use an appropriate comment on each of the lines of the WHERE clause on which there are equi-join conditions. (Note: You will need two equi-join conditions.)
1. How many rows did you get in the equi-join?
2. Use the COUNT(*) function and display the number of rows in the equi-join.
3. How many rows would you get in this meaningless, triple Cartesian product (use COUNT(*))?
4. In an equi-join of n tables, you always have _______ _ equi-join conditions in the WHERE clause.
  
  In the preceding three exercises, you created tables T1, T2, T3, Stu, and Major. When you have completed the three exercises, delete these tables.
  
  Answer questions 4 through 8 by using the Student_course database.
Display a list of course names for all of the prerequisite courses.
Use a JOIN or INNER JOIN to join the Section and Course tables.
1. List the course names, instructors, the semesters and years they were teaching in.
2. List the instructor, course names, and offering departments of each of the courses the instructors were teaching.
Use a LEFT OUTER JOIN to join the Section and Course tables.
1. List the course names, instructors, and the semesters and years they were teaching in. Sort in descending order by instructors.
2. List the instructor, course names, and offering departments of each of the courses the instructors were teaching.
Use a RIGHT OUTER JOIN to join the Section and Course tables.
1. For each instructor, list the name of each course they teach and the semester and year in which they teach that course.
2. For each course, list the name of the instructor and the name of the department in which it is offered.
1. Are there any differences in the answers for questions 5, 6, and 7? Why? Explain.
2. Use a FULL OUTER JOIN to join the Section and Course tables. How do the results vary from the results of questions 5, 6, and 7?

`Stu`
`Jones`	`CS`
`Smith`	`AC`
`Evans`	`MA`
`Adams`	`CS`
`Sumon`

`Major`
`AC`	`Accounting`
`CS`	`Computer Science`
`MA`	`Math`
`HI`	`History`

Discuss the output that the following query would produce:

SELECT *

FROM Course AS c, Prereq AS p

WHERE c.course_number<>p.course_number

Find all the sophomores who are more senior than other students. (Hint: Use a self-join.)
Find all the courses that have more credit hours than other courses. (Hint: Use a self-join.)
Display a list of the names of all students who have dependents, the dependents name, relationship and age, ordered by the age of the dependent.

`Stu`
`name`	`majorCode`
`Jones`	`CS`
`Smith`	`AC`
`Evans`	`MA`
`Adams`	`CS`
`Sumon`

`Major`
`majorCode`	`majorDesc`
`AC`	`Accounting`
`CS`	`Computer Science`
`MA`	`Math`
`HI`	`History`

Pages

Thursday, March 24, 2011

Wednesday, March 16, 2011

Thursday, February 24, 2011