Please enable JavaScript.
Coggle requires JavaScript to display documents.
Data manipulation using SQL (Creating SQL Databases and Tables…
Data manipulation
using SQL
SQL
What Is SQL
■ A relational database language, It is
not a programming
language but a comprehensive database sub-language language for controlling and interacting with a database management system
■ NOT a DBMS
■ A powerful data manipulation language
–It has capabilities for: •Insertion •Update •Deletion •Query •Protection
■ Also designed for end users
■ Non-procedural –We have to show ‘what is needed’and not ‘how’, like in ‘relational algebra’ –Is similar more to ‘relational calculus’
■ Used in two ways:
–Interactive
–Programmatic: Dynamic / Embedded
Standard versions of SQL
■ SQL-86 or SQL1
■ SQL-92 or SQL2
■ SQL-99 or SQL3
will extend SQL with object oriented and other recent developments, such as XML
Role of SQL
■ A database programming language
■ A database administration language
■ A client/server language
■ A distributed database language
■ It is vendor independent
■ If a user was dissatisfied with a particular DBMS he could switch products easily without much overhead, as both would follow the same language standard.
■ Client applications relatively portable
■ Programmer skills are portable
■ Supports many different client processes --end-users, applications, developers, etc
■ Database servers use SQL to request services from each other
Creating SQL
Databases and
Tables
.Defining tables and views
Specifying integrity constraints
Creating a Database
Data Control Language (DCL)
■ GRANT - Gives user access privileges
■ REVOKE - Removes privileges
■ COMMIT - Ends current transaction
■ ROLLBACK - Aborts current transaction
Data Manipulation Language (DML)
■ SELECT - Retrieves data
■ INSERT INSERT - Adds new rows of data
■ DELETE DELETE - Removes row of data
■ UPDATE- Modifies existing data
■ DECLARE - Defines a cursor for query
■ EXPLAIN - Describes data access for a query
■ OPEN - Opens a cursor to retrieve query results
■ FETCH - Retrieves a row of query
■ CLOSE - Closes a cursor
Data Definition Language (DDL)
■ CREATE TABLE - Adds new table
■ DROP TABLE - Removes existing tables
■ ALTER TABLE - Modifies structure of tables
■ CREATE VIEW CREATE VIEW - Adds a new view
■ DROP VIEW - Removes a view
■ CREATE INDEX CREATE INDEX - Build an index for a column
■ DROP INDEX - Removes an index
■ CREATE SYNONYM - Defines an alias for a database object
■ DROP SYNONYM - Remove an alias
■ COMMENTS - Describes a table or column
■ LABEL - Defines a title for a table or column
SQL Data Type
■ CHAR(length) - Fixed length character strings
■ CHARACTER
■ INT - Integer numbers
■ INTEGER
■ SMALLINT - Small integer numbers
■ NUMERIC(precision,scale)
■ NUMBER(precision,scale) - Integer or Decimal numbers
■ DECIMAL(precision, scale)
■ DEC(precision,scale)
■ FLOAT(precision) - Floating points numbers
■ REAL - Low-precision - floating point no.
■ DOUBLE PRECISION - High-precision floating point no
.Creating indexes
Data Integrity
■ Invalid data added to data base
■ Existing data modified to a incorrect value
■ Changes made lost due to system error or power failure
■ Changes partially applied
Types of integrity constraints
■ Required Data (NOT NULL)
■ Validity Checking (CHECK)
■ Entity Integrity (PRIMARY KEY & NOT NULL)
■ Referential Integrity (FOREIGN KEY)
■ Business Rules (ASSERTION, TRIGGER)
■ Consistency (CASCADE, RESTRICT, SET NULL)
Selecting Data
Single Table
Multiple Table
Queries
Functions
Sub queries
Data Insertion,
Updating
and Deletion
Updating Data
Deleting Data
Inserting Data