Notes

create database lecture2;
use supermarket;
create table order_header (
order_number Decimal(10,0) NOT NULL,
order_date DATE,
sales_person VARCHAR(25),
bill_to VARCHAR(35),
Bill_to_address VARCHAR(20),
Bill_to_city VARCHAR(20),
Bill_to_state VARCHAR(20),
Bill_to_Zip VARCHAR(20),
PRIMARY KEY (order_number));

insert into order_header(order_number,order_date,sales_person,bill_to,Bill_to_address,Bill_to_city,Bill_to_state,Bill_to_Zip)values('01','02-19-2019','Amandeep','Naresh','Brampton','Brampton','Ontario','144004');



create table Orders_items(
order_number Decimal(10,0) NOT NULL,
line_item Decimal(4,0) NOT NULL,
part_number VARCHAR(20) NOT NULL,
quantaty Decimal(4,0)
PRIMARY KEY (order_number,line_item),
FOREIGN KEY (order_number)
REFERENCES order_header(order_number));

create table my_company (
employee_id INTEGER,
store_id SMALLINT ,
first_name VARCHAR(20) NOT NULL,
m_name VARCHAR(20),
last_name VARCHAR(20) NOT NULL,
hire_date DATE NOT NULL,
department_id SMALLINT NOT NULL,
hourly_rate DECIMAL(5,2) NOT NULL check(hourly_rate >=14.25),
hour_worked DECIMAL(3,1) NOT NULL,
sale DECIMAL(7,0) NOT NULL,
nationality varchar(20) default ('India'),
gender char(1) NOT NULL check (gender='F' or gender='M'),
PRIMARY KEY (employee_id));

insert into my_company
values (13,23, 'Nabin', 'N','kumar','09-02-2017',33, 15.9, 7.3, 13.0, default,'M');

insert into my_company
values(11,29,'Rahul','R', 'kumar','03-02-2019',31,15.9, 7.8,13.1,default,'F');


select * from my_company;

create table my_companyall (
employee_id INTEGER,
store_id SMALLINT ,
first_name VARCHAR(20) NOT NULL,
m_name VARCHAR(20),
last_name VARCHAR(20) NOT NULL,
hire_date DATE NOT NULL,
department_id SMALLINT NOT NULL,
hourly_rate DECIMAL(5,2) NOT NULL check(hourly_rate >=14.25),
hour_worked DECIMAL(3,1) NOT NULL,
sale DECIMAL(7,0) NOT NULL,
nationality varchar(20) default ('India'),
gender char(1) NOT NULL check (gender='F' or gender='M'),
PRIMARY KEY (employee_id));

insert into my_companyall select * from my_company;