CIS 204- Chapters 3 & 4
SQL *GRANT* Syntax to *retrieve data but not take any other action*
*GRANT SELECT ON* TableName *TO* User;
Revoke Syntax
*REVOKE SELECT ON* Table_name FROM user
Sorting is accomplished using the _______.
ORDER BY clause
Revoke the ability to retrieve Customer records from user Jones, that is Jones will be no longer have the privilege granted earlier
REVOKE SELECT ON Customer FROM Jones;
allows Access to create a new table in a single operation
SELECT INTO command
Any of the simple conditions must be true for the compound condition to be true
OR operator
If the condition contains the ___, *either or both conditions must be true* for a record to appear in the query results.
OR operator
To *sort the output*, you include the words ___ in the SQL query, followed by the ___.
ORDER BY, Sort key field
Single-field index
an index key with one field (also called a single-column index)
Multiple-field index
an index with more than one key field - also called a multiple-column index - list more important key first
Users access database through
clients
Must enter ___ to obtain the desired results
commands
IN SQL query, you can specify to sort the output in descending (high-to-low) order by
following the sort key with the word *DESC*.
Simple condition
includes a field name, a comparison operator, and either another field name or value
Computed fields are specified in SQL queries by:
including the expression, followed by the word AS, followed by the name of the computed field
What does this syntax *ALTER TABLE table_name* execute to?
indicates the name of the table to be altered
Alteration clause
indicates the type of alteration to be performed
Between operator - definition
indicates the value must be *between* the listed numbers
Sys*indexes*
information about *indexes* that are defined on these tables
Sys*views*
information about *views* that have been created
Sys*columns*
information about the *columns* or fields within these tables
Sys*tables*
information about the *tables* known to SQL
The less important field is called the ___.
minor sort key or secondary sort key
When is a subquery executed?
The subquery (which is the inner query) is evaluated first, producing a list of item numbers. After the subquery has been evaluated, the outer query can be evaluated.
Application program's or individual user's picture of the database. (smaller version of a database - given access to see)
Views (are used to give each user his or her own view of the data in the database.)
When the data is sorted in more than one field, the more important field is called:
the major sort key or primary sort key
1) How do you enclose a character field, for example CustomerNum or CustomerName? 2) Example: Find the number of customer 126.
to which the field is being compared in single quotation marks. WHERE CustomerNum ='126'
Security is provided in SQL systems by what commands?
using the GRANT and REVOKE commands
stores information about the structure of a database
system catalog
command to remove the index
DROP INDEX
is used to make changes to existing data
Update command
What are the comparison operators and the meanings?
= (equal to) < (less than) > (greater than) <= (less than or equal to) >= (greater than or equal to) <> (not equal to, *Access uses this one!*) != (not equal to)
*List the complete Item table* Item (ItemNum, Description, OnHand, Category, Storehouse, Price)
Select * FROM Item;
SQL has built-in functions
Also called aggregate functions, such as *COUNT, SUM, AVG, MAX,* and *MIN*
command to create the index
CREATE INDEX
SELECT command that creates a view
Defining query
To qualify a field name in SQL
tablename.name_of_field (Ex: the RepNum field in the Rep table is written as Rep.RepNum)
In access, you can concatenate character fields such as FirstName and LastName fields into a single computed field by using the _____. Also, what would the syntax look like in SQL?
& operator Expression would be FirstName&' '&LastName, which places a space between the first name and the last name
PRIMARY KEY clause syntax
(*has the form PRIMARY KEY followed, in parentheses, by the field or fields that make up the primary key. When more than one field is included, the fields are separated by commas.*) PRIMARY KEY (field) or PRIMARY KEY (field, field)
calculates number of entries
COUNT
returns the number of values (NULL values will not be counted) of the specified column
COUNT(column_name) function
CHAR(n) (Data Type)
Character string n places long. In MS Access = short text
Grouping - definition
Create groups of records that share a common characteristic (Example: Group customers by rep number)
Format for using dates in *Access* queries
#MM/DD/YYYY#place number signs around the date (for example: #11/15/2015#)
In other versions of SQL, ____ is used as a wildcard to represent any collection of characters
% (percent sign)
How do you enforce legal-value integrity in SQL?
*CHECK* clause
SQL command to create an index
*CREATE INDEX* index_name *ON* table_name (column_name);
Enable users Smith and Park to add new records to the Item table.
*GRANT INSERT ON* Item *TO* Smith, Park;
SQL *GRANT* Syntax to *add new records to table*
*GRANT INSERT ON* table_name *TO* user
GRANT command that enables users to add new records to the table
*GRANT INSERT ON* table_name *TO* users;
Enable user Jones to *retrieve data* from the customer table but *not take any other action.*
*GRANT SELECT ON* Customer *TO* Jones;
GRANT command syntax to retrieve data from the table but not take any other action
*GRANT SELECT ON* table_name *TO* user
Options for indexes in Access
*No* - option for not creating an index *Yes(Duplicates OK)* - creates an index that allows duplicates *Yes (No duplicates)* - creates an index that prohibits duplicates
INTEGER (Data Type)
*Number without a decimal point* - *valid data from -2,147,483,648 to 2,147,483,647* - can be used for calculations
DECIMAL (*p,q*) (Data Type)
*P* number of digits; *q* number of decimal places Example: DECIMAL (5,2) represents a number with three places to the left and two places to the right of the decimal
Restrictions placed on *table and column names* by DBMS are: (RULES)
- names cannot exceed *18* characters, - names *must start with a letter* - names can contain only letters, numbers, and underscores ( _ ) - *names cannot contain spaces*
Primary Key Constraint
-PRIMARY KEY constraint uniquely identifies each record in a database table. - Primary keys must contain UNIQUE values. -A primary key column cannot contain NULL values. - Most tables should have a primary key, and each table can have only ONE primary key.
Union compatible
-Same number of fields -Corresponding fields must have same data types
Create an index on a field/fields when: (RULES)
-field is the primary key of the table - field is the foreign key in a relationship - field will be frequently used as a sort field - need to frequently locate a record based on a value in this field
Computed fields
-fields whose values you derive from existing fields addition (+), subtraction (-), multiplication (*), or division (/)
Indexes disadvantages
-occupies space on disk, -DBMS must update index whenever corresponding data are updated
Access DOES NOT SUPPORT
-stored procedures, but can create a parameter query instead - triggers
To join tables, construct the (3) SQL commands as:
1.Select Clause: list all fields you want to display 2.From Clause: list all tables that contain the data to display in the query results. 3. WHERE clause: give the condition that will restrict the data to be retrieved to only those rows from the two tables that match
ALTER TABLE command is used with what commands
ADD, DELETE, MODIFY
*to add, delete, or modify columns* in an existing table use what command?
ALTER TABLE command
This command to change a table's structure
ALTER TABLE command
Syntax of the ALTER table command
ALTER TABLE command, followed by the table name, and then the alternation to perform
To add a column in a table, use the following syntax:
ALTER TABLE table_name ADD column_name datatype;
*Deleting a field* from a table - syntax
ALTER TABLE table_name DELETE field_name;
*Changing properties* of an existing fields
ALTER TABLE table_name MODIFY column_name datatype;
DROP Column
ALTER TABLE table_name DROP COLUMN field;
All of the simple conditions must be true for the compound condition to be true
AND Operator
If the condition contains ___, *all of the conditions must be true* for a record to appear in the query results
AND operator
By using the word ___ after the computation, you can assign a name to the computed field
AS
calculates average of all entries in a given column
AVG
Ensure that the only legal values for credit limits are $5,000, $7,500, $10,000, or $15,000
CHECK (CreditLimit IN (5000,7500,10000,15000))
SQL legal-value integrity syntax
CHECK (column_name condition)
SQL command to create a multiple-field index with a field name in descending order
CREATE INDEX Index_name ON table_name (fields DESC)
In this *Generic* SQL command, you are describing a table that will be named Rep. The table contains nine fields: RepNum, LastName, FirstName, Street, City, State, PostalCode, Commission, and rate. RepNum is a character field that is two positions in length. LastName is a character field with 15 characters. FirstName is a character field with 15 characters. Street is a character field with 15 characters. City is a character field with 15 characters. State is a character field with 2 characters. PostalCode is a character with 5 characters. Commission is a numeric field that stores seven digits, including two decimal places. Rate is a numeric field that stores three digits, including two decimal places.
CREATE TABLE Rep (RepNum CHAR (2), LastName CHAR (15), FirstName CHAR(15), Street CHAR (15), City CHAR (15), State CHAR(2), PostalCode CHAR (5), Commission DECIMAL (7,2), Rate DECIMAL (3,2));
In this *Access* SQL command, you are creating the Rep table by describing its layout. The table contains nine fields: RepNum, LastName, FirstName, Street, City, State, PostalCode, Commission, and rate. RepNum is a character field that is two positions in length. LastName is a character field with 15 characters. Street is a character field with 15 characters. City is a character field with 15 characters. State is a character field with 2 characters. PostalCode is a character field with 5 characters. Commission is a currency field. Rate is a numeric field that stores numbers.
CREATE TABLE Rep (RepNum CHAR (2), LastName CHAR (15), FirstName CHAR(15), Street CHAR (15), City CHAR (15), State CHAR(2), PostalCode CHAR (5), Commission CURRENCY, Rate NUMBER );
This command is used to create a table by describing its layout to the DBMS, *creates the table in the database*
CREATE TABLE command
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE field_condition;
Formed by combining simple conditions using either or both: *AND* and *OR* operators
Compound condition
Oracle's system catalog uses
DBA_TABLES, DBA_TAB_COLUMNS, and DBA_VIEWS
*Delete any row in the OrderLine table on which the item number is DL51.* OrderLine (OrderNum, ItemNum, NumOrdered, QuotedPrice)
DELETE FROM OrderLine WHERE ItemNum='DL51';
Syntax of the DELETE table command
DELETE FROM table_name WHERE some_column=some_value; (The SQL Command used to delete a table. The word DELETE is followed by a FROM clause identifying the table. Use a WHERE clause to specify a condition. Any records satisfying the condition is deleted. If you omit the condition for selecting the records to delete, when you run the query, it will delete all records from the table.)
What command deletes a table
DROP TABLE command
Drop Table Syntax
DROP TABLE table_name
Advantages of views
Data independence, each user has his/her own view, view should contain only fields required by the user - (greatly simplifies user's perception of the database/security)
Date format for other programs (not Access) (Data Type)
Dates in DD-MON-YYYY or MM/DD/YYYY form Enter the day of the month, a hyphen, three-character abbreviation for the month, a hyphen, and the year, enclosed in a single quotation marks (ex: '15-Nov-2015')
allows user to print detailed documentation about any table, query, report, form, or other object in the database
Documenter
How to *Specify the primary key in SQL*
Enter a PRIMARY KEY clause in either an ALTER TABLE or a CREATE TABLE command.
the restriction that the primary key cannot allow null values. (In other words, preventing the primary key from accepting null values ensure that you can distinguish one record from another)
Entity integrity
Three integrity rules are:
Entity integrity, referential integrity, and League-Values integrity
FOREIGN KEY clause syntax
FOREIGN KEY (field) REFERENCES table_containing_primary_key
Referential integrity is specified in SQL using the
FOREIGN KEY clause
To specify referential integrity in SQL:
FOREIGN KEY clause in either the CREATE TABLE or ALTER TABLE commands
The part of an SQL SELECT command that indicates the *tables* in the query. In SQL retrieval commands, tables are listed in the ____
FROM clause
fields whose values is required to *match the value of the primary key for a second table* (way to relate the two tables, in one table points to a PRIMARY KEY in another table.)
Foreign Key
Suppose you own emp table. Now you want to *GRANT SELECT, UPDATE, INSERT*privilege on this table to other user "SAMI".
GRANT SELECT, UPDATE, INSERT ON emp TO sami;
*provides privileges to users*, such as select insert, update, and delete table data. *(allow privileges on tables, views, procedure to others users or roles)*
GRANT command
indicates grouping in SQL
GROUP BY clause
To restrict the groups to be displayed, use the ___. (___ is to groups, what the WHERE clause is to rows) (*Think of it as conditions for groups)
HAVING clause
___ provides a concise way of phrasing certain conditions
IN operator
*Add a new rep to the Rep table. Her number is 75, her name is Dorothy Argy; and her address is 424 Bournemouth, Grove, CA 90092. She has not yet earned any commission, but her commission rate is 6% (0.06).* Rep(RepNum, LastName, FirstName, Street, City, State, PostalCode, Commission, Rate)
INSERT INTO Rep VALUES ('75', 'Argy', 'Dorothy', '424 Bournemouth', 'Grove', 'CA', '90092', 0.00, 0.06);
Syntax of the INSERT command
INSERT INTO table_name VALUES (values for the new row) (After the words INSERT INTO, you list the name of the table, followed by the word VALUES. Then you list the values in parentheses for each of the column. Character values must be enclosed with quotation marks, and the values for each column are separated by commas.)
adds new data to a table.
INSERT command
- saves the results of a query as a table - Specified before FROM and WHERE clauses
INTO clause
The ___ is used in a *SELECT command* to create a table containing the results of the query
INTO clause
How do you create a view in SQL?
In SQL, a defining query creates a view. When you enter a query that references a view, it is merged with the defining query to produce the query that is actually executed.
field or combination of fields on which index is built
Index Key
*facilitate data retrieval from the database*. You can create an ___ on any field or combination of fields. (*Hint: pointer to data in a table*, think of a textbook)
Indexes (Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.)
To use a wildcard, include the ___ in the WHERE clause
LIKE operator
set of values allowable in a field
Legal values
no record can exist with a value in the field other than one of the legal values. (value entered in a field must be one of the legal values that satisfies some particular condition)
Legal-value integrity
calculates largest values respectively
MAX
calculates smallest values respectively
MIN
You can precede a single condition with the ___, to negate a condition. In other words, preceding a condition with the __, reverses the result to the original condition. That is, if the *original condition is true, then the new condition will be false*, and if the *original condition is false, the new condition will be true*
NOT Operator
used when a value is missing, unknown, or inapplicable. *(not similar to a blank or zero value, because they both have actual values)*
Nulls
Example to use a PRIMARY KEY clause to indicate that CustomerNum is the primary key for the Customer table
PRIMARY KEY (CustomerNum)
is the property that states that the value in any foreign key field must be null or must match an actual value in the primary key field of another table
Referential integrity
Cust(CustNum,CustName, Balance, CreditLimit, RepNum) Question: CreditLimit more than $5,000 AND less than $10,000
SELECT * FROM Cust WHERE CreditLimit>5000 AND CreditLimit<10000; ALTERNATIVE SELECT * FROM Cust WHERE CreditLimit BETWEEN 5000.01 AND 999.99
Syntax of the INTO clause
SELECT * INTO newtable FROM table1 WHERE condition; (The SELECT INTO statement selects data from one table and inserts it into a new table.)
*How many items are in category GME?* Item (ItemNum, Description, OnHand, Category, Storehouse, Price)
SELECT COUNT (*) FROM Item WHERE Category = 'GME';
Customer(CustNum, CustName, CustAddress,Balance, CreditLimit, RepNum) Count all the records that equal to NJ
SELECT COUNT(*) FROM Customer WHERE CustAddress='*NJ*';
*Find the total number of customers and the total of their balances. Change the column names for the number of customers and the total of their balances to CustomerCount and BalanceTotal, respectively.*
SELECT COUNT(*) AS CustomerCount, SUM (Balance) AS BalanceTotal FROM Customer;
The COUNT(*) function Syntax
SELECT COUNT(*) FROM table_name;
*Find the number of customers and the total of their balance from the customer table.* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT COUNT(*), SUM (Balance) FROM Customer;
The COUNT function to count the columns Syntax
SELECT COUNT(column_name) FROM table_name;
Systables syntax
SELECT Column_name, Column_type FROM Syscolumns WHERE Tablename='field'
Customer(CustNum, CustName, CustAddress,Balance, CreditLimit, RepNum) *List the customer's address and order (sort) customer's address record in Descending order.*
SELECT CustAddress FROM Customer ORDER BY CustAddress DESC
*Find the customer name for every customer located in the city of Grove.* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerName FROM Customer WHERE City='Grove';
*Find the name of customer 126.* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerName FROM Customer WHERE CustomerNum='126';
*List the name of every customer with a $10,000 credit limit.* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerName FROM Customer WHERE CreditLimit=1000
*List the number and name of all customers that are represented by rep 15 or that currently have orders on file or both.* Orders(OrderNum, OrderDate, CustomerNumber) Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerNum, CustomerName FROM Customer WHERE RepNum='15' UNION SELECT Customer.CustomerNum, CustomerName FROM Customer, Orders WHERE Customer.CustomerNum=Orders.CustomerNum;
*List the number, name, and balance of all customers with balances greater than or equal to $1,000 and less than or equal to $5,000.* (*2 different approaches*) Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE Balance BETWEEN 1000 and 5000; Alternative approach SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE Balance>=1000 AND Balance<=5000
*List the number, name, and balance for each customer whose balance is between $1,000 and $5,000.* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE Balance>1000 AND Balance <5000
*List the number, name, and balance of all customers* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerNum, CustomerName, Balance FROM Customer;
*List the number, name, credit limit, and balance for all customers with credit limits that exceeds their balances.* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerNum, CustomerName, CreditLimit, Balance FROM Customer WHERE CreditLimit>Balance;
*Create a new calculated field called AvailableCredit based on the difference from CreditLimit and Balance. Then list the number, name, and available credit for all customers with credits limits that exceed their balances.* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerNum, CustomerName, CreditLimit-Balance AS AvailableCredit FROM Customer WHERE CreditLimit>Balance;
List the number, name, and available credit for all customers. Create a new field named AvailableCredit from the existing fields: CreditLimit and Balance. Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerNum, CustomerName, CreditLimit-Balance AS AvailableCredit FROM Customer; (creates a new field named Available Credit, which is computed by subtracting the value in the Balance field from the value in the CreditLimit field (AvailableCredit=CreditLimit-Balance))
*List the number and name of each customer whose credit limit is $10,000 together with the number, last name, and first name of the rep who represents the customer. Order the record by customer number.* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum) Rep(RepNum, LastName, FirstName, Street, City, State, PostalCode, Commission, Rate)
SELECT CustomerNum, CustomerName, Rep.RepName, LastName, First Name FROM Customer, Rep WHERE Customer.RepNum=Rep.RepNum AND CreditLimit =10000 ORDER BY CustomerNum;
List the number and name of each customer together with the number, last name, and first name of the rep who represents the customer. Order the records by customer number. Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum) Rep(RepNum, LastName, FirstName, Street, City, State, PostalCode, Commission, Rate)
SELECT CustomerNum, CustomerName, Rep.RepNum, LastName, FirstName FROM Customer, Rep WHERE Customer.RepNum=Rep.RepNum ORDER BY CustomerNum;
*List the number, name, and complete address of every customer located on a street that contains the letters Oxford. (Not access version)* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerNum, CustomerName, Street, City, State, PostalCode FROM Customer WHERE Street LIKE '%Oxford%'
*List the number, name, and complete address of every customer located on a street that contains the letters Oxford. (ACCESS VERSION)* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerNum, CustomerName, Street, City, State, PostalCode FROM Customer WHERE Street LIKE '**Oxford**';
*List the number, name, street, and credit limit of all customers. Order (sort) the customers by name.* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer ORDER BY CustomerName;
*List the 2 versions to display the number, name, street, and credit limit for every customer with a credit limit of $7,500, $10,000, or $15,000.* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer WHERE CreditLimit IN (7500, 10000, 15000); Alternative SELECT CustomerNum, CustomerName, Street, CreditLimit FROM CreditLimit = 7500 OR CreditLimit = 10000 OR CreditLimit=15000
List the number, name, street, and credit limit of all customers. Order the customers by name within descending credit limit.
SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer ORDER BY CreditLimit DESC, CustomerName;
*List the descriptions of all items that are not in storehouse 3.* Item (ItemNum, Description, OnHand, Category, Storehouse, Price)
SELECT Description FROM Item WHERE NOT Storehouse ='3';
*List the descriptions of all items that are located in storehouse 3 and for which there are more than 40 units on hand.* Item (ItemNum, Description, OnHand, Category, Storehouse, Price)
SELECT Description FROM Item WHERE Storehouse ='3' AND OnHand>40;
*List the descriptions of all items that are located in storehouse 3 or for which there are more than 40 units on hand or both.* Item (ItemNum, Description, OnHand, Category, Storehouse, Price)
SELECT Description FROM Item WHERE Storehouse = '3' OR OnHand>40;
*List the order number for each order that contains an order line for an item located in storehouse 3. (*HINT: This is a subquery)* OrderLine(OrderNum, ItemNum, NumOrdered, QuotedPrice) Item (ItemNum, Description, OnHand, Category, Storehouse, Price)
SELECT OrderNum FROM OrderLine WHERE ItemNum IN (SELECT ItemNum FROM Item WHERE Storehouse='3');
For every order, list the order number, order date, customer number, and customer name. In addition, for each order line within the order, list the item number, description, number ordered, and quoted price. Order the records by order number Orders(OrderNum, OrderDate) Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum) OrderLine (OrderNum, ItemNum, NumOrdered, QuotedPrice) Item (ItemNum, Description, OnHand, Category, Storehouse, Price)
SELECT Orders.OrderNum, OrderDate,Customer.CustomerNum, CustomerName, Item.ItemNum, Description, NumOrdered, QuotedPrice FROM Orders, Customers, OrderLine, Item WHERE Customer.CustomerNum=Orders.CustomerNum AND Orders.OrderNum=OrderLine.OrderNum AND OrderLine.ItemNum=Item.ItemNum ORDER BY Orders.OrderNum;
*For each rep whose customer's average balance is less than $2,000, list the rep number, the number of customers assigned to the rep, and the average balance of the rep's customers. Rename the count of the number of customers and the average of the balances to NumCustomers and AverageBalance, respectively. Order the groups by rep number.*
SELECT RepNum, COUNT (*) AS NumCustomers, AVG (Balance) AS AverageBalance FROM Customer GROUP BY RepNum HAVING AVG (Balance) <2000 ORDER BY RepNum;
*For each rep, list the rep number, the number of customers assigned to the rep, and the average balance of the rep's customers. Group the records by rep number, and order the records by rep number.*
SELECT RepNum, COUNT (*), AVG (Balance) FROM Customer GROUP BY RepNum ORDER BY RepNum;
SQL retrieval command structure
SELECT field(s) FROM table(s) [WHERE condition(s)];
The part of an SQL SELECT command that indicates the *columns* to be included in the query results. In SQL retrieval commands, fields are listed in the ___.
SELECT clause
*Create a new table named SmallCust consisting of all fields from the existing Customer table and those rows on which the credit limit is less than or equal to $7,500.* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
SELECT* INTO SmallCust FROM Customer WHERE CreditLimit<=7500;
calculates all entries in a given column
SUM
To designate primary key in Access
Select primary key field in Table Design View, click the primary key button in the Tools group on the TABLE TOOLS DESIGN Tab
The field on which the records are sorted is called:
Sort key
SQL
Structured Query Language, is a language that is used to manipulate relational database
query saved in a file that users can execute later
Stored procedure
SMALLINT (Data Type)
Stores integers, but uses less space than INTEGER data type -*valid data from -32,768 to 32,767*
In SQL, a query that appears within another query. The inner query is considered this.
Subquery
Row-and-column subset view
Subset of rows and columns in an individual table
action that occurs automatically in response to an associated database operation such as an *INSERT, UPDATE, or DELETE*
Trigger
*Change the street address of customer 502 to 1445 Rivard.* Customer(CustomerNum,CustomerName,Street, City, State, PostalCode, Balance, CreditLimit, RepNum)
UPDATE Customer SET Street='1445 Rivard' WHERE CustomerNum='502';
ORACLE to make complex changes
Use CREATE TABLE command to describe the new table - Insert values into it using INSERT command combined with a SELECT clause
Structure of the Update Command
UPDATE table SET newvalue WHERE criteria; *(UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;)*
Syntax of the Update command
UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value; (After the word Update, you indicate the table to be updated. After the word SET, you indicate the field to be changed, followed by equal sign and the new value. Finally, include a condition in the WHERE clause in which case only the records that satisfy the condition will be changed. Note: *The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!*)
In Access, a rule that data entered in a field must satisfy
Validation Rule
informs user of the reason for rejection of data that violates the rule. (*In Access, a message that is displayed when a validation rule is violated.*)
Validation text
What would the syntax structure look like that displays a condition that selects rows on which the balance is greater than the credit limit?
WHERE Balance>CreditLimit
In SQL retrieval commands, the *conditions* are listed in the ___. The part of an SQL SELECT command that indicates the condition rows must satisfy to be displayed in the query. Note: *if you want to list all the fields in the query results, you will not use this clause*
WHERE clause
In Access SQL, the ___ is used as a wildcard to represent any collection of characters
asterisks (*)
How do you create a view in Access?
by saving queries that select the data to use in the view
NOT operator - definition
can use a simple condition and the "not equal to" operator (< >). Or you could use the "equals" operator (=) in the condition, but precede the entire condition with the NOT operator.
SELECT* INTO command executes
copy all columns into the new table:
Joining tables is accomplished in SQL by:
enter appropriate conditions in the WHERE clause
Union of two tables (definition)
is a table containing all rows in the first table, the second table, or both tables
DELETE command - definition
is used to delete data from the database
Indexes advantages
makes some data retrieval more efficient and quicker. (speed up searches and sorting on field)
The union of the results of two queries is specified by:
placing the UNION operator between the two queries
Changing field name in a view in Access - process
precede the name of the field with the desired name, followed by a colon.
In Access SQL, the ___ represents any individual character
question mark (?) (example: T?m represents the letter T followed by any single character, followed by the letter m and when used in a WHERE clause, retrieves records that includes the words Tim, Tom, or T3m, etc.)
What changes does *Oracle not allow* in the *DBMS*
reduction of field size and data type
Revoke statment - definition
removes privileges *from* users
Database resides on a computer
server
In conditions, character fields must be enclosed in ___.
single quotation marks
The COUNT(*) function returns
the number of records in a table
In other version of SQL, (not Access), uses a ____ to represent any *individual* character
underscore ( _ )
To select specific columns
use the *SELECT* clause with the list of columns separated by commas
To indicate *all fields* to be included in the query results
use the asterisk * symbol after the word SELECT; (when used after the word SELECT, the * symbol indicates that you want to include all fields in the query results in the order in which you described them to the DBMS)
Purpose of Joining tables together
you can find rows in two or more tables that have identical values in matching fields.
