SQL examples

testing.sql

CREATE DATABASE oinc;

USE oinc;

CREATE TABLE Employee (
	ID INT(2) auto_increment primary key,
	first_name VARCHAR(20),
	last_name VARCHAR(30),
	start_date DATE,
	salary int(6),
	city VARCHAR(20),
	description VARCHAR(20)
	);
	
# insert data
INSERT INTO Employee( First_name, Last_name, Start_date, Salary, city, description) values
( 'Jason',  'Martin',  '20060725', 1234.56, 'Toronto',   'Programmer'), 
( 'Alison', 'Mathews', '19860221', 6661.78, 'Vancouver', 'Tester'),
( 'James',  'Smith',   '19781212', 6544.78, 'Vancouver', 'Tester'),
( 'Celia',  'Rice',    '19990421', 2344.78, 'Vancouver', 'Manager'),
( 'Robert', 'Black',   '19980808', 2334.78, 'Vancouver', 'Tester'),
( 'Linda',  'Green',   '19960104', 4322.78, 'New York',  'Tester'),
( 'David',  'Larry',   '19980212', 7897.78, 'New York',  'Manager'),
( 'James',    'Cat',   '20020415', 1232.78, 'Vancouver', 'Tester');






# create and fill the table
CREATE TABLE Functions (
	ID INT(2) auto_increment primary key,
	name VARCHAR(20),
	description VARCHAR(30)
	);



INSERT INTO Functions( name, description) values
( 'Programmer', 'types code'),
( 'Tester', 'bugs the programmer'),
( 'Manager', 'earns money'),
( 'Cleaner', 'goes to the toilet and does other types of stuff');




# check how many of the same function exist
Select Employee.last_name, Employee.description, Functions.description
FROM Employee
LEFT JOIN Functions
ON Employee.description = Functions.name;


# number of people on each of the jobs
select Functions.name, count(*) from Employee
inner join Functions
	on Functions.name=Employee.description
group by Functions.name
having count(*) > 1;


# discover jobs without employees
select Functions.name from Functions
where name not in (
	select Functions.name from Functions
	inner join Employee on Functions.name=Employee.description
	);

# better
SELECT Functions.name
FROM Employee right join Functions on Employee.description=Functions.name
WHERE Employee.ID IS NULL

#
select Employee.city, count(*) as Number_employees From Employee
group by city;



select Employee.first_name, count(*) From Employee
group by first_name;



# get salaries between some values 
select * from Employee
where salary > 3000 and salary < 7000
order by salary;

select distinct city from Employee;





-- SELECT COUNT(First_name)
-- FROM (select * from Employee where salary > 2000 and salary < 4500);