SQL Queries

sqlQueries.png

1 a. 10 differences between the 4 types of table join queries: INNER JOIN, LEFT JOIN, RIGHT JOIN and Full OUTER JOIN.

      Answer:
      
      
      Definition of the 4 types of table join queries in SQL are:

      I. INNER JOIN: 
          This type of join returns only the matching records between two tables based on a common column. 
          Inner join combines rows from two tables that have matching values in both tables.

      II. LEFT JOIN: 
           This type of join returns all the records from the left table and the matching records from the 
           right table based on a common column. If there is no match in the right table, NULL values will 
           be displayed in the result set.

      III. RIGHT JOIN: 
            This type of join returns all the records from the right table and the matching records from 
            the left table based on a common column. If there is no match in the left table, NULL values 
            will be displayed in the result set.

       IV. FULL OUTER JOIN: 
            This type of join returns all the records from both tables, including the records that do not 
            have a match in the other table based on a common column. If there is no match in one of the tables, 
            NULL values will be displayed in the result set.
            
            
      10 differences between the 4 types of table join queries:

      I. Result set: 
          Inner join returns only the matching records between two tables. Left join returns all records 
          from the left table and the matching records from the right table. Right join returns all records 
          from the right table and the matching records from the left table. Full outer join returns all records 
          from both tables, including the records that do not have a match in the other table.

     II. Null values: 
          Inner join does not display null values. Left join displays null values for the unmatched records in 
          the right table. Right join displays null values for the unmatched records in the left table. Full outer 
          join displays null values for the unmatched records in either table.

    III. Syntax: 
          Inner join uses the keyword "JOIN". Left join uses the keyword "LEFT JOIN". Right join uses the keyword 
          "RIGHT JOIN". Full outer join uses the keyword "FULL OUTER JOIN".

     IV. Matching criteria: 
          Inner join requires a matching value in both tables to join records. Left join and right join require a 
          matching value in one table and allow null values for unmatched records in the other table. Full outer join 
          requires a matching value in at least one table and allows null values for unmatched records in both tables.

      V. Size of result set: 
          Inner join typically returns a smaller result set than left join, right join, or full outer join, since it 
          only returns matching records. Left join and right join return a larger result set than inner join, since 
          they include all records from one table and matching records from the other table. Full outer join returns 
          the largest result set, since it includes all records from both tables.

     VI. Use case: 
          Inner join is used when you only need to retrieve records that exist in both tables. Left join and right 
          join are used when you want to include all records from one table and matching records from the other table. 
          Full outer join is used when you want to include all records from both tables.

    VII. Order of tables: 
          The order of tables does not matter in inner join, left join, or right join. However, in full outer 
          join, the order of tables affects the result set, since the first table listed in the query is considered 
          the "left" table and the second table is considered the "right" table.

   VIII. Performance: 
          Inner join is typically faster than left join, right join, or full outer join, since it only needs to compare 
          matching values between two tables. Left join and right join can be slower than inner join, since they include 
          all records from one table and matching records from the other table. Full outer join can be the slowest, since 
          it includes all records from both tables.

     IX. Data duplication: 
          Left join, right join, and full outer join can result in data duplication if there are multiple matching records 
          in the non-matching table.

      X. Compatibility: 
          Inner join, left join, and right join are supported by most database systems. Full outer join is not supported by 
          some database systems, such as MySQL, but can be emulated using other SQL syntax.
      


1 b. Write a CREATE table query to create a university grading system, one table will consist of students, 
     another table will consist of their courses, and a grading table as a foreign key relationship between the 
     student and the courses, with their grades.
     
     Answer:
     
     CREATE TABLE students (
        student_id INT NOT NULL PRIMARY KEY,
        first_name VARCHAR(50) NOT NULL,
        last_name VARCHAR(50) NOT NULL,
        matric_number INT(50) unique,
        UNIT INT NOT NULL DEFAULT 
        email VARCHAR(100) NOT NULL,
        phone_number INT(11) NOT NULL
      );

      CREATE TABLE courses (
        course_id INT PRIMARY KEY,
        course_name VARCHAR(100),
        course_unit INT(10),
        lecturer_name VARCHAR(100)
      );

      CREATE TABLE grades (
        grade_id INT PRIMARY KEY,
        student_id INT,
        course_id INT,
        grade FLOAT,
        FOREIGN KEY (student_id) REFERENCES students(student_id),
        FOREIGN KEY (course_id) REFERENCES courses(course_id)
      );


1c.  Write a query to fetch a student’s grade.

     Answer:
     
      Assuming the ID of the student whose grade we want to fetch is 4 (i.e. student_id = 4) 
      and let's say the student is enrolled in a course with an ID of 7 (i.e. course_id = 7), 
      then the following query should suffice:

      SELECT grade FROM grades WHERE student_id = 4 AND course_id = 7;

      This query will retrieve the grade of the student with ID 4 in the course with ID 7 from the "grades" table.