Reference no: EM133569903
Database Fundamentals
Tutorial - 1
Task 1: Using Northwind Database, write SQL statements for the following data retrieval operations.
Note: You may need to refer to the schema for the exact field names while framing the queries to the following questions. While framing the question, the words ‘code', ‘number' or ‘ID' may have been synonymously used. For instance, ‘Customer Number' when used may refer to a field called CustomerID. Similarly, wherever the word ‘name' is used, appropriate interpretation may be needed based on the schema - for instance if ‘customer name' is required to be printed, you may need to retrieve CompanyName field from the Customers Table; likewise, when ‘employee name' is required (without any further qualification, you may retrieve the lastname field of Employees table.
Exercise
List all details of all Shippers that the company is dealing with.
List the all employees - you need to display only the details of their First Name, Last Name, Title, Date of birth and their city of residence.
Extract the information of employees having title (designation) as Sales Representative.
Retrieve the details of all orders made on 19 May 1997.
Retrieve details of all customers that are in the cities of London or Madrid.
Display all unique City from customer's table.
Display details of Products having unitprice in range of 100 to 200.
Display details of Products which unitprices are below 100 and above 200.
Display details of 'Ernst Handel' Customers
Display details of all Employees residing in USA or UK.
Tutorial - 2
Task 1: Using Northwind Database, write SQL statements for the following data retrieval operations.
Note: You may need to refer to the schema for the exact field names while framing the queries to the following questions. While framing the question, the words ‘code', ‘number' or ‘ID' may have been synonymously used. For instance, ‘Customer Number' when used may refer to a field called CustomerID. Similarly, wherever the word ‘name' is used, appropriate interpretation may be needed based on the schema - for instance if ‘customer name' is required to be printed, you may need to retrieve CompanyName field from the Customers Table; likewise, when ‘employee name' is required (without any further qualification, you may retrieve the lastname field of Employees table.
Exercise
List all details of Customers with the output presented in descending order of Company names.
Use Alias for each few columns of Customers and display Customers details.
Using "BETWEEN" Display product details having inventory of more than 100 and less than 120.
Display all details of customers having contact name starting with letter ‘r' or ending with ‘er'
Display the list of products that should be re-order.
List out only 5 percentage of total records from customers table.
Retrieve the Customer IDs and contact names of all customers who have yet to order any products.
Display product details which are ordered atleast once and having inventory of more than 100
List CustomerID and Company Name, those had orders in past having shipment postal code 50739.
Retrieve the details of all Products' having the word "lager" in the product label.
List all Orders (Order number and date) of the orders made by the Customer "Thomas Hardy". Note:
Thomas Hardy is the name of the contact person.
Tutorial - 3
Task 1: Using Northwind Database, write SQL statements for the following data retrieval operations.
Note: You may need to refer to the schema for the exact field names while framing the queries to the following questions. While framing the question, the words ‘code', ‘number' or ‘ID' may have been synonymously used. For instance, ‘Customer Number' when used may refer to a field called CustomerID. Similarly, wherever the word ‘name' is used, appropriate interpretation may be needed based on the schema - for instance if ‘customer name' is required to be printed, you may need to retrieve CompanyName field from the Customers Table; likewise, when ‘employee name' is required (without any further qualification, you may retrieve the lastname field of Employees table.
Exercise
Retrieve the IDs and company names of all customers that have made more than 10 orders.
Retrieve all details of Orders administered by persons who hold the designation Sales Representative and shipped by United Package.
Select the IDs and contact names of customers that have more orders than customer ‘BONAP'.
Retrieve the names of all employee. For each employee list the name of his/her manager in adjacent columns.
Find out all the orders placed by all the customers.
Find out all the Orders processed by each Employee.
Display all customers details (who have placed orders and who have not placed orders) and orders details which are placed by customers.
Find out all the customers and their orders and filter the result on customer's city (London).
Display all customers details (who have placed orders) and orders details which are placed by customers.
Tutorial - 4
Task 1: Using Northwind Database, write SQL statements for the following data retrieval operations.
Note: You may need to refer to the schema for the exact field names while framing the queries to the following questions. While framing the question, the words ‘code', ‘number' or ‘ID' may have been synonymously used. For instance, ‘Customer Number' when used may refer to a field called CustomerID. Similarly, wherever the word ‘name' is used, appropriate interpretation may be needed based on the schema - for instance if ‘customer name' is required to be printed, you may need to retrieve CompanyName field from the Customers Table; likewise, when ‘employee name' is required (without any further qualification, you may retrieve the lastname field of Employees table.
Excercise
Retrieve the first three letters of the employees' last names.
Retrieve the last two characters from the employees' last names.
Retrieve five characters from the employees' last names, starting at the second character.
Retrieve the firstname and lastname of employee. Output should be in the form of full name (firstname + "" + lastname) having first character in upper case for both firstname and lastname, and other characters in lower case.
Retrieve maximum, minimum and average of unitprice column from order details table.
Check whether each value in the PostalCode column of the Customers table are numeric or not.
Display the Day, Month and Year separetly of today's date.
Display day of year, day, week, weekday, hours, minutes and seconds of current day.
Display the dates respectively after adding 4 years, 2 months and 25 days to the current.
Display the day differences from Orders table between following:
a. Order date and required date.
b. Order date and shipped date.
c. Shipped date and required date.