student_management_query.sql

CREATE DATABASE student_management_system;
USE student_management_system;


CREATE TABLE student_details (
    student_id INT(20) NOT NULL UNIQUE PRIMARY KEY,
    facaulty VARCHAR(100) NOT NULL,
    elective_courses VARCHAR(200) NOT NULL,
    required_courses VARCHAR(200) NOT NULL,
    dept VARCHAR(70) NOT NULL,
    semester_term VARCHAR(40) NOT NULL,
    cgpa_percentage INT(10) NOT NULL,
    remark VARCHAR(200) NOT NULL
);
 
 
CREATE TABLE student_profile (
    student_id INT(12) NOT NULL UNIQUE PRIMARY KEY,
    full_name VARCHAR(300) NOT NULL,
    age INT(2) NOT NULL,
    nationality VARCHAR(40) NOT NULL,
    state_of_Origin VARCHAR(100) NOT NULL,
    subject_Course_offerred INT(2) NOT NULL,
    email_address VARCHAR(100) NOT NULL,
    phone_number INT(11),
    class_level VARCHAR(30) NOT NULL,
    department VARCHAR(50),
    religion VARCHAR(15) NOT NULL,
    parent_guardian_full_name VARCHAR(300) NOT NULL,
    parent_guardian_phone_number INT(11) NOT NULL,
    parent_guardian_email_address VARCHAR(100) NOT NULL,
    Residential_address VARCHAR(900) NOT NULL,
    Sex VARCHAR(10) NOT NULL,
    Marital_status VARCHAR(10) NOT NULL
);



CREATE TABLE courses (
    Course_code VARCHAR(20) NOT NULL UNIQUE PRIMARY KEY,
    Course_name VARCHAR(100) NOT NULL,
    Course_unit INT(10) NOT NULL
);
 
 
 
 
 -- to confirm if our details is not null, perform select query to retrieve all data from student_details and courses.
SELECT * FROM student_details;
SELECT * FROM courses;
 
 
 
 

-- this query will insert multiple data to student_details table.
INSERT INTO student_details(student_id, facaulty,elective_courses,required_courses,dept,semester_term,cgpa_percentage,remark)
VALUES
    (1212, 'science','1','10','computer science','1','3.50','good boy'),
    (1213, 'science','1','10','computer engr','1','3.78','nice boy'),
    (1214, 'science','1','10','microbiology','1','3.99','nice and good boy');
    
    -- this query will insert multiple data to courses table 
    INSERT INTO courses(Course_code, Course_name,Course_unit)
VALUES
    ('MTH111', 'Algebra',3),
    ('MTH221','Calculus',4),
    ('MTH321','Polynomial',3);
    
  
    
    
-- The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns.
-- An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables.
 
 -- example 1
SELECT student_details.student_id, courses.Course_code 
FROM student_details
JOIN courses 
ON courses.Course_code = 'MTH111' AND student_details.student_id = 1212; 


-- example 2    
SELECT student_details.student_id, courses.Course_code 
FROM student_details INNER JOIN courses 
ON courses.Course_code = 'MTH111' AND student_details.student_id = 1212; 




-- The RIGHT JOIN keyword returns all records from the right table, even if there are no matches in the left table 
SELECT student_profile.student_id AS IDStudentProfile,
student_profile.full_name AS StudentName, 
student_profile.email_address AS StudentEmail, 
student_profile.residential_address AS StudentResidentialAddress, 
courses.Course_code AS CourseCode,
courses.course_name AS CourseName
FROM student_profile
RIGHT JOIN courses 
ON courses.course_name = "probability" AND student_profile.student_id = '1212'
ORDER BY student_profile.student_id;