SQL Homework 3: Database Schema Design, Data Insertion, and Queries

This homework assignment focuses on SQL database creation and manipulation. The assignment includes the creation of a database, table, and insertion of data. The assignment covers SQL commands for adding, updating, and deleting data, as well as modifying the table structure, including adding and dropping columns, renaming tables, and modifying the primary key. The solution also includes the use of the truncate command and the drop table command. The assignment covers various SQL commands to create, modify, and manipulate data within a database. The provided solution demonstrates the practical application of SQL for database management and data integrity.

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