Week 1 Submission
Homework (100 points)
Part 1
Create the tables from demo.sql script
Download and
Copy the demo.sql file from the Doc Share to the C:\temp directory. At the sql prompt enter the command @V:\temp\demo.sql. 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; | 2. List employees name, job, and salary that is a manager and has a salary > $1,000 | SELECT ename,job,sal FROM emp WHERE sal > 1000 AND job = 'MANAGER'; | 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 sal > 1000 AND job != 'MANAGER'; | 4. Show all employee names and salary that earn between $1,000 anod $2,000. Use the between operator. | SELECT ename,sal FROM emp WHERE sal between 1000 and 2000; | 5. Select all employees that are in department 10 and 30. Use the IN operator. | SELECT * FROM EMP WHERE DEPTNO IN (10,30); | 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%'; | 7. Select all employees with an “A” as the second character of their name. Use a wildcard. | SELECT * FROM EMP WHERE ENAME LIKE 'A%'; | 8. List the employee names in alphabetical sequence. | SELECT ENAME FROM EMP ORDER BY ENAME ASC; | 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,SAL DESC; | 10. Show a list of different jobs. Eliminate repeating values. | SELECT DISTINCT
Part 1
Create the tables from demo.sql script
Download and
Copy the demo.sql file from the Doc Share to the C:\temp directory. At the sql prompt enter the command @V:\temp\demo.sql. 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; | 2. List employees name, job, and salary that is a manager and has a salary > $1,000 | SELECT ename,job,sal FROM emp WHERE sal > 1000 AND job = 'MANAGER'; | 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 sal > 1000 AND job != 'MANAGER'; | 4. Show all employee names and salary that earn between $1,000 anod $2,000. Use the between operator. | SELECT ename,sal FROM emp WHERE sal between 1000 and 2000; | 5. Select all employees that are in department 10 and 30. Use the IN operator. | SELECT * FROM EMP WHERE DEPTNO IN (10,30); | 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%'; | 7. Select all employees with an “A” as the second character of their name. Use a wildcard. | SELECT * FROM EMP WHERE ENAME LIKE 'A%'; | 8. List the employee names in alphabetical sequence. | SELECT ENAME FROM EMP ORDER BY ENAME ASC; | 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,SAL DESC; | 10. Show a list of different jobs. Eliminate repeating values. | SELECT DISTINCT