Hi Folks,
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
==>> 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.
ODBC and JDBC
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
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
