Brainscape's Knowledge GenomeTM

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


See full index

Data Management Foundations D426 > Zbook Chapter 1 > Flashcards

Zbook Chapter 1 Flashcards

1
Q

Information Management System

A

a software application that manages corporate data for a specific business function

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

What is a database administrator responsible for?

A

Securing the database system against unauthorized users and enforcing procedures for user access and database system availability.

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

What does a database designer determine?

A

The format of each data element and the overall database structure.

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

What priorities must a database designer balance?

A

Storage, response time, and support for rules that govern the data.

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

What does a database programmer do?

A

Develops computer programs that utilize a database and writes applications that combine database query languages and general-purpose programming languages.

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

Who is a database user?

A

A consumer of data in a database who requests, updates, or uses stored data to generate reports or information.

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

How do database users usually access the database?

A

Via applications, but they can also submit queries directly to the database system.

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

How can small databases shared by one or two users be managed?

A

In a text file or spreadsheet.

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

Why are text files and spreadsheets inadequate for large, complex databases?

A

Because as databases grow in size, complexity, and use, they have special requirements that text files and spreadsheets cannot meet.

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

What happens to query response time when many users and applications access large databases simultaneously?

A

Query response time degrades rapidly.

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

How do database systems maintain fast response times?

A

By structuring data properly on storage media and processing queries efficiently.

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

By structuring data properly on storage media and processing queries efficiently.

A

Because many database users should have limited access to specific tables, columns, or rows of a database.

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

How do database systems ensure security?

A

By ensuring authorized users only access permissible data and protecting against hackers through encryption and restricted access.

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

What do database systems ensure regarding data consistency?

A

That data is consistent with structural and business rules, such as synchronizing multiple copies of data and ensuring referenced data exists.

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

What must database systems do in case of failures?

A

Recover from failures and restore the database to a consistent state without loss of data.

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

What is a transaction in database systems?

A

A group of queries that must be either completed or rejected as a whole to ensure data consistency.

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

Why must a transaction be processed completely or not at all?

A

Because executing some but not all queries can result in inconsistent or incorrect data.

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

Give an example of a transaction that must be processed completely or not at all.

A

A debit-credit transaction where $100 is transferred from one bank account to another. Both the debit and credit queries must succeed or fail together.

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

What must database systems do if a computer or application fails while processing a transaction?

A

Reverse partial results and restore the database to the values prior to the transaction.

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

Why is it important to prevent conflicts between concurrent transactions?

A

To avoid conflicts that can occur when multiple transactions access the same data at the same time.

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

Give an example of a conflict between concurrent transactions.

A

Sam selects a seat on a flight, and Maria purchases the same seat in a separate transaction before Sam completes his transaction, making the seat unavailable for Sam.

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

What must database systems ensure about transaction results?

A

That transaction results are never lost and are always saved on storage media, regardless of application or computer failures.

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

What does the architecture of a database system describe?

A

The internal components and the relationships between components.

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

What is the role of the query processor in a database system?

A

It interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application. It also performs query optimization.

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

How does the storage manager function in a database system?

A

It translates the query processor instructions into low-level file-system commands that modify or retrieve data and uses indexes to quickly locate data.

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

What is the responsibility of the transaction manager?

A

Ensuring transactions are properly executed, preventing conflicts between concurrent transactions, and restoring the database to a consistent state in the event of a failure.

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

What is the purpose of the log in a database system?

A

It contains a complete record of all inserts, updates, and deletes processed by the database, and is used by the transaction manager to restore the database in the event of a failure.

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

What is the catalog (or data dictionary) in a database system?

A

A directory of tables, columns, indexes, and other database objects used by other components to process and execute queries.

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

Why do database systems have different capabilities and component details?

A

Because some database systems do not support transactions and therefore have no transaction manager, and the storage manager implementation depends on the physical structure of data on storage media.

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

Steps for database query

A

A database system consists of:

Query processor
Storage manager
Transaction manager
Log
Catalog
An application sends queries to the query processor.

The query processor uses information from the catalog to perform query optimization.

The storage manager translates the query processor instructions into file-system commands and uses an index to quickly locate the requested data.

The transaction manager logs insert, update, and delete queries.

The result is sent back to the application.

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

Metadata

A

data about the database, such as column names and the number of rows in each table

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

What is a relational database?

A

A database that stores data in tables, columns, and rows, similar to a spreadsheet.

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

What does SQL stand for and what does it do?

A

SQL stands for Structured Query Language and includes statements to read/write data, create/delete tables, and administer the database.

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

For what types of databases are relational systems ideal?

A

Databases requiring an accurate record of every transaction, such as banking, airline reservations, and student records.

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

What is big data and how did it impact database systems?

A

Big data refers to massive volumes of online data with poorly structured or missing information, leading to the development of NoSQL systems optimized for big data.

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

What is NoSQL?

A

NoSQL stands for “not only SQL” and refers to non-relational systems optimized for big data.

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

How did the licensing model for database systems change after 2000?

A

Open source software became popular, allowing anyone to inspect, copy, and modify software with no licensing fee.

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

How are database systems ranked on db-engines.com?

A

By tracking product references on social media, internet searches, job websites, and technical websites.

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

CRUD operations

A

Create, Read, Update, and Delete data

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

What does the SQL statement INSERT do?

A

Inserts rows into a table.

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

What does the SQL statement SELECT do?

A

Retrieves data from a table.

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

What does the SQL statement UPDATE do?

A

Modifies data in a table.

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

What does the SQL statement DELETE do?

A

Deletes rows from a table.

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

What does the SQL CREATE TABLE statement do?

A

Creates a new table by specifying the table and column names.

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

What does the INT data type store?

A

Integer values.

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

What does the DECIMAL data type store?

A

Fractional numeric values.

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

What does the VARCHAR data type store?

A

Textual values.

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

What does the DATE data type store?

A

Year, month, and day.

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

What does VARCHAR(10) indicate?

A

A textual value with a maximum of ten characters.

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

What does DECIMAL(10, 3) indicate?

A

A numeric value with ten significant digits, including three after the decimal point.

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

What is a database design?

A

A specification of database objects such as tables, columns, data types, and indexes, and the process used to develop this specification.

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

How is the database design process different for small and large databases?

A

For small, simple databases, the process can be informal and unstructured. For large, complex databases, it has three phases: Analysis, Logical design, and Physical design.

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

What does the analysis phase specify?

A

Database requirements without regard to a specific database system, represented as entities, relationships, and attributes.

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

What is an entity in database design?

A

A person, place, activity, or thing.

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

What is a relationship in database design

A

A link between entities.

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

What is an attribute in database design?

A

A descriptive property of an entity.

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

What are some alternative names for the analysis phase?

A

Conceptual design, entity-relationship modeling, and requirements definition.

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

How are entities, relationships, and attributes depicted in ER diagrams?

A

Rectangles represent entities.
Lines between rectangles represent relationships.
Text inside rectangles and below entity names represent attributes.

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

What does the logical design phase implement?

A

Database requirements in a specific database system.

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

What does logical design convert in relational database systems?

A

Entities, relationships, and attributes into tables, keys, and columns.

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

What is a key in a table?

A

A column used to identify individual rows of a table.

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

How are tables, keys, and columns specified in SQL?

A

With CREATE TABLE statements.

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

How are tables depicted in a table diagram?

A

Rectangles represent tables.
Table names appear at the top of rectangles.
Text within rectangles and below table names represents columns.
Solid bullets (●) indicate key columns.
Empty bullets (○) and arrows indicate columns that refer to keys.

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

logical design

A

either a specification for tables, keys, and columns, or the process that creates the specification.

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

What does the physical design phase add and specify?

A

Adds indexes and specifies how tables are organized on storage media.

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

How is physical design specified?

A

With SQL statements such as CREATE INDEX.

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

Why are physical design diagrams not commonly used?

A

Because logical design is more important for database users and programmers.

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

How do logical and physical design affect queries differently?

A

Logical design affects the query result, while physical design affects query processing speed but not the result.

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

What is data independence?

A

The principle that physical design never affects query results, allowing performance tuning without changes to application programs.

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

Why is data independence important?

A

It allows database designers to tune query performance without changing applications, contributing to the rapid adoption of relational technology in the 1980s.

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

Tuple

A

an ordered collection of elements enclosed in parentheses.

Ex: (a, b, c) and (c, b, a) are different, since tuples are ordered.

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

Data Definition Language (DDL)

A

Defines the structure of the database.

CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);

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

Data Query Language (DQL)

A

Retrieves data from the database

SELECT * FROM Students WHERE Age > 18;

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

Data Manipulation Language (DML)

A

Manipulates data stored in a database

INSERT INTO Students (ID, Name, Age) VALUES (1, ‘Alice’, 20);
UPDATE Students SET Age = 21 WHERE ID = 1;
DELETE FROM Students WHERE ID = 1;

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

Data Transaction Language (DTL)

A

Manages database transactions

BEGIN TRANSACTION;
UPDATE Students SET Age = 22 WHERE ID = 1;
COMMIT;
ROLLBACK;

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

What is the benefit of data independence in relational databases?

A

Applications can be programmed before physical design is in place, running slowly but generating correct results.

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

What is a major limitation of SQL?

A

SQL lacks important programming features and is not object-oriented.

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

How is SQL typically used in database programming?

A

SQL is combined with general-purpose languages like C++, Java, or Python.

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

What is an API in the context of database programming?

A

An API is a library of procedures or classes linking a host programming language to a database.

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

What is JDBC?

A

JDBC is a library of Java classes that access relational databases.

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

Do programming languages support multiple database APIs?

A

Yes, major languages like C++ and Java support several APIs.

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

Why is MySQL popular?

A

Easy to install and use, advanced capabilities, runs on all major OS.

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

What are the two editions of MySQL?

A

MySQL Community (free) and MySQL Enterprise (paid).

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

What is MySQL Community suitable for?

A

Non-commercial applications like education.

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

What does MySQL Enterprise include?

A

MySQL Server and additional administrative applications.

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

What is the MySQL Command-Line Client?

A

A text interface included in the MySQL Server download for connecting to the database server, performing administrative functions, and executing SQL statements.

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

MySQL Workbench

A

GUI of MySQL Server

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

What is a database model?

A

A conceptual framework for database systems with data structures, operations, and rules.

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

What is the relational model based on?

A

A tabular data structure

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

How are the data structure, operations, and rules of the relational model standardized?

A

SQL

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

Which database models were dominant in the 1960s and 1970s?

A

Hierarchical and network databases.

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

Hierarchical Model

A

Data Structure: Tree
Product Release: 1960s
Example System: IMS
Strengths: Fast queries, efficient storage

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

Network Model

A

Data Structure: Linked list
Product Release: 1970s
Example System: IDMS
Strengths: Fast queries, efficient storage

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

Relational Model

A

Data Structure: Table
Product Release: 1980s
Example System: Oracle Database
Strengths: Productivity and simplicity, transactional applications

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

Object Model

A

Data Structure: Class
Product Release: 1990s
Example System: ObjectStore
Strengths: Integration with object-oriented programming languages

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

Graph Model

A

Data Structure: Vertex and edge
Product Release: 2000s
Example System: Neo4j
Strengths: Flexible schema, evolving business requirements

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

Document Model

A

Data Structure: XML, JSON
Product Release: 2010s
Example System: MongoDB
Strengths: Flexible schema, unstructured and semi-structured data

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

Set Theory

A

Concept: The relational data structure is based on set theory.
Example: {a, b, c} and {c, b, a} are the same set.

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

Tuple

A

Concept: A tuple is an ordered collection of elements.
Example: (a, b, c) and (c, b, a) are different tuples.

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

Table Structure

A

Concept: A table has a name, a fixed tuple of columns, and a varying set of rows.

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

Column

A

A column has a name and a data type.

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

Row

A

A row is an unnamed tuple of values, each corresponding to a column.

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

Data Type

A

A data type is a named set of values from which column values are drawn.

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

Row Order

A

Since a table is a set of rows, the rows have no inherent order.

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

Select

A

Selects a subset of rows of a table.

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

Project

A

Eliminates one or more columns of a table.

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

Product

A

Lists all combinations of rows of two tables.

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

Join

A

Combines two tables by comparing related columns.

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

Union

A

Selects all rows of two tables.

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

Intersect

A

Selects rows common to two tables.

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

Difference

A

Selects rows that appear in one table but not another.

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

Rename

A

Changes a table name.

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

Aggregate

A

Computes functions over multiple table rows, such as sum and count.

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

What are relational rules?

A

Logical constraints that ensure data is valid in every relational database.

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

What is a unique primary key?

A

A primary key column, or group of columns, in which values may not repeat.

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

What is the rule about column names in a table?

A

Different columns of the same table must have different names.

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

What is the rule about duplicate rows in a table?

A

No two rows of the same table can have identical values in all columns.

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

What are business rules?

A

Constraints based on business policy and specific to a particular database.

EX: Constraints based on business policy and specific to a particular database.

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

How are relational rules implemented?

A

As SQL constraints and enforced by the database system.

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

What is an SQL statement?

A

A complete command composed of one or more clauses.

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

How should an SQL statement be written for good practice?

A

Each clause should be written on a separate line.

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

What does a clause in an SQL statement group together?

A

SQL keywords (e.g., SELECT, FROM, WHERE), table names (e.g., City), column names (e.g., Name), and conditions (e.g., Population > 100000).

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

What does Data Definition Language (DDL) do?

A

Defines the structure of the database.

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

How do SQL statements end in MySQL?

A

With a semicolon.

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

Are SQL keywords case sensitive?

A

No, SQL keywords like SELECT, FROM, and WHERE are not case sensitive.

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

Are identifiers like column names and table names case sensitive?

A

Yes, in many database systems, identifiers are case sensitive.

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

What does Data Query Language (DQL) do?

A

Retrieves data from the database.

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

What does Data Manipulation Language (DML) do?

A

Manipulates data stored in a database.

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

What does Data Control Language (DCL) do?

A

Controls database user access.

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

What does Data Transaction Language (DTL) do?

A

Manages database transactions.

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

What is an automated script in SQL?

A

A series of SQL statements that is executed repeatedly.

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

What does the SQL statement CREATE DATABASE DatabaseName do?

A

Creates a new database.

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

What does the SQL statement DROP DATABASE DatabaseName do?

A

Deletes a database, including all tables in the database.

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

What does the SHOW statement provide?

A

Information about databases, database contents (tables, columns, etc.), and server status information.

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

What does SHOW DATABASES do?

A

Lists databases available in the database system.

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

What does SHOW TABLES do?

A

Lists tables available in the currently selected database.

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

What does SHOW COLUMNS do?

A

Lists columns available in a specific table named by a FROM clause.

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

What does SHOW CREATE TABLE do?

A

Shows the CREATE TABLE statement for a given table.

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

What is a table in a relational database?

A

A table has a name, a fixed sequence of columns, and a varying set of rows.

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

What does the USE statement do?

A

Selects a database and is required to show information about tables within a specific database.

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

What is a row in a table?

A

An unnamed sequence of values, with each value corresponding to a column and belonging to the column’s data type.

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

What does a column in a table have?

A

A name and a data type.

129
Q

What is a cell in a table?

A

A single column of a single row.

130
Q

What is the minimum and maximum number of columns and rows a table can have?

A

A table must have at least one column but can have any number of rows. A table without rows is called an empty table.

131
Q

What is the rule about values per cell in a table?

A

Exactly one value per cell. Unknown data is represented with a special NULL value.

132
Q

What is the rule about column names in a table?

A

No duplicate column names are allowed in the same table.

133
Q

What is the rule about duplicate rows in a table?

A

No two rows may have identical values in all columns.

134
Q

What is the rule about row order in a table?

A

Rows are not ordered, and the organization of rows on a storage device never affects query results.

135
Q

What is data independence?

A

The principle that allows database administrators to improve query performance by changing the organization of data on storage devices without affecting query results.

136
Q

What does the INT or INTEGER data type store?

A

Integer values.

137
Q

What does the VARCHAR(N) data type store?

A

Values with 0 to N characters.

138
Q

What does the DATE data type store?

A

Date values.

139
Q

What does the DECIMAL(M, D) data type store?

A

Numeric values with M digits, of which D digits follow the decimal point.

140
Q

What does the DROP TABLE statement do?

A

Deletes a table, along with all the table’s rows, from a database.

141
Q

What does the ADD clause in ALTER TABLE do?

A

Adds a column.

EX
ALTER TABLE TableName
ADD ColumnName DataType;

142
Q

What does the CHANGE clause in ALTER TABLE do?

A

Modifies a column.

EX:
ALTER TABLE TableName
CHANGE CurrentColumnName NewColumnName NewDataType;

143
Q

What does the DROP clause in ALTER TABLE do?

A

Deletes a column.

EX:
ALTER TABLE TableName
DROP ColumnName;

144
Q

What is the transpose operation on a table?

A

An operation where rows become columns, and columns become rows.

145
Q

What do integer data types represent?

A

Positive and negative integers.

146
Q

Give examples of common integer data types.

A

INT (4 bytes) and SMALLINT (2 bytes).

147
Q

What do decimal data types represent?

A

Numbers with fractional values.

148
Q

Give examples of common decimal data types

A

FLOAT and DECIMAL

149
Q

What do character data types represent?

A

Textual characters.

150
Q

Give examples of common character data types

A

CHAR (fixed length) and VARCHAR (variable length).

151
Q

What do date and time data types represent?

A

Date, time, or both.

152
Q

Give examples of common date and time data types

A

DATE, TIME, DATETIME, and TIMESTAMP

153
Q

What do binary data types store?

A

Data exactly as it appears in memory or computer files, bit for bit.

154
Q

Give examples of common binary data types

A

BLOB, BINARY, VARBINARY, and IMAGE.

155
Q

What do spatial data types store?

A

Geometric information, such as lines, polygons, and map coordinates.

156
Q

Give examples of spatial data types

A

POLYGON, POINT, and GEOMETRY.

157
Q

What do document data types contain?

A

Textual data in a structured format such as XML or JSON.

158
Q

What are some additional specialized data types supported by databases?

A

MONEY (currency values), BOOLEAN (true-false values), BIT (zeros and ones), and ENUM (a small, fixed set of alternative values).

159
Q

What is the storage size and range of TINYINT?

A

1 byte.
Signed range: -128 to 127
Unsigned range: 0 to 255.

160
Q

What is the storage size and range of SMALLINT?

A

2 bytes
Signed range: -32,768 to 32,767
Unsigned range: 0 to 65,535.

161
Q

What is the storage size and range of MEDIUMINT?

A

3 bytes.
Signed range: -8,388,608 to 8,388,607.
Unsigned range: 0 to 16,777,215.

162
Q

What is the storage size and range of INTEGER or INT?

A

4 bytes
Signed range: -2,147,483,648 to 2,147,483,647
Unsigned range: 0 to 4,294,967,295.

163
Q

What is the storage size and range of BIGINT?

A

8 bytes
8 bytes. Signed range: -2^63 to 2^63 - 1
Unsigned range: 0 to 2^64 - 1.

164
Q

What does DECIMAL(M,D) represent?

A

Exact decimal number where M = number of significant digits, D = number of digits after the decimal point.

165
Q

What is the storage size and range of FLOAT?

A

4 bytes
Approximate decimal numbers with range: -3.4E+38 to 3.4E+38.

166
Q

What is the storage size and range of DOUBLE?

A

8 bytes
Approximate decimal numbers with range: -1.8E+308 to 1.8E+308.

167
Q

What is the format and range of DATE?

A

Format: YYYY-MM-DD.
Range: ‘1000-01-01’ to ‘9999-12-31’.

168
Q

What is the format of TIME?

A

Format: hh:mm:ss.

169
Q

What is the format and range of DATETIME?

A

Format: YYYY-MM-DD hh:mm:ss
Range: ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

170
Q

What is the storage size of CHAR(N)?

A

N bytes
Fixed-length string of length N; 0 ≤ N ≤ 255

171
Q

What is the storage size of VARCHAR(N)?

A

Length of characters + 1 byte
Variable-length string with maximum N characters; 0 ≤ N ≤ 65,535.

172
Q

What is the storage size of TEXT?

A

Length of characters + 2 bytes
Variable-length string with maximum 65,535 characters.

173
Q

What is an operator?

A

A symbol that computes a value from one or more other values, called operands.

174
Q

What do arithmetic operators compute?

A

Numeric values from numeric operands.

175
Q

What do comparison operators compute?

A

Logical values TRUE or FALSE
Operands may be numeric, character, and other data types.

176
Q

What do logical operators compute?

A

Logical values from logical operands.

177
Q

What is a unary operator?

A

An operator with one operand.

178
Q

What is a binary operator?

A

An operator with two operands

179
Q

Give an example of a unary operator

A

The logical operator NOT.

180
Q

Give an example of an operator that can be both unary and binary

A

The arithmetic operator -

181
Q

What happens when either operand is NULL in an operation?

A

Operators may return NULL

182
Q

What does the arithmetic operator + do?

A

Adds two numeric values.
Example: 4 + 3
Value: 7

183
Q

What does the unary arithmetic operator - do?

A

Reverses the sign of one numeric value.
Example: -(-2)
Value: 2

184
Q

What does the binary arithmetic operator - do?

A

Subtracts one numeric value from another.
Example: 11 - 5
Value: 6

185
Q

What does the arithmetic operator * do?

A

Multiplies two numeric values.
Example: 3 * 5
Value: 15

186
Q

What does the arithmetic operator / do?

A

Divides one numeric value by another.
Example: 4 / 2
Value: 2

187
Q

What does the arithmetic operator % (modulo) do?

A

Divides one numeric value by another and returns the integer remainder.
Example: 5 % 2
Value: 1

188
Q

What does the arithmetic operator ^ do?

A

Raises one numeric value to the power of another.
Example: 5^2
Value: 25

189
Q

What does the comparison operator = do?

A

Compares two values for equality.
Example: 1 = 2
Value: FALSE

190
Q

What do the comparison operators != and <> do?

A

Compare two values for inequality.
Example: 1 != 2 or 1 <> 2
Value: TRUE

191
Q

What does the comparison operator < do?

A

Compares two values with <.
Example: 2 < 2
Value: FALSE

192
Q

What does the comparison operator <= do?

A

Compares two values with ≤.
Example: 2 <= 2
Value: TRUE

193
Q

What does the comparison operator > do?

A

Compares two values with >.
Example: ‘2019-08-13’ > ‘2021-08-13’
Value: FALSE

194
Q

What does the comparison operator >= do?

A

Compares two values with ≥.
Example: ‘apple’ >= ‘banana’
Value: FALSE

195
Q

What does the logical operator AND do?

A

Returns TRUE only when both values are TRUE.
Example: TRUE AND FALSE
Value: FALSE

196
Q

What does the logical operator OR do?

A

Returns FALSE only when both values are FALSE.
Example: TRUE OR FALSE
Value: TRUE

197
Q

What does the logical operator NOT do?

A

Reverses a logical value.
Example: NOT FALSE
Value: TRUE

198
Q

What is an expression?

A

An expression is a string of operators, operands, and parentheses that evaluates to a single value.
Example: Salary > 34000 AND Department = ‘Marketing’.

199
Q

What is a simple expression?

A

A simple expression may consist of a single column name or a fixed value.
Example: EmployeeName or ‘Maria’.

200
Q

How are expressions evaluated?

A

Column names in expressions are replaced with column values for a specific row, so the expression’s value can vary by row.

201
Q

How does operator precedence affect expressions?

A

Operators are evaluated in the order of precedence. Operators of the same precedence are evaluated left to right. Parentheses override precedence.

202
Q

Can expressions evaluate to NULL?

A

Yes, expressions can evaluate to NULL.

203
Q

Operator Precedence

A

1 - (unary)
2 ^
3 * / %
4 + - (binary)
5 = != < > <= >=
6 NOT
7 AND
8 OR

204
Q

What do the SELECT and FROM clauses specify?

A

The SELECT clause specifies expressions to return values for each row, and the FROM clause specifies the table from which rows are selected.

205
Q

How do you write a SELECT statement with expressions?

A

SELECT Expression1, Expression2, … FROM TableName;

206
Q

How do you write a SELECT statement with column names?

A

SELECT Column1, Column2, … FROM TableName;

207
Q

How do you select all columns in a table?

A

Use an asterisk: SELECT *
FROM TableName;

208
Q

How do you use the LIMIT clause to return only the first 100 rows from a table?

A

SELECT * FROM City LIMIT 100;

209
Q

What is a condition in an expression?

A

A condition is an expression that evaluates to a logical value (TRUE, FALSE, or NULL).

210
Q

What is the purpose of the WHERE clause in a SELECT statement?

A

The WHERE clause specifies a condition for selecting rows. Only rows where the condition is TRUE are selected.

211
Q

What happens to rows when the condition in the WHERE clause is FALSE or NULL?

A

Rows are omitted when the condition is FALSE or NULL.

212
Q

WHERE clause

A

SELECT Expression1, Expression2, …
FROM TableName
WHERE Condition;

213
Q

What is NULL?

A

NULL is a special value representing unknown or inapplicable data. It is not the same as zero or blanks.

214
Q

What is the default behavior for column values regarding NULL?

A

By default, columns may contain NULL values.

215
Q

What does the NOT NULL constraint do?

A

The NOT NULL constraint prevents a column from having a NULL value.

216
Q

What happens when comparison operators are used with NULL values?

A

Comparison operators return NULL when either operand is NULL, so they cannot be used to select NULL values.
Example: =

217
Q

How do you select rows where a value is NULL?

A

Use the IS NULL operator. Example: Value IS NULL returns TRUE when the value is NULL.

218
Q

How do you select rows where a value is not NULL?

A

Use the IS NOT NULL operator. Example: Value IS NOT NULL returns TRUE when the value is not NULL.

219
Q

Logical expressions when concerning a null value

A

TRUE AND TRUE is TRUE.
TRUE AND FALSE is FALSE.
TRUE AND NULL is NULL.

220
Q

Truth Table

A

x y x AND y x OR y
TRUE NULL NULL TRUE
NULL TRUE

FALSE NULL FALSE NULL
NULL FALSE
NULL NULL NULL NULL

x NOT x
NULL NULL

221
Q

What does the INSERT INTO clause specify?

A

The INSERT INTO clause names the table and columns where data is to be added. The keyword INTO is optional.

222
Q

What does the VALUES clause specify?

A

The VALUES clause specifies the column values to be added.
Back: The VALUES clause may list any number of rows in parentheses to insert multiple rows.

223
Q

What happens when columns are omitted from an INSERT statement?

A

When columns are omitted, they are assigned a NULL value. If the column has a NOT NULL constraint, the insert is rejected.

224
Q

How can you specify a default value for a column?

A

Use the DEFAULT keyword and default value after the column name and data type in a CREATE TABLE statement.

225
Q

What happens when a column with a default value is omitted from an INSERT statement?

A

The column is assigned the default value instead of NULL.

226
Q

What does the UPDATE statement do?

A

The UPDATE statement modifies existing rows in a table.

227
Q

What does the SET clause specify in an UPDATE statement?

A

The SET clause specifies the new column values.

228
Q

What is the purpose of the WHERE clause in an UPDATE statement?

A

The WHERE clause specifies which rows are updated. Omitting the WHERE clause results in all rows being updated.

229
Q

What does the DELETE statement do?

A

The DELETE statement deletes existing rows in a table.

230
Q

What follows the FROM keyword in a DELETE statement?

A

The table name whose rows are to be deleted follows the FROM keyword.

231
Q

What is the purpose of the WHERE clause in a DELETE statement?

A

The WHERE clause specifies which rows should be deleted. Omitting the WHERE clause results in all rows being deleted.

232
Q

What does the TRUNCATE statement do?

A

The TRUNCATE statement deletes all rows from a table.

TRUNCATE TABLE TableName;

233
Q

What is a primary key?

A

A primary key is a column, or group of columns, used to identify a row

234
Q

Where is the primary key usually located in table diagrams?

A

The primary key is usually the table’s first column and appears on the left of table diagrams, but its position is not significant to the database.

235
Q

How is the primary key represented in table diagrams in this material?

A

A solid circle (●) precedes the primary key in table diagrams.

236
Q

Can any unique column be specified as a primary key?

A

Yes, any unique column, or group of columns, may be specified as the primary key if the table contains several unique columns.

237
Q

What does the uniqueness rule for primary keys ensure?

A

The uniqueness rule ensures that each value identifies at most one row.

238
Q

What does the NOT NULL rule for primary keys ensure?

A

The NOT NULL rule ensures that each value identifies at least one row

239
Q

What do the combined rules for primary keys ensure?

A

Together, the uniqueness and NOT NULL rules ensure that each primary key value identifies exactly one row.

240
Q

What is a composite primary key?

A

A composite primary key consists of multiple columns used together to identify a row.
Example: (ColumnA, ColumnB).

241
Q

What is the uniqueness requirement for composite primary keys?

A

Values of primary key columns, when grouped together, must be unique. No group of values may repeat in multiple rows.

242
Q

What is the NOT NULL requirement for composite primary keys?

A

No column of a composite primary key may contain a NULL value.

243
Q

What is the minimal requirement for composite primary keys?

A

All primary key columns are necessary for uniqueness. Removing any column makes the key no longer unique.

244
Q

How are simple and composite primary keys different in terms of minimality?

A

Simple primary keys are necessarily minimal, as no column can be removed from a simple key.

245
Q

What does the PRIMARY KEY constraint do in a CREATE TABLE statement?

A

The PRIMARY KEY constraint names the table’s primary key and ensures that a column or group of columns is always unique and non-null.

246
Q

What is an auto-increment column?

A

An auto-increment column is a numeric column that is assigned an automatically incrementing value when a new row is inserted.

247
Q

How is an auto-increment column defined in a CREATE TABLE statement?

A

The AUTO_INCREMENT keyword follows the column’s data type to define an auto-increment column.

248
Q

How are integer primary keys commonly implemented?

A

Integer primary keys are commonly implemented as auto-increment columns.

249
Q

To which columns can AUTO_INCREMENT be applied in MySQL?

A

In MySQL, AUTO_INCREMENT may be applied only to primary key columns.

250
Q

What is a common error with auto-increment primary keys?

A

Inserting values for auto-increment primary keys.

251
Q

What is a common error with primary keys that are not auto-increment columns?

A

Omitting values for primary keys that are not auto-increment columns.

252
Q

Does MySQL allow inserting specific values to auto-increment columns?

A

Yes, MySQL allows it, but overriding auto-increment for a primary key is usually a mistake.

253
Q

What is a foreign key?

A

A foreign key is a column, or group of columns, that refer to a primary key. The data types must be the same, but the names may be different.

253
Q

Do foreign keys obey the same rules as primary keys?

A

No, foreign key values may be repeated and may be NULL.

EX: The Sales and Marketing departments can have the same manager, and the Technical Support department may not have a manager.

254
Q

How are foreign keys represented in table diagrams in this material?

A

An empty circle (○) precedes foreign keys, and an arrow leads to the referenced primary key.

254
Q

What is referential integrity?

A

Referential integrity requires that foreign key values must either be NULL or match some value of the referenced primary key.

254
Q

Can multiple foreign keys refer to the same primary key?

A

Yes, multiple foreign keys can refer to the same primary key.

Example: In the DepartmentStaff table, the Manager and Assistant foreign keys both refer to the ID primary key of Employee.

255
Q

Can a foreign key refer to a primary key in the same table?

A

Yes, a foreign key can refer to a primary key in the same table.

Example: In the EmployeeManager table, the Manager foreign key refers to the ID primary key.

255
Q

What is required for a foreign key that refers to a composite primary key?

A

A foreign key that refers to a composite primary key must also be composite. All columns of the composite foreign key must either be NULL or match the corresponding primary key columns.

255
Q

How is a foreign key constraint added in a CREATE TABLE statement?

A

A foreign key constraint is added with the FOREIGN KEY and REFERENCES keywords.

256
Q

What happens when a foreign key constraint is specified?

A

The database rejects insert, update, and delete statements that violate referential integrity.

257
Q

Foreign Key example

A

CREATE TABLE Department (
Code TINYINT UNSIGNED,
Name VARCHAR(20),
ManagerID SMALLINT UNSIGNED,
PRIMARY KEY (Code),
FOREIGN KEY (ManagerID) REFERENCES Employee(ID)
);

258
Q

FOREIGN KEY and PRIAMRY constraint

A

Parentheses are required around both the foreign key and primary key column names.

Ex: FOREIGN KEY (ShipmentID) REFERENCES Shipment(ID)

259
Q

What is referential integrity?

A

A relational rule requiring foreign key values to be either fully NULL or match a primary key value.

260
Q

What is a fully NULL foreign key?

A

A foreign key where all columns are NULL.

261
Q

Why must foreign keys obey referential integrity?

A

To ensure data consistency and prevent errors in the database.

262
Q

What happens if referential integrity is violated?

A

Violations must be corrected before data is stored in the database.

263
Q

How can referential integrity be violated?

A

A primary key is updated.
A foreign key is updated.
A row containing a primary key is deleted.
A row containing a foreign key is inserted.

264
Q

What is the RESTRICT action in referential integrity?

A

RESTRICT rejects an insert, update, or delete that violates referential integrity.

265
Q

What does the SET NULL action do?

A

SET NULL sets invalid foreign keys to NULL.

266
Q

What is the purpose of the SET DEFAULT action?

A

SET DEFAULT sets invalid foreign keys to the foreign key default value

267
Q

How does the CASCADE action work for primary key updates?

A

CASCADE propagates primary key changes to foreign keys, updating matching foreign keys to the same value.

268
Q

How does the CASCADE action work for primary key deletes?

A

If a primary key is deleted, rows containing matching foreign keys are also deleted.

269
Q

Which actions apply to both primary key updates and deletes, and foreign key inserts and updates?

A

RESTRICT, SET NULL, and SET DEFAULT.

270
Q

Which action applies only to primary key updates and deletes?

A

CASCADE

271
Q

What action does MySQL support for foreign key inserts and updates?

A

MySQL supports only RESTRICT for foreign key inserts and updates.

272
Q

Which actions does MySQL support for primary key updates and deletes?

A

MySQL supports RESTRICT, SET NULL, SET DEFAULT, and CASCADE

273
Q

Where are actions for primary key updates and deletes specified in MySQL?

A

In the optional ON UPDATE and ON DELETE clauses of the FOREIGN KEY constraint.

274
Q

What do the ON UPDATE and ON DELETE clauses determine?

A

They determine what happens to the foreign key when the referenced primary key is updated or deleted.

275
Q

Can different actions be specified for several foreign keys referring to the same primary key?

A

Yes, different actions can be specified for each foreign key.

276
Q

What happens if the ON UPDATE or ON DELETE clause is omitted?

A

RESTRICT is applied.

277
Q

When can SET NULL not be used?

A

When a foreign key is not allowed NULL values.

278
Q

What is a constraint in a database?

A

A rule that governs allowable values in a database, based on relational and business rules.

279
Q

What happens if an insert, update, or delete statement violates a constraint?

A

The database automatically rejects the statement.

280
Q

What is a column constraint?

A

A constraint that appears after the column name and data type in a CREATE TABLE statement, governing values in a single column.

281
Q

Give an example of a column constraint.

A

NOT NULL

282
Q

What is a table constraint?

A

A constraint that appears in a separate clause of a CREATE TABLE statement, governing values in one or more columns.

283
Q

Give an example of a table constraint.

A

FOREIGN KEY.

284
Q

Can some constraint types be defined as either column or table constraints?

A

Yes, for example, a PRIMARY KEY constraint on a single column can appear either in the column declaration or a separate CREATE TABLE clause.

285
Q

How must a PRIMARY KEY constraint on a composite column be defined?

A

As a table constraint.

286
Q

FOREIGN KEY

A

A FOREIGN KEY constraint governs values in both the foreign key column(s) and the referenced primary key column(s). The foreign and primary keys may be either simple or composite. The FOREIGN KEY constraint must appear in a separate clause.

287
Q

NOT NULL

A

The NOT NULL constraint governs values in a single column and must appear in a column declaration.

288
Q

PRIMARY KEY

A

A simple PRIMARY KEY constraint governs values in a single column and may appear either in a column declaration or a separate clause. A composite PRIMARY KEY constraint governs values in multiple columns and must appear in a separate clause.

289
Q

What does the UNIQUE constraint ensure?

A

It ensures that values in a column, or group of columns, are unique.

290
Q

Where can the UNIQUE constraint appear when applied to a single column?

A

Either in the column declaration or a separate clause.

291
Q

Where must the UNIQUE constraint appear when applied to a group of columns?

A

As a table constraint in a separate clause.

292
Q

Is the UNIQUE constraint necessary for primary key columns?

A

No, because primary keys are automatically unique.

293
Q

What does MySQL create for each UNIQUE constraint?

A

An index that stores the values of the unique column, or group of columns, in sorted order.

294
Q

How does MySQL use the index created for a UNIQUE constraint?

A

It searches the index to quickly determine if new values are unique when they are inserted or updated.

295
Q

What is the purpose of the SQL IN operator?

A

The IN operator specifies multiple possible values for a column in the WHERE clause, acting as a shorthand for multiple OR conditions

EX:
SELECT * FROM table_name
WHERE Language IN (‘Dutch’, ‘Kongo’, ‘Albanian’);

296
Q

What does the SQL BETWEEN operator do?

A

It filters values within a specified range, including the boundary values.

EX:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

297
Q

What does the SQL LIKE operator do?

A

It matches text in a column against a specified pattern using wildcards.

298
Q

What are the two wildcards used with LIKE?

A

%: Matches zero or more characters (e.g., 'L%t' matches “Lt”, “Lift”).

_: Matches exactly one character (e.g., 'L_t' matches “Lot” but not “Loot”).

299
Q

Is the LIKE operator case-sensitive?

A

By default, it is case-insensitive but can be made case-sensitive using BINARY (e.g., LIKE BINARY 'L%t')

300
Q

How do you search for % or _ as literal characters?

A

Use a backslash (\) before them, e.g., 'a\%'.

301
Q

What does the SQL DISTINCT clause do?

A

It returns only unique (non-duplicate) rows from a query result.

EX:
SELECT DISTINCT column1, column2, …
FROM table_name
WHERE condition;

302
Q

What does the ORDER BY clause do in SQL?

A

It sorts query results in ascending (default) or descending order based on one or more columns

By default it goes from lowest to highest or A-Z

Add DESC to do highest to lowest

EX:
SELECT columns FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

303
Q

What is a numeric function in SQL?

A

A function that operates on numeric data types (integer or decimal) and returns a numeric result.

304
Q

Q: What does ABS(n) do?

A

A: Returns the absolute value of n.

305
Q

Q: What does LOG(n) do?

A

A: Returns the natural logarithm of n.

306
Q

Q: What does POW(x, y) do?

A

A: Returns x raised to the power of y.

307
Q

Q: What does RAND() do?

A

A: Returns a random number between 0 (inclusive) and 1 (exclusive).

308
Q

Q: What does SQRT(n) do?

A

A: Returns the square root of n.

309
Q

Q: What does CONCAT(s1, s2, ...) do?

A

A: Returns a string by concatenating the arguments.
Example:
SELECT CONCAT(‘Dis’, ‘en’, ‘gage’); – Returns: ‘Disengage’

310
Q

Q: What does LOWER(s) do?

A

A: Converts all characters in s to lowercase.
Example:
SELECT LOWER(‘MySQL’); – Returns: ‘mysql’

311
Q

Q: What does REPLACE(s, from, to) do?

A

A: Replaces all occurrences of from with to in s.
Example:
SELECT REPLACE(‘This and that’, ‘and’, ‘or’); – Returns: ‘This or that’

312
Q

Q: What does SUBSTRING(s, pos, len) do?

A

A: Extracts a substring from s starting at position pos with length len.
EX:
SELECT SUBSTRING(‘Boomerang’, 1, 4); – Returns: ‘Boom’

313
Q

Q: What does TRIM(s) do?

A

A: Removes leading and trailing spaces from s.
Example:
SELECT TRIM(‘ test ‘); – Returns: ‘test’

314
Q

Q: What does UPPER(s) do?

A

A: Converts all characters in s to uppercase.
Example:
SELECT UPPER(‘mysql’); – Returns: ‘MYSQL’

315
Q

Q: What does CURDATE() do?

A

A: Returns the current date in YYYY-MM-DD format.
Example:
SELECT CURDATE(); – Returns: ‘2019-01-25’

316
Q

Q: What does CURTIME() do?

A

A: Returns the current time in HH:MM:SS format.
Example:
SELECT CURTIME(); – Returns: ‘21:05:44’

317
Q

Q: What does NOW() do?

A

A: Returns the current date and time in YYYY-MM-DD HH:MM:SS format.
Example:
SELECT NOW(); – Returns: ‘2019-01-25 21:05:44’

318
Q

Q: What does DATE(expr) do?

A

A: Extracts the date part from a datetime expression.
Example:
SELECT DATE(‘2013-03-25 22:11:45’); – Returns: ‘2013-03-25’

319
Q

Q: What does TIME(expr) do?

A

A: Extracts the time part from a datetime expression.
Example:
SELECT TIME(‘2013-03-25 22:11:45’); – Returns: ‘22:11:45’

320
Q

Q: What do DAY(d), MONTH(d), and YEAR(d) do?

A

A: Extract the day, month, or year from a date.
Examples:
SELECT DAY(‘2016-10-25’); – Returns: 25
SELECT MONTH(‘2016-10-25’); – Returns: 10
SELECT YEAR(‘2016-10-25’); – Returns: 2016

321
Q

Q: What do HOUR(t), MINUTE(t), and SECOND(t) do?

A

A: Extract the hour, minute, or second from a time.
Examples:
SELECT HOUR(‘22:11:45’); – Returns: 22
SELECT MINUTE(‘22:11:45’); – Returns: 11
SELECT SECOND(‘22:11:45’); – Returns: 45

322
Q

Q: What does DATEDIFF(expr1, expr2) do?

A

A: Calculates the difference in days between two dates.
Example:
SELECT DATEDIFF(‘2013-03-10’, ‘2013-03-04’); – Returns: 6

323
Q

Q: What does TIMEDIFF(expr1, expr2) do?

A

A: Calculates the difference in time between two time values.
Example:
SELECT TIMEDIFF(‘10:00:00’, ‘09:45:30’); – Returns: ‘00:14:30’

324
Q

Q: What is an aggregate function in SQL?

A

A: A function that processes values from multiple rows and returns a single summary value.
EX:

COUNT() counts the number of rows in the set.
MIN() finds the minimum value in the set.
MAX() finds the maximum value in the set.
SUM() sums all the values in the set.
AVG() computes the arithmetic mean of all the values in the set.

325
Q

Q: What does the GROUP BY clause do in SQL?

A

A: It groups rows with identical values in specified columns and computes aggregate functions for each group

326
Q

Q: What does the GROUP BY clause do in SQL?

A

A: It groups rows with identical values in specified columns and computes aggregate functions for each group

327
Q

Q: What is an SQL JOIN?

A

A: A JOIN combines rows from two or more tables based on a related column, typically using a foreign key and primary key relationship.

328
Q

Q: What are the main types of SQL Joins?

A

A:
• INNER JOIN: Returns rows with matching values in both tables.
• LEFT JOIN (OUTER): Returns all rows from the left table, with matching rows from the right table or NULL if no match.
• RIGHT JOIN (OUTER): Returns all rows from the right table, with matching rows from the left table or NULL if no match.
• FULL JOIN (OUTER): Returns all rows from both tables, with NULL for non-matching rows.

329
Q

Why are prefixes used in SQL joins?

A

A: To distinguish columns with the same name from different tables by prefixing the table name, e.g., Table1.Column

330
Q

Q: What does an INNER JOIN do?

A

A: Selects only rows with matching values in both the left and right tables.

331
Q

Q: What does a FULL JOIN do?

A

A: Selects all rows from both tables, with NULL for non-matching rows.

332
Q

Q: How does the ON clause work in joins?

A

A: Specifies the condition to match rows between two tables, typically comparing a foreign key in one table to a primary key in another.

333
Q

Q: What does a LEFT JOIN do?

A

A: Returns all rows from the left table and matching rows from the right table. Unmatched right table rows are filled with NULL.

334
Q

Q: What does a RIGHT JOIN do?

A

A: Returns all rows from the right table and matching rows from the left table. Unmatched left table rows are filled with NULL.

335
Q

Q: What is an outer join?

A

A: Any join that includes unmatched rows, such as LEFT JOIN, RIGHT JOIN, or FULL JOIN.

336
Q

Q: Can joins be written without a JOIN clause?

A

A: Yes, joins can be written using alternative methods like UNION or subqueries, but using the JOIN clause is clearer and better practice.

Data Management Foundations D426 (4 decks)

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