Name:
1. Sajid M. Choudhury
2. Muntazir Ahmed
3. Saiful Islam
4. Amina Ahmed Hammayo
5. Mohammad Iqbal Hossain
Homework (100 points) Resources:
- demo.sql
- demoaddrows.sql
Part 1
Create the tables from demo.sql script
Download the demo.sql file from the Doc Sharing. Run demo.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. Please use the template below to provide your solutions.
Write SQL statements to solve the following requests.
Question (4 pts per question)
SQL statement or answer
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 JOB<>”MANAGER”
OR SAL > 1000;
ENAME
JOB
SAL
SMITH
CLERK
800.00
ALLEN
SALESMAN
1600.00
WARD
SALESMAN
1250.00
JONES
MANAGER
2975.00
MARTIN
SALESMAN
1250.00
BLAKE
MANAGER
2850.00
CLARK
MANAGER
2450.00
SCOTT
ANALYST
3000.00
KING
PRESIDENT
5000.00
TURNER
SALESMAN
1500.00
ADAMS
CLERK
1100.00
JAMES
CLERK
950.00
FORD
ANALYST
3000.00
MILLER
CLERK
1300.00
KIRK
CAPTAIN
6000.00
4. Show all employee names and salary that earn between $1,000 and $2,000. Use the between operator.
SELECT ENAME, SAL
FROM EMP
WHERE SAL BETWEEN 1000 AND 2000;
ENAME
SAL
ALLEN
1600.00
WARD
1250.00
MARTIN
1250.00
TURNER
1500.00
ADAMS
1100.00
MILLER
1300.00
5. Select all employees that are in department 10 and 30. Use the IN operator.
SELECT *
FROM EMP
WHERE DEPTNO IN (10,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
7782
CLARK
MANAGER
7839
1980-12-17
2450.00
NULL
10
7839
KING
PRESIDENT
NULL
1980-12-17
5000.00
NULL
10
7844
TURNER
SALESMAN
7698
1980-12-17
1500.00
0.00
30
7900
JAMES
CLERK
7698
1980-12-17
950.00
NULL
30
7934
MILLER
CLERK
7782
1980-12-17
1300.00
NULL
10
6. Select all employee names with an “A” in the first position of the employee name. Use the substring function or a wild card.
SELECT *
FROM EMP
WHERE ENAME LIKE "A%";
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
7499
ALLEN
SALESMAN
7698
1980-12-17
1600.00
300.00
30
7876
ADAMS
CLERK
7788
1980-12-17
1100.00
NULL
20
7. Select all employees with an “A” as the second character of their name. Use a wildcard.
SELECT ENAME
FROM EMP
WHERE ENAME LIKE "_A%";
ENAME
WARD
MARTIN
JAMES
8. List the employee names in alphabetical sequence.
SELECT ENAME
FROM EMP
ORDER BY ENAME ASC;
ENAME
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
KIRK
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
9. List the job, salary, and employee name in job order and then salary in descending order.
SELECT JOB, SAL, ENAME
FROM EMP
ORDER BY JOB ASC, SAL DESC;
JOB
SAL
ENAME
ANALYST
3000.00
SCOTT
ANALYST
3000.00
FORD
CAPTAIN
6000.00
KIRK
CLERK
1300.00
MILLER
CLERK
1100.00
ADAMS
CLERK
950.00
JAMES
CLERK
800.00
SMITH
MANAGER
2975.00
JONES
MANAGER
2850.00
BLAKE
MANAGER
2450.00
CLARK
PRESIDENT
5000.00
KING
SALESMAN
1600.00
ALLEN
SALESMAN
1500.00
TURNER
SALESMAN
1250.00
WARD
SALESMAN
1250.00
MARTIN
10. Show a list of different jobs. Eliminate repeating values.
