Assessment Questions Below Tables You Need To Consider

3assessment Questionsnotebelow Tables You Need To Consider For Writi

Below tables you need to consider for writing SQL queries. Name of the tables, attributes, and data is available in all the tables.

Write the SQL queries for the following questions:

Question 1: Create two different forms of the INSERT command to add a student with a student ID of 60001 and last name John to the STUDENT table. (8 points)

Question 2: Write a SQL command that will modify the name of course ISM4212 from Database to introduction to Oracle. (8 points)

Question 3: Which students are enrolled in Database and Networking course? (8 points)

Question 4: How many students are enrolled in Section 2714 in the first semester of 2018? (8 points)

Question 5: Write a query to add a column state in student table and also show the use of check constraint by inserting state name (CA, FL, LA, NY). (8 points)

Question 6: Write a query using above table (one table or combination of tables) to show the use of all parts of select command. (8 points)

Question 7: What is the smallest section number used in the first semester of 2018. (8 points)

Question 8: Write a query to show the student ID, faculty ID where date qualified is 9/2008 in descending order. (8 points)

Question 9: Write a query using above tables to show the use of AND, OR operators. (8 points)

Question 10: In student table add the grade column and insert data for any two students. (8 points)

Question 11: Write a query using above table / tables to show the use of Alias name. (8 points)

Question 12: Write a query or set of queries to establish the relationships among student, faculty, and section tables. You may add any new column in these three table. (12 points)

Paper For Above instruction

In this paper, I will address each of the SQL query tasks outlined in the assignment, providing detailed explanations and the actual SQL code to execute each operation. The focus will be on demonstrating fundamental SQL skills, including data insertion, modification, table alteration, querying, and establishing relationships among tables. Understanding how to manipulate and interrogate relational databases effectively is essential for database management and application development.

Question 1: Creating Multiple INSERT Statements

To add a new student with a student ID of 60001 and last name John to the STUDENT table, two different forms of the INSERT command can be used.

The first form uses explicit column specification, which explicitly states the columns being inserted into:

INSERT INTO STUDENT (student_id, last_name) VALUES (60001, 'John');

The second form leverages the all-rows insert syntax, which assumes the order of the columns matches the table definition:

INSERT INTO STUDENT VALUES (60001, 'John');

Alternatively, if additional columns are present and default values are set for others, the explicit form ensures clarity and correctness. Both methods are valid; however, explicitly specifying columns is generally recommended for clarity and maintainability.

Question 2: Modifying Course Name

To change the course name from "Database" to "Introduction to Oracle" for course ISM4212, the SQL UPDATE statement is used:

UPDATE COURSE
SET course_name = 'Introduction to Oracle'
WHERE course_id = 'ISM4212' AND course_name = 'Database';

This command locates the course with ID ISM4212 and updates its name.

Question 3: Identifying Students Enrolled in Specific Courses

To find students enrolled in both Database and Networking courses, a typical approach involves joining enrollment, student, and course tables, filtering by course names, and selecting distinct student identifiers:

SELECT DISTINCT s.student_id, s.last_name
FROM STUDENT s
JOIN ENROLLMENT e ON s.student_id = e.student_id
JOIN COURSE c ON e.course_id = c.course_id
WHERE c.course_name IN ('Database', 'Networking')
GROUP BY s.student_id, s.last_name
HAVING COUNT(DISTINCT c.course_name) = 2;

This query retrieves students enrolled in both courses by checking for two distinct courses in their enrolled courses list.

Question 4: Counting Students in a Specific Section and Semester

To determine how many students are enrolled in Section 2714 during the first semester of 2018, a COUNT operation with appropriate WHERE clause is applied:

SELECT COUNT(DISTINCT e.student_id) AS total_students
FROM ENROLLMENT e
JOIN SECTION sec ON e.section_id = sec.section_id
WHERE sec.section_number = 2714
AND sec.semester = 'Spring'
AND sec.year = 2018;

This query counts unique student IDs enrolled in the specified section and semester.

Question 5: Altering Table and Using Check Constraints

Adding a new column 'state' to the STUDENT table with a check constraint to limit values is done as follows:

ALTER TABLE STUDENT
ADD (state VARCHAR2(2) CONSTRAINT check_state CHECK (state IN ('CA', 'FL', 'LA', 'NY')));

Inserting data with valid state names would be:

INSERT INTO STUDENT (student_id, last_name, state) VALUES (60002, 'Doe', 'CA');

Attempting to insert a state outside the specified options will result in constraint violation errors.

Question 6: Demonstrating All Parts of the SELECT Command

Utilizing a combined query involving multiple tables, one could demonstrate SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and aggregations:

SELECT s.student_id, s.last_name, c.course_name, e.grade
FROM STUDENT s
JOIN ENROLLMENT e ON s.student_id = e.student_id
JOIN COURSE c ON e.course_id = c.course_id
WHERE e.grade IS NOT NULL
ORDER BY s.student_id ASC, c.course_name;

This query retrieves specific student-course-grade data, ordered by student ID and course name, illustrating multiple SELECT parts.

Question 7: Smallest Section Number in a Semester

To find the smallest section number used during the first semester of 2018:

SELECT MIN(section_number) AS smallest_section
FROM SECTION
WHERE semester = 'Spring' AND year = 2018;

Question 8: Query With ORDER BY and Specific Date

Retrieving student ID and faculty ID where the qualification date is September 2008, ordered descending:

SELECT student_id, faculty_id
FROM QUALIFICATION
WHERE date_qualified = TO_DATE('09/2008', 'MM/YYYY')
ORDER BY faculty_id DESC;

Note: Date format may need adjustment based on database design.

Question 9: Using AND, OR in Queries

A query combining conditions with AND and OR operators could be:

SELECT student_id, last_name
FROM STUDENT
WHERE (major = 'Computer Science' AND GPA >= 3.0)
OR (last_name LIKE 'S%');

This query pulls students majoring in Computer Science with high GPA or students with last names starting with S.

Question 10: Adding and Populating Grade Column

To add a new column 'grade' to the STUDENT table and insert sample data:

ALTER TABLE STUDENT
ADD (grade VARCHAR2(2));

UPDATE STUDENT SET grade = 'A' WHERE student_id = 60001;
UPDATE STUDENT SET grade = 'B' WHERE student_id = 60002;

Question 11: Using Aliases in Queries

Using aliasing to rename columns or tables improves clarity:

SELECT s.student_id AS ID, s.last_name AS LastName, c.course_name AS Course
FROM STUDENT s
JOIN ENROLLMENT e ON s.student_id = e.student_id
JOIN COURSE c ON e.course_id = c.course_id;

Question 12: Establishing Relationships Among Tables

To clarify relationships, foreign keys are established among STUDENT, FACULTY, and SECTION tables, possibly adding new columns:

ALTER TABLE ENROLLMENT
ADD (student_id NUMBER REFERENCES STUDENT(student_id),
section_id NUMBER REFERENCES SECTION(section_id));
ALTER TABLE SECTION
ADD (faculty_id NUMBER REFERENCES FACULTY(faculty_id));

This links students to sections, sections to faculty, thereby modeling their relationships explicitly.

Conclusion

This comprehensive application of SQL commands demonstrates core database operations necessary for managing and querying a relational database effectively. Mastery of insertions, updates, modifications, constraints, joins, and relationship establishment forms the foundation for advanced database administration and development tasks.

References

• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
• Relational Database Design and Implementation. Morgan Kaufmann.
• Database System Concepts (7th ed.). McGraw-Hill Education.
• Database Design and Relational Theory. O'Reilly Media.
• Database Management Systems (3rd ed.). McGraw-Hill.
• . Springer.
• Database Concepts (8th ed.). Pearson.
• Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.
• Communications of the ACM, 13(6), 377–387.
• Principles of Database and Knowledge-Base Systems. Computer Science Press.