Midterm+Study+Guide

=What you need to know for the Midterm: Midterm 3/7/2013=


 * What is the purpose of a database
 * lists vs database (problems associated with lists e.g modification problems)
 * Know what a relational Database is (what we need to do to a list create a relational database) Know three things a relational database does
 * What is a database application (Functions of database applications)
 * What is SQL? Know some database applications that use SQL
 * Four components of a database system-->explain
 * What is a DBMS (What are the functions of a DBMS)
 * Know referential integrity (An example of referential integrity)
 * Personal Database systems vs Enterprise Database systems (difference and functions of each)
 * Ability to name a few Commercial DBMS products
 * What is an entity and why it is useful
 * What is an attribute
 * Know what a relation is and some characteristics of a relation
 * Difference between a unique and non-unique key
 * What is a composite key and the characteristics of a composite key
 * Know what a candidate key is and a primary key (How to define a Primary key in a table)
 * Know what a surrogate key and foreign key is
 * How do you show the relationship between tables
 * What is a null value
 * What is functional dependency and know what a determiner is
 * What is normalization: 1nf, 2nf, and 3nf
 * What kinds of relationships can one table have to another
 * What is database design, methods, requirements
 * Diagrams for database design
 * Create and running a SQL command
 * How to Create tables and delete tables
 * Know table and column name restrictions
 * Know common datatypes and how to restrict field limits
 * Adding Rows to a table (Insert command)
 * Viewing table data (Select)
 * Importance of the Describe command
 * Using Nulls (Inserting rows with null values
 * Correcting Errors in a table (update, delete, and insert)
 * Know what a query is, and how do you implement it in SQL
 * Sort data using the order by clause (Ascending and Descending order) sort by multiple keys
 * Using select, and the where clause
 * Using comparison operators with the where clause
 * Know how to retrieve certain columns and rows
 * Know how to retrieve all columns and rows
 * Using Simple and Compound Conditions
 * And, OR, and NOT operator
 * Using the between operator
 * Computed Columns using arithmetic operators
 * Using the As Clause
 * Using the Like operator with wildcards
 * Using the In Operator
 * Using aggregate functions (MIN, MAX, COUNT, AVG, SUM)
 * Using the Distinct operator
 * Nesting Queries and Grouping

Samples from test -->inclass
 * **1.** || **What does SQL stand for?** ||


 * || Structured Query Language ||
 * || Strong Question Language ||
 * || Structured Question Language ||


 * **2.** || **Which SQL statement is used to extract data from a database?** ||


 * || EXTRACT ||
 * || SELECT ||
 * || GET ||
 * || OPEN ||


 * **3.** || **Which SQL statement is used to update data in a database?** ||


 * || SAVE AS ||
 * || UPDATE ||
 * || MODIFY ||
 * || SAVE ||


 * **4.** || **Which SQL statement is used to delete data from a database?** ||


 * || REMOVE ||
 * || COLLAPSE ||
 * || DELETE ||


 * **5.** || **Which SQL statement is used to insert new data in a database?** ||


 * || INSERT NEW ||
 * || ADD RECORD ||
 * || ADD NEW ||
 * || INSERT INTO ||


 * **6.** || **With SQL, how do you select a column named "FirstName" from a table named "Persons"?** ||


 * || SELECT Persons.FirstName ||
 * || SELECT FirstName FROM Persons ||
 * || EXTRACT FirstName FROM Persons ||


 * **7.** || **With SQL, how do you select all the columns from a table named "Persons"?** ||


 * || SELECT * FROM Persons ||
 * || SELECT [all] FROM Persons ||
 * || SELECT Persons ||
 * || SELECT *.Persons ||


 * **8.** || **With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?** ||


 * || SELECT * FROM Persons WHERE FirstName='Peter' ||
 * || SELECT * FROM Persons WHERE FirstName<>'Peter' ||
 * || SELECT [all] FROM Persons WHERE FirstName='Peter' ||
 * || SELECT [all] FROM Persons WHERE FirstName LIKE 'Peter' ||


 * **9.** || **With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?** ||


 * || SELECT * FROM Persons WHERE FirstName='%a%' ||
 * || SELECT * FROM Persons WHERE FirstName LIKE '%a' ||
 * || SELECT * FROM Persons WHERE FirstName='a' ||
 * || SELECT * FROM Persons WHERE FirstName LIKE 'a%' ||


 * **10.** || **The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true** ||


 * || False ||
 * || True ||


 * **11.** || **With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?** ||


 * || SELECT FirstName='Peter', LastName='Jackson' FROM Persons ||
 * || SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson' ||
 * || SELECT * FROM Persons WHERE FirstName<>'Peter' AND LastName<>'Jackson' ||


 * **12.** || **With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?** ||


 * || SELECT LastName>'Hansen' AND LastName<'Pettersen' FROM Persons ||
 * || SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen' ||
 * || SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName<'Pettersen' ||


 * **13.** || **Which SQL statement is used to return only different values?** ||


 * || SELECT UNIQUE ||
 * || SELECT DIFFERENT ||
 * || SELECT DISTINCT ||


 * **14.** || **Which SQL keyword is used to sort the result-set?** ||


 * || ORDER ||
 * || SORT BY ||
 * || SORT ||
 * || ORDER BY ||


 * **15.** || **With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?** ||


 * || SELECT * FROM Persons SORT BY 'FirstName' DESC ||
 * || SELECT * FROM Persons ORDER BY FirstName DESC ||
 * || SELECT * FROM Persons ORDER FirstName DESC ||
 * || SELECT * FROM Persons SORT 'FirstName' DESC ||


 * **16.** || **With SQL, how can you insert a new record into the "Persons" table?** ||


 * || INSERT INTO Persons VALUES ('Jimmy', 'Jackson') ||
 * || INSERT ('Jimmy', 'Jackson') INTO Persons ||
 * || INSERT VALUES ('Jimmy', 'Jackson') INTO Persons ||


 * **17.** || **With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?** ||


 * || INSERT ('Olsen') INTO Persons (LastName) ||
 * || INSERT INTO Persons ('Olsen') INTO LastName ||
 * || INSERT INTO Persons (LastName) VALUES ('Olsen') ||


 * **18.** || **How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?** ||


 * || MODIFY Persons SET LastName='Hansen' INTO LastName='Nilsen ||
 * || UPDATE Persons SET LastName='Hansen' INTO LastName='Nilsen' ||
 * || UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen' ||
 * || MODIFY Persons SET LastName='Nilsen' WHERE LastName='Hansen' ||


 * **19.** || **With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?** ||


 * || DELETE FirstName='Peter' FROM Persons ||
 * || DELETE FROM Persons WHERE FirstName = 'Peter' ||
 * || DELETE ROW FirstName='Peter' FROM Persons ||