Database

1
Database

• Conventional
• Design driven

• RDBMS
• Data driven
• Relationship between two sets of data

2
What is SQL?

• SQL stands for Structured Query Language
• SQL allows you to access a database
• SQL is an ANSI standard computer language
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert new records in a database
• SQL can delete records from a database
• SQL can update records in a database
• SQL is a standard computer language for accessing
  and manipulating databases

3
SQL Data types

• Char
• Varchar
• Number
• Date
• Blob

4
What is table?

• A database most often contains one or more
  tables. Each table is identified by a name (e.g.
  "Customers" or "Orders").
• Tables contain records (rows) with data.

C O L U M N
ROW
5
SQL Keys

• Primary key
• Unique
• No two rows can have the same value
• Foreign Key
• Primary key of another table
• Eg
• Master table dept no and dept name
• Detail table empno, empname,deptno

Primary key
Foreign key
6
SQL Constraints

• A constraint is a rule or set of rules that apply
  to a column or combination of columns.
• Rules- Eg
• Staffno between 1000 and 9999
• Basic gt 15000
• Cadre JAO and basic gt15000

7
SQL -DDL

• SQL Data Definition Language (DDL)
• The Data Definition Language (DDL) part of SQL
  permits database tables to be created or deleted.
  
• The most important DDL statements in SQL are 
• CREATE TABLE - creates a new database table
• ALTER TABLE - alters (changes) a database table
• DROP TABLE - deletes a database table

8
SQL -DML

• SQL Data Manipulation Language (DML)
• SQL language includes a syntax to update, insert,
  and delete records.
• These query and update commands together form the
  Data Manipulation Language (DML) part of SQL
• SELECT - extracts data from a database table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• INSERT INTO - inserts new data into a database
  table

9
SQL -DCL

• SQL Data Control Language (DCL)
• SQL language includes a syntax to control
  transactions
• These commands together form the Data Control
  Language (DCL) part of SQL
• COMMIT Completes the transaction into the table
  permanently
• ROLLBACK Takes back to the previous savepoint

10
What is SQLPlus?

• Oracle's extensions to SQL are called SQLPlus.
  In general, the SQLPlus commands are very useful
  when you are writing queries and generating
  reports. They give you control over headings,
  page breaks, totals, and other reporting format
  issues

11
What is Cursor?

• Cursors. Think of a cursor as a holding tank for
  a query.
• It is where the results of a single row returned
  from a query are kept.
• Once you have the data from a row, you can
  manipulate the row, use the data to update other
  tables, and so forth.

12
What is Cursor?

• Implicit
• Not defined by the user
• Explicit
• Declare, open, fetch and close

13
Other database objects

•      View
• A view is a customized presentation of a table
  or tables.
•          Synonym
• A synonym is another name for a table, view, or
  other object.
• Sequence
• This powerful Oracle tool automatically
  assigns the next unique numeric value to a
  column. This is a great feature when you want
  sequential numbers for a column.
• Database triggers
• Database triggers are activities that happen
  automatically when
• something happens to a table. For
  example, if a student takes a meal
• in the hotel, a trigger will cause the
  available balance in his account
• to be reduced.
• A trigger is really code that you create and is
  fired when a particular event happens.

14
Aggregate functions

• SUM () gives the total of all the rows,
  satisfying any conditions, of the given column,
  where the given column is numeric.
• AVG () gives the average of the given column.
• MAX () gives the largest figure in the given
  column.
• MIN () gives the smallest figure in the given
  column.
• COUNT() gives the number of rows satisfying the
  conditions.

15
What is PL/SQL?

• Oracle has developed PL/SQL (Procedural
  Language/SQL), which supports more traditional
  programming, such as loops and IF..THEN
  statements.
• When you write PL/SQL statements, you will be
  creating blocks. These blocks generally have
  three distinct sectionsDeclareBegin and End

16
What is PL/SQL?

• It may also have Exception
• This handles errors and relevant error messages
  are shown via these
• Exception
• User defined exception
• Exception as data type in declare section
• System defined exception
• Too_many_rows no_data_found