INSY DBMS final

What is SQL used for?

SQL is used to talk to the database.

Is SQL a full-featured programming language?

No, SQL is not a full-featured programming language.

What type of language is SQL considered?

SQL is a data sublanguage for creating and processing database data and metadata.

Why is SQL programming considered a critical skill?

SQL is ubiquitous in enterprise-class DBMS products.

What is SQL primarily used for in terms of data manipulation?

SQL is used for inserting, querying, modifying, and deleting data.

What are the main SQL data types?

Character/String, Numeric, Date/Time, Binary.

What must be created before a new RDBMS can be used?

The database structure and the tables that will hold the end-user data must be created.

What is a database schema?

A schema is a logical group of database objects, such as tables and indexes, that are related to each other.

What is the SQL command to create a table?

CREATE TABLE tableName ( attr1 data type [constraint],… PRIMARY KEY (attrName), FOREIGN KEY (attrName));

How do you load data into a table in SQL?

INSERT INTO tablename VALUES (val1, val2, …).

What is the purpose of the SELECT statement in SQL?

SELECT identifies the column(s) you want your query to select for your results.

What does the FROM clause do in a SQL query?

FROM identifies the table(s) your query will pull data from.

What is the function of the WHERE clause in SQL?

WHERE specifies record filtering criteria for filtering your results.

What does the GROUP BY clause do in SQL?

GROUP BY specifies how to group the data in your results.

What is the purpose of the HAVING clause in SQL?

HAVING specifies group filtering criteria for filtering your results.

What does the ORDER BY clause do in a SQL query?

ORDER BY specifies the order in which your query results are displayed.

What does the BETWEEN operator do in SQL?

BETWEEN checks whether an attribute value is within a range.

What does the IN operator do in SQL?

IN checks whether an attribute value matches any value within a value list.

What is the function of the LIKE operator in SQL?

LIKE checks whether an attribute value matches a given string pattern.

What does the DISTINCT keyword do in SQL?

DISTINCT limits values to unique values.

What is the basic structure of a SQL query?

SELECT columnName(s) FROM tableNames(s);

How do you query all elements of a table?

SELECT * FROM product;

What is the purpose of the WHERE clause in SQL?

The WHERE clause adds conditional restrictions to the SELECT statement, limiting the rows returned by the query.

What are the comparison operators used in SQL?

= (equal to), < (less than),

How can comparison operators be used with character attributes in SQL?

Comparison operators can be used to place restrictions on character-based attributes.

What is the significance of logical operators in SQL?

Logical operators (AND, OR, NOT) allow the inclusion of multiple conditions in a query.

What is the BETWEEN operator used for in SQL?

The BETWEEN operator is used to filter results within a specified range, e.g., WHERE p_price BETWEEN 50 AND 100.

How does the IN operator function in SQL?

The IN operator allows you to specify multiple values in a WHERE clause, e.g., WHERE v_code IN (21344, 24288).

What does the LIKE operator do in SQL?

The LIKE operator is used for pattern matching in strings, e.g., WHERE v_conact LIKE 'Smith%';.

What is an Inner Join in SQL?

An Inner Join links tables by selecting only the rows with common values in their common attributes.

What is the difference between Equijoin and Theta join in SQL?

Equijoin links tables based on an = condition, while Theta join uses other comparison operators.

What is a Left Outer Join in SQL?

A Left Outer Join returns all records from the left table and matched records from the right table.

What is a Right Outer Join in SQL?

A Right Outer Join returns all records from the right table and matched records from the left table.

What does a Full Outer Join do in SQL?

A Full Outer Join returns all records when there is a match in either left or right table.

What is a workaround for Full Outer Join in MySQL?

MySQL does not support Full Outer Join natively; a workaround is to UNION the results of a Left Join and a Right Join.

How can you use table aliases in SQL joins?

Table aliases can be used to simplify queries, e.g., SELECT cuscode, t1.agent FROM customer AS t1 JOIN agent AS t2 ON t1.agentcode = t2.agent_code.

What is an aggregate function in SQL?

An aggregate function takes a collection of rows and calculates a single summary value.

What is the purpose of the GROUP BY command in SQL?

The GROUP BY command groups rows with the same values to create summary rows, often used with aggregate functions.

What is the HAVING clause used for in SQL?

The HAVING clause is used to filter groups based on a specified condition after aggregation.

What is the COUNT function in SQL?

The COUNT function calculates the number of rows that match a specified condition.

What do the MIN and MAX functions do in SQL?

The MIN function returns the smallest value, while the MAX function returns the largest value in a specified column.

What does the SUM function do in SQL?

The SUM function calculates the total sum of a numeric column.

What does the AVG function do in SQL?

The AVG function calculates the average value of a numeric column.

What is the difference between HAVING and WHERE in SQL?

WHERE filters records before aggregation, while HAVING filters groups after aggregation.

What is the purpose of the WHERE clause in SQL?

The WHERE clause selects a subset of the result based on individual rows and can use operators to filter but cannot compare aggregated results.

How does HAVING differ from WHERE in SQL?

HAVING allows filtering on aggregate functions and is only used in SELECT statements, while WHERE is used for individual row filtering.

What are some examples of built-in SQL functions?

Examples include Date/Time functions (e.g., DATE_FORMAT, YEAR, MONTH), Numeric functions (e.g., ABS, ROUND), and String functions (e.g., CONCAT, UCASE, LCASE).

What is Business Intelligence (BI)?

Business Intelligence is a comprehensive set of tools and processes used to capture, integrate, store, and analyze data to support decision-making.

What is the insights value chain?

The insights value chain consists of Data, Analytics, IT, People, and Processes, and its effectiveness is determined by the weakest link.

What does ETL stand for in Business Intelligence architecture?

ETL stands for Extraction, Transformation, and Loading.

What is the goal of data visualization?

The goal of data visualization is to provide a visual representation of data that enhances comprehension and allows users to identify trends, patterns, and relationships.

What are the main characteristics of Online Analytical Processing (OLAP)?

OLAP systems are characterized by multidimensional data analysis techniques, advanced database support, and easy-to-use end-user interfaces.

How does operational data differ from decision support data?

Operational data has a narrow time span and low granularity, focusing on single transactions, while decision support data has a broader time span, high granularity, and can be examined in multiple dimensions.

What is an attribute hierarchy in data analysis?

An attribute hierarchy allows users to perform drill down and roll up searches across dimensions such as location, product, and time.

What is a data mart?

A data mart is a small, single-subject subset of a data warehouse that provides decision support to a specific group of users.

What is a star schema in data modeling?

The star schema is a data-modeling technique that maps multidimensional decision support data into a relational database, consisting of facts and dimensions.

What are the components of a data warehouse?

The components include being integrated, subject-oriented, time-variant, and nonvolatile.

What is denormalization in database design?

Denormalization is the process of reducing the number of tables in a database to improve system speed, which can be affected by excessive normalization.

What is the significance of the slice and dice operation in data cubes?

Slice and dice operations allow users to view data from different perspectives by selecting specific dimensions and aggregations.

What is the purpose of data visualization tools?

Data visualization tools enable users to zoom in and out, drill up and down, and filter data, making it easier to understand large datasets.

What is the difference between facts and dimensions in a star schema?

Facts are numeric values representing specific business aspects, while dimensions are characteristics that provide additional perspectives to those facts.

What is the role of monitoring and alerting in Business Intelligence?

Monitoring and alerting help track data and system performance, ensuring timely responses to issues and maintaining data integrity.

What are the advantages of current data visualization tools?

They allow for interactive data exploration, making it easier to identify trends and patterns in large datasets.

What is the purpose of query and reporting in Business Intelligence?

Query and reporting enable users to extract meaningful insights from data through structured queries and generate reports for analysis.

What does the term 'nonvolatile' mean in the context of a data warehouse?

Nonvolatile indicates that the data in a data warehouse is stable and does not change frequently, providing a consistent view for analysis.

What is the significance of the data store in Business Intelligence architecture?

The data store is optimized for analysis and speed, allowing efficient data retrieval and processing for reporting and analytics.

What is the importance of capturing value from data?

Capturing value from data requires excellence in all elements of the insights value chain, as each link's effectiveness impacts overall outcomes.