SQL Database Reviewer for Grade 9 PDF

LESSON 11 Extracting Records Using SQL Join Commands Joining Tables A relational database management system (RDBMS) provides a way of joining two or more tables with the use of different Structured Query Language (SQL) Join commands. The JOIN commands are used to combine rows from two or more table...

LESSON 11 Extracting Records Using SQL Join Commands Joining Tables A relational database management system (RDBMS) provides a way of joining two or more tables with the use of different Structured Query Language (SQL) Join commands. The JOIN commands are used to combine rows from two or more tables. It will also enable you to produce exact values based on the conditional parameters added using the WHERE clause. SQL Join A JOIN clause is used to combine rows from two or more tables, based on a related column between them. JOIN conditions can be specified using either FROM or WHERE clauses; specifying them in the FROM clause is recommended. WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the join conditions. JOINS can be categorized as follows: 1. Inner Join - It is the typical join operation, which uses some comparison operator like = or \. Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. 2\. Outer Join- It can be a left, right, or full outer join. They are specified with one of the following sets of keywords when they are specified in the FROM clause: A. Left Join or Left Outer Join The result - set of a left outer join includes all the rows from the left table specified in the left outer clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result-set row contains null values for all select list columns coming from the right table. B. Right Join or Right Outer Join - A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table. C. Full Join or Full Outer Join - A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result-set row contains data values from the base tables. 3\. Cross Join - Cross join returns all rows from the left table; each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products. Cartesian products - multiply the value of one table to joined table. KEY UNDERSTANDING: The key in extracting records from tables with a relationship is the common column that joins them or simply the key. As long as there is a common key in each table, a combine report may be extracted. Joining Tables will enable you to connect from one table to another using a primary key. Joins are categorized as: \(1) inner join and (2) outer join. 1\. Inner Join - uses a comparison operator to match rows from two tables based on the values in common from each table 2\. Outer Join - is specified with one of the following sets of keywords when they are specified in the FROM clause To create a view, right-click the View folder, then select New View. The View design is divided into four panes: (1) diagram, (2) criteria, (3) SQL query, and (4) results. The diagram will enable you to view the column names of each table added in the view; you can also generate commands by connecting one table to another table. The SQL query pane will let you code commands which will automatically generate an SQL command whenever the diagram pane is triggered. LESSON 12 Understanding the Use of SQL Filters Using the WHERE Clause Operators in the WHERE Clause An operator is a symbol or a reserved word specifying an action which evaluates the value expression either true or false and performs arithmetic operations Below are the different kinds of operators that are used in creating expressions on conditions. 1\. Conditional Operators Conditional operators are also called comparison operators. They evaluate whether or not the given expressions yield to a true or false value. Comparison operators can be used in all expressions except for those with text, next, or image data types. 2. Logical Operators Logical operators connect two expressions and test their conditional values. Same with comparison operators, they are also a Boolean data type with a value of true or false. The AND operator was used to display records with grades between 85 and 99. It displayed a record if all the conditions are TRUE. There are two conditions in the statement: Grade \>= 85 and Grade \= 85 is evaluated first. When it evaluates to False, the second condition which grade \ = - means not equal. Between clause - will enable you to filter data within the given range. Conditional operator - evaluates a Boolean expression to either true or false. In clause - will enable you to filter data within the given range. Like clause - compares a value similar to a specified one with the use of a wildcard character. Logical operator - is used to combine two or more expressions. Not in clause - will enable you to filter data not within the given range. Underscore (\_) - denotes a single character in the Like operator used in the Where clause. Where clause - will enable you to filter data based on the given expression.