Midterm exam for IT Database management systems

what does the symbol % represent in LIKE pattern?

Any number of characters (zero or more)

What does the underscore _ represent?

Exactly one character

The pattern address LIKE 'H%' means:

Starts with H

The pattern address LIKE 'H___' means:

Exactly 4 characters, starts with H

What does the note say about 'H%'?

Starts with H

The pattern address LIKE '%e' means:

Ends with the letter e

Which values match address LIKE '%e'?

Apple, Home, House

The pattern '%e' requires the value to be at least 1 character long.

True

Which values do not match address LIKE '%e'?

Appley, Hi

What is address LIKE '%Staff%' ?

Contains “Staff anywhere“ in the text

What does NOT LIKE 'H%' return?

Does not start with H

SELECT

retrive/get data from a table

COUNT(*)

counts all rows

Which data type can COUNT , MIN and MAX be used with?

numeric + nonnumeric

What does the ORDER BY clause do?

Sorts the results of your SQL query

How many columns can you sort by with ORDER BY?

One or more columns

ASC

Ascending order (small to large)

DESC mean?

Descending order (large to small)

Which data type can SUM and AVG be used with?

Numeric

__________ can be used in SELECT and HAVING

Aggregate functions

What are the five aggregate functions?

COUNT(), SUM(), AVG(), MIN(), MAX().

Aggregate functions normally return?

one result

Does DISTINCT affect MIN() and MAX()?

No, DISTINCT has no effect.

Does DISTINCT affect SUM() and AVG()?

Yes, it can affect both.

Which aggregate functions can be affected by DISTINCT?

SUM() and AVG()

What does GROUP BY do?

Puts rows with the same value into groups

How many summary rows does GROUP BY give you?

One summary row for each group

Which columns can you use with GROUP BY in a SELECT statement?

Columns listed in GROUP BY and aggregate functions

What else can you use in SELECT with GROUP BY?

Aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX().

Any column name in SELECT must also be in GROUP BY, unless?

It is inside an aggregate function like COUNT() or SUM()

Can GROUP BY have extra columns that are not shown in SELECT?

True

If you use WHERE with GROUP BY, what happens first?

WHERE filters the rows

What is the correct order of clauses when using WHERE and GROUP BY in SQL?

SELECT → FROM → WHERE → GROUP BY

What does WHERE do before GROUP BY?

Filters the rows

What happens after WHERE filters the rows?

GROUP BY then groups the remaining rows

Where does HAVING come in a query?

After GROUP BY

What is HAVING commonly used with?

Aggregate functions

What does WHERE filter?

Individual rows

What does HAVING filter?

Groups

What is the correct order of SQL clauses when using WHERE, GROUP BY, and HAVING?

SELECT → FROM → WHERE → GROUP BY → HAVING

What is a subquery?

A SELECT statement inside another SELECT statement.

The query inside another query. It runs first.

Inner Query

The main query that uses the inner query's results.

outer query

Where can subqueries be used?

In the WHERE and HAVING clauses.

Which subquery returns one row and one column?

Scalar subquery

Which subquery returns one row with multiple columns?

Row subquery

Which subquery returns multiple rows and one or more columns?

Table subquery

The five main components of a DBMS environment are:

Hardware, software, Data, procedures, and people

Which component of a DBMS is used for retrieving and updating data?

DML

Which component of a DBMS is used for defining the database structure and controlling access?

DDL

Which operator means “not equal to” in SQL?

<>

What does the DATE data type store?

year-month-day (yyyy-mm-dd)

What does the <= operator mean?

Less than or equal to

What does the > operator mean?

Greater than

An SQL schema is identified by:

A schema name and authorization identifier

List the 4 major advantages of a DBMS

Data independence, Reduced data duplication / controlled redundancy, and Controlled access and integrity

List the 4 major disadvantages of a DBMS?

High cost, complexity, Performance overhead / resource and Size and resource use.

Why can a DBMS be complex?

It requires specialized knowledge to manage

When might a DBMS be slower than a file-based system?

For very simple applications

External level

The user's view of the database; shows the data a specific user needs.

Conceptual Level

The overall view of the database; shows what data is stored and how it is related.

Internal Level

Shows how the database is physically stored on the computer.

JOIN

uses a common column to match rows between 2 tables.

NATURAL JOIN

automatically matches columns with the same name.

What are the three types of OUTER JOIN?

LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

FULL OUTER JOIN?

Combines everything from the left and right table, including matching and unmatched rows.