Structured Query Language (SQL)

Introduction

Structured Query Language (SQL) is domain-specific language for managing data in Relational Database Management System (DBMS). Even through SQL is standardized by ANSI and ISO, the SQL could not be completely portable across different database.

  • Query
  • Data Manipulation Language
  • Transaction Controls
  • Data Definition Language
  • Data Control Language

 

Implementation

  • Oracle
  • Microsoft SQL Server
  • SQLite

 

Reference

Oracle Performance Testing for Composite Key Using Cost

Composite Key

-- CREATE DUMMY TABLE FOR TESTING
CREATE TABLE DUMMY_TABLE1 (
  COL1 VARCHAR(100),
  COL2 VARCHAR(100),
  COL3 VARCHAR(100), 
  COL4 VARCHAR(100),
  COL5 VARCHAR(100),
  COL6 VARCHAR(100),
  COL7 VARCHAR(100),
  COL8 VARCHAR(100),
  COL9 VARCHAR(100),
  COL10 VARCHAR(100)
);

-- GENERATE DUMMY DATA
BEGIN
  FOR I IN 1..1000000
  LOOP
    INSERT INTO DUMMY_TABLE1 VALUES (I, I, I, I, I, I, I, I, I, I);
  END LOOP;
  COMMIT;
END; 
/ 

SELECT * FROM DUMMY_TABLE1 WHERE COL1 = '1';
-- COST: 3021

CREATE INDEX DUMMY_INDEX1 ON DUMMY_TABLE1 (COL1, COL2, COL3);
CREATE INDEX DNUMMY_INDEX2 ON DUMMY_TABLE1 (COL4);

-- COMPOSITE INDEX AND QUERY INCLUDING FIRST COLUMN OF THE COMPOSITE KEY
SELECT * FROM DUMMY_TABLE1 WHERE COL1 = '1';
-- COST: 819
SELECT * FROM DUMMY_TABLE1 WHERE COL1 = '1' AND COL2 = '1';
-- COST: 7
SELECT * FROM DUMMY_TABLE1 WHERE COL1 = '1' AND COL2 = '1' AND COL3 = '1'
-- COST: 4

SELECT * FROM DUMMY_TABLE1 WHERE COL2 = '1';
-- COST: 3021

SELECT * FROM DUMMY_TABLE1 WHERE COL4 = '4';
-- COST: 801

-- DROP TABLE
DROP TABLE DUMMY_TABLE1;

Conclusion

For composite index,

  • The first cols of the index must be matched in order to improve the performance.
  • For same condition, the more cols you included in composite index would reduce performance

 

 

 

 

SQLs for Tables

The below is the tables storing meta data of table and relationship information.

  User Tables All Table
Table List USER_TABLES ALL_TABLES
Columns of Tables USER_TAB_COLS ALL_TAB_COLS
Constraint List USER_CONSTRAINTS ALL_CONSTRAINTS
Columns of Constraint USER_CONS_COLUMNS ALL_CONS_COLUMNS
Index List USER_INDEXES ALL_INDEXES
Columns of Index USER_IND_COLUMNS ALL_IND_COLUMNS

 

Query Table List

SELECT TABLE_NAME FROM USER_TABLES;
SELECT * FROM USER_TABLES

Query Columns of a specialized table

DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT COLUMN_NAME FROM USER_TAB_COLS WHERE TABLE_NAME = '&TABLE_NAME';
DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT FROM USER_TAB_COLS WHERE TABLE_NAME = '&TABLE_NAME';
DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT * FROM USER_TAB_COLS WHERE TABLE_NAME = '&TABLE_NAME';

Query Constraints

SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS;

Primary Key

DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT B.COLUMN_NAME FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONS_COLUMNS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'P'
AND A.TABLE_NAME = '&TABLE_NAME'
;
DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT B.TABLE_NAME, B.COLUMN_NAME FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONS_COLUMNS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'P'
AND A.TABLE_NAME = '&TABLE_NAME'
;

 

Foreign Key

Information of the table linking to other tables.

Query Result Columns: 

  1. Foreign Key columns,
  2. Foreign Table
  3. Foreign Table Primary Key Columns
DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT 
  D.COLUMN_NAME,                  -- 1. FOREIGN KEY COLUMN OF THE TABLE
  C.TABLE_NAME AS FOREIGN_TABLE,  -- 2. FOREIGN TABLE
  C.COLUMN_NAME AS FOREIGN_COLUMN -- 3. FOREIGN TABEL PRIMARY KEY COLUMNS
FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONSTRAINTS B
 ON A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS C
 ON B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS D
 ON A.CONSTRAINT_NAME = D.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'R'
AND A.TABLE_NAME = '&TABLE_NAME'
;

Information of other tables linking to the tables.

Query Result Columns:

  1. Primary Key Column
  2. Foreign Table
  3. Foreign Table Foreign Key Column
DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT 
  D.COLUMN_NAME,                  -- 1. PRIMARY KEY COLUMN OF THE TABLE
  C.TABLE_NAME AS FOREIGN_TABLE,  -- 2. FOREIGN TABLE
  D.COLUMN_NAME AS FOREIGN_COLUMN -- 3. FOREIGN TABLE FOREIGN KEY
FROM USER_CONSTRAINTS A 
LEFT JOIN USER_CONSTRAINTS B
 ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS C
 ON B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS D
 ON A.CONSTRAINT_NAME = D.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'P'
AND A.TABLE_NAME = '&TABLE_NAME'
;

Information of other tables linking to the table and the tables linking to other tables.

DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT 
 'FOREIGN' AS KEY_TYPE,          -- 1. KEY TYPE OF THE COLUMN (SECOND COLUMN)
 D.COLUMN_NAME,                  -- 2. FOREIGN KEY COLUMN OF THE TABLE
 C.TABLE_NAME AS FOREIGN_TABLE,  -- 3. FOREIGN TABLE
 C.COLUMN_NAME AS FOREIGN_COLUMN -- 4. FOREIGN TABEL PRIMARY KEY COLUMNS
FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONSTRAINTS B
 ON A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS C
 ON B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS D
 ON A.CONSTRAINT_NAME = D.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'R'
AND A.TABLE_NAME = '&TABLE_NAME'
UNION
SELECT 
 'PRIMARY' AS KEY_TYPE,           -- 1. KEY TYPE OF THE COLUMN (SECOND COLUMN)
 D.COLUMN_NAME,                   -- 2. PRIMARY KEY COLUMN OF THE TABLE
 C.TABLE_NAME FOREIGN_TABLE,      -- 3. FOREIGN TABLE
 D.COLUMN_NAME AS FOREIGN_COLUMN  -- 4. FOREIGN TABLE FOREIGN KEY COLUMNS
FROM USER_CONSTRAINTS A 
LEFT JOIN USER_CONSTRAINTS B
 ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS C
 ON B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS D
 ON A.CONSTRAINT_NAME = D.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'P'
AND A.TABLE_NAME = '&TABLE_NAME'
;

Check Constraints

DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT B.COLUMN_NAME, A.SEARCH_CONDITION FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONS_COLUMNS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'C'
AND A.TABLE_NAME = '&TABLE_NAME'
;

Unique Constraints

DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT B.COLUMN_NAME FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONS_COLUMNS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'U'
AND A.TABLE_NAME = '&TABLE_NAME'
;
DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT B.TABLE_NAME, B.COLUMN_NAME FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONS_COLUMNS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'U'
AND A.TABLE_NAME = '&TABLE_NAME'
;

 

Index

Query index list

SELECT 
 B.TABLE_NAME, 
 B.COLUMN_NAME, 
 A.INDEX_TYPE,
 A.UNIQUENESS
FROM USER_INDEXES A
LEFT JOIN USER_IND_COLUMNS B
ON A.INDEX_NAME = B.INDEX_NAME
;

Query index of specialized table

DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT 
 B.TABLE_NAME, 
 B.COLUMN_NAME, 
 A.INDEX_TYPE,
 A.UNIQUENESS
FROM USER_INDEXES A
LEFT JOIN USER_IND_COLUMNS B
ON A.INDEX_NAME = B.INDEX_NAME
WHERE A.TABLE_NAME = '&TABLE_NAME'
;

PL/SQL Programming Concepts: Review

PL/SQL Run-Time Architecture

Image 699

Block Type

  • Procedure
  • Function
  • Anonymous

PL/SQL Block Structure

  • Declare
    • Variables, Cursors, User-defined exceptions
  • Begin
    • SQL Statement
    • PL/SQL Statement
  • Exception
    • Action to perform when exception occurs
  • End;

Type of Variables

  • PL/SQL Variables
    • Scalar
    • Reference
    • Large object (LOB)
    • Composite (Record, Collection)
  • Non-PL/SQL Variables
    • Bind variables

 

Reference:

http://people.inf.elte.hu/sila/DB1Pract/Oracle_PLSQL.pdf

 

 

 

 

Oracle function – DBMS_XMLGEN.GETXML

Reference: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xmlgen.htm#i1012053

Get XML format

Example 

SQL: SELECT ‘1’ AS ONE FROM DUAL;

Result: 

ONE

1

SQL: SELECT DBMS_XMLGEN.GETXML(‘select “1″ as one from dual’)  FROM DUAL;

Result:

DBMS_XMLGEN.GETXML(‘SELECT"1″ASONEFROMDUAL’)
————————————————————————–
<?xml version="1.0″?>
<ROWSET>
<ROW>
<ONE>1</ONE>
</ROW>
</ROWSET>

Java example – SQL Statements

 

package com.ittechoffice.sql;

import java.util.ArrayList;
import java.util.List;

/**
 * SqlStatementContent represent a list of SQL statement which are separated by ";" or "/"
 * @author Ben_c
 *
 */
public class SqlStatementContent {
 
 private List<String> statements;
 private String content;
 
 public SqlStatementContent(String statementContent){
 statements = new ArrayList<String>();
 init();
 }
 
 /**
 * Split the statement by ";" or "/"
 * The Comments would be skipped inclining 
 * 1. in-line comment -- 
 * 2. multiple line comment 
 */
 private void init(){
 String statement = "";
 content = content.replace("\r\n", "\n");
 String[] lines = content.split("\n");
 boolean isCommentStart = false;
 for (int i = 0; i<lines.length; i++){
 boolean isQuoteStart = false;
 String line = lines[i];
 
 for (int k=0 ; k<line.length() ; k++){
 if(line.charAt(k) == '\''){
 if (!isQuoteStart){
 isQuoteStart = true;
 }else{
 isQuoteStart = false;
 }
 }
 if (!isQuoteStart){
 if(line.charAt(k) == '-'){
 if (k > 0){
 if (line.charAt(k - 1) == '-'){
 break; 
 }
 }
 }
 if (line.charAt(k)== '/'){
 if (line.length() > k + 1){
 if (line.charAt(k + 1) == '*'){
 isCommentStart = true;
 }
 }
 if (k > 0){
 if (line.charAt(k - 1) == '*'){
 isCommentStart = false;
 continue;
 }
 }
 }
 
 if (!isCommentStart){
 if (line.charAt(k) == '/' || line.charAt(k) == ';'){
 if (statement.trim().equals("")){
 statement = "";
 continue;
 }
 statements.add(statement);
 statement = "";
 continue;
 }
 if (k == 0 ){
 statement += " ";
 }
 statement += line.charAt(k); 
 }
 }
 }
 }
 }
 
 
}

 

 

SQL – Query Q&A

Q1: Why sysdate and data of date format cannot be checked by “="?

ANS:

Because sysdate contain time information.

Alternative: use to_date() in sysdate and the data.

Q2: What is the difference between Slash and Commit

ANS:

Reference:

https://community.oracle.com/thread/1068784?tstart=0

https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12004.htm

Q3: What is the difference between Union All and Union

ANS:

Union would only show distinct record while Union All would should duplicate records.

 

Create Table SQL Statement Information

For a create table SQL statement, it would contain the information of:

  1. Table Name
  2. Column Name
  3. Column Data Type
  4. Column Data Length
  5. Column Data Length Unit
  6. Not Null
  7. Encrypted
  8. Primary Key Constraint
  9. Default Value
  10. Generated Always
  11. Table Space
  12. Storage
  13. Comment

 

Example 

From: https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm

CREATE TABLE hr.admin_emp (
         empno      NUMBER(5) PRIMARY KEY,
         ename      VARCHAR2(15) NOT NULL,
         ssn        NUMBER(9) ENCRYPT,
         job        VARCHAR2(10),
         mgr        NUMBER(5),
         hiredate   DATE DEFAULT (sysdate),
         photo      BLOB,
         sal        NUMBER(7,2),
         hrly_rate  NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),
         comm       NUMBER(7,2),
         deptno     NUMBER(3) NOT NULL
                     CONSTRAINT admin_dept_fkey REFERENCES hr.departments
                     (department_id))
   TABLESPACE admin_tbs
   STORAGE ( INITIAL 50K);

COMMENT ON TABLE hr.admin_emp IS 'Enhanced employee table';

Create Table Statement Info