Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

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
SQL 3 main categories, each with its own subcategories : Character types(2) Numeric types(3) Temporal types(3)
Character types: -Fixed-length -Variable-length -Numeric types Decimal: -Integer -Floating point Temporal types: -Date -Time -Timestamp
26
The purpose of SQL data types is to constraint a column's expected value. T/F?
True
27
SQL is used to...
Define database structure Manipulate stored data Define data access permissions Control concurrent data access Query stored data
28
6 DDL commands are:
CREATE ALTER DROP TRUNCATE RENAME COMMENT
29
3 DML commands are:
INSERT UPDATE DELETE
30
3 DQL commands are:
SELECT -DCL GRANT REVOKE
31
Data Definition Language is the SQL language subset used for...
defining data or altering structure in the database.
32
CREATE can be used to create:
objects on the server Database User Table Index Trigger Function Stored Procedure View
33
The DROP command is used to remove
objects from the server
34
Any object created using the CREATE command can be dropped using...
the DROP command
35
The ALTER command is used to...
change some characteristics of an object. The command will ultimately be used to add, drop, or modify some options on the object.
36
The RENAME command is used
to rename objects
37
The TRUNCATE command is used to remove
all data from a table along with all space allocated for the records.
38
Difference between truncate and drop command
Unlike DROP truncate will preserve the structure of the table.
39
The DDL sublanguage is used to ______ in a database.
define data structure
40
Choose the DDL Commands Insert, Update, Delete Grant, Revoke Commit, Rollback, Savepoint Create, Drop, Alter, Truncate, Comment, Rename None of these
Create, Drop, Alter, Truncate, Comment, Rename
41
The Drop command maintains the structure of the database while removing all data from a table. T/F?
false
42
The Truncate command removes a table from the database schema. T/F?
False
43
Syntax to create a table:
CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) );
44
Define primary key:
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.
45
Describe the purpose of schema in a database:
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.
46
Schema defines a set of. integrity constraints templates normalization rules none of these
integrity constraints
47
In MySQL, SCHEMA is synonymous with
DATABASE
48
Unlike the other sublanguages, DQL is only associated with a single command
SELECT
49
The DQL sublanguage is used to ______ in a database.
query for data
50
Which clause is associated with restricting record count? From table_ref Where where_condition Limit count Offset count None of these
Limit count Offset count
51
Which clause is associated with filtering records before grouping? Having having_condition Where where_condition Limit limit_condition None of these
Where where_condition
52
. 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.
Select * from data where x>0 and x<6;
53
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.
Select * from data where x between 0 and -10 order by x;
54
A _____ in a table represents a relationship among a set of values.
Row
55
The term 'TUPLE' is also referred to as _____.
Row
56
The term _____ refers to a column in table.
Attribute
57
A relational database consists of a collection of ____?
tables
58
Syntax: Add values to the newly created table.
INSERT INTO movies VALUE("Mr.Bean", "comedy", "Mr.ABC", 2019);
59
Syntax: Use DROP command to completely remove the table.
DROP TABLE table_name;
60
Syntax: Use TRUNCATE command is used to delete the data inside a table, but not the table itself.
TRUNCATE TABLE table_name;
61
Syntax: to add a column
ALTER TABLE table_name ADD column_name datatype;
62
Syntax: to drop a column
ALTER TABLE table_name DROP COLUMN column_name;
63
Syntax: to modify a column
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
64
Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language
65
Which operator performs pattern matching? MATCH operator EXISTS operator BETWEEN operator LIKE operator
LIKE operator
66
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
Distinct
67
Syntax to create database:
CREATE DATABASE WORK;
68
Syntax to select/use database:
Syntax to select/use database:
69
Syntax to create TABLE:
CREATE TABLE EMP (id INT, NAME VARCHAR(25), AGE INT, PRIMARY KEY(id));
70
Syntax to alter TABLE:
ALTER TABLE EMP ADD SALARY DOUBLE;
71
Syntax to insert values:
INSERT INTO EMP VALUES(111, "Ryan", 24, 5000);
72
Syntax to update record:
UPDATE EMP SET AGE = 23 WHERE id = 111;
73
Syntax to delete record:
DELETE FROM EMP WHERE id = 111;
74
Syntax to select record:
SELECT * FROM EMP;
75
Syntax to truncate table:
TRUNCATE TABLE EMP;
76
Syntax to drop table:
DROP TABLE EMP;
77
Data Modification Language is the SQL language subset used
for modifying data in the database.
78
DML commands:
INSERT UPDATE DELETE
79
The insert command is used
to add records to a database table
80
The update command is used
to modify existing data in a database table.
81
The delete command is used
to remove data from a database table
82
Syntax: The insert command is used to store new records in a database table
Insert into my_table (id, my_value, my_other_value) values (1, 'some data', 1.0);
83
Syntax: Multiple records can be inserted using a single insert command.
insert into my_table (id, my_value, my_other_value) values (2, 'misc data', 2.0), (3, 'even data', 100.13);
84
Syntax: The update command is used to modify whole records or parts of records in a database table.
UPDATE my_table SET my_value='new value';
85
Syntax: To limit the number or rows that are updated, it is best practice to include the optional WHERE clause.
UPDATE my_table SET my_value='where val' WHERE id=1;
86
Syntax: The delete command is used to remove data from a database table.
DELETE FROM [WHERE where_condition]
87
Syntax: update command the delete command has an optional WHERE clause
DELETE FROM my_table WHERE id=3;
88
The 5 most important aggregate functions which are used to find statistics of a set of data are given as follows:
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
Describe Aggregate functions
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
Syntax: to count all employees from EMP table, If you want to count a paritcular column/field.
SELECT COUNT(*) FROM EMP; SELECT COUNT(EMP_NAME) FROM employee;
91
Syntax: to find sum of all values from a specific field from EMP table
SELECT SUM(duration) AS "Total duration" FROM EMP;
92
Syntax to calculate average of the values from specified column in EMP table
SELECT AVG(salary) FROM EMP;
93
Syntax to find minimum salary from EMP table
SELECT MIN(salary) FROM employee;
94
Syntax to find maximum salary from EMP table
SELECT MAX(salary) FROM employee;
95
To specifiy duplicate retention, we can use the keyword in place of distinct, which is? Whole Duplicate Select distinct All of the above
All of the above
96
If the rows are satisfying the where predicate, then they are placed into groups by the?
Group by clause
97
The clause that apply the aggregate functions to get a single result tuple for each group, is known to be
Select clause
98
Aggregate functions are functions that take a ___________ as input and return a single value.
Collection of values
99
All aggregate functions except _____ ignore null values in their input collection.
COUNT(*)
100
If we do want to eliminate duplicates, we use the keyword ______in the aggregate expression.
DISTINCT
101
WHERE clause
WHERE clause is used to filter the data from the table based on user needs.
102
GROUP BY clause
GROUP BY clause is used in MySQL queries to organize records which have same attribute values.
103
ORDER BY clause
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
The difference between ORDER BY and GROUP BY
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
What is the meaning of “ORDER BY” clause in Mysql?
Sorting your result set using column data
106
The RESULTSET is an
object which represents a set of data returned from a database as a result of a query input.
107
Which of the following is advantage of using PreparedStatement?
Prevents SQL injection
108
There are 3 types of methods of ResultSet Interface-
Get Methods Update Methods Navigational Method
109
There are 3 types of methods of ResultSet Interface-
Get Methods Update Methods Navigational Method
110
Get Methods :
Used to view the data in the columns/attributes of the current tuple being pointed by the cursor.
111
Update Methods :
Used to update the data in the columns/attributes of the current tuple. The updates can then be committed to the database.
112
Navigational Methods :
Used to move the cursor around.
113
What is JDBC
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
In order to interact with a database, we need to do several things:
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
Creating a Connection (jdbc)
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
syntax for creating a connection (jdbc)
try (Connection conn = DriverManager.getConnection(DB_URL,USERNAME,PASSWORD)) { // more code goes here } catch (SQLException e) {}
117
syntax Executing SQL --write sql statement --use ps statements -- execute query
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
What does JDBC stand for?
Java Database Connection API
119
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
Sign up for an account with Oracle
120
Which of these is NOT required for opening a database connection? Database URL Username Programmer's static IP address Password
Programmer's static IP address
121
Which is not an SQL statement type?
`RelayStatement`
122
DriverManager class
DriverManager class - to make a connection with a database driver
123
DataSource interface
- for retrieving connections, an alternative to DriverManager
124
Connection interface
- represents a physical connection with a database
125
SQLException class -
a general exception thrown when something goes wrong when accessing the database
126
Statement interface
- used for executing static SQL statements
127
PreparedStatement interface
- represents pre-compiled SQL statements
128
CallableStatement interface
- used to execute stored procedures
129
CallableStatement interface
- used to execute stored procedures
130
ResultSet interface
- represents data returned from the database
131
DAO design pattern
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
.execute()
for any kind of SQL statement, returns a boolean
133
.executeUpdate()
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.
© 2025 Bold Learning Solutions. Terms and Conditions