answered: 1. [12 pts] Concepts: Explain the following terms a) Databas

  

1. [12 pts] Concepts: Explain the following terms
a) Database Management System
b) Database Schema
c) Database Instance
d) Relation
e) Relational Database
f) Data manipulation
2. [6 pts] Draw data models for two entities that you selected (not from the examples
discussed in classes). For each of them, give at least three attributes. Make certain that you
show the attributes and identifiers.
3. [6 pts] Write SQL statements to create tables for each of the entities from Question 2.
4. [25 pts] For each table you created in Question 3, do the following:
a) Insert 5 rows into the table (using some data you made-up).
b) Write a query to list all data in the table and give the output.
c) Write a query to give an output that contains one column.
d) Write a query to give an output that contains two columns.
e) Write a query to give an output that contains only one row.
f) Write a query to give an output that contains two rows.
g) Write a query that contains the operator MIN.
h) Write a query that contains the operator AVG.
i) Write a query that contains the operator MAX
5. [21 pts] Create a table with the name “shr” using the example share discussed in class (see
Lecture 1, slide 20). Insert the 10 rows as shown on slide 20. Do the following queries
using SQL and show output from each of the queries:
(a) List full details for all shares with a price less than one dollar.
(b) Create a report showing firm name, share price, share holding, and total value of
shares held. (Value of shares held is price times quantity.)
(c) Report the total dividend payment of Patagonian Tea. (The total dividend
payment is dividend times quantity.)
(d) Find the yield of all firms except Bolivian Sheep and Canadian Sugar. (yield =
dividend /price*100)
e) List firm name and value in descending order of value.
(f) Find shares with a code starting with “B.”
(g) Find the maximum and minimum dividend, maximum and minimum price to
earning ratio.
6. [30 pts] The Entity Student has the attributes: snum: integer, sname: string, major:
string, level: string, age: integer
Create the table Student. Insert data listed on the file student1.csv posted on BlackBoard
(folder SQL data). Write the following queries using SQL. Give the output of each query.
(a) List all data in the Student table.
(b) Report a student’s name and his age.
(c) Find all students who is older than 21 years old.
(d) Find the name and age of students whose level is ‘SR’.
(e) Find the student who has student number 322654189.
(f) Find student’s number and name for the major ‘Computer Science’.
(g) Find the student number of students whose age is younger than 18 and at level ‘JR’.
(h) Find the student numbers of students whose age is 17 or 22.
(i) Find the student number of students whose name contains ‘A’ or ‘a’.
(j) Count the total number of students in the table.
Note: Show your code and the output by screenshot.