BIS325 - Database Creation and Manipulation Assignment - Fall 2024

This assignment solution demonstrates the creation and manipulation of a database using SQL commands. It begins by creating a database named `My_BIS325` and then proceeds to create a table named `Employee` with several attributes like `first_name`, `last_name`, `home_phone`, `social_security_number`, `home_address`, `salary` and `email`. The solution includes inserting data into the `Employee` table and creating another table named `Projects`. It then demonstrates various SQL commands such as `ALTER TABLE` to add and remove columns, `RENAME TABLE`, `UPDATE` to modify data, `TRUNCATE` to remove all data from a table, `DROP TABLE` to delete a table, `DELETE FROM` to remove specific rows, and `DROP PRIMARY KEY` to remove the primary key and `ADD PRIMARY KEY` to add a primary key. The solution effectively showcases basic and advanced SQL operations for managing and querying data within a database.

Create database `My_BIS325`;
CREATE TABLE `Employee` (
`first_name` varchar(50) NOT NULL,
`last_name` varchar(75) NOT NULL,
`middle_initial` varchar(1) ,
`home_phone` varchar(12) NOT NULL,
`social_security_number` varchar(11) NOT NULL,
`home_address` varchar(100) NOT NULL,
`home_state` varchar(20) NOT NULL,
`home_city` varchar(20) NOT NULL,
`salary` decimal(10,2) NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`social_security_number`)
);
CREATE TABLE IF NOT EXISTS `Projects` (
`project_ID` int(11) NOT NULL AUTO_INCREMENT,
`project_name` varchar(250) NOT NULL,
PRIMARY KEY (`project_ID`)
);
INSERT INTO `Projects` (`project_name`) VALUES ('Alpha'), ('Beta');
project_ID attribute has value 1 and 2 for alpha and beta project_name because we have
assigned auto increment to this field.
INSERT INTO `My_BIS325`.`Employee` (`first_name`, `last_name`, `middle_initial`,
`home_phone`, `social_security_number`, `home_address`, `home_state`, `home_city`, `salary`,

`email`) VALUES ('Brian', 'Finnegan', 'Q', '215.670.9308', '123-45-6789', '1420 Pine St', 'PA',
'Philadelphia', '17500', '[e-mail]');
SELECT * FROM `Employee`;
INSERT INTO `My_BIS325`.`Employee` (`first_name`, `last_name`, `middle_initial`,
`home_phone`, `social_security_number`, `home_address`, `home_state`, `home_city`, `salary`,
`email`) VALUES ('Micheal', 'Strauss', 'P', '114.154.4546', '147-258-456', '256 North Head', 'QA',
'Chicago', '18000', '[e-mail]'), ('Julia', 'Waite', 'S', '547.478.4787', '365-254-784', '47
District', 'PS', 'Boston', '20000', '[e-mail]');
ALTER TABLE `Employee` ADD `zipcode` VARCHAR(10) AFTER `email`;
SELECT * FROM `Employee`;
ALTER TABLE `Employee` DROP `middle_initial`;
RENAME TABLE `Projects` TO `Secret_Projects`;
UPDATE `Employee` SET salary='175000' WHERE social_security_number='123-45-6789';
UPDATE `Employee` SET `salary`=`salary`*1.5 WHERE social_security_number='365-254-
784';
TRUNCATE `Secret_Projects`;

DROP TABLE `Secret_Projects`;
DELETE FROM `Employee` WHERE salary>100000;
ALTER TABLE `Employee` DROP PRIMARY KEY , ADD PRIMARY KEY
( `first_name`,`last_name` );