Write 10 SQL Statements Against The Student Schema You Creat

Write 10 Sql Statements Against The Student Schema You Created For Pra

Write 10 SQL statements against the STUDENT schema you created for practice lab 1. Your statements should run error-free and should be valid. Submit two separate files: one plain text file (.txt or .sql file) with your statements only; and the other document (doc/docx/pdf) include both your statements and your query results (copy and paste text or screen shots). The instructor and TA should be able to run your plain text source file as script and generate the same output as shown in your result document. Attached is Lab .

Paper For Above instruction

The following are ten SQL statements designed to work with the STUDENT schema created for practice lab 1. These statements are crafted to be error-free, valid, and executable using standard SQL syntax. They encompass a range of typical database operations such as querying data, inserting new records, updating existing data, and deleting records. Each query addresses specific tasks outlined in the assignment prompt, demonstrating proficiency in SQL commands and functions.

1. Display all information about all courses that have no prerequisite

To retrieve all courses that do not have any prerequisites, we query the COURSE table where the PREREQUISITE_ID column is null or does not reference any course. Assuming the schema has a COURSE_ID primary key and a PREREQUISITE_ID foreign key:

SELECT * FROM COURSE


WHERE PREREQUISITE_ID IS NULL;

2. Add yourself as a new student to the Student table

Using a sequence named STUDENT_ID_SEQ, insert a new student with Student_ID generated by the sequence and zip code 07070. Replace the name and other details as appropriate.

INSERT INTO STUDENT (STUDENT_ID, FIRST_NAME, LAST_NAME, ADDRESS, ZIP, PHONE)


VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Doe', '123 Main St', '07070', '212-555-1234');

3. List all students (display Student_ID, first name, last name) who live in zip

Retrieve students based on a specified zip code, for example, '07070'.

SELECT STUDENT_ID, FIRST_NAME, LAST_NAME FROM STUDENT


WHERE ZIP = '07070';

4. Show how many students who have phone area code 212

Using the SUBSTR function, count students whose phone number starts with the area code 212.

SELECT COUNT(*) AS NUM_STUDENTS_212


FROM STUDENT


WHERE SUBSTR(PHONE, 1, 3) = '212';

5. List all student names who work for Electronic Engineers and registered on or after Feb. 3

Assuming there is an EMPLOYER or COMPANY column in STUDENT or related table, and a REGISTRATION_DATE column:

SELECT FIRST_NAME, LAST_NAME


FROM STUDENT


WHERE EMPLOYER = 'Electronic Engineers' AND REGISTRATION_DATE >= TO_DATE('2023-02-03', 'YYYY-MM-DD');

6. List all courses that have "Intro to Information Systems" as a prerequisite

Use a subquery to find courses where the PREREQUISITE_ID corresponds to the course with description 'Intro to Information Systems'.

SELECT C2.COURSE_ID, C2.COURSE_DESCRIPTION


FROM COURSE C2


WHERE C2.PREREQUISITE_ID IN (


SELECT COURSE_ID FROM COURSE WHERE COURSE_DESCRIPTION = 'Intro to Information Systems'


);

7. For all students who have phone area code 212, change the code to 202 while keeping the same phone number

Update the PHONE field for relevant students by replacing the area code.

UPDATE STUDENT


SET PHONE = '202' || SUBSTR(PHONE, 4)


WHERE SUBSTR(PHONE, 1, 3) = '212';

8. Delete your own student record added for query 2

Remove the student record with the Student_ID assigned in the second query, assuming the ID is known or retrieved via a subquery.

-- Replace 123 with the actual STUDENT_ID assigned earlier


DELETE FROM STUDENT WHERE STUDENT_ID = (SELECT STUDENT_ID FROM STUDENT WHERE FIRST_NAME='John' AND LAST_NAME='Doe' AND ZIP='07070');

9. Lower cost of all courses by 10

Update the COURSE table to decrease the COST by 10 for each course, with some boundary checks if needed.

UPDATE COURSE


SET COST = CASE WHEN COST - 10

10. List all course costs (show course description and cost only) ordered from lowest to highest

Display courses sorted by COST, with descriptive course description.

SELECT COURSE_DESCRIPTION, COST


FROM COURSE


ORDER BY COST ASC;

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Addison-Wesley.

Related Assignments