Denmark Denolan
Before starting, import the Northwind database.
Steps:
1) Search 'northwind mysql' in google
2) first result with github link: two files northwind.sql and northwind-data.sql
3) open those links in separate tab
4) copy first northwind.sql in SQLYOG, paste and Execute All Queries
5) if no errors, copy northwind-data.sql and paste in SQLyog and Execute all Queries
6) refresh list of databases, northwind should be displayed there
7) use this database for backup and restore :)
Use the correct SQL commands to do the following actions:
1. Display all customers of Northwind
Write your SQL command here:
SELECT*FROM customers;
How many records are displayed? 29
2. Display all records of customers (company, last_name, first_name, job_title, city,
state) who are purchasing managers from Florida.
Write your SQL command here:
SELECT company, last_name, first_name, job_title, city, state_province FROM customers
WHERE job_title='Purchasing Manager' AND state_province='FL';
How many records are displayed? 1
3. Display all customers who are from Portland.
Write your SQL command here:
SELECT*FROM customers
WHERE city='Portland';
How many records are displayed? 2
Paste your screenshot of the output here:
Denmark Denolan
4. Display all employees’ firstname, lastname, jobtitle and notes to see the remarks
stored for these employees.
Write your SQL command here:
SELECT first_name, last_name, job_title, notes FROM employees;
How many records are displayed? 9
Paste your screenshot of the output here:
Denmark Denolan
5. Update all products in the products table which has the reorder level of below 10 to
8.
Write your SQL command here:
UPDATE products
SET reorder_level=8
WHERE reorder_level<10;
How many records are affected? 3
Paste your screenshot of the output here:
Denmark Denolan
6. Count the products which belong to the Canned Fruits and Vegetables. Label the
resulting column as Total_Items.
Write your SQL command here:
SELECT COUNT(category) AS Total_Items
FROM products
WHERE category='Canned Fruit & Vegetables';
Paste your screenshot of the output here:
7. Which Item has the highest price in terms of standard cost? Display product name
and standard cost.
Write your SQL command here:
SELECT MAX(standard_cost) AS HighestCost
FROM products;
Write your output here:
Highest price (in terms of standard cost)=60.7500
Product name: Northwind Traders Marmalade
8. Which Item has the lowest price in terms of standard cost?
Write your SQL command here:
SELECT MIN(standard_cost) AS LowestCost
FROM products
Write your output here:
Highest price (in terms of standard cost)=0.5000
Product names: Northwind Traders Potato Chips and Northwind Traders Tuna Fish
Denmark Denolan
9. Convert all lastnames in the suppliers table to upper case.
Write your SQL command here:
SELECT UCASE(last_name) AS last_name
FROM suppliers;
Paste your screenshot of the output here:
10. Insert two records of shipping companies in table shippers.
Write your SQL command here:
INSERT INTO shippers (city, company, first_name, last_name, zip_postal_code,
state_province)
VALUES ('Dumaguete', 'Lay Mega Plaza', 'Denmark', 'Denolan', '6200', 'Negros Oriental') ,
('Bacong', 'Leon Kilat Mall', 'Shenine', 'Alvios', '6216', 'Negros Oriental')
(answers shown below)
Denmark Denolan
Paste your screenshot of the output here after successfully inserting the records
Denmark Denolan
