Appendix C
Answers to Quizzes and Exercises
Hour 1, “Understanding the Relational Database and SQL”
Quiz
1. What does the acronym SQL stand for?
Answer: Structured Query Language
2. What is a schema? Give an example.
Answer: A schema is a collection of database objects owned by a single database user that can be made available to other users in the database. The tables in the
BIRDSdatabase are an example of a schema.3. How do logical and physical components within a relational database differ? How are they related?
Answer: Logical components are used to conceive and model a database (entities and attributes). Physical components are the actual objects, such as tables, that are created based on the database design.
4. What keys are used to define and enforce referential integrity in a relational database?
Answer: Primary and foreign keys
5. What is the most basic type of object in a relational database?
Answer: Table
6. What elements comprise this object?
Answer: Columns
7. Must primary key column values be unique?
Answer: Yes
8. Must foreign key column values be unique?
Answer: No
Exercises
For the following exercises, refer to Figure 1.6 in Hour 1.
1. Who are Mary Smith’s dependents?
Answer: John and Mary Ann
2. How many employees do not have dependents?
Answer: One, Bob Jones
3. How many duplicate foreign key values exist in the
DEPENDENTStable?Answer: Three
4. Who is Tim’s parent or guardian?
Answer: Ron Burk
5. Which employee can be deleted without first having to delete any dependent records?
Answer: Bob Jones
Hour 2, “Exploring the Components of the SQL Language”
Quiz
1. What are the six main categories of SQL commands?
Answer: Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Query Language (DQL)
Data Control Language (DCL)
Data administration commands (DAC)
Transactional control commands (TCC)
2. What is the difference between data administration commands and database administration?
Answer: Data administration commands enable a user to perform audit-type functions on data within the database, whereas database administration involves the overall management of the database and related resources as a whole.
3. What are some benefits to a SQL standard?
Answer: A standard encourages consistency between different vendor-specific implementations of SQL. This enables you to apply your SQL knowledge to various implementations, and data is more portable between different implementations. A standard ensures that core fundamentals exist within a technology. The SQL language itself is not specific to any one implementation.
Exercises
1. Identify the categories for the following SQL commands:
CREATE TABLE DELETE SELECT INSERT ALTER TABLE UPDATE
Answer:
CREATE TABLE—Data Definition Language (DDL)DELETE—Data Manipulation Language (DML)SELECT—Data Query Language (DQL)INSERT—Data Manipulation Language (DML)ALTER TABLE—Data Definition Language (DDL)UPDATE—Data Manipulation Language (DML)2. List the basic SQL statements to manipulate data.
Answer:
INSERT, UPDATE, and DELETE3. List the SQL statement that is used to query a relational database.
Answer:
SELECT4. Which transactional control command is used to save a transaction?
Answer:
COMMIT5. Which transactional control command is used to undo a transaction?
Answer:
ROLLBACK
Hour 3, “Getting to Know Your Data”
Quiz
1. What is the difference between entities and tables?
Answer: An entity is a logical object that is used to represent a table when designing a database. A table is the physical object that an entity becomes—that is, a physical object that contains data.
2. What is the purpose of the
BIRD_FOODentity?Answer: It is a base table that facilitates a relationship between
BIRDSandFOOD.3. How might photograph locations somehow be related to the food that birds eat?
Answer: Some birds are more likely to be found in certain locations and more likely to be photographed where certain food items exist.
4. What does the abbreviation ERD stand for?
Answer: Entity Relationship Diagram
5. How many direct relationships exist between entities in the
Birddatabase?Answer: Nine
6. What is another name for a naming standard?
Answer: Naming convention
Exercises
1. Give an example of an entity or attributes that might be added to this database.
Answer: Predators of birds might be added, listing the predator identification and information about the predator itself. A base table might also be required to facilitate a relationship between this entity and
BIRDS.2. Give some examples of candidates for primary keys, based on Figure 1.2.
Answer:
BIRD_ID,FOOD_ID,MIGRATION_ID,LOCATION_ID3. Give some examples of candidates for foreign keys, based on Figure 1.2.
Answer:
BIRD_IDin the base tables,FOOD_IDin the base table, and so on
Hour 4, “Setting Up Your Database”
Quiz
Refer to the data for the BIRDS database that was listed during this hour.
1. Why is the
BIRDStable split into two separate output sets?Answer: This was done simply for readability so that data did not wrap.
2. Why do errors occur the first time you execute the file
tables.sql?Answer: The first time you run the scripts, no tables or data exist, so the
DROP TABLEstatements in the scripts are not applicable.3. Why are zero rows deleted the first time you execute the file
data.sql?Answer: The first time you run this script, no data exists in the database tables.
4. What must an administrative user do after creating a user before that user can create and manage objects in the database?
Answer: An administrative user must grant the appropriate privileges to the user to create and manage database objects. Those privileges vary among SQL implementations.
5. How many tables are in the
BIRDSdatabase?Answer: Ten, so far.
Exercises
Refer to the data for the BIRDS database that was listed during this hour.
1. Give some examples of parent tables in the
BIRDSdatabase.Answer:
BIRDS,FOOD,MIGRATION2. Give some examples of child tables in the
BIRDSdatabase.Answer:
BIRDS_FOOD,NICKNAMES3. How many unique types of birds are in the database?
Answer: 23
4. What foods does the Bald Eagle eat?
Answer: Fish, carrion, ducks
5. Who builds the most nests, male or female, or both?
Answer: Female
6. How many birds migrate to Central America?
Answer: 12
7. Which bird spends the most time raising its young?
Answer: Mute swan, 190 days
8. Which birds have the term eagle in their nickname?
Answer: Bald Eagle, Golden Eagle
9. What is the most popular migration location for birds in the database?
Answer: Southern United States
10. Which bird(s) has/have the most diverse diet?
Answer: American Crow, Golden Eagle
11. What is the average wingspan of birds that eat fish?
Answer: 52.35 inches
Hour 5, “Understanding the Basics of Relational (SQL) Database Design”
Quiz
1. How is database design related to SQL?
Answer: It is important to understand at least the basics of relational database design so that you can fully unlock the potential of using SQL to understand your data, see how it is related to other data in the database, and know how to effectively work with the data.
2. What diagram is used to model data and relationships for a database? What are fields also called in a physical database?
Answer: Entity relationship diagram (ERD); columns
3. During database design, groups of data (also referred to as entities) become what type of object in the physical database?
Answer: Tables
4. What is the difference between logical and physical design?
Answer: Logical design deals with the initial modeling of data into entities, attributes, and relationships. Physical design takes the logical design to the next step by finalizing referential integrity, defining data structures, and converting entities to tables.
5. What are the three most common database environments that accommodate the database life cycle?
Answer: Development, test, and production
Exercises
1. During the next few hours, you will be designing a database about wildlife photographers who take pictures of birds. This database should be designed so that it can eventually be integrated with the existing
BIRDSdatabase. Take a minute and review the ERD for theBIRDSdatabase. These exercises have no right or wrong solutions; what’s important is the way you interpret the information and envision the data coming together into a database model. Also review the examples during this hour on adding entities to theBIRDSdatabase about bird rescues.Answer: No answer.
2. Read and analyze the following information about photographer data that you will be adding to the
BIRDSdatabase. Consider what the database is about, the purpose of the database, the anticipated users, the potential customers, and so on.All photographers have names, addresses, and education information. They might have received awards and maintain various websites or social media sites. Each photographer also likely has a particular passion, an artistic approach, photographic style, preferred bird targets, and so on. Additionally, photographers use various cameras and lenses and might produce media in a variety of formats using editing software. They have different types of clients, often are published in certain products, and contribute images to publications. Photographers also might be mentors or volunteers for bird rescue groups or other nonprofit organizations. They definitely have varying skill levels—beginner, novice, hobbyist, competent photographer, skilled, artist, and world class. In addition, photographers might market and sell various products, whether they are self-employed or work for an organization.
The equipment photographers use includes cameras, lenses, and editing software. Cameras have a make and model, sensor type (full frame or crop sensor), megapixels, frames per second, ISO range, and cost. Lenses have a make, lens type, aperture range, and cost.
Answer: No answer.
3. Make a list of all the basic entities, or groups of data, of wildlife photographers. This is then your basis for entities, and that eventually becomes your ERD.
Answer: All answers vary, based on how you perceive the information.
4. Draw a basic data model based on the lists that you derived in the previous exercises. Draw lines between the entities in your diagram to depict relationships. This is the starting point for your ERD.
Answer: All answers vary, based on how you perceive the information.
5. Make a list of all the basic attributes within each entity that you defined in the previous exercises, or fields, of wildlife photographers. This is the basis for your entities, and that eventually becomes your ERD.
Answer: All answers vary, based on how you perceive the information.
Hour 6, “Defining Entities and Relationships”
Quiz
1. What are the four basic types of relationships between entities in a relational database?
Answer: One-to-one, one-to-many, many-to-many, and recursive.
2. In which relationship does an attribute relate to another attribute in the same table?
Answer: A recursive relationship
3. What constraints, or keys, are used to enforce referential integrity in a relational database?
Answer: Primary key and foreign key constraints
4. If a primary key represents a parent record, what represents a child record in a relational database?
Answer: A foreign key represents a child record
Exercises
1. Suppose that you came up with a group of data and lists of fields similar to the following for the information on photographers that will be integrated into the
BIRDSdatabase. This baseline data is provided for your convenience so that you can build on it during the next few hours. However, feel free to use any list that you already have derived, or you can combine your list of data with this example. Keep in mind that the solutions that you come up with might vary from the example solutions provided. You will also find throughout this book that although many of your solutions might vary from the book solutions, they still yield the same results, similar results, or even better results, based on how you interpret the data. Also keep in mind that the following data is merely a subset of the data that can be derived from the description provided in Hour 5. Figure 6.12 hints at where you might find a recursive relationship. Complete the other relationships as you see fit.
FIGURE 6.12
Example basic ERD for photographersAnswer: Sample limited lists of fields for photographer data might include the following:
PHOTOGRAPHERS Photographer_Id Photographer_Name Photographer_Contact_Info Education Website Mentor STYLES Style_Id Style CAMERAS Camera_Id Camera_Make Camera_Model Sensor_Type Megapixels Frames_Per_Second ISO_Range Cost LENSES Lens_Id Lens_Make Lens_Type Aperature_Range Cost2. List some possible relationships for the information provided for photographers that will be integrated into the
BIRDSdatabase.Answer: Photographers might have multiple cameras, lenses might be compatible with multiple cameras, and cameras might have multiple lenses available.
3. List the attributes that you anticipate will comprise the primary keys for the entities that you have defined for the photographers.
Answer:
PHOTOGRAPHER_ID,STYLE_ID,CAMERA_ID,LENSE_ID4. List the attributes that you anticipate will comprise the foreign keys for the entities that you have defined for the photographers.
Answer: Any attributes in base entities that you define that are used to join other entities together, such as
PHOTOGRAPHERSandCAMERAS.5. Draw a basic ERD depicting the entities and the relationships between those entities that you envision at this point for the photographer data.
Answer: Solutions will vary, based on how you perceive the information.
6. Using words, describe the two-way relationships between your entities. The types of relationships should already be represented on your ERD from question 5 using the symbols introduced during this hour.
Answer: A photographer might have many cameras, a camera might be used by many photographers, and so on.
7. Refer to Figure 6.4 to answer the remaining questions in this exercise. Some of these questions might seem too simple, but these are the same types of questions you will be asking of the database using SQL commands. Remember that a major goal of this book is to get you to think the way that SQL does.
What are the nicknames of the Great Blue Heron?
Answer: Big Cranky, Blue Crane
What are the nicknames of the Mallard?
Answer: Green Head, Green Cap
Which birds have the word green in their nickname?
Answer: Green Head, Green Cap
Which birds have a nickname that starts with the letter B?
Answer: Great Blue Heron
Which birds do not have a nickname listed in the example?
Answer: Common Loon, Bald Eagle
How many unique birds are listed in this example?
Answer: 4
What is the average number of nicknames per bird in this example?
Answer: 1
Which birds do not have a nickname listed in the example?
Answer: Common Loon, Bald Eagle
Can any birds be deleted from the
BIRDStable without first having to delete nicknames from theNICKNAMEStable?Answer: Yes. The Common Loon and Bald Eagle can be deleted because they do not have any child records in this example.
Which birds have child records in the
NICKNAMEStable?Answer: Great Blue Heron, Mallard
Does any duplicate data exist in either table besides the
BIRD_IDitself?Answer: No.
Hour 7, “Normalizing Your Database”
Quiz
1. True or false: Normalization is the process of grouping data into logical related groups.
Answer: True
2. True or false: Having no duplicate data or redundant data in a database, and having everything in the database normalized, is always the best way to go.
Answer: False. Generally, a normalized database is the best way to go, but levels of normalization vary, based on the data itself and how it is used.
3. True or false: If data is in the third normal form, it is automatically in the first and second normal forms.
Answer: True
4. What is a major advantage of a denormalized database versus a normalized database?
Answer: Performance
5. What are some major disadvantages of denormalization?
Answer: A denormalized database has redundant data, so it is more difficult to enforce referential integrity and even make changes to the database as it evolves.
6. How do you determine whether data needs to be moved to a separate table when normalizing your database?
Answer: If data is not fully dependent on the primary key in a table, it is normally better to move that data into its own entity with its own attributes.
7. What are the disadvantages of overnormalizing your database design?
Answer: Performance degradation
8. Why is it important to eliminate redundant data?
Answer: To protect the integrity of data
9. What is the most common level of normalization?
Answer: Third normal form (TNF)
Exercises
1. Assuming that you came up with a similar configuration to the entities shown in Figure 7.11 for the photographer data set that is to be incorporated into the
BIRDSdatabase, take a minute to compare this example to yours. Feel free to use this example as a baseline for these exercises, use your own, or combine the two as you see fit. Also review Figure 6.12 in Hour 6, “Defining Entities and Relationships,” to envision how this data might be integrated into the originalBIRDSdatabase.
FIGURE 7.11
Example entities and attributes for photographer data2. List some of the redundant data that you see in this example and the ERD you modeled.
Answer: The most potential for redundant data occurs with cameras, styles, and lenses. Each photographer might have many of each of those items, and each of those items could be associated with multiple photographers.
3. Use the guidelines of the first normal form to model your database appropriately.
4. Use the guidelines in this hour to take your data model to the second normal form.
5. Finally, use the guidelines in this hour to take your data model to the third normal form.
6. Verbally describe all relationships in your third normal form model.
Answer: Each photographer might use many different cameras. Each type of camera might be used by many different photographers. Cameras have sensors such as full frame or crop sensor. Each sensor type might be used on many different types of cameras. However, each camera has only one sensor.
7. List all primary and foreign keys from your third normal form model.
Answer: Primary keys:
PHOTOGRAPHERS.PHOTOGRAPHER_ID,PHOTOGRAPHER_ID, andCAMERA_IDtogether comprise a composite primary key inPHOTOGRAPHER_CAMERAS,CAMERAS.CAMERA_ID,CAMERA_SENSORS.SENSOR_ID.Foreign keys:
PHOTOGRAPHER_IDinPHOTOGRAPHER_CAMERASreferencesPHOTOGRAPHER_IDinPHOTOGRAPHERS; CAMERA_IDinPHOTOGRAPHER_CAMERASreferencesCAMERA_IDinCAMERAS; SENSOR_IDinCAMERASreferencesSENSOR_IDinCAMERA_SENSORS.8. Can you envision any other data that could be added to your ERD?
Answer: Solutions will vary, based on how you interpret the information.
Hour 8, “Defining Data Structures”
Quiz
1. What are the three most basic categories of data types?
Answer: Character, numeric, date
2. True or false: An individual’s Social Security number, entered in the format
'111111111', can be any of the following data types: constant-length character, varying-length character, or numeric.Answer: True
3. True or false: The scale of a numeric value is the total length allowed for values.
Answer: False
4. Do all implementations use the same data types?
Answer: No, but most are similar and follow the same basic rules.
5. What are the precision and scale of the following?
DECIMAL(4,2) DECIMAL(10,2) DECIMAL(14,1)
Answer: Precision of 4 and scale of 2 (99.99 maximum value), precision of 10 and scale of 2 (99999999.99), precision of 14 and scale of 1 (9999999999999.9)
6. Which numbers can be inserted into a column whose data type is
DECIMAL(4,1)?16.2116.216.211116.21116.21
Answer: A and B
Exercises
1. Assign the following column titles to a data type, decide on the proper length, and give an example of the data you would enter into that column:
ssnstatecityphone_numberziplast_namefirst_namemiddle_namesalaryhourly_pay_ratedate_hired
Answer: Solutions vary, based on your interpretation.
2. Using the same column titles, decide whether they should be
NULLorNOT NULL. Be sure to realize that, for some columns that would normally beNOT NULL, the column could beNULL,or vice versa, depending on the application.ssnstatecityphone_numberziplast_namefirst_namemiddle_namesalaryhourly_pay_ratedate_hired
Answer: Solutions vary, based on your interpretation.
3. Based on the birds rescue data from previous hours, assign data types and nullability as you see fit.
Answer: Solutions vary, based on your interpretation.
4. Based on the photographer data you have modeled, assign data types and nullability as you see fit.
Answer: Solutions vary, based on your interpretation.
Hour 9, “Creating and Managing Database Objects”
Quiz
1. What is the most common object created in a database to store data?
Answer: A table
2. Can you drop a column from a table?
Answer: Yes, but you cannot drop a column or any table element if the drop violates any predefined data rules or constraints.
3. What statement do you issue to create a primary key constraint on the preceding
BIRDStable?Answer:
ALTER TABLE4. What statement do you issue on the preceding
BIRDStable to allow theWINGSPANcolumn to acceptNULLvalues?Answer:
ALTER TABLE modify WINGSPAN null;5. What statement do you use to restrict the birds added into the preceding
MIGRATIONtable to migrate only to certain migration locations?Answer:
ALTER TABLE MIGRATION ADD CONSTRAINT CHK_LOC CHECK (MIGRATION_LOCATION IN ('LOCATION1', 'LOCATION2');6. What statement do you use to add an autoincrementing column called
BIRD_IDto the precedingBIRDStable using both the MySQL and SQL Server syntax?Answer:
CREATE TABLE BIRDS (BIRD_ID SERIAL, BIRD_NAME VARCHAR(30));
7. What SQL statement can be used to create a copy of an existing table?
Answer:
CREATE TABLE AS SELECT…8. Can you drop a column from a table?
Answer: Yes, but you cannot drop a column or any table element if the drop violates any predefined data rules or constraints.
Exercises
In this exercise, refer to the examples in the previous hours for the BIRDS database, as well as the information provided for rescues and photographers that are integrated into the BIRDS database. At this point, you have been designing entities for photographer data that will be integrated into the BIRDS database. Review what you have come up with so far and use that information for the following exercises.
1. Using the SQL commands that you’ve learned during this hour, create a physical database based on the photographer data that you previously modeled that will be integrated with the
BIRDSdatabase.Answer: Solutions vary by individual.
2. Think about any columns in your tables that can be altered in any way. Use the
ALTER TABLEcommand as an example to change the way one of your tables is defined.Answer: Solutions vary by individual.
3. Have you used SQL and the
CREATE TABLEstatements to define all primary key and foreign key constraints? If not, use theALTER TABLEstatement to define at least one.Answer: Solutions vary by individual.
4. Can you think of any check constraints that you can add to the photographer data? If so, use the
ALTER TABLEstatement to add the appropriate constraints.Answer: Solutions vary by individual.
Hour 10, “Manipulating Data”
Quiz
1. Do you always need to supply a column list for the table that you use an
INSERTstatement on?Answer: You do not have to specify a column list if you are inserting data into all columns in the order in which the columns are listed in the table.
2. What can you do if you do not want to enter a value for one particular column?
Answer: Insert a
NULLvalue by using the keywordNULLor by inserting''(two single quotations with no value between them).3. Why is it important to use a
WHEREclause withUPDATEandDELETE?Answer: If you do not specify criteria with the
WHEREclause, the SQL statement attempts to update or delete all data in the table.4. What is an easy way to check that an
UPDATEorDELETEwill affect the rows that you want?Answer: Perform a simple
SELECTstatement before theUPDATEorDELETEusing the same criteria in theWHEREclause.
Exercises
1. Review the table structures in the
BIRDSdatabase, particularly the data in theBIRDStable itself. You will use this data to perform the exercises.Answer: No solution.
2. Use the
SELECTstatement to display all the data currently in theBIRDStable.Answer: SELECT * FROM BIRDS;
3. Create a new table called
TALL_BIRDSthat is based on theBIRDStable itself, with the following columns:BIRD_ID,BIRD_NAME, andWINGSPAN.Answer:
CREATE TABLE TALL_BIRDS AS SELECT BIRD_ID, BIRD_NAME, WINGSPAN FROM BIRDS;
4. Insert data from the
BIRDStable into theTALL_BIRDStable for birds taller than 30 inches.Answer:
INSERT INTO TALL_BIRDS SELECT BIRD_ID, BIRD_NAME, WINGSPAN FROM BIRDS WHERE HEIGHT > 30;
5. Use the
SELECTstatement to display all the new data in theTALL_BIRDStable.Answer:
SELECT * FROM TALL_BIRDS;6. Insert the following data into the
TALL_BIRDStable:BIRD_NAME=Great EgretHEIGHT=40WINGSPAN=66Answer:
INSERT INTO TALL_BIRDS VALUES (24, 'Great Egret', 66);7. Update every data value in the
BIRDStable for the bird name column to readBird. Was this command successful? Why or why not?Answer:
UPDATED TALL_BIRDS SET BIRD_NAME = 'Bird';
8. Update the wingspan of every bird in the
TALL_BIRDStable to aNULLvalue.Answer:
UPDATE TALL_BIRDS SET WINGSPAN = null;
9. Delete the record for
Great Egretfrom theTALL_BIRDStable.Answer:
DELETE FROM TALL_BIRDS WHERE BIRD_ID = 24;
10. Delete every remaining row of data from the
TALL_BIRDStable.Answer:
DELETE FROM TALL_BIRDS;
11. Drop the
TALL_BIRDStable.Answer:
DROP TABLE TALL_BIRDS;
Hour 11, “Managing Database Transactions”
Quiz
1. True or false: If you have committed several transactions, you have several more transactions that have not been committed, and you issue a
ROLLBACKcommand, all your transactions for the same session are undone.Answer: False. Only the transactions since the last
COMMITorROLLBACKcommand are undone.2. True or false: A
SAVEPOINTorSAVE TRANSACTIONcommand saves transactions after a specified number of transactions have executed.Answer: False. A
SAVEPOINTis only a marker within transactions to provide logical points for aCOMMITorROLLBACK.3. Briefly describe the purpose of each one of the following commands:
COMMIT,ROLLBACK, andSAVEPOINT.Answer:
COMMITsaves work to the database since the lastCOMMITorROLLBACK.ROLLBACKundoes any transactions since the lastCOMMITorROLLBACK.SAVEPOINTcreates logical or periodic markers within transactions.4. What are some differences in the implementation of transactions in Microsoft SQL Server?
Answer: Minor differences in syntax exist, but the concepts are the same.
5. What are some performance implications when using transactions?
Answer: Poor transactional control can hurt database performance and even bring the database to a halt. Repeated poor database performance might result from a lack of transactional control during large inserts, updates, or deletes. Large batch processes also cause temporary storage for rollback information to grow until either a
COMMITor aROLLBACKcommand is issued.6. When using several
SAVEPOINTorSAVE TRANSACTIONcommands, can you roll back more than one?Answer: Yes, you can roll back to previous
SAVEPOINTlocations.
Exercises
1. For the following exercises, create the following tables based on the
BIRDSdatabase:Use the SQL statement
CREATE TABLE table_name AS SELECT...to create a table calledBIG_BIRDSthat is based on the originalBIRDStable. Include only the following columns in theBIG_BIRDStable:BIRD_ID,BIRD_NAME,HEIGHT,WEIGHT, andWINGSPAN. Use theWHEREclause to include only records for birds that have a wingspan greater than 48 inches.CREATE TABLE BIG_BIRDS AS SELECT BIRD_ID, BIRD_NAME, HEIGHT, WEIGHT, WINGSPAN FROM BIRDS WHERE WINGSPAN > 48;
Create a table called
LOCATIONS2that is based on the originalLOCATIONStable.CREATE TABLE LOCATIONS2 AS SELECT * FROM LOCATIONS;
2. Write a simple query to display all records in the
BIG_BIRDStable, to familiarize yourself with the data.Answer:
SELECT * FROM BIG_BIRDS;
3. Write a simple query to display all records in the
LOCATIONS2table, to familiarize yourself with the data.Answer:
SELECT * FROM LOCATIONS2;
4. Modify the
BIG_BIRDStable to change the name of the columnWINGSPANtoAVG_WINGSPAN.Answer:
DROP TABLE BIG_BIRDS; CREATE TABLE BIG_BIRDS AS SELECT BIRD_ID, BIRD_NAME, HEIGHT, WEIGHT, WINGSPAN "AVG_WINGSPAN" FROM BIRDS WHERE WINGSPAN > 48;
5. Manually compute the average wingspan of birds in the
BIG_BIRDStable, and use theUPDATEstatement to update the value of all birds’ wingspans to the average wingspan value that you calculated.Answer:
SELECT AVG(WINGSPAN) FROM BIG_BIRDS; UPDATE BIG_BIRDS SET AVG_WINGSPAN = 73;
6. Issue the
ROLLBACKcommand.Answer:
ROLLBACK;
7. Query all data in the
BIG_BIRDStable using theSELECTstatement. You should see in the output from the query that all the values forWINGSPANhave been restored to their original values; however, the name of the column is still the updated value ofAVG_WINGSPAN.Answer:
SELECT * FROM BIG_BIRDS;
8. Why did the
ROLLBACKnegate the update to data values in theAVG_WINGSPANcolumn, but not theUPDATE TABLEstatement to rename theWINGSPANcolumn?Answer: Because a previous
COMMITwas not performed9. Insert a new row of data into the
LOCATIONS2table for a location calledLake Tahoe.Answer:
INSERT INTO LOCATIONS2 VALUES (7, 'Lake Tahoe');
10. Issue the
COMMITcommand.Answer:
COMMIT;
11. Query the
LOCATIONS2table to verify the changes you made.Answer:
SELECT * FROM LOCATIONS;
12. Insert another new row of data into the
LOCATIONS2table for a location ofAtlantic Ocean.Answer:
INSERT INTO LOCATIONS2 VALUES (8, 'Atlantic Ocean');
13. Create a
SAVEPOINTcalled SP1.Answer:
SAVEPOINT SP1;
14. Update the value of
Atlantic OceantoPacific Ocean.Answer:
UPDATE LOCATIONS2 SET LOCATION_NAME = 'Pacific Ocean' WHERE LOCATION_NAME = 'Atlantic Ocean';
15. Create a
SAVEPOINTcalledSP2.Answer:
SAVEPOINT SP2;
16. Update the value of
Lake Tahoethat you previously added toLake Erie.Answer:
UPDATE LOCATIONS2 SET LOCATION_NAME = 'Lake Erie WHERE LOCATION_NAME = 'Lake Tahoe;
17. Create a
SAVEPOINTcalledSP3.Answer:
SAVEPOINT SP3;
18. Issue the
ROLLBACKcommand back toSAVEPOINT SP2.Answer:
ROLLBACK;
19. Query the
LOCATIONS2table and study the behavior of theROLLBACKcommand toSAVEPOINT.Answer:
SELECT * FROM LOCATIONS2;
20. Get creative with some transactions of your own on these two tables. Remember that these tables are copies of the original tables, so anything you do should not affect the original data. Also remember that, at any point during your progression through this book, you can rerun the scripts provided called
tables.sqland, subsequently,data.sqlto restore your tables and the data for theBIRDSdatabase back to its original state.Answer: No solution
Hour 12, “Introduction to Database Queries”
Quiz
1. Name the required parts for any
SELECTstatement.Answer:
SELECTandFROM2. In the
WHEREclause, are single quotation marks required for all the data?Answer: Single quotation marks are not required for numeric data.
3. Can multiple conditions be used in the
WHEREclause?Answer: Yes
4. Is the
DISTINCToption applied before or after theWHEREclause?Answer: After
5. Is the
ALLoption required?Answer: No
6. How are numeric characters treated when ordering based on a character field?
Answer: From 0 to 9
7. How does Oracle handle its default case sensitivity differently from Microsoft SQL Server?
Answer: By default, Oracle is case sensitive when testing data, whereas Microsoft SQL Server is not, by default.
8. How is the ordering of the fields in the
ORDER BYclause important?Answer: The
ORDER BYclause provides a final sort for the data that a query returns.9. How is the ordering determined in the
ORDER BYclause when you use numbers instead of column names?Answer: Numbers in the
ORDER BYclause are shorthand for the position of columns in theSELECTclause.
Exercises
1. Write a query that tells you how many birds are stored in the database.
Answer:
select count(*) from birds;
2. How many types of nest builders exist in the database?
Answer:
select count(distinct(nest_builder)) from birds;
3. Which birds lay more than seven eggs?
Answer:
select bird_name from birds where eggs > 7;
4. Which birds have more than one brood per year?
Answer:
select bird_name from birds where broods > 1;
5. Write a query from the
BIRDStable showing only the bird’s name, the number of eggs the bird typically lays, and the incubation period.Answer:
select bird_name, eggs, incubation from birds;
6. Modify the previous query in exercise 5 to show only the birds that have a wingspan greater than 48 inches.
Answer:
select bird_name, eggs, incubation from birds where wingspan > 48;
7. Sort the previous query by
WINGSPANin ascending order.Answer:
select bird_name, eggs, incubation from birds where wingspan > 48 order by wingspan;
8. Sort the previous query by
WINGSPANin descending order, to show the biggest birds first.Answer:
select bird_name, eggs, incubation from birds where wingspan > 48 order by wingspan desc;
9. How many nicknames are stored in the database?
Answer:
select count(*) from nicknames;
10. How many different food items are stored in the database?
Answer:
select count(*) from food;
11. Using the manual process described in this hour, determine which food items the Bald Eagle consumes.
Answer:
FOOD_NAME ---------- Fish Carrion Ducks
12. Bonus exercise: Using a manual process and simple SQL queries, provide a list of birds that eat fish.
Answer:
BIRD_NAME ------------------------------ Great Blue Heron Common Loon Bald Eagle Golden Eagle Osprey Belted Kingfisher Common Sea Gull Ring-billed Gull Double-crested Cormorant Common Merganser American Crow Green Heron Brown Pelican Great Egret Anhinga Black Skimmer
Hour 13, “Using Operators to Categorize Data”
Quiz
1. True or false: When using the
ORoperator, both conditions must beTRUEfor data to be returned.Answer: False
2. True or false: All specified values must match when using the
INoperator for data to be returned.Answer: False
3. True or false: The
ANDoperator can be used in theSELECTand theWHEREclauses.Answer: False
4. True or false: The
ANYoperator can accept an expression list.Answer: True
5. What is the logical negation of the
INoperator?Answer:
NOT IN6. What is the logical negation of the
ANYandALLoperators?Answer:
<> ANY,<> ALL
Exercises
1. Use the original
BIRDSdatabase for these exercises. Write aSELECTstatement from theBIRDStable to return all rows of data, to familiarize yourself with the data. Then write the appropriateSELECTstatements using the operators you learned in this chapter for the remaining exercises.Answer:
select * from birds;
2. Which birds have more than two broods per year?
Answer:
select bird_name from birds where broods > 2;
3. Show all records in the
MIGRATIONStable in which theMIGRATION_LOCATIONis not Mexico.Answer:
select * from migration where migration_location != 'Mexico';
4. List all birds that have a wingspan less than 48 inches.
Answer:
select bird_name, wingspan from birds where wingspan < 48;
5. List all birds that have a wingspan greater than or equal to 72 inches.
Answer:
select bird_name, wingspan from birds where wingspan >= 72;
6. Write a query to return the
BIRD_NAMEandWINGSPANof birds that have a wingspan between 30 and 70 inches.Answer:
select bird_name,wingspan from birds where wingspan between 30 and 70;
7. Select all migration locations that are in Central America and South America.
Answer:
select migration_location from migration where migration_location in ('Central America', 'South America');8. List all birds by name that have the word green in their name.
Answer:
select bird_name from birds where bird_name like '%Green%';
9. List all birds that begin with the word bald.
Answer:
select bird_name from birds where bird_name like 'Bald%';
10. Do any birds have a wingspan less than 20 inches or a height shorter than 12 inches?
Answer:
select bird_name from birds where wingspan < 20 or height < 12;11. Do any birds have a weight more than 5 pounds and a height shorter than 36 inches?
Answer:
select bird_name from birds where weight > 5 and height < 36;
12. List all bird names that do not have the word green in their name.
Answer:
select bird_name from birds where bird_name not like '%Green%';
13. List all bird names that have one of the three primary colors in their name.
Answer:
select bird_name from birds where bird_name like '%Red%' or bird_name like '%Blue%' or bird_name like '%Yellow%';
14. How many birds spend more than 75 days total with their young?
Answer:
select bird_name, incubation + fledging from birds where incubation + fledging > 75;
15. Experiment with some of your own queries using the operators you learned in this chapter.
Hour 14, “Joining Tables in Queries”
Quiz
1. What type of join do you use to return records from one table, regardless of the existence of associated records in the related table?
Answer: Outer join
2. The
JOINconditions are located in which parts of the SQL statement?Answer: The
WHEREclause3. What type of
JOINdo you use to evaluate equality among rows of related tables?Answer: Equijoin
4. What happens if you select from two different tables but fail to join the tables?
Answer: All possible combinations of rows of data are joined between all tables, which is called a Cartesian product.
Exercises
1. Type the following code into the database and study the result set (Cartesian product):
Answer:
select bird_name, migration_location from birds, migration;
2. Now modify the previous query with a proper table join to return useful data and avoid the Cartesian product. You might have to review the ERD for the
BIRDSdatabase in Hour 3 to refresh your memory on how these two tables are related to one another.Answer:
select bird_name, migration_location from birds, migration, birds_migration where birds.bird_id = birds_migration.bird_id and migration.migration_id = birds_migration.migration_id;3. Generate a list of the food items eaten by the Great Blue Heron.
Answer:
select b.bird_name, f.food_name from birds b, birds_food bf, food f where b.bird_id = bf.bird_id and bf.food_id = f.food_id and b.bird_name = 'Great Blue Heron';4. Which birds in the database eat fish?
Answer:
select b.bird_name, f.food_name from birds b, birds_food bf, food f where b.bird_id = bf.bird_id and bf.food_id = f.food_id and f.food_name = 'Fish';5. Create a report showing the
BIRD_NAMEandMIGRATION_LOCATIONfor birds that migrate to South America.Answer:
select b.bird_name, m.migration_location from birds b, birds_migration bm, migration m where b.bird_id = bm.bird_id and bm.migration_id = m.migration_id and m.migration_location = 'South America';6. Do any birds have a wingspan less than 30 inches and also eat fish?
Answer:
select b.bird_name, b.wingspan, f.food_name from birds b, birds_food bf, food f where b.bird_id = bf.bird_id and bf.food_id = f.food_id and f.food_name = 'Fish' and b.wingspan < 30;7. Write a query to display the following results: the
BIRD_NAME,FOOD_NAME, andNEST_TYPEfor any birds that eat fish or build a platform nest.Answer:
select b.bird_name, f.food_name, n.nest_name from birds b, birds_food bf, food f, birds_nests bn, nests n where b.bird_id = bf.bird_id and bf.food_id = f.food_id and b.bird_id = bn.bird_id and bn.nest_id = n.nest_id and (f.food_name = 'Fish' or n.nest_name = 'Platform');8. Ask some questions you would anticipate database users, photographers, bird rescues, and so forth might inquire about the
BIRDSdatabase. Experiment with some of your own queries using table joins.
Hour 15, “Restructuring the Appearance of Data”
Quiz
1. Match the descriptions with the possible functions.
Answer:
Description
Function
a. Selects a portion of a character string
SUBSTRb. Trims characters from either the right or the left of a string
LTRIM/RTRIMc. Changes all letters to lower case
LOWERd. Finds the length of a string
LENGTHe. Combines strings
||2. True or false: Using functions in a
SELECTstatement to restructure the appearance of data in output also affects the way the data is stored in the database.Answer: False
3. True or false: The outermost function is always resolved first when functions are embedded within other functions in a query.
Answer: False. The innermost function is always resolved first when embedding functions within one another.
Exercises
1. Write a query to select (display) the word
Somewherefor everyMIGRATION_LOCATIONin theMIGRATION_LOCATIONStable.Answer:
select 'Somewhere' from migration;
2. Write a query to produce results for every bird in the
BIRDStable that looks like the following:The Bald Eagle eats Fish. The Bald Eagle eats Mammals. Etc.
Answer:
select 'The ' || b.bird_name || ' eats ' || f.food_name ||'.' from birds b, birds_food bf, food f where b.bird_id = bf.bird_id and bf.food_id = f.food_id order by b.bird_name;3. Write a query to convert all nicknames to upper case.
Answer:
select upper(nickname) from nicknames;
4. Use the
REPLACEfunction to replace the occurrence of everyMIGRATION_LOCATIONthat has the wordUnited Statesin it withUS.Answer:
select replace(migration_location, 'United States', 'US') from migration;
5. Write a query using the
RPADfunction to produce output to display every numeric column in theBIRDStable as a character, or left-justify.Answer:
select bird_name, rpad(height, 6, ' ') height, rpad(weight, 6, ' ') weight, rpad(wingspan, 8, ' ') wingspan, rpad(eggs, 4, ' ') eggs, rpad(broods, 6, ' ') broods, rpad(incubation, 10, ' ') incubation, rpad(fledging, 8, ' ') fledging from birds;6. Write a query to produce the following results for types of herons in the
BIRDStable.Answer:
BIRD_NAME TYPE OF HERON ------------------------------ ----------------------- Great Blue Heron Great Blue Green Heron Green 2 rows selected. select bird_name, ltrim(bird_name, ' ') "TYPE OF HERON" from birds where bird_name like '%Heron%';7. Experiment with the functions in this hour on your own. Trial and error is a good way to learn with queries because you do not affect any data that is actually stored in the database.
Hour 16, “Understanding Dates and Times”
Quiz
1. Where are the system date and time normally derived from?
Answer: The system date and time are derived from the current date and time of the operating system on the host machine.
2. What are the standard internal elements of a
DATETIMEvalue?Answer: Year, month, day, time of day, day of week, and so forth
3. What is a major factor for international organizations when representing and comparing date and time values?
Answer: The time zone
4. Can a character string date value be compared to a date value defined as a valid
DATETIMEdata type?Answer: Yes, if a
TO_DATEconversion function is used5. What do you use in SQL Server and Oracle to get the current date and time?
Answer: The system date, such as:
SELECT SYSDATE FROM DUAL;(Oracle)
Exercises
1. Type the following SQL code into the
sqlprompt to display the current date from the database:SELECT SYSDATE FROM DUAL;2. Create the
PHOTOGRAPHERStable and insert the data shown at the beginning of this hour for these exercises.Answer:
create table photographers2 (p_id number(3) not null primary key, photographer varchar(30) not null, mentor_p_id number(3) null, dob date not null, dt_start_photo date not null, constraint p2_fk1 foreign key (mentor_p_id) references photographers2 (p_id)); insert into photographers2 values ( 7, 'Ryan Notstephens' , null, '07-16-1975', '07-16-1989'); insert into photographers2 values ( 8, 'Susan Willamson' , null, '12-03-1979', '02-22-2016'); insert into photographers2 values ( 9, 'Mark Fife' , null, '01-31-1982', '12-25-2000'); insert into photographers2 values ( 1, 'Shooter McGavin' , null, '02-24-2005', '01-01-2019'); insert into photographers2 values ( 2, 'Jenny Forest' , 8, '08-15-1963', '08-16-1983'); insert into photographers2 values ( 3, 'Steve Hamm' , null, '09-14-1969', '01-01-2000'); insert into photographers2 values ( 4, 'Harry Henderson' , 9, '03-22-1985', '05-16-2011'); insert into photographers2 values ( 5, 'Kelly Hairtrigger' , 8, '01-25-2001', '02-01-2019'); insert into photographers2 values ( 6, 'Gordon Flash' , null, '09-14-1971', '10-10-2010'); insert into photographers2 values ( 10, 'Kate Kapteur' , 7, '09-14-1969', '11-07-1976'); commit;
3. Write a query to display all the data in the
PHOTOGRAPHERStable that you just created.Answer:
select * from photographers2;4. Calculate your own age in a query using the system date.
Answer:
select round((sysdate - to_date('09-14-1969','mm-dd-yyyy'))/365) age from dual;5. Display the day of the week only that each photographer was born.
Answer:
select photographer, to_char(dob, 'Day') "DAY OF BIRTH" from photographers2;
6. What is the age of Harry Henderson (rounded, of course, unless you just need to know)?
Answer:
select round((sysdate - dob)/365) age from photographers2 where photographer = 'Harry Henderson';
7. Which photographer has been taking photos the longest?
Answer:
select photographer, round((sysdate - dt_start_photo)/365) "YEARS TAKING PHOTOS" from photographers2 order by 2 desc;8. Were any photographers born on the same day?
Answer:
select photographer, to_char(dob, 'Day') "DAY OF BIRTH" from photographers2 order by 2;
9. Which photographers might have started taking photos because of a New Year’s resolution?
Answer:
select photographer from photographers2 where to_char(dt_start_photo, 'Mon dd') = 'Jan 01';
10. Write a query to determine today’s Julian date (day of year).
Answer:
select to_char(sysdate, 'DDD') from dual;
11. What is the combined age of all the photographers in the database?
Answer:
select round(sum((sysdate - dob)/365)) "COMBINED AGES OF ALL PHOTOGRAPHERS" from photographers2;
12. Which photographer started taking photos at the youngest age?
Answer:
select photographer, round((dt_start_photo - dob)/365) "AGE STARTED TAKING PHOTOS" from photographers2 order by 2;13. Have some fun and come up with some queries of your own on this database or simply using the system date.
Hour 17, “Summarizing Data Results from a Query”
Quiz
1. True or false: The
AVGfunction returns an average of all rows from aSELECTcolumn, including anyNULLvalues.Answer: False. Columns with
NULLvalues are not evaluated.2. True or false: The
SUMfunction adds column totals.Answer: True
3. True or false: The
COUNT(*)function counts all rows in a table.Answer: True
4. True or false: The
COUNT([column name])function countsNULLvalues.Answer: False. A count of non-
NULLvalues found in a column is returned.5. Do the following
SELECTstatements work? If not, what fixes the statements?SELECT COUNT *FROM BIRDS;No; parentheses must enclose the
*:SELECT COUNT(*)FROM BIRDS;-
SELECT COUNT(BIRD_ID), BIRD_NAME FROM BIRDS;
Yes
-
SELECT MIN(WEIGHT), MAX(HEIGHT) FROM BIRDS WHERE WINGSPAN > 48;
Yes
-
SELECT COUNT(DISTINCT BIRD_ID) FROM BIRDS;
No; another set of parentheses must enclose
BIRD_ID:SELECT COUNT(DISTINCT(BIRD_ID)) FROM BIRDS;
-
SELECT AVG(BIRD_NAME) FROM BIRDS;
No; you cannot calculate an average on a non-numeric column.
6. What is the purpose of the
HAVINGclause, and which other clause is it closest to?Answer: The
HAVINGclause places criteria for data returned from groups as defined in theGROUP BYclause.HAVINGhas a function similar to theWHERE BYclause.7. True or false: You must also use the
GROUP BYclause when using theHAVINGclause.Answer: True. The
HAVINGclause is applied to data returned by theGROUP BYclause.8. True or false: The columns selected must appear in the
GROUP BYclause in the same order.Answer: True
9. True or false: The
HAVINGclause tells theGROUP BYclause which groups to include.Answer: True
Exercises
1. What is the average wingspan of birds?
Answer:
select avg(wingspan) from birds;
2. What is the average wingspan of birds that eat fish?
Answer:
select avg(b.wingspan) from birds b, birds_food bf, food f where b.bird_id = bf.bird_id and bf.food_id = f.food_id and f.food_name = 'Fish';3. How many different types of food does the Common Loon eat?
Answer:
select count(bf.food_id) from birds b, birds_food bf where b.bird_id = bf.bird_id and b.bird_name = 'Common Loon';4. What is the average number of eggs per type of nest?
Answer:
select n.nest_name, avg(b.eggs) from birds b, birds_nests bn, nests n where b.bird_id = bn.bird_id and bn.nest_id = n.nest_id group by n.nest_name;5. What is the lightest bird?
Answer:
select min(weight) from birds; select bird_name from birds where weight = "weight from previous query";
6. Generate a list of birds that are above average in all the following areas: height, weight, and wingspan.
Answer:
select avg(wingspan) from birds; select avg(height) from birds; select avg(weight) from birds; select bird_name from birds where wingspan > "value from previous query" and height > "value from previous query" and weight > "value from previous query";
7. Write a query to generate a list of all migration locations and their average wingspans, but only for locations of birds that have an average wingspan greater than 48 inches.
Answer:
select m.migration_location, avg(b.wingspan) from migration m, birds_migration bm, birds b where m.migration_id = bm.migration_id and bm.bird_id = b.bird_id group by m.migration_location having avg(b.wingspan) > 48;8. Write a query showing a list of all photographers and the number of photographers mentored by each photographer.
Answer:
select p2b.photographer, count(p2a.photographer) from photographers2 p2a, photographers2 p2b where p2a.mentor_p_id(+) = p2b.p_id group by p2b.photographer;9. Experiment on your own using aggregate functions, along with other functions that you learned in previous hours.
Hour 18, “Using Subqueries to Define Unknown Data”
Quiz
1. What is the function of a subquery when used with a
SELECTstatement?Answer: A subquery returns a set of values that are substituted into the
WHEREclause of a query.2. Can you update more than one column when using the
UPDATEstatement with a subquery?Answer: Yes
3. Can you embed subqueries within other subqueries?
Answer: Yes
4. What is a subquery called that has a column related to a column in the main query?
Answer: Correlated subquery
5. Can you embed subqueries within other subqueries?
Answer: Yes
6. What is an example of an operator that cannot be used when accessing a subquery?
Answer:
BETWEEN
Exercises
1. Write a query with a subquery to create a list of birds and their wingspans for birds that have a wingspan less than the average wingspan in the
BIRDStable.Answer:
select bird_name, wingspan from birds where wingspan < (select avg(wingspan) from birds);2. Produce a list of birds and their associated migration locations for only birds that migrate to locations that have birds migrating there with an above average wingspan.
Answer:
select b.bird_name, m.migration_location from birds b, birds_migration bm, migration m where b.bird_id = bm.bird_id and bm.migration_id = m.migration_id and b.wingspan > (select avg(wingspan) from birds);3. Use a subquery to find any food items that are eaten by the shortest bird in the database.
Answer:
select b.bird_name, f.food_name from birds b, birds_food bf, food f where b.bird_id = bf.bird_id and bf.food_id = f.food_id and b.height = (select min(height) from birds);4. Applying the concept of a subquery, create a new table called
BIRD_APPETIZERSbased on the following information: This new table should list theFOOD_IDandFOOD_NAME, but only for food items associated with birds that are in the bottom 25 percentile of height.Answer:
create table bird_appetizers as select food_id, food_name from food where food_id in (select bf.food_id from birds_food bf, birds b where bf.bird_id = b.bird_id and b.height < (select avg(height) * .5 from birds)); select * from bird_appetizers;
Hour 19, “Combining Multiple Queries into One”
Quiz
1. Match the correct operator to the following statements:
Statement
Operator
a. Show duplicates
UNIONb. Return only rows from the first query that match those in the second query
INTERSECTc. Return no duplicates
UNION ALLd. Return only rows from the first query not returned by the second
EXCEPTAnswer:
UNION ALLINTERSECTUNIONEXCEPT(andMINUS)
2. How many times can
ORDER BYbe used in a compound query?Answer: Once
3. How many times can
GROUP BYbe used in a compound query?Answer: Once for each
SELECTin the compound query4. How many times can
HAVINGbe used in a compound query?Answer: Once for each
SELECTin the compound query5. Consider a query that uses the
EXCEPT(orMINUS) operator. Suppose that the firstSELECTstatement returns 10 rows of distinct rows, and the secondSELECTstatement returns 4 distinct rows of data. How many rows of data are returned in the final result set of the compound query?Answer: 6 rows
Exercises
1. Create a table using the following SQL code; then write a query to select all records from the table.
SQL> create table birds_menu as 2 select b.bird_id, b.bird_name, 3 b.incubation + b.fledging parent_time, 4 f.food_name 5 from birds b, 6 food f, 7 birds_food bf 8 where b.bird_id = bf.bird_id 9 and bf.food_id = f.food_id 10 and f.food_name in ('Crustaceans', 'Insects', 'Seeds', 'Snakes') 11 order by 1;Answer:
select * from birds_menu;
2. Issue the following queries and study the results. The first query selects the bird’s name from the new table for birds whose parenting time exceeds 85 days. The second query is for those birds in the new table whose parenting time is less than or equal to 85 days. The third query combines both queries using the
UNIONoperator.SQL> select bird_name 2 from birds_menu 3 where parent_time > 85 4 order by 1; SQL> select bird_name 2 from birds_menu 3 where parent_time <= 85 4 order by 1; SQL> select bird_name 2 from birds_menu 3 where parent_time > 85 4 UNION 5 select bird_name 6 from birds_menu 7 where parent_time <= 85 8 order by 1;
3. Issue the following SQL statement to practice with the previous query using the
UNION ALLoperator, and compare the results to the previous result set.SQL> select bird_name 2 from birds_menu 3 where parent_time > 85 4 UNION ALL 5 select bird_name 6 from birds_menu 7 where parent_time <= 85 8 order by 1;
4. Issue the following SQL statement to practice with the
INTERSECToperator, and compare the results to the base data In theBIRDS_MENUtable.SQL> select bird_name 2 from birds_menu 3 INTERSECT 4 select bird_name 5 from birds_menu 6 where food_name in ('Insects', 'Snakes') 7 order by 1;5. Issue the following SQL statement to practice with the
MINUSoperator, and compare the results to the base data in theBIRDS_MENUtable.SQL> select bird_name 2 from birds_menu 3 MINUS 4 select bird_name 5 from birds_menu 6 where food_name in ('Insects', 'Snakes') 7 order by 1;6. Issue the following SQL statement to return a count of the number of food items eaten by each bird in the
BIRDS_MENUtable.SQL> select bird_name, count(food_name) 2 from birds_menu 3 group by bird_name;
7. Issue the following SQL statement to using aggregate functions in a compound query. Study the results closely.
SQL> select bird_name, count(food_name) 2 from birds_menu 3 where parent_time > 100 4 group by bird_name 5 UNION 6 select bird_name, count(food_name) 7 from birds_menu 8 where parent_time < 80 9 group by bird_name;
8. Experiment with some compound queries on your own using the new table you created during these exercises, or for any tables in the
BIRDSdatabase, or for any tables you have created thus far.
Hour 20, “Creating and Using Views and Synonyms”
Quiz
1. Can you delete a row of data from a view that you created from multiple tables?
Answer: No
2. When creating a table, the owner is automatically granted the appropriate privileges on that table. Is this true when creating a view?
Answer: Yes
3. Which clause orders data when creating a view?
Answer:
GROUP BYand, in some implementations,ORDER BY4. Do Oracle and SQL Server handle the capability to order a view in the same way?
Answer: They both can achieve a sort with
GROUP BY, but Oracle does not allow the use ofORDER BY.5. Which option can you use when creating a view from a view to check integrity constraints?
Answer:
WITH CHECK OPTION6. You try to drop a view and receive an error because of one or more underlying views. What must you do to drop the view?
Answer: Drop the underlying views or any dependencies
Exercises
1. Write a SQL statement to create a view based on the total contents of the
BIRDStable. Select all data from your view.Answer:
create or replace view more_birds as select * from birds; select * from more_birds;
2. Write a SQL statement that creates a summarized view containing the average wingspan of birds in each migration location. Select all data from your view.
Answer:
create or replace view migration_view as select m.migration_location, avg(b.wingspan) avg_wingspan from migration m, birds_migration bm, birds b where m.migration_id = bm.migration_id and bm.bird_id = b.bird_id group by m.migration_location; select * from migration_view;3. Query your view to return only the migration locations that are above average in the average wingspan category.
Answer:
select * from migration_view where avg_wingspan > (select avg(avg_wingspan) from migration_view);4. Drop your view.
Answer:
drop view migration_view;
5. Create a view called
FISH_EATERSfor only those birds that eat fish. Select all data fromFISH_EATERS.Answer:
create or replace view fish_eaters as select b.bird_id, b.bird_name from birds b, birds_food bf, food f where b.bird_id = bf.bird_id and bf.food_id = f.food_id and f.food_name = 'Fish'; select * from fish_eaters;6. Write a query joining your
FISH_EATERSview to theMIGRATIONtable, to return only migration locations for birds that eat fish.Answer:
select m.migration_location from migration m, birds_migration bm, fish_eaters f where m.migration_id = bm.migration_id and bm.bird_id = f.bird_id;7. Create a synonym for your
FISH_EATERSview, and then write a query using the synonym.Answer:
create synonym eat_fish for fish_eaters; select * from eat_fish;
8. Experiment with some views of your own. Try joining views and tables, and employ some SQL functions that you previously learned.
Hour 21, “Managing Database Users and Security”
Quiz
1. Which command establishes a session?
Answer:
CONNECT2. Which option drops a schema that still contains database objects?
Answer:
DROP SCHEMAwith theCASCADEoption3. Which statement removes a database privilege?
Answer:
REVOKE4. Which command creates a grouping or collection of tables, views, and privileges?
Answer:
CREATE SCHEMA(orCREATE USER), with the appropriate privileges, creates a user that can create objects in a database, which is a schema.5. What option must a user have to grant another user privileges on an object that the user does not own?
Answer:
WITH GRANT OPTION6. When privileges are granted to
PUBLIC, do all database users acquire the privileges or only specified users?Answer:
PUBLICapplies to all database users.7. What privilege is required to look at data in a specific table?
Answer:
SELECT8. What type of privilege is
SELECT?Answer: The capability to query data from a database object. It is an object-level privilege.
9. What option revokes a user’s privilege to an object, as well as the other users that they might have granted privileges to, by use of the
GRANToption?Answer: The
CASCADEoption
Exercises
1. Describe how you would create the new user
Johnin yoursampledatabase.Answer: Use the
CREATE USERcommand2. Explain the steps you would take to grant access to the
BIRDStable to your new user,John.Answer: Use the
GRANTcommand.3. Describe how you would assign permissions to all objects within the
BIRDSdatabase toJohn.Answer: Use the
GRANTcommand4. Describe how you would revoke the previous privileges from
Johnand then remove his account.Answer: Use the
REVOKEcommand.5. Create a new database user as follows:
Username: Steve Password: Steve123
Answer:
CREATE USER STEVE IDENTIFIED BY STEVE123;
6. Create a role for your new database user,
Steve, from the previous exercise. Call the rolebird_queryand give the roleSELECTon just theBIRDStable. AssignSteveto this role.Answer:
CREATE ROLE BIRD_QUERY; GRANT SELCT TO BIRD_QUERY ON BIRDS; GRANT BIRD_QUERY TO STEVE;
7. Connect as Steve and query the
BIRDStable. Be sure to qualify theBIRDStable because Steve is not the owner (owner.table_name).Answer:
CONNECT STEVE SELECT * FROM RYAN.BIRDS;
8. Connect back as your original user.
Answer:
CONNECT RYAN
9. Now revoke Steve’s
SELECTaccess from the other tables in the database. Connect to the database as Steve, and try to select from theEMPLOYEES,AIRPORTS, andROUTEStables. What happened?Answer:
revoke select on birds from steve;
10. Connect to the database once again as Steve and try to query the
BIRDStable.Answer:
connect steve select * from ryan.birds;
11. Experiment on your own with your database.
Hour 22, “Using Indexes to Improve Performance”
Quiz
1. What are some major disadvantages of using indexes?
Answer: Major disadvantages of an index include slowed batch jobs, storage space on the disk, and maintenance upkeep on the index.
2. Why is the order of columns in a composite index important?
Answer: Because query performance is improved by putting the column with the most restrictive values first
3. Should a column with a large percentage of
NULLvalues be indexed?Answer: No. A column with a large percentage of
NULLvalues should not be indexed because the speed of accessing these rows degrades when the value of a large percentage of rows is the same.4. Is the main purpose of an index to stop duplicate values in a table?
Answer: No. The main purpose of an index is to enhance data retrieval speed; although a unique index stops duplicate values in a table.
5. True or false: The main reason for a composite index is for aggregate function usage in an index.
Answer: False. The main reason for composite indexes is for two or more columns in the same table to be indexed.
6. What does cardinality refer to? What is considered a column of high cardinality?
Answer: Cardinality refers to the uniqueness of the data within a column. The
SSNcolumn is an example of such a column.
Exercises
1. For the following situations, decide whether an index should be used and, if so, what type of index should be used:
Several columns, but a rather small table.
Answer: No
Medium-sized table, no duplicates allowed
Answer: No
Several columns, very large table, several columns used as filters in the
WHEREclauseAnswer: Yes
Large table, many columns, a lot of data manipulation
Answer: No
2. Write a SQL statement to create an index called
FOOD_IDXinFOODon theFOOD_NAMEcolumn.Answer:
create index food_idx on food (food_name);
3. Write a SQL statement to create an index called
WINGSPAN_IDXinBIRDSon theWINGSPANcolumn.Answer:
create index wingspan_idx on birds (wingspan);
4. Drop the indexes you created.
Answer:
drop index food_idx; drop index wingspan_idx;
5. Study the tables used in this book. List some good candidates for indexed columns, based on how a user might search for data.
6. Create some additional indexes on your tables as you like. Think about how your data might be searched and where indexes can create efficiencies.
Hour 23, “Improving Database Performance”
Quiz
1. Is using a unique index on a small table beneficial?
Answer: The index might not be of any use for performance issues, but the unique index keeps referential integrity intact. Referential integrity is discussed in Hour 3.
2. What happens when the optimizer chooses not to use an index on a table when a query has been executed?
Answer: A full table scan occurs.
3. Should the most restrictive clause(s) be placed before or after the join condition(s) in the
WHEREclause?Answer: The most restrictive clause(s) should be evaluated before the join condition(s) because join conditions normally return a large number of rows.
4. When is the
LIKEoperator considered bad in terms of performance?Answer: When the use of
LIKEdisables the use of indexes that exist on the target tables5. How can you optimize batch load operations in terms of indexes?
Answer: You can remove indexes prior to a batch load and re-create them afterward. It is beneficial to performance to rebuild indexes periodically anyhow.
6. Which three clauses are the cause of sort operations that degrade performance?
Answer:
ORDER BY,GROUP BY, andHAVING.
Exercises
1. Rewrite the following SQL statements to improve their performance. Use the fictitious
EMPLOYEE_TBLandEMPLOYEE_PAY_TBL, as described here:EMPLOYEE_TBL EMP_ID VARCHAR(9) NOT NULL Primary key LAST_NAME VARCHAR(15) NOT NULL, FIRST_NAME VARCHAR(15) NOT NULL, MIDDLE_NAME VARCHAR(15), ADDRESS VARCHAR(30) NOT NULL, CITY VARCHAR(15) NOT NULL, STATE VARCHAR(2) NOT NULL, ZIP INTEGER(5) NOT NULL, PHONE VARCHAR(10), PAGER VARCHAR(10), EMPLOYEE_PAY_TBL EMP_ID VARCHAR(9) NOT NULL primary key POSITION VARCHAR(15) NOT NULL, DATE_HIRE DATETIME, PAY_RATE DECIMAL(4,2) NOT NULL, DATE_LAST_RAISE DATETIME, SALARY DECIMAL(8,2), BONUS DECIMAL(8,2),
SELECT EMP_ID, LAST_NAME, FIRST_NAME, PHONE FROM EMPLOYEE_TBL WHERE SUBSTRING(PHONE, 1, 3) = '317' OR SUBSTRING(PHONE, 1, 3) = '812' OR SUBSTRING(PHONE, 1, 3) = '765';Answer:
SELECT EMP_ID, LAST_NAME, FIRST_NAME, PHONE FROM EMPLOYEE_TBL WHERE SUBSTRING(PHONE, 1, 3) IN ('317', '812', '765');Typically, convert multiple
ORconditions to anINlist works better.-
SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEE_TBL WHERE LAST_NAME LIKE '%ALL%';
Answer:
SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEE_TBL WHERE LAST_NAME LIKE 'WAL%';
You cannot take advantage of an index if you do not include the first character in a condition’s value.
-
SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME, EP.SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE LAST_NAME LIKE 'S%' AND E.EMP_ID = EP.EMP_ID;Answer:
SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME, EP.SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE E.EMP_ID = EP.EMP_ID AND LAST_NAME LIKE 'S%';
2. Add another table called
EMPLOYEE_PAYHIST_TBLthat contains a large amount of pay history data. Use the following table to write a series of SQL statements to address the following problems. Be sure to ensure that the queries you write perform well.EMPLOYEE_PAYHIST_TBL PAYHIST_ID VARCHAR(9) NOT NULL primary key, EMP_ID VARCHAR(9) NOT NULL, START_DATE DATETIME NOT NULL, END_DATE DATETIME, PAY_RATE DECIMAL(4,2) NOT NULL, SALARY DECIMAL(8,2) NOT NULL, BONUS DECIMAL(8,2) NOT NULL, CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID)
Find the
SUMof the salaried versus nonsalaried employees, by the year in which their pay started.Answer:
SELECT START_YEAR,SUM(SALARIED) AS SALARIED,SUM(HOURLY) AS HOURLY FROM (SELECT YEAR(E.START_DATE) AS START_YEAR,COUNT(E.EMP_ID) AS SALARIED,0 AS HOURLY FROM EMPLOYEE_PAYHIST_TBL E INNER JOIN ( SELECT MIN(START_DATE) START_DATE,EMP_ID FROM EMPLOYEE_PAYHIST_TBL GROUP BY EMP_ID) F ON E.EMP_ID=F.EMP_ID AND E.START_DATE=F.START_DATE WHERE E.SALARY > 0.00 GROUP BY YEAR(E.START_DATE) UNION SELECT YEAR(E.START_DATE) AS START_YEAR,0 AS SALARIED, COUNT(E.EMP_ID) AS HOURLY FROM EMPLOYEE_PAYHIST_TBL E INNER JOIN ( SELECT MIN(START_DATE) START_DATE,EMP_ID FROM EMPLOYEE_PAYHIST_TBL GROUP BY EMP_ID) F ON E.EMP_ID=F.EMP_ID AND E.START_DATE=F.START_DATE WHERE E.PAY_RATE > 0.00 GROUP BY YEAR(E.START_DATE) ) A GROUP BY START_YEAR ORDER BY START_YEARFind the difference in the yearly pay of salaried employees versus nonsalaried employees, by the year in which their pay started. Consider the nonsalaried employees to be working full time during the year (
PAY_RATE× 52 × 40).Answer:
SELECT START_YEAR,SALARIED AS SALARIED,HOURLY AS HOURLY, (SALARIED - HOURLY) AS PAY_DIFFERENCE FROM (SELECT YEAR(E.START_DATE) AS START_YEAR,AVG(E.SALARY) AS SALARIED, 0 AS HOURLY FROM EMPLOYEE_PAYHIST_TBL E INNER JOIN ( SELECT MIN(START_DATE) START_DATE,EMP_ID FROM EMPLOYEE_PAYHIST_TBL GROUP BY EMP_ID) F ON E.EMP_ID=F.EMP_ID AND E.START_DATE=F.START_DATE WHERE E.SALARY > 0.00 GROUP BY YEAR(E.START_DATE) UNION SELECT YEAR(E.START_DATE) AS START_YEAR,0 AS SALARIED, AVG(E.PAY_RATE * 52 * 40 ) AS HOURLY FROM EMPLOYEE_PAYHIST_TBL E INNER JOIN ( SELECT MIN(START_DATE) START_DATE,EMP_ID FROM EMPLOYEE_PAYHIST_TBL GROUP BY EMP_ID) F ON E.EMP_ID=F.EMP_ID AND E.START_DATE=F.START_DATE WHERE E.PAY_RATE > 0.00 GROUP BY YEAR(E.START_DATE) ) A GROUP BY START_YEAR ORDER BY START_YEARFind the difference in what employees make now versus what they made when they started with the company. Again, consider the nonsalaried employees to be full time. Also consider that the employees’ current pay is reflected in
EMPLOYEE_PAY_TBLand alsoEMPLOYEE_PAYHIST_TBL. In the pay history table, the current pay is reflected as a row with theEND_DATEfor pay equal toNULL.Answer:
SELECT CURRENTPAY.EMP_ID,STARTING_ANNUAL_PAY,CURRENT_ ANNUAL_PAY, CURRENT_ANNUAL_PAY - STARTING_ANNUAL_PAY AS PAY_DIFFERENCE FROM (SELECT EMP_ID,(SALARY + (PAY_RATE * 52 * 40)) AS CURRENT_ANNUAL_PAY FROM EMPLOYEE_PAYHIST_TBL WHERE END_DATE IS NULL) CURRENTPAY INNER JOIN (SELECT E.EMP_ID,(SALARY + (PAY_RATE * 52 * 40)) AS STARTING_ANNUAL_PAY FROM EMPLOYEE_PAYHIST_TBL E ( SELECT MIN(START_DATE) START_DATE,EMP_ID FROM EMPLOYEE_PAYHIST_TBL GROUP BY EMP_ID) F ON E.EMP_ID=F.EMP_ID AND E.START_DATE=F.START_DATE ) STARTINGPAY ON CURRENTPAY.EMP_ID = STARTINGPAY.EMP_ID
Hour 24, “Working with the System Catalog”
Quiz
1. What is the system catalog also known as in some implementations?
Answer: The data dictionary
2. Can a regular user update the system catalog?
Answer: No
3. Who owns the system catalog?
Answer: The database itself, the owner of the database, or the system user
4. What is the difference between the Oracle system objects
ALL_TABLESandDBA_TABLES?Answer:
ALL_TABLESshows information about all tables that a specific user can access, whereasDBA_TABLEScontains information about all tables that exist in the database as a whole.5. Who makes modifications to the system tables?
Answer: The database itself makes modifications to the system tables, based on the activity in the database.
Exercises
1. At the prompt, type in queries to bring up each of the following:
▶ Information on all your tables
▶ Information on all columns in your tables
Answer:
select table_name from user_tables where table_name not like 'DMRS_%'; select * from user_tab_columns order by table_name;
2. Show the name of the current database.
Answer:
select * from v$database;
3. Show the name of the user by which you are connected to the database.
Answer:
show user;
4. Create a role to update your
FOODtable.Answer:
alter session set "_ORACLE_SCRIPT"=true; create role updatefoodinfo_role; grant update on updatefoodinfo_role to "username"; select role from role_tab_privs where owner = 'RYAN';
5. Select information about the role you created from the system catalog.
Answer:
SQL> select role, owner, table_name, privilege 2 from role_tab_privs 3 where role = 'SEE_BIRDS';
6. Explore the system catalog on your own. The information contained is nearly endless. Remember that, in Oracle, you can use the
DESCRIBE table_namecommand to show the columns that comprise a table.
Hour 25, “Bonus Workshop for the Road”
Exercises
1. Select data from your tables to explore your data, starting with the following:
▶ Select all the data from the
BIRDStable.▶ Select all the data from the
PHOTOGRAPHERStable.▶ What are the predators of the Bald Eagle?
Answer:
select * from birds; select * from photographers; select p.predator from predators p, birds_predators bp, birds b where p.pred_id = bp.pred_id and b.bird_id = bp.bird_id and b.bird_name = 'Bald Eagle';2. When looking at the ERD for this database, you can see that a relationship is missing between the
PHOTOGRAPHERSandPHOTOStables.▶ What kind of relationship should be added?
▶ Use SQL to define a relationship between these two tables in your database. (Hint: add any necessary foreign key constraints.)
Answer: A photographer might take many photographs.
PHOTOGRAPHERSandPHOTOSshould have a common column to join the two tables.3. Add a table called
COLORSfor colors that each bird might have and create any other necessary tables and relationships (primary and foreign keys) to create a relationship betweenBIRDSandCOLORS. If you want, you can save yourCREATE TABLEstatements in a file to easily regenerate at a later time, or you can add them to your maintables.sql script. If you add these statements to the maintables.sqlscript, be sure that theDROPandCREATE TABLEstatements are in the proper order in relation to the other tables in your database, based on primary/foreign key relationships (for example, you must drop child tables before parent tables, and you must create parent tables before child tables).Answer:
create table colors (color_id number(2) not null primary key, color varchar2(20) not null); create table birds_colors (bird_id number(3) not null, color_id number(2) not null, constraint birds_colors_pk1 primary key (bird_id, color_id), constraint birds_colors_fk1 foreign key (bird_id) references birds(bird_id), constraint birds_colors_fk2 foreign key(color_id) references colors(color_ id));
4. Insert a few rows of data into the
COLORStable and any other tables you may have created.Answer:
insert into colors values (1, 'Blue'); insert into colors values (2, 'Green'); insert into colors values (3, 'Black'); insert into colors values (4, 'White'); insert into colors values (5, 'Brown'); insert into colors values (6, 'Gray'); insert into colors values (7, 'Yellow'); insert into colors values (8, 'Purple');
5. Add the following bird to your database: Easter Kingbird, 9 inches, .2 pounds, 15-inch wingspan, 5 eggs, up to 4 broods per season, incubation period of 17 days, fledging period of 17 days, both participate with the nest.
Answer:
insert into birds values (24, 'Eastern Kingbird', 9, .2, 15, 5, 4, 17, 17, 'B');
6. Add the following photographer to your database: Margaret Hatcher, mentored by Susan Williamson, hobbyist level.
Answer:
insert into photographers values (12, 'Margaret Hatcher', 8, 3);
7. Make sure you have saved your recent transactions. If you want, you can save the previous
INSERTstatements to a file so that you can regenerate this data later if you need to, or you can add them to the main data.sql script.Answer:
Commit;
8. Generate a simple list of all photographers, skill levels, and styles. Then sort the results, showing the photographers with the highest skill levels first in your results.
Answer:
select p.photographer, pl.photo_level, s.style from photographers p, photo_levels pl, photographer_styles ps, photo_styles s where p.level_id = pl.level_id and p.photographer_id = ps.photographer_id and ps.style_id = s.style_id order by 3 desc;9. Generate a list of photographers and their protégés (the photographers they mentor). List only the photographers that are mentors.
Answer:
select mentors.photographer mentor, proteges.photographer protege from photographers mentors, photographers proteges where mentors.photographer_id = proteges.mentor_photographer_id order by 1;10. Re-create the same list of photographers and their protégés, but show all photographers, regardless of whether they mentor anyone, and sort the results by the photographer with the highest skill levels first.
Answer:
select mentors.photographer mentor, proteges.photographer protege, pl.photo_level from photographers mentors, photographers proteges, photo_levels pl where mentors.photographer_id(+) = proteges.mentor_photographer_id and proteges.level_id = pl.level_id order by 1;11. Using
UNIONto combine the results of multiple queries, show a list of all animals in your database (for example, birds and predators).Answer:
select bird_name from birds union select predator from predators;
12. Create a table called
FORMER_PHOTOGRAPHERSthat is based on thePHOTOGRAPHERStable.Answer:
create table former_photographers as select * from photographers;
13. Truncate the table
FORMER_PHOTOGRAPHERS.Answer:
truncate table former_photographers;
14. Insert all data from the
PHOTOGRAPHERStable into theFORMER_PHOTOGRAPHERStable.Answer:
insert into former_photographers select * from photographers;
15. Can you think of any views that can be created to make the exercises from the previous section easier, or more easily repeatable, for similar future queries?
Answer: Solutions will vary.
16. Create at least one view that you can imagine.
Answer: Solutions will vary.
17. Write a query that produces a list of the most versatile photographers in your database. For example, the query might contain one or more of the following subqueries:
▶ Photographers that have a skill level greater than the average skill level of all photographers
▶ Photographers whose count of birds of interest is more than the average of all photographers
▶ Photographers who have more styles than the average photographer
Answer:
select p.photographer, pl.photo_level, count(fb.photographer_id) birds, count(ps.photographer_id) styles from photographers p, photo_levels pl, favorite_birds fb, photographer_styles ps, photo_styles s where p.level_id = pl.level_id and p.photographer_id = fb.photographer_id and p.photographer_id = ps.photographer_id and s.style_id = ps.style_id and pl.level_id > (select avg(level_id) from photographers) group by p.photographer, pl.photo_level having count(fb.photographer_id) > (select avg(count(f.bird_id)) from favorite_birds f, photographers p where p.photographer_id = f.photographer_id group by p.photographer_id) and count(ps.photographer_id) > (select avg(count(ps.style_id)) from photographer_styles ps, photographers p where ps.photographer_id = p.photographer_id group by p.photographer_id);18. Create a view called
Versatile_Photographersthat is based on the subqueries you used earlier.Answer:
create view versatile_photographers as select p.photographer, pl.photo_level, count(fb.photographer_id) birds, count(ps.photographer_id) styles from photographers p, photo_levels pl, favorite_birds fb, photographer_styles ps, photo_styles s where p.level_id = pl.level_id and p.photographer_id = fb.photographer_id and p.photographer_id = ps.photographer_id and s.style_id = ps.style_id and pl.level_id > (select avg(level_id) from photographers) group by p.photographer, pl.photo_level having count(fb.photographer_id) > (select avg(count(f.bird_id)) from favorite_birds f, photographers p where p.photographer_id = f.photographer_id group by p.photographer_id) and count(ps.photographer_id) > (select avg(count(ps.style_id)) from photographer_styles ps, photographers p where ps.photographer_id = p.photographer_id group by p.photographer_id); select * from versatile_photographers;19. Suppose that each photographer needs a database user account to access information in the database about birds and other photographers. Generate a SQL script that creates database user accounts for each photographer with the following syntax:
CREATE USER MARGARET_HATCHER IDENTIFIED BY NEW_PASSWORD_1;
Answer:
select 'CREATE USER ' || substr(photographer, 1, instr(photographer, ' ', 1, 1) -1) || '_' || substr(photographer, instr(photographer, ' ', 1, 1) +1) || ' IDENTIFIED BY NEW_PASSWORD_1;' "SQL CODE TO CREATE NEW USERS" from photographers;20. What are the predators of each bird?
Answer:
select b.bird_name, p.predator from birds b, birds_predators bp, predators p where b.bird_id = bp.bird_id and bp.pred_id = p.pred_id order by 1;21. Which birds have the most predators?
Answer:
select b.bird_name, count(bp.pred_id) from birds b, birds_predators bp where b.bird_id = bp.bird_id group by b.bird_name order by 2 desc;22. Which predators eat the most birds in your database?
Answer:
select p.predator, count(bp.bird_id) "BIRD DIET" from birds b, predators p, birds_predators bp where b.bird_id = bp.bird_id and p.pred_id = bp.pred_id group by p.predator order by 2 desc;23. Which predators eat only fish-eating birds?
Answer:
select distinct(p.predator) predators from predators p, birds_predators bp, fish_eaters fe where p.pred_id = bp.pred_id and fe.bird_id = bp.bird_id;24. Which types of nests attract the most predators?
Answer:
select n.nest_name, count(distinct(bp.pred_id)) predators from nests n, birds_nests bn, birds b, birds_predators bp where n.nest_id = bn.nest_id and bn.bird_id = b.bird_id and b.bird_id = bp.bird_id group by n.nest_name order by 2 desc;25. Which photographers are most likely to see a crocodile when capturing an image of a bird?
Answer:
select p.photographer, b.bird_name "FAVORITE BIRDS", pr.predator from photographers p, favorite_birds fb, birds b, birds_predators bp, predators pr where p.photographer_id = fb.photographer_id and fb.bird_id = b.bird_id and b.bird_id = bp.bird_id and bp.pred_id = pr.pred_id and pr.predator = 'Crocodile'; select p.photographer, b.bird_name "FAVORITE BIRDS", pr.predator from photographers p, favorite_birds fb, birds b, birds_predators bp, predators pr where p.photographer_id = fb.photographer_id and fb.bird_id = b.bird_id and b.bird_id = bp.bird_id and bp.pred_id = pr.pred_id and pr.predator = 'Other Birds';26. Which predators are most likely to be in a landscape-style photograph?
Answer:
select distinct(pr.predator) from photographers p, favorite_birds fb, birds b, birds_predators bp, predators pr, photographer_styles ps, photo_styles s where p.photographer_id = fb.photographer_id and fb.bird_id = b.bird_id and b.bird_id = bp.bird_id and bp.pred_id = pr.pred_id and ps.photographer_id = p.photographer_id and s.style_id = ps.style_id and s.style = 'Landscape';27. What birds have a diet more diverse than the average bird in the database?
Answer:
select b.bird_name, count(bf.bird_id) "FOOD ITEMS" from birds b, birds_food bf where b.bird_id = bf.food_id group by b.bird_name having count(bf.bird_id) > (select avg(count(bf.bird_id)) from birds b, birds_food bf where b.bird_id = bf.bird_id group by b.bird_id);28. What cameras are used by these photographers?
Photographers that mentor others
Photographers with a skill level above average
Answer:
select c.camera from cameras c, photographer_cameras pc, photographers p where c.camera_id = pc.camera_id and pc.photographer_id = p.photographer_id and p.level_id > (select avg(level_id) from photographers) and p.photographer_id in (select p.photographer_id from photographers p, photographers m where m.mentor_photographer_id = p.photographer_ id);