SQL Homework #3: DDL and DML

The assignment content is about SQL (Structured Query Language) homework #3 that deals with DDL (Data Definition Language) and DML (Data Manipulation Language). It involves creating a database named 'My_BIS325' and two tables, 'Employee' and 'Projects'. The 'Employee' table has various columns such as first name, last name, home phone number, social security number, etc. The assignment also includes INSERT statements to add data to the tables, SELECT statement to retrieve data, ALTER TABLE statements to modify table structures, UPDATE statements to update existing data, TRUNCATE and DROP statements to delete or remove data from a table.

0 | P a g e
SQL HW #3: DDL and DML SQL
STUDENT NAME:
11 October 2016

1. Create database My_BIS325;
2. CREATE TABLE Employee (
first_name varchar(50) NOT NULL,
last_name varchar(75) ,
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)
);
3. CREATE TABLE Projects
(
Project_ID int NOT NULL AUTO_INCREMENT,
Project_name varchar(250) NOT NULL,
PRIMARY KEY (Project_ID)
);
4. INSERT INTO Projects(Project_name) VALUES ('Alpha');
INSERT INTO Projects(Project_name) VALUES ('Beta');
OUTPUT:
5. INSERT INTO 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;
1 | P a g e

6. INSERT INTO Employee (first_name, last_name, middle_initial, home_phone,
social_security_number, home_address, home_state, home_city, salary, email)
VALUES('Julia', 'Waite', 'S', '547.478.4787', '365-254-784', '47 District', 'PS', 'Boston',
'20000', '[e-mail]');
OUTPUT:
7. ALTER TABLE Employee ADD zipcode VARCHAR(10);
DESC EMPLOYEE;
8. ALTER TABLE Employee DROP column middle_initial;
9. RENAME TABLE Projects TO Secret_Projects;
2 | P a g e

10. UPDATE Employee SET salary = 175000 WHERE social_security_number = '123-45-
6789';
11. UPDATE Employee SET salary = (salary + (50/100 * salary)) WHERE
social_security_number='365-254-784';
12. TRUNCATE table Secret_Projects;
13. DROP TABLE Secret_Projects;
14. DELETE FROM Employee WHERE salary > 100000;
15. ALTER TABLE Employee DROP PRIMARY KEY ;
ALTER TABLE employee ADD PRIMARY KEY ( first_name, last_name);
3 | P a g e