final exam Code

Question 1

Change the order

Answer 1

ORDER BY <field> DESC (default is ascending)</field>

Question 2

counting

Answer 2

SELECT pub_id, COUNT( * )
FROM titles
GROUP BY pub_id
HAVING COUNT( * ) > 5;

Question 3

Insert Statement

Answer 3

INSERT INTO <table>
VALUES (v1, v2, …);

INSERT INTO <table>(field1, field2,…)
VALUES (v1, v2, …);

Question 4

Update Statement

Answer 4

UPDATE <table name>
SET <field> = <value>
WHERE <logical></logical></value></field>

Question 5

Delete Statement

Answer 5

DELETE FROM <table name>
WHERE <logical></logical>

Question 6

Inner join

Answer 6

From <table> AS <letter> INNER JOIN <table2> as <letter2> ON <use></use></letter2></table2></letter>

SELECT Name, Language
FROM Country AS c INNER JOIN CountryLanguage AS cl ON c.Code = cl.CountryCode

Question 7

pom file

Answer 7

<dependencies>
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.39.4.0</version>
</dependency>
</dependencies>

Question 8

JDBC imports and package

Answer 8

package edu.psu.bd.cs.akv5335.<name>;
import java.sql.*;
import java.util.Scanner;</name>

Question 9

creating a connection code

Answer 9

1. create a connection
   Connection connection;
   Scanner scanner = new Scanner(System.in);
2. try statement (driver manager, prepared statement, result set, execute query, close connections)
   try
   {
   connection = DriverManager.getConnection(“jdbc:sqlite:\C…”);

PreparedStatement statement = connection.prepareStatement(“write query, use ? for the thing we don’t have”);

ResultSet set;

do
{
get what you need from user
statement.set<user>(1,varName)
set = statement.executeQuery();</user>

//to print out a certain part of the result, do set.getString(“what you want”);

//after do-while
set.close();
connection.close();

3. catch statement
   catch(SQLException e)
   {
   e.printStackTrace();
   }

Question 10

web application code

Answer 10

@RestController
public class className
{
@GetMapping(path =”/classNameObject”)
public className methodName(@RequestParam(name = “word”) String word)