(ADBS) PRELIM

1. Database

2. Database Management System (DBMS)

3. Database Application

4. Users

What are the components of database system?

Database

What is an organized collection of structured information, or data, typically stored electronically in a computer system?

Database Management System (DBMS)

What serves as an interface between the database and its end-users or programs?

Database Management System (DBMS)

What allows users to retrieve, update, and manage how the information is organized and optimized?

• Performance monitoring

• Tuning

• Backup and recovery

A DBMS also facilitates oversight and control of databases, enabling a variety of administrative operations such as?

Database Application

What is a set of one or more computer programs that serves as an intermediary between the user and the DBMS?

• Application programs

• by sending DB query language to the DBMS

What does read or modify database data? And how?

1. Naive Users

2. Application Programmers

3. Sophisticated Users

What are the types of users?

Naive Users

Who are the unsophisticated users who interact with the system by using predefined user interfaces; such as web or mobile apps?

Application Programmers

Who are the computer professional who write application programs?

Sophisticated Users

Who interact with the system without writing programs? Instead they form their requests either using a DB query language.

Database System Architecture

What involves database design and construction on how the database will operate and function within existing structures and location?

1. Centralized Database System

2. Distributed Database System

What are the two main types of database system architecture?

Centralized Database System

What is the database that supports data located at a single site?

Distributed Database System

Which refers to a collection of multiple interconnected databases, and spread physically across various locations?

1. Structured Data

2. Unstructured Data

3. Semi-structured Data

What are the structures of data in DBMS?

Structured Data

Which refers to data that are stored in relational databases?

Structured Data

Which type of data uses a predefined and expected format or schema?

Unstructured Data

Which refers to data that exist in its original or raw state the can come in all shapes and sizes?

Semi-structured Data

Which refers to individual items of the same type may have different sets of attributes?

Semi-structured Data

Which contains semantic tags and there is no predefined schema?

Structured Data

What data structure does the example represent?

Unstructured Data

What data structure does the example represent?

Semi-structured Data

What data structure does the example represent?

1. SQL (Structured Query Language)

2. NoSQL

3. NewSQL

4. Object-Oriented DB

5. XML (Extensible Markup Language)

What are the types of database management system?

SQL (Structured Query Language)

What refers to a computer language for storing, manipulating, and retrieving data stored in a relational database?

NoSQL

What is generally used to describe a new generation of DBMS that is not based on the traditional relational database model?

Not only SQL

What does NoSQL stands for?

Big Data

NoSQL has been developed to address the challenges represented by what?

NoSQL

Which features a dynamic schema for unstructured data and the data can be stored in different ways?

NoSQL

What allows you to create documents without first having to carefully plan and define their structure?

NewSQL

Which incorporates and builds on the concepts and principles of SQL and NoSQL systems?

NewSQL

Which provides improved functionality and services, by combining the reliability of SQL with the speed and performance of NoSQL?

Object-Oriented Database

Which refers to a database that stores object rather than data as individual relations?

C++, Java and C#

Object-Oriented Database makes use of object-oriented languages such as?

Object-Oriented Database

Choose this when you have a business need for high performance on complex data.

XML (Extensible Markup Language)

Which is designed to facilitate the exchange of structured documents?

XML (Extensible Markup Language)

Which simplifies data sharing between various systems because of its

platform-independent nature?

XML (Extensible Markup Language)

Choose this as long as your datasets stay relatively small.

• PL-SQL

• T-SQL

• PostgreSQL

• MySQL

• SQLite

What are the examples of SQL Extensions?

PL-SQL

What is the combination of SQL along with the procedural features of programming languages?

PL-SQL

What was developed by Oracle Corporation and stands for Procedural Language?

Transact-SQL

T-SQL stands for?

T-SQL

What is the Microsoft’s and Sybase’s extension of SQL that adds and declare variables, support transaction control, error and exception handling, and row processing to SQLs existing functions?

PostgreSQL

What is the open-source object-relational database system that uses and extends the SQL language?

MySQL

What is the open-source SQL relational database management system that was developed and supported by Oracle?

SQLite

What is the relational database management system contained in a C programming library?

• BEGIN/END

• DECLARE

• SET or SELECT

• CAST

• PRINT

• TRY-CATCH

What are examples of T-SQL Statement Blocks?

BEGIN/END

What is used to define a set of SQL statements that execute together?

DECLARE

What is used to declare a variable in SQL server?

@ symbol

If we want to use a variable in SQL server, what do we need to declare first and start variable names with?

SET or SELECT

What is used to assign a value(s) to a variable?

CAST

What converts an expression of one data type to another?

PRINT

What allows users to return a pre-defined message or value?

TRY-CATCH

What consists of a try block followed by one or more catch clauses, which specify handlers for different exceptions?

TRY-CATCH

What feature helps to deal with any unexpected or exceptional situations that occur when a program/query is running?

• IF-ELSE

• ELSE

• ELSE-IF

• WHILE

• CASE

What are some examples of T-SQL Conditional Statements?

IF-ELSE

This conditional statement specify a block of code to be executed, if the returning condition is true.

ELSE

This conditional statement specify a block of code to be executed, if the returning condition is false.

ELSE-IF

This conditional statement specify a new condition to test, if the previous returning condition is false.

WHILE

This conditional statement loops through a block of code as long as a specified condition is true.

CASE

This conditional statement is the extension of IF-ELSE statement.

CASE

This conditional statement allows the user to apply multiple conditions to perform different sets of actions.

Common Table Expression (CTE)

What does specify a temporary named result set?

WITH

What operator can be used to define a CTE?

Common Table Expression (CTE)

What allows users to define tables that can be used in a particular query?

• SELECT

• INSERT

• UPDATE

• DELETE

What are the other statements CTE can be referenced within?

• ORDER BY

• INTO

• OPTION

What are the clauses that cannot be used in CTE queries?

• Non-Recursive CTE

• Recursive CTE

What are the types of CTE?

Non-Recursive CTE

Which doesn’t use repeated procedural loops or recursion?

Non-Recursive CTE

Which type of CTE is easier to understand?

Recursive CTE

Which type of CTE uses recursion?

Recursive CTE

Which is useful when working with hierarchical data?

Recursive CTE

Which type of CTE continues to execute until the query returns the entire hierarchy?

Subqueries

What is a query (SELECT statement) inside another query?

parantheses

Subqueries are expressed inside what?

Outer Query

What refers to the first query in the SQL statement?

Inner Query

What refers to the query inside the SQL statement?

Inner Query

Which query is executed first?

output of an inner query

What is used as the input for the outer query?

1. In WHERE clause

2. In FROM clause

3. In SELECT clause

4. In IN operator

5. In ALL and ANY operator

6. Correlated Subqueries

7. In EXISTS operator

What are examples of subqueries?

WHERE clause

Which subquery work as part of the row selection process?

WHERE clause

Which subquery is often found in the WHERE clause?

Nested Subqueries

WHERE clause is also called as?

FROM clause

Which subquery returns a temporary or virtual table?

FROM clause

Which subquery is useful in data warehousing application?

Inline View or Derived Table

FROM clause is also called as?

SELECT clause

Which subquery is nested in the list of another SELECT statement?

IN operator

Which subquery allows users to match one item from any of those in the list?

ANY operator

Which subquery returns true if any of the subquery values satisfy the condition?

ALL operator

Which subquery returns true if all the subquery values meet the condition?

Correlated Subqueries

Which subquery is used to select data from a table referenced in the outer query?

Correlated Subqueries

Which is executed repeatedly, once for each row evaluated by the outer query?

Simple Subquery

Correlated subqueries cannot be executed independently as?

Repeating Subquery

Correlated subqueries are also known as?

EXISTS operator

Which subquery is used to check whether a subquery produces any rows of query results?

Correlated Subqueries

EXISTS operator is commonly used with what?

Views

What refers to a virtual table that is constructed from other tables or views and saved as an object in the database?

Views

Which has no data of its own, but obtains data from tables or other views?

• ORDER BY clause

• A reference to a temporary table or a table variable

What cannot be included in VIEWS?