SQL SESSION 6

SESSION – 06

Challan_Header

Column Name	Data Type	Size	Attribute Constraints
challan_no	Varchar2	6	Primary Key/ First two letter to start with ‘CH’.
S_order_no	Varchar2	6	Foreign Key references s_order_no of            sales_order  table
challan_date	Date		Not null
Billed_yn	varchar2	1	‘Y’ or ‘N’ Default is ‘N’

Challan_Header:-

Write the SQL commands for,

a) creating the above ‘Challan_Header’ table.

b) inserting the values- CH9001,O19001,12-dec-95, Y

CH6865,O46865,12-nov-95, Y

CH3965,O10008,12-oct-95, N

c) Displaying challan numbers more than CH6000 and

billed one.

Solution:

a) Creating the above ‘Challan_Header’ table.

Step1:

CREATE TABLE Sales_Order(

S_Order_No VARCHAR(6) PRIMARY KEY);

Step2:

CREATE TABLE Challan_Header(

Challan_no VARCHAR(6) NOT NULL PRIMARY KEY,

S_order_no VARCHAR(6),

Challan_date Date NOT NULL,

Billed_yn VARCHAR(1),

CONSTRAINT Sorders FOREIGN KEY (s_order_no)

REFERENCES Sales_Order(s_order_no));

b) Inserting the values- CH9001,O19001,12-dec-95, Y

CH6865,O46865,12-nov-95, Y

CH3965,O10008,12-oct-95, N

INSERT INTO Challan_Header VALUES(

'CH6865','O46865','12-nov-95', 'Y');

INSERT INTO Challan_Header VALUES(

'CH9001','O19001','12-dec-95','Y');

INSERT INTO Challan_Header VALUES(

'CH3965','O10008','12-oct-95','N');

c) Displaying challan numbers more than CH6000 and billed one.

SELECT challan_no

FROM Challan_Header

WHERE challan_no > 'CH6000' AND billed_yn NOT LIKE 'N';