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