Assignment 1: SQL Queries Department Store Database: DeptStoreDatabase-01.accdb

*Assignment 1: SQL Queries*
Department Store Database: DeptStoreDatabase-01.accdb
Queries (Marks 25×4)

Q1 List all employee’s employee number and names within the organisation
Q2 List all employees within the organisation and rename eno column ‘Employee number’.
Q3 List all employees within the organisation whose name begins with “B”.
Q4 List all employee’s details within the organisation
Q5 list all employee’s details within the organisation in alphabetical employee name order.
Q6 Find the names of employees in the Toy department.
Q7 Find the items sold by no departments on the second floor.
Q8 Find the items sold by the departments on the second floor.
Q9 Find the items sold by all departments on the first floor.
Q10 Find the items sold by at least two departments on the second floor.
Q11 Find the salary of Beacham’s manager.
Q12 Find the names of employees who make more than their manager.
Q13 Find the departments where all the employees earn less than their manager.
Q14 Find the names of employees who earn the same as their manager.
Q15 Find the names of the employees who are in the same department as their manager.
Q16 List the departments having an average salary greater than 25000.
Q17 List the departments where the average salary of the employees of each manager is greater than or equal to 25000.
Q18 List the name and manager of the employees of the Toy department who have a salary greater than 25000.
Q19 List pairs of staff names (employee1, employee2) where the name of the employees who earn more than any employee in the TOY department.
Q20 Among all the departments with total salary greater than 50000, find the departments that sell chairs.
Q21 Find the suppliers that supply to the cloths department.
Q22 Find the suppliers that do not supply to the cloths department.
Q23 List the names of employees, the floors they are located, the types of item they sell and suppliers they sell items for.
Q24 List the names of employees with their ‘full address’ details as one field. The full address field is to contain both the address and postcode separated by a commas (‘,’).
Q25 Calculate the total sales for each item sold.

Tags: