http://www.cs.sjsu.edu/faculty/pollett/157a.3.18f/?PracFinal.shtml?Monday,%2010-Dec-2018%2011:19:58%20PST#top

1. CREATE INDEX boo USING HASH ON foo(A, B); <br>

<br>

Connection conn = DriverManager.getConnection( jdbc:mysql://localhost/foo?" + "user=root&password=root"); <br> <br>

• David Bui, Yuta Sugiura, Emerson Ye, Hongbin Zheng

Student Names:

Alexander Duong

Chico Malto

Conover Wang

<nowiki>

1. Give the SQL syntax to create a table WorksOn(emp_id:INT, pid:INT) where emp_id has a foreign key constraint referencing the Employee ID attribute and pid has a foreign key constraint referencing Project ID. Explain the Cascade policy and how we could use it to handle DELETE modifications to this table.

CREATE TABLE WorksOn( emp_id INT, pid INT, FOREIGN KEY (emp_id) REFERENCES Employee(ID), FOREIGN KEY (pid) REFERENCES Project(ID) );

Under the cascade policy, changes to the referenced attributes are mimicked at the foreign key.

The policy can be specified in an ON DELETE clause to handle DELETE modifications to the table.

</nowiki>

What is a tablespace? Give the SQL command to create a tablespace using the folder /usr/local/my-space. <br> A tablespace defines the physical location where we are storing our databases and tables. This is useful when we want some tables on the SSD versus the spin drive. <br> CREATE TABLESPACE tablespace1 LOCATION ‘/usr/local/my-space’;

By: Cindy Ho and Ada La

1. Give SQL to do the following DML operations:

(a) Create a table R(A:int,B:int,C:int) where A, B is the primary key, C is a key, the default value for B is 10, and B must be at least 5

CREATE TABLE R ( A INT, B INT DEFAULT 10 CHECK (B >= 5), C INT UNIQUE, PRIMARY KEY (A, B) );

(b) Insert into R all distinct values (A, B, C) from S(A:int, B:int, C:int, D:int) where D > 10

INSERT INTO R (SELECT DISTINCT A, B, C FROM S WHERE S.D > 10);

1. Give SQL for the following operations on relation R(A:int,B:int,C:int):

(a) Delete all rows of R where A>5 and B<10

DELETE FROM R WHERE A > 5 AND B < 10;

(b) Update the salaries of all MovieExecs with a salary less than 10,000,000 to make it 10,000,000

UPDATE MovieExecs SET salary = 10000000 WHERE salary < 10000000;

Student names: Priscilla Ng, Monsi Magal, Serena Pascual, D. Adam Ball, Kevin Prakasa

Hovsep Lalikian, Sunil Thapa, Andrew Yuan, parameswaran ranganatan

1. Assertions are used to ensure that constraints are still upheld when inserting and updating a database.

CREATE ASSERTION ValidSalary CHECK (NOT EXISTS (SELECT Employee.name FROM Employee WHERE salary < 0 ) );

1. The view should be defined by selecting (without distinct) some attributes from one relation R (which is also allowed to be an updatable view).

The WHERE clause is not allowed to involve R in a subquery. The FROM clause can only consist of one occurrence of R and no other relation. The list in the SELECT clause must include enough attributes that for every tuple inserted into the view, we can fill the other attributes out with NULL or the proper default values.

By: Yosias Hailu, Yu Ning, Erin Yang, Sam Esmaeili

1 a). SELECT * FROM (R NATURAL JOIN S);

b). ((( SELECT * FROM R) UNION ALL (SELECT * FROM S)) INTERSECT ALL ((SELECT * FROM T));

2 a). SELECT AVG(netWorth) FROM MovieExec;

b). Movies(<u>title</u>, <u>length</u>, year, genre, producerC#) MovieExec(name, address, cert#, netWorth) StarsIn(<u>movieTitle</u>, <u>MovieYear</u>, StarName)

SELECT MovieExec name, SUM(MovieLength)
FROM Movie, MovieExec, StarsIn
WHERE producersC# = cert# AND 
title = MovieTitle AND year = MovieYear AND starName = 'Harrison Ford'
GROUP BY MovieExec.name;

By: Parnika De, Alex Frank, Dominic Dinh, Ryan Moore and Himanshu Mehta