MGT 4560 Exam 1 (Ch. 4)

What are the three types of SQL commands?

(1) Data Definition Language (DDL) (2) Data Manipulation Language (DML) (3) Data Control Language (DCL)

Define Self-Join

A join requires matching rows in a table with other rows in that same table, e.g., unary relationship (most common). no special command in SQL to do this

Define Join

A relational operation that causes two or more tables with a common domain to be combined into a single table or view

________________ statement allows you to remove tables from your schema

DROP TABLE ex: DROP TABLE CUSTOMER_T

What does the NOT Boolean Operator do?

Negates an expression

What does the HAVING clause do?

The HAVING clause acts like a WHERE clause, but it identifies groups, rather than rows, that meet a criterion.

What is the Difference between join and subqueries?

The joining technique is useful when data from several relations are to be retrieved and displayed and the relationships are not necessarily nested. Subquery technique allows you to display data from only the tables mentioned in the outer query.

Define a Schema

The structure that contains descriptions of objects created by a user (base tables, views, constraints)

True or False: A SQL environment includes an instance of a SQL DBMS along with the accessible databases and the users and programs that may use the DBMS to access the databases.

True

True or False: SQL is supported by most vendors (IBM DB2, MySQL, Oracle, PostgreSQL, Microsoft SQL Server)

True

Define CREATE SCHEMA

Used to define the portion of a database that a particular user owns. Schemas are dependent on a catalog and contain schema objects, including base tables and views, domains, constraints, etc.

Each database is contained in a _________________.

catalog

Define an Information schema

contains descriptions of all schemas in the catalog, tables, views, attributes, privileges, constraints, and domains, along with other information relevant to the database

The development version is used when the ________________________________ and continues to serve as a development tool where enhancements and maintenance efforts can be thoroughly tested before being applied to the production database.

database is being built

Define Subquery Technique

display data from only the tables mentioned in the outer query Involves placing an inner query (SELECT ... FROM ... WHERE) within a WHERE or HAVING clause of another query.

Define Structured Query Language (SQL)

the standard for relational database management systems (RDBMS)

Define Joining Technique

when data from several relations are to be retrieved and displayed and the relationships are not necessarily nested

The production version is the _____________ version, which captures real business data and thus must be very tightly controlled and monitored.

live

What are the benefits of standardization?

reduce training costs; increase productivity and application portability; decrease dependence on a single vendor

If a DELETE request comes for a record with dependent records in another table, the DBMS could _____________ the delete, which means to disallow it. Or, it could ______________ the delete, so that dependent records with matching foreign keys will also be deleted. Or it could _________________, which means that deleting the primary key record will result in setting all corresponding foreign keys to be set to null (this would imply an optional one cardinality in the relationship).

restrict, cascade, set null

The column constraint __________________________ can be used to enforce referential integrity

REFERENCES

What are the Steps to Create a Table in SQL?

Step 1 - identify the appropriate data type, including length, precision, and scale, if required, for each attribute Step 2 - identify the columns that should not accept null values Also, when a column control of UNIQUE is established for a column, the data in that column must have a different value for each row of data within that table (i.e., no duplicate values). Where a column or set of columns is designated as UNIQUE, that column or set of columns is a candidate key. Step 3 - Identify any columns for which domain specifications may be stated that are more constrained than those established by data type. The CHECK operator always ensures that update and insert attempts to this table will only allow the values listed. Step 4 - Identifying Foreign Keys and Establishing Relationships

What makes up a SQL Environment?

(1) Catalog (2) Schema (3) Data Definition Language (4) Data Manipulation Language (5) Data Control Language

_________________________ statement allows you to change column specifications.

ALTER TABLE ALTER TABLE table_name alter_table_action Table action could be: ADD[COLUMN] column_definition ALTER[COLUMN] column_name SET DEFAULT default-value DROP [COLUMN] column name [RESTRICT] [CASCADE] ADD table_constraint ex: ALTER TABLE CUSTOMER_T ADD COLUMN CustomerType VARCHAR (10) DEFAULT "Commercial"

What is the basic syntax for creating a database?

CREATE SCHEMA database_name AUTHORIZATION owner_user id The database will be owned by the authorized user, although it is possible for other specified users to work with the database or even to transfer ownership of the database.

Define Data Control Language (DCL)

Commands that control a database, including administering privileges and committing data

Define Data Definition Language (DDL)

Commands that define a database, including creating, altering, and dropping tables and establishing constraints

Define Data Manipulation Language (DML)

Commands that maintain and query a database

Define CREATE VIEW

Defines a logical table from one or more tables or views. Views may not be indexed. There are limitations on updating data through a view. Where views can be updated, those changes can be transferred to the underlying tables originally referenced to create the view.

Define CREATE TABLE

Defines a new table and its columns. The table may be a base table or a derived table. Tables are dependent on a schema. Derived tables are created by executing a query that uses one or more tables or views.

Define a Catalog

Describes any object that is a part of the database, regardless of which user created that object (a set of Schemas) Each catalog must also contain an information schema

What does the AND Boolean Operator do?

Joins two or more conditions and returns results only when all conditions are true

What does the OR Boolean Operator do?

Joins two or more conditions and returns results when any conditions are true

True or False: Because character data will process more quickly, numeric data should be stored as character data if no arithmetic calculations are expected.

True

True or False: Each DBMS has a defined list of data types that it can handle. All contain numeric, string, and date/time-type variables. When a table is created, the data type for each attribute must be specified. Selection of a particular data type is affected by the data values that need to be stored and the expected uses of the data.

True

True or False: Each of these CREATE commands can be reversed by using a DROP command. Thus, DROP TABLE table-name will destroy a table, including its definition, contents, and any constraints, views, or indexes associated with it. Usually only the table creator may delete the table. DROP SCHEMA or DROP VIEW will also remove the named schema or view.

True

True or False: Once the data model is designed and normalized, the columns needed for each table can be defined, using the SQL CREATE TABLE command.

True

True or False: Tables will not be dropped if there are other tables that depend on them. This means that if any table has a foreign key to the table being dropped, the drop will fail. Therefore, it makes a difference in which order you drop the tables.

True

True or False: The ALTER command will be done after tables have already been created.

True

True or False: The information contained in the catalog is maintained by the DBMS as a result of the SQL commands issued by the users and can be rebuilt without conscious action by the user. It is part of the power of the SQL language that syntactically simple SQL commands may result in complex data management activities carried out by the DBMS software.

True

True or False: The standards are not perfectly applied - different database vendors may use some different constructs in their SQL versions. You may modify SQL statements when migrating from one vendor to another (for example, switching from Oracle to Microsoft SQL Server or vice versa).

True

Most companies keep at least ________ versions of any database they are using.

two