2018-12-10

Dec 10 Practice Final Solutions .

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

-- Dec 10 Practice Final Solutions

CREATE INDEX boo USING HASH ON foo(A, B);

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

David Bui, Yuta Sugiura, Emerson Ye, Hongbin Zheng

(Edited: 2018-12-10)

9. CREATE INDEX boo USING HASH ON foo(A, B); 10. Connection conn = DriverManager.getConnection( jdbc:mysql://localhost/foo?" + "user=root&password=root"); - David Bui, Yuta Sugiura, Emerson Ye, Hongbin Zheng

-- Dec 10 Practice Final Solutions

Student Names:

Alexander Duong

Chico Malto

Conover Wang

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>

(Edited: 2018-12-10)

Student Names: Alexander Duong Chico Malto Conover Wang <nowiki> 6. 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>

-- Dec 10 Practice Final Solutions

What is a tablespace? Give the SQL command to create a tablespace using the folder /usr/local/my-space. 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. CREATE TABLESPACE tablespace1 LOCATION ‘/usr/local/my-space’;

By: Cindy Ho and Ada La

(Edited: 2018-12-10)

-- Dec 10 Practice Final Solutions

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

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

(Edited: 2018-12-10)

3. 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); 4. 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

-- Dec 10 Practice Final Solutions

Hovsep Lalikian, Sunil Thapa, Andrew Yuan, parameswaran ranganatan

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

Hovsep Lalikian, Sunil Thapa, Andrew Yuan, parameswaran ranganatan 7. 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 ) );

-- Dec 10 Practice Final Solutions

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

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

-- Dec 10 Practice Final Solutions

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(title, length, year, genre, producerC#) MovieExec(name, address, cert#, netWorth) StarsIn(movieTitle, MovieYear, 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

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(title, length, year, genre, producerC#) MovieExec(name, address, cert#, netWorth) StarsIn(movieTitle, MovieYear, 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

Query Statistics

https://www.yioop.com/thread/9595

Total Elapsed Time for Queries: 0.0464787483215332 seconds.

SELECT LOCALE_NAME, WRITING_MODE FROM LOCALE WHERE LOCALE_TAG ='en-US'

Time: 0.00020599365234375 seconds.

SELECT COALESCE(MAX(UPDATE_TIMESTAMP), 0) AS MOST_RECENT FROM ITEM_IMPRESSION_SUMMARY WHERE USER_ID = 2 AND ITEM_TYPE = 3 AND ITEM_ID IN (SELECT GROUP_ID FROM USER_GROUP WHERE USER_ID = 2 AND STATUS = 1) AND UPDATE_PERIOD = -4

Time: 0.0005209445953369141 seconds.

DELETE FROM ITEM_IMPRESSION_SUMMARY WHERE USER_ID=? AND ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD = -4
Array ( [0] => 2 [1] => 9595 [2] => 1 )

Time: 0.0037689208984375 seconds.

INSERT INTO ITEM_IMPRESSION_SUMMARY VALUES (?, ?, ?, -4, ?, 0, -1, -1) ON CONFLICT DO NOTHING
Array ( [0] => 2 [1] => 9595 [2] => 1 [3] => 1789977381 )

Time: 0.002346992492675781 seconds.

UPDATE ITEM_IMPRESSION_SUMMARY SET NUM_VIEWS = NUM_VIEWS + 1 WHERE USER_ID=? AND ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD = -2 AND UPDATE_TIMESTAMP = 0
Array ( [0] => 2 [1] => 9595 [2] => 1 )

Time: 0.000125885009765625 seconds.

DELETE FROM ITEM_IMPRESSION_SUMMARY WHERE USER_ID=? AND ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD = -4
Array ( [0] => 2 [1] => 303 [2] => 3 )

Time: 0.002103090286254883 seconds.

INSERT INTO ITEM_IMPRESSION_SUMMARY VALUES (?, ?, ?, -4, ?, 0, -1, -1) ON CONFLICT DO NOTHING
Array ( [0] => 2 [1] => 303 [2] => 3 [3] => 1789977381 )

Time: 0.002205133438110352 seconds.

UPDATE ITEM_IMPRESSION_SUMMARY SET NUM_VIEWS = NUM_VIEWS + 1 WHERE USER_ID=? AND ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD = -2 AND UPDATE_TIMESTAMP = 0
Array ( [0] => 2 [1] => 303 [2] => 3 )

Time: 8.893013000488281E-5 seconds.

SELECT COUNT(GI.ID) AS NUM FROM GROUP_ITEM GI WHERE GI.GROUP_ID IN (SELECT GROUP_ID FROM USER_GROUP WHERE USER_ID = ? AND STATUS = 1) AND GI.TITLE NOT LIKE ? AND GI.PUBDATE > ?
Array ( [0] => 2 [1] => %2% [2] => 1789977380 )

Time: 0.0003020763397216797 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-4 )

Time: 0.0001149177551269531 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-37 )

Time: 4.601478576660156E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-202 )

Time: 4.291534423828125E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-687 )

Time: 3.385543823242188E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-1115 )

Time: 4.100799560546875E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-1138 )

Time: 5.412101745605469E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-1140 )

Time: 3.790855407714844E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-1149 )

Time: 3.886222839355469E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-1152 )

Time: 3.194808959960938E-5 seconds.

SELECT * FROM GROUP_ITEM WHERE ID=? LIMIT 1
Array ( [0] => 9595 )

Time: 8.511543273925781E-5 seconds.