Answer questions Screen capture each answer Place in word document 5- Create a l
ID: 3801168 • Letter: A
Question
Answer questions
Screen capture each answer
Place in word document
5- Create a list of customers who do not have a region code.
6- Create a list of customerID’s who used Speedy Express as the shipper.
7- Create a list of orders who used SupplierID of 1.
8- Create a list of products who used SupplierID of 1.
9-Create a list of ordersID’s where the unit price is greater than $10.00
10-Create a list of orderID’s where the discount is grater than 0.
SELECT *
FROM employees;
SELECT *
FROM employees
WHERE birthdate > '1958-01-01';
Employee 2 and 5 are missing
SELECT *
FROM employees
WHERE birthdate < '1958-01-01';
Employee 2 and 5 are displayed
SELECT *
FROM employees
WHERE hiredate < '1994-01-02';
Employees 1 thru 6 are displayed
SELECT *
FROM employees
WHERE hiredate > '1994-01-02';
Employees 8 and 9 are displayed
SELECT *
FROM employees
WHERE region is null;
Employees 5,6,7 and 9 are displayed
SELECT *
FROM employees
WHERE region is not null;
Employees 1 thru 4 and 8 are displayed
SELECT titleofcourtesy, firstname, lastname, title
FROM employees;
SELECT CONCAT( titleofcourtesy, " ", firstName, " ", lastname ) AS 'Employee Name'
FROM employees
SELECT CONCAT( titleofcourtesy, " ", firstName, " ", lastname ) AS 'Employee Name'
FROM employees
ORDER BY lastname;
SELECT CONCAT( titleofcourtesy, " ", firstName, " ", lastname ) AS 'Employee Name',
CONCAT(Address, " ", City, ", ", Region, " ", PostalCode) AS Address
FROM employees
ORDER BY lastname;
SQL result
Host: 127.0.0.1
Database: northwind
Generation Time: Mar 19, 2017 at 07:12 PM
Generated by: phpMyAdmin 3.5.2.2 / MySQL 5.5.27
SQL query: SELECT CONCAT( titleofcourtesy, " ", firstName, " ", lastname ) AS 'Employee Name', CONCAT(Address, " ", City, ", ", Region, " ", PostalCode) AS Address FROM employees ORDER BY lastname LIMIT 0, 30 ;
Rows: 9
Employee Name
Address
Mr. Steven Buchanan
NULL
Ms. Laura Callahan
4726 - 11th Ave. N.E. Seattle, WA 98105
Ms. Nancy Davolio
507 - 20th Ave. E. Apt. 2A Seattle, WA 98122
Ms. Anne Dodsworth
NULL
Dr. Andrew Fuller
908 W. Capital Way Tacoma, WA 98401
Mr. Robert King
NULL
Ms. Janet Leverling
722 Moss Bay Blvd. Kirkland, WA 98033
Mrs. Margaret Peacock
4110 Old Redmond Rd. Redmond, WA 98052
Mr. Michael Suyama
NULL
SELECT companyname, contactname, phone, country
FROM customers
WHERE companyname like "c%";
SELECT companyname, contactname, phone, country
FROM customers
WHERE companyname like "co%";
SELECT companyname, contactname, phone, country
FROM customers
WHERE companyname like "c%"
AND country = 'UK';
SELECT companyname, contactname, phone, country
FROM customers
WHERE companyname like "c%"
AND NOT country = 'UK';
SELECT *
FROM suppliers
WHERE region = " ";
SELECT *
FROM suppliers
WHERE region IS NULL;
SELECT *
FROM suppliers
WHERE region IS NOT NULL
AND Fax IS NULL;
SELECT *
FROM suppliers
WHERE region IS NOT NULL
AND Fax = ""
AND Country = "UK";
SELECT *
FROM suppliers
WHERE region IS NULL
ORDER BY city DESC;
SELECT *
FROM suppliers
WHERE region IS NULL
AND country = 'Germany'
ORDER BY city;
SELECT *
FROM suppliers
WHERE region IS NULL
AND country = 'Germany'
ORDER BY city DESC;
USE information_schema;
SELECT *
FROM `COLUMNS`
WHERE table_schema = 'Northwind
Explanation / Answer
5-
Select * from Customers where Region IS NULL;
6-
Select c.CustomerID from Customers inner join Orders o on c.CustomerID = o.CustomerID inner join Shippers s on o.ShipVia = s.ShipperID where s.Companyname = 'Speedy Express';
7-
Select o.* from Orders o inner join OrderDetails od on o.OrderID = od.OrderID inner join Products p on od.ProductID = p.ProductID inner join Suppliers s on p.SupplierId = s.SupplierId where s.SupplierId = 1;
8-
Select p.* from Products p inner join Suppliers s on p.SupplierId = s.SupplierId where s.SupplierId = 1;
9-
Select OrderID from OrderDetails where UnitPrice > 10;
10-
Select OrderID from OrderDetails where discount > 0;