Yioop - PHP Search Engine

Practice Final Solutions.

10. David Janda, Aaron John, Sanford Huynh

 import java.sql.*;
 import java.util.ArrayList;

class JdbcTest {

    public static void main(String[] args) {
        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost/foo?" + "user=root&password=secret");
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM goo");

            ResultSetMetaData rsmd = rs.getMetaData();
            int numberOfColumns = rsmd.getColumnCount();

            ArrayList<ArrayList<String>> results = new ArrayList<>();
            ArrayList<String> row = new ArrayList<>();

            while (rs.next()) {
                int i = 0;
                while (i < numberOfColumns) {
                    String s = rs.getString(i);
                    row.add(s);
                    i++;
                }
                results.add(row);
                row.clear();
            }

            for (int i = 0; i < results.size(); i++) {
                for (int j = 0; j < results.get(i).size(); j++) {
                    System.out.print(results.get(i).get(j) + ",");
                }
                System.out.println();
            }

        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException sqlEx) {
                }
                rs = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) {
                }
                stmt = null;
            }
        }
    }

}

(Edited: 2019-12-09)

10. David Janda, Aaron John, Sanford Huynh import java.sql.*; import java.util.ArrayList; class JdbcTest { public static void main(String[] args) { Connection conn = null; ResultSet rs = null; Statement stmt = null; try { conn = DriverManager.getConnection("jdbc:mysql://localhost/foo?" + "user=root&password=secret"); stmt = conn.createStatement(); rs = stmt.executeQuery("SELECT * FROM goo"); ResultSetMetaData rsmd = rs.getMetaData(); int numberOfColumns = rsmd.getColumnCount(); ArrayList<ArrayList<String>> results = new ArrayList<>(); ArrayList<String> row = new ArrayList<>(); while (rs.next()) { int i = 0; while (i < numberOfColumns) { String s = rs.getString(i); row.add(s); i++; } results.add(row); row.clear(); } for (int i = 0; i < results.size(); i++) { for (int j = 0; j < results.get(i).size(); j++) { System.out.print(results.get(i).get(j) + ","); } System.out.println(); } } catch (SQLException ex) { System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); System.out.println("VendorError: " + ex.getErrorCode()); } finally { if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) { } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { } stmt = null; } } } }

-- Practice Final Solutions

4. Spencer Wong, Stefan Do, Jessica Hoang

 (a) an anonymous variable slot. 
 Instead of variables, you replace them with underscores 
 Example:
 LongMovie(t,y) <- Movies(t,y,l,_,_,_) AND l ≥ 100

 (b) A safe variable. 
 A safe variable is a variable that appears anywhere in the rule, and must appear in some nonnegated, relational subgoal of the body.

 Example: 
 Ancestor(x, z) <- Father(x, y), Ancestor(y, z). where x = “bob” and z=y=”tony”

 (c) Intensional database. 
 Intensional predicates are predicates whose relations are computed by applying one or more Datalog rules which are not facts. The collection of all intesional 
 predicates in a database we call the intensional database (IDB).
 (collection of calculated rules)
 Example: 
 H(x,z)<−R(x,y),S(y,z)

 (d) Extensional Database.
 Extensional predicates are predicates whose relation is stored in a database (can be thought of a collection of fact rules. 
 The collection of all intensional predicates in a database we call the Extensional database (EDB).
 Example:
 Father("bob", "tony") <-.

4. Spencer Wong, Stefan Do, Jessica Hoang (a) an anonymous variable slot. Instead of variables, you replace them with underscores Example: LongMovie(t,y) <- Movies(t,y,l,_,_,_) AND l ≥ 100 (b) A safe variable. A safe variable is a variable that appears anywhere in the rule, and must appear in some nonnegated, relational subgoal of the body. Example: Ancestor(x, z) <- Father(x, y), Ancestor(y, z). where x = “bob” and z=y=”tony” (c) Intensional database. Intensional predicates are predicates whose relations are computed by applying one or more Datalog rules which are not facts. The collection of all intesional predicates in a database we call the intensional database (IDB). (collection of calculated rules) Example: H(x,z)<−R(x,y),S(y,z) (d) Extensional Database. Extensional predicates are predicates whose relation is stored in a database (can be thought of a collection of fact rules. The collection of all intensional predicates in a database we call the Extensional database (EDB). Example: Father("bob", "tony") <-.

-- Practice Final Solutions

1. Iris Cheung, Adam Golab, Joseph Nguyen https://i.imgur.com/hYxaeab.jpg

-- Practice Final Solutions

 Isabelle, Harshit, Arthur 
 
 Problem 7	
 
 -- a subquery in a FROM clause
 SELECT name 
 FROM MovieExec, (SELECT producerC# 
    FROM Movies, StarsIn 
    WHERE title = movieTitle AND
        year = movieYear AND
        starName = 'Harrison Ford' 
    );
 -- b natural join
 select * from (Employee NATURAL JOIN Projects);

 -- c duplicate emilination 
 SELECT DISINCT managerId FROM Project;

 -- d union as bags:
 (SELECT title, year FROM Movies)
 UNION ALL
 (SELECT MovieTitle as title, movieYear AS year FROM StarsIn);

Isabelle, Harshit, Arthur '''Problem 7''' -- a subquery in a FROM clause SELECT name FROM MovieExec, (SELECT producerC# FROM Movies, StarsIn WHERE title = movieTitle AND year = movieYear AND starName = 'Harrison Ford' ); -- b natural join select * from (Employee NATURAL JOIN Projects); -- c duplicate emilination SELECT DISINCT managerId FROM Project; -- d union as bags: (SELECT title, year FROM Movies) UNION ALL (SELECT MovieTitle as title, movieYear AS year FROM StarsIn);

-- Practice Final Solutions

8) Yun Cao, Thy Nguyen, Chuan Seng Edward Lim, Sero Nazarian

a. UPDATE Employee SET salary = salary * 1.1 WHERE lastname='Pollet';

b. DELETE FROM Employee WHERE lastname = 'Negativity';

c. CREATE TABLE Employee (

	EmployeeID VARCHAR(10) PRIMARY KEY,
	name VARCHAR(32) NOT NULL,
	supervisorID VARCHAR(10),
	FOREIGN KEY (supervisorID) REFERENCES Employee(EmployeeID)

);

(Edited: 2019-12-11)

8) Yun Cao, Thy Nguyen, Chuan Seng Edward Lim, Sero Nazarian a. UPDATE Employee SET salary = salary * 1.1 WHERE lastname='Pollet'; b. DELETE FROM Employee WHERE lastname = 'Negativity'; c. CREATE TABLE Employee ( EmployeeID VARCHAR(10) PRIMARY KEY, name VARCHAR(32) NOT NULL, supervisorID VARCHAR(10), FOREIGN KEY (supervisorID) REFERENCES Employee(EmployeeID) );

-- Practice Final Solutions

2. Sophie Chen, Aaron Francis, Yu Cao

https://drive.google.com/file/d/1zDU7obpoQxlMxrzZymAm_-dguAX8lm5h/view?usp=sharing

2. Sophie Chen, Aaron Francis, Yu Cao https://drive.google.com/file/d/1zDU7obpoQxlMxrzZymAm_-dguAX8lm5h/view?usp=sharing

-- Practice Final Solutions

9) Connie Huynh, Chanip Chong
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.

9) Connie Huynh, Chanip Chong 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.

-- Practice Final Solutions

5. Tan Nguyen, Welby Chan, Alexander Len

a/ SELECT title AS NAME FROM Movies

WHERE year >= 1995

ORDER BY year ASC

b/ SELECT name

FROM (StarIn SI1 JOIN StarIn SI2 ON SI1.name = SI2.name)

WHERE SI1.year <1950 AND SI2.year >1980)

5. Tan Nguyen, Welby Chan, Alexander Len a/ SELECT title AS NAME FROM Movies WHERE year >= 1995 ORDER BY year ASC b/ SELECT name FROM (StarIn SI1 JOIN StarIn SI2 ON SI1.name = SI2.name) WHERE SI1.year <1950 AND SI2.year >1980)

-- Practice Final Solutions

	BY TOAN TRAN, SUDHANSHU DANGI, AND PARMEET SINGH

	6) Define the following and give an example: (a) Left Outer Join,.(b) nested for Loop query semantics. (c) correlated subquery.

	A)LEFT OUTER JOIN is the dangling tuples of the left argument R are pad with (NIL) and add to the result.

	EX: 	U = A(1, 4, 7), B(2, 5, 8), C(3, 6, 9)

		V = B(2, 2, 6), C(3, 3, 7), D(10, 11, 12)

	The LEFT OUTER JOIN of U & V is A(1, 1, 4, 7), B(2, 2, 5, 8), C(3, 3, 6, 9), D(10, 11, NIL, NIL)

	B) Nested for loop query semantics is used when there are several tuple variables in which each variable range over the tuples of their respective relation. The FOR clause assign the tuple expression to the tuple variable to decide whether the WHERE clause is true, then we produce a tuple consisting of the values of the expression followed by a SELECT clause.

EX:

	LET the tuple variables in the from-clause range over relations R1,R2,...,Rn;

	FOR each tuple t1 in relation R1 DO

		...something

		FOR each tuple t2 in relation R2 DO

			FOR each tuple tn in relation Rn DO

				IF the where-clause is satisfied when the values from t1,t2,... ,tn are substituted for all attribute references THEN

					evaluate the expressions of the select-clause according to t1,t2,... ,tn andproducethe tuple of values that results.

	C) Correlated subquery is a nested subqueries requires the subquery to be evaluated many times, once for each assignment of a value to some term in the subquery that comes from a tuple variable outside the subquery.

	EX: Given this relation.

	Movies(title, year, length, genre, studioName, producerC#)

	We want to find the titles that have been used for two or more movies. We start with an outer query that looks at all tuples in the relation.

	SELECT title

	FROM Movies Old

	WHERE year < ANY

		(SELECT year

		FROM Movies

		WHERE title = Old.title);

(Edited: 2019-12-10)

BY TOAN TRAN, SUDHANSHU DANGI, AND PARMEET SINGH 6) Define the following and give an example: (a) Left Outer Join,.(b) nested for Loop query semantics. (c) correlated subquery. A)LEFT OUTER JOIN is the dangling tuples of the left argument R are pad with (NIL) and add to the result. EX: U = A(1, 4, 7), B(2, 5, 8), C(3, 6, 9) V = B(2, 2, 6), C(3, 3, 7), D(10, 11, 12) The LEFT OUTER JOIN of U & V is A(1, 1, 4, 7), B(2, 2, 5, 8), C(3, 3, 6, 9), D(10, 11, NIL, NIL) B) Nested for loop query semantics is used when there are several tuple variables in which each variable range over the tuples of their respective relation. The FOR clause assign the tuple expression to the tuple variable to decide whether the WHERE clause is true, then we produce a tuple consisting of the values of the expression followed by a SELECT clause. EX: LET the tuple variables in the from-clause range over relations R1,R2,...,Rn; FOR each tuple t1 in relation R1 DO ...something FOR each tuple t2 in relation R2 DO FOR each tuple tn in relation Rn DO IF the where-clause is satisfied when the values from t1,t2,... ,tn are substituted for all attribute references THEN evaluate the expressions of the select-clause according to t1,t2,... ,tn andproducethe tuple of values that results. C) Correlated subquery is a nested subqueries requires the subquery to be evaluated many times, once for each assignment of a value to some term in the subquery that comes from a tuple variable outside the subquery. EX: Given this relation. Movies(title, year, length, genre, studioName, producerC#) We want to find the titles that have been used for two or more movies. We start with an outer query that looks at all tuples in the relation. SELECT title FROM Movies Old WHERE year < ANY (SELECT year FROM Movies WHERE title = Old.title);

-- Practice Final Solutions

Ealrada Piroyan, Devanshi Sukhadia, Zizhen Huang

Answer to question#3

  a)  γ COUNT(title) (δ (πtitle (StarsIn)))

  b)  τStarName(δStarName,MIN(year)->minYear(StarIn))

(Edited: 2019-12-15)

Ealrada Piroyan, Devanshi Sukhadia, Zizhen Huang [[https://drive.google.com/file/d/1gW3QUTulA9natO_BwjLOz_AvdZAVPCFo/view?usp=sharing|Answer to question#3]] a) γ COUNT(title) (δ (πtitle (StarsIn))) b) τStarName(δStarName,MIN(year)->minYear(StarIn))