Upgrade to remove ads
Only $35.99/year

(K&A) Chapter 3

Terms in this set (77)

/ and /
SQL comment symbols
American National Standards Institute (ANSI)
Endorsed SQL in 1986, 1989, and 1992. IBM developed SQL
AND keyword
Means that only rows meeting all the conditions will be selected (p.148)
ASC keyword
Ascending order for ORDER BY
AS keyword
Used to assign name or alias
asterisk (*)
Microsoft Access uses this to represent multiple characters instead of asterisk (*)
AVG
...
business rule
...
CASCADE keyword
Means that the same change in one table should be made to the related columns in other related tables
CHECK constraint
ALTER TABLE PROJECT
ADD CONSTRAINT PROJECT_Check_Dates
CHECK (StartDate < EndDate);
comparison operators
>, <, =, >=, <=, ><
CONSTRAINT keyword
Can be used to implement various constraints
COUNT
...
data control language (DCL)
Used to grant database permissions (or to revoke those permissions) to users and groups, so that the users or groups can perform various operations on the data in the database
data definition language (DDL)
Used for creating tables, relationship, and other structures
data manipulation language (DML)
Used for querying, inserting, modifying, and deleting data. One component is SQL view (used to create predefined queries)
data sublanguage
SQL is not a language. It's a _____________
DEFAULT keyword
Not considered as a column constraint
DESC keyword
Descending order for ORBER BY
DISTINCT keyword
Use this if you want DBMS to check for and eliminate duplicate rows
Extensible Markup Language (XML)
One of the newer features of SQL. SQL supports this language
FOREIGN KEY constraint
...
graphical user interface (GUI)
...
GROUP BY clause
Used to group rows by common values
HAVING clause
Used to apply conditions to the groups that are formed

e.g. SELECT Department, Count (*) AS NumberOfEmployee
FROM EMPLOYEE
GROUP BY Department
HAVING COUNT(*) > 1;
IDENTITY (M,N) property
How Microsoft Access defines surrogate keys
IN keyword
Another use of the WHERE clause is to specify that a column have one set values by using this keyword (p.150)
International Organization for Standardization (ISO)
SQL has also been endorsed by this organization (other than ANSI)
IS NOT NULL phase
The opposite of IS NULL phrase
IS NULL keyword
Can be used in a WHERE clause to search for null values
LIKE keyword
Used to select partial values. It's used with wildcard characters (aka '_')
MAX
e.g. MAX(MaxHours) AS MaxmumHours
MIN
e.g. MIN(MinHours) AS MinmumHours
NO ACTION keyword
No action should be taken if a value of the primary key changes
NOT IN phrase
SELECT FirstName, LastName, Phone, Department
FROM EMPLOYEE
WHERE Department NOT IN ('Accounting', 'Finance', 'Marketing')
(Same example as 'NO keyword') (p.150)
NOT keyword
SELECT FirstName, LastName, Phone, Department
FROM EMPLOYEE
WHERE Department NOT IN ('Accounting', 'Finance', 'Marketing') (p.150)
NOT LIKE phrase
The opposite of LIKE
NOT NULL constraint
...
NULL constraint
...
ON DELETE phrase
Shows that action should be taken if a row is deleted
ON UPDATE phrase
Shows that action should be taken if a value of the primary key changes
OR keyword
Means that rows that meet any of the conditions will be selected (p.149)
ORDER BY clause
Used to sort the rows
percent sign (%)
Wildcard characters. Used to represent a series of one or more unspecified characters (p.151)
PRIMARY KEY constraint
...
Query by Example (QBE)
Microsoft Access uses SQL but hides it behind the scene
question mark (?)
Microsoft Access uses this to represent single character instead of an underscore
SQL ALTER TABLE statement
Used to add, modify, and drop columns and constraints

ALTER TABLE ASSGINMENT DROP CONSTRAINT ASSIGN_EMP_FK;
SQL asterisk (*) wildcard operator
Another way to specify all the columns of a table (p.148)
SQL built-in functions
Used for calculations, including COUNT, SUM, AVG, MAX, MIN
SQL comment
A block of text that is used to document the SQL statements but not executed as part of the SQL statement
SQL CREATE TABLE statement
Used to create tables
SQL DELETE statement
Be aware of the ON DELETE CASCADE

DELETE
FROM EMPLOYEE
WHERE EmployeeNumber = 1;
SQL DROP TABLE statement
DROP TABLE ASSIGNMENT (drop the entire table including index and formatting)
SQL equijoin
Another name for inner join
SQL FROM clause
Specifies which tables are to be used in the query
SQL inner join
Only displays data from the rows that match based on join conditions, and thus data could be lost
SQL INSERT statement
Adds data to a relation
SQL JOIN keyword
Can replace the comma in the FROM clause (p.169)
SQL join operation
To form a new relation by connecting the contents of two or more other relations
SQL JOIN ON syntax
SELECT FirstName, LastName, HoursWorked
FROM EMPLOYEE JOIN ASSIGNMENT
ON EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber
ORDER BY EMPLOYEE.EmployeeNumber, ProjectID
SQL left outer join
LEFT JOIN EMPLOYEE AS E
ON ...........
SQL MERGE statement
Combine INSERT and UPDATE statements into one, but requires more complex code
SQL ON keyword
Can replace WHERE clause. Used with JOIN keyword (p.169)
SQL outer join
...
SQL right outer join
RIGHT JOIN EMPLOYEE AS E
ON ...........
SQL SELECT clause
Specifies which columns are to be listed in the query results
SQL SELECT/FROM/WHERE framework
SQL SELECT, SQL FROM, SQL WHERE clauses
WHERE can't be used with built-in functions. Use GROUP BY .... HAVING ..... instead
SQL TRUNCATE TABLE statement
Same as DELETE: remove data but not table structure. But also resets any primary or foreign key value back to the starting point. Doesn't need WHERE to specify conditions since the data in a table will always be removed

TRUNCATE TABLE PROJECT;
SQL UPDATE...SET statement
e.g.
UPDATE EMPLOYEE
SET Phone = '202-431-9480'
WHERE EmployeeNumber = 11;
SQL view
A virtual table created by a DBMS-stored SELECT statement and thus can combines access to data in multiple tables and even in other views
SQL WHERE clause
Specifies which rows are to be listed in the query results
SQL/Persistent stored modules (SQL/PSM)
Extend SQL by adding procedural programming capabilities, such as variables and flow-of-control statements, that provide some programmability within the SQL framework
transaction control language (TLC)
used to mark transactions boundaries and control transaction behavior
underscore symbol (_)
Wildcard characters, which represent unknown characters in a pattern. An underscore symbol represents a single, unspecified character (p.151)
UNIQUE constraint
...
wildcard characters
...
Other sets by this creator