Oracle Database notes: Basic SELECT statements

The purpose of this experiment is to help students master the basic usage of simple query statements and the alias designation methods, and create and execute SQL scripts. Lab requirements (1) S

The purpose of this experiment is to help students master the basic usage of simple query statements and the alias designation methods, and create and execute SQL scripts. Lab requirements (1) S

Lab Purpose

This experiment allows the students to master the basic usage of simple query statements and the specified alias methods, and create and execute SQL scripts.

Lab requirements

(1) master the basic use of SQL * Plus

(2) understand the simple usage of SELECT statements.

Lab procedure

(1) log on to SQL * Plus with the initial username SCOTT and password TIGER.

You are not authorized to upload files ..

(2) display the structure of the EMP, DETP, and SALGRADE tables, and view the data in the tables.

DESC emp;

(3) create a query to display the name, work, employment date, and employee number of each employee, and use the employee number as the first column. Finally, save the SQL statement as the p1q1. SQL file and execute the script file.

SELECT empno, ename, job, hiredate FROM emp;

(4) run the script file p1q1. SQL requests to the SQL Buffer, sets the column title to Emp #, Employee, Job, and Hire Date, and saves the SQL statement as the p1q2 file. SQL, and finally return the query results.

Get e: \ Experiment 1 \ plql. SQL

For example.

SELECT empno "Emp #", ename "Employee", job "Job", hiredate "Hire Date"

FROM emp;

You are not authorized to upload files ..

Exercise questions

1. Is the SQL * Plus command used to access the database?

A: SQL * Plus is a development tool for Oracle databases.

2. Can the following SELECT statement be successfully executed?

SQL> SELECT ename, job, sal Salary

2 FROM emp;

A: Yes.

3. The following statements have three errors. correct them.

SQL> SELECT empno, ename

2 Salary x 12 ANNUAL SALARY

3 FROM emp;

A: (1) Add fewer commas after the ename; (2) Change the first Salary to sal;

(3) annual salary is enclosed in double quotation marks.

4. Create a query to retrieve non-repeated work names from the EMP table.

A: select distinct job FROM emp;

5. Use a comma and a space to connect the name and work as a whole output. The displayed column Title is "Employee and Title.

SELECT ename | ',' | job "Employee and Title"

FROM emp;

6. Create a query and use a comma to connect the information of all fields in the table as a whole output. The output column title is THE_OUTPUT.

SQL> SELECT empno | ',' | ename | ',' | job | ',' | sal | ',' | comm | ', '| deptno

2 FROM emp;

,