1. Write a query to display the current date. Label the column Date.
2. The HR department needs a report to display the employee number, last name, salary, and salary increased by 15.5% (expressed as a whole number) for each employee. Label the column New Salary. Place your SQL statement in a text file named lab_03_02.sql. 3. Run your query in the file lab_03_02.sql
4. Modify your query lab_03_02.sql to add a column that subtracts the old salary from the new salary. Label the column Increase. Save the contents of the file as lab_03_04.sql. Run the revised query.
5. Write a query that displays the last name (with the first letter uppercase and all other letters lowercase) and the length of the last name for all employees whose name starts with the letters J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last names. Rewrite the query so that the user is prompted to enter a letter that starts the last name. For example, if the user enters H when prompted for a letter, then the output should show all employees whose last name starts with the letter H.
Practice 4
Determine the validity of the following three statements. Circle either True or False.
1. Group functions work across many rows to produce one result per
group. True/False
2. Group functions include nulls in calculations.
True/False
3. The WHERE clause restricts rows before inclusion in a group
calculation. True/False
The HR department needs the following reports:
4. Find the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number. Place your SQL statement in a text file named lab_04_04.sql.
5. Modify the query in lab_04_04.sql to display the minimum, maximum, sum, and average salary for each job type. Resave lab_04_04.sql as lab_04_05.sql. Run the statement in lab_04_05.sql.
6、 Write a query to display the number of people with the same job.
Save the script to a file named lab_04_06.sql.
7. Determine the number of managers without listing them. Label the column Number of Managers. Hint: Use the MANAGER_ID column to determine the number of managers.
8. Find the difference between the highest and lowest salaries. Label the column DIFFERENCE.
If you have time, complete the following exercises:
9. Create a report to display the manager number and the salary of the lowest-paid
employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is $6,000 or less. Sort the output in descending order of salary.
If you want an extra challenge, complete the following exercises:
10. Create a query to display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings.
11. Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job, for departments 20, 50, 80, and 90, giving each column an appropriate heading.
Practice 5
1、 Write a query for the HR department to produce the addresses of all the departments. Use
the LOCATIONS and COUNTRIES tables. Show the location ID, street address, city, state or province, and country in the output. Use a NATURAL JOIN to produce the results.
2、The HR department needs a report of all employees. Write a query to display the last name, department number, and department name for all employees.
3、The HR department needs a report of employees in Toronto. Display the last name, job, department number, and department name for all employees who work in Toronto.
4、Create a report to display employees’ last name and employee number along with their manager’s last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively. Place your SQL statement in a text file named lab_05_04.sql.
5、Modify lab_05_04.sql to display all employees including King, who has no manager. Order the results by the employee number. Place your SQL statement in a text file named lab_05_05.sql. Run the query in lab_05_05.sql.
6、numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label. Save the script to a file named lab_05_06.sql.
7、The HR department needs a report on job grades and salaries. To familiarize yourself with the JOB_GRADES table, first show the structure of the JOB_GRADES table. Then create a query that displays the name, job, department name, salary, and grade for all employees.
If you want an extra challenge, complete the following exercises:
8. The HR department wants to determine the names of all employees who were hired after Davies. Create a query to display the name and hire date of any employee hired after employee Davies.
9、The HR department needs to find the names and hire dates for all employees who were hired before their managers, along with their managers’ names and hire dates. Save the script to a file named lab5_09.sql.
Practice 6
1. The HR department needs a query that prompts the user for an employee last name. The query then displays the last name and hire date of any employee in the same department as the employee whose name they supply (excluding that employee). For example, if the user enters Zlotkey, find all employees who work with Zlotkey (excluding Zlotkey).
2. Create a report that displays the employee number, last name, and salary of all employees who earn more than the average salary. Sort the results in order of ascending salary.
3. Write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains a u. Place your SQL statement in a text file named lab_06_03.sql. Run your query.
4. The HR department needs a report that displays the last name, department number, and job ID of all employees whose department location ID is 1700.
Modify the query so that the user is prompted for a location ID. Save this to a file named lab_06_04.sql.
5. Create a report for HR that displays the last name and salary of every employee who reports to King.
6、 Create a report for HR that displays the department number, last name, and job ID for every employee in the Executive department.
If you have time, complete the following exercise:
7. Modify the query in lab_06_03.sql to display the employee number, last name, and salary of all employees who earn more than the average salary and who work in a department with any employee whose last name contains a u. Resave lab_06_03.sql as lab_06_07.sql. Run the statement in lab_06_07.sql.
Practice 7
1. The HR department needs a list of department IDs for departments that do not contain
the job ID ST_CLERK. Use set operators to create this report.
2. The HR department needs a list of countries that have no departments located in them. Display the country ID and the name of the countries. Use set operators to create this report.
3.
Produce a list of jobs for departments 10, 50, and 20, in that order. Display
job ID and department ID using set operators.
4. Create a report that lists the employee IDs and job IDs of those employees who currently have a job title that is the same as their job title when they were initially hired by the company (that is, they changed jobs but have now gone back to doing their original job).
5. The HR department needs a report with the following specifications:
- Last name and department ID of all the employees from the EMPLOYEES table, regardless of whether or not they belong to a department
- Department ID and department name of all the departments from the DEPARTMENTS table, regardless of whether or not they have employees working in them Write a compound query to accomplish this.
Practice 8
The HR department wants you to create SQL statements to insert, update, and delete employee data. As a prototype, you use the MY_EMPLOYEE table, before giving the statements to the HR department.
Insert data into the MY_EMPLOYEE table.
1. Run the statement in the lab_08_01.sql script to build the MY_EMPLOYEE table to be used for the lab.
2. Describe the structure of the MY_EMPLOYEE table to identify the column names.
2.
Create an INSERT statement to add the first row of data to the MY_EMPLOYEE table from the following sample data. Do not list the columns in the INSERT clause. Do not enter all rows yet.
ID LAST_NAME FIRST_NAME USERID SALARY
1 Patel Ralph rpatel 895
Betty bdancs 860 2 Dancs 3 Biri Ben bbiri 1100 4 Newman Chad cnewman 750
5 Ropeburn Audrey aropebur 1550
4. Populate the MY_EMPLOYEE table with the second row of sample data from the preceding list. This time, list the columns explicitly in the INSERT clause.
5. Confirm your addition to the table.
6. Write an insert statement in a dynamic reusable script file named loademp.sql to load rows into the MY_EMPLOYEE table. Concatenate the first letter of the first name and the first seven characters of the last name to produce the user ID. Save this script to a file named lab_08_06.sql.
7. Populate the table with the next two rows of sample data listed in step 3 by running the insert statement in the script that you created. 8. Confirm your additions to the table.
9. Make the data additions permanent.
Update and delete data in the MY_EMPLOYEE table. 10. Change the last name of employee 3 to Drexler.
11. Change the salary to $1,000 for all employees who have a salary less than $900.
12. Verify your changes to the table.
13. Delete Betty Dancs from the MY_EMPLOYEE table. 14. Confirm your changes to the table.
Practice 9
1. Create the DEPT table based on the following table instance chart. Place the
syntax in a script called lab_09_01.sql, and then execute the statement in the script to create the table. Confirm that the table is created.
Column Name Key Type Nulls/Unique FK Table FK Column Data type Length ID Primary key NUMBER 7 NAME VARCHAR2 25
2. Populate the DEPT table with data from the DEPARTMENTS table. Include only columns that you need.
3. Create the EMP table based on the following table instance chart. Place the syntax in
a script called lab_09_03.sql, and then execute the statement in the script to create the table. Confirm that the table is created. ID LAST_NAME FIRST_NAME DEPT_ID Column Name Key Type Nulls/Unique DEPT FK Table FK Column Data type Length NUMBER 7 VARCHAR2 25 VARCHAR2 25 ID NUMBER 7
4. Create the EMPLOYEES2 table based on the structure of the EMPLOYEES table. Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns. Name the columns in your new table ID, FIRST_NAME, LAST_NAME, SALARY, and DEPT_ID, respectively.
5. Drop the EMP table. Practice 10 Part 1
1. The staff in the HR department want to hide some of the data in the EMPLOYEES table. They want a view called EMPLOYEES_VU based on the employee numbers, employee names, and department numbers from the EMPLOYEES table. They want the heading for the employee name to be EMPLOYEE.
2. Confirm that the view works. Display the contents of the EMPLOYEES_VU view.
all employee names and department numbers.
4.
Department 50 needs access to its employee data. Create a view named
DEPT50 that contains the employee numbers, employee last names, and department numbers for all employees in department 50. You have been asked to label the view columns EMPNO, EMPLOYEE, and DEPTNO. For security purposes, do not allow an employee to be reassigned to another department through the view.
5. Display the structure and contents of the DEPT50 view.
6. Test your view. Attempt to reassign Matos to department 80. Practice 11
1. For a specified table, create a script that reports the column names, data types, and data types’ lengths, as well as whether nulls are allowed. Prompt the user to enter the table name. Give appropriate aliases to the columns DATA_PRECISION and DATA_SCALE. Save this script in a file named lab_11_01.sql.
For example, if the user enters DEPARTMENTS, the following output results:
2. Create a script that reports the column name, constraint name, constraint type, search condition, and status for a specified table. You must join the USER_CONSTRAINTS and USER_CONS_COLUMNS tables to obtain all of this information. Prompt the user to enter the table name. Save the script in a file named lab_11_02.sql.
For example, if the user enters DEPARTMENTS, the following output results:
3. Add a comment to the DEPARTMENTS table. Then query the USER_TAB_COMMENTS view to verify that the comment is present.
4.
Find the names of all synonyms that are in your schema.
5. You need to determine the names and definitions of all of the views in your schema. Create a report that retrieves view information: the view name and text from the USER_VIEWS data dictionary view.
Note: Another view already exists. The EMP_DETAILS_VIEW was created as part of your schema. Also, if you completed practice 10, you will see the DEPT50 view.
Note: To see more contents of a LONG column, use the iSQL*Plus command SET LONG n, where n is the value of the number of characters of the LONG column that you want to see.
6、Find the names of your sequences. Write a query in a script to display the following information about your sequences: sequence name, maximum value, increment size, and last number. Name the script lab_11_06.sql. Run the statement in your script.
因篇幅问题不能全部显示，请点此查看更多更全内容