Yioop - PHP Search Engine

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

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

(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

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.

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

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

-- Dec 10 Practice Final Solutions

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

(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

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

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

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

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