Oracle Database--pl/sql BASIC Programming

I. Related content

1. Master the structure of PL/SQL program block, understand and familiar with the application of various variables.

Second, the specific operation

(i) using the system user login Sql*plus, create a user using the SQL statement: U_ your initials (for example: U_zs), the password is: T_ your number three (for example: t_165). and grant the new user permission to connect to the database, and then connect to the database as a new user.

1. Create User U_SXL

2. Grant new users U_SXL access to the database

3. Connect the database with the new user U_SXL

(ii) using the Scott user to connect to the database, using SQL statements to create the table Dep_ your number three digits (for example: dep_165)

NOTE: Create a constraint while creating a table

Description of Column name data type column

DNO integer system number (primary KEY constraint)

Dname char (12) system name (UNIQUE constraint)

1. Connect to the database using the Scott User (change the password first)

2. Create a table dep_120

(iii) Using SQL statements to create a table stu_ three digits after your study number (ex: stu_165)

NOTE: Create a constraint while creating a table

Description of Column name data type column

Sno Integer number (primary KEY constraint)

Sname Varchar2 (20) name (non-null constraint)

Sage integer Age (check constraint: between 12~99岁)

SDEP integer system number (FOREIGN KEY constraint)

The table that you created looks like this:

(iv) Use the EMP table and the Dept table under the Scott scheme to complete the following queries. (Note: Only one SQL statement can be used for each problem)

1. Check the names and department names of all employees in department 20th (requirements: Using natural JOIN)

2. Check the department location (LOC) and employee name. If there is no employee in a department, the department location should also be displayed. (Required: Use left outer connection)

3. Check the total value of employee bonus (COMM) in department 30th.

4. Query the name of each employee and the name of his immediate supervisor (MGR) (Request: Connect using self)

5. The enquiry monthly salary is below 2100 yuan, and the department number is 30 employee number and monthly salary. (Requirements: Using intersect)

6. Query the department number of the two departments with the highest average wage. (Requirement: Use rownum pseudo-column)

7. Query the names of all employees who have the same department and year of Employment as Allen. (Requirements: Using multi-column subqueries)

8. Find out the number of employees in each position by job Group

9. Check the lowest value of the salary for each position by working in the job (job) group.

10. Increase the salary of employees in the "SALES" department by 15% (note: Two sheets are required)

11. The average monthly salary is higher than 2800 yuan, the name of the department, employee name, department average monthly salary.

12. Remove the information from the EMP table of the employee who is located in the "BOSTON" section of your department. (Note: two sheets required)

(v) the preparation of PL/SQL blocks, each subject using a block implementation.

1. Create and execute a PL/SQL program block, enter two values x and y from the keyboard by substituting variables, multiply the first number x by the second number Y and divide by 10 rounding (decimal rounding), and put the result into a variable: v_ your initials (for example: V_zs), and then output to the screen.

2. Create and execute a PL/SQL program block, query the maximum salary in the EMP table of the Scott scheme, and put it into a variable: v_ your initials (for example: V_zs) and then display it on the screen.

3. Create and execute a PL/SQL program block to calculate the annual salary of an employee of the EMP table in the Scott scheme, the employee's number is entered by the keyboard via an alternative variable, the annual salary equals the monthly salary sal multiplied by 12 months plus the bonus comm, and the null value is processed using the NVL function. Put the calculated annual salary into the variable: v_ your initials (for example: V_zs), and then output the results to the screen.

4. Create and execute a PL/SQL program block, add a row of data to the Dept table, enter the department's number and name with an alternate variable, and the department position is null.

5. Create and execute a PL/SQL program block, update the value of the department position, enter the department's number with the substitution variable, and the new department position.

6. Create and execute a PL/SQL program block, delete the department added by the 4th question, and enter the department's number with the substitution variable.

Oracle Database--pl/sql BASIC Programming