...basics of SQL. Follow the steps and Oracle will be fun for you. Step 1: Click the Start button, select Programs, then select SecureCRT 3.x. This will open up the quickconnect dialog box of SecureCRT. Select 1. Protocol as: SSH2 2. Hostname: babbage2.cs.iit.edu 3. SSH Server: Standard As depicted in figure below. On clicking connect at this point will bring up another dialog. Select “Accept and Save” to bring up a window asking username and password for Unix. Upon entering valid username and password, you should now see unix bash prompt. Now type just “sqlplus” at bash prompt to connect to Oracle. It will prompt for username and password for oracle. Upon entering valid username and password, you should now see SQL prompt as: SQL> 1 You are now connected to oracle through SQLPlus client installed on Unix Machine (babbage2.cs.iit.edu). Have fun. Also, if you want to find more information on installing oracle on your personal machine, go to www.oracle.com. Remember at SQL> prompt, after every sql command, don’t forget to put a semicolon(;). A SQL statement is the statement that complies with the syntax rule of SQL. A list of SQL statements such as create, delete, insert, select, and update will be frequently used. The SQL*PLUS commands are the auxiliary set of commands that may be only recognized by ORACLE SQL*PLUS. Frequently used commands in ORACLE SQL*PLUS interface are clear, describe, edit, get, help, list, save, spool, and run. To quit and end the SQL*PLUS session,...
Words: 1881 - Pages: 8
...Introduction to Oracle9i: SQL Instructor Guide • Volume 1 40049GC11 Production 1.1 October 2001 D33993 Authors Nancy Greenberg Priya Nathan Copyright © Oracle Corporation, 2000, 2001. All rights reserved. This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any form or by any means without the express prior written permission of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987). The information in this document...
Words: 33172 - Pages: 133
...An Training Guide SQL & SQL*Plus for Beginners Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Course Introduction Copyright © 1999 – 2004 ApplTop Solutions Limited 2 Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Course Objectives This course is designed to give each delegate a basic understanding/awareness of the following... • Oracle SQL • Oracle SQL*Plus Copyright © 1999 – 2004 ApplTop Solutions Limited 3 Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Course Objectives This course is designed to give each delegate a basic understanding of the following topics... • Oracle SQL. You will learn how to use the industry standard tool for working with a Relational Database. You will also learn many other things, from constructing simple queries to creating your own tables. • Oracle SQL*Plus. After the course you should have a good understanding of SQL*Plus, from starting it, to creating simple reports on it. During the course there will be several exercises to complete. Copyright © 1999 – 2004 ApplTop Solutions Limited 4 Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Note that this guide is meant only as an introduction to SQL and SQL*Plus and therefore much of the newer, more advanced features available in Oracle databases 8i, 9i and 10g are not covered. Copyright © 1999 – 2004 ApplTop Solutions...
Words: 19188 - Pages: 77
...Practice 1: Overview This is the first of many practices in this course. Practices are intended to cover all topics that are presented in the corresponding lesson. Note the following location for the lab files: E:\labs\SQL1\labs If you are asked to save any lab files, save them at this location. To start ISQL*Plus, start your browser. You need to enter a URL to access iSQL*Plus. The URL requires the host name, which your instructor will provide. Enter the following command, replacing the host name with the value that your instructor provides: http://<HOSTNAME:5561>/isqlplus In any practice, there may be exercises that are prefaced with the phrases “If you have time” or “If you want an extra challenge.” Work on these exercises only if you have completed all other exercises in the allocated time and would like a further challenge to your skills. Perform the practices slowly and precisely. You can experiment with saving and running command files. If you have any questions at any time, ask your instructor. Practice 1 Test your knowledge: 1. Initiate an iSQL*Plus session using the user ID and password that are provided by the instructor. 2. iSQL*Plus commands access the database. True/False 3. The following SELECT statement executes successfully: SELECT last_name, job_id, salary AS Sal FROM employees; True/False 4. The following SELECT statement executes successfully: SELECT * FROM job_grades; True/False 5. There are four...
Words: 520 - Pages: 3
...Assignment 1: SQL Query Design Diane King June 24, 2014 IT526-01 SQL Query Design Pr4ofessor: Jon McKeeby, DSc. Kaplan University Part1. Install SQL Express Part2. Questions. a. Each CHAR and VARCHAR store non binary strings characters as the maximum length allowed. The difference between the two is the maximum length allowed and how each handle trailing spaces (My SQL, n.d.). The CHAR data type is a fixed length typed and VARCHAR is a variable length data type. When the CHAR data type is used the length of strings allowed in each column definition should be from 0-225. As an example in the SQL database there is a dbo.table where tables hold a type column defined as (CHAR 12). This requires 12 characters for each value. My SQL states that values shorter than the designated length are padded spaces to that length when they are stored; and trailing spaces are removed from CHAR values when they are retrieved, so retrieved values may not be the same length as when stored (para, 2). The VARCHAR maximum numbers are up to 65-535. Any string stored in a VARCHAR column takes the number of characters required “plus one or two bites to record the strings length” (My SQL, n.d.). b. The Query gave the error: Msg 207, Level 16, State 1, Line 3 Invalid column name 'YearHired'. c. The Query returned a error on both therefore, I’m unsure. Msg 207, Level 16, State 1, Line 5 Invalid column name 'FileName'. Msg 207, Level 16, State 1, Line 4 Invalid column name 'filetable'...
Words: 515 - Pages: 3
...Oracle/SQL Tutorial1 Michael Gertz Database and Information Systems Group Department of Computer Science University of California, Davis gertz@cs.ucdavis.edu http://www.db.cs.ucdavis.edu This Oracle/SQL tutorial provides a detailed introduction to the SQL query language and the Oracle Relational Database Management System. Further information about Oracle and SQL can be found on the web site www.db.cs.ucdavis.edu/dbs. Comments, corrections, or additions to these notes are welcome. Many thanks to Christina Chung for comments on the previous version. Recommended Literature George Koch and Kevin Loney: Oracle8 The Complete Reference (The Single Most Comprehensive Sourcebook for Oracle Server, Includes CD with electronic version of the book), 1299 pages, McGraw-Hill/Osborne, 1997. Michael Abbey and Michael Corey: Oracle8 : A Beginner’s Guide [A Thorough Introduction for First-time Users], 767 pages, McGraw-Hill/Osborne, 1997. Steven Feuerstein, Bill Pribyl, Debby Russell: Oracle PL/SQL Programming (2nd Edition), O’Reilly & Associates, 1028 pages, 1997. C.J. Date and Hugh Darwen: A Guide to the SQL Standard (4th Edition), Addison-Wesley, 1997. Jim Melton and Alan R. Simon: Understanding the New SQL: A Complete Guide (2nd Edition, Dec 2000), The Morgan Kaufmann Series in Data Management Systems, 2000. 1 revised Version 1.01, January 2000, Michael Gertz, Copyright 2000. Contents 1. SQL – Structured Query Language 1.1. Tables 1.2. Queries (Part I) 1.3. Data Definition...
Words: 21631 - Pages: 87
...Knowledge of Installation, Configuration and maintenance of Oracle 10g and 11g Databases. * Configuring Physical Standby database and its maintenance.Database Upgradation from 10g to 11g. * Analyzing database performance using sql trace and TKPROF. Good in Export and Import for taking logical backup & refresh activities. * Configuration of Flash Recovery Area, Flashback Database and Block Change Tracking for Target Databases. * Creation and maintenance of database links and materialized views. * Index creation and reorganization. * Moderate understanding MSSQL & SYBASE. Mainly User-management, backup & restore activities. * Using AWR, ADDM, ASH report and other Oracle utilities for Proactive Oracle Tuning. * Good in Database Cloning (Cold/Hot ) * Good in User Management and User security. * Database backup and restoration activities. * Good in Business Communication. * Good in Business Writing, Coordination. * Ability to exhibit good team spirit. * Ability to complete assigned work on time. Technical Skills Language | SQL, PL/SQL,Shell Scripting. | Database | Oracle 10g,11g, MSSQL,SYBASE. | Operating System | Linux(RHEL 4,RHEL 5). | Oracle Tools | SQL*PLUS,RMAN,tkprof. | Work Experience Organization: Cambridge Technology India Pvt. Ltd. Duration: Dec 2009 to Till Date . Role: Oracle DBA. Project Details: Client Profile | Societe Generale is a France based Corporate...
Words: 532 - Pages: 3
...Project Report On Session (2004-2005) For the partial fulfillment of requirement of degree of Bachelor of Science in Information Technology (B.Sc – IT). Submitted By: Submitted To: Ms. Sandeep Bassi Lyallpur Khalsa college, Jalandhar City. (Affiliated To Guru Nanak Dev University) Tables of Contents ❖ Acknowledgement 4 ❖ Bonafide Certificate 5 ❖ Introduction 6 o Purpose o Design Constraints o Scope Of Project o Problem Definition ❖ Software Process 10 o Objective o Model Of Project ❖ Software Requirement Specification 12 o System Analysis o Functional Requirements o Performance Requirement o Hardware & Software Requirements ❖ Software Interface 23 ❖ Project Scheduling 46 o Pert Chart o Gantt Chart ❖ System Design 49 ...
Words: 9647 - Pages: 39
...Teach Yourself SQL in 21 Days, Second Edition Table of Contents: Introduction Week 1 at a Glance Day 1 Introduction to SQL Day 2 Introduction to the Query: The SELECT Statement Day 3 Expressions, Conditions, and Operators Day 4 Functions: Molding the Data You Retrieve Day 5 Clauses in SQL Day 6 Joining Tables Day 7 Subqueries: The Embedded SELECT Statement Week 1 in Review Week 2 at a Glance Day 8 Manipulating Data Day 9 Creating and Maintaining Tables Day 10 Creating Views and Indexes Day 11 Controlling Transactions Day 12 Database Security Day 13 Advanced SQL Topics Day 14 Dynamic Uses of SQL Week 2 in Review Week 3 at a Glance Day 15 Streamlining SQL Statements for Improved Performance Day 16 Using Views to Retrieve Useful Information from the Data Dictionary Day 17 Using SQL to Generate SQL Statements Day 18 PL/SQL: An Introduction Day 19 Transact-SQL: An Introduction Day 20 SQL*Plus Day 21 Common SQL Mistakes/Errors and Resolutions Week 3 in Review Appendixes A Glossary of Common SQL Statements B Source Code Listings for the C++ Program Used on Day 14 C Source Code Listings for the Delphi Program Used on Day 14 D Resources E ASCII Table F Answers to Quizzes and Excercises © Copyright, Macmillan Computer Publishing. All rights reserved. Teach Yourself SQL in 21 Days, Second Edition Acknowledgments A special thanks to the following individuals: foremost to my loving wife, Tina, for her tolerance and endless support, to Dan Wilson for his...
Words: 128515 - Pages: 515
...dwqwdlqkdmqwkldnqwkldnwkdnwqdldkwd Solutions To Workshop Exercises Chapter 1: SQL and Data 4 Chapter 2: SQL: The Basics 13 Chapter 3: The WHERE and ORDER BY Clauses 20 Chapter 4: Character, Number, and Miscellaneous Functions 27 Chapter 5: Date and Conversion Functions 42 Chapter 6: Aggregate Functions, GROUP BY and HAVING 57 Chapter 7: Equijoins 73 Chapter 8: Subqueries 108 Chapter 9: Set Operators 126 Chapter 10: Complex Joins 137 Chapter 11: Insert, Update, and Delete 164 Chapter 12: Create, Alter, and Drop Tables 178 Chapter 13: Indexes, Sequences, and Views 188 Chapter 14: The Data Dictionary, Scripting, and Reporting 197 Chapter 15: Security 213 Chapter 16: Regular Expressions and Hierarchical Queries 222 Chapter 17: Exploring Data Warehousing Features 235 Chapter 18: SQL Optimization 248 Chapter 1: SQL and Data In this chapter, you learned about data, how data is organized in tables, and how the relationships among the tables are depicted in a schema diagram. Based on your newly acquired knowledge, design a schema diagram based on the fictional ACME Construction Company. Draw on your own work experience to design the following components. 1. Draw boxes for these three tables: EMPLOYEE, POSITION, and DEPARTMENT. Solution: See the solution for Exercise 3. 2. Create at least three columns for each of the tables and designate a primary key for each table. Solution: See the solution for Exercise 3. 3. Create relationships among the...
Words: 6586 - Pages: 27
...® OCA Oracle Database 11g: SQL Fundamentals I Exam Guide (Exam 1Z0-051) ABOUT THE AUTHORS John Watson (Oxford, UK) works for BPLC Management Consultants, teaching and consulting throughout Europe and Africa. He was with Oracle University for several years in South Africa, and before that worked for a number of companies, government departments, and NGOs in England and Europe. He is OCP qualified in both database and Application Server administration. John is the author of several books and numerous articles on technology and has 25 years of experience in IT. Roopesh Ramklass (South Africa), OCP, is an independent Oracle specialist with over 10 years of experience in a wide variety of IT environments. These include software design and development, systems analysis, courseware development, and lecturing. He has worked for Oracle Support and taught at Oracle University in South Africa for several years. Roopesh is experienced in managing and executing IT development projects, including infrastructure systems provisioning, software development, and systems integration. About the Technical Editor Bruce Swart (South Africa) works for 2Cana Solutions and has over 14 years of experience in IT. Whilst maintaining a keen interest for teaching others, he has performed several roles including developer, analyst, team leader, administrator, project manager, consultant, and lecturer. He is OCP qualified in both database and developer roles. He has taught at Oracle University...
Words: 150089 - Pages: 601
...SQL Cursor A SQL cursor is a private Oracle SQL working area. There are two types of SQL cursor: implicit or explicit cursor. The implicit cursor is used by Oracle server to test and parse the SQL statements and the explicit cursors are declared by the programmers. Using the implicit cursor, we can test the outcome of SQL statements in PL/SQL. For example, • SQL%ROWCOUNT, return the number of rows affected; • SQL%FOUND, a BOOLEAN attribute indicating whether the recent SQL statement matches to any row; • SQL%NOTFOUND, a BOOLEAN attribute indicating whether the recent SQL statement does not match to any row; • SQL%ISOPEN, a BOOLEAN attribute and always evaluated as FALSE immediately after the SQL statement is executed. To write the explicit cursor, please refer to the following example. Note that a cursor definition can array a number of arguments. For example, DECLARE CURSOR csr_ac (p_name VARCHAR2) IS SELECT empno, name, sal FROM employee WHERE name LIKE '%p_name%'; BEGIN FOR rec_ac IN csr_ac ('LE') LOOP DBMS_OUTPUT.PUT_LINE(rec_ac.empno || ' ' ||rec_ac.name || ' '||v_sal); END LOOP ; CLOSE csr_ac; END; / Another way of writing the above code, is to use the basic loop and the SQL%NOTFOUND cursor, as shown in the following. SQL> DECLARE 2 CURSOR csr_ac (p_name VARCHAR2) IS 3 SELECT empno, ename, sal 4 FROM emp 5 6 WHERE ename LIKE '%SMITH%'; 7 8 v_a emp.empno%TYPE; ...
Words: 2868 - Pages: 12
...SQL> connect system/db Connected. SQL> create user section3 identify by section3; create user section3 identify by section3 * ERROR at line 1: ORA-00922: missing or invalid option SQL> create user section3 identified by section3; User created. SQL> grant connect, resource, , create view to section3; grant connect, resource, , create view to section3 * ERROR at line 1: ORA-00990: missing or invalid privilege SQL> grant connect,resource,create view to section3; Grant succeeded. SQL> disconnect Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Produ ction SQL> connect section3/section3 Connected. SQL> select * from tab; no rows selected SQL> help index Enter Help [topic] for help. @ COPY PAUSE SHUTDOWN @@ DEFINE PRINT SPOOL / DEL PROMPT SQLPLUS ACCEPT DESCRIBE QUIT START APPEND DISCONNECT RECOVER STARTUP ARCHIVE LOG EDIT REMARK STORE ATTRIBUTE EXECUTE REPFOOTER TIMING BREAK EXIT REPHEADER TTITLE BTITLE GET RESERVED WORDS (SQL) UNDEFINE CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE CLEAR HOST RUN WHENEVER OSERROR COLUMN...
Words: 302 - Pages: 2
...and invoice_total is greater than or equal to $500? A) payment_date IS NULL AND invoice_total > 500 B) payment_date IS NOT NULL OR invoice_total >= 500 C) NOT (payment_date IS NULL AND invoice_total = 500 Points Earned: 2.0/2.0 Correct Answer(s): D Correct 4. The order of precedence for the logical operators in a WHERE clause is A) Not, And, Or B) And, Or, Not C) Or, And, Not D) Not, Or, And Points Earned: 2.0/2.0 Correct Answer(s): A Correct 5. Which of the following types of SQL statements is not a DML statement? A) INSERT B) UPDATE C) SELECT D) CREATE TABLE Points Earned: 2.0/2.0 Correct Answer(s): D Incorrect 6. Which of the following is not a common error when entering and executing SQL statements? A) Misspelling a keyword B) Misspelling the name of a table C) Selecting the wrong database D) Forgetting to connect to a database Points Earned: 0.0/2.0 Correct Answer(s): D Incorrect 7. When you run a SQL statement that contains a coding error, MySQL Workbench displays an error message that does not include A) an error code B) brief description...
Words: 1309 - Pages: 6
...Learning Team: Create Database, Table & Inserts Read and follow the instructions carefully and completely for submission of the Week 3 Learning Team Assignment. NOTE: This is a large assignment. Starting early in the week and giving your team time to post code for a review and assistance will help you prepare it accurately and completely. I encourage everyone to work on the assignment throughout the week and not wait until the day it is due to begin the work. Please see the Instructor Policies for Team Participation credit guidelines. REQUIRED: Coding Standards for this course are required. Please see the attached Coding Standards. CRITICAL Resource: The attached Errors Table is a resource for dealing with error messages and other potential obstacles you may encounter writing code. Optional: Read the Week 2 and 3 Handouts in the Recommended Learning Activities to assist with creating this assignment. Optional: Complete the Week 2 and 3 Learning Activity Tutorials in the Recommended Learning Activities. REQUIRED: Use the Kudler Chart of Accounts PROVIDED. Analyze the "PROVIDED" Chart of Accounts spreadsheet in order to decide how you will design the database. Plan your Data Dictionary (You will be submitting data dictionary with your final project submission, Week 5.) REQUIRED: Use the Data Dictionary Template attached to create the Data Dictionary for the Learning Team Assignment. REQUIRED: Follow the instructions in the provided...
Words: 1276 - Pages: 6
