DBMS Data Manipulation SQL Joins PDF

DATABASE MANAGEMENT SYSTEMS Data Manipulation SQL Joins (Inner, Left, Right and Full Join) Information Technology Science and Technology Department Ilocos Sur Community College Overview Data Manipula...

DATABASE MANAGEMENT SYSTEMS Data Manipulation SQL Joins (Inner, Left, Right and Full Join) Information Technology Science and Technology Department Ilocos Sur Community College Overview Data Manipulation? Data manipulation in Database Management Systems (DBMS) refers to the Database Management Systems process of retrieving, inserting, updating, and deleting data in a database W h y i t i s i m p o r t a n t? data manipulation in DBMS is essential for managing and leveraging data assets effectively, enabling organizations to make informed decisions, improve efficiency, and drive innovation. Data Retrieval: It allows users to extract specific information from large datasets efficiently. Data Modification: DBMS enables users to add, update, or delete data Data Manipulation as needed. Data Integrity: Proper data manipulation operations help maintain the integrity of the database. Data Security: DBMS often provides mechanisms to control access to data and perform operations based on user privileges. ILOCOS SUR COMMUNITY COLLEGE Overview W h y i t i s i m p o r t a n t? Data Analysis: By manipulating data within the database, users can Database Management Systems perform various analytical tasks, such as aggregating data, calculating statistics, and identifying patterns. Data Maintenance: Data manipulation operations are essential for maintaining the quality of data over time. Data Integration: In modern applications, data is often distributed across multiple databases or systems. Data Manipulation ILOCOS SUR COMMUNITY COLLEGE SQL Joins (Inner, Left, Right and Full Join) SQL Join operation combines data or rows from two or more tables based on a common field between them. Database Management Systems SQL JOIN SQL JOIN clause is used to query and access data from multiple tables by establishing logical relationships between them. It can access data from multiple tables simultaneously using common key values shared across different tables. Data Manipulation ILOCOS SUR COMMUNITY COLLEGE SQL JOIN Example Consider the two tables below as follows: -- Create the Student table -- Insert sample data into the Student Database Management Systems table CREATE TABLE Student ( INSERT INTO Student (StudentID, StudentID INT PRIMARY KEY, FirstName, LastName, Age) FirstName VARCHAR(50), VALUES (1, 'John', 'Doe', 20), LastName VARCHAR(50), (2, 'Jane', 'Smith', 22), Age INT (3, 'Michael', 'Johnson', 21), ); (4, 'Emily', 'Brown', 23); -- Insert sample data into the -- Create the StudentCourse table StudentCourse table CREATE TABLE StudentCourse ( INSERT INTO StudentCourse EnrollmentID INT PRIMARY KEY, (EnrollmentID, StudentID, CourseID, Data Manipulation Grade) StudentID INT, VALUES CourseID INT, (1, 1, 101, 'A'), Grade VARCHAR(2) (2, 2, 102, 'B'), ); (3, 3, 103, 'C'), (4, 4, 101, 'B'); ILOCOS SUR COMMUNITY COLLEGE SQL JOIN Example We can perform a JOIN operation using the given SQL query: SELECT s.FirstName, s.LastName, s.Age, sc.Grade Database Management Systems FROM Student s INNER JOIN StudentCourse sc ON s.StudentID = sc.StudentID; Output: Data Manipulation ILOCOS SUR COMMUNITY COLLEGE Types of JOIN in SQL We can perform a JOIN operation using the given SQL query: There are many types of Joins in SQL. Depending on the use case, you can Database Management Systems use different type of SQL JOIN clause. Here are the frequently used SQL JOIN types: 1. INNER JOIN 2. LEFT JOIN 3. RIGHT JOIN 4. FULL JOIN 5. NATURAL JOIN Data Manipulation ILOCOS SUR COMMUNITY COLLEGE SQL INNER JOIN The INNER JOIN keyword selects all rows from both the tables as long as the condition is satisfied. This keyword will create the result-set by combining all Database Management Systems rows from both the tables where the condition satisfies i.e value of the common field will be the same. Syntax: SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column; Data Manipulation ILOCOS SUR COMMUNITY COLLEGE INNER JOIN Example Let’s look at the example of INNER JOIN clause, and understand it’s working. This query will show SELECT s.FirstName, s.LastName, s.Age, sc.CourseID Database Management Systems the names and age FROM Student s of students enrolled INNER JOIN StudentCourse sc ON s.StudentID = in different courses. sc.StudentID; Output: Data Manipulation ILOCOS SUR COMMUNITY COLLEGE SQL LEFT JOIN LEFT JOIN returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join. For the rows for which there is no matching row on the right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN. Database Management Systems SELECT table1.column1,table1.column2,table2.column1,.... Syntax FROM table1 LEFT JOIN table2 ON table1.matching_column = table2.matching_column; Explanation 1. table1: First table. 2. table2: Second table Data Manipulation 3. matching_column: Column common to both the tables. ILOCOS SUR COMMUNITY COLLEGE SQL LEFT JOIN SQL query: SELECT s.FirstName, s.LastName, s.Age, sc.CourseID, sc.Grade FROM Student s Database Management Systems LEFT JOIN StudentCourse sc ON s.StudentID = sc.StudentID; Output: Data Manipulation Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are the same. ILOCOS SUR COMMUNITY COLLEGE SQL RIGHT JOIN | EXAMPLE SQL query: SELECT s.FirstName, s.LastName, s.Age, sc.CourseID, sc.Grade FROM StudentCourse sc Database Management Systems RIGHT JOIN Student s ON sc.StudentID = s.StudentID; Output: Data Manipulation Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are the same. ILOCOS SUR COMMUNITY COLLEGE SQL FULL JOIN FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain NULL values. Database Management Systems Syntax SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 FULL JOIN table2 ON table1.matching_column = table2.matching_column; explanation Data Manipulation 1. table1: First table. 2. table2: Second table 3. matching_column: Column common to both the tables. ILOCOS SUR COMMUNITY COLLEGE SQL FULL JOIN | EXAMPLE FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain NULL values. Database Management Systems Example SELECT s.FirstName, s.LastName, s.Age, sc.CourseID, sc.Grade FROM Student s FULL JOIN StudentCourse sc ON s.StudentID = sc.StudentID; explanation Data Manipulation 1. table1: First table. 2. table2: Second table 3. matching_column: Column common to both the tables. ILOCOS SUR COMMUNITY COLLEGE SQL FULL JOIN | EXAMPLE Example SELECT s.FirstName, s.LastName, s.Age, sc.CourseID, sc.Grade Database Management Systems FROM Student s LEFT JOIN StudentCourse sc ON s.StudentID = sc.StudentID UNION SELECT s.FirstName, s.LastName, s.Age, sc.CourseID, sc.Grade FROM Student s RIGHT JOIN StudentCourse sc ON s.StudentID = sc.StudentID; Data Manipulation ILOCOS SUR COMMUNITY COLLEGE