Database Domain #3 Lesson 1 to 3

University:
American Baptist College
Course:
CIT370 | Database Design and Processing
Academic year:

2024
Views:

18

Pages:

2
Author:

Muthia M.

Database Domain 3 Lesson 1 to 4 Join Types To build a query that retrieves information from two or more tables, one should use a JOIN statement. An inner join is a join in which records will only display if the value of the join field is in both tables. A left outer join shows all records from the first table in a join plus matching records from the second table. A right outer join shows all records from the second table in a join plus matching records from the first table. A full outer join will show all records from both tables. If a join is not specified, a cartesian product will show how every record in one table is related to every record in another table. A self-join is a join between two columns in the same table, usually when a table has a hierarchical structure. Unions, Intersects, And Distinct The UNION keyword combines two or more tables into one dataset. It is important to line the columns from each table up when using the UNION keyword so that one set of records stacks upon the other. There will likely be duplicates among individual columns. The INTERSECT keyword creates a list of matching records between tables based on the specified fields in a query. To be an intersect, each column in the query must have identical values in both tables. The DISTINCT keyword ensures that all records in a dataset are unique. Only values with a distinct occurrence from the specified columns will be in the new dataset. Column Alias and Computed Columns Column names in databases are often named in a way that is too technical for the average database user. Aliases are a way to substitute column names in a query. Column aliasing offers a shortcut for using joins and clearing ambiguities in columns used in a query. When creating a report, one may want to show data in a calculated form. Using computed columns, one can format the numbers to display correct data. Order By, Where, And Like Data in queries often need filters to find certain data. There are several clauses that one can use to accomplish this. The ORDER BY clause sorts the information returned from a query. Add the letters DESC at the end of the column name to sort descending order. The WHERE clause creates filters on the data. This clause comes after the tables and fields defined within a query. A LIKE clause added to a WHERE clause allows wildcards to get approxima matches for a set of criteria.