Oracle SQL Practice exercises


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 who has salary less than 250.
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 who placed orders with value less than $500.
            SELECT            a.aname            FROM               ORDERS o, AGENTS a
            WHERE            a.city = ‘Amman’ and o.dollars < 500 and           o.aid = a.aid;
g)         Find all the products names of products in Amman city ordered in March month.
            SELECT            p.pname           FROM               ORDERS o, PRODUCTS p
            WHERE            p.city = ‘Amman’ and o.month = ‘March’ and      o.pid = p.pid;
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 city.
            SELECT            aid        FROM               CUSTOMERS c, ORDERS o
            WHERE            o.dollars > 500 and c.city = ‘Amman’ and           c.cid = o.cid;