Week 4 - More Java & SQL Intro - Non Access Modifiers and other keywords, variable scope, SQL intro Flashcards
What is data?
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.
What is a database?
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.
SQL databases
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.
NoSQL databases
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.
Which of the following program copies the databases from one server to another?
mysqlmoveitdb
mysqldbmoveit
mysqlcopydb
mysqldbcopy
mysqldbcopy
To use mysqldbcopy which privileges are needed at the source server?
SELECT
CREATE
INSERT
UPDATE
SELECT
The program that performs logical backups is _____?
mysqldump
What Does Query Mean?
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.
Describe an RDBMS
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.
RDBMS is a a DBMS that revolves around a relational model
True
An RDBMS manages data by
storing them in tables
A single relational database can contain many tables along with many other types of objects. T/F?
True
Structured Query Language or SQL is…
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.
SQL is used to…
Define database structure
Manipulate stored data
Define data access permissions
Control concurrent data access
Query stored data
DDL
Data Definition Language. Defines data structure
DML
Data Manipulation Language. Insert, Update, Delete record
DCL
Data Control Language. Grant or revoke access permissions to database object
TCL
Transaction Control Language. Defines concurrent operation boundaries
DQL
Data Query Language. Search, filter, group, and aggregate stored data
To accommodate the operations of the above categories, SQL is broken into 5 sublanguages
Each sublanguage is responsible for a specific set of operations on the database
DDL
DML
DCL Data
TCL
DQL
What is the main purpose of SQL?
Administering RDBMS
In a database, each table is defined with a set of
columns
Each column must have a data type which restricts…
the type of data that can be assigned to it
SQL provides a standard list of data types which can be categorized into 3 main categories, name them.
Character types
Numeric types
Temporal types
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
The purpose of SQL data types is to constraint a column’s expected value. T/F?
True
SQL is used to…
Define database structure
Manipulate stored data
Define data access permissions
Control concurrent data access
Query stored data
6 DDL commands are:
CREATE
ALTER
DROP
TRUNCATE
RENAME
COMMENT
3 DML commands are:
INSERT
UPDATE
DELETE
3 DQL commands are:
SELECT -DCL
GRANT
REVOKE
Data Definition Language is the SQL language subset used for…
defining data or altering structure in the database.
CREATE can be used to create:
objects on the server
Database
User
Table
Index
Trigger
Function
Stored Procedure
View
The DROP command is used to remove
objects from the server
Any object created using the CREATE command can be dropped using…
the DROP command
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.
The RENAME command is used
to rename objects
The TRUNCATE command is used to remove
all data from a table along with all space allocated for the records.
Difference between truncate and drop command
Unlike DROP truncate will preserve the structure of the table.
The DDL sublanguage is used to ______ in a database.
define data structure
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
The Drop command maintains the structure of the database while removing all data from a table. T/F?
false
The Truncate command removes a table from the database schema. T/F?
False
Syntax to create a table:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
…..
columnN datatype,
PRIMARY KEY( one or more columns )
);
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.
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.
Schema defines a set of.
integrity constraints
templates
normalization rules
none of these
integrity constraints
In MySQL, SCHEMA is synonymous with
DATABASE
Unlike the other sublanguages, DQL is only associated with a single command
SELECT
The DQL sublanguage is used to ______ in a database.
query for data
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
Which clause is associated with filtering records before grouping?
Having having_condition
Where where_condition
Limit limit_condition
None of these
Where where_condition
. 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;
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;
A _____ in a table represents a relationship among a set of values.
Row
The term ‘TUPLE’ is also referred to as _____.
Row
The term _____ refers to a column in table.
Attribute
A relational database consists of a collection of ____?
tables
Syntax: Add values to the newly created table.
INSERT INTO movies VALUE(“Mr.Bean”, “comedy”, “Mr.ABC”, 2019);
Syntax: Use DROP command to completely remove the table.
DROP TABLE table_name;
Syntax: Use TRUNCATE command is used to delete the data inside a table, but not the table itself.
TRUNCATE TABLE table_name;
Syntax: to add a column
ALTER TABLE table_name
ADD column_name datatype;
Syntax: to drop a column
ALTER TABLE table_name
DROP COLUMN column_name;
Syntax: to modify a column
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language
Which operator performs pattern matching?
MATCH operator
EXISTS operator
BETWEEN operator
LIKE operator
LIKE operator
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
Syntax to create database:
CREATE DATABASE WORK;
Syntax to select/use database:
Syntax to select/use database:
Syntax to create TABLE:
CREATE TABLE EMP
(id INT, NAME VARCHAR(25), AGE INT, PRIMARY KEY(id));
Syntax to alter TABLE:
ALTER TABLE EMP
ADD SALARY DOUBLE;
Syntax to insert values:
INSERT INTO EMP VALUES(111, “Ryan”, 24, 5000);
Syntax to update record:
UPDATE EMP SET AGE = 23 WHERE id = 111;
Syntax to delete record:
DELETE FROM EMP WHERE id = 111;
Syntax to select record:
SELECT * FROM EMP;
Syntax to truncate table:
TRUNCATE TABLE EMP;
Syntax to drop table:
DROP TABLE EMP;
Data Modification Language is the SQL language subset used
for modifying data in the database.
DML commands:
INSERT
UPDATE
DELETE
The insert command is used
to add records to a database table
The update command is used
to modify existing data in a database table.
The delete command is used
to remove data from a database table
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);
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);
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’;
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;
Syntax: The delete command is used to remove data from a database table.
DELETE FROM <table_name> [WHERE where_condition]</table_name>
Syntax: update command the delete command has an optional WHERE clause
DELETE FROM my_table WHERE id=3;
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.
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.
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;
Syntax: to find sum of all values from a specific field from EMP table
SELECT SUM(duration) AS “Total duration” FROM EMP;
Syntax to calculate average of the values from specified column in EMP table
SELECT AVG(salary) FROM EMP;
Syntax to find minimum salary from EMP table
SELECT MIN(salary) FROM employee;
Syntax to find maximum salary from EMP table
SELECT MAX(salary) FROM employee;
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
If the rows are satisfying the where predicate, then they are placed into groups by the?
Group by clause
The clause that apply the aggregate functions to get a single result tuple for each group, is known to be
Select clause
Aggregate functions are functions that take a ___________ as input and return a single value.
Collection of values
All aggregate functions except _____ ignore null values in their input collection.
COUNT(*)
If we do want to eliminate duplicates, we use the keyword ______in the aggregate expression.
DISTINCT
WHERE clause
WHERE clause is used to filter the data from the table based on user needs.
GROUP BY clause
GROUP BY clause is used in MySQL queries to organize records which have same attribute values.
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
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
What is the meaning of “ORDER BY” clause in Mysql?
Sorting your result set using column data
The RESULTSET is an
object which represents a set of data returned from a database as a result of a query input.
Which of the following is advantage of using PreparedStatement?
Prevents SQL injection
There are 3 types of methods of ResultSet Interface-
Get Methods
Update Methods
Navigational Method
There are 3 types of methods of ResultSet Interface-
Get Methods
Update Methods
Navigational Method
Get Methods :
Used to view the data in the columns/attributes of the current tuple being pointed by the cursor.
Update Methods :
Used to update the data in the columns/attributes of the current tuple. The updates can then be committed to the database.
Navigational Methods :
Used to move the cursor around.
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.
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
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.
syntax for creating a connection (jdbc)
try (Connection conn = DriverManager.getConnection(DB_URL,USERNAME,PASSWORD)) {
// more code goes here
} catch (SQLException e) {}
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);
What does JDBC stand for?
Java Database Connection API
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
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
Which is not an SQL statement type?
RelayStatement
DriverManager class
DriverManager class - to make a connection with a database driver
DataSource interface
- for retrieving connections, an alternative to DriverManager
Connection interface
- represents a physical connection with a database
SQLException class -
a general exception thrown when something goes wrong when accessing the database
Statement interface
- used for executing static SQL statements
PreparedStatement interface
- represents pre-compiled SQL statements
CallableStatement interface
- used to execute stored procedures
CallableStatement interface
- used to execute stored procedures
ResultSet interface
- represents data returned from the database
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.
.execute()
for any kind of SQL statement, returns a boolean
.executeUpdate()
for DML statements, returns an int which is the number of rows affected
