DATA CLEANING - MYSQL
DATA CLEANING - MYSQL
---Inspecting the data
select *
from staff_id;
--- Changing the table name
ALTER TABLE staff_id RENAME TO staff_data;
--- checking to see if table was renamed
select count(*)
from staff_data;
--- renaming a column
alter table `project2`.`staff_data`
rename column `id` to staff_id;
--- checking for duplicate records
select staff_id from staff_data
group by staff_id having count(staff_id) >1;
select staff_id, first_name from staff_data
group by staff_id, first_name having count(staff_id) >1;
--- checking to see how many times a record was duplicated
select staff_id, first_name, count(staff_id) from staff_data
group by staff_id, first_name having count(staff_id)>1;
--- selecting the duplicates
select * from staff_data where staff_id in (select staff_id from staff_data
group by staff_id having count(staff_id) >1 );
EXTRACTING DATA FROM TABLE
create table Users
(User_ID int,
First_name varchar (100),
Last_name varchar (100),
City varchar (100),
Primary Key (User_ID));
insert into Users values (1, 'John', 'Adam', 'Aba'),
(2,'Ejima', 'Grace', 'Aba'),
(3, 'Ebele', 'Udoh', 'Owerri'),
(4, 'Jessica', 'Udanoh', 'Abakiliki'),
(5, 'Mimi', 'Shedrack', 'Jos'),
(6, 'Eze', 'Mike', 'Port Harcourt'),
(7, 'Lucky', 'John', 'Asaba'),
(8, 'Joy', 'Amiaka', 'Warri South'),
(9, 'Ebiere', 'Josiah', 'Uyo'),
(10, 'Gabriel', 'Chukwu', 'Ugwuata'),
(11, 'Chetachi', 'Ezedike', 'Enugwu'),
(12, 'Samuel', 'Christian', 'Port Harcourt'),
(13, 'Ifeoma', 'Henry', 'Owerri'),
(14, 'Glory', 'Godwill', 'Uyo'),
(15, 'Chizoba', 'Nnamdi', 'Asaba'),
(16, 'Mercy', 'Osas', 'Benin'),
(17, 'Mirabel', 'Johnbosco', 'Calabar'),
(18, 'Gift', 'Tamuno', 'Port Harcourt'),
(19, 'Bridget', 'Amanga', 'Yenegoa'),
(20, 'Ralph', 'Okpaso', 'Umuahia');
--- checking our newly created table
select *
from Users;
--- deleting null values from our table
delete from users
where User_ID = NULL;
--- checking to see if the null values have been deleted
select*
from Users
-- creating a View
create view Gee as
select first_name, last_name
from Users
where first_name like 'G%';
--- grouping users by cities
select city, count(city) as count
from Users
group by city;
--- restricting our result by using the "Having Clause"
select city, count(city) as count
from Users
group by city
having count>1
--- retrieving users whose city is Aba/PortHarcourt
select first_name, last_name
from users
where city like 'Aba' or 'Port Harcourt';
--- retrieving users whose first name starts with G
SELECT first_name, last_name
from users
where first_name like 'G%';