Tugas Matakuliah Basis Data Kelas Sore

Use the data in those tables and show the SQL statements to display or modify data as indicated in the following questions:

1 Show the ages and salaries of all salespeople.
2 Show the ages and salaries of all salespeople but omit duplicates.
3 Show the names of all salespeople under thirty years old.
4 Show the names of all salespeople who have an order with Abernathy Construction.
5 Show the names and salary of all salespeople who do not have an order with
Abernathy Construction, in ascending order of salary.
6 Compute the number of orders.
7 Compute the number of different customers who have an order.
8 Compute the average age of a salesperson.
9 Show the name of the oldest salesperson.
10.Compute the number of orders for each salesperson.
11.Compute the number of orders for each salesperson, considering only or¬ders for an
amount exceeding 500.
12.Show the names and ages of salespeople who have an order with ABER¬NATHY
CONSTRUCTION, in descending order of age (use a subquery).
13 Show the names and ages of salespeople who have an order with ABERNATHY
CONSTRUCTION, in descending order of age (use a join).
14 Show the age of salespeople who have an order with a customer in MEM¬PHIS
(use a subquery).
15 Show the age of salespeople who have an order with a customer in MEM¬PHIS
(use a join).
16 Show the industry type and ages of the salespeople of all orders for com¬panies in
MEMPHIS.
17 Show the names of salespeople who have two or more orders.
18 Show the names and ages of salespeople who have two or more orders.
19 Show the names and ages of salespeople who have an order with all customers.
20 Show an SQL statement to insert a new row into CUSTOMER.
21 Show an SQL statement to insert a new name and age into SALESPERSON;
assume that salary is not determined.
22 Show an SQL statement to insert rows into a new table, HIGH-ACHIEVER (Name,
Age), in which, to be included, a salesperson must have a salary of at least
100,000.
23 Show an SQL statement to delete customer ABERNATHY CONSTRUCTION.
24 Show an SQL statement to delete all orders for ABERNATHY CONSTRUCTION.
25 Show an SQL statement to change the salary of salesperson JONES to 45,000.
26 Show an SQL statement to give all salespeople a 10 percent pay increase.
27 Assume that salesperson JONES changes his name to PARKS. Show the SQL
statements that make the appropriate changes.

SALESPERSON(Name, Age, Salary)
ORDER(Number, CustName, SalespersonName, Amount)
CUSTOMER(Name, City, IndustryType)

28. Create a virtual table named Salesperson_Staff consisting name and age fields.
29. Create a virtual table named Salesperson_StaffAge consisting name and age (above 30) and salary fields.
30. Create a table named Supervisor using CREATE TABLE command consisting of several fields as follows:

Name CHAR  20, UNIQUE
Age INTEGER, NOT NULL
Salesperson CHAR  2