Oracle SQL Jumpstart with Examples- P2

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

107
lượt xem 9
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle SQL Jumpstart with Examples- P2: Review "As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle SQL Jumpstart with Examples- P2

20 1.6 SQL Tools Before you begin the following steps, you will need these two pieces of information about your database: The database name or network name. If you are running the database on your own computer, this is the name you gave the data- base when it was created. If you are not sure what you named it, go to a command prompt (see step 1 to learn how) and then type this command: lsnrctl status Look for a line that begins like this. The word in quotation marks is your database name. In this example, the database SID name is OLTP. Instance "oltp", status READY, ... If you are running from a client computer and using a remote database on the network, you must use the network name deﬁned in your local Oracle Net conﬁguration. The conﬁguration ﬁle named TNSNAMES.ORA has all of the network names available to you. The ﬁle is located in $ORACLE_HOME/network/admin directory. Here is an example of the text found in the TNSNAMES.ORA ﬁle for the OLTP network name: OLTP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 1300server) (PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oltp))) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1.6 SQL Tools 21 Figure 1.11 Win2K Registry ORACLE_SID Variable. The password for the users named SYS and SYSTEM. The Oracle Database Conﬁguration Assistant in Oracle Database 10g allows set- ting of SYS and SYSTEM passwords to the same value. If you are running a database on your own computer or on a database server, you can reach the database directly by omitting the database name. When you omit the name, Oracle uses the bequeth protocol and the cur- rent $ORACLE_SID variable setting to access the database. In Windows the $ORACLE_SID variable is set in the registry, and on UNIX or Linux in a user or root proﬁle. Figure 1.11 shows a Win2K registry location. When you use the database name, Oracle uses the transmission control protocol (TCP). Follow these steps to start up SQL*Plus Command Line and run an SQL command: Note: The steps here, and throughout the book, use the sample tables and data created especially for this book. Appendix A contains instructions for locating and installing all of the sample tables. 1. Go to a command-line prompt on your computer. If you are using Windows, click on Start/Programs/Accessories/Command Prompt. A window appears with a blinking cursor. This is your Chapter 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
22 1.6 SQL Tools Figure 1.12 SQL*Plus Command Line in Windows 2000. command prompt. If you are using UNIX, you may be at the command prompt when you log in. It looks like a dollar sign ($). If you are not already at the UNIX command prompt, select Ter- minal Window from your Utilities menu or execute an operating system shell. 2. Type the following command, replacing pwd with the password for the SYSTEM user and replacing name with your appropriate network name, and press Enter. sqlplus system/pwd@name 3. You will see status information about SQL*Plus and the database and a message stating you are connected. Then your display’s prompt changes to “SQL>”, indicating that you are now in SQL*Plus. Figure 1.12 shows an example of the command prompt window after starting up SQL*Plus. 4. Type the following SQL*Plus commands, and press Enter after each line. These set up the column width displayed for the query that follows. (More on SQL*Plus commands in Chapter 8.) COL PRODUCT FORMAT A35 COL VERSION FORMAT A15 COL STATUS FORMAT A15 5. Type the following query and press Enter: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1.6 SQL Tools 23 Figure 1.13 SQL*Plus SQL Commands Return Instant Results. SELECT * FROM PRODUCT_COMPONENT_VERSION; The asterisk represents all of the columns. Thus all columns are displayed in this query. Figure 1.13 shows the results. The actual data may be different, depending on the shape of your Oracle Database 10g installation. 6. Exit SQL*Plus by typing EXIT and pressing Enter. This returns you to your command prompt. 7. Exit from the command prompt by typing EXIT and pressing Enter. One of the disadvantages of using the command-line mode of SQL*Plus is the inability to use the mouse to correct your typing. You must erase using the backspace key. Table 1.1 shows the editing commands you can use. An advantage of the command-line mode is the ability to add parame- ters to the sqlplus command. For example, you can run a script immedi- ately upon startup, or start SQL*Plus without logging into any database instance (this is useful for issuing SQL commands for starting and stopping the database). SQL*Plus does have a line editor built into it that you can use instead of starting up an editor. Chapter 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
24 1.6 SQL Tools Table 1.1 SQL*Plus Line Editing Commands. Command Description c/old/new Change old to new characters in current line. l or list List the SQL in the buffer. ln Go to line n in the SQL buffer. del n or del * or del n m Delete line n in the SQL buffer, or delete the current line (*) or delete lines n through m. a text or append text Add text to the end of the current line. i or input or i text or input text Insert a new line after the current line. Add text to the line, if text is speciﬁed. Next, you will look at the Windows-like SQL*Plus tool. 1.6.2 SQL*Plus in Windows Mode This version of SQL*Plus gives you a Windows-like interface with a few environmental options. However, it still requires you to type a single line at a time. To try out SQL*Plus in Windows mode, follow these steps: 1. If you are using a Windows operating system, start the tool by clicking Start/Programs/Oracle – Orahome10/Application Devel- opment/SQL*Plus. If you are using another operating system, go to a command-line prompt, type sqlplusw, and press Enter. You will see a Log On window appear. You must log on with valid credentials now. 2. Type SYSTEM in the User Name box, the current password for SYSTEM in the Password box, and your database name in the Host String box. Figure 1.14 shows the Log On window with the information ﬁlled in. Notice that the password appears as a line of asterisks. This is to keep your password private. 3. Click OK to log in. The SQL*Plus window appears. Just like the command-line version, you see status information and get Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1.6 SQL Tools 25 Figure 1.14 Log into Your Database as a Valid User. an SQL> prompt telling you that SQL*Plus is ready to accept commands. 4. Type the following command and press Enter. This is an SQL*Plus command that tells the database to list the structure of the table or view that you name. A view is a query stored with a name in the database. It acts like a table but does not store any data. (Chapter 19 covers views in detail). DESC DBA_USERS 5. The screen shows the names and datatypes of all the columns in this view. This is very useful when you are about to write an SQL command and you need a quick reminder of the exact column names in a table. Now type this query and press Enter after each line. Notice that the prompt changed from “SQL>” to “2” on the second line. This indicates that SQL*Plus knows you have started a command and you are continuing it on the next line. The semi- colon at the end of the second line signals to SQL*Plus that the command is complete and should be immediately executed. SELECT USERNAME, ACCOUNT_STATUS, CREATED FROM DBA_USERS; 6. The results scroll by, and you can use the scroll bar on the right side of the window to move up or down and view the results. Fig- ure 1.15 shows the results from the query. The column headings Chapter 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
26 1.6 SQL Tools Figure 1.15 SQL*Plus in Windows Mode Has a Scroll Bar and Menu. and report feedback are standard parts of every mode of SQL*Plus; however, the scroll bar and the menu are features of the Windows mode and not of the command-line mode. Some operating systems will allow conﬁguration changes to allow addi- tion of scroll bars to command-line windows. 7. Click on Edit in the top menu and invoke the editor. A window appears with an editing program and the text of the query you wrote ready for editing. In Windows, the default editor is Note- pad. In UNIX, Linux, and other operating systems, the default editor can be conﬁgured in a user proﬁle. 8. The editor can be used to change the command you created while working in SQL*Plus. You can retrieve ﬁles with SQL commands in them using the File/Open command on the menu. Selecting the File/Run command from the menu will execute the most recent SQL command. Modify the query by removing the CRE- ATED column from the query. 9. Save the ﬁle and exit the editor. The modiﬁed query now appears on the screen, ready to run if you choose. Figure 1.16 shows what your SQL*Plus screen should look like now. 10. Before running the command, select File/Spool/Spool File from the menu. A window opens in which you can select the ﬁle name and location. This ﬁle will contain everything you type and SQL*Plus returns from the moment you return to SQL*Plus Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1.6 SQL Tools 27 Figure 1.16 Invoking (Opening) the Editor. until you turn it off, or until you exit SQL*Plus. This is a handy way to record your work. In addition, in Chapter 8, you will ﬁnd out how to write reports using this spooling technique. Navigate to a directory of your choosing, such as C:\TEMP in Windows, and then type “testing” as the ﬁle name and click Save. The ﬁle will automatically receive a sufﬁx of “.LST” on Windows and of “.lis” on UNIX and other operating systems. 11. Type / (a forward slash) to run the query. The forward slash and the semi-colon both tell SQL*Plus to execute a command. The forward slash must be used alone on a line by itself, whereas the semi-colon is used at the end of a line of code. The semi-colon terminates and submits a single-line SQL command to the data- base. The forward slash does the same and additionally compiles and executes blocked sections of PL/SQL code. 12. The results scroll into the window as before. 13. Type the letter L and press Enter. This is the LIST command of SQL*Plus. It displays whatever SQL command is currently in the SQL*Plus buffer. 14. Select File/Spool/Spool off to end the spooling of data to the ﬁle. This closes the ﬁle that has been receiving data from the SQL*Plus session. Your spool ﬁle will be empty until this com- mand is executed or you exit SQL*Plus. Chapter 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
28 1.6 SQL Tools 15. Exit SQL*Plus by typing EXIT and pressing Enter or by clicking the X at the top right corner of the window. 16. Navigate through Windows Explorer to ﬁnd the TESTING.LST ﬁle that was spooled in the location you chose. If you are using UNIX, use the cd command or your File Directory window to ﬁnd the testing.lst ﬁle. Open the ﬁle with your editor and view the results. You should see the forward slash (the ﬁrst thing you typed after turning on spooling), the query results, the “L” com- mand, and the query in this ﬁle. Spooling is useful for saving queries you develop in SQL*Plus. In addition, with a few extra commands, you can create a report (with headings, titles, summaries, and so on) from SQL queries and spool the report to a ﬁle ready for printing. 17. Close the ﬁle. Note: If you make a mistake and press Enter before ﬁxing it, you sometimes get a line number prompt instead of an SQL prompt. This means SQL*Plus has interpreted your line as the beginning of a command and is expecting you to complete the command before executing. To get out of this continuing line mode, type a period (.) alone on a line and press Enter. You will be returned to the SQL prompt so you can begin again. Another form of the SQL*Plus tool can be found within Oracle Enter- prise Manager. 1.6.3 SQL*Plus Worksheet The Oracle Enterprise Manager (OEM) is a great set of tools for the data- base administrator (DBA). The OEM Console gives you a bird’s-eye view of your database, or many databases if you have access to more than one. The SQL*Plus Worksheet is a standard part of the OEM suite that is installed when you install Oracle Database 10g (Enterprise, Standard, or Personal Editions). To run the worksheet by itself, without going through the OEM Con- sole, follow these instructions: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1.6 SQL Tools 29 1. Start SQL*Plus Worksheet from Windows by clicking Start/Pro- grams/Oracle – Orahome10/Application Development/SQL*Plus Worksheet. If you are using UNIX, Linux, or other platforms, go to a command prompt and type: oemapp worksheet 2. A login window appears. The window title is “Oracle Enterprise Manager Login” because the same login window appears for the OEM Console and other OEM tools. Select the “Connect directly to the database” button. Note: The Management Server is out of the scope of this book. Type SYSTEM in the User Name box, the current password for SYSTEM in the Password box, and your database name in the Service Name box. Leave the Connect As box defaulting to “Nor- mal.” Figure 1.17 shows the completed login window; click OK to log into SQL*Plus Worksheet. 3. The SQL*Plus Worksheet window appears. The top windowpane is your area for typing SQL commands. The lower pane displays the results. Click Enter to clear the window. 4. Type the following query in the top pane: SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES; Figure 1.17 Log into SQL*Plus Worksheet. Chapter 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
30 1.6 SQL Tools Figure 1.18 SQL*Plus Worksheet Has Useful Windows- like Functions. 5. Click the Execute icon. The icon looks like a lightning bolt and is located on the left side of the window. The results of the query scroll down on the lower pane. Figure 1.18 shows the SQL*Plus Worksheet window at this point. 6. Modify the query by changing “FILE_NAME” to “MAX- BYTES.” Notice that you can edit directly in this tool without resorting to an editor. 7. Run the changed command by clicking the Execute icon again. The results appear in the lower pane. 8. Hold your mouse over each of the icons on the left side of the window to see the other handy functions available in this tool. For example, the icon just below the Execute icon can list up to 50 previous SQL commands. 9. Exit this tool by clicking the X in the top window or typing EXIT and clicking the Execute icon. The SQL*Plus Worksheet may be the most versatile of the SQL*Plus variations. You may ﬁnd it easier to work with than SQL*Plus Windows or command-line versions and capable of more features than the Web version found in the next section. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1.6 SQL Tools 31 Note: SQL*Plus Worksheet is the tool this book uses to guide you through learning SQL and SQL*Plus commands. Feel free to use the other tools if you prefer, although screenshots, when they are used, will display SQL*Plus Worksheet window in most cases. 1.6.4 iSQL*Plus The Web server, called Oracle HTTP Server, can be installed with Oracle Database 10g. The HTTP Server is a miniature application server set up to run the Web-based tools and programming aids that come with Oracle Database 10g. To start up the HTTP Server on UNIX, type this command at a com- mand prompt: $ORACLE_HOME/Apache/Apache/bin/apachectl start In most cases, if you are using Windows, the Oracle HTTP Server is already running when you boot up your computer. If you need to start it, however, you can do so by selecting Start/Programs/Oracle – Orahome10/ Oracle HTTP Server/Start HTTP Server powered by Apache from the Task bar. Alternatively, you can start it by clicking Start/Services/Control Panel and opening the Services window (go to Administrative Controls ﬁrst, if you are running Windows 2000). Then start the Oracle HTTP Server service. Note: If there are problems, see the troubleshooting section in Chapter 8. Follow these steps to look around with the iSQL*Plus tool: 1. Open your browser. 2. Type in this address in the Location box of your browser and press Enter. You must replace mymachine with the actual net- work name of your computer and mydomain with the actual domain name your computer is in (if none, leave this out). The default port number is 7778, so try that ﬁrst. http://.:7778/isqlplus Chapter 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
32 1.6 SQL Tools Figure 1.19 iSQL*Plus Gives Direct Access to the Database. iSQL*Plus presents a login screen, as seen in Figure 1.19. If you do not see this screen, read the section on troubleshooting in Chapter 8. 3. Type SYSTEM in the Username box, the current password for SYSTEM in the Password box, and your network name in the Connection Identiﬁer box. Figure 1.19 shows the boxes ﬁlled in. As usual, the password is displayed as a string of asterisks for pri- vacy. Click the Login button to go to the main screen. 4. The main screen for iSQL*Plus appears. Type the following query into the box labeled “Enter statements,” and then scroll down and click the Execute button. SELECT VIEW_NAME, TEXT FROM USER_VIEWS; The SQL command is executed, and the results appear at the bot- tom of the screen. Scroll down to view the results, as shown in Figure 1.20. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1.6 SQL Tools 33 Figure 1.20 Query Results Shown as an HTML Table. 5. Scroll back to the top of the screen. Here are some control but- tons to explore, as you see in Figure 1.21. The History button is similar to the SQL History icon in SQL*Plus Worksheet. It lists previous SQL commands and loads them back into the Work Screen. The Preferences button displays a selection of settings especially for the browser window, such as the width of the out- put area and whether to place the results in the same browser win- dow (the default) or in a new browser window. In addition, the Preferences screen sets SQL*Plus environmental variables (com- mon to all versions of SQL*Plus), such as LINESIZE, ECHO, and HEADINGS. Learn more about these settings in Chapter 8. 6. Click on the Help icon in the top right corner of the window. This brings up a directory of links to commands especially for iSQL*Plus (in the ﬁrst column), and links to SQL*Plus com- mands that are used for all the versions of SQL*Plus in the sec- ond and third columns. Use this if you are not sure how to do SQL*Plus tasks such as setting the number of lines per page on a report, automatically displaying (or suppressing) the SQL com- mand before running the command, and so on. 7. Exit this window by clicking the X in the top right corner. The main iSQL*Plus window is still open. Chapter 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
34 1.7 The MUSIC Schema Figure 1.21 iSQL*Plus Is a Rich Environment. 8. Exit the iSQL*Plus browser by clicking Logout link, and then clicking the X in the top right corner when you see the iSQL*Plus login screen. Note: See Chapter 8 if you cannot reach the iSQL*Plus login screen. 1.7 The MUSIC Schema The sample data described here will be used as a basis to write your own SQL commands as you follow along with step-by-step exercises in every chapter. Figure 1.22 shows the database design structure of the tables, includ- ing their primary keys (the columns that deﬁne a unique record) and their columns. This schema supports a ﬁctional music studio. The music studio keeps track of the musicians who use the studio and the time they spend in the studio recording songs. Here is a short description of each table: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1.7 The MUSIC Schema 35 Figure 1.22 The Music Studio Schema. ARTIST. A musician who has either recorded a song or participated in recording another musician’s song. Each artist record has the name, address, and e-mail of the musician. Every artist has a unique identiﬁcation number (ARTIST_ID) assigned when the record was entered into the database table. In addition, a special column called INSTRUMENTS contains a list of musical instruments the musician plays. This special column is a collection (a list of many values held in a single column). SONG. The ARTIST_ID column identiﬁes the owner of each song in the table. Each song has a title, recording date, and playing time. The RECORDING column contains the ﬁnal recorded song in an audio format, ready to play. MUSICCD and CDTRACK. A music CD has two tables for all the information. First, the MUSICCD table holds the CD title, date it was pressed, and the total playing time of the CD. Second, the CDTRACK table contains all the songs for each CD and the order in which that song appears on the CD. This arrangement of tables Chapter 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
36 1.7 The MUSIC Schema allows one song to be included on more than one CD. For example, The Beatles’ song “Let It Be” is on the White Album CD and on The Beatles’ Greatest Hits CD. GENRE. Music CDs can be categorized into genres or types of music. Genres are hierarchical in nature, where one genre can be a subset of another genre. STUDIOTIME. When a musician (artist) comes into the studio to record a song, the studio charges the artist for time spent in the stu- dio. This table contains information needed for billing the artist. An artist may have many studio sessions, and each session is a row in the STUDIOTIME table. GUESTAPPEARANCE. A musician seldom records a song alone. Even though the musician owns the song, he or she often asks other musicians to collaborate on the recording. This table keeps track of which musician (called the guest artist) played on what other musi- cian’s songs. INSTRUMENTATION. When a guest artist plays on a song, he or she plays one or more instruments. This table keeps track of which instrument each guest artist played on each song. For example, Jim played drums and sang backup vocals (the voice is considered an “instrument” in these tables) on Amy’s song. Later Amy played guitar on Jim’s song. INSTRUMENT. The instrument table assigns an identifying num- ber to each instrument. The number is used in the INSTRUMEN- TATION table. So, instruments are actually stored in two different ways in the schema: (1) as a collection in the ARTIST table and (2) as individual rows in the INSTRUMENT table. This is done to illustrate the variety of methods you can use when designing a data- base system. 1.7.1 The MUSIC Schema Sales Data Warehouse The OLTP schema in Figure 1.22 is expanded in Figure 1.23 to create a data warehouse5 structure for CD sales. In general, data warehouse tables can be broken into dimension and fact tables. Fact tables contain facts such as sales record history, and dimensions describe the facts such as the countries in which sales took place. Roughly, dimensions are equivalent to OLTP static tables such as a table of customers. Facts are roughly equiva- lent to OLTP transactional tables such as sales transactions. A data ware- Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1.7 The MUSIC Schema 37 Figure 1.23 The Music Studio Schema Sales Data Warehouse. house data model should in its ideal form be in the structure of a star (star schema) or in a less ideal form of a degraded star schema (snowﬂake). In Figure 1.23 the SALES table is the fact table, and all other tables are dimensions of those sales entries. The schema represented by the entity relationship diagram is in fact a snowﬂake schema because of the relation- ship between the CONTINENT and COUNTRY tables. This relation- ship is not strictly necessary, but it was useful during the process of writing this book. Dimension Tables. RETAILER, CONTINENT, and COUNTRY are all purely dimensional entities of SALES. Partial Dimension Tables. The CUSTOMER table could be con- strued as being a partial fact table describing SALES table entries, apart from the fact that there is a link to the GENRE table in the OLTP structure. Links are shown in Figure 1.24. Fact Tables. The SALES table is a fact table because it contains facts about sales or, more simply put, sales transaction records. All dimen- sions describe SALES such as what country a sale occurred in. Chapter 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
38 1.8 Endnotes Figure 1.24 OLTP to Data Warehouse Links. There are links between the two sets of OLTP and data warehouse tables, as highlighted in Figure 1.24. All scripts used to create tables and their data are provided in Appendix A. The next chapter will brieﬂy list new features available for SQL in both Oracle Database 10g and Oracle Database 9i. 1.8 Endnotes 1. www.oracledbaexpert.com/oracle/secure/Normalization.doc 2. Oracle Performance Tuning for 9i and 10g (ISBN: 1-555-58305-9) 3. My version of the Normalization is a highly simpliﬁed version. I have twisted Normal Forms deliberately. 4. http://www.oracledbaexpert.com/oracle/secure/ Denormalization.doc 5. http://www.oracledbaexpert.com/oracle/secure/ TheVeryBasicsOfDataWarehouseDesign.doc Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
2 New Features of Oracle SQL In this chapter: What are the new features of Oracle SQL in Oracle Database 10g? What were the new features of Oracle SQL in Oracle Database 9i? What PL/SQL improvements are there? How is XML development better supported? What’s new in Oracle SQL utilities? This chapter takes a bird’s-eye view of Oracle SQL changes in both Ora- cle Database 10g and Oracle Database 9i. Without further ado, let’s get started with Oracle Database 10g. 2.1 New Features in Oracle Database 10g Oracle Database 10g contains the following SQL and PL/SQL features. 2.1.1 Oracle SQL Improvements in Oracle Database 10g Oracle documentation states that case sensitivity is no longer required for ﬁltering and sorting in SQL statements. Proving this point is a tuning exercise and does not belong in this book. The CONNECT BY clause now allows ancestor-descendant pairs as opposed to only parent-child pairs. In other words, pairs can be matched and returned where those pairs are not directly related within a hierarchy but related from the top to the bottom of a hierar- chy (see Chapter 13). 39 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.