Skip to content

Latest commit

 

History

History
109 lines (78 loc) · 1.81 KB

CSCI_4400_NOTES_1_31_2019.md

File metadata and controls

109 lines (78 loc) · 1.81 KB
Raw

CSCI_4400_NOTES_1_31_2019.md

Starting Access work

  • Databases
  • Sources
  • Files
  • into SQL
    • Structured Query Language
  • Tools for retrieving data

SQL

  • SELECT
  • FROM
  • WHERE

Basic Pets SQL Query

SELECT Pets.Name, Pets.[Date of Birth]
FROM Pets
WHERE (((Pets.Kind)="cat"));

There are reserved words in SQL ie SELECT, FROM, and WHERE

* means all colums

Query for all pets

SELECT *
FROM Pets;

Query that filters just name and kind of pet

SELECT name, kind
FROM Pets;

Query: Get all dogs

SELECT *
FROM Pets
WHERE kind='dog';

gives back all pet entries with dog

Query: Cats or dogs

SELECT *
FROM Pets
WHERE kind='dog' OR
      kind='cat';

Query: Find Khan

SELECT *
FROM Pets
WHERE name='khan';

Query: Pass 2 Number 1

The customer number and company name for all customers that are the owner or sales manager of their business.

SELECT CustomerID, CompanyName
FROM Customers
WHERE ContactTitle="Owner" OR ContactTitle="Sales Manager"

Query: Pass 2 Number 2

Query2 The product name, category name, and units in stock for all products that cost at least 20.00 and have a reorder level of 0.

SELECT Products.ProductName, Categories.CategoryName, Products.UnitsInStock
FROM Products, Categories
WHERE Products.UnitsInStock >= 20 AND Products.ReorderLevel=0 AND Categories.CategoryID=Products.CategoryID;

Query: Pass 2 Number 3

Query3 The names of all customers who ordered a product supplied by Leka Trading or Exotic Liquids.

SELECT Customers.CompanyName
FROM Customers, Suppliers
WHERE (((Suppliers.CompanyName)="Leka Trading" Or (Suppliers.CompanyName)="Exotic Liquids"));

*Incomplete

Aggregation Function

Give me the count of all records

SELECT count(*)
FROM ORDERS;