IT 2105 Mathematics for Computing I Bachelor of

IT 2105 Mathematics for Computing I Bachelor of Information Technology Database Systems I IT 2305 04. Data Manipulation using SQL 4. 3 Selecting Data - Part 6 4. 3. 3 Multiple Table U J Prashad Srilal BSc (Hons) in Business IT

IT 2305_Database Systems I Intended Learning Outcomes After completing this module students should be able to; • Use SQL to Retrieve data from multiple tables

IT 2305_Database Systems I Outline Aliases for table names, Multiple Table Joins, Inner Join, Left outer join.

IT 2305_Database Systems I SQL Aliases • SQL aliases are used to give a table, or a column in a table, a temporary name. • Aliases are often used to make column names more readable. • An alias only exists for the duration of the query.

IT 2305_Database Systems I SQL Aliases Alias Column Syntax SELECT column_name AS alias_name FROM table_name;

IT 2305_Database Systems I SQL Aliases Alias Table Syntax SELECT column_name(s) FROM table_name AS alias_name;

IT 2305_Database Systems I Customer. N Contact. N Customer. ID Address ame City Postal. Code Country 2 Ana Trujillo Ana Emparedad Trujillo os y helados Avda. de la Constitución 2222 México D. F. 05021 Mexico 3 Antonio Moreno Taquería Mataderos 2312 México D. F. 05023 Mexico 4 Around the Thomas Horn Hardy 120 Hanover Sq. London WA 1 1 DP UK Antonio Moreno

IT 2305_Database Systems I Order. ID Customer. ID Employee. ID Order. Date Shipper. ID 10354 58 8 1996 -11 -14 3 10355 4 6 1996 -11 -15 1 10356 86 6 1996 -11 -18 2

IT 2305_Database Systems I Alias for Columns Examples The following SQL statement creates two aliases, one for the Customer. ID column and one for the Customer. Name column: Example SELECT Customer. ID as ID, Customer. Name AS Customer FROM Customers;

IT 2305_Database Systems I Alias for Columns Examples The following SQL statement creates two aliases, one for the Customer. Name column and one for the Contact. Name column. Note: It requires double quotation marks or square brackets if the alias name contains spaces: Example SELECT Customer. Name AS Customer, Contact. Name AS [Contact Person] FROM Customers;

IT 2305_Database Systems I Alias for Table Examples The following SQL statement selects all the orders from the customer with Customer. ID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter): Example SELECT o. Order. ID, o. Order. Date, c. Customer. Name FROM Customers AS c, Orders AS o WHERE c. Customer. Name="Around the Horn" AND c. Customer. ID=o. Customer. ID;

IT 2305_Database Systems I Alias for Table Examples The following SQL statement is the same as above, but without aliases: Example SELECT Orders. Order. ID, Orders. Order. Date, Customers. Customer. Name FROM Customers, Orders WHERE Customers. Customer. Name="Around the Horn" AND Customers. Customer. ID=Orders. Customer. ID;

IT 2305_Database Systems I Aliases can be useful when: • There are more than one table involved in a query • Functions are used in the query • Column names are big or not very readable • Two or more columns are combined together

IT 2305_Database Systems I SQL Joins • A JOIN clause is used to combine rows from two or more tables, based on a related column between them. • The SQL JOIN is used to get data from multiple tables. These tables must be related to each other via some columns. • Though it is not necessary, usually these tables are related through primary key. • That is why those databases are called Relational Databases (RDBMS).

IT 2305_Database Systems I SQL Joins Type (INNER) JOIN: Returns records that have matching values in both tables LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

IT 2305_Database Systems I Question movie( m. ID, title, year, director ) - There is a movie with ID number m. ID, a title, a release year, and a director. reviewer( r. ID, name ) - The reviewer with ID number r. ID has a certain name. rating(r. ID, m. ID, stars, rating. Date ) - The reviewer r. ID gave the movie m. ID a number of stars rating (1 -5) on a certain rating. Date.

IT 2305_Database Systems I Question Which of the following SQL statements would find all years that have a movie that received a rating of 4 or 5 in increasing order?

IT 2305_Database Systems I (a) SELECT mov. year FROM movie AS mov, rating AS rat WHERE mov. m. ID = rat. m. ID AND (stars = 4 OR stars = 5) ORDER BY year; (b) SELECT DISTINCT mov. year FROM movie AS mov, rating AS rat WHERE mov. m. ID = rat. m. ID AND (stars = 4 OR stars = 5) ORDER BY year ASC; (c) SELECT DISTINCT mov. year FROM movie AS mov, rating AS rat WHERE mov. m. ID = rat. m. ID AND (stars = 4 OR stars = 5) ORDER BY year; (d) SELECT UNIQUE (mov. year) FROM movie AS mov, rating AS rat WHERE mov. m. ID = rat. m. ID AND (stars = 4 OR stars = 5) ORDER BY year ASC; (e) SELECT mov. year FROM movie AS mov, rating AS rat WHERE mov. m. ID = rat. m. ID AND (stars = 4 OR stars = 5) ORDER BY UNIQUE (year) ASC;

IT 2305_Database Systems I Orders table Order. ID Customer. ID Order. Date 10308 2 1996 -09 -18 10309 37 1996 -09 -19 10310 77 1996 -

IT 2305_Database Systems I Customers table Customer. ID Customer. Name Contact. Name Country 1 Alfreds Futterkiste Maria Anders Germany 2 Ana Trujillo Emparedados y helados Mexico 3 Antonio Moreno Taquería Mexico Antonio Moreno

IT 2305_Database Systems I Inner Join • The SQL INNER JOIN statement is used to get data from multiple tables in the database. • INNER JOIN will return the data from both tables if there is at least one match. • INNER JOIN = JOIN

IT 2305_Database Systems I Inner Join

IT 2305_Database Systems I INNER JOIN Syntax SELECT column_name(s) FROM table 1 INNER JOIN table 2 ON table 1. column_name = table 2. column_name;

IT 2305_Database Systems I Inner Join The "Customer. ID" column in the "Orders" table refers to the "Customer. ID" in the "Customers" table. The relationship between the two tables above is the "Customer. ID" column. Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

IT 2305_Database Systems I Example SELECT Orders. Order. ID, Customers. Customer. Name, Orders. Order. Date FROM Orders INNER JOIN Customers ON Orders. Customer. ID=Customers. Cust omer. ID;

IT 2305_Database Systems I Result set Order. ID Customer. Name Order. Date 10308 Ana Trujillo Emparedados 9/18/1996 y helados

IT 2305_Database Systems I Join Three Tables SELECT Orders. Order. ID, Customers. Customer. Name, Shippers. Shipper. Name FROM ((Orders INNER JOIN Customer ON Orders. Customer. ID= Customers. Customer. ID) INNER JOIN Shippers ON Orders. Shipper. ID= Shippers. Shipper. ID;

IT 2305_Database Systems I Join Three Tables To select all orders with customer and shipper information.

IT 2305_Database Systems I SQL LEFT JOIN Keyword • The LEFT JOIN keyword returns all records from the left table (table 1), and the matched records from the right table (table 2). The result is NULL from the right side, if there is no match. • In Some databases LEFT JOIN known as LEFT OUTER JOIN

IT 2305_Database Systems I SQL LEFT JOIN Keyword

IT 2305_Database Systems I SQL LEFT JOIN Keyword LEFT JOIN Syntax SELECT column_name(s) FROM table 1 LEFT JOIN table 2 ON table 1. column_name = table 2. column_name;

IT 2305_Database Systems I SQL LEFT JOIN Example The following SQL statement will select all customers, and any orders they might have: Example SELECT Customers. Customer. Name, Orders. Order. ID FROM Customers LEFT JOIN Orders ON Customers. Customer. ID = Orders. Customer. ID ORDER BY Customers. Customer. Name;

IT 2305_Database Systems I SQL LEFT JOIN Example Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

IT 2305_Database Systems I Question Lecturer (Emp. No, Name, Gender, Salary, Category, DNo). Department (DNo, Dname, Head. Emp. No) Course(CNo, Cname, Credits, DNo) Deliver(Emp. No, CNo, Hours) Research_Fund(RFName, Emp. No, Budget) Primary Keys are underlined and Foreign Keys are in italics. Lecturers can teach courses offered by other departments as well.

IT 2305_Database Systems I Question Which SQL statement would display the Emp. No and Names of all lecturers along with the total sum of the research funds (Total. Fund) if a lecturer controls some research fund (otherwise Total. Fund would be NULL)?

IT 2305_Database Systems I (a) SELECT L. Emp. No, L. Name, SUM(F. Budget) AS Total. Fund FROM Lecturer AS L RIGHT OUTER JOIN Research_Fund AS F ON L. Emp. No = F. Emp. No GROUP BY L. Emp. No, L. Name; (b) SELECT L. Emp. No, L. Name, SUM(F. Budget) AS Total. Fund FROM Lecturer AS L LEFT OUTER JOIN Research_Fund AS F ON L. Emp. No = F. Emp. No GROUP BY L. Emp. No, L. Name; (c) SELECT L. Emp. No, L. Name, F. Total. Fund FROM Lecturer AS L LEFT OUTER JOIN (SELECT Emp. No, SUM(Budget) AS Total. Fund FROM Research_Fund GROUP BY Emp. No) AS F ON L. Emp. No = F. Emp. No;

IT 2305_Database Systems I (d) SELECT L. Emp. No, L. Name, SUM(F. Budget) AS Total. Fund FROM Lecturer AS L, Research_Fund AS F WHERE L. Emp. No = F. Emp. No GROUP BY L. Emp. No, L. Name; (e) SELECT L. Emp. No, L. Name, F. Total. Fund FROM Lecturer AS L RIGHT OUTER JOIN (SELECT Emp. No, SUM(Budget) AS Total. Fund FROM Research_Fund) AS F ON L. Emp. No = F. Emp. No GROUP BY L. Emp. No;

IT 2305_Database Systems I Answer (b) SELECT L. Emp. No, L. Name, SUM(F. Budget) AS Total. Fund FROM Lecturer AS L LEFT OUTER JOIN Research_Fund AS F ON L. Emp. No = F. Emp. No GROUP BY L. Emp. No, L. Name; (c) SELECT L. Emp. No, L. Name, F. Total. Fund FROM Lecturer AS L LEFT OUTER JOIN (SELECT Emp. No, SUM(Budget) AS Total. Fund FROM Research_Fund GROUP BY Emp. No) AS F ON L. Emp. No = F. Emp. No;

IT 2305_Database Systems I Lesson Summary Aliases for table names, Multiple Table Joins, Inner Join, Left outer join.

IT 2105 Mathematics for Computing I Bachelor of Information Technology Database Systems I IT 2305 04. Data Manipulation using SQL 4. 3 Selecting Data - Part 6 4. 3. 3 Multiple Table U J Prashad Srilal BSc (Hons) in Business IT