Data base Backup and Restoration

To manage database persistence, use the mysqldump utility.

Exporting Data: To export both table structure and existing records:

mysqldump -u username -p db_name > backup.sql

To export only the schema (structure):

mysqldump -u username -d db_name > schema_only.sql

Importing Data: To restore data from a file, ensure the target database exists:

CREATE DATABASE new_db CHARACTER SET utf8mb4;

mysqldump -u username -p new_db < backup.sql

Advanced Query Exercises

The following examples utilize a schema containing student, class, course, teacher, and score tables.

Subqueries and Derived Tables Use internal select statements to filter data dynamically:

SELECT student_id FROM (SELECT * FROM score WHERE val > 60) AS filtered_scores;

Aggregation and Joins Retrieve students with an average score higher than 60:

SELECT s.student_id, st.sname, avg_data.avg_val 
FROM (SELECT student_id, AVG(val) AS avg_val FROM score GROUP BY student_id HAVING AVG(val) > 60) AS avg_data 
LEFT JOIN student st ON avg_data.student_id = st.sid;

Teacher-Course Analysis Calculate the workload per instructor:

SELECT teacher_id, COUNT(cname) FROM course GROUP BY teacher_id;

Set Operations for Exclusion Iedntify students who have not enrolled in courses taught by a specific teacher:

SELECT sname FROM student WHERE sid NOT IN (
    SELECT DISTINCT student_id FROM score WHERE course_id IN (
        SELECT cid FROM course c JOIN teacher t ON c.teacher_id = t.tid WHERE t.tname = 'Specific Teacher'
    )
);

Handling Multi-Criteria Enrollments Find students who enrolled in both Course A and Course B:

SELECT student_id 
FROM score 
WHERE course_id IN (1, 2) 
GROUP BY student_id 
HAVING COUNT(DISTINCT course_id) = 2;

Conditional Aggregations Calculate pass rates per subject using conditional logic:

SELECT course_id, 
       AVG(val) AS avg_score, 
       SUM(CASE WHEN val >= 60 THEN 1 ELSE 0 END) / COUNT(*) * 100 AS pass_rate 
FROM score 
GROUP BY course_id;

Data Modification Insert calculated values based on current records:

INSERT INTO score (student_id, course_id, val) 
SELECT sid, 2, (SELECT AVG(val) FROM score WHERE course_id = 2) 
FROM student 
WHERE sid NOT IN (SELECT student_id FROM score WHERE course_id = 2);