Chapter 5 Introduction to SQL Essay

93) Explain the three classes of SQL commands and when they would be used. Answer: There are three classes or types of SQL commands. Data definition language commands are used to create tables, alter and drop tables, views and indexes. These commands are used to build the structure of the database as well as some additional objects. The next type is Data Manipulation Language commands, which are used to maintain and query a database. Commands in this class include select, update, delete and insert. The last type is the data control language commands, which are used to grant and revoke privileges on tables and other objects in the database. LO: 5.1: Define key terms. Difficulty: Moderate Classification: Concept AACSB: Information Technology 94) Discuss the pros and cons of using dynamic views. Answer: A dynamic view is useful, since it provides a way to access part of a table or a combination of columns from multiple tables. A view can simplify query commands, since much of the complicated syntax of a query can be done to create the view. A view can also improve programmer productivity, since programmers can access views rather than have to rewrite the SQL commands for a query. Security is increased, since users can only see what the view presents. Also, views use little or no storage space. On the downside, views use processing time each time that the view is recreated for reference. Also, the views may or may not be recreatable. LO: 5.1: Define key terms. Difficulty: Difficult Classification: Concept AACSB: Information Technology 95) What is a materialized view, and when would it be used? Answer: Materialized views are just like dynamic views, except that an actual copy of the data is kept. With a dynamic view, the SQL is stored on the server and executed when the view is referenced. A materialized view, on the other hand, keeps a copy of the actual data. This increases performance and is particularly useful if the data are relatively static and the number of queries against the view are high. LO: 5.1: Define key terms. Difficulty: Difficult Classification: Concept AACSB: Information Technology 21 96) What were the original purposes of SQL, and does SQL as we know it today live up to those standards? Answer: The following were the original purposes of SQL: 1. To specify the syntax and semantics of SQL data definition and manipulation languages 2. To define the data structures and basic operations for designing, accessing, maintaining, controlling, and protecting an SQL database 3. To provide a vehicle for portability of database definition and application modules between conforming DBMSs 4. To specify both minimal (Level 1) and complete (Level 2) standards, which permit different degrees of adoption in products 5. To provide an initial standard, although incomplete, that will be enhanced later to include specifications for handling such topics as referential integrity, transaction management, userdefined functions, join operators beyond the equi-join, and national character sets While SQL as we know it today does have some variants, there is a basic standard which is adhered to. As mentioned in the text, it seems that industry lags behind the standards somewhat. Each vendor has its own set of proprietary features which differ from the standard. LO: 5.2: Interpret the history and role of SQL in database development. Difficulty: Difficult Classification: Synthesis AACSB: Information Technology 97) What steps should be followed when preparing to create a table? Answer: When preparing to create a table, one should: 1. Identify the appropriate data type and length for each attribute 2. Identify the columns that should accept null values 3. Identify that columns that need to be unique 4. Identify all primary-foreign key mates 5. Determine default values 6. Identify any columns for which domain constraints, such as check, need to be stated 7. Create the table and any indexes using create table and create index statements LO: 5.3: Define a database using the SQL data definition language. Difficulty: Difficult Classification: Synthesis AACSB: Information Technology 98) What three clauses are contained in most SQL retrieval statements? Answer: The SELECT clause, which lists the columns and calculated expression from base tables. The FROM clause, which identifies tables and views which we want to gather data from in the query. Finally, the WHERE clause, which is used to specify conditions for selection of rows in the result set. LO: 5.4: Write single-table queries using SQL commands. Difficulty: Moderate Classification: Concept AACSB: Information Technology 22 99) What are some of the standard SQL functions that can be used in the SELECT clause? Answer: The standard functions can be broken down into 4 categories: Mathematical, String, Date, and Analytical. Mathematical functions include: Min, Max, Count, Sum, Round, Trunc and Mod. String functions include lower, upper, initcap, concat, substr, and coalesce. Date functions are used for converting dates and calculating dates and include next_day, add_months and months_between. Top (to find the top n values) is one of the analytical functions. LO: 5.4: Write single-table queries using SQL commands. Difficulty: Moderate Classification: Concept AACSB: Information Technology 100) Discuss when to use the GROUP BY clause. Answer: The GROUP BY clause is useful when you have a set of values for one column (such as a salesperson ID) and you would like to then calculate something like total sales for each salesperson. Rather than having to use the sum function with one salesperson's ID in the WHERE clause (and run multiple queries) you can use the GROUP BY to get the same results in one query. LO: 5.4: Write single-table queries using SQL commands. Difficulty: Moderate Classification: Concept AACSB: Information Technology 101) How is the HAVING clause different from the WHERE clause? Answer: While the WHERE clause works on each row in a query resultset, the HAVING clause works on the aggregate (or combined) rows in a GROUP BY. WHERE does not allow aggregates while the HAVING does allow aggregates. For example, if you had the following query: SELECT customer_id, sum(purchase_price*quantity) FROM customer WHERE sum(purchase_price*quantity) > 100 this would not work. However, with a GROUP BY and HAVING written as follows we would get back all customers whose total purchases were greater than $100 SELECT customer_id, sum(purchase_price*quantity) FROM customer GROUP BY customer_id HAVING SUM(purchase_price*quantity) > 100 LO: 5.4: Write single-table queries using SQL commands. Difficulty: Moderate Classification: Concept AACSB: Information Technology 23 102) What are some of the advantages and disadvantages to an SQL standard? Answer: Some of the advantages are: reduced training costs, increased productivity, application portability, application longevity, reduced dependence on a single vendor, and cross-system communication. Some disadvantages include: stifling creativity, difficulty in changing standard, and loss of application portability when adding additional proprietary features. LO: 5.6: Discuss the SQL:1999 and SQL:2016 standards. Difficulty: Difficult Classification: Synthesis AACSB: Information Technology 24