SQL Homework Assignments – Week 1

Focus

Introduction to SQL, basic SELECT statements, filtering with WHERE, ORDER BY, alias names, understanding OR vs. AND, and using parentheses to ensure correct order of operations.

Assignments

1. Basic SELECT Statements:
   • Write a query to select all columns from the Person.Person table.
   • Write a query to select only the FirstName, LastName, and BusinessEntityID from the Person.Person table.
2. ORDER BY:
   • Write a query to select all columns from the Sales.Customer table, ordered by CustomerID in descending order.
   • Write a query to select ProductID, Name, and ListPrice from the Production.Product table, ordered by ListPrice in ascending order.
3. Alias Names:
   • Write a query to select FirstName and LastName from the Person.Person table with aliases FN and LN for the columns, respectively.
   • Write a query to select Name and ListPrice from the Production.Product table with aliases ProductName and Price, respectively.
4. Filtering Using WHERE:
   • Write a query to select all products from the Production.Product table where the Color is ‘Black’.
   • Write a query to select all employees from the HumanResources.Employee table who were hired after January 1, 2005.
5. Common Types of Filters:
   • Write a query to select all products from the Production.Product table where the ListPrice is greater than 1000.
   • Write a query to select all employees from the HumanResources.Employee table where the JobTitle is ‘Production Technician – WC60’.
6. OR vs. AND in WHERE Statement:
   • Write a query to select all products from the Production.Product table where the Color is ‘Black’ OR the ListPrice is greater than 1000.
   • Write a query to select all products from the Production.Product table where the Color is ‘Black’ AND the ListPrice is greater than 1000.
7. Using AND and OR Together with Parentheses:
   • Write a query to select all products from the Production.Product table where the Color is ‘Black’ AND the ListPrice is greater than 1000 OR the ProductLine is ‘T’ (indicating Touring products). Ensure proper use of parentheses to correctly group the conditions.

Answers

1. Basic SELECT Statements:

   SELECT
       *
   FROM
       AdventureWorks2019.Person.Person;
   

   SELECT
       FirstName,
       LastName,
       BusinessEntityID
   FROM
       AdventureWorks2019.Person.Person;
   

2. ORDER BY:

   SELECT
       *
   FROM
       AdventureWorks2019.Sales.Customer
   ORDER BY
       CustomerID DESC;
   

   SELECT
       ProductID,
       Name,
       ListPrice
   FROM
       AdventureWorks2019.Production.Product
   ORDER BY
       ListPrice ASC;
   

3. Alias Names:

   SELECT
       FirstName AS FN,
       LastName AS LN
   FROM
       AdventureWorks2019.Person.Person;
   

   SELECT
       Name AS ProductName,
       ListPrice AS Price
   FROM
       AdventureWorks2019.Production.Product;
   

4. Filtering Using WHERE:

   SELECT
       *
   FROM
       AdventureWorks2019.Production.Product
   WHERE
       Color = 'Black';
   

   SELECT
       *
   FROM
       AdventureWorks2019.HumanResources.Employee
   WHERE
       HireDate > '2005-01-01';
   

5. Common Types of Filters:

   SELECT
       *
   FROM
       AdventureWorks2019.Production.Product
   WHERE
       ListPrice > 1000;
   

   SELECT
       *
   FROM
       AdventureWorks2019.HumanResources.Employee
   WHERE
       JobTitle = 'Production Technician - WC60';
   

6. OR vs. AND in WHERE Statement:

   SELECT
       *
   FROM
       AdventureWorks2019.Production.Product
   WHERE
       Color = 'Black'
       OR ListPrice > 1000;
   

   SELECT
       *
   FROM
       AdventureWorks2019.Production.Product
   WHERE
       Color = 'Black'
       AND ListPrice > 1000;
   

7. Using AND and OR Together with Parentheses:

   SELECT
       *
   FROM
       AdventureWorks2019.Production.Product
   WHERE
       (Color = 'Black' AND ListPrice > 1000)
       OR ProductLine = 'T';