Creating Table Branch

Create Table Branch(BranchNo Varchar2(6) Not Null,
Street Varchar2(20) Not Null, 
City Varchar2(10) Not Null,
Postcode Varchar2(8) Not Null,
Primary key (BranchNo));





Insert Records

Insert into Branch values('B005', '22 Deer Rd', 'London', 'SW1 4EH');
Insert into Branch values('B007', '16 Argll St', 'Aberdeen', 'AB2 3SU');
Insert into Branch values('B003', '163 Main St', 'Glascow', 'G11 9QX');
Insert into Branch values('B004', '32 Manse Rd', 'Bristol', 'BS99 1Nz');
Insert into Branch values('B002', '56 Clover Rd', 'London', 'NW10 6EU');

Delete Records

 Delete From Branch Where BranchNo = 'Boo5';

Alter TAble Branch
drop Constraint branchno;

Alter table branch drop primary key cascade;

Unconditional selection of records

Select BranchNo, Street, City, Postcode from Branch;
Select * from Branch;
Select BranchNo, Street, City from Branch;
Select BranchNo, City from Branch;

Conditional selection of records

Select * from Branch Where BranchNo = 'B003';
Select * from Branch Where BranchNo = 'B003' AND City = 'London';
Select * from Branch Where BranchNo = 'B003' AND City = 'glascow';
Select * from Branch Where BranchNo = 'B003' AND City = 'Glascow';
Select * from Branch Where BranchNo = 'B003' OR City = 'London';


Creating Table Staff

Drop table Staff;

Create table staff(
StaffNo varchar2(8),
fname varchar2(8),
lname varchar2(8),
position varchar2(15),
sex varchar2(8),
DOB Date,
salary Number(7, 2),
BranchNo varchar2(6),
Primary Key (StaffNo), 
Foreign Key (BranchNo) references Branch);

Inserting records

Insert into Staff values('SL21', 'John', 'White', 'Manager', 'M', '1-Oct-1945', 30000, 'B005');
Insert into Staff values('SG37', 'Ann', 'Beech', 'Assistant', 'F', '10-Nov-1960', 12000, 'B003');
Insert into Staff values('SG14', 'David', 'Ford', 'Supervisor', 'M', '24-Mar-1958', 18000, 'B003');
Insert into Staff values('SA9', 'Mary', 'Howe', 'Assistant', 'F', '19-Feb-1970', 9000, 'B007');
Insert into Staff values('SG5', 'Susan', 'Brand', 'Manager', 'F', '3-Jun-1940', 24000, 'B003');
Insert into Staff values('SL41', 'Julie', 'Lee', 'Assistant', 'F', '13-Jun-1965', 9000, 'B005');




Select BranchNo from staff;
Select BranchNo from Branch;




Select DISTINCT BranchNo from staff;

Select staffno, fname, lname, salary from staff;


Calculated Fields
List of monthly salary for all staff

SELECT staffno, fname, lname, salary/12 FROM staff;
SELECT staffno, fname, lname, salary/12 As Monthly_Salary FROM staff;