printable version
Test 1 Review A
[1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13] [14] [15] [16] [17] [18] [19] [20] [21] [22] [23] [24] [25] [26] [27]
Problem R1a.1.
Explain what is meant by the term relational database model.
In a relational database, all information is represented by a collection of tables, which consist of rows all containing exactly the same attributes. A relationship between rows in different tables (or in the same table) is represented by incorporating information in one row to identify the other row; for example, in our database for bid points, each row in bids contains a course ID referencing a row from the courses row.
Problem R1a.2.
Suppose we have two tables. The first, named customers, lists information about users.
userid a unique identifier for the customer name the customer's name
The second, named orders, contains informations about items purchased.
userid the user making the purchase item the name of the item purchased amount the amount of the purchase
Write an SQL query that lists all purchases of more than $100; the result should include both the customer name and the name of the item purchased.
SELECT name, item
FROM customers JOIN orders ON customers.userid = orders.userid
WHERE amount > 100
Problem R1a.3.
Based on two tables as described by the following two schemas, write an SQL query that lists the names of all people who deposited money on the day they were born.
accounts(acct_num, name, birthdate)
deposits(acct_num, date, amount)
SELECT name
FROM accounts JOIN deposits ON accounts.acct_num = deposits.acct_num
WHERE birthdate = date
Problem R1a.4.
The following two tables represent the history of presidents and the electoral college results for each election. The most recent five rows are listed for each.
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||
Write an SQL query to list the years when at least twice as many electors voted for the winning candidate as for the runner-up.
Write an SQL query to list the states that have produced presidents receiving at least 350 votes in the electoral college. Don't worry about duplicates.
SELECT year
FROM elections
WHERE votes >= 2 * runnerupSELECT state
FROM presidents JOIN elections ON winner = name
WHERE votes >= 350
Problem R1a.5.
Suppose we have the following relations.
students(student_id, name)
enrollments(student_id, course_id, grade)
courses(course_id, title)
Write an SQL query that displays the names for all students enrolled in a course titled “Databases.”
SELECT name
FROM students
JOIN enrollments ON students.student_id = enrollments.student_id
JOIN courses ON enrollments.course_id = courses.course_id
WHERE courses.title = 'Databases'
Problem R1a.6.
Given the below two tables, what is the result of the following query?
students table id name gradyear 791 Alyssa 2011 642 Ben 2010 850 Eva 2011
grades table id course grade 791 CSCI340 A 642 CSCI340 C 642 MATH420 B
SELECT name, course, grade
FROM students LEFT JOIN grades ON students.id = grades.id
WHERE gradyear = 2011
| name | course | grade |
| Alyssa | CSCI340 | A |
| Eva | (null) | (null) |
Problem R1a.7.
A database for tracking customer orders includes tables based on the following schemas.
invoices(invoice_id, customer_name)
orders(invoice_id, item_id, cost)
items(item_id, item_name)
Write an SQL query that displays each invoice ID, accompanied by the customer name for that invoice and the total cost of the items to appear on the invoice.
SELECT invoices.invoice_id, customer_name, SUM(cost)
FROM invoices JOIN orders ON invoices.invoice_id = orders.invoice_id
GROUP BY invoices.invoice_id
Problem R1a.8.
A database for tracking customer orders includes tables based on the following schemas.
invoices(invoice_id, customer_name)
orders(invoice_id, item_id, cost)
items(item_id, item_name)
a. Write an SQL query that lists the name of each item that has been ordered and the number of invoices including that item. You may assume that each item is listed on an invoice only once.
b. Write an SQL query that lists each item's name and the number of invoices including that item, including items that have never been ordered.
| a. | SELECT item_name, COUNT(*) |
| b. | SELECT item_name, COUNT(invoice_id) |
Problem R1a.9.
Suppose we want a database of rural property sales including sales price, sales date, address, and lot size in acres. You should assume that a plot's size does not change over time.
- A well-designed database (in BCNF) would represent this data using two tables. Write the schema for each.
- Using your schemas, write an SQL SELECT query that lists each address, sales price, and lot size that has sold at $1,000 per acre or more; the output should be sorted by sales date.
- lots(address, lot_size) and sales(address, date, price)
SELECT address, price, lot_size
FROM lots JOIN sales ON lots.address = sales.address
WHERE price > 1000 * lot_size
Problem R1a.10.
For the following relations, write an SQL query for each task.
students(student_id, name)
grades(student_id, course_id, grade)
courses(course_id, title, credit_value)
- For each course whose credit value is 0.25, list the course ID and title of the course.
- List the name of each student with the total number of credits earned (no credit is awarded when the grade is F); you don't need to worry about students who have earned no grades.
| a. | b. | |
SELECT course_id, title | SELECT name, SUM(credit_value) |
Problem R1a.11.
Given the below two tables, what is the result of the following query?
customers table uid city alyssa Conway ben Conway eva Morrilton
orders table uid product alyssa laptop alyssa tablet eva tablet
SELECT customers.uid, city, product
FROM customers LEFT JOIN orders ON customers.uid = orders.uid
| alyssa | Conway | laptop |
| alyssa | Conway | tablet |
| ben | Conway | (null) |
| eva | Morrilton | tablet |
Problem R1a.12.
Suppose the below two relations.
|
| |||||||||||||||||||||||||||||||
a. What is the result of the below SQL query?
SELECT instructor, COUNT(course)
FROM sections
GROUP BY instructor
b. Write an SQL query that lists every course number with the year that the instructor was hired. If the hire year is not in the database, the course number should still be listed (with no hire year).
| a. | Zimmerman | 1 | |
| Ferrer | 3 | ||
| Collins | 2 | ||
| Burch | 1 | ||
| b. | SELECT course, hire | ||
Problem R1a.13.
Major Web browsers can interpret three main languages when showing a Web page to the user. Name and describe the purpose of each of them.
- HTML
Presents the content of the document, including the overall structure of the document (such as indications of section headers and paragraphs) and the words to appear within that structure.
- CSS
Determines how the page should be graphically formatted to appear in the browser window, such as the color, font, and borders for regions of the page.
- JavaScript
Modifies the default behavior of the browser, so the browser can perform computation as specified by the page in its JavaScript.
Problem R1a.14.
Explain what CSS is used for in Web development.
CSS is used to determine the way in which the content of a page should be displayed. For instance, one would use CSS to configure the color in which some text should be drawn, or to configure how large the whitespace margins around an element should be.
Problem R1a.15.
Using well-formed HTML5, complete the below file so that it will appear as at right when viewed within a browser. (Notice how the two columns are aligned.)
<!DOCTYPE html> |
Tests
|
<body>
<h1>Tests</h1>
<table><tbody>
<tr><td>Tue 6 Mar</td><td>Test 2</td></tr>
<tr><td>Tue 10 Apr</td><td>Test 3</td></tr>
<tr><td>Wed 2 May</td><td>Final</td></tr>
</tbody></table>
</body>
</html>
Problem R1a.16.
Suppose the HTML given below.
<!DOCTYPE html>
<html><head>
<meta charset="utf-8">
<title>tables</title>
</head><body>
<p id="first"><img src="a.jpg" />
<img src="b.jpg" /></p>
<p id="second"><img src="c.jpg" />
<img src="d.jpg" /></p>
</body></html>
Write a CSS file that would cause each img element within the p element whose ID is second to be displayed with a 2-pixel border. You'll want to use the CSS border-width property with a value of 2px.
p#second img { border-width: 2px }
Problem R1a.17.
For the below HTML, what CSS will lead each odd number to appear in blue?
<!DOCTYPE html>
<html><head>
<meta charset="utf-8">
<title>Numbers</title>
</head><body>
<ul>
<li class="odd"><b>1</b> - A</li>
<li class="even"><b>2</b> - B</li>
<li class="odd"><b>3</b> - C</li>
<li class="even"><b>4</b> - D</li>
<li class="odd"><b>5</b> - E</li>
</ul>
</body></html>
li.odd b { color: blue }
Problem R1a.18.
Consider the following HTML.
<ul>
<li><b>1</b> is first</li>
<li class="prime"><b>2</b> is second</li>
<li class="prime"><b>3</b> is third</li>
<li><b>4</b> is fourth</li>
<li class="prime"><b>5</b> is fifth</li>
</ul>
What CSS can be used so that only the prime numbers (2, 3, 5 — but not any words) appear in red?
li.prime b { color: red }
Problem R1a.19.
Suppose we execute the following sequence of statements.
obj = { a: 1, "b": 2 };
obj.c = 3;
obj["d"] = 4;
What properties will obj have?
The properties will be named a, b, c, and d. (They are indistinguishible from one another.)
Problem R1a.20.
Write a JavaScript function named harmonic
that given a parameter n assumed to be a positive integer
computes and returns the sum
1/1 + 1/2 + … + 1/n.
Your solution should conform to JSLint's guidelines on coding style.
function harmonic(n) {
"use strict";
var i, sum;
sum = 0.0;
for (i = 1; i <= n; i += 1) {
sum += 1.0 / i;
}
return sum;
}
Problem R1a.21.
Using the jQuery library, what JavaScript code would lead the letter X being placed into the center square of the below HTML grid?
<table><tbody>
<tr><td id="cell0"></td><td id="cell1"></td><td id="cell2"></td></tr>
<tr><td id="cell3"></td><td id="cell4"></td><td id="cell5"></td></tr>
<tr><td id="cell6"></td><td id="cell7"></td><td id="cell8"></td></tr>
</tbody></table>
$('#cell4').text('X');
Problem R1a.22.
Supposing we have the following HTML file. What should be in
the file incr.js so that
each time the Increment button is pressed, the numerical value
in the <b> element goes up by one? You can use a
global variable to track the current value in the
element.
<!DOCTYPE html>
<html><head>
<meta charset="utf-8">
<title>Counter</title>
</head><body>
<b id="cur">0</b>
<button id="incr">Increment</button>
<script src="jquery.js"></script>
<script src="incr.js"></script>
</body></html>
var cur_count = 0;
$(document).ready(function () {
$('#incr').click(function () {
cur_count += 1;
$('#cur').text(cur_count);
});
}
Problem R1a.23.
Suppose we have the below HTML.
<!DOCTYPE html>
<html><head>
<meta charset="utf-8">
<title>Temperature</title>
</head><body>
Celsius: <input id="cels" />
<button id="conv">Convert</button>
<br>Fahrenheit: <b id="fahr">32</b>
<script src="jquery.js"></script>
<script src="temp.js"></script>
</body></html>
Write the file temp.js
so that each time the Convert button is pressed, the numerical
value in the Celsius text field is displayed as its Fahrenheit
equivalent in the boldface
element whose ID is fahr. (The conversion formula is
F = 1.8 C + 32.)
$(document).ready(function() {
$('#conv').click(function () {
var cels = $('#cels').val();
$('#fahr').text(1.8 * cels + 32);
});
});
Problem R1a.24.
Suppose we have below HTML.
<!DOCTYPE html>
<html><head>
<meta charset="utf-8">
<title>Adder</title>
</head><body>
<input id="num0" />
<input id="num1" />
<input id="num2" />
<input id="num3" />
<input id="num4" />
<input id="num5" />
<input id="num6" />
<input id="num7" />
<button id="sum">Add</button><br />
Result: <b id="res"></b>
<script src="jquery.js"></script>
<script src="adder.js"></script>
</body></html>
Write the file adder.js so that each time the Add button
is pressed, the sum of the text fields' numerical values are
displayed in the boldface element; your solution should use a
loop to iterate through the fields.
(You can assume that all text fields hold a numerical value.)
$(document).ready(function() {
$('#sum').click(function () {
var total, i;
total = 0;
for (i = 0; i < 8; i++) {
total += parseInt($('#num' + i).val());
}
$('#res').text(total);
});
}
Problem R1a.25.
For when a user completes a Web page form to be sent to the server, HTTP defines two methods called GET and POST for sending the information from the completed form, called GET and POST. Explain the difference in terms of how the browser encodes the information sent to the server.
In sending form data using the GET method, all of the form data is incorporated into the URL request sent to the server, with a question mark separating the name of the file which should process the data from the actual data itself. This form data will be on the first line of the HTTP request sent to the server, followed by the auxiliary header information.
With the POST method, the browser sends the header first specifying only which file that should process the data; then it sends the form data as the body of the request, following the auxiliary header information.
Problem R1a.26.
When should a browser send information to a server using a POST request rather than a GET request?
A POST request should be used when requesting modification to the data stored at the server (such as when posting a new message into a forum). It should also be used when the information sent as part of the request has the potential to be very large (as when uploading a file). POST should also be used when some information sent is secure and thus should not be displayed in the browser's location bar.
Problem R1a.27.
What is the primary purpose of JSON in Web development?
JSON is a standard way to encode data as text, used most often by a Web server to respond back to a AJAX request from JavaScript running on a browser. The intention is that the browser JavaScript code can quickly interpret the JSON and update the browser display appropriately.