Lesson 6 Flashcards
alias
An alternative name for a column or table in a SQL statement.
ALTER TABLE
- The SQL command used to make changes to table structure.
- When the command is followed by a keyword
ADDorMODIFY, it adds a column or changes column characteristics.
AND
- The SQL logical operator used to link multiple conditional expressions in a
WHEREorHAVINGclause. - It requires that all conditional expressions evaluate to true.
AVG
A SQL aggregate function that outputs the mean average for a specified column or expression.
BETWEEN
In SQL, a special comparison operator used to check whether a value is within a range of specified values.
Boolean algebra
A branch of mathematics that uses the logical operators OR, AND, and NOT.
cascading order sequence
A nested ordering sequence for a set of rows.
“Such as a list in which all last names are alphabetically ordered and, within the last names, all first names are ordered.”
COMMIT
The SQL command that permanently writes data changes to a database.
COUNT
A SQL aggregate function that outputs the number of rows containing not null values for a given column or expression
sometimes used in conjunction with the DISTINCT clause.
CREATE INDEX
A SQL command that creates indexes on the basis of a selected attribute or attributes.
CREATE TABLE
A SQL command that creates a table’s structures using the characteristics and attributes given.
DELETE
- The
SQL DELETEcommand can delete entire rows from a table. - If you use it with a
WHEREclause, it deletes only the rows matching your condition. - Without a
WHEREclause, it deletes all rows from the table.
DISTINCT
A SQL clause that produces only a list of values that are different from one another.
DROP INDEX
A SQL command that permanently deletes an index.
DROP TABLE
A SQL command that permanently deletes a table and its data.
EXISTS
In SQL, a comparison operator that checks whether a subquery returns any rows.
FROM
A SQL clause that specifies the table or tables from which data is to be retrieved.
GROUP BY
A SQL clause used to create frequency distributions when combined with any of the aggregate functions in a SELECT statement.
HAVING
A clause applied to the output of a GROUP BY operation to restrict selected rows.
IN
In SQL, a comparison operator used to check whether a value is among a list of specified values.
inner query
- A query that is embedded or nested inside another query.
- Also known as a nested query or a subquery.
INSERT
A SQL command that allows the insertion of one or more data rows into a table.
IS NULL
In SQL, a comparison operator used to check whether an attribute has a value.
LIKE
In SQL, a comparison operator used to check whether an attribute’s text value matches a specified string pattern.
MAX
A SQL aggregate function that yields the maximum attribute value in a given column.
MIN
A SQL aggregate function that yields the minimum attribute value in a given column.
nested query
In SQL, a query that is embedded in another query.
NOT
A SQL logical operator that negates a given predicate.
OR
- The SQL logical operator used to link multiple conditional expressions in a
WHEREorHAVINGclause. - It requires only one of the conditional expressions to be true.
ORDER BY
A SQL clause that is useful for ordering the output of a SELECT query
for example, in ascending or descending order.
ROLLBACK
A SQL command that restores the database table contents to the condition that existed after the last COMMIT statement.
rules of precedence
- Basic algebraic rules that specify the order in which operations are performed.
subquery
A query that is embedded (or nested) inside another query. Also known as a nested query or an inner query.
SUM
A SQL aggregate function that yields the sum of all values for a given column or expression.
UPDATE
A SQL command that allows attribute values to be changed in one or more rows of a table.
WHERE
A SQL clause that adds conditional restrictions to a SELECT statement.
limits the rows returned by the query
wildcard character
A symbol that can be used as a general substitute for:
-
All columns in a table (
*) when used in an attribute list of aSELECTstatement -
zero or more charactersin a SQL
LIKEclause condition (%and_).
SQL commands can be divided into two overall categories: _______ and ______ commands.
- Data definition languauge (DDL)
- Data manipulation language (DML)
The basic DML commands are:
- SELECT
- INSERT
- UPDATE
- DELETE
- COMMIT
- ROLLBACK
- The basic data definition commands allow you to create tables and indexes.
- Many SQL constraints can be used with columns. The commands are:
- CREATE TABLE
- CREATE INDEX
- ALTER TABLE
- DROP TABLE
- DROP INDEX
The ANSI prescribes a standard SQL-the current fully approved version is known as SQL 07. (T/F)
False
- How can you select specific contents from a table?
- What methods can you use to restrict the rows included in the output?
- You can select specific contents from a table by naming the desired fields and applying restrictions to the rows you want to include in the output.
- You can restrict the rows included in the output by using conditions such asfiltering based on specific values, setting limits, or applying criteria that meet certain requirements.
The ______ command, coupled with appropriate search conditions, is an incredibly powerful tool that enables a user to transform data into information.
SELECT
The conditional LIKE should be used in conjunction with wildcard characters. (T/F)
True
What does a database language allow users to do?
A database language enables users to perform complex queries to transform raw data into useful information.
The special operator used to check whether a subquery returns any rows is _____.
EXISTS
According to the rules of precedence, which of the following computations should be completed first?
Performing operations within parentheses
The SQL command that allows a user to permanently save data changes is _____.
COMMIT
In SQL, all _______ expressions evaluate to true or false.
conditional or boolean
database language
A database language enables the user to create database and table structures to perform basic data management chores.
The SQL data manipulation command HAVING:
- The
HAVINGclause in SQL is used to filter the results of an aggregate query - It works in conjunction with the
GROUP BYclause.
A.query: a query that uses aggregate functions like SUM, AVG, COUNT, etc.
How are String comparisons made.
String somparisons are made from left to right
To make the output more readable, the SQL standard permits the use of aliases for any column in a ______ statement.
SELECT
Which operator is used to check whether an attribute value lies within two bounds?
BETWEEN
The SQL command that allows a user to list the contents of a table is _____.
SELECT
Why are Date procedures often more software-specific than other SQL procedures?
- Different database systems (like MySQL, PostgreSQL, etc.) have their own ways of handling dates.
- This means functions for things like extracting the day from a date or adding days might be called different names or have different syntax.
- This makes it tricky to write date-related SQL code that works across all databases.
A cascade order sequence is a multilevel ordered sequence created how?
Cascades happen by
- listing several attributes
- separating each by commas
- All after the
ORDER BYclause.
Oracle users can use the Access QBE (query by example) query generator. (T/F)
False
The _____ special operator is used to check whether an attribute value is null.
IS NULL
SQL allows the use of logical restrictions on its inquiries such as OR, AND, and NOT. (T/F)
True
ANSI-standard SQL allows conjunction of?
- The use of special operators
- With the
WHEREclause
The basic SQL vocabulary has fewer than _____ words.
hundred
What is the ISO and how does it correlate with the ANSI SQL standard?
- ANSI develops the SQL standards, and ISO adopts them, making them internationally recognized.
- This ensures that SQL databases worldwide can “speak” the same language and work together effectively. Learn More
Why is SQL considered easier to learn?
- Declarative language: You tell the database what you want, not how to get it. This simplifies the logic compared to procedural programming languages.
- Readable syntax: Commands like
SELECT,FROM,WHEREare easy to understand and remember. - Widely used: There are tons of resources, tutorials, and communities to help you learn.
How mathematical operators can be used with character-based attributes?
Mathematical operators can be used with character-based attributes in various ways, such as:
- Equality (
=): city = “New York” checks if the attribute “city” holds the value “New York”. - Inequality (
!=): country != “USA” checks if the attribute “country” does not hold the value “USA”. - Pattern matching (
LIKE): name LIKE “J%” checks if the attribute “name” starts with the letter “J”.
A specialty field in mathematics, known as ______ algebra, is dedicated to the use of logical operators.
Boolean
A(n) _____ is an alternate name given to a column or table in any SQL statement.
alias
The SQL command that allows a user to insert rows into a table is _____.
INSERT
The special operator used to check whether an attribute value matches a given string pattern is _____.
LIKE
How do most SQL implementations handle case sensitivity in searches?
Most SQL implementations yield case-insensitive searches.
Do all Relational Database Management Systems (RDBMSs) treat uppercase and lowercase letters the same way when comparing data?
- Some, like Microsoft Access, automatically handle case sensitivity by performing necessary conversions, ensuring that ‘apple’ and ‘Apple’ are treated as equivalent.
- Other RDBMSs might differentiate between uppercase and lowercase, requiring specific functions or techniques to achieve case-insensitive comparisons.
In the SQL environment, the word _____ covers both questions and actions.
query
What does ISO stand for?
International Organization for Standardization
What is the nature of the ISO?
An independent, non-governmental international organization
What is the primary goal of ISO standards?
To ensure quality, safety, and efficiency across the globe
What does ANSI stand for?
American National Standards Institute
What is the role of ANSI?
Develops standards in the United States
American National Standards Institute
What does SQL stand for?
Structured Query Language
What is SQL used for?
Managing data in relational database management systems
How does ANSI relate to SQL standards?
ANSI develops standards for SQL
What is the relationship between ISO and ANSI SQL standards?
ISO recognizes and adopts ANSI SQL standards.
What is the benefit of ISO adopting ANSI SQL standards?
Provides global recognition and acceptance
What does the adoption of ANSI SQL standards by ISO promote?
International Organization for Standardization | American National Standards Institute
Interoperability and consistency among SQL databases
(in ter-op-er a-bil-i-ty): the ability of computer systems/software to exchange and make use of information
True or False: ISO is a governmental organization.
False
Fill in the blank: ANSI develops the SQL standards, and ISO _______ them.
adopts
What is the main purpose of developing SQL standards?
To ensure that different database systems can work together seamlessly operability
