SQL Introduction Chapter 7, sections 1 & 4. Introduction to SQL. Introduction to SQL. Introduction to SQL

(1)

SQL – Introduction

Chapter 7, sections 1 & 4 • Objectives – To understand Oracle’s SQL client interface – Understand the difference between commands to the interface and SQL language. – To understand the Oracle server

environment, and how to access that environment.

• Outline – SQL – what is it? – Basic functions • DDL coomands • DML commands – Basic Queries – System Catalog • Querying User_Tables • Querying All_Tables – SQLPlus commands – SQLPlus installation 1 CS275 Fall 2010

Introduction to SQL

• SQL meets ideal database language requirements: – SQL is relatively easy to learn

– Basic command set has vocabulary of less than 100 words

– Nonprocedural – a 4th_{Generation Language} • Specifies what you want, not how to retrieve it. – American National Standards Institute (ANSI)

prescribes a standard SQL – Several SQL dialects exist

2

CS275 Fall 2010

Introduction to SQL

SQL coverage fits into two categories: • Data definition (DDL)

– Create database objects and define rights to those objects

• Data manipulation (DML)

– To retrieve, insert, update, and delete data.

3 CS275 Fall 2010

Introduction to SQL

• SQL DML Commands • SQL DDL Commands 4 CS275 Fall 2010

(2)

Listing Table Rows

• SELECT

– Used to list contents of table – Syntax:

SELECT column, column,… FROM user.tablename; SELECT * FROM user.tablename;

• The column list represents one or more attributes, separated by commas

• Asterisk can be used as wildcard character to list all attributes

– Useful to view the data in a table before and again after changes/additions have been made.

5

CS275 Fall 2010

Using the Select Command

• Examples:

6

CS275 Fall 2010

SELECT EMPNO, ENAME FROM CS275.EMP; SELECT * FROM CS275.EMP;

SELECT DNAME, LOC FROM CS275.DEPT;

Selecting Rows with

Conditional Restrictions

• Select partial table contents by placing

restrictions on rows to be included in output – Add conditional restrictions to SELECT statement,

using WHERE clause • Syntax: SELECT columnlist FROM tablelist [ WHERE conditionlist ] ; 7 CS275 Fall 2010

Basic Queries – ‘Where’

• Partial Listing of Table Contents

SELECT <column(s)> FROM <table name> WHERE <conditions>; Example:

SELECT ENAME, JOB FROM EMP WHERE DEPTNO = 20; 8 CS275 Fall 2010 ENAME JOB --- ---SMITH CLERK JONES MANAGER SCOTT ANALYST ADAMS CLERK FORD ANALYST

(3)

Queries – ‘Where’

• The where clause selects rows based a logical comparison operator given two operands.

9

CS275 Fall 2010

Queries – ‘Where’

SELECT EMPNO, ENAME

FROM EMP WHERE SAL >= 2500; 10 CS275 Fall 2010 EMPNO ENAME --- ---7566 JONES 7698 BLAKE 7788 SCOTT 7839 KING 7902 FORD

Queries – ‘Where’

SELECT DEPTNO, DNAME

FROM DEPT

WHERE LOC != 'NEW YORK‘;

11 CS275 Fall 2010 DEPTNO DNAME --- ---20 RESEARCH 30 SALES 40 OPERATIONS

Queries – ‘Where’

SELECT EMPNO, ENAME, DEPTNO

FROM EMP

WHERE HIREDATE >= '01-JAN-85‘;

12

CS275 Fall 2010

EMPNO ENAME DEPTNO

--- ---

---7788 SCOTT 20 7876 ADAMS 20

(4)

The user’s data dictionary

• Looking at your environment

– Displays tables for current logon >select table_name from user_tables; >select * from tab;

– Displays other objects for current logon >select * from user_objects;

– Looking at a table’s data dictionary with a SQLPlus Command

sqlplus> describe user.<tablename>

13

CS275 - Fall 2010

The user’s data dictionary

• Looking at objects and tables in the database

where you are not the owner.

– Displays tables or objects

>select table_name from all_tables; >select * from all_objects;

– To display tables or objects from a specific account where you have read access rights

>select table_name

from all_tables where owner = ‘CS275’;

14

CS275 - Fall 2010

Oracle Interface - Basic SQLPLUS

• Getting on the Data Base

– SQLPLUS

• Programs->Oracle->Appl.Dev.->SQL plus • Vista: Run as Administrator

– Logon: Name/password/database • Name: FirstName*

• Password:L#

• Database – CITORACLE & CITORACLE_W – Logging off

• EXIT

*_{duplicates need to add the first character of the last name}

15

CS275 Fall 2010

Review Basic SQLPlus Interface

• MENU System

– File: Open, Save, Spool

• Open will set your local directory path – Editing

• Invoking the editor • Temp file afied.buf • Saving from the editor – Options -> Environment

• Pagesize • Linesize

• These also have command line alternatives

16

(5)

Basic SQLPLUS Commands

>RUN [/]

Executes the sql statement in the buffer >LIST [l]

Displays current statement in the buffer >DESCRIBE

Displays attribute information for a table >EDIT

invokes host operating system text editor >EXIT

terminates the client session

17

CS275 Fall 2010

Basic SQLPLUS

• Scripting SQL from TEXT FILES

>GET <filename>

Retrieves SQL statement from a file and puts it into the active buffer.

>SAVE <filename>

Saves the active SQL statement in named file >START <filename>

Allows execution of multiple SQL statements from a file

>SPOOL <filename>

Saves session history in named file

18

CS275 - Fall 2010

Working off-site - SQLPlus

• Installing the SQL Client on your own machine.

– Must have WinXP, Vista, or Win7, but higher versions than the limited ‘home’ version.

– Download the directions & file from our website resource page.

• Connecting to the Database.

– You will first need to put the tnsnames file into your Oracle home. This file will be given out in class or you may get it in the Lab.

– Open the SQL Client and logon using the “Wireless” database connection.

• For Vista, use Run as an administrator

19

CS275 - Fall 2010

Summary

• SQL commands can be divided into two overall categories:

– Data definition language commands – Data manipulation language commands • The basic SELECT statement

– Where clause

• Using the SQLPlus environment – Basic SQLplus commands – Setting the environment. – Creating and using script files

20