Test 1 Review A: Questions

R1a.1.

Explain what is meant by the term relational database model.

R1a.2.

Suppose we have two tables. The first, named customers, lists information about users.

userida unique identifier for the customer
namethe customer's name

The second, named orders, contains informations about items purchased.

useridthe user making the purchase
itemthe name of the item purchased
amountthe 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.

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)
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.

presidents table
namestatestart
ObamaIllinois2009
BushTexas2001
ClintonArkansas1993
BushTexas1989
ReaganCalifornia1981
    
elections table
yearwinnervotesrunnerup
2008Obama 365173
2004Bush 286251
2000Bush 271266
1996Clinton379159
1992Clinton370168
  1. 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.

  2. 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.

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.”

R1a.6.

Given the below two tables, what is the result of the following query?

students table
id name gradyear
791Alyssa2011
642Ben2010
850Eva2011
grades table
id course grade
791CSCI340A
642CSCI340C
642MATH420B
SELECT namecoursegrade
FROM  students LEFT JOIN grades ON students.id = grades.id
WHERE gradyear = 2011
R1a.7.

A database for tracking customer orders includes tables based on the following schemas.

invoices(invoice_idcustomer_name)
orders(invoice_iditem_idcost)
items(item_iditem_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.

R1a.8.

A database for tracking customer orders includes tables based on the following schemas.

invoices(invoice_idcustomer_name)
orders(invoice_iditem_idcost)
items(item_iditem_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.

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.

  1. A well-designed database (in BCNF) would represent this data using two tables. Write the schema for each.
  2. 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.
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)
  1. For each course whose credit value is 0.25, list the course ID and title of the course.
  2. 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.
R1a.11.

Given the below two tables, what is the result of the following query?

customers table
uidcity
alyssaConway
benConway
evaMorrilton
orders table
uidproduct
alyssalaptop
alyssatablet
evatablet
SELECT customers.uidcityproduct
FROM   customers LEFT JOIN orders ON customers.uid = orders.uid
R1a.12.

Suppose the below two relations.

sections table
courseinstructor
CSCI 135Zimmerman
CSCI 150Ferrer
CSCI 151Collins
CSCI 250Ferrer
CSCI 330Burch
CSCI 350Ferrer
CSCI 410Collins
   
faculty table
lasthire
Barel1981
Burch2004
Collins1982
Ferrer2002

a. What is the result of the below SQL query?

SELECT instructorCOUNT(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).

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.

R1a.14.

Explain what CSS is used for in Web development.

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>
<html>
  <head>
    <meta charset="utf-8">
    <title>Test dates</title>
  </head>

Tests

Tue 6 MarTest 2
Tue 10 AprTest 3
Wed 2 MayFinal
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.

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>
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?

R1a.19.

Suppose we execute the following sequence of statements.

obj = { a1"b"2 };
obj.c = 3;
obj["d"] = 4;

What properties will obj have?

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.

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>
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>
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.)

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.)

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.

R1a.26.

When should a browser send information to a server using a POST request rather than a GET request?

R1a.27.

What is the primary purpose of JSON in Web development?

Test 1 Review A: Solutions

R1a.1.

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.

R1a.2.
SELECT nameitem
FROM   customers JOIN orders ON customers.userid = orders.userid
WHERE  amount > 100
R1a.3.
SELECT name
FROM   accounts JOIN deposits ON accounts.acct_num = deposits.acct_num
WHERE  birthdate = date
R1a.4.
  1. SELECT year
    FROM elections
    WHERE votes >= 2 * runnerup
  2. SELECT state
    FROM presidents JOIN elections ON winner = name
    WHERE votes >= 350
R1a.5.
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'
R1a.6.
name course grade
AlyssaCSCI340A
Eva(null)(null)
R1a.7.
SELECT invoices.invoice_idcustomer_nameSUM(cost)
FROM invoices JOIN orders ON invoices.invoice_id = orders.invoice_id
GROUP BY invoices.invoice_id
R1a.8.
a.SELECT item_nameCOUNT(*)
FROM   items JOIN orders ON items.item_id = orders.item_id
GROUP BY items.item_id
b.SELECT item_nameCOUNT(invoice_id)
FROM   items LEFT JOIN orders ON items.item_id = orders.item_id
GROUP BY items.item_id
R1a.9.
  1. lots(address, lot_size) and sales(address, date, price)
  2. SELECT addresspricelot_size
    FROM   lots JOIN sales ON lots.address = sales.address
    WHERE  price > 1000 * lot_size
R1a.10.
a.b.
SELECT course_idtitle
FROM   courses
WHERE   credit_value = 0.25
   SELECT nameSUM(credit_value)
FROM students
  JOIN grades ON students.student_id = grades.student_id
  JOIN courses ON grades.course_id = courses.course_id
WHERE grade <> 'F'
GROUP BY students.student_id
R1a.11.
alyssaConwaylaptop
alyssaConwaytablet
benConway(null)
evaMorriltontablet
R1a.12.
a.Zimmerman1
Ferrer3
Collins2
Burch1
b.SELECT coursehire
FROM   sections LEFT JOIN faculty ON instructor = last
R1a.13.
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.

R1a.14.

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.

R1a.15.
  <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>
R1a.16.
p#second img { border-width2px }
R1a.17.
li.odd b { colorblue }
R1a.18.
li.prime b { colorred }
R1a.19.

The properties will be named a, b, c, and d. (They are indistinguishible from one another.)

R1a.20.
function harmonic(n) {
    "use strict";
    var isum;
    sum = 0.0;
    for (i = 1i <= ni += 1) {
        sum += 1.0 / i;
    }
    return sum;
}
R1a.21.
$('#cell4').text('X');
R1a.22.
var cur_count = 0;

$(document).ready(function () {
    $('#incr').click(function () {
        cur_count += 1;
        $('#cur').text(cur_count);
    });
}
R1a.23.
$(document).ready(function() {
    $('#conv').click(function () {
        var cels = $('#cels').val();
        $('#fahr').text(1.8 * cels + 32);
    });
});
R1a.24.
$(document).ready(function() {
    $('#sum').click(function () {
        var totali;

        total = 0;
        for (i = 0i < 8i++) {
            total += parseInt($('#num' + i).val());
        }
        $('#res').text(total);
    });
}
R1a.25.

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.

R1a.26.

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.

R1a.27.

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.