أخوانى إليكم درس شامل فى جمل الإستعلام أرجو أن ينال إعجابكم
1. Write a query to display the last name and hire date of any employee in the same department as Zlotkey. Exclude Zlotkey.005select last_name hire_date,department_id006FROM employees 007WHERE department_id =(select department_id008 FROM employees009 WHERE last_name like 'Zlotkey')010AND last_name not like 'Zlotkey';011 012 0132. create a query to display the employee numbers and last names of all employees who earn more than the average salary. Sort the results in ascending Order of salary.014select last_name ,employee_id,salary015FROM employees016WHERE salary > (select avg(salary)017 FROM employees)018 0193. Write a query that displays the employee numbers and last names of all employees who work in a department with any employee whose last name contains a u. 020select employee_id,last_name021FROM employees022WHERE department_id in(select department_id 023 FROM employees024 WHERE last_name like'%u%');025 026 0274. Display the last name, department number, and job ID of all employees whose department location ID is 1700028select last_name,department_id,job_id029FROM employees030WHERE department_id in(select department_id 031 FROM departments032 WHERE location_id =1700);033 0345. Display the last name and salary of every employee who reports to King.035 036 037 0386. Display the department number, last name, and job ID for every employee in the Executive department.039select department_ id , last _ name ,job_id040FROM employees041WHERE job_id like 'AD_%';042 0437. Modify the query in lab6_3.sql to display the employee numbers, last names, and salaries of all employees who earn more than the average salary and who work in a department with any employee with a u in their name.044select employee _ id, last_ name , salary , department _id045FROM employees046WHERE salary >(select avg(salary)047 FROM employees)048AND department _ id in (select department _ id049 FROM employees050 WHERE last_name like '%u%');051 052 053 054 055Chapter 8056 057Practice 80581. Run the statement in the lab8_1.sql script to build the MY_EMPLOYEE table to be used for the lab.059create table my_employees 060 (id number(4),last_name varchar2(25),061 first_name varchar2(25),userid varchar2(8),062 salary number(9,2));063 064 0652. Describe the structure of the MY_EMPLOYEE table to identify the column names.066DESC my_employees067 0683. 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.069insert INTO my_employees 070VALUES (1,'Patel','Ralph', 'rpatel' ,895);071 0724. Populate the MY_EMPLOYEE table with the second row of sample data from the preceding list.073This time, list the columns explicitly in the insert clause.074insert INTO my_employees(id,last_name ,first_name,userid,salary) 075VALUES (2,' Dancs', 'Betty',' bdancs', 860);076 0775. Confirm your addition to the table.078select *079FROM my_employees;080 0816. Write an insert statement in a text 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.082insert INTO my_employees083VALUES (&id,' &frist_mame',084 '&last_name','lower(substr(&frist_name,1,1))|| 085 'lower( substr(&last_name,1,7)) ', &salary);086 087 088 0897. Populate the table with the next two rows of sample data by running the insert statement in the090script that you created.091insert INTO my_employees092VALUES (&id,' &frist_mame',093 '&last_name','lower(substr(&frist_name,1,1))|| 094 'lower( substr(&last_name,1,7)) ', &salary);095 096 0978. Confirm your additions to the table.098select * 099FROM my_employees;100 1019. Make the data additions permanent.102COMMIT;103 10410. Change the last name of employee 3 to Drexler.105update my_employees 106SET last_name = 'Drexler'107WHERE id=3;108 10911. Change the salary to 1000 for all employees with a salary less than 900.110update my_employees 111SET salary= 1000112WHERE salary <900;113 11412. Verify your changes to the table.115select * 116FROM my_employees;117 11813. delete Betty Dancs from the MY_EMPLOYEE table.119delete FROM my_employees 120WHERE first_name like 'Betty';121 12214. Confirm your changes to the table123select * 124FROM my_employees;125 126 127 12815. Commit all pending changes.129COMMIT;130 13116. Populate the table with the last row of sample data by modifying the statements in the script that you created in step 6.132 Run the statements in the script.133insert INTO my_employees134VALUES (&id,' &frist_mame',135 '&last_name','lower(substr(&frist_name,1,1))|| 136 'lower( substr(&last_name,1,7)) ', &salary);137 13817. Confirm your addition to the table.139select * 140FROM my_employees;141 14218. Mark an intermediate point in the processing of the transaction.143SAVEPOINT b;144 14519. Empty the entire table.146delete FROM my_employees147 14820. Confirm that the table is empty.149select * 150FROM my_employees;151 15221. Discard the most recent delete operation without discarding the earlier insert operation.153ROLLBACK TO b;154 15522. Confirm that the new row is still intact.156select * 157FROM my_employees;158 15923. Make the data addition permanent.160COMMIT;161 162 163 164 165Chapter 9166 167Practice 91681. create the DEPT table based on the following table instance chart. 169Confirm that the table is created.170create TABLE dept171 (ID number(7),NAME varchar2(25));172DESC dept;173 1742. Populate the DEPT table with data from the DEPARTMENTS table. Include only columns that you need.175create TABLE dept 176AS select department_id,department_name177 FROM departments;178 1793. create the EMP table based on the following table instance chart.180Confirm that the table is created181create TABLE emp (id number(7),last_name varchar2(25),182 first_name varchar2(25),dept_id number(7)); 183DESC emp;184 1854. Modify the EMP table to allow for longer employee last names. Confirm your modification.186ALTER TABLE emp 187MODIFY (last_name varchar2(50)); 188DESC emp;189 1905. Confirm that both the DEPT and EMP tables are stored in the data dictionary. (Hint: USER_TABLES)191select table_name192FROM user_tables;193 194 195 196 197 198 199 2006. 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.201 Name the columns in your new table ID, FIRST_NAME, LAST_NAME, SALARY , and DEPT_ID, respectively.202 203 204create TABLE employees2 205AS select employee_id "ID",first_name "FIRST_NAME" ,206 last_name "LAST_NAME", 207 salary "SALARY" ,department_id "DEPT_ID" 208FROM employees;209DESC employees2210 2117. drop the EMP table.212drop TABLE emp;213 2148. Rename the EMPLOYEES2 table as EMP.215RENAME employees TO emp;216 2179. Add a comment to the DEPT and EMP table definitions describing the tables. Confirm your additions in the data dictionary.218COMMENT ON TABLE emp 219IS 'employees information';220COMMENT ON TABLE dept 221IS 'department information';222select * 223FROM user_tab_comments;224 22510. drop the FIRST_NAME column from the EMP table. Confirm your modification by checking the description of the table.226ALTER TABLE emp227drop COLUMN first_name;228DESC emp229 230 231 23211. In the EMP table, mark the DEPT_ID column in the EMP table as UNUSED. Confirm your modification by checking the description of the table.233ALTER TABLE emp234SET UNUSED (dept_id);235DESC emp;236 23712. drop all the UNUSED columns from the EMP table. Confirm your modification by checking the description of the table.238ALTER TABLE emp239drop UNUSED columns;240DESC emp
1. Write a query to display the last name and hire date of any employee in the same department as Zlotkey. Exclude Zlotkey.005select last_name hire_date,department_id006FROM employees 007WHERE department_id =(select department_id008 FROM employees009 WHERE last_name like 'Zlotkey')010AND last_name not like 'Zlotkey';011 012 0132. create a query to display the employee numbers and last names of all employees who earn more than the average salary. Sort the results in ascending Order of salary.014select last_name ,employee_id,salary015FROM employees016WHERE salary > (select avg(salary)017 FROM employees)018 0193. Write a query that displays the employee numbers and last names of all employees who work in a department with any employee whose last name contains a u. 020select employee_id,last_name021FROM employees022WHERE department_id in(select department_id 023 FROM employees024 WHERE last_name like'%u%');025 026 0274. Display the last name, department number, and job ID of all employees whose department location ID is 1700028select last_name,department_id,job_id029FROM employees030WHERE department_id in(select department_id 031 FROM departments032 WHERE location_id =1700);033 0345. Display the last name and salary of every employee who reports to King.035 036 037 0386. Display the department number, last name, and job ID for every employee in the Executive department.039select department_ id , last _ name ,job_id040FROM employees041WHERE job_id like 'AD_%';042 0437. Modify the query in lab6_3.sql to display the employee numbers, last names, and salaries of all employees who earn more than the average salary and who work in a department with any employee with a u in their name.044select employee _ id, last_ name , salary , department _id045FROM employees046WHERE salary >(select avg(salary)047 FROM employees)048AND department _ id in (select department _ id049 FROM employees050 WHERE last_name like '%u%');051 052 053 054 055Chapter 8056 057Practice 80581. Run the statement in the lab8_1.sql script to build the MY_EMPLOYEE table to be used for the lab.059create table my_employees 060 (id number(4),last_name varchar2(25),061 first_name varchar2(25),userid varchar2(8),062 salary number(9,2));063 064 0652. Describe the structure of the MY_EMPLOYEE table to identify the column names.066DESC my_employees067 0683. 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.069insert INTO my_employees 070VALUES (1,'Patel','Ralph', 'rpatel' ,895);071 0724. Populate the MY_EMPLOYEE table with the second row of sample data from the preceding list.073This time, list the columns explicitly in the insert clause.074insert INTO my_employees(id,last_name ,first_name,userid,salary) 075VALUES (2,' Dancs', 'Betty',' bdancs', 860);076 0775. Confirm your addition to the table.078select *079FROM my_employees;080 0816. Write an insert statement in a text 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.082insert INTO my_employees083VALUES (&id,' &frist_mame',084 '&last_name','lower(substr(&frist_name,1,1))|| 085 'lower( substr(&last_name,1,7)) ', &salary);086 087 088 0897. Populate the table with the next two rows of sample data by running the insert statement in the090script that you created.091insert INTO my_employees092VALUES (&id,' &frist_mame',093 '&last_name','lower(substr(&frist_name,1,1))|| 094 'lower( substr(&last_name,1,7)) ', &salary);095 096 0978. Confirm your additions to the table.098select * 099FROM my_employees;100 1019. Make the data additions permanent.102COMMIT;103 10410. Change the last name of employee 3 to Drexler.105update my_employees 106SET last_name = 'Drexler'107WHERE id=3;108 10911. Change the salary to 1000 for all employees with a salary less than 900.110update my_employees 111SET salary= 1000112WHERE salary <900;113 11412. Verify your changes to the table.115select * 116FROM my_employees;117 11813. delete Betty Dancs from the MY_EMPLOYEE table.119delete FROM my_employees 120WHERE first_name like 'Betty';121 12214. Confirm your changes to the table123select * 124FROM my_employees;125 126 127 12815. Commit all pending changes.129COMMIT;130 13116. Populate the table with the last row of sample data by modifying the statements in the script that you created in step 6.132 Run the statements in the script.133insert INTO my_employees134VALUES (&id,' &frist_mame',135 '&last_name','lower(substr(&frist_name,1,1))|| 136 'lower( substr(&last_name,1,7)) ', &salary);137 13817. Confirm your addition to the table.139select * 140FROM my_employees;141 14218. Mark an intermediate point in the processing of the transaction.143SAVEPOINT b;144 14519. Empty the entire table.146delete FROM my_employees147 14820. Confirm that the table is empty.149select * 150FROM my_employees;151 15221. Discard the most recent delete operation without discarding the earlier insert operation.153ROLLBACK TO b;154 15522. Confirm that the new row is still intact.156select * 157FROM my_employees;158 15923. Make the data addition permanent.160COMMIT;161 162 163 164 165Chapter 9166 167Practice 91681. create the DEPT table based on the following table instance chart. 169Confirm that the table is created.170create TABLE dept171 (ID number(7),NAME varchar2(25));172DESC dept;173 1742. Populate the DEPT table with data from the DEPARTMENTS table. Include only columns that you need.175create TABLE dept 176AS select department_id,department_name177 FROM departments;178 1793. create the EMP table based on the following table instance chart.180Confirm that the table is created181create TABLE emp (id number(7),last_name varchar2(25),182 first_name varchar2(25),dept_id number(7)); 183DESC emp;184 1854. Modify the EMP table to allow for longer employee last names. Confirm your modification.186ALTER TABLE emp 187MODIFY (last_name varchar2(50)); 188DESC emp;189 1905. Confirm that both the DEPT and EMP tables are stored in the data dictionary. (Hint: USER_TABLES)191select table_name192FROM user_tables;193 194 195 196 197 198 199 2006. 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.201 Name the columns in your new table ID, FIRST_NAME, LAST_NAME, SALARY , and DEPT_ID, respectively.202 203 204create TABLE employees2 205AS select employee_id "ID",first_name "FIRST_NAME" ,206 last_name "LAST_NAME", 207 salary "SALARY" ,department_id "DEPT_ID" 208FROM employees;209DESC employees2210 2117. drop the EMP table.212drop TABLE emp;213 2148. Rename the EMPLOYEES2 table as EMP.215RENAME employees TO emp;216 2179. Add a comment to the DEPT and EMP table definitions describing the tables. Confirm your additions in the data dictionary.218COMMENT ON TABLE emp 219IS 'employees information';220COMMENT ON TABLE dept 221IS 'department information';222select * 223FROM user_tab_comments;224 22510. drop the FIRST_NAME column from the EMP table. Confirm your modification by checking the description of the table.226ALTER TABLE emp227drop COLUMN first_name;228DESC emp229 230 231 23211. In the EMP table, mark the DEPT_ID column in the EMP table as UNUSED. Confirm your modification by checking the description of the table.233ALTER TABLE emp234SET UNUSED (dept_id);235DESC emp;236 23712. drop all the UNUSED columns from the EMP table. Confirm your modification by checking the description of the table.238ALTER TABLE emp239drop UNUSED columns;240DESC emp
أسف على خطأ النقل
إليكم المرفق
