lab_10_worksheet
docx
keyboard_arrow_up
School
Langara College *
*We aren’t endorsed by this school
Course
1050
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
6
Uploaded by GrandGuineaPig4093
Lab 10: Chapter 12, Information Systems. Lab16_Manual.pdf, Databases, Excel.
[56 marks] In all of the problems, you must show
your work to qualify for the mark.
Type your answer in this worksheet after each question. Submit the PDF version of the completed worksheet with D2L.
Learning Objectives
At the end of this lab, you should be able to gain a deeper understanding SQL in Databases and Spreadsheet with Excel.
Lab Readings 1.
Chapter 12 – Database Management Systems
2.
Lab 16 Databases (Lab16_Manual.pdf)
Lab Questions Lab 16 Databases (Lab16_Manual.pdf)
1.
[6] Exercise 1
2.
[10] Exercise 2
3.
[6] Exercise 3
Result 1 sorted by Names and Result 2 Sorted by Population.
4.
[14] Exercise 4. For part 9) of Exercise 4, provide the results by examining the tables manually. Do not do parts 10) to 12) of Exercise 4 or the Deeper Investigation. Bird
Meadowlark
Meadowlark
Meadowlark
California Quail
Lark Bunting
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
5.
(SQL) Given the following table called 'forSale':
ID
Address
SqFt
Price
Owner
103
470 Lindquist St.
1500
786000
James Owens
155
12 Ridge Circle
2000
957900
Alice Perez
165
940 Midway Dr.
1800
834100
Robert Gallant
180
1200 Main St.
2200
999000
John Smith
a.
[3] Write a SQL statement, type the query, that returns the Address and Owner of all houses that have a price not less than $250,000 and SqFt not less than 2000. Show the results in the form of a table.
SELECT Address, Owner FROM forSale WHERE Price >= 250000 AND SqFt >= 2000
Address
Owner
12 Ridge Circle
Alice Perez
1200 Main St.
John Smith
a.
[3] 103 is sold to Susan Saran. Write a SQL statement that updates the table.
DELETE FROM forSale WHERE ID = 103 (This Deletes the entire row as the property is no longer for sale.)
But if we want to update the owner f the property,
UPDATE forSale SET Owner = Susan Saran WHERE ID = 103
b.
[3] Write a SQL statement that adds a record to the table for a property with ID 200 at 202 Clarke St., 1200 SqFt. The price is 200,000 and it belongs to Joe Smith.
INSERT INTO forSale VALUES(200, ‘202 Clarke St.’, 1200, 200000, ‘Joe Smith’)
c.
[3] Write a SQL statement, type the query, that returns the ID, Address, and Owner of all properties that belong to the Smith family. Show the results in the form of a table.
SELECT ID, Address, Owner FROM forSale WHERE Owner like ‘%Smith%’
ID
Address
Owner
180
1200 Main St.
John Smith
6.
(Spreadsheet) Given the following Excel Worksheet
a.
[2] Write the formula that is likely in cell E4 in such a way that it can be copied to cells E5:E21 and correctly compute their values too.
=SUM(B4:D4)
b.
[2] Write the formula that is likely in cell B21 in such a way that it can be copied to cells C21:F21 and correctly compute their values too.
=AVERAGE(B4:B19)
c.
[4] Write the formula that is likely in cell F4 in such a way that it can be copied to cells F5:F21 and correctly compute their values too.
=B4*10%+B4
Useful link:
https://www.w3schools.com/sql/
Submit the PDF version of the completed worksheet with D2L
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