Chapter 3
What can be done using the CONSTRAINT phrase?
-Create a single attribute primary key. -Define a foreign key. -Establish a referential integrity constraint. -Defining a name for the constraint.
What's true about primary keys?
-Primary keys cannot be null. -Primary keys must be unique. -Primary keys are used to represent relationships. -Primary keys can be defined using an SQL CONSTRAINT phrase.
DROP command
A complete table, including both the table structure and any data in the table, can be removed from the database
Two methods for defining a primary key using SQL
A primary key may be defined by either the CREATE TABLE column description method or the CONSTRAINT phrase method. To create a primary key using the CREATE TABLE column description method, the primary key must be a single attribute. For the primary key attribute, the column property is specified to be PRIMARY KEY. There can be only one attribute specified as PRIMARY KEY with the CREATE TABLE column description method. This means that the creation of a composite key requires the use of the CONSTRAINT phrase method. Defining a primary key using the CONSTRAINT phrase requires that the column or columns that will serve as the primary key must have been defined as NOT NULL. Using the CONSTRAINT phrase method, the primary key is defined by adding a constraint to the table using a CONSTRAINT phrase after the table columns have been defined. The CONSTRAINT phrase names the column or columns that will be the table's primary key. The structure of the CONSTRAINT phrase is: CONSTRAINT ConstraintName PRIMARY KEY (column names)
A composite primary key can be defined using the CONSTRAINT phrase in which SQL command?
CREATE TABLE
Explain the essential format of the CREATE TABLE statement
CREATE TABLE tablename ( column-description, column-description, column-description, . . . optional table constraints ); "Tablename" is the name that will be given to the newly created table. "Column-description" is a three-part description of each column to appear in the table. This description includes the name of the column, the column's data type, and an optional column constraint (either Primary Key, Null, or Not Null), in that order. The CONSTRAINT phrase can be used to set optional primary key, foreign key and referential integrity constraints for the table. All SQL statements must end with a semi-colon (;).
Conditions after the WHERE keyword require single quotes around the values for columns that have which data type?
Char
Distinguish between Char and VarChar data types
Char data type is fixed length, so that no matter what the actual length of the data entered is, it will always take exactly the same storage space. For example, Char(10) indicates that 10 characters will always be stored for each value of that column. If the actual data entered is less than the specified fixed length, the data will be padded with blanks. VarChar data type is variable length so that only the amount of space actually needed to store the data is used. Although VarChar may be more efficient in its use of space, it is not always preferred. VarChar requires the storage of some extra data to indicate the length of the data values, plus it requires some extra processing by the DBMS to arrange the variable length data.
Standard data type used in SQL
Char, Varchar, Integer, Numeric
Discuss SQL data types
Common examples of standard SQL data types are Char, VarChar, Integer, and Numeric. The Char data type is for fixed-length character data. VarChar is for variable-length character data. Integer is for numeric data that are whole numbers only. Numeric is for numeric data that may include decimals. Char, VarChar, and Numeric must be qualified by a length specification to indicate the amount of storage space to be allocated for each data item. For example, Char(10) indicates fixed-length character data that is always stored as 10 characters.
The values of existing data can be removed from a table using the SQL ________ command, which can even be used to remove all the data in the table at one time.
DELETE
Which SQL command would be used to remove only the data from a table named STUDENT while leaving the table structure intact?
DELETE FROM STUDENT;
Which SQL command would be used to remove both the data and the table structure of a table named STUDENT?
DROP TABLE STUDENT;
Built-in SQL functions cannot be applied to data combined using the GROUP BY keyword
FALSE
Data from a maximum of three tables can be combined through the use of subqueries in SQL.
FALSE
For a column to be defined as the primary key using table constraints, the column must have been given the property NULL.
FALSE
In SQL, the WHERE clause is used to specify which columns will be included in the result
FALSE
In SQL, the order of the rows that result from a SELECT statement can be set using the SORT BY phrase.
FALSE
In SQL, to refer to a range of values in a WHERE clause, use the WITHIN keyword.
FALSE
Microsoft Access SQL supports and will correctly implement the numeric data type with (m,n) notation.
FALSE
Microsoft Access can run QBE queries, but not SQL queries.
FALSE
The ON DELETE CASCADE referential integrity constraint does not apply when rows are deleted using the SQL DELETE command.
FALSE
The TOP built-in function in SQL is used to find the maximum value in a numeric column.
FALSE
The result for SELECT statements in SQL is a relation unless the result is a single number.
FALSE
Values of existing data can be changed using SQL through the CHANGE command.
FALSE
When using SQL to create a table, a column is defined by declaring, in this order: data type, column name, and optional constraints.
FALSE
When using SQL to create a table, specifying the NULL property for a column indicates that only null values may be stored in that column.
FALSE
Given the table STUDENT(StudentID, Name, Advisor), which SQL statement would be used to add new student data to the STUDENT table?
INSERT INTO STUDENT VALUES (123, 'Jones', 'Smith');
Which SQL keyword can be used in conjunction with wildcards to select partial values?
LIKE
Why it is important to learn SQL
Most modern DBMS products support SQL as a standardized data language. These products usually provide graphical tools to perform the tasks associated with SQL, but there are some tasks that cannot be performed using these graphical tools. SQL is text-oriented, and SQL code must be written in order to embed SQL commands within program applications.
Which of the following standard SQL data types is not supported in Microsoft Access SQL?
Numeric(5,3)
What is the correct SQL clause to sort the results of a SELECT query in reverse-alphabetic order using the Department field?
ORDER BY Department DESC
Which type of join, although not included in standard SQL, was created to allow unmatched rows to appear in the result of a join operation?
OUTER JOIN
One way to specify all of the columns of a table is to use the special character * after the SQL keyword
SELECT
Given the table CUSTOMER(CustID, Name, PhoneNum, AcctBalance) what is the standard SQL query phrase to retrieve the Name and Phone Number of customers?
SELECT Name, PhoneNum
Given the table CUSTOMER(CustID, Name, PhoneNumber, AccountBalance) write the standard SQL query to retrieve the Name and Phone Number of customers with a balance greater than 50.
SELECT Name, PhoneNumber FROM CUSTOMER WHERE AccountBalance > 50;
Given the table CUSTOMER(CustID, Name, PhoneNumber, AccountBalance) write the standard SQL query to retrieve the Name and Phone Number of customers whose name begins with 'S'.
SELECT Name, PhoneNumber FROM CUSTOMER WHERE Name LIKE 'S%';
What are SQL Built-in Functions?
SQL Built-in Functions are functions that manipulate the results of an SQL SELECT statement. The built-in functions for standard SQL are COUNT, SUM, AVG, MAX, and MIN. The COUNT function counts the number of rows in the result. The SUM function totals the values in a number-oriented field. The AVG function calculates the mean of the values in a number-oriented field. The MAX function determines the highest value, and the MIN function determines the lowest value, in a number-oriented field.
Microsoft Access SQL commands are run in the ________ of a Query window.
SQL View
HAVING
SQL keyword used to apply conditions to restrict groups that appear in the results of a SELECT query that uses GROUP BY
DISTINCT
SQL keyword used to eliminate duplicate rows in the results of an SQL SELECT query
WHERE
SQL keyword used to specify a condition that rows must meet to be included in the results of an SQL SELECT query
SQL built-in functions
SUM, COUNT, MAX, AVG
In Microsoft Access, tables are added to a QBE Query window by selecting the tables from the ________ dialog box
Show Table
Compare and contrast subqueries and joins
Subqueries and joins are both used to process data for queries that require data from multiple tables. Subqueries involve embedding a second query within the WHERE clause of the first query. Many levels of subqueries can exist. Joins involve combining the rows of multiple tables into a single relation whose rows contain the data from all the tables that are joined. This new relation can then be processed by the other clauses of the SELECT query. One notable issue with subqueries is that only data from a single table may appear in the result that is returned by the query. One notable issue with joins is that any unmatched rows in either table will not appear in the result that is returned by the query
A subquery is appropriate only if the final result contains only data from a single table.
TRUE
Data is added to a table using the SQL INSERT command.
TRUE
If you need to create a primary key that is a composite key using SQL, the key may be defined when the table is created using the CREATE TABLE statement.
TRUE
In Microsoft Access, default values must be set as a field property while the table is in Design View.
TRUE
In SQL, multiple conditions in the WHERE clause can be combined by using the SQL AND keyword.
TRUE
In SQL, the IS NULL keyword can be used to select on records containing NULL values in a particular column.
TRUE
In SQL, the LIKE keyword can be combined with the NOT keyword to form the NOT LIKE condition for selecting values.
TRUE
In SQL, the LIKE keyword can be used to select on partial values
TRUE
In SQL, the NOT keyword can be combined with the IN keyword to form the NOT IN condition for selecting values.
TRUE
Microsoft Access SQL commands are run within the SQL View of a Query window.
TRUE
Referential integrity constraints can be created using the ON DELETE phrase when the table is created using the CREATE TABLE statement.
TRUE
Referential integrity constraints using the ON DELETE NO ACTION phrase may be explicitly stated when the table is created using the CREATE TABLE statement.
TRUE
SQL is a data sublanguage, not a complete programming language.
TRUE
Standard SQL does not allow built-in functions to be used in a WHERE clause
TRUE
The SQL GROUP BY keyword can be used to group rows by common values.
TRUE
The SUM built-in function in SQL is used to total the values in a numeric column
TRUE
The basic idea of a join is to combine the contents of two or more relations into a new relation.
TRUE
To force the DBMS to remove duplicate rows from the results of an SQL SELECT query, the keyword DISTINCT must be used.
TRUE
To open a new Microsoft Access Query window, click the Query Design button on the Create command tab.
TRUE
Using standard SQL, unmatched rows will not appear in the result of a join.
TRUE
When using SQL to create a table, specifying a data type of Char(10) indicates a fixed length field of 10 characters.
TRUE
Distinguish between the HAVING clause and the WHERE clause
The HAVING clause and the WHERE clause differ in that the WHERE clause is used to identify rows that satisfy a stated condition. The HAVING clause is used to identify groups which have been created by the GROUP BY clause and that satisfy a stated condition. In cases when the WHERE clause and the HAVING clause are both allowed to appear in the same SELECT statement, the WHERE clause is generally implemented to execute before the HAVING clause.
INSERT
The SQL command used to add new data to a table
How to run an SQL query in Microsoft Access
To run an SQL query in Microsoft Access, start on the Create command tab. Click the Query Design button, which will display a Query window together with the Show Tables dialog box. Close the Show Tables dialog box by clicking the Close button, and then click the SQL View button on the Design command tab to switch the Query window to SQL View. An SQL statement can now be entered into the Query window. To execute the query after the SQL statement is complete, click the Run button on the Design command tab.
Given the table STUDENT(StudentID, Name, Advisor), what SQL statement would be used to change the value of the Advisor field to 'Smith' for all rows in the STUDENT table?
UPDATE STUDENT SET Advisor = 'Smith';
The values of existing data can be modified using the SQL ________ command, which can be used to change several column values at once.
UPDATE...SET
An SQL data type of ________ would indicate a variable-length character string with maximum length 75.
VarChar(75)
Conditions in an SQL INSERT command and after the SQL ________ keyword require single quotes around values for Char and VarChar columns, but not around values for Integer and Numeric columns.
WHERE
Given the table CUSTOMER(CustID, Name, PhoneNum, AcctBalance) what is the standard SQL query phrase to retrieve data for customers with an account balance greater than 50?
WHERE AcctBalance > 50
What is the correct SQL clause to restrict the results of a SELECT query to only records that have a value in the range of 10 to 50 in the Hours column?
WHERE Hours BETWEEN 10 AND 50
Given the tables STUDENT(StudentID, StudentName, AdvisorID) ADVISOR(AdvisorID, AdvisorName, Office, Phone) which SQL statement would be used to implant a join between the two tables?
WHERE STUDENT JOIN ADVISOR ON STUDENT.AdvisorID = ADVISOR.AdvisorID
Which of the following data types used in SQL would define a fixed-length text field of 10 characters?
char(10)
To open a new Microsoft Access Query window
click the Query Design button on the Command tab
SQL is not a complete programming language, but is rather a(n) ________.
data sublanguage
IBM
developed SQL in the late 1970s.
SELECT
extracts data from a database
Using SQL commands, multiple tables can be queried using either subqueries or ________.
joins
Table and relationship properties not supported by Microsoft Access SQL can usually be set ________ in the appropriate window or dialog box.
manually
OUTER JOIN command
multiple tables can be queried so that all the values from one table appear in the results, regardless of whether they have one or more corresponding values in the other table
Which of the following data types used in SQL would define a numeric field of the pattern 99.99?
numeric(4,2)
The order of the columns returned by an SQL SELECT statement is determined by the________
order they are listed in following SELECT
SQL TRUNCATE TABLE statement
removes all data from a table while leaving the table structure, and also resets any surrogate primary key values back to the starting point
In Microsoft Access, tables are added to a Query window by
selecting the tables from the Show Table dialog box
In Microsoft Access, standard SQL table creation syntax items not supported by Access SQL can generally be implemented by ________.
setting the field properties in Access table Design View
SQL SELECT clause
specifies which columns are to be listed in the query results
SQL WHERE clause
specifies which rows are to be listed in the query results
SQL FROM clause
specifies which tables are to be used in the query
% (percent sign)
symbol used in standard SQL as a wildcard to represent a series of one or more unspecified characters
_ (underscore)
symbol used in standard SQL as a wildcard to represent a single, unspecified character
ALTER TABLE command
tables in a database can be structurally modified
Microsoft Access SQL commands are run in
the SQL View of a Query window
NOT NULL
the SQL keyword that means a value must be supplied before a new row can be created.
Query By Example (QBE)
the default method of creating and running a query in Microsoft Access
UPDATE
updates data in a database
Numeric(7,2)
values consist of seven decimal numbers with two numbers assumed to the right of the decimal point.
