write the OUTPUT below each query. Create an SOL STATEMENT for each query and write it...

        

Transcribed Image Text:

write the OUTPUT below each query. Create an SOL STATEMENT for each query and write it on the right. Test your SQL statements and save your results. OUTPUT 1. Name and salary of all clerks. OUTPUT: 2. All the different salaries of the salesmen. OUTPUT: 3. Name, classification, and age of all students younger than 21 in order from oldest to youngest. OUTPUT: 4. The information on classes that meet in a room 10. OUTPUT: 5. List the different manager numbers of all employees who do not work on commission in order from smallest to largest. OUTPUT: SOL STATEMENT TABLE NAME: EMP EMPNO | ENAME SMITH 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 COP 3710 DATABASE MANAGEMENT SYSTEMS DEPTNO 10 20 30 40 GRADE 1 2 3 4 5 TABLE NAME: DEPT DEFAULT DATABASE TABLES JOB MGR HIREDATE SAL COMM DEPTNO 12/17/1980 800 CLERK 7902 SALESMAN 7698 ALLEN 02/20/1981 1600 300 1250 500 WARD SALESMAN 7698 02/22/1981 JONES MANAGER 7839 04/02/1981 2975 MARTIN SALESMAN 7698 09/28/1981 1250 1400 BLAKE MANAGER 7839 05/01/1981 2850 CLARK MANAGER 7839 06/09/1981 2450 SCOTT ANALYST 7566 12/09/1982 3000 11/17/1981 5000 KING PRESIDENT SALESMAN 7698 7788 09/08/1981 1500 0 01/12/1983 1100 12/03/1981 950 CLERK 7698 ANALYST 7566 12/03/1981 3000 7782 01/23/1982 1300 TABLE NAME: ENAME SMITH ALLEN WARD JONES MARTIN TURNER ADAMS CLERK JAMES FORD MILLER CLERK TABLE NAME: DNAME ACCOUNTING RESEARCH SALES OPERATIONS SALGRADE LOSAL 700 1201 1401 2001 3001 BONUS JOB CLERK SALESMAN SALESMAN MANAGER SALESMAN SAL 800 1600 1250 2975 1250 LOC NEW YORK DALLAS CHICAGO BOSTON HISAL 1200 1400 2000 3000 9999 COMM 300 500 1400 20 30 30 20 30 30 10 20 10 30 20 30 20 10 TABLE NAME: SID 100 150 200 250 300 350 400 450 COP 3710 DATABASE MANAGEMENT SYSTEMS NAME JONES PARKS BAKER GLASS BAKER RUSSEL TABLE NAME: NAME BA200 BD445 BF410 CS150 CS250 RYE JONES STUDENT TABLE NAME: STUDENT NUMBER 100 150 200 200 300 400 400 400 450 DEFAULT DATABASE TABLES GRADE LEVEL GR SO GR SN ACCOUNTING SN JR ACCOUNTING FR HISTORY SN MAJOR HISTORY ACCOUNTING CLASS MATH HISTORY ENROLLMENT MATH CLASS NAME BD445 BA200 BD445 CS250 CS150 BA200 BF410 CS250 BA200 TIME M-F9 MWF3 MWF8 MWF3 MWF12 POSITION NUMBER 1 1 2 1 1 2 1 2 3 AGE 21 19 50 50 41 20 18 24 ROOM SC110 SC213 SC213 EA304 EB210 Part II: Evaluate each SQL to determine its output and an equivalent query. 6. 7. 8. 9. 10. SOL STATEMENT select name, major from student where name like "*S" order by name select ename, comm from emp where (job = 'salesman' and comm IS NOT NULL) order by comm desc select ename, sal from emp where sal between 1200 and 1300 select deptno, count(*) from emp group by deptno select major, max(age) from student group by major order by major desc QUERY: OUTPUT: QUERY: OUTPUT: QUERY: OUTPUT: QUERY: OUTPUT: QUERY: OUTPUT: QUERY and OUTPUT Please review the SQL Power Point and complete the SQL exercise using the given Access database. Your database with the actual SQL queries must be submitted too. write the OUTPUT below each query. Create an SOL STATEMENT for each query and write it on the right. Test your SQL statements and save your results. OUTPUT 1. Name and salary of all clerks. OUTPUT: 2. All the different salaries of the salesmen. OUTPUT: 3. Name, classification, and age of all students younger than 21 in order from oldest to youngest. OUTPUT: 4. The information on classes that meet in a room 10. OUTPUT: 5. List the different manager numbers of all employees who do not work on commission in order from smallest to largest. OUTPUT: SOL STATEMENT TABLE NAME: EMP EMPNO | ENAME SMITH 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 COP 3710 DATABASE MANAGEMENT SYSTEMS DEPTNO 10 20 30 40 GRADE 1 2 3 4 5 TABLE NAME: DEPT DEFAULT DATABASE TABLES JOB MGR HIREDATE SAL COMM DEPTNO 12/17/1980 800 CLERK 7902 SALESMAN 7698 ALLEN 02/20/1981 1600 300 1250 500 WARD SALESMAN 7698 02/22/1981 JONES MANAGER 7839 04/02/1981 2975 MARTIN SALESMAN 7698 09/28/1981 1250 1400 BLAKE MANAGER 7839 05/01/1981 2850 CLARK MANAGER 7839 06/09/1981 2450 SCOTT ANALYST 7566 12/09/1982 3000 11/17/1981 5000 KING PRESIDENT SALESMAN 7698 7788 09/08/1981 1500 0 01/12/1983 1100 12/03/1981 950 CLERK 7698 ANALYST 7566 12/03/1981 3000 7782 01/23/1982 1300 TABLE NAME: ENAME SMITH ALLEN WARD JONES MARTIN TURNER ADAMS CLERK JAMES FORD MILLER CLERK TABLE NAME: DNAME ACCOUNTING RESEARCH SALES OPERATIONS SALGRADE LOSAL 700 1201 1401 2001 3001 BONUS JOB CLERK SALESMAN SALESMAN MANAGER SALESMAN SAL 800 1600 1250 2975 1250 LOC NEW YORK DALLAS CHICAGO BOSTON HISAL 1200 1400 2000 3000 9999 COMM 300 500 1400 20 30 30 20 30 30 10 20 10 30 20 30 20 10 TABLE NAME: SID 100 150 200 250 300 350 400 450 COP 3710 DATABASE MANAGEMENT SYSTEMS NAME JONES PARKS BAKER GLASS BAKER RUSSEL TABLE NAME: NAME BA200 BD445 BF410 CS150 CS250 RYE JONES STUDENT TABLE NAME: STUDENT NUMBER 100 150 200 200 300 400 400 400 450 DEFAULT DATABASE TABLES GRADE LEVEL GR SO GR SN ACCOUNTING SN JR ACCOUNTING FR HISTORY SN MAJOR HISTORY ACCOUNTING CLASS MATH HISTORY ENROLLMENT MATH CLASS NAME BD445 BA200 BD445 CS250 CS150 BA200 BF410 CS250 BA200 TIME M-F9 MWF3 MWF8 MWF3 MWF12 POSITION NUMBER 1 1 2 1 1 2 1 2 3 AGE 21 19 50 50 41 20 18 24 ROOM SC110 SC213 SC213 EA304 EB210 Part II: Evaluate each SQL to determine its output and an equivalent query. 6. 7. 8. 9. 10. SOL STATEMENT select name, major from student where name like "*S" order by name select ename, comm from emp where (job = 'salesman' and comm IS NOT NULL) order by comm desc select ename, sal from emp where sal between 1200 and 1300 select deptno, count(*) from emp group by deptno select major, max(age) from student group by major order by major desc QUERY: OUTPUT: QUERY: OUTPUT: QUERY: OUTPUT: QUERY: OUTPUT: QUERY: OUTPUT: QUERY and OUTPUT Please review the SQL Power Point and complete the SQL exercise using the given Access database. Your database with the actual SQL queries must be submitted too.