emp
|
ename
|
Salary
|
supno
|
dno
|
Write an SQL statement for each of the following requests.
INSERT INTO emp values (22, "Ahmad",
20000, 2, 3);
INSERT INTO emp(eno, ename, salary, supno,
dno) VALUES (22,"Ahmad", 20000,
2, 3);
INSERT INTO emp(ename, eno, salary,
supno, dno) VALUES ("Ahamd",22,
20000, 2, 3);
2. Insert the following
record to emp table: 23, "Ali",
, 1, 3 (salary is null)
INSERT INTO emp values (23,
"Ali", NULL, 1, 3);
INSERT INTO emp values (23,
"Ali", , 1, 3);
INSERT INTO emp(eno, ename, supno,
dno) VALUES (23, "Ali", 1, 3);
3. List all the data in
the emp table. SELECT * FROM emp;
4. List employee number
and salary from emp table.
SELECT eno, salary FROM emp;
5. Change the department
of employ # 10 to be 2.
UPDATE emp SET dno
= 2 WHERE eno = 10;
6. Change the
department of employ # 13 to be 2 and the supervisor to be 3.
UPDATE emp SET supno = 3, dno = 2 WHERE eno = 13;
7. Delete the employee
whose name "irwin".
DELETE FROM emp WHERE ename = "irwin";
8. What does the
following query do? DELETE FROM emp;
9. What does the following query do? INSERT INTO proj SELECT * FROM project;
10. Retrieve eno, ename, salary for all employees in department
3.
SELECT eno, ename, salary FROM emp WHERE
dno = 3;
11. Retrieve ename, dno for all employees who has salary
greater that 25000.
SELECT eno, ename, dno FROM emp WHERE
salary > 25000;
12. Display the salary of each employee with an increase 10%.
SELECT eno, ename, salary*1.1 as
newsal FROM emp;
13. Retrieve ename for employees in dept # 2
SELECT ename FROM emp WHERE dno = 2
AND salary < 250;
14. List all employees that are not in department 1.
SELECT * FROM emp WHERE NOT(dno =
1);
SELECT * FROM emp WHERE dno
<> 1;
15. List all employees that has salary between 100 and 250.
SELECT eno, ename FROM emp WHERE
salary>=100 AND salary<=250;
SELECT eno, ename FROM emp WHERE
salary BETWEEN 100 AND 250;
16. List all employees that do no have a supervisor.
SELECT eno, ename FROM emp WHERE
supno IS NULL;
17. List all employees whose names start by letter
"s".
SELECT eno, ename FROM emp WHERE
ename LIKE "s%"; ORACLE
18. List all employees whose names do not start with letter
"a".
SELECT eno, ename FROM emp WHERE
ename NOT LIKE "a%"; ORACLE
19. List all employees who are working at department 1 or 2.
SELECT eno, ename FROM emp WHERE
dno = 1 OR dno = 2;
SELECT eno, ename FROM emp WHERE
dno IN (1, 2);
20. List all employees sorted by their names.
SELECT eno, ename FROM emp ORDER BY
ename;
21. List all employees sorted by their names descending.
SELECT eno, ename FROM emp ORDER BY
ename DESC;
22. List all employees ordered by their dno and then by supno.
SELECT eno, ename FROM emp ORDER BY
dno, supno;
23. List all salary values. SELECT salary FROM
emp;
24. List all unique salary values. SELECT DISTINCT salary FROM emp;
25. Display the count, maximum, minimum, average of salary.
SELECT COUNT(*), MAX(salary),
MIN(salary),AVG(salary) FROM emp;
26. Display the count, max, and sum of salary of dno = 2.
SELECT COUNT(*), MAX(sal), SUM(sal)
FROM emp WHERE dno = 2;
27. Display the ename for employees that have maximum salary.
SELECT eno, ename FROM emp WHERE
salary = Select MAX(salary) from emp);
1. List
all data about employees. select
_______ from _________;
2. Insert
new record to the dept table with the following values: deptno = 30, dname =
"housing" and location = "Zalaq".
insert _________ dept
________ (30, "housing", "Zalaq");
3. Delete all
departments that are located in "Zalaq". delete _
dept _ location="Zalaq";
4. Give
a salary increase by 10% for all employees in department #3.
________ emp ________ salary
= salary*1.1 where deptno=3;
5. List empno, ename,
and salary for all employees who have commission 10.
_________ empno, ename,
salary from emp where ______________;
6. List empno and salary for all
employees who have salary greater that 2000.
select empno, salary from
__________ where ________________;
7. List empno and salary of each
employee with an increase 10%. Name the increased salary as newsal.
select empno, salary*_______
as ______ from emp;
8. List empno, ename for employees in
department # 2 who has salary less than 1500.
select empno, ename from emp
where deptno = 2 ______ salary _____;
9. List all data about employees that
are not in department #1.
select _______ from emp
where ________ (deptno = 1);
10. List all data about employees that
have salary between 1000 and 2000.
select * from emp WHERE
salary _________ 1000 _______ 2000;
11. List all data about employees that do
not have a manager.
select * from emp where
___________ is ___________;
12. List all data about employees whose
names end with the letter "d".
select * from emp where
ename ________"___";
13. List all data about employees who have
commission 10 or 40.
select * from emp where
________ ________ (10, 40);
14. List all data about employees sorted
by their salaries descending.
select * from emp _________
salary ________;
15. List all unique commission values. select ___________ comm ________ emp;
16. List the maximum salary value. select _______(_________) from emp;
17. List the average of salary values of
employees in department #3.
select ________(________)
from emp where deptno = 3;
18. For each department,
list deptno and the sum of salary values of its employees.
select deptno, sum(salary)
from emp ____________ _______;
19. List the deptno for
departments that have more than 3 employees.
select deptno from emp group
by deptno _______ _______ > 3;
20. What is the output of
the following query?
select * from emp where comm
< (select min(comm) from emp);
Consider the
following table descriptions. Solve in SQL the following queries.
CUSTOMERS A table containing information about
customers
cid Unique identifier for a
customer
cname Name of a customer
city City where the customer is
located
discnt Each customer has a discount percent
AGENTS A table containing
information about agent employees
aid Unique identifier for an agent
aname Name of agent
city City where agent is based
percent Percent commission each agent receives on
each sale
PRODUCTS A table containing information
about products for sale
pid Unique identifier for a
product
pname Name of product
city City where this product is
warehoused
quantity Quantity on hand for sale
price Wholesale price for each unit product
ORDERS A table containing
information about orders
ordno Unique identifier for this order
month Month the order was placed
cid This customer …
aid ... purchased through this agent ...
pid ... this specific product ...
qty ... in this total quantity ...
dollars ... at this dollar cost
a) Find
all (ordno, pid) pairs for orders
of quantity equal to 1000 or more.
SELECT ordno,
pid FROM ORDERS WHERE qty >= 1000;
b) Find
all product
names of
products priced between $0.5 and $1.00 inclusive.
SELECT pname FROM PRODUCTS
WHERE price between 0.5 and 1;
c) Find
all (ordno,
cname)
pairs for orders of dollar value less than $500. Use one join
here.
SELECT ordno,
cname FROM ORDERS o, CUSTOMERS c
WHERE dollars
< 500 and o.cid = c.cid;
d) Find
all (ordno,
aname)
pairs for orders in March month. Use one join here.
SELECT ordno,
aname FROM ORDERS o, AGENTS a
WHERE month
= ‘March’ and o.aid = a.aid;
e) Find
all (ordno,
cname, aname)
triples for orders in March month. Use two joins here.
SELECT ordno,
cname, aname FROM ORDERS o, CUSTOMERS
c, AGENTS a
WHERE month
= ‘March’ and o.cid = c.cid and o.aid
= a.aid;
f) Find all the names of agents in Amman
SELECT a.aname FROM ORDERS
o, AGENTS a
WHERE a.city
= ‘Amman
g) Find all the products names of products
in Amman
SELECT p.pname FROM ORDERS
o, PRODUCTS p
WHERE p.city
= ‘Amman
h) Find all (cid, aid, pid) triples for customer, agent,
product combinations that are all in the same city.
SELECT c.cid, a.aid, p.pid FROM CUSTOMERS
c, AGENTS a, PRODUCTS p
WHERE c.city
= a.city and a.city = p.city;
i) Display
all pairs of aids for agent
who live in the same city.
SELECT a1.aid,
a2.aid FROM AGENTS a1, AGENTS a2
WHERE a1.city
= a2.city and a1.aid < a2.aid;
j) Find pids of product ordered through agent
a03 but not through agent a06.
SELECT Distinct
pid FROM ORDERS
WHERE aid
= ‘a03’ and pid not in (Select pid from orders where aid = ‘a06’)
k) Get aids of agents who place individual orders greater
than $500 for customers living in Amman
SELECT aid FROM CUSTOMERS
c, ORDERS o
WHERE o.dollars
> 500 and c.city = ‘Amman
