ISYS 4283 UARK Harmon Final Flashcards
(20 cards)
Data Definition Language (DDL)
Commands that define a database, including creating, altering, and dropping tables and establishing constraints
Data Manipulation Language (DML)
Commands that are used for updating, inserting, modifying, and querying the data
Data Control Language (DCL)
Commands that control a database, including administering privileges and committing data
Join
A relational operation that causes two or more tables with a common domain to be combined into a single table or view
Inner Join or Equi-Join
A join in which the joining condition is based on equality between values in the common columns; common columns appear redundantly in the result table (INNER JOIN)
Natural Join
An equi-join in which one of the duplicate columns is eliminated in the result table
Join Info (Know this)
The common columns in joined tables are usually the primary key of the dominant table and the foreign key of the dependent table in 1:M relationships
Outer Join
A join in which rows that do not have matching values in common columns are nonetheless included in the result table (as opposed to inner join, in which rows must have matching values in order to appear in the result table)
Subquery
Placing an inner query (SELECT statement) inside an outer query
Options for Subquery
- In a condition of the WHERE clause
- As a “table” of the FROM clause
- In the HAVING clause
When to use join vs subquery
Join technique only-data from several relations are the be retrieved and displayed. Subquery only allows you to display data from tables in outer query.
Subquery Example: Show all customers who have placed an order
SELECT CustomerName
FROM Customer_T
Where CustomerID IN
(SELECT DISTINCT CustomerID FROM ORDER_T);
IN Clause
Allows you to specify multiple values in a WHERE clause. Shorthand for multiple OR conditions
Update Clause
Modifies data in existing rows
Insert Clause
Adds one or more rows to a table
Clauses of the SELECT statement
Select
FROM
WHERE
ORDER BY
WHERE clause
Indicate the conditions under which a row will be included in the result
ORDER BY
Sort the result according to specified criteria
DESC- Descending (Default is ascending)
FROM
Indicates the table from which data will be obtained
Order of Clauses of the SELECT statement
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
