Skip to content

Tyler spaulding #1

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
NightlyD3V wants to merge 2 commits into
base: master
Choose a base branch
from
Open

Tyler spaulding #1

Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
c0d8418
Working on join problem
NightlyD3V May 13, 2019
69944cf
working on delete
NightlyD3V May 13, 2019
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
17 changes: 17 additions & 0 deletions README.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -18,32 +18,49 @@ Answer the following data queries. Keep track of the SQL you write by pasting it

### find all customers that live in London. Returns 6 records.
> This can be done with SELECT and WHERE clauses
SELECT * FROM customers WHERE City = 'London'

### find all customers with postal code 1010. Returns 3 customers.
> This can be done with SELECT and WHERE clauses
SELECT * FROM customers WHERE PostalCode = 1010

### find the phone number for the supplier with the id 11. Should be (010) 9984510.
> This can be done with SELECT and WHERE clauses
SELECT Phone FROM suppliers WHERE SupplierID = 11


### list orders descending by the order date. The order with date 1997-02-12 should be at the top.
> This can be done with SELECT, WHERE, and ORDER BY clauses
SELECT * FROM orders ORDER BY OrderDate DESC

### find all suppliers who have names longer than 20 characters. You can use `length(SupplierName)` to get the length of the name. Returns 11 records.
> This can be done with SELECT and WHERE clauses
SELECT * FROM suppliers WHERE length(SupplierName) > 20


### find all customers that include the word "market" in the name. Should return 4 records.
> This can be done with SELECT and a WHERE clause using the LIKE keyword

> Don't forget the wildcard '%' symbols at the beginning and end of your substring to denote it can appear anywhere in the string in question
SELECT * FROM customers WHERE CustomerName LIKE '%market%

### add a customer record for _"The Shire"_, the contact name is _"Bilbo Baggins"_ the address is _"1 Hobbit-Hole"_ in _"Bag End"_, postal code _"111"_ and the country is _"Middle Earth"_.
> This can be done with the INSERT INTO clause
INSERT INTO customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('The Shire', 'Bilbo Baggins', '1 Hobbit-Hole', 'Bag End', '111', 'Middle Earth')

### update _Bilbo Baggins_ record so that the postal code changes to _"11122"_.
> This can be done with UPDATE and WHERE clauses
UPDATE customers
SET PostalCode = 11122
WHERE CustomerName = 'The Shire'

### list orders grouped by customer showing the number of orders per customer. _Rattlesnake Canyon Grocery_ should have 7 orders.
> This can be done with SELECT, COUNT, JOIN and GROUP BY clauses. Your count should focus on a field in the Orders table, not the Customer table
SELECT Orders.OrderID, Customers.CustomerID, Customers.CustomerName

@jheinz1999 jheinz1999 May 14, 2019

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

you are going to want to use the COUNT function here to list the number of orders per customer

@jheinz1999 jheinz1999 May 14, 2019

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

You will also need to use GROUP BY to get this to work

FROM Orders
JOIN Customers ON Orders.CustomerID=Customers.CustomerID
WHERE CustomerName = 'Rattlesnake Canyon Grocery'

> There is more information about the COUNT clause on [W3 Schools](https://www.w3schools.com/sql/sql_count_avg_sum.asp)

Expand Down