DBS301PRACTICESOLUTIONSETCSUPPLEMENTARYNOTESMARCH20,3003

DBS301PRACTICESOLUTIONSETCSUPPLEMENTARYNOTESMARCH20,3003 -...

This preview shows page 1 - 5 out of 160 pages.

A Practice Solutions
Image of page 1

Subscribe to view the full document.

Introduction to Oracle9 i : SQL A - 2 Practice 1 Solutions 1. Initiate an i SQL*Plus session using the user ID and password provided by the instructor. 2. i SQL*Plus commands access the database. False 3. The following SELECT statement executes successfully: True SELECT last_name, job_id, salary AS Sal FROM employees; 4. The following SELECT statement executes successfully: True SELECT * FROM job_grades; 5. There are four coding errors in this statement. Can you identify them? SELECT employee_id, last_name sal x 12 ANNUAL SALARY FROM employees; The EMPLOYEES table does not contain a column called sal . The column is called SALARY . The multiplication operator is *, not x, as shown in line 2. The ANNUAL SALARY alias cannot include spaces. The alias should read ANNUAL_SALARY or be enclosed in double quotation marks. A comma is missing after the column, LAST_NAME . 6. Show the structure of the DEPARTMENTS table. Select all data from the DEPARTMENTS table. DESCRIBE departments SELECT * FROM departments; 7. Show the structure of the EMPLOYEES table. Create a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first. Provide an alias STARTDATE for the HIRE_DATE column. Save your SQL statement to a file named lab1_7.sql . DESCRIBE employees SELECT employee_id, last_name, job_id, hire_date StartDate FROM employees;
Image of page 2
Introduction to Oracle9 i : SQL A - 3 Practice 1 Solutions (continued) 8. Run your query in the file lab1_7.sql . SELECT employee_id, last_name, job_id, hire_date FROM employees; 9. Create a query to display unique job codes from the EMPLOYEES table. SELECT DISTINCT job_id FROM employees; If you have time, complete the following exercises: 10. Copy the statement from lab1_7.sql into the i SQL*Plus Edit window. Name the column headings Emp # , Employee , Job , and Hire Date , respectively. Run your query again. SELECT employee_id "Emp #", last_name "Employee", job_id "Job", hire_date "Hire Date" FROM employees; 11. Display the last name concatenated with the job ID, separated by a comma and space, and name the column Employee and Title . SELECT last_name||’, ’||job_id "Employee and Title" FROM employees; If you want an extra challenge, complete the following exercise: 12. Create a query to display all the data from the EMPLOYEES table. Separate each column by a comma. Name the column THE_OUTPUT . SELECT employee_id || ’,’ || first_name || ’,’ || last_name || ’,’ || email || ’,’ || phone_number || ’,’|| job_id || ’,’ || manager_id || ’,’ || hire_date || ’,’ || salary || ’,’ || commission_pct || ’,’ || department_id THE_OUTPUT FROM employees;
Image of page 3

Subscribe to view the full document.

Introduction to Oracle9 i : SQL A - 4 Practice 2 Solutions 1. Create a query to display the last name and salary of employees earning more than $12,000. Place your SQL statement in a text file named lab2_1.sql . Run your query.
Image of page 4
Image of page 5
  • Fall '97
  • Professor
  • Salary, emp

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern