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 DISTINCT BranchNo from staff;

Select staffno, fname, lname, salary from staff;


List monthly salaries for all staff.(Calculated field)
Select staffno, fname, lname, salary/12 from staff;
Select staffno, fname, lname, salary/12 AS Monthly_salary from staff;


UPDATE ALL ROWS

Give all staff a 3% raise.
UPDATE staff 
SET salary = salary * 1.03;


UPDATE SPECIFIC ROWS

Give all managers a 5% raise
UPDATE staff
SET salary = salary * 1.05
WHERE position = 'Manager';


UPDATE Multiple columns

Promote David Ford (StaffNo = 'SG14') to Manager and change his salary to $28000
UPDATE staff
SET position = 'Manager', salary= 28000
WHERE staffno = 'SG14';


Delete ALL ROWS

Delete FROM Staff; (Won't give any warning)


Comparison Search Condition
List all staff with salary greater than 10000
Select staffno, fname, lname, position, salary FROM staff
WHERE salary > 10000;

Compund Comparison Search condition
List the addresses of all Branch offices in London or Glascow

Select BranchNo, Street, City, Postcode FROM Branch
WHERE city = 'London' OR city = 'Glascow';


Range Search Condition
List all staff with salary between 20000 and 30000
Select staffno, fname, lname, position, salary FROM staff
WHERE salary BETWEEN 20000 AND 30000;

Set membership search condition
List all Managers and Deputy managers (IN/NOT IN)
Select staffno, fname, lname, position, salary FROM staff
WHERE position IN ('Manager', 'Assistant');

Equivalent to:
Select staffno, fname, lname, position, salary FROM staff
WHERE position ='Manager' OR position = 'Assistant';

Exercise:
Select staffno, fname, lname, position, salary FROM staff
WHERE position IN ('Manager', 'Supervisor');

Pattern match search condition (LIKE / LIKE NOT)
Find all staff, sounds like 'ee' in their last name (embedded string)
 Select staffno, fname, lname, position, salary FROM staff 
WHERE LName LIKE '%ee%'; 

Find all branches with the string 'Glas' in the CITY.
Select * from Branch WHERE city LIKE 'Glas%';

Select * from Branch WHERE city LIKE '%cow';

*********
Single Coulmn Ordering

Produce a list of salaries of all staff in descnding order of salary 
Select staffno, fname, lname, salary FROM staff ORDER BY salary DESC;

Select staffno, fname, lname, salary FROM staff ORDER BY salary;

Select staffno, fname, lname, salary FROM staff ORDER BY salary ASC;


Multiple Column Ordering

Select staffno, fname, lname, salary FROM staff ORDER BY lname, salary ASC;

Select staffno, fname, lname, salary FROM staff ORDER BY lname, salary DESC;


Using the SQL aggregate functions
COUNT, SUM, AVG, MIN, MAX

Count the Number of staff
Select COUNT(*) AS count
FROM staff ;

Total salary for Managers
Select SUM(salary) As Sum From staff where position = 'manager';
Select SUM(salary) As Sum From staff where position = 'Manager';

Total number of managers and sum of their salaries
Select COUNT(staffno) As Count, SUM(salary) As Sum From staff where position = 'Manager';

To find the minimum, maximum and average salary of staff
Select MIN(salary) AS Minimum_Salary, MAX(salary) AS Maximum_Salary, AVG(salary) AS Average_Salary FROM Staff;

Use of GROUP BY (another example Grouping students by Major)
Find the number of staff working in each Branch and the sum of their salaries
Branch
	Sum(salary)

Major
	Average(GPA)

Select BranchNo, COUNT(staffno) As count, SUM(salary) As Sum FROM staff 
GROUP BY branchno;


Select BranchNo, COUNT(staffno) As count, SUM(salary) As Sum FROM staff 
GROUP BY branchno ORDER BY branchno DESC;

*****************************

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);


Select BranchNo, COUNT(staffno) As count, SUM(salary) As Sum FROM Staff
GROUP BY branchno ORDER BY branchno;


Use of Having (Filter Groups)
For each branch office with more than one member of staff, find the number of staff working in each branch and the sum of their salaries.

Select branchno, COUNT(staffno) AS count, SUM(salary) AS Sum FROM Staff Group By branchno 
HAVING COUNT(staffno)>1 ORDER By branchno;
****************************

SUBQUERIES
List the staff who work in the Branch at '163 Main St'.
List the staff who work in the Branch at '163 main st'

SELECT staffno, fname, lname, position FROM Staff
WHERE branchno IN (Select branchno FROM Branch WHERE street = '163 Main St');

Using a Subquery with an aggregate function

List all staff whose salary is greater than the average salary.

Select staffno, fname, lname, position, salary From staff WHERE 
salary > avg(salary); (Error: Group function is not allowed here)


Select staffno, fname, lname, position, salary From staff WHERE 
salary > (Select avg(salary) from staff);

Exercise:
1. List all staff with salary, average salary and whose salary is
 greater than the average salary. 

Select staffno, fname, lname, position, salary,
 (select avg(salary)  from staff) As avg_sal from staff 
WHERE salary > (Select avg(salary) from staff);


2. List all staff whose salary is greater than the average salary and list by how much.

Select staffno, fname, lname, position, 
salary-(select avg(salary) from staff) As Sal_Diff From staff 
WHERE salary > (Select avg(salary) from staff);

ANY/SOME

Find staff whose salary is larger than the salary of atleast one member at Branch B003.

Select staffno, fname, lname, position, salary from staff
Where salary > SOME (Select salary from staff where branchno = 'B003');
(ANY/SOME: The inner query produces a set of salaries, 
and the outer query selects the minimum as the criteria value)


ALL

Find all staff whose salry is larger than the salary of every member of staff
 at branch B003.

Select staffNo, fName, lName, position, salary FROM staff
 Where salary > ALL (Select salary from staff where branchno = 'B003');
(ALL: The inner query produces a set of salaries, and the outer query 
selects the maximum as the criteria value)