MIS562 W2 Assignment 2

Name: _______________________

Homework (100 points) Resources:
- demo.sql
- demoaddrows.sql
- createStudent.sql
Part 1
Create the tables from demo.sql script
1. Download the demo.sql and createStudent.sql file from the Doc Sharing. Run demo.sql and createStudent.sql in MySQL Omnymbus Environment. This will create some tables and insert data into them. View the script in notepad to determine the table names that were created. Use the describe command to view the structure of the tables.
2. Please use the template below to provide your solutions. You must submit your Input SQL Query in the second column in the template and you must also include executed output of each input query below input SQL Query. Please truncate to 20 rows, if output is more than 20 rows. Any answers without executed output WILL NOT earn any credit.
3. Please use demo.sql for questions Q1 through Q12 and remaining questions use createStudent.sql
4. If you combined demo.sql and createStudent.sql under MIS562_xxxx database, then there is no need to change database.
Write SQL statements to solve the following requests.
Question (4 pts per question)
SQL statement and answer output
1. List all employee information in department 30.
SELECT *
FROM EMP
WHERE DEPTNO=30;
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
7499
ALLEN
SALESMAN
7698
1980-12-17
1600.00
300.00
30
7521
WARD
SALESMAN
7698
1980-12-17
1250.00
500.00
30
7654
MARTIN
SALESMAN
7698
1980-12-17
1250.00
1400.00
30
7698
BLAKE
MANAGER
7839
1980-12-17
2850.00
NULL
30
7844
TURNER
SALESMAN
7698
1980-12-17
1500.00
0.00
30
7900
JAMES
CLERK
7698
1980-12-17
950.00
NULL
30

2. List employees name, job, and salary that is a manager and has a salary > $1,000
SELECT ENAME, JOB, SAL
FROM EMP
WHERE JOB="MANAGER"
AND SAL > 1000;
ENAME
JOB
SAL
JONES
MANAGER
2975.00
BLAKE
MANAGER
2850.00
CLARK
MANAGER
2450.00

3. Repeat exercise 2 for any employee that is not a manager or earns a salary > $1,000
SELECT ENAME, JOB, SAL
FROM EMP
WHERE