[ Pobierz całość w formacie PDF ]
.7.Will the following statement work?SQL> SELECT SUBSTR LASTNAME,1,5 FROM NAME_TBL;No, missing () around lastname,1,5.Also, a better plan is to give the column analias.The statement should look like this:SQL> SELECT SUBSTR(LASTNAME,1,5) NAME FROM NAME_TBL;Exercise Answers1.Using today's TEAMSTATS table, write a query to determine who is battingunder.25.(For the baseball-challenged reader, batting average is hits/ab.)INPUT:SQL> SELECT NAME FROM TEAMSTATS2 WHERE (HITS/AB) SELECT *2 FROM NAME_TABLE;LAST_NAME FIRST_NAMEJONES NANCYSMITH JOHN2 rows selected.SQL> SELECT LAST_NAME2 FROM NAME_TABLE3 WHERE LAST_NAME LIKE '%MITH';No rows selected.ANALYSIS:You were looking for SMITH, but SMITH does exist in our table.The query findsSMITH because the column LAST_NAME is CHAR and there are spaces after SMITH.The SELECT statement did not ask for these spaces.Here's the correct statementto find SMITH:INPUT/OUTPUT:SQL> SELECT LAST_NAME2 FROM NAME_TABLE3 WHERE LAST_NAME LIKE '%MITH%';LAST_NAMESMITH1 row selected.ANALYSIS:By adding the % after MITH, the SELECT statement found SMITH and the spacesafter the name.TIP: When creating tables, plan your data types to avoid this type ofsituation.Be aware of how your data types act.If you allocate 30 bytes fora column and some values in the column contain fewer than 30 bytes, doesthe particular data type pad spaces to fill up 30 bytes? If so, consider howthis may affect your select statements.Know your data and its structure.8.Can you have duplicate table names?Yes.Just as long as the owner or schema is not the same. Exercise Answers1.Add two tables to the BILLS database named BANK and ACCOUNT_TYPE usingany format you like.The BANK table should contain information about the BANKfield used in the BANK_ACCOUNTS table in the examples.The ACCOUNT_TYPE tableshould contain information about the ACCOUNT_TYPE field in theBANK_ACCOUNTS table also.Try to reduce the data as much as possible.You should use the CREATE TABLE command to make the tables.Possible SQLstatements would look like this:SQL> CREATE TABLE BANK2 ( ACCOUNT_ID NUMBER(30) NOT NULL,BANK_NAME VARCHAR2(30) NOT NULL,ST_ADDRESS VARCHAR2(30) NOT NULL,CITY VARCHAR2(15) NOT NULL,STATE CHAR(2) NOT NULL,ZIP NUMBER(5) NOT NULL;SQL> CREATE TABLE ACCOUNT_TYPE( ACCOUNT_ID NUMBER(30) NOT NULL,SAVINGS CHAR(30),CHECKING CHAR(30);2.With the five tables that you have created--BILLS, BANK_ACCOUNTS, COMPANY,BANK, and ACCOUNT_TYPE--change the table structure so that instead of usingCHAR fields as keys, you use integer ID fields as keys.SQL> ALTER TABLE BILLS DROP PRIMARY KEY;SQL> ALTER TABLE BILLS ADD (PRIMARY KEY (ACCOUNT_ID));SQL> ALTER TABLE COMPANY ADD (PRIMARY KEY (ACCOUNT_ID));3.Using your knowledge of SQL joins (see Day 6, "Joining Tables"), write severalqueries to join the tables in the BILLS database.Because we altered the tables in the previous exercise and made the key field theACCOUNT_ID column, all the tables can be joined by this column.You can join thetables in any combination; you can even join all five tables.Don't forget toqualify your columns and tables.Day 10, "Creating Views and Indexes"Quiz Answers1.What will happen if a unique index is created on a nonunique field? Depending on which database you are using, you will receive some type of errorand no index at all will be created.The constituent fields of a unique index mustform a unique value.2.Are the following statements true or false?Both views and indexes take up space in the database and therefore must befactored in the planning of the database size.False.Only indexes take up physical space.If someone updates a table on which a view has been created, the view must havean identical update performed on it to see the same data.False.If someone updates a table, then the view will see the updated data.If you have the disk space and you really want to get your queries smoking, themore indexes the better.False.Sometimes too many indexes can actually slow down your queries.3.Is the following CREATE statement correct?SQL> create view credit_debts as(select all from debtswhere account_id = 4);No [ Pobierz całość w formacie PDF ]
zanotowane.pl doc.pisz.pl pdf.pisz.pl odbijak.htw.pl
.7.Will the following statement work?SQL> SELECT SUBSTR LASTNAME,1,5 FROM NAME_TBL;No, missing () around lastname,1,5.Also, a better plan is to give the column analias.The statement should look like this:SQL> SELECT SUBSTR(LASTNAME,1,5) NAME FROM NAME_TBL;Exercise Answers1.Using today's TEAMSTATS table, write a query to determine who is battingunder.25.(For the baseball-challenged reader, batting average is hits/ab.)INPUT:SQL> SELECT NAME FROM TEAMSTATS2 WHERE (HITS/AB) SELECT *2 FROM NAME_TABLE;LAST_NAME FIRST_NAMEJONES NANCYSMITH JOHN2 rows selected.SQL> SELECT LAST_NAME2 FROM NAME_TABLE3 WHERE LAST_NAME LIKE '%MITH';No rows selected.ANALYSIS:You were looking for SMITH, but SMITH does exist in our table.The query findsSMITH because the column LAST_NAME is CHAR and there are spaces after SMITH.The SELECT statement did not ask for these spaces.Here's the correct statementto find SMITH:INPUT/OUTPUT:SQL> SELECT LAST_NAME2 FROM NAME_TABLE3 WHERE LAST_NAME LIKE '%MITH%';LAST_NAMESMITH1 row selected.ANALYSIS:By adding the % after MITH, the SELECT statement found SMITH and the spacesafter the name.TIP: When creating tables, plan your data types to avoid this type ofsituation.Be aware of how your data types act.If you allocate 30 bytes fora column and some values in the column contain fewer than 30 bytes, doesthe particular data type pad spaces to fill up 30 bytes? If so, consider howthis may affect your select statements.Know your data and its structure.8.Can you have duplicate table names?Yes.Just as long as the owner or schema is not the same. Exercise Answers1.Add two tables to the BILLS database named BANK and ACCOUNT_TYPE usingany format you like.The BANK table should contain information about the BANKfield used in the BANK_ACCOUNTS table in the examples.The ACCOUNT_TYPE tableshould contain information about the ACCOUNT_TYPE field in theBANK_ACCOUNTS table also.Try to reduce the data as much as possible.You should use the CREATE TABLE command to make the tables.Possible SQLstatements would look like this:SQL> CREATE TABLE BANK2 ( ACCOUNT_ID NUMBER(30) NOT NULL,BANK_NAME VARCHAR2(30) NOT NULL,ST_ADDRESS VARCHAR2(30) NOT NULL,CITY VARCHAR2(15) NOT NULL,STATE CHAR(2) NOT NULL,ZIP NUMBER(5) NOT NULL;SQL> CREATE TABLE ACCOUNT_TYPE( ACCOUNT_ID NUMBER(30) NOT NULL,SAVINGS CHAR(30),CHECKING CHAR(30);2.With the five tables that you have created--BILLS, BANK_ACCOUNTS, COMPANY,BANK, and ACCOUNT_TYPE--change the table structure so that instead of usingCHAR fields as keys, you use integer ID fields as keys.SQL> ALTER TABLE BILLS DROP PRIMARY KEY;SQL> ALTER TABLE BILLS ADD (PRIMARY KEY (ACCOUNT_ID));SQL> ALTER TABLE COMPANY ADD (PRIMARY KEY (ACCOUNT_ID));3.Using your knowledge of SQL joins (see Day 6, "Joining Tables"), write severalqueries to join the tables in the BILLS database.Because we altered the tables in the previous exercise and made the key field theACCOUNT_ID column, all the tables can be joined by this column.You can join thetables in any combination; you can even join all five tables.Don't forget toqualify your columns and tables.Day 10, "Creating Views and Indexes"Quiz Answers1.What will happen if a unique index is created on a nonunique field? Depending on which database you are using, you will receive some type of errorand no index at all will be created.The constituent fields of a unique index mustform a unique value.2.Are the following statements true or false?Both views and indexes take up space in the database and therefore must befactored in the planning of the database size.False.Only indexes take up physical space.If someone updates a table on which a view has been created, the view must havean identical update performed on it to see the same data.False.If someone updates a table, then the view will see the updated data.If you have the disk space and you really want to get your queries smoking, themore indexes the better.False.Sometimes too many indexes can actually slow down your queries.3.Is the following CREATE statement correct?SQL> create view credit_debts as(select all from debtswhere account_id = 4);No [ Pobierz całość w formacie PDF ]