Database Operations

Create a database named 'My_BIS325' and two tables: 'Employee' and 'Projects'. The 'Employee' table has columns for first name, last name, middle initial, home phone, social security number, home address, home state, home city, salary, and email. The 'Projects' table has columns for project ID and project name. Insert three employees into the database, including Brian Finnegan with a social security number of 123-45-6789. Update Brian's salary to $175000. Update Julia Waite's salary by 50%. Drop the 'Secret_Projects' table and truncate it before dropping it. Delete all employees with salaries greater than $100000. Finally, change the primary key of the 'Employee' table from social security number to a composite key of first name and last name.

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