Advanced SQL in DBMS - PK Series

Published on Friday, February 05, 2016
Hi Folks, 
advanced sql
Continuing with SQL, we'll discuss some areas of advanced SQL today. Queries will be handled in comments section, use it freely!

==>> This article is a part of PK series IT

User defined types

1. Create type - creates user defined type
create type Dollars as numeric (12,2) final

2. Create domain - creates user defined domain types

create domain person_name char (20) not null

Difference between both is that domains can have constraints, such as NOT NULL, on them. We'll discuss constraints in next section. 

Integrity Constraints

Integrity constraints protect against accidental damage to database, by ensuring the authorized changes to database do not result in loss of data consistency. E.g - Employee must have NOT NULL phone number, Number of leaves shouldn't be more than 20 etc. 

Types of constraints

1. NOT NULL - indicates that a column can not store NULL value

Emp_name char(15) NOT NULL

2. UNIQUE - ensures that each row for a column must have a unique value

emp_id int NOT NULL UNIQUE

3. CHECK - ensures that value in a column meets a specific condition like limit the value range etc

emp id int NOT NULL,
CHECK (emp_id >999)

4. DEFAULT - It is used to insert a default value into a column. It will be added to all new records, if no other value is specified. 

emp_id int NOT NULL, 
city varchar(255) DEFAULT 'Mumbai'

5. Referential Integrity - It ensures that value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation. It is achieved through primary key and unique key. 


1. Open Database Connectivity (ODBC)

ODBC is a standard for application program to communicate with a database server. Application program interface (API) opens a connection with database using SQLConnect( ), sends queries using SQLExecDirect & gets back results using SQLFetch( ). ODBC first provides an SQL environment and then a database connection handle.  

Application such as GUI, Spreadsheets etc can use ODBC. It works with C,C++, VisualBasic and C#.

2. Java Database Connectivity (JDBC)

Its a JAVA API for communicating with database systems supporting SQL. JDBC also supports metadata retrieval such as querying about relations present in database and names, types of relation attributes. Flow of communication goes as follows:
  • Open a connection
  • create a statement object
  • execute queries using statement object to send queries and fetch results
  • exception mechanisms to handle error
It works with Java language. 

SQL Functions

Aggregate Functions
SQL aggregate functions return a single value calculated from values in the column. Some important aggregate functions are as follows:

  • AVG( ) - return avg value
  • COUNT( ) - return number of rows
  • FIRST( ) - first value
  • LAST( ) - last value
  • MAX( ) - largest value
  • MIN( ) - smallest value
  • SUM( ) - returns the sum
Scalar Functions
These return a single value based on input value. Important scalar functions are as follows:

  • UCASE( ) - convert to upper case
  • LCASE ( ) - lower case
  • MID( ) - extract characters from a text field
  • LEN( ) - return length of text field
  • ROUND( ) - round off to specified decimal
  • NOW( ) - return current system data and time
  • FORMAT( ) - format field to be displayed 

Quote of the day

Great talent finds happiness in execution.      - J W Goethe

                                                                                                                                          Deepak A

Can I help you?

ramandeep singh

Hey I am Ramandeep Singh. I am determined to help students prepaing for RBI, SEBI, NABARD and IBPS exams. Do you want me to help you ?

Join my class here
    Follow me:
Close Menu
Close Menu