<jupyter_start><jupyter_text>2. Easy Sql questionsIn this section we will use some easy sql questions to get familiar with sql syntaxe Configure sql connectionMake sure your database server is up and running<jupyter_code>%load_ext sql
%config SqlMagic.autocommit=False
%config SqlMagic.autolimit=20
%config SqlMagic.displaylimit=20
%sql postgresql://pliu:northwind@127.0.0.1:5432/northwind<jupyter_output>The sql extension is already loaded. To reload it, use:
  %reload_ext sql<jupyter_text>2.1 Question 1We have a table called Shippers. Return all the fields from all the shippers HintThe standard format for a select statement that returns all columns and all rows is **Select * from TableName**<jupyter_code>%%sql

select * from shippers;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
6 rows affected.<jupyter_text>2.2 Question 2In the **categories table**, selecting all the fields using below SQL code:```sqlSelect * from categories```will return 4 columns. We only want to see two columns, category_name and description. HintInstead of * in the Select statement, specify the column names with a comma between them<jupyter_code>%%sql

select category_name, description from categories;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
8 rows affected.<jupyter_text>2.3 Question 3 Sales RepresentativesWe’d like to see just the first_name, last_name, and hire_date of all the employees with the Title of **Sales Representative**. Write an SQL statement that returns only those employees.You should see below result:```textfirst_name 	last_name 	hire_date 	titleJanet 	Leverling 	1992-04-01 	Sales RepresentativeMargaret 	Peacock 	1993-05-03 	Sales RepresentativeMichael 	Suyama 	1993-10-17 	Sales RepresentativeRobert 	King 	1994-01-02 	Sales RepresentativeAnne 	Dodsworth 	1994-11-15 	Sales RepresentativeNancy 	Davolio 	1992-05-01 	Sales Representative``` HintTo filter out only certain rows from a table, use a **Where clause**. The format for a where clause with a string filter is:```sqlWhere FieldName = 'Filter Text'```The column names are first_name, last_name, hire_date, titleThe table name is employees<jupyter_code>%%sql


select first_name, last_name, hire_date, title from employees where title='Sales Representative';<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
6 rows affected.<jupyter_text>2.4 Question 4 Sales Representatives in the United StatesNow we’d like to see the same columns as above, but only for those employees that both have the title of SalesRepresentative, and also are in the USA.You should see below result:```textfirst_name 	last_name 	hire_date 	title 	countryJanet 	Leverling 	1992-04-01 	Sales Representative 	USAMargaret 	Peacock 	1993-05-03 	Sales Representative 	USANancy 	Davolio 	1992-05-01 	Sales Representative 	USA``` HintTo apply multiple filters in a where clause, use **and** to separate the filters.The information of employee all in the table **employees**<jupyter_code>%%sql

select first_name, last_name, hire_date, title, country from employees where title='Sales Representative' and country='USA' ;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
3 rows affected.<jupyter_text>2.5 Question 5 Orders placed by specific EmployeeIDShow all the orders placed by a specific employee. The employee_id for this employee (<NAME>) is 5.You should find 42 rows in the result HintThe employee_id is an integer field, and not a string field. So, the value “5” does not need to be surrounded bysingle quotes in the where clause.The information of orders all in the table **orders**<jupyter_code>%%sql

select * from orders where employee_id=5;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
42 rows affected.<jupyter_text>2.6 Question 6 Suppliers and ContactTitlesIn the **suppliers table**, show the supplier_id, contact_name, and contact_title for those supplierswhose contact_title is not Marketing Manager.You should find 24 rows in the result HintTo learn how to do the “not”, you can search online for SQL comparison operators.The column names are supplier_id, contact_name, contact_title, contact_titleThe table name is suppliers<jupyter_code>%%sql

select supplier_id, contact_name, contact_title, contact_title from suppliers where contact_title <> 'Marketing Manager'<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
24 rows affected.<jupyter_text>2.7 Question 7 Products with “queso” in ProductNameIn the products table, we’d like to see the product_id and product_name for those products where the product_nameincludes the string “Queso”.You should find the two rows below```textproduct_id 	product_name11 	Queso Cabrales12 	Queso Manchego La Pastora``` HintHere, we’re looking for rows where product_name field has the value “Queso” somewhere in it. Use the “like” operator, with wildcards(i.e. regular expression).Check the [regex](https://dataschool.com/how-to-teach-people-sql/how-regex-works-in-sql/) example<jupyter_code>%%sql

select product_id, product_name from products where product_name like '%Queso%'<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
2 rows affected.<jupyter_text>2.8 Question 8 Orders shipping to France or BelgiumLooking at the **orders table**, there’s a column called **ship_country**. Write a query that shows the order_id,customer_id, and ship_country for the orders where the ship_country is either France or Belgium.You should find 96 rows in your result HintIn the where clause, instead of combining the filters with an “and” use the “or”.<jupyter_code>%%sql

select order_id, customer_id, ship_country from orders where ship_country='France' or ship_country='Belgium'<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
96 rows affected.<jupyter_text>2.9 Question 9 Orders shipping to any country in Latin AmericaNow, instead of just wanting to return all the orders from France of Belgium, we want to show all the orders fromany Latin American country. But we don’t have a list of Latin American countries in a table in the Northwind database. So, we’re going to just use this list of Latin American countries that happen to be in the Orders table:- Brazil - Mexico - Argentina - Venezuela You should have 173 rows in your result HintHere’s an example of the previous questions, about orders shipping to France or Belgium, done as an Instatement instead of using multiple Where clauses.```sqlselect order_id, customer_id, ship_country from orders where ship_country in ('France','Belgium')``` The list of the latin America country ('Brazil', 'Mexico', 'Argentina', 'Venezuela')<jupyter_code>%%sql

select order_id, customer_id, order_date, ship_country from orders where ship_country in ('Brazil', 'Mexico', 'Argentina', 'Venezuela')<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
173 rows affected.<jupyter_text>2.10: Question 10 Employees, in order of ageFor all the employees in the **employees table**, show the first_name, last_name, title, birth_date, and Order theresults by birth_date, so we have the oldest employees first.You should see below rows in your example```textfirst_name 	last_name 	title 	birth_dateMargaret 	Peacock 	Sales Representative 	1937-09-19Nancy 	Davolio 	Sales Representative 	1948-12-08Andrew 	Fuller 	Vice President, Sales 	1952-02-19Steven 	Buchanan 	Sales Manager 	1955-03-04Laura 	Callahan 	Inside Sales Coordinator 	1958-01-09Robert 	King 	Sales Representative 	1960-05-29Michael 	Suyama 	Sales Representative 	1963-07-02Janet 	Leverling 	Sales Representative 	1963-08-30Anne 	Dodsworth 	Sales Representative 	1966-01-27``` HintYou’ll need to use the Order by clause here for sorting the results. Note desc ordering will put the youngest employee at first.<jupyter_code>%%sql

select first_name, last_name, title, birth_date from employees order by birth_date asc;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
9 rows affected.<jupyter_text>2.11 Question 11 showing the birth date with Time fieldIn the output of the query above, showing the Employees in order of BirthDate, we don't see the time of the birth_date field. We want to convert the colmun type from date to timestampYour result rows should looks like below ```textfirst_name 	last_name 	title 	birth_dateMargaret 	Peacock 	Sales Representative 	1937-09-19 00:00:00Nancy 	Davolio 	Sales Representative 	1948-12-08 00:00:00Andrew 	Fuller 	Vice President, Sales 	1952-02-19 00:00:00``` HintUse the Convert function to convert the birth_date column (originally a date column) to a timestamp column.Note different sql server may have different syntax for the convert function. For postgresql, you can use cast<jupyter_code>%%sql

select first_name, last_name, title, cast(birth_date as timestamp) from employees order by birth_date;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
9 rows affected.<jupyter_text>2.12 Question 12 Employees full nameShow the first_name and last_name columns from the **employees table**, and then create a new column calledfull_name, showing first_name and first_name joined together in one column, with a space in-between.You should see below outputs```textfirst_name 	last_name 	full_nameAndrew 	Fuller 	<NAME> 	Leverling 	<NAME> 	Peacock 	<NAME>``` HintJoining two fields like this is called concatenation. Look online for examples of string concatenation with SQL Server.<jupyter_code>%%sql

select first_name, last_name, (first_name || ' ' || last_name) as full_name from employees;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
9 rows affected.<jupyter_text>2.13 Question 13 OrderDetails amount per line itemIn the order_details table, we have the fields unit_price and quantity. Create a new field, total_price, thatmultiplies these two together. We’ll ignore the discount field for now.In addition, show the order_id, product_id, unit_price and quantity. Order by order_id, product_id.You should see below outputs```textorder_id | product_id | unit_price | quantity |   total_price    ----------+------------+------------+----------+------------------    10248 |         11 |         14 |       12 |              168    10248 |         42 |        9.8 |       10 | 98.0000019073486    10248 |         72 |       34.8 |        5 | 173.999996185303    10249 |         14 |       18.6 |        9 | 167.400003433228    10249 |         51 |       42.4 |       40 | 1696.00006103516``` HintIn this computed column, you need to use the arithmetic operator for multiplication.<jupyter_code>%%sql

select order_id, product_id, unit_price, quantity, (unit_price*quantity) as total_price from order_details order by order_id, product_id;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
2155 rows affected.<jupyter_text>2.14 Question 14 How many customers?How many customers do we have in the Customers table? Show one value only, and don’t rely on getting therecordcount at the end of a resultset. And rename the count column to **total_customers**You should see below result```texttotal_customers -----------------              91``` HintIn order to get the total number of customers, we need to use what’s called an **aggregate function**. Look online for an aggregate function that would work for this problem.<jupyter_code>%%sql

select count(*) as total_customers from customers;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
1 rows affected.<jupyter_text>2.15 Question 15 When was the first order?Show the date of the first order ever made in the **orders table**.You should see below result:```textfirst_order_date ------------------ 1996-07-04``` HintInstead of * in the Select statement, specify the column names with a comma between themselect order_date as first_order_date from orders order by order_date asc limit 1; this will cost too much time<jupyter_code>%%sql


select min(order_date) as first_order_date from orders;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
1 rows affected.<jupyter_text>2.16 Question 16 Countries where there are customersShow a list of countries where the Northwind company has customers.Your result rows should look like:```text country   ------------- Argentina Spain Switzerland Italy Venezuela Belgium Norway```will return 4 columns. We only want to see two columns, CategoryName and Description. HintYou’ll want to use the Group By clause for this query.<jupyter_code>%%sql

select country from customers group by country;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
21 rows affected.<jupyter_text>2.17 Question 17 Contact titles for customersShow a list of all the different values in the **customers table** for contact_titles. Also include a count for each**contact_title**.This is similar in concept to the previous question “Countries where there are customers”, except we nowwant a count for each contact_title.Your result rows should look like:```textcontact_title          | total_contact_title --------------------------------+--------------------- Sales Manager                  |                  11 Owner                          |                  17 Owner/Marketing Assistant      |                   1 Accounting Manager             |                  10 Sales Associate                |                   7 Sales Agent                    |                   5``` HintThe answer for this problem builds on multiple concepts introduced in previous problem, such as grouping,aggregate functions, and aliases.<jupyter_code>%%sql
select contact_title,count(contact_title) as total_contact_title from customers group by contact_title;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
12 rows affected.<jupyter_text>2.18 Question 18 Products with associated supplier namesWe’d like to show, for each product, the associated supplier. Show the product_id, product_name, and thecompany_name of the Supplier. Sort by ProductID.This question will introduce what may be a new concept, the Join clause in SQL. The Join clause is used to jointwo or more relational database tables together in a logical way.Your result rows should look like:```textproduct_id |           product_name           |              company_name              ------------+----------------------------------+----------------------------------------          1 | Chai                             | Specialty Biscuits, Ltd.          2 | Chang                            | Exotic Liquids          3 | Aniseed Syrup                    | Exotic Liquids          4 | Chef Anton's Cajun Seasoning     | New Orleans Cajun Delights          5 | Chef Anton's Gumbo Mix           | New Orleans Cajun Delights          6 | Grandma's Boysenberry Spread     | Grandma Kelly's Homestead          7 | Uncle Bob's Organic Dried Pears  | Grandma Kelly's Homestead          8 | Northwoods Cranberry Sauce       | Grandma Kelly's Homestead``` HintJust as a reference, here’s an example of what the syntax for the Join looks like, using different tables from theNorthwind database. It will show all the products, with the associated category_name.```sqlSelect product_id ,product_name ,category_nameFrom products pJoin categories con p.category_id = c.category_id```<jupyter_code>%%sql

select p.product_id, p.product_name, s.company_name 
from products p 
inner join suppliers s 
on p.supplier_id=s.supplier_id 
order by product_id;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
77 rows affected.<jupyter_text>2.19 Question 19 Orders and the Shipper that was usedWe’d like to show a list of the **orders** that were made, including the **shipper** that was used. Show the order_id, order_date (date only), and company_name of the shippers table, and sort by order_id.In order to not show all the orders (there’s more than 800), show only those rows with an order_id of less than10300.Your result rows should look like:```text order_id | order_date |   company_name   ----------+------------+------------------    10248 | 1996-07-04 | Federal Shipping    10249 | 1996-07-05 | Speedy Express    10250 | 1996-07-08 | United Package    10251 | 1996-07-08 | Speedy Express``` HintSimilar to previous question, you need to join two tables **orders and shippers**. But this time the corresponding column of the two tables are different. One is called **ship_via**, the other is called **shipper_id**<jupyter_code>%%sql
select order_id, order_date, company_name 
from orders o
inner join shippers s
on o.ship_via = s.shipper_id
where o.order_id < 10300
order by o.order_id;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/northwind
52 rows affected.
{'fraction_non_alphanumeric': 0.09072264446857281, 'fraction_numerical': 0.043973941368078175, 'mean_word_length': 4.08558282208589, 'pattern_counts': {'":': 0, '<': 67, '<?xml version=': 0, '>': 66, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 20, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '20', 'dir': 'jupyter-structured-clean-dedup', 'id': '91939', 'license': 'Apache-2.0', 'n_tokens_mistral': 5259, 'n_tokens_neox': 4632, 'n_words': 1859, 'path': 'SQL_practice_problems/notebook/2.Easy_questions.ipynb', 'repo_name': 'pengfei99/LearningSQL', 'size': '16626'}