SQL Revamp Journey Week 3

Week 3 of SQL:

This is the last part of my 21 days SQL challenge series. I have used the Northwind sample database to practice and brush up my SQL skills. I will just post the questions and hints for solving the queries here in this blog. You can find the entire project for Questions and Answers in the following GitHub link. The README.md in the GitHub link provides a step by step guide to load the database onto your mysql server.

The Questions I have used for practising the queries are also attached in the same link as Northwind_Database_Questions.docx and answers/queries are in the Northwind_Database_Answers.docx file.

So, here is my Week 3 recap:

Day 15- Queries #1- #7

Create a report that shows the CategoryName and Description from the categories table sorted by CategoryName.

• Use SELECT for selecting the CategoryName and Description columns and use ORDER BY for sorting the data according to the CategoryName column.

Create a report that shows the ContactName, CompanyName, ContactTitle and Phone number from the customers table sorted by Phone.

• Use SELECT for selecting the ContactName, CompanyName, ContactTitle and Phone number columns and use ORDER BY for sorting the data according to the Phone column.

Create a report that shows the capitalised FirstName and capitalised LastName renamed as FirstName and Lastname respectively and HireDate from the employees table sorted from the newest to the oldest employee.

• Use SELECT for selecting the FirstName, LastName columns and use UPPER to convert the columns into uppercase. Use AS to rename the columns and ORDER BY for sorting the data according to the HireDate column.

Create a report that shows the top 10 OrderID, OrderDate, ShippedDate, CustomerID, Freight from the orders table sorted by Freight in descending order.

• Use SELECT for selecting the required columns and use ORDER BY for sorting the data according to the Freight column with DESC attribute. Use LIMIT to show the top n items.

Create a report that shows all the CustomerID in lowercase letters and renamed as ID from the customers table.

• Use SELECT for selecting the CustomerID column and use LOWER to convert the column into lowercase. Use AS to rename the column.

Create a report that shows the CompanyName, Fax, Phone, Country, HomePage from the suppliers table sorted by the Country in descending order then by CompanyName in ascending order.

• Use SELECT for selecting the required columns and use ORDER BY for sorting the data according to the Country column with DESC attribute followed by CompanyName column without any attribute.

Create a report that shows CompanyName, ContactName of all customers from ‘Buenos Aires’ only.

• Use SELECT for selecting the required columns and use WHERE to find the required City from city column.

Day 16- Queries #8- #14

Create a report showing ProductName, UnitPrice, QuantityPerUnit of products that are out of stock.

• Use SELECT for selecting the required columns and use WHERE to find the required Discontinued product.

Create a report showing all the ContactName, Address, City of all customers not from Germany, Mexico, Spain.

• Use SELECT for selecting the required columns and use WHERE and NOT IN to find the required country.

Create a report showing OrderDate, ShippedDate, CustomerID, Freight of all orders placed on 21 May 1996.

• Use SELECT for selecting the required columns and use WHERE to find the orders required date.

Create a report showing FirstName, LastName, Country from the employees not from the United States.

• Use SELECT for selecting the required columns and use WHERE and <> to find the required country.

Create a report that shows the EmployeeID, OrderID, CustomerID, RequiredDate, ShippedDate from all orders shipped later than the required date.

• Use SELECT for selecting the required columns and use WHERE to find the required comparison.

Create a report that shows the City, CompanyName, ContactName of customers from cities starting with A or B.

• Use SELECT for selecting the required columns and use WHERE and LIKE to find the required pattern.

Create a report showing all the even numbers of OrderID from the orders table.

• Use SELECT for selecting the required columns and use WHERE and mod() operator to find the required values.

Day 17- Queries #15- #21

Create a report that shows all the orders where the freight cost more than $500.

• Use SELECT for selecting the required columns and use WHERE to find the required comparison.

Create a report that shows the ProductName, UnitsInStock, UnitsOnOrder, ReorderLevel of all products that are up for reorder.

• Use SELECT for selecting the required columns and use WHERE to find the required comparison i.e. reorderlevel = 0.

Create a report that shows the CompanyName, ContactName number of all customers that have no fax number.

• Use SELECT for selecting the required columns and use WHERE and IS NOT NULL to find the required comparison.

Create a report that shows the FirstName, LastName of all employees that do not report to anybody.

• Use SELECT for selecting the required columns and use WHERE and IS NULL to find the required comparison.

Create a report showing all the odd numbers of OrderID from the orders table.

• Use SELECT for selecting the required columns and use WHERE and mod() operator to find the required values.

Create a report that shows the CompanyName, ContactName, Fax of all customers that do not have Fax number and sorted by ContactName.

• Use SELECT for selecting the required columns and use WHERE and IS NOT NULL to find the required comparison. Use ORDER BY to get the ordered list.

Create a report that shows the City, CompanyName, ContactName of customers from cities that have letter L in the name sorted by ContactName.

• Use SELECT for selecting the required columns and use WHERE and LIKE to find the required list. Use ORDER BY to get the ordered list.

Day 18- Queries #22- #28

Create a report that shows the FirstName, LastName, BirthDate of employees born in the 1950s.

• Use SELECT for selecting the required columns and use WHERE, BETWEEN and AND to find the required list.

Create a report that shows the FirstName, LastName, the year of Birthdate as birth year from the employees table.

• Use SELECT for selecting the required columns and use EXTRACT() to get the year from Birthdate.

Create a report showing OrderID, total number of Order ID as NumberofOrders from the orderdetails table grouped by OrderID and sorted by NumberofOrders in descending order. HINT: you will need to use a Groupby statement.

• Use SELECT for selecting the required columns and use GROUP BY and ORDER BY to get the list in appropriate sequence.

Create a report that shows the SupplierID, ProductName, CompanyName from all product Supplied by Exotic Liquids, Specialty Biscuits, Ltd., Escargots Nouveaux sorted by the supplier ID

• Use SELECT for selecting the required columns and use JOIN for two tables on appropriate key columns. Use WHERE and IN to find the required columns.

Create a report that shows the ShipPostalCode, OrderID, OrderDate, RequiredDate, ShippedDate, ShipAddress of all orders with ShipPostalCode beginning with “98124”.

• Use SELECT for selecting the required columns and use WHERE to find the required comparison.

Create a report that shows the ContactName, ContactTitle, CompanyName of customers that have no “Sales” in their ContactTitle.

• Use SELECT for selecting the required columns and use WHERE and NOT LIKE to find the subsequence.

Create a report that shows the LastName, FirstName, City of employees in cities other than “Seattle”.

• Use SELECT for selecting the required columns and use WHERE and <> to find the required city.

Day 19- Queries #29- #35

Create a report that shows the CompanyName, ContactTitle, City, Country of all customers in any city in Mexico or other cities in Spain other than Madrid.

• Use SELECT for selecting the required columns and use WHERE and <> to find the required city and country.

Create a select statement that outputs the following:

• Use SELECT for selecting the required columns and use CONCAT to form the structure and AS to name the column.

Create a report that shows the ContactName of all customers that do not have letter A as the second alphabet in their Contact Name.

• Use SELECT for selecting the required columns and use WHERE and NOT LIKE to find the subsequence “_A%”.

Create a report that shows the average UnitPrice rounded to the next whole number, total price of UnitsInStock and maximum number of orders from the products table. All saved as AveragePrice, TotalStock and MaxOrder respectively.

• Use SELECT for selecting the required columns and use ROUND() to round off the unitprice and AVG() to find average and MAX() to find the maximum of the column.

Create a report that shows the SupplierID, CompanyName, CategoryName, ProductName and UnitPrice from the products, suppliers and categories table.

• Use SELECT for selecting the required columns and use JOIN for three tables on appropriate key columns by joining the first and second table and joining the second and third table with appropriate keys respectively.

Create a report that shows the CustomerID, sum of Freight, from the orders table with sum of freight greater $200, grouped by CustomerID. HINT: you will need to use a Groupby and a Having statement.

• Use SELECT for selecting the required columns and use HAVING and SUM() to find the required comparison.

Create a report that shows the OrderID ContactName, UnitPrice, Quantity, Discount from the order details, orders and customers table with discount given on every purchase.

• Use SELECT for selecting the required columns and use JOIN for three tables on appropriate key columns by joining the first and second table and joining the second and third table with appropriate keys respectively. Use WHERE to satisfy the condition in the joint table.

Day 20- Queries #36- #42

Create a report that shows the EmployeeID, the LastName and FirstName as employee, and the LastName and FirstName of who they report to as manager from the employees table sorted by EmployeeID.

• Use SELECT for selecting the required columns and use CONCAT to form the structure and AS to name the column. Use LEFT JOIN with same table on employeeid column and reportsto column and use ORDER BY to sort the table according to required sequence.

Create a report that shows the average, minimum and maximum UnitPrice of all products as AveragePrice, MinimumPrice and MaximumPrice respectively.

• Use SELECT for selecting the required columns, AVG() to find the average, MAX() to find the maximum and MIN() to find the minimum of the columns.

Create a view named CustomerInfo that shows the CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Country, Phone, OrderDate, RequiredDate, ShippedDate from the customers and orders table.

• Use CREATE VIEW to create a view for a table/query. Use SELECT for selecting the required columns and use JOIN for two tables on appropriate key columns.

Change the name of the view you created from customerinfo to customer details.

• Use RENAME TABLE tablename to change the name of the table.

Create a view named ProductDetails that shows the ProductID, CompanyName, ProductName, CategoryName, Description, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued from the supplier, products and categories tables.

• Use CREATE VIEW to create a view for a table/query. Use SELECT for selecting the required columns and use JOIN for three tables on appropriate key columns by joining the first and second table and joining the second and third table with appropriate keys respectively.

Drop the customer details view.

• Use DROP VIEW viewname to drop the view created.

Create a report that fetch the first 5 character of categoryName from the category tables and renamed as ShortInfo

• Use SELECT for selecting the required columns and use SUBSTRING() to form the required substring structure.

Day 21- Queries #43- #50

Create a copy of the shipper table as shippers_duplicate. Then insert a copy of shippers data into the new table

• Use CREATE TABLE, use the LIKE Statement and INSERT INTO statement.

Create a select statement that outputs the following from the shippers_duplicate Table:

• Use ALTER TABLE to modify the contents of the table. Use ADD Column to add a new column and use UPDATE, SET to change the values of the column.

Create a report that shows the CompanyName and ProductName from all products in the Seafood category.

• Use SELECT for selecting the required columns and use JOIN for three tables on appropriate key columns by joining the first and second table and joining the second and third table with appropriate keys respectively. Use WHERE to satisfy the condition in the joint table.

Create a report that shows the CategoryID, CompanyName and ProductName from all products in the categoryID 5.

• Use SELECT for selecting the required columns and use JOIN for three tables on appropriate key columns by joining the first and second table and joining the second and third table with appropriate keys respectively. Use WHERE to satisfy the condition in the joint table.

Delete the shippers_duplicate table.

• Use DROP TABLE tablename to delete the table.

Create a select statement that outputs the following from the employees table.

• Use SELECT for selecting the required columns, DATE_FORMAT(FROM_DAYS(DATEDIFF(CURRENT_DATE, BirthDate)) to count the age.

Create a report that the CompanyName and total number of orders by customer renamed as number of orders since December 31, 1994. Show number of Orders greater than 10.

• Use SELECT for selecting the required columns and use JOIN for two tables on appropriate key columns. Use WHERE and GROUP BY to find the required columns and group the same. Use HAVING and COUNT() to satisfy the second condition.

Create a select statement that outputs the following from the product table

• Use SELECT for selecting the required columns and use CONCAT to form the structure and AS to name the column. Use ROUND() to round off the unitprice.