oracle9 بتاريخ: 12 يناير 2012 تقديم بلاغ مشاركة بتاريخ: 12 يناير 2012 أخوانى أقدم بكم بعض جمل سيكوال لإفادة من يريد تعلم جمل سيكوال Chapter 1 002 003 Practice 1 004 1. Initiate an iSQL*Plus session using the user ID and password provided by the instructor. 005 User_id : scott 006 Password: tiger 007 008 2. iSQL*Plus commands access the database. 009 True/False 010 True 011 012 3. The following select statement executes successfully: 013 select last_name, job_id, salary AS Sal FROM employees; 014 True/False 015 True 016 017 4. The following select statement executes successfully: 018 select * 019 FROM job_grades; 020 True/False 021 False 022 023 5. There are four coding errors in this statement. Can you identify them? 024 select employee_id, last_name 025 sal x 12 ANNUAL SALARY 026 FROM employees; 027 False 028 029 6. Show the structure of the DEPARTMENTS table. select all data from the table. 030 1.desc departments; 031 2. select * 032 FROM departments; 033 034 035 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. 036 desc employees; 037 038 039 8. Run your query in the file lab1_7.sql. 040 select employee_id, last_name, job_id, hire_date "Start Date" 041 FROM employees; 042 043 9. create a query to display unique job codes from the EMPLOYEES table. 044 select job_id 045 FROM employees; 046 047 048 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. 049 select employee_id "emp#", last_name "Employee" ,job_id "Job", 050 hire_date "hire date" 051 FROM employees; 052 053 11. Display the last name concatenated with the job ID, separated by a comma and space, and name the column Employee and Title. 054 select last_name || ',' || job_id "employee and title" 055 FROM employees; 056 057 12. create a query to display all the data from the EMPLOYEES table. Separate each column by a comma. Name the column THE_OUTPUT 058 select EMPLOYEE_ID||',' 059 ||FIRST_NAME||','||LAST_NAME||','||EMAIL||',' 060 ||PHONE_NUMBER||','||HIRE_DATE||','||JOB_ID||',' 061 ||SALARY||','||COMMISSION_PCT|| ',' 062 ||MANAGER_ID||','||DEPARTMENT_ID "THE _OUTPUT" 063 FROM employees; 064 065 066 067 068 069 Chapter 2 070 071 Practice 2 072 1. create a query to display the last name and salary of employees earning more than $12,000. 073 select last_name ,salary 074 FROM employees 075 WHERE salary >12000; 076 077 2. create a query to display the employee last name and department number for employee number 078 176 079 select last_name,department_id 080 FROM employees 081 WHERE employee_id=176; 082 083 084 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. 085 select last_name,salary 086 FROM employees 087 WHERE salary not between 12000 and 55000; 088 089 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. 090 select last_name,job_id ,hire_date 091 FROM employees 092 WHERE hire_date between '20-feb-98' AND '01-may-98'; 093 094 095 5. Display the last name and department number of all employees in departments 20 AND 50 in alphabetical order by name. 096 select last_name ,department_id 097 FROM employees 098 WHERE department_id in(20,50) 099 ORDER BY last_name; 100 101 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. 102 select last_name "employee",salary "monthly salary" 103 FROM employees 104 WHERE salary between 12000 and 5000 105 AND department_id in(20,50); 106 107 108 7. Display the last name and hire date of every employee who was hired in 1994. 109 select last_name , hire_date 110 FROM employees 111 WHERE hire_date like'%94'; 112 113 8. Display the last name and job title of all employees who do not have a manager. 114 select last_name ,job_id 115 FROM employees 116 WHERE manager_id is null; 117 118 9. Display the last name, salary, and commission for all employees who earn commissions. Sort data in descending Order of salary and commissions 119 select last_name , salary, commission_pct 120 FROM employees 121 WHERE commission_pct is not null 122 ORDER BY salary DESC; 123 124 125 10. Display the last names of all employees where the third letter of the name is an a. 126 select last_name 127 FROM employees 128 WHERE last_name like'__a%'; 129 130 131 132 133 134 11. Display the last name of all employees who have an a and an e in their last name. 135 select last_name 136 FROM employees 137 WHERE last_name like'%a%' 138 OR last_name like '%e%'; 139 140 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. 141 select last_name,job_id,salary 142 FROM employees 143 WHERE job_id like'ST_CLERK' 144 OR job_id like 'SA_REP' 145 AND salary not in(2005,3500,7000); 146 147 148 13. Modify lab2_6.sql to display the last name, salary, and commission for all employees whose commission amount is 20%. 149 select last_name "Employee",salary "Monthly Salary",commission_pct 150 FROM employees 151 WHERE commission_pct =.2 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 Chapter 3 172 173 Practice 3 174 1.Write a query to display the current date. Label the column Date. 175 select sysdate “data” 176 FROM employees; 177 178 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. 179 select EMPLOYEE_ID , LAST_NAME , salary,salary*1.15 180 "NEW _SALARY" 181 FROM employees; 182 183 3. Run your query in the file lab3_2.sql. 184 185 4. Modify your query lab3_2.sql to add a column that subtracts the old salary from the new salary. Label the column Increase. 186 select EMPLOYEE_ID,LAST_NAME, salary, 187 salary*1.15 "NEW _ SALARY",salary*1.15- salary "Increase" 188 FROM employees; 189 190 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. 191 select initcap (last_name) "name",length(last_name) "length" 192 FROM employees 193 WHERE last_name like 'A%' 194 OR last_name like 'J%' 195 OR last_name like 'M%' 196 ORDER BY last_name; 197 198 199 200 201 202 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. 203 order your results by the number of months employed. Round the number of months up to the closest whole number. 204 select last_name , 205 round (months_between(sysdate, 206 hire_date),0)"MONTHS_WORKE" 207 FROM employees 208 ORDER BY round (months_between( sysdate ,hire_date),0); 209 210 211 7. Write a query that produces the following for each employee: 212 <employee last name> earns <salary> monthly but wants <3 times 213 salary>. Label the column Dream Salaries. 214 select last_name ||'earns' || to_char(salary,'$99,999.00') ||'monthly but 215 wents'||to_char(3* salary,'$99,999.00') "dream salaries" 216 FROM employees; 217 218 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. 219 select last_name, lpad (salary,15,'$') "salary" 220 FROM employees; 221 222 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 223 format similar to “Monday, the Thirty-First of July, 2000.” 224 select last_name ,hire_date, 225 to_char(next_day(add_months(hire_date,6) ,'monday'), 226 'day "the" fmDdspth"of" month yyyy') "review" 227 FROM employees; 228 229 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. 230 select last_name ,hire_date,to_char(hire_date,'day') "day" 231 FROM employees 232 ORDER BY to_char(hire_date,'day') ; 233 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. 234 select last_name ,nvl (to_char(commission_pct),'NO COMMISSION') 235 FROM employees; 236 237 238 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. 239 select rpad(last_name,trunc(salary/1000,0)+length(last_name),'*') 240 EMPLOYEES_AND_THEIR_SALARIES 241 FROM employees;; 242 243 244 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: 245 Job Grade 246 AD_PRES A 247 ST_MAN B 248 IT_PROG C 249 SA_REP D 250 ST_CLERK E 251 select job_id , 252 Decode (job_id, 'AD_PRES','A', 253 'ST_MAN','B', 254 'IT_PROG','C', 255 'SA_REP','D', 256 'ST_CLERK','E', 257 '0') 258 FROM employees; 259 260 261 262 263 264 265 Chapter 4 266 267 Practice 4 268 1. Write a query to display the last name, department number, and department name for all employees. 269 select e.last_name, e.department_id ,d.department_name 270 FROM employees e, departments d 271 WHERE e.department_id = d.department_id; 272 273 2. create a unique listing of all jobs that are in department 80. Include the location of the department in the output. 274 select e.job_id , d.location_id 275 FROM employees e, departments d 276 WHERE d.department_id= 80 277 AND e.department_id = d.department_id; 278 279 280 3. Write a query to display the employee last name, department name, location ID, and city of all employees who earn a commission. 281 select e.last_name,d.department_name,l.location_id,l.city 282 FROM employees e, departments d ,locations l 283 WHERE e.department_id = d.department_id 284 AND d.location_id=l.location_id 285 AND commission_pct is not null ; 286 287 4. Display the employee last name and department name for all employees who have an a (lowercase) in their last names. 288 select e.last_name,d.department_name 289 FROM employees e, departments d 290 WHERE e.department_id = d.department_id 291 AND last_name like '%a%' ; 292 293 294 5. Write a query to display the last name, job, department number, and department name for all employees who work in Toronto. 295 select e.last_name ,e.job_id,e.department_id,d.department_name 296 FROM employees e,departments d,locations l 297 WHERE l.city like 'Toronto' 298 AND e.department_id=d.department_id; 299 Chapter 5 300 301 Practice 5 302 Determine the validity of the following three statements. Circle either True or False. 303 1. Group functions work across many rows to produce one result per group. 304 True/False 305 True 306 307 2. Group functions include nulls in calculations. 308 True/False 309 False 310 311 3. The WHERE clause restricts rows prior to inclusion in a group calculation. 312 True/False 313 False 314 315 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. 316 select max(salary)"Maximum",min(salary)"Minimum", 317 sum (salary)"Sum", avg(salary)"Average" 318 FROM employees; 319 320 5. Modify the query in lab5_4.sql to display the minimum, maximum, sum, and average salary for each job type. 321 select job_id,max(salary)"Maximum",min(salary)"Minimum", 322 sum (salary)"Sum", avg(salary)"Average" 323 FROM employees 324 GROUP BY job_id; 325 326 6. Write a query to display the number of people with the same job. 327 select job_id,count(employee_id) 328 FROM employees 329 GROUP BY job_id;; 330 331 332 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. 333 select count (DISTINCT(manager_id)) " Number of Manager" 334 FROM employees 335 336 337 8. Write a query that displays the difference between the highest and 338 lowest salaries. Label the column DIFFERENCE. 339 select max(salary)-min(salary) difference 340 FROM employees;; 341 342 343 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. 344 select manager_id ,min(salary) 345 FROM employees 346 HAVING min(salary)>=6000 347 AND manager_id is not null 348 GROUP BY manager_id 349 ORDER BY min(salary) DESC; 350 351 352 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. 353 select d.department_name "Name",d.location_id "Location", 354 count(e.employee_id) "Nember of People",avg(e.salary) 355 "Salary" 356 FROM departments d,employees e 357 WHERE d.department_id= e.department_id 358 GROUP BY d.department_name,location_id ; 359 360 361 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. 362 select count(employee_id) "Total", 363 sum(decode(to_char(hire_date,'yyyy'),1995,1,0))"1995", 364 sum(decode(to_char(hire_date,'yyyy'),1996,1,0))"1996", 365 sum(decode(to_char(hire_date,'yyyy'),1997,1,0))"1997", 366 sum(decode(to_char(hire_date,'yyyy'),1998,1,0))"1998" 367 FROM employees; 368 369 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. 370 371 select job_id "Job", 372 sum(decode (department_id,20,salary,0))"dept 20", 373 sum(decode (department_id,50,salary,0))"dept 50", 374 sum(decode (department_id,80,salary,0))"dept 80", 375 sum(decode (department_id,90,salary,0))"dept 90", 376 sum(salary) 377 FROM employees 378 GROUP BY job_id; اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
oracle9 بتاريخ: 12 يناير 2012 كاتب الموضوع تقديم بلاغ مشاركة بتاريخ: 12 يناير 2012 إليكم الجزء الثانى 1. Write a query to display the last name and hire date of any employee in the same department as Zlotkey. Exclude Zlotkey. 005 select last_name hire_date,department_id 006 FROM employees 007 WHERE department_id =(select department_id 008 FROM employees 009 WHERE last_name like 'Zlotkey') 010 AND last_name not like 'Zlotkey'; 011 012 013 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. 014 select last_name ,employee_id,salary 015 FROM employees 016 WHERE salary > (select avg(salary) 017 FROM employees) 018 019 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. 020 select employee_id,last_name 021 FROM employees 022 WHERE department_id in(select department_id 023 FROM employees 024 WHERE last_name like'%u%'); 025 026 027 4. Display the last name, department number, and job ID of all employees whose department location ID is 1700 028 select last_name,department_id,job_id 029 FROM employees 030 WHERE department_id in(select department_id 031 FROM departments 032 WHERE location_id =1700); 033 034 5. Display the last name and salary of every employee who reports to King. 035 036 037 038 6. Display the department number, last name, and job ID for every employee in the Executive department. 039 select department_ id , last _ name ,job_id 040 FROM employees 041 WHERE job_id like 'AD_%'; 042 043 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. 044 select employee _ id, last_ name , salary , department _id 045 FROM employees 046 WHERE salary >(select avg(salary) 047 FROM employees) 048 AND department _ id in (select department _ id 049 FROM employees 050 WHERE last_name like '%u%'); 051 052 053 054 055 Chapter 8 056 057 Practice 8 058 1. Run the statement in the lab8_1.sql script to build the MY_EMPLOYEE table to be used for the lab. 059 create 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 065 2. Describe the structure of the MY_EMPLOYEE table to identify the column names. 066 DESC my_employees 067 068 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. 069 insert INTO my_employees 070 VALUES (1,'Patel','Ralph', 'rpatel' ,895); 071 072 4. Populate the MY_EMPLOYEE table with the second row of sample data from the preceding list. 073 This time, list the columns explicitly in the insert clause. 074 insert INTO my_employees(id,last_name ,first_name,userid,salary) 075 VALUES (2,' Dancs', 'Betty',' bdancs', 860); 076 077 5. Confirm your addition to the table. 078 select * 079 FROM my_employees; 080 081 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. 082 insert INTO my_employees 083 VALUES (&id,' &frist_mame', 084 '&last_name','lower(substr(&frist_name,1,1))|| 085 'lower( substr(&last_name,1,7)) ', &salary); 086 087 088 089 7. Populate the table with the next two rows of sample data by running the insert statement in the 090 script that you created. 091 insert INTO my_employees 092 VALUES (&id,' &frist_mame', 093 '&last_name','lower(substr(&frist_name,1,1))|| 094 'lower( substr(&last_name,1,7)) ', &salary); 095 096 097 8. Confirm your additions to the table. 098 select * 099 FROM my_employees; 100 101 9. Make the data additions permanent. 102 COMMIT; 103 104 10. Change the last name of employee 3 to Drexler. 105 update my_employees 106 SET last_name = 'Drexler' 107 WHERE id=3; 108 109 11. Change the salary to 1000 for all employees with a salary less than 900. 110 update my_employees 111 SET salary= 1000 112 WHERE salary <900; 113 114 12. Verify your changes to the table. 115 select * 116 FROM my_employees; 117 118 13. delete Betty Dancs from the MY_EMPLOYEE table. 119 delete FROM my_employees 120 WHERE first_name like 'Betty'; 121 122 14. Confirm your changes to the table 123 select * 124 FROM my_employees; 125 126 127 128 15. Commit all pending changes. 129 COMMIT; 130 131 16. 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. 133 insert INTO my_employees 134 VALUES (&id,' &frist_mame', 135 '&last_name','lower(substr(&frist_name,1,1))|| 136 'lower( substr(&last_name,1,7)) ', &salary); 137 138 17. Confirm your addition to the table. 139 select * 140 FROM my_employees; 141 142 18. Mark an intermediate point in the processing of the transaction. 143 SAVEPOINT b; 144 145 19. Empty the entire table. 146 delete FROM my_employees 147 148 20. Confirm that the table is empty. 149 select * 150 FROM my_employees; 151 152 21. Discard the most recent delete operation without discarding the earlier insert operation. 153 ROLLBACK TO b; 154 155 22. Confirm that the new row is still intact. 156 select * 157 FROM my_employees; 158 159 23. Make the data addition permanent. 160 COMMIT; 161 162 163 164 165 Chapter 9 166 167 Practice 9 168 1. create the DEPT table based on the following table instance chart. 169 Confirm that the table is created. 170 create TABLE dept 171 (ID number(7),NAME varchar2(25)); 172 DESC dept; 173 174 2. Populate the DEPT table with data from the DEPARTMENTS table. Include only columns that you need. 175 create TABLE dept 176 AS select department_id,department_name 177 FROM departments; 178 179 3. create the EMP table based on the following table instance chart. 180 Confirm that the table is created 181 create TABLE emp (id number(7),last_name varchar2(25), 182 first_name varchar2(25),dept_id number(7)); 183 DESC emp; 184 185 4. Modify the EMP table to allow for longer employee last names. Confirm your modification. 186 ALTER TABLE emp 187 MODIFY (last_name varchar2(50)); 188 DESC emp; 189 190 5. Confirm that both the DEPT and EMP tables are stored in the data dictionary. (Hint: USER_TABLES) 191 select table_name 192 FROM user_tables; 193 194 195 196 197 198 199 200 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. 201 Name the columns in your new table ID, FIRST_NAME, LAST_NAME, SALARY , and DEPT_ID, respectively. 202 203 204 create TABLE employees2 205 AS select employee_id "ID",first_name "FIRST_NAME" , 206 last_name "LAST_NAME", 207 salary "SALARY" ,department_id "DEPT_ID" 208 FROM employees; 209 DESC employees2 210 211 7. drop the EMP table. 212 drop TABLE emp; 213 214 8. Rename the EMPLOYEES2 table as EMP. 215 RENAME employees TO emp; 216 217 9. Add a comment to the DEPT and EMP table definitions describing the tables. Confirm your additions in the data dictionary. 218 COMMENT ON TABLE emp 219 IS 'employees information'; 220 COMMENT ON TABLE dept 221 IS 'department information'; 222 select * 223 FROM user_tab_comments; 224 225 10. drop the FIRST_NAME column from the EMP table. Confirm your modification by checking the description of the table. 226 ALTER TABLE emp 227 drop COLUMN first_name; 228 DESC emp 229 230 231 232 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. 233 ALTER TABLE emp 234 SET UNUSED (dept_id); 235 DESC emp; 236 237 12. drop all the UNUSED columns from the EMP table. Confirm your modification by checking the description of the table. 238 ALTER TABLE emp 239 drop UNUSED columns; 240 DESC emp اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
عزيز القادري بتاريخ: 12 يناير 2012 تقديم بلاغ مشاركة بتاريخ: 12 يناير 2012 جزاك الله خير وزادك الله علماً اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
mohamed hosary بتاريخ: 12 يناير 2012 تقديم بلاغ مشاركة بتاريخ: 12 يناير 2012 جزاك الله كل خير اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
محمد بطاله بتاريخ: 12 يناير 2012 تقديم بلاغ مشاركة بتاريخ: 12 يناير 2012 جزاك الله خيرااا اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
medani بتاريخ: 13 يناير 2012 تقديم بلاغ مشاركة بتاريخ: 13 يناير 2012 مشاركة اكثر من راااااااااااااااااااائعةجزاك الله خير وزادك علما وان شاء الله فى ميزان حسناتك اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
oracle9 بتاريخ: 15 يناير 2012 كاتب الموضوع تقديم بلاغ مشاركة بتاريخ: 15 يناير 2012 هل من مشاكل تواجهكم يا أخوانى فى جمل الإستعلامأنا تحت أمركم اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
Ahmad.Hasan بتاريخ: 15 يناير 2012 تقديم بلاغ مشاركة بتاريخ: 15 يناير 2012 جزاك الله كل خير... اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
Recommended Posts
انضم إلى المناقشة
يمكنك المشاركة الآن والتسجيل لاحقاً. إذا كان لديك حساب, سجل دخولك الآن لتقوم بالمشاركة من خلال حسابك.