Chapter 3 Lab - Retrieving Data From A Single Table
docx
keyboard_arrow_up
School
Greenville Technical College *
*We aren’t endorsed by this school
Course
272
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
4
Uploaded by JudgeDeer3005
Chapter 3 Query Lab
Name: Emily Shackelford_________________
Date:01-27-24_____________
Exercise Instructions
1.
Type your name and the date in the spaces provided.
2.
Use the SQL Server Management Studio. 3.
Write T-SQL statements to query the tables contained in the IST272EagleCorp database and complete each of the exercises in this lab per the directions provided below.
Exercises
1. Write a SELECT statement that returns four columns from the Supplier table:
SupplierID
, State
, City
, Phone
Paste below the code
you wrote and type the number of rows returned
for exercise 1:
SELECT SupplierID
, State
, City
, Phone
FROM Supplier
Rows: 33
------------------------------------------------
2. Write a SELECT statement that returns columns from the PurchOrderLine table. The columns to return are: PurchOrderID – assign an aliase of PID
SupplierID – assign an aliase of SID
UnitQuantity- assign an aliase of Q
OrderUnitCost – assign an aliase of UC
UnitQuantity * OrderedUnitCost - assign an aliase of TotalCost Hint: Use the AS keyword to assign aliases. Paste below the code
you wrote and type the number of rows returned
for exercise 2:
SELECT PurchOrderID AS PID
, SupplierID AS SID
, UnitQuantity AS Q
, OrderedUnitCost AS UC
, UnitQuantity * OrderedUnitCost AS TotalCost
FROM PurchOrderLine
Rows: 151
------------------------------------------------
Chapter 3 Query Lab
3. Modify the solution to exercise 2 to filter for rows with a OrderedUnitCost value that's greater than or equal to 74.2 but less than
or equal to 106.
Paste below the code
you wrote and the run results
you obtained for exercise 3:
SELECT PurchOrderID AS PID
, SupplierID AS SID
, UnitQuantity AS Q
, OrderedUnitCost AS UC
, UnitQuantity * OrderedUnitCost AS TotalCost
FROM PurchOrderLine
WHERE OrderedUnitCost >= 74.2 AND OrderedUnitCost <= 106
Rows: 7
4. Write a SELECT statement that returns one column from the Customer table named Full_Name. Create this column from the CustLastName and CustFirstName columns. Format it as follows: last name, comma, first name (for example "Doe,John"). Sort the result set by last name, then by
first name.
Paste below the code
you wrote and type the number of rows returned
for exercise 4:
SELECT CustLastName + ',' + CustFirstName AS Full_Name
FROM Customer
ORDER BY CustLastName + CustFirstName
Rows: 237
------------------------------------------------
5. Modify the solution to exercise 4 to filter for Customers
whose lastname begins with the letter I, U, or Z.
Paste below the code
you wrote and the run results
you obtained for exercise 5:
SELECT CustLastName + ',' + CustFirstName AS Full_Name
FROM Customer
WHERE CustLastName like '[I, U, Z]%'
ORDER BY CustLastName + CustFirstName
Rows: 10
------------------------------------------------
Chapter 3 Query Lab
6. Modify the solution to exercise 4 to filter for Customers
whose lastname begins with the two letters Ra (for example a last name of Ray should get selected, and a last name of Rouls should not get selected")
Paste below the code
you wrote and the run results
you obtained for exercise 6:
SELECT CustLastName + ',' + CustFirstName AS Full_Name
FROM Customer
WHERE CustLastName like 'Ra%'
ORDER BY CustLastName + CustFirstName
Rows: 5
------------------------------------------------
7. Modify the solution to exercise 4 to filter for Customers
whose lastname begins with the letter M and ends with the letter O.
Paste below the code
you wrote and the run results
you obtained for exercise 7:
SELECT CustLastName + ',' + CustFirstName AS Full_Name
FROM Customer
WHERE CustLastName like 'M%O'
ORDER BY CustLastName + CustFirstName
Rows: 1
------------------------------------------------
8. Write a SELECT statement that returns four columns from the InventoryPart table: named PartDescription, ReorderLevel, StockLevel, StockOnOrder
Only return those rows where a reorder needs to be placed
(Stocklevel is less than the ReorderLevel minus the StockOnOrder)
Sort the results by PartDescription.
Paste below the code
you wrote and type the number of rows returned
for exercise 8:
SELECT PartDescription
, ReorderLevel
, StockLevel
, StockOnOrder
FROM InventoryPart
WHERE StockLevel <(
ReorderLevel - StockOnOrder
)
ORDER BY PartDescription
Rows: 13
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Chapter 3 Query Lab
------------------------------------------------
9. Write a SELECT statement that returns six columns from the Employee table named LastName, FirstName, JobTitle, City, State, ReleaseDate. Filter for Employees that have a null value for their ReleaseDate (only list Employees that have a null value for their release date).
Paste below the code
you wrote and type the number of rows returned
for exercise 9:
SELECT LastName
, FirstName
, JobTitle
, City
, State
, ReleaseDate
FROM Employee
WHERE ReleaseDate is null
Rows: 42
------------------------------------------------
10. Write a SELECT statement that returns six columns from the Employee table named LastName, FirstName, JobTitle, City, State, ReleaseDate. Filter for Employees that do not have null value for their ReleaseDate(only list Employees that do not have a null value for their release date) .
Paste below the code
you wrote and the run results
you obtained for exercise 10:
SELECT LastName
, FirstName
, JobTitle
, City
, State
, ReleaseDate
FROM Employee
WHERE ReleaseDate is not null
Rows: 4
------------------------------------------------