SQL Create Table Commands with Default Values in Columns

In this article, we're going to comprehend SQL 'Create' table statements with default values in the columns. For that purpose we need the problem table structures first, let's see:

a) Table Name:  Product_Master

    Description:  Used to store product information.      

Column Name	Data Type	Size	Attributes
Productno	varchar2	6	Primary key, the first letter must start with ‘P’
Description	varchar2	15	Not null
Profitpercent	number	4,2	Not null
Unitmeasure	varchar2	10	Not null
Qtyonhand	number	8	Not null
reorderLvl	number	8	Not null
Sellprice	number	8,2	Not null, cannot be 0
Costprice	number	8,2	Not null, cannot be 0

The SQL query for the above table will be:-

Create table Product_Master(  
  Productno Varchar2(6) Check (Productno LIKE 'P%') Primary Key,
  Description Varchar2(15) Not Null,
  Profitpercent Number (4,2) Not Null,
  Unitmeasure Varchar2(10) Not Null,
  Qtyonhand Number (8) Not Null,
  reorderLvl Number (8) Not Null,
  Sellprice Number (8,2) Check(Sellprice<>0) Not Null,
  Costprice Number (8,2) Check(Costprice<>0) Not Null
);

Output:-

Productno	Description	Profitpercent	Unitmeasure	Qtyonhand	reorderLvl	Sellprice	Costprice
empty

Here, in the above table, we have to use the check option to validate the constraints on the columns. For example, the primary key Productno should start with the letter 'P'. Here primary key column Productno starts with P% which means the first letter always has to be P, and then the rest of the letters or numbers will not be checked.

In the next columns the constraint is not null, so that'll be provided in the create table statement with the constraint not null implicitly.

But, in the last columns, the constraints are not null & not 0 value. So, here again, we have to check the column names Sellprice & Costprice is not equal to 0 or not. If all the conditions are satisfied, then the proper table will be created.

Now, let's see another example from a different table structure-

Table: SALESMAN_MASTER    

    Description:  Used to store salesman information working for the company.

Column Name	Data Type	Size	Attributes
SALESMANNO	varchar2	6	Primary key, the first letter must start with ‘P’
SALESMANNAME	varchar2	15	Not null
ADDRESS1	varchar2	30	Not null
ADDRESS2	varchar2	10
CITY	number	8
PINCODE	number	8
STATE	varchar2	20
SALAMT	number	8,2	Not null, cannot be 0
TGTTOGET	number	6,2	Not null, cannot be 0
YTDSALES	number	6,2	Not null

The last table is also pretty much the same as the first table. Here also the primary key column SALESMANNO has the attribute where all the values should start with the letter 'P'. SALAMT, and TGTTOGET columns have the attribute as the value cannot be 0. So, pretty much the same as the previous one. I think it will not create any problems before development.

Anyways, let's see the SQL query for this table also:-

Create table SALESMAN_MASTER(  
 SALESMANNO Varchar2(6) Check (SALESMANNO LIKE 'P%') Primary Key,
 SALESMANNAME Varchar2(15) Not Null,
 ADDRESS1 Varchar2(30) Not Null,
 ADDRESS2 Varchar2(10),
 CITY Varchar2(8),
 PINCODE Number (8),
 STATE Varchar2(20),
 SALAMT Number (8,2) Check(SALAMT<>0) Not Null,
 TGTTOGET Number (6,2) Check(TGTTOGET<>0) Not Null,
 YTDSALES Number (6,2) Not Null
);

Output:-

Queries for Next Tables:-

create table sales_order(
orderno varchar2(10) primary key check(orderno like 'O%'),
orderdate date,
clientno varchar2(8) references client_master(clientno),
delytype varchar2(8) default 'F' check(delytype in('P','F')), 
billedyn varchar2(6),
salesmanno varchar2(6),
delydate date,
orderstatus varchar2(10) check(orderstatus in('IN PROCESS','FULLFILLED','BACKORDER','CANCELLED')));

insert into sales_order values('O1001','12/2/1999','C2301','F','Good','S101','22/7/1999','BACKORDER');

Create table sales_order_details(

orderno varchar2(6) references sales_order(orderno),
productno varchar2(6) references product_master(productno),
qtyordered number(8),
qtydisp number(8),
productrate number(10),
primary key(orderno,productno));

insert into sales_order_details values('O1001','P1201',101,56,5000);

Create table Product_Master(  

  Productno Varchar2(6) Check (Productno LIKE 'P%') Primary Key,
  Description Varchar2(15) Not Null,
  Profitpercent Number (4,2) Not Null,
  Unitmeasure Varchar2(10) Not Null,
  Qtyonhand Number (8) Not Null,
  reorderLvl Number (8) Not Null,
  Sellprice Number (8,2) Check(Sellprice<>0) Not Null,
  Costprice Number (8,2) Check(Costprice<>0) Not Null
);

insert into Product_Master values('P1201','Very Good',34.40,'120KG',6020,1900,1045.30,989.45);

create view vw_sales as select sales_order.orderno,sales_order.orderdate,sales_order.orderstatus,sales_order_details.productno,sales_order_details.qtyordered  

from sales_order,sales_order_details WHERE orderstatus = 'BACKORDER';

select * from vw_sales;

I hope you'll get the desired result after running the SQL command on your end. For any kind of query don't forget to mention them in the comments section.