Oracle قسم قواعد البيانات أوراكل

 اسم العضو:وليد القدسي  الأحد,16/فر/1431 هـ,09:39 مساءً
السلام عليكم ورحمه الله وبركاته حقدم لكم مجموعه من الاستعلامات الذي كنا نتمرن عليها ايام الدراسه واتمني تعجبكم [CODE language="sql"] Chapter 1 Practice 1 1. Initiate an iSQL*Plus session using the user ID and password provided by the instructor. User_id : scott Password: tiger 2. iSQL*Plus commands access the database. True/False True 3. The following select statement executes successfully: select last_name, job_id, salary AS Sal FROM employees; True/False True 4. The following select statement executes successfully: select * FROM job_grades; True/False False 5. There are four coding errors in this statement. Can you identify them? select employee_id, last_name sal x 12 ANNUAL SALARY FROM employees; False 6. Show the structure of the DEPARTMENTS table. select all data from the table. 1.desc departments; 2. select * FROM departments; 7. Show the structure of the EMPLOYEES table. create a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first. Provide an alias STARTDATE for the HIRE_DATE column. desc employees; 8. Run your query in the file lab1_7.sql. select employee_id, last_name, job_id, hire_date "Start Date" FROM employees; 9. create a query to display unique job codes from the EMPLOYEES table. select job_id FROM employees; 10. Copy the statement from lab1_7.sql into the iSQL*Plus Edit window. Name the column headings Emp #, Employee, Job, and Hire Date, respectively. select employee_id "emp#", last_name "Employee" ,job_id "Job", hire_date "hire date" FROM employees; 11. Display the last name concatenated with the job ID, separated by a comma and space, and name the column Employee and Title. select last_name || ',' || job_id "employee and title" FROM employees; 12. create a query to display all the data from the EMPLOYEES table. Separate each column by a comma. Name the column THE_OUTPUT select EMPLOYEE_ID||',' ||FIRST_NAME||','||LAST_NAME||','||EMAIL||',' ||PHONE_NUMBER||','||HIRE_DATE||','||JOB_ID||',' ||SALARY||','||COMMISSION_PCT|| ',' ||MANAGER_ID||','||DEPARTMENT_ID "THE _OUTPUT" FROM employees; Chapter 2 Practice 2 1. create a query to display the last name and salary of employees earning more than $12,000. select last_name ,salary FROM employees WHERE salary >12000; 2. create a query to display the employee last name and department number for employee number 176 select last_name,department_id FROM employees WHERE employee_id=176; 3. Modify lab2_1.sql to display the last name and salary for all employees whose salary is not in the range of $5,000 and $12,000. select last_name,salary FROM employees WHERE salary not between 12000 and 55000; 4. Display the employee last name, job ID, and start date of employees hired between February 20, 1998, AND May 1, 1998. Order the query in ascending order by start date. select last_name,job_id ,hire_date FROM employees WHERE hire_date between '20-feb-98' AND '01-may-98'; 5. Display the last name and department number of all employees in departments 20 AND 50 in alphabetical order by name. select last_name ,department_id FROM employees WHERE department_id in(20,50) ORDER BY last_name; 6. Modify lab2_3.sql to list the last name and salary of employees who earn between $5,000 and $12,000, and are in department 20 or 50. Label the columns Employee and Monthly Salary, respectively. select last_name "employee",salary "monthly salary" FROM employees WHERE salary between 12000 and 5000 AND department_id in(20,50); 7. Display the last name and hire date of every employee who was hired in 1994. select last_name , hire_date FROM employees WHERE hire_date like'%94'; 8. Display the last name and job title of all employees who do not have a manager. select last_name ,job_id FROM employees WHERE manager_id is null; 9. Display the last name, salary, and commission for all employees who earn commissions. Sort data in descending Order of salary and commissions select last_name , salary, commission_pct FROM employees WHERE commission_pct is not null ORDER BY salary DESC; 10. Display the last names of all employees where the third letter of the name is an a. select last_name FROM employees WHERE last_name like'__a%'; 11. Display the last name of all employees who have an a and an e in their last name. select last_name FROM employees WHERE last_name like'%a%' OR last_name like '%e%'; 12. Display the last name, job, and salary for all employees whose job is sales representative or stock clerk and whose salary is not equal to $2,500, $3,500, or $7,000. select last_name,job_id,salary FROM employees WHERE job_id like'ST_CLERK' OR job_id like 'SA_REP' AND salary not in(2005,3500,7000); 13. Modify lab2_6.sql to display the last name, salary, and commission for all employees whose commission amount is 20%. select last_name "Employee",salary "Monthly Salary",commission_pct FROM employees WHERE commission_pct =.2 Chapter 3 Practice 3 1.Write a query to display the current date. Label the column Date. select sysdate “data” FROM employees; 2. FOR each employee, display the employee number, last_name, salary, AND salary increased by 15% and expressed as a whole number. Label the column New Salary. select EMPLOYEE_ID , LAST_NAME , salary,salary*1.15 "NEW _SALARY" FROM employees; 3. Run your query in the file lab3_2.sql. 4. Modify your query lab3_2.sql to add a column that subtracts the old salary from the new salary. Label the column Increase. select EMPLOYEE_ID,LAST_NAME, salary, salary*1.15 "NEW _ SALARY",salary*1.15- salary "Increase" FROM employees; 5. Write a query that displays the employee’s last names with the first letter capitalized and all other letters lowercase, and the length of the names, for all employees whose name starts with J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last_names. select initcap (last_name) "name",length(last_name) "length" FROM employees WHERE last_name like 'A%' OR last_name like 'J%' OR last_name like 'M%' ORDER BY last_name; 6. for each employee, display the employee’s last name, and calculate the number of months between today and the date the employee was hired. Label the column MONTHS_WORKED. order your results by the number of months employed. Round the number of months up to the closest whole number. select last_name , round (months_between(sysdate, hire_date),0)"MONTHS_WORKE" FROM employees ORDER BY round (months_between( sysdate ,hire_date),0); 7. Write a query that produces the following for each employee: <employee last name> earns <salary> monthly but wants <3 times salary>. Label the column Dream Salaries. select last_name ||'earns' || to_char(salary,'$99,999.00') ||'monthly but wents'||to_char(3* salary,'$99,999.00') "dream salaries" FROM employees; 8. create a query to display the last name and salary for all employees. format the salary to be characters long, left-padded with $. Label the column SALARY. select last_name, lpad (salary,15,'$') "salary" FROM employees; 9. Display each employee’s last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.” select last_name ,hire_date, to_char(next_day(add_months(hire_date,6) ,'monday'), 'day "the" fmDdspth"of" month yyyy') "review" FROM employees; 10. Display the last name, hire date, and day of the week on which the employee started. Label the column DAY. order the results by the day of the week starting with Monday. select last_name ,hire_date,to_char(hire_date,'day') "day" FROM employees ORDER BY to_char(hire_date,'day') ; 11. create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, put “No Commission.” Label the column COMM. select last_name ,nvl (to_char(commission_pct),'NO COMMISSION') FROM employees; 12. create a query that displays the employees’ last names and indicates the amounts of their annual salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Label the column EMPLOYEES_AND_THEIR_SALARIES. select rpad(last_name,trunc(salary/1000,0)+length(last_name),'*') EMPLOYEES_AND_THEIR_SALARIES FROM employees;; 13. Using the DECODE function, write a query that displays the grade of all employees based on the value of the column JOB_ID, as per the following data: Job Grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E select job_id , Decode (job_id, 'AD_PRES','A', 'ST_MAN','B', 'IT_PROG','C', 'SA_REP','D', 'ST_CLERK','E', '0') FROM employees; Chapter 4 Practice 4 1. Write a query to display the last name, department number, and department name for all employees. select e.last_name, e.department_id ,d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id; 2. create a unique listing of all jobs that are in department 80. Include the location of the department in the output. select e.job_id , d.location_id FROM employees e, departments d WHERE d.department_id= 80 AND e.department_id = d.department_id; 3. Write a query to display the employee last name, department name, location ID, and city of all employees who earn a commission. select e.last_name,d.department_name,l.location_id,l.city FROM employees e, departments d ,locations l WHERE e.department_id = d.department_id AND d.location_id=l.location_id AND commission_pct is not null ; 4. Display the employee last name and department name for all employees who have an a (lowercase) in their last names. select e.last_name,d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id AND last_name like '%a%' ; 5. Write a query to display the last name, job, department number, and department name for all employees who work in Toronto. select e.last_name ,e.job_id,e.department_id,d.department_name FROM employees e,departments d,locations l WHERE l.city like 'Toronto' AND e.department_id=d.department_id; Chapter 5 Practice 5 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 True 2. Group functions include nulls in calculations. True/False False 3. The WHERE clause restricts rows prior to inclusion in a group calculation. True/False False 4. Display 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. select max(salary)"Maximum",min(salary)"Minimum", sum (salary)"Sum", avg(salary)"Average" FROM employees; 5. Modify the query in lab5_4.sql to display the minimum, maximum, sum, and average salary for each job type. select job_id,max(salary)"Maximum",min(salary)"Minimum", sum (salary)"Sum", avg(salary)"Average" FROM employees GROUP BY job_id; 6. Write a query to display the number of people with the same job. select job_id,count(employee_id) FROM employees GROUP BY job_id;; 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. select count (DISTINCT(manager_id)) " Number of Manager" FROM employees 8. Write a query that displays the difference between the highest and lowest salaries. Label the column DIFFERENCE. select max(salary)-min(salary) difference FROM employees;; 9. 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. select manager_id ,min(salary) FROM employees HAVING min(salary)>=6000 AND manager_id is not null GROUP BY manager_id ORDER BY min(salary) DESC; 10. Write a query to display each department’s name, location, number of employees, and the average salary for all employees in that department. Label the columns Name, Location, Number of People, and Salary, respectively. Round the average salary to two decimal places. select d.department_name "Name",d.location_id "Location", count(e.employee_id) "Nember of People",avg(e.salary) "Salary" FROM departments d,employees e WHERE d.department_id= e.department_id GROUP BY d.department_name,location_id ; 11. create a query that will 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. select count(employee_id) "Total", sum(decode(to_char(hire_date,'yyyy'),1995,1,0))"1995", sum(decode(to_char(hire_date,'yyyy'),1996,1,0))"1996", sum(decode(to_char(hire_date,'yyyy'),1997,1,0))"1997", sum(decode(to_char(hire_date,'yyyy'),1998,1,0))"1998" FROM employees; 12. 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. select job_id "Job", sum(decode (department_id,20,salary,0))"dept 20", sum(decode (department_id,50,salary,0))"dept 50", sum(decode (department_id,80,salary,0))"dept 80", sum(decode (department_id,90,salary,0))"dept 90", sum(salary) FROM employees GROUP BY job_id;[/CODE] 

 اسم العضو:وليد القدسي  الأحد,16/فر/1431 هـ,09:48 مساءً
[CODE language="sql"] Chapter 6 Practice 6 1. Write a query to display the last name and hire date of any employee in the same department as Zlotkey. Exclude Zlotkey. select last_name hire_date,department_id FROM employees WHERE department_id =(select department_id FROM employees WHERE last_name like 'Zlotkey') AND last_name not like 'Zlotkey'; 2. 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. select last_name ,employee_id,salary FROM employees WHERE salary > (select avg(salary) FROM employees) 3. 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. select employee_id,last_name FROM employees WHERE department_id in(select department_id FROM employees WHERE last_name like'%u%'); 4. Display the last name, department number, and job ID of all employees whose department location ID is 1700 select last_name,department_id,job_id FROM employees WHERE department_id in(select department_id FROM departments WHERE location_id =1700); 5. Display the last name and salary of every employee who reports to King. 6. Display the department number, last name, and job ID for every employee in the Executive department. select department_ id , last _ name ,job_id FROM employees WHERE job_id like 'AD_%'; 7. 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. select employee _ id, last_ name , salary , department _id FROM employees WHERE salary >(select avg(salary) FROM employees) AND department _ id in (select department _ id FROM employees WHERE last_name like '%u%'); Chapter 8 Practice 8 1. Run the statement in the lab8_1.sql script to build the MY_EMPLOYEE table to be used for the lab. create table my_employees (id number(4),last_name varchar2(25), first_name varchar2(25),userid varchar2(8), salary number(9,2)); 2. Describe the structure of the MY_EMPLOYEE table to identify the column names. DESC my_employees 3. 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. insert INTO my_employees VALUES (1,'Patel','Ralph', 'rpatel' ,895); 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. insert INTO my_employees(id,last_name ,first_name,userid,salary) VALUES (2,' Dancs', 'Betty',' bdancs', 860); 5. Confirm your addition to the table. select * FROM my_employees; 6. 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. insert INTO my_employees VALUES (&id,' &frist_mame', '&last_name','lower(substr(&frist_name,1,1))|| 'lower( substr(&last_name,1,7)) ', &salary); 7. Populate the table with the next two rows of sample data by running the insert statement in the script that you created. insert INTO my_employees VALUES (&id,' &frist_mame', '&last_name','lower(substr(&frist_name,1,1))|| 'lower( substr(&last_name,1,7)) ', &salary); 8. Confirm your additions to the table. select * FROM my_employees; 9. Make the data additions permanent. COMMIT; 10. Change the last name of employee 3 to Drexler. update my_employees SET last_name = 'Drexler' WHERE id=3; 11. Change the salary to 1000 for all employees with a salary less than 900. update my_employees SET salary= 1000 WHERE salary <900; 12. Verify your changes to the table. select * FROM my_employees; 13. delete Betty Dancs from the MY_EMPLOYEE table. delete FROM my_employees WHERE first_name like 'Betty'; 14. Confirm your changes to the table select * FROM my_employees; 15. Commit all pending changes. COMMIT; 16. Populate the table with the last row of sample data by modifying the statements in the script that you created in step 6. Run the statements in the script. insert INTO my_employees VALUES (&id,' &frist_mame', '&last_name','lower(substr(&frist_name,1,1))|| 'lower( substr(&last_name,1,7)) ', &salary); 17. Confirm your addition to the table. select * FROM my_employees; 18. Mark an intermediate point in the processing of the transaction. SAVEPOINT b; 19. Empty the entire table. delete FROM my_employees 20. Confirm that the table is empty. select * FROM my_employees; 21. Discard the most recent delete operation without discarding the earlier insert operation. ROLLBACK TO b; 22. Confirm that the new row is still intact. select * FROM my_employees; 23. Make the data addition permanent. COMMIT; Chapter 9 Practice 9 1. create the DEPT table based on the following table instance chart. Confirm that the table is created. create TABLE dept (ID number(7),NAME varchar2(25)); DESC dept; 2. Populate the DEPT table with data from the DEPARTMENTS table. Include only columns that you need. create TABLE dept AS select department_id,department_name FROM departments; 3. create the EMP table based on the following table instance chart. Confirm that the table is created create TABLE emp (id number(7),last_name varchar2(25), first_name varchar2(25),dept_id number(7)); DESC emp; 4. Modify the EMP table to allow for longer employee last names. Confirm your modification. ALTER TABLE emp MODIFY (last_name varchar2(50)); DESC emp; 5. Confirm that both the DEPT and EMP tables are stored in the data dictionary. (Hint: USER_TABLES) select table_name FROM user_tables; 6. 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. create TABLE employees2 AS select employee_id "ID",first_name "FIRST_NAME" , last_name "LAST_NAME", salary "SALARY" ,department_id "DEPT_ID" FROM employees; DESC employees2 7. drop the EMP table. drop TABLE emp; 8. Rename the EMPLOYEES2 table as EMP. RENAME employees TO emp; 9. Add a comment to the DEPT and EMP table definitions describing the tables. Confirm your additions in the data dictionary. COMMENT ON TABLE emp IS 'employees information'; COMMENT ON TABLE dept IS 'department information'; select * FROM user_tab_comments; 10. drop the FIRST_NAME column from the EMP table. Confirm your modification by checking the description of the table. ALTER TABLE emp drop COLUMN first_name; DESC emp 11. 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. ALTER TABLE emp SET UNUSED (dept_id); DESC emp; 12. drop all the UNUSED columns from the EMP table. Confirm your modification by checking the description of the table. ALTER TABLE emp drop UNUSED columns; DESC emp[/CODE] اتمنى من الجميع الاستفاده سلام 

 اسم العضو:عباس جاسم محمد  الثلاثاء,14/ذو القعدة/1432 هـ,12:34 صباحاً
السلام عليكم اني اخوك من العراق احتاج مساعدة في الاوراكل الفورم السادس اي وهي مشكلة كيف تكوين شربط اخبار متحرك للاخبار العاجلة على الفورم السادس باستخدام الاوراكل اذا واحد ممكن يساعدني في الفورم ممكن يبعث الي ملف مع كود الخاص بالشريط الاخبار يكمنكم ارسال ملف على الايميل fadel.abbas58@yahoo.com ان شاء الله يحفط من كل مكروه الي يعمل الي ويحفظ الامة الاسلامية اجمعين 

جميع الحقوق محفوظة لـمنتديات المحيط العربي 2004 - 2019