Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Revature pre-training > Week 4 - More Java & SQL Intro - Non Access Modifiers and other keywords, variable scope, SQL intro > Flashcards

Week 4 - More Java & SQL Intro - Non Access Modifiers and other keywords, variable scope, SQL intro Flashcards

1
Q

What is data?

A

Data refers to any piece of information, facts, or statistics that can be recorded and analyzed. In the context of computing, data typically refers to digital information that can be processed by computers and other electronic devices.

Data is information with a purpose. In enterprise applications, data provides aggregated state information for the application. Businesses will use this data for various reasons including, marketing, usage statistics, error reporting, and more. Data is specifically designed to provide insights and persistence information for the applications which they support.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is a database?

A

A database is a system of software and capabilities that make validating, storing, searching, filtering, aggregating, grouping, and administering data possible. In enterprise applications databases fall into 2 main categories SQL and NoSQL.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

SQL databases

A

SQL databases are a type of RDBMS that use the standard Structured Query Language to administer the data. Data in a SQL database are started in objects called tables. Tables provide the relational information for the data stored in the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

NoSQL databases

A

NoSQL (Not Only SQL) databases are not necessarily a type of RDBMS. NoSQL databases typically use some other means or DSL for administering data and use different structures for storing data and relational information.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Which of the following program copies the databases from one server to another?
mysqlmoveitdb
mysqldbmoveit
mysqlcopydb
mysqldbcopy

A

mysqldbcopy

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

To use mysqldbcopy which privileges are needed at the source server?

SELECT
CREATE
INSERT
UPDATE

A

SELECT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

The program that performs logical backups is _____?

A

mysqldump

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What Does Query Mean?

A

A query is a request for data or information from a database table or combination of tables. This data may be generated as results returned by Structured Query Language (SQL) or as pictorials, graphs or complex results, e.g., trend analyses from data-mining tools.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Describe an RDBMS

A

Relational Database Management System or RDBMS is a data storage system based on a relational model where data that is related to a particular object is stored in tables with each entry being represented as a row and each data point is a column in the row that is validated by a set of constraints. The most common type of RDBMS is based on a standard called SQL, however, there are many different implementations (vendors) of the standard each providing a unique set of benefits and features.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

RDBMS is a a DBMS that revolves around a relational model

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

An RDBMS manages data by

A

storing them in tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

A single relational database can contain many tables along with many other types of objects. T/F?

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Structured Query Language or SQL is…

A

the standard language for working with RDBM systems. SQL is used to administer and manipulate SQL servers. SQL is a scripting language that is interpreted by the database server.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

SQL is used to…

A

Define database structure
Manipulate stored data
Define data access permissions
Control concurrent data access
Query stored data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

DDL

A

Data Definition Language. Defines data structure

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

DML

A

Data Manipulation Language. Insert, Update, Delete record

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

DCL

A

Data Control Language. Grant or revoke access permissions to database object

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

TCL

A

Transaction Control Language. Defines concurrent operation boundaries

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

DQL

A

Data Query Language. Search, filter, group, and aggregate stored data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

To accommodate the operations of the above categories, SQL is broken into 5 sublanguages

A

Each sublanguage is responsible for a specific set of operations on the database
DDL
DML
DCL Data
TCL
DQL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is the main purpose of SQL?

A

Administering RDBMS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

In a database, each table is defined with a set of

A

columns

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Each column must have a data type which restricts…

A

the type of data that can be assigned to it

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

SQL provides a standard list of data types which can be categorized into 3 main categories, name them.

A

Character types
Numeric types
Temporal types

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

SQL 3 main categories, each with its own subcategories :
Character types(2)
Numeric types(3)
Temporal types(3)

A

Character types:
-Fixed-length
-Variable-length
-Numeric types
Decimal:
-Integer
-Floating point
Temporal types:
-Date
-Time
-Timestamp

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

The purpose of SQL data types is to constraint a column’s expected value. T/F?

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

SQL is used to…

A

Define database structure
Manipulate stored data
Define data access permissions
Control concurrent data access
Query stored data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

6 DDL commands are:

A

CREATE
ALTER
DROP
TRUNCATE
RENAME
COMMENT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

3 DML commands are:

A

INSERT
UPDATE
DELETE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

3 DQL commands are:

A

SELECT -DCL
GRANT
REVOKE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Data Definition Language is the SQL language subset used for…

A

defining data or altering structure in the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

CREATE can be used to create:

A

objects on the server
Database
User
Table
Index
Trigger
Function
Stored Procedure
View

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

The DROP command is used to remove

A

objects from the server

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

Any object created using the CREATE command can be dropped using…

A

the DROP command

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

The ALTER command is used to…

A

change some characteristics of an object. The command will ultimately be used to add, drop, or modify some options on the object.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

The RENAME command is used

A

to rename objects

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

The TRUNCATE command is used to remove

A

all data from a table along with all space allocated for the records.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

Difference between truncate and drop command

A

Unlike DROP truncate will preserve the structure of the table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

The DDL sublanguage is used to ______ in a database.

A

define data structure

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

Choose the DDL Commands
Insert, Update, Delete
Grant, Revoke
Commit, Rollback, Savepoint
Create, Drop, Alter, Truncate, Comment, Rename
None of these

A

Create, Drop, Alter, Truncate, Comment, Rename

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

The Drop command maintains the structure of the database while removing all data from a table. T/F?

A

false

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

The Truncate command removes a table from the database schema. T/F?

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

Syntax to create a table:

A

CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
…..
columnN datatype,
PRIMARY KEY( one or more columns )
);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

Define primary key:

A

The PRIMARY KEY constraint is used to uniquely identify each record in a table.
Primary keys must contain values that are UNIQUE and NOT NULL.
A table can have only a single primary key.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

Describe the purpose of schema in a database:

A

A database schema defines the form of database and the data within it. An RDBMS schema can include objects like tables, triggers, functions, procedures, indexes, and views. In this section we focused on tables. In and RDBMS table the schema defines the columns, their data types, and constraints.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

Schema defines a set of.
integrity constraints
templates
normalization rules
none of these

A

integrity constraints

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

In MySQL, SCHEMA is synonymous with

A

DATABASE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

Unlike the other sublanguages, DQL is only associated with a single command

A

SELECT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

The DQL sublanguage is used to ______ in a database.

A

query for data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

Which clause is associated with restricting record count?
From table_ref
Where where_condition
Limit count Offset count
None of these

A

Limit count Offset count

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

Which clause is associated with filtering records before grouping?
Having having_condition
Where where_condition
Limit limit_condition
None of these

A

Where where_condition

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

. Given a data set data = [-8, 1, 0, -2, -3, 7, 5, 4, -6, 6, -9, 10, -4, -10, 3] With a result set [1,2,3,4,5] Choose the select statement that best represents the query.

A

Select * from data where x>0 and x<6;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

Given a data set data = [-8, 1, 0, -2, -3, 7, 5, 4, -6, 6, -9, 10, -4, -10, 3] With a result set [-10, -9, -8, -6, -3, -2] Choose the select statement that best represents the query.

A

Select * from data where x between 0 and -10 order by x;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

A _____ in a table represents a relationship among a set of values.

A

Row

55
Q

The term ‘TUPLE’ is also referred to as _____.

A

Row

56
Q

The term _____ refers to a column in table.

A

Attribute

57
Q

A relational database consists of a collection of ____?

A

tables

58
Q

Syntax: Add values to the newly created table.

A

INSERT INTO movies VALUE(“Mr.Bean”, “comedy”, “Mr.ABC”, 2019);

59
Q

Syntax: Use DROP command to completely remove the table.

A

DROP TABLE table_name;

60
Q

Syntax: Use TRUNCATE command is used to delete the data inside a table, but not the table itself.

A

TRUNCATE TABLE table_name;

61
Q

Syntax: to add a column

A

ALTER TABLE table_name
ADD column_name datatype;

62
Q

Syntax: to drop a column

A

ALTER TABLE table_name
DROP COLUMN column_name;

63
Q

Syntax: to modify a column

A

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

64
Q

Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?

A

Data Definition Language

65
Q

Which operator performs pattern matching?
MATCH operator
EXISTS operator
BETWEEN operator
LIKE operator

A

LIKE operator

66
Q

To remove duplicate rows from the result of a SELECT, we use which of the following keyword?
NO DUPLICATE
UNIQUE
DISTINCT
NONE OF THE ABOVE

A

Distinct

67
Q

Syntax to create database:

A

CREATE DATABASE WORK;

68
Q

Syntax to select/use database:

A

Syntax to select/use database:

69
Q

Syntax to create TABLE:

A

CREATE TABLE EMP
(id INT, NAME VARCHAR(25), AGE INT, PRIMARY KEY(id));

70
Q

Syntax to alter TABLE:

A

ALTER TABLE EMP
ADD SALARY DOUBLE;

71
Q

Syntax to insert values:

A

INSERT INTO EMP VALUES(111, “Ryan”, 24, 5000);

72
Q

Syntax to update record:

A

UPDATE EMP SET AGE = 23 WHERE id = 111;

73
Q

Syntax to delete record:

A

DELETE FROM EMP WHERE id = 111;

74
Q

Syntax to select record:

A

SELECT * FROM EMP;

75
Q

Syntax to truncate table:

A

TRUNCATE TABLE EMP;

76
Q

Syntax to drop table:

A

DROP TABLE EMP;

77
Q

Data Modification Language is the SQL language subset used

A

for modifying data in the database.

78
Q

DML commands:

A

INSERT
UPDATE
DELETE

79
Q

The insert command is used

A

to add records to a database table

80
Q

The update command is used

A

to modify existing data in a database table.

81
Q

The delete command is used

A

to remove data from a database table

82
Q

Syntax: The insert command is used to store new records in a database table

A

Insert into my_table (id, my_value, my_other_value) values (1, ‘some data’, 1.0);

83
Q

Syntax: Multiple records can be inserted using a single insert command.

A

insert into my_table (id, my_value, my_other_value) values (2, ‘misc data’, 2.0), (3, ‘even data’, 100.13);

84
Q

Syntax: The update command is used to modify whole records or parts of records in a database table.

A

UPDATE my_table SET my_value=’new value’;

85
Q

Syntax: To limit the number or rows that are updated, it is best practice to include the optional WHERE clause.

A

UPDATE my_table SET my_value=’where val’ WHERE id=1;

86
Q

Syntax: The delete command is used to remove data from a database table.

A

DELETE FROM <table_name> [WHERE where_condition]</table_name>

87
Q

Syntax: update command the delete command has an optional WHERE clause

A

DELETE FROM my_table WHERE id=3;

88
Q

The 5 most important aggregate functions which are used to find statistics of a set of data are given as follows:

A

count() Returns the number of rows, including rows with NULL values in a group.
sum() Returns the total summed values in a set.
average() Returns the average value of an expression.
min() Returns the minimum (lowest) value in a set.
max() Returns the maximum (highest) value in a set.

89
Q

Describe Aggregate functions

A

in MySQL provides the output as a single value after performing different operations on a set of values like the sum of all values, the average of all values, maximum and minimum value among certain group of values.

90
Q

Syntax: to count all employees from EMP table,
If you want to count a paritcular column/field.

A

SELECT COUNT(*) FROM EMP;

SELECT COUNT(EMP_NAME) FROM employee;

91
Q

Syntax: to find sum of all values from a specific field from EMP table

A

SELECT SUM(duration) AS “Total duration” FROM EMP;

92
Q

Syntax to calculate average of the values from specified column in EMP table

A

SELECT AVG(salary) FROM EMP;

93
Q

Syntax to find minimum salary from EMP table

A

SELECT MIN(salary) FROM employee;

94
Q

Syntax to find maximum salary from EMP table

A

SELECT MAX(salary) FROM employee;

95
Q

To specifiy duplicate retention, we can use the keyword in place of distinct, which is?
Whole
Duplicate
Select distinct
All of the above

A

All of the above

96
Q

If the rows are satisfying the where predicate, then they are placed into groups by the?

A

Group by clause

97
Q

The clause that apply the aggregate functions to get a single result tuple for each group, is known to be

A

Select clause

98
Q

Aggregate functions are functions that take a ___________ as input and return a single value.

A

Collection of values

99
Q

All aggregate functions except _____ ignore null values in their input collection.

A

COUNT(*)

100
Q

If we do want to eliminate duplicates, we use the keyword ______in the aggregate expression.

A

DISTINCT

101
Q

WHERE clause

A

WHERE clause is used to filter the data from the table based on user needs.

102
Q

GROUP BY clause

A

GROUP BY clause is used in MySQL queries to organize records which have same attribute values.

103
Q

ORDER BY clause

A

clause is used in MySQL queries to return the records in a sorted manner. By default, it returns in ascending order, which can also be specified as ASC. To return in descending order, We mention DESC after the expression along with ORDER BY

104
Q

The difference between ORDER BY and GROUP BY

A

ORDER BY
It ensures the presentation of columns
It is always used after the GROUP BY clause in SELECT statement
It is not mandatory to use aggregate functions in the ORDER BY
The output is sorted based on the column’s attribute values

GROUP BY
It ensures the presentation of rows
It is always used before the ORDER BY clause in SELECT statement
It is mandatory to use aggregate functions in the GROUP BY
The grouping of records is done based on the similarity among the row’s attribute values

105
Q

What is the meaning of “ORDER BY” clause in Mysql?

A

Sorting your result set using column data

106
Q

The RESULTSET is an

A

object which represents a set of data returned from a database as a result of a query input.

107
Q

Which of the following is advantage of using PreparedStatement?

A

Prevents SQL injection

108
Q

There are 3 types of methods of ResultSet Interface-

A

Get Methods
Update Methods
Navigational Method

109
Q

There are 3 types of methods of ResultSet Interface-

A

Get Methods
Update Methods
Navigational Method

110
Q

Get Methods :

A

Used to view the data in the columns/attributes of the current tuple being pointed by the cursor.

111
Q

Update Methods :

A

Used to update the data in the columns/attributes of the current tuple. The updates can then be committed to the database.

112
Q

Navigational Methods :

A

Used to move the cursor around.

113
Q

What is JDBC

A

stands for Java Database Connectivity. It is a relatively low-level API used to write Java code that interacts with relational databases via SQL.

114
Q

In order to interact with a database, we need to do several things:

A

Register the JDBC driver
Open a connection using:
-Database URL
-Username
-Password
Execute some SQL statement using either:
-Statement
-PreparedStatement
-CallableStatement
Retrieve the results that are returned in a ResultSet object

115
Q

Creating a Connection (jdbc)

A

use the DriverManager class to get a Connection to the database, given that we have the JDBC URL, username, and password. Generally these parameters should be stored in an external configuration file that can be loaded dynamically and changed without affecting the application code.

116
Q

syntax for creating a connection (jdbc)

A

try (Connection conn = DriverManager.getConnection(DB_URL,USERNAME,PASSWORD)) {
// more code goes here
} catch (SQLException e) {}

117
Q

syntax Executing SQL
–write sql statement
–use ps statements
– execute query

A

PreparedStatement ps = conn.prepareStatement();
String sql = “SELECT * FROM employees WHERE age > ? AND location = ?”;
ps.setInt(1, 40);
ps.setString(2, “New York”);
ResultSet rs = ps.executeQuery(sql);

118
Q

What does JDBC stand for?

A

Java Database Connection API

119
Q

Which of these is NOT a step for interacting with a database using JDBC?

Register a JDBC server
Open a database connection
Sign up for an account with Oracle
Execute a SQL statement

A

Sign up for an account with Oracle

120
Q

Which of these is NOT required for opening a database connection?

Database URL
Username
Programmer’s static IP address
Password

A

Programmer’s static IP address

121
Q

Which is not an SQL statement type?

A

RelayStatement

122
Q

DriverManager class

A

DriverManager class - to make a connection with a database driver

123
Q

DataSource interface

A
  • for retrieving connections, an alternative to DriverManager
124
Q

Connection interface

A
  • represents a physical connection with a database
125
Q

SQLException class -

A

a general exception thrown when something goes wrong when accessing the database

126
Q

Statement interface

A
  • used for executing static SQL statements
127
Q

PreparedStatement interface

A
  • represents pre-compiled SQL statements
128
Q

CallableStatement interface

A
  • used to execute stored procedures
129
Q

CallableStatement interface

A
  • used to execute stored procedures
130
Q

ResultSet interface

A
  • represents data returned from the database
131
Q

DAO design pattern

A

One way to address this problem is to logically separate the code that accesses the database into Data Access Objects
To use the DAO design pattern, define an interface that declares methods through which the database will be queried.

132
Q

.execute()

A

for any kind of SQL statement, returns a boolean

133
Q

.executeUpdate()

A

for DML statements, returns an int which is the number of rows affected

Revature pre-training (8 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions