Database Concepts and XML Querying Quiz
45 Questions
0 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to Lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What does the .nodes() method commonly return?

  • A hierarchical structure of data.
  • A list of nodes in XML format.
  • A string containing XML content.
  • Table and Column aliases to be used in SELECT statements. (correct)

In XML querying, the path provided is not case-sensitive.

False (B)

What is the compulsory keyword used in recursive Common Table Expressions?

UNION ALL

A method where a function calls itself to solve a problem is known as __________.

<p>recursion</p> Signup and view all the answers

Match the following XML querying methods with their appropriate characteristics:

<p>.query() = Used to extract specific XML nodes. .value() = Fetches a specific value from an XML element. .nodes() = Breaks the XML into a table format for querying. Subselects = Provide intermediate results for other queries.</p> Signup and view all the answers

What happens when a subselect does not return any rows?

<p>The result is NULL. (C)</p> Signup and view all the answers

Table-valued subqueries must be aliased in the FROM clause.

<p>True (A)</p> Signup and view all the answers

What condition signals the end of recursion in a recursive algorithm?

<p>No data found</p> Signup and view all the answers

What is a candidate key in the context of the content provided?

<p>A key that defines the genre (B)</p> Signup and view all the answers

A junction table is used to resolve 1:N relationships in database normalization.

<p>False (B)</p> Signup and view all the answers

What should be created to avoid losing roles when deleting records from the People table?

<p>Roles table</p> Signup and view all the answers

In PascalCase, the first letter of every word is _____

<p>Uppercase</p> Signup and view all the answers

Match the naming conventions with their respective formats:

<p>PascalCase = First letter of every word is Uppercase camelCase = First letter of every word is Uppercase except the first snake_case = Uses underscore between words kebab-case = Uses hyphen between words</p> Signup and view all the answers

What happens when a temporary table is created with the name starting with ##?

<p>It is visible to all sessions. (B)</p> Signup and view all the answers

The statement 'NULL is the default behavior for columns in a database' is true.

<p>True (A)</p> Signup and view all the answers

What command is used to remove an existing table from a database?

<p>DROP TABLE</p> Signup and view all the answers

To add a new column to an existing table, we use the command ALTER TABLE table_name ADD column_name datatype [/].

<p>NULL, NOT NULL</p> Signup and view all the answers

Match the commands with their purposes in SQL.

<p>CREATE TABLE = Creates a new table in the database ALTER TABLE = Modifies an existing table DROP TABLE = Deletes a table from the database SELECT = Retrieves data from the database</p> Signup and view all the answers

Which of the following statements about session in a database is correct?

<p>A session starts when a connection is established. (D)</p> Signup and view all the answers

The rules for naming tables include that the name can start with a number.

<p>False (B)</p> Signup and view all the answers

What is a constraint in a database?

<p>A rule applied to columns to ensure data integrity.</p> Signup and view all the answers

What is the main use of a cursor in SQL?

<p>To perform row-level operations (B)</p> Signup and view all the answers

By default, input parameters of a stored procedure can be modified.

<p>False (B)</p> Signup and view all the answers

What are the two types of user-defined functions (UDFs) in Microsoft SQL Server?

<p>Scalar valued function and Table valued function</p> Signup and view all the answers

Which SQL command will remove all rows from a table without logging individual row deletions?

<p>TRUNCATE TABLE tablename (A)</p> Signup and view all the answers

Stored procedures are a precompiled collection of one or more SQL statements, which can be executed as a single ______.

<p>unit</p> Signup and view all the answers

An IDENTITY column can be set on an existing column using the ALTER TABLE command.

<p>False (B)</p> Signup and view all the answers

Match the following SQL concepts with their descriptions:

<p>Cursor = Allows iteration through query rows Stored Procedure = Precompiled SQL collection User-defined Function = Reusable code block for specific tasks Loop = Executes statements repeatedly</p> Signup and view all the answers

What do Common Table Expressions (CTEs) use to implement recursion in SQL Server?

<p>UNION ALL</p> Signup and view all the answers

A primary key constraint ensures that a column has __________ values.

<p>unique</p> Signup and view all the answers

What command is used to delete a stored procedure in SQL Server?

<p>DROP PROCEDURE (C)</p> Signup and view all the answers

Match the SQL command to its function:

<p>DELETE FROM = Removes specific rows from a table based on a condition TRUNCATE TABLE = Removes all rows from a table UPDATE = Modifies existing records in a table CREATE TABLE = Defines a new table along with its columns and constraints</p> Signup and view all the answers

The use of BEGIN...END block is mandatory for a single statement in SQL.

<p>False (B)</p> Signup and view all the answers

The _____ statement is responsible for interrupting the current iteration in a loop in SQL.

<p>CONTINUE</p> Signup and view all the answers

Which of the following statements is true regarding temporary tables?

<p>Temporary tables disappear when the creating session ends. (C)</p> Signup and view all the answers

It is possible to have multiple IDENTITY columns in a single table.

<p>False (B)</p> Signup and view all the answers

What is the purpose of the CHECK constraint in SQL?

<p>To enforce a specific rule on what values can be inserted into a column.</p> Signup and view all the answers

Which of the following statements is true regarding constraints in SQL?

<p>PRIMARY KEY constraints must specify columns in a comma-separated list. (D)</p> Signup and view all the answers

On-premise software is hosted and accessed over the internet.

<p>False (B)</p> Signup and view all the answers

What command is used to create a database in Microsoft SQL Server?

<p>CREATE DATABASE</p> Signup and view all the answers

In Azure SQL databases, creating a new database with the CREATE DATABASE command uses _____ settings by default.

<p>default</p> Signup and view all the answers

Match the SQL statements with their correct descriptions:

<p>CREATE DATABASE MyFirstDatabase = Creates a new database BEGIN = Defines the beginning of a block of code BREAK = Exits a loop FOREIGN KEY = Links two tables together</p> Signup and view all the answers

Which statement accurately describes the use of cursors in SQL?

<p>Cursors process rows sequentially in a result set. (D)</p> Signup and view all the answers

In T-SQL, 'CONTINUE' is used to exit a loop.

<p>False (B)</p> Signup and view all the answers

What do BEGIN and END denote in T-SQL?

<p>Code block</p> Signup and view all the answers

Flashcards

XML Query using .query()

Extracts data from XML data using a path expression.

XML Query .value()

Retrieves a specific value from XML data at a given path and with a specific data type.

.nodes() in FROM clause

This function is used in the FROM clause to return XML data as rows and columns, making it usable with XML methods.

Recursive CTE

A common table expression (CTE) that calls itself to process hierarchical data.

Signup and view all the flashcards

Recursion

A programming technique where a function calls itself to solve a problem by breaking it into smaller, similar subproblems.

Signup and view all the flashcards

Subquery in SELECT clause

A query nested inside another SQL query, typically used to get a single value or one row from a table.

Signup and view all the flashcards

Subquery in FROM clause

A SQL query that produces a table that's used in the FROM clause of another query.

Signup and view all the flashcards

Common Table Expression (CTE)

A temporary, named result set that can be referenced within a single SQL statement; Can be used like a table but is discarded after use.

Signup and view all the flashcards

On-Premise Software

Software installed and run directly on a company's servers within its own facilities.

Signup and view all the flashcards

Cloud-Based Software

Software hosted and accessed over the internet, not directly installed on company servers.

Signup and view all the flashcards

CREATE DATABASE

Command used to create a new database in both on-premise and Azure SQL Server.

Signup and view all the flashcards

BEGIN and END

Keywords in T-SQL used to define blocks of code, especially required for multiple operations.

Signup and view all the flashcards

BREAK

Keyword used to exit a loop in T-SQL, ending its execution completely.

Signup and view all the flashcards

CONTINUE

Keyword used to skip to the next iteration of a loop without executing the remaining code within the current iteration.

Signup and view all the flashcards

Cursor

A database object that allows for the sequential processing of rows in a result set.

Signup and view all the flashcards

Multiple Databases

Both on-premise and Azure SQL servers can support multiple databases on a single server.

Signup and view all the flashcards

Column Naming Rules

Column names must be unique within a table and should be descriptive, reflecting the data stored.

Signup and view all the flashcards

Update Table with Constant Values (Single Table)

Modify all rows of a table by setting specified columns to constant values.

Signup and view all the flashcards

Update Table with Constant Values (Filtering)

Modify only selected rows of a table based on specified conditions (WHERE clause) by setting columns to constant values.

Signup and view all the flashcards

Update Table with Constant Values (Multiple Tables)

Modify rows from a table based on information from other tables (FROM clause) and conditions (WHERE clause) by setting columns to constant values.

Signup and view all the flashcards

Delete All Rows from a Table

Remove all rows from a table leaving the table structure intact.

Signup and view all the flashcards

Delete Rows with Filtering

Remove specific rows from a table based on defined conditions (WHERE clause) leaving other rows intact.

Signup and view all the flashcards

Global Temporary Table (##)

A table created in the temporary database that persists until all referencing sessions end. Visible to all users.

Signup and view all the flashcards

Local Temporary Table (#)

A table created in the temporary database that persists only until the creating session ends. Visible only to the creating session.

Signup and view all the flashcards

TempDB

A system database in SQL Server used for temporary data, like temporary tables, indexes, and intermediate query results. It's temporary and automatically deleted when the session closes.

Signup and view all the flashcards

Temporary Tables

Tables created in TempDB that exist only for a specific session. Tables starting with '#' are visible only to the creating session, while those starting with '##' are visible to all sessions.

Signup and view all the flashcards

Session

An interaction between a user or application and the database, lasting from connection establishment to termination. During a session, various database operations can be performed.

Signup and view all the flashcards

NULL

A value indicating that a specific cell in a database table does not have a value. It is different from 0 or an empty string.

Signup and view all the flashcards

NOT NULL

A constraint that ensures that a column in a database table cannot have a NULL value; requires a value to be entered.

Signup and view all the flashcards

ALTER TABLE

A command used to modify an existing table in a database. It can be used to add, delete, or modify columns and apply changes.

Signup and view all the flashcards

DROP TABLE

A command used to delete a table from a database. This action is permanent and irreversible.

Signup and view all the flashcards

Constraints

Rules or conditions applied to columns in a table that enforce data integrity. They ensure data meets specific criteria, such as uniqueness or data relationships.

Signup and view all the flashcards

Scalar Value Function

A user-defined function (UDF) in SQL Server that returns a single value, like a number or string, based on input parameters.

Signup and view all the flashcards

Table Value Function

A user-defined function in SQL Server that returns a table as its output, similar to a stored procedure, but designed for query-like operations.

Signup and view all the flashcards

Stored Procedure

A precompiled block of SQL statements and procedural logic stored in a database that can be executed as a unit with different parameters.

Signup and view all the flashcards

Input Parameter

A parameter in a stored procedure or function that takes a value as input, but its value cannot be altered within the procedure or function.

Signup and view all the flashcards

IF...ELSE Statement

A conditional construct in SQL Server used to execute different code blocks based on a truth condition.

Signup and view all the flashcards

WHILE Loop

A control flow statement in SQL Server that repeatedly executes a block of code as long as a specified condition is true.

Signup and view all the flashcards

BREAK Statement

Used to interrupt the execution of a loop immediately, ending the loop iteration and continuing with the next statement after the loop.

Signup and view all the flashcards

3NF: Non-Transitive Dependencies

In 3NF, dependencies between attributes must be direct and not indirect (transitive). A dependency is transitive if it passes through other attributes. For example, if A depends on B and B depends on C, then A transitively depends on C.

Signup and view all the flashcards

Normalizing 1:N Relations

In 1:N relationships (one-to-many), you separate tables to avoid data redundancy and anomalies. This means creating a primary table (e.g., 'People') and a related table (e.g., 'Roles'), linking them with a foreign key.

Signup and view all the flashcards

Normalizing M:N Relations

In M:N relationships (many-to-many), you create a junction table to connect two tables that have multiple relationships with each other. This ensures data integrity and removes data duplication.

Signup and view all the flashcards

PascalCase

A naming convention where the first letter of every word is capitalized (e.g., StreamingProviderId).

Signup and view all the flashcards

snake_case

A naming convention where underscores separate words, with all letters lowercase (e.g., imdb_id).

Signup and view all the flashcards

Study Notes

Hierarchical Data Structures

  • Hierarchical data structures are common in real-life systems (e.g., file systems, menus, organizations).
  • Data can be represented as adjacency lists.
  • An adjacency list is a hierarchical data representation where each element (node) is stored in a table, and relationships between elements are indicated using references.
  • This structure is used in relational databases where each element links to its parent's identifier.

XML (eXtensible Markup Language)

  • XML is a markup language designed for storing and transporting data in a human- and machine-readable format.
  • XML documents have a well-defined hierarchical structure, organized in a tree-like fashion with a single root element at the top.
  • Each element is enclosed within opening and closing tags (e.g., <element>content</element>).
  • Elements can contain other elements, creating a parent-child relationship.
  • XML elements define the structure and content within the XML document.
  • Elements can be nested.
  • Attributes provide extra information about an element (name-value pairs within the opening tag).
  • Example: <element attribute="value">content</element>

JSON (JavaScript Object Notation)

  • JSON is a lightweight data interchange format easily read and written by humans and machines.
  • JSON data is represented as a collection of key-value pairs, where keys are strings and values can be strings, numbers, objects, arrays, boolean values, or null.
  • Key-value pairs are enclosed in curly braces ({}) for objects and square brackets ([]) for arrays.
  • This hierarchical structure allows for complex data representation using string, number, boolean, null, array and object datatypes.

Seminar 7 Recap

  • Stands for eXtensible Markup Language.
  • Defines and stores data in a shareable manner for information exchange between systems.
  • Has a tree-like hierarchical structure with a single root.
  • Each item has one parent.
  • Each element has either child elements or a value.
  • Elements are building blocks.
  • Elements begin with an opening tag and end with a closing tag.
  • Elements can contain values or other elements.
  • Extra information about an element is included in attributes.

FOR XML AUTO/PATH

  • FOR XML AUTO: Each row of the query becomes an element, columns become attributes.
  • FOR XML PATH: Each row becomes an element, columns become children elements for that element.

Other Key Concepts (from subsequent seminar pages)

  • Subqueries: Scalar-valued and table-valued subqueries using in SELECT and FROM clauses.
  • Common Table Expressions (CTEs): Table-valued subqueries usable multiple times.
  • XML Data Type: Using XML data type for storing and handling XML data.
  • Print Value: Methods of printing values including querying XML data.
  • Recursion: A programming technique where a function calls itself to solve a problem.
  • Temporary Tables: Tables accessible only within a specific session or till related sessions end (# and ## prefixes).
  • NULL and NOT NULL: Data constraints specifying whether a column can hold null values.
  • ALTER TABLE: Command for modifying existing tables (adding/modifying/deleting columns).
  • DROP TABLE: Command for deleting tables.
  • Naming Conventions: Rules for creating names for tables, columns, and other objects.
  • Constraints: Rules defining data restrictions for columns or tables (e.g., CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY).
  • CREATE or ALTER (for procedures and functions): Commands to create or modify functions.
  • Data Definition Language (DDL): Statements that define the database schema (tables, views, constraints)
  • Data Manipulation Language (DML): Statements that manipulate data within the database (INSERT, UPDATE, DELETE, SELECT)
  • Views: Storing queries and referencing them as tables, allowing data selection from multiple tables and performing more complicated selections.
  • Normalization: A database design technique for organizing data to minimize anomalies (insertion, update, deletion).

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Quiz Team

Related Documents

Seminar 7 - Database Design

Description

Test your knowledge on database concepts and XML querying techniques with this comprehensive quiz. Questions cover topics like recursive algorithms, candidate keys, naming conventions, and more. Perfect for students in database management courses.

More Like This

Use Quizgecko on...
Browser
Open
Browser
Browser