oracle实验内容1(共40题)

实验内容1,建立本地连接2,用toad第三方工具登录到服务器,用户名:hr,密码hr3,在toad工具里做下列题目,方法:shift+f9,执行当前光标的sql语句Part 1Test your knowledge:1. Initiate an i SQL*Plus session using the user ID and password that are provided by the instructor.2. i SQL*Plus commands access the database.True/False T3. The following SELECT statement executes successfully:SELECT last_name, job_id, salary AS SalFROM employees;True/False T4. The following SELECT statement executes successfully:SELECT *FROM job_grades;//不存在该表True/False F5. There are four coding errors in the following statement. Can you identify them?SELECT employee_id, last_name //少符号“,”sal x 12 ANNUAL SALARY //不能用salary的缩写“sal”；“X”应用“* ”；使用别名的时候中间不能再有空格。

FROM employees;6.The HR department needs a query to display all unique job codes from the EMPLOYEES table.select distinct job_idfrom EMPLOYEES;7、Because of budget issues, the HR department needs a report that displays the last name and salary of employees who earn more than $12,000.select last_name, salaryfrom EMPLOYEESwhere salary>120008.Create a report that displays the last name and department number for employee number 176.select last_name, department_idfrom employeeswhere employee_id=1769.The HR department needs to find high-salary and low-salary employees. Modify lab_02_01.sql to display the last name and salary for any employee whose salary is not in the range of $5,000 to $12,000.select last_name, salaryfrom employeeswhere salary>12000or salary<500010.Create a report to display the last name, job ID, and start date for the employees with thelast names of Matos and Taylor. Order the query in ascending order by start date.select last_name,job_id,hire_datefrom employeeswhere last_name='Matos'or last_name ='Taylor'order by hire_date11.Display the last name and department number of all employees in departments 20 or 50 in ascending alphabetical order by name.select last_name,department_idfrom employeeswhere department_id in('20','50')order by last_name12.The HR department needs a report that displays the last name and hire date for all employees who were hired in 1994.select last_name,hire_datefrom employeeswhere hire_date like'%94'13.Create a report to display the last name and job title of all employees who do not have a manager.select last_name,job_idfrom employeeswhere manager_id is null14.Create a report to display the last name, salary, and commission of all employees who earn commissions. Sort data in descending order of salary and commissions.select last_name,salary,commission_pctfrom employeeswhere commission_pct>0order by salary desc,commission_pct desc15.Members of the HR department want to have more flexibility with the queries that you are writing. They would like a report that displays the last name and salary of employees who earn more than an amount that the user specifies after a prompt. (You can use the query that you created in practice exercise 1 and modify it.)select last_name,salaryfrom employeeswhere salary>=&sal16.. The HR department wants to run reports based on a manager. Create a query that prompts the user for a manager ID and generates the employee ID, last name, salary, and department for that manager’s employees. The HR department wants the ability to sort the report on a selected column. You can test the data with the following values:manager ID = 103, sorted by employee last name:select employee_id,last_name,salary,department_idfrom employeeswhere manager_id =&m_idorder by last_namemanager ID = 201, sorted by salary:manager ID = 124, sorted by employee ID:17.Display all employee last names in which the third letter of the name is a.select last_namefrom employeeswhere last_name like'__a%'18.Display the last names of all employees who have both an a and an e in their last name. select last_namefrom employeeswhere last_name like'%a%'and last_name like'%e%'19.Display the last name, job, and salary for all employees whose jobs are either salesrepresentative or stock clerk and whose salaries are not equal to $2,500, $3,500, or $7,000.select last_name,job_id,salaryfrom employeeswhere(job_id ='SA_REP'or job_id='ST_CLERK')and salary not in (2500,3500,7000)。