All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database
[PDF] advanced sql server books
[PDF] advanced sql server queries interview questions
[PDF] advanced sql server tutorial
[PDF] advanced sql server tutorial pdf
[PDF] advanced sql server tutorial point
[PDF] advanced sql tuning burleson pdf
[PDF] advanced sql tuning tips and techniques pdf
[PDF] advanced stored procedure examples in oracle
[PDF] advanced stored procedure examples in sql server pdf
[PDF] advanced t sql books
[PDF] advanced t sql querying and programming pdf
[PDF] advanced test in c and embedded system programming pdf free download
[PDF] advanced transition words for college essays
[PDF] advanced video editing app for android
[PDF] advanced vocabulary exercises with answers
SQL & ADVANCED SQL
Marcin Blaszczyk (CERN IT-DB)
marcin.blaszczyk@cern.ch
¾Goal of this tutorial:
9Present the overview of basic SQL capabilities
9Explain several selected advanced SQL features
¾Outline
9Introduction
9SQL basics
9Joins & Complex queries
9Analytical functions & Set operators
9Other DB objects (Sequences, Synonyms, DBlinks, Views & Mviews)
9Indexes & IOTs
9Partitioning
9Undo & Flashback technologies
AGENDA
Oracle Tutorials 5th of May 2012
¾Objective: be able to perform the basic operation of the
RDBMS data model
9create, modify the layout of a table
9remove a table from the user schema
9insert data into the table
9retrieve and manipulate data from one or more tables
9update/ delete data in a table
9+
Some more advanced modifications
SQL LANGUAGE
Oracle Tutorials 5th of May 2012
¾Structured Query Language
9Programing language
9Designed to mange data in relational databases
¾DDL Data Definition Language
9Creating, replacing, altering, and dropping objects
9Example: DROP TABLE [TABLE];
¾DML Data Modification Language
9Inserting, updating, and deleting rows in a table
9Example: DELETE FROM [TABLE];
¾DCL Data Control Language
9Controlling access to the database and its objects
9Example: GRANT SELECT ON [TABLE] TO [USER];
SQL LANGUAGE (2)
Oracle Tutorials 5th of May 2012
STATEMENT DESCRIPTION
SELECT Data Retrieval
INSERT
UPDATE
DELETE
Data Manipulation Language (DML)
CREATE
ALTER DROP
RENAME
TRUNCATE
Data Definition Language (DDL)
GRANT
REVOKE Data Control Language (DCL)
COMMIT
ROLLBACK Transaction Control
Oracle Tutorials
SQL LANGUAGE(3)
5th of May 2012
¾A transaction is a sequence of SQL Statements that Oracle treats as a single unit of work ¾A transaction must be commited or rolled back: COMMIT; - makes permanent the database changes you made during the transaction. ROLLBACK; - ends the current transaction and undoes any changes made since the transaction began.
¾Check COMMIT settings in your Client Tool (eg
AUTOCOMMIT, EXITCOMMIT in SQL*Plus)
¾UNDO tablespace:
9circular buffer
9records all actions of transactions
9used when rolling back a transaction
Oracle Tutorials
TRANSACTION & UNDO
5th of May 2012
STATEMENT DESCRIPTION
SELECT Data Retrieval
INSERT
UPDATE
DELETE
Data Manipulation Language (DML)
CREATE
ALTER DROP
RENAME
TRUNCATE
Data Definition Language (DDL)
GRANT
REVOKE Data Control Language (DCL)
COMMIT
ROLLBACK Transaction Control
Oracle Tutorials
SQL LANGUAGE(3)
5th of May 2012
¾Collection of logical structures of data
9called schema objects
9tables, views, indexes, synonyms, sequences, packages, triggers,
OLQNV "
¾Owned by a database user
9same name of the user
¾Schema objects can be created and manipulated with SQL
SELECT user DROM dual;
SHOW USER; (in SQL*Plus)
DATABASE SCHEMA (USER)
Oracle Tutorials 5th of May 2012
¾Define the table layout:
9table identifier
9column identifiers and data types
9column constraints,
9default values
9integrity constraints
9relational constraints
CREATE A TABLE
Oracle Tutorials
CREATE TABLE employees (
employee_id NUMBER(6) NOT NULL, first_name VARCHAR2(20), last_name VARCHAR2(25), hire_date DATE DEFAULT SYSDATE, department_id NUMBER(4), salary NUMBER(8,2) CHECK (salary > 0));
SQL> describe employees
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
DEPARTMENT_ID NUMBER(4)
SALARY NUMBER(8,2)
5th of May 2012
¾Each value has a datatype
9defines the domain of values that each column can contain
9when you create a table, you must specify a datatype for each
of its columns
¾ANSI defines a common set
9Oracle has its set of built-in types
9User-defined types
DATATYPES
Oracle Tutorials
ANSI data type Oracle
integer NUMBER(38) smallint NUMBER(38) numeric(p,s) NUMBER(p,s) varchar(n) VARCHAR2(n) char(n) CHAR(n) float NUMBER real NUMBER
5th of May 2012
SELECT [ALL | DISTINCT] column1[,column2]
FROM table1[,table2]
[WHERE "conditions"] [GROUP BY "column-list"] [HAVING "conditions] [ORDER BY "column-list" [ASC | DESC] ]
Oracle Tutorials
SELECT STATEMENT
SELECT d.department_name,
sum(e.salary)as DEPT_AL
FROM departments d, employees e
WHERE d.department_id = e.department_id
GROUP BY d.department_name
HAVING SUM(e.salary) > 10000
ORDER BY department_name;
DEPARTMENT_NAME DEPT_SAL
Accounting 20300
Executive 58000
Finance 51600
IT 28800
Marketing 19000
Purchasing 24900
Sales 304500
Shipping 156400
5th of May 2012
¾Insert some data
INSERT INTO table1 values(value-list) ;
INSERT INTO table1(column-list) values(value-list);
INSERT INTO table1(column-list)
SELECT values(value-list);
COMMIT;
¾Update
UPDATE table1 SET column = value;
COMMIT;
¾Delete
DELETE FROM table1;
COMMIT;
INSERT, UPDATE, DELETE (DML)
Oracle Tutorials 5th of May 2012
¾Modify the name:
ALTER TABLE employees RENAME TO newemployees;
¾Modify the layout:
ALTER TABLE employees ADD (salary NUMBER(7));
ALTER TABLE employees RENAME COLUMN id TO emp_id;
ALTER TABLE employees DROP(hiredate);
¾But also:
9Add/modify/drop constraints
9Enable/Disable constraints
90RGLI\ PRUH MGYMQŃHG SURSHUPLHV"
ALTER TABLE (DDL)
Oracle Tutorials 5th of May 2012
¾NOT NULL / CHECK
ALTER TABLE employees MODIFY last_name NOT NULL;
ALTER TABLE employees MODIFY salary CHECK (salary > 1000);
¾PRIMARY KEY
ALTER TABLE employees ADD PRIMARY KEY(emp_id);
¾FOREIGN KEY
ALTER TABLE employees ADD FOREIGN KEY(dept_id) REFERENCES departments(department_id);
¾Constraints errors:
9ORA-02290: check constraint (owner.constraintname) violated DURING INSERT
9ORA-02291: integrity constraint (owner.constraintname) violated - parent key not found DURING INSERT
9ORA-02292:violated integrity constraint (owner.constraintname)- child record found DURING DELETE
CONSTRAINTS (DDL)
Oracle Tutorials 5th of May 2012
¾special value that means
9unavailable
9unassigned
9unknown
9inapplicable
¾not equivalent to
9zero
9blank space
SELECT * FROM [TABLE] where id = 0;
SELECT * FROM [TABLE] where id IS NULL;
¾Often used as default
NULL VALUE
Oracle Tutorials 5th of May 2012
¾special one-row table present by default in all Oracle database installations
9Accessible to all users
9Examples of use:
SELECT SYSDATE FROM DUAL;
SELECT USER FROM DUAL;
-- equal to SHOW USER in SQL*Plus ¾Create really big table in one command - use dual;
CREATE TABLE BIG_TABLE
AS SELECT trunc(dbms_random.value(0,20)) RANDOM_INT
FROM DUAL
CONNECT BY LEVEL <= 100000;
DUAL TABLE
Oracle Tutorials
SQL> describe dual;
Name Null? Type
DUMMY VARCHAR2(1)
5th of May 2012
¾What is the difference between:
DELETE FROM employees;
vs
TRUNCATE TABLE employees;
9DML vs DDL commands?
Is COMMIT essential? In which case?
9Generate UNDO segments?
Which is more efficient?
DELETE ALL ROWS FROM A TABLE
Oracle Tutorials 5th of May 2012
EQUIJOIN Values in the two corresponding columns of the different tables must be equal
NON-EQUIJOIN
The relationship between the columns of the
different tables must be other than equal
OUTERJOIN
(LEFT, RIGHT, FULL)
It returns also the rows that do not satisfy the
join condition
SELFJOIN Joining data in a table to itself
TYPES OF JOINS
Oracle Tutorials 5th of May 2012
EQUIJOIN
Oracle Tutorials
EMP_NAME EMP_DEPTNO
KING 10
BLAKE 30
CLARK 10
DEPT_NO DEPT_NAME
10 ACCOUNTING
quotesdbs_dbs22.pdfusesText_28
[PDF] Chapter 8 Advanced SQL