Question

Write and run SQL statements to complete the following tasks

1. Show the details of the employees who are located in area code 901 and their manager employee number is 108.
2. Show the details of the employees who are also mangers.
3. Show the details of the customers whose balance is greater than 220 but less than 500.
4. Show the details of the customers whose balance is highest.
5. Show customer 10014’s name and the product’s descriptions which he/she purchased and the number of units of each.
6. List the names of customers and the number of invoices each customer has generated.
7. Show the codes and names of the customers who have not generated any invoice.
8. Add a new attribute (column) engaged varchar(3) to the customer table and update the engaged attribute for the customers who have not generated any invoice to ‘No’.

Answer 1

1. Select employee.* from employee inner join department on employee.Dept_Num = department.Dept_Num where Emp_Phone LIKE '%901%' and department.emp_num = 108;
2. Select employee.* from employee , department where department.Emp_Num = employee.Emp_Num;
3. Select * from Customer where Cust_Balance > 220 and Cust_Balance < 500;
4. Select * from Customer where Cust_Balance = ( Select Max(Cust_Balance) from Customer);
5. Select Cust_Fname, Cust_Lname, Prod_Descript, Line_Qty from Customer, Invoice, Line, product where Cust_Code = 10014;
6. Select Cust_Fname, Cust_Lname, Count(Inv_Num) from Customer inner join Invoice on Customer.Cust_Code = Invoice.Cust_Code group by Cust_Fname, Cust_Lname;
7. Select Customer.Cust_Code, Cust_Fname, Cust_Lname from Customer , Invoice where Customer.Cust_Code != Invoice.Cust_Code ;
8. Alter Table Customer Add Column engaged varchar(3) ;
9. Update Customer set engaged = 'No' where Cust_Code NOT IN( Select Cust_Code from Invoice);

Do ask if any doubt.