SQL Essentials - Level 1

Liked the masterclass! Give it a shot for free

Sharon, as promised to Rajiv dropped a mail with a set of tasks for him to try. Check out the mail below :

Hi Rajiv,

As promised, I've put up some simple tasks for you to think about. I know you are starting off in SQL. However, I know you can definitely think over the logic given your Excel background.

I'll set up a separate meeting to discuss over these tasks and I'll guide you through the nitty gritties of SQL to get them done.

Set - 1 :

Write a query to find out unique states in US. How many states you are getting as outcome? (refer table mskl_customers).
Write a query to find out count of orders which are expected to be delivered between 1st Jan 2017 to 30th June 2017 (refer table orders).

Set - 2 :

Write a query to pull out all orders which are ordered by either Arizona or Texas and the customer segment is Consumer (refer table mskl_customers).
You got a request from your customer support team to pull out records of a customer. Unfortunately, they don't remember the customer name very clearly, It could be Darren, or Daren or Darrin or Darin. Write a query to pull out customer names which match one of the examples given here (refer table mskl_customers).

Set - 3 :

Write query to derive a new column called profitability_status from the table transactions. If profit column has +ve values, set profitability_status = 'Profit' else 'Loss' .
Write query to derive a new column called delivery_delay in orders table. Delivery_delay is the difference between order_estimated_delivery_date and order_delivered_customer_date in days.
Use the delivery_delay column and create a new column called delivery_delay_status. If the delivery_delay is +ve, the value of delivery_delay_status will be 'Late' else 'On-Time/Early'.

Set - 4 :

Use the Sales and Quantity column to calculate price of a given product sold in a transaction. Sort the result in decreasing order of price and print the Product ID and price of the costliest transaction.
Write a query to find out all orders which have not been delivered as on date.
Write a query to derive a new column discount_value in the transactions table Use the discount and sales_amt to calculate the discount_value . Save this discount_value rounded off to the nearest greater integer (Remember, discount is shown as ratio. A discount of 0.25 means 25% discount on top of sales amount).

As I mentioned, don't worry too much about how you'll write SQL statements. Apply your logic building skills and see if you can visualize doing the same in Excel. With the logic being clear, translating the same to SQL is simply the matter of knowing some simple constructs and syntax.

Looking forward to catch up again!

Sharon

Rajiv carefully thought through the problem sets given by Sharon and has called her for a catch, 2 days later. Let's spend some time going through their conversation and learn how Rajiv solved the above problems using newly introduced tool called SQL.

Rajiv : Hi Sharon, I spent last 2 days thinking through the problems you sent via mail. I have figured out a few of them while for some I'll need your guidance.

Sharon : That's perfectly ok. All I wanted you is to think through them and build a mental model of the logic which you'd write. But, as mentioned earlier, the first step of starting with SQL would be to connect to a relational database. For your information, GlobalMart uses the widely popular Microsoft SQL Server Database for storing most of it's customers, orders, products, vendors and transactions data. So, Step 1 would be to connect to the database.

Check out the below hint to learn about the steps to connect to SQL Server database.

Note : You should have an SQL client called SSMS (SQL Server Management Studio, a tool used to connect to SQL server database)

Sharon : Once done connecting to the database, click on the + sign on the left of the highlighted database.

Next, expand Tables and look for the table mskl_customers. Similarly, you can look for tables : orders and transactions when you scroll below.

These are the tables you'll need to use for the tasks.

Next, get some basics clear. Check out the hint for the same. I'd want you to move fast and get a high level idea of the required syntax.

Which of the following SQL clause will help you filter records in a table?

SELECT

FROM

CASE...WHEN

WHERE

Rajiv : Thanks Sharon. I can connect to the database, figure out the table and have also gone through the video. Let me give the problems in Set - 1 a try.

Sharon : Yes please. Here's a helpful hint for a code walkthrough with explanations to assist you in case you encounter any difficulties. However, I encourage you to give it a try on your own first. Putting in a genuine effort always yields great results!

Copy paste your SQL code for Set-1 problems below. Check the hint to understand how to copy paste your code correctly

How many unique states you are getting as outcome, for question 1 (in Set 1)?

Which of the below SQL construct will help you get count of all orders here (Note : order_id can repeat as a given order can have multiple products purchased together)

SUM(ORDER_ID)

COUNT(ORDER_ID)

COUNT(DISTINCT ORDER_ID)

DISTINCT COUNT(ORDER_ID)

In a table named "Employees", the "Department" column contains duplicate values. To retrieve a list of unique department names from the table, which SQL query would you use?

SELECT DISTINCT Department FROM Employees;

SELECT Department FROM Employees WHERE DISTINCT;

SELECT UNIQUE Department FROM Employees;

SELECT Department FROM Employees GROUP BY Department;

Consider the following scenario: You have a database table called "employees" with a column named "salary." You want to retrieve all employees whose salary is between $3000 and $5000 (inclusive). Which SQL query should you use for this condition?

SELECT * FROM employees WHERE salary >= 3000 AND salary <= 5000

SELECT * FROM employees WHERE salary > 3000 AND salary < 5000

SELECT * FROM employees WHERE salary BETWEEN 3000 AND 5000

SELECT * FROM employees WHERE salary BETWEEN 2999 AND 5001

Rajiv : That was so simple! I could realize how many clicks I could avoid via simple english statements. The coolest thing is, I don't have to remember much about the syntax. It's like I can build the logic in my head and type as I think. I can already start to see the power of SQL

Sharon : Nice to know. Let's continue the momentum and try out tasks in Set - 2. You may need to pick up few addition concepts and constructs. Check out the hint below to learn the same

Rajiv : Thanks Sharon. Let me give the problems in Set - 2 a try.

Sharon : Yes please. Feel free to refer to the following hint for a code walkthrough and explanation if you encounter any issues while working on the task, although I'd encourage you to give an honest attempt before referring the solution.

Copy paste your SQL code for Set-2 problems below

Consider the following SQL query: SELECT * FROM products WHERE (category = 'Electronics' OR category = 'Appliances') AND (price > 5000 AND price < 10000) Which statement accurately describes the results obtained from this query?

Retrieves products in any category regardless of the price range

Retrieves products with price between 5000 and 10000 regardless of the category

Retrieves products in "Electronics" or "Appliances" category with price between 5000 and 10000

Retrieves products in both "Electronics" and "Appliances" categories with price between 5000 and 10000

In SQL, which wildcard character is used to represent any sequence of characters?

In a database table containing employee records, you want to retrieve all employees whose email addresses end with either "@companyA.com" or "@companyB.org". Which SQL query should you use for this pattern matching?

SELECT * FROM employees WHERE email LIKE '%@companyA.com' AND email LIKE '%@companyB.org'

SELECT * FROM employees WHERE email LIKE '@companyA.com' OR email LIKE '@companyB.org'

SELECT * FROM employees WHERE email LIKE '@companyA.com' AND email LIKE '@companyB.org'

SELECT * FROM employees WHERE email LIKE '%@companyA.com' OR email LIKE '%@companyB.org'

You have got 3 customers with below names (first name) : 1. Michelle 2. Michellene 3. Michael You don't know the exact spelling of the customer and would like to use an SQL construct to achieve the same. Mark all the correct clause(s) below which will help you find the name Michellene

Select * from customers where first_name like '%ne'

Select * from customers where first_name like 'Mic%ne'

Select * from customers where first_name like 'M__ne'

Select * from customers where first_name like '%ch__'

Rajiv : SQL is really cool. I can see how we can stitch along the constructs to build more and more logic. This would need so much to memorize in Excel about the Menu options and clicks etc.

Sharon : Cool. I think you now have enough concepts and constructs available with you to try out problems in Sets 3 & 4. Check out the below hint for a code walkthrough with explanation to help you work along in case you face issues.

Copy paste your SQL code for Set-3 problems below

You wish to create a new column called Profitability which will show the value Profit if the profit amount is +ve else will show Loss. Which SQL construct will help you derive this logic?

CAST

WHERE

CASE...WHEN

COUNT

Consider a table named "employees" with columns "employee_id", "first_name", "last_name", and "salary". You want to create a new column called "salary_status" based on the salary of each employee. Which SQL query should you use for this condition?

What is the purpose of the CAST function in SQL?

To convert a string value to a numeric value

To convert a date value to a string value

To convert a numeric value to a string value

To convert a string value to a date value

Mark the correct statements from the following:

The ROUND() function rounds a number to a specified number of decimal places.

The CEILING() function returns the largest integer value that is smaller than or equal to a number.

The FLOOR() function returns the smallest integer value that is larger than or equal to a number.

Copy paste your SQL code for Set-4 problems below

Consider a table named "employees" with columns "employee_id" and "salary". Which of the following queries will retrieve the employee IDs of those employees who have a NULL value for their salary?

SELECT employee_id FROM employees WHERE salary IS NULL;

SELECT employee_id FROM employees WHERE salary = NULL;

SELECT employee_id FROM employees WHERE salary IS NOT NULL;

SELECT employee_id FROM employees WHERE salary <> NULL;

What is the purpose of the IS NULL and IS NOT NULL operators in SQL?

Rajiv : Sharon, I cannot thank you enough for introducing me to SQL. I suddenly feel like Superman who can fly instead of just walk!

Sharon : I can relate to this feeling. I felt the same when my mentor introduced me to SQL for the first time. It is super power indeed. Go and use it to impress your bosses :)

Rajiv : I'll surely do. Will surely apply my skills into our business problems and let you know my experience

Sharon : Will look forward to hear from you. Happy Learning!

💡 Hint

💡 Hint

💡 Hint

💡 Hint

💡 Hint

💡 Hint

💡 Hint

💡 Hint