بسم الله الرحمن الرحيم
أخوانى وأخواتى الأعزاء لدى بعض التمارين عن الــSQL وهى مفيدة جداً خاصة للمبتدئين
سوف أقوم بوضع عدد منها كل أسبوع بإذن الله
وبعد ذلك سوف أضع إجابتها
ومن يتمكن من إجابتها أتمنى أن يعطى الفرصة لمدة 4 أيام تقريباً لغيرة بأن يفكر فيها
ملاحظة :
كل سؤال يحتوى على OUTPUT وهذا لكى تتأكد من حلك هل هو صح أم خطأ
1. Create a query to display the name and salary of employees earning more than $2850.
Save your SQL statement to a file named p2q1.sql. Run your query.
ENAME SAL
-------- ----
KING 5000
JONES 2975
FORD 3000
SCOTT 3000
2. Create a query to display the employee name and department number for employee number
7566.
ENAME DEPTNO
------ ------
JONES 20
3. Modify p2q1.sql to display the name and salary for all employees whose salary is not in the range of $1500 and $2850. Resave your SQL statement to a file named p2q3.sql. Rerun your query.
ENAME SAL
------- -----
KING 5000
JONES 2975
MARTIN 1250
JAMES 950
WARD 1250
FORD 3000
SMITH 800
SCOTT 3000
ADAMS 1100
MILLER 1300
10 rows selected.
4. Display the employee name, job, and start date of employees hired between February 20, 1981, and May 1, 1981. Order the query in ascending order by start date.
ENAME JOB HIREDATE
------- --------- ----------------
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
JONES MANAGER 02-APR-81
BLAKE MANAGER 01-MAY-81
5. Display the employee name and department number of all employees in departments 10 and 30 in alphabetical order by name.
ENAME DEPTNO
-------- ------
ALLEN 30
BLAKE 30
CLARK 10
JAMES 30
JONES 10
MARTIN 30
MILLER 10
TURNER 30
WARD 30
9 rows selected.
6. Modify p2q3.sql to list the name and salary of employees who earn more than $1500 and are in department 10 or 30. Label the columns Employee and Monthly Salary, respectively. Resave your SQL statement to a file named p2q6.sql. Rerun your query.
Employee Monthly Salary
-------- --------------
KING 5000
BLAKE 2850
CLARK 2450
ALLEN 1600
7. Display the name and hire date of every employee who was hired in 1982.
ENAME HIREDATE
------ ---------
SCOTT 09-DEC-82
MILLER 23-JAN-82
8. Display the name and job title of all employees who do not have a manager.
ENAME JOB
----- ---------
KING PRESIDENT
9. Display the name, salary, and commission for all employees who earn commissions. Sort
data in descending order of salary and commissions.
ENAME SAL COMM
------- ---- ----
ALLEN 1600 300
TURNER 1500 0
MARTIN 1250 1400
WARD 1250 500
10. Display the names of all employees where the third letter of their name is an A.
ENAME
-----------
BLAKE
CLARK
ADAMS
11. Display the name of all employees who have two Ls in their name and are in department 30 or their manager is 7782.
ENAME
----------
ALLEN
MILLER
12. Display the name, job, and salary for all employees whose job is Clerk or Analyst and
their salary is not equal to $1000, $3000, or $5000.
ENAME JOB SAL
------ ------- -----
JAMES CLERK 950
SMITH CLERK 800
ADAMS CLERK 1100
MILLER CLERK 1300
13. Modify p2q6.sql to display the name, salary, and commission for all employees whose
commission amount is greater than their salary increased by 10%. Rerun your query. Resave your query as p2q13.sql.
Employee Monthly Salary COMM
---------- --------------- -----
MARTIN 1250 1400