🗊Презентация An Exercise of SQL Using SQL* Plus

Assignment #2 An Exercise of SQL Using SQL*Plus KAIST Myoung Ho Kim

Table of Contents SQL*Plus Oracle SQL*Plus Access to database SQL*Plus Commands Assignment #2 SQL Queries

SQL*Plus

Oracle SQL*Plus An Oracle command-line utility program that can run SQL commands interactively or from a script.

Access to DB using SQL*Plus Install Oracle Client http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html Scroll down to Oracle Database 11g Release 2 Click See All

Access to DB using SQL*Plus (Cont’d) Install Oracle Client Download Oracle Database 11g Release 2 Client Run setup.exe Install ‘Manager’ type

Access to DB using SQL*Plus (cont’d) Run SQL Plus Download tnsnames.ora from course homepage and copy it to (directory that Oracle Client is installed: ex. C:\app\MyDirect\product\11.2.0\client_2)\network\admin Run SQL Plus

Access to DB using SQL*Plus (cont’d) Access to database User-name: s[studentID]@cs360 ex) If your studentID is 20151234, then your user-name is s20151234@cs360 Password: s[studentID] ex) If your studentID is 20151234, then your password is s20151234 It is recommended to change your password for security

SQL*Plus Commands

SQL*Plus Commands SQL*Plus buffer commands LIST List one or more lines of the SQL buffer CHANGE Change text on the current line in the buffer DEL Delete one or more lines of the buffer APPEND Add specified text to the end of the current line in the buffer RUN Execute the SQL command currently stored in the SQL buffer CLEAN BUFFER Erase the SQL command currently stored in the SQL buffer SQL*Plus file commands SAVE Save the contents of the SQL buffer in a host operating system file GET Load a host operating system file into the SQL buffer START Execute the contents of the specified script SPOOL Store query results in an operating system file (.sql) HOST Execute a host operating system command without leaving SQL*Plus EDIT Open a text editor like the notepad to edit an text file (.sql, .lst, etc)

SQL*Plus Commands (cont’d) SQL*Plus buffer commands Besides sending SQL statements to the server, SQL*Plus also saves them into a local buffer and allow users to view and change the statements LIST Display one or more lines of the SQL buffer CHANGE Change text on the current line in the buffer RUN(or /) Execute the SQL command currently stored in the SQL buffer

SQL*Plus Commands (cont’d) SQL*Plus buffer commands DEL Delete one or more lines of the buffer APPEND/ INPUT Add specified text / line(s) to the end of the current line in the buffer CLEAR BUFFER Erase the SQL command currently stored in the SQL buffer

SQL*Plus Commands (cont’d) SQL*Plus buffer commands LIST, CHANGE

SQL*Plus Commands (cont’d) SQL*Plus buffer commands RUN(or /), DEL

SQL*Plus Commands (cont’d) SQL*Plus buffer commands APPEND

SQL*Plus Commands (cont’d) SQL*Plus buffer commands INPUT, CLEAR BUFFER

SQL*Plus Commands (cont’d) SQL*Plus file commands SAVE, GET, START(or @) Save the contents of the SQL buffer into a script file Load a contents of script file into the SQL buffer Execute the contents of the specified script SPOOL Store query results in an operating system file SPOOL result.lst : start to write in result.lst SPOOL OFF : stop to write HOST Execute a host operating system command without leaving SQL*Plus ex) HOST dir : execute a MS-DOS command dir

SQL*Plus Commands (cont’d) SQL*Plus file commands SAVE, GET, START(or @)

SQL*Plus Commands (cont’d) SQL*Plus file commands SPOOL, HOST

Assignment #2

Submission Due Sep. 23, 2:00 a.m. Delay is not accepted Submission standard [student ID].lst contains the executions of SQL commands and their results. You may use SPOOL command. Upload the .lst file to course homepage Evaluation You will get points if your SQL queries find the right answers. Do not cheat others. Both of them will get no point.

Example Database Create tables for homework. Download HW2db.sql from the course homepage and Copy it to (directory that Oracle Client is installed)\BIN @HW2db.sql or start HW2db.sql

Example Database (cont’d) Database Design You can see all the tables stored in your database using a command ‘select * from tab’

Queries Q1. Find all the tuples in the Printer relation for color printers. Remember that color is a boolean-valued attribute. If a value of color attribute is 1 then the printer is a color printer. If a value of color attribute is 0 then the printer is not a color printer Q2. Find the model number, speed, and hard-disk size for all PC’s whose price is under $800. Q3. Find the manufacturers of laptops

Queries Q4. Find those manufactures that sell PC’s but not Laptops In oracle, the operator for difference of sets is ‘MINUS’ (instead of ‘EXCEPT’) Q5. Find the model number and price of all products (of any type) made by manufacturer C Q6. Find those processor speeds that occur in two or more PC’s

References Lecture notes Text book Chapter 6.1, 6.2, 6.3 Oracle SQL Plus Tutorial http://www.holowczak.com/oracle/sqlplus/