Question

How can we create table?

What are the various data types available using sql

What are the various constraints that you can create. What is the difference between table level constraints and column level constraints?

table create with SQL Using oracle.

Answer 1

1. How can we create table?

Sol) Create table in SQL is using DDL (Data Defination Language) command using a CREATE TABLE statement. Table contain columns and constraints. In Table-level constraints specify a column or columns, columns have a data type and specify column constraints i.e., Column-level constraints)

Syntax:

   CREATE TABLE <table_name>(column1 datatype column_constraint,
               column2 datatype column_constraint,
               ------
               ------
               columnN datatype column_constraint,
               table_constraint);

Where <table_name) specifies new table name and create columns(column1,column2,...columnN) with datatype and and column_constraint(optional) you need to separate them by commans. The datatype are NUMBER,VARCHAR2,etc., and column constraint are NOT NULL, PRIMARY KEY, CHECK. If you need apply table constraints(optional) i.e., PRIMARY KEY, FOREIGN KEY, CHECK.

Example: Creating new table using CREATE TABLE Statement

   CREATE TABLE employee(id NUMBER,name VARCHAR2(20) NOT NULL,salary NUMBER(5,2), PRIMARY KEY(id));

   Here, column(id) is NUMBER type, column(name) is VARCHAR2(20) with column-level constraint NOT NULL and column(salary) is NUMBER with precision(decimal) values and finally table-level constraint is PRIMARY KEY(id)

2. What are the various data types available using SQL?

Sol)
Data type: Data type is associated with a specific storage format, constraints and a valid range of values. Each column value and constant in a sql statement has a datatype. In ORACLE provides built-in data types.

1. Character Datatype
2. Numeric Datatype
3. Date Datatype
4. LOB Datatype
5. RAW Datatype
6. ROWID Datatype

1. Character Datatype: Character datatypes store characters (both alpha and numerical) data in strings, with byte values corresponding to the character encoding scheme such as 7-bit ASCII, EBCDIC, etc., support oracle. In Character Datatypes are two types (CHAR or VARCHAR2)
a) CHAR datatype stores fixed-length character strings, you must specify a string length (in bytes or characters) between 1-2000 bytes for the CHAR column width. The default is 1 byte.

b) VARCHAR2 datatype stores variable-length character strings, you must specify a string length (in bytes or characters) between 1-4000 bytes for VARCHAR2 column. The default maximum size 50 characters.

2. Numeric Datatype: In Numeric Datatype store positive and negative fixed and floating-point numbers from 0-infinity. In this Numeric Datatype contains NUMBER datatype and floating-point numbers. In ORACLE DATABASE upto 38 digits of precisions.
syntax: column_name NUMBER(digits,decimal_points)

3. Date Datatype: The DATE datatype stores point-in-time values(dates and times) in a table. The DATE datatype stores the year(including the century), month, day, hours, minutes and seconds.
syntax: column_name DATE

4. LOB Datatype: The LOB(Large OBject) datatypes BLOB(Binary Large OBject), CLOB(Character Large OBject), NCLOB(National Character Large OBject) and BFILE(external binary file stored outside of the database), you can store and manipulate Large blocks of unstrctured data such as text,graphic images, video clips and sound waveforms.

5. RAW Datatype:RAW datatype used to store binary data or byte strings, it is variable-length datatype like tha VARCHAR2 characters. RAW data is always returned as hexadecimal character value. Actually, there are two types RAW and LONG RAW datatypes. LONG RAW datatype can be used to store graphics, sound, documents or arrays of binary data.
  

6. ROWID Datatype: ROWID datatype stores the address(rowid) of every row in the database. Rowid are two types
a) Physical rowid: This is store addresses of rows in ordinary tables (excluding index-organized tables), clustered table, table particitions and subpartions, indexes and index partitions and subpartitions.
b) Logical rowid: This is store the addresses of rows in index-organized tables.
Another type of rowid is UROWID (Universal rowid) is support both logical and physical rowids.

Example: SELECT ROWID, name FROM employee;

3. What are the various constraints that you can create. What is the difference between table level constraints and column level constraints?

Sol) Types of Constraints:

a) PRIMARY KEY (Unique and NOT NULL)
It is defined column or columns that uniquely identify a given row in a table. It is also define a table, you can define a single primary key on a table.
  
b) FOREIGN KEY:
This constraint used to relationship between two tables.

c) NOT NULL
This constraints are in-line constraints the that indicate that column can not contains NULL values.

d) CHECK
   This constraints validate that values in a given column meet a spefic criteria.

e) UNIQUE
   This constraints define a column or columns that must be unique in value.

Difference between Table-level and Column-level constraints:

Table-Level Constraints:
This type of constraint is checked if there is any modification to a row, the value of the column changed or not. Example CHECK constraint, if we create CHECK constraint in table level the constraint will be check each time the row has been affected by any type of change.
Example:
CREATE TABLE employee(ID NUMBER PRIMARY KEY,NAME VARCHAR2(30),AGE NUMBER NOT NULL,SALARY NUMBER(10,2), CONSTRAINT TABLE_LEVEL_CONSTRAINT CHECK(AGE BETWEEN 19 AND 45));

Column-Level Constraints:
   This type of constraint is check when the value of the column changed.
Example:
CREATE TABLE employee(ID number PRIMARY KEY,NAME VARCHAR2(20),AGE NUMBER,SALARY NUMBER(10,2), CONSTRAINT COLUMN_LEVEL_CONSTRAINT CHECK(AGE>18))