Cursor-style processing involves retrieving data from the cursor, one row at a time.

SQL and Oracle SQL (Week - 6,7,8 & 9)

Avimanyou Vatsa, Ph.D.

Department of Computer Science Fairleigh Dickinson University

Slides are adapted and modified from c© Carlos Coronel, Steven Morris, Silberschatz, Korth, and Sudarshan. All rights reserved.

September 23, 2020

Last Week, Quick Review

CLO – ER Diagram (individual) Extended Entity Relationship (EER) Model Specialization & Generalization (Inheritance) Time Variant Data ERD with FAN Trap Redundant Relationship Quiz – EERD

This Week, Learning Objectives

The basic commands and functions of SQL How to use SQL for data administration (to create tables and indexes)

How to use SQL for data manipulation (to add, modify, delete, and retrieve data)

How to use SQL to query a database for useful information How to use the advanced SQL JOIN operator syntax About the different types of subqueries and correlated queries How to use SQL functions to manipulate dates, strings, and other data

About the relational set operators UNION, UNION ALL, INTERSECT, and MINUS

How to create and use views and updatable views How to create and use triggers and stored procedures How to create embedded SQL

Structured Query Language (SQL)

Categories of SQL functions:

Data definition language (DDL) Data manipulation language (DML)

Nonprocedural language with basic command vocabulary set of less than 100 words

Differences in SQL dialects are minor

Command or Option Description

CREATE SCHEMA Authorization Create a database schema

CREATE TA- BLE Create new table in the user’s database schema

NOT NULL Ensure that a column will not have NULL values

UNIQUE Ensure that a column will not have duplicate values

PRIMARY KEY Define a primary key for a table

FOREIGN KEY Define a foreign key for a table

DEFAULT Define a default value for a column (when no value is given)

CHECK Validate data in an attribute

Command or Option Description

CREATE INDEX Create an index for a table

CREATE VIEW Create a dynamic subset of rows and columns from one or more tables

ALTER TABLE Modifies a table’s defination (adds, modifies, or deletes attributes or constraints)

CREATE TA- BLE AS create a new table based on a query in the user’s database schema

DROP TABLE Permanently deletes a table (and its data)

DROP INDEX Permanently deletes an index

DROP VIEW Permanently deletes a view

Command or Option Description

INSERT Insert row(s) into a table

SELECT Selects attributes from rows in one or more ta- bles or views

WHERE Restricts the selection of rows based on a con- ditional expression

GROUP BY Groups the selected rows based on one or more attributes

HAVING Restricts the selection of grouped rows based on a condition

ORDER BY Orders the selected rows based on one or more attributes

UPDATE Modifies an attribute’s values in one or more table’s rows

DELETE

COMMIT

ROLLBACK

=, <,>,≤,≥ , <>, ! =

AND, OR, NOT

BETWEEN

IS NULL

Description

Deletes one or more rows from a table

Permanently saves data changes

Restores data to its original values

Comparision Operators

Used in conditional expressions

Logical Operators

Used in conditional expressions

Special Operators

Checks whether an attribute value is within a range

Checks whether an attribute value is null

Command or Option Description

LIKE Checks whether an attribute value matches a given string pattern

IN Checks whether an attribute value matches any value within a value list

EXISTS Checks whether a subquery returns any row

DISTINCT Limits values to unique values

Aggregate Functions Used with SELECT to return mathematical summaries on columns

COUNT Returns the number of rows with non-null values for a given columns

MIN Returns the minimum attribute value found in a given column

Command or Option Description

MAX Returns the maximum attribute value found in a given column

SUM Returns the sum of all values for a given column

AVG Returns the average of all values for a given column

Oracle & Front-End Resources

1 Reference manual: https://docs.oracle.com/en/

database/oracle/oracle-database/19/index.html

2 Web technology tools: https://www.w3schools.com/ 3 Programming Courses: CSCI 6810/4380(Java) or CSCI

3444/6844/Info 4844 - Programming for Internet, C, C++, C#, Python.

Oracle SQL

Open Oracle code file (*.sql) and execute on sqlplus command prompt.

Install ATOM (texteditor) on your machine

1 Go to: https://github.com/atom/atom

2 Go to releases >> Latest release instead of Beta version >>

atom-mac.zip

3 Download and open it and drag and drop in applications. 4 Open Atom >> Go to “ATOM” menu and open “Install Shell

Command”.

Download and execute “musicForeignKey-Oracle.sql” code on SQL terminal.

Download “createStudent.sql” & “sql book add tables.sql” & “AppendixD.pdf” from blackboard and open *.sql code in any text editor (Atom, textEdit, notepad ++, vi, emacs, etc).

Optional: Copy these *.sql files on opus server using following command at terminal or Power Shell:

scp -r Source address of file Destination address of file

Common SQL Data Types

Creating Table Structures

Use one line per column (attribute) definition Use spaces to line up attribute characteristics and constraints Table and attribute names are fully capitalized

Features of table creating command sequence:

NOT NULL specification ensures data entry UNIQUE specification avoids duplicated values

Table d

SQL Constraints

SQL Indexes

When primary key is declared, DBMS automatically creates unique index

The CREATE INDEX command can be used to create indexes on the basis of any selected attribute UNIQUE qualifier prevents a value that has been used before Composite indexes prevent data duplication

To delete an index use the DROP INDEX command

NOTE: SQL names are case insensitive (i. e. you may use upper or lower-case letters.)

User–Defined Types & Domains

create type construct in SQL creates user–defined type:

create type Dollars as numeric (12,2) final

Example: create table department(dept name varchar (20), building varchar (15), budget Dollars);

Creates user-defined domain types

create domain person name char(20) not null

Example: create domain degree level varchar(10) constraint degree level test

check (value in (’Bachelors’, ’Masters’, ’Doctorate’));

Large–Object Types

Large objects (photos, videos, CAD files, etc.) are stored as a large object:

blob: binary large object

Object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system)

clob: character large object

Object is a large collection of character data

Note: When a query returns a large object, a pointer is returned rather than the large object itself.

Advanced SQL

SQL Join Expression Styles

Subqueries & Correlated Queries

Subquery is a query inside another query Subquery can return:

One single value - One column and one row A list of values - One column and multiple rows A virtual table - Multicolumn, multirow set of values No value - Output of the outer query might result in an error or a null empty set

Inline subquery: Subquery expression included in the attribute list that must return one value

Correlated Subqueries

Executes once for each row in the outer query Inner query references a column of the outer subquery Can be used with the EXISTS special operator

SQL Functions

Functions always use a numerical, date, or string value Value may be part of a command or may be an attribute located in a table

Function may appear anywhere in an SQL statement where a value or an attribute can be used

SQL Functions:

Virtual Tables: Creating a View

View: Virtual table based on a SELECT query Base tables: Tables on which the view is based CREATE VIEW statement: Data definition command that stores the subquery specification in the data dictionary

CREATE VIEW command: CREATE VIEW viewname AS SELECT query

Updatable Views

Used to update attributes in any base tables used in the view Batch update routine: Pools multiple transactions into a single batch to update a master table field in a single operation

Updatable view restrictions

GROUP BY expressions or aggregate functions cannot be used Set operators cannot be used JOINs or group operators cannot be used

Authorization Specification in SQL

grant statement is used to confer authorization

grant < privilege list > on < relation name or view name > to < user list > < user list > is:

a user-id

public, which allows all valid users the privilege granted

A user role

Granting a privilege on a view does not imply granting any privileges on the underlying relations.

The grantor of the privilege must already hold the privilege on the specified item (or be the database administrator). Privileges in SQL: Select, Insert, Update, Delete, All Privilleges.

Revoke

revoke statement is used to revoke authorization.

Revoking Authorization in SQL

revoke < privilege list > on < relation name or view name > from < user list >

Example:

revoke select on branch from U1,U2,U3

Examples

Conditional Updates

update instructor set salary = case when salary = 100000 then salary ∗ 1.05 else salary ∗ 1.03 end

Recompute and update tot creds value for all students update student S

set tot cred = (select sum(credits) from takes, course where takes.course id = course.course id and S.ID= takes.ID. and takes.grade <> ’F’ and takes.grade is not null)

PL/SQL

Procedural SQL

Performs a conditional or looping operation by isolating critical code and making all application programs call the shared code:

Yields better maintenance and logic control

Persistent stored module (PSM): Block of code containing:

Standard SQL statements Procedural extensions that is stored and executed at the DBMS server

Procedural Language SQL (PL/SQL)

Use and storage of procedural code and SQL statements within the database Merging of SQL and traditional programming constructs

Procedural code is executed as a unit by DBMS when invoked by end user

End users can use PL/SQL to create:

Anonymous PL/SQL blocks and triggers Stored procedures and PL/SQL functions

PL/SQL Basic Data Types

Triggers

Procedural SQL code automatically invoked by RDBMS when given data manipulation event occurs

Parts of a trigger definition

Triggering timing – Indicates when triggers PL/SQL code executes Triggering event – Statement that causes the trigger to execute Triggering level – Statement- and row-level Triggering action – PL/SQL code enclosed between the BEGIN and END keywords

DROP TRIGGER trigger name command: Deletes a trigger without deleting the table Trigger action based on DML predicates: Actions depend on the type of DML statement that fires the trigger

PL/SQL Processing with Cursors

Cursor: Special construct used to hold data rows returned by a SQL query

Implicit cursor: Automatically created when SQL statement returns only one value

Explicit cursor: Holds the output of a SQL statement that may return two or more rows

Cursor–style processing involves retrieving data from the cursor one row at a time:

Current row is copied to PL/SQL variables Cursor Attributes:

Cursor Processing Commands

Stored Procedures

Named collection of procedural and SQL statements Advantages

Reduce network traffic and increase performance Reduce code duplication by means of code isolation and code sharing

PL/SQL Stored Functions

Stored function: Named group of procedural and SQL statements that returns a value:

As indicated by a RETURN statement in its program code

Can be invoked only from within stored procedures or triggers

SQL statements contained within an application programming language

Host language: Any language that contains embedded SQL statements

Differences between SQL and procedural languages: Run-time mismatch

SQL is executed one instruction at a time Host language runs at client side in its own memory space

Processing mismatch

Conventional programming languages process one data element at a time

Newer programming environments manipulate data sets in a cohesive manner

Embedded SQL

Data type mismatch

Data types provided by SQL might not match data types used in different host languages

Embedded SQL framework defines

Standard syntax to identify embedded SQL code within the host language

Standard syntax to identify host variables Communication area used to exchange status and error information between SQL and host language

Static SQL: Programmer uses predefined SQL statements and parameters

SQL statements will not change while application is running

Dynamic SQL: SQL statement is generated at run time

Attribute list and condition are not known until end user specifies them Slower than static SQL Requires more computer resources

SQL Status and Error Reporting Variables

Thanks!

Thank You! Questions?

Applied Sciences

Architecture and Design

Biology

Business & Finance

Chemistry

Computer Science

Geography

Geology

Education

Engineering

English

Environmental science

Spanish

Government

History

Human Resource Management

Information Systems

Law

Literature

Mathematics

Nursing

Physics

Political Science

Psychology

Reading

Science

Social Science

Home

Blog

Archive

Contact

google+twitterfacebook

Copyright © 2019 HomeworkMarket.com