SQL Basics Quiz

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What does the % operator do in SQL?

It divides two numbers and returns the quotient.

It returns the integer remainder of a division operation. (correct)

It compares two values for equality.

It combines data from two tables.

In the analysis phase of database design, what are attributes?

Details about the entities. (correct)

Tables that hold data in the system.

Relationships between entities.

Constraints that ensure data integrity.

What SQL command is used to delete rows from a table?

DROP

CLEAR

REMOVE

DELETE (correct)

What is the primary function of the ALTER TABLE command in SQL?

To modify the structure of an existing table. Signup and view all the answers

Which SQL command is used to create a new table?

CREATE TABLE Signup and view all the answers

Which of the following correctly describes the relationships in the analysis phase of database design?

They show how entities are connected. Signup and view all the answers

In SQL, what does the TRUNCATE command accomplish?

It removes all rows from a table without logging each deletion. Signup and view all the answers

What is the effect of using the SET NULL option in SQL?

It sets invalid foreign keys to NULL. Signup and view all the answers

What does the SQL function ABS(n) do?

Returns the absolute value of n Signup and view all the answers

Which SQL function would you use to convert a string to lowercase?

LOWER(s) Signup and view all the answers

What is the purpose of the TRIM(s) function in SQL?

To remove leading and trailing spaces from a string Signup and view all the answers

Which function would return the minute part from a time string?

MINUTE(t) Signup and view all the answers

How is a tuple defined in SQL?

An ordered collection of elements enclosed in parentheses Signup and view all the answers

What does a table in SQL consist of?

A name, fixed columns, and changeable rows Signup and view all the answers

Which SQL function would you use to extract seconds from a time format?

SECOND(t) Signup and view all the answers

In the context of SQL, what does an alias represent?

A temporary name assigned to a column or table Signup and view all the answers

What is the function of COUNT() in SQL?

Counts the number of rows in the dataset Signup and view all the answers

Which type of JOIN in SQL will include all rows from the right table and only matched rows from the left table?

RIGHT JOIN Signup and view all the answers

What is the primary purpose of the UNION operation in SQL?

To generate a single result set from two datasets Signup and view all the answers

What does an INNER JOIN specifically select?

Only rows with matching values in both tables Signup and view all the answers

In the context of database design, what does an Entity Relationship Model (ER diagram) primarily focus on?

Displaying a high-level overview of data entities and relationships Signup and view all the answers

What is the primary responsibility of a database administrator?

Securing the database system from unauthorized access Signup and view all the answers

Which SQL data type is used to store integer values?

TINYINT Signup and view all the answers

What does the Transaction Manager ensure?

Proper execution of transactions Signup and view all the answers

Which data type would you use to store a value like 123.45?

DECIMAL Signup and view all the answers

Which of the following correctly describes 'Query optimization'?

Finds the most efficient way to execute a query Signup and view all the answers

What type of values does the VARCHAR data type store?

Textual values Signup and view all the answers

How many bytes does the INT data type use for storage?

4 bytes Signup and view all the answers

Which role is responsible for turning instructions from the Query Processor into file commands?

Storage Manager Signup and view all the answers

What is the maximum value for an unsigned TINYINT?

255 Signup and view all the answers

Which of the following operations can be performed using the addition operator (+)?

Adding two numeric values Signup and view all the answers

Which type of key is a single-column primary key used when there is no suitable primary key existing?

Artificial Key Signup and view all the answers

What does a Foreign Key reference in a database?

A primary key to enforce referential integrity Signup and view all the answers

Which SQL sublanguage is used specifically for controlling user permissions and access?

Data Control Language (DCL) Signup and view all the answers

In the context of aggregate functions, when is the HAVING clause used?

To filter results after grouping Signup and view all the answers

What does an Auto-increment column do in a database?

It automatically increases by one for each new row Signup and view all the answers

What is described as a column or group of columns used to uniquely identify a row in a table?

Primary Key Signup and view all the answers

Which of the following correctly describes a Composite Primary Key?

Multiple columns used together to identify a row Signup and view all the answers

In the SQL hierarchy, which language is responsible for defining and setting up the structure of the database?

Data Definition Language (DDL) Signup and view all the answers

What is an example of a foreign key in a database schema?

The department an employee manages Signup and view all the answers

Which step involves transforming the ER model into specific database structures?

Logical Design Signup and view all the answers

What type of table uses a hash function to organize rows into buckets?

Hash table Signup and view all the answers

Which of the following correctly defines a subtype entity?

A subset of a supertype entity Signup and view all the answers

What is the primary function of a Materialized View?

To store data persistently, updating as base tables change Signup and view all the answers

Which table structure is characterized by having no specific order for its rows?

Heap table Signup and view all the answers

In database terminology, what does the term 'cardinality' refer to?

Maxima and minima of relationships and attributes Signup and view all the answers

What is the purpose of an Application Programming Interface (API) in the context of databases?

To facilitate communication between SQL and programming languages Signup and view all the answers

Study Notes

Database Roles

Database administrators secure the database system from unauthorized users, controlling user access.
Authorization limits what each user can access.
Rules ensure the database system maintains structural and business consistency.
Query processors read and understand queries, creating plans to retrieve or modify data, and returning results to applications.
Query optimization finds the most efficient way to execute queries.
Storage managers translate query processor instructions into file commands to retrieve or modify data, using indexes for faster location.
Transaction managers ensure transactions execute correctly, prevent conflicts between concurrent transactions, and restore the database in case of failure.

SQL Data Types

INT: Stores integers (positive and negative).

Category	Example	Data Type	Storage	Notes
Integer	34 and -739448	TINYINT	1 byte	Signed: -128 to 127 Unsigned: 0 to 255
		SMALLINT	2 bytes	Signed: -32,768 to 32,767 Unsigned: 0 to 65,535
		MEDIUMINT	3 bytes	Signed: -8,388,608 to 8,388,607 Unsigned: 0 to 16,777,215
		INT	4 bytes	Signed: -2,147,483,648 to 2,147,483,647 Unsigned: 0 to 4,294,967,295
		BIGINT	8 bytes	Signed: -2⁶³ to 2⁶³ -1 Unsigned: 0 to 2⁶⁴ -1

Arithmetic and Comparison Operators

+: Adds two numeric values (e.g., 4 + 3 = 7).
-: Reverses the sign of a numeric value (e.g., -(-2) = 2).
-: Subtracts one numeric value from another (e.g., 11 - 5 = 6).
%: Returns the integer remainder of a division (e.g., 5 % 2 = 1).
=: Compares for equality (e.g., 1 = 2 is FALSE).
: Compares using greater than (e.g., '2019-08-13' > '2021-08-13' is FALSE).
!=: Compares for inequality (e.g., 1 != 2).

Database Design Phases

Analysis: Outlines database needs, focusing on entities (things or people), relationships between entities, and attributes (details about entities).
Logical Design: Converts database requirements into a specific system format (tables, columns, keys).
Physical Design: Optimizes data retrieval speed by adding indexes to data tables.

SQL Commands

CREATE TABLE: Creates a table (e.g., CREATE TABLE employees (...)).
INSERT INTO: Inserts new rows into a table (e.g., INSERT INTO employees).
SELECT: Retrieves data from a table (e.g., SELECT first_name, last_name FROM employees).
UPDATE: Modifies data within a table (e.g., UPDATE employees SET salary = 55000 WHERE employee_id = 101).
DELETE FROM: Removes rows from a table (e.g., DELETE FROM employees WHERE employee_id = 101).
ALTER TABLE: Modifies existing tables (adding or deleting columns) (e.g., ALTER TABLE employees ADD phone_number VARCHAR(15)).

SQL Operators

BETWEEN: Checks if a value falls within a specified range.
LIKE: Matches text against a pattern in a column. Uses '%' for multiple characters and '_' for single characters.
Alias: Temporary names assigned to table columns (e.g., SELECT first_name AS "First Name").

SQL Functions

ABS(n): Returns the absolute value of a number.
LOWER(s): Converts a string to lowercase.
TRIM(s): Removes leading and trailing spaces from a string.
HOUR(t), MINUTE(t), SECOND(t): Extracts the hour, minute, and second from a time value.

Data Structure

Tuple: Ordered collection of elements.
Table: Stores data with columns and rows.
Column: Holds data of a specific type (e.g., number, text).
Row: Holds values for each column.
Cell: A single column within a row.

SQL Elements

Literals: Explicit string, numeric, or binary values.
Keywords: Words with special meaning (e.g., SELECT, FROM, WHERE).
Identifiers: Objects in the database (e.g., tables, columns).
Primary Key: Column uniquely identifying a row.
Simple Primary Key: Single column.
Composite Primary Key: Multi-column.
Artificial Key: Single-column primary key created when no other suitable key exists.
Candidate Key: Simple or composite column that uniquely identifies a row (and is minimal).
Auto-Increment Column: Numeric column that automatically increases with each new row.
Foreign Key: References a primary key (crucial for relational connections).

SQL Sublanguages

DDL(Data Definition Language): Defines database structure (e.g., creating tables).
DQL (Data Query Language): Retrieves data from the database (e.g., SELECT).
DML (Data Manipulation Language): Manipulates table data (e.g., INSERT, UPDATE, DELETE).
DCL (Data Control Language): Controls user permissions (e.g., GRANT, REVOKE).
DTL (Data Transaction Language): Manages database transactions (e.g., COMMIT, ROLLBACK).

Aggregate Functions

COUNT(): Counts rows.
MIN(): Finds the minimum value.
MAX(): Finds the maximum value.
SUM(): Computes the sum of values.
AVG(): Computes the average of values.

JOIN and Union in SQL

Join combines data from multiple tables (e.g., INNER JOIN, FULL JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, UNION).
Equijoins use =, non-equijoins (e.g., <, >).
Cross-join combines data from two tables without matching based on columns.

Entity Relationship Model (ERM)

ER diagram defines entity relations/links.
Entities represent real-world objects (e.g., employees, products).
Relationships show how entities interact (e.g., employee to department).
Attributes of Entities provide details about entities.
Instances are specific instances of entities.
Relationships are defined by Instances between entities.

Table Structures

Heap table: No specific order for rows (useful for bulk loading).
Sorted table: Ordered by a column for fast lookups.
Hash table: uses a hash to place rows into buckets for quick lookup.
Table cluster: Reduces data access times by grouping related tables together.

Other Definitions

API (Application Programming Interface): Allows different programs to communicate and interact.
SQL Client: Text-based interface for interacting with the database.
Business Rules: Policies used within a database (specific to the system).
Subquery (Nested Query/Inner Query): SQL query nested within another query.
Materialized View: a stored representation of a query's result set.
Cardinality: Shows maximum and minimum values of entity relations.
Subtype Entity: set of related entities within a larger supertype.
Normalization: Reduces data redundancy by creating different tables.
Denormalization: Increases redundancy in a database for improved efficiency.
Hit Ratio: Percentage of results retrieved from a database.
Binary Search: A way to locate matching data in a sorted database.
Dense and Sparse Index: Different indexing methods with varying storage characteristics.

Studying That Suits You

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

Description

Test your knowledge of essential SQL commands and concepts, including data manipulation, table management, and attributes in database design. This quiz covers various SQL functions and operators that are critical for effective database management.