Question

4-5. List the junior level COSC courses (like COSC3xxx) and the name of the course. Use the Course table.

4-6. Using the COUNT feature, determine whether there are duplicate names or student numbers in the Student table.

4-7. Assume all math courses start with MATH. How many math courses are there in the Section table? From the COUNT of courses, does it appear there are any math courses in the Section table not in the Course table? Again, using COUNTs, are there any math courses in the Course table not in the Section table? Does it appear there are any courses at all in the Grade_report, Section, or Course tables not in the others? (We will study how to ask these questions in SQL in a later chapter). 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.

4-8. Display dictionary views for the tables we have in the Student-Course database (refer to the diagram in Appendix 3 for all the table names). Use “All_tables” as the dictionary view. Do the query as follows:

a. DESCRIBE the table with DESC All_tables;

b. Display the number of rows in All_tables. Use SELECT COUNT(*) FROM All_tables; When you are exploring the dictionary, it is not a good idea to simply SELECT * FROM whatever, where whatever is some dictionary view. Dictionary views are often long and wide --wide because there are often many attributes and many of those attributes are not necessarily interesting.

c. Display the owner and table_name from All_tables where owner = ‘your userid’.

CREATE THE TABLE FOR only for Student, GradeReport, and Section QUESTION 4-9.

4-9. For all the tables in the Student-Course database --Student, Grade_report, Section, Room, Course, Prereq, Department_to_major, list the attributes, number of rows, number of distinct rows, and number of rows without nulls. As you gather the information, put the information in a tabular format, as shown below (Note, you may want to create this table in your word processor as you gather the information.)
Table Attribute Rows Distinct Rows Rows without Nulls Student stno
48
sname major
class Section section_id etc.
Also, there is probably no “one” query to give you this information. You will have to fi nd this information using separate queries, and then put the information together in a tabular format. Hint: You can use:
SELECT COUNT (*) FROM Student WHERE sname IS NULL

Answer 1

Q4-5. List the junior level COSC courses (like COSC3xxx) and the name of the course. Use the Course table.
Answer:-----
SELECT course_number, course_name
FROM Course
WHERE course_number LIKE 'COSC3%';

Q4-6. Using the COUNT feature, determine whether there are duplicate names or student numbers in the Student table.
Answer:-----
SELECT COUNT(sname) FROM Student;
SELECT COUNT(stno) FROM Student​​​​​​​;
SELECT COUNT(DISTINCT sname) FROM Student​​​​​​​;
SELECT COUNT(DISTINCT stno) FROM Student​​​​​​​;

​​​​​​​
​​​​​​​
Q4-7. Assume all math courses start with MATH. How many math courses are there in the Section table? From the COUNT of courses, does it appear there are any math courses in the Section table not in the Course table? Again, using COUNTs, are there any math courses in the Course table not in the Section table? Does it appear there are any courses at all in the Grade_report, Section, or Course tables not in the others? (We will study how to ask these questions in SQL in a later chapter). 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.
Answer:----
​​​​SELECT COUNT(course_number)
FROM Section
WHERE course_number LIKE 'MATH%';
< > is NOT an = condition.

Q4-8. Display dictionary views for the tables we have in the Student-Course database (refer to the diagram in Appendix 3 for all the table names). Use “All_tables” as the dictionary view. Do the query as follows:

a. DESCRIBE the table with DESC All_tables;
Answer:-------
DESC ALL_TABLES ;

b. Display the number of rows in All_tables. Use SELECT COUNT(*) FROM All_tables; When you are exploring the dictionary, it is not a good idea to simply SELECT * FROM whatever, where whatever is some dictionary view. Dictionary views are often long and wide --wide because there are often many attributes and many of those attributes are not necessarily interesting.
Answer:-------
SELECT COUNT(*) FROM ALL_TABLES;

c. Display the owner and table_name from All_tables where owner = ‘your userid’.
Answer:-------
SELECT owner, table_name
FROM ALL_TABLES
WHERE owner = 'ATSW';