Reference no: EM13727320

Based On above MS access model answer the following SQL query questions.

Question 1: Design a query to find out details of CustomerNo, first name , last name who have paid amount more than the average amount paid by all customers.

Question 2: The Garage centre is interested in knowing details of Ford cars which have come for more than two times for servicing. Design a query to display the requested details.

PART 2

Build the above model in MS SQL server (Refer question 1 below under design SQL queries:). Choose appropriate data types for each attribute carefully. Choose PK and FK as shown in the above structure. Populate tables with appropriate data. In each table at least 10 records are required to be entered.

Question 1: Use CREATE statement to build EMPLOYEE, CUSTOMER, CAR, SERVICE tables in MS SQL Server. Rest of the tables can be built by using GUI tools.

Question 2: Use INSERT statement to enter sufficient number (minimum 10) of records into these tables.

Question 3: "Alter" EMPLOYEE table to add a field called Mobile. Choose appropriate data type.

Question 4: Design a SELECT statement to display EmployeeNo, first name and date of birth. The date of birth must be in the year 1990. The employee last name should start with letter "K". All duplicate records must be eliminated.

Question 5: The Management needs listing of employees by last name and first name. The first name should be displayed in capital letters. The entire name should be joined together to display in a single column with a heading "Employee Name". The last and first name should be separated by a comma. The name should start with first name.

Question 6: Employees working in accounts department need restricted access to employee information. Create a view named LimitedEmpl_view that includes only EmployeeNo, First Name , Last Name, HomePhone. The view should have more meaningful names (for the fields) than given in the table / s. The first and last names should be combined to display (Eg: Chandra, N) as EmployeeName & CAST as CHAR(25) . Write a select statement on the view so that employee numbers less than 400 are displayed.

Question 7: Drop LimitedEmpl_view from the database.

Question 8: The management is interested in knowing when a employee is reaching 30 years so that they can send them additional info for certain courses. Display details of EmployeeNo, Employee First Name and employee last name.